MySQL資料庫查詢效能優化的4個技巧幹貨

2022-08-01 22:06:37

前言

MySQL效能優化是一個老生常談的問題，無論是在實際工作中還是面試中，都不可避免遇到相應的場景，下面博主就總結一些能夠幫助大家解決這個問題的小技巧。

SQL優化之前需要確認哪些SQL需要優化，這時就需要引起SQL效能分析工具，主要優化的是查詢語句。

SQL的執行頻率

SQL效能優化一般是針對查詢語句，所以在定位是否需要優化之前，可以先確認表的更刪查改的一個執行頻率對比，如果是查詢占主導地位，則可以一步排查。

MySQL支援使用者端通過show [session|global] status命令對伺服器狀態進行查詢。

檢視執行頻率方式：

show global status like ‘com_______’(7個下劃線,表示後面會有7個字元)

慢查詢紀錄檔

確認了SQL的執行頻率，則需要通過慢查詢紀錄檔進行進一步定位哪些SQL語句執行時間佔用較長。

慢查詢紀錄檔記錄了所有執行時間超過指定引數(long_query_time，單位:秒，預設是10s)的所有SQL語句的紀錄檔。

預設情況下，慢查詢紀錄檔是沒有開啟的，需要在MySQL的組態檔(linux下為:/etc/my.cnf)中設定如下指令：

查詢伺服器端是否開啟慢查詢紀錄檔：show variables like 'slow_query_log';
在mysql的組態檔中新增如下設定啟動：
slow_query_log=1;開啟mysql慢紀錄檔查詢開關
long_query_time=xx;設定慢紀錄檔時間，只要SQL執行時間查過該值，則視為慢查詢，記錄在慢紀錄檔中。
設定完成後重啟mysql伺服器端
linux中mysql的慢紀錄檔檔案在: /var/lib/mysql/localhost-slow.log

window可以在my.ini檔案中設定具體的地址

Query_time SQL執行的時間,越長則越慢

Lock_time 在MySQL伺服器階段(不是在儲存引擎階段)等待表鎖時間

Rows_sent 查詢返回的行數

Rows_examined 查詢檢查的行數

show profiles詳情分析

通過慢查詢紀錄檔，我們可以定位到超過設定閾值的慢SQL，但是實際業務中，這並不能完全具有代表性，因為閾值是主觀設定的，可能有大量執行時間低於閾值的SQL也存在問題，因此慢紀錄檔SQL並不能完全定位出所有的慢SQL。

show profiles 能夠讓我們瞭解到SQL執行時時間都耗費到哪裡了。通過have_profiling引數，可以檢視mysql是否支援該profile操作。

格式: select @@have_profiling;

預設情況下，profiling是關閉的，可以同set指令開啟session|global級別的profiling。

格式: set global | session profiling = 1;

優化方案：

show profiles; 檢視每一條SQL的耗時基本情況
show profile for query query_id; 查詢指定query_id的SQL語句各個階段的耗時情況
show profile cpu for query query_id; 查詢指定query_id的SQL語句cpu使用情況

explain執行計劃

前面介紹的幾種方式都是通過執行時間長短來判斷SQL語句執行的效能好壞，但是這個相對來說是比較片面的，想要更全面地評判SQL語句好壞，則需要使用explain檢視SQL的執行計劃。

Explain或者DESC命令獲取MySQL如何執行SELECT語句的資訊，包括在SELECT語句過程中表如何連線和連線的順序。

語法：explain | desc select xxxx...

1、ID引數

select中的查詢序號，表示的是查詢中執行select子句或者是操作表的順序(id相同，執行順序從上往下，id不同，值越大，越先執行)

2、select_type引數

表示select查詢型別，常見的有SIMPLE(簡單表，即不使用表連線或者子查詢)、primary(主查詢，即外層查詢)、UNION(UNION中的第二個或者後面的查詢語句)、SUBQUERY(SELECT/WHERE之後包含了子查詢)

3、type引數

表示連線/存取型別，效能由好到差的連線型別為：null、system、const、eq_ref、ref、range、index、all

在優化的時候，儘量將type往前優化，最差也要為index

null：查詢的時候不存取任何表，如:select "1"
system：當存取一些系統表的時候會出現
const：根據主鍵或者唯一索引存取時，會出現const
eq_ref：待確認
ref：使用非唯一性索引進行存取時，可能出現ref
range：
index：使用到了索引，但是對整個索引都進行了遍歷，效能也比較差
all：全表掃描，效能最差

4、possible_key引數：顯示在執行查詢時，表中可能被使用到的索引，一個或者多個、

5、key引數：在執行查詢時，實際上會命中的索引

6、key_len引數：表示使用到的索引的位元組數，該值為索引欄位最大可能長度，在不損失精確性的前提下，長度越短越好。

7、rows引數：MySQL認為必須要執行查詢的行數，在idb引擎表中，是一個估計值，可能並不總是準確的

8、ref引數：待確認？

9、filtered引數：表示查詢返回的行數佔總讀取行數的百分比，值越大越好

10、extra引數：額外的一些執行資訊如排序

以上就是MySQL資料庫查詢效能優化的4個技巧幹貨的詳細內容，更多關於MySQL查詢效能優化的資料請關注it145.com其它相關文章！