Chapter 06

索引与查询性能优化

B-Tree 原理、创建索引、最左前缀原则、EXPLAIN 执行计划分析

6.1 什么是索引

数据库索引就像书的目录：没有目录，你只能从第1页翻到最后才能找到某个主题；有了目录，你直接跳到对应页码。没有索引，数据库需要逐行扫描整张表（全表扫描）；有了索引，数据库可以直接定位到目标行。

✅

索引的好处 大幅加快 SELECT 查询（WHERE、JOIN、ORDER BY 条件列）；对大表效果尤为显著（百万行表从秒级降到毫秒级）。

⚠️

索引的代价 每次 INSERT/UPDATE/DELETE 时都需要维护索引，写入性能略降；索引本身占用磁盘空间（可能是原表的 30%~100%）。

6.2 B-Tree 索引原理

MySQL InnoDB 和 PostgreSQL 默认使用 B+ 树（B-Plus Tree）结构存储索引。

📊

B+ 树查找示例 查找 id = 42 时：从根节点出发，比较键值决定走左/右子树，每层做一次磁盘 I/O，通常只需 3-4 次就能定位到目标行。对比全表扫描可能需要 10,000 次 I/O。

6.3 创建索引的语法

SQL-- 普通索引（允许重复值）
CREATE INDEX idx_users_email ON users(email);

-- 唯一索引（不允许重复值，也是 UNIQUE 约束的底层实现）
CREATE UNIQUE INDEX uq_users_email ON users(email);

-- 复合索引（多列索引）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 前缀索引（对长字符串只索引前 N 个字符，节省空间）
CREATE INDEX idx_posts_title_prefix ON posts(title(20));

-- 在 CREATE TABLE 中直接定义索引
CREATE TABLE orders (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  status  VARCHAR(20) NOT NULL,
  INDEX idx_user_id (user_id),
  INDEX idx_status (status)
);

-- 删除索引
DROP INDEX idx_users_email ON users;    -- MySQL
DROP INDEX idx_users_email;               -- PostgreSQL（索引独立于表）

-- 查看表上的索引
SHOW INDEX FROM users;                   -- MySQL
\d users                                  -- psql 元命令

6.4 复合索引的最左前缀原则

对于复合索引 (a, b, c)，索引会按 a → b → c 的顺序排列。查询时只有从最左边开始连续使用索引列，才能用到该索引。

查询条件能用索引 (a,b,c)？原因
WHERE a = 1✅ 能用（用到 a）从最左列开始
WHERE a = 1 AND b = 2✅ 能用（用到 a,b）连续使用前两列
WHERE a = 1 AND b = 2 AND c = 3✅ 完全使用全部三列
WHERE b = 2❌ 不能用跳过了 a
WHERE a = 1 AND c = 3⚠️ 部分（只用 a）b 中断，c 无法用
WHERE a > 1 AND b = 2⚠️ 只用 aa 是范围查询，b 无法用
 
SQL-- 假设有复合索引 INDEX idx (user_id, status, created_at)

-- ✅ 充分利用索引（三列都用到）
SELECT * FROM orders
WHERE user_id = 1
  AND status = 'paid'
  AND created_at > '2024-01-01';

-- ✅ 利用索引（前两列）
SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid';

-- ❌ 无法利用索引（跳过 user_id）
SELECT * FROM orders
WHERE status = 'paid';  -- 全表扫描！

6.5 EXPLAIN — 分析执行计划

EXPLAIN 是性能调优的核心工具，显示数据库如何执行你的查询。

MySQL EXPLAIN

SQLEXPLAIN SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid';
列名含义
type访问类型（最重要！见下方说明）
key实际使用的索引名，NULL 表示未用索引
key_len使用索引的字节数（越小表示只用了部分复合索引）
rows估算需要检查的行数（越小越好）
filtered经过 WHERE 过滤后剩余的行比例
Extra额外信息（"Using index"=覆盖索引好；"Using filesort"/"Using temporary" 需优化）

type 字段（效率从低到高）

type 值含义优化建议
ALL全表扫描，性能最差必须添加索引
index全索引扫描（比 ALL 好，但仍扫描全部）考虑更优化的索引
range索引范围扫描（BETWEEN、>、<、IN）可接受
ref非唯一索引等值匹配（可能返回多行）良好
eq_ref唯一索引等值匹配（最多1行，JOIN 时常见）很好
const / system主键或唯一索引等值匹配（最多1行，最快）最优

PostgreSQL EXPLAIN ANALYZE

SQL-- EXPLAIN ANALYZE 实际执行查询并显示真实耗时
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 1 AND status = 'paid';

-- 输出示例：
-- Bitmap Heap Scan on orders  (cost=4.57..16.32 rows=3 width=64)
--                              (actual time=0.045..0.048 rows=2 loops=1)
--   Recheck Cond: ((user_id = 1) AND (status = 'paid'))
--   ->  Bitmap Index Scan on idx_orders_user_status
--         (cost=0.00..4.57 rows=3 width=0)
--         (actual time=0.038..0.038 rows=2 loops=1)
--         Index Cond: ((user_id = 1) AND (status = 'paid'))
-- Planning Time: 0.12 ms
-- Execution Time: 0.08 ms

6.6 哪些情况索引失效

SQL-- ❌ 在索引列上使用函数（索引失效）
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ✅ 改写为范围查询（可用索引）
SELECT * FROM users
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- ❌ 隐式类型转换（phone 是 VARCHAR，但传入整数）
SELECT * FROM users WHERE phone = 13812345678;
-- ✅ 传入正确类型
SELECT * FROM users WHERE phone = '13812345678';

-- ❌ LIKE 前缀通配符（以 % 开头无法用索引）
SELECT * FROM users WHERE username LIKE '%bob%';
-- ✅ 后缀通配符可以用索引
SELECT * FROM users WHERE username LIKE 'bob%';

-- ❌ OR 两边列不同（至少一侧没有索引则整体失效）
SELECT * FROM users
WHERE username = 'alice' OR email = 'bob@example.com';
-- ✅ 改写为 UNION ALL（两个查询分别走各自的索引）
SELECT * FROM users WHERE username = 'alice'
UNION ALL
SELECT * FROM users WHERE email = 'bob@example.com';

6.7 慢查询日志

SQL-- MySQL 开启慢查询日志（在 my.cnf 或动态设置）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 超过 1 秒的查询记录到日志
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 查看慢查询统计
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- PostgreSQL 慢查询：在 postgresql.conf 中配置
-- log_min_duration_statement = 1000  # 毫秒，超过 1 秒记录
-- log_destination = 'csvlog'

分析工具

6.8 常见优化技巧

覆盖索引（Covering Index）

SQL-- 如果查询的所有列都在索引中，则无需回表查询原始数据
-- 这称为"覆盖索引"，EXPLAIN 中 Extra 显示 "Using index"

-- 假设有索引 INDEX idx (user_id, status, total_price)
SELECT user_id, status, total_price  -- 这三列都在索引中
FROM orders
WHERE user_id = 1;
-- 不需要回表，直接从索引读取数据，性能极佳

其他优化建议

📌

索引设计经验法则 先写出业务查询语句，分析 WHERE、JOIN ON、ORDER BY 涉及的列；通过 EXPLAIN 验证是否用到索引；添加索引后再次 EXPLAIN 对比 type 和 rows 的变化。不要盲目添加索引，要基于实际查询模式决定。

6.9 PostgreSQL 特有索引类型

PostgreSQL 除了默认的 B-Tree 索引，还提供多种专门用途的索引类型，每种针对不同数据特征进行优化：

SQL-- GIN 索引：用于 JSONB 字段
CREATE INDEX idx_products_attrs_gin ON products USING GIN (attributes);
-- attributes 是 JSONB 类型
-- 现在可以高效使用 @> (包含) 运算符
SELECT * FROM products
WHERE attributes @> '{"color": "red"}';

-- GIN 索引：用于数组类型
CREATE INDEX idx_posts_tags_gin ON posts USING GIN (tags);
-- tags 是 integer[] 数组类型
SELECT * FROM posts
WHERE tags && ARRAY[1, 2, 3];  -- 有重叠的文章

-- GIN 索引：全文搜索
CREATE INDEX idx_posts_fts ON posts USING GIN (
  to_tsvector('chinese', title || ' ' || content)
);
SELECT title
FROM posts
WHERE to_tsvector('chinese', title || ' ' || content)
      @@ to_tsquery('chinese', '数据库 & 索引');

-- BRIN 索引：适合时间序列表（按时间顺序插入）
CREATE INDEX idx_logs_created_at_brin ON logs USING BRIN (created_at)
  WITH (pages_per_range = 128);  -- 每 128 个数据页存一个摘要
-- BRIN 索引文件极小，对亿级时序数据几乎无存储开销

-- GiST 索引：地理坐标（需要 PostGIS 扩展）
-- CREATE INDEX idx_stores_location ON stores USING GIST (location);
-- 支持 <<->> 距离运算符，快速找最近门店

6.10 EXPLAIN ANALYZE 深度解读（PostgreSQL 17）

PostgreSQL 的 EXPLAIN ANALYZE 不仅显示执行计划，还实际运行查询并报告每个节点的真实耗时和行数，是性能调优的核心武器。

SQL-- 使用 BUFFERS 查看缓冲区命中率（PostgreSQL 推荐加上）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, u.username, o.total_price
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'paid'
  AND o.created_at > '2024-01-01'
ORDER BY o.total_price DESC
LIMIT 20;

-- 示例输出（含注解说明）：
--
-- Limit  (cost=42.31..42.36 rows=20 width=52)
--        (actual time=2.453..2.461 rows=20 loops=1)
--   Buffers: shared hit=35
--   ->  Sort  (cost=42.31..42.55 rows=95 width=52)
--             (actual time=2.451..2.453 rows=20 loops=1)
--         Sort Key: o.total_price DESC
--         Sort Method: top-N heapsort  Memory: 26kB
--         Buffers: shared hit=35
--         ->  Hash Join  (cost=8.05..39.24 rows=95 width=52)
--                        (actual time=0.521..2.420 rows=127 loops=1)
--               Hash Cond: (o.user_id = u.id)
--               Buffers: shared hit=35
--               ->  Bitmap Heap Scan on orders o
--                     (cost=4.57..35.02 rows=95 width=24)
--                     (actual time=0.085..2.102 rows=127 loops=1)
--                     Recheck Cond: (status='paid' AND created_at>'2024-01-01')
--                     Buffers: shared hit=28
--                     ->  Bitmap Index Scan on idx_orders_status_created
--                           (actual time=0.071..0.071 rows=127 loops=1)
--               ->  Hash  (cost=2.30..2.30 rows=130 width=32)
--                         (actual time=0.427..0.427 rows=130 loops=1)
--                         Buckets: 1024  Batches: 1  Memory Usage: 16kB
--                         Buffers: shared hit=7
--                         ->  Seq Scan on users u
-- Planning Time: 0.312 ms
-- Execution Time: 2.529 ms

关键指标解读

指标含义与解读
cost=X..Y估算代价（任意单位）。X=启动代价（返回第一行前），Y=总代价。数字越小越好，但这是估算值。
actual time=X..Y ms实测耗时（毫秒）。X=返回第一行耗时，Y=返回全部行耗时。重点关注 Y。
rows=N行数。cost 旁的是优化器估算行数，actual 旁的是真实行数。两者差距大说明统计信息过期，需 ANALYZE。
loops=N该节点执行次数（嵌套循环时 >1）。actual time 和 rows 是每次循环的数据，乘以 loops 才是总量。
shared hit/readhit=从缓存读取（快），read=从磁盘读取（慢）。hit 越多越好，read 多说明 shared_buffers 不够大。
Sort Methodexternal merge=磁盘排序（内存不足），quicksort/top-N heapsort=内存排序（好）。
⚠️

cost 估算与实际差距大时的原因 1. 统计信息过期：表数据大量变更后未 ANALYZE，优化器用旧的统计信息估算行数
2. 数据分布不均：某列值高度集中，平均统计信息不准确（可用扩展统计 CREATE STATISTICS 改善）
3. 参数嗅探：PreparedStatement 编译时的参数值与实际值差异很大
解决：运行 ANALYZE tablename 或 VACUUM ANALYZE tablename 更新统计信息。

📌

本章小结
索引是 SQL 性能优化的核心手段。B-Tree 是万用索引，适合等值和范围查询；PostgreSQL 的 GIN 用于 JSONB/数组/全文，BRIN 用于时序数据，GiST 用于空间数据。创建索引要遵循最左前缀原则，避免在索引列上做函数运算、隐式类型转换和前缀 LIKE。EXPLAIN ANALYZE + BUFFERS 是诊断慢查询的标准工具——重点看 actual time、rows 估算偏差、磁盘 hit/read 比例。每张表的索引数量建议不超过 5-6 个，索引越多写入越慢。