{T}

索引

索引是数据库优化中最重要的一环，使用得当可以将查询效率提升 10 倍甚至更多。本章详细介绍索引的种类、使用场景和最佳实践。

索引概述

什么是索引

索引好比一本书的目录，可以快速定位特定内容。在数据库中，索引是帮助高效获取数据的数据结构。

索引的工作原理

sql
-- 没有索引：全表扫描
SELECT * FROM students WHERE name = '张三';  -- 需要扫描所有数据

-- 有索引：索引查找
-- 通过索引快速定位到数据位置

索引不是万能的

不适合创建索引的情况：

情况原因
数据量小（< 1000 行）全表扫描更快
数据重复度高（> 10%）索引选择性差
频繁更新的字段维护索引成本高
很少用于查询的字段索引利用率低

示例：性别字段通常不需要索引

sql
-- 性别只有两个值，重复度 50%
-- 使用索引反而更慢：先访问 50 万次索引，再访问 50 万次数据表
SELECT * FROM users WHERE gender = '男';  -- 100 万数据中 50 万条

特殊情况：数据分布不均匀时

sql
-- 如果男性只占 0.001%（100 万人中只有 10 个男性）
-- 这时创建索引就有意义了
SELECT * FROM users WHERE gender = '男';  -- 只返回 10 条数据

索引的分类

按功能逻辑分类

索引类型说明特点
普通索引基础索引类型无约束，仅提高查询效率
唯一索引唯一性约束允许 NULL，一张表可有多个
主键索引主键约束NOT NULL + UNIQUE，一张表只有一个
全文索引全文搜索MySQL 原生只支持英文
sql
-- 普通索引
CREATE INDEX idx_name ON students(name);

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

-- 主键索引（自动创建）
ALTER TABLE students ADD PRIMARY KEY (id);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

按物理实现分类

索引类型说明特点
聚集索引数据按索引顺序存储叶子节点存储数据行
非聚集索引独立存储索引叶子节点存储数据位置

聚集索引特点：

  • 一张表只能有一个聚集索引
  • 数据按主键顺序物理存储
  • 查询效率高，但插入/更新/删除效率较低

非聚集索引特点：

  • 一张表可以有多个非聚集索引
  • 索引和数据分开存储
  • 查询需要回表（先查索引，再查数据）
sql
-- 聚集索引查询（主键）
SELECT * FROM users WHERE id = 900001;  -- 0.043s

-- 非聚集索引查询（普通字段）
SELECT * FROM users WHERE name = 'student_890001';  -- 无索引: 0.961s

-- 创建非聚集索引后
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = 'student_890001';  -- 有索引: 0.050s

按字段个数分类

索引类型说明使用场景
单列索引单个字段创建索引单字段查询
联合索引多个字段组合索引多字段组合查询
sql
-- 单列索引
CREATE INDEX idx_name ON students(name);

-- 联合索引
CREATE INDEX idx_name_age ON students(name, age);

联合索引与最左匹配原则

最左匹配原则

联合索引按照定义顺序从左到右匹配，只有满足最左前缀才能使用索引。

sql
-- 创建联合索引
CREATE INDEX idx_a_b_c ON students(a, b, c);

-- 可以使用索引的查询
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- 不能使用索引的查询
WHERE b = 2           -- 跳过了 a
WHERE c = 3           -- 跳过了 a 和 b
WHERE b = 2 AND c = 3 -- 跳过了 a

联合索引示例

sql
-- 创建联合索引 (user_id, user_name)
CREATE INDEX idx_user ON users(user_id, user_name);

-- 可以使用索引
SELECT * FROM users WHERE user_id = 900001;                    -- 0.046s
SELECT * FROM users WHERE user_id = 900001 AND user_name = 'x'; -- 0.046s

-- 不能使用索引
SELECT * FROM users WHERE user_name = 'student_890001';        -- 0.943s

联合索引设计原则

  1. 区分度高的字段放前面：选择性更好的字段优先
  2. 常用查询字段放前面：根据实际查询场景设计
  3. 覆盖索引优化：查询字段都在索引中，避免回表
sql
-- 覆盖索引示例
CREATE INDEX idx_covering ON orders(user_id, status, create_time);

-- 查询字段都在索引中，无需回表
SELECT status, create_time FROM orders WHERE user_id = 1;

索引创建与管理

创建索引

sql
-- 创建表时创建索引
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_name (name),
    INDEX idx_age (age)
);

-- 在已有表上创建索引
CREATE INDEX idx_name ON students(name);
ALTER TABLE students ADD INDEX idx_name (name);

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

-- 创建联合索引
CREATE INDEX idx_name_age ON students(name, age);

查看索引

sql
-- 查看表索引
SHOW INDEX FROM students;

-- 查看索引使用情况
EXPLAIN SELECT * FROM students WHERE name = '张三';

删除索引

sql
DROP INDEX idx_name ON students;
ALTER TABLE students DROP INDEX idx_name;

索引使用场景

适合创建索引的场景

场景说明
WHERE 条件字段提高条件过滤效率
ORDER BY 字段避免文件排序
JOIN 关联字段提高连接效率
GROUP BY 字段提高分组效率
区分度高的字段索引选择性好

不适合创建索引的场景

场景说明
数据量小的表全表扫描更快
频繁更新的字段维护成本高
区分度低的字段如性别、状态
很少查询的字段利用率低

索引失效场景

1. 对索引字段进行运算

sql
-- 索引失效
SELECT * FROM students WHERE YEAR(create_time) = 2024;

-- 索引生效
SELECT * FROM students WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01';

2. 使用函数

sql
-- 索引失效
SELECT * FROM students WHERE SUBSTRING(name, 1, 3) = '张三';

-- 索引生效
SELECT * FROM students WHERE name LIKE '张三%';

3. 隐式类型转换

sql
-- 索引失效（name 是字符串类型，但使用了数字）
SELECT * FROM students WHERE name = 123;

-- 索引生效
SELECT * FROM students WHERE name = '123';

4. LIKE 以 % 开头

sql
-- 索引失效
SELECT * FROM students WHERE name LIKE '%张三';

-- 索引生效
SELECT * FROM students WHERE name LIKE '张三%';

5. OR 条件

sql
-- 如果 OR 两边的字段都有索引，索引生效
-- 如果 OR 一边的字段没有索引，索引失效
SELECT * FROM students WHERE name = '张三' OR age = 20;

6. NOT IN 和 <>

sql
-- 索引可能失效
SELECT * FROM students WHERE age NOT IN (18, 19, 20);
SELECT * FROM students WHERE age <> 18;

索引优化建议

1. 选择合适的索引类型

sql
-- 等值查询多：B+ 树索引
-- 范围查询多：B+ 树索引
-- 全文搜索：全文索引或搜索引擎

2. 控制索引数量

sql
-- 索引不是越多越好
-- 每个索引都需要存储空间和维护成本
-- 建议：单表索引数量不超过 5 个

3. 使用覆盖索引

sql
-- 创建覆盖索引
CREATE INDEX idx_covering ON orders(user_id, status, amount);

-- 查询字段都在索引中，避免回表
SELECT status, amount FROM orders WHERE user_id = 1;

4. 定期维护索引

sql
-- 分析索引使用情况
SELECT * FROM sys.schema_index_statistics;

-- 重建索引
ANALYZE TABLE students;

-- 优化表
OPTIMIZE TABLE students;

总结

索引类型对比

分类方式类型说明
功能逻辑普通/唯一/主键/全文按约束程度划分
物理实现聚集/非聚集按存储方式划分
字段个数单列/联合按字段数量划分

索引使用原则

  1. 选择性原则：区分度高的字段适合创建索引
  2. 最左原则：联合索引按顺序匹配
  3. 覆盖原则：查询字段尽量在索引中
  4. 适度原则：索引数量要适度

最佳实践

  1. 优先考虑 WHERE、ORDER BY、JOIN 字段
  2. 避免在索引字段上使用函数或运算
  3. 联合索引注意字段顺序
  4. 定期分析和优化索引