高性能mysql读书笔记三性能查询优化
文章目錄
- 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優化。
FROM. song WHERE si.signer_mid = song.singer_mid AND song_name = “畫”。
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} limit3.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` = 14. 查詢優化器提示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读书笔记三性能查询优化的全部內容,希望文章能夠幫你解決所遇到的問題。
                            
                        - 上一篇: 前端学习(159):meta
 - 下一篇: coreseek4.1