mysql中limit优化的示例分析

共计 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 行业资讯频道！