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

一、limit子句用法示例

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

• limit m,n 返回偏移量為m之后的n條數(shù)據(jù)，即先獲取m+n條記錄，然后丟棄前面的m條，返回之后的n條記錄
• limit n 返回開頭的n條數(shù)據(jù)，相當(dāng)于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會(huì)立刻返回一個(gè)空結(jié)果集，它通常用來檢測SQL語法是否正確或者快速獲取結(jié)果集的字段屬性。limit n用來返回最先獲取的n條記錄，找到足夠的記錄時(shí)SQL就會(huì)停止執(zhí)行并返回結(jié)果：

select * from test limit 3;

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

select * from test limit 3,3;

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

1.2 limit和order by

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

select * from test order by salary desc limit 3;

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

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

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

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

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í)掃描的行數(shù)量從24032降低到了3。

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

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

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

2.1 延遲關(guān)聯(lián)

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

例如查詢：

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

通過延遲關(guān)聯(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ù)的主鍵，隨后再與主表關(guān)聯(lián)，通過主鍵取出這n條數(shù)據(jù)內(nèi)容。雖然SQL看起來稍微復(fù)雜了，但是它繞過了獲取前m條數(shù)據(jù)內(nèi)容這個(gè)步驟，當(dāng)m值比較大時(shí)，性能提升是很明顯的。

2.2 轉(zhuǎn)換為位置查詢

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

例如查詢：

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條件預(yù)先維護(hù)好的每一列的順序編號(hào)，此后每次分頁查詢都不需要計(jì)算偏移量，而是被轉(zhuǎn)換成了索引范圍掃描（Index Range Scan）。

2.3 記錄偏移位置

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

假設(shè)首次查詢?nèi)缦拢╥d列單調(diào)遞增）：

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

假設(shè)上面查詢返回結(jié)果集的最大id為123456，程序可以將這個(gè)值單獨(dú)記錄下來，那么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ù)，但這種方法的缺陷就是它只能一頁一頁的順序往后翻，不能跳轉(zhuǎn)翻頁，對比上面2種方法不夠靈活。

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

最新評(píng)論