第4章：数据库性能核心——查询优化与架构调整
章节介绍
数据库性能往往是决定PHP应用快慢的生命线。无论你的代码逻辑多么优雅，一旦数据库响应缓慢，整个应用都会陷入等待。我们这章要解决的，就是这个核心问题：如何优化查询，以及何时调整架构。
想象一个典型的场景：你的页面加载突然变慢，经过排查，发现是某个商品列表页的SQL查询花费了好几秒钟。问题出在哪？可能是查询语句写得不够高效，比如在WHERE子句里对未索引的列进行条件筛选，导致全表扫描。也可能是因为表结构设计之初考虑不周，随着数据量增长，原有的单表结构已经不堪重负。
查询优化是解决性能问题的第一线。它关注于如何让单条SQL语句执行得更快。作为PHP开发者，我们的职责不仅仅是拼接SQL字符串。你需要学会审视你的查询，理解它的执行计划。一个常见的优化手段是确保高频率查询条件所使用的列已被索引。你可以利用 `
<?php
function createIndexIfNotExists(PDO $pdo, string $table, string $column, string $indexType = 'INDEX'): bool
{
// 检查索引是否已存在
$checkSql = "SHOW INDEX FROM {$table} WHERE Column_name = :column";
$stmt = $pdo->prepare($checkSql);
$stmt->execute([':column' => $column]);
if ($stmt->rowCount() > 0) {
return false; // 索引已存在
}
// 创建索引
$indexName = "idx_{$table}_{$column}";
$sql = "ALTER TABLE {$table} ADD {$indexType} {$indexName} ({$column})";
try {
$pdo->exec($sql);
return true;
} catch (PDOException $e) {
throw new RuntimeException("创建索引失败: " . $e->getMessage());
}
}
函数，安全地为指定列创建索引，避免重复创建的错误。当一个查询慢下来时，
<?php
function optimizeSlowQuery(PDO $pdo, string $sql): array
{
$optimizations = [];
// 检查是否缺少索引
if (preg_match('/WHERE\s+([\w.]+)/i', $sql, $matches)) {
$column = $matches[1];
$optimizations[] = "建议为列 '{$column}' 添加索引以提高查询性能";
}
// 检查是否使用了SELECT *
if (preg_match('/SELECT\s+\*\s+FROM/i', $sql)) {
$optimizations[] = "建议避免使用 SELECT *，只选择需要的列";
}
// 检查是否有子查询可以优化
if (preg_match('/\(\s*SELECT/i', $sql)) {
$optimizations[] = "考虑将子查询转换为JOIN以提高性能";
}
// 检查ORDER BY是否可以使用索引
if (preg_match('/ORDER BY\s+([\w.]+)\s+(ASC|DESC)/i', $sql, $matches)) {
$optimizations[] = "确保 ORDER BY 列 '{$matches[1]}' 上有合适的索引";
}
return [
'original_sql' => $sql,
'optimization_suggestions' => $optimizations,
'checklist' => [
'indexes_exist' => count($optimizations) > 0,
'select_columns_optimized' => !preg_match('/SELECT\s+\*\s+FROM/i', $sql),
'no_unnecessary_joins' => substr_count($sql, 'JOIN') <= 3,
]
];
}
` 函数能帮你分析瓶颈所在，它可能会告诉你缺少某个关键索引，或者建议你重写一个更高效的JOIN方式。
但有时候，问题比一条查询语句更深。当一个表达到千万级甚至亿级行数时，再好的索引可能也力不从心；或者当应用的读写比例极高时，单台数据库服务器会成为瓶颈。这时，就需要考虑架构调整。调整架构意味着改变数据的组织和管理方式。例如，将历史数据从活跃的主表中分离出去（分表），或者设置一个只读的数据库副本来分担查询压力（读写分离）。在考虑这类调整前，你需要清晰了解当前的架构现状。`
<?php
function analyzeDatabaseSchema(PDO $pdo, string $database = null): array
{
if ($database !== null) {
try {
// 切换到指定数据库
$pdo->exec("USE `" . str_replace('`', '``', $database) . "`");
} catch (PDOException $e) {
throw new RuntimeException("无法切换到数据库 '{$database}': " . $e->getMessage());
}
}
try {
// 获取当前数据库名称
$stmt = $pdo->query("SELECT DATABASE()");
$currentDb = $stmt->fetchColumn();
if ($currentDb === false) {
throw new RuntimeException("无法获取当前数据库信息");
}
$result = [
'database' => $currentDb,
'tables' => []
];
// 获取所有表名
$stmt = $pdo->query("SHOW TABLES");
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($tables as $table) {
$tableInfo = [
'name' => $table,
'columns' => [],
'indexes' => [],
'engine' => '',
'collation' => '',
'row_count' => 0,
'data_size' => 0,
'index_size' => 0
];
// 获取表结构信息
$stmt = $pdo->query("SHOW CREATE TABLE `" . str_replace('`', '``', $table) . "`");
$createTable = $stmt->fetch(PDO::FETCH_ASSOC);
// 获取表状态信息
$stmt = $pdo->query("SHOW TABLE STATUS LIKE '" . str_replace("'", "''", $table) . "'");
$tableStatus = $stmt->fetch(PDO::FETCH_ASSOC);
if ($tableStatus) {
$tableInfo['engine'] = $tableStatus['Engine'] ?? '';
$tableInfo['collation'] = $tableStatus['Collation'] ?? '';
$tableInfo['row_count'] = (int)($tableStatus['Rows'] ?? 0);
$tableInfo['data_size'] = (int)($tableStatus['Data_length'] ?? 0);
$tableInfo['index_size'] = (int)($tableStatus['Index_length'] ?? 0);
}
// 获取列信息
$stmt = $pdo->query("DESCRIBE `" . str_replace('`', '``', $table) . "`");
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($columns as $column) {
$tableInfo['columns'][] = [
'name' => $column['Field'],
'type' => $column['Type'],
'null' => $column['Null'] === 'YES',
'key' => $column['Key'],
'default' => $column['Default'],
'extra' => $column['Extra']
];
}
// 获取索引信息
$stmt = $pdo->query("SHOW INDEX FROM `" . str_replace('`', '``', $table) . "`");
$indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
$indexMap = [];
foreach ($indexes as $index) {
$indexName = $index['Key_name'];
if (!isset($indexMap[$indexName])) {
$indexMap[$indexName] = [
'name' => $indexName,
'columns' => [],
'unique' => $index['Non_unique'] == 0,
'type' => $index['Index_type'],
'primary' => $indexName === 'PRIMARY'
];
}
$indexMap[$indexName]['columns'][] = $index['Column_name'];
}
$tableInfo['indexes'] = array_values($indexMap);
$result['tables'][$table] = $tableInfo;
}
// 计算统计信息
$result['statistics'] = [
'table_count' => count($tables),
'total_rows' => array_sum(array_column(array_column($result['tables'], 'row_count'), null)),
'total_data_size' => array_sum(array_column(array_column($result['tables'], 'data_size'), null)),
'total_index_size' => array_sum(array_column(array_column($result['tables'], 'index_size'), null))
];
return $result;
} catch (PDOException $e) {
throw new RuntimeException("数据库架构分析失败: " . $e->getMessage());
}
}
` 函数可以为你提供数据库内所有表的结构、索引和大小信息，这是决策的基础。
那么，在PHP项目中具体怎么做呢？首先是建立监控。你不能优化一个你无法测量的东西。定期使用 `
<?php
function getSlowQueries(PDO $pdo, int $limit = 10): array
{
$sql = "SELECT
query_time,
lock_time,
rows_sent,
rows_examined,
db,
sql_text,
start_time
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT :limit";
try {
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
// 如果mysql.slow_log表不存在，使用performance_schema替代
$altSql = "SELECT
SQL_TEXT,
TIMER_WAIT/1000000000 as query_time_seconds,
LOCK_TIME/1000000000 as lock_time_seconds,
ROWS_SENT,
ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT :limit";
$stmt = $pdo->prepare($altSql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
获取慢查询日志，或者对关键业务接口使用
<?php
function analyzeQueryPerformance(PDO $pdo, string $sql, array $params = []): array
{
$startTime = microtime(true);
$stmt = executeQuery($pdo, $sql, $params);
$result = $stmt->fetchAll();
$endTime = microtime(true);
$executionTime = $endTime - $startTime;
// 获取查询解释计划
$explainSql = "EXPLAIN " . $sql;
$explainStmt = executeQuery($pdo, $explainSql, $params);
$explainResult = $explainStmt->fetchAll();
return [
'execution_time' => round($executionTime * 1000, 2), // 毫秒
'row_count' => count($result),
'explain_plan' => $explainResult,
'memory_usage' => memory_get_usage(true),
'peak_memory_usage' => memory_get_peak_usage(true),
];
}
进行性能分析。将收集到的数据通过
<?php
function generateQueryReport(PDO $pdo, array $queryIds): array
{
$report = [
'total_queries' => count($queryIds),
'queries' => [],
'summary' => [
'total_execution_time' => 0,
'average_execution_time' => 0,
'slowest_query_time' => 0,
'fastest_query_time' => PHP_FLOAT_MAX,
],
];
foreach ($queryIds as $queryId) {
// 这里假设有一个查询历史表，实际使用时需要调整
$sql = "SELECT * FROM query_history WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $queryId]);
$queryData = $stmt->fetch(PDO::FETCH_ASSOC);
if ($queryData) {
$performanceData = analyzeQueryPerformance($pdo, $queryData['sql'], json_decode($queryData['params'] ?? '[]', true));
$optimizationSuggestions = optimizeSlowQuery($pdo, $queryData['sql']);
$queryReport = [
'id' => $queryId,
'sql' => $queryData['sql'],
'execution_time' => $performanceData['execution_time'],
'row_count' => $performanceData['row_count'],
'explain_plan' => $performanceData['explain_plan'],
'optimization_suggestions' => $optimizationSuggestions['optimization_suggestions'],
'executed_at' => $queryData['executed_at'] ?? date('Y-m-d H:i:s'),
];
$report['queries'][] = $queryReport;
// 更新统计信息
$report['summary']['total_execution_time'] += $performanceData['execution_time'];
$report['summary']['slowest_query_time'] = max($report['summary']['slowest_query_time'], $performanceData['execution_time']);
$report['summary']['fastest_query_time'] = min($report['summary']['fastest_query_time'], $performanceData['execution_time']);
}
}
if (count($report['queries']) > 0) {
$report['summary']['average_execution_time'] =
$report['summary']['total_execution_time'] / count($report['queries']);
} else {
$report['summary']['fastest_query_time'] = 0;
}
return $report;
}
生成报告，并用
<?php
function formatPerformanceReport(array $report, string $format = 'html'): string
{
// 检查格式参数是否有效
if (!in_array($format, ['html', 'text'])) {
throw new InvalidArgumentException('格式参数必须为"html"或"text"');
}
// 检查报告数据是否为空
if (empty($report)) {
return $format === 'html'
? '<div class="performance-report empty">无性能数据</div>'
: '无性能数据';
}
if ($format === 'html') {
// HTML格式输出
$output = '<div class="performance-report">';
$output .= '<h3>性能报告</h3>';
$output .= '<table class="performance-table">';
$output .= '<thead><tr><th>指标</th><th>值</th><th>单位</th></tr></thead>';
$output .= '<tbody>';
foreach ($report as $key => $data) {
// 确保每个数据项都有必要的字段
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$safeKey = htmlspecialchars($key, ENT_QUOTES, 'UTF-8');
$safeValue = htmlspecialchars($data['value'], ENT_QUOTES, 'UTF-8');
$safeUnit = htmlspecialchars($data['unit'], ENT_QUOTES, 'UTF-8');
// 为关键指标添加高亮样式
$highlightClass = '';
if (isset($data['critical']) && $data['critical']) {
$highlightClass = ' class="critical"';
}
$output .= "<tr{$highlightClass}>";
$output .= "<td>{$safeKey}</td>";
$output .= "<td>{$safeValue}</td>";
$output .= "<td>{$safeUnit}</td>";
$output .= '</tr>';
}
$output .= '</tbody>';
$output .= '</table>';
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$safeSummary = htmlspecialchars($report['summary'], ENT_QUOTES, 'UTF-8');
$output .= '<div class="summary">';
$output .= "<p><strong>总结:</strong> {$safeSummary}</p>";
$output .= '</div>';
}
$output .= '</div>';
return $output;
} else {
// 文本格式输出
$output = "=== 性能报告 ===\n\n";
foreach ($report as $key => $data) {
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$line = "{$key}: {$data['value']} {$data['unit']}";
// 为关键指标添加标记
if (isset($data['critical']) && $data['critical']) {
$line .= " [重要]";
}
$output .= $line . "\n";
}
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$output .= "\n总结: {$report['summary']}\n";
}
$output .= "\n================";
return $output;
}
}
` 格式化成团队易于阅读的格式，这能让问题一目了然。
其次，引入缓存层。对于那些频繁读取但很少变更的数据，反复查询数据库是一种浪费。`
<?php
function implementQueryCaching(string $key, callable $queryCallback, int $ttl = 3600): mixed
{
static $cache = null;
// 初始化缓存（这里使用简单的数组缓存，生产环境应使用Redis等）
if ($cache === null) {
$cache = [];
}
// 检查缓存中是否存在有效的缓存项
if (isset($cache[$key])) {
$cacheData = $cache[$key];
// 检查是否过期（时间戳方式）
if ($cacheData['expire'] > time()) {
// 缓存命中，返回缓存数据
return $cacheData['data'];
} else {
// 缓存已过期，删除该项
unset($cache[$key]);
}
}
try {
// 执行查询回调函数获取数据
$result = $queryCallback();
// 将结果存入缓存，记录过期时间
$cache[$key] = [
'data' => $result,
'expire' => time() + $ttl
];
return $result;
} catch (Exception $e) {
// 查询失败时，不缓存错误结果
// 可以选择记录日志或重新抛出异常
error_log("查询缓存失败: " . $e->getMessage());
throw $e; // 重新抛出异常，让调用者处理
}
}
` 函数提供了一个现成的缓存包装器。你可以用它把数据库查询结果缓存到Redis或Memcached中，在缓存有效期内，后续请求将直接获取缓存数据，数据库的压力将显著降低。
最后，将优化作为持续过程。数据库性能不是一次性的任务。随着业务增长和数据变化，今天高效的查询明天可能就会变慢。你需要建立一种机制，让性能分析和优化成为开发周期的一部分。
这一章的内容，就是将你从一个只会执行查询的PHP开发者，武装成一个能够诊断和根治数据库性能问题的工程师。我们会直接使用那些工具函数，它们不是理论，而是你马上就能用到项目中的武器。
核心概念
性能调优常常从数据库开始，因为数据查询的耗时往往是Web应用中最主要的瓶颈。当页面响应变慢，十有八九是背后某条SQL语句出了问题。查询优化与架构调整的核心，就是让数据库用尽可能少的资源、尽可能快的方式，返回我们需要的数据。
你可能会想，我的查询逻辑没错，数据也回来了，怎么优化？关键在于细节。数据库处理查询的方式和我们写代码的逻辑不太一样。它需要解析SQL，决定如何读取数据表，是否使用索引，如何连接多个表。这个过程称为“查询执行计划”。优化的本质，就是引导数据库生成一个更高效的计划。
最直接的起点，是找到那些拖慢系统的“元凶”——慢查询。与其猜测，不如直接查看数据库的记录。使用 `
<?php
function getSlowQueries(PDO $pdo, int $limit = 10): array
{
$sql = "SELECT
query_time,
lock_time,
rows_sent,
rows_examined,
db,
sql_text,
start_time
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT :limit";
try {
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
// 如果mysql.slow_log表不存在，使用performance_schema替代
$altSql = "SELECT
SQL_TEXT,
TIMER_WAIT/1000000000 as query_time_seconds,
LOCK_TIME/1000000000 as lock_time_seconds,
ROWS_SENT,
ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT :limit";
$stmt = $pdo->prepare($altSql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
` 可以获取一段时间内执行最慢的SQL语句列表。这就像一份性能诊断书，告诉你应该优先处理哪里。
找到慢查询后，我们需要深入分析它为什么慢。是扫描了太多行数据？还是进行了复杂的文件排序？这时候， `
<?php
function analyzeQueryPerformance(PDO $pdo, string $sql, array $params = []): array
{
$startTime = microtime(true);
$stmt = executeQuery($pdo, $sql, $params);
$result = $stmt->fetchAll();
$endTime = microtime(true);
$executionTime = $endTime - $startTime;
// 获取查询解释计划
$explainSql = "EXPLAIN " . $sql;
$explainStmt = executeQuery($pdo, $explainSql, $params);
$explainResult = $explainStmt->fetchAll();
return [
'execution_time' => round($executionTime * 1000, 2), // 毫秒
'row_count' => count($result),
'explain_plan' => $explainResult,
'memory_usage' => memory_get_usage(true),
'peak_memory_usage' => memory_get_peak_usage(true),
];
}
就很有用。它不仅能返回查询结果，还能提供执行时间、影响行数等关键指标。更进一步，
<?php
function optimizeSlowQuery(PDO $pdo, string $sql): array
{
$optimizations = [];
// 检查是否缺少索引
if (preg_match('/WHERE\s+([\w.]+)/i', $sql, $matches)) {
$column = $matches[1];
$optimizations[] = "建议为列 '{$column}' 添加索引以提高查询性能";
}
// 检查是否使用了SELECT *
if (preg_match('/SELECT\s+\*\s+FROM/i', $sql)) {
$optimizations[] = "建议避免使用 SELECT *，只选择需要的列";
}
// 检查是否有子查询可以优化
if (preg_match('/\(\s*SELECT/i', $sql)) {
$optimizations[] = "考虑将子查询转换为JOIN以提高性能";
}
// 检查ORDER BY是否可以使用索引
if (preg_match('/ORDER BY\s+([\w.]+)\s+(ASC|DESC)/i', $sql, $matches)) {
$optimizations[] = "确保 ORDER BY 列 '{$matches[1]}' 上有合适的索引";
}
return [
'original_sql' => $sql,
'optimization_suggestions' => $optimizations,
'checklist' => [
'indexes_exist' => count($optimizations) > 0,
'select_columns_optimized' => !preg_match('/SELECT\s+\*\s+FROM/i', $sql),
'no_unnecessary_joins' => substr_count($sql, 'JOIN') <= 3,
]
];
}
` 可以尝试分析这条SQL，给出诸如“建议在某个字段上添加索引”或“避免在WHERE子句中使用函数”的具体建议。
谈到优化，索引是绕不开的话题。它就像是书籍的目录，让数据库无需翻阅整本书（全表扫描）就能找到需要的数据。但索引不是随便加的。你需要知道现有的索引情况， `
<?php
function getTableIndexInfo(PDO $pdo, string $table): array
{
$sql = "SHOW INDEX FROM {$table}";
$stmt = $pdo->query($sql);
$indexes = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$indexName = $row['Key_name'];
if (!isset($indexes[$indexName])) {
$indexes[$indexName] = [
'name' => $indexName,
'type' => $row['Index_type'],
'unique' => $row['Non_unique'] == 0,
'columns' => [],
'cardinality' => $row['Cardinality'],
];
}
$indexes[$indexName]['columns'][] = [
'column_name' => $row['Column_name'],
'sub_part' => $row['Sub_part'],
'collation' => $row['Collation'],
];
}
return array_values($indexes);
}
可以帮助你查看指定表的所有索引信息，避免创建重复或无效的索引。当确认某个频繁用于查询条件（WHERE）、排序（ORDER BY）或连接（JOIN）的字段缺少索引时，再使用
<?php
function createIndexIfNotExists(PDO $pdo, string $table, string $column, string $indexType = 'INDEX'): bool
{
// 检查索引是否已存在
$checkSql = "SHOW INDEX FROM {$table} WHERE Column_name = :column";
$stmt = $pdo->prepare($checkSql);
$stmt->execute([':column' => $column]);
if ($stmt->rowCount() > 0) {
return false; // 索引已存在
}
// 创建索引
$indexName = "idx_{$table}_{$column}";
$sql = "ALTER TABLE {$table} ADD {$indexType} {$indexName} ({$column})";
try {
$pdo->exec($sql);
return true;
} catch (PDOException $e) {
throw new RuntimeException("创建索引失败: " . $e->getMessage());
}
}
` 来创建。记住，索引虽然大幅提升查询速度，但会增加数据插入、更新和删除的开销，并占用额外存储空间。这是一个需要权衡的设计。
有时，问题不在单条查询，而在于整个数据库的架构设计。表结构是否合理？关联关系是否清晰？有没有冗余字段？调用 `
<?php
function analyzeDatabaseSchema(PDO $pdo, string $database = null): array
{
if ($database !== null) {
try {
// 切换到指定数据库
$pdo->exec("USE `" . str_replace('`', '``', $database) . "`");
} catch (PDOException $e) {
throw new RuntimeException("无法切换到数据库 '{$database}': " . $e->getMessage());
}
}
try {
// 获取当前数据库名称
$stmt = $pdo->query("SELECT DATABASE()");
$currentDb = $stmt->fetchColumn();
if ($currentDb === false) {
throw new RuntimeException("无法获取当前数据库信息");
}
$result = [
'database' => $currentDb,
'tables' => []
];
// 获取所有表名
$stmt = $pdo->query("SHOW TABLES");
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($tables as $table) {
$tableInfo = [
'name' => $table,
'columns' => [],
'indexes' => [],
'engine' => '',
'collation' => '',
'row_count' => 0,
'data_size' => 0,
'index_size' => 0
];
// 获取表结构信息
$stmt = $pdo->query("SHOW CREATE TABLE `" . str_replace('`', '``', $table) . "`");
$createTable = $stmt->fetch(PDO::FETCH_ASSOC);
// 获取表状态信息
$stmt = $pdo->query("SHOW TABLE STATUS LIKE '" . str_replace("'", "''", $table) . "'");
$tableStatus = $stmt->fetch(PDO::FETCH_ASSOC);
if ($tableStatus) {
$tableInfo['engine'] = $tableStatus['Engine'] ?? '';
$tableInfo['collation'] = $tableStatus['Collation'] ?? '';
$tableInfo['row_count'] = (int)($tableStatus['Rows'] ?? 0);
$tableInfo['data_size'] = (int)($tableStatus['Data_length'] ?? 0);
$tableInfo['index_size'] = (int)($tableStatus['Index_length'] ?? 0);
}
// 获取列信息
$stmt = $pdo->query("DESCRIBE `" . str_replace('`', '``', $table) . "`");
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($columns as $column) {
$tableInfo['columns'][] = [
'name' => $column['Field'],
'type' => $column['Type'],
'null' => $column['Null'] === 'YES',
'key' => $column['Key'],
'default' => $column['Default'],
'extra' => $column['Extra']
];
}
// 获取索引信息
$stmt = $pdo->query("SHOW INDEX FROM `" . str_replace('`', '``', $table) . "`");
$indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
$indexMap = [];
foreach ($indexes as $index) {
$indexName = $index['Key_name'];
if (!isset($indexMap[$indexName])) {
$indexMap[$indexName] = [
'name' => $indexName,
'columns' => [],
'unique' => $index['Non_unique'] == 0,
'type' => $index['Index_type'],
'primary' => $indexName === 'PRIMARY'
];
}
$indexMap[$indexName]['columns'][] = $index['Column_name'];
}
$tableInfo['indexes'] = array_values($indexMap);
$result['tables'][$table] = $tableInfo;
}
// 计算统计信息
$result['statistics'] = [
'table_count' => count($tables),
'total_rows' => array_sum(array_column(array_column($result['tables'], 'row_count'), null)),
'total_data_size' => array_sum(array_column(array_column($result['tables'], 'data_size'), null)),
'total_index_size' => array_sum(array_column(array_column($result['tables'], 'index_size'), null))
];
return $result;
} catch (PDOException $e) {
throw new RuntimeException("数据库架构分析失败: " . $e->getMessage());
}
}
` 可以帮你从整体上审视数据库，了解各个表的结构、索引和大概的数据量。这有助于发现更深层次的问题，比如是否需要将一个大表拆分为多个小表（分表），或者是否可以对某些很少更新的表进行反范式化设计以减少关联查询。
除了优化查询本身，另一个核心策略是减少查询的次数——这就是缓存。对于查询频繁但更新不频繁的数据（如商品分类、城市列表、配置信息），每次请求都去查数据库是一种浪费。`
<?php
function implementQueryCaching(string $key, callable $queryCallback, int $ttl = 3600): mixed
{
static $cache = null;
// 初始化缓存（这里使用简单的数组缓存，生产环境应使用Redis等）
if ($cache === null) {
$cache = [];
}
// 检查缓存中是否存在有效的缓存项
if (isset($cache[$key])) {
$cacheData = $cache[$key];
// 检查是否过期（时间戳方式）
if ($cacheData['expire'] > time()) {
// 缓存命中，返回缓存数据
return $cacheData['data'];
} else {
// 缓存已过期，删除该项
unset($cache[$key]);
}
}
try {
// 执行查询回调函数获取数据
$result = $queryCallback();
// 将结果存入缓存，记录过期时间
$cache[$key] = [
'data' => $result,
'expire' => time() + $ttl
];
return $result;
} catch (Exception $e) {
// 查询失败时，不缓存错误结果
// 可以选择记录日志或重新抛出异常
error_log("查询缓存失败: " . $e->getMessage());
throw $e; // 重新抛出异常，让调用者处理
}
}
` 提供了一种缓存机制。它的思路是：当需要数据时，先检查缓存（如Memcached或Redis）中是否存在；如果存在且未过期，直接返回；如果不存在，则执行查询回调函数获取数据，并将结果存入缓存供后续使用。这能极大减轻数据库的重复读取压力。
在着手优化之前，确保数据库连接本身是健康高效的也很重要。一个配置不当的连接或网络波动会抵消所有查询优化的努力。`
<?php
function validateDatabaseConnection(PDO $pdo): array
{
$checks = [];
try {
// 检查连接是否活跃
$startTime = microtime(true);
$pdo->query('SELECT 1');
$pingTime = (microtime(true) - $startTime) * 1000;
$checks['connection_ping'] = [
'status' => 'success',
'message' => '数据库连接正常',
'ping_time_ms' => round($pingTime, 2),
];
// 检查数据库版本
$version = $pdo->query('SELECT VERSION()')->fetchColumn();
$checks['database_version'] = [
'status' => 'success',
'message' => "数据库版本: {$version}",
'version' => $version,
];
// 检查当前连接数
$connections = $pdo->query('SHOW STATUS LIKE "Threads_connected"')->fetchColumn(1);
$maxConnections = $pdo->query('SHOW VARIABLES LIKE "max_connections"')->fetchColumn(1);
$checks['connection_count'] = [
'status' => $connections < ($maxConnections * 0.8) ? 'success' : 'warning',
'message' => "当前连接数: {$connections}/{$maxConnections}",
'current' => (int)$connections,
'max' => (int)$maxConnections,
'usage_percentage' => round(($connections / $maxConnections) * 100, 2),
];
} catch (PDOException $e) {
$checks['connection_ping'] = [
'status' => 'error',
'message' => '数据库连接失败: ' . $e->getMessage(),
];
}
return $checks;
}
` 可以用来检查连接的可用性、配置参数和基本性能，这是性能调优一个可靠的基础步骤。
最后，将分析结果清晰地呈现出来，有助于团队沟通和持续改进。`
<?php
function generateQueryReport(PDO $pdo, array $queryIds): array
{
$report = [
'total_queries' => count($queryIds),
'queries' => [],
'summary' => [
'total_execution_time' => 0,
'average_execution_time' => 0,
'slowest_query_time' => 0,
'fastest_query_time' => PHP_FLOAT_MAX,
],
];
foreach ($queryIds as $queryId) {
// 这里假设有一个查询历史表，实际使用时需要调整
$sql = "SELECT * FROM query_history WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $queryId]);
$queryData = $stmt->fetch(PDO::FETCH_ASSOC);
if ($queryData) {
$performanceData = analyzeQueryPerformance($pdo, $queryData['sql'], json_decode($queryData['params'] ?? '[]', true));
$optimizationSuggestions = optimizeSlowQuery($pdo, $queryData['sql']);
$queryReport = [
'id' => $queryId,
'sql' => $queryData['sql'],
'execution_time' => $performanceData['execution_time'],
'row_count' => $performanceData['row_count'],
'explain_plan' => $performanceData['explain_plan'],
'optimization_suggestions' => $optimizationSuggestions['optimization_suggestions'],
'executed_at' => $queryData['executed_at'] ?? date('Y-m-d H:i:s'),
];
$report['queries'][] = $queryReport;
// 更新统计信息
$report['summary']['total_execution_time'] += $performanceData['execution_time'];
$report['summary']['slowest_query_time'] = max($report['summary']['slowest_query_time'], $performanceData['execution_time']);
$report['summary']['fastest_query_time'] = min($report['summary']['fastest_query_time'], $performanceData['execution_time']);
}
}
if (count($report['queries']) > 0) {
$report['summary']['average_execution_time'] =
$report['summary']['total_execution_time'] / count($report['queries']);
} else {
$report['summary']['fastest_query_time'] = 0;
}
return $report;
}
可以为你感兴趣的查询生成详细报告，而
<?php
function formatPerformanceReport(array $report, string $format = 'html'): string
{
// 检查格式参数是否有效
if (!in_array($format, ['html', 'text'])) {
throw new InvalidArgumentException('格式参数必须为"html"或"text"');
}
// 检查报告数据是否为空
if (empty($report)) {
return $format === 'html'
? '<div class="performance-report empty">无性能数据</div>'
: '无性能数据';
}
if ($format === 'html') {
// HTML格式输出
$output = '<div class="performance-report">';
$output .= '<h3>性能报告</h3>';
$output .= '<table class="performance-table">';
$output .= '<thead><tr><th>指标</th><th>值</th><th>单位</th></tr></thead>';
$output .= '<tbody>';
foreach ($report as $key => $data) {
// 确保每个数据项都有必要的字段
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$safeKey = htmlspecialchars($key, ENT_QUOTES, 'UTF-8');
$safeValue = htmlspecialchars($data['value'], ENT_QUOTES, 'UTF-8');
$safeUnit = htmlspecialchars($data['unit'], ENT_QUOTES, 'UTF-8');
// 为关键指标添加高亮样式
$highlightClass = '';
if (isset($data['critical']) && $data['critical']) {
$highlightClass = ' class="critical"';
}
$output .= "<tr{$highlightClass}>";
$output .= "<td>{$safeKey}</td>";
$output .= "<td>{$safeValue}</td>";
$output .= "<td>{$safeUnit}</td>";
$output .= '</tr>';
}
$output .= '</tbody>';
$output .= '</table>';
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$safeSummary = htmlspecialchars($report['summary'], ENT_QUOTES, 'UTF-8');
$output .= '<div class="summary">';
$output .= "<p><strong>总结:</strong> {$safeSummary}</p>";
$output .= '</div>';
}
$output .= '</div>';
return $output;
} else {
// 文本格式输出
$output = "=== 性能报告 ===\n\n";
foreach ($report as $key => $data) {
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$line = "{$key}: {$data['value']} {$data['unit']}";
// 为关键指标添加标记
if (isset($data['critical']) && $data['critical']) {
$line .= " [重要]";
}
$output .= $line . "\n";
}
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$output .= "\n总结: {$report['summary']}\n";
}
$output .= "\n================";
return $output;
}
}
` 则能将生硬的数组数据转换成易于阅读的HTML或文本格式，让性能瓶颈一目了然。
这些核心概念和工具，构成了一个从发现、分析、优化到验证的完整闭环。它不是一次性的任务，而应成为开发流程中的常态。从一个缓慢的查询开始，运用这些方法，你就能系统地提升整个应用的数据库性能。
实践应用
数据库性能调优不是纸上谈兵，关键在于将理论融入日常开发流程。我们从一个常见场景开始：应用响应变慢，初步判断瓶颈在数据库。这时你需要系统地定位问题。
第一步是收集证据。盲目猜测没有意义，直接从数据库获取真实的慢查询记录。
<?php
function getSlowQueries(PDO $pdo, int $limit = 10): array
{
$sql = "SELECT
query_time,
lock_time,
rows_sent,
rows_examined,
db,
sql_text,
start_time
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT :limit";
try {
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
// 如果mysql.slow_log表不存在，使用performance_schema替代
$altSql = "SELECT
SQL_TEXT,
TIMER_WAIT/1000000000 as query_time_seconds,
LOCK_TIME/1000000000 as lock_time_seconds,
ROWS_SENT,
ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT :limit";
$stmt = $pdo->prepare($altSql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
可以帮你列出最近执行最耗时的SQL语句。拿到这些SQL后，你需要深入分析每一条。简单地看一眼SQL很难发现问题，
<?php
function optimizeSlowQuery(PDO $pdo, string $sql): array
{
$optimizations = [];
// 检查是否缺少索引
if (preg_match('/WHERE\s+([\w.]+)/i', $sql, $matches)) {
$column = $matches[1];
$optimizations[] = "建议为列 '{$column}' 添加索引以提高查询性能";
}
// 检查是否使用了SELECT *
if (preg_match('/SELECT\s+\*\s+FROM/i', $sql)) {
$optimizations[] = "建议避免使用 SELECT *，只选择需要的列";
}
// 检查是否有子查询可以优化
if (preg_match('/\(\s*SELECT/i', $sql)) {
$optimizations[] = "考虑将子查询转换为JOIN以提高性能";
}
// 检查ORDER BY是否可以使用索引
if (preg_match('/ORDER BY\s+([\w.]+)\s+(ASC|DESC)/i', $sql, $matches)) {
$optimizations[] = "确保 ORDER BY 列 '{$matches[1]}' 上有合适的索引";
}
return [
'original_sql' => $sql,
'optimization_suggestions' => $optimizations,
'checklist' => [
'indexes_exist' => count($optimizations) > 0,
'select_columns_optimized' => !preg_match('/SELECT\s+\*\s+FROM/i', $sql),
'no_unnecessary_joins' => substr_count($sql, 'JOIN') <= 3,
]
];
}
能对单条查询进行解析，它可能会指出缺少索引、使用了低效的函数或存在不必要的连接。
最常见的优化建议往往是“添加索引”。但如何安全地创建？你不可能手动去检查每个表。
<?php
function createIndexIfNotExists(PDO $pdo, string $table, string $column, string $indexType = 'INDEX'): bool
{
// 检查索引是否已存在
$checkSql = "SHOW INDEX FROM {$table} WHERE Column_name = :column";
$stmt = $pdo->prepare($checkSql);
$stmt->execute([':column' => $column]);
if ($stmt->rowCount() > 0) {
return false; // 索引已存在
}
// 创建索引
$indexName = "idx_{$table}_{$column}";
$sql = "ALTER TABLE {$table} ADD {$indexType} {$indexName} ({$column})";
try {
$pdo->exec($sql);
return true;
} catch (PDOException $e) {
throw new RuntimeException("创建索引失败: " . $e->getMessage());
}
}
提供了解决方案，它会在目标列上创建索引前先进行检查，避免重复创建导致错误。创建之后，如何确认索引生效了？
<?php
function getTableIndexInfo(PDO $pdo, string $table): array
{
$sql = "SHOW INDEX FROM {$table}";
$stmt = $pdo->query($sql);
$indexes = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$indexName = $row['Key_name'];
if (!isset($indexes[$indexName])) {
$indexes[$indexName] = [
'name' => $indexName,
'type' => $row['Index_type'],
'unique' => $row['Non_unique'] == 0,
'columns' => [],
'cardinality' => $row['Cardinality'],
];
}
$indexes[$indexName]['columns'][] = [
'column_name' => $row['Column_name'],
'sub_part' => $row['Sub_part'],
'collation' => $row['Collation'],
];
}
return array_values($indexes);
}
可以列出指定表的所有索引详情，让你核对索引是否被正确创建和使用。
有时问题不在于单条查询，而在于整体架构。比如，一个大表没有合适的分区，或者关联查询过多。
<?php
function analyzeDatabaseSchema(PDO $pdo, string $database = null): array
{
if ($database !== null) {
try {
// 切换到指定数据库
$pdo->exec("USE `" . str_replace('`', '``', $database) . "`");
} catch (PDOException $e) {
throw new RuntimeException("无法切换到数据库 '{$database}': " . $e->getMessage());
}
}
try {
// 获取当前数据库名称
$stmt = $pdo->query("SELECT DATABASE()");
$currentDb = $stmt->fetchColumn();
if ($currentDb === false) {
throw new RuntimeException("无法获取当前数据库信息");
}
$result = [
'database' => $currentDb,
'tables' => []
];
// 获取所有表名
$stmt = $pdo->query("SHOW TABLES");
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($tables as $table) {
$tableInfo = [
'name' => $table,
'columns' => [],
'indexes' => [],
'engine' => '',
'collation' => '',
'row_count' => 0,
'data_size' => 0,
'index_size' => 0
];
// 获取表结构信息
$stmt = $pdo->query("SHOW CREATE TABLE `" . str_replace('`', '``', $table) . "`");
$createTable = $stmt->fetch(PDO::FETCH_ASSOC);
// 获取表状态信息
$stmt = $pdo->query("SHOW TABLE STATUS LIKE '" . str_replace("'", "''", $table) . "'");
$tableStatus = $stmt->fetch(PDO::FETCH_ASSOC);
if ($tableStatus) {
$tableInfo['engine'] = $tableStatus['Engine'] ?? '';
$tableInfo['collation'] = $tableStatus['Collation'] ?? '';
$tableInfo['row_count'] = (int)($tableStatus['Rows'] ?? 0);
$tableInfo['data_size'] = (int)($tableStatus['Data_length'] ?? 0);
$tableInfo['index_size'] = (int)($tableStatus['Index_length'] ?? 0);
}
// 获取列信息
$stmt = $pdo->query("DESCRIBE `" . str_replace('`', '``', $table) . "`");
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($columns as $column) {
$tableInfo['columns'][] = [
'name' => $column['Field'],
'type' => $column['Type'],
'null' => $column['Null'] === 'YES',
'key' => $column['Key'],
'default' => $column['Default'],
'extra' => $column['Extra']
];
}
// 获取索引信息
$stmt = $pdo->query("SHOW INDEX FROM `" . str_replace('`', '``', $table) . "`");
$indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
$indexMap = [];
foreach ($indexes as $index) {
$indexName = $index['Key_name'];
if (!isset($indexMap[$indexName])) {
$indexMap[$indexName] = [
'name' => $indexName,
'columns' => [],
'unique' => $index['Non_unique'] == 0,
'type' => $index['Index_type'],
'primary' => $indexName === 'PRIMARY'
];
}
$indexMap[$indexName]['columns'][] = $index['Column_name'];
}
$tableInfo['indexes'] = array_values($indexMap);
$result['tables'][$table] = $tableInfo;
}
// 计算统计信息
$result['statistics'] = [
'table_count' => count($tables),
'total_rows' => array_sum(array_column(array_column($result['tables'], 'row_count'), null)),
'total_data_size' => array_sum(array_column(array_column($result['tables'], 'data_size'), null)),
'total_index_size' => array_sum(array_column(array_column($result['tables'], 'index_size'), null))
];
return $result;
} catch (PDOException $e) {
throw new RuntimeException("数据库架构分析失败: " . $e->getMessage());
}
}
能给你一个数据库的全局视图，包括所有表的大小、行数、索引情况。这张蓝图能帮你发现设计上的缺陷，比如哪些表体积膨胀过快需要归档，哪些关键的关系缺少外键索引。
在代码层面，优化意味着减少对数据库的请求。想象一个页面头部需要显示当前登录用户的基本信息，这个信息在多个地方都会被用到。每次都执行一次 SELECT * FROM users WHERE id = ? 显然是浪费。这时，
<?php
function implementQueryCaching(string $key, callable $queryCallback, int $ttl = 3600): mixed
{
static $cache = null;
// 初始化缓存（这里使用简单的数组缓存，生产环境应使用Redis等）
if ($cache === null) {
$cache = [];
}
// 检查缓存中是否存在有效的缓存项
if (isset($cache[$key])) {
$cacheData = $cache[$key];
// 检查是否过期（时间戳方式）
if ($cacheData['expire'] > time()) {
// 缓存命中，返回缓存数据
return $cacheData['data'];
} else {
// 缓存已过期，删除该项
unset($cache[$key]);
}
}
try {
// 执行查询回调函数获取数据
$result = $queryCallback();
// 将结果存入缓存，记录过期时间
$cache[$key] = [
'data' => $result,
'expire' => time() + $ttl
];
return $result;
} catch (Exception $e) {
// 查询失败时，不缓存错误结果
// 可以选择记录日志或重新抛出异常
error_log("查询缓存失败: " . $e->getMessage());
throw $e; // 重新抛出异常，让调用者处理
}
}
就很有价值。你可以将用户信息缓存起来，在设定的时间内（TTL），后续请求直接从缓存读取，从而显著降低数据库负载。
当你实施了索引添加或查询重写等优化后，如何验证效果？你需要对比数据。
<?php
function analyzeQueryPerformance(PDO $pdo, string $sql, array $params = []): array
{
$startTime = microtime(true);
$stmt = executeQuery($pdo, $sql, $params);
$result = $stmt->fetchAll();
$endTime = microtime(true);
$executionTime = $endTime - $startTime;
// 获取查询解释计划
$explainSql = "EXPLAIN " . $sql;
$explainStmt = executeQuery($pdo, $explainSql, $params);
$explainResult = $explainStmt->fetchAll();
return [
'execution_time' => round($executionTime * 1000, 2), // 毫秒
'row_count' => count($result),
'explain_plan' => $explainResult,
'memory_usage' => memory_get_usage(true),
'peak_memory_usage' => memory_get_peak_usage(true),
];
}
就是你的性能测试工具。在优化前后分别用这个函数执行同一条SQL，对比返回的执行时间、扫描行数等指标，量化你的优化成果。这种基于数据的验证比感觉更可靠。
将上述过程常态化，你就建立了一个性能监控与优化的闭环。定期运行
<?php
function getSlowQueries(PDO $pdo, int $limit = 10): array
{
$sql = "SELECT
query_time,
lock_time,
rows_sent,
rows_examined,
db,
sql_text,
start_time
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT :limit";
try {
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
// 如果mysql.slow_log表不存在，使用performance_schema替代
$altSql = "SELECT
SQL_TEXT,
TIMER_WAIT/1000000000 as query_time_seconds,
LOCK_TIME/1000000000 as lock_time_seconds,
ROWS_SENT,
ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT :limit";
$stmt = $pdo->prepare($altSql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
发现新问题，用
<?php
function generateQueryReport(PDO $pdo, array $queryIds): array
{
$report = [
'total_queries' => count($queryIds),
'queries' => [],
'summary' => [
'total_execution_time' => 0,
'average_execution_time' => 0,
'slowest_query_time' => 0,
'fastest_query_time' => PHP_FLOAT_MAX,
],
];
foreach ($queryIds as $queryId) {
// 这里假设有一个查询历史表，实际使用时需要调整
$sql = "SELECT * FROM query_history WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $queryId]);
$queryData = $stmt->fetch(PDO::FETCH_ASSOC);
if ($queryData) {
$performanceData = analyzeQueryPerformance($pdo, $queryData['sql'], json_decode($queryData['params'] ?? '[]', true));
$optimizationSuggestions = optimizeSlowQuery($pdo, $queryData['sql']);
$queryReport = [
'id' => $queryId,
'sql' => $queryData['sql'],
'execution_time' => $performanceData['execution_time'],
'row_count' => $performanceData['row_count'],
'explain_plan' => $performanceData['explain_plan'],
'optimization_suggestions' => $optimizationSuggestions['optimization_suggestions'],
'executed_at' => $queryData['executed_at'] ?? date('Y-m-d H:i:s'),
];
$report['queries'][] = $queryReport;
// 更新统计信息
$report['summary']['total_execution_time'] += $performanceData['execution_time'];
$report['summary']['slowest_query_time'] = max($report['summary']['slowest_query_time'], $performanceData['execution_time']);
$report['summary']['fastest_query_time'] = min($report['summary']['fastest_query_time'], $performanceData['execution_time']);
}
}
if (count($report['queries']) > 0) {
$report['summary']['average_execution_time'] =
$report['summary']['total_execution_time'] / count($report['queries']);
} else {
$report['summary']['fastest_query_time'] = 0;
}
return $report;
}
对一批可疑查询生成详细分析，再用
<?php
function formatPerformanceReport(array $report, string $format = 'html'): string
{
// 检查格式参数是否有效
if (!in_array($format, ['html', 'text'])) {
throw new InvalidArgumentException('格式参数必须为"html"或"text"');
}
// 检查报告数据是否为空
if (empty($report)) {
return $format === 'html'
? '<div class="performance-report empty">无性能数据</div>'
: '无性能数据';
}
if ($format === 'html') {
// HTML格式输出
$output = '<div class="performance-report">';
$output .= '<h3>性能报告</h3>';
$output .= '<table class="performance-table">';
$output .= '<thead><tr><th>指标</th><th>值</th><th>单位</th></tr></thead>';
$output .= '<tbody>';
foreach ($report as $key => $data) {
// 确保每个数据项都有必要的字段
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$safeKey = htmlspecialchars($key, ENT_QUOTES, 'UTF-8');
$safeValue = htmlspecialchars($data['value'], ENT_QUOTES, 'UTF-8');
$safeUnit = htmlspecialchars($data['unit'], ENT_QUOTES, 'UTF-8');
// 为关键指标添加高亮样式
$highlightClass = '';
if (isset($data['critical']) && $data['critical']) {
$highlightClass = ' class="critical"';
}
$output .= "<tr{$highlightClass}>";
$output .= "<td>{$safeKey}</td>";
$output .= "<td>{$safeValue}</td>";
$output .= "<td>{$safeUnit}</td>";
$output .= '</tr>';
}
$output .= '</tbody>';
$output .= '</table>';
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$safeSummary = htmlspecialchars($report['summary'], ENT_QUOTES, 'UTF-8');
$output .= '<div class="summary">';
$output .= "<p><strong>总结:</strong> {$safeSummary}</p>";
$output .= '</div>';
}
$output .= '</div>';
return $output;
} else {
// 文本格式输出
$output = "=== 性能报告 ===\n\n";
foreach ($report as $key => $data) {
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$line = "{$key}: {$data['value']} {$data['unit']}";
// 为关键指标添加标记
if (isset($data['critical']) && $data['critical']) {
$line .= " [重要]";
}
$output .= $line . "\n";
}
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$output .= "\n总结: {$report['summary']}\n";
}
$output .= "\n================";
return $output;
}
}
将技术数据整理成团队能看懂的格式（如HTML），便于汇报和跟进。
最后，别忘了基础设施的健康。
<?php
function validateDatabaseConnection(PDO $pdo): array
{
$checks = [];
try {
// 检查连接是否活跃
$startTime = microtime(true);
$pdo->query('SELECT 1');
$pingTime = (microtime(true) - $startTime) * 1000;
$checks['connection_ping'] = [
'status' => 'success',
'message' => '数据库连接正常',
'ping_time_ms' => round($pingTime, 2),
];
// 检查数据库版本
$version = $pdo->query('SELECT VERSION()')->fetchColumn();
$checks['database_version'] = [
'status' => 'success',
'message' => "数据库版本: {$version}",
'version' => $version,
];
// 检查当前连接数
$connections = $pdo->query('SHOW STATUS LIKE "Threads_connected"')->fetchColumn(1);
$maxConnections = $pdo->query('SHOW VARIABLES LIKE "max_connections"')->fetchColumn(1);
$checks['connection_count'] = [
'status' => $connections < ($maxConnections * 0.8) ? 'success' : 'warning',
'message' => "当前连接数: {$connections}/{$maxConnections}",
'current' => (int)$connections,
'max' => (int)$maxConnections,
'usage_percentage' => round(($connections / $maxConnections) * 100, 2),
];
} catch (PDOException $e) {
$checks['connection_ping'] = [
'status' => 'error',
'message' => '数据库连接失败: ' . $e->getMessage(),
];
}
return $checks;
}
可以在应用启动或定时任务中运行，确保数据库连接池状态良好，避免因连接问题导致的连锁性能故障。性能调优是一个持续的过程，将这些工具和实践嵌入你的开发习惯，就能让应用保持敏捷。
章节总结
数据库性能直接决定了PHP应用的响应速度和处理能力。优化工作通常从发现具体问题开始，而不是盲目猜测。你可以使用 `
<?php
function getSlowQueries(PDO $pdo, int $limit = 10): array
{
$sql = "SELECT
query_time,
lock_time,
rows_sent,
rows_examined,
db,
sql_text,
start_time
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT :limit";
try {
$stmt = $pdo->prepare($sql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
} catch (PDOException $e) {
// 如果mysql.slow_log表不存在，使用performance_schema替代
$altSql = "SELECT
SQL_TEXT,
TIMER_WAIT/1000000000 as query_time_seconds,
LOCK_TIME/1000000000 as lock_time_seconds,
ROWS_SENT,
ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE DIGEST_TEXT IS NOT NULL
ORDER BY TIMER_WAIT DESC
LIMIT :limit";
$stmt = $pdo->prepare($altSql);
$stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
$stmt->execute();
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
` 来获取一段时间内最耗时的查询列表，这为我们指明了首要的优化目标。
找到目标查询后，下一步是深入分析。`
<?php
function analyzeQueryPerformance(PDO $pdo, string $sql, array $params = []): array
{
$startTime = microtime(true);
$stmt = executeQuery($pdo, $sql, $params);
$result = $stmt->fetchAll();
$endTime = microtime(true);
$executionTime = $endTime - $startTime;
// 获取查询解释计划
$explainSql = "EXPLAIN " . $sql;
$explainStmt = executeQuery($pdo, $explainSql, $params);
$explainResult = $explainStmt->fetchAll();
return [
'execution_time' => round($executionTime * 1000, 2), // 毫秒
'row_count' => count($result),
'explain_plan' => $explainResult,
'memory_usage' => memory_get_usage(true),
'peak_memory_usage' => memory_get_peak_usage(true),
];
}
能够为你提供精确的执行时间和影响行数等关键指标。但数字本身有时不够直观，
<?php
function optimizeSlowQuery(PDO $pdo, string $sql): array
{
$optimizations = [];
// 检查是否缺少索引
if (preg_match('/WHERE\s+([\w.]+)/i', $sql, $matches)) {
$column = $matches[1];
$optimizations[] = "建议为列 '{$column}' 添加索引以提高查询性能";
}
// 检查是否使用了SELECT *
if (preg_match('/SELECT\s+\*\s+FROM/i', $sql)) {
$optimizations[] = "建议避免使用 SELECT *，只选择需要的列";
}
// 检查是否有子查询可以优化
if (preg_match('/\(\s*SELECT/i', $sql)) {
$optimizations[] = "考虑将子查询转换为JOIN以提高性能";
}
// 检查ORDER BY是否可以使用索引
if (preg_match('/ORDER BY\s+([\w.]+)\s+(ASC|DESC)/i', $sql, $matches)) {
$optimizations[] = "确保 ORDER BY 列 '{$matches[1]}' 上有合适的索引";
}
return [
'original_sql' => $sql,
'optimization_suggestions' => $optimizations,
'checklist' => [
'indexes_exist' => count($optimizations) > 0,
'select_columns_optimized' => !preg_match('/SELECT\s+\*\s+FROM/i', $sql),
'no_unnecessary_joins' => substr_count($sql, 'JOIN') <= 3,
]
];
}
的作用就是解读这些数字，它会尝试分析查询的执行计划，指出可能存在的全表扫描、临时表使用或糟糕的连接顺序等问题，并给出修改建议，比如调整WHERE`子句或连接条件。
很多性能问题的根源在于索引缺失或不当。一个没有索引的WHERE或JOIN条件会让查询变得异常缓慢。在实施优化建议前，先用 `
<?php
function getTableIndexInfo(PDO $pdo, string $table): array
{
$sql = "SHOW INDEX FROM {$table}";
$stmt = $pdo->query($sql);
$indexes = [];
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$indexName = $row['Key_name'];
if (!isset($indexes[$indexName])) {
$indexes[$indexName] = [
'name' => $indexName,
'type' => $row['Index_type'],
'unique' => $row['Non_unique'] == 0,
'columns' => [],
'cardinality' => $row['Cardinality'],
];
}
$indexes[$indexName]['columns'][] = [
'column_name' => $row['Column_name'],
'sub_part' => $row['Sub_part'],
'collation' => $row['Collation'],
];
}
return array_values($indexes);
}
查看相关表的索引情况。如果确认需要添加新索引，
<?php
function createIndexIfNotExists(PDO $pdo, string $table, string $column, string $indexType = 'INDEX'): bool
{
// 检查索引是否已存在
$checkSql = "SHOW INDEX FROM {$table} WHERE Column_name = :column";
$stmt = $pdo->prepare($checkSql);
$stmt->execute([':column' => $column]);
if ($stmt->rowCount() > 0) {
return false; // 索引已存在
}
// 创建索引
$indexName = "idx_{$table}_{$column}";
$sql = "ALTER TABLE {$table} ADD {$indexType} {$indexName} ({$column})";
try {
$pdo->exec($sql);
return true;
} catch (PDOException $e) {
throw new RuntimeException("创建索引失败: " . $e->getMessage());
}
}
` 可以帮助你安全地创建它，避免重复创建导致错误。记住，索引并非越多越好，不当的索引反而会增加写操作的开销，所以分析和测试至关重要。
除了针对单条查询的优化，我们还需要关注整体架构是否健康。定期使用 `
<?php
function validateDatabaseConnection(PDO $pdo): array
{
$checks = [];
try {
// 检查连接是否活跃
$startTime = microtime(true);
$pdo->query('SELECT 1');
$pingTime = (microtime(true) - $startTime) * 1000;
$checks['connection_ping'] = [
'status' => 'success',
'message' => '数据库连接正常',
'ping_time_ms' => round($pingTime, 2),
];
// 检查数据库版本
$version = $pdo->query('SELECT VERSION()')->fetchColumn();
$checks['database_version'] = [
'status' => 'success',
'message' => "数据库版本: {$version}",
'version' => $version,
];
// 检查当前连接数
$connections = $pdo->query('SHOW STATUS LIKE "Threads_connected"')->fetchColumn(1);
$maxConnections = $pdo->query('SHOW VARIABLES LIKE "max_connections"')->fetchColumn(1);
$checks['connection_count'] = [
'status' => $connections < ($maxConnections * 0.8) ? 'success' : 'warning',
'message' => "当前连接数: {$connections}/{$maxConnections}",
'current' => (int)$connections,
'max' => (int)$maxConnections,
'usage_percentage' => round(($connections / $maxConnections) * 100, 2),
];
} catch (PDOException $e) {
$checks['connection_ping'] = [
'status' => 'error',
'message' => '数据库连接失败: ' . $e->getMessage(),
];
}
return $checks;
}
检查连接池状态和基础配置。同时，
<?php
function analyzeDatabaseSchema(PDO $pdo, string $database = null): array
{
if ($database !== null) {
try {
// 切换到指定数据库
$pdo->exec("USE `" . str_replace('`', '``', $database) . "`");
} catch (PDOException $e) {
throw new RuntimeException("无法切换到数据库 '{$database}': " . $e->getMessage());
}
}
try {
// 获取当前数据库名称
$stmt = $pdo->query("SELECT DATABASE()");
$currentDb = $stmt->fetchColumn();
if ($currentDb === false) {
throw new RuntimeException("无法获取当前数据库信息");
}
$result = [
'database' => $currentDb,
'tables' => []
];
// 获取所有表名
$stmt = $pdo->query("SHOW TABLES");
$tables = $stmt->fetchAll(PDO::FETCH_COLUMN);
foreach ($tables as $table) {
$tableInfo = [
'name' => $table,
'columns' => [],
'indexes' => [],
'engine' => '',
'collation' => '',
'row_count' => 0,
'data_size' => 0,
'index_size' => 0
];
// 获取表结构信息
$stmt = $pdo->query("SHOW CREATE TABLE `" . str_replace('`', '``', $table) . "`");
$createTable = $stmt->fetch(PDO::FETCH_ASSOC);
// 获取表状态信息
$stmt = $pdo->query("SHOW TABLE STATUS LIKE '" . str_replace("'", "''", $table) . "'");
$tableStatus = $stmt->fetch(PDO::FETCH_ASSOC);
if ($tableStatus) {
$tableInfo['engine'] = $tableStatus['Engine'] ?? '';
$tableInfo['collation'] = $tableStatus['Collation'] ?? '';
$tableInfo['row_count'] = (int)($tableStatus['Rows'] ?? 0);
$tableInfo['data_size'] = (int)($tableStatus['Data_length'] ?? 0);
$tableInfo['index_size'] = (int)($tableStatus['Index_length'] ?? 0);
}
// 获取列信息
$stmt = $pdo->query("DESCRIBE `" . str_replace('`', '``', $table) . "`");
$columns = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($columns as $column) {
$tableInfo['columns'][] = [
'name' => $column['Field'],
'type' => $column['Type'],
'null' => $column['Null'] === 'YES',
'key' => $column['Key'],
'default' => $column['Default'],
'extra' => $column['Extra']
];
}
// 获取索引信息
$stmt = $pdo->query("SHOW INDEX FROM `" . str_replace('`', '``', $table) . "`");
$indexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
$indexMap = [];
foreach ($indexes as $index) {
$indexName = $index['Key_name'];
if (!isset($indexMap[$indexName])) {
$indexMap[$indexName] = [
'name' => $indexName,
'columns' => [],
'unique' => $index['Non_unique'] == 0,
'type' => $index['Index_type'],
'primary' => $indexName === 'PRIMARY'
];
}
$indexMap[$indexName]['columns'][] = $index['Column_name'];
}
$tableInfo['indexes'] = array_values($indexMap);
$result['tables'][$table] = $tableInfo;
}
// 计算统计信息
$result['statistics'] = [
'table_count' => count($tables),
'total_rows' => array_sum(array_column(array_column($result['tables'], 'row_count'), null)),
'total_data_size' => array_sum(array_column(array_column($result['tables'], 'data_size'), null)),
'total_index_size' => array_sum(array_column(array_column($result['tables'], 'index_size'), null))
];
return $result;
} catch (PDOException $e) {
throw new RuntimeException("数据库架构分析失败: " . $e->getMessage());
}
}
` 能提供数据库的宏观视图，帮助你发现设计上的问题，例如没有主键的表、缺失外键约束，或是可以被规范化或反规范化的数据结构。架构层面的调整往往能带来更根本的性能提升。
对于频繁执行且结果不常变化的查询，引入缓存是立竿见影的方法。`
<?php
function implementQueryCaching(string $key, callable $queryCallback, int $ttl = 3600): mixed
{
static $cache = null;
// 初始化缓存（这里使用简单的数组缓存，生产环境应使用Redis等）
if ($cache === null) {
$cache = [];
}
// 检查缓存中是否存在有效的缓存项
if (isset($cache[$key])) {
$cacheData = $cache[$key];
// 检查是否过期（时间戳方式）
if ($cacheData['expire'] > time()) {
// 缓存命中，返回缓存数据
return $cacheData['data'];
} else {
// 缓存已过期，删除该项
unset($cache[$key]);
}
}
try {
// 执行查询回调函数获取数据
$result = $queryCallback();
// 将结果存入缓存，记录过期时间
$cache[$key] = [
'data' => $result,
'expire' => time() + $ttl
];
return $result;
} catch (Exception $e) {
// 查询失败时，不缓存错误结果
// 可以选择记录日志或重新抛出异常
error_log("查询缓存失败: " . $e->getMessage());
throw $e; // 重新抛出异常，让调用者处理
}
}
` 展示了如何封装一个简单的缓存逻辑：首次查询数据库并将结果缓存，后续请求直接从缓存（如Memcached或Redis）中读取，显著降低数据库负载。你需要为不同的查询设计合适的缓存键和生存时间。
将以上步骤整合，就形成了一个实用的优化流程：监控并获取慢查询 -> 分析单条查询性能与执行计划 -> 检查并优化索引 -> 评估并调整数据库架构 -> 对合适的数据实施缓存。你可以使用 `
<?php
function generateQueryReport(PDO $pdo, array $queryIds): array
{
$report = [
'total_queries' => count($queryIds),
'queries' => [],
'summary' => [
'total_execution_time' => 0,
'average_execution_time' => 0,
'slowest_query_time' => 0,
'fastest_query_time' => PHP_FLOAT_MAX,
],
];
foreach ($queryIds as $queryId) {
// 这里假设有一个查询历史表，实际使用时需要调整
$sql = "SELECT * FROM query_history WHERE id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':id' => $queryId]);
$queryData = $stmt->fetch(PDO::FETCH_ASSOC);
if ($queryData) {
$performanceData = analyzeQueryPerformance($pdo, $queryData['sql'], json_decode($queryData['params'] ?? '[]', true));
$optimizationSuggestions = optimizeSlowQuery($pdo, $queryData['sql']);
$queryReport = [
'id' => $queryId,
'sql' => $queryData['sql'],
'execution_time' => $performanceData['execution_time'],
'row_count' => $performanceData['row_count'],
'explain_plan' => $performanceData['explain_plan'],
'optimization_suggestions' => $optimizationSuggestions['optimization_suggestions'],
'executed_at' => $queryData['executed_at'] ?? date('Y-m-d H:i:s'),
];
$report['queries'][] = $queryReport;
// 更新统计信息
$report['summary']['total_execution_time'] += $performanceData['execution_time'];
$report['summary']['slowest_query_time'] = max($report['summary']['slowest_query_time'], $performanceData['execution_time']);
$report['summary']['fastest_query_time'] = min($report['summary']['fastest_query_time'], $performanceData['execution_time']);
}
}
if (count($report['queries']) > 0) {
$report['summary']['average_execution_time'] =
$report['summary']['total_execution_time'] / count($report['queries']);
} else {
$report['summary']['fastest_query_time'] = 0;
}
return $report;
}
对一批查询进行集中分析，生成详细的诊断报告，并通过
<?php
function formatPerformanceReport(array $report, string $format = 'html'): string
{
// 检查格式参数是否有效
if (!in_array($format, ['html', 'text'])) {
throw new InvalidArgumentException('格式参数必须为"html"或"text"');
}
// 检查报告数据是否为空
if (empty($report)) {
return $format === 'html'
? '<div class="performance-report empty">无性能数据</div>'
: '无性能数据';
}
if ($format === 'html') {
// HTML格式输出
$output = '<div class="performance-report">';
$output .= '<h3>性能报告</h3>';
$output .= '<table class="performance-table">';
$output .= '<thead><tr><th>指标</th><th>值</th><th>单位</th></tr></thead>';
$output .= '<tbody>';
foreach ($report as $key => $data) {
// 确保每个数据项都有必要的字段
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$safeKey = htmlspecialchars($key, ENT_QUOTES, 'UTF-8');
$safeValue = htmlspecialchars($data['value'], ENT_QUOTES, 'UTF-8');
$safeUnit = htmlspecialchars($data['unit'], ENT_QUOTES, 'UTF-8');
// 为关键指标添加高亮样式
$highlightClass = '';
if (isset($data['critical']) && $data['critical']) {
$highlightClass = ' class="critical"';
}
$output .= "<tr{$highlightClass}>";
$output .= "<td>{$safeKey}</td>";
$output .= "<td>{$safeValue}</td>";
$output .= "<td>{$safeUnit}</td>";
$output .= '</tr>';
}
$output .= '</tbody>';
$output .= '</table>';
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$safeSummary = htmlspecialchars($report['summary'], ENT_QUOTES, 'UTF-8');
$output .= '<div class="summary">';
$output .= "<p><strong>总结:</strong> {$safeSummary}</p>";
$output .= '</div>';
}
$output .= '</div>';
return $output;
} else {
// 文本格式输出
$output = "=== 性能报告 ===\n\n";
foreach ($report as $key => $data) {
if (!isset($data['value']) || !isset($data['unit'])) {
continue;
}
$line = "{$key}: {$data['value']} {$data['unit']}";
// 为关键指标添加标记
if (isset($data['critical']) && $data['critical']) {
$line .= " [重要]";
}
$output .= $line . "\n";
}
// 添加总结部分（如果有的话）
if (isset($report['summary'])) {
$output .= "\n总结: {$report['summary']}\n";
}
$output .= "\n================";
return $output;
}
}
` 将其转换为易于阅读的格式，便于团队评审和跟踪优化效果。真正的性能调优是一个持续测量、分析、改进的循环过程。
扫一扫
1904
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
