索引与查询性能优化

6.1 什么是索引

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

6.2 B-Tree 索引原理

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

• 树形结构：根节点 → 内部节点（只存键值和指针）→ 叶子节点（存键值 + 指向数据行的指针）
• 叶子节点有序：所有叶子节点按键值升序排列，并通过双向链表连接，支持高效范围查询
• 平衡树：任意叶子节点深度相同（通常3~4层），查找任意值的 I/O 次数固定
• 查找时间复杂度：O(log N)，N 是数据量

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	⚠️ 只用 a	a 是范围查询，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'

分析工具

• mysqldumpslow：MySQL 自带，按各维度汇总慢查询
• pt-query-digest：Percona Toolkit 工具，更强大的慢查询分析
• pgBadger：PostgreSQL 专用日志分析工具，生成 HTML 报告

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;
-- 不需要回表，直接从索引读取数据，性能极佳

其他优化建议

• 只查需要的列，避免 SELECT *（减少数据传输，可能命中覆盖索引）
• 合理使用 LIMIT，避免一次性取出大量数据
• 对频繁 WHERE/JOIN/ORDER BY 用到的列建立索引
• 复合索引中，区分度高（不重复值多）的列放在最左边
• 单张表索引数量不宜过多（5-6 个是通常上限），索引越多，写入越慢
• 定期用 ANALYZE TABLE（MySQL）或 VACUUM ANALYZE（PostgreSQL）更新统计信息