關於mysql中limit最佳化的問題

# 建立測試環境：
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;
重複很多次，以便資料達到：
: 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行而已，

: 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;

: test 13:23:05> reset query cache ;
Query OK, 0 rows affected (0.00 sec)

: 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)

: 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)

: 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)

: 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
這裡是一個奇怪的地方？？？