MySQL LIMIT 子句
MySQL LIMIT 子句是控制 SELECT 語句返回行數的重要工具。透過指定從結果集中獲取的最大行數，它可以讓你處理資料子集，尤其是在涉及大表的情況下。該功能可提高查詢效能，並透過只獲取必要的行來最佳化資源使用。
MySQL LIMIT 子句的語法
MySQL 中的 LIMIT 子句接受一個或兩個引數：偏移量和計數。這兩個引數都應該是非負整數。
·偏移量參數列示要從結果集中返回的第一條記錄的位置，決定了在返回記錄之前要跳過的記錄數。
·計數引數指定要從結果集中檢索的最大行數，為要返回的行數設定限制。
使用兩個引數時，第一個引數代表偏移量，第二個引數代表計數。這樣就可以從結果集中檢索特定範圍的記錄。但如果只使用一個引數，則表示從結果集開始返回的記錄數。
LIMIT 子句的基本語法如下：
SELECT column1, column2, ... FROM table_name LIMIT offset, count;
如何在查詢中使用 ORDER BY 和 LIMIT 子句
在對大資料集進行排序的互動式應用程式中，MySQL ORDER BY 帶 LIMIT 是 ORDER BY 最常見的用法。在許多網站上，你會發現熱門標籤、最近註冊的使用者等，這通常需要在後端使用帶 LIMIT 的 ORDER BY。一般來說，這種 ORDER BY 型別看起來像 SELECT ..... WHERE [conditions] ORDER BY [sort] LIMIT N, M。
確保使用索引。在執行帶 LIMIT 的 ORDER BY 時，不對整個結果集進行掃描和排序是非常重要的，因此必須使用索引--在這種情況下，將啟動索引範圍掃描，並在產生所需數量的記錄後立即停止查詢執行。
MySQL LIMIT 子句示例
下面我們來看幾個如何使用 MySQL LIMIT 子句檢索特定結果的示例，包括建立日期、類別 ID 和在多列上執行查詢。
使用 MySQL LIMIT 10 按 "date_created"和 "category_id"查詢結果集
例如，如果我執行 SELECT * FROM sites ORDER BY date_created DESC LIMIT 10，我將使用 (date_created) 上的索引快速獲得結果集。
現在，如果我使用類似 SELECT * FROM sites WHERE category_id=5 ORDER BY date_created DESC LIMIT 10 的方法；
在這種情況下，透過 date_created 索引也可以工作，但它可能不是最有效的--如果是一個罕見的類別，可能要掃描表的大部分內容才能找到 10 條記錄。因此，以 (category_id, date_created) 為索引會更好。
讓我們來看看更復雜的情況： SELECT * FROM sites WHERE category_id in (5,10,12) ORDER BY date_created DESC LIMIT 10；
儘管看起來與前一個案例非常相似，但由於列表中有多個 category_id 值，因此不能直接使用（category_id、date_created）上的索引，這一點有很大不同。單獨使用 date_created 索引仍然有效。從效能角度來看，使用 UNION 解決方法是不錯的（儘管有點難看）。
在多列查詢中使用 MySQL LIMIT
如果應用可以在許多不同列上執行搜尋，但選擇性卻不盡如人意，該怎麼辦？各種社交網路和交友網站就是此類查詢的完美示例。
SELECT FROM people where gender='m' and age between 18 and 28 and country_id=5 and city_id=345 order by last_online desc limit 10；
限制因素可能有很多，而且都是可選的。這是一個很難解決的問題，可以開發高階的定製搜尋解決方案，但如果我們堅持使用簡單的 MySQL，在大多數選擇性列上使用多個索引將是提高此類查詢效能的一個好主意。
例如，可以在(gender,last_online)和(country_id,city_id,last_online)上建立索引，前者假定大多數人會指定性別，後者假定大多數情況下會指定這些。在這種情況下，需要對實際執行的查詢和資料選擇性進行仔細研究，才能得出一套好的索引，將來也可能需要進行調整。
如果沒有透過索引解析的完整 WHERE 子句，則需要注意的主要問題是需要掃描多少行才能解析 order by（這可以在慢查詢日誌中或透過檢查 Hander 統計資料找到）。如果只需檢查 50 條記錄就能提供 10 行結果集，那麼情況還算不錯。但如果是 5000 行，則可能需要重新考慮索引。
此外，請注意：根據特定的常量和其他因素，為提供結果集而掃描的記錄數量將是動態的。
例如，在我們的示例中，如果我們只使用 (last_online) 索引並查詢來自美國的人，那麼如果這個國家很小，或者來自這個國家的成員很少（例如斯洛維尼亞），我們可能很快就能找到十個人。
在上面的示例中，我們按最後一列排序。事實上，如果按前導列排序，索引也可用於 ORDER BY。但請注意，用於按列排序的列之後的列不能用於限制結果集。例如
key(a,b,c) SELECT * FROM tbl WHERE c=5 ORDER BY a,b limit 10 - 在這種情況下，可以使用索引中的前兩列來滿足order by，但索引不會幫助檢查 c=5（除非是索引覆蓋的查詢）。在 (c,a,b) 上建立索引對上述查詢更有效。
使用MySQL limit子句的最佳實踐
不要按表示式排序
我想這是顯而易見的--表示式或函式會阻止索引在 "按......排序 "時的使用。
按驅動表中的列排序
如果使用帶 ORDER BY ... LIMIT 的 JOIN，則應儘量使排序列位於驅動表中。有時，這意味著要打破規範化，並在其他表中重複 ORDER BY 要使用的列。
下面是一個 ORDER BY 由第二個表完成的示例，它需要檔案排序：
mysql> explain select test.i from test, test t where test.k=5 and test.i=t.k order by t.k limit 5; +----+-------------+-------+------+---------------+------+---------+-------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+---------------------------------+ | 1 | SIMPLE | test | ref | PRIMARY,k | k | 4 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+---------------------------------+ 2 rows in set (0.00 sec)
但是，如果第一張表的訪問型別是 "const"或 "system"，那麼它就會從連線執行中有效地移除（替換為常量），這樣，即使 ORDER BY 由第二張表完成，也可以對其進行最佳化：
mysql> explain select test.i from test, test t where test.i=5 and test.k=t.k order by t.k limit 5; +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | test | const | PRIMARY,k | PRIMARY | 4 | const | 1 | | | 1 | SIMPLE | t | ref | k | k | 4 | const | 1 | Using index | +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+ 2 rows in set (0.01 sec)
這裡的區別是：“i”是主鍵，而“k”只是索引列。
注意：在某些情況下，即使可以使用索引來執行帶 JOIN 的 ORDER BY，MySQL 仍然無法使用它，因為最佳化器還不夠智慧，無法檢測到這種情況：
mysql> explain select test.i from test, test t where test.k=5 and test.i=t.k order by test.k,t.j limit 5; +----+-------------+-------+------+---------------+------+---------+-------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+-------------+------+---------------------------------+ | 1 | SIMPLE | test | ref | PRIMARY,k | k | 4 | const | 1 | Using temporary; Using filesort | | 1 | SIMPLE | t | ref | k | k | 4 | test.test.i | 1 | Using where; Using index | +----+-------------+-------+------+---------------+------+---------+-------------+------+---------------------------------+ 2 rows in set (0.00 sec)
在這種情況下，表上有索引 (k,j)，因此可以在每個表上使用索引來最佳化排序，或者至少可以對第二個表的每個 t.k=const 值使用區域性排序。但這並沒有做到。
單個方向排序
如果使用 ORDER BY col1, col2，可以使用索引進行最佳化。如果使用 ORDER BY col1 DESC、col2 DESC，情況也一樣，但如果使用 ORDER BY col1、col2 DESC，MySQL 就必須使用檔案排序。經典的解決方案是建立適當排序的索引（按 col1 升序，按 col2 降序），但 MySQL 目前還做不到。目前可以使用的變通方法是單獨列儲存反向值，這樣就可以用 ORDER BY col1, col2_reverse 代替。
小心大的 LIMIT
如果需要對前幾條記錄進行排序，那麼使用索引排序的效率會很高，即使需要進行一些額外的過濾，因此透過索引掃描的記錄也會比 LIMIT 要求的多。但是，如果處理的 LIMIT 查詢偏移量較大，效率就會受到影響。LIMIT 1000,10 可能比 LIMIT 0,10 慢得多。誠然，大多數使用者不會瀏覽超過 10 頁的結果。但是，搜尋引擎機器人很可能會這樣做。在我的專案中，我就看到過機器人在檢視 200 多頁。此外，許多網站都沒有注意到這一點，這就為發起 DOS 攻擊提供了一個非常簡單的任務--從很少的連線中獲取大量的請求，這就足夠了。如果你不做其他任何事情，請確保你阻止了頁碼過大的請求。
在某些情況下，例如，如果結果是靜態的，可能需要預先計算結果，以便查詢它們的位置。因此，您可以用 WHERE 位置在 1000 和 1009 之間來代替 LIMIT 1000,10 查詢，這樣對任何位置（只要有索引）都有相同的效率。
必要時強制索引
在某些情況下，MySQL 最佳化器可能更傾向於使用不同的索引，它具有更好的選擇性或只是更好的估計，而不是支援你進行排序的索引。例如，在查詢 SELECT * FROM people WHERE country_id=5 and city_id=6 order by last_online desc limit 10 時，如果在（country_id,city_id）上有索引，在（country_id,last_online）上也有索引，那麼即使會導致檔案排序，也可能會選擇第一個索引。
要解決這個問題，要麼擴充套件你的索引，這樣 MySQL 最佳化器就不必在更好的排序和更好的查詢之間做出選擇，要麼使用 FORCE INDEX 強制它使用適當的索引。
對於 "SELECT ... WHERE [conditions] ORDER BY [sort] LIMIT N "這樣的查詢，最佳化器可能會選擇索引來解決 ORDER BY，而不是在 WHERE 子句中的列上使用索引。在 MySQL 5.7 中修復了一個bug。
bug：https://bugs.mysql.com/bug.php?id=73837
對於將 ORDER BY 與 LIMIT 結合在一起的查詢，最佳化器可能會切換到適用於 ORDER BY 的索引。在某些情況下，切換的決定基於啟發式而非成本。現在，最佳化器會統一根據成本來決定是否切換。當切換會導致查詢讀取整個索引或索引的大部分來查詢符合條件的行時，這將會帶來更好的效能。
使用降序索引
MySQL 8.0 引入了降序索引，按降序儲存索引的鍵值。降序索引可以按正向順序掃描，這樣效率更高。如果查詢混合了 ASC 和 DESC，那麼如果列上的索引也使用了相應的升序和降序混合列，最佳化器就可以使用該索引：
SELECT * FROM test ORDER BY k DESC, j ASC；
如果 k 是降序列，j 是升序列，最佳化器可以在 (k, j) 上使用索引。如果 k 是升序，j 是降序，最佳化器還可以在這些列上使用索引（使用後向掃描）。
如果多條記錄的 ORDER BY 列中的值相同，伺服器可按任意順序返回這些記錄。如果必須確保有 LIMIT 和無 LIMIT 時的記錄順序相同，可在 ORDER BY 子句中包含額外的列，使順序具有確定性。