20万记录表SQL查询耗时30秒 索引未命中慢查询问题排查求助

• 驱动表选择错误，连接逻辑代价过高
  EXPLAIN结果显示MySQL优先选择仅8条记录的user_table（别名c）作为驱动表执行全表扫描，每条用户记录匹配时需要扫描15000+条post_table记录，累计扫描量级超过12万行，后续还要关联标签表、分组、排序，执行代价指数级上升。
  另外你写的LEFT JOIN user_table后增加了c.stat='y'的非空过滤条件，实际上已经等价于内连接，LEFT JOIN语义失效，也会干扰优化器判断。
• 索引不符合最左前缀匹配原则，无法完成前置过滤
  post_table建立的联合索引index( userID, stat, mainID, title )第一个字段为userID，你的查询中没有给定userID的固定筛选值，无法利用该索引前置过滤stat=1、mainID=0的条件，只能在关联时使用该索引，导致大量无效行被扫描。
  user_table没有建立userID开头的联合索引，关联时无法直接通过索引拿到stat、user_name字段，需要回表查询。
• 临时表、文件排序开销过大
  当前执行逻辑需要先关联完所有表的数据后，再进行group by和order by操作，数据量过大时触发Using temporary（创建临时表存储中间结果）和Using filesort（磁盘排序），这两部分开销占总耗时的90%以上。

• 调整索引配置

1. 为post_table新建覆盖筛选、排序、关联的联合索引：

ALTER TABLE post_table ADD INDEX idx_stat_mainid_pid (stat, mainID, pid DESC, userID);

2. 为user_table新建覆盖关联、过滤、查询的联合索引：

ALTER TABLE user_table ADD INDEX idx_userid_stat (userID, stat, user_name);

• 改写SQL减少扫描量级
  优先分页过滤出需要的20条帖子数据，再关联用户和标签表，避免全量数据关联后再分页丢弃大部分数据：

SELECT p.pid, p.other_fields, c.user_name, GROUP_CONCAT(t.tag) AS tags
FROM (
    SELECT pid, other_fields, userID 
    FROM post_table 
    WHERE stat=1 AND mainID=0 
    ORDER BY pid DESC LIMIT 0,20
) AS p
JOIN user_table AS c ON p.userID = c.userID AND c.stat='y'
LEFT JOIN tag_table AS t ON p.pid = t.pid
GROUP BY p.pid
ORDER BY p.pid DESC

如果优化器依然选错驱动表，可以使用STRAIGHT_JOIN强制指定post_table为驱动表。

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