上亿表查询、大批量数据更新优化

上亿表查询、大批量数据更新优化

背景

某些头部租户用“任务系统（可以理解为自动化营销工具系统，后面叫“营销计划”）”下发了几十万甚至上百万任务数据，产品层面提供了撤回操作、逾期、结束操作，执行这 3 操作，这批任务从可见列表移除。

需求分析

需求概述

1. 管理员负责创建“营销计划”、结束“营销计划”、撤回”营销计划“；

2. 一线员工负责执行任务；

3. 系统调度任务自动执行，下发“营销计划”任务。

需求->模型

模型概述，模型做了简化方便理解。

“营销计划”模型

一句话概述，什么时间，通过某个员工，用某个方式，给某些客户下发什么内容。

任务模型

一句话概述，员工执任务执行明细。

方案概述

尝试过 2 种方案，随着数据体量增加，都遇到了一些问题，总结出来大家避坑。为了方便各位看官了解优化过程，对方案演化进行详细讲解。

方案一 多表 join 查询

多表 join，”营销计划“模型 status 标识“营销计划”状态，“营销计划”模型和任务模型 join 关联查询即可。方案一带来了很多问题如下

1. 因为模型简化了，最初有4张表join查询，数据体量逐步增加，SQL 性能越来越差；

2. SQL 和索引优化困难，尝试过多轮 SQL 优化、索引调优，效果不尽如意。

性能最差时任务表数据量：6000w，“营销计划”模型表数据量：40w，另外两张表数据分别是：70w 和 5w 左右。

查询 SQL 如下

SELECT DISTINCT
	a.id ,
	c.NAME 
FROM
	task AS ut
	LEFT JOIN plan_node AS c ON ut.node_id = c.id 
	AND a.tenant_id = c.tenant_id
	LEFT JOIN plan AS d ON ut.plan_id = d.id 
	AND ut.tenant_id = c.tenant_id
	LEFT JOIN task_detail AS utd ON utd.task_id = ut.id 
	AND utd.tenant_id = ut.tenant_id 
WHERE
	(
		ut.execute_id = '2604' 
		AND d.is_delete = 0 
	) 
	AND utd.STATUS IN ( '1', '2' ) 
	AND ut.tenant_id = 'xxx' 
ORDER BY
	a.create_time ASC 
	LIMIT 20

不用了解 SQL 逻辑，监控每天 5s 以上的 SQL 大概有20000+，对于头部租户使用体验是比较差的。

接下来进行了一次大优化，方案二👇👇👇👇👇👇

方案二 任务表冗余字段

为了简化 SQL 提升查询性能，用了一些反范式设计，比如适当冗余字段，减少表关联，主要做了下面几件事儿

1. 任务表冗余了'状态'字段；

2. 任务列表展示名称通过 byid 查接口补数据；

3. 撤回、结束等操作通过事件方式通知，更新任务表'状态'字段；

4. 完成前面 3 点，查询即可从多表 join 改成单表；

5. 完成 SQL 优化后，做了索引调优。

复杂 SQL 经过优化后👇

SELECT
	plan_id,
	id
FROM
	task_detail 
WHERE
	tenant_id = 'xxx' 
	AND execute_id = '0' 
	AND STATUS IN ( 'xx' ) 
GROUP BY
	plan_id
ORDER BY
	plan_id DESC 
	LIMIT 20 OFFSET 0;

SQL 看上去是不是简化了很多？这次改造确实也给我们带了一年左右的消停，基本没有 SQL 问题了，运维请喝茶的概率也低了。

好景也不长啊，最近运维同学发现任务表大批量更新（最高更新行 100w+ 行），导致数据库延迟严重

上图是最近更新 50w+ 数据从监控抓的数据。

从日志捞了这批 SQL，逻辑是每次更新 5000 行，循环更新直到数据更新完成退出循环，SQL 如下

UPDATE `task_detail`
SET `complete_time`=1719471442000,
    `status`=4,
    `update_time`=1719476815555
WHERE tenant_id = 'W00000016495'
  and plan_id = 'xx'
  and status <> 4
limit 5000;

看上去 SQL 比较简单（忽略执行分析，已经没有当时环境了，只需要知道每次执行更新 SQL 扫扫描行数是当 plan_id 对应的所有数据），决定研究下 TIDB 官方文档更新数据 | PingCAP 文档中心

看起来是大批量更新导致锁时间过长了，或者产生了大量冲突导致的。官方提供了大批量数据更新方案。我们打算按照官方文档对方案二进行优化👇👇👇

方案三 大批量更新方案

其实就是查询主键，通过主键更新（这个很早之前其实想过这个方案），官方案例非常巧妙，下面这代码可以了解下

// updateBatch select at most 1000 lines data to update score
func updateBatch(db *sql.DB, firstTime bool, lastBookID, lastUserID int64) (bookID, userID int64) {
    // select at most 1000 primary keys in five-point scale data
    var err error
    var rows *sql.Rows

    if firstTime {
        rows, err = db.Query("SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` " +
            "WHERE `ten_point` != true ORDER BY `book_id`, `user_id` LIMIT 1000")
    } else {
        rows, err = db.Query("SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` "+
            "WHERE `ten_point` != true AND `book_id` > ? AND `user_id` > ? "+
            "ORDER BY `book_id`, `user_id` LIMIT 1000", lastBookID, lastUserID)
    }

    if err != nil || rows == nil {
        panic(fmt.Errorf("error occurred or rows nil: %+v", err))
    }

    // joint all id with a list
    var idList []interface{}
    for rows.Next() {
        var tempBookID, tempUserID int64
        if err := rows.Scan(&tempBookID, &tempUserID); err != nil {
            panic(err)
        }
        idList = append(idList, tempBookID, tempUserID)
        bookID, userID = tempBookID, tempUserID
    }

    bulkUpdateSql := fmt.Sprintf("UPDATE `bookshop`.`ratings` SET `ten_point` = true, "+
        "`score` = `score` * 2 WHERE (`book_id`, `user_id`) IN (%s)", placeHolder(len(idList)))
    db.Exec(bulkUpdateSql, idList...)

    return bookID, userID
}

通过唯一id滚动查询和更新，扫描行数越来越少。

SELECT `book_id`, `user_id` FROM `bookshop`.`ratings` "+
            "WHERE `ten_point` != true AND `book_id` > ? AND `user_id` > ? "+
            "ORDER BY `book_id`, `user_id` LIMIT 1000

从官方案例中吸取了一些经验，优化了大批量更新 SQL ，看看优化前后 SQL 效果对比

预备 48w 数据，分别对新老 SQL 执行效率对比

优化前如下图

通过 plan_id 单次更新 5000 ，SQL 执行耗时长，另外内存消耗也非常大。

优化后如下图

先查 id 在通过 id in 更新，单次更新 5000 ，SQL 执行耗时明显降低了，另外内存消耗改善是非常明显的。

优化前，完成 48w 数据更新耗时（308s）

优化后，完成 48w 数据更新耗时（137s）

对比优化前后，SQL 执行效率有明显提升了。

总结

本文讲了上亿表查询、更新优化方案演进过程，优化前后对比效果非常明显。有几点比较重要

1. 反范式设计，适当冗余字段，简化 SQL 复杂度，提升查询效率；

2. 大批量数据更新，采用 SELECT 和 UPADATE 这套组合拳，先查主键id，通过主键id更新，需注意 SELECT where 条件的过滤性。

若有问题，欢迎评论区讨论。