MySQL上亿大表如何深度优化呢
背景
xx实例（一主一从）xxx告警中每天凌晨在报sla报警，该报警的意思是存在一定的主从延迟（若在此时发生主从切换，需要长时间才可以完成切换，要追延迟来保证主从数据的一致性）
xx实例的慢查询数量最多（执行时间超过1s的sql会被记录），xx应用那方每天晚上在做删除一个月前数据的任务
基于 spring boot + mybatis plus + vue & element 实现的后台管理系统 + 用户小程序，支持 rbac 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址：https://github.com/yunaiv/ruoyi-vue-pro
视频教程：https://doc.iocoder.cn/video/
分析
使用pt-query-digest工具分析最近一周的mysql-slow.log
pt-query-digest --since=148h mysql-slow.log | less
结果第一部分
最近一个星期内，总共记录的慢查询执行花费时间为25403s，最大的慢sql执行时间为266s，平均每个慢sql执行时间5s，平均扫描的行数为1766万
结果第二部分
select arrival_record操作记录的慢查询数量最多有4万多次，平均响应时间为4s，delete arrival_record记录了6次，平均响应时间258s。
select xxx_record语句
select arrival_record 慢查询语句都类似于如下所示，where语句中的参数字段是一样的，传入的参数值不一样select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 1400' and '2019-03-25 1500' and receive_spend_ms>=0g
select arrival_record 语句在mysql中最多扫描的行数为5600万、平均扫描的行数为172万，推断由于扫描的行数多导致的执行时间长
查看执行计划
explain select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 1400' and '2019-03-25 1500' and receive_spend_ms>=0g;*************************** 1. row ***************************id: 1select_type: simpletable: arrival_recordpartitions: nulltype: refpossible_keys: ixfk_arrival_recordkey: ixfk_arrival_recordkey_len: 8ref: constrows: 32261320filtered: 3.70extra: using index condition; using where1 row in set, 1 warning (0.00 sec)
用到了索引ixfk_arrival_record，但预计扫描的行数很多有3000多w行
show index from arrival_record;+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| arrival_record | 0 | primary | 1 | id | a | 107990720 | null | null | | btree | | || arrival_record | 1 | ixfk_arrival_record | 1 | product_id | a | 1344 | null | null | | btree | | || arrival_record | 1 | ixfk_arrival_record | 2 | station_no | a | 22161 | null | null | yes | btree | | || arrival_record | 1 | ixfk_arrival_record | 3 | sequence | a | 77233384 | null | null | | btree | | || arrival_record | 1 | ixfk_arrival_record | 4 | receive_time | a | 65854652 | null | null | yes | btree | | || arrival_record | 1 | ixfk_arrival_record | 5 | arrival_time | a | 73861904 | null | null | yes | btree | | |+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+show create table arrival_record;..........arrival_spend_ms bigint(20) default null,total_spend_ms bigint(20) default null,primary key (id),key ixfk_arrival_record (product_id,station_no,sequence,receive_time,arrival_time) using btree,constraint fk_arrival_record_product foreign key (product_id) references product (id) on delete no action on update no action) engine=innodb auto_increment=614538979 default charset=utf8 collate=utf8_bin |
该表总记录数约1亿多条，表上只有一个复合索引，product_id字段基数很小，选择性不好
传入的过滤条件 where product_id=26 and receive_time between '2019-03-25 1400' and '2019-03-25 1500' and receive_spend_ms>=0 没有station_nu字段，使用不到复合索引 ixfk_arrival_record的 product_id,station_no,sequence,receive_time 这几个字段
根据最左前缀原则，select arrival_record只用到了复合索引ixfk_arrival_record的第一个字段product_id，而该字段选择性很差，导致扫描的行数很多，执行时间长
receive_time字段的基数大，选择性好，可对该字段单独建立索引，select arrival_record sql就会使用到该索引
现在已经知道了在慢查询中记录的select arrival_record where语句传入的参数字段有 product_id，receive_time，receive_spend_ms，还想知道对该表的访问有没有通过其它字段来过滤了？
神器tcpdump出场的时候到了
使用tcpdump抓包一段时间对该表的select语句
tcpdump -i bond0 -s 0 -l -w - dst port 3316 | strings | grep select | egrep -i 'arrival_record' >/tmp/select_arri.log
获取select 语句中from 后面的where条件语句
ifs_old=$ifsifs=$''for i in `cat /tmp/select_arri.log `;do echo ${i#*'from'}; done | lessifs=$ifs_oldarrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=17 and arrivalrec0_.station_no='56742'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s7100'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4631'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s9466'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4205'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4105'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4506'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4617'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s8356'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s8356'select 该表 where条件中有product_id,station_no,sequence字段，可以使用到复合索引ixfk_arrival_record的前三个字段
综上所示，优化方法为，删除复合索引ixfk_arrival_record，建立复合索引idx_sequence_station_no_product_id，并建立单独索引indx_receive_time
delete xxx_record语句
该delete操作平均扫描行数为1.1亿行，平均执行时间是262s
delete语句如下所示，每次记录的慢查询传入的参数值不一样
delete from arrival_record where receive_time < str_to_date('2019-02-23', '%y-%m-%d')g
执行计划
explain select * from arrival_record where receive_time >${log_file} 2>& 1 red_echo onlie ddl stop red_echo add foreign key mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -eset sql_log_bin=0;alter table $db_.${table_name} add constraint _fk_${table_name}_product foreign key (product_id) references cq_new_cimiss.product (id) on delete no action on update no action; >>${log_file} 2>& 1 check_las_comm $? add foreign key error red_echo add foreign key stopred_echo start slavemysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -estart slavecheck_las_comm $? start slave failed
*执行时间 *
2019-04-08-1136 stop slavemysql: [warning] using a password on the command line interface can be insecure.ddl_start2019-04-08 1136ddl_stop2019-04-08 11132019-04-08-1113 onlie ddl stop2019-04-08-1113 add foreign keymysql: [warning] using a password on the command line interface can be insecure.2019-04-08-1248 add foreign key stop2019-04-08-1248 start slave
*再次查看delete 和select语句的执行计划 *
explain select count(*) from arrival_record where receive_time =0g;*************************** 1. row ***************************id: 1select_type: simpletable: arrival_recordpartitions: nulltype: rangepossible_keys: idx_product_id_sequence_station_no,idx_receive_timekey: idx_receive_timekey_len: 6ref: nullrows: 291448filtered: 16.66extra: using index condition; using where都使用到了idx_receive_time 索引，扫描的行数大大降低
索引优化后
delete 还是花费了77s时间
delete from arrival_record where receive_time < str_to_date('2019-03-10', '%y-%m-%d')g
delete 语句通过receive_time的索引删除300多万的记录花费77s时间*
delete大表优化为小批量删除
*应用端已优化成每次删除10分钟的数据（每次执行时间1s左右），xxx中没在出现sla（主从延迟告警） *
*另一个方法是通过主键的顺序每次删除20000条记录 *
#得到满足时间条件的最大主键id#通过按照主键的顺序去 顺序扫描小批量删除数据#先执行一次以下语句 select max(id) into @need_delete_max_id from `arrival_record` where receive_time<'2019-03-01' ; delete from arrival_record where id<@need_delete_max_id limit 20000; select row_count(); #返回20000#执行小批量delete后会返回row_count(), 删除的行数#程序判断返回的row_count()是否为0，不为0执行以下循环，为0退出循环，删除操作完成 delete from arrival_record where id<@need_delete_max_id limit 20000; select row_count();#程序睡眠0.5s
总结
表数据量太大时，除了关注访问该表的响应时间外，还要关注对该表的维护成本（如做ddl表更时间太长，delete历史数据）。
对大表进行ddl操作时，要考虑表的实际情况（如对该表的并发表，是否有外键）来选择合适的ddl变更方式。
对大数据量表进行delete，用小批量删除的方式，减少对主实例的压力和主从延迟。
戴尔XPS 13二合一7390值不值得买
什么是自适应控制_自适应控制基本原理
江西省2011年大学生电子设计自选赛赛题
多功能快速充电器,Quick Charger
AMD的第二代锐龙ThreadRipper正式发布，官方发布性能指标，与酷睿i9-7980XE哪个更好？
MySQL上亿大表如何深度优化呢
什么是超融合数据保护？
3个关于光模块温度范围的FAQ
台湾为何如此重视Micro-LED显示技术？
基于MC8051内核的便携幅频特性测试仪设计[图]
电动机软启动的原理图
骁龙875跑分成绩曝光，和苹果A14差距明显
电影产业复兴开始，专业CMOS迎来新一波创新
国家发布互联网平台指南，大数据杀熟或被认定垄断
TE助力中国电建海外秀实力
基于和欣嵌入式操作系统实现的一个构件化的网络协议栈设计浅析
交流感应电机的设计与操作理论
炒币没前途？如何看待区块链？
雷达系统之单脉冲跟踪技术
微软、Intel、IBM等合力把计算机推广到发达国家的千家万户
xx实例（一主一从）xxx告警中每天凌晨在报sla报警，该报警的意思是存在一定的主从延迟（若在此时发生主从切换，需要长时间才可以完成切换，要追延迟来保证主从数据的一致性）
xx实例的慢查询数量最多（执行时间超过1s的sql会被记录），xx应用那方每天晚上在做删除一个月前数据的任务
基于 spring boot + mybatis plus + vue & element 实现的后台管理系统 + 用户小程序，支持 rbac 动态权限、多租户、数据权限、工作流、三方登录、支付、短信、商城等功能
项目地址：https://github.com/yunaiv/ruoyi-vue-pro
视频教程：https://doc.iocoder.cn/video/
分析
使用pt-query-digest工具分析最近一周的mysql-slow.log
pt-query-digest --since=148h mysql-slow.log | less
结果第一部分
最近一个星期内，总共记录的慢查询执行花费时间为25403s，最大的慢sql执行时间为266s，平均每个慢sql执行时间5s，平均扫描的行数为1766万
结果第二部分
select arrival_record操作记录的慢查询数量最多有4万多次，平均响应时间为4s，delete arrival_record记录了6次，平均响应时间258s。
select xxx_record语句
select arrival_record 慢查询语句都类似于如下所示，where语句中的参数字段是一样的，传入的参数值不一样select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 1400' and '2019-03-25 1500' and receive_spend_ms>=0g
select arrival_record 语句在mysql中最多扫描的行数为5600万、平均扫描的行数为172万，推断由于扫描的行数多导致的执行时间长
查看执行计划
explain select count(*) from arrival_record where product_id=26 and receive_time between '2019-03-25 1400' and '2019-03-25 1500' and receive_spend_ms>=0g;*************************** 1. row ***************************id: 1select_type: simpletable: arrival_recordpartitions: nulltype: refpossible_keys: ixfk_arrival_recordkey: ixfk_arrival_recordkey_len: 8ref: constrows: 32261320filtered: 3.70extra: using index condition; using where1 row in set, 1 warning (0.00 sec)
用到了索引ixfk_arrival_record，但预计扫描的行数很多有3000多w行
show index from arrival_record;+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| table | non_unique | key_name | seq_in_index | column_name | collation | cardinality | sub_part | packed | null | index_type | comment | index_comment |+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| arrival_record | 0 | primary | 1 | id | a | 107990720 | null | null | | btree | | || arrival_record | 1 | ixfk_arrival_record | 1 | product_id | a | 1344 | null | null | | btree | | || arrival_record | 1 | ixfk_arrival_record | 2 | station_no | a | 22161 | null | null | yes | btree | | || arrival_record | 1 | ixfk_arrival_record | 3 | sequence | a | 77233384 | null | null | | btree | | || arrival_record | 1 | ixfk_arrival_record | 4 | receive_time | a | 65854652 | null | null | yes | btree | | || arrival_record | 1 | ixfk_arrival_record | 5 | arrival_time | a | 73861904 | null | null | yes | btree | | |+----------------+------------+---------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+show create table arrival_record;..........arrival_spend_ms bigint(20) default null,total_spend_ms bigint(20) default null,primary key (id),key ixfk_arrival_record (product_id,station_no,sequence,receive_time,arrival_time) using btree,constraint fk_arrival_record_product foreign key (product_id) references product (id) on delete no action on update no action) engine=innodb auto_increment=614538979 default charset=utf8 collate=utf8_bin |
该表总记录数约1亿多条，表上只有一个复合索引，product_id字段基数很小，选择性不好
传入的过滤条件 where product_id=26 and receive_time between '2019-03-25 1400' and '2019-03-25 1500' and receive_spend_ms>=0 没有station_nu字段，使用不到复合索引 ixfk_arrival_record的 product_id,station_no,sequence,receive_time 这几个字段
根据最左前缀原则，select arrival_record只用到了复合索引ixfk_arrival_record的第一个字段product_id，而该字段选择性很差，导致扫描的行数很多，执行时间长
receive_time字段的基数大，选择性好，可对该字段单独建立索引，select arrival_record sql就会使用到该索引
现在已经知道了在慢查询中记录的select arrival_record where语句传入的参数字段有 product_id，receive_time，receive_spend_ms，还想知道对该表的访问有没有通过其它字段来过滤了？
神器tcpdump出场的时候到了
使用tcpdump抓包一段时间对该表的select语句
tcpdump -i bond0 -s 0 -l -w - dst port 3316 | strings | grep select | egrep -i 'arrival_record' >/tmp/select_arri.log
获取select 语句中from 后面的where条件语句
ifs_old=$ifsifs=$''for i in `cat /tmp/select_arri.log `;do echo ${i#*'from'}; done | lessifs=$ifs_oldarrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=17 and arrivalrec0_.station_no='56742'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s7100'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4631'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s9466'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4205'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4105'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4506'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=24 and arrivalrec0_.station_no='v4617'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s8356'arrival_record arrivalrec0_ where arrivalrec0_.sequence='2019-03-27 08:40' and arrivalrec0_.product_id=22 and arrivalrec0_.station_no='s8356'select 该表 where条件中有product_id,station_no,sequence字段，可以使用到复合索引ixfk_arrival_record的前三个字段
综上所示，优化方法为，删除复合索引ixfk_arrival_record，建立复合索引idx_sequence_station_no_product_id，并建立单独索引indx_receive_time
delete xxx_record语句
该delete操作平均扫描行数为1.1亿行，平均执行时间是262s
delete语句如下所示，每次记录的慢查询传入的参数值不一样
delete from arrival_record where receive_time < str_to_date('2019-02-23', '%y-%m-%d')g
执行计划
explain select * from arrival_record where receive_time >${log_file} 2>& 1 red_echo onlie ddl stop red_echo add foreign key mysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -eset sql_log_bin=0;alter table $db_.${table_name} add constraint _fk_${table_name}_product foreign key (product_id) references cq_new_cimiss.product (id) on delete no action on update no action; >>${log_file} 2>& 1 check_las_comm $? add foreign key error red_echo add foreign key stopred_echo start slavemysql -uroot -p$passwd --socket=/datas/mysql/data/${port}/mysqld.sock -estart slavecheck_las_comm $? start slave failed
*执行时间 *
2019-04-08-1136 stop slavemysql: [warning] using a password on the command line interface can be insecure.ddl_start2019-04-08 1136ddl_stop2019-04-08 11132019-04-08-1113 onlie ddl stop2019-04-08-1113 add foreign keymysql: [warning] using a password on the command line interface can be insecure.2019-04-08-1248 add foreign key stop2019-04-08-1248 start slave
*再次查看delete 和select语句的执行计划 *
explain select count(*) from arrival_record where receive_time =0g;*************************** 1. row ***************************id: 1select_type: simpletable: arrival_recordpartitions: nulltype: rangepossible_keys: idx_product_id_sequence_station_no,idx_receive_timekey: idx_receive_timekey_len: 6ref: nullrows: 291448filtered: 16.66extra: using index condition; using where都使用到了idx_receive_time 索引，扫描的行数大大降低
索引优化后
delete 还是花费了77s时间
delete from arrival_record where receive_time < str_to_date('2019-03-10', '%y-%m-%d')g
delete 语句通过receive_time的索引删除300多万的记录花费77s时间*
delete大表优化为小批量删除
*应用端已优化成每次删除10分钟的数据（每次执行时间1s左右），xxx中没在出现sla（主从延迟告警） *
*另一个方法是通过主键的顺序每次删除20000条记录 *
#得到满足时间条件的最大主键id#通过按照主键的顺序去 顺序扫描小批量删除数据#先执行一次以下语句 select max(id) into @need_delete_max_id from `arrival_record` where receive_time<'2019-03-01' ; delete from arrival_record where id<@need_delete_max_id limit 20000; select row_count(); #返回20000#执行小批量delete后会返回row_count(), 删除的行数#程序判断返回的row_count()是否为0，不为0执行以下循环，为0退出循环，删除操作完成 delete from arrival_record where id<@need_delete_max_id limit 20000; select row_count();#程序睡眠0.5s
总结
表数据量太大时，除了关注访问该表的响应时间外，还要关注对该表的维护成本（如做ddl表更时间太长，delete历史数据）。
对大表进行ddl操作时，要考虑表的实际情况（如对该表的并发表，是否有外键）来选择合适的ddl变更方式。
对大数据量表进行delete，用小批量删除的方式，减少对主实例的压力和主从延迟。
戴尔XPS 13二合一7390值不值得买
什么是自适应控制_自适应控制基本原理
江西省2011年大学生电子设计自选赛赛题
多功能快速充电器,Quick Charger
AMD的第二代锐龙ThreadRipper正式发布，官方发布性能指标，与酷睿i9-7980XE哪个更好？
MySQL上亿大表如何深度优化呢
什么是超融合数据保护？
3个关于光模块温度范围的FAQ
台湾为何如此重视Micro-LED显示技术？
基于MC8051内核的便携幅频特性测试仪设计[图]
电动机软启动的原理图
骁龙875跑分成绩曝光，和苹果A14差距明显
电影产业复兴开始，专业CMOS迎来新一波创新
国家发布互联网平台指南，大数据杀熟或被认定垄断
TE助力中国电建海外秀实力
基于和欣嵌入式操作系统实现的一个构件化的网络协议栈设计浅析
交流感应电机的设计与操作理论
炒币没前途？如何看待区块链？
雷达系统之单脉冲跟踪技术
微软、Intel、IBM等合力把计算机推广到发达国家的千家万户