优化分页查询
方法一
在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容。
mysql> desc select customer_id,email from customer_t order by last_name limit 50,5 \G; *************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: customer_t
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 599
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
mysql> desc select a.customer_id,a.email from customer_t a inner join(select customer_id from customer_t order by last_name limit 50,5) b on a.customer_id=b.customer_id \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 55
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: a
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: b.customer_id
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: customer_t
partitions: NULL
type: index
possible_keys: NULL
key: idx_last_name
key_len: 182
ref: NULL
rows: 55
filtered: 100.00
Extra: Using index
3 rows in set, 1 warning (0.00 sec)
方法二
把 LIMIT 查询转换成某个位置的查询
mysql> desc select * from payment order by rental_id desc limit 410,10 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 16125
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
注意:这样把 LIMIT m,n 转换成 LIMIT n 的查询,只适合在排序字段不会出现重复值的特定环境,能够减轻分页翻页的压力;如果排序字段出现大量重复值,而进行这种优化,那么分页结果可能会丢失部分记录,不适用这种方式进行优化。
mysql> desc select * from payment where rental_id < 15640 order by rental_id desc limit 10 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: payment
partitions: NULL
type: range
possible_keys: fk_payment_rental
key: fk_payment_rental
key_len: 5
ref: NULL
rows: 8062
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.29 sec)