MySQL查询未走索引全表扫描，百万数据查询耗时3.5秒求优化

我有一条MySQL查询语句，原本预期性能更优，但在包含100万条记录的表上执行耗时3.5秒。

查询语句 #

set @numberofdayssinceexpiration = 1; 
set @today  = DATE(now());
set @start_position =  (@pagenumber-1)* @pagesize;

SELECT *
FROM   (SELECT ad.id,
               title,
               description,
               startson,
               expireson,
               ad.appuserid UserId,
               user.email   UserName,
               ExpiredCount.totalcount
        FROM   advertisement ad
               LEFT JOIN (SELECT servicetypeid,
                                  Count(*) AS TotalCount
                           FROM   advertisement
                           WHERE  Datediff(@today,expireson) =
                                  @numberofdayssinceexpiration
                                  AND sendreminderafterexpiration = 1
                           GROUP  BY servicetypeid) AS ExpiredCount
                       ON ExpiredCount.servicetypeid = ad.servicetypeid
               LEFT JOIN aspnetusers user
                       ON user.id = ad.appuserid
        WHERE  Datediff(@today,expireson) = @numberofdayssinceexpiration
               AND sendreminderafterexpiration = 1
        ORDER  BY ad.id) AS expiredAds
LIMIT 20 offset 1;

执行计划 #

已定义的索引 #

我想知道问题出在哪里，恳请各位提供帮助。

问题根源与优化方案 #

• 列上的函数调用导致索引失效：Datediff(@today, expireson)这种写法让expireson字段的索引无法被使用，MySQL不得不对advertisement表做全表扫描。把条件改写为expireson = DATE_SUB(@today, INTERVAL @numberofdayssinceexpiration DAY)，这样就能利用expireson的索引快速过滤数据。
• 重复扫描同一张表：主查询和子查询ExpiredCount都执行了相同条件的过滤，相当于两次全表扫描。改用窗口函数COUNT(*) OVER (PARTITION BY servicetypeid)可以在一次扫描中同时获取每条记录的分组统计数，避免重复扫描。优化后的查询示例：

  set @numberofdayssinceexpiration = 1; 
  set @today  = DATE(now());
  set @start_position =  (@pagenumber-1)* @pagesize;
  
  SELECT id,
         title,
         description,
         startson,
         expireson,
         appuserid UserId,
         user.email UserName,
         TotalCount
  FROM   (SELECT ad.id,
                 title,
                 description,
                 startson,
                 expireson,
                 ad.appuserid,
                 COUNT(*) OVER (PARTITION BY ad.servicetypeid) AS TotalCount
          FROM   advertisement ad
          WHERE  expireson = DATE_SUB(@today, INTERVAL @numberofdayssinceexpiration DAY)
                 AND sendreminderafterexpiration = 1) AS expiredAds
  LEFT JOIN aspnetusers user ON user.id = expiredAds.appuserid
  ORDER  BY id
  LIMIT 20 offset 1;
  

• 缺失高效的复合索引：当前索引没有覆盖查询的过滤条件、排序字段以及关联字段。建议创建复合索引：CREATE INDEX idx_ad_exp_send_id_st ON advertisement(expireson, sendreminderafterexpiration, id, servicetypeid, appuserid);，这个索引可以覆盖过滤、排序、分组所需的字段，实现索引覆盖查询，无需回表读取数据。
• OFFSET分页的潜在问题：虽然当前OFFSET很小，但如果后续分页偏移量增大，OFFSET会导致MySQL先扫描大量无关数据再丢弃。可以改用主键连续分页：比如记录上一页的最大id，下次查询用WHERE id > :last_id配合LIMIT 20，效率会更高。

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