MySQL 中使用索引一定有效吗？如何排查索引效果？
MySQL 中使用索引一定有效吗？如何排查索引效果？
回答重点
索引不一定有效。
例如查询条件中不包含索引列、低基数列索引效果不佳，或查询条件复杂且不匹配索引的顺序。
对于一些小表，MySQL可能选择全表扫描而非使用索引，因为全表扫描的开销可能更小。
最终是否用上索引是根据 MySQL 成本计算决定的，评估 CPU 和 I/O 成本最终选择用辅助索引还是全表扫描。有时候确实是全表扫描成本低所以没用上索引。但有时候由于一些统计数据的不准确，导致成本计算误判，而没用上索引。
排查索引效果的方法：使用 EXPLAIN 命令，通过在查询前加上EXPLAIN，可以查看 MySQL 选择的执行计划，了解是否使用了索引、使用了哪个索引、估算的行数等信息。
主要观察 EXPLAIN 结果以下几点：
- type（访问类型）：这个属性显示了查询使用的访问方法，例如
ALL、index、range等。当查询使用索引时，这个属性通常会显示为index或range，表示查询使用了索引访问。如果这个值是ALL，则表示查询执行了全表扫描，没有使用索引。 - key（使用的索引）：这个属性显示了查询使用的索引，如果查询使用了索引，则会显示索引的名称。如果这个值是
NULL，则表示查询没有使用索引。 - rows（扫描的行数）：这个属性显示了查询扫描的行数，需要评估下扫描量。
扩展知识
确定索引真的生效了吗？
索引失效的场景有很多，也是面试官经常喜欢问的，可以根据具体场景进行排查，典型场景可以分为以下几点（实际索引的选择会根据 mysql 优化器的成本评定，答案最后会提到）：
1）使用了联合索引却不符合最左前缀
举个例子：小鱼对 user 表建立了一个联合索引为 name_age_id 的联合索引。
他使用以下 SQL 查询select * from user where age = 10 and id = 1;
这样的写法恰恰不满足最左前缀原则，索引就失效啦。
2）索引中使用了运算
例如这个 SQL select * from user where id + 3 = 8。这样会导致全表扫描计算 id 的值再进行比较，使得索引失效。
3）索引上使用了函数也会失效
例如：select * from user where LOWER(name) like 'cong%';。这样也会导致索引失效，索引参与了函数处理，会导致去全表扫描。
4） like 的随意使用
例如：select * from user where name like '%cong%';因为索引是从左到右来进行排序查找的，占位符直接放在了最左边开头，可能会导致直接全表扫描，这种情况就会导致索引失效。
5）or 的随意使用
user 当前只有一个索引 name 。此时执行以下SQL ：select * from user where name= 'cong' or age = 18; 这可能也会导致索引失效，因为 age 没有索引。
6）随意的字段类型使用
不小心将 varchar 类型的 name 条件匹配了 int 类型字段。SQL 是这样的 select * from user where name = 1;，在代码中涉及 隐式转换 ！等于 select * from user where CAST(name AS signed int) = 1;，这就变成了第三条索引上使用了函数，导致索引失效。
除此之外还有隐式字符编码转换的问题，即联表查询的时候，如果不同表之间的关联字段字符编码不一致，也会导致隐式转换编码，等于变相用上了函数，使得索引失效。
7）不同的参数也会导致索引失效
这个就是我在回答重点里面说的“是否用上索引是根据 MySQL 成本计算决定的”。不同的参数 MySQL 评估成本不一致，有时候会选择使用索引，有时候会选择全表扫描，特别是在复杂查询（联表、子查询、需要回表等）的情况下。
比如根据商品从订单表查询，收集商品对应的所有买家的订单信息。如果传入的商品 id 是个热点商品，占据这家店铺 80% 的销量，那么本次查询对订单表很可能是全表查询，如果是冷门商品，则很可能是走索引查询。
8）表中两个不同字段进行比较
例如这样的SQL :select * from user where id > age; ，将 id 跟 age 字段做了比较，索引失效。
9）使用了 order by
当 order by 后面跟的 不是主键 或者 不是覆盖索引 会导致不走索引。
为什么索引生效了反而查询变慢了呢？
确认是否选对了索引！MySQL 根据优化器会评估成本选择对应的索引，但有时候 MySQL 因为估计值不准确，导致选错了索引，因此查询速度反而更慢。
关于使用索引的进一步认识
为了故事可以顺利的发展，先来一张表， DDL 如下：
现在要执行这个语句SELECT * FROM yes.t1 where f3 = 11;
你觉得用的是哪个索引？
很多同学可能会觉得虽然有 idx_f2_f3 这个联合索引，但是查询条件是 f3，没有 f2，不符合最左匹配原则，所以很显然只能进行全表扫描。
我们来 explain 一下，看看：
可以看到结果，key 是 idx_f2_f3 ，所以这个查询用上了 idx_f2_f3 这个索引！这里可能有人就理解不了，怎么不符合最左索引原则也能用上联合索引了呢？
从 explain 结果来看，确实用上的联合索引，但是它的 type 是 index，所以它不是你认为的那种用上索引，它等于全扫描的联合索引来获取结果。现在不太理解没事，听下面好好解释。
到底什么叫用了索引？
我们都知道 InnoDB 是基于聚簇索引来构建数据的，在没有创建其它二级索引的前提下，你查询数据就必须且只能用聚簇索引，因为数据就只在这个索引上，那你找数据不用这个索引那用什么呢？
如果你建起了其他二级索引，那么查找数据就可能不用聚簇索引，而是只用上二级索引就能直接得到想要的数据(就是上面查询的例子)。
综上所述，你在 InnoDB 查找任何数据，都只能用索引。
那为什么平日里有人说这个不走索引，走了全表扫描呢？
我们都知道默认 InnoDB 的索引结构是 B+ 树，这个树有个特性，它的叶子节点之间是通过链表相连的。
所以只要我们找到主键索引最左边的叶子结点，然后往右顺序遍历扫描，就能得到这张表的所有记录，这样的扫描方式就叫全表扫描。
小贴士：我不清楚 MySQL 是记录了最左叶子节点的位置还是根据根节点查找到最左叶子节点的，反正意思就是那个意思，不影响我们理解全表扫描。
那什么叫用上索引？
看红色的查询路线没，能利用树的快速查找能力来定位到数据所在的页，这样的查询才能算用上索引。
那从主键索引反推到二级索引，其实是一样的。能利用二级索引树的特性来查找数据，就是用上了索引。
但是如果用不上二级索引树的快速查找，是否有像全表扫描这样，直接扫描二级索引的所有叶子节点的操作呢？
有的，就是我们上面的例子，我再把执行结果的截图搬下来：
这次查询 MySQL 就是全扫描二级索引 idx_f2_f3 来直接返回所要的数据。
原因就是二级索引已经包含了查询所要到全部数据:f1、f2、f3，所以直接全扫描二级索引即可。
可以看到 explain 的 type=index，即全扫描二级索引。
这也叫用上了索引。
此时我们换用 f2 来作为查询条件，这样就符合最左匹配原则了，我们来看看 explain 有什么区别：
可以看到还是用了 idx_f2_f3 ，只是 type 变成 ref ，这个 ref 就表明可以与常量进行等值匹配时来快速查询得到结果。
这也叫用上了索引。
好了，我们来总结一下到底什么叫用了索引：
- 利用主键索引快速查找
- 利用二级索引快速查找
- 全扫描二级索引进行查找
以上这三种就是我们平日里所说的用上了索引，然后全扫描主键索引就是我们所说的全表扫描（等价于没用上索引，但是我们知道它其实是用上了主键索引对吧？）。
至此，我想你应该已经清楚，到底什么叫用了索引了。
小疑惑
关于 MySQL 索引的一些认识都讲完了，不过看了上面的例子，有些同学可能会有点疑惑：
既然都要全扫描，为什么要扫描二级索引，而不是主键索引？
因为主键索引存储了所有的数据，包括数据行相关的事务ID、回滚指针等等。
而二级索引不包含这些，它只有主键值和索引列的值。
所以相对而言直接读取二级索引开销会更小一些，所以在索引能覆盖返回值的时候，一般都会选择二级索引来查找。
比如我把上面的表再加个字段：f4。
现在我们再来执行SELECT * FROM yes.t1 where f3 = 11;
可以看到，这个时候就没用上二级索引了，而是直接全表扫描了。原因就是二级索引无法覆盖返回值，因为多了个 f4 的字段。
如果用了 idx_f2_f3 这个索引，想要得到 f4 的值，还需要再得到的主键再去主键索引里面进行查找，然后才能得到 f4 的值，这样回表的效率就低了。
当然其实结果还不一定，有时候也有可能 MySQL 的统计信息有误，用错了索引。因为 MySQL 是基于成本来选择执行计划的，有 I/O 成本和 CPU 成本，哪个索引成本低就用哪个索引。