mysql 分页查询优化。

Question

table 表中30万记录 id，自增主键， node，create_at 都有索引 但是没有联合索引

下面的语句查一次要8s左右， 可以预估随着数据的继续增加，速度会越来越慢。 最近在学习 mysql 查询优化

也看了很多文章，教程（但是没有系统的看mysql手册，不好意思）

请各位朋友指导下，如何优化，如果可以 请大概讲述下，怎么分析的，为什么使用xxxx方式优化就会有效。

谢谢各位。

EXPLAIN
SELECT 
    `id` 
  FROM
    `table` 
  WHERE `node` = 2
  ORDER BY `create_at` DESC 
  LIMIT 12 OFFSET 69996

------------------------------------------------------------------------
           id: 1
  select_type: SIMPLE
        table: table
         type: ref
possible_keys: node
          key: node
      key_len: 5
          ref: const
         rows: 72278
        Extra: Using where; Using filesort

Answer 1

优化分页的问题其实就是offset的问题，尤其是偏移量大的时候。mysql会扫描大量不需要的行然后抛弃，只取limit的数量。所以一般最好不要用offset。解决方法有
1.可以先使用索引覆盖扫描，而不是查询所有的列，然后做关联操作返回相关的列。这个方法可以叫做“延迟关联”
2.可以把limit查询转换成已知位置的查询，变成between XXX and XXX 。
3.可以记录上次查询的数据的位置，下一次查询直接从该位置开始扫描，楼上就是用的这种办法。

鉴于问题好像只查询id一个字段。1方法用不到，2，3可以考虑。

Answer 2

不要使用OFFSET方式分页
以你的例子来说

MySQL会先查询所有符合条件的数据，通过EXPLAIN可以发现（72278），查询了这么多

因为OFFSET的关系，MySQL丢弃前面（69996）的记录。
查询优化就是指在第1步的时候就让MySQL查询最少的数据。

我目前在用的分页方式（数据量千万级），依旧拿你的例子来说，假设分页大小为10第一页

#查询1
SELECT 
    `id` 
  FROM
    `table` 
  WHERE `node` = 2
  ORDER BY `id` ASC
  LIMIT 10

假设查询1的第10条数据的id是10，第1条数据的id是1
那么查询第二页的SQL如下

SELECT 
    `id` 
  FROM
    `table` 
  WHERE `node` = 2 AND `id`>10
  ORDER BY `id` ASC
LIMIT 10

这样你可以发现响应速度超快。不过有个问题是无法前往指定页数，不过从我目前的实际情况来看，这个没有影响，有个搜索功能就可以弥补