工作回顾之优化开户流程

发表于   |   分类于 后端   |   暂无评论

前言

众所周知，SQL文化博大精深，SQL优化那阵算得上百花齐放，啥样的优化方式都有。而我虽然天天干着CURD的工作，和SQL天天打交道，其实说句实话，对SQL优化确实所会不多，只能算的上是皮毛。

这不，什么不会就来什么，刚入职还在试用期的时候，老大派来一个任务

：“小陈，我们有个开户的流程，从其他平台迁移过来，你对接下”

：“好的，老大”

：“对了，还有一个开户的动作，特别慢，要半小时甚至更久才能完成，你顺便优化一下”

：“好的”

什么开户？什么流程？什么优化，刚接到这个问题的我也是一头雾水，后面再深入了解了一下，大概明白了这个问题的来龙去脉

背景介绍

我们是多租户系统。每签一个新客户，合同落地之后，使用我们产品之前，在我们部门这边都要为这个客户创建一个数据库，为客户使用系统保存业务数据用。

每一个客户都有一个独立的数据库。大部分客户的数据库都在一个数据库云服务上，只有那种数据量特别大的客户我们会单独为他开通一个云服务器，这个数据库云服务上只有他自己。

我在接收这个问题之前，物业这边的客户数据库开库动作都是在其他部门的平台上完成（因为物业部门都是从其他部门独立出来的，有些业务依赖原先部门的系统，这个开户的流程就是），那现在要做的事情其实有两个

  1. 原先的开户流程迁移到物业系统中

  2. 优化开户流程（缩短开户时间

那在着手操作之前，首先要熟悉下代码了，因为物业这边的系统是从原先部门迁移出来的，虽然经过了不同的迭代周期，但是核心代码应该是差不多的。

那就好办了，首先联系对接的对方工程师，然后了解业务代码路径，自己跟着链路走一遍，没问题把代码复制粘贴过来就好了，这一步还是很快的，迁移过来之后，就需要跑通一遍流程，跑流程的时候，发现果然很慢，开户要半个多小时

代码拆解

那现在就要进行开户全流程代码拆解了，看看究竟是哪一步出现的问题，整理开户流程如下：

  1. 从配置库获取空库信息

  2. 将空库下载到本地，形成SQL文件

  3. file_get_content获取到文件内容

  4. 字符串函数preg_replace替换指定内容，大概是删除空库名、DEFINER相关信息

  5. 然后将替换过的SQL文件内容重新写入file_put_content到SQL文件中

  6. 再将处理过的SQL文件导入到新指定租户数据库中

流程大概如上，具体跑一遍流程观察一下时间...

利用跑的时间去查个bug...
微信图片_20250523141735.jpg

查完bug过来一看还在跑，那就再等等吧（等等党>...
最终时间定格在30多分钟跑完，为什么这么慢呢？这么慢的速度，理论上来说肯定是有优化空间的。

在每个可能得节点打了日志，定位到步骤4耗时最长

分析步骤4为什么耗时这么久，当时想的是从这么几点来看

  1. 数据表有多少张？

  2. 导出的SQL文件有大

  3. 导出效率问题

  4. preg_replace效率问题

  5. 回写到租户库的效率问题

逐一确定下：

数据表大概有2000多张

导出SQL文件50M左右

导出方式是mysqldump

preg_replace适用的是正则替换字符串，执行复杂度O(n)到O(n²)取决于正则复杂度

通过分析以上几点来看，怎么优化就清晰多了

优化点

1、导出/导入方式优化

之前的导出代码是mysqldump -P -u -p tenant > tenant.sql ，可以优化为压缩导出mysqldump -P -u -p tenant | gzip > tenant.gz

这样有什么好处呢？

  1. 磁盘I/O时间大幅减少

    • 压缩后的gz文件是是原始文件（.sql）文件体积的10-25%大小

    • 写入更小的文件到磁盘所需要的时间更少

    • 因为我们的数据表很多，大概在2000多张，用压缩的效率就要比mysqldump导出sql的方式更好了

  2. 管道操作效率

    • gzip压缩可以和mysqldump同时进行（管道并行处理）

    • CPU的多核能力可以同时处理dump和压缩

同时创建新的租户库的时候也可以用这种方式提高效率。

2、替换指定字符串方式优化

这一点，其实可以在dump的时候利用linux命令sed可以同步进行，优化后的代码如下

sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*PROCEDURE/PROCEDURE/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*FUNCTION/FUNCTION/

sed是Linux/Unix系统中的流编辑器命令，通常用于处理MySQL导出的SQL文件。具体使用用途如下：

  1. 修改SQL导出文件：对mysqldump导出的文件进行批量修改

  2. 移出DEFINER信息：删除存储过程、视图、函数中的DEFINER=字句

  3. 替换字符集声明：修改数据库的默认字符集设置

  4. 调整SQL语法：使导出的SQL文件兼容不同版本的MySQL

通过这两步调整之后，再来跑一下，时间很快来到了3分半就执行完了租户库的创建流程。

完整导出/处理SQL伪代码如下：

mysqldump -h -P -u -p --set-gtid-purged=OFF -R empty | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*PROCEDURE/PROCEDURE/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*FUNCTION/FUNCTION/' | gzip > empty.gz

导入SQL伪代码：

gunzip < empty.gz | mysqldump -h -P -u -p new_tenent

现在来看？

是否可以更快？

答案是肯定的，可以进行库对库复制，且可以适用perl替换sed来处理正则操作

mysqldump --single-transaction -h source_host -u user -p empty_db \
| perl -pe 's/DEFINER\s*=\s*\S+//g; s/\/\*!50013 DEFINER=\S+\*\///g' \
| mysql -h target_host -u user -p new_tenant_db

如何更安全、更稳定？

事务保障机制

try {
    $db->beginTransaction();
    
    // 1. 创建新库
    $db->exec("CREATE DATABASE new_tenant CHARACTER SET utf8mb4");
    
    // 2. 导入结构
    exec("gunzip -c empty_db.gz | mysql -u user -p new_tenant");
    
    // 3. 初始化租户数据
    $db->exec("INSERT INTO new_tenant.tenant_info (...) VALUES (...)");
    
    $db->commit();
} catch (Exception $e) {
    $db->rollBack();
    // 清理失败残留
    $db->exec("DROP DATABASE IF EXISTS new_tenant");
    throw $e;
}

增量同步方案

试想如果在导出到本地之后，原始空库发生了变化，在本地SQL文件导入创建新的租户库的时候，那原始空库的信息是不会同步到新的租户库的，所以需要增加一个同步方案来解决这种问题。解决方案如下：

  1. 为原始空库创建一个版本表

记录对原始空库的任何一个变更

CREATE TABLE schema_version (
    id INT AUTO_INCREMENT PRIMARY KEY,
    version VARCHAR(50) NOT NULL,
    sql_file VARCHAR(255) NOT NULL,
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

  1. 每次修改原始库时记录变更

INSERT INTO schema_version (version, sql_file) 
VALUES ('1.0.1', 'alter_table_add_column_xxx.sql');

  1. 导出前获取最新原始空库版本号

$currentVersion = $db->query("SELECT MAX(version) FROM `empty_db`.schema_version")->fetchColumn();

  1. 导出后导入前获取空库版本表最新的执行数据

1. 获取最新的版本数据
$newVersions = $db->query(
        "SELECT version, sql_file FROM `$baseDb`.schema_version 
         WHERE version > '$baseVersion' 
         ORDER BY installed_on ASC"
    )->fetchAll(PDO::FETCH_ASSOC);
    
// 2. 在新的租户库按版本顺序执行更新
foreach ($newVersions as $update) {
    try {
        $db->beginTransaction();

        $db->exec($update['sql_file']);

        // 记录版本变更
        $db->exec("INSERT INTO `new_tenent`.schema_version 
                   (version, sql_file)", [
            ':version' => $update['version'],
            ':sql_file' => $update['sql_file'])
        ]);

        $db->commit();
    } catch (Exception $e) {
        $db->rollBack();
        throw new Exception("应用更新 {$update['version']} 失败: " . $e->getMessage());
    }
}

以上应该就能解决增量同步问题了。

总结

优化不可能只简单的分为某个部分，既要考虑效率、又要考虑安全，世界上没有完美的方案，只有最适合业务场景的解决方案。

与君共勉~
添加新评论