文章目錄

• • 1. sql執行生命周期
  • • 1.1 客戶端/服務端連接
    • 1.2 查詢優化處理
    • • 優化器作用
      • 優化策略
    • 1.3 統計數據
    • 1.4 mysql關聯查詢
    • 1.5 執行計劃
    • 1.6 排序優化
  • 2. 優化數據訪問
  • • 2.1 查詢數據過多
    • 2.2 掃描數據過多
    • 2.3 重構查詢條件
  • 3. mysql相關不足
  • • 3.1 EXISTS語法以及改進
    • 3.2 UNION 合并
    • 3.3 索引合并優化
    • 3.4 松散索引掃描
    • 3.5 最大最小值優化
    • 3.6 同時更新查找
  • 4. 查詢優化器提示hint

1. sql執行生命周期

? 想要對sql進行一些相關優化，我們需要了解查詢sql的整個生命周期，sql的執行流程分成了如下圖多個子任務，只要通過降低或者避免其中子任務的耗時，則整個sql性能就能得到提高。

客戶端連接服務端并發送請求，執行之前還會做權限的判斷。

在查詢緩存中,看該SQL有沒有執行過,如果有查詢過,則把緩存結果返回。在MySQL內部一個查詢緩存,查詢緩存默認是不開啟的。

因為查詢緩存要求SQL和參數都要一樣,所以這個命中率是非常低的。

沒有開啟查詢緩存或沒有命中,那么就到了解析器,解析器主要對SQL語法進行解析生成解析樹。

• 驗證sql的語法錯誤 關鍵字順序，別名等sql語法規范。
• 檢查數據表和數據列是否存在
• 驗證權限

得到沒有經過優化解析樹之后,需要對這棵樹進行預處理，得到優化后的解析數。比如常量放在什么地方,如果有計算的東西,把計算的結果算出來等等…

查詢優化器：這一步是MySQL性能中,最關鍵的核心點,也是我們的優化原則.我們平時所講的優化SQL。該優化器基于最低成本的執行計劃。

SELECT * FROM USER WHERE USERNAME = toby AND PASSWORD = 1是先執行username = toby還是password = 1? 每一條SQL的執行，查詢優化器就是根據MySQL對數據統計表的一些信息,比如索引,數據量,會根據統計數據,進行一個綜合的判定選擇一個運行最快查詢計劃。最低成本:由于mysql自身的統計信息可能不準確的,MySQL根據這些信息選了一個它自認為最優的方案,但這個方案可能和我們想象的不一樣。

執行計劃會傳給查詢執行引擎,執行引擎選擇存儲引擎來執行查詢計劃(涉及到磁盤IO讀取)，獲取對應的結果并返回。

如果開了查詢緩存,則返回結果給客戶端,并且查詢緩存也放一份。

1.1 客戶端/服務端連接

? mysql客戶端和服務端的連接是”半雙工“通信，則每次客戶端請求后，需要等待服務端將所有的數據返回給客戶端才能結束。

同時每一個客戶端與服務端的連接都有如下狀態：

• sleep: 服務端線程正在等待客戶端發送請求。
• Query: 服務端線程正在執行查詢/給客戶端發送結果。
• locked: 服務端線程正在等待表鎖。
• Analying and Statistics: 正在收集存儲引擎的統計信息，并生成查詢計劃。
• Copying to tmp table [on disk]: 線程在執行查詢并將結果復制到臨時表中，如果含on disk 則說明在將臨時表復制到磁盤中。
• Sorting result: 正在對結果集進行排序。
• Sending Data : 線程在多個狀態傳輸數據、生成結果集、向客戶端返回數據。

1.2 查詢優化處理

優化器作用

對于一條查詢有多種執行方式，優化器作用會基于成本(成本最低)選擇一條‘最好’的執行計劃。

-- 每當執行一個查詢 可以通過查詢當前會話的Last_query_cost獲取其查詢成本 selecr * from #{tableName} where #{query}-- 獲取當前查詢成本 SHOW STATUS LIKE "Last_query_cost"

? 上述圖顯示 查詢大概需要做12個數據頁的隨機查找才能完成。

多種原因會導致mysql優化器選擇錯誤的執行計劃

• 統計信息不準確
• 執行估算成本等于真實的成本、不考慮并發影響
• 我們期望時間短，優化器考慮成本最優

優化策略

靜態優化：簡單的代數、常量進行等價轉換，編譯時優化，一次優化，多次執行。

動態優化：每次執行根據數據量和索引優化執行。

• 重排關聯表的順序。

• 外鏈接轉換為內連接

• 等價變幻 (a=5 and a>5 = a>=5)

• count()、min()和max() B-tree 可以很快獲取到min 和max 通過explain 為"select tables optimized away" 常數計算。

• 轉換為常數表達式 explain: type = const

• 覆蓋索引

• 自查詢優化

• 提前終止查詢 比如空結果、limit。

• 等值傳播

  select a.fid from a inner join b using(fid) where a.fid >500 -- 等值傳播 select a.fid from a inner join b where a.fid > 500 and b.fid >500

• IN()查找 等待于mysql的 多個等值查找 適合列的取值重復。

1.3 統計數據

統計信息由存儲引擎實現，mysql生成查詢執行計劃時候從存儲引擎獲取統計信息，統計信息包括

• 表/索引占用的頁數(頁 存儲引擎基本單元)

• 表的索引基數

  基數又叫索引基數，是數據列所包含的不同值的數量。

  例如，某個數據列包含值1、3、7、4、7、3，那么它的基數就是4

• 數據行/索引長度

  數據行長度：每行數據長度限制

  索引行長度：索引列長度限制

• 索引的分布情況

1.4 mysql關聯查詢

關聯在mysql中并非指兩個表以上的查詢，而是每一次查詢都是關聯查詢

Mysql關聯執行的策略，任何關聯查詢均為嵌套循環關聯操作,所謂嵌套循環為：現在一張表中取出符合條件的數據，循環取出數據查找下一個表匹配的行，以此類推最終找到所有匹配的行，根據行獲取到對應的查找列。

for(int i =0 i<num;i++){for(int j = 0;j<num2;j++){for(int k = 0;k<num3;k++){//...以此往復}} }

• union查詢 一系列的單個查詢結果都放到一個臨時表中，最終再從臨時表獲取完整數據。
• 子查詢 有子查詢先執行子查詢將結果放入臨時表,在將其當作普通表，在進行關聯查詢。

對于n表關聯，則嵌套循環會有 n階乘或者n-1階乘關聯順序，關聯順序種類被稱為執行計劃的‘’搜索空間“，當搜索空間超過optimizer_search_depth的時候，優化器不會評估每一個關聯順序成本，而是使用貪婪搜索的方式獲取最優關聯順序。

1.5 執行計劃

mysql的執行計劃是一棵包含全部查詢的指令樹，存儲引擎執行這棵指令樹并返回結果

-- 查看執行計劃 EXPLAIN EXTENDED ${querySql};SHOW WARNINGS; # 強制不改變 查詢條件為內連接的表關聯 select STRAIGHT_JOIN {querySql} STRAIGHT_JOIN與 JOIN 類似，只不過左表始終在右表之前讀取。這可用于聯接優化器以次優順序處理表的那些（少數）情況。 STRAIGHT_JOIN只適用于內連接，因為left join、right join已經知道了哪個表作為驅動表，哪個表作為被驅動表

1.6 排序優化

排序是一個成本很高的操作，文件排序會使用很大的臨時存儲空間，如果可能避免排序或者使用索引排序。 索引排序參考之前博文高性能mysql學習筆記二構建高性能索引

當不能使用索引排序，mysql需要自行進行排序，排序方式數據量是否超過“排序緩沖區”大小不同，如果不超過該閾值內存排序,否則磁盤排序，兩者都統稱為文件排序。

磁盤排序：先將數據分開，對每個塊使用快速排序，最終各個排好序的塊進行合并。

排序算法：

• 兩次傳輸排序：第一次讀取行指針和排序字段進行排序，再次根據排好序的結果再次讀取記錄第二次可能會產生大量隨機IO)，因為只讀行指針和排序字段，可以讓緩存區容納更多數據進行排序。
• 單次傳輸排序：讀取所需要數據，根據字段進行排序最后返回排序結果，減少IO操作，缺點會占用大量的空間。

同時mysql排序需要使用大量的臨時存儲空間。

關聯表的排序

• 所有排序字段均來自第一個表：extra字段為“using filesort”

• 非上述情況，mysql會先將關聯的結果存放臨時表，再進行文件排序，extra字段"using temporary;Using filesort"。

2. 優化數據訪問

2.1 查詢數據過多

• 查詢數據不帶分頁但是只獲取其中前n條數據
• 關聯查詢或者查詢了過多的列
• 重復相同請求。

這個產生的性能問題屬于低級錯誤，大概率開發不會犯這種問題。

2.2 掃描數據過多

是否掃描了過多的數據性能指標

響應時間：需要區分是排隊時間(等待鎖，等待資源) + 執行sql時間。

掃描的行數返回的行數: 通過EXPLAIN分析sql。主要看type字段和extra字段。

mysql性能分析 第二章節。

2.3 重構查詢條件

復雜查詢簡化成多個簡單查詢（具體問題具體分析，衡量多個查詢和一個復雜查詢成本優劣）。

數據分治處理，比如批量更新、刪除不必一次性全部執行，可以分成多個部分執行，減輕對服務器的壓力，不必鎖太多的數據。

分解關聯查詢

select * from tagleft join tag_post on tag.tag_id = tag_post.tag_idleft join post on post.post_id = tag_post.post_id where tag.name = "mysql"#轉換為三個單表查詢 select * from tag where name = "mysql"select * from tag_post where tag_id in (xxx,xxxx)select * from post where post_id in (xxx,xxxx,xxxx,xxxx)

• 緩存，轉換后的第一條sql 如果開啟了緩存，則重復查詢會直接獲取到緩存。
• 等值查詢 后面的兩個sql使用in可以使用多行等值查詢。
• 單表查詢減少了鎖的競爭。
• 關聯查詢會造成同一條記錄重復掃描。
• 便于以后數據表拆分。

3. mysql相關不足

-- 語法示例 SELECT 字段 FROM table WHERE EXISTS (subquery); -- 語法示例 SELECT 字段 FROM table WHERE cloumn_name in (subquery); -- 例子 SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.id = A.id); SELECT * FROM A WHERE EXISTS A.id in(SELECT b.id FROM B WHERE B.cloumn = ${query});

上述使用exists 和in語法涉及到了相關子查詢，主表A的數據依賴內部子查詢的結果。

EXISTS / NOT EXISTS 用于檢查子查詢是否至少會返回一行數據，該子查詢實際上并不返回任何數據，而是返回布爾值true或false>

3.1 EXISTS語法以及改進

創建兩張表，一張singer 歌手表，一張song 歌曲表，兩表之間的關聯關系為1對多。

DROP TABLE IF EXISTS `singer`;CREATE TABLE `singer` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `signer_id` int(11) DEFAULT NULL COMMENT 'qq音樂的主鍵', `signer_mid` varchar(50) DEFAULT NULL COMMENT 'qq音樂唯一標識', `full_name` varchar(50) DEFAULT NULL COMMENT '歌手全名', `english_name` varchar(50) DEFAULT NULL COMMENT '英文名', `short_name` varchar(50) DEFAULT NULL COMMENT '歌手簡稱（別名 多個）', `contract_id` varchar(10) DEFAULT NULL COMMENT '國籍', PRIMARY KEY (`id`) USING BTREE, KEY `IDX_SINGER_MID` (`signer_mid`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;DROP TABLE IF EXISTS `song`;CREATE TABLE `song` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主鍵', `song_id` int(11) DEFAULT NULL COMMENT 'qq音樂id', `song_mid` varchar(50) DEFAULT NULL COMMENT 'qq音樂mid', `song_name` varchar(50) DEFAULT NULL COMMENT '歌曲名稱', `song_type` varchar(5) DEFAULT NULL COMMENT '歌曲類型', `album_id` varchar(50) DEFAULT NULL COMMENT '所屬專輯', `singer_mid` varchar(255) DEFAULT NULL COMMENT '所屬歌手' PRIMARY KEY (`id`) USING BTREE, KEY `IDX_NAME` (`song_name`)) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

場景如下：查找指定歌曲所屬歌手，singer表中的singer_mid和song表中的name為普通索引

exists

SELECT si.full_name FROM singer siWHERE EXISTS (SELECT 1 FROM song WHERE si.signer_mid = song.`singer_mid` AND song_name = "畫");

執行順序:

對該語句進行explain分析如下：先全表掃描singer表，在將掃描結果帶入自查詢進行song表的比較。

-- explain extended + show warnings 顯示真正執行的sqlselect `utopia`.`si`.`full_name` AS `full_name` from `utopia`.`singer` `si` where exists( select 1 from `utopia`.`song` where (( `utopia`.`si`.`signer_mid` = `utopia`.`song`.`singer_mid`) and (`utopia`.`song`.`song_name` = '畫')))

從真實的執行sql來看，mysql并未對該sql語句進行優化。exists不會被mysql優化。

首先執行一次外部查詢，并緩存結果集，如 SELECT * FROM singer

遍歷外部查詢結果集的每一行記錄R，代入子查詢中作為條件進行查詢, SELECT 1
FROM. song WHERE si.signer_mid = song.singer_mid AND song_name = “畫”。

如果子查詢有返回結果，則EXISTS子句返回TRUE(NOT EXISTS 相反返回FALSE)，這一行R可作為外部查詢的結果行，否則不能作為結果。

in語句

SELECT si.full_name FROM singer si WHERE si.signer_mid IN (SELECT singer_mid FROM song so WHERE so.song_name = "畫")

explain 分析

select `utopia`.`si`.`full_name` AS `full_name` from `utopia`.`singer` `si` semi join (`utopia`.`song` `so`) where ((`utopia`.`so`.`song_name` = '畫') and (`utopia`.`si`.`signer_mid` = `<subquery2>`.`singer_mid`))

Semi join 半連接查詢: 檢查一個結果集（外表）的記錄是否在另外一個結果集（字表）中存在匹配記錄，半連接僅關注”子表是否存在匹配記錄”，而并不考慮”子表存在多少條匹配記錄”，半連接的返回結果集僅使用外表的數據集，查詢語句中IN或EXISTS語句常使用半連接來。

可能需要臨時表存儲數據，但是比上面的exists 性能要好很多。

join

SELECT si.full_nameFROM singer si INNER JOIN song so ON si.signer_mid = so.`singer_mid` WHERE so.song_name = "畫"

<b<explain分析

簡單的關聯查詢，首先執行song 使用IDX_NAME索引，再執行singer表 使用singer_mid 索引查找，查找性能比上邊的更好

3.2 UNION 合并

在union語句中mysql無法將外部的限制條件作用到每一條子句內，所以如果想兩張表匯取出前20條，需要按照相同的排序條件和limit作用到每一個字據中

-- 耗性能的寫法-- 會將A表和B表所有排序的數據匯總到臨時表中再取出其中20條(SELECT A.name from A order by nameUNION ALLSELECT B.name from B order by name)limit 20；-- 正確的寫法(SELECT A.name from A order by name limit 20 UNION ALLSELECT B.name from B order by name limit 20)limit 20；

3.3 索引合并優化

同一張表的 where中多個條件(或者join)進行AND/OR操作可能會使用到 index merge 技術。index merge：對多個索引分別進行條件掃描，然后將它們各自的結果進行合并(intersect/union) ,參考本人好友的文章索引合并優化

索引合并并交集訪問算法（Index Merge Intersection Access Algorithm）：對所有的索引同時執行掃描，掃描的結果取交集。

索引合并聯合訪問算法（Index Merge Union Access Algorithm）：對所有的索引同時執行掃描，掃描的結果取并集。

select * from song where song_name ="潛力" OR singer_mid = "000P8peU0HhORi"

**索引合并排序聯合訪問算法（Index Merge Sort-Union Access Algorithm）：**排序聯合算法和聯合算法之間的區別在于，排序聯合算法必須首先獲取所有行的行ID，然后對它們進行排序，然后再返回這些行。

EXPLAINselect * from song where song_name >"潛力" OR singer_mid = "000P8peU0HhORi"

3.4 松散索引掃描

mysql不持索引的松散(跳躍式)掃描，索引掃描需要先確定起點和終點，即使需要的數據很少也要掃描索引中每一條。且不支持hash索引和并行執行。

3.5 最大最小值優化

-- 如下字句會掃描全表 并獲取最小主鍵值，其實如果使用主見掃描 查詢第一條即為最小值select min(primaryKeCloumn) from table where noramCloumn = ${query} -- 調整如下 強制走主鍵掃描select min(primaryKeCloumn) from table USE INDEX(PRIMARY) where noramCloumn = ${query} limit

3.6 同時更新查找

mysql不支持對一張表同時進行查找和更新操作

UPDATE tb_testSET NAME = ( SELECT NAME FROM tb_test WHERE id= 2)WHERE `status` = 1-- 報錯如下：-- You can't specify target table 'tb_test' for update in FROM clause,-- 可以將查詢作為臨時表進行操作UPDATE tb_testSET NAME = (select name from ( SELECT NAME FROM tb_test WHERE id = 2) as t)WHERE `status` = 1

4. 查詢優化器提示hint

mysql的優化器并非萬能，他不一定按照我們想要的結果來進行優化執行，所以mysql提供了**查詢優化提示（Query Optimizer Hints）**讓我們去引導優化器進行更好的優化。

查詢優化提示詳細說明使用場景

HIGH_PRIORITY	優先操作，多條語句訪問某個表時，使用該hint 的語句可以優先執行，簡單的控制訪問表的優先順序	作用在insert/update/select語句中，只適用于表鎖引擎。
LOW_PRIORITY	滯后操作，條語句訪問某個表時，使用該hint 的語句最后執行，簡單的控制訪問表的優先順序	同HIGH_PRIORITY
DELAYED	使用該提示立即返回數據并將插入或修改的數據行放在緩存中，在數據庫閑時寫入磁盤	適用于 insert 和 replace，
straight_join	強制連接順序 SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE…; 讓關聯表按照出現的順序進行關聯	放在select關鍵字之后，或者兩個關聯表之間
SQL_SMALL_RESUL TSQL_BIG_RESULT	sql_small_result 告訴優化器結果集會很小，可以將結果集放在內存中的索引臨時表，以避免排序操作；sql_big_result 則告訴優化器結果集會很大，建議使用磁盤臨時表做排序操作；	這兩個提示只對select語句有效，它們告訴優化器對 group by 或者 distinct 查詢如何使用臨時表及排序。
sql_buffer_result	告訴優化器將查詢放入到一個臨時表，然后盡可能地釋放鎖
SQL_NO_CACHE	獲取實時數據，每次不獲取緩存區，每次執行sql
SQL_CACHE	必須要開啟緩存，才能使用該關鍵字進行緩存查詢
for update lock in share mode	這兩個提示主要控制select 語句的鎖機制。但只對實現了行級鎖的存儲引擎有效。使用該提示會對符合查詢條件的數據加鎖。
USE INDEX IGNORE INDEX FORCE INDEX	使用關鍵字 force index 來強制使用某個索引 使用 use index 的索引提示來使用某個索引 使用關鍵字 ignore index 來忽略某個索引

總結

以上是生活随笔為你收集整理的高性能mysql读书笔记三性能查询优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。