mysql中limit优化的示例分析
小编给大家分享一下MySQL中limit优化的示例分析,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!
创新互联主要为客户提供服务项目涵盖了网页视觉设计、VI标志设计、营销网站、网站程序开发、HTML5响应式重庆网站建设公司、手机网站开发、微商城、网站托管及成都网站维护、WEB系统开发、域名注册、国内外服务器租用、视频、平面设计、SEO优化排名。设计、前端、后端三个建站步骤的完善服务体系。一人跟踪测试的建站服务标准。已经为广告设计行业客户提供了网站设计服务。
| 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优化的示例分析”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注创新互联行业资讯频道!
本文标题:mysql中limit优化的示例分析
新闻来源:http://ybzwz.com/article/gjoodd.html