README

一个服务提供程序，用于监控SQL语句，检查性能（EXPLAIN命令），查看实时查询，并收集SQL日志以优化它们。

安装

请通过Composer安装此包

composer require verbanent/laravel-sql-performance-guard

如果您需要更改其默认值，可以发布配置文件

php artisan vendor:publish --provider="Verbanent\SqlPerformanceGuard\SqlPerformanceGuardServiceProvider" --tag="config"

默认设置

<?php

return [
    'time_threshold' => env('SQL_TIME_THRESHOLD', 100.00),
    'key_length_threshold' => env('SQL_KEY_LENGTH_THRESHOLD', 256),
    'rows_threshold' => env('SQL_ROWS_THRESHOLD', 1000),
];

使用方法

目前，该库仅在调试模式下工作。请在您的开发环境中运行它以测试SQL查询并对SQL性能进行必要的修改。如果您看到更好的结果，请将这些更改应用到您的生产环境中。

示例

安装包并检查您的日志

tail -f storage/logs/laravel.log

通过更新您的.env文件将您的应用程序模式更改为DEBUG

APP_ENV=local
APP_DEBUG=true
LOG_CHANNEL=single
LOG_LEVEL=debug

这只是一个示例。如果您已经配置了日志，可能不需要在这里进行任何更改。

打开一个页面或运行CLI命令以触发保存调试日志。您应该看到类似以下内容

=============== EXPLAIN BEGIN ===============
SQL {"sql":"select * from `users` where `email` = 'example@example.com' limit 1"}

TABLE 1: users {"id":1,"select_type":"SIMPLE","table":"users","partitions":null,"type":"ALL","possible_keys":null,"key":null,"key_len":null,"ref":null,"rows":83289,"filtered":10.0,"Extra":"Using where"}
PASSED: TIME {"time < 100.00":66.73}
WARNING: POSSIBLE KEYS {"possible keys are null":null}
WARNING: KEY {"key is null":null}
WARNING: KEY LEN {"key len is null":null}
WARNING: KEY LEN VALUE {"key len >= 256":null}
WARNING: ROWS {"rows >= 1000":83289}
=============== EXPLAIN = END ===============

我们看到的问题是查询没有使用任何索引（键）来过滤结果。这意味着它必须通过超过80,000行才能获取结果。

基于这些知识，让我们通过添加索引来优化我们的users表

create index email_idx on users (email)

更好的了解

=============== EXPLAIN BEGIN ===============
SQL {"sql":"select * from `users` where `email` = 'example@example.com' limit 1"}

TABLE 1: users {"id":1,"select_type":"SIMPLE","table":"users","partitions":null,"type":"ref","possible_keys":"email_idx","key":"email_idx","key_len":"515","ref":"const","rows":1,"filtered":100.0,"Extra":null}
PASSED: TIME {"time < 100.00":17.88}
PASSED: POSSIBLE KEYS {"possible keys exist":"email_idx"}
PASSED: KEY {"key chosen":"email_idx"}
PASSED: KEY LEN {"key len is not null":"515"}
WARNING: KEY LEN VALUE {"key len >= 256":"515"}
PASSED: ROWS {"rows < 1000":1}
=============== EXPLAIN = END ===============

但仍然不完美。有关键长度的最后一个警告。这意味着该列包含较长的字符串，如果我们不限制它，我们的索引可能非常大。让我们根据这些信息尝试修复它

drop index email_idx on users;
create index email_idx on users (email(32));

结果显示所有测试均通过

=============== EXPLAIN BEGIN ===============
SQL {"sql":"select * from `users` where `email` = 'example@example.com' limit 1"}

TABLE 1: users {"id":1,"select_type":"SIMPLE","table":"users","partitions":null,"type":"ref","possible_keys":"email_idx","key":"email_idx","key_len":"131","ref":"const","rows":1,"filtered":100.0,"Extra":"Using where"}
PASSED: TIME {"time < 100.00":14.31}
PASSED: POSSIBLE KEYS {"possible keys exist":"email_idx"}
PASSED: KEY {"key chosen":"email_idx"}
PASSED: KEY LEN {"key len is not null":"131"}
PASSED: KEY LEN VALUE {"key len < 256":"131"}
PASSED: ROWS {"rows < 1000":1}
=============== EXPLAIN = END ===============

这是一个简单的工具，可以帮助您根据EXPLAIN命令诊断查询的问题。应在开发过程中使用。我们不推荐在生产环境中运行它。