admin管理员组文章数量:1608599
问题场景
- 某数据上传接口经常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/cscscscsc/article/details/79932610
4, 最终决定放弃使用分区表
1)分区表,分区键设计不太灵活(这个看第三点就知道,分区键必须是属于主键属于uniqueid。。。),如果不走分区键,很容易出现全表锁
2)一旦数据量并发量上来,如果在分区表实施关联,就是一个灾难
3)自己分库分表,自己掌控业务场景与访问模式,可控。分区表,研发写了一个sql,都不确定mysql是怎么玩的,不太可控
版权声明:本文标题:mysql - 单表千万数据插入速度过慢解决方法-分库分表分区 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://www.elefans.com/dianzi/1728542389a1162897.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论