MySQL索引缺失导致查询慢？批量检测与优化完整指南

问题背景与适用场景

当你的网站或应用出现页面加载慢、数据库CPU升高、慢查询日志大量记录时，索引缺失是最常见的原因之一。
MySQL索引就像书的目录，没有索引，查询会全表扫描，数据量越大越慢。
本文围绕“数据库索引缺失查询缓慢批量优化”这一核心需求，教你使用SQL脚本批量检测缺失索引，并自动生成创建语句，适用于MySQL 5.7及以上版本（含MariaDB 10.3+）。

前置准备：你需要知道的几个前提

• 数据库权限：至少需要 SELECT、CREATE INDEX 和 ALTER 权限。建议准备一个具有读写权限的账号（如root或拥有SUPER权限的用户）。
• 确认查询慢的来源：可以通过 SHOW FULL PROCESSLIST 或开启慢查询日志定位高耗时语句。使用宝塔面板的用户可进入「数据库」→「慢查询」查看。
• 备份数据：对大表添加索引可能短暂锁表，建议先在低峰期操作，或先对生产库创建副本测试。

核心步骤：批量检测与生成索引创建语句

1. 开启 MySQL 的 sys schema（推荐）

MySQL 5.7.7+ 默认自带 sys 库，它提供了视图 schema_unused_indexes 和 schema_index_statistics。
执行以下SQL查询当前库中没有被使用的索引（即可能缺失的反面）：

SELECT * FROM sys.schema_unused_indexes WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema');

这个查询会显示未被使用的索引，它们可以安全删除。
但我们更需要发现“应该存在但缺失”的索引。

2. 利用慢查询日志确定缺失索引的候选列

如果你有慢查询日志，提取出 WHERE 条件、JOIN 关联字段和 ORDER BY 列。
假设慢查询如下：

SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;

那么 user_id 和 created_at 就是需要索引的候选列。

3. 批量生成缺失索引的创建语句

手动检查每个表太慢。
我们可以使用 information_schema 写一个脚本来根据字段名模式批量生成索引创建语句。
注意：此脚本只是个起手模板，请根据实际慢查询涉及的字段调整。

SELECT 
    CONCAT('ALTER TABLE `', TABLE_SCHEMA, '`.`', TABLE_NAME, '` ADD INDEX `idx_', COLUMN_NAME, '` (`', COLUMN_NAME, '`);') AS create_index_sql
FROM 
    information_schema.COLUMNS
WHERE 
    TABLE_SCHEMA NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
    -- 这里替换成你要检查的字段名，例：'user_id', 'created_at', 'status'
    AND COLUMN_NAME IN ('user_id', 'created_at', 'status')
    -- 排除已经存在索引的字段（可选，需要多表查询，这里简化）
ORDER BY 
    TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION;

运行后会输出类似 ALTER TABLE db1.orders ADD INDEX idx_user_id (user_id); 的一系列语句。
你可以先复制保存到文本文件，复查再执行。

4. 安全执行批量创建（推荐低峰期）

• 对于小表（< 10万行）：直接逐条执行上述 ALTER 语句。
• 对于大表（> 100万行）：使用工具 pt-online-schema-change（Percona Toolkit）在线添加索引，避免锁表。如果无法安装该工具，可以在业务低峰期直接执行，并在执行前设置 lock_wait_timeout 为较小值避免长等待。

效果验证：如何确认索引生效

执行添加索引后，用原慢查询SQL加上 EXPLAIN 前缀：

EXPLAIN SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at;

检查 type 列是否从 ALL（全表扫描）变为 ref 或 range，并且 possible_keys 和 keys 列出现你新建的索引名。
同时 rows 列估算的行数会大幅降低。

避坑指南与常见问题

❌ 不要一次性对多个大表加索引

在MySQL 5.7下，ALTER TABLE 会重建表，导致源表被锁（只读）。
对于百万级表，建议分批执行，每次间隔10分钟以上。

❌ 不要盲目为所有字段加索引

索引虽然加速查询，但会减慢写入（INSERT/UPDATE/DELETE）。
只对 WHERE、JOIN 和 ORDER BY 中的高频列加索引。

✅ 如何批量删除无效索引？

用第一步 sys.schema_unused_indexes 查出未使用的索引，然后使用 DROP INDEX 删除。
删除也会短暂锁表，同样建议低峰期操作。

❓ 为什么我创建的索引没被使用？

• 查询中使用了函数包裹字段，如 WHERE DATE(created_at)=...，索引失效。请改写为范围查询。
• 联合索引顺序不对：需遵循最左前缀原则。
• 数据量太小：MySQL 可能认为全表扫描更快。

FAQ

Q1：我没有 sys 库怎么办？
A：可使用 performance_schema + information_schema 手动统计；或安装第三方工具 mytop 监控。

Q2：添加索引时提示 Duplicate key name？
A：该字段已有同名索引。可以先用 SHOW INDEX FROM table_name 查看已有索引，避免重复创建。

Q3：批量操作时数据库卡死怎么办？
A：立即 KILL 执行中的 ALTER 线程（SHOW PROCESSLIST→KILL ID）。建议使用 pt-online-schema-change 或先在测试环境演练。

Q4：我用的宝塔面板，怎么查看慢查询？
A：宝塔面板 → 左侧“数据库” → 选择对应数据库 → 点击“慢查询” → 开启慢查询日志后即可看到执行超过 long_query_time 秒的SQL。

---

完成以上步骤后，你可以明显感受到查询速度的提升。
如果仍有疑问，欢迎继续查阅相关优化教程。
记住：索引优化是持续过程，建议定期（如每月）检查一次慢查询，让优化形成习惯。