MySQL效能調優之查詢最佳化

這篇文章為大家帶來了關於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<=1;

因为where和select在查询的不同阶段执行，所以看到查询到两条记录，这不符合预期

（b）set @rownum:=0;

select actor_id,@rownum:=@rownum+1 as cnt from actor where @rownum<=1 order by first_name

当引入了orde;r by之后，发现打印出了全部结果，这是因为order by引入了文件排序，而where条件是在文件排序操作之前取值的 。

（c）解决这个问题的关键在于让变量的赋值和取值发生在执行查询的同一阶段：

set @rownum:=0;
select actor_id,@rownum as cnt from actor where (@rownum:=@rownum+1)<=1;

推荐学习：mysql视频教程

以上是MySQL效能調優之查詢最佳化的詳細內容。更多資訊請關注PHP中文網其他相關文章！