MySQL 索引

索引概述

索引是数据库优化最核心的手段之一，类似于书籍的目录，可以快速定位数据。

-- 无索引：全表扫描 O(n)
SELECT * FROM users WHERE name = 'Alice';

-- 有索引：快速定位 O(log n)
CREATE INDEX idx_name ON users(name);

索引分类

类型	说明
主键索引	PRIMARY KEY，唯一且非空
唯一索引	UNIQUE，唯一但可为空
普通索引	INDEX，无唯一性限制
全文索引	FULLTEXT，文本内容搜索
复合索引	多列组合的索引

-- 主键索引
ALTER TABLE users ADD PRIMARY KEY(id);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 复合索引（最左前缀原则）
CREATE INDEX idx_name_age ON users(name, age);

B+ 树原理

InnoDB 使用 B+ 树作为索引结构：

B+ 树特点：
- 所有数据都在叶子节点
- 叶子节点之间用链表连接
- 查询稳定（都到叶子）
- 适合范围查询

        [10 | 20 | 30]
       /      |       \
    [5|8]   [15|18]   [35|40]
      |         |          |
   (数据)    (数据)      (数据)

EXPLAIN 分析

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

-- 关键字段：
-- type: ALL(全表) < index < range < ref < eq_ref < const
-- key: 实际使用的索引
-- rows: 预计扫描行数
-- Extra: Using index(覆盖索引), Using where(需要回表)

索引失效情况

-- ❌ 使用函数
SELECT * FROM users WHERE LEFT(name, 1) = 'A';

-- ❌ 类型转换
SELECT * FROM users WHERE age = '25';  -- age 是 int

-- ❌ 前缀通配符
SELECT * FROM users WHERE name LIKE '%ice';

-- ✅ 正确写法
SELECT * FROM users WHERE name LIKE 'Ali%';

-- ❌ OR 连接（可能不用索引）
SELECT * FROM users WHERE name = 'A' OR age = 25;

-- ✅ 用 UNION 代替 OR
SELECT * FROM users WHERE name = 'A'
UNION ALL
SELECT * FROM users WHERE age = 25;

复合索引最左前缀

-- 创建复合索引
CREATE INDEX idx_name_age_city ON users(name, age, city);

-- ✅ 生效（使用了 name）
SELECT * FROM users WHERE name = 'Alice';

-- ✅ 生效（使用了 name, age）
SELECT * FROM users WHERE name = 'Alice' AND age = 25;

-- ✅ 生效（使用了全部三列）
SELECT * FROM users WHERE name = 'Alice' AND age = 25 AND city = 'Beijing';

-- ❌ 不生效（跳过了 name）
SELECT * FROM users WHERE age = 25;

-- ⚠️ 部分生效（只用了 name, city，跳过了 age）
SELECT * FROM users WHERE name = 'Alice' AND city = 'Beijing';

覆盖索引

查询的列都在索引中，无需回表：

-- 覆盖索引：不需要回表
CREATE INDEX idx_name ON users(name);
SELECT name FROM users WHERE name = 'Alice';  -- Using index

-- 非覆盖索引：需要回表查询其他列
SELECT name, age FROM users WHERE name = 'Alice';  -- 需要回表

索引优化建议

1. 区分度高的列放前面

   -- 区分度：name > gender（name 唯一性更高）
   CREATE INDEX idx_name_gender ON users(name, gender);

2. 避免过多索引

   • 每个索引占用磁盘空间
   • 更新时维护索引有开销

3. 使用短索引

   -- 只索引前10个字符
   CREATE INDEX idx_name ON users(name(10));

4. 利用索引排序

   -- 可以利用索引排序
   SELECT * FROM users ORDER BY name;

[[返回 MySQL 首页|../index]]