MySQL分頁查詢慢的原因及解決方案
一、背景
我們在開發的過程中使用分頁是不可避免的，通常情況下我們的做法是使用limit加偏移量：select * from table where column=xxx order by xxx limit 1,20。當資料量比較小時（100萬以內），無論你翻到哪一頁，效能都是很快的。如果查詢慢，只要在where條件和order by 的列上加上索引就可以解決。但是，當資料量大的時候（小編遇到的情況是500萬資料），如果翻到最後幾頁，即使加了索引，查詢也是非常慢的，這是什麼原因導致的呢？我們該如何解決呢？
二、limit分頁原理
當我們翻到最後幾頁時，查詢的sql通常是：select * from table where column=xxx order by xxx limit 1000000,20。查詢非常慢。但是我們檢視前幾頁的時候，速度並不慢。這是因為limit的偏移量太大導致的。MySql使用limit時的原理是（用上面的例子舉例）：
SQL/">MySQL將查詢出1000020條記錄。
然後舍掉前面的1000000條記錄。
返回剩下的20條記錄。
上述的過程是在《高效能MySQL》書中確認的。
三、解決方案
解決的方法就是儘量使用索引覆蓋掃描，就是我們select後面檢出的是索引列，而不是所有的列，而且這個索引的列最好是id。然後再做一次關聯查詢返回所有的列。上述的sql可以寫成：
SELECT
*
FROM
table t
INNER JOIN (
SELECT
id
FROM
table
WHERE
xxx_id = 143381
LIMIT 800000,20
) t1 ON t.id = t1.id
我們在mysql中做的真實的實驗：
上圖是沒有優化過的sql，執行時間為2s多。經過優化後如下：
執行時間為0.3s，效能有了大幅度的提升。雖然做了優化，但是隨著偏移量的增加，效能也會隨著下降，MySql官方雖然也給出了
其他的解決方案，但是在實際開發中很難使用。
有的同學可能會問，能不能使用IN巢狀子查詢，而不使用INNER JOIN的方式，答案是不可以，因為MySql在子查詢中不能使用LIMIT。
MySQL分頁優化就先介紹到這裡了。
Linux公社的RSS地址 ： ofollow,noindex" target="_blank">https://www.linuxidc.com/rssFeed.aspx
本文永久更新連結地址： https://www.linuxidc.com/Linux/2018-11/155568.htm