分析SQL執(zhí)行時(shí)間及查詢執(zhí)行計(jì)劃(附數(shù)據(jù)庫(kù)和一千萬數(shù)據(jù))
我們干開發(fā)面試工作的時(shí)候，發(fā)現(xiàn)對(duì)數(shù)據(jù)庫(kù)的面試比重很大。說明對(duì)數(shù)據(jù)庫(kù)的知識(shí)掌握對(duì)我們程序員越來越重要了。接下來這篇文章我們來看看如何分析我們的sql執(zhí)行效率。首先找到執(zhí)行慢的sql，然后對(duì)執(zhí)行慢的SQL進(jìn)行分析。
一 準(zhǔn)備數(shù)據(jù)
在分析之前是不是有這樣的困惑，我的數(shù)據(jù)量這么少。我如何分析SQL執(zhí)行效率。不要慌，我們已準(zhǔn)備了一千萬條數(shù)據(jù)。接下來看看如何將這一千萬條數(shù)據(jù)快速導(dǎo)入到數(shù)據(jù)庫(kù)中。
1. 創(chuàng)建表和導(dǎo)入一千萬條數(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)入上千萬數(shù)據(jù)會(huì)需要很久時(shí)間。
二 慢查詢分析(查找執(zhí)行時(shí)間長(zhǎng)的sql)
2.1 show profiles
show profiles是mysql提供可以用來分析當(dāng)前會(huì)話中語句執(zhí)行的資源消耗情 況。可以用來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，代表沒有開啟
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í)行時(shí)間
- show profiles; //如果執(zhí)行沒有反應(yīng)，查看profiling是否開啟了，命令為select @@profiling;
查看沒每一條sql執(zhí)行時(shí)間。
3. 查詢每一條sql每個(gè)階段執(zhí)行時(shí)間
- select profile for query 6; //6,代表Query_ID
上圖解釋
- Sending data MySQL線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端，而不僅僅是
- 返回給客戶端。在Sending data狀態(tài)下，MySQL線程往往進(jìn)行大量的磁盤讀取
- 操作，所以在查詢中最耗時(shí)的狀態(tài)。
4. 查看線程在什么資源上耗費(fèi)過高 (類型 all、cpu、block io 、context、switch、page faults)
- show profile cpu for query 7;
上圖說明
2.2 慢查詢?nèi)罩?/span>
慢查詢?nèi)罩居涗浟怂袌?zhí)行時(shí)間超過參數(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ù)用來控制慢查詢?nèi)罩臼欠耖_啟，可取值：1和0，1代表開啟，0代表關(guān)閉
- slow_query_log=1
- #該參數(shù)用來指定慢查詢?nèi)罩镜奈募?nbsp;
- slow_query_log_file=slow_query.log
- #該選項(xiàng)用來配置查詢的時(shí)間限制， 超過這個(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)罩竞芏啵柚柚趍ysql自帶的mysqldumpslow工具，進(jìn)行分類匯總
三 explain執(zhí)行計(jì)劃、索引使用和SQL優(yōu)化
通過以上步驟查詢到效率低的SQL語句后，可以通過EXPLAIN命令獲取Mysql如何執(zhí)行Select語句信息，包含select語句執(zhí)行過程中表如何連接和連接的順序。
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， 則沒有使用索引。
- C. key_len : 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，在不損失精確性的前提下， 長(zhǎng)度越短越好 。
5. rows
- 掃描行的數(shù)量。
6. filtered
- 這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在server層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例。
總結(jié)這篇文章可以分為三個(gè)點(diǎn)。
1 往數(shù)據(jù)庫(kù)導(dǎo)入一千萬數(shù)據(jù)，查看上面文章，按照步驟操作沒有任何問題。
2 查找慢查詢語句，查看慢查詢?nèi)罩尽０凑彰顖?zhí)行就好
3 對(duì)sql執(zhí)行進(jìn)行分析，查看sql是否使用索引和執(zhí)行效率。
