索引夺命 10 连问 - 文章 - 开发者社区

大家好，我是苏三，又跟大家见面了。

前言

今天我们来聊聊让无数开发者又爱又恨的——数据库索引 。

相信不少小伙伴在工作中都遇到过这样的场景：

明明已经加了索引，为什么查询还是慢？
为什么有时候索引反而导致性能下降？
联合索引到底该怎么设计才合理？

别急，今天我就通过10个问题，带你彻底搞懂索引的奥秘！

希望对你会有所帮助。

一、什么是索引？为什么需要索引？

1.1 索引的本质

简单来说，索引就是数据的目录 。

就像一本书的目录能帮你快速找到内容一样，数据库索引能帮你快速定位数据。

  
-- 没有索引的查询（全表扫描）  
SELECT * FROM users WHERE name = '苏三'; -- 需要遍历所有记录  
  
-- 有索引的查询（索引扫描）  
CREATE INDEX idx\_name ON users(name);  
SELECT * FROM users WHERE name = '苏三'; -- 通过索引快速定位

1.2 索引的工作原理

picture.image

索引的底层结构（B+树） ： picture.image

二、索引的10个常见问题

1.为什么我加了索引，查询还是慢？

场景还原 ：

  
CREATE INDEX idx\_name ON users(name);  
SELECT * FROM users WHERE name LIKE '%苏三%'; -- 还是很慢！

原因分析 ：

前导通配符 ： LIKE '%苏三% 导致索引失效
索引选择性差 ：如果name字段大量重复，索引效果不佳
回表代价高 ：索引覆盖不全，需要回表查询

解决方案 ：

  
-- 方案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 ONusers(name);  
SELECT * FROM users WHERE MATCH(name) AGAINST('苏三');

2.索引是不是越多越好？

绝对不是！ 索引需要维护代价：

  
-- 每个索引都会影响写性能  
INSERT INTO users (name, email, age) VALUES ('苏三', 'susan@example.com', 30);  
-- 需要更新:   
-- 1. 主键索引  
-- 2. idx\_name索引（如果存在）  
-- 3. idx\_email索引（如果存在）  
-- 4. idx\_age索引（如果存在）

索引的代价 ：

存储空间 ：每个索引都需要额外的磁盘空间
写操作变慢 ：INSERT/UPDATE/DELETE需要维护所有索引
优化器负担 ：索引太多会增加查询优化器的选择难度

黄金法则 ：一般建议表的索引数量不超过5-7个

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 AND name = '苏三'; -- √ 优化器会调整顺序  
  
-- 不能使用索引的查询  
SELECT * FROM users WHERE age = 30; -- × 不符合最左前缀

联合索引结构 ：

picture.image

4.如何选择索引字段的顺序？

选择原则 ：

高选择性字段在前 ：选择性高的字段能更快过滤数据
经常查询的字段在前 ：优先满足常用查询场景
等值查询在前，范围查询在后

  
-- 计算字段选择性  
SELECT   
    COUNT(DISTINCT name) / COUNT(*) as name\_selectivity,  
    COUNT(DISTINCT age) / COUNT(*) as age\_selectivity,  
    COUNT(DISTINCT city) / COUNT(*) as city\_selectivity  
FROM users;  
  
-- 根据选择性决定索引顺序  
CREATE INDEX idx\_name\_city\_age ON users(name, city, age); -- name选择性最高

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值对索引有什么影响？

NULL值的问题 ：

  
-- 创建索引  
CREATE INDEX idx\_email ON users(email);  
  
-- 查询NULL值  
SELECT * FROM users WHERE email IS NULL; -- 可能不使用索引  
SELECT * FROM users WHERE email IS NOT NULL; -- 可能不使用索引

解决方案 ：

避免NULL值 ：设置默认值
使用函数索引 （MySQL 8.0+）

  
-- 使用函数索引处理NULL值  
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; -- √ 使用索引优化分组  
  
-- 无法使用索引排序的情况  
SELECT * FROM users ORDER BY name, age; -- × 不符合最左前缀  
SELECT * FROM users ORDER BY age DESC, name ASC; -- × 排序方向不一致

8.如何发现索引失效的场景？

常见索引失效场景 ：

函数操作 ： WHERE YEAR(create\_time) = 2023
类型转换 ： WHERE phone = 13800138000 （phone是varchar）
数学运算 ： WHERE age + 1 > 30
前导通配符 ： WHERE name LIKE '%苏三'

使用EXPLAIN分析 ：

  
EXPLAIN SELECT * FROM users WHERE name = '苏三';  
  
-- 查看关键指标：  
-- type: const|ref|range|index|ALL（性能从好到坏）  
-- key: 实际使用的索引  
-- rows: 预估扫描行数  
-- Extra: Using index（覆盖索引）| Using filesort（需要排序）| Using temporary（需要临时表）

9.如何维护和优化索引？

定期索引维护 ：

  
-- 查看索引使用情况（MySQL）  
SELECT * FROM sys.schema\_index\_statistics   
WHERE table\_schema = 'your\_database'AND table\_name = 'users';  
  
-- 重建索引（优化索引碎片）  
ALTER TABLE users REBUILD INDEX idx\_name;  
  
-- 分析索引使用情况  
ANALYZE TABLE users;

索引监控 ：

  
-- 开启索引监控（Oracle）  
ALTER INDEX idx\_name MONITORING USAGE;  
  
-- 查看索引使用情况  
SELECT * FROM v$object\_usage WHERE index\_name = 'IDX\_NAME';

10.不同数据库的索引有什么差异？

MySQL vs PostgreSQL索引差异 ：

| 特性 | MySQL | PostgreSQL | | --- | --- | --- | | 索引类型 | B+Tree, Hash, Fulltext | B+Tree, Hash, GiST, SP-GiST | | 覆盖索引 | 支持 | 支持（使用INCLUDE） | | 函数索引 | 8.0+支持 | 支持 | | 部分索引 | 支持 | 支持 | | 索引组织表 | 聚簇索引 | 堆表 |

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;  
  
-- 创建表达式索引（Expression Index）  
CREATE INDEX idx\_name\_lower ON users (LOWER(name));