4
回答
关于mysql的limit语句优化后与优化前没有区别的疑问
终于搞明白,存储TCO原来是这样算的>>>   

大家好,小弟最近在看《高性能mysql一书》,其中看到该书上一个关于优化limit查询的sql语句写法,于是便跃跃欲试建了张测试表,然后往里面插入了一百多万条数据,下面是我测试的三条sql语句:
①最普通的写法
select * from test2 limit 1000000, 100;

②《高性能mysql》一书的写法:
select * 
from (select no from test2 limit 1000000, 100) as A natural join test2

③网上一些教程的写法:
select * 
from no >= 
     (select no from test2 order by no limit 1000000,1)
limit 100

然而最终结果却让我有点失望,后两条语句在navicat上手动执行并没有比第一条快,①和③的执行时间都是二点多秒,而②则夸张的去到了四点多秒,而且重复了很多次都是一样的结果。之后想想觉得可能是软件的耗时数据不准确,于是又打开Apache jmeter进行jdbc测试,开10个线程每个线程执行100次,可是最终结果依然很让人困惑,②语句没有像之前那么夸张落后那么多了,可是三条语句的平均耗时、耗时中位数以及每秒操作次数都没太大差别(甚至第①条还略微领先一丢丢),这完全不像书上以及网上教程说得那样“大大提高查询性能”啊,请教一下大家这是什么回事

举报
Siu_Keung
发帖于2个月前 4回/163阅

引用来自“mlovewt”的评论

表结构

CREATE TABLE `test2` (
  `no` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` bigint(20) DEFAULT NULL,
  `message` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`no`),
  CONSTRAINT `test2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `test1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1379274 DEFAULT CHARSET=utf8

表结构如上,no是主键,id是引用另一张表的外键(用作别的测试的,跟本测试无关)。

1、explain select *  from (select no from test2 t1 limit 1000000, 100) as A natural join test2 t2 ;
2、explain select * from test2 t1 where  no >= (select no from test2 t2 order by no limit 1000000,1)limit 100 ;
3、explain select * from test2 t1 limit 1000000, 100;

[root@localhost][test]> explain select *  from (select no from test2 t1 limit 1000000, 100) as A natural join test2 t2 ;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000100 |   100.00 | NULL        |
|  1 | PRIMARY     | t2         | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | A.no |       1 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | NULL       | index  | NULL          | PRIMARY | 8       | NULL | 2125760 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+---------+----------+-------------+

[root@localhost][test]> explain select * from test2 t1 where  no >= (select no from test2 t2 order by no limit 1000000,1)limit 100 ;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | PRIMARY     | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL | 1062880 |   100.00 | Using where |
|  2 | SUBQUERY    | t2    | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
[root@localhost][test]> explain select * from test2 t1 limit 1000000, 100;
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2125760 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------+

这是你那三条sql,执行的速度也是这个顺序

实测第二种跟第三种写法都是0.8s,第一种就不说了,注意你的第三条语句比第二条多了个OrderBy,这个性能影响是巨大的,去掉之后就持平了。还有注意缓存的影响

数据库数据126w,mysql5.7

顶部