MySQL 索引学习与使用攻略

索引是数据库性能优化的核心手段之一。它就像一本书的目录，可以快速定位到所需的数据，极大地提升查询效率。然而，不当的索引设计和使用也可能成为性能瓶颈。本攻略将带你从基础到精通，全面掌握 MySQL 索引。

第一阶段：基础概念与原理

1. 什么是索引？

   • 索引是一种特殊的数据库结构，它包含着表中一列或多列数据的值，并指向这些值所在的实际行的物理地址。
   • 目的是为了提高数据库表的检索速度，加快 WHERE 条件、ORDER BY、GROUP BY 等操作的速度。

2. 索引的代价:

   • 空间代价: 索引本身需要占用额外的磁盘空间。
   • 时间代价: 维护索引需要成本。每当对表进行 INSERT、UPDATE、DELETE 操作时，都需要同时维护索引树，这会使增删改的速度变慢。

3. 常见的索引数据结构:

   • B+ Tree (InnoDB 存储引擎): 这是 MySQL InnoDB 存储引擎使用的主流索引结构。

     • 特点:
       • 所有的数据都存储在叶子节点上，非叶子节点只存储索引信息。
       • 叶子节点之间用指针连接，形成一个双向链表，非常适合范围查询和排序 (ORDER BY)。
       • 查询效率稳定，任何一条记录的查询路径长度基本相同。

   • Hash (Memory 存储引擎):

     • 特点: 查找速度非常快，时间复杂度为 O(1)。但不支持范围查询和排序。

第二阶段：索引类型详解

1. 主键索引 (PRIMARY KEY):

   • 一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。
   • 通常在创建表时通过 PRIMARY KEY 约束来创建。

2. 唯一索引 (UNIQUE):

   • 索引列的值必须唯一，但允许有空值（NULL）。
   • 可以有多个唯一索引。

3. 普通索引 (INDEX):

   • 最基本的索引类型，没有任何限制。

4. 复合索引 (Composite Index):

   • 在表的多个列上创建的索引。例如，CREATE INDEX idx_name_age ON user(name, age);。
   • 查询时遵循“最左前缀”原则，即查询条件必须包含索引最左边的列，才能有效利用该索引。

5. 全文索引 (FULLTEXT):

   • 用于对文本数据进行全文搜索，仅支持 MyISAM 和 InnoDB (MySQL 5.6+) 存储引擎。
   • 主要用于 MATCH() AGAINST 语法。

第三阶段：索引的创建与管理

1. 创建索引:

   -- 创建普通索引
   CREATE INDEX index_name ON table_name (column_name);
   
   -- 创建复合索引
   CREATE INDEX idx_name_age_addr ON table_name (name, age, address);
   
   -- 创建唯一索引
   CREATE UNIQUE INDEX idx_email ON table_name (email);
   

2. 删除索引:

   DROP INDEX index_name ON table_name;
   

3. 查看索引:

   SHOW INDEX FROM table_name;
   

第四阶段：核心法则与最佳实践

1. 最左前缀法则:

   • 原理: 对于复合索引 (a, b, c)，查询条件必须从最左边的列开始，并且不能跳过中间的列，才能充分利用索引。
   • 能使用索引的情况:
     • WHERE a = ?
     • WHERE a = ? AND b = ?
     • WHERE a = ? AND b = ? AND c = ?
   • 不能使用索引或部分使用索引的情况:
     • WHERE b = ? (跳过了 a)
     • WHERE a = ? AND c = ? (跳过了 b)
     • WHERE b = ? AND c = ? (跳过了 a)

2. 不适合建立索引的场景:

   • 表记录太少: 数据量小时，全表扫描可能比走索引更快。
   • 经常更新的字段: 频繁的 DML 操作会导致索引频繁重建，影响性能。
   • 区分度低的字段: 如性别、状态字段，建立索引的意义不大。
   • 在 WHERE 子句中使用 != 或 <> 操作符: 这种情况下，MySQL 引擎将放弃索引而进行全表扫描。
   • 在 WHERE 子句中对字段进行 NULL 值判断: WHERE column IS NULL 或 WHERE column IS NOT NULL 通常无法利用索引。

3. 会导致索引失效的情况:

   • 在索引列上进行计算或使用函数: WHERE YEAR(date_column) = 2023 会让索引失效。应改为 WHERE date_column >= '2023-01-01' AND date_column < '2024-01-01'。
   • 使用 LIKE '%value%': 以 % 开头的模糊查询无法使用索引。LIKE 'value%' 可以。
   • 在复合索引中使用 OR: 如果 OR 连接的条件不是复合索引的第一部分，可能导致索引失效。例如，对于索引 (a, b)，WHERE a = 1 OR b = 2 可能不会使用索引。
   • 类型转换: WHERE name = 123 (假设 name 是 VARCHAR 类型) 会发生隐式类型转换，导致索引失效。

4. 覆盖索引 (Covering Index):

   • 定义: 一个索引包含了所有查询需要的字段。
   • 优势: 查询可以直接从索引中获取所有数据，而无需回表（访问主键索引或聚簇索引）去查找行数据，极大地提升了查询效率。
   • 示例: 表 user(id, name, age, address)，有一个复合索引 idx_name_age(name, age)。查询 SELECT name, age FROM user WHERE name = 'John' 就是覆盖索引查询，因为所有需要的数据（name, age）都在索引 idx_name_age 中。

第五阶段：实战优化与分析工具

1. EXPLAIN 执行计划:

   • 这是分析 SQL 是否使用索引、如何使用索引的最重要工具。

   EXPLAIN SELECT * FROM user WHERE name = 'John' AND age > 25;
   

   • 关键字段解读:
     • id: 查询的序列号。
     • select_type: 查询类型（SIMPLE, PRIMARY, SUBQUERY 等）。
     • table: 查询的表名。
     • type: 访问类型，性能从好到差依次为：system > const > eq_ref > ref > range > index > ALL。ALL 表示全表扫描，需要优化。
     • possible_keys: 可能选用的索引。
     • key: 实际使用的索引。
     • key_len: 使用到的索引长度，越短越好。
     • rows: MySQL 估算的找到所需行数需要读取的行数，数值越小越好。
     • Extra: 包含了重要的额外信息，如 Using where, Using index (覆盖索引), Using filesort (需要文件排序，性能较差), Using temporary (使用临时表，性能较差)。

2. 优化步骤:

   • 定位慢查询: 使用 slow_query_log 开启慢查询日志，找出执行时间长的 SQL。
   • 分析执行计划: 使用 EXPLAIN 分析这些 SQL 的执行计划。
   • 创建或调整索引: 根据分析结果，为查询条件创建合适的索引。
   • 重写 SQL: 有时改变 SQL 的写法也能提升性能，例如避免 SELECT *，只查询需要的列。

🧐 SQL 语句层面的规避策略

这是最常见也最需要开发者注意的层面，许多索引失效都源于 SQL 语句的写法。

1. 避免对索引列进行运算或使用函数

   • 原因: 索引存储的是列的原始值，对列进行运算或使用函数后，数据库无法直接匹配索引。
   • 错误示例: SELECT * FROM users WHERE YEAR(create_time) = 2023;
   • 正确做法: 将函数或运算应用在等号右边。

     SELECT * FROM users WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';
     

2. 避免在 LIKE 模糊查询中以通配符 % 开头

   • 原因: B+树索引是按照顺序存储的，无法高效地进行后缀或中间匹配。
   • 错误示例: SELECT * FROM users WHERE name LIKE '%son';
   • 正确做法: 尽量使用右模糊或全模糊结合其他方案。
     • 右模糊: SELECT * FROM users WHERE name LIKE 'son%';
     • 全模糊: 对于必须的全模糊搜索，可以考虑使用 MySQL 8.0+ 的倒序存储+右模糊，或使用全文索引（FULLTEXT Index）。

3. 避免隐式类型转换

   • 原因: 当查询条件的类型与索引列的类型不一致时，MySQL 会进行隐式转换，这相当于在索引列上使用了函数，导致索引失效。
   • 错误示例: user_id 是 INT 类型，但查询时用了字符串 WHERE user_id = '123' (通常仍可走索引，但不推荐)；或者 phone 是 VARCHAR 类型，但查询时用了数字 WHERE phone = 13812345678。
   • 正确做法: 确保查询值的类型与字段定义严格一致。

4. 遵循联合索引的最左前缀原则

   • 原因: 联合索引 (a, b, c) 的 B+ 树是先按 a 排序，再按 b 排序，最后按 c 排序。如果查询条件不包含最左边的列 a，则无法有效定位数据。
   • 错误示例: SELECT * FROM users WHERE b = 2 AND c = 3; (跳过了 a)
   • 正确做法: 查询条件应从联合索引的最左侧列开始。

5. 注意范围查询对联合索引的影响

   • 原因: 在联合索引中，如果一个列使用了范围查询（如 >, <, BETWEEN, LIKE 'abc%'），那么该列之后的索引列将无法被使用。
   • 错误示例: 联合索引为 (a, b, c)，查询语句为 WHERE a = 1 AND b > 2 AND c = 3;，此时 c 列无法使用索引。
   • 正确做法: 在设计联合索引时，将范围查询的列放在联合索引的最后面。

6. 谨慎使用 OR 条件

   • 原因: 如果 OR 连接的条件中，有一部分列没有索引，或者索引不是最优的，优化器可能会放弃使用索引，进行全表扫描。
   • 错误示例: SELECT * FROM users WHERE indexed_column = 1 OR non_indexed_column = 2;
   • 正确做法: 可以考虑使用 UNION ALL 将查询拆分为多个使用索引的查询。

🛠️ 索引设计层面的优化策略

良好的索引设计是性能的基石。

1. 利用覆盖索引

   • 策略: 尽量让查询的字段都包含在索引中，这样数据库可以直接从索引中获取数据，而无需回表查询，极大地提升了效率。
   • 示例: 如果有一个联合索引 (name, age)，那么查询 SELECT name, age FROM users WHERE name = 'John' 就是覆盖索引查询。

2. 合理创建联合索引

   • 策略: 根据查询需求，将区分度高、筛选性强的列放在联合索引的前面，将需要进行范围查询的列放在后面。

3. 考虑使用函数索引 (MySQL 8.0+)

   • 策略: 如果业务上必须对某个字段进行函数运算后查询，可以考虑直接创建函数索引。
   • 示例: CREATE INDEX idx_name_upper ON users ((UPPER(name)));

⚙️ 数据库与配置层面的保障

1. 保证统计信息准确

   • 策略: MySQL 优化器依赖统计信息来选择执行计划。当表数据量发生巨大变化后，可以手动执行 ANALYZE TABLE table_name; 来更新统计信息，帮助优化器做出更优的选择。

2. 统一字符集和排序规则

   • 策略: 在进行多表 JOIN 查询时，确保关联字段的字符集和排序规则完全一致，否则可能会引发隐式转换，导致索引失效。

🔍 如何诊断索引是否失效？

使用 EXPLAIN 命令是诊断索引使用情况的最核心工具。

执行 EXPLAIN SELECT ... 后，重点关注以下几列：

• type: 访问类型。性能从好到差为 const > eq_ref > ref > range > index > ALL。如果出现 ALL，说明是全表扫描，需要重点优化。
• key: 实际使用的索引。如果为 NULL，则表示未使用索引。
• rows: 预计扫描的行数。数值越小越好。
• Extra: 额外信息。如果出现 Using filesort 或 Using temporary，通常意味着性能不佳，需要优化排序或分组。