Featured image of post 一次慢接口引起的数据库查询测试+全自动接口测试断言
编程

一次慢接口引起的数据库查询测试+全自动接口测试断言

代码要写测试,数据库查询要谨慎

今天客户端测试接口的时候, 和我反馈了某个接口比较慢,对接的同事请假了, 我打开项目看了一下. 确实有这个问题

  • 找到了那个接口, 然后在控制器加了一个SQL打点
# 日志打点
DB::listen(fn(QueryExecuted $query) => Log::info($query->sql));
# 查看日志
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:15] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1  
[2021-07-22 12:46:16] local.INFO: select * from `albums` where `albums`.`id` = ? and `albums`.`deleted_at` is null limit 1
  • 一看这个SQL就应该能猜到是预加载没用,使用动态属性查询数据库了
  • 这是Laravel提供的一个特性, 但是也是一个隐患.幸好分页只是 20 条, 如果是上百条, 那就是一个很可怕的事情.
  • 数据库查询的伪代码如下
class Controller
{
    public function index()
    {
        $models = Model::query()->limit(20)->get();

        return ModelsResource::collection($models);
    }
}

class ModelsResource extends JsonResource
{

    public function toArray($request)
    {
        return [
            'id'            => $this->id,
            'name'          => $this->title,
            'album_id'      => $this->album_id,
            // TODO 这一行代码
            'album_name'    => $this->album->title,
        ];

    }


}
  • 缘由就是在Resource使用了album的动态属性, 然后没有提前加载, 导致了动态查询数据库
  • 解决方式很简单, 在查询的时候增加
$models = Model::query()->with('album')->limit(20)->get();

如何避免

  • 解决问题之前得发现问题, 而发现问题是一个代价很大的事情.
  • 我们应该想怎么避免此类问题, 我是这样处理的
  • 项目里已经对每一个接口进行HTTP测试(当然实际没有HTTP请求, 而是内部mock),具体可查看Laravel的测试
  • 因为框架的每一个测试类都继承了tests\TestCase.php, 所以我们改造这个类来实现我们的目的, 直接重写方法json
# 重写为
public function json($method, $uri, array $data = [], array $headers = [])
{
    // 开启 SQL 查询日志, 最大数量自定义
    $maxCount = 10;
    DB::enableQueryLog();

    $response = parent::json($method, $uri, $data, $headers);

    // 当父类模拟出框架启动流程, 并反射到响应的控制器上执行完成之后
    // 我们开始获取所有执行的 SQL 数量, 如果大于限定的, 断言失败
    $queries = collect(DB::getQueryLog())->pluck('query');
    // 这样子的确不能完全的保证, 可能某个接口就是查询比较多, 
    // 我们可以替换成去重之后的和之前的对比, 当这个差值到达一定限度断言失败
    // 因为一般使用动态属性,都是重复的 SQL
    // $queries->count() - $queries->unique()->count()
    $this->assertLessThan(
        $maxCount, 
        $queries->count(), 
        sprintf("接口:%s[%s], SQL:%d\r\n%s", $uri, $method, $maxCount, $queries->implode("\r\n"))
    );


    DB::flushQueryLog();

    return $response;
}
  • 人来写代码, 不可避免的会出现一些错误, 这时候有一个机器流程去帮我们发现这些错误, 这是一个很好的例子
  • 还可以在这里进行更多的尝试, 比如必须所有接口都进行测试, 当新增接口没写测试, 就断言失败
// 每次测试都把测试的路由存储起来
public function json($method, $uri, array $data = [], array $headers = [])
{
    $response = parent::json($method, $uri, $data, $headers);

    $route = $this->app->make(Route::class);

    // $uri 为实际的请求包含参数, 如: /users/1
    // $route->uri() 为定义的路由如: /users/{id}
    // 在这里我们可以把所有的 $route->uri() 存储到静态变量, 记得定义一下, 这里省略
    self::$testRoutes[$route->uri()][] = $method;

    return $response;
}
  • 在phpunit.xml增加一个结束的测试类, 保证我们的这个测试是最后进行的
<?xml version="1.0" encoding="UTF-8"?>
<phpunit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:noNamespaceSchemaLocation="./vendor/phpunit/phpunit/phpunit.xsd"
         bootstrap="vendor/autoload.php"
         stopOnFailure="true"
         colors="true">
    <testsuites>

        // 框架的 Unit 测试
        <testsuite name="Unit">
            <directory suffix="Test.php">./tests/Unit</directory>
        </testsuite>

        // 框架的 Feature 测试
        <testsuite name="Feature">
            <directory suffix="Test.php">./tests/Feature</directory>
        </testsuite>

        // 这里是我们新加的, 必须放在后面
        // phpunit 从上往下执行
        <testsuite name="End">
            <file>tests/EndApiTest.php</file>
        </testsuite>
    </testsuites>
</phpunit>
  • 然后新建测试类EndApiTest
<?php

namespace Tests;

use Illuminate\Routing\Router;
use Illuminate\Support\Collection;
use Illuminate\Support\Str;

class EndApiTest extends TestCase
{
    public function testRoutes()
    {
        // 代码中定义的所有路由
        $defineRouters = $this->app->make(Router::class)->getRoutes();
        // 当前类继承了 TestCase 类, 直接使用存储的路由
        self::$testRoutes;
        // 程序中可自由发挥, 取这两个数组相互比较, 如果有差集
        foreach ($defineRouters as $router) {

            // 如果不存在, 代表没有写测试, 存在滞后, 还可以取出所有 method 来判断等等
            if (! isset(self::$testRoutes[$router->uri()])) {


            }
        }
    }
}