10万条coupons数据下MySQL条件查询耗时6-7秒的性能优化求助

首先，你的查询慢的主要原因有两个：查询语句的类型不匹配导致索引失效，以及现有索引的列顺序不合理，无法高效过滤数据。下面一步步解决：

1. 修复查询语句的类型不匹配问题 #

你当前用DATE_FORMAT(NOW(),"%Y-%m-%d")生成日期字符串，和starts、ends（date类型）进行比较时，MySQL会对starts和ends做隐式类型转换，这会导致索引无法被正常使用，只能做全表扫描或者低效的索引扫描，这是性能慢的核心原因之一。

修改后的查询语句：

SELECT `id`, `code`, `description`, `minamt` 
FROM `coupons` 
WHERE `starts` <= CURDATE() 
  AND `ends` >= CURDATE() 
  AND active = 1 
  AND is_public = 1;

CURDATE()直接返回date类型的当前日期，和字段类型完全匹配，确保索引能被正确利用。

2. 优化索引结构 #

现有索引startEndDate(starts,ends,is_public,active)的列顺序不合理：MySQL在使用复合索引时，等值条件的列应该放在最前面，范围条件的列放在后面。因为一旦遇到范围查询（比如<=、>=），后面的索引列就无法被用于快速过滤了。

你的查询中，active=1和is_public=1是等值条件，starts<=CURDATE()和ends>=CURDATE()是范围条件，所以应该把等值条件列放在索引的最前面，再放范围条件列。

方案一：创建高效复合索引（优先推荐） #

删除现有低效索引，创建新的复合索引：

DROP INDEX startEndDate ON coupons;
CREATE INDEX idx_active_public_dates ON coupons (active, is_public, starts, ends);

这个索引的逻辑是：

• 先快速定位所有active=1且is_public=1的记录（等值匹配，索引效率最高）
• 再在这个子集里过滤starts<=当前日期的记录（范围匹配）
• 最后过滤ends>=当前日期的记录，完成数据筛选

方案二：创建覆盖索引（进一步优化，避免回表） #

如果你的查询频率很高，想要彻底避免回表操作（InnoDB通过二级索引找到主键后，再去主键索引取数据的过程），可以创建覆盖索引，把查询需要的字段也加入索引中。不过注意description是text类型，加入索引会让索引体积大幅增加，可能影响写入性能，所以需要权衡：

CREATE INDEX idx_active_public_dates_covering ON coupons (active, is_public, starts, ends, id, code, minamt);

因为id是主键，InnoDB的二级索引已经包含主键值，不过显式加入也没问题；description是text，不建议加入索引，所以查询时如果可以接受回表取description，就用方案一的索引即可，性能已经足够。

3. 验证优化效果 #

执行修改后的查询，用EXPLAIN查看执行计划：

EXPLAIN
SELECT `id`, `code`, `description`, `minamt` 
FROM `coupons` 
WHERE `starts` <= CURDATE() 
  AND `ends` >= CURDATE() 
  AND active = 1 
  AND is_public = 1;

如果执行计划的type列显示range或ref，key列显示我们创建的新索引，说明索引已经生效，查询耗时会大幅降低（通常能降到毫秒级）。

内容的提问来源于stack exchange，提问作者Er Sahaj Arora