MySQL深分页调优实战

商品评论系统数据量为十亿量级，对评论数据库做分库分表后，单表的评论数据在百万级。每个商品的所有评论都放在一个库的一张表，确保用户在分页查询一个商品的评论时，直接从一个库的一张表里执行分页查询语句即可。

热点商品销量多达100w，商品评论可能多达10w万条，而有些用户就爱看商品评论，他就不停对某热点商品评论不断进行分页，一页一页翻，有时候还会用上分页跳转功能，就是直接输入自己要跳到第几页。

这就涉及针对一个商品几十万评论的深分页问题。

简化后的对评论表进行分页查询的SQL：

SELECT * 
FROM comments 
WHERE product_id ='xx' 
and is_good_comment='1' 
ORDER BY id desc 
LIMIT 100000,20

比如用户选择了查看某个商品的评论，因此必须限定Product_id，同时还选了只看好评，所以is_good_commit也要限定，

接着看第5001页评论，则limit的offset=(5001 - 1) * 20，20是每页的数量， 此时起始offset就是100000，所以limit后100000,20。

评论表最核心的索引index_product_id，所以正常肯定走这索引：

• 根据product_id ='xx’条件从表里先筛选出表里指定商品的评论数据

• 然后按照 is_good_comment=‘1’，筛选出该商品评论数据里的所有好评了！

  但index_product_id的索引里，并没有is_good_commet字段值，所以此时只能回表。即对该商品的每条评论，都要进行一次回表，根据id找到那条数据，取出is_good_comment字段值，接着对is_good_comment='1’条件做比对，筛选符合条件的数据。假设商品的评论有几十万条，岂不是要几十万次回表？虽然每次回表都是根据id在聚簇索引快速查找，但撑不住你每条数据都回表。

• 接着对筛选完毕的所有满足**WHERE product_id =‘xx’ and is_good_comment=‘1’**的数据，假设有十万条，就按id做倒序排序，此时还得基于临时磁盘文件进行倒序排序，又耗时很久

• 排序完毕，基于limit 100000,20获取第5001页的20条数据

• 最后返回

该过程有几十万次回表查询，还有十多万条数据的磁盘文件排序，所以要跑个1~2s。如何优化呢？

• 之前的案例基于商品品类去查商品表，是尽量避免对聚簇索引进行扫描，因为有可能找不到你指定的品类商品而出现聚簇索引全表扫描问题，所以强制使用联合索引，快速定位到数据，这过程中因无需回表，所以效率较高
• 还有案例直接根据id临时磁盘文件排序后找到20条分页数据，再回表查询20次，找到20条商品的完整数据。因此当时不涉及大量回表，所以这么做基本合适，性能通常1s内。

但本案例不是这样，因为

WHERE product_id ='xx' and is_good_comment='1'

这俩条件不是一个联合索引，所以会出现大量回表，耗时严重。

因此对该案例，一般采取如下方式改造分页查询语句：

SELECT *
from comments a,
     (
         SELECT id 
         FROM comments 
         WHERE product_id = 'xx' 
           and is_good_comment = '1' 
         ORDER BY id desc 
         LIMIT 100000,20) b
WHERE a.id = b.id

该SQL的执行计划就会彻底改变其执行方式。

通常先执行括号里的子查询，子查询反而会使用PRIMARY聚簇索引，按聚簇索引id值的倒序方向进行扫描，扫描过程中就把符合

WHERE product_id ='xx' and is_good_comment='1'

的数据筛选出来。

比如这里筛选出10w条数据，并不需要把符合条件的数据都找到，因为limit 100000,20，理论上，只要有100000+20条符合条件的数据，且按id有序的，此时就能执行根据limit 100000,20提取到5001页的这20条数据。

接着你会看到执行计划里会针对这个子查询的结果集，一个临时表，进行全表扫描，拿到20条数据，再对20条数据遍历，每条数据都按id去聚簇索引查找一下完整数据。

所以本案例，反而是优化成这种方式来执行分页，更合适，他只有一个扫描【聚簇索引】筛选符合你分页所有数据的成本：

• 分页越深，扫描数据越多
• 分页越浅，扫描数据就越少

然后再做一页20条数据的20次回表查询即可。当时做了该分页优化后，发现分页语句一下子执行时间降低到了几百ms，达到优化目的。

SQL调优没有银弹：

• 比如第二个案例，按顺序扫描聚簇索引方案可能会因为找不到数据导致亿级数据量的全表扫描，所以对第二个案例而 言，必须得根据二级索引去查找

• 但这第三个案例，因为前提是做了分库分表，评论表单表数据一般在百万左右，所以首先，他即使一个商品没有评论，有全表扫描，也绝对不会像扫描上亿数据表那么慢

  其次，如果你根据product_id二级索引查找，反而可能出现几十w次回表查询，所以二级索引查找方式反而不适合，而按照聚簇索引顺序扫描的方式更好。

不同场景，要具体情况具体分析，到底慢在哪儿，再针对性优化。