项目中MySQL遇到的索引失效的问题

前言

最近做项目，遇到一些数据库MySQL日常使用中的一些问题，记录心得，主要是索引失效，基本上是无意中发生，在生产中很可能出现查询超时，全表扫描等导致业务失败。有些是不命中索引，有些是索引失效，原因各异。

准备

MySQL数据库，笔者使用9.2.0版本，一般使用8.0版本

问题

数据隐式转换

实际上我们在编程中一般都会无意识的出现隐式转换的现象，实际上大部分场景是没有任何影响的。但是在MySQL数据库中则会出现索引失效的问题。

随意建一张表

对addrId使用字符串存储，然后建索引，一般而言，其实反之亦然。

随意insert一些数据，最好超过500条，数据量少时，MySQL会出现自己会判断全表扫描更优的情况。笔者先测试500条数据。

然后查看使用索引字段查询的执行计划，可以看到使用数字查询没有走索引

这里就是隐式转换的坑，MySQL隐式转换可以正常操作数据，但是在查询时就会全表扫描，这个时候就不仅仅是慢SQL的问题了，如果出现一定量的数据，比如100W，会造成数据库本身的CPU使用率非常高，性能极差。

使用字符串查询试试

可以看到走了索引，实际上表数据量越大越明显，现在几百条数据，实际上全表扫描也没什么问题，毕竟现在数据库基本上都是SSD存储数据。

如果代码使用数字（int），而数据库使用字符串（varchar）是经常可能会出现的场景，问题很好排查，就是MySQL慢日志就能发现，但是这种情况没法及时修复，需要改代码。而且数据量小的时候不会有什么问题，不容易发现。

另外关于MySQL的索引字段使用varchar还是int类型：结论是能使用int就使用int，根据来源于B+树🌲的存储结构，字符串varchar的排序效率低于int，而且字符串的排序规则与int也是有区别的。

而我们经常使用基本上是int类型的数值比较。

MySQL统计信息收集

MySQL（其实其他数据库也存在这种现象）会根据数据的增删改进行统计信息的收集，一般而言对索引影响不大，但是在短时间大量的数据变化时，尤其是进行删除或者truncate时，统计信息来不及收集，会导致新增大量数据无法命中索引。实际上这种情况出现在分析型数据库居多，比如TIDB等。比如有批处理，使用某个表缓存数据

1. 执行前清空表truncate
2. 然后短时间大批量写入，比如100w条
3. 再通过查询执行处理任务

即使查询的字段有建索引，也会面临索引失效的问题。实际上可以配置数据的变化超过一定的数量，MySQL自动触发更新统计信息分析，但是数据量变化太多，会更新不及时，导致MySQL认为全表扫描更有效。而且默认情况MySQL说是24小时失效一次（information-schema-statistics-table）。

Columns in STATISTICS that represent table statistics hold cached values. The information_schema_stats_expiry system variable defines the period of time before cached table statistics expire. The default is 86400 seconds (24 hours). If there are no cached statistics or statistics have expired, statistics are retrieved from storage engines when querying table statistics columns. To update cached values at any time for a given table, use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry=0. 

我这里新增了500条数据，元数据统计还是19条。以9.2版本MySQL为例：https://dev.mysql.com/doc/refman/9.2/en/optimizer-statistics.html

解决方法：

1. use ANALYZE TABLE. To always retrieve the latest statistics directly from storage engines, set information_schema_stats_expiry=0

写入大量数据后，手动执行ANALYZE TABLE；或者设置缓存失效时间为一个较小的值，但频繁失效会造成MySQL统计分析的负担。

2. hint强制索引，因为我们明确知道数据量，所以可以强制MySQL走索引，提高查询效率。

总结

实际上项目中为了快速上线，很多时候都考虑到了数据库的各种情况，索引的优化，但是毕竟业务需求多，难免会在项目的某个时候没注意就出现了索引失效的情况，而且刚开始数据量较小，实际上也没啥影响，但是随着数据的增加，索引一旦失效，在大表查询时，查询缓慢，数据库CPU消耗高。所以本质上需要建立一套验证环境SQL全表扫描的监控工具，避免问题的在实际场景发生。