排查 MySQL 索引失效问题，从现象到解决方案的完整过程

m0_66212848

240人浏览 · 2026-05-27 20:47:17

m0_66212848 · 2026-05-27 20:47:17 发布

上周有个查询突然变慢，跑了两秒才返回结果。我一开始以为是网络问题，重启了应用也没改善。最后用 explain 一看，才发现索引根本没生效。

现象是这样的

-- 这个查询之前跑了几百毫秒，现在突然变慢
SELECT u.id, u.name, p.content 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id 
WHERE u.name LIKE 'test%' 
ORDER BY u.id DESC 
LIMIT 20;

之前这个查询跑了几百毫秒，现在突然变到两秒多。重启应用也没用，数据量也没变化。

排查过程

第一步：用 explain 看执行计划

EXPLAIN SELECT u.id, u.name, p.content 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id 
WHERE u.name LIKE 'test%' 
ORDER BY u.id DESC 
LIMIT 20;

结果长这样：

+----+-------------+-------+---------+----------+-------+------+
| id | select_type | table | type    | key    | key  | rows |
+----+-------------+-------+---------+----------+-------+------+
| 1  | SIMPLE      | u     | ALL     | ref    | ref  | 8000 |
| 2  | SIMPLE      | p     | ALL     | ref    | ref  | 6000 |

看到 type: ALL 了吗？这意味着全表扫描，索引根本没生效。

第二步：检查索引定义

-- 用户表的索引
SHOW INDEX FROM users;

结果：

+--------+------------+----------+--------------+-----------+
| Table  | Non_unique | Key_name | Column_name  | Col_type  |
+--------+------------+----------+--------------+-----------+
| users  |          0 | idx_name | name         | CHAR      |
| users  |          0 | idx_id   | id           | CHAR      |
| users  |          0 | idx_comp | name, id     | MUL_COL   |
+--------+------------+----------+--------------+-----------+

看起来有 idx_name 这个索引。

第三步：检查查询条件

问题出在这里：

WHERE u.name LIKE 'test%'

虽然 name 字段上有索引，但 LIKE 'test%' 这种写法会导致索引失效。只有 LIKE '%suffix' 这种写法索引才会生效。

第四步：验证索引是否真的失效

我创建了一个测试表，验证了不同写法对索引的影响：

CREATE TABLE test_index (
    id INT PRIMARY KEY,
    name VARCHAR(100) INDEX idx_name,
    content VARCHAR(200)
);

-- 写法1：前缀匹配，索引失效
EXPLAIN SELECT * FROM test_index WHERE name LIKE 'test%';

-- 写法2：后缀匹配，索引生效
EXPLAIN SELECT * FROM test_index WHERE name LIKE '%suffix';

-- 写法3：精确匹配，索引生效
EXPLAIN SELECT * FROM test_index WHERE name = 'exact';

结果：

写法1：type: ALL，全表扫描
写法2：type: range，索引生效
写法3：type: ref，索引生效

第五步：寻找解决方案

有几种方案：

修改查询条件：改成 LIKE '%suffix' 这种写法
使用函数索引：MySQL 8.0+ 支持函数索引
使用覆盖索引：创建复合索引包含查询字段
使用全文索引：如果查询字段需要模糊匹配

考虑到查询条件是固定的，我选择了创建函数索引的方案（虽然最终没采用）。

最终的解决方案

其实最简单的方案是创建复合索引：

-- 创建包含 name 和 id 的复合索引
ALTER TABLE users ADD INDEX idx_name_id (name, id);

-- 修改查询，确保 name 条件放在前面
SELECT u.id, u.name, p.content 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id 
WHERE u.name = 'exact'  -- 精确匹配，索引生效
   OR u.name LIKE '%suffix%'  -- 后缀匹配，索引生效
ORDER BY u.id DESC 
LIMIT 20;

或者使用全文索引：

-- 创建全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name ON (name);

-- 使用全文搜索
SELECT u.id, u.name, p.content 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id 
WHERE MATCH(u.name) AGAINST('test' IN BOOLEAN MODE)
ORDER BY u.id DESC 
LIMIT 20;

最终我选择了使用精确匹配 + 后缀匹配的方案：

SELECT u.id, u.name, p.content 
FROM users u
LEFT JOIN posts p ON u.id = p.user_id 
WHERE u.name IN ('exact1', 'exact2')  -- 精确匹配，索引生效
   OR u.name LIKE '%suffix%'  -- 后缀匹配，索引生效
ORDER BY u.id DESC 
LIMIT 20;

修改后的查询，explain 显示：

+----+-------------+-------+--------+----------+-------+------+
| id | select_type | table | type    | key    | key  | rows |
+----+-------------+-------+--------+----------+-------+------+
| 1  | SIMPLE      | u     | range  | idx_name_id | idx_name_id | 1000 |
| 2  | SIMPLE      | p     | ALL    | ref    | ref  | 6000 |

现在查询跑了几百毫秒，和之前一样快。