MySQL 中使用索引一定有效吗？如何排查索引效果？

回答重点

索引不一定有效。

最终是否使用上索引是由MySQL成本计算决定的，评估CPU和I/O成本，最终选择用辅助索引还是全表扫描。有时候确实是全表扫描成本低（如数据量小），所以没有用索引；也有时候因为统计数据的不准确，导致成本估算误判，没用索引。

索引失效场景

1. 查询条件不符合最左匹配原则

2. 索引中使用了计算（select * from user where id + 10 = 20;）

3. 索引上使用了函数（select * from user where LOWER(name) like 'xx%';）

4. like滥用（%xx或者%xx%，因为索引是从左至右进行排序查找的，占位符放在最左侧，可能会导致全表扫描，索引失效）

5. or使用不当（select * from user where name = 'xx' or age = 18;如果只有一个索引name，此时age是没有索引的，也可能会导致索引失效）

6. 条件两侧字段类型不一致（如果name字段是varchar类型，select * from user where name = 1;在代码中涉及了隐式转换，等于select * from user where CAST(name AS signed int) = 1;这就变成了第三条的例子，在索引上使用了函数，导致索引失效。相同的例子很多，如日期相关字段作为条件，一般都会先转换成对应类型后再判断，切记是转换入参，而不是索引字段，CREATE_DT = STR_TO_DATE('2023-10-05', '%Y-%m-%d')）

7. 不同的参数也会导致索引失效（是否用上索引是根据MySQL成本计算决定的，不同参数的MySQL评估成本不一致，在复杂查询（联表、子查询、回表等）情况下，有时候会选择全表扫描）

8. 表中两个不同字段进行比较（id = age）

9. order by使用不当（后面排序的字段不是主键/覆盖索引）

排查索引效果

使用explain命令，在SQL前加上explain命令，查看MySQL选择的执行计划，可以了解到是否使用了索引，使用了什么索引，估算的行数等信息。

主要属性：

• type（访问类型）：如果查询走索引，通常type为index或者range；如果这个值为ALL，则表示该查询执行了全表扫描，并没有使用索引。

• key（使用的索引）：如果查询使用了索引，会显示索引的名称；如果这个值为NULL，则表示该查询没有走索引。

• rows（扫描的行数）：这个属性显示了查询扫描的行数，需要评估下扫描数据量。

为什么索引生效，反而查询变慢了呢？

确认是否正确使用索引，MySQL根据优化器会评估成本，选择对应的索引，有时候MySQL也可能估计值不准确，导致选错了索引，查询速度反而更慢。