【踩坑总结】Mysql统计信息异常问题及解决

由于是公司日常运维遇到的问题，文档中实际表名均用sam_table进行替换。

问题背景及描述

问题背景：
sam_table表是一个分库分表，目前是有200张分表，表数据行数每日在200w+，且每周都有对该表进行归档处理(从该数据库集群拷贝到另一数据库集群，且会对数据进行delete操作)。
问题描述：
从21年12月25日起，在服务中某个接口对该表数据进行查询时，第52分表一定会造成慢查，对用户体验带来较大问题。
慢查语句大概为：

select id, column1, column2, column3, column4 from sam_table52 where column1 = '143001521507852' and column2 in('data1', 'data2', 'data3'......'data325');

数据库内存在column1与column2的索引。

问题原因

慢查根本原因： 该接口进行查询时mysql查询优化器选择了错误的索引导致查询扫描行数过多引起慢查。
1 索引选择错误的原因： 查询的SQL语句中，有用到in进行条件限制，且in中的参数量大于200，在Mysql中对于in语句的查询机制有提到，即当in内参数小于200的时候，会走index dive（通过访问索引来计算扫描行），大于200的时候走index statistic（通过统计信息来计算扫描行）。
关于index dive与index statistic：

index dive：MySQL 5.6之后增加了一个新的系统变量eq_range_index_dive_limit，用于IN子句的代价计算，在MySQL 5.7.3之前它的默认值是10，之后调整为200。如果IN子句中参数的个数小于等于200，查询优化器会在执行真正的查询前率先访问索引来计算需要扫描的索引记录数量，
index statistic：如果IN子句中参数的个数大于200，查询优化器会使用之前在后台产生的一些统计数据去估算匹配的二级索引记录的条数，如果扫描的行数过大，不会再走这个索引。
index statistic相比 index dive速度更快，但准确性较低。

因此通过上述的查询条件和MySql的in语句查询机制，可以将问题定位到查询语句执行时，走了index statistic方法来计算查询代价。
2 走index statistic方法代价计算错误的原因： 走index statistic方法会通过统计信息来计算sql扫描行数，扫描行数计算错误的最大的可能就是统计信息异常，通过查询该库表统计信息，发现主库内column1和column2的统计信息均为1（上述查询语句在代码中有强制走主库规范）。
查询语句：

select * from information_schema.STATISTICS where TABLE_NAME = 'sam_table52';

查询结果：

查询结果中各个字段的含义参考：mysql8 参考手册-INFORMATION_SCHEMA STATISTICS表

其中CARDINALITY的含义是基数，基数是数据表一列中不同值的数目,也可以认为是查询运算符返回的行数。当查询具有索引的列时，每个列的基数会影响哪个访问方法最有效。
简单概括：基数越大，索引的区分度就越大，选择该索引的可能性就越大。
因此可以得出结论，走index statistic方法代价计算错误的原因即该分表的统计信息异常。

3 该分表的统计信息异常的原因：
25日该问题发生之前，24日对数据库进行过一次归档操作，且归档频率为每周一次，归档过程中会不断delete数据，长期执行会导致主集群的表统计信息不准确。
统计信息的相关内容可参考文档：MySQL的统计信息学习总结

至此，该问题的原因可以理解为：24日不断对数据库进行delete操作，导致52分表的统计信息错误，25题查询时该统计信息错误加上SQL语句中in的参数量大于200导致索引选择错误，最终导致扫描行数过多，造成慢查询。

问题解决

解决方法：刷新表的统计信息。
通过下列sql语句，进行表重建，刷新统计信息。

alter table sam_table52 engine = InnoDB

此外，analyze table也可以刷新统计信息，但是analyze语句原生执行会对表加锁，可能会对业务造成影响。
alter table 不仅能对表的统计信息进行刷新，还可以释放表空洞，释放DB容量。

表空洞：
对数据表进行delete操作(insert也会)时，引擎只会标记这条数据记录为删除(逻辑删除)，并未将数据占用的空间返还给操作系统，这些被占用的空间即表空洞。
在这里可以先将表空洞产生的原因大概理解为上述，底层（B+树）原因后续有时间继续学习整理。

后续如何预防该问题的发生？

定期进行表空洞清理和重建表信息，使表中数据量稳定，避免问题发生