(/≧▽≦)/~┴┴ 嗨~我叫小奧 ???
👀👀👀 個人博客：小奧的博客
👍👍👍：個人CSDN
??????：傳送門
🍹 本人24應屆生一枚，技術和水平有限，如果文章中有不正確的內容，歡迎多多指正！
📜 歡迎點贊收藏關注喲！ ??

MySQL優化之SQL優化詳解

在應用的開發過程中，由于初期數據量小，開發人員寫SQL語句時更重視功能上的實現，但是當應用系統正式上線后，隨著生產數據量的急劇增長,很多SOL語句開始逐漸顯露出性能問題，對生產的影響也越來越大，此時這些有問題的SQL語句就成為整個系統性能的瓶頸，因此我們必須要對它們進行優化。

優化SQL的步驟

當面對一個有SOL性能問題的數據庫時，我們應該從何處入手來進行系統的分析，使得能夠盡快定位問題SOL并盡快解決問題，我將帶大家一起學習這個過程。

1. 了解SQL執行頻率

MySQL客戶端連接成功后，通過show [session|global] status命令可以提供服務器狀態信息。通過如下指令，可以查看當前數據庫的INSERT、UPDATE、DELETE、SELETE的訪問頻次：

show global status like 'Com_______';  //注意：七個下劃線代表七個字符

Com_xxx表示每個xxx語句執行的次數，我們比較關心的是以下幾個統計參數：

• Com_select：執行SELECT操作的次數，一次查詢只累加1；
• Com_insert：執行INSERT操作的次數，對于批量插入的INSERT操作，只累加一次；
• Com_update：執行UPDATE操作的次數；
• Com_delete：執行DELETE操作的次數。

通過以上幾個參數，可以很容易地了解當前數據庫的應用是以插入更新為主還是以查詢操作為主，以及各種類型的SQL大致的執行比例是多少。對于更新操作的計數，是對執行次數的計數，不論提交還是回滾都會進行累加。.

對于事務型的應用，通過Com_commit和Com_rollback可以了解事務提交和回滾的情況對于回滾操作非常頻繁的數據庫，可能意味著應用編寫存在問題。

2. 定位執行效率低的SQL

慢查詢日志記錄了所有執行時間超過指定參數（long_query_time，單位：秒，默認10秒）的所有SQL語句的日志。

我們可以通過如下命令查詢慢查詢是否開啟：

show variables like 'slow_query_log';

MySQL的慢查詢日志默認沒有開啟，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 開啟MySQL慢查詢日志開關
slow_query_log=1
# 設置慢日志的時間為2秒。SQL語句的執行時間超過兩秒，就會被視為慢查詢，記錄慢查詢日志
long_query_time=2

配置完畢之后，通過以下指令重新啟動MySQL服務進行測試，查看慢日志文件中記錄的信息 /var/lib/mysql/192-slow.log

systemctl restart mysqld

我們可以去var/lib/mysql/目錄下查看一個名字帶-slow.log的日志文件，初始內容為數據庫版本信息。

3. 查看SQL執行的耗時

show profiles能夠在做SQL優化時幫助我們了解事件都耗費到哪里去了。通過have_profiling參數，能夠看到當前MySQL是否支持profile操作：

select @@have_profiling;

然后可以通過select @@profiling;查看profile是否開啟

select @@profiling;

默認profiling是關閉的，可以通過set語句在session/global級別開啟profiling：

set profiling=1;

然后我們就可以執行一系列的業務SQL的操作，通過如下指令查看SQL的執行耗時：

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

4. 分析SQL的執行計劃

通過以上步驟查詢到效率低的 SQL語句后，可以通過 EXPLAIN或者DESC命令獲取MySQL 如何執行SELECT 語句的信息，包括在SELECT 語句執行過程中表如何連接和連接的順序。

# 直接在select語句之前加上關鍵字explain/desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 條件;

下面簡單的介紹一下每個字段的作用：

id

select查詢的序列號，表示查詢中執行select子句或者是操作表的順序（id相同，執行順序從上到下；id不同，值越大，越先執行），如果為null表示這個是一個結果集，不需要用它來進行查詢。

select_type

表示SELECT的類型。

取值	含義
simple	簡單的select查詢，查詢中不包含子查詢或者union
primary	查詢中包含任何復雜的子查詢，最外層查詢被標記為primary
union	若第二個select出現在union之后，則會被標記為union；若union包含在from子句的子查詢中，外層select將被標記為derived
dependent union	與union一樣，出現在union或union all語句中的，但是這個查詢要受到外部查詢的影響
union result	包含在union的結果集，在union和union all語句中，因為它不需要參與查詢，所以id字段為null
subquery	在select或where列表中包含子查詢
dependent subquery	與dependent union類似，表示這個subquery的查詢主要受到外部表查詢的影響
derived	在from列表中包含的子查詢被標記為derived(衍生)，mysql或者遞歸執行這些子查詢，把結果放在臨時表里

table

輸出結果集的表名，如果查詢使用了別名，那么這里顯示的是別名。

• 如果不涉及對數據表的操作，那么這顯示為null，
• 如果顯示為尖括號括起來的就表示這個是臨時表，后邊的N就是執行計劃中的id，表示結果來自于這個查詢產生。
• 如果是尖括號括起來的<union M,N>，與類似，也是一個臨時表，表示這個結果來自于union查詢的id為M,N的結果集。

type

表示連接類型，描述了找到所需數據使用的掃描方式。

性能有好到差的連接類型為null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

取值	含義
system	系統表，少量數據，往往不需要磁盤IO
const	常量連接
eq_ref	使用索引是唯一索引，對于每個索引鍵值，表中只有一條記錄，即多表連接使用primary key或者 unique index作為關聯條件
ref	使用非唯一索引掃描或者唯一索引的前綴掃描，返回匹配某個單獨值得記錄行
ref_or_null	與ref方法類型，只是增加了null值的比較
range	索引范圍掃描，常見于<、<=、>、>=、between等操作符
index	索引全掃描，MySQL遍歷整個索引來查詢匹配的行
index_merge	表示查詢使用了兩個以上的索引，最后取交集或者并集
fulltext	全文檢索索引
unique_subquery	用于where中的in形式子查詢，子查詢返回不重復唯一的值
index_subquery	用于in形式子查詢，子查詢可能返回重復值，可以使用索引將子查詢去重
all	全表掃描，MySQL遍歷全表來找到匹配的行

possible_key

查詢可能涉及在這張表上的索引，一個或多個，但不一定被查詢實際使用。

key

實際使用的到索引，如果為null，則說明沒有使用索引；查詢中如果使用了覆蓋索引，則該索引僅出現在key列表中。

key_len

表示索引中使用的字節數，該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下，長度越短越好。

ref

如果是使用的常量等值查詢，這里會顯示const，如果是連接查詢，被驅動表的執行計劃這里會顯示驅動表的關聯字段，如果是條件使用了表達式或者是函數，或者條件列發生了內部隱式轉換，這里可能顯示為func。

rows

MySQL認為必須要執行查詢的行數，在innodb引擎的表中，是一個估計值，可能并不總是準確的。

filtered

表示返回結果的行數占需讀取行數的百分比，filter的值越大越好。

Extra

取值	含義	舉例
Using where	說明SQL使用了where條件過濾數據	explain select * from table where id > 3
Usering index	說明SQL需要返回所有列數據均在一棵索引樹，而無需訪問實際的行記錄	explain select id from table
Using index condition	說明命中了索引，但是并不是所有列數據都在索引樹上，還需要訪問實際的行記錄	explain select * from table t1, table t2 where t1.id= t2.id;
Using filesort	說明得到所需結果集，需要對所有記錄進行文件排序。比如在一個沒有建立索引的列上進行order by	explain select id from table order by name
Using temporary	說明需要建立臨時表(temporary table)來暫存中間結果。	explain select name, count(*) from table group by name order by name;

5. 分析優化器如何選擇執行計劃

MySQL 5.6提供了對SQL的跟蹤trace，通過trace文件能夠進一步了解為什么優化器選擇A執行計劃而不選擇B執行計劃，幫助我們更好地理解優化器的行為。

使用方式：首先打開trace，設置格式為JSON，設置trace最大能夠使用的內存大小，避免解析過程中因為默認內存過小而不能夠完整顯示。

# 打開trace并設置格式為json
set OPTIMIZER_TRACE = "enabled=0n", END_MARKERS_IN_JSON=on;
# 設置最大內存
set OPTIMIZER_TRACE_MAX_MEM_SIZE = 1000000;

執行完我們想要分析的SQL之后，檢查information_schema.OPTIMIZER_TRACE就可以知道MySQL是如何執行SQL的。

select * from information_schema.OPTIMIZER_TRACE;