慢查询优化求助：如何确保SQL查询正确使用索引？

咱们先从你的原始查询开始拆解，先把SQL贴出来：

SELECT posts.* FROM posts 
INNER JOIN categories ON posts.category_id = categories.id 
AND categories.main = 1 
AND(categories.private_category = 0 OR categories.private_category IS NULL) 
WHERE posts.id NOT IN('') 
AND posts.deleted = 0 
AND posts.hidden = 0 
AND posts.total_points >= - 5 
ORDER BY posts.id DESC LIMIT 10;

先看你两次EXPLAIN暴露的问题 #

第一次EXPLAIN结果

第二次添加index_categories_on_main后的EXPLAIN结果

核心问题拆解 #

• 无用条件干扰优化器
  你SQL里的posts.id NOT IN('')完全是多余的：posts.id是int(10) unsigned类型，和空字符串''比较永远返回false，这个条件等于没加，还会让优化器多做一次无效判断，建议直接删掉。

• categories表单索引收益极低
  你给main加了单索引，但categories表总共只有12行数据，MySQL优化器会认为全表扫描比走索引更高效——因为索引需要额外的IO去定位数据再回表，对于极小表来说，全表扫的成本更低，所以优化器压根不会选这个单索引。

• posts表才是性能瓶颈
  两次EXPLAIN里，posts表都要扫描37516行数据，这才是查询慢的核心原因。当前的索引没办法同时覆盖deleted=0、hidden=0、total_points >= -5这三个过滤条件，还要关联category_id，最后按id排序，导致需要大量的行过滤和排序操作。

具体优化方案 #

方案1：先过滤categories，缩小posts的扫描范围

先提前找出符合条件的分类ID，再关联posts，这样可以减少posts表的扫描行数：

-- 先获取符合条件的category id
SELECT id FROM categories 
WHERE main = 1 
AND (private_category = 0 OR private_category IS NULL);

-- 再关联posts（可以写成子查询或JOIN）
SELECT posts.* FROM posts 
WHERE posts.category_id IN (SELECT id FROM categories WHERE main = 1 AND (private_category = 0 OR private_category IS NULL))
AND posts.deleted = 0 
AND posts.hidden = 0 
AND posts.total_points >= -5 
ORDER BY posts.id DESC LIMIT 10;

方案2：创建针对性的联合索引

• 给categories表创建覆盖过滤条件的联合索引：
  因为过滤条件是main=1 + private_category的判断，创建联合索引(main, private_category, id)，这样可以直接通过索引拿到符合条件的category id，不需要回表查询全表数据。

  CREATE INDEX idx_categories_main_private_id ON categories(main, private_category, id);
  

• 给posts表创建覆盖过滤+排序的联合索引：
  我们需要同时覆盖deleted、hidden、category_id、total_points这几个过滤条件，还要支持按id排序，创建联合索引(deleted, hidden, category_id, total_points, id)。这个索引可以让MySQL直接通过索引过滤出符合条件的行，并且直接按id排序，不需要额外的filesort，也避免回表（因为最后包含了id，而主键索引可以直接定位到全量数据）。

  CREATE INDEX idx_posts_deleted_hidden_category_points_id ON posts(deleted, hidden, category_id, total_points, id);
  

方案3：调整JOIN顺序（可选）

你可以强制优化器先扫描categories表再关联posts，比如用STRAIGHT_JOIN：

SELECT posts.* FROM categories 
STRAIGHT_JOIN posts ON posts.category_id = categories.id 
WHERE categories.main = 1 
AND (categories.private_category = 0 OR categories.private_category IS NULL)
AND posts.deleted = 0 
AND posts.hidden = 0 
AND posts.total_points >= -5 
ORDER BY posts.id DESC LIMIT 10;

不过这个方案的优先级低于前面两个，因为核心还是索引优化。

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