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的o?set=(5001 - 1) * 20，20是每頁的數量， 此時起始o?set就是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次回表查詢，所以二級索引查找方式反而不適合，而按照聚簇索引順序掃描的方式更好。

不同場景，要具體情況具體分析，到底慢在哪兒，再針對性優化。

SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。