持续创作，加速成长！这是我参与「掘金日新计划 · 10 月更文挑战」的第2天，点击查看活动详情

背景

最近有这么一个业务需求，需要基于客户信息，根据一套业务规则，给客户打上不同的标签。客户的量级是千万级别。因为客户信息会变，所以要求每天都要跑一遍全量客户打上新的标签。

挺简单的逻辑，就是分页查询整个数据库表就行了，但是实现过程中仍然采了不少坑，这里记录分享下。代码是用Python 写的，但是注释里贴上了对应的sql，所以不了解 Python 也可以瞄一瞄。

关键字：SQL 调优，扫表，刷数，数据库

第一个坑：深度分页

当时心想，不就是分页嘛，这能难到我？

我上去就是啪啪啪一顿输出：

def step1():
    """
    SELECT id FROM customer WHERE id LIMIT 0 500
    SELECT id FROM customer WHERE id LIMIT 500 500
    SELECT id FROM customer WHERE id LIMIT 1000 500
    """
    page = 1
    page_size = 500
    while True:
        print("doing，数据扫描中 page=%s" % (page))
        query_result = db.session.query(CustomerModel.id) \
        .offset((page - 1) * page_size) \
        .limit((page_size)).all()
        customer_id_list = [i[0] for i in query_result]
        if not customer_id_list:
            print("finish，数据已扫描完毕")
            break
        for customer_id in customer_id_list:
            pass
            # 各种刷数逻辑
        page = page + 1

大致思路是，一个while ture死循环，每次循环查一页数据，如果查询结果为空，就说明扫描完了。

结果上线一跑，我不禁眉头紧锁：咋越跑越慢呢？

后来分析一波，是数据库深度分页导致的，当时的数据库表大概是 800W 的数据量。

MySQL 分页，虽然结果只会返回某一页的数据，但会遍历之前的所有rows。所以分页到后面，SQL 执行时间会越来越长。

第二个坑：不带Order by，漏跑数据

分页有问题，那我就分页了，用游标的方式来搞：

def step2():
    """
    SELECT id FROM customer WHERE id > 0 LIMIT 500
    SELECT id FROM customer WHERE id > 500 LIMIT 500
    SELECT id FROM customer WHERE id > 1000 LIMIT 500
    """
    start = 0
    limit = 500
    while True:
        print("doing，数据扫描中 start=%s" % (start))
        query_result = db.session.query(CustomerModel.id) \
            .filter(CustomerModel.id > start) \
            .limit(limit).all()
        customer_id_list = [i[0] for i in query_result]
        if not customer_id_list:
            print("finish，数据已扫描完毕")
            break
        for customer_id in customer_id_list:
            pass
            # 各种刷数逻辑

        start = customer_id_list[-1]

结果上线一跑，发现有的客户没有执行我的刷数逻辑。后来排查半天，发现是没有指定 id 排序，导致有的数据页被 MySQL 直接跳过没有返回。

啥也不说了，加上 Order by 搞定：

def step3():
    """
    SELECT id FROM customer WHERE id > 0 LIMIT 500 ORDER BY id ASC
    SELECT id FROM customer WHERE id > 500 LIMIT 500 ORDER BY id ASC
    SELECT id FROM customer WHERE id > 1000 LIMIT 500 ORDER BY id ASC
    """
    start = 0
    limit = 500
    while True:
        print("doing，数据扫描中 start=%s" % (start))
        query_result = db.session.query(CustomerModel.id) \
            .filter(CustomerModel.id > start) \
            .order_by(CustomerModel.id) \
            .limit(limit) \
            .all()
        customer_id_list = [i[0] for i in query_result]
        if not customer_id_list:
            print("finish，数据已扫描完毕")
            break
        for customer_id in customer_id_list:
            pass
            # 各种刷数逻辑

        start = customer_id_list[-1]

第三个坑：数据量太大，脚本耗时太长

之后这个脚本一直在线上好好的跑着，每天跑一遍全量数据，半年了，没来打扰我平淡的生活。突然有一天，我无意中发现这个脚本跑一次要五六天，原来所扫描的客户表已经从 800W 膨胀到 3000W+。

那么该如何优化？一种思路是多线程，但是代码复杂性高，Python 线程池封装的也不好，就没考虑。

另外一种思路的是根据 modifyTime 来搞。因为是基于客户信息进行打标签的业务，客户信息不变，业务标签也就不会变，所以不需要全表扫描，每天跑一次，根据 modifyTime，把近一天修改过的客户重新跑一遍打一次标签就可以了。

给 modify_time 加上索引后，当时试跑的SQL如下，发现因为按 id 排序的缘故，MySQL仍然选择走的主键索引，而不是我们手动建的索引

EXPLAIN SELECT id FROM customer
WHERE id > 0 AND modify_time >= '2022-10-01T17:42:59'
ORDER BY id DESC LIMIT 10 ;

possible_keys   PRIMARY,idx_modify_time
key             PRIMARY
rows            18082890

随后加上了FORCE INDEX，强制走 modify_time 才解决，脚本如下：

def step5():
    """
    EXPLAIN SELECT id FROM customer
    FORCE INDEX(idx_modify_time)
    WHERE id > 0 AND modify_time >= '2022-10-01T17:42:59'
    ORDER BY id DESC LIMIT 10 ;

    possible_keys   idx_modify_time
    key             idx_modify_time
    rows            259884
    """
    start = 0
    limit = 500
    last_modify_time = datetime.datetime.now() - datetime.timedelta(hours=24)
    while True:
        print("doing，数据扫描中 start=%s" % (start))
        query_result = db.session.query(CustomerModel.id) \
            .with_hint(CustomerModel, 'FORCE INDEX(idx_modify_time)') \
            .filter(CustomerModel.id > start) \
            .filter(CustomerModel.modify_time >= last_modify_time) \
            .order_by(CustomerModel.id) \
            .limit(limit).all()
        customer_id_list = [i[0] for i in query_result]
        if not customer_id_list:
            print("finish，数据已扫描完毕")
            break
        for customer_id in customer_id_list:
            pass
            # 各种刷数逻辑

        start = customer_id_list[-1]

第四个坑：一段时间内有大量的客户被修改

之后这个脚本又安安静静跑了小半年，相安无事。

可是，某一天突然发现他一直超时，翻下日志发现 sql 执行又变慢了。抓出来一看，发现因为其他需求影响，刷了一遍数修改了大量客户信息，而且集中在某一段时间内，导致数据库扫描行数变大，触发了file sort：

    EXPLAIN SELECT id FROM customer
    FORCE INDEX(idx_modify_time)
    WHERE id > 0 AND modify_time >= '2022-10-01T17:42:59'
    ORDER BY id DESC LIMIT 10 ;

    possible_keys   idx_modify_time
    key             idx_modify_time
    rows            18084776
    Extra			Using filesort

这种情况的根本原因还是数据量大，又需要排序，随决定直接按时间片，一段一段的扫描数据，减少数据量，避免排序。

如下，把数据看做每分钟一组，每次循环处理一分钟内的数据：

def step6():
    """
    SELECT id
    FROM customer
    FORCE INDEX(idx_modify_time)
    WHERE modify_time >= '2022-09-27 02:00:00'
    AND modify_time <= '2022-09-27 02:00:59';

    possible_keys   idx_modify_time
    key             idx_modify_time
    rows            7470
    """
    now = datetime.datetime.now()
    start = datetime.datetime.now() - datetime.timedelta(hours=24)
    start_modify_time = datetime.datetime(start.year, start.month, start.day, start.hour, start.minute, 0)
    end_modify_time = datetime.datetime(start_modify_time.year, start_modify_time.month, start_modify_time.day,
                                        start_modify_time.hour, start_modify_time.minute, 59)
    while end_modify_time < now:
        print("doing，数据扫描中 start_modify_time=%s" % (start_modify_time))
        query_result = db.session.query(CustomerModel.id) \
            .filter(CustomerModel.modify_time >= start_modify_time) \
            .filter(CustomerModel.modify_time <= end_modify_time) \
            .all()
        customer_id_list = [i[0] for i in query_result]
        for customer_id in customer_id_list:
            pass
            # 各种刷数逻辑
        start_modify_time = start_modify_time + datetime.timedelta(minutes=1)
        end_modify_time = datetime.datetime(start_modify_time.year, start_modify_time.month, start_modify_time.day,
                                            start_modify_time.hour, start_modify_time.minute, 59)

总结

日常工作中，各种迁移、刷数可能都需要扫表。对于数据量较小的表，用排序+游标的方式就能完全应付。而对于数据量比较大的，短时间没法全量跑完的，就需要按照修改时间来处理。

后来又深入思考了下，其实按修改时间并不靠谱，因为修改时间这种字段的维护，比较隐性，保不齐出了问题也没人发现，所以大批量、日常性的刷数还是更应该让数据团队来处理。