相關文章
資料庫系列：MySQL慢查詢分析和效能最佳化
資料庫系列：MySQL索引最佳化總結（綜合版）
資料庫系列：高併發下的資料欄位變更
資料庫系列：覆蓋索引和規避回表
資料庫系列：資料庫高可用及無損擴容
資料庫系列：使用高區分度索引列提升效能
資料庫系列：字首索引和索引長度的取捨
資料庫系列：MySQL引擎MyISAM和InnoDB的比較
資料庫系列：InnoDB下實現高併發控制
資料庫系列：事務的4種隔離級別
資料庫系列：RR和RC下，快照讀的區別
資料庫系列：MySQL InnoDB鎖機制介紹
資料庫系列：MySQL不同操作分別用什麼鎖？
資料庫系列：業內主流MySQL資料中介軟體梳理
1 背景
前段時間面試新員工，跟候選人溝通起來分頁效能問題，正好之前遇到過這類問題，就拿出來再討論下！
2 分析
分頁效能問題，特別是在資料量大的情況下，是一個常見的問題。通常，當我們使用類似 LIMIT 和 OFFSET 的SQL語句進行分頁時，效能問題尤其明顯。這是因為隨著 OFFSET 的增加，資料庫需要跳過更多的行才能獲取到需要的資料，這導致了查詢時間的增加。
我們在檢視前幾頁的時候，發現速度非常快，比如 limit 200,25，瞬間就出來了。但是越往後，速度就越慢，特別是百萬條之後，卡到不行，那這個是什麼原理呢。先看一下我們翻頁翻到後面時，查詢的sql是怎樣的：
1 select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;
這種查詢的慢，其實是因為limit後面的偏移量太大導致的。比如像上面的 limit 2000000,25 ，這個等同於資料庫要掃描出 2000025 條資料，然後再丟棄前面的 20000000 條資料，返回剩下25條資料給使用者，這種取法明顯不合理。
在《高效能MySQL》第六章：查詢效能最佳化，對這個問題有過詳細說明：
分頁操作通常會使用limit加上偏移量的辦法實現，同時再加上合適的order by子句。但這會出現一個常見問題：當偏移量非常大的時候，它會導致MySQL掃描大量不需要的行然後再拋棄掉。
3 最佳化
以下是一些最佳化分頁效能的策略：
1. 使用索引+子查詢最佳化：
確保你的查詢涉及的列（尤其是用於排序和過濾的列）都被索引，沒有索引的列會導致資料庫進行全表掃描，這會大大降低查詢效能。
確保有索引之後，可以在索引樹中找到開始位置的 id值，再根據找到的id值查詢行資料。
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影響的行: 0
時間: 0.106s
2. 使用更有效的分頁技術：
考慮使用基於遊標或鍵的分頁而不是基於 OFFSET 的分頁。例如，如果你正在根據時間戳或ID排序，你可以記住上一頁最後一個條目的時間戳或ID，並從那裡開始下一頁的查詢。
記住上次查詢結果的主鍵位置，避免使用偏移量 offset
[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影響的行: 0
時間: 0.001s
[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影響的行: 0
時間: 0.000s
3. 減少返回的資料量：
只選擇需要的列，而不是使用 SELECT * , 減少資料量可以顯著提高查詢速度。
這個好理解，獲取資料，越精簡越好，千萬別都fetch回來，MySQL准入規範也是這麼定的。
4. 分割槽表：
對於非常大的表，考慮使用分割槽技術。透過將資料分佈到不同的分割槽，可以提高查詢效能，因為查詢可以在更小的資料集上操作。
5. 使用快取：
對於經常訪問的頁面，可以考慮使用快取技術，如Redis或Memcached，來儲存查詢結果。這樣，對於相同的查詢請求，可以直接從快取中獲取結果，而不是每次都查詢資料庫。
6. 考慮物理設計
資料庫的物理設計，如硬碟的速度和型別（SSD vs HDD），伺服器的記憶體大小，也會影響查詢效能。
4 總結
透過實施上述策略，你可以顯著提高資料庫分頁的效能，尤其是在處理大量資料時，每種方法都有其適用場景，因此我們需要根據具體需求和資料庫環境來選擇合適的最佳化策略。