[语句1]:
select * from daily_uac_data_migrate_ order by id limit 10000000,20;
成功, 耗时: 8319(ms)
影响行数: 20
影响行数: 20
[语句2]:
select * from daily_uac_data_migrate_ order by id limit 20000000,20;
成功, 耗时: 16596(ms)
影响行数: 20
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
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
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
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
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
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
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)