索引

表数据一多(几万、几百万行),查询就会慢。索引(Index) 是 SQL 性能的命脉——它给某一列(或几列)建一个"目录",让数据库找数据时不用一页页翻全表,而是直接定位。索引用得好,查询速度能从"几秒"变到"几毫秒"。这一篇是 SQL 进阶最重要的一篇。

1. 什么是索引?

索引本质上是一种独立于表数据的数据结构,它把某一列的值按特定顺序存起来,附带指向原表行的"指针"。最常见的索引结构是 B+ 树(MySQL InnoDB 默认),它的查找效率是 O(log N)。

打个比方:一本书没有目录时,你想找"SQL 索引"那一节,得从第一页翻到最后一页,慢得要命。有了目录,直接看目录定位到第几页,翻过去就行。索引就是表的"目录"。代价是占额外存储空间,且每次 INSERT/UPDATE/DELETE 都要同步更新索引(变慢)。

2. CREATE INDEX 创建索引

加索引很简单,一条 DDL 语句:

-- 创建索引:加速 WHERE / JOIN / ORDER BY
CREATE INDEX idx_age ON students(age);
CREATE INDEX idx_class_id ON students(class_id);

-- 唯一索引:同时保证值不重复(类似 UNIQUE 约束)
CREATE UNIQUE INDEX idx_email ON students(email);

-- 删除索引(MySQL 语法,其他库略有不同)
DROP INDEX idx_age ON students;
-- PostgreSQL: DROP INDEX idx_age;

-- 查看表的所有索引(MySQL)
SHOW INDEX FROM students;

-- 主键自动创建索引,UNIQUE 约束也自动创建唯一索引
-- 所以通常不需要为这些列再额外建索引

几个要点:主键自动建索引(叫聚集索引/聚簇索引),UNIQUE 约束自动建唯一索引,所以这些列不用重复建。索引名建议用 idx_列名 这种规范,方便团队识别。

3. 复合索引与最左前缀原则

多个列一起建一个索引,叫复合索引。它有个非常重要的规则——最左前缀原则。理解错了,索引就白建了:

-- 复合索引(多列一起索引)
CREATE INDEX idx_class_age ON students(class_id, age);

-- ⭐ 最左前缀原则(leftmost prefix):
-- 复合索引 (class_id, age) 可用于:
--   WHERE class_id = 1                    ✅ 用上索引
--   WHERE class_id = 1 AND age > 18       ✅ 用上索引(完全匹配)
--   WHERE class_id = 1                    ✅ 用上索引(只用第一列)
--   WHERE age > 18                        ❌ 用不上(跳过了 class_id)
--   WHERE class_id = 1 OR age > 18        ❌ OR 部分用不上

-- 设计原则:
-- 1. 把过滤性最强的列放最前面(如 user_id 在 created_at 前)
-- 2. 把等值查询的列放前面,范围查询的列放后面
-- 3. 复合索引 (a, b, c) 隐含了 (a)、(a, b) 两个索引
--    所以不要单独再为 a 或 a+b 建索引

最左前缀的核心是:复合索引 (a, b, c) 只能从左到右连续使用。WHERE 跳过 a 直接用 b,索引失效。设计时把过滤性最强(区分度高)的列放最前面,把范围查询(> BETWEEN)的列放最后。

4. 覆盖索引(性能极致)

如果一个查询所有要的列都在索引里,数据库直接从索引返回结果,不用回主表查——这种叫覆盖索引,速度极快:

-- 覆盖索引(covering index):索引里包含查询要的所有列
-- 数据库从索引直接返回结果,【不需要回表】(查主表)

-- 假设有索引 idx_name_age (name, age)
-- 下面这个查询是"覆盖"的:SELECT 的列都在索引里
SELECT name, age FROM students WHERE name = '小明';
-- 数据库从 idx_name_age 直接返回,不读主表 → 极快

-- 反例:需要回表(索引里没有 email)
SELECT name, age, email FROM students WHERE name = '小明';
-- 先从索引找到行,再去主表读 email,慢一些

-- MySQL 支持 INCLUDE 语法(MySQL 8.0+)或直接加列
-- PostgreSQL 支持 INCLUDE 关键字
-- CREATE INDEX idx_name ON students(name) INCLUDE (age, email);

实战中,把高频查询的"WHERE 列 + SELECT 列"一起做成覆盖索引,查询性能可能提升 5-10 倍。但要注意索引变长(列多),写入和存储成本也上升。MySQL EXPLAIN 输出里看到 Extra: Using index 就是覆盖索引生效了。

5. EXPLAIN 看执行计划

不知道查询用了什么索引?用 EXPLAIN。它是 SQL 优化的核心工具,慢查询分析必用:

-- EXPLAIN:查看 SQL 的执行计划(用了什么索引、扫了多少行)
-- 这是 SQL 优化的【核心工具】

EXPLAIN SELECT * FROM students WHERE age = 20;

-- 输出关键字段(MySQL):
-- +----+-------------+----------+------+---------------+----------+
-- | id | select_type | table    | type | key            | rows      |
-- +----+-------------+----------+------+---------------+----------+
-- |  1 | SIMPLE      | students | ref  | idx_age        | 5         |
-- +----+-------------+----------+------+---------------+----------+

-- 解读:
-- type    访问类型,const > eq_ref > ref > range > index > ALL
--         ALL 是全表扫描(最慢),理想是 ref 或更高
-- key     实际用的索引名(NULL 表示没用索引)
-- rows    估算要扫描的行数(越小越好)
-- Extra   额外信息,"Using index" 是好兆头(覆盖索引)
--         "Using filesort" / "Using temporary" 是坏兆头(慢)

-- 慢查询优化流程:
-- 1. EXPLAIN 看用了什么索引、扫了多少行
-- 2. 如果 type=ALL 或 rows 很大 → 加合适的索引
-- 3. 再 EXPLAIN 验证是否生效

重点看 type(访问类型,ALL 是全表扫描,最慢)和 rows(估算扫描行数,越小越好)。Extra 字段里 Using index 是好兆头(覆盖索引),Using filesort 或 Using temporary 是坏兆头(需要额外排序或临时表)。

6. 什么时候该加索引?

索引不是越多越好。下面是经验法则:

-- 适合加索引的列
-- 1. 主键(自动建)
-- 2. WHERE 频繁过滤的列(如 user_id、created_at)
-- 3. JOIN 关联的列(外键和被引用列都要)
-- 4. ORDER BY / GROUP BY 频繁的列
-- 5. 唯一性约束的列(用唯一索引)

-- 不适合加索引的场景
-- 1. 数据量小(几百行)的表:全表扫描就够快
-- 2. 写多读少的表:每次 INSERT/UPDATE 都要更新索引,反而慢
-- 3. 区分度低的列(如 gender 只有"男/女"):索引意义不大
-- 4. 频繁更新的列:索引维护成本高

-- 索引不是银弹!权衡原则:
-- 读密集(OLAP/报表):多加索引
-- 写密集(OLTP/日志):少加索引

核心权衡:读密集(OLAP、报表)多加索引,写密集(OLTP、日志)少加索引。一般业务表的主键、外键、WHERE 高频列、JOIN 关联列都要加。

7. 索引失效的常见陷阱

建了索引不代表一定会用上。下面这些写法会让索引失效,变成全表扫描:

-- ⚠️ 索引失效的常见场景

-- 1. 函数包字段:索引失效
SELECT * FROM students WHERE YEAR(created_at) = 2024;     -- ❌ 失效
SELECT * FROM students
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';  -- ✅ 走索引

-- 2. 隐式类型转换:索引失效
SELECT * FROM students WHERE phone = 13800138000;         -- ❌ 失效(phone 是字符串)
SELECT * FROM students WHERE phone = '13800138000';       -- ✅ 走索引

-- 3. LIKE 以 % 开头:索引失效
SELECT * FROM students WHERE name LIKE '%明';             -- ❌ 失效
SELECT * FROM students WHERE name LIKE '小明%';           -- ✅ 可能走索引

-- 4. OR 两边不全有索引:索引失效
SELECT * FROM students
WHERE age = 20 OR email = 'xm@example.com';              -- 如果 email 没索引,整条失效

-- 5. != / NOT IN / IS NOT NULL:索引可能失效(看数据库)
SELECT * FROM students WHERE age <> 20;                   -- 通常不走索引

-- 6. 计算字段:索引失效
SELECT * FROM students WHERE age + 1 = 21;                -- ❌ 失效
SELECT * FROM students WHERE age = 20;                    -- ✅ 走索引

记住这条铁律:WHERE 里字段不要被函数包、不要做计算、不要发生隐式类型转换。改写 SQL 让索引能被识别,往往比重设索引更立竿见影。

索引类型速查

小结

索引是 SQL 性能优化的第一利器。学会 CREATE INDEX、理解复合索引最左前缀、会看 EXPLAIN、避免索引失效陷阱——你就算半个 DBA 了。下一篇我们看 SQL 另一个核心概念:事务,它保证数据一致性。

← 上一篇 SQL 函数

下一篇 事务 →

✈️💬