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

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

在業務中實現分頁功能就離不了MySQL的limit語句，而隨著數據表中數據量的增加，則不可避免會導致查詢時偏移量過大。

我們知道隨著偏移量的增大，limit語句的耗時會增加，接下來我們就探討下如何去更好的處理limit的耗時問題。

一、實驗

1、MySQL版本：

mysql> select version();

+-----------+

| version() |

+-----------+

| 8.0.18 |

+-----------+

1 row in set (0.00 sec)

2、實驗表結構：

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、實驗數據量近200萬：

mysql> select count(*) from t213;

+----------+

| count(*) |

+----------+

| 1979311 |

+----------+

1 row in set (0.11 sec)

4、開始測試：

當偏移量為100萬時：

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)

我們知道以上的方法效率并不高，一般我們在數據量大的數據表中，不直接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是如何執行相差巨大的兩條語句的呢？

二、分析

根據高性能MySQL中關于limit的說明：

limit語句在偏移量巨大時，如select * from t213 where a=4 limit 1000000,10;。

對效率的影響主要在于MySQL會查詢1,000,010條數據，并取最后10條，拋棄掉前面的1,000,000條。

也就是說，MySQL耗時耗力找到的數據，絕大部分都得廢棄！

MySQL查找索引a的二級索引樹，然后根據二級索引樹上的主鍵值回表到聚簇索引樹上進行掃描數據，為了limit而重復大量無用的IO操作

關于MySQL為什么limit時會遍歷這么多數據，而不是遍歷所需的幾條，我們不去深究其設計原理，我們只分析下：

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中查詢的數據會放在數據頁中以便快速獲取，

而系統表information_schema.innodb_buffer_page保存著InnoDB緩沖池中每個頁面的信息。

我們在執行sql后查詢innodb_buffer_page表中數據頁的個數來判斷下兩個sql語句的不同之處。

t213表中有近200萬數據

首先，重啟MySQL服務，以便innodb_buffer_page表中t213測試表的數據頁為空，然后執行不優化的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個二級索引a的索引數據頁，使用到2156個聚簇索引數據頁。

然后我們再次重啟MySQL服務，確保innodb_buffer_page是空的，并執行優化的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)

以上可以看到優化后的sql使用了聚簇索引樹的3個數據頁。

通過兩個對比，我們可以發現，在

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

語句中，首先執行關聯語句 select id from test.t213 where a=4 limit 1000000,10

使用到覆蓋索引的概念，掃描二級索引樹并獲取到主鍵id值。

之后執行外部sql時，由于id已經找到，直接回表聚簇索引樹查找響應id數據即可。

而執行未優化的select * from test.t213 where a=4 limit 1000000,10;語句時，

每一次在二級索引獲取到的id值都需要回表，執行到最后才判斷哪些數據是滿足條件的，這樣導致費力不討好，效率很慢。

三、總結

高性能MySQL中提供有以下幾種limit分頁的優化方式：

1、join關聯方式：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來執行：select * from test.t213 where id>1000000 and a=4 limit 10;

3、主鍵遞增的表，通過between id值來執行分頁：select * from test.t213 where a=4 and id between 1000001 and 1000010;

一般來說2，3兩種方法雖然效率更高，但是局限性稍大。

實際項目中，針對分頁我們要注意，隨著數據量的增加，如果limit使用不當，分頁效率會越來越慢，導致接口響應時間增加，用戶友好度下降。

編寫sql時使用合適的limit方式，會減少很多不必要的問題。

總結

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

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。