MySQL 索引核心知识体系总结

1. 索引概述

  • 定义：索引（Index）是帮助 MySQL 高效获取数据的数据结构（有序）。
  • 本质：空间换时间。
  • 优缺点：
    • 优点：
      1. 大大减少服务器扫描的数据量（降低 I/O 成本）。
      2. 帮助服务器避免排序和临时表（降低 CPU 成本）。
    • 缺点：
      1. 占用磁盘空间。
      2. 降低写操作（INSERT, UPDATE, DELETE）的速度，因为需要维护索引结构。

2. 索引结构 (InnoDB)

MySQL 默认存储引擎 InnoDB 使用 B+ Tree 结构。

  • B+ Tree 特点：
    1. 非叶子节点仅存储键值（Key）和指针，不存储数据，增加了节点的度（Fan-out），树的高度通常控制在 3-4 层，减少 I/O 次数。
    2. 叶子节点存储所有数据（Key + Row Data 或 Key + PK），且叶子节点之间通过双向链表连接。
    3. 扫库方便：支持范围查询，全表扫描只需遍历叶子节点链表。
  • 为什么不用其他结构？
    • Hash：仅支持精确匹配（=），不支持范围查询（>, <），不支持排序。
    • 二叉树/红黑树：大数据量下树太高，磁盘 I/O 次数过多；可能会退化成链表（二叉树）。
    • B-Tree：非叶子节点也存数据，导致单页存储的 Key 变少，树的高度变高，I/O 增加。

3. 索引分类

3.1 按物理存储分类 (InnoDB 核心)

分类 聚集索引 (Clustered Index) 二级索引 (Secondary Index)
存储内容 叶子节点存储完整行数据 叶子节点存储索引列值 + 主键值
数量 有且仅有一个 可以有多个
选取规则 1. 主键 (Primary Key) 2. 第一个唯一非空索引 (Unique Not Null) 3. 自动生成隐藏列 ROW_ID 除了聚集索引外的所有索引
回表查询 不需要 需要（先查二级索引拿到主键，再回聚集索引查数据），除非覆盖索引

3.2 按逻辑/功能分类

  • 主键索引 (Primary)：唯一且非空。
  • 唯一索引 (Unique)：索引列值必须唯一，允许 NULL。
  • 普通索引 (Normal)：无特殊限制。
  • 全文索引 (Fulltext)：用于大文本搜索（通常用 ES 替代）。

3.3 按字段个数分类

  • 单列索引：一个索引包含一个列。
  • 联合索引：一个索引包含多个列（涉及最左前缀原则）。

4. 索引相关语法

1
2
3
4
5
6
7
8
-- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (col_name [ASC|DESC], ...);

-- 查看索引
SHOW INDEX FROM table_name;

-- 删除索引
DROP INDEX index_name ON table_name;

5. SQL 性能分析工具

5.1 SQL 执行频率

1
SHOW GLOBAL STATUS LIKE 'Com_______';
  • 查看 Com_select, Com_insert, Com_update, Com_delete 的数值，判断系统是读多写少还是写多读少，决定优化方向。

5.2 慢查询日志 (Slow Query Log)

  • 作用：记录执行时间超过 long_query_time（默认 10s）的 SQL。
  • 配置：/etc/my.cnf 中设置 slow_query_log=1 和 long_query_time=2 (示例)。
  • 分析：定位出拖慢系统的具体 SQL 语句。

5.3 Profile 详情

  • 作用：查看 SQL 执行在每一个阶段（CPU、IO、Context Switch）的耗时。
  • 命令：
    • SET profiling = 1; (开启)
    • SHOW PROFILES; (查看最近 SQL 列表)
    • SHOW PROFILE FOR QUERY query_id; (查看具体资源消耗)

5.4 EXPLAIN 执行计划 (核心)

使用 EXPLAIN SELECT ... 分析查询。关注核心字段：

  1. type (访问类型)：性能由好到差排序：
    • NULL (不查表)
    • system (系统表 1 行)
    • const (主键/唯一索引等值查询)
    • eq_ref (主键/唯一索引关联)
    • ref (非唯一索引等值查询)
    • range (索引范围查询)
    • index (全索引扫描)
    • all (全表扫描)
    • 目标：至少达到 range，最好是 ref。
  2. possible_keys：可能用到的索引。
  3. key：实际用到的索引。
  4. key_len：索引使用的字节数（越短越好，用于检查联合索引是否完全生效）。
  5. Extra：
    • Using index：好。使用了覆盖索引，无需回表。
    • Using where：需要过滤。
    • Using temporary：差。使用了临时表（常见于 group by）。
    • Using filesort：差。需要文件排序（常见于 order by 未命中索引）。

6. 索引的使用与失效场景

6.1 最左前缀法则 (联合索引)

  • 查询从索引的最左列开始，不能跳过索引中的列。
  • 如果跳过某一列，后面的列索引失效。
  • 范围查询右侧失效：如果遇到范围查询 (>, <)，则该列之后的列索引失效。（>=、<= 通常不会失效）。

6.2 常见索引失效情况

  1. 函数运算：WHERE substring(name, 1, 2) = 'ab'（对索引列做运算）。
  2. 字符串不加引号：WHERE phone = 123（发生隐式类型转换）。
  3. 模糊查询：LIKE '%abc'（头部模糊匹配失效，'abc%' 走索引）。
  4. OR 连接：OR 两侧只要有一侧没有索引，涉及的索引全部失效。
  5. 数据分布影响：如果 MySQL 评估全表扫描比走索引快（例如表中绝大多数数据都符合条件），则放弃索引。

6.3 覆盖索引 (Covering Index)

  • 定义：查询的列 (SELECT 后的列) 刚好都在索引中，不需要回表查询。
  • 建议：尽量避免 SELECT *，指定必要列，争取命中覆盖索引（Extra: Using index）。

6.4 前缀索引

  • 针对 VARCHAR, TEXT 等长字符串，只对前 N 个字符建立索引，以节省空间。
  • 语法：CREATE INDEX idx_name ON table(column(n));

7. 索引的设计原则

  1. 针对性：对查询频次高、数据量大（>100万行）的表建立索引。

  2. 字段选择：

    • WHERE 子句中的过滤条件字段。
    • ORDER BY、GROUP BY 涉及的字段。

  3. 区分度：选择区分度高（唯一性高）的列（如 UUID、手机号）。区分度低的列（如性别、状态）不适合建单列索引。

  4. 联合索引优先：尽量使用联合索引，利用覆盖索引机制，减少回表。

  5. 字符串优化：长字符串使用前缀索引。

  6. 控制数量：索引不是越多越好，过多的索引会拖慢写性能并增加优化器负担。

  7. 非空约束：尽量将字段设为 NOT NULL，有利于优化器确定索引效率。

    8. SQL 提示 (SQL Hints)

    8.1 概述

    • 定义：开发者在 SQL 语句中显式地添加指令，人为干预数据库优化器（Optimizer）对索引的选择。
    • 场景：当 MySQL 优化器生成的执行计划不是最优解（例如选错了索引，或者放弃索引走全表扫描）时使用。

    8.2 常用语法

    SQL 提示需紧跟在表名之后。

    1. USE INDEX (建议)

    • 含义：建议 MySQL 使用指定的索引之一。

    • 特点：这只是一个参考，优化器评估后如果觉得全表扫描更快，依然可能忽略该提示。

    • 语法：

      1
      SELECT * FROM table_name USE INDEX (idx_name) WHERE col = 'value';

    2. IGNORE INDEX (忽略)

    • 含义：禁止 MySQL 使用指定的索引。

    • 场景：

      • 用于验证某个索引是否对查询产生负面影响。

  • 当你确定全表扫描比走该索引更快时（例如数据分布极不均匀）。

  • 语法：

    1
    SELECT * FROM table_name IGNORE INDEX (idx_name) WHERE col = 'value';

3. FORCE INDEX (强制)

  • 含义：强制 MySQL 使用指定的索引。

  • 特点：比 USE INDEX 语气更重。如果强制的索引无法使用，MySQL 通常会选择全表扫描作为代价，极少会忽略该指令。

  • 语法：

    1
    SELECT * FROM table_name FORCE INDEX (idx_name) WHERE col = 'value';