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

距离上一次更新该文章已经过了 630 天，文章所描述的內容可能已经发生变化，请留意。

最近一个项目模块的数据库要进行结构调整优化,所以这里记录一下

首先我们是做汽车后市场业务的产品, 所有的业务和车型的sku数据息息相关,所以关于车型服务模块的压力在同比当中是
比较大的。而目前我们的问题是qps并不高,但是数据库的cpu却经常7/8十

为什么会这样?

主要是该模型库的数据量庞大,数据表结构厚,导致在qps并不高的时候cpu消耗也比较高.
所以大qps,小cpu消耗是我们解决问题的方向

前景有了,接下来就是解决问题的方案

`数据库调整`,怎么调整?

从表思考两个方向

长度-数据量
厚度-表结构
上游调用需要频率

当然具体探讨流程就不叙述了(这里涉及到业务的东西很多),最终领导敲板车型库其中两个数据量庞大,调用频率高的表按分片键水平分表

这里记一下整体调整的过程

接口服务调整

plaintext

1 2	1. 所有有关这俩表的crud都必须带上分片键 2. 既要保证兼容,又要确保可拓展(其实很简单,就是分片键传参粒度高些)

分表及数据迁移(存储过程)

sql

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();

执行sql文件

第一种方法（进入mysql客户端后执行）：

source xx.sql

第二种方法：

mysql -h {host} -P {port} –u用户名 –p密码 –D数据库 < {sql脚本文件路径全名}

如果在 sql 脚本文件中使用了 use 数据库，则-D数据库选项可以忽略

分表后的核验

sql

- 核对两个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 待分表名;