mysql中limit优化的示例剖析
|
小编给大家分享一下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优化的示例分析”这篇文章的所有内容，感谢各位的阅读！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
