mysql offset 为什么这么慢。。。

之前从来没觉得 offset 有什么坑，也没有细想过 mysql 的 offset 的实现原理。

直到这周打算把 4000w+ 的数据热到 redis 中，写了一个脚本, 主要的代码大概如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
from redis import Redis
from sqlalchemy import select

import table  # Mysql Table Object

redis_cli = Redis(xxxxxxxxxxx)

CUR = 0
MAX = 40000000

while CUR <= MAX:
    query = text("""SELECT id FROM example
                    LIMIT CUR, 1000
                    ORDER BY id DESC""")
    result = table.execute(query).fetchall()
    pipe = redis_cli.pipeline()  # 使用 pipeline 来减少连接开销
    for item in result:
         pipe.set(item.id, 'foo')
    pipe.execute()
    CUR += 1000

开始执行大概下午 6 点左右，然后我就去吃饭逗猫写代码又睡了一觉。

上午 11 点左右来公司发现，才完成了 1000w 左右的数据，内心是崩溃的。。。。。

看了一眼 slow log，一次 Mysql 的查询需要 40s， 然后开始查一些资料找原因，发现 offset/limit 根本无法用到 index 机制，而是读整张表，然后数到需要便宜的位置，所以上面的代码到 1000w 时， mysql 会按照 id 的顺序逐条累加，一直找到第 1000w 的位置（至于为什么不通过 index 来直接找到 id 为 10000000 的数据，原因很简单，id 为 10000000 的数据并不已经代表是第 1000w 条数据，中间有可能会有数据被删除使得 id 非连续）。

找到了原因重写了一把脚本，把 offset 改成 where 就解决了这个问题，然后用了半个小时就跑完了数据- -。

thumbnail