索引常见问题笔记

一、索引的基本概念

• 什么是索引？
  • 索引是数据库中用于快速查找数据的一种数据结构，类似于书的目录。它通过以空间换时间的方式，帮助存储引擎快速获取数据。
  • 存储引擎：MySQL的存储引擎（如InnoDB、MyISAM）负责数据的存储、索引的创建和查询的执行。InnoDB是MySQL 5.5之后的默认存储引擎。

二、索引的分类

1. 按数据结构分类

   • B+树索引：最常用的索引类型，适用于范围查询和顺序扫描。
   • Hash索引：适用于等值查询，不支持范围查询。
   • Full-text索引：用于全文搜索。

2. 按物理存储分类

   • 聚簇索引：主键索引通常是聚簇索引，数据存储在索引的叶子节点中。
   • 二级索引（辅助索引）：叶子节点存储的是主键值，需要通过主键索引回表获取数据。

3. 按字段特性分类

   • 主键索引：唯一标识记录的索引，不允许有重复值和NULL值。
   • 唯一索引：字段值必须唯一，但允许有NULL值。
   • 普通索引：无特殊限制的索引。
   • 前缀索引：对字段的前几个字符建立索引，适用于大字符串字段。

4. 按字段个数分类

   • 单列索引：基于单个字段的索引。
   • 联合索引：基于多个字段的组合索引，遵循最左匹配原则。

三、索引的使用场景

• 什么时候需要创建索引？

  • 字段有唯一性限制（如商品编码）。
  • 经常用于WHERE查询条件的字段。
  • 经常用于GROUP BY和ORDER BY的字段，避免文件排序。

• 什么时候不需要创建索引？

  • 字段中存在大量重复数据（如性别字段）。
  • 表数据太少。
  • 经常更新的字段（如用户余额）。

四、索引的优化方法

1. 前缀索引优化

   • 使用字段的前几个字符建立索引，减少索引大小，提高查询速度。
   • 但不能用于ORDER BY或作为覆盖索引。

2. 覆盖索引优化

   • 如果查询的所有字段都在索引的叶子节点中，可以直接从索引中获取数据，避免回表操作。
   • 示例代码：

     CREATE INDEX idx_product_no_name_price ON product(product_no, name, price);
     SELECT product_no, name, price FROM product WHERE product_no = '0002';
     

     注释：通过联合索引idx_product_no_name_price，查询可以直接从索引中获取数据，避免回表。

3. 主键索引最好是自增的

   • 自增主键插入数据时是顺序的，避免页分裂，提高插入效率。
   • 非自增主键可能导致页分裂，影响性能。

4. 索引最好设置为NOT NULL

   • 避免优化器在索引选择时的复杂性。
   • 减少存储空间的浪费。

5. 防止索引失效

   • 避免在查询条件中对索引列进行计算、函数调用或类型转换。
   • 联合索引查询时，必须遵循最左匹配原则。

五、联合索引的特殊场景

1. 最左匹配原则

   • 联合索引(a, b, c)查询时，必须从最左边的字段开始匹配。
   • 示例：
     • WHERE a=1：可以使用联合索引。
     • WHERE b=2：无法使用联合索引。
     • WHERE a=1 AND b=2：可以使用联合索引。

2. 范围查询

   • 范围查询字段可以用到联合索引，但后面的字段无法用到。
   • 特殊情况：
     • WHERE a>=1 AND b=2：a和b都可以使用联合索引。
     • WHERE a BETWEEN 2 AND 8 AND b=2：a和b都可以使用联合索引。
     • WHERE name LIKE 'j%' AND age=22：name和age都可以使用联合索引。

3. 索引下推优化

   • MySQL 5.6引入的优化，可以在联合索引中直接过滤不满足条件的记录，减少回表次数。
   • 示例：

     SELECT * FROM table WHERE a > 1 AND b = 2;
     

     注释：如果执行计划中Extra显示Using index condition，则说明使用了索引下推优化。

4. 索引区分度

   • 索引字段的不同值个数与表总行数的比值。
   • 区分度高的字段更适合放在联合索引的前面。

5. 联合索引用于排序

   • 示例：

     CREATE INDEX idx_status_create_time ON order(status, create_time);
     SELECT * FROM order WHERE status = 1 ORDER BY create_time ASC;
     

     注释：通过联合索引idx_status_create_time，查询可以直接利用索引的有序性，避免文件排序。

六、索引失效的常见场景

• 使用左或左右模糊匹配（如LIKE '%xx%'）。
• 在查询条件中对索引列进行计算、函数调用或类型转换。
• 联合索引查询时未遵循最左匹配原则。
• WHERE子句中，OR前的条件列是索引列，而OR后的条件列不是索引列。

七、执行计划的分析

• type字段：描述扫描方式的效率。

  • ALL：全表扫描，效率最低。
  • range：索引范围扫描，效率较高。
  • ref：非唯一索引扫描。
  • eq_ref：唯一索引扫描。
  • const：主键或唯一索引与常量比较，效率最高。

• Extra字段：

  • Using filesort：需要额外的排序操作，效率低。
  • Using temporary：使用临时表保存中间结果，效率低。
  • Using index：使用覆盖索引，避免回表操作，效率高。

参考文档：索引问题