共計 6295 個字符，預計需要花費 16 分鐘才能閱讀完成。
本篇內容介紹了“MySQL 性能調優(yōu)之查詢優(yōu)化的方法”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！
一、查詢慢的原因
1、網(wǎng)絡
2、CPU
3、IO
4、上下文切換
5、系統(tǒng)調用
6、生成統(tǒng)計信息
7、鎖等待時間
二、優(yōu)化數(shù)據(jù)訪問
1、查詢性能低下的主要原因是訪問的數(shù)據(jù)太多，某些查詢不可避免的需要篩選大量的數(shù)據(jù)，我們可以通過減少訪問數(shù)據(jù)量的方式進行優(yōu)化
（1）確認應用程序是否在檢索大量超過需要的數(shù)據(jù)
（2）確認 mysql 服務器層是否在分析大量超過需要的數(shù)據(jù)行
2、是否向數(shù)據(jù)庫請求了不需要的數(shù)據(jù)
（1）查詢不需要的記錄（我們常常會誤以為 mysql 會只返回需要的數(shù)據(jù)，實際上 mysql 卻是先返回全部結果再進行計算，在日常的開發(fā)習慣中，經(jīng)常是先用 select 語句查詢大量的結果，然后獲取前面的 N 行后關閉結果集。優(yōu)化方式是在查詢后面添加 limit）
（2）多表關聯(lián)時返回全部列（select * from actor inner join film_actor using(actor_id) inner join film using(film_id) where film.title= Academy Dinosaur select actor.* from actor…;）
（3）總是取出全部列（在公司的企業(yè)需求中，禁止使用 select *, 雖然這種方式能夠簡化開發(fā)，但是會影響查詢的性能，所以盡量不要使用）
（4）重復查詢相同的數(shù)據(jù)（如果需要不斷的重復執(zhí)行相同的查詢，且每次返回完全相同的數(shù)據(jù)，因此，基于這樣的應用場景，我們可以將這部分數(shù)據(jù)緩存起來，這樣的話能夠提高查詢效率。）
三、執(zhí)行過程的優(yōu)化
1、查詢緩存
在解析一個查詢語句之前，如果查詢緩存是打開的，那么 mysql 會優(yōu)先檢查這個查詢是否命中查詢緩存中的數(shù)據(jù)，如果查詢恰好命中了查詢緩存，那么會在返回結果之前會檢查用戶權限，如果權限沒有問題，那么 mysql 會跳過所有的階段，就直接從緩存中拿到結果并返回給客戶端
2、查詢優(yōu)化處理
mysql 查詢完緩存之后會經(jīng)過以下幾個步驟：解析 SQL、預處理、優(yōu)化 SQL 執(zhí)行計劃，這幾個步驟出現(xiàn)任何的錯誤，都可能會終止查詢。
（1）語法解析器和預處理
mysql 通過關鍵字將 SQL 語句進行解析，并生成一顆解析樹，mysql 解析器將使用 mysql 語法規(guī)則驗證和解析查詢，例如驗證使用使用了錯誤的關鍵字或者順序是否正確等等，預處理器會進一步檢查解析樹是否合法，例如表名和列名是否存在，是否有歧義，還會驗證權限等等
（2）查詢優(yōu)化器
當語法樹沒有問題之后，相應的要由優(yōu)化器將其轉成執(zhí)行計劃，一條查詢語句可以使用非常多的執(zhí)行方式，最后都可以得到對應的結果，但是不同的執(zhí)行方式帶來的效率是不同的，優(yōu)化器的最主要目的就是要選擇最有效的執(zhí)行計劃。
mysql 使用的是基于成本的優(yōu)化器，在優(yōu)化的時候會嘗試預測一個查詢使用某種查詢計劃時候的成本，并選擇其中成本最小的一個。
a、select count(*) from film_actor; show status like last_query_cost
可以看到這條查詢語句大概需要做 1104 個數(shù)據(jù)頁才能找到對應的數(shù)據(jù)，這是經(jīng)過一系列的統(tǒng)計信息計算來的.
(a) 每個表或者索引的頁面?zhèn)€數(shù)
(b) 索引的基數(shù)
(c) 索引和數(shù)據(jù)行的長度
(d) 索引的分布情況
b、在很多情況下 mysql 會選擇錯誤的執(zhí)行計劃，原因如下：
（a）統(tǒng)計信息不準確（InnoDB 因為其 mvcc 的架構，并不能維護一個數(shù)據(jù)表的行數(shù)的精確統(tǒng)計信息）
（b）執(zhí)行計劃的成本估算不等同于實際執(zhí)行的成本 (有時候某個執(zhí)行計劃雖然需要讀取更多的頁面，但是他的成本卻更小，因為如果這些頁面都是順序讀或者這些頁面都已經(jīng)在內存中的話，那么它的訪問成本將很小，mysql 層面并不知道哪些頁面在內存中，哪些在磁盤，所以查詢之際執(zhí)行過程中到底需要多少次 IO 是無法得知的)
(c) mysql 的最優(yōu)可能跟你想的不一樣 (mysql 的優(yōu)化是基于成本模型的優(yōu)化，但是有可能不是最快的優(yōu)化)
(d) mysql 不考慮其他并發(fā)執(zhí)行的查詢
(e) mysql 不會考慮不受其控制的操作成本 (執(zhí)行存儲過程或者用戶自定義函數(shù)的成本)
c、優(yōu)化器的優(yōu)化策略
（a）靜態(tài)優(yōu)化（直接對解析樹進行分析，并完成優(yōu)化）
（b）動態(tài)優(yōu)化（動態(tài)優(yōu)化與查詢的上下文有關，也可能跟取值、索引對應的行數(shù)有關）
（c）mysql 對查詢的靜態(tài)優(yōu)化只需要一次，但對動態(tài)優(yōu)化在每次執(zhí)行時都需要重新評估
d、優(yōu)化器的優(yōu)化類型
（a）重新定義關聯(lián)表的順序 (數(shù)據(jù)表的關聯(lián)并不總是按照在查詢中指定的順序進行，決定關聯(lián)順序時優(yōu)化器很重要的功能)
（b）將外連接轉化成內連接，內連接的效率要高于外連接
（c）使用等價變換規(guī)則，mysql 可以使用一些等價變化來簡化并規(guī)劃表達式
（d）優(yōu)化 count(),min(),max()（索引和列是否可以為空通常可以幫助 mysql 優(yōu)化這類表達式：例如，要找到某一列的最小值，只需要查詢索引的最左端的記錄即可，不需要全文掃描比較）
（e）預估并轉化為常數(shù)表達式，當 mysql 檢測到一個表達式可以轉化為常數(shù)的時候，就會一直把該表達式作為常數(shù)進行處理。（explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1）
（f）索引覆蓋掃描，當索引中的列包含所有查詢中需要使用的列的時候，可以使用覆蓋索引
（g）子查詢優(yōu)化（mysql 在某些情況下可以將子查詢轉換一種效率更高的形式，從而減少多個查詢多次對數(shù)據(jù)進行訪問，例如將經(jīng)常查詢的數(shù)據(jù)放入到緩存中。）
（h）等值傳播（如果兩個列的值通過等式關聯(lián)，那么 mysql 能夠把其中一個列的 where 條件傳遞到另一個上：
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id 500;這里使用 film_id 字段進行等值關聯(lián)，film_id 這個列不僅適用于 film 表而且適用于 film_actor 表
explain select film.film_id from film inner join film_actor using(film_id
) where film.film_id 500 and film_actor.film_id 500;）e、關聯(lián)查詢
mysql 的關聯(lián)查詢很重要，但其實關聯(lián)查詢執(zhí)行的策略比較簡單：mysql 對任何關聯(lián)都執(zhí)行嵌套循環(huán)關聯(lián)操作，即 mysql 先在一張表中循環(huán)取出單條數(shù)據(jù)，然后再嵌套到下一個表中尋找匹配的行，依次下去，直到找到所有表中匹配的行為止。然后根據(jù)各個表匹配的行，返回查詢中需要的各個列。mysql 會嘗試再最后一個關聯(lián)表中找到所有匹配的行，如果最后一個關聯(lián)表無法找到更多的行之后，mysql 返回到上一層次關聯(lián)表，看是否能夠找到更多的匹配記錄，以此類推迭代執(zhí)行。整體的思路如此，但是要注意實際的執(zhí)行過程中有多個變種形式：
f、排序優(yōu)化
無論如何排序都是一個成本很高的操作，所以從性能的角度出發(fā)，應該盡可能避免排序或者盡可能避免對大量數(shù)據(jù)進行排序。
推薦使用利用索引進行排序，但是當不能使用索引的時候，mysql 就需要自己進行排序，如果數(shù)據(jù)量小則再內存中進行，如果數(shù)據(jù)量大就需要使用磁盤，mysql 中稱之為 filesort。
如果需要排序的數(shù)據(jù)量小于排序緩沖區(qū) (show variables like %sort_buffer_size%),mysql 使用內存進行快速排序操作，如果內存不夠排序，那么 mysql 就會先將樹分塊，對每個獨立的塊使用快速排序進行排序，并將各個塊的排序結果存放再磁盤上，然后將各個排好序的塊進行合并，最后返回排序結果，以下是排序的算法：
（a）兩次傳輸排序
第一次數(shù)據(jù)讀取是將需要排序的字段讀取出來，然后進行排序，第二次是將排好序的結果按照需要去讀取數(shù)據(jù)行。
這種方式效率比較低，原因是第二次讀取數(shù)據(jù)的時候因為已經(jīng)排好序，需要去讀取所有記錄而此時更多的是隨機 IO，讀取數(shù)據(jù)成本會比較高
兩次傳輸?shù)膬?yōu)勢，在排序的時候存儲盡可能少的數(shù)據(jù)，讓排序緩沖區(qū)可以盡可能多的容納行數(shù)來進行排序操作
（b）單次傳輸排序
先讀取查詢所需要的所有列，然后再根據(jù)給定列進行排序，最后直接返回排序結果，此方式只需要一次順序 IO 讀取所有的數(shù)據(jù)，而無須任何的隨機 IO，問題在于查詢的列特別多的時候，會占用大量的存儲空間，無法存儲大量的數(shù)據(jù)
（c）如何選擇
當需要排序的列的總大小超過 max_length_for_sort_data 定義的字節(jié)，mysql 會選擇雙次排序，反之使用單次排序，當然，用戶可以設置此參數(shù)的值來選擇排序的方式
四、優(yōu)化特定類型的查詢
1、優(yōu)化 count() 查詢
count() 是特殊的函數(shù)，有兩種不同的作用，一種是某個列值的數(shù)量，也可以統(tǒng)計行數(shù)。
（1）總有人認為 myisam 的 count 函數(shù)比較快，這是有前提條件的，只有沒有任何 where 條件的 count(*) 才是比較快的。
（2）使用近似值
在某些應用場景中，不需要完全精確的值，可以參考使用近似值來代替，比如可以使用 explain 來獲取近似的值
其實在很多 OLAP 的應用中，需要計算某一個列值的基數(shù)，有一個計算近似值的算法叫 hyperloglog。
（3）更復雜的優(yōu)化
一般情況下，count() 需要掃描大量的行才能獲取精確的數(shù)據(jù)，其實很難優(yōu)化，在實際操作的時候可以考慮使用索引覆蓋掃描，或者增加匯總表，或者增加外部緩存系統(tǒng)。
2、優(yōu)化關聯(lián)查詢
（1）確保 on 或者 using 子句中的列上有索引，在創(chuàng)建索引的時候就要考慮到關聯(lián)的順序
當表 A 和表 B 使用列 C 關聯(lián)的時候，如果優(yōu)化器的關聯(lián)順序是 B、A，那么就不需要再 B 表的對應列上建上索引，沒有用到的索引只會帶來額外的負擔，一般情況下來說，只需要在關聯(lián)順序中的第二個表的相應列上創(chuàng)建索引。
（2）確保任何的 groupby 和 order by 中的表達式只涉及到一個表中的列，這樣 mysql 才有可能使用索引來優(yōu)化這個過程
3、優(yōu)化子查詢
子查詢的優(yōu)化最重要的優(yōu)化建議是盡可能使用關聯(lián)查詢代替
4、優(yōu)化 limit 分頁
在很多應用場景中我們需要將數(shù)據(jù)進行分頁，一般會使用 limit 加上偏移量的方法實現(xiàn)，同時加上合適的 orderby 的子句，如果這種方式有索引的幫助，效率通常不錯，否則的話需要進行大量的文件排序操作，還有一種情況，當偏移量非常大的時候，前面的大部分數(shù)據(jù)都會被拋棄，這樣的代價太高。
要優(yōu)化這種查詢的話，要么是在頁面中限制分頁的數(shù)量，要么優(yōu)化大偏移量的性能。
優(yōu)化此類查詢的最簡單的辦法就是盡可能地使用覆蓋索引，而不是查詢所有的列。
select film_id,description from film order by title limit 50,5;
explain select film.film_id,film.description from film inner join (select film_id from film order by title limit 50,5) as lim using(film_id);5、優(yōu)化 union 查詢
mysql 總是通過創(chuàng)建并填充臨時表的方式來執(zhí)行 union 查詢，因此很多優(yōu)化策略在 union 查詢中都沒法很好的使用。經(jīng)常需要手工的將 where、limit、order by 等子句下推到各個子查詢中，以便優(yōu)化器可以充分利用這些條件進行優(yōu)化
除非確實需要服務器消除重復的行，否則一定要使用 union all，因此沒有 all 關鍵字，mysql 會在查詢的時候給臨時表加上 distinct 的關鍵字，這個操作的代價很高。
6、推薦使用用戶自定義變量
用戶自定義變量是一個容易被遺忘的 mysql 特性，但是如果能夠用好，在某些場景下可以寫出非常高效的查詢語句，在查詢中混合使用過程化和關系話邏輯的時候，自定義變量會非常有用。
用戶自定義變量是一個用來存儲內容的臨時容器，在連接 mysql 的整個過程中都存在。
（1）自定義變量的使用
set @one :=1
set @min_actor :=(select min(actor_id) from actor)
set @last_week :=current_date-interval 1 week;（2）自定義變量的限制
a、無法使用查詢緩存
b、不能在使用常量或者標識符的地方使用自定義變量，例如表名、列名或者 limit 子句
c、用戶自定義變量的生命周期是在一個連接中有效，所以不能用它們來做連接間的通信
d、不能顯式地聲明自定義變量地類型
e、mysql 優(yōu)化器在某些場景下可能會將這些變量優(yōu)化掉，這可能導致代碼不按預想地方式運行
f、賦值符號：= 的優(yōu)先級非常低，所以在使用賦值表達式的時候應該明確的使用括號。
g、使用未定義變量不會產(chǎn)生任何語法錯誤。
（3）自定義變量的使用案例
a、優(yōu)化排名語句
在給一個變量賦值的同時使用這個變量
select actor_id,@rownum:=@rownum+1 as rownum from actor limit 10;查詢獲取演過最多電影的前 10 名演員，然后根據(jù)出演電影次數(shù)做一個排名
select actor_id,count(*) as cnt from film_actor group by actor_id order by cnt desc limit 10;b、避免重新查詢剛剛更新的數(shù)據(jù)
當需要高效的更新一條記錄的時間戳，同時希望查詢當前記錄中存放的時間戳是什么
update t1 set lastUpdated=now() where id =1;
select lastUpdated from t1 where id =1;
update t1 set lastupdated = now() where id = 1 and @now:=now();
select @now;c、確定取值的順序
在賦值和讀取變量的時候可能是在查詢的不同階段
（a）set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum因為 where 和 select 在查詢的不同階段執(zhí)行，所以看到查詢到兩條記錄，這不符合預期
（b）set @rownum:=0;
select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum=1 order by first_name當引入了 orde;r by 之后，發(fā)現(xiàn)打印出了全部結果，這是因為 order by 引入了文件排序，而 where 條件是在文件排序操作之前取值的 。
（c）解決這個問題的關鍵在于讓變量的賦值和取值發(fā)生在執(zhí)行查詢的同一階段：
set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)“MySQL 性能調優(yōu)之查詢優(yōu)化的方法”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關的知識可以關注丸趣 TV 網(wǎng)站，丸趣 TV 小編將為大家輸出更多高質量的實用文章！
