23-优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头痛的分页场景是“limit 1000,20”，此时 MySQL排序出前 1020条记录后仅仅需要返回第 1001到 1020条记录，前1000条记录都会被抛弃，查询和排序的代价非常高。
1．第一种优化思路
在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。例如，对电影表film根据标题title排序后取某一页数据，直接查询的时候，能够从explain的输出结果中看到优化器实际上做了全表扫描，处理效率不高：
mysql> explain select film_id, description from film order by title limit 50,5\G
1. row
id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 919
Extra: Using filesort
1 row in set (0.00 sec)
而按照索引分页后回表方式改写SQL后，从explain的输出结果中已经看不到全表扫描了：
mysql> explain select a.film_id, a.description from film a inner join (select film_id from film order by title limit 50,5)b on a.film_id = b.film_id\G
1. row
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra:
2. row
id: 1
select_type: PRIMARY
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: b.film_id
rows: 1
Extra:
3. row
id: 2
select_type: DERIVED
table: film
type: index
possible_keys: NULL
key: idx_title
key_len: 767
ref: NULL
rows: 55
Extra: Using index
3 rows din set (0.00 sec)
这种方式让MySQL扫描尽可能少的页面来提高分页效率。
2．第二种优化思路
把LIMIIT查询转换成某个位置的查询，例如，假设每页10条记录，查询支付表payment中按照租赁编号rental_id逆序排序的第42页记录，能够看到执行计划走了全表扫描：
mysql> explain select * from payment order by rental_id desc limit 410,10\G
1. row
id: 1
select_type: SIMPLE
table: payment
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16451
Extra: Using filesort
1 row in set (0.00 sec)
和开发人员协商一下，翻页的过程中通过增加一个参数last_page_record，用来记录上一页最后一行的租赁编号rental_id，例如第41页最后一行的租赁编号rental_id=15640：
mysql> select payment_id, rental_id from payment order by rental_id desc limit 400,10;
+------------+-----------+
| payment_id | rental_id |
+------------+-----------+
| 1669 | 15649 |
| 2193 | 15648 |
| 6785 | 15647 |
| 3088 | 15646 |
| 5831 | 15645 |
| 1201 | 15644 |
| 8105 | 15643 |
| 4369 | 15642 |
| 6499 | 15641 |
| 7095 | 15640 |
+------------+-----------+
10 rows in set (0.00 sec)
那么在翻页到第42页时，可以根据第41页最后一条记录向后追溯，相应的SQL可以改写为：
mysql> explain select * from payment where rental_id < 15640 order by rental_id desc limit 10\G
1. row
id: 1
select_type: SIMPLE
table: payment
type: range
possible_keys: fk_payment_rental
key: fk_payment_rental
key_len: 5
ref: NULL
rows: 8225
Extra: Using where
1 row in set (0.00 sec)
注意，这样把LIMIT m,n 转换成LIMIT n的查询，只适合在排序字段不会出现重复值的特定环境，能够减轻分页翻页的压力；如果排序字段出现大量重复值，而仍进行这种优化，那么分页结果可能会丢失部分记录，不适用这种方式进行优化。