5.7mysql删除索引,MySQL 频繁删除导致的索引选择错误

问题描述

线上出现一个根据索引update字段，却出现全表扫描，并锁定表的case。

监控显示

现象

1. 慢查询日志中出现

# Query_time: 424.767255 Lock_time: 0.000029 Rows_sent: 0 Rows_examined: 142439284SET timestamp=1515479326;UPDATE `Table_XXX` SET `Field_XXX` = 1 WHERE `Key_XXX` = 'YYYYYYY'

2. 表上的其他更新都被这个update blocked，持续7分钟。

实际上字段Key_XXX上有索引，而且从show index上看，结果区分度很好，与主键几乎是1：1的区分度。

说明

1. 出现问题的版本是5.5.39

2. InnoDB 5.6之前的版本，没有Persistent Statistics，因此索引统计信息是动态修改的。触发条件：

1) counter > 2000000000  或者

2) counter > 16 + table->stat_n_rows / 16

3.  这个实例从19号凌晨1点开始到出问题的时候，总共删除了4000w行数据，占表总行数的1/3，因此必然触发了索引统计(不止一次索引统计)

4.  MySQL 5.7.18之前，存在这样的bug: 在做索引重建过程中，会先清空统计信息。由于锁控制问题，可能会导致优化器在获取索引统计的时候，得到一个“空的统计信息”

参考https://bugs.mysql.com/bug.php?id=82968

5. 这样优化器认为只能做全表扫描。如果是一个update语句，就会锁全表。

6. 考虑到这个实例当天删除的数据量，触发这个bug的概率提升。

(说明，以上为间接分析。由于MySQL做索引统计信息重建时并没有明确记录，因此没有找到直接证据。)

解决方案

1. 升级到5.7.18及以上版本

2. 5.6 版本可以在执行批量删除期间，打开Persistent Statistics(innodb_stats_persistent)

3. 对于暂时无法升级的5.5版本

1) 一种方式是通过监控，kill掉异常的update。但是需要人为接入，kill update线程是危险操作

2) 控制删除的量。在开始大量删除之前，利用innodb_stats_on_metadata 主动做一次统计，然后每删除一个批量，在还没有达到1/16的时候，再主动做一次。这样可以确保删除期间不会出现索引重建。当然手动重建时，也是要观察是否影响update。好处只是由于是定点操作，可以主动检查。

根源还是删除导致，这个表一天内删除了1/2的数据，这个设计本身不够合理。这种情况应该将这个表按照天分表，之后直接drop table的形式来做。

查询过程问题记录

查询问题过程中，需要去查看slow log，访问日志(这里是看proxy的访问日志)，期间要看各种访问的时序关系，遇到的几个疑问，在这里记录一下。

问题一：

主从相同的事务 xid不同？

同一个事务，主库binlog的xid和从库relay log的xid是相同的，但是和同一个事务从库上的xid确实不一定相同

MySQL内部xid格式：MYSQL_XID_PREFIX + server_id + my_xid

MYSQL_XID_PREFIX：MySQLXid(源码写死)8 bytes

问题二：

slow记录的语句在binlog里面没有找到记录， 具有和binlog不相同的logid(这里的logid是和业务约定，在每一个语句后面加一个标识，查询问题时候容易在日志中定位具体的SQL语句)？

slow log中记录的并不一定是执行成功的，一个执行失败，或者执行过程中被kill的语句，只要终止的时候时间超过了slowlog设置的阈值，就会记录到slow log。

问题三：

在查询日志的时候我们看到749xxxx这个请求(真正锁表的请求)确实是先发的, 而750xxxx的语句(实际执行成功的语句)则是较晚发送的

因此时间序列是这样的:

时刻A: client1 发起update(749xxxx)，触发全表锁

5.03s后：client1 超时关闭

客户端重试，client2: 发起update (750xxxx)

大概0.1s后，client2 执行完成。

7分钟后， 全表锁的语句被kill，记录slow log，并退出。在这个过程中部分的update被锁住没法执行，部分的update是执行成功的。

问题:既然client1 的事务是7分钟才完成，client2为什么没有被block住？

问题解释：

1.MySQL的行锁是要对于满足过滤条件的行，一行行读取并锁定的。

2.client2的update，由于用上索引，因此很快定位到行。

3.client1 的update由于触发bug走全表扫描，表又比较大(>1亿行)，因此锁需要时间才能遍历到行。

4. 也就是说client2 ”后发先至“，加锁、执行、写日志完成时，client1的事务还没有锁到这行。

因此client2的执行没有被client1的update锁住。

PS:从上面序列中发现，在客户端关闭请求的时候，db端并没有停止执行。其实既然客户端已经关闭了，继续执行意义不大，尤其是对于select语句，MySQL客户端在这种时候会主动发一个kill去把执行的命令停止掉。因此建议proxy能够模拟MySQL这个行为。

问题四：

开启innodb表持续统计的一些参数

(1) stats_persistent  对于innodb表是否保证持续统计ALTER TABLE table_name stats_persistent=1默认是由innodb_stats_persistent选项决定的(2) stats_auto_recalc 对于innodb表是否自动计算持续统计默认是由innodb_stats_auto_recalc 选项决定的，为1 时，当有10%的数据发生改变时，就重新计算,按照我的测试大概超过10%(3) stats_sample_pages 指定随机索引页的数量