为什么你的 MySQL 索引用了却没有效果，90% 是踩了这些坑

ynglan2026/01/19mysqlMySQL 索引 SQL优化数据库

为什么你的 MySQL 索引用了却没有效果，90% 是踩了这些坑

上线一个商品查询接口，SQL 很简单：

SELECT * FROM product
WHERE category_id = 100 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

为了加速查询，给 category_id 和 status 建了联合索引。结果一跑慢查询日志，发现还是在扫全表。

这个场景我见过很多次。索引建了没用，不是 MySQL 有 bug，是你的用法踩了坑。

索引失效的常见原因

1. 索引列参与了运算

看这个查询：

SELECT * FROM order WHERE YEAR(create_time) = 2025;

YEAR() 函数包裹了索引列，MySQL 无法利用 B+Tree 有序特性，只能放弃索引全表扫描。

正确做法是用范围查询替换函数计算：

SELECT * FROM order
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01';

类似的还有对字符串列用 LEFT()、SUBSTRING()、LIKE 前缀匹配等情况。

2. 类型转换导致索引失效

字段类型是 VARCHAR，查询时用了 INT：

-- phone 字段是 varchar
SELECT * FROM user WHERE phone = 13800138000;

MySQL 会对字符串列隐式做类型转换，相当于 CAST(phone AS SIGNED) = 13800138000，索引失效。

解决方式是保持类型一致：

SELECT * FROM user WHERE phone = '13800138000';

3. LIKE 前缀模糊匹配

SELECT * FROM product WHERE name LIKE '%手机%';

LIKE '%xxx%' 和 LIKE '%xxx' 都无法使用索引，因为 B+Tree 按前缀有序，你这种通配符模式无法确定有序区间。

如果是全文搜索需求，考虑用 MySQL 全文索引或 ES。

如果是 %xxx 结尾，可以考虑反向索引或增加前缀冗余字段。

4. OR 连接了非索引列

SELECT * FROM user WHERE name = '张三' OR age = 25;

name 有索引，age 没索引，OR 条件会导致索引失效。改成 UNION：

SELECT * FROM user WHERE name = '张三'
UNION ALL
SELECT * FROM user WHERE age = 25 AND name != '张三';

5. 使用了 NOT IN / NOT EXISTS

SELECT * FROM product WHERE id NOT IN (1, 2, 3);

NOT IN 和 NOT EXISTS 通常无法利用索引。如果数据量不大可以接受全表扫描，如果数据量大考虑改成 LEFT JOIN ... IS NULL：

SELECT p.* FROM product p
LEFT JOIN excluded e ON p.id = e.id
WHERE e.id IS NULL;

联合索引的顺序问题

联合索引 (A, B, C) 和 (A, C, B) 是不同的。MySQL 遵循最左前缀原则：

-- 能命中索引
WHERE A = ? AND B = ?
WHERE A = ?

-- 无法命中索引
WHERE B = ?
WHERE C = ?
WHERE B = ? AND C = ?

举一个实际场景：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    user_id BIGINT,
    status TINYINT,
    pay_time DATETIME,
    INDEX idx_user_status (user_id, status)
);

-- 这个能命中索引
SELECT * FROM orders WHERE user_id = 1001 AND status = 1;

-- 这个只能命中 user_id，status 无法利用索引
SELECT * FROM orders WHERE user_id = 1001 ORDER BY status;

经验法则：把等值查询的列放前面，范围查询的列放后面。

慢查询日志分析

MySQL 提供了慢查询日志来帮你定位问题：

-- 查看慢查询配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- 查看慢查询日志文件
SHOW VARIABLES LIKE 'slow_query_log_file';

然后用 mysqldumpslow 分析：

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log

或者用 pt-query-digest（Percona Toolkit）做更详细的分析：

pt-query-digest slow.log

一个完整优化案例

之前的订单查询接口，我优化了 7 个地方，最终 QPS 从 120 提升到 2800：

原始 SQL：

SELECT o.id, o.user_id, o.total_amount, o.status,
       u.username, u.email,
       p.product_name
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
LEFT JOIN products p ON o.product_id = p.id
WHERE o.status = 1
  AND o.created_at >= '2026-01-01'
ORDER BY o.created_at DESC
LIMIT 20;

问题分析：

status 字段是条件但没建索引
created_at 范围查询放最后导致无法利用索引排序
LIMIT 20 看似很小，但无索引排序要扫全表

优化步骤：

第一步：建立合适索引

ALTER TABLE orders ADD INDEX idx_status_created (status, created_at);

第二步：业务上允许的话，加时间上限

WHERE o.status = 1
  AND o.created_at >= '2026-01-01'
  AND o.created_at < '2026-01-20'  -- 缩小范围
ORDER BY o.created_at DESC
LIMIT 20;

第三步：如果数据量大考虑延迟关联

SELECT o.id, o.user_id, o.total_amount, o.status,
       u.username, u.email,
       p.product_name
FROM (
    SELECT id FROM orders
    WHERE status = 1
      AND created_at >= '2026-01-01'
    ORDER BY created_at DESC
    LIMIT 20
) t
JOIN orders o ON t.id = o.id
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;

索引不是越多越好

很多人觉得索引多总没坏处，这是个误区。索引的副作用：

增加存储开销：每条索引都是一棵独立的 B+Tree
降低写性能：INSERT/UPDATE/DELETE 要同时维护索引
增加维护成本：数据变更要更新统计信息和索引结构

一般建议：

单表索引数量控制在 5 个以内
复合索引控制 3 个列以内
定期用 EXPLAIN 检查索引使用情况

如何确认索引是否生效

用 EXPLAIN 查看执行计划：

EXPLAIN SELECT * FROM orders WHERE status = 1;

-- 关键字段
-- type: ALL=全表扫描, ref/range=索引扫描
-- key: 实际使用的索引
-- rows: 扫描行数预估
-- Extra: Using filesort/Using temporary 表示有问题

生产环境可以这样批量检查：

SELECT
    t.TABLE_SCHEMA,
    t.TABLE_NAME,
    i.INDEX_NAME,
    i.COLUMN_NAME,
    s.CARDINALITY,
    s.SEQ_IN_INDEX
FROM information_schema.TABLES t
JOIN information_schema.STATISTICS s
    ON t.TABLE_NAME = s.TABLE_NAME
JOIN information_schema.STATISTICS i
    ON s.INDEX_NAME = i.INDEX_NAME
WHERE t.TABLE_SCHEMA = 'your_database'
  AND t.TABLE_NAME = 'your_table'
ORDER BY t.TABLE_NAME, i.INDEX_NAME, s.SEQ_IN_INDEX;

总结

索引用了没效果，90% 是因为：列参与了运算、类型不匹配、OR 破坏索引、LIKE 前缀滥用、联合索引顺序不对。

建议拿到一个慢 SQL 按这个顺序排查：

EXPLAIN 看执行计划
检查列是否被函数包裹
检查类型是否一致
检查是否违背最左前缀原则
检查是否有多余的 OR 条件
考虑是否能加索引或调整索引顺序

索引优化的本质是让 B+Tree 的有序性发挥作用，只要记住这一点，大部分问题都能自己推导出来。

为什么你的 MySQL 索引用了却没有效果，90% 是踩了这些坑

ON THIS PAGE