遗失的角落
mysql表分区
2023-02-24
undefined
00
请注意，本文编写于 427 天前，最后修改于 425 天前，其中某些信息可能已经过时。

目录

1. 概述
2.完成一个空的分区表创建
2.1 Create Table:
2.2 创建表后, 数据库目录下会生成3个分区的表空间
2.3 分区查询
2.4 查看分区字段的信息
2.5 删除分区
3. 对已经存在的数据表进行range分区
3.1 备份表, 只有插入数据
3.2 删除表
3.3 创建表, 要根据已经创建表格式, 将需要分区的字段作为主键或复合主键
3.4 表不存在分区, 将主键字段作为范围去分区, timestamp类型只能使用UNIX_TIMESTAMP函数
3.5 添加分区， 已经存在分区了
3.6 删除分区, 上面已经有命令
4. 对已经存在的数据表进行list分区
4.1 创建表, 要根据已经创建表格式, 将需要分区的字段作为主键或复合主键
4.2 表不存在分区, 对一个字段可以存在的范围去定义分区. 而且只能插入满足的定义分区的数据
5. 脚本小结

1. 概述

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

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

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

查看是否支持分区

MariaDB []> SHOW PLUGINS;
NameStatusType
partitionACTIVEtSTORAGE ENGINE

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

2.1 Create Table:

bash
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个分区的表空间

bash
	[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

插入测试数据:

bash
	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 分区查询

bash
mysql 5.6之后才引入分区查询
	MariaDB [test]> SELECT * FROM test1 partition(p2);
  • 查看分区
	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();
  • 查看分区数目 
    MariaDB [test]> SELECT count(PARTITION_NAME) FROM INFORMATION_SCHEMA.partitions  WHERE TABLE_NAME LIKE 'cdr' AND TABLE_SCHEMA LIKE SCHEMA();
  • 根据创建的分区范围界定来查询数据, 确定是对应哪个分区
	MariaDB [test]> explain partitions SELECT * FROM test1 WHERE insertTime="2019-03-30 01:00:03";
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest1p1ALLNULLNULLNULLNULL2Using where
    MariaDB [test]> explain partitions SELECT * FROM test1 WHERE insertTime="2019-03-27 01:00:03";
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest1p0ALLNULLNULLNULLNULL2Using where
	MariaDB [test]> explain partitions SELECT * FROM test1 WHERE insertTime="2019-03-31 01:00:03";
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsExtra
1SIMPLEtest1p2ALLNULLNULLNULLNULL2Using where

2.4 查看分区字段的信息

  • 查看最小插入和最大插入时间
select min(insertTime),max(insertTime) from test1;
  • 查看每天插入数据的条数
 select date(insertTime),count(*) from test group by date(insertTime);

2.5 删除分区

  • 命令行删除
MariaDB [test]> alter table test1 drop partition p0;
  • 数据库下对应的分区会消失
[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
  • 分区中涵盖的数据也会消失
SELECT count(*) FROM test1;

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

3.1 备份表, 只有插入数据

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

3.2 删除表

DROP TABLE tb1;

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

  • 查看原表创建语句
bash
SHOW CREATE TABLE tb1;
  • 修改原来的语句, 生成新的语句
bash
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函数

bash
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 添加分区， 已经存在分区了

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

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

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

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

  • 查看原表创建语句
bash
SHOW CREATE TABLE tb1;
  • 修改原来的语句, 生成新的语句
bash
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 表不存在分区, 对一个字段可以存在的范围去定义分区. 而且只能插入满足的定义分区的数据

bash
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. 脚本小结

bash
#!/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

本文作者:mykernel

本文链接:

版权声明:本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！