MySQL文章投票表上下票统计结果异常及sum查询性能低下优化咨询

结果异常排查 #

你给出的测试数据执行你写的sum聚合语句是可以得到预期结果的（文章2得票1排第一，文章1得票0排第二），如果你的实际运行结果不符合预期，优先做以下校验：

• 排查脏数据：执行SELECT * FROM articlevote WHERE vote NOT IN (1,-1);检查是否存在不符合规则的vote值，如果有可以在查询时加过滤条件：

SELECT v.articleid, sum(v.vote) as votes 
FROM articlevote v 
WHERE v.vote IN (1,-1)
GROUP BY v.articleid 
ORDER BY votes DESC;

• 校验单篇文章的投票数据：执行SELECT vote FROM articlevote WHERE articleid = 1;确认文章1的投票记录是否符合你的预期。

性能优化方案 #

你遇到的sum查询性能低于count查询是正常现象：原count查询有vote>0的过滤条件，扫描的数据量更小，而sum查询需要扫描全表的所有投票记录做计算，开销自然更大。可以通过以下方案优化性能：

方案1：添加覆盖索引（改动最小，适配中小数据量） #

创建覆盖索引，让查询可以直接走索引完成聚合，不需要回表查询主键数据，性能可以提升数倍：

CREATE INDEX `idx_articleid_vote` ON `articlevote` (`articleid`, `vote`);

索引创建完成后再执行你的sum查询即可，MySQL优化器会直接走这个覆盖索引完成计算，不需要扫全表。

方案2：预聚合统计表（适配大数据量+高并发场景） #

如果投票数据量超过百万级，每次实时聚合开销还是很高，可以新增一张文章投票统计表做预计算：

CREATE TABLE `article_vote_stat` (
  `articleid` int NOT NULL PRIMARY KEY,
  `up_count` int NOT NULL DEFAULT 0 COMMENT '点赞数',
  `down_count` int NOT NULL DEFAULT 0 COMMENT '点踩数',
  `hot_score` int NOT NULL DEFAULT 0 COMMENT '热度分=点赞数-点踩数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

每次用户新增/修改投票时，原子更新这张统计表的对应字段，后续查热度列表直接查这张表即可：

SELECT articleid, hot_score as votes 
FROM article_vote_stat 
ORDER BY hot_score DESC;

这个方案查询性能接近O(1)，是生产环境高并发场景的通用解决方案。

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