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

商品检索慢时，最常见的原因是数据库表中缺少合适的索引，导致每次查询都要全表扫描。
本文教你用 MySQL 自带的慢查询日志和基本 SQL 命令，定位缺失索引并批量添加。
即使不懂数据库原理，按下面三步操作，也能明显提升商品列表、搜索页面的加载速度。

为什么商品检索会变慢：缺少索引的典型表现

当商品表数据量超过几万行，搜索条件（如商品名、分类、价格区间）对应的列上没有索引时，MySQL 会逐行扫描整个表。
这种“全表扫描”在数据量大时响应时间会从毫秒级飙升到秒级。缺少索引的直接后果就是检索响应变慢、CPU 和内存消耗激增。

准备工作：安全地开始操作

• 备份数据库：使用 mysqldump -u root -p 数据库名 > 备份.sql，防止操作失误。
• 确认 MySQL 版本：运行 SELECT VERSION();，不同版本支持的功能有差异。
• 获取数据库访问权限：需要有 ALTER 和 CREATE 权限。
• 准备工具：如果表很大，建议安装 pt-online-schema-change（Percona Toolkit），可以不停机添加索引。

第一步：找到缺失的索引（定位问题）

方法一：开启慢查询日志（推荐新手）

执行以下 SQL，让 MySQL 记录执行时间超过 2 秒的查询：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 2;
SET GLOBAL log_queries_not_using_indexes = ON;

等待一段时间（比如业务高峰后 1 小时），查看慢查询日志路径：

SHOW VARIABLES LIKE 'slow_query_log_file';

用文本编辑器打开该文件，找到关于商品查询的 SQL 语句。
常见的慢查询如：

SELECT * FROM products WHERE product_name LIKE '%手机%';
SELECT * FROM products WHERE category_id = 5 AND price BETWEEN 100 AND 500;

这些 SQL 的 WHERE 条件中涉及的列（如 product_name、category_id、price）就是需要添加索引的目标。

方法二：使用 EXPLAIN 手动验证

对怀疑慢的查询，在前面加 EXPLAIN：

EXPLAIN SELECT * FROM products WHERE category_id = 5;

查看输出中的 type 字段：如果值是 ALL（全表扫描），而且 Extra 为空，说明该查询没有用到索引，需要添加。

第二步：批量添加缺失索引（执行操作）

编写 ALTER TABLE 语句

将第一步锁定的缺失列整理成一条或多条 ALTER 命令。建议将多条添加操作放在同一个事务中执行，减少多次锁表。 例如：

ALTER TABLE products
  ADD INDEX idx_category_id (category_id),
  ADD INDEX idx_price (price),
  ADD INDEX idx_product_name (product_name);

注意：如果 product_name 用于 LIKE 模糊查询，需要创建前缀索引或全文索引，普通索引对 LIKE '%keyword%' 无效。这里先假设是你使用的是等值或前缀查询。

针对大表：使用 pt-online-schema-change 无锁添加

如果你的商品表超过 100 万行，直接 ALTER 会导致长时间锁表。
改用以下命令（需要已安装 Percona Toolkit）：

pt-online-schema-change --alter "ADD INDEX idx_category_id (category_id)" D=数据库名,t=products --execute

它会创建临时表、同步数据，完成后自动切换，业务几乎不受影响。可以逐次添加多个索引，或直接在 --alter 参数中写多条语句并用逗号隔开。

第三步：验证效果

方法一：用 EXPLAIN 检查执行计划

重新执行慢查询前的 EXPLAIN，看 type 是否变为 ref 或 range，possible_keys 和 key 是否显示新索引。

方法二：对比查询耗时

SELECT * FROM products WHERE category_id = 5 LIMIT 10;

用秒表或 MySQL 客户端的时间显示，添加索引后耗时应该降低 90% 以上。

避坑指南

• 不要盲目加索引：索引会占用磁盘空间并拖慢插入、更新速度。只给高频查询条件列加索引。
• 复合索引的顺序很重要：例如 (category_id, price) 能加速 WHERE category_id=5 AND price>100，但如果只查询 price 则不会走该索引。
• 大表操作别在白天高峰执行：用 pt-online-schema-change 可以避免锁表，但仍需要监控磁盘和 CPU。
• 索引名要有含义：如 idx_字段名，方便后续维护。

常见问题解答

Q1：添加索引后商品检索还是慢怎么办？
A：检查慢查询是否还有其他未被索引覆盖的查询（比如非索引列的排序、函数运算），或者虽然走了索引但返回数据量太大（加 LIMIT 或分页）。

Q2：索引不是越多越好吗？
A：索引越多，写入（INSERT/UPDATE/DELETE）越慢。商品表通常是读多写少，但也要避免无谓索引。一般单表索引不超过 5-8 个。

Q3：怎么判断哪些列该加索引？
A：重点看 WHERE 条件列、JOIN 连接列、ORDER BY 列（排序用索引可避免文件排序）。利用慢查询日志和 examine_sql 工具辅助。

Q4：批量添加索引锁表了怎么办？
A：如果表较小且业务允许，直接用 ALTER；大表必须用 pt-online-schema-change 或 MySQL 8.0 的 INSTANT 算法（仅支持部分操作），具体参考官方文档。

如果你需要稳定的数据库运行环境，可以考虑使用具备正规 IDC 资质的云服务商（如泽御云）提供的云服务器，确保磁盘 IO 和内存足够应对索引维护。
但本文的操作本身不依赖特定厂商，在任何 MySQL 环境都能执行。
按照以上步骤操作后，你的商品检索速度应当有明显提升。
如果遇到异常，优先检查慢查询日志和 EXPLAIN 输出，定位具体问题后再调整索引策略。