Hyperf方案 慢查询监控优化

?php/** * 案例039慢查询监控优化 * 说明监听SQL执行事件记录超过阈值的慢查询输出到日志和告警 * 需要安装的包hyperf/database、hyperf/logger */declare(strict_types1);namespaceApp\Listener;useHyperf\Database\Events\QueryExecuted;useHyperf\Event\Annotation\Listener;useHyperf\Event\Contract\ListenerInterface;usePsr\Log\LoggerInterface;/** * SQL慢查询监听器 * Hyperf每次执行SQL都会触发 QueryExecuted 事件 * 注册到 config/autoload/listeners.php */#[Listener]classSlowQueryListenerimplementsListenerInterface{// 慢查询阈值超过200毫秒就算慢查询privatefloat$slowThreshold200.0;publicfunction__construct(privateLoggerInterface$logger){}// 告诉框架我要监听哪些事件publicfunctionlisten():array{return[QueryExecuted::class];}publicfunctionprocess(object$event):void{/** var QueryExecuted $event */$time$event-time;// 执行耗时单位毫秒if($time$this-slowThreshold){return;// 没超阈值不管}// 把绑定参数填回SQL里方便直接复制到MySQL客户端执行$sql$this-buildRealSql($event-sql,$event-bindings);$logData[sql$sql,time_msround($time,2),connection$event-connectionName,// 哪个数据库连接threshold$this-slowThreshold,];// 超过1秒的记error200ms-1秒记warningif($time1000){$this-logger-error(【超级慢查询】.round($time).ms,$logData);// 超级慢查询可以接入告警系统发钉钉/企业微信$this-sendAlert($logData);}else{$this-logger-warning(【慢查询】.round($time).ms,$logData);}}/** * 把SQL里的 ? 占位符替换成真实参数值 * 方便直接复制SQL去数据库里执行分析 */privatefunctionbuildRealSql(string$sql,array$bindings):string{$bindingsarray_map(function($binding){if(is_string($binding)){return.addslashes($binding).;// 字符串加引号}if($bindingnull){returnNULL;}if(is_bool($binding)){return$binding?1:0;}return$binding;},$bindings);// 把?替换成真实值foreach($bindingsas$binding){$sqlpreg_replace(/\?/,(string)$binding,$sql,1);// 一次只替换一个?}return$sql;}/** * 发告警通知这里用企业微信机器人webhook举例 */privatefunctionsendAlert(array$logData):void{// 实际项目里放到队列里发别在这里同步HTTP调用会阻塞协程\Hyperf\Utils\ApplicationContext::getContainer()-get(\Hyperf\AsyncQueue\Driver\DriverFactory::class)-get(default)-push(new\App\Job\SendSlowQueryAlertJob($logData));}}/** * SQL执行统计收集器 * 记录每个请求里执行了多少条SQL总耗时多少 * 可以帮你发现N1查询问题 */classQueryCollector{privatearray$queries[];privatefloat$totalTime0;publicfunctionrecord(QueryExecuted$event):void{$this-queries[][sql$event-sql,time$event-time,];$this-totalTime$event-time;}/** * 输出本次请求的SQL统计加在响应头里或者debug工具里 */publicfunctiongetSummary():array{$countcount($this-queries);return[query_count$count,total_msround($this-totalTime,2),avg_ms$count0?round($this-totalTime/$count,2):0,// 超过20条SQL就可能有N1问题了has_n_plus_1$count20,];}publicfunctiongetSlowQueries(float$threshold100.0):array{returnarray_filter($this-queries,fn($q)$q[time]$threshold);}}/** * 慢查询记录到独立表方便后续分析和优化 * 表结构id, sql_text, time_ms, connection, created_at */classSlowQueryRepository{publicfunctionsave(string$sql,float$timeMs,string$connection):void{\Hyperf\DbConnection\Db::table(slow_query_logs)-insert([sql_textsubstr($sql,0,5000),// 最多存5000字符time_ms(int)$timeMs,connection$connection,created_atdate(Y-m-d H:i:s),]);}/** * 查最近一天最慢的20条SQL用于每日优化排查 */publicfunctiongetTopSlowQueries(int$limit20):array{return\Hyperf\DbConnection\Db::table(slow_query_logs)-where(created_at,,date(Y-m-d 00:00:00))-orderByDesc(time_ms)-limit($limit)-get()-toArray();}}