[[360007]]

 我們干開發(fā)面試工作的時候，發(fā)現(xiàn)對數(shù)據(jù)庫的面試比重很大。說明對數(shù)據(jù)庫的知識掌握對我們程序員越來越重要了。接下來這篇文章我們來看看如何分析我們的sql執(zhí)行效率。首先找到執(zhí)行慢的sql，然后對執(zhí)行慢的SQL進行分析。

一 準備數(shù)據(jù)

在分析之前是不是有這樣的困惑，我的數(shù)據(jù)量這么少。我如何分析SQL執(zhí)行效率。不要慌，我們已準備了一千萬條數(shù)據(jù)。接下來看看如何將這一千萬條數(shù)據(jù)快速導入到數(shù)據(jù)庫中。

1. 創(chuàng)建表和導入一千萬條數(shù)據(jù)

表和數(shù)據(jù)地址(gitee) https://gitee.com/flowerAndJava/millions_data

2. 大批量數(shù)據(jù)導入數(shù)據(jù)

a 將數(shù)據(jù)庫導入服務器中(如果是windows系統(tǒng)，這步省略)

b 創(chuàng)建一個數(shù)據(jù)庫

創(chuàng)建數(shù)據(jù)庫（db2），表tb_sku 

c 命令行登錄數(shù)據(jù)庫

mysql -u 用戶名 -p 密碼 ; 

d 切換到使用的數(shù)據(jù)庫

use db2; 

e 使用命令

load data local infile '/tmp/tb_sku1.sql' into table `tb_sku` fields terminated by ',' lines terminated by '\n'; 

對命令解釋： '/tmp/tb_sku1.sql' 數(shù)據(jù)的目錄(windows目錄例如：D:\life\tb_sku1.sql),tb_sku 要導入到的表。

 

注意：

我們之前使用insert的sql將數(shù)據(jù)導入到數(shù)據(jù)庫中，但是往庫中導入上千萬數(shù)據(jù)會需要很久時間。

二 慢查詢分析(查找執(zhí)行時間長的sql)

2.1 show profiles

show profiles是mysql提供可以用來分析當前會話中語句執(zhí)行的資源消耗情 況?？梢杂脕鞸QL的調(diào)優(yōu)測量。

2.1.1 設置MySQL支持profile

1. 查看是否支持

select @@have_profiling 

結果為YES,代表支持。

2. 查看profiling(profiling默認是關閉的)

select @@profiling 

結果為0，代表沒有開啟

3. 開啟profiling

set profiling=1;  

2.1.2 show profiles的使用

1. 輸入一系列查詢語句

show databases; 
use db01; 
show tables; 
select * from tb_ksu where id < 5; 
select count(*) from tb_ksu; 

2. 查看沒一條SQL執(zhí)行時間

show profiles;    //如果執(zhí)行沒有反應，查看profiling是否開啟了，命令為select @@profiling;  

查看沒每一條sql執(zhí)行時間。

3. 查詢每一條sql每個階段執(zhí)行時間

select profile for query 6;   //6,代表Query_ID 

上圖解釋

Sending data MySQL線程開始訪問數(shù)據(jù)行并把結果返回給客戶端，而不僅僅是 
返回給客戶端。在Sending data狀態(tài)下，MySQL線程往往進行大量的磁盤讀取 
操作，所以在查詢中最耗時的狀態(tài)。 

4. 查看線程在什么資源上耗費過高 (類型 all、cpu、block io 、context、switch、page faults)

show profile cpu for query 7; 

上圖說明

2.2 慢查詢?nèi)罩?/span>

慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過參數(shù)(long_query_time)設置值并且掃描 記錄數(shù)不少于min_examined_row_limit，的所有SQL日志。long_query_time默 認為10秒，最小為0，精度可以到微秒。

2.2.1 設置慢查詢?nèi)罩?/strong>

1. 修改配置文件(慢查詢?nèi)罩灸J關閉的) 修改配置文件命令 vi /etc/my.cnf 然后在配置文件最下方加入下面配置

# 該參數(shù)用來控制慢查詢?nèi)罩臼欠耖_啟，可取值：1和0，1代表開啟，0代表關閉 
slow_query_log=1 
#該參數(shù)用來指定慢查詢?nèi)罩镜奈募?nbsp;
slow_query_log_file=slow_query.log 
#該選項用來配置查詢的時間限制， 超過這個時間將認為是慢查詢， 將進行日志記錄， 默認10s 
long_query_time=10 

2. 重啟mysql服務

service mysqld restart 

備注

如果執(zhí)行命令報如下錯誤

請使用命令 systemctl restart mysqld.service

3. 查看慢查詢?nèi)罩灸夸?/p>

cd /var/lib/mysql 

2.2.2 日志讀取

1. 查詢long_query_time的值

show variables like 'long%'; 

 

2. 執(zhí)行查詢操作

select * from tb_sku where id = '100000030074'\G; 
select * from tb_sku where name like '%HuaWei手機Meta87384 Pro%'\G; 

3. 查詢慢查詢?nèi)罩?/p>

a 使用cat

b 如果慢查詢?nèi)罩竞芏?，借助借助于mysql自帶的mysqldumpslow工具，進行分類匯總

 

三 explain執(zhí)行計劃、索引使用和SQL優(yōu)化

通過以上步驟查詢到效率低的SQL語句后，可以通過EXPLAIN命令獲取Mysql如何執(zhí)行Select語句信息，包含select語句執(zhí)行過程中表如何連接和連接的順序。

3.1 執(zhí)行explain命令，進行分析

explain select * from tb_sku where id = '100000030074'; 

 

explain select * from tb_sku where name like '%HuaWei 手機Meta87384 Pro%'; 

 

執(zhí)行計劃字段解釋

3.2 對字段取值解釋

1. id

A. id 相同表示加載表的順序是從上到下。 
B. id 不同id值越大，優(yōu)先級越高，越先被執(zhí)行。 
C. id 有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執(zhí)行；在所有的組中，id的值越大，優(yōu)先級越高，越先執(zhí)行。 

2. select_type

3. type

 

結果由好到壞

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL 
 
system > const > eq_ref > ref > range > index > ALL 

4. key

A. possible_keys : 顯示可能應用在這張表的索引， 一個或多個。 
B. key ： 實際使用的索引， 如果為NULL， 則沒有使用索引。 
C. key_len : 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下， 長度越短越好 。 

5. rows

掃描行的數(shù)量。 

6. filtered

這個字段表示存儲引擎返回的數(shù)據(jù)在server層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例。 

總結這篇文章可以分為三個點。

1 往數(shù)據(jù)庫導入一千萬數(shù)據(jù)，查看上面文章，按照步驟操作沒有任何問題。

2 查找慢查詢語句，查看慢查詢?nèi)罩?。按照命令?zhí)行就好

3 對sql執(zhí)行進行分析，查看sql是否使用索引和執(zhí)行效率。