MySQL 索引详解

河野知青
0 阅读9分钟

MySQL 索引详解

一、概述

索引（Index） 是数据库中用于快速查找数据的一种数据结构，类似于书籍的目录。通过索引，MySQL 可以避免全表扫描，大幅提升查询性能。

  • 优点：加速数据检索，减少磁盘 I/O；保证数据唯一性（唯一索引）；提高排序和分组效率。
  • 缺点：占用额外存储空间；降低 DML（INSERT/UPDATE/DELETE）操作的速度，因为索引需要同步维护。

二、索引的分类

分类方式类型说明
数据结构B+树索引最常用，适用于全值匹配、范围查询、排序
哈希索引Memory 引擎默认，精确匹配快，不支持范围查询
全文索引用于大文本字段的关键词检索
空间索引用于地理数据（GIS）
逻辑功能普通索引（INDEX）无唯一性约束
唯一索引（UNIQUE）要求列值唯一，允许 NULL
主键索引（PRIMARY KEY）特殊的唯一索引，不允许 NULL，每表一个
全文索引（FULLTEXT）用于文本搜索
列数单列索引基于单个列
组合索引（复合索引）基于多个列，遵循最左前缀原则
聚簇性聚簇索引（Clustered Index）数据行与索引存储在一起，InnoDB 主键即聚簇索引
二级索引（Secondary Index）存储索引列 + 主键值，需回表查询

三、索引数据结构：B+树

3.1 为什么选择 B+树？

  • 多路平衡树：降低树的高度（通常 3~4 层），减少磁盘 I/O。
  • 叶子节点存储数据：叶子节点之间通过双向链表连接，支持范围扫描。
  • 非叶子节点仅存储键值：每个节点可容纳更多键，进一步降低树高。

3.2 B+树结构示意图

         [30|60]           ← 非叶子节点（仅存键值）
        /    |    \
   [10|20] [40|50] [70|80] ← 叶子节点（存键值+数据指针/主键）
      │      │      │
      └──────┴──────┘      ← 双向链表

3.3 查询过程

  • 等值查询：从根节点二分查找，向下逐层定位到叶子节点。
  • 范围查询：定位到范围起始点，沿叶子节点链表顺序扫描。

四、InnoDB 的索引模型

4.1 聚簇索引（Clustered Index）

  • 定义：表数据按照索引顺序物理存储。InnoDB 中，主键就是聚簇索引。

  • 规则：

    • 如果定义了主键，则主键作为聚簇索引。
    • 如果没有主键，则第一个 NOT NULL UNIQUE 键作为聚簇索引。
    • 否则，InnoDB 自动生成一个 6 字节的隐藏 ROW_ID 作为聚簇索引。

  • 叶子节点存储：完整的行数据（所有列）。

  • 优势：通过主键查询最快，无需回表。

4.2 二级索引（Secondary Index）

  • 定义：非聚簇索引，叶子节点存储索引列值 + 主键值。

  • 查询过程（回表） ：

    1. 在二级索引树上找到对应的主键。
    2. 再到聚簇索引树上根据主键获取完整行数据。

  • 优化：如果索引列覆盖了查询所需的所有列（覆盖索引），则无需回表。

4.3 索引结构示例

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    age INT,
    INDEX idx_name (name)
);
  • 聚簇索引（id） ：叶子节点直接存储整行数据。
  • 二级索引（name） ：叶子节点存储 (name, id)。
  • 查询 SELECT * FROM user WHERE name = 'Alice';：先查 idx_name 得到 id=1，再回表查聚簇索引获取完整行。

五、索引的使用场景与优化原则

5.1 适合建立索引的列

  • 出现在 WHERE、ORDER BY、GROUP BY、JOIN 条件中的列。
  • 区分度高（选择性好）的列：COUNT(DISTINCT col)/COUNT(*) 越大越好。
  • 数据量较大的表（几万行以上）。

5.2 不适合建立索引的列

  • 区分度极低（如性别、布尔值）。
  • 频繁更新的列（索引维护开销大）。
  • 长文本列（可考虑前缀索引或全文索引）。

5.3 组合索引的最左前缀原则

  • 定义：查询条件必须从索引的最左列开始，才能使用该索引。

  • 示例：索引 (a, b, c)

    • ✅ WHERE a = 1 AND b = 2
    • ✅ WHERE a = 1 AND c = 3（仅使用 a 列，c 无法使用）
    • ❌ WHERE b = 2 AND c = 3（无法使用索引）

  • 优化建议：将区分度高的列放在左侧，频繁查询的列优先。

5.4 索引失效的常见情况

情况示例原因
使用函数或计算WHERE YEAR(date) = 2020对列进行了计算，无法使用索引
隐式类型转换WHERE phone = 13800000000（phone 是 VARCHAR）MySQL 会将索引列转换为数字，导致失效
左模糊或全模糊查询WHERE name LIKE '%张三'无法使用 B+树前缀匹配
OR 条件未全索引WHERE a = 1 OR b = 2（只有 a 有索引）需要全表扫描
索引列参与运算WHERE id + 1 = 10对索引列运算
使用 != 或 <>WHERE status != 1范围太大，优化器认为全表更快
组合索引未遵循最左前缀WHERE b = 2（索引 (a,b)）未使用第一列

六、高级索引策略

6.1 覆盖索引（Covering Index）

  • 定义：索引的叶子节点已经包含了查询所需的所有列，无需回表。

  • 示例：

    CREATE INDEX idx_name_age ON user (name, age);
SELECT name, age FROM user WHERE name = 'Alice';  -- 覆盖索引，无需回表
SELECT * FROM user WHERE name = 'Alice';          -- 需要回表

  • 好处：减少随机 I/O，提升性能。

6.2 索引下推（Index Condition Pushdown, ICP）

  • 适用版本：MySQL 5.6+。

  • 原理：在存储引擎层直接利用索引过滤掉不符合条件的记录，减少回表次数。

  • 示例：索引 (name, age)，查询 WHERE name LIKE '张%' AND age = 20。

    • 无 ICP：先回表拿到所有 name 以“张”开头的行，再过滤 age。
    • 有 ICP：在索引树上判断 age = 20，仅回表满足条件的行。

6.3 索引合并（Index Merge）

  • 定义：查询使用多个单列索引，MySQL 将结果合并（取交集、并集）。
  • 场景：WHERE a = 1 OR b = 2（a 和 b 各有索引）。
  • 注意：组合索引通常比索引合并更高效。

6.4 前缀索引

  • 定义：仅索引列的前 N 个字符，减少索引空间。
  • 适用：长字符串列（如 VARCHAR(255)）。
  • 示例：CREATE INDEX idx_url_prefix ON url (url(20));
  • 缺点：无法用于 ORDER BY 或 GROUP BY，且区分度可能下降。

七、索引的创建与管理

7.1 创建索引

-- 普通索引
CREATE INDEX idx_name ON table_name (column_name);
​
-- 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON user (email);
​
-- 组合索引
CREATE INDEX idx_name_age ON user (name, age);
​
-- 全文索引
CREATE FULLTEXT INDEX idx_fulltext_content ON article (content);
​
-- 主键索引（通常在建表时指定）
ALTER TABLE user ADD PRIMARY KEY (id);
​
-- 删除索引
DROP INDEX idx_name ON table_name;

7.2 查看索引

-- 查看表的所有索引
SHOW INDEX FROM table_name;
​
-- 从 information_schema 查询
SELECT * FROM information_schema.STATISTICS WHERE table_name = 'user';

7.3 索引的代价评估

维度代价
存储空间每个索引占用额外磁盘空间
插入性能每插入一行，需要更新所有索引
更新性能UPDATE 索引列时需同步维护索引
删除性能删除行时需从所有索引中删除对应项

建议：单表索引数量控制在 5~6 个以内，避免过度索引。

八、索引优化实战

8.1 使用 EXPLAIN 分析查询

EXPLAIN SELECT * FROM user WHERE name = 'Alice';

关键字段解读：

列名含义好索引的标志
type访问类型const > eq_ref > ref > range > index > ALL（越靠前越好）
possible_keys可能使用的索引不为 NULL
key实际使用的索引不为 NULL
rows估计扫描行数越小越好
Extra额外信息Using index（覆盖索引），Using index condition（ICP）

8.2 慢查询日志定位问题索引

-- 开启慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;  -- 超过1秒记录
​
-- 分析慢查询
mysqldumpslow /var/log/mysql/slow.log

8.3 强制使用或忽略索引

-- 强制使用指定索引
SELECT * FROM user FORCE INDEX (idx_name) WHERE name = 'Alice';
​
-- 忽略索引
SELECT * FROM user IGNORE INDEX (idx_name) WHERE name = 'Alice';

通常不建议在生产中强制指定，让优化器自行选择。

九、特殊引擎的索引特性

存储引擎索引特性
InnoDB支持事务、行锁；主键为聚簇索引；二级索引叶子存储主键值
MyISAM表锁；索引和数据分离，所有索引均为非聚簇索引，叶子存储行指针
Memory默认哈希索引，也支持 B+树索引；数据重启后丢失

十、常见问题与解决方案

Q1：为什么对长字符串列建索引效率低？

  • B+树节点存储的键值较大，导致每个节点能存放的键数量减少，树高增加，I/O 次数增加。
  • 解决：使用前缀索引或哈希索引（如对 URL 做 CRC32 存储）。

Q2：为什么 WHERE a = 1 AND b = 2 不一定会使用组合索引 (a,b)？

  • 如果表很小，全表扫描可能更快；优化器根据统计信息选择。
  • 如果 a 的区分度很低（如只有 0 和 1），优化器可能放弃索引。

Q3：为什么 ORDER BY 有时不使用索引？

  • 排序列未与 WHERE 条件构成最左前缀，或排序方向不一致（部分 ASC 部分 DESC）。
  • 使用了函数或表达式：ORDER BY UPPER(name)。
  • 查询返回大量数据，优化器认为排序成本高于全表扫描。

Q4：索引会影响 INSERT 速度多少？

  • 对于每个索引，插入时需要维护 B+树，增加约 23 次磁盘 I/O。若有 5 个索引，则额外增加 1015 次 I/O。
  • 优化：批量插入（INSERT ... VALUES 多行）；先删除索引，插入后再重建。

十一、总结与最佳实践

最佳实践说明
根据查询创建索引分析 WHERE、JOIN、ORDER BY、GROUP BY 列
优先使用组合索引代替多个单列索引，遵循最左前缀
选择区分度高的列索引选择性越好，查询效率越高
避免过度索引每增加一个索引都会降低 DML 性能
定期维护索引使用 OPTIMIZE TABLE 回收碎片（InnoDB 可重建表）
监控未使用的索引sys.schema_unused_indexes（MySQL 5.7+）
使用覆盖索引优化回表减少随机 I/O
注意隐式转换和函数保持索引列原始类型

检查清单：

  • 是否对每个主要查询都分析了 EXPLAIN？
  • 组合索引的顺序是否合理？
  • 是否有冗余索引（如 (a) 和 (a,b)）？
  • 频繁更新的表是否索引过多？
  • 是否使用了前缀索引优化长文本？
avatar
文章
阅读
粉丝