eleven26
0%

从一次优化想到的优化方向

Posted on Edited on

本文用的 MySQL 是阿里云的 RDS，一主一从，主 1 核，从 2 核。配置很低，这不是本文讨论内容，不多说为什么。

背景

在很长一段时间的上线流程中，我们都要跑一个脚本（Laravel artisan 命令），用来对所有用户的权限做一些可能的更新操作。这个过程需要很长的时间，30w 左右的客户，19 个进程跑这些脚本短则 12 小时左右，多则一天以上。

影响

运行这个脚本的过程中，整个网站响应速度都慢了很多。查看服务器 CPU、内存 负载，没有什么压力，最后发现是 MySQL 从库 CPU 负载一直 100%（2 核的配置）。

分析

  1. 大家会说，很明显啊，升级一下 MySQL 从库，让它读得快一点就好了啊。我开始也是这么想的，从 2 核升到 8 核后，发现虽然快了，但是没有快 4 倍，后来去看主库发现，这个时候主库的 CPU 100% 了（原来平稳 30% 左右）。

  2. 现在发现了主库满载，我也想到了可以考虑升级一下主库，但是主库是包年包月的，一升就要按之前付费的时间来补足这些费用，但这些我决定不了。所以暂时放弃这个想法了。

本地测试

上一次上线的时候，也是发现了这个问题，上一次个人是想从代码层面找到优化的地方的（比如，会不会有嵌套的循环等，这个可以用 xhprof），后来把这个给忘了。

发现升级 MySQL 服务器的想法失败了，想起了自己之前写过的一个记录 SQL 语句的工具，便想着可以看看是不是有什么 SQL 执行太久了。

  1. 本地测试发现，一个脚本执行下来，有 200 个 SQL 语句，其中有很多是可以修改为批量操作的。因为 Laravel 模型的 createMany 是将关联数据一条一条插入的，所以将 createMany 修改为 insert 同时插入多条记录。

  2. 我们模型里面还用了 spatie/laravel-activitylog 这个包，这就导致，上面的批量语句有多少条，最终产生的 SQL 语句是这个数量的两倍。考虑到这个包记录的那些数据从来就没有用过，就把相关模型记录变更的操作禁用掉了

更新后的效果

把这两百个 SQL 缩减为 17 个之后，信心满满地将代码更新上去，失望地发现，感觉一点都没有快（Laravel horizon 面板 job 数量减少还是很缓慢）。然后接手这个功能的人告诉我，其实真实场景是写的情况是很少的，大部分是读语句。

本地测试的时候，打印的语句好像并没有非常慢的，但是有几个 80ms 左右的查询，个人觉得这是一个比较正常的。但事实证做优化的时候不能太感性，优化应该是有某些指标、方向指导的。

记录生成的 SQL 语句

既然去掉了 90% 的 SQL 语句都没有效，那就可能是一些语句在测试环境和生产的执行效率差别太大（生产数据多一些）。所以就写了下面的代码：

1
2
3
4
5
6
7
8
9
10
11
$cache = \Cache::get('permission');
if (!$cache) {
    enable_mySQL_log();
    enable_mongo_log();
    \Cache::put('permission', 1, 120);
}
// ... 这里省略业务代码 ...
if (!$cache) {
    disable_mySQL_log();
    disable_mongo_log();
}

这里的 enable_mySQL_log 几个函数是自定义用来记录 SQL 语句的，使用 Cache 的目的是为了只记录一次（一次就够）。

从记录的语句中，发现了有两条语句都是用了 300ms 以上，而其他语句平均时间是 3ms。但这两个语句所在的表数据不是很多（20w左右），而且只是一个简单的 join 加一些条件筛选。

到这里原因其实已经很明显了，把这两条 SQL 语句拿去 explain 一下，答案没有让我失望，type: All。

接下来的事情就很简单了，给涉及的那张表加了两个字段的独立索引，再去看 SQL 语句，300ms 降到了平均水平 3ms。

原因

MySQL join 表的时候，关联表的关联字段如果没有索引的话，会导致全表扫描。

回到测试环境加上索引后测试发现，原来 80ms 的语句，现在只需要 10ms 左右。

其他可以优化的地方

因为我们的脚本是扫描全表做处理的，所以可以考虑将里面的大部分查询转换为批量查询，查询到结果之后再进行业务逻辑处理。

总而言之，可以批量就不要分开操作。

反思总结

  1. 优化的时候如果找不到方向，不妨可以看看产生的 SQL 语句，可能只是某个表忘记加索引了。

  2. 关于 spatie/laravel-activitylog，虽然我们从来没有用过，但是它一直在记录，感觉如果从来不需要去看模型变更记录的话，还不如不要。

  3. 关于定位系统性能问题，上一次上线的时候，虽然想从代码着手，但是并没有发现有什么特别影响性能的地方。经过这次经历发现，发现系统有性能的时候，靠谱的做法是先定位到哪里产生了性能的问题。因为这个系统从开始到现在一直很多地方都有一些性能问题，另外一方面觉得那一部分业务负责，所以觉得慢是正常的。这些都是非常感性的想法，非常有害的，这会导致我们做不出正确的判断。

正确的做法是，从各方面去定位原因，比如应用服务器负载、数据库服务器负载，如果有某一个方面到达了瓶颈的话，马上升级并不是一个明智的做法，我们需要明确的知道是不是服务器不够用，还是代码写得有问题，如果最终确定的确是服务器支撑不住的话，再考虑升级。因为有些严重的性能问题，带来的性能下降是指数级的，单靠升级服务器要付出很大的成本。

如果我们遇到问题就凭感觉来断定的话，往往导致走很多弯路（走就算了，更坏的结果是到最后问题也还是没有解决）。

  1. 那些你觉得理所当然的未必就是理所当然的。这个问题从出现到解决经历的周期说实话非常长了，接手它的人和大家其实一致觉得，是因为里面业务逻辑复杂，可能需要的查询很多，处理很多东西。但事实证明，事情不像表明看到的那样。