背景與問(wèn)題

數(shù)據(jù)庫(kù)慢查詢是導(dǎo)致應(yīng)用響應(yīng)緩慢最常見的原因之一。當(dāng)業(yè)務(wù)人員反饋“頁(yè)面加載慢”、“查詢超時(shí)”、“系統(tǒng)卡頓”時(shí)，很多運(yùn)維人員的第一反應(yīng)是讓開發(fā)人員“加個(gè)索引”。但加索引只是優(yōu)化查詢的眾多手段之一，盲目加索引不僅可能無(wú)效，還可能適得其反。

真正的慢查詢優(yōu)化需要系統(tǒng)的方法：首先確認(rèn)慢查詢的事實(shí)，然后分析查詢的執(zhí)行計(jì)劃，理解數(shù)據(jù)庫(kù)的查詢優(yōu)化器決策，找出真正的瓶頸所在，最后選擇最合適的優(yōu)化手段。優(yōu)化手段包括但不限于：創(chuàng)建合適的索引、重寫 SQL 語(yǔ)句、調(diào)整數(shù)據(jù)庫(kù)配置、優(yōu)化表結(jié)構(gòu)、分表分庫(kù)、引入緩存等。

本文以 MySQL 為例，詳細(xì)講解慢查詢的排查思路、分析方法、優(yōu)化手段和最佳實(shí)踐。這些方法論同樣適用于 PostgreSQL、Oracle 等主流數(shù)據(jù)庫(kù)，只是具體命令和語(yǔ)法有所不同。

1 慢查詢的發(fā)現(xiàn)與確認(rèn)

1.1 開啟慢查詢?nèi)罩?/p>

MySQL 的慢查詢?nèi)罩臼桥挪槁樵兊幕A(chǔ)工具。默認(rèn)情況下，慢查詢?nèi)罩臼顷P(guān)閉的。需要通過(guò)配置啟用。

查看當(dāng)前慢查詢配置：

-- 查看慢查詢相關(guān)變量
SHOWVARIABLESLIKE'slow_query%';
SHOWVARIABLESLIKE'long_query_time';
SHOWVARIABLESLIKE'log_output';

-- 查看是否啟用了慢查詢?nèi)罩?SHOWVARIABLESLIKE'slow_query_log';

-- 查看慢查詢?nèi)罩疚募窂?SHOWVARIABLESLIKE'slow_query_log_file';

配置慢查詢?nèi)罩荆?/p>

-- 開啟慢查詢?nèi)罩?SETGLOBALslow_query_log ='ON';

-- 設(shè)置慢查詢閾值為 1 秒（可以是浮點(diǎn)數(shù)）
SETGLOBALlong_query_time =1;

-- 設(shè)置日志輸出格式（TABLE 或 FILE）
SETGLOBALlog_output ='FILE';

-- 設(shè)置慢查詢?nèi)罩疚募窂?SETGLOBALslow_query_log_file ='/var/log/mysql/slow.log';

上述配置重啟后會(huì)丟失，需要寫入配置文件永久生效。編輯 MySQL 配置文件（/etc/mysql/my.cnf或/etc/my.cnf）：

[mysqld]
# 開啟慢查詢?nèi)罩?slow_query_log = 1

# 慢查詢?nèi)罩疚募窂?slow_query_log_file = /var/log/mysql/slow.log

# 慢查詢閾值（秒）
long_query_time = 1

# 記錄沒(méi)有使用索引的查詢
log_queries_not_using_indexes = 1

# 將慢查詢記錄到表（mysql.general_log）
log_output = FILE

重啟 MySQL 服務(wù)使配置生效：

systemctl restart mysql
systemctl restart mysqld

1.2 慢查詢?nèi)罩靖袷浇庾x

MySQL 慢查詢?nèi)罩居涗浟嗣看温樵兊脑敿?xì)信息：

# Time: 2024-01-15T1045.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 5.234567 Lock_time: 0.001234 Rows_sent: 100 Rows_examined: 50000
SET timestamp=1705315845;
SELECT * FROM orders WHERE user_id = 12345 AND status ='paid'ORDER BY created_at DESC LIMIT 20;

關(guān)鍵字段說(shuō)明：Time是查詢執(zhí)行的時(shí)間；User@Host是執(zhí)行查詢的用戶和主機(jī)；Query_time是查詢實(shí)際執(zhí)行時(shí)間（秒），這是最核心的指標(biāo)；Lock_time是等待鎖的時(shí)間；Rows_sent是返回給客戶端的行數(shù)；Rows_examined是掃描的行數(shù)，這個(gè)數(shù)字與Rows_sent的比值反映了查詢效率。

Rows_examined大而Rows_sent小，說(shuō)明查詢?cè)趻呙璐罅繑?shù)據(jù)后才找到符合條件的記錄，這是需要優(yōu)化的典型特征。高效的查詢應(yīng)該讓Rows_examined盡可能接近Rows_sent。

1.3 使用 mysqldumpslow 分析慢查詢?nèi)罩?/p>

直接閱讀原始慢查詢?nèi)罩痉浅＠щy，mysqldumpslow工具可以對(duì)日志進(jìn)行分析匯總：

# 安裝 MySQL 后即可使用
mysqldumpslow -t 10 /var/log/mysql/slow.log

# 參數(shù)說(shuō)明：
# -t N   顯示前 N 條最慢的查詢
# -s C   按平均查詢時(shí)間排序（c=計(jì)數(shù)，t=時(shí)間，l=鎖時(shí)間，r=返回行數(shù)）
# -s S   按總查詢時(shí)間排序
# -s R   按平均掃描行數(shù)排序
# -a    不聚合相同的查詢
# -g PAT  只顯示匹配 pattern 的查詢

# 示例：顯示最慢的 10 條查詢
mysqldumpslow -t 10 -s t /var/log/mysql/slow.log

# 顯示掃描行數(shù)最多的查詢
mysqldumpslow -t 10 -s r /var/log/mysql/slow.log

# 顯示查詢次數(shù)最多的查詢
mysqldumpslow -t 10 -s c /var/log/mysql/slow.log

# 過(guò)濾特定表的查詢
mysqldumpslow -t 10 -g'orders'/var/log/mysql/slow.log

# 使用正則過(guò)濾
mysqldumpslow -t 10 -a -g'SELECT.*FROM.*WHERE'/var/log/mysql/slow.log

1.4 使用 pt-query-digest 進(jìn)行深度分析

pt-query-digest是 Percona Toolkit 中的專業(yè)分析工具，比mysqldumpslow功能更強(qiáng)大：

# 安裝 Percona Toolkit
# Ubuntu/Debian
apt-get install percona-toolkit

# RHEL/CentOS
yum install percona-toolkit

# 基本用法
pt-query-digest /var/log/mysql/slow.log

# 輸出到文件
pt-query-digest /var/log/mysql/slow.log > /tmp/query_analysis.txt

# 只分析最近 24 小時(shí)的慢查詢（需要日志中有時(shí)間戳）
pt-query-digest --since'24h'/var/log/mysql/slow.log

# 分析特定時(shí)間的查詢
pt-query-digest --since'2024-01-15 1000'--until'2024-01-15 1200'/var/log/mysql/slow.log

# 輸出查詢響應(yīng)時(shí)間分布
pt-query-digest --typegenlog /var/log/mysql/slow.log

pt-query-digest的輸出包括：查詢執(zhí)行時(shí)間分布直方圖；按響應(yīng)時(shí)間排序的查詢列表；每種查詢的出現(xiàn)次數(shù)、平均執(zhí)行時(shí)間、掃描行數(shù)統(tǒng)計(jì)；查詢執(zhí)行計(jì)劃摘要；可能導(dǎo)致問(wèn)題的查詢特征標(biāo)記。

2 使用 EXPLAIN 分析查詢執(zhí)行計(jì)劃

2.1 EXPLAIN 基本用法

EXPLAIN是分析 SQL 查詢執(zhí)行計(jì)劃的核心命令，它告訴 MySQL 優(yōu)化器將如何執(zhí)行查詢，是排查慢查詢最重要的工具。

-- 基本格式
EXPLAINSELECT*FROMordersWHEREuser_id =12345;

-- 更詳細(xì)的輸出（包括擴(kuò)展信息）
EXPLAINFORMAT=JSONSELECT*FROMordersWHEREuser_id =12345;

-- 查看 UPDATE、DELETE、INSERT 的執(zhí)行計(jì)劃
EXPLAINUPDATEordersSETstatus='shipped'WHEREorder_id =100;
EXPLAINDELETEFROMordersWHEREstatus='cancelled';

2.2 EXPLAIN 輸出字段詳解

EXPLAIN輸出的每一列都包含重要的優(yōu)化信息：

EXPLAINSELECTo.*, u.nameFROMorders o
JOINusersuONo.user_id = u.id
WHEREo.user_id =12345ANDo.status ='paid'
ORDERBYo.created_atDESCLIMIT20;

輸出示例：

+----+-------------+-------+------+-------------+------+--------+-------+---------------------+
| id | select_type| table |type| key     | rows | filtered| Extra |
+----+-------------+-------+------+-------------+------+--------+-------+
| 1 | SIMPLE   | o   | ref | idx_user  |  50 | 100.00| Using |
| 1 | SIMPLE   | u   | const| PRIMARY   |  1 | 100.00|    |
+----+-------------+-------+------+-------------+------+--------+-------+

id是查詢中 SELECT 的序號(hào)，復(fù)合查詢中 id 越大優(yōu)先級(jí)越高。select_type是查詢類型：SIMPLE 是簡(jiǎn)單查詢（不含子查詢和 UNION）；PRIMARY 是外層查詢；SUBQUERY 是子查詢；DERIVED 是派生表（FROM 中的子查詢）；UNION 是 UNION 操作的查詢。table是涉及的表名。type是訪問(wèn)類型，表示 MySQL 決定如何查找表中的行，這是最關(guān)鍵的字段之一。possible_keys是可供選擇的索引列表。key是實(shí)際選擇的索引。key_len是使用的索引長(zhǎng)度。ref是與索引比較的列。rows是預(yù)計(jì)需要掃描的行數(shù)。filtered是按條件過(guò)濾后剩余的百分比。Extra是額外信息，包含優(yōu)化提示。

2.3 type 字段詳解

type字段反映了查詢的效率，從最好到最差依次是：system、const、eq_ref、ref、ref_or_null、index_merge、unique_subquery、index_subquery、range、index、ALL。

const是最優(yōu)的類型，表示只匹配一行，通常是通過(guò)主鍵或唯一索引查找。eq_ref表示通過(guò)主鍵或唯一索引關(guān)聯(lián)查詢，每個(gè)索引值只對(duì)應(yīng)一行記錄。ref表示通過(guò)普通索引查找，返回所有匹配索引值的行。range表示使用索引范圍查詢（>、<、BETWEEN、IN 等）。index?表示全索引掃描，雖然比全表掃描好但仍然很慢。ALL?是最差的全表掃描，意味著沒(méi)有使用任何索引。

如果查詢的 type 是 ALL，說(shuō)明沒(méi)有使用索引，這是需要重點(diǎn)優(yōu)化的對(duì)象。

-- 查看 type 為 ALL 的查詢（需要優(yōu)化的）
EXPLAINSELECT*FROMordersWHEREcreated_at >'2024-01-01';

-- 創(chuàng)建索引后，type 變?yōu)?range
CREATEINDEXidx_orders_created_atONorders(created_at);
EXPLAINSELECT*FROMordersWHEREcreated_at >'2024-01-01';

2.4 Extra 字段詳解

Extra字段包含 MySQL 解析查詢的額外信息，常見的值及其含義：

Using filesort表示 MySQL 無(wú)法利用索引完成排序，需要額外的排序操作。這是需要優(yōu)化的信號(hào)。Using temporary表示查詢需要?jiǎng)?chuàng)建臨時(shí)表來(lái)存儲(chǔ)結(jié)果，通常發(fā)生在 ORDER BY 和 GROUP BY 操作中。Using index表示使用了覆蓋索引，查詢只需要索引就能完成，不需要回表。Using index condition表示使用了索引下推優(yōu)化。Using where表示在存儲(chǔ)引擎層使用 WHERE 條件過(guò)濾。Using join buffer表示使用了連接緩存。

-- 出現(xiàn) Using filesort，需要優(yōu)化排序
EXPLAINSELECT*FROMordersWHEREuser_id =123ORDERBYcreated_atDESC;

-- 優(yōu)化方案：利用索引消除 filesort
CREATEINDEXidx_orders_user_createdONorders(user_id, created_atDESC);

2.5 使用 EXPLAIN ANALYZE（MySQL 8.0+）

MySQL 8.0 引入了EXPLAIN ANALYZE，它不僅顯示執(zhí)行計(jì)劃，還實(shí)際執(zhí)行查詢并報(bào)告真實(shí)的統(tǒng)計(jì)數(shù)據(jù)：

EXPLAINANALYZE
SELECTo.*, u.nameFROMorders o
JOINusersuONo.user_id = u.id
WHEREo.user_id =12345ANDo.status ='paid'
ORDERBYo.created_atDESCLIMIT20;

輸出包括預(yù)估的 rows 和實(shí)際執(zhí)行的 rows，以及實(shí)際執(zhí)行時(shí)間。通過(guò)對(duì)比預(yù)估和實(shí)際，可以發(fā)現(xiàn)統(tǒng)計(jì)信息過(guò)時(shí)或執(zhí)行計(jì)劃錯(cuò)誤的問(wèn)題。

3 索引的創(chuàng)建與優(yōu)化

3.1 索引原理概述

理解索引的原理是正確使用索引的前提。MySQL 中最常用的是 B+Tree 索引，它將數(shù)據(jù)按照 B+Tree 數(shù)據(jù)結(jié)構(gòu)組織，每個(gè)葉子節(jié)點(diǎn)包含所有數(shù)據(jù)（聚簇索引）或數(shù)據(jù)指針（非聚簇索引）。

B+Tree 索引的特點(diǎn)：所有葉子節(jié)點(diǎn)在同一層級(jí)，樹的高度低，查詢效率穩(wěn)定；葉子節(jié)點(diǎn)之間通過(guò)鏈表連接，支持范圍查詢；每個(gè)節(jié)點(diǎn)可以存儲(chǔ)多個(gè)鍵值，寬度大，層級(jí)淺。

索引的優(yōu)勢(shì)：大幅減少查詢需要掃描的數(shù)據(jù)量；避免排序和臨時(shí)表生成；支持索引覆蓋掃描，直接返回結(jié)果。

索引的代價(jià)：占用額外的磁盤空間；寫入操作（INSERT、UPDATE、DELETE）需要維護(hù)索引，降低寫入性能；創(chuàng)建索引需要加鎖，可能阻塞其他操作。

3.2 創(chuàng)建索引的原則

創(chuàng)建索引需要遵循以下原則：

選擇區(qū)分度高的列創(chuàng)建索引。區(qū)分度指列中不重復(fù)值的比例，比例越高，索引效率越好。使用SELECT COUNT(DISTINCT column) / COUNT(*) FROM table計(jì)算區(qū)分度。

-- 查看列的區(qū)分度
SELECTCOUNT(DISTINCTstatus) /COUNT(*)FROMorders;
SELECTCOUNT(DISTINCTuser_id) /COUNT(*)FROMorders;

-- status 區(qū)分度低（只有幾種狀態(tài)），user_id 區(qū)分度高
-- 應(yīng)該為 user_id 創(chuàng)建索引，而不是 status

復(fù)合索引遵循最左前綴原則。復(fù)合索引INDEX idx(a, b, c)可以支持WHERE a = ?、WHERE a = ? AND b = ?、WHERE a = ? AND b = ? AND c = ?的查詢，但不支持WHERE b = ?或WHERE c = ?的查詢。

-- 創(chuàng)建復(fù)合索引
CREATEINDEXidx_orders_user_statusONorders(user_id,status, created_at);

-- 這些查詢可以使用該索引
SELECT*FROMordersWHEREuser_id =123;
SELECT*FROMordersWHEREuser_id =123ANDstatus='paid';
SELECT*FROMordersWHEREuser_id =123ANDstatus='paid'ANDcreated_at >'2024-01-01';

-- 這個(gè)查詢無(wú)法使用該索引（最左前綴不滿足）
SELECT*FROMordersWHEREstatus='paid';

為主鍵和唯一約束創(chuàng)建唯一索引。唯一索引保證列值的唯一性，同時(shí)提供快速的唯一性查找。

3.3 索引創(chuàng)建實(shí)戰(zhàn)

根據(jù)慢查詢?nèi)罩竞?EXPLAIN 分析結(jié)果，為慢查詢創(chuàng)建合適的索引：

-- 分析慢查詢
-- SELECT * FROM orders WHERE user_id = 12345 AND status = 'paid' ORDER BY created_at DESC LIMIT 20;

-- 根據(jù) WHERE 條件和 ORDER BY 創(chuàng)建復(fù)合索引
-- 1. 區(qū)分度高的列靠前：user_id > status
-- 2. ORDER BY 的列需要與 WHERE 條件一起創(chuàng)建索引
-- 3. 需要倒序排序，如果是 MySQL 8.0+ 可以創(chuàng)建倒序索引

CREATEINDEXidx_orders_user_status_createdONorders(user_id,status, created_atDESC);

-- 如果是 MySQL 5.7，需要?jiǎng)?chuàng)建兩個(gè)索引
CREATEINDEXidx_orders_user_statusONorders(user_id,status);
CREATEINDEXidx_orders_user_createdONorders(user_id, created_atDESC);

3.4 索引使用注意事項(xiàng)

避免在索引列上使用函數(shù)或進(jìn)行計(jì)算，這會(huì)導(dǎo)致索引失效：

-- 索引失效
SELECT*FROMordersWHEREYEAR(created_at) =2024;
SELECT*FROMordersWHEREcreated_at +INTERVAL1DAY>NOW();

-- 正確的做法
SELECT*FROMordersWHEREcreated_at >='2024-01-01'ANDcreated_at 

	

	避免使用 LIKE 開頭是通配符的查詢：

	
-- 索引失效
SELECT*FROMordersWHEREorder_noLIKE'%123%';
SELECT*FROMordersWHEREorder_noLIKE'%123';

-- 可以使用索引
SELECT*FROMordersWHEREorder_noLIKE'ABC123%';


	

	數(shù)據(jù)類型要匹配：

	
-- 如果 user_id 是 INT 類型
-- 錯(cuò)誤：字符串與數(shù)字比較
SELECT*FROMordersWHEREuser_id ='12345';

-- 正確：使用相同類型
SELECT*FROMordersWHEREuser_id =12345;


	

	3.5 查看索引使用情況

	創(chuàng)建索引后，需要確認(rèn)索引是否被使用：

	
-- 查看表的索引
SHOWINDEXFROMorders;

-- 使用 EXPLAIN 檢查是否使用索引
EXPLAINSELECT*FROMordersWHEREuser_id =12345;

-- 查看索引的基數(shù)（Cardinality，反映區(qū)分度）
SHOWTABLESTATUSLIKE'orders';

-- MySQL 8.0+ 使用 INDEX_STATISTICS
SELECT*FROMmysql.index_statisticsWHEREtable_name ='orders';


	

	4 SQL 語(yǔ)句優(yōu)化

	4.1 常見低效 SQL 模式

	避免 SELECT *，只查詢需要的列：

	
-- 低效：返回所有列
SELECT*FROMordersWHEREorder_id =12345;

-- 高效：只查詢需要的列
SELECTorder_id, user_id,status, total_amount, created_at
FROMordersWHEREorder_id =12345;


	

	使用 LIMIT 限制返回行數(shù)：

	
-- 低效：沒(méi)有 LIMIT，可能返回大量數(shù)據(jù)
SELECT*FROMordersWHEREuser_id =12345ORDERBYcreated_atDESC;

-- 高效：使用 LIMIT
SELECT*FROMordersWHEREuser_id =12345ORDERBYcreated_atDESCLIMIT20;


	

	分解大查詢，減少單次查詢的掃描范圍：

	
-- 低效：一個(gè)復(fù)雜查詢處理大量數(shù)據(jù)
SELECTo.*, u.*, p.*
FROMorders o
JOINusersuONo.user_id = u.id
JOINproducts pONo.product_id = p.id
WHEREo.created_at >'2024-01-01';

-- 高效：分步處理，先篩選出符合條件的訂單，再關(guān)聯(lián)其他表
SELECT*FROMordersWHEREcreated_at >'2024-01-01'LIMIT1000;
-- 然后用 IN 子句關(guān)聯(lián)用戶和產(chǎn)品
SELECTu.*FROMusersWHEREidIN(SELECTDISTINCTuser_idFROMordersWHEREcreated_at >'2024-01-01');


	

	4.2 優(yōu)化 ORDER BY 和 GROUP BY

	ORDER BY 使用索引可以避免 filesort：

	
-- 創(chuàng)建復(fù)合索引使 ORDER BY 使用索引
CREATEINDEXidx_orders_user_status_createdONorders(user_id,status, created_atDESC);

-- 這個(gè)查詢可以使用索引排序
SELECT*FROMorders
WHEREuser_id =123
ORDERBYstatus, created_atDESC
LIMIT20;


	

	GROUP BY 同樣可以利用索引：

	
-- 如果查詢經(jīng)常按時(shí)間分組統(tǒng)計(jì)，創(chuàng)建按時(shí)間的索引
CREATEINDEXidx_orders_created_atONorders(created_at);

-- 分組查詢
SELECTDATE(created_at),COUNT(*),SUM(total_amount)
FROMorders
GROUPBYDATE(created_at);


	

	4.3 優(yōu)化 JOIN 操作

	確保 JOIN 條件有索引：

	
-- orders.user_id 和 users.id 都應(yīng)該有索引
CREATEINDEXidx_orders_user_idONorders(user_id);
CREATEINDEXidx_users_idONusers(id);

-- 使用 EXPLAIN 檢查 JOIN 類型
EXPLAINSELECTo.*, u.name
FROMorders o
JOINusersuONo.user_id = u.id
WHEREo.user_id =12345;


	

	小表驅(qū)動(dòng)大表（讓小表先過(guò)濾，減少被驅(qū)動(dòng)表的掃描次數(shù)）：

	
-- orders 表有 1000 萬(wàn)條記錄，users 表有 10 萬(wàn)條記錄
-- 正確：用小表驅(qū)動(dòng)大表
SELECTo.*, u.name
FROMusersu
JOINorders oONo.user_id = u.id
WHEREu.status ='vip';

-- 可能效率更低
SELECTo.*, u.name
FROMorders o
JOINusersuONo.user_id = u.id
WHEREu.status ='vip';


	

	4.4 使用 EXISTS 替代 IN

	
-- 子查詢表很大的情況下，IN 的效率可能很低
SELECT*FROMorders
WHEREuser_idIN(SELECTidFROMusersWHEREstatus='vip');

-- 使用 EXISTS 通常更高效
SELECT*FROMorders o
WHEREEXISTS(SELECT1FROMusersuWHEREu.id = o.user_idANDu.status ='vip');


	

	5 數(shù)據(jù)庫(kù)配置優(yōu)化

	5.1 關(guān)鍵配置參數(shù)

	MySQL 的配置文件對(duì)性能有重大影響。關(guān)鍵參數(shù)包括：

	
[mysqld]
# InnoDB 緩沖池大小，通常設(shè)置為可用內(nèi)存的 70-80%
innodb_buffer_pool_size = 12G

# 日志文件大小
innodb_log_file_size = 1G

# 刷新日志的策略（影響寫入性能和數(shù)據(jù)安全）
innodb_flush_log_at_trx_commit = 1

# 最大連接數(shù)
max_connections = 500

# 查詢緩存（MySQL 8.0 已移除）
query_cache_size = 0
query_cache_type = 0

# 臨時(shí)表和內(nèi)存表大小
tmp_table_size = 256M
max_heap_table_size = 256M

# 慢查詢?nèi)罩?slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# 記錄未使用索引的查詢
log_queries_not_using_indexes = 1


	

	5.2 InnoDB 緩沖池優(yōu)化

	InnoDB 緩沖池是 InnoDB 最重要的性能參數(shù)，它緩存表數(shù)據(jù)和索引。緩沖池越大，可以緩存的數(shù)據(jù)越多，磁盤 I/O 越少。

	
-- 查看緩沖池使用情況
SHOWSTATUSLIKE'Innodb_buffer_pool%';

-- 查看緩沖池大小配置
SHOWVARIABLESLIKE'innodb_buffer_pool_size';

-- 動(dòng)態(tài)調(diào)整緩沖池大?。∕ySQL 5.7+）
SETGLOBALinnodb_buffer_pool_size =12873741824; -- 12GB

-- 設(shè)置緩沖池實(shí)例數(shù)量（建議 CPU 核心數(shù)）
innodb_buffer_pool_instances = 8

-- 預(yù)熱緩沖池（數(shù)據(jù)庫(kù)重啟后自動(dòng)加載熱數(shù)據(jù)到內(nèi)存）
innodb_buffer_pool_load_at_startup = 1


	

	5.3 連接數(shù)管理

	連接數(shù)過(guò)多會(huì)導(dǎo)致資源耗盡，連接數(shù)過(guò)少會(huì)限制并發(fā)能力。

	
-- 查看當(dāng)前連接數(shù)
SHOWSTATUSLIKE'Threads_connected';
SHOWSTATUSLIKE'Max_used_connections';

-- 查看最大連接數(shù)
SHOWVARIABLESLIKE'max_connections';

-- 調(diào)整最大連接數(shù)
SETGLOBALmax_connections =1000;

-- 查看連接來(lái)源
SHOWPROCESSLIST;
SHOWFULLPROCESSLIST;

-- 殺掉長(zhǎng)時(shí)間空閑的連接
SELECTCONCAT('KILL ',id,';')
FROMinformation_schema.processlist
WHERECommand ='Sleep'ANDTime>3600;


	

	6 慢查詢優(yōu)化實(shí)戰(zhàn)案例

	6.1 案例一：分頁(yè)查詢優(yōu)化

	后臺(tái)管理系統(tǒng)中常見的需求是分頁(yè)查詢訂單列表：

	
-- 低效的深分頁(yè)查詢（頁(yè)面越大越慢）
SELECT*FROMordersORDERBYorder_idDESCLIMIT1000000,20;

-- 問(wèn)題分析：
-- 1. LIMIT offset 很大時(shí)，MySQL 需要掃描 offset + limit 條記錄
-- 2. rows_examined 會(huì)非常大
EXPLAINSELECT*FROMordersORDERBYorder_idDESCLIMIT1000000,20;

-- 優(yōu)化方案一：使用游標(biāo)分頁(yè)（基于上一頁(yè)最后一條記錄的 ID）
SELECT*FROMorders
WHEREorder_id 

	

	6.2 案例二：統(tǒng)計(jì)查詢優(yōu)化

	需要統(tǒng)計(jì)每天的訂單數(shù)量和金額：

	
-- 低效：實(shí)時(shí)計(jì)算，每次查詢都掃描全表
SELECTDATE(created_at)ASday,
   COUNT(*)ASorder_count,
   SUM(total_amount)AStotal_amount
FROMorders
WHEREcreated_at >='2024-01-01'
GROUPBYDATE(created_at);

-- 優(yōu)化方案一：使用匯總表
CREATETABLEorders_daily_summary (
  stat_dateDATEPRIMARYKEY,
  order_countINTNOTNULLDEFAULT0,
  total_amountDECIMAL(15,2)NOTNULLDEFAULT0,
  updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP
);

-- 定期匯總（可以使用事件或 cron 任務(wù)）
INSERTINTOorders_daily_summary (stat_date, order_count, total_amount)
SELECTDATE(created_at),COUNT(*),SUM(total_amount)
FROMorders
WHEREDATE(created_at) ='2024-01-15'
GROUPBYDATE(created_at)
ONDUPLICATEKEYUPDATE
  order_count =VALUES(order_count),
  total_amount =VALUES(total_amount);

-- 查詢匯總表
SELECT*FROMorders_daily_summaryWHEREstat_date >='2024-01-01';


	

	6.3 案例三：模糊搜索優(yōu)化

	用戶表需要支持按用戶名模糊搜索：

	
-- 低效：LIKE 開頭是通配符導(dǎo)致全表掃描
SELECT*FROMusersWHEREnameLIKE'%zhang%';

-- 優(yōu)化方案一：使用全文索引（MySQL 5.6+）
ALTERTABLEusersADDFULLTEXTINDEXft_users_name(name);

SELECT*FROMusersWHEREMATCH(name) AGAINST('zhang');

-- 優(yōu)化方案二：使用 Elasticsearch 等專業(yè)搜索引擎
-- 適用于模糊搜索需求復(fù)雜、對(duì)性能要求高的場(chǎng)景

-- 優(yōu)化方案三：使用前綴索引（適用于前綴固定的場(chǎng)景）
CREATEINDEXidx_users_name_prefixONusers(name(10));

-- 如果 name 通常以姓開頭
SELECT*FROMusersWHEREnameLIKE'zhang%'; -- 可以使用索引


	

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

	7.1 持續(xù)監(jiān)控慢查詢

	使用 pt-query-digest 定期分析慢查詢?nèi)罩荆?/p>

	
#!/bin/bash
# 每天凌晨分析昨天的慢查詢?nèi)罩?
DATE=$(date -d"yesterday"+%Y-%m-%d)
SLOW_LOG="/var/log/mysql/slow.log"
REPORT="/var/log/mysql/slow_query_report_${DATE}.txt"

pt-query-digest --since"$(date -d 'yesterday 0000' +%s)seconds"
        --until"$(date -d 'yesterday 2359' +%s)seconds"
        --report-format=query_report 
       $SLOW_LOG>$REPORT

# 如果有新的慢查詢，發(fā)送告警
if[ -s"$REPORT"];then
  count=$(grep -c"Query"$REPORT||true)
 if["$count"-gt 10 ];then
   echo"Found$countslow queries in the report"| mail -s"Slow Query Alert"ops@example.com
 fi
fi


	

	7.2 使用 Performance Schema

	MySQL 5.6+ 提供了 Performance Schema，可以實(shí)時(shí)監(jiān)控查詢性能：

	
-- 啟用相關(guān)的 instrument 和 consumer
UPDATEperformance_schema.setup_instruments
SETENABLED ='YES'
WHERENAMELIKE'statement/%';

UPDATEperformance_schema.setup_consumers
SETENABLED ='YES'
WHERENAMELIKE'events_statements%';

-- 查看當(dāng)前最慢的查詢
SELECT
  DIGEST,
  COUNT_STAR,
  SUM_TIMER_WAIT /1000000000000AStotal_time_sec,
  AVG_TIMER_WAIT /1000000000000ASavg_time_sec,
  SUM_ROWS_EXAMINED,
  SUM_ROWS_SENT,
 SUBSTR(DIGEST_TEXT,1,100)ASquery_sample
FROMperformance_schema.events_statements_summary_by_digest
ORDERBYSUM_TIMER_WAITDESC
LIMIT10;


	

	7.3 建立慢查詢治理流程

	生產(chǎn)環(huán)境的慢查詢治理應(yīng)該成為日常工作的一部分：

	日常監(jiān)控：每天檢查新增的慢查詢

	分析根因：使用 EXPLAIN 分析執(zhí)行計(jì)劃

	制定優(yōu)化方案：索引優(yōu)化、SQL 改寫、配置調(diào)整

	上線驗(yàn)證：確認(rèn)優(yōu)化效果

	歸檔記錄：記錄問(wèn)題和解決方案，形成知識(shí)庫(kù)

	8 結(jié)論

	數(shù)據(jù)庫(kù)慢查詢優(yōu)化是一個(gè)系統(tǒng)性的工作，需要綜合運(yùn)用多種方法和工具。

	首先，通過(guò)慢查詢?nèi)罩景l(fā)現(xiàn)慢查詢，使用 mysqldumpslow 或 pt-query-digest 進(jìn)行分析；其次，使用 EXPLAIN 分析執(zhí)行計(jì)劃，找出性能瓶頸；然后，根據(jù)分析結(jié)果選擇優(yōu)化手段：創(chuàng)建合適的索引、重寫 SQL 語(yǔ)句、調(diào)整數(shù)據(jù)庫(kù)配置；最后，通過(guò)持續(xù)監(jiān)控防止慢查詢復(fù)發(fā)。

	加索引只是優(yōu)化手段之一，不是萬(wàn)能藥。正確的優(yōu)化思路是：先分析，再?zèng)Q策，最后實(shí)施。不加分析的盲目加索引可能適得其反。

	運(yùn)維工程師應(yīng)該掌握的慢查詢排查技能：開啟和配置慢查詢?nèi)罩?；使?EXPLAIN 分析執(zhí)行計(jì)劃；創(chuàng)建和分析索引；識(shí)別常見的低效 SQL 模式；根據(jù)業(yè)務(wù)特點(diǎn)制定優(yōu)化方案。