优化分页查询

优化分页查询

方法一

​ 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

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)