跳到主要内容

索引优化实战

问题​

哪些情况会导致索引失效？如何设计合理的索引？常见的索引优化手段有哪些？

答案​

索引失效的 12 种场景​

1. 对索引列使用函数​

-- ❌ 索引失效：对列使用函数
SELECT * FROM users WHERE YEAR(created_at) = 2024;
SELECT * FROM users WHERE LEFT(name, 3) = '张三丰';

-- ✅ 改写为范围查询
SELECT * FROM users 
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

2. 对索引列做运算​

-- ❌ 索引失效
SELECT * FROM orders WHERE id + 1 = 10;

-- ✅ 将运算移到右边
SELECT * FROM orders WHERE id = 9;

3. 隐式类型转换​

-- phone 是 VARCHAR 类型，传入数字
-- ❌ MySQL 会将 phone 列转为数字 → 等同于对列使用函数 → 索引失效
SELECT * FROM users WHERE phone = 13800138000;

-- ✅ 传入字符串
SELECT * FROM users WHERE phone = '13800138000';
类型转换规则

MySQL 的隐式转换规则：字符串 → 数字。所以：

  • VARCHAR 列对比数字 → 列被转换 → 索引失效 ❌
  • INT 列对比字符串 → 字符串被转换 → 索引有效 ✅

最安全的做法是始终保持类型一致。

4. 隐式字符集转换​

-- 两个表的字符集不同（utf8 和 utf8mb4）
-- 关联时 MySQL 会转换字符集 → 索引失效
SELECT * FROM t1 JOIN t2 ON t1.name = t2.name;

-- ✅ 统一字符集
ALTER TABLE t1 CONVERT TO CHARACTER SET utf8mb4;

5. LIKE 以通配符开头​

-- ❌ 索引失效：左模糊
SELECT * FROM users WHERE name LIKE '%三';
SELECT * FROM users WHERE name LIKE '%三%';

-- ✅ 索引有效：右模糊
SELECT * FROM users WHERE name LIKE '张%';

6. OR 条件未全部有索引​

-- ❌ 如果 age 没有索引，整个查询无法用索引
SELECT * FROM users WHERE name = '张三' OR age = 20;

-- ✅ 方案1：为 age 也建索引（Index Merge）
-- ✅ 方案2：改为 UNION
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 20 AND name != '张三';

7. NOT IN / NOT EXISTS​

-- ❌ 可能导致索引失效（取决于数据量和优化器判断）
SELECT * FROM users WHERE id NOT IN (1, 2, 3);
SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM blacklist WHERE ...);

-- ✅ 改用 LEFT JOIN + IS NULL
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;

8. IS NULL / IS NOT NULL​

-- 对于有大量 NULL 值的列，IS NOT NULL 可能不走索引
-- 对于大部分非 NULL 的列，IS NULL 可能不走索引
-- 具体取决于优化器的代价评估

9. 联合索引不满足最左前缀​

详见 联合索引 — 最左前缀原则。

10. 优化器选择全表扫描​

-- 当查询返回数据量超过表的 20%-30% 时
-- 优化器判断全表扫描比索引+回表更快
SELECT * FROM orders WHERE status = 'completed';
-- 如果 90% 的订单都是 completed → 全表扫描

11. 使用 != 或 <>​

-- ❌ 不等值查询通常不走索引（取决于数据分布）
SELECT * FROM users WHERE status != 'deleted';

-- ✅ 改为等值查询
SELECT * FROM users WHERE status IN ('active', 'pending');

12. 索引列参与条件表达式​

-- ❌ 索引失效
SELECT * FROM users WHERE age - 1 > 20;

-- ✅ 改为
SELECT * FROM users WHERE age > 21;

索引失效速查表​

场景示例原因
函数操作YEAR(date)破坏索引有序性
列运算id + 1 = 10等同于函数
隐式类型转换varchar = int列被转换
左模糊LIKE '%abc'无法确定前缀
OR 无索引列a=1 OR b=2（b 无索引）无法合并
不等于!=、<>范围太大
优化器放弃返回行数多全表扫描更快

前缀索引​

对长字符串列（如 email、URL），可以只索引前 N 个字符，减少索引大小。

-- 创建前缀索引
CREATE INDEX idx_email ON users (email(7));

-- 选择合适的前缀长度：选择性接近完整列
SELECT 
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS sel_5,
    COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS sel_7,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS sel_10,
    COUNT(DISTINCT email) / COUNT(*) AS sel_full
FROM users;
-- sel_5=0.75, sel_7=0.92, sel_10=0.98, sel_full=0.99
-- 选择 sel = 0.9+ 的最短前缀 → 7
前缀索引的限制
  1. 不能做覆盖索引：前缀索引只存储部分值，无法覆盖完整列
  2. 不能做 ORDER BY：排序需要完整值
  3. 不能做 GROUP BY：分组需要完整值

索引设计通用原则​

索引设计 Checklist​

项目检查点
是否需要查询频率高？数据量大？
列顺序ESR 原则？选择性？
覆盖能避免回表？
冗余有没有已有索引能复用？
宽度索引列不超过 5 个？
写入影响写入密集型表不要太多索引
主键自增或有序 ID？
NULL需要查 NULL 的列建索引
前缀长字符串用前缀索引

索引优化实战案例​

案例 1：慢查询优化​

-- 原查询（1.2s）
SELECT * FROM orders 
WHERE user_id = 1001 AND status = 'paid'
ORDER BY created_at DESC 
LIMIT 10;

-- 分析
EXPLAIN ...
-- type: ALL（全表扫描）
-- rows: 500000
-- Extra: Using where; Using filesort

-- 方案：创建联合索引
-- ESR: user_id(E), status(E), created_at(S→R可兼做排序)
CREATE INDEX idx_user_status_created 
ON orders (user_id, status, created_at);

-- 优化后（3ms）
-- type: ref
-- rows: 10
-- Extra: Using index condition (Backward index scan)

案例 2：覆盖索引 + 延迟关联​

-- 原查询（深分页，3.5s）
SELECT * FROM products 
WHERE category_id = 5 
ORDER BY sales DESC 
LIMIT 100000, 20;

-- 优化后（50ms）
SELECT p.* FROM products p
INNER JOIN (
    SELECT id FROM products 
    WHERE category_id = 5 
    ORDER BY sales DESC 
    LIMIT 100000, 20
) AS t ON p.id = t.id;

-- 索引：(category_id, sales)
-- 子查询在索引中完成，只回表 20 行

案例 3：消除 filesort​

-- 原查询（Using filesort）
SELECT id, name, created_at FROM users 
WHERE status = 'active' 
ORDER BY created_at DESC;

-- 创建覆盖索引 + 排序
CREATE INDEX idx_status_created_name 
ON users (status, created_at, name);

-- 优化后
-- Extra: Using index（覆盖索引 + 索引排序，无 filesort）

索引监控​

-- 1. 查看索引使用情况（MySQL 8.0+）
SELECT 
    object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb'
ORDER BY count_star DESC;

-- 2. 查看未使用的索引
SELECT 
    object_schema, object_name, index_name
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL 
  AND count_star = 0 
  AND object_schema NOT IN ('mysql', 'performance_schema');

-- 3. 查看索引大小
SELECT 
    table_name, index_name,
    ROUND(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS size_mb
FROM mysql.innodb_index_stats
WHERE stat_name = 'size'
ORDER BY stat_value DESC;

常见面试问题​

Q1: 列举 5 种常见的索引失效场景​

答案：

  1. 对索引列使用函数：WHERE YEAR(date) = 2024 → 改为范围查询
  2. 隐式类型转换：VARCHAR 列对比数字 → 保持类型一致
  3. LIKE 左模糊：LIKE '%abc' → 改为全文索引或 ES
  4. 联合索引不满足最左前缀：(a,b,c) 只查 b → 调整索引或查询
  5. 查询返回数据量大：优化器判断全表扫描更快 → 缩小查询范围

Q2: 如何判断一个 SQL 是否使用了索引？​

答案：

使用 EXPLAIN 分析执行计划，关注以下字段：

字段关注点
typeALL 全表扫描 → ref/range 索引 → const 主键常量
key实际使用的索引名（NULL 表示没用索引）
rows预估扫描行数（越少越好）
ExtraUsing index 覆盖索引、Using filesort 需要排序

Q3: 一个表的索引太多会有什么问题？​

答案：

  1. 写入变慢：每次 INSERT/UPDATE/DELETE 都要同步维护所有索引
  2. 空间膨胀：每个索引都是一棵 B+ 树，占用额外存储
  3. 优化器选择困难：索引太多，优化器评估每个索引的代价增加
  4. 可能选错索引：优化器的代价模型不一定准确，索引多了容易选到次优索引

建议：单表索引不超过 5-6 个，定期清理未使用的索引。

Q4: 什么是前缀索引？什么时候用？​

答案：

前缀索引是只索引字符串列的前 N 个字符的索引。适用于长字符串列（email、URL 等）。

选择前缀长度的方法：

-- 计算不同前缀长度的选择性
SELECT COUNT(DISTINCT LEFT(col, N)) / COUNT(*) FROM table;
-- 选择选择性达到 90%+ 的最短前缀

限制：不能做覆盖索引、不能做 ORDER BY / GROUP BY。

Q5: 如何优化一个慢 SQL？​

答案：

标准流程：

  1. 定位慢 SQL：开启慢查询日志或 APM 监控
  2. EXPLAIN 分析：查看执行计划
  3. 检查索引：是否有合适索引？是否索引失效？
  4. 优化索引：创建/调整联合索引，考虑覆盖索引
  5. 改写 SQL：避免子查询 → JOIN、深分页 → 延迟关联
  6. 验证效果：EXPLAIN 确认 type 从 ALL → ref/range
  7. 持续监控：上线后观察慢查询日志和性能指标

更详细的 SQL 优化方法见 SQL 优化。

相关链接​