数据库缺失索引批量添加提升商品检索速度

商品列表页越来越慢、搜索卡顿，大多数时候不是服务器性能不够，而是数据库里缺了索引。
本文从零开始，带你用 SQL 查出缺失索引，批量生成 ADD INDEX 语句并执行，最后通过慢查询日志确认商品检索速度有没有真正提升。
整个过程不需要改代码，照着操作就能完成。

先确认检索慢的罪魁祸首是不是索引

索引相当于书的目录，没有目录就要整本翻。
商品表数据量到几万、几十万条后，缺失索引的代价会直接体现在查询响应上。

先花两分钟确认是不是索引问题，在数据库客户端或命令行里执行：

SHOW CREATE TABLE goods\G;

重点看 KEY 或 INDEX 后面的字段。
如果商品检索经常用到的 category_id、goods_name、status 等字段没有出现在任何索引里，那基本就是缺失索引导致的慢查询。

再用慢查询日志验证，MySQL 中执行：

SHOW VARIABLES LIKE 'slow_query_log';
SHOW VARIABLES LIKE 'long_query_time';

如果慢查询日志已开启，可以查到执行时间超过阈值的 SQL，这些 SQL 的 WHERE、ORDER BY、JOIN 字段就是索引优化的重点。

用系统表批量找出缺失索引

MySQL 自带一张视图 sys.schema_unused_indexes，但真正适合“找缺失”的是表 sys.statistics 和 information_schema 的组合。
更实用的方式是先看实际查询集中在哪些条件字段，再结合 EXPLAIN 确认。

下面这条 SQL 可以列出所有自建表中没有被任何索引覆盖的字段组合，帮你在批量操作前圈定候选字段：

SELECT
  TABLE_NAME,
  COLUMN_NAME,
  COUNT(*) AS occurrences
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '你的数据库名'
  AND COLUMN_NAME IN ('category_id', 'goods_name', 'status', 'brand_id')
  AND NOT EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS s
    WHERE s.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA
      AND s.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME
      AND s.COLUMN_NAME = information_schema.COLUMNS.COLUMN_NAME
  )
GROUP BY TABLE_NAME, COLUMN_NAME;

把 你的数据库名 换成实际库名。
运行以后，能看到哪些表、哪些字段虽然频繁参与查询，但当前没有索引。
如果结果为空，说明索引缺失问题不明显，可以继续用 EXPLAIN 逐个排查慢 SQL。

批量生成添加索引的 ALTER 语句

确认目标字段后，不要手工一个个建，直接用 SQL 拼接出 ALTER 语句，把结果复制出来统一执行。

SELECT
  CONCAT(
    'ALTER TABLE `', TABLE_NAME, '` ADD INDEX `idx_', COLUMN_NAME, '` (`', COLUMN_NAME, '`);'
  ) AS alter_statement
FROM (
  -- 这里直接引用上一步“候选字段”的查询逻辑
  SELECT DISTINCT TABLE_NAME, COLUMN_NAME
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = '你的数据库名'
    AND COLUMN_NAME IN ('category_id', 'goods_name', 'status', 'brand_id')
    AND NOT EXISTS (
      SELECT 1 FROM information_schema.STATISTICS s
      WHERE s.TABLE_SCHEMA = information_schema.COLUMNS.TABLE_SCHEMA
        AND s.TABLE_NAME = information_schema.COLUMNS.TABLE_NAME
        AND s.COLUMN_NAME = information_schema.COLUMNS.COLUMN_NAME
    )
) AS missing_cols;

执行后会得到类似这样的一批语句：

ALTER TABLE `goods` ADD INDEX `idx_category_id` (`category_id`);
ALTER TABLE `goods` ADD INDEX `idx_status` (`status`);
ALTER TABLE `goods` ADD INDEX `idx_brand_id` (`brand_id`);

把生成的结果复制保存，先不要急着全部执行。

批量执行索引的实操与避坑

执行批量添加索引时，最容易踩的坑有三个。

第一，大表直接 ALTER 会锁表。 商品表如果已经到了百万级，直接在业务高峰期执行 ALTER TABLE，可能造成长时间不可写。
推荐用 gh-ost 或 pt-online-schema-change 这类在线变更工具，如果表不大（几十万行以内），可以短暂在低峰期执行。

第二，重复索引会影响写入性能。 如果字段已经是某个联合索引的最左前缀，单独再加一个单列索引反而多余。
执行前先用下面语句查一下现有索引：

SHOW INDEX FROM goods;

看到 Seq_in_index 为 1 且字段顺序已经包含目标字段时，不要再重复添加。

第三，不要一次性全部执行。 建议把 ALTER 语句按表分组，一次执行一张表，每张表完成后用 SHOW INDEX FROM 表名 验证。

低峰期批量执行的示例脚本（bash）：

mysql -uroot -p 你的数据库名 < add_index.sql

add_index.sql 里只放确认过的 ALTER 语句。
执行过程中如果出现 Duplicate key name 报错，说明索引已存在，跳过该语句即可。

验证商品检索速度是否真正提升

索引加完不是结束，还要验证效果。

先看执行计划，对之前慢的查询语句执行：

EXPLAIN SELECT * FROM goods WHERE category_id = 123 AND status = 1 ORDER BY id DESC LIMIT 20;

重点看 type 列，从 ALL（全表扫描）变成 ref 或 range，说明索引生效了。key 列会显示实际用到的索引名。

再看真实响应时间，在 MySQL 命令行里开启执行时间统计：

SET profiling = 1;
SELECT * FROM goods WHERE category_id = 123 AND status = 1 ORDER BY id DESC LIMIT 20;
SHOW PROFILES;

对比添加索引前后的 Duration 值，通常会有明显下降。

最后回看慢查询日志，如果之前记录的 SQL 不再出现或执行时间远低于 long_query_time，说明批量添加索引确实提升了商品检索速度。

常见疑问补充

为什么添加索引后商品搜索反而变慢了？ 可能添加了重复索引或冗余索引，导致写入时维护成本增加。
建议用 sys.schema_redundant_indexes 检查多余索引，在低峰期删除。

所有常用字段都需要加索引吗？ 不一定。
区分度低的字段如 status，单独建索引可能收益有限，更适合和其他字段组成联合索引。
可以用 SELECT COUNT(DISTINCT column_name) / COUNT(*) 粗略估算区分度。

批量添加索引失败怎么办？ 看报错信息，Duplicate column name 是已经存在，Table doesn't exist 是库名或表名错误。
把报错语句单独取出，手动执行确认。

如果你正在处理数据库缺失索引批量添加提升商品检索速度的问题，建议先按本文步骤完整执行，再根据自己的环境微调；
遇到异常时优先回看避坑和高频问题部分，尤其是锁表和重复索引这两个最常见的坑。
索引优化是个持续过程，后续每次上线新功能或增加查询条件，都建议顺带检查一次索引使用情况。