MySQL慢查詢調(diào)優(yōu)指南
背景與目的
MySQL慢查詢是數(shù)據(jù)庫性能問題的最常見原因。當(dāng)一條SQL語句執(zhí)行超過1秒時(shí)，就可能影響用戶體驗(yàn)；超過10秒時(shí)，通常會(huì)收到用戶投訴；而超過30秒的查詢，往往意味著系統(tǒng)存在嚴(yán)重的性能問題。本文從實(shí)戰(zhàn)角度出發(fā)，系統(tǒng)講解慢查詢的發(fā)現(xiàn)、分析、定位和優(yōu)化方法，幫助DBA和運(yùn)維工程師建立完整的慢查詢優(yōu)化知識(shí)體系。
前置知識(shí)：本文假設(shè)你具備基本的SQL知識(shí)，了解MySQL/MariaDB的基本操作，有過實(shí)際數(shù)據(jù)庫維護(hù)經(jīng)驗(yàn)。
環(huán)境說明：本文基于MySQL 8.0.36（社區(qū)版），MariaDB 10.11.x，使用InnoDB存儲(chǔ)引擎。命令示例兼容Percona Server 8.0。
1. 慢查詢?nèi)罩九渲门c開啟
1.1 慢查詢?nèi)罩净A(chǔ)
慢查詢?nèi)罩居涗泩?zhí)行時(shí)間超過指定閾值的SQL語句，是優(yōu)化工作的起點(diǎn)。
# 檢查慢查詢?nèi)罩臼欠耖_啟 mysql -e"SHOW VARIABLES LIKE 'slow_query_log%';" mysql -e"SHOW VARIABLES LIKE 'long_query_time%';" mysql -e"SHOW VARIABLES LIKE 'log_output%';" # 輸出示例： # +---------------------+-------------------------------+ # | Variable_name | Value | # +---------------------+-------------------------------+ # | slow_query_log | OFF | # | slow_query_log_file | /var/lib/mysql/mysql-slow.log | # +---------------------+-------------------------------+ # | Variable_name | Value | # +---------------------+-------------------------------+ # | long_query_time | 10.000000 | # +---------------------+-------------------------------+
1.2 臨時(shí)開啟慢查詢?nèi)罩?/p>
-- 臨時(shí)開啟慢查詢?nèi)罩?SETGLOBALslow_query_log ='ON'; SETGLOBALslow_query_log_file ='/var/lib/mysql/mysql-slow.log'; SETGLOBALlong_query_time =1; -- 超過1秒記錄 SETGLOBALlog_queries_not_using_indexes ='ON'; -- 記錄未使用索引的查詢
1.3 永久配置（my.cnf）
[mysqld] # 慢查詢?nèi)罩鹃_關(guān) slow_query_log = 1 slow_query_log_file = /var/lib/mysql/mysql-slow.log long_query_time = 1 # 記錄未使用索引的查詢（生產(chǎn)環(huán)境謹(jǐn)慎開啟，可能產(chǎn)生大量日志） log_queries_not_using_indexes = OFF # 記錄管理語句 log_slow_admin_statements = ON # 記錄慢查詢到表（mysql.slow_log） # log_output = 'TABLE' # 需要時(shí)改為FILE或TABLE # 最小鎖定時(shí)間（只記錄超過此時(shí)間的鎖定） # min_examined_row_limit = 1000
1.4 配置腳本
#!/bin/bash # script: enable_slow_query_log.sh # 用途：啟用并配置MySQL慢查詢?nèi)罩? SLOW_LOG_FILE="/var/lib/mysql/mysql-slow.log" LONG_QUERY_TIME=1 echo"=== 啟用MySQL慢查詢?nèi)罩?===" # 檢查MySQL是否運(yùn)行 if! systemctl is-active mysql &>/dev/null;then echo"MySQL未運(yùn)行" exit1 fi # 創(chuàng)建慢查詢?nèi)罩疚募?touch"$SLOW_LOG_FILE" chown mysql:mysql"$SLOW_LOG_FILE" # 臨時(shí)啟用 mysql <
1.5 pt-query-digest工具
Percona Toolkit中的pt-query-digest是分析慢查詢?nèi)罩镜纳衿鳌?/p>
# 安裝 dnf install percona-toolkit -y # 分析慢查詢?nèi)罩?pt-query-digest /var/lib/mysql/mysql-slow.log # 輸出前10個(gè)最慢的查詢 pt-query-digest --limit20 /var/lib/mysql/mysql-slow.log # 分析特定時(shí)間段（需要日志包含時(shí)間戳） pt-query-digest --since='2026-04-03 0000'--until='2026-04-03 1200'/var/lib/mysql/mysql-slow.log # 分析特定數(shù)據(jù)庫 pt-query-digest --filter'$event->{db} && $event->{db} eq "mydb"'/var/lib/mysql/mysql-slow.log # 將分析結(jié)果保存到文件 pt-query-digest /var/lib/mysql/mysql-slow.log > /tmp/query_analysis.txt # 實(shí)時(shí)分析當(dāng)前查詢（從processlist） pt-query-digest --processlist h=localhost --interval 1 --run-time 60
2. explain執(zhí)行計(jì)劃解讀
2.1 explain基礎(chǔ)用法
-- 基礎(chǔ)explain EXPLAINSELECT*FROMusersWHEREid=1; -- 更詳細(xì)的輸出 EXPLAINANALYZESELECT*FROMusersWHEREid=1; -- 注意：EXPLAIN ANALYZE只在MySQL 8.0.18+支持 -- 查看JSON格式（更完整） EXPLAINFORMAT=JSONSELECT*FROMusersWHEREid=1;
2.2 explain輸出字段詳解
EXPLAINSELECTu.name, o.order_id FROMusersu LEFTJOINorders oONu.id = o.user_id WHEREu.status ='active'ANDo.create_time >'2026-01-01';
輸出字段說明：
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+ | id | select_type| table | partitions |type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
字段 說明 id 查詢中SELECT的序列號(hào) select_type SELECT類型（SIMPLE/PRIMARY/SUBQUERY等） table 涉及的表 partitions 涉及的分區(qū) type 連接類型（性能關(guān)鍵） possible_keys 可能使用的索引 key 實(shí)際使用的索引 key_len 索引長度 ref 與索引比較的列 rows 預(yù)計(jì)掃描的行數(shù) filtered 過濾后剩余的百分比 Extra 附加信息 2.3 type字段詳解（性能從優(yōu)到差）
-- system：表只有一行（系統(tǒng)表） EXPLAINSELECT*FROMmysql.time_zone_name; -- const：最多匹配一行（主鍵或唯一索引） EXPLAINSELECT*FROMusersWHEREid=1; -- eq_ref：JOIN時(shí)使用主鍵或唯一索引 EXPLAINSELECT*FROMorders oJOINusersuONo.user_id = u.id; -- ref：使用非唯一索引 EXPLAINSELECT*FROMordersWHEREstatus='pending'; -- ref_or_null：類似ref，但包含NULL查詢 EXPLAINSELECT*FROMordersWHEREuser_id =1ORuser_idISNULL; -- range：使用索引范圍查詢 EXPLAINSELECT*FROMordersWHEREcreate_timeBETWEEN'2026-01-01'AND'2026-01-31'; -- index：全索引掃描 EXPLAINSELECTCOUNT(*)FROMorders; -- ALL：全表掃描（最差） EXPLAINSELECT*FROMordersWHEREorder_nameLIKE'%test%';
2.4 Extra字段常見值
-- Using index：覆蓋索引，無需回表 EXPLAINSELECTuser_id,nameFROMusersWHEREname='John'; -- Using where：使用WHERE過濾 EXPLAINSELECT*FROMusersWHEREstatus='active'; -- Using temporary：使用臨時(shí)表（性能差） EXPLAINSELECTname,COUNT(*)FROMordersGROUPBYname; -- Using filesort：使用文件排序（性能差） EXPLAINSELECT*FROMusersORDERBYcreate_timeDESC; -- Using index condition：索引條件下推 EXPLAINSELECT*FROMordersWHEREuser_id =1ANDorder_nameLIKE'A%'; -- Using MRR：使用多范圍讀優(yōu)化 EXPLAINSELECT*FROMordersWHEREuser_idIN(1,2,3);
2.5 慢查詢分析腳本
#!/bin/bash # script: analyze_slow_queries.sh # 用途：從慢查詢?nèi)罩咎崛〔⒎治鯯QL SLOW_LOG="/var/lib/mysql/mysql-slow.log" REPORT_FILE="/tmp/slow_query_report_$(date +%Y%m%d).txt" echo"=== MySQL慢查詢分析報(bào)告 ===">"$REPORT_FILE" echo"生成時(shí)間：$(date)">>"$REPORT_FILE" echo"">>"$REPORT_FILE" # 使用pt-query-digest分析 ifcommand-v pt-query-digest &> /dev/null;then echo"【1】最慢的10個(gè)查詢：">>"$REPORT_FILE" pt-query-digest --limit10"$SLOW_LOG">>"$REPORT_FILE"2>&1 echo"【2】查詢次數(shù)最多的SQL：">>"$REPORT_FILE" pt-query-digest --order-by Query_time:sum --limit10"$SLOW_LOG">>"$REPORT_FILE"2>&1 echo"【3】未使用索引的查詢：">>"$REPORT_FILE" pt-query-digest --filter'$event->{巡查索引} =~ /No index/'"$SLOW_LOG">>"$REPORT_FILE"2>&1 else echo"pt-query-digest未安裝，使用mysqldumpslow">>"$REPORT_FILE" # 備用方案：使用mysqldumpslow echo"【1】最慢的10個(gè)查詢：">>"$REPORT_FILE" mysqldumpslow -t 10"$SLOW_LOG">>"$REPORT_FILE"2>&1 fi cat"$REPORT_FILE"
3. 索引數(shù)據(jù)結(jié)構(gòu)：B+樹原理
3.1 為什么MySQL選擇B+樹
現(xiàn)代關(guān)系數(shù)據(jù)庫索引幾乎都使用B+樹作為數(shù)據(jù)結(jié)構(gòu)，原因如下：
磁盤友好：
B+樹每個(gè)節(jié)點(diǎn)通常等于一個(gè)磁盤頁（16KB）
樹高通常為3-4層（16KB * 3層 = 數(shù)十GB索引）
查詢只需3-4次磁盤IO
范圍查詢高效：
葉子節(jié)點(diǎn)用雙向鏈表連接
范圍查詢只需定位起點(diǎn)，順序掃描即可
與B樹的區(qū)別：
B樹所有節(jié)點(diǎn)都存儲(chǔ)數(shù)據(jù)
B+樹只有葉子節(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，內(nèi)部節(jié)點(diǎn)只存儲(chǔ)鍵
B+樹內(nèi)部節(jié)點(diǎn)更小，樹高更低
3.2 B+樹可視化
[50 | 100 | 200] / | [<50] ? [50-99] [100-199] [>=200] | | | | 數(shù)據(jù)1 數(shù)據(jù)2 數(shù)據(jù)3 數(shù)據(jù)4 | | | | 數(shù)據(jù)5 數(shù)據(jù)6 數(shù)據(jù)7 數(shù)據(jù)8 實(shí)際B+樹結(jié)構(gòu)： [50 | 100 | 200 ] / | [頁1] [頁2] [頁3] | | | +--+--+--+--+ +--+--+--+--+ +--+--+--+--+ | | | | | | | | | | | | | | | 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) 數(shù)據(jù) (磁盤頁) (磁盤頁) (磁盤頁)
3.3 主鍵索引與普通索引
-- users表 CREATETABLEusers( idINTPRIMARYKEY, -- 主鍵索引（聚集索引） nameVARCHAR(100), emailVARCHAR(100), ageINT, statusCHAR(1), INDEXidx_email (email), -- 普通索引（非聚集） INDEXidx_age_status (age,status) -- 聯(lián)合索引 ); -- 主鍵索引結(jié)構(gòu)（B+樹，葉子節(jié)點(diǎn)存儲(chǔ)完整行數(shù)據(jù)） -- id=1 -> [完整行數(shù)據(jù)] -- id=2 -> [完整行數(shù)據(jù)] -- ... -- 普通索引結(jié)構(gòu)（B+樹，葉子節(jié)點(diǎn)存儲(chǔ)主鍵值） -- email='a@test.com' -> [id=1] -- email='b@test.com' -> [id=2] -- ... -- 查詢時(shí)需要回表：根據(jù)email查到id，再根據(jù)id查完整數(shù)據(jù)
4. 索引類型詳解
4.1 主鍵索引
-- 創(chuàng)建表時(shí)指定主鍵 CREATETABLEorders ( order_idBIGINTPRIMARYKEY, user_idBIGINT, total_amountDECIMAL(10,2), create_time DATETIME ); -- 修改表添加主鍵 ALTERTABLEordersADDPRIMARYKEY(order_id); -- 復(fù)合主鍵 CREATETABLEorder_items ( order_idBIGINT, item_idBIGINT, quantityINT, PRIMARYKEY(order_id, item_id) -- 復(fù)合主鍵 );
特點(diǎn)：
每個(gè)表只能有一個(gè)主鍵
主鍵值唯一且非空
InnoDB會(huì)自動(dòng)使用主鍵作為聚集索引
建議使用自增BIGINT作為主鍵（性能最優(yōu)）
4.2 唯一索引
-- 創(chuàng)建唯一索引 CREATEUNIQUEINDEXidx_emailONusers(email); -- 或在表定義中 CREATETABLEusers( idINTPRIMARYKEY, emailVARCHAR(100)UNIQUE, -- 自動(dòng)創(chuàng)建唯一索引 phoneVARCHAR(20), UNIQUEINDEXidx_phone (phone) ); -- 添加唯一索引 ALTERTABLEusersADDUNIQUEINDEXidx_email (email);
與主鍵的區(qū)別：
主鍵不允許NULL，唯一索引允許NULL（但只能有一個(gè)NULL）
一個(gè)表只有一個(gè)主鍵，可以有多個(gè)唯一索引
主鍵自動(dòng)創(chuàng)建聚集索引，唯一索引是普通索引
4.3 普通索引
-- 單列索引 CREATEINDEXidx_nameONusers(name); -- 查看表的所有索引 SHOWINDEXFROMusers; -- 創(chuàng)建索引的完整語法 CREATEINDEXidx_statusONorders(status) USINGBTREE COMMENT'訂單狀態(tài)索引';
4.4 前綴索引
適用于VARCHAR或TEXT類型的前N個(gè)字符創(chuàng)建索引。
-- 取前10個(gè)字符 CREATEINDEXidx_email_prefixONusers(email(10)); -- 取前20個(gè)字符 CREATEINDEXidx_address_prefixONusers(address(20)); -- 注意事項(xiàng)： -- 1. 前綴長度選擇要足夠長，避免過多沖突 -- 2. 前綴索引只支持 =、<、>、LIKE 'xxx%' 查詢 -- 3. 不能用于 ORDER BY 和 GROUP BY
前綴長度選擇參考：
-- 計(jì)算前綴選擇性（區(qū)分度） SELECT COUNT(DISTINCTLEFT(email,5)) /COUNT(*)asprefix_5, COUNT(DISTINCTLEFT(email,10)) /COUNT(*)asprefix_10, COUNT(DISTINCTLEFT(email,15)) /COUNT(*)asprefix_15, COUNT(DISTINCTLEFT(email,20)) /COUNT(*)asprefix_20, COUNT(DISTINCTemail) /COUNT(*)asfull FROMusers;
4.5 聯(lián)合索引
多個(gè)列組合成一個(gè)索引，最左前綴原則是核心。
-- 創(chuàng)建聯(lián)合索引 CREATEINDEXidx_user_status_timeONorders(user_id,status, create_time); -- 聯(lián)合索引的B+樹結(jié)構(gòu) -- 按照 (user_id, status, create_time) 順序構(gòu)建 -- 排序優(yōu)先級(jí)：user_id > status > create_time
聯(lián)合索引使用條件：
-- 可以使用索引（全匹配） SELECT*FROMordersWHEREuser_id =1; SELECT*FROMordersWHEREuser_id =1ANDstatus='paid'; SELECT*FROMordersWHEREuser_id =1ANDstatus='paid'ANDcreate_time >'2026-01-01'; -- 無法使用索引（跳過user_id） SELECT*FROMordersWHEREstatus='paid'; SELECT*FROMordersWHEREstatus='paid'ANDcreate_time >'2026-01-01'; SELECT*FROMordersWHEREcreate_time >'2026-01-01'; -- 可以使用索引（范圍查詢后的列無法使用） SELECT*FROMordersWHEREuser_id =1ANDstatus>'paid'; -- status之后的列無法使用 -- LIKE前綴匹配可以使用 SELECT*FROMordersWHEREuser_id =1ANDcreate_timeLIKE'2026-01%';
5. 索引失效的典型場(chǎng)景
5.1 函數(shù)和運(yùn)算導(dǎo)致索引失效
-- 失效：函數(shù)運(yùn)算 SELECT*FROMordersWHEREYEAR(create_time) =2026; SELECT*FROMordersWHEREDATE_FORMAT(create_time,'%Y') ='2026'; SELECT*FROMordersWHEREcreate_time +INTERVAL1DAY>NOW(); -- 正確做法：保持索引列獨(dú)立 SELECT*FROMordersWHEREcreate_time >='2026-01-01'ANDcreate_time
5.2 類型轉(zhuǎn)換導(dǎo)致索引失效
-- 失效：字符串列用數(shù)字查詢（MySQL會(huì)隱式轉(zhuǎn)換） CREATETABLEtest(phoneVARCHAR(20)); SELECT*FROMtestWHEREphone =13800138000; -- 數(shù)字自動(dòng)轉(zhuǎn)字符串，但無法使用索引 -- 正確做法 SELECT*FROMtestWHEREphone ='13800138000'; -- 失效：數(shù)字列用字符串查詢 CREATETABLEtest2 (idINT); SELECT*FROMtest2WHEREid='1'; -- 字符串轉(zhuǎn)數(shù)字，可以走索引 -- 但反過來： SELECT*FROMtest2WHEREid='1abc'; -- 數(shù)字轉(zhuǎn)字符串，無法使用索引
5.3 LIKE通配符導(dǎo)致索引失效
-- 失效：前導(dǎo)通配符 SELECT*FROMordersWHEREorder_nameLIKE'%test%'; SELECT*FROMordersWHEREorder_nameLIKE'%test'; -- 生效：后置通配符 SELECT*FROMordersWHEREorder_nameLIKE'test%'; -- 優(yōu)化方案：使用全文索引 ALTERTABLEordersADDFULLTEXTINDEXft_order_name (order_name); SELECT*FROMordersWHEREMATCH(order_name) AGAINST('test'); -- 優(yōu)化方案：使用Elasticsearch
5.4 OR條件導(dǎo)致索引失效
-- 失效：OR條件兩邊都未使用索引 SELECT*FROMusersWHEREname='John'ORemail ='john@test.com'; -- 生效：確保OR兩邊都有索引（MySQL 8.0+） SELECT*FROMusersWHEREname='John' UNIONALL SELECT*FROMusersWHEREemail ='john@test.com'ANDname<>'John'; -- 使用IN替代OR（如果有索引） SELECT*FROMusersWHEREnameIN('John','Mary','Tom');
5.5 NOT操作符導(dǎo)致索引失效
-- 失效：NOT IN / NOT EXISTS SELECT*FROMordersWHEREstatusNOTIN('paid','shipped'); SELECT*FROMordersWHEREstatus!='paid'; SELECT*FROMordersWHERENOTEXISTS(SELECT1FROMusersWHEREusers.id = orders.user_id); -- 正確做法：盡量使用IN或正向條件 SELECT*FROMordersWHEREstatusIN('pending','cancelled'); -- 某些情況下可以使用覆蓋索引優(yōu)化 SELECT*FROMordersWHEREidNOTIN(SELECTorder_idFROMcancelled_orders);
5.6 索引失效檢查腳本
#!/bin/bash # script: check_index_usage.sh # 用途：檢查索引使用情況，找出未使用的索引 mysql -e" -- 檢查未使用的索引（需要 PERFORMANCE_SCHEMA 開啟） SELECT OBJECT_SCHEMA AS '數(shù)據(jù)庫', OBJECT_NAME AS '表名', INDEX_NAME AS '索引名', SEQ_IN_INDEX AS '索引順序', COLUMN_NAME AS '列名' FROM information_schema.STATISTICS WHERE OBJECT_SCHEMA = 'your_database' ORDER BY OBJECT_NAME, INDEX_NAME, SEQ_IN_INDEX; " # 或者使用 pt-index-usage 工具分析慢查詢?nèi)罩?# pt-index-usage /var/lib/mysql/mysql-slow.log --user=root --password=xxx echo"檢查慢查詢中的索引使用情況" echo"建議使用 EXPLAIN 分析可疑查詢"
6. 深入理解count(*)優(yōu)化
6.1 count(*) vs count(1) vs count(col)
-- 沒有任何性能差異（MySQL優(yōu)化器會(huì)統(tǒng)一處理） SELECTCOUNT(*)FROMorders; SELECTCOUNT(1)FROMorders; SELECTCOUNT(primary_key)FROMorders; -- 主鍵非NULL，始終有值 -- 有差異的情況 SELECTCOUNT(col)FROMorders; -- col列可能為NULL，需要檢查每行 -- 測(cè)試驗(yàn)證 EXPLAINSELECTCOUNT(*)FROMorders; -- type: index, rows: 預(yù)估行數(shù) EXPLAINSELECTCOUNT(1)FROMorders; -- 相同執(zhí)行計(jì)劃 EXPLAINSELECTCOUNT(id)FROMorders; -- 相同執(zhí)行計(jì)劃
6.2 count(*) 在不同引擎的實(shí)現(xiàn)
InnoDB引擎：
count(*) 需要全表掃描或索引掃描
使用主鍵索引掃描最快（因?yàn)橹麈I索引B+樹葉子節(jié)點(diǎn)包含完整數(shù)據(jù)）
如果有WHERE條件，需要過濾后計(jì)數(shù)
-- 最快的count(*)（使用主鍵索引） SELECTCOUNT(*)FROMorders; -- 全表計(jì)數(shù) -- 最快的count(*)，帶條件 SELECTCOUNT(*)FROMordersWHEREstatus='paid'; -- 需要掃描status索引
6.3 count(*) 優(yōu)化技巧
-- 場(chǎng)景：需要同時(shí)統(tǒng)計(jì)多個(gè)條件的數(shù)量 -- 低效：多次全表掃描 SELECTCOUNT(*)FROMordersWHEREstatus='paid'; SELECTCOUNT(*)FROMordersWHEREstatus='pending'; SELECTCOUNT(*)FROMordersWHEREstatus='cancelled'; -- 高效：一次掃描，多個(gè)統(tǒng)計(jì) SELECT SUM(status='paid')ASpaid_count, SUM(status='pending')ASpending_count, SUM(status='cancelled')AScancelled_count, COUNT(*)AStotal_count FROMorders; -- 或者使用 GROUP BY SELECTstatus,COUNT(*)ascntFROMordersGROUPBYstatus;
6.4 大表count(*)優(yōu)化
-- 創(chuàng)建計(jì)數(shù)器表（適合實(shí)時(shí)性要求不高的場(chǎng)景） CREATETABLEorder_stats ( stat_dateDATEPRIMARYKEY, total_ordersBIGINTDEFAULT0, paid_ordersBIGINTDEFAULT0, pending_ordersBIGINTDEFAULT0 ); -- 定時(shí)更新統(tǒng)計(jì)（使用事件調(diào)度器） DELIMITER $$ CREATEEVENTe_update_order_stats ONSCHEDULE EVERY1HOUR DO BEGIN INSERTINTOorder_stats (stat_date, total_orders, paid_orders, pending_orders) SELECT CURRENT_DATE, COUNT(*), SUM(status='paid'), SUM(status='pending') FROMorders ONDUPLICATEKEYUPDATE total_orders =VALUES(total_orders), paid_orders =VALUES(paid_orders), pending_orders =VALUES(pending_orders); END$$ DELIMITER ; -- 使用近似值（準(zhǔn)實(shí)時(shí)場(chǎng)景） SELECTTABLE_ROWSFROMinformation_schema.TABLESWHERETABLE_NAME ='orders'; -- 注意：TABLE_ROWS是估算值，可能有50%誤差
7. 分頁優(yōu)化：深度分頁問題
7.1 深度分頁問題原理
-- 問題查詢：偏移量越大，越慢 SELECT*FROMordersORDERBYidLIMIT1000000,20; -- 執(zhí)行過程： -- 1. 讀取前1000020行 -- 2. 丟棄前1000000行 -- 3. 返回20行 -- 偏移量100萬，需要掃描100萬+20行
7.2 優(yōu)化方案1：使用主鍵ID游標(biāo)
-- 第一頁 SELECT*FROMordersORDERBYidLIMIT20; -- 得到 last_id = 1000 -- 第二頁：使用上一頁的最大ID SELECT*FROMorders WHEREid>1000 ORDERBYid LIMIT20; -- 進(jìn)階：支持任意跳轉(zhuǎn) -- 假設(shè)用戶想跳到第50000頁，每頁20條 -- 最后一頁的id需要從數(shù)據(jù)庫獲取，或使用其他定位方式
7.3 優(yōu)化方案2：延遲關(guān)聯(lián)
-- 原始查詢（慢） SELECT*FROMorders WHEREstatus='paid' ORDERBYcreate_timeDESC LIMIT100000,20; -- 優(yōu)化：先查ID，再關(guān)聯(lián)獲取完整數(shù)據(jù) SELECTo.* FROMorders o INNERJOIN( SELECTidFROMorders WHEREstatus='paid' ORDERBYcreate_timeDESC LIMIT100000,20 ) tONo.id = t.id;
7.4 優(yōu)化方案3：范圍查詢
-- 如果有連續(xù)的自增ID，可以利用范圍查詢 -- 用戶在第500頁，看到的最后一條ID是 10000 -- 查詢第501頁 SELECT*FROMorders WHEREid>10000 ORDERBYid LIMIT20; -- 結(jié)合條件 SELECT*FROMorders WHEREid>10000ANDstatus='paid' ORDERBYid LIMIT20;
7.5 優(yōu)化方案4：記錄總數(shù)緩存
-- 不顯示精確總數(shù)，只顯示"上一頁/下一頁" -- 適合Feed流等場(chǎng)景 -- 獲取每頁數(shù)據(jù) SELECT*FROMorders WHEREid 20，說明還有下一頁
7.6 分頁優(yōu)化腳本
#!/bin/bash # script: test_pagination_performance.sh # 用途：測(cè)試不同分頁方式的性能 mysql -e" -- 測(cè)試不同偏移量的查詢時(shí)間 SET profiling = 1; -- 淺分頁 SELECT SQL_NO_CACHE * FROM orders ORDER BY id LIMIT 20; SELECT SQL_NO_CACHE * FROM orders ORDER BY id LIMIT 10000, 20; SELECT SQL_NO_CACHE * FROM orders ORDER BY id LIMIT 50000, 20; SELECT SQL_NO_CACHE * FROM orders ORDER BY id LIMIT 100000, 20; SHOW PROFILES; "
8. 慢查詢案例分析
8.1 案例1：訂單統(tǒng)計(jì)查詢
問題SQL：
-- 原始慢查詢 SELECT DATE(create_time)ASorder_date, COUNT(*)ASorder_count, SUM(total_amount)AStotal_amount, user_name FROMorders WHEREcreate_time >='2026-01-01' GROUPBYDATE(create_time), user_name ORDERBYorder_dateDESC;
問題分析：
GROUP BY 和 ORDER BY 字段不一致
user_name 未被索引覆蓋
缺少合適的索引
優(yōu)化后：
-- 創(chuàng)建索引 ALTERTABLEordersADDINDEXidx_create_time (create_time); -- 優(yōu)化SQL SELECT DATE(create_time)ASorder_date, COUNT(*)ASorder_count, SUM(total_amount)AStotal_amount FROMorders WHEREcreate_time >='2026-01-01' GROUPBYDATE(create_time) ORDERBYorder_dateDESC;
8.2 案例2：用戶行為分析
問題SQL：
-- 原始查詢（20秒） SELECT u.id, u.name, COUNT(DISTINCTo.id)ASorder_count, COUNT(DISTINCTe.id)ASevent_count FROMusersu LEFTJOINorders oONu.id = o.user_id LEFTJOINeventseONu.id = e.user_id WHEREu.register_time >='2026-01-01' GROUPBYu.id, u.name;
優(yōu)化方案：
-- 創(chuàng)建索引 ALTERTABLEusersADDINDEXidx_register_time (register_time); ALTERTABLEordersADDINDEXidx_user_id (user_id); ALTERTABLEeventsADDINDEXidx_user_id (user_id); -- 改寫SQL：分解為多個(gè)簡單查詢 SELECT u.id, u.name, COALESCE(o.order_count,0)ASorder_count, COALESCE(e.event_count,0)ASevent_count FROMusersu LEFTJOIN( SELECTuser_id,COUNT(*)ASorder_count FROMorders GROUPBYuser_id ) oONu.id = o.user_id LEFTJOIN( SELECTuser_id,COUNT(*)ASevent_count FROMevents GROUPBYuser_id ) eONu.id = e.user_id WHEREu.register_time >='2026-01-01';
8.3 案例3：分頁導(dǎo)出
問題SQL：
-- 導(dǎo)出100萬條數(shù)據(jù)，每次20條，需要50000次 SELECT*FROMorders WHEREstatus='completed' ORDERBYcreate_timeDESC LIMIT1000000,20;
優(yōu)化方案：
-- 方案1：使用主鍵范圍 -- 第一次查詢 SELECTidFROMorders WHEREstatus='completed' ORDERBYcreate_timeDESC LIMIT20; -- 得到最大ID：last_id = 1000000 -- 后續(xù)查詢 SELECT*FROMorders WHEREstatus='completed'ANDid
8.4 慢查詢分析報(bào)告腳本
#!/bin/bash # script: slow_query_report.sh # 用途：生成慢查詢分析報(bào)告 DB_NAME="orders_db" REPORT_FILE="/tmp/mysql_slow_report_$(date +%Y%m%d).txt" echo"=== MySQL慢查詢分析報(bào)告 ===">"$REPORT_FILE" echo"數(shù)據(jù)庫：${DB_NAME}">>"$REPORT_FILE" echo"生成時(shí)間：$(date)">>"$REPORT_FILE" echo"">>"$REPORT_FILE" # 1. 慢查詢統(tǒng)計(jì) echo"【1】慢查詢統(tǒng)計(jì)（最近24小時(shí)）：">>"$REPORT_FILE" mysql -D"$DB_NAME"-e" SELECT COUNT(*) AS total_slow_queries, AVG(query_time) AS avg_query_time, MAX(query_time) AS max_query_time, COUNT(DISTINCT db) AS affected_databases FROM mysql.slow_log WHERE start_time >= DATE_SUB(NOW(), INTERVAL 24 HOUR); ">>"$REPORT_FILE"2>&1 echo"">>"$REPORT_FILE" # 2. 最慢的10個(gè)查詢 echo"【2】最慢的10個(gè)查詢：">>"$REPORT_FILE" mysql -D"$DB_NAME"-e" SELECT query_time, rows_sent, rows_examined, LEFT(query_sql, 100) AS sql_preview, last_seen FROM ( SELECT query_time, rows_sent, rows_examined, query_sql, MAX(start_time) AS last_seen FROM mysql.slow_log WHERE start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) GROUP BY query_sql ORDER BY query_time DESC LIMIT 10 ) t; ">>"$REPORT_FILE"2>&1 echo"">>"$REPORT_FILE" # 3. 未使用索引的查詢 echo"【3】未使用索引的查詢統(tǒng)計(jì)：">>"$REPORT_FILE" mysql -D"$DB_NAME"-e" SELECT LEFT(query_sql, 100) AS sql_preview, COUNT(*) AS exec_count, AVG(query_time) AS avg_time FROM mysql.slow_log WHERE start_time >= DATE_SUB(NOW(), INTERVAL 7 DAY) AND query_sql LIKE '%Using%filesort%' GROUP BY LEFT(query_sql, 100) ORDER BY exec_count DESC LIMIT 10; ">>"$REPORT_FILE"2>&1 cat"$REPORT_FILE"
9. SQL改寫技巧
9.1 IN改寫為EXISTS/JOIN
-- 低效：IN子查詢 SELECT*FROMusers WHEREidIN(SELECTuser_idFROMordersWHEREamount >1000); -- MySQL 5.6+會(huì)自動(dòng)優(yōu)化為JOIN，但顯式寫法更清晰 SELECTDISTINCTu.* FROMusersu INNERJOINorders oONu.id = o.user_id WHEREo.amount >1000; -- EXISTS改寫 SELECT*FROMusersu WHEREEXISTS( SELECT1FROMorders o WHEREo.user_id = u.idANDo.amount >1000 );
9.2 OR改寫為UNION
-- 低效：OR條件 SELECT*FROMproducts WHEREcategory='electronics'ORbrand ='Apple'; -- 高效：UNION SELECT*FROMproductsWHEREcategory='electronics' UNION SELECT*FROMproductsWHEREbrand ='Apple'ANDcategory!='electronics'; -- 或者使用UNION ALL（如果確認(rèn)不重復(fù)） SELECT*FROMproductsWHEREcategory='electronics' UNIONALL SELECT*FROMproductsWHEREbrand ='Apple'ANDcategory!='electronics';
9.3 LIKE改寫
-- 低效：前導(dǎo)通配符 SELECT*FROMproductsWHEREnameLIKE'%iphone%'; -- 優(yōu)化方案1：全文索引 ALTERTABLEproductsADDFULLTEXTINDEXft_name (name); SELECT*FROMproductsWHEREMATCH(name) AGAINST('+iphone'INBOOLEANMODE); -- 優(yōu)化方案2：Elasticsearch -- 將數(shù)據(jù)同步到ES，使用ES搜索 -- 優(yōu)化方案3：使用虛擬列+索引 ALTERTABLEproductsADDCOLUMNname_first_charCHAR(1)GENERATEDAS(LEFT(name,1))STORED; CREATEINDEXidx_name_first_charONproducts(name_first_char); SELECT*FROMproductsWHEREname_first_char ='i'ANDnameLIKE'i%iphone%';
9.4 COUNT(DISTINCT)優(yōu)化
-- 低效：多個(gè)COUNT DISTINCT SELECT COUNT(DISTINCTuser_id)ASuser_count, COUNT(DISTINCTproduct_id)ASproduct_count, COUNT(DISTINCTcategory_id)AScategory_count FROMorders; -- 高效：使用子查詢 SELECT (SELECTCOUNT(DISTINCTuser_id)FROMorders)ASuser_count, (SELECTCOUNT(DISTINCTproduct_id)FROMorders)ASproduct_count, (SELECTCOUNT(DISTINCTcategory_id)FROMorders)AScategory_count; -- 或者使用SQL_CALC_FOUND_ROWS（已廢棄） SELECTSQL_CALC_FOUND_ROWS*FROMordersLIMIT20; SELECTFOUND_ROWS();
10. 表結(jié)構(gòu)設(shè)計(jì)優(yōu)化
10.1 選擇合適的數(shù)據(jù)類型
-- 使用最小數(shù)據(jù)類型 TINYINT vs INT vs BIGINT -- TINYINT: -128 to 127 (無符號(hào)0-255) -- INT: -2B to 2B (無符號(hào)0-4B) -- BIGINT: -9 Quintillion to 9 Quintillion -- 使用DECIMAL而非FLOAT/DOUBLE（金融場(chǎng)景） DECIMAL(10,2) vs FLOAT vs DOUBLE -- DECIMAL精確存儲(chǔ)，適合金額 -- FLOAT/DOUBLE近似值，有精度丟失風(fēng)險(xiǎn) -- VARCHAR vs CHAR CHAR(10) -- 固定長度，不足右補(bǔ)空格，適合定長如手機(jī)號(hào) VARCHAR(255)-- 可變長度，適合大多數(shù)字符串 -- 日期類型選擇 DATE -- '2026-01-01' 精確到天 DATETIME -- '2026-01-01 1200' 精確到秒 TIMESTAMP-- 4字節(jié)，時(shí)間戳，適合記錄創(chuàng)建/更新時(shí)間
10.2 范式化與反范式化
-- 第三范式（3NF）設(shè)計(jì) -- 訂單表：只存user_id，不存用戶詳細(xì)信息 CREATETABLEorders ( order_idBIGINTPRIMARYKEY, user_idINTNOTNULL, total_amountDECIMAL(10,2), create_time DATETIME, INDEXidx_user_id (user_id) ); -- 用戶表：用戶詳細(xì)信息 CREATETABLEusers( user_idINTPRIMARYKEY, user_nameVARCHAR(100), emailVARCHAR(200), phoneVARCHAR(20) ); -- 如果需要關(guān)聯(lián)查詢，使用JOIN SELECTo.*, u.user_name FROMorders o JOINusersuONo.user_id = u.user_id; -- 反范式化：冗余數(shù)據(jù)提升查詢性能 -- 適合讀多寫少、實(shí)時(shí)性要求不高的場(chǎng)景 CREATETABLEorders_denormalized ( order_idBIGINTPRIMARYKEY, user_idINTNOTNULL, user_nameVARCHAR(100), -- 冗余存儲(chǔ)，避免JOIN total_amountDECIMAL(10,2), create_time DATETIME );
10.3 分區(qū)表
-- 按日期分區(qū) CREATETABLEorders ( order_idBIGINT, user_idINT, total_amountDECIMAL(10,2), create_time DATETIME, PRIMARYKEY(order_id, create_time) -- 必須包含分區(qū)鍵 )PARTITIONBYRANGE(YEAR(create_time)) ( PARTITIONp2024VALUESLESSTHAN(2025), PARTITIONp2025VALUESLESSTHAN(2026), PARTITIONp2026VALUESLESSTHAN(2027), PARTITIONp_futureVALUESLESSTHANMAXVALUE ); -- 查詢特定分區(qū)的數(shù)據(jù)（只掃描目標(biāo)分區(qū)） SELECT*FROMordersWHEREcreate_time >='2026-01-01'ANDcreate_time
10.4 分庫分表
-- 按用戶ID哈希分表（邏輯上） -- 實(shí)際實(shí)現(xiàn)依賴中間件如 ShardingSphere、MyCAT -- 示例：訂單表拆分為4張表 orders_0, orders_1, orders_2, orders_3 -- 分片規(guī)則：user_id % 4 -- 查詢時(shí)需要指定分片鍵 SELECT*FROMorders_1WHEREuser_id =123; -- 全局表（數(shù)據(jù)量小，所有分片都冗余存儲(chǔ)） CREATETABLEcategories ( idINTPRIMARYKEY, nameVARCHAR(100) ); -- 復(fù)制到所有分片
11. 總結(jié)：索引使用避坑指南
11.1 索引設(shè)計(jì)原則
【核心原則】 1. 為WHERE、ORDER BY、GROUP BY的列創(chuàng)建索引 2. 索引列盡量選擇區(qū)分度高的列 3. 聯(lián)合索引遵循最左前綴原則 4. 避免在索引列上使用函數(shù)或運(yùn)算 5. 控制索引數(shù)量（每個(gè)索引占用磁盤空間） 【創(chuàng)建索引的場(chǎng)景】 - 主鍵自動(dòng)有索引，無需額外創(chuàng)建 - 外鍵列創(chuàng)建索引（提升JOIN性能） - 經(jīng)常作為查詢條件的列 - 經(jīng)常需要排序的列 - 區(qū)分度高的列（cardinality高） 【避免創(chuàng)建索引的場(chǎng)景】 - 區(qū)分度低的列（如性別、狀態(tài)） - 更新頻繁的列 - 表數(shù)據(jù)量很小
11.2 慢查詢優(yōu)化Checklist
【第一步：發(fā)現(xiàn)】 □ 確認(rèn)慢查詢?nèi)罩疽验_啟 □ 找到最慢的查詢 □ 記錄查詢時(shí)間和影響行數(shù) 【第二步：分析】 □ 使用EXPLAIN分析執(zhí)行計(jì)劃 □ 檢查type列（避免ALL/index） □ 檢查Extra列（避免Using filesort/temporary） □ 確認(rèn)索引是否被使用 【第三步：優(yōu)化】 □ 添加/修改索引 □ 改寫SQL語句 □ 優(yōu)化表結(jié)構(gòu) □ 減少查詢范圍 【第四步：驗(yàn)證】 □ 重新執(zhí)行查詢，對(duì)比時(shí)間 □ 再次使用EXPLAIN確認(rèn) □ 監(jiān)控慢查詢?nèi)罩敬_認(rèn)改善
11.3 常用優(yōu)化命令速查
操作 SQL 查看慢查詢配置 SHOW VARIABLES LIKE 'slow_query%'; 開啟慢查詢?nèi)罩?/td> SET GLOBAL slow_query_log = ON; 設(shè)置閾值 SET GLOBAL long_query_time = 1; 分析執(zhí)行計(jì)劃 EXPLAIN SELECT ... 查看索引 SHOW INDEX FROM table_name; 創(chuàng)建索引 CREATE INDEX idx_name ON table(col); 刪除索引 DROP INDEX idx_name ON table; 查看表狀態(tài) SHOW TABLE STATUS LIKE 'table_name'; 11.4 EXPLAIN結(jié)果速判
【type- 從優(yōu)到差】 const > eq_ref > ref > range > index > ALL 【Extra - 需要優(yōu)化的標(biāo)志】 Using filesort 需要優(yōu)化 Using temporary 需要優(yōu)化 Usingwhere 可能需要優(yōu)化 Using index 覆蓋索引，好 Using index condition ICP，可接受
參考信息
版本信息：
MySQL：8.0.36（社區(qū)版）
Percona Server：8.0.36
MariaDB：10.11.x
存儲(chǔ)引擎：InnoDB（默認(rèn)）
操作系統(tǒng)：Rocky Linux 9.4
工具推薦：
Percona Toolkit：pt-query-digest、pt-index-usage
MySQL Workbench：圖形化EXPLAIN
performance_schema：查詢性能分析
sys schema：性能診斷視圖
參考文檔：
MySQL 8.0 Reference Manual: Optimization
High Performance MySQL, 3rd Edition
MySQL Internals Manual
聲明：本文內(nèi)容及配圖由入駐作者撰寫或者入駐合作網(wǎng)站授權(quán)轉(zhuǎn)載。文章觀點(diǎn)僅代表作者本人，不代表電子發(fā)燒友網(wǎng)立場(chǎng)。文章及其配圖僅供工程師學(xué)習(xí)之用，如有內(nèi)容侵權(quán)或者其他違規(guī)問題，請(qǐng)聯(lián)系本站處理。
舉報(bào)投訴
-
數(shù)據(jù)庫
+關(guān)注
關(guān)注
7文章
4078瀏覽量
68519 -
MySQL
+關(guān)注
關(guān)注
1文章
928瀏覽量
29737
原文標(biāo)題：MySQL 慢查詢調(diào)優(yōu)：別讓索引背鍋，你真的用對(duì)了嗎？
文章出處：【微信號(hào)：magedu-Linux，微信公眾號(hào)：馬哥Linux運(yùn)維】歡迎添加關(guān)注！文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
熱點(diǎn)推薦
如何對(duì)電機(jī)進(jìn)行調(diào)優(yōu)？調(diào)優(yōu)的好處是什么？
如何自動(dòng)對(duì)電機(jī)進(jìn)行調(diào)優(yōu)
MySQL 基本知識(shí)點(diǎn)梳理和查詢優(yōu)化
本文主要是總結(jié)了工作中一些常用的操作，以及不合理的操作，在對(duì)慢查詢進(jìn)行優(yōu)化時(shí)收集的一些有用的資料和信息，適合有 MySQL 基礎(chǔ)的開發(fā)人員。
MySQL查詢幫助的使用
在使用MySQL過程中，當(dāng)遇到操作語法、數(shù)據(jù)類型的取值范圍、功能是否支持等問題時(shí)，可以使用MySQL自帶的幫助文檔查詢。
總結(jié)一下MySQL常用的調(diào)優(yōu)方法
在my.cnf中加上skip-name-resolve，這樣可以避免由于解析主機(jī)名延遲造成mysql執(zhí)行慢；
查詢SQL在mysql內(nèi)部是如何執(zhí)行？
我們知道在mySQL客戶端，輸入一條查詢SQL,然后看到返回查詢的結(jié)果。這條查詢語句在 MySQL 內(nèi)部到底是如何執(zhí)行的呢？本文跟大家探討一
AM6xA ISP調(diào)優(yōu)指南
電子發(fā)燒友網(wǎng)站提供《AM6xA ISP調(diào)優(yōu)指南.pdf》資料免費(fèi)下載
發(fā)表于 09-07 09:52
?0次下載
TAS58xx系列通用調(diào)優(yōu)指南
電子發(fā)燒友網(wǎng)站提供《TAS58xx系列通用調(diào)優(yōu)指南.pdf》資料免費(fèi)下載
發(fā)表于 09-14 10:49
?1次下載
MCT8315A調(diào)優(yōu)指南
電子發(fā)燒友網(wǎng)站提供《MCT8315A調(diào)優(yōu)指南.pdf》資料免費(fèi)下載
發(fā)表于 11-12 14:14
?1次下載
MCT8316A調(diào)優(yōu)指南
電子發(fā)燒友網(wǎng)站提供《MCT8316A調(diào)優(yōu)指南.pdf》資料免費(fèi)下載
發(fā)表于 11-13 13:49
?0次下載
MCF8316A調(diào)優(yōu)指南
電子發(fā)燒友網(wǎng)站提供《MCF8316A調(diào)優(yōu)指南.pdf》資料免費(fèi)下載
發(fā)表于 11-20 17:21
?2次下載
MySQL配置調(diào)優(yōu)技巧
上個(gè)月，我們公司的核心業(yè)務(wù)系統(tǒng)突然出現(xiàn)大面積超時(shí)，用戶投訴電話不斷。經(jīng)過緊急排查，發(fā)現(xiàn)是MySQL服務(wù)器CPU飆升到99%，大量慢查詢堆積。通過一系列配置調(diào)
MySQL慢查詢終極優(yōu)化指南
作為一名在生產(chǎn)環(huán)境摸爬滾打多年的運(yùn)維工程師，我見過太多因?yàn)?b class='flag-5'>慢查詢導(dǎo)致的線上故障。今天分享一套經(jīng)過實(shí)戰(zhàn)檢驗(yàn)的MySQL慢查詢分析與索引優(yōu)化方法
MySQL慢查詢分析與索引調(diào)優(yōu)全流程
MySQL 性能問題在生產(chǎn)環(huán)境中的表現(xiàn)通常是漸進(jìn)式的：業(yè)務(wù)量增長、數(shù)據(jù)量膨脹，某天突然發(fā)現(xiàn) P99 響應(yīng)時(shí)間從 50ms 漲到 2s。慢查詢是最常見的根因，而索引設(shè)計(jì)不合理又是慢
MySQL數(shù)據(jù)庫慢查詢分析與優(yōu)化實(shí)戰(zhàn)
在討論MySQL慢查詢之前，需要先明確一個(gè)關(guān)鍵前提：什么是慢查詢？ 不同業(yè)務(wù)場(chǎng)景下，慢
評(píng)論