mysql表分区

发表于 | 更新于 | 分类于 | 评论数： | 阅读次数：
|

1. 概述

官方文档：https://dev.mysql.com/doc/refman/5.7/en/partitioning-overview.html

  1. 通过删除仅包含该数据的分区（或多个分区），通常可以轻松地从分区表中删除失去其实用性的数据。相反，在某些情况下，通过添加一个或多个新分区来特别存储该数据，可以极大地促进添加新数据的过程。

  2. 由于满足给定WHERE子句的数据只能存储在一个或多个分区上，因此可以大大优化某些查询，这会自动从搜索中排除任何剩余的分区。由于在创建分区表后可以更改分区，因此可以重新组织数据以增强在首次设置分区方案时可能不常使用的频繁查询。这种排除不匹配分区（以及它们包含的任何行）的能力通常称为 分区修剪。

查看是否支持分区

1
MariaDB []> SHOW PLUGINS;
Name Status Type
partition ACTIVEt STORAGE ENGINE

2.完成一个空的分区表创建

2.1 Create Table:

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sip` varchar(32) NOT NULL, 
`insertTime` timestamp NOT NULL,
PRIMARY KEY (`id`,`insertTime`)
)ENGINE=InnoDB  
PARTITION BY RANGE (UNIX_TIMESTAMP(`insertTime`))(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2019-03-30 00:00:00')),
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2019-03-31 00:00:00')),
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2019-04-01 00:00:00'))
);

2.2 创建表后, 数据库目录下会生成3个分区的表空间

1
2
3
4
5
6
7
[root@localhost ~]# ls /var/lib/mysql/test -lh
总用量 41M
-rw-rw----. 1 mysql mysql 14K 3月  29 11:37 test1.frm
-rw-rw----. 1 mysql mysql  44 3月  29 11:37 test1.par
-rw-rw----. 1 mysql mysql 96K 3月  29 11:38 test1#P#p0.ibd
-rw-rw----. 1 mysql mysql 96K 3月  29 11:38 test1#P#p1.ibd
-rw-rw----. 1 mysql mysql 96K 3月  29 11:38 test1#P#p2.ibd

插入测试数据:

1
2
3
INSERT INTO test1(sip,insertTime) VALUES ('1.1.1.4','2019-03-27 01:00:03'); #p0
INSERT INTO test1(sip,insertTime) VALUES ('1.1.1.4','2019-03-30 01:00:03'); #p1
INSERT INTO test1(sip,insertTime) VALUES ('1.1.1.4','2019-03-31 01:00:03'); #p2

2.3 分区查询

1
2
mysql 5.6之后才引入分区查询
	MariaDB [test]> SELECT * FROM test1 partition(p2);

  • 查看分区

    1
    MariaDB [test]> SELECT TABLE_SCHEMA, TABLE_NAME, PARTITION_NAME, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.partitions  WHERE TABLE_NAME LIKE 'cdr' AND TABLE_SCHEMA LIKE SCHEMA();

  • 查看分区数目

    1
    MariaDB [test]> SELECT count(PARTITION_NAME) FROM INFORMATION_SCHEMA.partitions  WHERE TABLE_NAME LIKE 'cdr' AND TABLE_SCHEMA LIKE SCHEMA();

  • 根据创建的分区范围界定来查询数据, 确定是对应哪个分区

    1
    MariaDB [test]> explain partitions SELECT * FROM test1 WHERE insertTime="2019-03-30 01:00:03";
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE test1 p1 ALL NULL NULL NULL NULL 2 Using where 
1
MariaDB [test]> explain partitions SELECT * FROM test1 WHERE insertTime="2019-03-27 01:00:03";
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE test1 p0 ALL NULL NULL NULL NULL 2 Using where 
1
MariaDB [test]> explain partitions SELECT * FROM test1 WHERE insertTime="2019-03-31 01:00:03";
id select_type table partitions type possible_keys key key_len ref rows Extra
1 SIMPLE test1 p2 ALL NULL NULL NULL NULL 2 Using where

2.4 查看分区字段的信息

  • 查看最小插入和最大插入时间

    1
    select min(insertTime),max(insertTime) from test1;

  • 查看每天插入数据的条数 

    1
    select date(insertTime),count(*) from test group by date(insertTime);

2.5 删除分区

  • 命令行删除

    1
    2
    MariaDB [test]> alter table test1 drop partition p0;
    `

  • 数据库下对应的分区会消失

    1
    2
    3
    4
    5
    6
    [root@localhost ~]# ls /var/lib/mysql/test -lh
    	总用量 41M
    	-rw-rw----. 1 mysql mysql 14K 3月  29 11:41 test1.frm
    	-rw-rw----. 1 mysql mysql  40 3月  29 11:41 test1.par
    	-rw-rw----. 1 mysql mysql 96K 3月  29 11:40 test1#P#p1.ibd
    	-rw-rw----. 1 mysql mysql 96K 3月  29 11:40 test1#P#p2.ibd

  • 分区中涵盖的数据也会消失

    1
    SELECT count(*) FROM test1;

3. 对已经存在的数据表进行range分区

3.1 备份表, 只有插入数据

1
mysqldump --no-create-info testdb tb1 > cdrtest.sql

3.2 删除表

1
DROP TABLE tb1;

3.3 创建表, 要根据已经创建表格式, 将需要分区的字段作为主键或复合主键

  • 查看原表创建语句 

    1
    SHOW CREATE TABLE tb1;

  • 修改原来的语句, 生成新的语句

    1
    2
    3
    4
    5
    6
    CREATE TABLE `tb1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `sip` varchar(32) NOT NULL,
      `insertTime` timestamp NOT NULL,
      PRIMARY KEY (`id`,`insertTime`)
    ) ENGINE=InnoDB;

3.4 表不存在分区, 将主键字段作为范围去分区, timestamp类型只能使用UNIX_TIMESTAMP函数

1
2
3
4
5
ALTER TABLE tb1 PARTITION by range(UNIX_TIMESTAMP(insertTime))(
partition par0 values less than (UNIX_TIMESTAMP('2016-02-01 00:00:00')),
partition par1 values less than (UNIX_TIMESTAMP('2019-03-29 00:00:00')),
partition par2 values less than (UNIX_TIMESTAMP('2019-03-30 00:00:00'))
);

3.5 添加分区， 已经存在分区了

1
ALTER TABLE cdr add partition (partition par3 values less than (UNIX_TIMESTAMP('2019-04-01 00:00:00')));

3.6 删除分区, 上面已经有命令

4. 对已经存在的数据表进行list分区

4.1 创建表, 要根据已经创建表格式, 将需要分区的字段作为主键或复合主键

  • 查看原表创建语句 

    1
    SHOW CREATE TABLE tb1;

  • 修改原来的语句, 生成新的语句

    1
    2
    3
    4
    5
    6
    CREATE TABLE `tb1` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `sip` varchar(32) NOT NULL,
      `insertTime` timestamp NOT NULL,
      PRIMARY KEY (`id`,`response_code`)
    ) ENGINE=InnoDB;

4.2 表不存在分区, 对一个字段可以存在的范围去定义分区. 而且只能插入满足的定义分区的数据

1
2
3
4
5
6
7
8
9
10
ALTER TABLE cdr PARTITION BY list(response_code)
(
partition p200 values in (200),
partition p404 values in (404),
partition p503 values in (503),
partition p901 values in (901),
partition p902 values in (902),
partition p903 values in (903),
partition p0 values in (0)
);

5. 脚本小结

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
#!/bin/bash
#

user='root'
password=''
DB='test'
TABLE='cdr'
backpath=/var/tmp/${TABLE}insert.sql

pre3date=`date -d"-3 days" +%Y-%m-%d`
curdate=`date +%Y-%m-%d`
after3date=`date -d"+3 days" +%Y-%m-%d`

function reset() {
# 判断表是否支持分区
if ! mysql -u${user} -p${password} -D ${DB} -e "show variables like '%partition%'" | grep -e "have_partitioning" | grep -q "YES"; then
	echo "no support PARTITION"
	exit 1
fi

# 备份
if ! mysqldump -u${user} -p${password} --no-create-info --single-transaction -R -E --triggers ${DB} ${TABLE} > ${backpath}; then
	echo error.....
fi

# 删除
mysql -u${user} -p${password} -D ${DB} << EOF
DROP TABLE IF EXISTS ${TABLE};
CREATE TABLE ${TABLE} (
  id int(11) NOT NULL AUTO_INCREMENT,
  sip varchar(32) NOT NULL,
  insertTime timestamp,
  PRIMARY KEY ($1,$2)
) ENGINE=InnoDB;
EOF

# 导入
mysql -u${user} -p${password} -D ${DB} < ${backpath}

# 删除备份
rm -rf ${backpath}
}

function cron_range() {
# 判断分区数
count=`mysql -u${user} -p${password} -D ${DB} -e "SELECT count(PARTITION_NAME) FROM INFORMATION_SCHEMA.partitions  WHERE TABLE_NAME LIKE \"${TABLE}\" AND TABLE_SCHEMA LIKE SCHEMA();" | grep -v "^count" `
echo 分区数量 ${count}
if [ ${count} -ne 0 ]; then
	# 存在分区, 先删除三天前的分区
	parname=par`echo ${pre3date} | tr -d '-'`
	mysql -u${user} -p${password} -D ${DB} -e "ALTER TABLE ${TABLE} drop partition ${parname}"
	if [ $? -ne 0 ]; then
		# 说明还未到第三天, 删除不了
		echo "drop range partition pre3, error...."
	else
		# 刚至第三天, 可以删除, 就增加后三天
		parname=par`echo ${after3date} | tr -d '-'`
		mysql -u${user} -p${password} -D ${DB} -e "ALTER TABLE cdr add partition (partition ${parname} values less than (UNIX_TIMESTAMP(\"${after3date}\")));"
	fi

else
	# 不存在分区,将就今天前和后3天前分区. 3天后的现在过1s就不能写入了.就有分区了
	echo "no partition"
	parname1=par`echo ${curdate} | tr -d '-'`
	parname2=par`echo ${after3date} | tr -d '-'`
	mysql -u${user} -p${password} -D ${DB} -e "ALTER TABLE ${TABLE} PARTITION by range(UNIX_TIMESTAMP(insertTime))(partition ${parname1} values less than (UNIX_TIMESTAMP(\"${curdate}\")), partition ${parname2} values less than (UNIX_TIMESTAMP(\"${after3date}\")));"
fi
}

function cron_list() {
# 判断分区数
count=`mysql -u${user} -p${password} -D ${DB} -e "SELECT count(PARTITION_NAME) FROM INFORMATION_SCHEMA.partitions  WHERE TABLE_NAME LIKE \"${TABLE}\" AND TABLE_SCHEMA LIKE SCHEMA();" | grep -v "^count" `
echo 分区数量 ${count}
if [ ${count} -ne 0 ]; then
	# 存在分区, 重建901
	ls -lhS /opt/mysql/data/cdn_log_system/cdr*
    mysql -u${user} -p${password} -D ${DB} -e "ALTER TABLE cdr DROP PARTITION p901;"
    mysql -u${user} -p${password} -D ${DB} -e "ALTER TABLE cdr ADD PARTITION (PARTITION p901 VALUES IN (901));"
else
	echo "no partition"
    echo "创建分区error_code in (200 404 503 901 902 903)"
	mysql -u${user} -p${password} -D ${DB} -e "ALTER TABLE cdr PARTITION BY list(response_code) ( partition p200 values in (200), partition p404 values in (404), partition p503 values in (503), partition p901 values in (901), partition p902 values in (902), partition p903 values in (903), partition p0 values in (0) );"
fi
}

if [ $UID -eq 0 ]; then
	# 将分区字段转换为主键
#	reset id insertTime
	# 添加range分区
#	cron_range

	# 添加对error_code的list分区
#	reset id response_code
	cron_list
fi
---------------- 谢谢光临 ----------------

本文标题:mysql表分区

文章作者:pxrux

发布时间:2019年04月01日 - 14:04

最后更新:2019年08月13日 - 09:08

原始链接:http://www.mykernel.cn/lcnx-mysql表分区.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

0%