「MySQL系列」分析SQL執行時間及查詢執行計劃(附數據庫和一千萬數據)
一 準備數據
1. 創建表和導入一千萬條數據
表和數據地址(gitee) https://gitee.com/flowerAndJava/millions_data
2. 大批量數據導入數據
a 將數據庫導入服務器中(如果是windows系統，這步省略)
b 創建一個數據庫
- 創建數據庫（db2），表tb_sku
c 命令行登錄數據庫
- mysql -u 用戶名 -p 密碼 ;
d 切換到使用的數據庫
- 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' 數據的目錄(windows目錄例如：D:\life\tb_sku1.sql),tb_sku 要導入到的表。
注意： 我們之前使用insert的sql將數據導入到數據庫中，但是往庫中導入上千萬數據會需要很久時間。
二 慢查詢分析(查找執行時間長的sql)
2.1 show profiles
show profiles是mysql提供可以用來分析當前會話中語句執行的資源消耗情 況。可以用來SQL的調優測量。
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執行時間
- show profiles; //如果執行沒有反應，查看profiling是否開啟了，命令為select @@profiling;
查看沒每一條sql執行時間。
3. 查詢每一條sql每個階段執行時間
- select profile for query 6; //6,代表Query_ID
上圖解釋
- Sending data MySQL線程開始訪問數據行并把結果返回給客戶端，而不僅僅是
- 返回給客戶端。在Sending data狀態下，MySQL線程往往進行大量的磁盤讀取
- 操作，所以在查詢中最耗時的狀態。
4. 查看線程在什么資源上耗費過高 (類型 all、cpu、block io 、context、switch、page faults)
- show profile cpu for query 7;
上圖說明
2.2 慢查詢日志
慢查詢日志記錄了所有執行時間超過參數(long_query_time)設置值并且掃描 記錄數不少于min_examined_row_limit，的所有SQL日志。long_query_time默 認為10秒，最小為0，精度可以到微秒。
2.2.1 設置慢查詢日志
1. 修改配置文件(慢查詢日志默認關閉的) 修改配置文件命令 vi /etc/my.cnf 然后在配置文件最下方加入下面配置
- # 該參數用來控制慢查詢日志是否開啟，可取值：1和0，1代表開啟，0代表關閉
- slow_query_log=1
- #該參數用來指定慢查詢日志的文件名
- slow_query_log_file=slow_query.log
- #該選項用來配置查詢的時間限制， 超過這個時間將認為是慢查詢， 將進行日志記錄， 默認10s
- long_query_time=10
2. 重啟mysql服務
- service mysqld restart
備注 如果執行命令報如下錯誤
請使用命令 systemctl restart mysqld.service
3. 查看慢查詢日志目錄
- cd /var/lib/mysql
2.2.2 日志讀取
1. 查詢long_query_time的值
- show variables like 'long%';
2. 執行查詢操作
- select * from tb_sku where id = '100000030074'\G;
- select * from tb_sku where name like '%HuaWei手機Meta87384 Pro%'\G;
3. 查詢慢查詢日志
a 使用cat
b 如果慢查詢日志很多，借助借助于mysql自帶的mysqldumpslow工具，進行分類匯總
三 explain執行計劃、索引使用和SQL優化(對某個sql進行分析)
通過以上步驟查詢到效率低的SQL語句后，可以通過EXPLAIN命令獲取Mysql如 何執行Select語句信息，包含select語句執行過程中表如何連接和連接的順 序。
3.1 執行explain命令，進行分析
- explain select * from tb_sku where id = '100000030074';
- explain select * from tb_sku where name like '%HuaWei 手機Meta87384 Pro%';
執行計劃字段解釋
3.2 對字段取值解釋
1. id
- A. id 相同表示加載表的順序是從上到下。
- B. id 不同id值越大，優先級越高，越先被執行。
- C. id 有相同，也有不同，同時存在。id相同的可以認為是一組，從上往下順序執行；在所有的組中，id的值越大，優先級越高，越先執行。
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 : 表示索引中使用的字節數， 該值為索引字段最大可能長度，并非實際使用長度，在不損失精確性的前提下， 長度越短越好 。
5. rows
- 掃描行的數量。
6. filtered
- 這個字段表示存儲引擎返回的數據在server層過濾后，剩下多少滿足查詢的記錄數量的比例。
