小知識，大挑戰！本文正在參與“程式設計師必備小知識”創作活動。

本文已參與 「掘力星計劃」 ，贏取創作大禮包，挑戰創作激勵金。

一、引言

本文將以一個案例詳細展開介紹如何針對單條SQL進行效能分析。

二、背景

在定位到需要優化的單條查詢SQL後，我們可以針對此查詢“鑽取”更多資訊，分析為什麼會花費怎麼長的時間執行，以及如何去優化的大致方向。

三、準備工作

1、環境準備

• 作業系統：window/linux
• 資料庫: MySQL 5.7

2、資料準備

建立一個數據庫表

```sql CREATE TABLE emp (empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '編號', ename VARCHAR(20) NOT NULL DEFAULT "" COMMENT '名字', job VARCHAR(9) NOT NULL DEFAULT "" COMMENT '工作', mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '上級編號', hiredate DATE NOT NULL COMMENT '入職時間', sal DECIMAL(7,2) NOT NULL COMMENT '薪水', comm DECIMAL(7,2) NOT NULL COMMENT '紅利', deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '部門編號' )ENGINE=InnoDB DEFAULT CHARSET=utf8; ````

構建一個儲存函式，這個儲存函式會返回一個長度為引數 n 的隨機字串

```sql delimiter $$

create function rand_string(n INT) returns varchar(255) #該函式會返回一個字串 begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1)); set i = i + 1; end while; return return_str; end $$

delimiter ; ```

接下來我們再建立一個儲存函式，該儲存函式會返回一個隨機 int 值 ```sql delimiter $$

create function rand_num( ) returns int(5) begin declare i int default 0; set i = floor(10+rand()*500); return i; end $$

delimiter ; ```

然後我們利用剛剛建立的兩個儲存函式建立一個儲存過程，該儲存過程包含一個引數，該引數表示插入資料表 emp 的資料條數

```sql elimiter $$

create procedure insert_emp(in max_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into emp values (i ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num()); until i = max_num end repeat; commit; end $$

delimiter ; ```

我們呼叫建立的儲存過程，對 emp 表插入 1000w 條資料 sql call insert_emp(10000000); 最後，統計表資料

四、案例分析

1、查詢SQL

現在我們執行一個查詢時間超過 1s 的查詢語句

2、Explain 執行計劃

MySQL 提供了一個 EXPLAIN 命令, 它可以對 SELECT 語句進行分析, 並輸出 SELECT 執行的詳細資訊, 以供開發/測試人員針對性優化

EXPLAIN 命令的使用十分簡單，只需要"EXPLAIN + SQL 語句"即可，如下命令就是對我們剛剛的慢查詢語句使用 EXPLAIN 之後的結果

可以看到，EXPLAIN 命令的結果一共有以下幾列： - id: SELECT 查詢的識別符號. 每個 SELECT 都會自動分配一個唯一的識別符號. - select_type: SELECT 查詢的型別 - PRIMARY(子查詢中最外層查詢） - SUBQUERY（子查詢內層第一個 SELECT） - UNION（ UNION 語句中第二個 SELECT 開始後面所有 SELECT） - SIMPLE（除了子查詢或者 UNION 之外的其他查詢） - table: 查詢的是哪個表 - partitions: 匹配的分割槽 - type: join 型別，效能關係：ALL < index < range ~ index_merge < ref < eq_ref < const < system - all(全表掃描) - index（全索引掃描） - rang（索引範圍掃描） - ref（join語句中被驅動表索引引用查詢） - eq_ref（通過主鍵或唯一索引訪問，最多隻會有一條結果） - const（讀常量，只需讀一次） - system（系統表，表中只有一條資料） - null（速度最快） - possible_keys: 此次查詢中可能選用的索引 - key: 此次查詢中確切使用到的索引 - key_len：使用索引的最大長度； - ref: 哪個欄位或常數與 key 一起被使用 - rows: 顯示此查詢一共掃描了多少行. 這個是一個估計值. - filtered: 表示此查詢條件所過濾的資料的百分比 - extra: 額外的資訊 - distinct - using filesort（order by 操作） - using index（所查資料只需要在 index 中即可獲取） - using temporary（使用臨時表） - using where（如果包含 where，且不是僅通過索引即可獲取內容，就會包含此資訊）

這樣，通過執行計劃我們就可以清楚的看到，這條查詢語句是一個全表掃描語句，查詢時沒有用到任何索引，所以它的查詢時間肯定會很慢。

3、Show Profiling

Show Profiling 命令是在 MySQL5.1 以後引入的，來源於開源社群中的 Jeremy Cole 的貢獻。在 MySQL 資料庫中預設是禁用的，可以通過伺服器變數在會話（連線）級別動態地修改。然後，在伺服器上執行的所有語句，都會測量其耗費的時間和其它一些查詢執行狀態變更相關資料。

接下來我們執行一條查詢命令

在開啟了Query Profiler 功能之後,MySQL 就會自動記錄所有執行的 Query 的 Profiling 資訊。 然後我們通過以下命令獲取系統中儲存的所有 Query 的 profile 概要資訊

然後我們可以通過以下命令檢視具體的某一次查詢的 Profiling 資訊

Profiling 剖析報告給出了執行查詢的每個步驟及其花費的時間，看結果可以快速的確定是那個步驟花費的時間最多。

以上各欄位含義： - starting：開始 - checking permissions：鑑權 - Opening tables ：開啟表 - init：初始化 - System lock：系統鎖
- optimizing：優化
- statistics：統計
- preparing：準備
- executing：執行
- Sending data：傳送資料
- end：結束
- query end：查詢結果
- closing tables：關閉表
- freeing items：釋放items
- cleaning up：清理

type 引數可以指定以顯示特定的其他型別的資訊： - ALL：顯示所有資訊 - BLOCK IO： 顯示塊輸入和輸出操作的計數 - CONTEXT SWITCHES： 顯示自願和非自願上下文切換的計數 - CPU： 顯示使用者和系統 CPU 使用時間 - IPC： 顯示傳送和接收的訊息的計數 - MEMORY： 目前尚未實施 - PAGE FAULTS： 顯示主要和次要頁面錯誤的計數 - SOURCE： 顯示原始碼中的函式名稱，以及函式發生的檔案的名稱和行號 - SWAPS： 顯示交換計數

通過這個結果可以很容易看到，由於這是一次全表掃描，這裡耗時最大是在 sending data（傳送資料）上。 除了這種情況，以下幾種情況也可能耗費大量時間： - converting HEAP to MyISAM (查詢結果太大時,把結果放在磁碟) - create tmp table (建立臨時表,如 group 時儲存中間結果) - Copying to tmp table on disk (把記憶體臨時表複製到磁碟) - locked (被其他查詢鎖住) - logging slow query (記錄慢查詢)

4、HOW STATUS

SHOW STATUS 命令返回一些計數器，既有伺服器級別的全域性計時器，也有基於某個連線的會話級別的計數器。例如其中的 Queries 在會話開始時為0，每提交一次查詢增加1。如果執行 SHOW GLOBAL STATUS，則可以檢視伺服器級別（從伺服器啟動時開始計算的查詢次數統計）。不同的計數器可見範圍不一樣，全域性計數器也會出現在SHOW STATUS 的結果中，這樣容易被誤認為會話級，所以一定不能搞迷糊了。 SHOW STATUS是一個很有用的工具，但並不是一款剖析工具。雖然無法提供基於時間的統計，但是執行查詢完後觀察某些計數器的值還是很有幫助的。

從結果可以看出該查詢有很多的沒有用到索引的 Handler_read_rnd_next（讀操作）以及Key_blocks_unused（未使用的快取簇(blocks)數）。假設我們不知道這條 SQL 具體的定義僅從結果來推測，這個查詢有可能是全表掃描，沒有合適的索引。 我們可能注意到通過 Explain 執行計劃也可以獲得大部分相同的資訊，但是 Explain 是通過估計得到的結果，而通過計數器則是實際的測量結果。

各引數詳解參考官方資料： https://dev.mysql.com/doc/refman/5.6/en/server-status-variables.html

5、慢查詢日誌

我們可以用以下命令檢視慢查詢次數

使用該命令只能檢視慢查詢次數，但是我們沒有辦法知道是哪些查詢產生了慢查詢，如果想要知道是哪些查詢導致的慢查詢，那麼我們必須修改 mysql 的配置檔案。開啟 mysql 的配置檔案（windows系統是my.ini，linux系統是my.cnf），在 [mysqld] 下面加上以下程式碼 ```bash

General and Slow logging.

log-output=FILE general-log=0 general_log_file="DESKTOP-MLD0KTS.log" slow-query-log=1 slow_query_log_file="DESKTOP-MLD0KTS-slow.log" long_query_time=1 ``` 此時我們在 mysql 中執行以下命令，可以看到 slow_query_log 是 ON 狀態，log_file 也是我們指定的檔案

執行以下命令我們可以看到我們設定的慢查詢時間也生效了，此時只要查詢時間大於 1s，查詢語句都將存入日誌檔案

現在我們執行一個查詢時間超過 1s 的查詢語句,然後檢視 mysql 安裝目錄下的 data 目錄，該目錄會產生一個慢查詢日誌檔案：mysql_slow.log，該檔案內容如下

在該日誌檔案中，我們可以知道慢查詢產生的時間，最終產生了幾行結果，測試了幾行結果，以及執行語句是什麼。在這裡我們可以看到，這條語句產生一個結果，但是檢測了 1000w 行記錄，是一個全表掃描語句。

參考資料： - [1]Vadim Tkacbenko著.高效能MySQL.北京:電子工業出版社,2013.