Execute the command and paste the result below.

hf2.0.11复杂连表查询，数据库连接池持续增长，少部分会重用，大量sleep空闲状态,重启应用还会残留僵尸进程

Description:

服务配置如下

databases.php

`<?php

declare(strict_types=1);

return [
'default' => [
'driver' => env('DB_DRIVER', 'mysql'),
'read' => [
'host' => [env('DB_READ_HOST', '127.0.0.1')],
'username' => env('DB_READ_USERNAME', 'admin'),
'password' => env('DB_READ_PASSWORD', '123456'),
],
'write' => [
'host' => [env('DB_WRITE_HOST', '127.0.0.1')],
'username' => env('DB_WRITE_USERNAME', 'admin'),
'password' => env('DB_WRITE_PASSWORD', '123456'),
],
'sticky' => true,
'database' => env('DB_DATABASE', 'db_base'),
'port' => env('DB_PORT', 3306),
'charset' => env('DB_CHARSET', 'utf8'),
'collation' => env('DB_COLLATION', 'utf8_unicode_ci'),
'prefix' => env('DB_PREFIX', ''),
'pool' => [
'min_connections' => 2,
'max_connections' => 10,
'connect_timeout' => 10.0,
'wait_timeout' => 3.0,
'heartbeat' => 5,
'max_idle_time' => (float)env('DB_MAX_IDLE_TIME', 10),
],
'options' => [
PDO::ATTR_CASE => PDO::CASE_NATURAL,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_ORACLE_NULLS => PDO::NULL_NATURAL,
PDO::ATTR_STRINGIFY_FETCHES => false,
PDO::ATTR_EMULATE_PREPARES => false,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
],
'cache' => [
'handler' => \Hyperf\ModelCache\Handler\RedisHandler::class,
'cache_key' => 'base:%s:%s:%s:%s',
'prefix' => 'base',
'ttl' => 3600 * 24,
'empty_model_ttl' => 3600,
'load_script' => true
],
'commands' => [
'gen:model' => [
'path' => 'app/Model',
'force_casts' => true,
'inheritance' => 'Model',
'refresh_fillable' => true,
'table_mapping' => []
],
],
],
];
`

server.php

`<?php

declare(strict_types=1);

use Hyperf\Server\Server;
use Hyperf\Server\SwooleEvent;
return [
'mode' => SWOOLE_PROCESS,
'servers' => [
[
'name' => 'http',
'type' => Server::SERVER_HTTP,
'host' => '0.0.0.0',
'port' => 19503,
'sock_type' => SWOOLE_SOCK_TCP,
'callbacks' => [
SwooleEvent::ON_REQUEST => [Hyperf\HttpServer\Server::class, 'onRequest']
],
]
],
'settings' => [
'enable_coroutine' => true,
'worker_num' => 1,
'task_worker_num' => 1,
'task_enable_coroutine' => false,
'pid_file' => BASE_PATH.'/runtime/hyperf.pid',
'open_tcp_nodelay' => true,
'max_coroutine' => 100000,
'open_http2_protocol' => true,
'socket_buffer_size' => 2 * 1024 * 1024,
'max_wait_time' => 5,
'heartbeat_idle_time' => 120,//2分钟后没消息自动释放连接
'heartbeat_check_interval' => 60,//1分钟检测一次
'log_level' => 1,//设置Server错误日志打印的等级
'log_file' => BASE_PATH.'/runtime/logs/swoole.log',
'reload_async' => true,//异步重启
],
'callbacks' => [
SwooleEvent::ON_BEFORE_START => [Hyperf\Framework\Bootstrap\ServerStartCallback::class, 'beforeStart'],
SwooleEvent::ON_MANAGER_START => [Hyperf\Framework\Bootstrap\ManagerStartCallback::class, 'onManagerStart'],
SwooleEvent::ON_MANAGER_STOP => [Hyperf\Framework\Bootstrap\ManagerStopCallback::class, 'onManagerStop'],
SwooleEvent::ON_WORKER_START => [Hyperf\Framework\Bootstrap\WorkerStartCallback::class, 'onWorkerStart'],
SwooleEvent::ON_WORKER_STOP => [Hyperf\Framework\Bootstrap\WorkerStopCallback::class, 'onWorkerStop'],
SwooleEvent::ON_WORKER_EXIT => [Hyperf\Framework\Bootstrap\WorkerExitCallback::class, 'onWorkerExit'],
SwooleEvent::ON_CONNECT => [Hyperf\Framework\Bootstrap\ConnectCallback::class, 'onConnect'],
SwooleEvent::ON_TASK => [Hyperf\Framework\Bootstrap\TaskCallback::class, 'onTask'],
SwooleEvent::ON_FINISH => [Hyperf\Framework\Bootstrap\FinishCallback::class, 'onFinish'],
SwooleEvent::ON_PIPE_MESSAGE => [Hyperf\Framework\Bootstrap\PipeMessageCallback::class, 'onPipeMessage'],
SwooleEvent::ON_RECEIVE => [Hyperf\Framework\Bootstrap\ReceiveCallback::class, 'onReceive'],
SwooleEvent::ON_CLOSE => [Hyperf\Framework\Bootstrap\CloseCallback::class, 'onClose'],
SwooleEvent::ON_SHUTDOWN => [Hyperf\Framework\Bootstrap\ShutdownCallback::class, 'onShutdown'],
SwooleEvent::ON_WORKER_ERROR => [Hyperf\Framework\Bootstrap\WorkerErrorCallback::class, 'onWorkerError'],
],
];`

数据表及测试数据

两张表如下：

a table

CREATE TABLEa(idint(10) unsigned NOT NULL AUTO_INCREMENT,shop_namevarchar(50) NOT NULL,picvarchar(100) NOT NULL,school_idint(10) unsigned NOT NULL,is_cooperatetinyint(1) unsigned NOT NULL DEFAULT '1',is_displaytinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '是否显示,1:是,0:否',is_opentinyint(1) unsigned NOT NULL DEFAULT '1',is_deletetinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除 1:是,0:否',created_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',updated_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后更新时间',deleted_at timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT '删除时间', PRIMARY KEY (id), KEY school_id (school_id) USING BTREE, KEY is_display (is_display) USING BTREE, KEY is_cooperate (is_cooperate`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='a表';

INSERT INTO a VALUES ('2', '店铺名称2', 'http://www.baidu.com', '316', '1', '1', '1', '1', '2017-11-25 09:48:36', '2020-09-09 11:29:25', '2017-11-25 16:57:15'), ('3', '店铺名称4', 'http://www.baidu.com', '316', '1', '1', '1', '1', '2017-11-25 14:50:36', '2020-09-09 11:29:25', '2017-12-21 11:50:46');
`

b table

CREATE TABLEb(idint(10) NOT NULL AUTO_INCREMENT,shop_idint(10) unsigned NOT NULL,school_idint(10) NOT NULL,uidbigint(20) NOT NULL,order_numint(5) DEFAULT '0',is_deletetinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除 1:是,0:否',created_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',updated_attimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',deleted_at timestamp NULL DEFAULT '0000-00-00 00:00:00' COMMENT '删除时间', PRIMARY KEY (id), KEY school_id (school_id), KEY shop_id (shop_id,school_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='B表';

INSERT INTO b VALUES ('3', '219', '1899', '1287255', '1', '0', '2019-08-13 17:28:39', '2019-08-13 17:28:39', '0000-00-00 00:00:00'), ('4', '219', '1899', '2011129', '2', '0', '2019-08-13 17:28:40', '2019-08-13 17:28:40', '0000-00-00 00:00:00'), ('5', '219', '1899', '2250228', '1', '0', '2019-08-13 17:28:40', '2019-08-13 17:28:40', '0000-00-00 00:00:00'), ('6', '219', '1899', '3093271', '1', '0', '2019-08-13 17:28:40', '2019-08-13 17:28:40', '0000-00-00 00:00:00');
`

触发bug代码

`<?php
class Test{

public function recommendShopByOrderNumTest($schoolId, $uid, $num = 6)
{
    $info = Db::table('b')
        ->leftJoin('a', 'b.shop_id', '=', 'a.id')
        ->where([
            ['b.uid', '=', $uid],
            ['b.school_id', '=', $schoolId],
            ['a.is_open', '=', 1],
            ['a.is_delete', '=', 0],
            ['a.is_cooperate', '=', 1],
            ['a.is_display', '=', 1],
            ['a.is_delete', '=', 0]
        ])
        ->groupBy('b.school_id', 'b.shop_id')
        ->orderByRaw('sum(b.order_num) DESC')
        ->limit($num)
        ->get([
            'b.shop_id',
            'a.shop_name',
            'a.pic'
        ]);
    return $info;
}

}
`

并发1000次，后续低频请求，数据库连接池居高不下，持续增长，大量sleep会出现

谢谢