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 table's partitioning function
           

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

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

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

以主键ID为例。 
我们陷入了误区就是，我们一直认为加了分区字段前ID是唯一，加了分区字段后ID还是唯一的？ 
我们忽略的一点是，我们的表结构变了，索引结构变了，现在的唯一键并不能保证之前的字段是唯一的了，换句话说就是，Mysql的分区表并不能保证单字段唯一。 
原因是MySQL的分区表索引都是local的（在Oracle中，local也不要能保证全局唯一）。 
所以，MySQL希望你做妥协，要么，你唯一索引去掉，这样你分区表随便建，要么，你把分区键加进之前的唯一索引中，这样MySQL能保证组合唯一。 
因为Oracle的索引以及表的物理存储和MySQL不一样。所以，Oracle普通的索引依然能保证单字段唯一，因为Oracle的普通索引不是local的。
————————————————
参考链接：https://blog.csdn.net/cscscscsc/article/details/79932610
           

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

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