索引10连问，你能抗住第几问？

本文将系统性地讲解数据库索引的核心原理、常见陷阱及优化方案，通过代码示例和图表辅助理解，协助开发者彻底掌握索引设计精髓。

一、索引基础概念

1.1 索引的本质与价值

索引是数据的目录，类似于书籍的目录，用于加速查询。没有索引时，数据库必须进行全表扫描，效率极低；而合理的索引可以快速定位数据。

代码对比示例：

-- 无索引的全表扫描（效率低）
SELECT * FROM users WHERE name = '苏三';

-- 创建索引后的高效查询
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '苏三';  -- 通过索引快速定位

1.2 索引底层原理：B+树结构

索引的底层一般采用B+树实现，其多路平衡树特性支持高效的范围查询和排序，减少磁盘IO。

索引10连问，你能抗住第几问？

二、索引10大关键问题详解

问题1：索引失效场景分析

场景还原：
即使创建索引，前导通配符查询仍可能缓慢：

CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name LIKE '%苏三%';  -- 索引失效！

缘由：

前导通配符（%苏三）导致索引无法匹配前缀。
索引选择性差（字段重复值多）。
回表代价高（索引未覆盖查询字段）。

解决方案：

-- 方案1：避免前导通配符
SELECT * FROM users WHERE name LIKE '苏三%';

-- 方案2：使用覆盖索引
CREATE INDEX idx_name_covering ON users(name, id, email);
SELECT name, id, email FROM users WHERE name LIKE '苏三%';  -- 无需回表

-- 方案3：全文索引（适用于文本搜索）
CREATE FULLTEXT INDEX ft_name ON users(name);
SELECT * FROM users WHERE MATCH(name) AGAINST('苏三');

问题2：索引数量的权衡

误区：索引越多越好。
实际代价：

写操作性能下降（每次INSERT/UPDATE/DELETE需维护所有索引）。
存储开销增加。
优化器选择负担加重。

黄金法则：单表索引数量提议不超过5-7个。
示例：

INSERT INTO users(name, email, age) VALUES ('苏三', 'susan@example.com', 30);
-- 需更新：主键索引 + idx_name + idx_email + idx_age（如有）

问题3：联合索引的最左前缀原则

规则：联合索引仅从最左列开始生效。
代码示例：

CREATE INDEX idx_name_age ON users(name, age);

-- 有效查询（使用索引）
SELECT * FROM users WHERE name = '苏三';
SELECT * FROM users WHERE name = '苏三' AND age = 30;

-- 无效查询（索引失效）
SELECT * FROM users WHERE age = 30;  -- 不符合最左前缀

联合索引的存储结构：按字段左到右排序，优化范围查询。

索引10连问，你能抗住第几问？

问题4：索引字段顺序策略

原则：

高选择性字段在前（快速过滤数据）。
等值查询字段优先于范围查询字段。

选择性计算：

SELECT 
  COUNT(DISTINCT name) / COUNT(*) AS name_selectivity,
  COUNT(DISTINCT age) / COUNT(*) AS age_selectivity
FROM users;

设计示例：若name选择性最高，创建索引idx_name_city_age。

问题5：覆盖索引的优势

定义：索引包含查询所需所有字段，避免回表。
对比代码：

-- 非覆盖索引（需回表）
CREATE INDEX idx_name ON users(name);
SELECT * FROM users WHERE name = '苏三';  -- 回表查询其他字段

-- 覆盖索引（无需回表）
CREATE INDEX idx_name_covering ON users(name, email, age);
SELECT name, email, age FROM users WHERE name = '苏三';  -- 索引覆盖所有字段

优势：减少磁盘IO、降低内存占用、提升查询速度。

问题6：NULL值对索引的影响

问题：查询IS NULL或IS NOT NULL时索引可能失效。
解决方案：

设置默认值替代NULL。
使用函数索引（MySQL 8.0+）：

CREATE INDEX idx_email_null ON users( (COALESCE(email, '')) );
SELECT * FROM users WHERE COALESCE(email, '') = '';

问题7：索引优化排序与分组

有效场景：

CREATE INDEX idx_age_name ON users(age, name);

-- 索引优化排序
SELECT * FROM users ORDER BY age, name;  -- 避免额外排序

-- 索引优化分组
SELECT age, COUNT(*) FROM users GROUP BY age;  -- 利用索引加速

失效场景：

排序字段不符合最左前缀（如ORDER BY name, age）。
排序方向不一致（如ORDER BY age DESC, name ASC）。

问题8：索引失效诊断方法

常见失效场景：

函数操作：WHERE YEAR(create_time) = 2023
类型转换：WHERE phone = 13800138000（phone为varchar类型）
数学运算：WHERE age + 1 > 30

使用EXPLAIN分析：

EXPLAIN SELECT * FROM users WHERE name = '苏三';
-- 关键指标：
-- type: const|ref|range|index|ALL（性能从优到差）
-- key: 实际使用的索引
-- Extra: Using index（覆盖索引）/ Using filesort（需排序）

问题9：索引维护与优化

定期维护操作：

-- 重建索引消除碎片（MySQL）
ALTER TABLE users REBUILD INDEX idx_name;

-- 分析索引使用情况
ANALYZE TABLE users;

监控工具：

MySQL：查询sys.schema_index_statistics表。
Oracle：使用ALTER INDEX … MONITORING USAGE。

问题10：不同数据库索引差异

MySQL vs PostgreSQL对比：

特性	MySQL	PostgreSQL
索引类型	B+Tree, Hash, Fulltext	B+Tree, Hash, GiST, SP-GiST
覆盖索引	直接包含字段	使用INCLUDE语法
部分索引	支持	支持（WHERE条件）

PostgreSQL示例：

-- 包含索引（Covering Index）
CREATE INDEX idx_users_covering ON users(name) INCLUDE (email, age);

-- 部分索引（Partial Index）
CREATE INDEX idx_active_users ON users(name) WHERE is_active = true;