隨著偏移量的增加，limit語句的執(zhí)行會更加耗時(shí)，那么這是為什么呢？

隨著偏移量的增加，limit語句的執(zhí)行會更加耗時(shí)，那么這是為什么呢？
在業(yè)務(wù)中實(shí)現(xiàn)分頁功能就離不了MySQL的limit語句，而隨著數(shù)據(jù)表中數(shù)據(jù)量的增加，則不可避免會導(dǎo)致查詢時(shí)偏移量過大。
我們知道隨著偏移量的增大，limit語句的耗時(shí)會增加，接下來我們就探討下如何去更好的處理limit的耗時(shí)問題。

一、實(shí)驗(yàn)
1、MySQL版本：

mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.18 | +-----------+ 1 row in set (0.00 sec)

2、實(shí)驗(yàn)表結(jié)構(gòu)：

mysql> desc t213; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) | NO | PRI | NULL | auto_increment | | a | int(10) unsigned | NO | MUL | 0 | | | b | int(10) unsigned | NO | | 0 | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec)

其中，id為自增主鍵，字段a為普通索引

3、實(shí)驗(yàn)數(shù)據(jù)量近200萬：

mysql> select count(*) from t213; +----------+ | count(*) | +----------+ | 1979311 | +----------+ 1 row in set (0.11 sec)

4、開始測試：
當(dāng)偏移量為100萬時(shí)：

mysql> select * from t213 where a=4 limit 1000000,10; +---------+---+-----+ | id | a | b | +---------+---+-----+ | 1000001 | 4 | 123 | | 1000002 | 4 | 123 | | 1000003 | 4 | 123 | | 1000004 | 4 | 123 | | 1000005 | 4 | 123 | | 1000006 | 4 | 123 | | 1000007 | 4 | 123 | | 1000008 | 4 | 123 | | 1000009 | 4 | 123 | | 1000010 | 4 | 123 | +---------+---+-----+ 10 rows in set (2.00 sec)

我們知道以上的方法效率并不高，一般我們在數(shù)據(jù)量大的數(shù)據(jù)表中，不直接limit，而是通過連接去先查詢id，再查詢字段：

mysql> select c1.id, c1.a, c1.b from t213 c1 right join(select id from t213 where a=4 limit 1000000,10)c2 on c1.id=c2.id; +---------+------+------+ | id | a | b | +---------+------+------+ | 1000001 | 4 | 123 | | 1000002 | 4 | 123 | | 1000003 | 4 | 123 | | 1000004 | 4 | 123 | | 1000005 | 4 | 123 | | 1000006 | 4 | 123 | | 1000007 | 4 | 123 | | 1000008 | 4 | 123 | | 1000009 | 4 | 123 | | 1000010 | 4 | 123 | +---------+------+------+ 10 rows in set (0.16 sec)

這兩種方法的效率相差巨大，那么為什么會如此呢？MySQL是如何執(zhí)行相差巨大的兩條語句的呢？

二、分析
根據(jù)高性能MySQL中關(guān)于limit的說明：
limit語句在偏移量巨大時(shí)，如select * from t213 where a=4 limit 1000000,10;。
對效率的影響主要在于MySQL會查詢1,000,010條數(shù)據(jù)，并取最后10條，拋棄掉前面的1,000,000條。

也就是說，MySQL耗時(shí)耗力找到的數(shù)據(jù)，絕大部分都得廢棄！
MySQL查找索引a的二級索引樹，然后根據(jù)二級索引樹上的主鍵值回表到聚簇索引樹上進(jìn)行掃描數(shù)據(jù)，為了limit而重復(fù)大量無用的IO操作

關(guān)于MySQL為什么limit時(shí)會遍歷這么多數(shù)據(jù)，而不是遍歷所需的幾條，我們不去深究其設(shè)計(jì)原理，我們只分析下：

select c1.id, c1.a, c1.b from t213 c1 right join(select id from t213 where a=4 limit 1000000,10)c2 on c1.id=c2.id;

語句為何會比

select * from t213 where a=4 limit 1000000,10;

快那么多。

我們知道，MySQL中查詢的數(shù)據(jù)會放在數(shù)據(jù)頁中以便快速獲取，
而系統(tǒng)表information_schema.innodb_buffer_page保存著InnoDB緩沖池中每個(gè)頁面的信息。

我們在執(zhí)行sql后查詢innodb_buffer_page表中數(shù)據(jù)頁的個(gè)數(shù)來判斷下兩個(gè)sql語句的不同之處。

t213表中有近200萬數(shù)據(jù)
首先，重啟MySQL服務(wù)，以便innodb_buffer_page表中t213測試表的數(shù)據(jù)頁為空，然后執(zhí)行不優(yōu)化的sql：

mysql> select index_name,count(*) from information_schema.innodb_buffer_page-> where index_name in('a','primary') and table_name like '%t213%' group by index_name; Empty set (0.07 sec)mysql> select * from test.t213 where a=4 limit 1000000,10; +---------+---+-----+ | id | a | b | +---------+---+-----+ | 1000001 | 4 | 123 | | 1000002 | 4 | 123 | | 1000003 | 4 | 123 | | 1000004 | 4 | 123 | | 1000005 | 4 | 123 | | 1000006 | 4 | 123 | | 1000007 | 4 | 123 | | 1000008 | 4 | 123 | | 1000009 | 4 | 123 | | 1000010 | 4 | 123 | +---------+---+-----+ 10 rows in set (3.29 sec)mysql> select index_name,count(*) from information_schema.innodb_buffer_page -> where index_name in('a','primary') and table_name like '%t213%' group by index_name; +------------+----------+ | index_name | count(*) | +------------+----------+ | a | 901 | | PRIMARY | 2156 | +------------+----------+ 2 rows in set (0.04 sec)

可以看到select * from test.t213 where a=4 limit 1000000,10;語句使用到901個(gè)二級索引a的索引數(shù)據(jù)頁，使用到2156個(gè)聚簇索引數(shù)據(jù)頁。

然后我們再次重啟MySQL服務(wù)，確保innodb_buffer_page是空的，并執(zhí)行優(yōu)化的sql：

mysql> select index_name,count(*) from information_schema.innodb_buffer_page-> where index_name in('a','primary') and table_name like '%t213%' group by index_name; Empty set (0.03 sec)mysql> select * from test.t213 c1 right join(select id from test.t213 where a=4 limit 1000000,10)c2 on c1.id=c2.id; +---------+------+------+---------+ | id | a | b | id | +---------+------+------+---------+ | 1000001 | 4 | 123 | 1000001 | | 1000002 | 4 | 123 | 1000002 | | 1000003 | 4 | 123 | 1000003 | | 1000004 | 4 | 123 | 1000004 | | 1000005 | 4 | 123 | 1000005 | | 1000006 | 4 | 123 | 1000006 | | 1000007 | 4 | 123 | 1000007 | | 1000008 | 4 | 123 | 1000008 | | 1000009 | 4 | 123 | 1000009 | | 1000010 | 4 | 123 | 1000010 | +---------+------+------+---------+ 10 rows in set (0.22 sec)mysql> select index_name,count(*) from information_schema.innodb_buffer_page-> where index_name in('a','primary') and table_name like '%t213%' group by index_name; +------------+----------+ | index_name | count(*) | +------------+----------+ | a | 901 | | PRIMARY | 3 | +------------+----------+ 2 rows in set (0.04 sec)

以上可以看到優(yōu)化后的sql使用了聚簇索引樹的3個(gè)數(shù)據(jù)頁。

通過兩個(gè)對比，我們可以發(fā)現(xiàn)，在select * from test.t213 c1 right join(select id from test.t213 where a=4 limit 1000000,10)c2 on c1.id=c2.id;
語句中，首先執(zhí)行關(guān)聯(lián)語句 select id from test.t213 where a=4 limit 1000000,10
使用到覆蓋索引的概念，掃描二級索引樹并獲取到主鍵id值。
之后執(zhí)行外部sql時(shí)，由于id已經(jīng)找到，直接回表聚簇索引樹查找響應(yīng)id數(shù)據(jù)即可。

而執(zhí)行未優(yōu)化的select * from test.t213 where a=4 limit 1000000,10;語句時(shí)，
每一次在二級索引獲取到的id值都需要回表，執(zhí)行到最后才判斷哪些數(shù)據(jù)是滿足條件的，這樣導(dǎo)致費(fèi)力不討好，效率很慢。

三、總結(jié)
高性能MySQL中提供有以下幾種limit分頁的優(yōu)化方式：
1、join關(guān)聯(lián)方式：select * from test.t213 c1 right join(select id from test.t213 where a=4 limit 1000000,10)c2 on c1.id=c2.id;
2、主鍵遞增的表，每次分頁記錄上次的最大id值，下次分頁查詢通過判斷id > last_id_num來執(zhí)行：select * from test.t213 where id>1000000 and a=4 limit 10;
3、主鍵遞增的表，通過between id值來執(zhí)行分頁：select * from test.t213 where a=4 and id between 1000001 and 1000010;
一般來說2，3兩種方法雖然效率更高，但是局限性稍大。

實(shí)際項(xiàng)目中，針對分頁我們要注意，隨著數(shù)據(jù)量的增加，如果limit使用不當(dāng)，分頁效率會越來越慢，導(dǎo)致接口響應(yīng)時(shí)間增加，用戶友好度下降。
編寫sql時(shí)使用合適的limit方式，會減少很多不必要的問題。

總結(jié)

以上是生活随笔為你收集整理的mysql ---- limit使用方式的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。