一、什么是慢SQL？——數(shù)據(jù)庫的"交通堵塞"

慢SQL的定義

慢SQL是指執(zhí)行時(shí)間超過設(shè)定閾值（通常0.5-2秒）的SQL語句。就像高速路上的事故車，它會(huì)阻塞整個(gè)交通流！

慢SQL的典型危害

二、開啟慢SQL監(jiān)控:安裝"交通攝像頭"

1. 配置慢查詢?nèi)罩?/h3>

-- 查看當(dāng)前設(shè)置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

-- 動(dòng)態(tài)開啟（重啟失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 1秒閾值
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久生效（my.cnf）
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1 -- 記錄無索引查詢
log_output = FILE

2. 性能模式監(jiān)控

-- 開啟性能監(jiān)控
UPDATE setup_consumers SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

-- 查看慢SQL統(tǒng)計(jì)
SELECT * FROM events_statements_summary_by_digest 
WHERE SUM_TIMER_WAIT > 1000000000000; -- 查詢超過1秒的SQL

3. 慢查詢?nèi)罩靖袷浇馕?/h3>

# Time: 2023-10-05T08:12:34.123456Z
# User@Host: root[root] @ localhost []  Id:    15
# Query_time: 5.123456  Lock_time: 0.001000 Rows_sent: 10  Rows_examined: 100000
SET timestamp=1696493554;
SELECT * FROM orders WHERE status='pending' AND amount > 100;

關(guān)鍵字段:

• Query_time:SQL執(zhí)行時(shí)間
• Rows_examined:掃描行數(shù)
• Rows_sent:返回行數(shù)
• Lock_time:鎖定時(shí)間

三、分析慢SQL:找出"堵點(diǎn)"原因

1. EXPLAIN命令詳解

EXPLAIN SELECT * FROM users WHERE age > 30 AND country = 'CN';

2. 關(guān)鍵指標(biāo)解讀表

3. 可視化分析工具

pt-query-digest使用:

# 安裝Percona Toolkit
sudo apt install percona-toolkit

# 分析慢日志
pt-query-digest /var/log/mysql/slow.log

# 輸出報(bào)告示例
# 220ms avg, 95% 350ms, 最大耗時(shí)2.5s
# 執(zhí)行次數(shù):120次/天
# 建議:添加索引(status,amount)

四、十大慢SQL優(yōu)化技巧:從入門到精通

1. 索引優(yōu)化:數(shù)據(jù)庫的"高速公路"

-- 添加組合索引
CREATE INDEX idx_status_amount ON orders(status, amount);

-- 刪除冗余索引
DROP INDEX idx_status ON orders;

索引優(yōu)化矩陣:

2. 查詢重寫:更高效的表達(dá)

-- 優(yōu)化前（函數(shù)導(dǎo)致索引失效）
SELECT * FROM orders WHERE YEAR(create_time) = 2023;

-- 優(yōu)化后
SELECT * FROM orders 
WHERE create_time BETWEEN '2023-01-01' AND '2023-12-31';

3. 避免SELECT *:精準(zhǔn)數(shù)據(jù)獲取

-- 優(yōu)化前
SELECT * FROM products;

-- 優(yōu)化后
SELECT id, name, price FROM products;

4. 深度分頁優(yōu)化

-- 傳統(tǒng)分頁（慢）
SELECT * FROM orders LIMIT 1000000, 20;

-- 游標(biāo)分頁（快）
SELECT * FROM orders WHERE id > 1000000 LIMIT 20;

5. 批量操作:減少交互次數(shù)

// Java示例:批量插入
PreparedStatement ps = conn.prepareStatement("INSERT INTO logs VALUES (?)");
for (String log : logs) {
    ps.setString(1, log);
    ps.addBatch(); // 添加到批處理
}
ps.executeBatch(); // 批量執(zhí)行

6. 合理使用JOIN

-- 使用EXISTS代替JOIN
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

7. 控制事務(wù)大小

-- 小事務(wù)提交
START TRANSACTION;
INSERT INTO ...; -- 少量操作
COMMIT;

8. 使用覆蓋索引

-- 創(chuàng)建覆蓋索引
CREATE INDEX idx_cover ON orders(user_id, status);

-- 查詢只需索引
SELECT user_id, status FROM orders WHERE user_id=100;

9. 分區(qū)表優(yōu)化

-- 按時(shí)間分區(qū)
CREATE TABLE logs (
    id INT,
    log_time DATETIME
) PARTITION BY RANGE (YEAR(log_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022)
);

10. 架構(gòu)升級

五、自動(dòng)化監(jiān)控工具:24小時(shí)守護(hù)

工具對比表

Prometheus監(jiān)控配置

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['mysql-server:9104']
    metrics_path: /metrics

Grafana儀表盤示例

六、實(shí)戰(zhàn)案例:電商系統(tǒng)優(yōu)化之旅

問題場景

• 訂單查詢超時(shí)（>5秒）
• 高峰期數(shù)據(jù)庫CPU 100%
• 每分鐘超時(shí)錯(cuò)誤50+

優(yōu)化過程

優(yōu)化效果

七、預(yù)防慢SQL:最佳實(shí)踐

開發(fā)規(guī)范清單

1. 所有查詢使用EXPLAIN分析
2. 避免全表掃描（type=ALL）
3. 為WHERE條件列添加索引
4. 禁止超過3表JOIN
5. 事務(wù)內(nèi)操作不超過5條SQL
6. 分頁查詢使用游標(biāo)模式
7. 批量操作代替循環(huán)操作
8. 定期進(jìn)行SQL審查

持續(xù)優(yōu)化流程

八、總結(jié):慢SQL優(yōu)化黃金法則

優(yōu)化金字塔

終極忠告

慢SQL優(yōu)化是"治未病"的藝術(shù)——最好的優(yōu)化是在問題發(fā)生前預(yù)防！

行動(dòng)指南:

1. 立即開啟慢查詢?nèi)罩?/li>
2. 分析TOP 10慢SQL
3. 實(shí)施優(yōu)化方案
4. 建立監(jiān)控告警
5. 制定開發(fā)規(guī)范

記住:數(shù)據(jù)庫優(yōu)化不是一次性的任務(wù)，而是持續(xù)的過程。現(xiàn)在就開始你的優(yōu)化之旅吧！

（本文基于MySQL 8.0，部分命令在舊版本中可能不同）

以上就是MySQL中慢SQL的監(jiān)控與優(yōu)化技巧的詳細(xì)內(nèi)容，更多關(guān)于MySQL慢SQL監(jiān)控與優(yōu)化的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論