效能分析之單條SQL查詢案例分析(mysql)

語言: CN / TW / HK

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

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

一、引言

本文將以一個案例詳細展開介紹如何針對單條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.