MySQL優化目标
✓減少磁盤IO
可從硬體，是否全表掃描，磁盤臨時表，日志、資料塊fsync，系統配置等方面着手
✓減少網絡帶寬
可從傳回太多資料，互動次數過多，庫表結構等方面考慮
✓降低CPU消耗
可從排序分組 order by, group by，SQL及索引，聚合函數：max,min,sum...等方面着手
優化流程及思路
關注的名額
➢CPU使用率
• sql查詢關鍵資源名額
• 資料掃描、顯式計算
➢IOPS
• 每秒io請求次數
• 實體讀寫關鍵資源名額
➢QPS/TPS
• 吞吐量
• 業務壓力
➢會話數/活躍會話數
• 應用配置
• 執行效率
➢Innodb邏輯讀/實體讀
• 反映整體查詢效率的引擎名額
➢臨時表
• 導緻SQL執行效率下降的特殊行為
MySQL優化流程
建構完備的監控體系
• 細緻合理的告警
• 多元度圖形化名額
• 暴露性能缺陷，掌控大規模資源
分析定位問題
• 異常時間區間
定性分析
監控你的系
• System log、DB Error Log 統
• Slow Log
• SQL執行統計
• session
SQL優化原則與方法
原則
⚫減少通路量：資料存取是資料庫系統最核心功能，是以IO是資料庫系統中最容易出現性能瓶頸，減少SQL訪
問IO量是SQL優化的第一步；資料塊的邏輯讀也是産生CPU開銷的因素之一。
• 減少通路量的方法：建立合适的索引、減少不必通路的列、使用索引覆寫、語句改寫。
⚫減少計算操作：計算操作進行優化也是SQL優化的重要方向。SQL中排序、分組、多表連接配接操作等計算操作
都是CPU消耗的大戶。
• 減少SQL計算操作的方法：排序列加入索引、适當的列備援、SQL拆分、計算功能拆分。
方法
• 建立索引減少掃描量
• 調整索引減少計算量
• 索引覆寫（減少不必通路的列，避免回表查詢）
• SQL改寫
• 幹預執行計劃
原理剖析
B+ Tree index
MySQL SQL執行過程
1.客戶送出一條語句
2.先在查詢緩存檢視是否存在對應的緩存資料，如
有則直接傳回(一般有的可能性極小，是以一般建
議關閉查詢緩存)。
3.交給解析器處理，解析器會将送出的語句生成一
個解析樹。
4.預處理器會處了解析樹，形成新的解析樹。這一
階段存在一些SQL改寫的過程。
5.改寫後的解析樹送出給查詢優化器。查詢優化器
生成執行計劃。
6.執行計劃交由執行引擎調用存儲引擎接口，完成
執行過程。這裡要注意，MySQL的Server層和
Engine層是分離的。
7.最終的結果由執行引擎傳回給用戶端，如果開啟
查詢緩存的話，則會緩存。
MySQL SQL執行過程
SQL執行順序：
(8) SELECT (9) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP <group_by_list> (6) WITH {CUBE|ROLLUP}
(7) HAVING <having_condition> (10) ORDER BY <order_by_list> (11) LIMIT <limit_number> 18 MySQL優化器與執行計劃
查詢優化器
⚫負責生成 SQL 語句的有效執行計劃的資料庫元件
⚫優化器是資料庫的核心價值所在，它是資料庫的“大腦”
⚫優化SQL，某種意義上就是了解優化器的行為
⚫優化的依據是執行成本（CBO）
⚫優化器工作的前提是了解資料，工作的目的是解析SQL，生成執行計劃
MySQL優化器與執行計劃
查詢優化器工作過程
1.詞法分析、文法分析、語義檢查
2.預處理階段(查詢改寫等)
3.查詢優化階段(可詳細劃分為邏輯優化、物
理優化兩部分)
4.查詢優化器優化依據，來自于代價估算器估
算結果(它會調用統計資訊作為計算依據)
5.交由執行器執行
檢視和幹預執行計劃
執行計劃：
➢ explain [extended] SQL_Statement 優化器開關：
➢ show variables like 'optimizer_switch'
processlist
➢ show [full] processlist
➢ information_schema.processlist copy to tmp table：出現在某些alter table語句的copy table操作
Copying to tmp table on disk：由于臨時結果集大于tmp_table_size，正在将臨時表
從記憶體存儲轉為磁盤存儲以此節省記憶體
converting HEAP to MyISAM：線程正在轉換内部MEMORY臨時表到磁盤MyISAM臨
時表
Creating sort index：正在使用内部臨時表處理select查詢
Sorting index：磁盤排序操作的一個過程
Sending data ：正在處理SELECT查詢的記錄，同時正在把結果發送給用戶端
Waiting for table metadata lock: 等待中繼資料鎖
...
正常優化政策
SELECT優化-order by
order by查詢的兩種情況：
• Using index。MySQL直接通過索引傳回有序記錄，不需要額外的排序操作，操作效率較高
• Using filesort。無法隻通過索引擷取有序結果集，需要額外的排序，某些特殊情況下，會出現
Using temporary
優化目标：盡量通過索引來避免額外的排序，減少CPU資源的消耗
✓where條件和order by使用相同的索引
✓order by的順序和索引順序相同
✓order by的字段同為升序或降序
注：當where條件中的過濾字段為覆寫索引的字首列，而order by字段是第二個索引列時，隻有
where條件是const比對時，才可以通過索引消除排序，而between...and或>?、<?這種range比對
都無法避免filesort操作
SELECT優化-order by
當無法避免filesort操作時，優化思路就是讓filesort的操作更快
排序算法：
• 兩次掃描算法。兩次通路資料，第一步擷取排序字段的行指針資訊，在記憶體中排序，第二步根據
行指針擷取記錄
• 一次掃描算法。一次性取出滿足條件的所有記錄，在排序區中排序後輸出結果集。是采用空間換
時間的方式
注：需要排序的字段總長度越小，越趨向于第二種掃描算法，MySQL通過
max_length_for_sort_data參數的值來進行參考選擇
優化政策：
1、适當調大max_length_for_sort_data這個參數的值，讓優化器更傾向于選擇第二種掃描算法
2、隻使用必要的字段，不要使用select *的寫法
3、适當加大sort_buffer_size這個參數的值，避免磁盤排序的出現（線程參數，不要設定過大）
SELECT優化-Subquery
➢子查詢會用到臨時表，需盡量避免
➢可以使用效率更高的join查詢來替代
優化政策：
等價改寫、反嵌套
如下SQL：
select * from customer where customer_id not in (select customer_id from payment) 改寫形式：
select * from customer a left join payment b on a.customer_id=b.customer_id where b.customer_id is null 26 SELECT優化-limit
➢分頁查詢，就是将過多的結果在有限的界面上分好多頁來顯示。
➢其實質是每次查詢隻傳回有限行，翻頁一次執行一次。
優化目标：
1、消除排序
2、避免掃描到大量不需要的記錄
SQL場景（film_id為主鍵）：
select film_id,description from film order by title limit 10000,20 此時MySQL排序出前10020條記錄後僅僅需要傳回第10001到10020條記錄，前
10000條記錄造成額外的代價消耗
SELECT優化-limit
優化政策一：
覆寫索引
alter table film add index idx_lmtest(title,description); • 記錄直接從索引中擷取，效率最高
• 僅适合查詢字段較少的情況
優化政策二：
SQL改寫
select a.film_id,a.description from film a inner join (select film_id from film order by title limit 1000,20) b on a.film_id=b.film_id; • 優化的前提是title字段有索引
• 思路是從索引中取出20條滿足條件記錄的主鍵值，然後回表擷取記錄
SELECT優化-or/and condition
• and結果集為關鍵字前後過濾結果的交集
• or結果集為關鍵字前後分别查詢的并集
OR
• and條件可以在前一個條件過濾基礎上過濾
• or條件被處理為UNION，相當于兩個單獨條件的查詢
• 複合索引對于or條件相當于一個單列索引
處理政策：
✓and子句多個條件中擁有一個過濾性較高的索引即可
✓or條件前後字段均要建立索引
✓為最常用的and組合條件建立複合索引
join優化
• Nested-Loop Join算法
for each row in t1 matching range {
for each row in t2 matching reference key {
for each row in t3 {
if row satisfies join conditions, send to client
}
}
} • 關聯字段索引：每層内部循環僅擷取需要關心的資料
• 引申算法：Bloack Nested-Loop
• 小表驅動原則：減少循環次數
• 小表：傳回結果集較少的表
join優化
• 關聯字段索引的必要性
join優化
• 小表驅動原則
忽略b表的索引，使b表作為驅動表：
忽略a表的索引，使a表作為驅動表：
insert優化
優化政策一：
減少互動次數
如批量插入語句：
insert into test values(1,2,3); insert into test values(4,5,6); insert into test values(7,8,9); 可改寫為如下形式：
insert into test values(1,2,3),(4,5,6),(7,8,9) ...; 優化政策二：
文本裝載方式
通過LOAD DATA INFILE句式，從文本裝載資料，通常比insert語句快20倍
06 小結
MySQL查詢優化
✓優化的目的是讓資源發揮價值
✓SQL和索引是調優的關鍵，往往可以起到“四兩撥千斤”的效果
✓充分了解核心名額，并建構完備的監控體系，這是優化工作的前提
✓SQL優化的原則是減少資料通路及計算
✓常用的優化方法主要是調整索引、改寫SQL、幹預執行計劃
✓innodb的表是典型的IOT，資料本身是B+ tree索引的葉節點
✓掃描二級索引可以直接擷取資料，或者傳回主鍵id
✓優化器是資料庫的大腦，我們要了解優化器，并觀測以及幹預MySQL的行為