「MySQL系列」分析SQL執(zhí)行時間及查詢執(zhí)行計劃(附數(shù)據(jù)庫和一千萬數(shù)據(jù))
一 準(zhǔn)備數(shù)據(jù)
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ù)庫導(dǎo)入服務(wù)器中(如果是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 要導(dǎo)入到的表。
注意： 我們之前使用insert的sql將數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，但是往庫中導(dǎo)入上千萬數(shù)據(jù)會需要很久時間。
二 慢查詢分析(查找執(zhí)行時間長的sql)
2.1 show profiles
show profiles是mysql提供可以用來分析當(dāng)前會話中語句執(zhí)行的資源消耗情 況?？梢杂脕鞸QL的調(diào)優(yōu)測量。
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í)行時間
- show profiles; //如果執(zhí)行沒有反應(yīng)，查看profiling是否開啟了，命令為select @@profiling;
查看沒每一條sql執(zhí)行時間。
3. 查詢每一條sql每個階段執(zhí)行時間
- select profile for query 6; //6,代表Query_ID
上圖解釋
- Sending data MySQL線程開始訪問數(shù)據(jù)行并把結(jié)果返回給客戶端，而不僅僅是
- 返回給客戶端。在Sending data狀態(tài)下，MySQL線程往往進(jìn)行大量的磁盤讀取
- 操作，所以在查詢中最耗時的狀態(tài)。
4. 查看線程在什么資源上耗費(fèi)過高 (類型 all、cpu、block io 、context、switch、page faults)
- show profile cpu for query 7;
上圖說明
2.2 慢查詢?nèi)罩?/strong>
慢查詢?nèi)罩居涗浟怂袌?zhí)行時間超過參數(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
- #該選項用來配置查詢的時間限制， 超過這個時間將認(rèn)為是慢查詢， 將進(jìn)行日志記錄， 默認(rèn)10s
- long_query_time=10
2. 重啟mysql服務(wù)
- service mysqld restart
備注 如果執(zhí)行命令報如下錯誤
請使用命令 systemctl restart mysqld.service
3. 查看慢查詢?nèi)罩灸夸?/strong>
- 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í)行計劃、索引使用和SQL優(yōu)化(對某個sql進(jìn)行分析)
通過以上步驟查詢到效率低的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í)行計劃字段解釋
3.2 對字段取值解釋
1. id
- A. id 相同表示加載表的順序是從上到下。
- B. id 不同id值越大，優(yōu)先級越高，越先被執(zhí)行。
- C. id 有相同，也有不同，同時存在。id相同的可以認(rèn)為是一組，從上往下順序執(zhí)行；在所有的組中，id的值越大，優(yōu)先級越高，越先執(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)用在這張表的索引， 一個或多個。
- B. key ： 實際使用的索引， 如果為NULL， 則沒有使用索引。
- C. key_len : 表示索引中使用的字節(jié)數(shù)， 該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下， 長度越短越好 。
5. rows
- 掃描行的數(shù)量。
6. filtered
- 這個字段表示存儲引擎返回的數(shù)據(jù)在server層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例。
