[[360007]]

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

一 準(zhǔn)備數(shù)據(jù)

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

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

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

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

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

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

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

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

mysql -u 用戶名 -p 密碼 ; 

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

use db2; 

e 使用命令

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

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

 

注意：

我們之前使用insert的sql將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中，但是往庫(kù)中導(dǎo)入上千萬(wàn)數(shù)據(jù)會(huì)需要很久時(shí)間。

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

2.1 show profiles

show profiles是mysql提供可以用來(lái)分析當(dāng)前會(huì)話中語(yǔ)句執(zhí)行的資源消耗情 況?？梢杂脕?lái)SQL的調(diào)優(yōu)測(cè)量。

2.1.1 設(shè)置MySQL支持profile

1. 查看是否支持

select @@have_profiling 

結(jié)果為YES,代表支持。

2. 查看profiling(profiling默認(rèn)是關(guān)閉的)

select @@profiling 

結(jié)果為0，代表沒(méi)有開(kāi)啟

3. 開(kāi)啟profiling

set profiling=1;  

2.1.2 show profiles的使用

1. 輸入一系列查詢語(yǔ)句

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

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

show profiles;    //如果執(zhí)行沒(méi)有反應(yīng)，查看profiling是否開(kāi)啟了，命令為select @@profiling;  

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

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

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

上圖解釋

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

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

show profile cpu for query 7; 

上圖說(shuō)明

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

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

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

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

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

2. 重啟mysql服務(wù)

service mysqld restart 

備注

如果執(zhí)行命令報(bào)如下錯(cuò)誤

請(qǐng)使用命令 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手機(jī)Meta87384 Pro%'\G; 

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

a 使用cat

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

 

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

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

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

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

 

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

 

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

3.2 對(duì)字段取值解釋

1. id

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

2. select_type

3. type

 

結(jié)果由好到壞

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 : 顯示可能應(yīng)用在這張表的索引， 一個(gè)或多個(gè)。 
B. key ： 實(shí)際使用的索引， 如果為NULL， 則沒(méi)有使用索引。 
C. key_len : 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，在不損失精確性的前提下， 長(zhǎng)度越短越好 。 

5. rows

掃描行的數(shù)量。 

6. filtered

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

總結(jié)這篇文章可以分為三個(gè)點(diǎn)。

1 往數(shù)據(jù)庫(kù)導(dǎo)入一千萬(wàn)數(shù)據(jù)，查看上面文章，按照步驟操作沒(méi)有任何問(wèn)題。

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

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