肥宅之家  ⁝︎  登录
帖子读取算法修复与InnoDB索引填坑小记
之前发现了帖子列表页读取算法的严重问题:修改日期后文章在列表中没有显示。经排查发现为之前设计的日期大量重复时按tid索引排序,导致日期小于该贴但tid处于该日期之后的内容被过滤。
这个逻辑算法错误并没有带来任何安全隐患,但会严重影响内容的展现与排列,所以进行了修复。新算法改为先读取ID等于某时间的所有帖子,计算剩余读取帖数,再读取余下的帖子。
修复之后给我程序测试了一个百万级的数据库,结果让我十分蛋疼,读取一页要6秒。进行了一系列优化之后读取缩减到了0.001s,以下是一些填坑小记:
1. 进行分页操作时不要直接用 SELECT * ,而是先 SELECT `tid`,`time` 读出带索引的项,然后再用 SELECT * FROM `topic` WHERE `tid` IN (1,2,3) 来查询内容。这么做的原因是MySQL需要指定一次索引列才能缓存索引,你会发现 SELECT `tid`,`time` 一次之后再SELECT * 也秒读了。但是为了避免不必要的麻烦,还是建议每次先预读索引项后再按此读取内容。
2. 多项查询符 IN (1,2,3) 如果不是索引最后一项(末端),那么即便有索引也会遍历 ,大幅降低效率。建议先把每项都 SELECT ,然后 UNION ALL ,最后排序和限制数量。网上说的加不加 IN 效率差不多,100万数据实测试效率差距是0.6s和0.001s,还是挺大的,所以建议优化。
3. InnoDB 引擎 EXPLAIN 中 rows 是影响行数的预估值,不是必须遍历的总数,如果正确走索引并不会逐行检查。如果限定读取范围并且被限定项有索引,基本可以秒读。如果按普通分页 LIMIT 1000,10 这样读取会稍慢一些。
4. filesort 是输出结果后的排序,和索引关系不大。只不过在遍历时容易出现 filesort ,所以被人当作没走索引的标志。
以上测试都是在 MySQL8.0 环境下进行,足以证明 MySQL 经过这么多代的更迭后索引逻辑依然很笨。本测试和网上的测试都比较片面,各位不要相信一面之词,要根据自己所需环境测试得出结论。
最后庆祝本站系统顺利通过百万级数据测试,分页时间被严格顺利控制在了0.003秒内,性能远超Wordpress、Typecho、Zblog、Emlog、Disucz、Xiuno等程序。
C
应用中心 脑浆物语 霖博客 Jet 设计笔记 依萌萌 灰常记忆 乐亦人生 拔剑舞 云破天开 秋日 Yoooo 幻焕 月宅酱 空城 笨猫