mysql limit性能分析

[语句1]:

select * from daily_uac_data_migrate_ order by id limit 10000000,20;

成功, 耗时: 8319(ms)
影响行数: 20

 

[语句2]:
select * from daily_uac_data_migrate_ order by id limit 20000000,20;
成功, 耗时: 16596(ms)
影响行数: 20

 

[语句3]:
select * from daily_uac_data_migrate_ order by id limit 30000000,20;
成功, 耗时: 24894(ms)
影响行数: 20

[语句4]:
select * from daily_uac_data_migrate_ order by id limit 40000000,20;
成功, 耗时: 33248(ms)
影响行数: 20

 ==========================================================

[语句1]:
select * from daily_uac_data_migrate_ limit 10000000,20;
成功, 耗时: 8398(ms)
影响行数: 20

 

[语句2]:
select * from daily_uac_data_migrate_ limit 20000000,20;
成功, 耗时: 16795(ms)
影响行数: 20

 

[语句3]:
select * from daily_uac_data_migrate_ limit 30000000,20;
成功, 耗时: 25355(ms)
影响行数: 20

[语句4]:
select * from daily_uac_data_migrate_ limit 40000000,20;
成功, 耗时: 33585(ms)
影响行数: 20

==============================================

MySQL的limit工作原理就是先读取n条记录，然后抛弃前n条，读m条想要的，所以n越大，性能会越差。 

优化前SQL: SELECT * FROM member ORDER BY last_active LIMIT 50,5 
优化后SQL: SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id) 
分别在于，优化前的SQL需要更多I/O浪费，因为先读索引，再读数据，然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了，然后通过member_id读取需要的列。

也就是分页的优化，查阅资料得以下几种方式：

1、索引列大于法

SELECT id FROM table WHERE id>(SELECT id FROM table ORDER BY id LIMIT $start,1) ORDER BY id LIMIT $length;

30万数据时，加order by效率提高不多(1秒左右)，不加order by 效率提高一半

10万数据时，效率提高明显。

2、超过半数逆序分页法。

意为判断分页开始id是否超过总数的一半，如果超过一半则反写sql，减小了LIMIT里面的offset值，从而提高效率。

30万数据，假如原SQL语句是ORDER BY id LIMIT 200000,20,可改写为ORDER BY id DESC LIMIT 99980,20,效率提高超过一个数量级。

3、使用临时表缓存索引列，分页时使用临时表，获取到id用IN子查询。

SELECT id FROM table WHERE id IN(SELECT id FROM table_tmp LIMIT $start,$length);

4、还可以用分页表，实验如下：

查询mysql表的最后一页的数据：

mysql> select * from t_group where 1 limit 10324547,20;

20 rows in set (4.88 sec)
 

mysql> select a.* from t_wordlist as a inner join t_wd_ids as b where a.id = b.wd_id and b.id = 516227;

20 rows in set (0.01 sec)