MySQL索引优化实战：剖析6大失效场景，将慢查询从5秒降至0.05秒

“这个页面怎么加载这么慢？！”

产品经理已经第三次来敲我的工位了。我看了看监控，一个简单的用户列表查询居然要5秒。打开SQL一看，索引全没生效！

经过一天的优化，我把查询时间从5秒降到了0.05秒。今天就把这次线上救火过程中遇到的6种让索引失效的典型写法，以及对应的修复方案，一次性讲清楚。

故事背景：一个“简单”查询引发的性能血案

上周五下午，运营同事着急地跑过来：“用户列表页面又卡死了！客户在群里催了！”

我赶紧打开监控面板，看到这个“罪魁祸首”查询：

SELECT * FROM users
WHERE city LIKE '%北京%'
AND age > 18
AND create_time > '2023-01-01'
ORDER BY create_time DESC
LIMIT 20;

执行时间：5.2秒！ 而这张表只有100万数据。问题出在哪里？我们一个个坑来填。

第一个坑：最左前缀原则——你永远绕不开的坎

问题分析

-- 问题SQL片段
WHERE city LIKE '%北京%'

这个 LIKE '%北京%' 就是罪魁祸首之一。

为什么这么慢？

1. 索引失效：B+树索引是按照前缀排序的，LIKE '北京%' 可以快速定位到以“北京”开头的记录，但 LIKE '%北京%' 则需要检查每一条记录的内容。
2. 全表扫描：MySQL不得不扫描所有100万行，逐行检查 city 字段是否包含“北京”二字。

实际测试数据

-- 测试1：使用LIKE '%北京%'
EXPLAIN SELECT * FROM users WHERE city LIKE '%北京%';
-- type: ALL（全表扫描）
-- rows: 1,000,000
-- 执行时间: 1.8秒

-- 测试2：使用LIKE '北京%'
EXPLAIN SELECT * FROM users WHERE city LIKE '北京%';
-- type: range（范围扫描）
-- rows: 50,000
-- 执行时间: 0.1秒

性能差距：18倍！

解决方案

方案一：调整业务逻辑（推荐）

-- 如果业务允许，使用前缀匹配
WHERE city LIKE '北京%'

-- 或者使用等值查询
WHERE city = '北京市'

方案二：使用全文索引（MySQL 5.6+）

-- 创建全文索引
ALTER TABLE users ADD FULLTEXT INDEX idx_city_fulltext(city);

-- 使用全文搜索
SELECT * FROM users
WHERE MATCH(city) AGAINST('北京' IN BOOLEAN MODE);

方案三：使用Elasticsearch等专业搜索工具

• 对于复杂的模糊搜索需求，建议将数据同步到 Elasticsearch。
• 架构上可以让 MySQL 专注存储，ES 专注搜索。

第二个坑：范围查询后的索引列失效

问题分析

-- 问题SQL片段
WHERE age > 18 AND create_time > ‘2023-01-01’

假设我们有一个复合索引 idx_age_time(age, create_time)。

为什么索引会部分失效？

1. 复合索引的顺序：索引先按 age 排序，再按 create_time 排序。
2. 范围查询的副作用：当使用 age > 18 这样的范围条件时，在这个范围内，create_time 在索引中不再是全局有序的。因此，MySQL 只能利用索引加速 age 的筛选，对于 create_time 的条件，则需要在筛选出的行中进一步检查。

实际测试数据

-- 创建测试索引
CREATE INDEX idx_age_time ON users(age, create_time);

-- 测试查询
EXPLAIN SELECT * FROM users
WHERE age > 18 AND create_time > ‘2023-01-01’;
-- key: idx_age_time
-- key_len: 4（只用了age，create_time没用上！）
-- type: range
-- rows: 800,000

-- 对比：调整查询顺序
EXPLAIN SELECT * FROM users
WHERE create_time > ‘2023-01-01’ AND age > 18;
-- 结果一样！因为优化器会调整WHERE条件顺序

解决方案

方案一：调整索引顺序（根据查询模式）

-- 如果经常按create_time范围查询，然后按age过滤
CREATE INDEX idx_time_age ON users(create_time, age);

-- 查询时就能用到完整索引
EXPLAIN SELECT * FROM users
WHERE create_time > ‘2023-01-01’ AND age > 18;
-- key_len: 8（两个字段都用上了！）

方案二：使用IN替代范围查询（如果范围值有限）

-- 如果age只有有限的几个可选值
SELECT * FROM users
WHERE age IN (19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30)
AND create_time > ‘2023-01-01’;
-- IN是等值查询，其后的create_time依然可以用索引

方案三：建立多个单列索引，让优化器选择

CREATE INDEX idx_age ON users(age);
CREATE INDEX idx_create_time ON users(create_time);

-- MySQL可能会使用index_merge（索引合并）
EXPLAIN SELECT * FROM users
WHERE age > 18 AND create_time > ‘2023-01-01’;
-- type: index_merge
-- Extra: Using intersect(idx_age, idx_create_time)

第三个坑：函数操作导致索引失效

问题分析

-- 常见的函数操作
WHERE DATE(create_time) = '2023-10-01'
WHERE YEAR(create_time) = 2023
WHERE LEFT(name, 2) = '北京'
WHERE UPPER(email) = 'USER@EXAMPLE.COM'

为什么索引失效？
索引存储的是字段的原始值，而不是函数计算后的值。当你在条件中对索引列使用函数时，MySQL 无法使用索引树来快速定位，因为树是按照原始值构建的。

实际测试数据

-- 测试1：使用函数
SELECT * FROM users WHERE DATE(create_time) = ‘2023-10-01’;
-- type: ALL（全表扫描）
-- 执行时间: 1.5秒

-- 测试2：不使用函数
SELECT * FROM users
WHERE create_time >= ‘2023-10-01 00:00:00’
AND create_time < ‘2023-10-02 00:00:00’;
-- type: range（范围扫描）
-- 执行时间: 0.05秒

性能差距：30倍！

解决方案

方案一：把函数移到等号右边（如果可能）

-- 错误写法
WHERE YEAR(create_time) = 2023

-- 正确写法
WHERE create_time >= ‘2023-01-01’
  AND create_time < ‘2024-01-01’

方案二：使用计算列（MySQL 5.7+）

-- 创建计算列（存储型）
ALTER TABLE users
ADD COLUMN create_year INT
GENERATED ALWAYS AS (YEAR(create_time)) STORED;

-- 为计算列创建索引
CREATE INDEX idx_create_year ON users(create_year);

-- 查询计算列
SELECT * FROM users WHERE create_year = 2023;

方案三：预先计算并存储冗余字段

-- 添加冗余字段
ALTER TABLE users ADD COLUMN create_date DATE;
UPDATE users SET create_date = DATE(create_time);

-- 为冗余字段创建索引
CREATE INDEX idx_create_date ON users(create_date);

-- 查询冗余字段
SELECT * FROM users WHERE create_date = ‘2023-10-01’;

第四个坑：OR条件导致索引失效

问题分析

-- OR连接不同索引列
WHERE name = ‘张三’ OR phone = ‘13800138000’
WHERE age > 18 OR salary > 10000

为什么索引可能失效？
MySQL 在处理 OR 条件连接不同列时，优化器可能会评估认为：分别使用两个索引再合并结果集的成本，比直接进行全表扫描还要高，因此会选择放弃使用索引。

实际测试数据

-- 创建测试索引
CREATE INDEX idx_name ON users(name);
CREATE INDEX idx_phone ON users(phone);

-- 测试OR查询
EXPLAIN SELECT * FROM users
WHERE name = ‘张三’ OR phone = ‘13800138000’;
-- type: ALL（全表扫描）
-- rows: 1,000,000
-- 执行时间: 1.2秒

-- 测试分别查询
SELECT * FROM users WHERE name = ‘张三’
-- type: ref, rows: 1, time: 0.001s

SELECT * FROM users WHERE phone = ‘13800138000’
-- type: ref, rows: 1, time: 0.001s

解决方案

方案一：使用UNION ALL替代OR

SELECT * FROM users WHERE name = ‘张三’
UNION ALL
SELECT * FROM users WHERE phone = ‘13800138000’;
-- 执行时间: 0.002秒

方案二：使用覆盖索引

-- 创建覆盖索引，包含查询所需的所有字段
CREATE INDEX idx_name_phone_covering ON users(name, phone, id, email);

-- 只查询索引包含的字段
SELECT id, name, phone, email
FROM users
WHERE name = ‘张三’ OR phone = ‘13800138000’;
-- type: index（扫描覆盖索引即可，无需回表）

方案三：使用CASE WHEN重构条件（特定场景）

SELECT * FROM users
WHERE 1 = (
CASE
WHEN name = ‘张三’ THEN 1
WHEN phone = ‘13800138000’ THEN 1
ELSE 0
END
);
-- 有时优化器能更好地处理这种写法，但并非总是有效，需测试验证。

第五个坑：类型转换导致隐式索引失效

问题分析

-- 常见的类型转换问题
WHERE phone = 13800138000  -- phone是varchar，传入数字
WHERE id = ‘123’           -- id是int，传入字符串
WHERE status = ‘1’         -- status是tinyint，传入字符串

为什么索引失效？
MySQL 在比较不同类型的数据时，会进行隐式类型转换。如果这个转换发生在索引列上（例如将字符串列 phone 转换为数字来与传入的数字比较），索引就会失效，因为转换后的值并未存储在索引中。

实际测试数据

-- phone字段是varchar(20)
CREATE INDEX idx_phone ON users(phone);

-- 测试1：传入字符串（类型一致）
EXPLAIN SELECT * FROM users WHERE phone = ‘13800138000’;
-- type: ref
-- key_len: 63

-- 测试2：传入数字（类型不一致）
EXPLAIN SELECT * FROM users WHERE phone = 13800138000;
-- type: ALL（全表扫描）
-- 因为MySQL需要把每行的phone转换为数字再比较

性能差距：可能高达上千倍！

解决方案

方案一：保持应用程序中传入的数据类型与表字段类型一致

-- 首先，查看字段类型
DESC users;

-- 然后，确保传入值的类型与字段类型一致
WHERE phone = ‘13800138000’  -- phone是varchar
WHERE id = 123               -- id是int
WHERE status = 1             -- status是tinyint

方案二：修改表结构（如果最初设计不合理）

-- 如果phone字段确实应该只存储数字
ALTER TABLE users MODIFY COLUMN phone BIGINT;

-- 修改后就可以直接比较数字了
WHERE phone = 13800138000

方案三：使用CAST显式转换（需注意，仍然可能导致索引失效）

-- 将传入值转换为列的类型
WHERE phone = CAST(13800138000 AS CHAR)
-- 这样转换发生在等号右侧，索引列phone未被函数包裹，索引可能生效

第六个坑：ORDER BY和GROUP BY导致文件排序

问题分析

-- 常见的排序和分组问题
ORDER BY create_time DESC
GROUP BY city
ORDER BY age, create_time

为什么慢？
如果排序（ORDER BY）或分组（GROUP BY）的字段没有索引，或者索引顺序无法满足排序需求，MySQL 就需要在内存或磁盘上进行一次额外的排序操作，即“文件排序（filesort）”，这在数据量大时非常耗时。

实际测试数据

-- 测试1：没有索引的排序
EXPLAIN SELECT * FROM users ORDER BY create_time DESC LIMIT 20;
-- type: ALL
-- Extra: Using filesort
-- 执行时间: 1.8秒

-- 测试2：有索引的排序
CREATE INDEX idx_create_time ON users(create_time);
EXPLAIN SELECT * FROM users ORDER BY create_time DESC LIMIT 20;
-- type: index
-- Extra: Backward index scan（反向索引扫描）
-- 执行时间: 0.01秒

性能差距：180倍！

解决方案

方案一：为排序字段创建索引

-- 单字段排序
CREATE INDEX idx_create_time ON users(create_time);

-- 多字段排序
CREATE INDEX idx_age_create_time ON users(age, create_time);

方案二：使用覆盖索引避免回表

-- 如果查询只需要部分字段
CREATE INDEX idx_covering ON users(create_time, id, name, email);

SELECT id, name, email, create_time
FROM users
ORDER BY create_time DESC
LIMIT 20;
-- 使用覆盖索引，不需要回表查数据行，效率极高

方案三：优化深分页查询

-- 传统分页（越往后越慢）
SELECT * FROM users ORDER BY id LIMIT 1000000, 20;

-- 优化分页（记住上次的位置）
SELECT * FROM users
WHERE id > 1000000 -- 上次查询的最后一个id
ORDER BY id
LIMIT 20;

完整优化案例：从5秒到0.05秒的实战复盘

原始问题SQL

SELECT u.*, o.order_count, p.point_balance
FROM users u
LEFT JOIN (
SELECT user_id, COUNT(*) as order_count
FROM orders
WHERE create_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY user_id
) o ON u.id = o.user_id
LEFT JOIN user_points p ON u.id = p.user_id
WHERE u.city LIKE ‘%北京%’
AND u.age > 18
AND u.create_time > ‘2023-01-01’
ORDER BY u.create_time DESC
LIMIT 20;

优化步骤

第一步：使用EXPLAIN分析问题根因

EXPLAIN [原始SQL];
-- 发现：
-- 1. users表全表扫描（city LIKE导致）
-- 2. 子查询全表扫描orders表
-- 3. ORDER BY导致filesort

第二步：优化users表的主查询

-- 1. 与业务方沟通，将模糊查询改为等值查询（假设业务支持）
-- WHERE u.city LIKE ‘%北京%’ 改为 ->
WHERE u.city = ‘北京市’

-- 2. 创建符合查询模式的复合索引
CREATE INDEX idx_city_age_time ON users(city, age, create_time);

-- 3. 先优化主查询部分
SELECT u.*
FROM users u
WHERE u.city = ‘北京市’
AND u.age > 18
AND u.create_time > ‘2023-01-01’
ORDER BY u.create_time DESC
LIMIT 20;

第三步：优化关联的子查询

-- 为orders表创建利于聚合的索引
CREATE INDEX idx_user_create ON orders(user_id, create_time);
-- 该索引可以高效地完成“按user_id分组，并筛选时间”的操作

第四步：重构整个查询，限制驱动集

-- 最终优化版：先筛选出20个核心用户，再做关联
SELECT
    u.id, u.name, u.age, u.city, u.create_time,
    COALESCE(o.order_count, 0) as order_count,
    COALESCE(p.point_balance, 0) as point_balance
FROM (
    SELECT id, name, age, city, create_time
    FROM users
    WHERE city = ‘北京市’
    AND age > 18
    AND create_time > ‘2023-01-01’
    ORDER BY create_time DESC
    LIMIT 20
) u
LEFT JOIN (
    SELECT user_id, COUNT(*) as order_count
    FROM orders
    WHERE create_time > DATE_SUB(NOW(), INTERVAL 30 DAY)
    GROUP BY user_id
) o ON u.id = o.user_id
LEFT JOIN user_points p ON u.id = p.user_id
ORDER BY u.create_time DESC;

优化效果对比

优化阶段	执行时间	扫描行数	关键改进
原始SQL	5.2秒	1,000,000+	-
第一步（改条件）	2.1秒	200,000	修改city条件，避免全模糊
第二步（建索引）	0.8秒	50,000	创建复合索引 idx_city_age_time
第三步（子查询）	0.3秒	20,000	为orders表创建覆盖索引
最终版（限驱动集）	0.05秒	20	使用子查询先限制结果集大小

总性能提升：超过100倍！

必备工具和技巧：工欲善其事，必先利其器

1. 使用EXPLAIN进行执行计划分析

EXPLAIN 是理解 SQL 如何执行的最重要工具。

-- 基础用法
EXPLAIN SELECT * FROM users WHERE id = 1;

-- 查看详细的JSON格式执行计划
EXPLAIN FORMAT=JSON SELECT * FROM users WHERE id = 1;

-- MySQL 8.0+ 可以使用ANALYZE进行实际执行分析
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;

关键字段解读：

• type：访问类型，从好到坏：system > const > eq_ref > ref > range > index > ALL
• key：实际使用的索引
• rows：预估需要扫描的行数（越小越好）
• Extra：额外信息，需特别注意 Using filesort（文件排序）和 Using temporary（使用临时表）

2. 开启并分析慢查询日志

慢查询日志能帮你发现所有执行缓慢的语句。

-- 查看相关配置
SHOW VARIABLES LIKE ‘%slow_query%’;
SHOW VARIABLES LIKE ‘%long_query_time%’;

-- 动态开启（重启后会失效）
SET GLOBAL slow_query_log = ‘ON’;
SET GLOBAL long_query_time = 1; -- 设置慢查询阈值为1秒
SET GLOBAL log_queries_not_using_indexes = ‘ON’; -- 记录未使用索引的查询

永久配置需修改 my.cnf 文件：

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100 -- 仅记录扫描行数超过100的查询

3. 使用性能分析工具

MySQL性能模式：

-- 查看最耗时的SQL摘要
SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

第三方工具（如Percona Toolkit）：

# 分析慢查询日志，生成报告
pt-query-digest slow.log

# 检查索引使用情况
pt-index-usage slow.log

# 查找重复或冗余的索引
pt-duplicate-key-checker -u root -p password

4. 监控关键系统视图

-- 查看可能未使用的索引（MySQL 8.0 sys schema）
SELECT * FROM sys.schema_unused_indexes;

-- 查看索引统计信息，了解大小和组成
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’
AND database_name = ‘your_database’
ORDER BY size_mb DESC;

索引设计的最佳实践与哲学

1. 核心设计原则

• 选择区分度高的列：索引列不重复值的比例越高，过滤效果越好。
• 考虑最左前缀：设计复合索引时，将等值查询的列放在范围查询列之前。
• 避免过多索引：每个索引都有写入和维护成本。通常建议单表索引数量不超过5个。
• 优先使用覆盖索引：如果查询的所有字段都包含在某个索引中，可以避免回表，极大提升性能。

2. 索引维护策略

索引不是创建完就一劳永逸的，需要定期维护。

-- 更新表的统计信息，帮助优化器做出正确判断
ANALYZE TABLE users;

-- 重建表并整理碎片（适用于表数据删除较多的情况）
OPTIMIZE TABLE users;

最后的话：索引优化的哲学

优化 MySQL 索引就像中医调理，需要“望闻问切”，对症下药。没有所谓的“银弹”，只有结合具体业务场景的持续分析、测试和调整。

记住这些核心心法：

1. 理解业务重于技术：索引是为查询服务的，必须基于真实的、高频的查询模式来设计。
2. 索引是双刃剑：加速查询的同时，会降低写入速度并占用空间。
3. 测试验证是王道：任何优化方案在上线前，都必须在模拟环境中验证其效果。
4. 监控驱动优化：没有监控，你就无法发现潜在的性能衰退点。

最关键的体会是：一个好的、设计合理的索引，其带来的性能收益往往远超一百行复杂的业务代码优化。 当你深入理解数据访问路径后，优化本身就是一种艺术。希望这次从5秒到0.05秒的实战经历，能帮助你在自己的项目中游刃有余地解决性能问题。如果你在实践中遇到其他棘手的场景，欢迎到 云栈社区 的数据库板块与大家交流探讨。