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

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

關(guān)鍵痛點(diǎn)：

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

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

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

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

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

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

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

選型理由：

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

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

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

在my.cnf中開啟慢查詢?nèi)罩?，并設(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

報(bào)告中會(huì)列出最耗時(shí)、最頻繁的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í)間范圍在單個(gè)或少數(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' }
  }
});

4. 踩過的坑與解決方案

坑1：索引列順序錯(cuò)誤導(dǎo)致無效索引。

解決：嚴(yán)格按照WHERE和GROUP BY字段順序設(shè)計(jì)組合索引。

坑2：分區(qū)表改造在線遷移復(fù)雜。

解決：采用pt-online-schema-change工具在線拆分分區(qū)、添加索引。

坑3：讀寫分離一致性問題。

解決：針對關(guān)鍵業(yè)務(wù)使用session.pin或讀寫同連接，確保讀到最新數(shù)據(jù)。

坑4：過度使用IN子查詢引起臨時(shí)表。

解決：改寫為JOIN或EXISTS，或使用窗口函數(shù)（MySQL 8.0+）。

5. 總結(jié)與最佳實(shí)踐

• 常規(guī)優(yōu)化步驟：慢日志→分析報(bào)告→EXPLAIN→補(bǔ)索引→SQL重寫。
• 大表建議分區(qū)分表，結(jié)合分區(qū)裁剪減少掃描范圍。
• 生產(chǎn)環(huán)境上線前使用pt-query-digest+EXPLAIN驗(yàn)證性能。
• 讀寫分離及緩存（如Redis）配合使用，可進(jìn)一步提升讀性能。
• 定期回顧慢日志：隨著數(shù)據(jù)增長，不斷迭代優(yōu)化。

通過以上實(shí)戰(zhàn)方法，可以將統(tǒng)計(jì)接口響應(yīng)時(shí)間從5秒優(yōu)化至500ms以內(nèi)。在實(shí)際項(xiàng)目中，建議結(jié)合自身業(yè)務(wù)特點(diǎn)，靈活運(yùn)用上述手段，持續(xù)監(jiān)控并優(yōu)化數(shù)據(jù)庫性能。

到此這篇關(guān)于MySQL性能優(yōu)化之慢查詢優(yōu)化實(shí)戰(zhàn)指南的文章就介紹到這了,更多相關(guān)MySQL慢查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論