MySQL查询优化求助：WHERE列有索引却未被选用

核心问题分析 #

从执行计划和SQL来看，MySQL不使用idx_createdTimestamp索引的主要原因是WHERE条件的时间单位不匹配，导致过滤后的结果集接近全表数据，优化器认为全表扫描成本更低：

• 字段createdTimestamp存储的是毫秒级时间戳（13位数字），而unix_timestamp(date_add(now(6), INTERVAL -7 day))返回的是秒级时间戳（10位数字）。
• 条件createdTimestamp >= 秒级时间戳会匹配几乎所有数据（13位数值远大于10位），此时索引扫描的成本高于全表扫描，优化器自然选择全表扫描。

具体优化步骤 #

1. 修复WHERE条件的单位匹配 #

将右边的秒级时间戳转换为毫秒级，确保过滤出真正的最近7天数据：

SELECT 
  cart_medicine_detail._id AS _id, 
  cart_medicine_detail.cartId AS cartId, 
  cart_medicine_detail.medicineId AS medicineId, 
  cart_medicine_detail.distributorId AS distributorId, 
  cart_medicine_detail.quantity AS quantity, 
  cart_medicine_detail.name AS name, 
  cart_medicine_detail.itemCode AS itemCode, 
  cart_medicine_detail.dealType AS dealType, 
  cart_medicine_detail.reason AS reason, 
  cart_medicine_detail.isDeleted AS isDeleted, 
  cart_medicine_detail.actualPrice AS actualPrice, 
  cart_medicine_detail.source AS source, 
  from_unixtime(cart_medicine_detail.createdTimestamp / 1000) AS createdTimestamp, 
  from_unixtime(cart_medicine_detail.updatedTimestamp / 1000) AS updatedTimestamp, 
  cart_medicine_detail.medicineCategory AS medicineCategory, 
  cart_medicine_detail.medicineSubCategory AS medicineSubCategory 
FROM 
  saveo_prod.cart_medicine_detail 
WHERE 
  cart_medicine_detail.createdTimestamp >= unix_timestamp(date_add(now(6), INTERVAL -7 day)) * 1000;

2. 创建覆盖索引避免回表 #

即使条件修复后，如果查询需要返回的字段较多，仅靠idx_createdTimestamp索引需要频繁回表查询数据，成本依然很高。建议创建覆盖索引，包含过滤字段和所有查询字段：

• MySQL 8.0+版本（支持INCLUDE，索引体积更小）：

CREATE INDEX idx_created_covering ON saveo_prod.cart_medicine_detail (createdTimestamp)
INCLUDE (
  _id, cartId, medicineId, distributorId, quantity, name, 
  itemCode, dealType, reason, isDeleted, actualPrice, source, 
  updatedTimestamp, medicineCategory, medicineSubCategory
);

• 低版本MySQL（无INCLUDE，需将所有字段加入索引）：

CREATE INDEX idx_created_covering ON saveo_prod.cart_medicine_detail (
  createdTimestamp, _id, cartId, medicineId, distributorId, quantity, 
  name, itemCode, dealType, reason, isDeleted, actualPrice, source, 
  updatedTimestamp, medicineCategory, medicineSubCategory
);

覆盖索引可以让MySQL直接从索引中获取所有需要的数据，无需回表，大幅提升查询效率。

3. 更新表统计信息（可选） #

如果MySQL的表统计信息过时，可能导致优化器做出错误的执行计划选择，更新统计信息：

ANALYZE TABLE saveo_prod.cart_medicine_detail;

4. 强制使用索引（临时方案） #

如果上述操作后优化器仍不选择索引，可以临时强制使用索引（不推荐长期依赖，优先解决根本问题）：

SELECT 
  cart_medicine_detail._id AS _id, 
  cart_medicine_detail.cartId AS cartId, 
  cart_medicine_detail.medicineId AS medicineId, 
  cart_medicine_detail.distributorId AS distributorId, 
  cart_medicine_detail.quantity AS quantity, 
  cart_medicine_detail.name AS name, 
  cart_medicine_detail.itemCode AS itemCode, 
  cart_medicine_detail.dealType AS dealType, 
  cart_medicine_detail.reason AS reason, 
  cart_medicine_detail.isDeleted AS isDeleted, 
  cart_medicine_detail.actualPrice AS actualPrice, 
  cart_medicine_detail.source AS source, 
  from_unixtime(cart_medicine_detail.createdTimestamp / 1000) AS createdTimestamp, 
  from_unixtime(cart_medicine_detail.updatedTimestamp / 1000) AS updatedTimestamp, 
  cart_medicine_detail.medicineCategory AS medicineCategory, 
  cart_medicine_detail.medicineSubCategory AS medicineSubCategory 
FROM 
  saveo_prod.cart_medicine_detail FORCE INDEX (idx_createdTimestamp)
WHERE 
  cart_medicine_detail.createdTimestamp >= unix_timestamp(date_add(now(6), INTERVAL -7 day)) * 1000;

内容的提问来源于stack exchange，提问作者RRQ