在MySQL中，如果只想獲取select查詢結果的一部分，可以使用limit子句來限制返回記錄的數(shù)量，limit在獲取到滿足條件的數(shù)據(jù)量時即會立刻終止SQL的執(zhí)行。相比于返回所有數(shù)據(jù)然后丟棄一部分，執(zhí)行效率會更高。

一、limit子句用法示例

limit子句通常放在select查詢的最后，語法是limit [offset,] rowcount ：

• limit m,n 返回偏移量為m之后的n條數(shù)據(jù)，即先獲取m+n條記錄，然后丟棄前面的m條，返回之后的n條記錄
• limit n 返回開頭的n條數(shù)據(jù)，相當于limit 0, n

1.1 基本用法

新建一張測試表并填充幾條數(shù)據(jù)：

create table test(
id int auto_increment primary key,
name varchar(32),
salary decimal(10,2));

insert into test values(null, 'aaa',1000),(null, 'bbb',2000),(null, 'ccc',3000),(null, 'ddd',4000),(null, 'eee',5000),(null, 'fff',6000),(null, 'ggg',7000),(null, 'hhh',8000),(null, 'iii',9000);

limit 0會立刻返回一個空結果集，它通常用來檢測SQL語法是否正確或者快速獲取結果集的字段屬性。limit n用來返回最先獲取的n條記錄，找到足夠的記錄時SQL就會停止執(zhí)行并返回結果：

select * from test limit 3;

采用limit m,n的形式，就是跳過前面的m條記錄，返回之后的n條記錄：

select * from test limit 3,3;

如果只是想跳過開頭的m條記錄，只需要給n一個足夠大的數(shù)字即可，例如跳過開頭100條記錄：limit 100, 9999999999

1.2 limit和order by

如果order by子句和limit子句同時出現(xiàn)，那么MySQL會先對結果進行排序，對排序后的結果集應用limit子句。例如查詢工資最高的3個人（按salary列倒序排列后取前3條記錄）：

select * from test order by salary desc limit 3;

如果排序的列存在重復數(shù)據(jù)，例如本例返回3條數(shù)據(jù)，但是3,4,5條記錄的salary列都是相同的（它們都可以排在第三），這時返回的結果集是不確定的，查詢時需注意。

1.2.1 排序瓶頸優(yōu)化

與order by子句配合使用時，雖然limit子句最終獲取的結果集可能很小，但需要先對所有的數(shù)據(jù)進行排序，如果這個數(shù)據(jù)量很大，那么排序操作就會成為性能瓶頸。

如果你發(fā)現(xiàn)limit子句加上order by之后語句執(zhí)行很慢，可以嘗試通過在排序列上增加索引來消除這個排序操作。由于示例表很小，優(yōu)化器傾向于走全表掃描，這里找一張更大的表test1來演示，表中約有2萬多條數(shù)據(jù)。觀察添加索引前后的執(zhí)行計劃：

explain select * from test1 order by salary desc limit 3;
create index idx_salary on test1(salary);
explain select * from test1 order by salary desc limit 3;

可以看到索引反向掃描替代了原來的排序操作，同時掃描的行數(shù)量從24032降低到了3。

二、limit分頁優(yōu)化

limit子句最常用場景就是數(shù)據(jù)分頁，通過變更偏移量來對數(shù)據(jù)進行分頁展示。例如第一頁顯示100條數(shù)據(jù)，limit子句就是limit 0,100。第二頁是limit 100,100，第三頁是limit 200,100…. 但是當頁數(shù)非常大時，limit m,n 中被丟棄的m條數(shù)據(jù)可能成為性能瓶頸。

由于前m條數(shù)據(jù)（偏移量）是最終需要的丟棄的，它們的內容我們并不關心，因此優(yōu)化的思路就是"避免查詢前m條數(shù)據(jù)的內容"。

2.1 延遲關聯(lián)

為了避免查詢偏移量m條數(shù)據(jù)的內容，我們可以先通過索引獲取的n條數(shù)據(jù)的偏移量/主鍵（而不是對全量數(shù)據(jù)進行排序），然后通過主鍵直接獲取n條數(shù)據(jù)的內容。這種策略叫做"延遲關聯(lián)"。

例如查詢：

select * from test1 order by salary desc limit 10000,100;

通過延遲關聯(lián)可以改寫為：

select salary from test1
join ( select id from test1 order by salary desc limit 10000,100) d on d.id=test1.id;

如果salary列上有索引，那么獲取id是不需要回表的，通過索引就可以獲取n條數(shù)據(jù)的主鍵，隨后再與主表關聯(lián)，通過主鍵取出這n條數(shù)據(jù)內容。雖然SQL看起來稍微復雜了，但是它繞過了獲取前m條數(shù)據(jù)內容這個步驟，當m值比較大時，性能提升是很明顯的。

2.2 轉換為位置查詢

這種策略是根據(jù)排序條件預先計算每行記錄的順序編號并加上索引，例如在表中新增一列position（或者單獨新增一張順序表也可以），保存的是每一行位置順序。這相當于分頁排序已經(jīng)預先執(zhí)行了，而偏移操作就被轉換成了索引范圍掃描。

例如查詢：

select * from test1 order by salary desc limit 10000,100;

通過位置查詢可以改寫為：

select * from test1 where position between 10001 and 10100;

position列是根據(jù)order by salary desc條件預先維護好的每一列的順序編號，此后每次分頁查詢都不需要計算偏移量，而是被轉換成了索引范圍掃描（Index Range Scan）。

2.3 記錄偏移位置

記錄偏移位置的方法，就是當排序列存在順序的情況下，每次查詢后將其最后的值記錄下來，然后作為下一次SQL查詢的過濾條件。

假設首次查詢如下（id列單調遞增）：

select * from test1 order by id limit 9900,100;

假設上面查詢返回結果集的最大id為123456，程序可以將這個值單獨記錄下來，那么SQL：

select * from test1 order by id limit 10000,100;

就可以改寫為：

select * from test1 where id>123456 order by id limit 100;

通過條件where id>123456就可以過濾掉前m條數(shù)據(jù)，但這種方法的缺陷就是它只能一頁一頁的順序往后翻，不能跳轉翻頁，對比上面2種方法不夠靈活。

到此這篇關于MySQL limit子句用法及優(yōu)化小結的文章就介紹到這了,更多相關MySQL limit子句內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論