记录一次索引失效引发全表扫描的解决过程

项目场景：

提示：这里简述项目相关背景：

开开心心上着班，突然收到Sql审计平台发过来一条警告：
“高风险！XXX涉及全表扫描，sql审查不通过” >_<

赶紧打开测试库，拿到刚刚的那条sql，explain一下，我倒要看看是哪里在搞古怪!（撸袖子）

---

问题描述

提示：这里描述项目中遇到的问题：

首先是去代码里看了这个SQL，确实在上个版本动过，这是个统计数量的sql，改的只是统计的字段而已，按理说没有影响。可能是触发了增量扫描，把以前的问题暴露了。
然后去测试库explain，获得以下结果：

Cannot use ref access on index ‘XXX_IDX’ due to type or collation conversion on field ‘XXX’

---

原因分析：

提示：这里填写问题的分析：

从结果可以看到，possible_keys 是两个，分别是graphic_user_oper_ACTION_TYPE_IDX和graphic_user_oper_GRAPHIC_ID_IDX
但是实际使用的 key，只有第一个ACTION_TYPE，输出中也可见问题是出在是GRAPHIC_ID上。给出的原因是“due to type or collation conversion”，翻译过来大概是指字段的类型或排序规则转换的问题。
这个SQL使用了左连接，GRAPHIC_ID也是连接条件，于是我去查询了两张表GRAPHIC_ID，都是varchar，看来问题是出在排序规则了。
表A：

---

表B：

果然，两张表虽然字符集都是utf8mb4，但是他们排序规则不相同，一个是utf8mb4_bin，一个是utf8mb4_0900_ai_ci。
utf8mb4_bin：这是一个二进制排序规则，按照字节值进行比较，对大小写敏感，对重音符号敏感（带有音调的字符，如：é）。
utf8mb4_0900_ai_ci：按照字符的语义内容进行比较，不考虑字节值，大小写不敏感，对重音符号不敏感（é和e）。

解决方案：

提示：这里填写该问题的具体解决方案：

在了解两种排序方式不会对数据库有什么影响后，结合其他表的排序都是utf8mb4_bin，决定把表b的排序方式改为utf8mb4_bin。
现在，让我们再次运行explain，哈哈哈，成功吧！

什么！还是这个问题（捂脸）type也确认了，表排序规则也确认了，为什么为什么！至此，我陷入了久久懵逼中，不断对比两张表的差别，甚至怀疑是索引有问题，在一阵无奈中，双击了一下Graphic_id 字段：

好哇，原来是你！至此，问题解决。

写在最后，其他索引失效的情况

总结下索引失效的其他原因吧，欢迎补充：

1. 索引列参与运算：当索引列参与到运算中时，可能会导致索引失效。例如，id = 2 - 1 这样的表达式会导致数据库进行全表扫描，因为数据库需要先计算出结果，然后再进行匹配，这使得索引无法被有效利用。
2. 索引列使用函数：如果索引列上使用了函数，如 SUBSTR(id_no,1,3) = ‘100’，这会导致索引失效，因为数据库需要先对数据进行函数处理，然后再进行匹配，这同样使得索引无法被有效利用。
3. 错误的Like使用：在使用模糊匹配时，如果通配符 % 出现在模式的首部，如 id_no like ‘%00%’，则无法使用索引。这是因为索引是基于有序的数据构建的，而这种模式匹配破坏了这种顺序。
4. 类型隐式转换：如果查询条件中的数据类型与索引列的类型不匹配，可能会导致类型隐式转换，从而使得索引失效。例如，如果 id_no 字段类型为 varchar，但在查询中使用了 int 类型的值，如 id_no = 1002，则可能导致索引失效。
5. 使用OR操作：在使用 OR 连接多个条件时，如果其中一个条件没有使用索引，可能会导致整个查询语句的索引失效。例如，id = 2 or username = ‘Tom2’，如果 username 字段没有索引，那么即使 id 字段有索引，查询也可能不会使用索引。
6. 两列做比较：如果查询条件中对两个列进行了比较操作，如 id > age，即使这两个列都有索引，索引也可能失效。
7. 不等于比较：使用不等于操作符 <> 可能会导致索引失效，因为这种操作通常需要全表扫描来确保没有遗漏。