您现在的位置：程式碼之家 >> 編程語言 > >> 網頁程式設計 >> PHP 程式設計 >> PHP 相關知識

PHP 解析 MySQL 慢查詢日誌並導出 Excel 實戰指南

編輯：PHP 相關知識

PHP 解析 MySQL 慢查詢日誌並導出 Excel 實戰指南

在資料庫效能調優中，分析 MySQL 慢查詢日誌是關鍵步驟。本文提供一個完整的 PHP 解決方案，能夠從慢查詢日誌文件中提取關鍵指標（如查詢時間、鎖定時間、掃描行數等）及 SQL 語句，並自動生成 Excel 報表，方便 DBA 和開發人員快速定位效能瓶頸。

核心邏輯解析

程式採用流式讀取方式處理大型日誌文件，避免一次性載入導致記憶體溢出。通過特定標識符 # User@Host 分割單條慢查詢記錄，再利用正則表達式提取結構化字段：

User@Host：執行用戶及客戶端資訊
Query_time：查詢耗時（秒）
Lock_time：鎖定等待時間
Rows_examined：優化器檢查的行數
Rows_read：實際讀取的行數
timestamp：執行時間戳
Bytes_sent：返回客戶端的位元組數
sql：完整的 SQL 語句

完整程式碼實現

<?php
header("Content-type:text/html; charset=UTF-8");
ini_set('max_execution_time', '10000');
ini_set('memory_limit','1024M');

function dealslowContent($content) {
    $data = array();
    preg_match('/#\sUser@Host:(.+)\n#\sThread_id/', $content, $match);
    $data['User@Host'] = $match[1];
    preg_match('/#\sQuery_time:\s(.+)\sLock_time/', $content, $match);
    $data['Query_time'] = $match[1];
    preg_match('/\sLock_time:\s(.+)\sRows_sent/', $content, $match);
    $data['Lock_time'] = $match[1];
    preg_match('/\sRows_examined:\s(.+)\sRows_affected/', $content, $match);
    $data['Rows_examined'] = $match[1];
    preg_match('/\sRows_read:\s(.+)\n#\sBytes_sent/', $content, $match);
    $data['Rows_read'] = $match[1];
    preg_match('/timestamp=(\d+);\s/', $content, $match);
    $data['timestamp'] = $match[1];
    preg_match('/#\sBytes_sent:\s(.+)\sSET/', $content, $match);
    $data['Bytes_sent'] = $match[1];
    $sqlPos = strpos($content, ';');
    $data['sql'] = substr($content, $sqlPos + 1, strlen($content));
    return $data;
}

$slowData = array();
$handle = fopen('mysql-slow.log.2', 'r');
$content = '';
$middlerCha = '';
$identifier = '# User@Host';
$identifierLength = strlen($identifier);

while (!feof($handle)) {
    $oneTakeCha = fread($handle, 1);
    if ($oneTakeCha === '#') {
        $middlerCha = '#' . fread($handle, $identifierLength - 1);
        if ($middlerCha === $identifier) {
            if ($content != '') {
                $slowData[] = dealslowContent($content);
                $content = $middlerCha;
            } else {
                $content = $middlerCha;
            }
        } else {
            $content .= $middlerCha;
        }
    } else {
        $content .= $oneTakeCha;
    }
}
$slowData[] = dealslowContent($content);
fclose($handle);

// 使用 PHPExcel 導出 Excel
include 'libraries/PHPExcel.php';
include 'libraries/PHPExcel/IOFactory.php';

$objPHPExcel = new PHPExcel();
$objPHPExcel->getProperties()->setTitle("export")->setDescription("none");
$objPHPExcel->setActiveSheetIndex(0);

// 設定欄寬
$widths = array('A'=>20, 'B'=>25, 'C'=>25, 'D'=>25, 'E'=>25, 'F'=>15, 'G'=>15, 'H'=>15);
foreach ($widths as $col => $w) {
    $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setWidth($w);
}

// 表頭
$titlearray = array(
    "主機信息" => 'User@Host',
    "查詢時間" => 'Query_time',
    "鎖表時間" => 'Lock_time',
    "查詢時檢查行數" => 'Rows_examined',
    "讀取行數" => 'Rows_read',
    "時間" => 'timestamp',
    "發送字節數" => 'Bytes_sent',
    "執行的sql" => 'sql'
);

$col = 0;
foreach ($titlearray as $key => $title) {
    $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, 1, $key);
    $col++;
}

// 填充資料
$j = 2;
foreach ($slowData as $key => $sd) {
    $col = 0;
    foreach ($titlearray as $key2 => $title) {
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow($col, $j, $sd[$title]);
        $col++;
    }
    $j++;
}

// 輸出下載
$objWriter = IOFactory::createWriter($objPHPExcel, 'Excel5');
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="slowsql.xls"');
header('Cache-Control: max-age=0');
$objWriter->save('php://output');