记一次数据库结构优化经验-水平分表

mysql -hlocalhost -P3306 -uroot -proot --database 待分库;
use 待分库;
#游标用法可参考https://www.huaweicloud.com/articles/315219a239f3707c3f240e5a4b159f4d.html
#创建存储过程之前判断该存储过程是否已存在，若存在则删除
DROP PROCEDURE IF EXISTS xxx;
#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
delimiter $$
#创建存储过程
CREATE PROCEDURE xxx()
BEGIN
# declare定义的变量类似java类中的局部变量，仅在方法中生效。即只在存储过程中的begin和end之间生效。
# @set定义的变量，叫做会话变量，也叫用户定义变量，在整个会话中都起作用(比如某个应用的一个连接过程中)，即这个变量可以在被调用的存储过程或者代码之间共享数据。
DECLARE `@arrayCount` int(11);
DECLARE `@arrayContent` text;
#以上声明变量
#将结果集赋值给变量
select GROUP_CONCAT(distinct(分片键)  SEPARATOR ',') into `@arrayContent` from 待分表名; #查询出所有分片键,以逗号分割
select count(distinct(分片键)) into `@arrayCount` from 待分表名; #计算表的条数

# 针对已分完的表
# SELECT group_concat(distinct(TABLE_NAME) SEPARATOR ',') into `@arrayContent` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='库名' and TABLE_NAME like '表名前缀_%';
# SELECT count(distinct(TABLE_NAME)) into `@arrayCount` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='库名' and TABLE_NAME like '表名前缀_%';

set @i = 1;
WHILE @i<(`@arrayCount`+1)
    DO
#(SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i+1),',',-1)): 以逗号分割先截取前i个,再以逗号分割截取最后1个(SUBSTRING_INDEX从1开始)
#cast as utf8: 解决Illegal mix of collations for operation 'concat'
#cast更多可用于类型转换(等同convert)
#可以转换的类型是有限制的。这个类型可以是以下值其中的一个：
#    二进制，同带binary前缀的效果 : BINARY    
#    字符型，可带参数 : CHAR()     
#    日期 : DATE     
#    时间: TIME     
#    日期时间型 : DATETIME     
#    浮点数 : DECIMAL      
#    整数 : SIGNED     
#    无符号整数 : UNSIGNED 
SET @sqlCreateTable = CONCAT(CAST('CREATE TABLE 表名前缀_' AS CHAR CHARACTER SET utf8), (SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i),',',-1)),
'建表语句');
prepare stmt from @sqlCreateTable;
execute stmt;
#以上动态创建表
SET @sqlCreateIndex = CONCAT(CAST('create index idx_xxx1
    on 表名前缀_' AS CHAR CHARACTER SET utf8),(SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i),',',-1)),'(索引字段);');
prepare stmt from @sqlCreateIndex;
execute stmt;
SET @sqlCreateIndex = CONCAT(CAST('create index idx_xxx2
    on 表名前缀_' AS CHAR CHARACTER SET utf8),(SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i),',',-1)),'(索引字段);');
prepare stmt from @sqlCreateIndex;
execute stmt;
SET @sqlCreateIndex = CONCAT(CAST('create index idx_xxx3
    on 表名前缀_' AS CHAR CHARACTER SET utf8),(SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i),',',-1)),'(索引字段1,索引字段2);');
prepare stmt from @sqlCreateIndex;
execute stmt;
#以上动态创建索引
SET @sqlInsert = CONCAT(CAST('INSERT 表名前缀_' AS CHAR CHARACTER SET utf8), (SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i),',',-1)),
    '(详细列名)
select 详细列名 from 待分表名 where 分片键 = ',(SUBSTRING_INDEX(SUBSTRING_INDEX(`@arrayContent`,',',@i),',',-1)),';');
prepare stmt from @sqlInsert;
execute stmt;
#以上表数据迁移（如果分片键类型是字符串，where后面要记得加双引号，不然会报错unkonwn colunm xxx where cause...）
SET @i = @i+1;
END WHILE;
END $$
#将语句的结束符号恢复为分号
delimiter ;

#这里参考https://www.programmersought.com/article/6362493524/
#解决Row * was cut by GROUP_CONCAT()
SET SESSION group_concat_max_len = 102400;
#执行
call xxx();

- 核对两个count数
use mysql;
SELECT count(distinct(table_name)) from information_schema.columns 
where table_name like '${prefix}%' 
and TABLE_SCHEMA = '${table_schema}';
SELECT count(distinct(分片键)) from 待分表名;