# 開啟慢查詢?nèi)罩?SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

# 使用pt-query-digest分析
pt-query-digest /var/log/mysql/slow.log

3.Profile分析

SETprofiling=1;
-- 執(zhí)行SQL
SHOWPROFILEFORQUERY1;

5.2 優(yōu)化六步法

1.索引優(yōu)化

? 為WHERE條件創(chuàng)建索引

? 為JOIN字段創(chuàng)建索引

? 考慮覆蓋索引

? 注意索引順序（選擇性高的在前）

2.SQL改寫

? 小表驅(qū)動(dòng)大表

? 先過濾再JOIN

? 避免SELECT *

? 合理使用子查詢

3.表結(jié)構(gòu)優(yōu)化

? 適當(dāng)?shù)姆捶妒交?/p>

? 字段類型優(yōu)化（避免隱式轉(zhuǎn)換）

? 分區(qū)表考慮

4.緩存策略

? Query Cache（MySQL 8.0已移除）

? Redis緩存熱數(shù)據(jù)

? 應(yīng)用層緩存

5.讀寫分離

? 主從復(fù)制

? 讀負(fù)載均衡

6.數(shù)據(jù)歸檔

? 歷史數(shù)據(jù)定期歸檔

? 冷熱數(shù)據(jù)分離

5.3 索引設(shè)計(jì)原則

-- 好的索引設(shè)計(jì)
ALTER TABLEordersADDINDEX idx_merchant_status_created
  (merchant_id, status, created_at);

-- 原因：
-- 1. merchant_id 用于JOIN
-- 2. status 選擇性較高（假設(shè)狀態(tài)值分布均勻）
-- 3. created_at 用于范圍查詢，放最后

索引設(shè)計(jì)口訣：

? 等值查詢放前面

? 范圍查詢放后面

? 排序字段要考慮

? 選擇性高的優(yōu)先

5.4 常見陷阱避坑

1.隱式類型轉(zhuǎn)換

-- 錯(cuò)誤：字符串字段用數(shù)字查詢
WHEREphone=13812345678-- phone是VARCHAR

-- 正確
WHEREphone='13812345678'

2.函數(shù)破壞索引

-- 錯(cuò)誤：對索引字段使用函數(shù)
WHEREDATE(created_at)='2024-03-15'

-- 正確
WHEREcreated_at>='2024-03-15'
ANDcreated_at

	

	3.OR條件陷阱
-- 可能不走索引
WHEREmerchant_id=100ORuser_id=200

-- 優(yōu)化方案：使用UNION
SELECT*FROMordersWHEREmerchant_id=100
UNION
SELECT*FROMordersWHEREuser_id=200

	

	六、實(shí)戰(zhàn)案例集錦

	案例1：分頁查詢優(yōu)化

	問題SQL：

	
SELECT*FROMorders
ORDERBYcreated_atDESC
LIMIT1000000,20; -- 深分頁問題

	

	優(yōu)化方案：

	
-- 使用覆蓋索引 + 子查詢
SELECT*FROMorders o
INNERJOIN(
 SELECTidFROMorders
 ORDERBYcreated_atDESC
  LIMIT1000000,20
) tONo.id=t.id;

	

	案例2：COUNT優(yōu)化

	問題SQL：

	
SELECTCOUNT(*)FROMorders
WHEREstatus=1
ANDcreated_at>='2024-01-01';

	

	優(yōu)化方案：

	
-- 方案1：使用索引覆蓋
ALTER TABLEordersADDINDEX idx_status_created (status, created_at);

-- 方案2：使用匯總表
CREATE TABLEorder_count_summary (
  count_dateDATE,
  status TINYINT,
  order_countINT,
 PRIMARY KEY(count_date, status)
);

	

	案例3：IN查詢優(yōu)化

	問題SQL：

	
SELECT*FROMorders
WHEREmerchant_idIN(
 SELECTidFROMmerchants
 WHEREcity='北京'ANDlevel=3
);

	

	優(yōu)化方案：

	
-- 改寫為JOIN
SELECTo.*FROMorders o
INNERJOINmerchants mONo.merchant_id=m.id
WHEREm.city='北京'ANDm.level=3;

	

	七、監(jiān)控與預(yù)防

	7.1 建立監(jiān)控體系

	
-- 創(chuàng)建慢查詢監(jiān)控視圖
CREATEVIEWslow_query_monitorAS
SELECT
 DATE(start_time)asquery_date,
 LEFT(sql_text,100)asquery_sample,
 COUNT(*)asexec_count,
 AVG(query_time)asavg_time,
 MAX(query_time)asmax_time,
 SUM(rows_examined)astotal_rows_examined
FROMmysql.slow_log
GROUPBYDATE(start_time),LEFT(sql_text,100)
ORDERBYavg_timeDESC;

	

	7.2 自動(dòng)化告警腳本

	
#!/usr/bin/env python3
importMySQLdb
importsmtplib
fromemail.mime.textimportMIMEText

defcheck_slow_queries():
  db = MySQLdb.connect(host="localhost", user="monitor",
            passwd="password", db="mysql")
  cursor = db.cursor()
 
 # 檢查最近1小時(shí)的慢查詢
  cursor.execute("""
    SELECT COUNT(*) as slow_count,
       AVG(query_time) as avg_time
    FROM mysql.slow_log
    WHERE start_time >= DATE_SUB(NOW(), INTERVAL 1 HOUR)
  """)
 
  result = cursor.fetchone()
  slow_count, avg_time = result
 
 # 觸發(fā)告警條件
 ifslow_count >100oravg_time >5:
    send_alert(f"慢查詢告警：數(shù)量={slow_count}, 平均時(shí)間={avg_time}秒")
 
  cursor.close()
  db.close()

defsend_alert(message):
 # 發(fā)送郵件告警
  msg = MIMEText(message)
  msg['Subject'] ='MySQL慢查詢告警'
  msg['From'] ='monitor@example.com'
  msg['To'] ='ops@example.com'
 
  s = smtplib.SMTP('localhost')
  s.send_message(msg)
  s.quit()

if__name__ =="__main__":
  check_slow_queries()

	

	7.3 定期優(yōu)化建議

	1.每周檢查

	? 分析慢查詢TOP 10

	? 檢查索引使用情況

	? 評估表數(shù)據(jù)增長

	2.每月優(yōu)化

	? 重建碎片化嚴(yán)重的表

	? 更新統(tǒng)計(jì)信息

	? 清理無用索引

	3.每季度評估

	? 架構(gòu)層面優(yōu)化需求

	? 分庫分表評估

	? 硬件升級評估

	八、性能優(yōu)化工具箱

	8.1 必備工具清單

	1.MySQL自帶工具

	? EXPLAIN / EXPLAIN ANALYZE

	? SHOW PROFILE

	? Performance Schema

	? sys schema

	2.第三方工具

	? pt-query-digest（Percona Toolkit）

	? MySQLTuner

	? MySQL Workbench

	? Prometheus + Grafana

	3.在線分析工具
# 實(shí)時(shí)查看進(jìn)程
mysqladmin -uroot -p processlist

# 查看當(dāng)前鎖等待
SELECT * FROM information_schema.INNODB_LOCKS;

# 查看事務(wù)狀態(tài)
SELECT * FROM information_schema.INNODB_TRX;

	

	8.2 快速診斷腳本

	
#!/bin/bash
# quick_check.sh - MySQL性能快速檢查腳本

echo"=== MySQL Performance Quick Check ==="

# 1. 檢查慢查詢設(shè)置
echo-e"
[1] Slow Query Settings:"
mysql -e"SHOW VARIABLES LIKE '%slow%';"

# 2. 查看最近的慢查詢
echo-e"
[2] Recent Slow Queries:"
mysql -e"SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 5G"

# 3. 檢查表索引使用情況
echo-e"
[3] Index Usage Stats:"
mysql -e"
SELECT
  table_schema,
  table_name,
  index_name,
  cardinality
FROM information_schema.STATISTICS
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY cardinality DESC
LIMIT 10;"

# 4. 檢查表大小
echo-e"
[4] Table Sizes:"
mysql -e"
SELECT
  table_schema,
  table_name,
  ROUND(data_length/1024/1024, 2) as 'Data_MB',
  ROUND(index_length/1024/1024, 2) as 'Index_MB',
  ROUND((data_length+index_length)/1024/1024, 2) as 'Total_MB'
FROM information_schema.TABLES
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY data_length + index_length DESC
LIMIT 10;"

# 5. 當(dāng)前連接狀態(tài)
echo-e"
[5] Current Connections:"
mysql -e"SHOW STATUS LIKE '%connect%';"

echo-e"
=== Check Complete ==="

	

	九、優(yōu)化心得與經(jīng)驗(yàn)總結(jié)

	9.1 優(yōu)化的黃金法則

	1.測量先于優(yōu)化

	? 不要靠猜，要靠數(shù)據(jù)說話

	? 建立基準(zhǔn)測試，量化優(yōu)化效果

	2.二八定律

	? 80%的性能問題由20%的SQL造成

	? 優(yōu)先優(yōu)化最頻繁、最耗時(shí)的查詢

	3.漸進(jìn)式優(yōu)化

	? 每次只改一個(gè)地方

	? 記錄每步優(yōu)化的效果

	? 保留回滾方案

	9.2 團(tuán)隊(duì)協(xié)作建議

	1.建立SQL Review機(jī)制
-- SQL審核檢查清單
-[ ] 是否有合適的索引？
-[ ] 是否會產(chǎn)生全表掃描？
-[ ]JOIN的表不超過3個(gè)？
-[ ] 是否使用了SELECT*？
-[ ] 子查詢是否可以改為JOIN？
-[ ] 是否考慮了數(shù)據(jù)增長？

	

	2.制定開發(fā)規(guī)范

	? 統(tǒng)一命名規(guī)范

	? 索引命名規(guī)則

	? SQL編寫標(biāo)準(zhǔn)

	3.知識分享

	? 定期的技術(shù)分享會

	? 維護(hù)優(yōu)化案例庫

	? 建立內(nèi)部Wiki

	9.3 踩坑經(jīng)驗(yàn)分享

	坑1：過度索引

	
-- 錯(cuò)誤：為每個(gè)查詢條件都建索引
ALTER TABLEordersADDINDEX idx_merchant (merchant_id);
ALTER TABLEordersADDINDEX idx_status (status);
ALTER TABLEordersADDINDEX idx_created (created_at);

-- 正確：建立復(fù)合索引
ALTER TABLEordersADDINDEX idx_merchant_status_created
  (merchant_id, status, created_at);

	

	坑2：忽視寫入性能

	? 索引越多，寫入越慢

	? 需要在查詢和寫入之間找平衡

	坑3：緩存失效風(fēng)暴

	? 大量緩存同時(shí)失效

	? 解決方案：緩存過期時(shí)間隨機(jī)化

	十、寫在最后

	從20秒優(yōu)化到200毫秒，這不僅僅是一個(gè)數(shù)字游戲，更是對技術(shù)精益求精的追求。每一次優(yōu)化都是一次學(xué)習(xí)，每一個(gè)問題都是一次成長。

	記住三個(gè)關(guān)鍵點(diǎn)：

	1.優(yōu)化是持續(xù)的過程，不是一次性的任務(wù)

	2.監(jiān)控比優(yōu)化更重要，預(yù)防勝于治療

	3.理論結(jié)合實(shí)踐，知其然更要知其所以然

	作為運(yùn)維工程師，我們不僅要解決問題，更要預(yù)防問題。建立完善的監(jiān)控體系，制定合理的優(yōu)化策略，讓系統(tǒng)始終保持在最佳狀態(tài)。