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