【MYSQL优化15】大数据量分页优化

select field from table limit offset,rows

• 1.从业务上优化
  不允许翻过100页
  在offset比较大时，limte offset 的效率很低，因为它的要先查询再跳过.
  查询1kw为例，要把1kw取出来，再跳过这1kw行

  mysql> select log_id,create_uid from gold_room_log limit 0,10;
  +--------+------------+
  | log_id | create_uid |
  +--------+------------+
  |      1 |    1542090 |
  |      2 |    1542090 |
  |      3 |    1542090 |
  |      4 |    1542089 |
  |      5 |    1542089 |
  |      6 |    1542089 |
  |      7 |    1542089 |
  |      8 |    1542089 |
  |      9 |    1542089 |
  |     10 |    1542094 |
  +--------+------------+
  10 rows in set (0.00 sec)
  
  mysql> select log_id,create_uid from gold_room_log limit 100000,10;
  +---------+------------+
  | log_id  | create_uid |
  +---------+------------+
  | 1000001 |    1555441 |
  | 1000002 |    1552879 |
  | 1000003 |    1553922 |
  | 1000004 |    1556527 |
  | 1000005 |    1556772 |
  | 1000006 |    1554096 |
  | 1000007 |    1553254 |
  | 1000008 |    1555427 |
  | 1000009 |    1564131 |
  | 1000010 |    1547377 |
  +---------+------------+
  10 rows in set (0.01 sec)
  
  mysql> select log_id,create_uid from gold_room_log limit 10000000,10;
  +----------+------------+
  | log_id   | create_uid |
  +----------+------------+
  | 10194814 |    1656483 |
  | 10194815 |    1644228 |
  | 10194816 |    1681150 |
  | 10194817 |    1645149 |
  | 10194818 |    1658664 |
  | 10194819 |    1658611 |
  | 10194820 |    1585559 |
  | 10194821 |    1570038 |
  | 10194822 |    1654489 |
  | 10194823 |    1556482 |
  +----------+------------+
  10 rows in set (1.37 sec)
  
  mysql> show profiles;
  +----------+------------+---------------------------------------------------------------+
  | Query_ID | Duration   | Query                                                         |
  +----------+------------+---------------------------------------------------------------+
  |        1 | 1.36947275 | select log_id,create_uid from gold_room_log limit 10000000,10 |
  |        2 | 0.01329775 | select log_id,create_uid from gold_room_log limit 100000,10   |
  |        3 | 0.00016700 | select log_id,create_uid from gold_room_log limit 0,10        |
  +----------+------------+---------------------------------------------------------------+
  3 rows in set, 1 warning (0.01 sec)
  
  mysql> show profile for query 1;
  +----------------------+----------+
  | Status               | Duration |
  +----------------------+----------+
  | starting             | 0.000039 |
  | checking permissions | 0.000004 |
  | Opening tables       | 0.000014 |
  | init                 | 0.000010 |
  | System lock          | 0.000005 |
  | optimizing           | 0.000004 |
  | statistics           | 0.000009 |
  | preparing            | 0.000007 |
  | executing            | 0.000002 |
  | Sending data         | 1.369337 |
  | end                  | 0.000012 |
  | query end            | 0.000005 |
  | closing tables       | 0.000008 |
  | freeing items        | 0.000012 |
  | cleaning up          | 0.000007 |
  +----------------------+----------+
  15 rows in set, 1 warning (0.00 sec)
  
  

  查询时间1.37s 都花在 Sending data

• 2.把offset换成主键索引条件查询，减少查询时间
  这种方式要求：
  数据没有被删除过，否者查询的数据不准确
  或者不物理删除，只逻辑删除(is_del=1)，在页面显示数据时做处理

  mysql> select log_id,create_uid from gold_room_log where log_id>10000000 limit 10;
  +----------+------------+
  | log_id   | create_uid |
  +----------+------------+
  | 10000001 |    1653829 |
  | 10000002 |    1680856 |
  | 10000003 |    1671385 |
  | 10000004 |    1543762 |
  | 10000005 |    1649077 |
  | 10000006 |    1658246 |
  | 10000007 |    1630592 |
  | 10000008 |    1665355 |
  | 10000009 |    1637759 |
  | 10000010 |    1587961 |
  +----------+------------+
  10 rows in set (0.02 sec)
  
  

• 3.非要物理删除，还用offset精确查找，还不限制用户
  优化思路：不查，少查，查索引，少取.
  如果必须要查，则只查索引不查数据，得到id.
  再用id去查具体条目，这种技巧就是延迟索引

  mysql> select a.log_id,create_uid from gold_room_log as a inner join(select log_id from gold_room_log limit 10000000,10) as b on b.log_id=a.log_id;
  +----------+------------+
  | log_id   | create_uid |
  +----------+------------+
  | 10194814 |    1656483 |
  | 10194815 |    1644228 |
  | 10194816 |    1681150 |
  | 10194817 |    1645149 |
  | 10194818 |    1658664 |
  | 10194819 |    1658611 |
  | 10194820 |    1585559 |
  | 10194821 |    1570038 |
  | 10194822 |    1654489 |
  | 10194823 |    1556482 |
  +----------+------------+
  10 rows in set (1.08 sec)
  
  

  其原理就是按索引id查找1kw中的10条数据，再用id查找其它字段
  避免了没有索引的字段，回行取数据的时间