快捷導航

MySQL性能優(yōu)化之慢查詢優(yōu)化實戰(zhàn)指南

更新時間：2025年07月07日 08:41:55 作者：淺沫云歸

這篇文章主要為大家詳細介紹了MySQL如何進行慢查詢優(yōu)化,文中的示例代碼簡潔易懂,具有一定的借鑒價值,有需要的小伙伴可以跟隨小編一起學習一下

1. 業(yè)務(wù)場景描述

在某電商平臺，對商品訂單數(shù)據(jù)進行統(tǒng)計分析時，后臺報表接口響應(yīng)時間經(jīng)常超過5秒，嚴重影響業(yè)務(wù)體驗。進一步定位發(fā)現(xiàn)，涉及千萬級別的order和order_item表，多表JOIN和聚合查詢導致MySQL查詢性能瓶頸。為了保證統(tǒng)計接口的實時性與可用性，需要對慢查詢進行系統(tǒng)優(yōu)化。

關(guān)鍵痛點：

表數(shù)據(jù)量大（訂單表超過2000萬行）
多表關(guān)聯(lián)和復(fù)雜聚合（SUM、GROUP BY）
高并發(fā)讀請求影響主庫負載

2. 技術(shù)選型過程

為了解決上述問題，我們評估了以下幾種方案：

方案A：在主庫打開慢查詢?nèi)罩?使用EXPLAIN手動優(yōu)化

方案B：使用MySQL Proxy/中間件做SQL路由及分片

方案C：引入Elasticsearch做離線統(tǒng)計

方案D（最終選型）：主庫+備庫讀寫分離 + 組合索引優(yōu)化 + SQL重寫 + 分區(qū)分表方案

選型理由：

A方案可快速定位并優(yōu)化單條SQL，但無法構(gòu)建整體可擴展體系
B方案需要中間件改造成本高，團隊不具備足夠維護經(jīng)驗
C方案脫離MySQL生態(tài)，數(shù)據(jù)同步延遲高，無法滿足實時性
D方案在現(xiàn)有架構(gòu)上擴展成本較低，可漸進式上線，兼顧實時性與可維護性

3. 實現(xiàn)方案詳解

3.1 開啟慢查詢?nèi)罩九c收集數(shù)據(jù)

在my.cnf中開啟慢查詢?nèi)罩荆⒃O(shè)置合理閾值（例如2秒）：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

重啟后，讓MySQL開始記錄慢查詢。

3.2 使用pt-query-digest分析日志

借助Percona Toolkit：

pt-query-digest /var/log/mysql/slow.log > slow_report.txt

報告中會列出最耗時、最頻繁的SQL以及全表掃描等信息。

3.3 EXPLAIN分析瓶頸SQL

以典型慢查詢?yōu)槔?/p>

SELECT oi.product_id, SUM(oi.quantity) AS total_sold
FROM order_item oi
JOIN `order` o ON oi.order_id = o.id
WHERE o.status = 'COMPLETED'
  AND o.created_at BETWEEN '2023-01-01' AND '2023-06-30'
GROUP BY oi.product_id;

執(zhí)行EXPLAIN：

+----+-------------+-------+------------+------+---------------+------+---------+----------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+-------+----------+-------------+
| 1 | SIMPLE | o | NULL | ALL | idx_status | NULL | NULL | NULL |2000000| 10.00 | Using where |
| 1 | SIMPLE | oi | NULL | ref | idx_order_id | idx_order_id | 4 | test.o.id | 500000| 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+----------------------+-------+----------+-------------+

可以看到訂單表o全表掃描，需要優(yōu)化索引。

3.4 添加組合索引

針對order(status, created_at)添加組合索引：

ALTER TABLE `order`
  ADD INDEX idx_status_created_at (status, created_at);

再次執(zhí)行EXPLAIN：

| type: range, key: idx_status_created_at, rows: 50000, Extra: Using where; Using index

大幅減少掃描行數(shù)。

3.5 SQL重寫與分區(qū)

分區(qū)表：

ALTER TABLE `order`
PARTITION BY RANGE ( YEAR(created_at) ) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION pMax VALUES LESS THAN MAXVALUE
);

重寫SQL使分區(qū)裁剪生效：

... WHERE created_at >= '2023-01-01' AND created_at < '2023-07-01' ...

保證時間范圍在單個或少數(shù)分區(qū)。

3.6 讀寫分離

使用MySQL Proxy或中間件（如Atlas、MyCAT）將讀請求路由到從庫，減輕主庫壓力。

JS配置示例（Sequelize+XORM）：

const sequelize = new Sequelize('db', 'user', 'pass', {
  dialect: 'mysql',
  replication: {
    read: [{ host: 'slave1', username: 'user', password: 'pass' }],
    write: { host: 'master', username: 'user', password: 'pass' }
  }
});