在mysql中,通常使用limit做分页,而且经常会跟order by 连用。在order by 上加索引有时候是很有帮助的,不然系统会做很多的filesort。
经常碰到的一个问题是limit的offset太高,如:limit 100000,20,这样系统会查询100020条,然后把前面的100000条都扔掉,这是开销很大的操作,导致查询很慢。假设所有分页的页面访问频率一样,这样的查询平均扫描表的一半数据。优化的方法,要么限制访问后面的页数,要么提升高偏移的查询效率。
select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);
SQL>explain select * from user_order_info limit 1000000,5;
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
| 1 | SIMPLE | user_order_info | ALL | NULL | NULL | NULL | NULL | 23131886 | |
+----+-------------+-----------------+------+---------------+------+---------+------+----------+-------+
1 row in set (0.00 sec)
SQL>explain extended select * from user_order_info inner join (select pin from user_order_info limit 1000000,5) as lim using(pin);
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | |
| 1 | PRIMARY | user_order_info | eq_ref | PRIMARY | PRIMARY | 42 | lim.pin | 1 | 100.00 | |
| 2 | DERIVED | user_order_info | index | NULL | PRIMARY | 42 | NULL | 23131886 | 100.00 | Using index |
+----+-------------+-----------------+--------+---------------+---------+---------+---------+----------+----------+-------------+
3 rows in set, 1 warning (0.66 sec)