共計 6065 個字符，預計需要花費 16 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 mysql 中 limit 優化的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
| stest | CREATE TABLE `stest` (
`id` int(10) unsigned NOT NULL,
`k` int(10) unsigned NOT NULL DEFAULT 0 ,
`c` char(120) NOT NULL DEFAULT ,
`pad` char(60) NOT NULL DEFAULT ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
第一條測試語句
mysql select * from stest where id =(select id from stest order by id asc limi
t 900000,1) limit 50;
50 rows in set (2.58 sec)
第二條測試語句
mysql select * from stest order by id asc limit 900000,50;
50 rows in set (2.53 sec)
按道理來說第一條應該比第二條查詢要快。。[@more@]
# 建立測試環境：
MYSQL：5。1。40
RHEL 5u4
CREATE TABLE `heyftest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4980635 DEFAULT CHARSET=utf8
insert into heyftest(name) values (zzzzzzzzzzzzzzzzzzzzzzzzz
insert into heyftest(name) select name from heyftest;
重復很多次，以便數據達到：
root@127.0.0.1 : test 12:41:35 select count(*) from heyftest;
+———-+
| count(*) |
+———-+
| 2097408 |
+———-+
1 row in set (0.54 sec)
# 從執行計劃來分析：
explain extended select * from heyftest where id =(select id from heyftest order by id asc limit 900000,1) limit 50;
+—-+————-+———-+——-+—————+———+———+——+———+———-+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———-+——-+—————+———+———+——+———+———-+————-+
| 1 | PRIMARY | heyftest | range | PRIMARY | PRIMARY | 4 | NULL | 1048908 | 100.00 | Using where |
| 2 | SUBQUERY | heyftest | index | NULL | PRIMARY | 4 | NULL | 900001 | 233.09 | Using index |
+—-+————-+———-+——-+—————+———+———+——+———+———-+————-+
先通過子查詢中，找到第 900001 行，
然后再通過主鍵去 RANGE 訪問，但這里 ROWS=1048908，有點不理解？？？因為我只想要 50 行而已，
root@127.0.0.1 : test 12:58:23 explain extended select * from heyftest order by id asc limit 900000,50;
+—-+————-+———-+——-+—————+———+———+——+——–+———-+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+—-+————-+———-+——-+—————+———+———+——+——–+———-+——-+
| 1 | SIMPLE | heyftest | index | NULL | PRIMARY | 4 | NULL | 900050 | 233.08 | |
+—-+————-+———-+——-+—————+———+———+——+——–+———-+——-+
ROW=900050，可以理解，從第一行開始掃描，在索引里一直加到 900000+50
# 從邏輯讀來分析：
邏輯讀，LIO = 兩次 Innodb_buffer_pool_read_requests 之差
這個測試盡量讓表的所有內容都能在 INNODB_BUFFER 里，以避免有物理 IO，這樣我們拿到的數據會準一些；
所以測試之前請運行：select count(distinct name) from heyftest;
root@127.0.0.1 : test 13:23:05 reset query cache ;
Query OK, 0 rows affected (0.00 sec)
root@127.0.0.1 : test 13:23:06 show status like Innodb_buffer_pool_read_requests
+———————————-+———-+
| Variable_name | Value |
+———————————-+———-+
| Innodb_buffer_pool_read_requests | 57953539 |
+———————————-+———-+
1 row in set (0.01 sec)
root@127.0.0.1 : test 13:23:06 select * from heyftest where id =(select id from heyftest order by id asc limit 900000,1) limit 50;
show status like Innodb_buffer_pool_read_requests
+———+—————————-+
| id | name |
+———+—————————-+
| 2324111 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324113 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324115 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324117 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324119 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324121 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324123 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324125 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324127 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324129 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324131 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324133 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324135 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324137 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324139 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324141 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324143 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324145 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324147 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324149 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324151 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324153 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324155 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324157 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324159 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324161 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324163 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324165 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324167 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324169 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324171 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324173 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324175 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324177 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324179 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324181 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324183 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324185 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324187 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324189 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324191 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324193 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324195 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324197 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324199 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324201 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324203 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324205 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324207 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
| 2324209 | zzzzzzzzzzzzzzzzzzzzzzzzzz |
+———+—————————-+
50 rows in set (0.61 sec)
root@127.0.0.1 : test 13:23:06 show status like Innodb_buffer_pool_read_requests
+———————————-+———-+
| Variable_name | Value |
+———————————-+———-+
| Innodb_buffer_pool_read_requests | 58856559 |
+———————————-+———-+
1 row in set (0.00 sec)
root@127.0.0.1 : test 13:23:06 select 58856559-57953539;
+——————-+
| 57953539-58856559 |
+——————-+
| 903020 |
+——————-+
1 row in set (0.00 sec)
LIO：903020
我們都用上面這種方法來測試，對 SQL 語句得到的邏輯讀對應如下：
SQL1：select * from heyftest where id =(select id from heyftest order by id asc limit 900000,1) limit 50;
LIO：903020
SQL2：select * from heyftest order by id asc limit 900000,50;
LIO：115503
SQL4：select id from heyftest order by id asc limit 900000,1; — 結果：2324111
LIO：115497
SQL5：select * from heyftest where id =2324111 limit 50;
LIO：26
其實我們認為 SQL1 的理想計劃是 =SQL4+SQL5，其實即使這樣，115497+26 115503, 所以這里先否掉樓主的想法。
而實際上我們看到 MYSQL 沒有與我們想像的一樣去把 SQL 拆分成 SQL4，SQL5，
從 SQL1 邏輯讀看 LIO：903020，從執行計劃看，ROWS=1048908
以上是“mysql 中 limit 優化的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！
