/>

第三部分：索引使用策略与

EXPLAIN

实战

理论懂了，接下来就是在实际

SQL

中如何用好索引，以及如何用EXPLAIN诊断问题。

8.

索引失效的常见场景（面试高频）

知道什么时候索引生效，更要清楚什么时候会失效。

以下情况，索引很可能不会被使用：

1. 不满足最左前缀原则：对于联合索引(a,

   c)，如果查询条件只包含b或c，而缺少a，则索引失效。

2. 使用!=或<>操作符：在某些情况下，会导致索引失效，变成全表扫描。

3. 索引列上使用了函数或计算：例如where

   YEAR(create_time)

   10。

   解决方案：应该把函数或计算放在等式右侧，如where

   create_time

   '2024-01-01'。

4. 类型隐式转换：例如，索引列user_id是varchar类型，但查询时用的是数字：where

   user_id

   会将字符串转为数字，相当于在索引列上用了CAST函数，导致索引失效。

5. like以通配符%开头：where

   name

   B+树索引是按索引值的最左部分进行排序的，所以无法确定以%开头的字符串在树中的位置。

   where

   name

   '张三%'则可以用到索引。

6. 使用OR连接：如果OR前后的条件中，有一个列没有索引，那么即便另一个列有索引，也可能导致索引失效。

   解决方案：改为两个查询用UNION连接，或者确保OR两边的列都有独立的索引。

7. NOT

   IN、NOT

   EXISTS：在某些情况下会导致全表扫描。

8. 数据分布：如果优化器认为走索引还不如直接全表扫描快（例如查询的数据量占全表很大比例），它可能会放弃索引。

9.

SQL

性能？

EXPLAIN是

SQL

优化的利器。

重点关注以下几个字段：

• type：连接类型，是衡量查询好坏的重要指标。

  性能从好到差依次是：

  • system：表只有一行记录（系统表），是const类型的特例。

  • const：通过主键或唯一索引一次就找到了，速度极快。

  • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录匹配。

    常见于主键或唯一索引作为关联条件。

  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。

  • range：只检索给定范围的行，使用一个索引来选择行。

    如between、<、>、in()等。

  • index：Full

    Index

    Scan，扫描了整个索引树。

    比全表扫描好一点，因为索引树通常比表数据小。

  • ALL：全表扫描，性能最差，需要尽量避免。

• possible_keys：指出

  MySQL

  可能用到的索引。

• key：实际使用的索引。

  如果为NULL，则表示未使用索引。

• key_len：使用的索引字节数。

  在不损失精度的情况下，值越小越好。

  它可以帮助判断联合索引中真正用到了哪几列。

• rows：根据统计信息预估的必须检查的行数。

  值越小越好。

• Extra：包含额外的、非常重要的信息。

  • Using

    index：覆盖索引。

    查询的所有列都在索引中，无需回表。

    性能很棒！

  • Using

    where：表示

    MySQL

    在存储引擎层返回结果后，又进行了条件过滤。

  • Using

    index

    condition：索引下推（ICP）。

    MySQL

    5.6

    后的优化，在遍历索引过程中，先对索引包含的字段进行条件判断，过滤掉不满足条件的记录，减少回表次数。

  • Using

    temporary：使用了临时表保存中间结果，常见于GROUP

    BY。

    性能差，需优化。

  • Using

    filesort：无法利用索引完成排序操作，需要进行额外的排序操作（可能在内存或磁盘）。

    性能差，需优化。

10.

什么是回表、覆盖索引、索引下推？

• 回表：如上所述，从二级索引查到主键后，再到聚簇索引查找完整数据的过程。

• 覆盖索引：查询的所有字段都包含在同一个二级索引中。

  这样，直接从二级索引中就能获取到所需数据，无需回表。

  这是非常有效的优化手段。

  例如：有联合索引(name,

  age)，执行select

  name,

  '张三'，此时该联合索引就是覆盖索引。

• 索引下推（ICP）：也是针对联合索引的优化。

  在没有

  ICP

  时，存储引擎根据索引定位到数据行后，会返回给

  Server

  ICP，可以在存储引擎层直接判断索引中包含的其他字段的条件，提前过滤掉不符合的数据，减少回表次数和数据传输。

  例如：联合索引(name,

  age)，查询where

  name

  时，会找到所有name以“张”开头的记录，然后全部回表。

  有

  ICP

  10这个条件，只对符合条件的记录回表。

1. 区分度高的列放前面：让查询能尽快地缩小范围。

   区分度

   col)

   count(*)，值越大，区分度越高。

2. 查询频率高的列放前面：优先满足最左前缀原则，让索引被更多查询用到。

3. 考虑空间大小：在区分度和频率相近的情况下，选择占用空间较小的字段，因为非叶子节点能存储更多的

   key，降低树高。

4. 避免不必要的排序：如果查询中经常需要对字段a排序，对b分组，那么(a,

   a)更优，因为索引本身是有序的，可以避免filesort。

12.

前缀索引

• 定义：对于很长的字符串列（如varchar(255)），可以只索引字符串的前几个字符，而不是整个字符串。

• 语法：alter

  table

  (name(10));

• 优点：大大节省索引空间，提高索引的查询效率。

• 缺点：无法使用覆盖索引，并且可能降低索引的区分度（增加扫描行数）。

• 选择长度：目标是找到“足够高区分度”的最短长度。

  可以通过计算不同长度的区分度，选择一个与完整列区分度最接近的长度。

13.

索引合并

• 定义：MySQL

  5.0

  及以后引入的优化。

  当WHERE条件中包含了多个条件，且这些列分别有单列索引时，MySQL

  可能会分别用这些索引进行扫描，然后将结果合并（取交集INTERSECT、并集UNION等）。

• 例子：select

  from

  10;，假设name和age都有索引。

• 局限性：虽然能用，但通常不如建立一个合适的联合索引高效。

  因为索引合并需要读取多个索引树，并进行合并操作，消耗

  CPU

  和内存。

  可以通过optimizer_switch关闭。

14.

索引与排序、分组

• ORDER

  BY：如果ORDER

  BY的字段顺序与索引的列顺序完全一致，且排序方向（ASC/DESC）也一致，那么

  MySQL

  可以直接利用索引的有序性，避免filesort。

• GROUP

  BY：GROUP

  BY本质上也是先排序后分组（或直接利用索引顺序分组）。

  因此，GROUP

  BY类似，利用索引可以避免创建临时表。

15.

分页查询优化（深分页问题）

• 问题：select

  from

  行，代价极高。

• 优化方案：

  1. 延迟关联

     子查询：先利用覆盖索引快速找到需要的行的主键，再通过主键关联回原表获取其他列。

     sql

     select
     from
     t2.id;

  2. 书签记录：记录上一页的最大id，下一页查询时带上where

     >

     10。

     这种方式只能用于排序字段唯一且连续的情况（如自增主键）。