sql语句性能优化问题

Question

假设我有一个 mysql 数据表 tab，总数据量为3400万，id 为主键，偏移量 达到2000万，执行以下两条语句：

语句A（耗时129.570s）

select * from tab where id < 20000010 LIMIT 20000000, 10;

语句B（耗时5.114s）

SELECT a.* FROM tab a, (select id from tab LIMIT 20000000, 10) b where a.id = b.id;

想请教一下为什么这两条语句的差异这么大，其中的原理是什么呢？十分感谢~~

Answer 1

explain analyze select * from tab limit 1000000,10;
-> Limit/Offset: 10/1000000 row(s)  (actual time=509.358..509.363 rows=10 loops=1)
    -> Table scan on tab  (cost=122356.30 rows=1214028) (actual time=0.023..451.303 rows=1000010 loops=1)

explain analyze select id from tab limit 1000000,10;
-> Limit/Offset: 10/1000000 row(s)  (actual time=248.328..248.338 rows=10 loops=1)
    -> Index scan on tab using id  (cost=122356.30 rows=1214028) (actual time=0.031..192.377 rows=1000010 loops=1)

mysql的主键用的是聚簇索引，所以只查一个主键的时候是扫索引，查全部的时候是扫表。
你的表越大，2者的差距就越大。

Answer 2

我个人的想法, 不一定正确, 首先可以参考sql的执行顺序: 先是from 然后是 jion ON where 分组、 聚合函数、 having、 select、 排序、 limit ,
你上面的

select * from tab where id < 20000010

使用到的主键索引能定位到ID小于20000010的, 但是你这样使用到select * 所有需要执行多次回表查询,查询到其他字段数据2000多万-3400万, 执行完select后, 才会去执行limit。

下面的sql应该蛮好理解了!
(PS: 个人理解, 不正确的地方大家可以指出。)

Answer 3

感觉问题出在id < 20000010这里，需要判断的次数太多了吧