mysql – 单表千万数据插入速度过慢解决方法-分库/分表/分区

问题场景

• 某数据上传接口经常timeout,发现是因为查询的某消费记录数据库，起初定制时没有按照月份进行分表操作，而是按照客户手机尾号进行了分表，因此固定为9个

定位问题原因

• 由于是历史数据，所以单表数据达到千万级别，导致数据过多 因为设置了unique id，导致插入过慢 建议不由mysql本身确定唯一，而是在代码中生成唯一值去做判断（可参考-唯一索引和自建索引的区别） 建议用批量插入可以提高十倍的插入速度（但是在innodb中如果其中一个出错，则着整个插入都会回滚，myisam则可以保证出错前的正常插入）

解决思路

• 改插入为批量插入（已经实施，确实速度上去了，但是遇到失败数据会导致整个失败，只能一个一个插入） 分表或者分库（需要改代码） 分区——–不说了，就用这个了原因如下 分区不用改动代码-美滋滋 分区后面需要新增分区会很方面 分区相当于把table的单个文件拆分成了几个文件，调高了io 分区相当于把table的单个文件拆分成了几个文件，如果要删除历史数据也特别快

分区操作

1. 比较各种分区的不同点，挑选分区类型

• range 分区 基于属于一个给定连续区间的列值，把多行分配给分区。 list 分区 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。 hash 分区 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。 key 分区 类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 COLUMN分区（COLUMN分区是5.5开始引入的分区功能，只有RANGE COLUMN和LIST COLUMN这两种分区；支持整形、日期、字符串；RANGE和LIST的分区方式非常的相似。）

2. 需要根据时间进行分区，时间字段为datetime类型，故选择column分区

• 开始干活 对ctime进行分区

ALTER TABLE tb_consume_0 PARTITION BY RANGE COLUMNS(ctime)
(   
PARTITION p1 VALUES LESS THAN (2019-05-01 00:00:00),
PARTITION p2 VALUES LESS THAN (2019-06-01 00:00:00), 
PARTITION p3 VALUES LESS THAN MAXVALUE 
);

报错

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the tables partitioning function

• 分区字段必须属于主键 解决方法： 修改表主键，构成符合主键 解决方法2： 直接用ID做分区

3，决定重新建表，把ctime塞进主键，做联合主键，然后再对数据进行分区

• 遇到问题： 如果表有uniqueID，则分区键必须是uniqueid的一部分 问题思考：为什么MySQL的分区表中唯一键必须加上分区字段？

4, 最终决定放弃使用分区表

1）分区表，分区键设计不太灵活（这个看第三点就知道，分区键必须是属于主键属于uniqueid。。。），如果不走分区键，很容易出现全表锁
2）一旦数据量并发量上来，如果在分区表实施关联，就是一个灾难
3）自己分库分表，自己掌控业务场景与访问模式，可控。分区表，研发写了一个sql，都不确定mysql是怎么玩的，不太可控