20万+记录following表慢查询优化求助

看起来你在处理一个典型的社交关注流查询性能问题——20万+的following记录不算特别庞大，但索引设计和查询写法的不合理，确实会让核心过滤逻辑拖慢整个查询。我从索引优化、查询改写、表结构调整三个维度给你针对性的方案：

一、优先修复索引（最直接的性能提升） #

1. 给following表创建高效的覆盖索引 #

你的子查询select following from following where user =? and block=0是性能瓶颈。原表的唯一键(user, following)虽然能定位user，但过滤block=0时需要回表检查，而且无法直接返回following字段。建议创建覆盖索引：

-- MySQL 8.0+ 支持INCLUDE，只把需要返回的字段附加在索引后，不参与排序
CREATE INDEX idx_following_user_block ON following (user, block) INCLUDE (following);

-- 如果是MySQL 5.7及更早版本，把following加入索引列
CREATE INDEX idx_following_user_block_following ON following (user, block, following);

这个索引能让数据库直接从索引里拿到user=?且block=0对应的following值，完全不需要回表查询主键，子查询速度会大幅提升。

2. 给posts表创建适配查询的复合索引 #

原posts表的索引无法有效支持p.user IN (...) AND p.delete='0'的过滤，以及后续的ORDER BY p.id DESC。建议创建：

CREATE INDEX idx_posts_user_delete_id ON posts (`user`, `delete`, `id`);

这个索引可以快速筛选出指定用户的未删除帖子，并且已经按id降序排列，省去了额外的排序开销。如果你的MySQL版本支持覆盖索引，可以把查询中常用的posts字段也加入INCLUDE，进一步减少回表：

CREATE INDEX idx_posts_user_delete_id ON posts (`user`, `delete`, `id`) 
INCLUDE (data, titulo, youtube, foto);

二、改写查询语句，避免OR导致的索引失效 #

原查询中的(p.user in (...) or p.user=?)是另一个性能杀手——OR条件经常会让数据库放弃使用索引，转而做全表扫描。可以把查询拆成用户自己的帖子和关注用户的帖子两部分，用UNION ALL合并：

-- 第一部分：当前用户自己的帖子
SELECT 
    c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto,
    COUNT(DISTINCT likes.user) as likes_count,
    COUNT(DISTINCT comentarios.id) as comentarios_count,
    COUNT(DISTINCT l2.user) as count2
FROM posts p 
JOIN cadastro c ON p.user=c.id 
LEFT JOIN profile_picture pp ON p.user = pp.user 
LEFT JOIN likes ON likes.post = p.id 
LEFT JOIN comentarios ON comentarios.foto = p.id AND comentarios.delete = 0 
LEFT JOIN likes l2 ON l2.post = p.id AND l2.user = ? 
WHERE p.user = ? AND p.delete='0' 
GROUP BY p.id 

UNION ALL

-- 第二部分：用户关注的人的帖子
SELECT 
    c.nome, p.foto, c.user, p.user, p.id, p.data, p.titulo, p.youtube, pp.foto,
    COUNT(DISTINCT likes.user) as likes_count,
    COUNT(DISTINCT comentarios.id) as comentarios_count,
    COUNT(DISTINCT l2.user) as count2
FROM posts p 
JOIN cadastro c ON p.user=c.id 
LEFT JOIN profile_picture pp ON p.user = pp.user 
LEFT JOIN likes ON likes.post = p.id 
LEFT JOIN comentarios ON comentarios.foto = p.id AND comentarios.delete = 0 
LEFT JOIN likes l2 ON l2.post = p.id AND l2.user = ? 
WHERE p.user IN (SELECT following FROM following WHERE user =? AND block=0) AND p.delete='0' 
GROUP BY p.id 

-- 合并后统一排序分页
ORDER BY id DESC LIMIT ?;

拆分后，两部分查询都能高效利用我们刚才创建的idx_posts_user_delete_id索引，性能会比原查询好很多。

三、其他可选优化（进一步提升长期性能） #

1. 统一delete字段的数据类型 #

注意到posts表的delete是字符串类型（p.delete='0'），而following表的block是tinyint(1)。建议把posts.delete改成tinyint(1)，避免字符串类型转换的开销，同时节省存储空间。

2. 预计算统计值，避免实时聚合 #

如果likes_count和comentarios_count的实时性要求不是极高，可以在posts表新增两个字段likes_count和comentarios_count，每次用户点赞/评论时更新这两个字段。这样查询时就不需要做COUNT(DISTINCT)的昂贵聚合操作，直接读取字段值即可，查询速度会有质的提升。

3. 优化大偏移量分页 #

如果你的分页偏移量很大（比如LIMIT 10000, 20），可以利用主键排序的特性，改成基于上一页最后一个id的查询，避免数据库扫描大量无关数据：

-- 假设上一页最后一条帖子的id是last_id
WHERE p.id < last_id AND (...) ORDER BY p.id DESC LIMIT 20;

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