1.資料庫伺服器的最佳化步驟
當遇到資料庫調優問題時，思考的流程如下圖。
整個流程劃分成了觀察(Show status)和行動(Action)兩個部分。字母S的部分代表觀察(會使用相應的分析工具)，字母A代表的部分是行動(對應分析可以採取的行動)。
上圖，就是資料庫調優的思路。如果發現執行SQL時存在不規則或卡頓的時候，就可以採用分析工具幫我們定位有問題的SQL，這三種分析工具你可以理解是SQL調優的三個步驟：慢查詢、EXPLAIN和SHOW PROFILING。
小結：
2.檢視系統效能引數
在MySQL中，可以使用SHOW STATUS語句查詢一些MySQL資料庫伺服器的效能引數、執行頻率。
SHOW STATUS語句語法如下：
SHOW [GLOBAL|SESSION] STATUS LIKE '引數';
一些常用的效能引數如下：
- Connections：連線MySQL伺服器的次數。
- Uptime：MySQL伺服器的上線時間。單位是秒s
- Slow_queries：慢查詢的次數。
- Innodb_rows_read：Select查詢返回的行數
- Innodb_rows_inserted：執行INSERT操作插入的行數
- Innodb_rows_updated：執行UPDATE操作更新的行數
- Innodb_rows_deleted：執行DELETE操作刪除的行數
- Com_select：查詢操作的次數。
- Com_insert：插入操作的次數。對於批次插入的 INSERT 操作，只累加一次。
- Com_update：更新操作的次數。
- Com_delete：刪除操作的次數。
show status like 'Connections';#查詢伺服器連線次數
show status like 'Uptime';#查詢伺服器工作時間
show status like 'Slow_queries';#查詢MySQL伺服器的慢查詢次數
show status like 'Innodb_rows_%';#檢視相關的指令情況
慢查詢次數引數可以結合慢查詢日誌找出慢查詢語句，然後針對慢查詢語句進行表結構最佳化或者查詢語句最佳化
3.統計SQL的查詢成本：last_query_cost
以第8章的student_info表為例
CREATE TABLE `student_info` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`student_id` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`course_id` INT NOT NULL ,
`class_id` INT(11) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
第8章為student_info表，構建了1000000條資料
如果我們想要查詢id = 900001的記錄，然後看下查詢成本，我們可以直接在聚簇索引上進行查詢：
mysql> select * from student_info where id = 900001;
+--------+------------+--------+-----------+----------+---------------------+
| id | student_id | name | course_id | class_id | create_time |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 | 118791 | NcVxAk | 10058 | 10013 | 2024-05-03 17:41:49 |
+--------+------------+--------+-----------+----------+---------------------+
1 row in set (0.00 sec)
檢視最佳化器的成本，實際上我們只需要檢索一個頁即可：
mysql> show status like 'last_query_cost';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| Last_query_cost | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
如果我們想要查詢id在900001到900100之間的學生記錄呢？
mysql> select * from student_info where id >= 900001 and id <= 900100;
+--------+------------+--------+-----------+----------+---------------------+
| id | student_id | name | course_id | class_id | create_time |
+--------+------------+--------+-----------+----------+---------------------+
| 900001 | 118791 | NcVxAk | 10058 | 10013 | 2024-05-03 17:41:49 |
| 900002 | 162004 | yWlrZW | 10050 | 10172 | 2024-05-03 17:41:49 |
| 900003 | 160036 | JLNHVI | 10072 | 10159 | 2024-05-03 17:41:49 |
...
| 900098 | 56625 | akXhUc | 10002 | 10094 | 2024-05-03 17:41:49 |
| 900099 | 22024 | VnDeIe | 10054 | 10109 | 2024-05-03 17:41:49 |
| 900100 | 158129 | SSOofI | 10046 | 10007 | 2024-05-03 17:41:49 |
+--------+------------+--------+-----------+----------+---------------------+
100 rows in set (0.01 sec)
然後再看下查詢最佳化器的成本，這時我們大概需要進行20個頁的查詢。
mysql> show status like 'last_query_cost';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| Last_query_cost | 20.290751 |
+-----------------+-----------+
1 row in set (0.00 sec)
從結果來看，頁的數量是剛才的20倍，但是查詢的效率並沒有明顯的變化，實際上這兩個SQL查詢的時間基本上一樣，就是因為採用了順序讀取(第8章提到的順序I/O)的方式將頁面一次性載入到快取池中，然後再進行查詢，雖然頁數量(last_query_cost)增加了不少，但是透過緩衝池的機制，並沒有增加多少查詢時間。
使用場景：它對於比較開銷是非常有用的，特別是我們有好幾種查詢方式可選的時候。
SQL查詢是一個動態的過程，從頁的載入的角度來看，我們可以得到以下兩點結論：
1.位置決定效率。如果頁就在資料庫緩衝池中，那麼效率是最高的，否則還需要從記憶體或者磁碟中進行讀取，當然針對單個頁的讀取來說，如果頁存在於記憶體中，會比在磁碟中讀取效率高很多。2.
批次決定效率。如果我們從磁碟中對單一頁進行隨機讀，那麼效率是很低的(差不多10ms)，而採用順序讀取的方式，批次對頁進行讀取，平均一頁的讀取效率就會提升很多，甚至要快於單個頁面在記憶體中的隨機讀取。所以說，遇到I/O並不用擔心，方法找對了，效率還是很高的。我們首先要考慮資料存放的位置，如果是經常使用的資料就要儘量放到
緩衝池中，其次應該充分利用磁碟的吞吐能力，一次性批次讀取資料，這樣單個頁的讀取效率也就得到了提升。
4.定位執行慢的SQL：慢查詢日誌
MySQL的慢查詢日誌，用來記錄在MySQL中的響應時間超過閾值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。long_query_time的預設值為10，意思是執行10秒以上(不含10秒)的語句，認為是超出了我們的最大忍耐時間值。
它的主要作用是，幫助我們發現那些執行時間特別長的SQL查詢，並且有針對性的進行最佳化，從而提高系統的整體效率。當我們的資料庫伺服器發生阻塞，執行變慢的時候，檢查一下慢查詢日誌，找到那些慢查詢，對解決問題很有幫助。比如一條sql執行超過5秒鐘，就算慢sql的話，希望能收集超過5s的sql，結合explain進行全面分析。
預設情況下，MySQL資料庫沒有開啟慢查詢日誌，需要我們手動來設定這個引數。如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。
慢查詢日誌支援將日誌記錄寫入檔案。
4.1 開啟慢查詢日誌引數
1.開啟slow_query_long
在使用前，我們需要先看下慢查詢是否已經開啟。
mysql> show variables like '%slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+
1 row in set (0.00 sec)
能看到slow_query_log=OFF，我們可以把慢查詢日誌開啟，注意設定變數值的時候需要使用global，否則會報錯：
mysql> set global slow_query_log = on;
然後，檢視慢查詢日誌是否開啟，以及慢查詢日誌檔案的位置：
mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/x-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
可以看到，此時慢查詢已經開啟，同時檔案儲存在/var/lib/mysql/x-slow.log檔案中。注意x代表當前linux作業系統的主機名。這是預設情況。
2.修改long_query_time閾值
接下來我們來看下慢查詢的時間閾值設定，使用如下命令
mysql> show variables like '%long_query_time';#單位是秒s
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
這裡我們想把時間縮短，比如設定1秒，可以這樣設定：
mysql> set global long_query_time = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like '%long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
mysql> show session variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
注意，從執行結果來看，修改全域性慢sql的時間閾值，long_query_time的值時，預設對新的會話生效，當前會話還是修改前的值。
補充：配置檔案中--並設定引數
可以以修改配置檔案的方式，設定為永久的方式，是對比命令列方式，稱為永久。
[mysqld]
slow_query_log=ON #開啟慢查詢日誌的開關
slow_query_log_file=/var/lib/mysql/x-slow.log #慢查詢日誌的目錄和檔案資訊，可以配置成非預設位置
long_query_time = 3 #設定慢查詢的閾值為3秒，超過此設定的值的sql即被記錄到慢查詢日誌
log_output=FILE #以檔案的形式輸出日誌
如果不明確指定儲存路徑，慢查詢日誌將預設儲存到MySQL資料庫的資料資料夾下。如果不指定檔名，預設檔名為hostname-slow.log。
4.2 檢視慢查詢數目
查詢當前系統中有多少條慢查詢記錄
show global status like '%Slow_queries';
4.3 案例演示
步驟1.建表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
步驟2.設定引數log_bin_trust_function_creators
建立函式，假如報錯
This function has none of DETERMINISTIC......
- 命令開啟，允許建立函式設定
set global log_bin_trust_function_creators=1; # 不加global只是當前視窗有效。
步驟3.建立函式
隨機產生字串：(同上一章)
DELIMITER //
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #該函式會返回一個字串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
#測試
SELECT rand_string(10);
產生隨機數值：(同上一章)
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1)) ;
RETURN i;
END //
DELIMITER ;
#測試：
SELECT rand_num(10,100);
步驟4.建立儲存過程
DELIMITER //
CREATE PROCEDURE insert_stu1( START INT , max_num INT )
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0; #設定手動提交事務
REPEAT #迴圈
SET i = i + 1; #賦值
INSERT INTO student (stuno, NAME ,age ,classId ) VALUES
((START+i),rand_string(6),rand_num(10,100),rand_num(10,1000));
UNTIL i = max_num
END REPEAT;
COMMIT; #提交事務
END //
DELIMITER ;
步驟5.呼叫儲存過程
#呼叫剛剛寫好的函式, 4000000條記錄,從100001號開始 執行時間較長 大概5分鐘左右
CALL insert_stu1(100001,4000000)
4.4 測試及分析
1.測試
mysql> SELECT * FROM student WHERE stuno = 3455655;
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 3355654 | 3455655 | dOJcnf | 81 | 599 |
+---------+---------+--------+------+---------+
1 row in set (0.85 sec)
mysql> SELECT * FROM student WHERE name = 'oQmLUr';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 664741 | 764742 | Oqmlur | 55 | 502 |
| 895221 | 995222 | OQMlUR | 57 | 85 |
| 1068466 | 1168467 | Oqmlur | 56 | 542 |
| 2060322 | 2160323 | OQMlUR | 51 | 806 |
| 2173596 | 2273597 | OQMlUR | 53 | 870 |
| 2354014 | 2454015 | Oqmlur | 51 | 294 |
| 2451838 | 2551839 | oQmLUr | 96 | 791 |
| 2522014 | 2622015 | oQmLUr | 100 | 935 |
| 3249176 | 3349177 | OQMlUR | 54 | 912 |
| 3333250 | 3433251 | Oqmlur | 57 | 554 |
+---------+---------+--------+------+---------+
10 rows in set (0.92 sec)
注意，此結果中name的值，是不區分大小寫的。是因為，在建立表示未設定Collate也即校對規則。
使用的是當前資料庫的校對規則，而在前一章，建立資料庫時，也為指定校對規則，所以在mysql8.0.25預設的校對規則是utf8mb4_0900_ai_ci 是不區分大小寫的
不用記，知道有這個東西即可。
可以使用show create 語法檢視錶或資料庫使用的校對規則。
還有個小細節，在使用show create 語法檢視資料庫時，如果資料庫名中有-的需要加``才能執行成功
從上面的結果可以看出來，查詢學生編號為“3455655”的學生資訊花費時間為0.85秒。查詢學生姓名為“oQmLUr”的學生資訊花費時間為0.92秒。已經達到了秒的數量級，說明目前查詢效率是比較低的，下面的小節我們分析一下原因。
2.分析
show status like 'slow_queries';
補充說明
除了上述變數，控制慢查詢日誌的還有一個系統變數：min_examined_row_limit。這個變數的意思是，查詢
掃描過的最少記錄數。這個變數和查詢的執行時間，共同組成了判別一個查詢是否是慢查詢的條件。如果查詢掃描過的記錄數大於等於這個變數的值，並且查詢執行時間超過long_query_time的值，那麼，這個查詢就被記錄到慢查詢日誌中；反之，則不被記錄到慢查詢日誌中。mysql> show variables like 'min_%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | min_examined_row_limit | 0 | +------------------------+-------+ 1 row in set (0.00 sec)這個值預設是0。與long_query_time=10合在一起，表示只要查詢的執行時間超過10秒鐘，哪怕一條記錄也沒有掃描過，都要被記錄到慢查詢日誌中。你也可以根據需要，透過修改"my.ini"檔案，來修改查詢時長，或者透過set指令，用SQL語句修改"min_examined_row_limit"的值。
4.5 慢查詢日誌分析工具：mysqldumpslow
在生產環境中，如果要手工分析日誌，查詢，分析sql，顯然是個體力活，MySQL提供了日誌分析工具mysqldumpslow。
檢視mysqldumpslow的幫助資訊
mysqldumpslow --help #在命令列模式下執行，本質是.sh指令碼檔案
mysqldumpslow命令的具體引數如下：
[root@LinuxCentOS7-132 ~]# mysqldumpslow --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
- -a: 不將數字抽象成N，字串抽象成S
- -s: 是表示按照何種方式排序：
- c:訪問次數
- l:鎖定時間
- r:返回記錄
- t:查詢時間
- al:平均鎖定時間
- ar:平均返回記錄數
- at:平均查詢時間
- -t:即位返回前面多少條的資料；
- -g：後邊搭配一個正則匹配模式，大小寫不敏感的。
舉例：我們想要按照查詢時間排序，檢視前五條SQL語句，這樣寫即可：
#注意替換為自己的慢sql日誌檔案
[root@LinuxCentOS7-132 mysql]# mysqldumpslow -s t -t 5 /var/lib/mysql/LinuxCentOS7-132-slow.log
Reading mysql slow query log from /var/lib/mysql/LinuxCentOS7-132-slow.log
Count: 1 Time=268.52s (268s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@xxxx
CALL insert_stu1(N,N)
Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.
#這裡只有一條的原因是，設定的慢sql的long_qiery_time閾值是1秒，而之前測試的sql語句執行時間都小於1s所以，只有呼叫儲存過程的sql語句儲存在了慢sql日誌檔案中
[root@LinuxCentOS7-132 mysql]# mysqldumpslow -a -s t -t 5 /var/lib/mysql/LinuxCentOS7-132-slow.log
Reading mysql slow query log from /var/lib/mysql/LinuxCentOS7-132-slow.log
Count: 1 Time=268.52s (268s) Lock=0.00s (0s) Rows=0.0 (0), root[root]@xxxx
CALL insert_stu1(100001,4000000)
Died at /usr/bin/mysqldumpslow line 162, <> chunk 1.
#演示-a的作用，就是不將數字抽象成N，字串抽象成S 其他略
工作常用參考
#得到返回記錄集最多的10個SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
#得到訪問次數最多的10個SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
#得到按照時間排序的前10條裡面含有左連線的查詢語句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
#另外建議在使用這些命令時結合 | 和more 使用 ，否則有可能出現爆屏情況
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
4.6 關閉慢查詢日誌
MySQL伺服器停止慢查詢日誌功能有兩種方法：
方式1：永久性方式
[mysqld]
slow_query_log=OFF
或者，把slow_query_log一項註釋掉或刪除
[mysqld]
#slow_query_log=OFF
方式2：臨時性方式
使用set 語句來設定。
(1) 停止MySQL慢查詢日誌功能，具體SQL語句如下。
SET GLOBAL slow_query_log=off;
(2) 重啟MySQL服務，使用SHOW 語句查詢慢查詢日誌功能資訊，具體SQL語句如下
SHOW VARIABLES LIKE '%slow%';
#以及
SHOW VARIABLES LIKE '%long_query_time%';
4.7 刪除慢查詢日誌
使用show語句顯示慢查詢日誌資訊，具體SQL語句如下
mysql> SHOW VARIABLES LIKE 'slow_query_log%';
+---------------------+------------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/LinuxCentOS7-132-slow.log |
+---------------------+------------------------------------------+
2 rows in set (0.00 sec)
從執行結果可以看出，慢查詢日誌的目錄預設為MySQL的資料目錄，在該目錄下手動刪除慢查詢日誌檔案即可。
使用命令mysqladmin flush-logs來重新生成查詢日誌檔案，具體命令如下，執行完畢會在資料目錄下重新生成慢查詢日誌檔案。
mysqladmin -uroot -p flush-logs slow #需要輸入命令
提示
慢查詢日誌都是使用mysqladmin flush-logs命令來刪除重建的。使用時一定要注意，一旦執行了這個命令，慢查詢日誌都只存在新的日誌檔案中，如果需要舊的查詢日誌，就要先備份。
5.檢視SQL執行成本：SHOW PROFILE
在邏輯架構章節有筆記，這裡回顧
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql> set profiling = on;#開啟執行計劃
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set (0.00 sec)
mysql> use atguigudb1;
Database changed
#執行兩條sql
mysql> select * from student where stuno = 3453451;
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 3353450 | 3453451 | sWJqJx | 31 | 824 |
+---------+---------+--------+------+---------+
1 row in set (3.20 sec)
mysql> select * from student where name = 'sWJqJx';
+---------+---------+--------+------+---------+
| id | stuno | name | age | classId |
+---------+---------+--------+------+---------+
| 2160136 | 2260137 | SwJQjx | 74 | 228 |
| 3353450 | 3453451 | sWJqJx | 31 | 824 |
| 3543434 | 3643435 | sWJqJx | 33 | 938 |
| 3845668 | 3945669 | SwJQjx | 73 | 164 |
| 3918212 | 4018213 | SwJQjx | 76 | 325 |
+---------+---------+--------+------+---------+
5 rows in set (0.94 sec)
mysql> show profiles;#檢視當前會話的執行計劃
+----------+------------+---------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------+
| 1 | 0.00142000 | show variables like 'profiling' |
| 2 | 0.00033150 | SELECT DATABASE() | #這裡為何是select database()函式
| 3 | 3.20470075 | select * from student where stuno = 3453451 |
| 4 | 0.93289375 | select * from student where name = 'sWJqJx' |
+----------+------------+---------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
mysql> show profile;#檢視最近的一次查詢的開銷
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000085 |
| Executing hook on transaction | 0.000007 |
| starting | 0.000007 |
| checking permissions | 0.000006 |
| Opening tables | 0.000032 |
| init | 0.000005 |
| System lock | 0.000006 |
| optimizing | 0.000035 |
| statistics | 0.000018 |
| preparing | 0.000015 |
| executing | 0.932597 |
| end | 0.000019 |
| query end | 0.000005 |
| waiting for handler commit | 0.000008 |
| closing tables | 0.000009 |
| freeing items | 0.000033 |
| cleaning up | 0.000011 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql> show profile for query 4;#根據Query_ID檢視指定查詢
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000085 |
| Executing hook on transaction | 0.000007 |
| starting | 0.000007 |
| checking permissions | 0.000006 |
| Opening tables | 0.000032 |
| init | 0.000005 |
| System lock | 0.000006 |
| optimizing | 0.000035 |
| statistics | 0.000018 |
| preparing | 0.000015 |
| executing | 0.932597 |
| end | 0.000019 |
| query end | 0.000005 |
| waiting for handler commit | 0.000008 |
| closing tables | 0.000009 |
| freeing items | 0.000033 |
| cleaning up | 0.000011 |
+--------------------------------+----------+
17 rows in set, 1 warning (0.00 sec)
mysql> show profile cpu,block io for query 4;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000085 | 0.000066 | 0.000013 | 0 | 0 |
| Executing hook on transaction | 0.000007 | 0.000004 | 0.000001 | 0 | 0 |
| starting | 0.000007 | 0.000006 | 0.000001 | 0 | 0 |
| checking permissions | 0.000006 | 0.000004 | 0.000001 | 0 | 0 |
| Opening tables | 0.000032 | 0.000027 | 0.000006 | 0 | 0 |
| init | 0.000005 | 0.000003 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000005 | 0.000001 | 0 | 0 |
| optimizing | 0.000035 | 0.000030 | 0.000007 | 0 | 0 |
| statistics | 0.000018 | 0.000014 | 0.000002 | 0 | 0 |
| preparing | 0.000015 | 0.000012 | 0.000003 | 0 | 0 |
| executing | 0.932597 | 0.947367 | 0.177593 | 0 | 0 |
| end | 0.000019 | 0.000012 | 0.000000 | 0 | 0 |
| query end | 0.000005 | 0.000004 | 0.000000 | 0 | 0 |
| waiting for handler commit | 0.000008 | 0.000008 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000009 | 0.000000 | 0 | 0 |
| freeing items | 0.000033 | 0.000034 | 0.000000 | 0 | 0 |
| cleaning up | 0.000011 | 0.000009 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
17 rows in set, 1 warning (0.00 sec)
show profile的常用查詢引數：
① ALL：顯示所有的開銷資訊。
② BLOCK IO：顯示塊IO開銷。
③ CONTEXT SWITCHES：上下文切換開銷。
④ CPU：顯示CPU開銷資訊。
⑤ IPC：顯示傳送和接收開銷資訊。
⑥ MEMORY：顯示記憶體開銷資訊。
⑦ PAGE FAULTS：顯示頁面錯誤開銷資訊。
⑧ SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷資訊。
⑨ SWAPS：顯示交換次數開銷資訊。
日常開發需注意的結論：
- converting HEAP to MyISAM：查詢結果太大，記憶體不夠，資料往磁碟上寫入了。
- Creating tmp table：建立臨時表。先複製資料到臨時表，用完後再刪除臨時表。
- Copying to tmp table on disk：把記憶體中臨時表複製到磁碟上，警惕！
- locked。
如果在show profile診斷結果中出現了以上4條結果中的任何一條，則SQL語句需要最佳化。
注意
不過SHOW PROFILE命令將被棄用，我們可以從information_schema中的profiling資料表進行檢視。
6.分析查詢語句：EXPLAIN
6.1 概述
定位了查詢慢的SQL之後，我們就可以使用EXPLAIN或DESCRIBE工具做針對性的分析查詢語句。DESCRIBE語句的使用方法與EXPLAIN語句是一樣的，並且分析結果也是一樣的。
MySQL中有專門負責最佳化SELECT語句的最佳化器模組，主要功能：透過計算分析系統中收集到的統計資訊，為客戶端請求的Query提供它認為最優的執行計劃(他認為最優的資料檢索方式，但不見得是DBA認為的最優的，這部分最耗費時間)。
這個執行計劃展示了接下來具體執行查詢的方式，比如多表連線的順序是什麼，對於每個表採用什麼訪問方法來具體執行查詢等等。MySQL為我們提供了EXPLAIN語句來幫助我們檢視某個查詢語句的具體執行計劃，看懂EXPLAIN語句的各個輸出項，可以有針對性的提高查詢語句的效能。
1.能做什麼？
-
表的讀取順序
-
資料讀取操作的操作型別
-
那些索引可以使用
-
那些索引被實際使用
-
表之間的引用
-
每張表有多少行被最佳化器查詢
-
額外資訊
2.官網介紹
5.7
8.0
3.版本情況
- MySQL5.6.3以前只能
EXPLAIN SELECT;MySQL 5.6.3以後就可以EXPLAIN SELECT，UPDATE，DELETE - 在5.7以前的版本中，想要顯示
partitios需要使用explain partitions命令；想要顯示filtered需要使用explain extended命令。在5.7版本後，預設explain直接顯示partitions和filtered中的資訊。
6.2 基本語法
EXPLAIN或DESCRIBE語句的語法形式如下：
EXPLAIN SELECT select_options;
#或
DESCRIBE SELECT select_options;
#可以簡單的理解為，在select語句前，explain和describe關鍵字
如果我們想看看某個查詢的執行計劃的話，可以在具體的查詢語句前邊加一個EXPLAIN
mysql> explain select * from student where id = 100001;
輸出的上述資訊就是所謂的執行計劃。在這個執行計劃的輔助下，我們需要知道應該怎樣改進自己的查詢語句以使查詢執行起來更高效。其實除了以SELECT開頭的查詢語句，其餘的DELETE，INSERT，REPLACE以及UPDATE語句等都可以加上EXPLAIN，用來檢視這些語句的執行計劃，只是平時我們對SELECT語句更感興趣。
注意，執行EXPLAIN時並沒有真正的執行該關鍵字後面的語句，因此可以安全的檢視執行計劃。
EXPLAIN語句輸出的各個列的作用如下
| 列名 | 描述 |
|---|---|
id |
在一個大的查詢語句中每個SELECT關鍵字都對應一個唯一的id |
select_type |
SELECT關鍵字對應的那個查詢的型別 |
table |
表名 |
partitions |
匹配的分割槽資訊 |
type |
針對單表的訪問方法 |
possible_keys |
可能用到的索引 |
key |
實際上使用的索引 |
key_len |
實際使用到的索引長度 |
ref |
當使用索引等職查詢時，與索引列進行等職匹配的物件資訊 |
rows |
預估的需要讀取的記錄條數 |
filtered |
某個表經過搜尋條件過濾後剩餘記錄條數的百分比 |
Extra |
一些額外的資訊 |
6.3 資料準備
1.建表
CREATE DATABASE atguigudb2;
USE atguigudb2;
CREATE TABLE s1 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2 (
id INT AUTO_INCREMENT,
key1 VARCHAR(100),
key2 INT,
key3 VARCHAR(100),
key_part1 VARCHAR(100),
key_part2 VARCHAR(100),
key_part3 VARCHAR(100),
common_field VARCHAR(100),
PRIMARY KEY (id),
INDEX idx_key1 (key1),
UNIQUE INDEX idx_key2 (key2),
INDEX idx_key3 (key3),
INDEX idx_key_part(key_part1, key_part2, key_part3)
) ENGINE=INNODB CHARSET=utf8;
2.設定引數log_bin_trust_function_creators
建立函式，假如報錯，需開啟如下命令，允許建立函式設定
set global log_bin_trust_function_creators = 1;#不加global只是當前會話有效
#出現錯誤原因是bin_log日誌，不信任使用者建立的函式
3.建立函式
DELIMITER //
CREATE FUNCTION rand_string1(n INT)
RETURNS VARCHAR(255) #該函式會返回一個字串
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;
4.建立儲存過程
#向s1表插入資料
DELIMITER //
CREATE PROCEDURE insert_s1 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s1 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
#向s2表插入資料
DELIMITER //
CREATE PROCEDURE insert_s2 (IN min_num INT (10),IN max_num INT (10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO s2 VALUES(
(min_num + i),
rand_string1(6),
(min_num + 30 * i + 5),
rand_string1(6),
rand_string1(10),
rand_string1(5),
rand_string1(10),
rand_string1(10));
UNTIL i = max_num
END REPEAT;
COMMIT;
END //
DELIMITER ;
5.呼叫儲存過程
CALL insert_s1(10001,10000);#向s1表插入1萬條資料
CALL insert_s2(10001,10000);#向s2表插入1萬條資料
6.4 EXPLAIN各列作用
宋紅康老師在講解的過程中，調整了EXPLAIN輸出列的順序。
1.table
不論我們的查詢語句有多複雜，裡邊兒包含了多少個表，到最後也是需要對每個表進行單表訪問的，所以MySQL規定EXPLAIN語句輸出的每條記錄都對應著某個單表的訪問方法，該條記錄的table列代表著該表的表名（有時不是真實的表名字，可能是簡稱）。
#1. table：表名
#查詢的每一行記錄都對應著一個單表
EXPLAIN SELECT * FROM s1;
#該條語句執行結果只輸出一條記錄，其中的table列的值是s1，表明這條記錄是用來說明對s1表的單表訪問方法的
#s1:驅動表 s2:被驅動表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;
#結果，是指執行計劃的結果
#在多表連線中，假如有兩行結果，從上到下，依次是驅動表和被驅動表
#三行結果，從上到下，s1,s2,s3多表連線，其中s2是s1的被驅動表，也是s3的驅動表，其他類似
#執行結果來看，這兩條記錄的table列分別是`s1`和`s2`，這兩條記錄用來分別對`s1`表和`s2`表的訪問方法是什麼。
2.id
我們寫的查詢語句一般都以SELECT關鍵字開頭，比較簡單的查詢語句裡只有一個SELECT關鍵字，比如下邊這個查詢語句：
SELECT * FROM s1 WHERE key1 = 'a';
稍微複雜一點的連線查詢中也只有一個 SELECT 關鍵字，比如：
SELECT * FROM s1 INNER JOIN s2
ON s1.key1 = s2.key1
WHERE s1.common_field = 'a';
一條查詢sql中包含多個select關鍵字的情況
- 查詢中包含子查詢的情況
SELECT * FROM s1
WHERE key1 IN (SELECT key3 FROM s2);
- 查詢中包含
UNION語句的情況
SELECT * FROM s1 UNION SELECT * FROM s2;
查詢語句中每出現一個SELECT關鍵字，MySQL就會為其分配一個唯一的id值。這個id值就是EXPLAIN語句的第一個列，比如下邊這個查詢中只有一個SELECT關鍵字，所以EXPLAIN的結果中也就只有一條id列為1的記錄：
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
對於連線查詢來說，一個SELECT關鍵字後邊的FROM子句中可以跟隨多個表，所以在連線查詢的執行計劃中，每個表都會對應一條記錄，但是這些記錄的id值都是相同的，比如：
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key2 FROM s2 WHERE common_field = 'a');
######查詢最佳化器可能對涉及子查詢的查詢語句進行重寫,轉變為多表查詢的操作########
#如果未轉化，此處執行計劃，得select_type列，應該是一個SIMPLE和一個SUBQUERY
#Union去重
mysql> EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
#注意，在union語句的執行計劃中，會多出一行資料，因為會使用到臨時表，因為union關鍵字會去重，也就是會將合併後的資料去除重複的，
#此時就使用到了臨時表，用以儲存去重後的表資料。在Extra列，會有Using temporary
mysql> EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
#UNION ALL不會多一行資料，是因為不用去重
小結
- id如果相同，可以認為是一組，從上往下順序執行
- 在所有組中，id值越大，優先順序越高，越先執行
- 關注點：id號每個號碼，表示一趟獨立的查詢,一個sql的查詢趟數越少越好
3.select_type
一條大的查詢語句裡邊可以包含若干個SELECT關鍵字，每個SELECT關鍵字代表著一個小的查詢語句，而每個SELECT關鍵字的FROM子句中都可以包含若干張表(這些表用來做連線查詢)，每一張表都對應著執行計劃輸出中的一條記錄，對於在同一個SELECT關鍵字中的表來說，它們的id值是相同的。
MySQL為每一個SELECT關鍵字代表的小查詢都定義了一個稱之為select_type的屬性，意思是我們只要知道了某個小查詢的select_type屬性，就知道了這個小查詢在整個大查詢中扮演了一個什麼角色，我們看一下select_type都能取哪些值
mysql> EXPLAIN SELECT * FROM s1;
# 查詢語句中不包含`UNION`或者子查詢的查詢都算作是`SIMPLE`型別
#連線查詢也算是`SIMPLE`型別
mysql> EXPLAIN SELECT * FROM s1 INNER JOIN s2;
#對於包含`UNION`或者`UNION ALL`或者子查詢的大查詢來說，它是由幾個小查詢組成的，其中最左邊的那個
#查詢的`select_type`值就是`PRIMARY`
#對於包含`UNION`或者`UNION ALL`的大查詢來說，它是由幾個小查詢組成的，其中除了最左邊的那個小查詢
#以外，其餘的小查詢的`select_type`值就是`UNION`
#`MySQL`選擇使用臨時表來完成`UNION`查詢的去重工作，針對該臨時表的查詢的`select_type`就是
#`UNION RESULT`
EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;
EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;
#子查詢：
#如果包含子查詢的查詢語句不能夠轉為對應的`semi-join`(多表連線)的形式，並且該子查詢是不相關子查詢。
#該子查詢的第一個`SELECT`關鍵字代表的那個查詢的`select_type`就是`SUBQUERY`
EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2) OR key3 = 'a';
#如果包含子查詢的查詢語句不能夠轉為對應的`semi-join`(多表連線)的形式，並且該子查詢是相關子查詢，
#則該子查詢的第一個`SELECT`關鍵字代表的那個查詢的`select_type`就是`DEPENDENT SUBQUERY`
EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2 WHERE s1.key2 = s2.key2) OR key3 = 'a';
#注意的是，select_type為`DEPENDENT SUBQUERY`的查詢可能會被執行多次。
#在包含`UNION`或者`UNION ALL`的大查詢中，如果各個小查詢都依賴於外層查詢的話，那除了
#最左邊的那個小查詢之外，其餘的小查詢的`select_type`的值就是`DEPENDENT UNION`。
EXPLAIN SELECT * FROM s1
WHERE key1 IN (SELECT key1 FROM s2 WHERE key1 = 'a' UNION SELECT key1 FROM s1 WHERE key1 = 'b');
#注意，從sql語句上看，該sql語句，中的子查詢，只會執行一次，也即是不相關子查詢，怎麼也會是DEPENDENT，也就是相關子查詢才會出現得關鍵字呢？
#根據宋紅康老師的講解，查詢最佳化器，會把IN關鍵字轉換成exists，也就是會拿外查詢中s1的資料中的key列的值，去和子查詢中得出的結果，進行比較判斷當前列是否在子查詢的執行結果中，如此迴圈，直至判斷全部的s1表的key1列資料，這樣也是相關子查詢。這估計也是IN關鍵字會使得索引失效的原因，我個人根據宋紅康老師講解做的解釋，不一定正確。
#對於包含`派生表`的查詢，該派生表對應的子查詢的`select_type`就是`DERIVED`
EXPLAIN SELECT *
FROM (SELECT key1, COUNT(*) AS c FROM s1 GROUP BY key1) AS derived_s1 WHERE c > 1;
#當查詢最佳化器在執行包含子查詢的語句時，選擇將子查詢物化之後與外層查詢進行連線查詢時，
#該子查詢對應的`select_type`屬性就是`MATERIALIZED`
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 IN (SELECT key1 FROM s2)\G #子查詢被轉為了物化表，資料過長，採用\G進行行列轉換
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: s1
partitions: NULL
type: ALL
possible_keys: idx_key1
key: NULL
key_len: NULL
ref: NULL
rows: 9895
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: <subquery2>
partitions: NULL
type: eq_ref
possible_keys: <auto_distinct_key>
key: <auto_distinct_key>
key_len: 303
ref: atguigudb2.s1.key1
rows: 1
filtered: 100.00
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: MATERIALIZED
table: s2
partitions: NULL
type: index
possible_keys: idx_key1
key: idx_key1
key_len: 303
ref: NULL
rows: 9895
filtered: 100.00
Extra: Using index
3 rows in set, 1 warning (0.00 sec)
4.partitions(可略)
-- 建立分割槽表，
-- 按照id分割槽，id<100 p0分割槽，其他p1分割槽
CREATE TABLE user_partitions (
id INT auto_increment,
NAME VARCHAR(12),
PRIMARY KEY(id))
PARTITION BY RANGE(id)(
PARTITION p0 VALUES less than(100),
PARTITION p1 VALUES less than MAXVALUE
);
#查詢id大於200（200>100，p1分割槽）的記錄，檢視執行計劃，partitions是p1，符合我們的分割槽規則
DESC SELECT * FROM user_partitions WHERE id>200;
5.type(重點)
執行計劃的一條記錄就代表著MySQL對某個表的執行查詢時的訪問方法，又稱"訪問型別"，其中的type列就表明了對這個表的訪問方法，是一個重要的指標。比如，看到type列的值是ref，表明MySQL即將使用ref訪問方法來執行對s1表的查詢。
完整的訪問方法如下：system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL。
不在展示結果。
#當表中`只有一條記錄`並且該表使用的儲存引擎的統計資料是精確的，比如MyISAM、Memory，
#那麼對該表的訪問方法就是`system`。
CREATE TABLE t(i INT) ENGINE=MYISAM;
INSERT INTO t VALUES(1);
EXPLAIN SELECT * FROM t;
#換成InnoDB
CREATE TABLE tt(i INT) ENGINE=INNODB;
INSERT INTO tt VALUES(1);
EXPLAIN SELECT * FROM tt;
#當我們根據主鍵或者唯一二級索引列與常數進行等值匹配時，對單表的訪問方法就是`const`
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
EXPLAIN SELECT * FROM s1 WHERE key2 = 10066;
#在連線查詢時，如果被驅動表是透過主鍵或者唯一二級索引列等值匹配的方式進行訪問的
#（如果該主鍵或者唯一二級索引是聯合索引的話，所有的索引列都必須進行等值比較），則
#對該被驅動表的訪問方法就是`eq_ref`
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
#當透過普通的二級索引列與常量進行等值匹配時來查詢某個表，那麼對該表的訪問方法就可能是`ref`
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
#當對普通二級索引進行等值匹配查詢，該索引列的值也可以是`NULL`值時，那麼對該表的訪問方法
#就可能是`ref_or_null`
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key1 IS NULL;
#單表訪問方法時在某些場景下可以使用`Intersection`、`Union`、
#`Sort-Union`這三種索引合併的方式來執行查詢 這個是index_merge
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';#注意過濾條件的連線符必須是OR
#`unique_subquery`是針對在一些包含`IN`子查詢的查詢語句中，如果查詢最佳化器決定將`IN`子查詢
#轉換為`EXISTS`子查詢，而且子查詢可以使用到主鍵進行等值匹配的話，那麼該子查詢執行計劃的`type`
#列的值就是`unique_subquery`
EXPLAIN SELECT * FROM s1
WHERE key2 IN (SELECT id FROM s2 WHERE s1.key1 = s2.key1) OR key3 = 'a';
#如果使用索引獲取某些`範圍區間`的記錄，那麼就可能使用到`range`訪問方法
EXPLAIN SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c');
#同上
EXPLAIN SELECT * FROM s1 WHERE key1 > 'a' AND key1 < 'b';
#當我們可以使用索引覆蓋，但需要掃描全部的索引記錄時，該表的訪問方法就是`index`
EXPLAIN SELECT key_part2 FROM s1 WHERE key_part3 = 'a';
#索引覆蓋，就是過濾條件和查詢的列都屬於聯合索引的一部分的話，就會使用到該聯合索引的一種現象。也可以講，不用回表操作就是索引覆蓋，後面會講。
#最熟悉的全表掃描
EXPLAIN SELECT * FROM s1;
小結：
結果值從最好到最壞依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index > index_merge > unique_subquery > index_subquery > range > index > ALL
其中比較重要的是，藍色的部分。SQL效能最佳化的目標：至少要達到range級別，要求是ref級別，最好是const級別。(阿里巴巴開發手冊要求)
6.possible_key和key
possible_key表示可能會使用的索引，key列表示實際使用到的索引。
mysql> EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key3 = 'a';
7.key_len(重要)
#7. key_len：實際使用到的索引長度(即：位元組數)
# 幫你檢查`是否充分的利用上了索引`，`值越大越好`,主要針對於聯合索引，有一定的參考意義。
EXPLAIN SELECT * FROM s1 WHERE id = 10005;
EXPLAIN SELECT * FROM s1 WHERE key2 = 10126;
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b';
EXPLAIN SELECT * FROM s1 WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';
EXPLAIN SELECT * FROM s1 WHERE key_part3 = 'a';#最左字首原則
#練習：
#單位 位元組B 1B = 8bit
#varchar(10)變長欄位且允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長欄位)
#varchar(10)變長欄位且不允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+2(變長欄位)
#char(10)固定欄位且允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)
#char(10)固定欄位且不允許NULL = 10 * ( character set：utf8=3,gbk=2,latin1=1)
8.ref
# 8. ref：當使用索引列等值查詢時，與索引列進行等值匹配的物件資訊。
#比如只是一個常數或者是某個列。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a';
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.id = s2.id;
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s2.key1 = UPPER(s1.key1);
9.rows(重要)
# 9. rows：預估的需要讀取的記錄條數
# `值越小越好`
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z';
10.filtered
# 10. filtered: 某個表經過搜尋條件過濾後剩餘記錄條數的百分比，值越大越好
#如果使用的是索引執行的單表掃描，那麼計算時需要估計出滿足除使用
#到對應索引的搜尋條件外的其他搜尋條件的記錄有多少條。
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND common_field = 'a';
#對於單表查詢來說，這個filtered列的值沒什麼意義，我們`更關注在連線查詢
#中驅動表對應的執行計劃記錄的filtered值`，它決定了被驅動表要執行的次數(即：rows * filtered)
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key1 WHERE s1.common_field = 'a';
11.Extra
#11. Extra:一些額外的資訊
#更準確的理解MySQL到底將如何執行給定的查詢語句
#``中的字串，是在Extra列顯示的
#當查詢語句的沒有`FROM`子句時將會提示該額外資訊 `No tables used`
EXPLAIN SELECT 1;
#查詢語句的`WHERE`子句永遠為`FALSE`時將會提示該額外資訊 `Impossible WHERE`
EXPLAIN SELECT * FROM s1 WHERE 1 != 1;
#當我們使用全表掃描來執行對某個表的查詢，並且該語句的`WHERE`
#子句中有針對該表的搜尋條件時，在`Extra`列中會提示上述額外資訊。 `Using where`
EXPLAIN SELECT * FROM s1 WHERE common_field = 'a';
#當使用索引訪問來執行對某個表的查詢，並且該語句的`WHERE`子句中 `Using where`
#有除了該索引包含的列之外的其他搜尋條件時，在`Extra`列中也會提示上述額外資訊。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' AND common_field = 'a';
#當查詢列表處有`MIN`或者`MAX`聚合函式，但是並沒有符合`WHERE`子句中
#的搜尋條件的記錄時，將會提示該額外資訊
#`No matching min/max row`
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'abcdefg';
EXPLAIN SELECT MIN(key1) FROM s1 WHERE key1 = 'STNmSD'; #STNmSD 是 s1表中key1欄位真實存在的資料
# `Select tables optimized away`
#select * from s1 limit 10;
#當我們的查詢列表以及搜尋條件中只包含屬於某個索引的列，也就是在可以
#使用覆蓋索引的情況下，在`Extra`列將會提示該額外資訊。比方說下邊這個查詢中只
#需要用到`idx_key1`而不需要回表操作： `Using index`
EXPLAIN SELECT key1,id FROM s1 WHERE key1 = 'a';
#有些搜尋條件中雖然出現了索引列，但卻不能使用到索引 `Using index condition`
#看課件理解索引條件下推
EXPLAIN SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
#`索引條件下推` 是指如果key1 > 'z' 此時使用到了key1列對應的索引，但是後續又有過濾條件使用該索引列，
#此時不急著把查出的id回表，也就是根據後續繼續使用該索引過濾的條件，
# 進行先過濾一部分id，之後，在把過濾後的主鍵id，用以回表查詢資料 這樣的一個過程，稱為`索引條件下推`
#因為回表操作其實是一個`隨機IO`，比較耗時，所以上述修改雖然只改進了一點點，但是可以省去好多回表操作的成本。
#在連線查詢執行過程中，當被驅動表不能有效的利用索引加快訪問速度，MySQL一般會為
#其分配一塊名叫`join buffer`的記憶體塊來加快查詢速度，也就是我們所講的`基於塊的巢狀迴圈演算法`
#見課件說明
EXPLAIN SELECT * FROM s1 INNER JOIN s2 ON s1.common_field = s2.common_field;
#當我們使用左（外）連線時，如果`WHERE`子句中包含要求被驅動表的某個列等於`NULL`值的搜尋條件，
#而且那個列又是不允許儲存`NULL`值的，那麼在該表的執行計劃的Extra列就會提示`Not exists`額外資訊
EXPLAIN SELECT * FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE s2.id IS NULL;
#如果執行計劃的`Extra`列出現了`Using intersect(...)`提示，說明準備使用`Intersect`索引
#合併的方式執行查詢，括號中的`...`表示需要進行索引合併的索引名稱；
#如果出現了`Using union(...)`提示，說明準備使用`Union`索引合併的方式執行查詢；
#出現了`Using sort_union(...)`提示，說明準備使用`Sort-Union`索引合併的方式執行查詢。
EXPLAIN SELECT * FROM s1 WHERE key1 = 'a' OR key3 = 'a';
#當我們的`LIMIT`子句的引數為`0`時，表示壓根兒不打算從表中讀出任何記錄，將會提示該額外資訊
EXPLAIN SELECT * FROM s1 LIMIT 0;#`Zero Limit`
#有一些情況下對結果集中的記錄進行排序是可以使用到索引的。
#比如：無提示資訊
EXPLAIN SELECT * FROM s1 ORDER BY key1 LIMIT 10;
#很多情況下排序操作無法使用到索引，只能在記憶體中（記錄較少的時候）或者磁碟中（記錄較多的時候）
#進行排序，MySQL把這種在記憶體中或者磁碟上進行排序的方式統稱為檔案排序（英文名：`filesort`）。
#如果某個查詢需要使用檔案排序的方式執行查詢，就會在執行計劃的`Extra`列中顯示`Using filesort`提示
#出現此提示詞，要考慮進行最佳化
EXPLAIN SELECT * FROM s1 ORDER BY common_field LIMIT 10;
#在許多查詢的執行過程中，MySQL可能會藉助臨時表來完成一些功能，比如去重、排序之類的，比如我們
#在執行許多包含`DISTINCT`、`GROUP BY`、`UNION`等子句的查詢過程中，如果不能有效利用索引來完成
#查詢，MySQL很有可能尋求透過建立內部的臨時表來執行查詢。如果查詢中使用到了內部的臨時表，在執行
#計劃的`Extra`列將會顯示`Using temporary`提示
EXPLAIN SELECT DISTINCT common_field FROM s1;
#EXPLAIN SELECT DISTINCT key1 FROM s1;
#同上。
EXPLAIN SELECT common_field, COUNT(*) AS amount FROM s1 GROUP BY common_field;
#執行計劃中出現`Using temporary`並不是一個好的徵兆，因為建立與維護臨時表要付出很大成本的，所以
#我們`最好能使用索引來替代掉使用臨時表`。比如：掃描指定的索引idx_key1即可
EXPLAIN SELECT key1, COUNT(*) AS amount FROM s1 GROUP BY key1;
12.小結
- EXPLAIN不考慮各種Cache
- EXPLAIN不能顯示MySQL在執行查詢時所做的最佳化工作
- EXPLAIN不會告訴你關於觸發器，儲存過程的資訊或使用者自定義函式對查詢的影響情況
- 部分統計資訊是估算的，並非精確值
7.EXPLAIN的進一步使用
7.1 EXPLAIN的四種輸出格式
這裡談談EXPLAIN的輸出格式。EXPLAIN可以輸出四種格式：傳統格式，JSON格式，TREE格式以及視覺化輸出。
1.傳統格式
見6.4小節。此處略
2.JSON格式
第1種格式中介紹的EXPLAIN語句輸出中缺少了一個衡量執行計劃好壞的重要屬性--成本。而JSON格式是四種格式裡輸出資訊最詳盡的格式，裡面包含了執行的成本資訊。
- JSON格式：在EXPLAIN單詞和真正的查詢語句中間加上
FORMAT=JSON
EXPLAIN FORMAT=JSON SELECT...
- EXPLAIN的COLUMN與JSON的對應關係
這樣我們就可以得到一個json格式的執行計劃，裡面包含該計劃花費的成本，
mysql> EXPLAIN FORMAT=JSON SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2
-> WHERE s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: {
"query_block": {
"select_id": 1,#整個查詢語句只有1個SELECT關鍵字，該關鍵字對應的id號為1
"cost_info": {
"query_cost": "1360.07" #整個查詢的執行成本預計為3197.16
},
#以下是參與巢狀迴圈連線演算法的各個表的資訊
"nested_loop": [
{
"table": {
"table_name": "s1", #s1表是驅動表
"access_type": "ALL", #訪問方法為ALL，意味著使用全表掃描訪問
"possible_keys": [
"idx_key1"
],
"rows_examined_per_scan": 9895,#查詢一次s1表大致需要掃描9688條記錄
"rows_produced_per_join": 989,#驅動表s1的扇出是968
"filtered": "10.00",#condition filtering代表的百分比
"cost_info": {
"read_cost": "914.80",
"eval_cost": "98.95",
"prefix_cost": "1013.75",#單次查詢s1表總共的成本
"data_read_per_join": "1M"#讀取的資料量
},
"used_columns": [#執行查詢中涉及到的列
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
],
#對s1表訪問時針對單表查詢的條件
"attached_condition": "((`atguigudb2`.`s1`.`common_field` = 'a') and (`atguigudb2`.`s1`.`key1` is not null))"
}
},
{
"table": {
"table_name": "s2", #s2表是被驅動表
"access_type": "eq_ref", #訪問方法為ref，意味著使用索引等值匹配的方式訪問
"possible_keys": [
"idx_key2"
],
"key": "idx_key2",
"used_key_parts": [#使用到的索引列
"key2"
],
"key_length": "5",#key_len
"ref": [#與key2列進行等值匹配的物件
"atguigudb2.s1.key1"
],
"rows_examined_per_scan": 1,#查詢一次s2表大致需要掃描1條記錄
"rows_produced_per_join": 989,#被驅動表s2的扇出是968(由於後邊沒有多餘的表進行連線，所以這個值沒啥用)
"filtered": "100.00",#filtered列
#s2表使用索引進行查詢的搜尋條件
"index_condition": "(cast(`atguigudb2`.`s1`.`key1` as double) = cast(`atguigudb2`.`s2`.`key2` as double))",
"cost_info": {
"read_cost": "247.38",
"eval_cost": "98.95",
"prefix_cost": "1360.08", #單次查詢s1,多次查詢s2表總共的成本
"data_read_per_join": "1M"
},
"used_columns": [#執行查詢中涉及到的列
"id",
"key1",
"key2",
"key3",
"key_part1",
"key_part2",
"key_part3",
"common_field"
]
}
}
]
}
}
1 row in set, 2 warnings (0.00 sec)
關於cost_info的解釋
"cost_info": {
"read_cost": "914.80",
"eval_cost": "98.95",
"prefix_cost": "1013.75",#單次查詢s1表總共的成本
"data_read_per_join": "1M"#讀取的資料量
},
- read_cost是由下邊這兩部分組成的
- IO成本
- 檢測rows * (1 - filter)條記錄的CPU成本
rows和filter都是我們前邊介紹執行計劃的輸出列，在JSON格式的執行計劃中，rows相當於rows_examined_per_scan，filtered名稱不變。
- eval_cost 是這樣計算的：檢測 rows × filter 條記錄的成本。
- prefix_cost 就是單獨查詢 s1 表的成本，也就是：read_cost + eval_cost
- data_read_per_join 表示在此次查詢中需要讀取的資料量。
對與s2表的cost_info部分是這樣的
"cost_info": {
"read_cost": "247.38",
"eval_cost": "98.95",
"prefix_cost": "1360.08", #單次查詢s1,多次查詢s2表總共的成本
"data_read_per_join": "1M"
},
由於s2表是被驅動表，所以可能被讀取多次，這裡的read_cost和eval_cost是訪問多次s2表後累加起來的值，大家主要關注裡邊兒的prefix_cost的值代表的是整個連線查詢預計的成本，也就是單次查詢s1表和多次查詢s2表後的成本的和，也就是：
1013.75 + 247.38 + 98.95 = 1360.08
3.TREE格式
TREE格式是8.0.16版本之後引入的新格式，主要根據查詢的各個部分之間的關係和各部分的執行順序來描述如何查詢。
mysql> EXPLAIN FORMAT=TREE SELECT * FROM s1 INNER JOIN s2 ON s1.key1 = s2.key2 WHERE s1.common_field = 'a'\G
*************************** 1. row ***************************
EXPLAIN: -> Nested loop inner join (cost=1360.08 rows=990)
-> Filter: ((s1.common_field = 'a') and (s1.key1 is not null)) (cost=1013.75 rows=990)
-> Table scan on s1 (cost=1013.75 rows=9895)
-> Single-row index lookup on s2 using idx_key2 (key2=s1.key1), with index condition: (cast(s1.key1 as double) = cast(s2.key2 as double)) (cost=0.25 rows=1)
1 row in set, 1 warning (0.00 sec)
4. 視覺化輸出
視覺化輸出，可以透過MySQL Workbench視覺化檢視MySQL的執行計劃。透過點選Workbench的放大鏡圖示，即可生成視覺化的查詢計劃。
略
7.2 SHOW WARINGS的使用
mysql> EXPLAIN SELECT s1.key1, s2.key1 FROM s1 LEFT JOIN s2 ON s1.key1 = s2.key1 WHERE
-> s2.common_field IS NOT NULL;
注意，從執行計劃來看，s2作為了驅動表，s1作為了被驅動表，但是SQL語句中LEFT JOIN應該是s2作為主表的。這就表明select查詢最佳化器，選擇了它認為更好的執行計劃，對原有的sql語句進行了改寫，可以透過show warnings語句檢視，注意，該語句要作為檢視執行計劃下一條sql。使用該語句，可以看到改寫後執行的sql語句。
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `atguigudb2`.`s1`.`key1` AS `key1`,`atguigudb2`.`s2`.`key1` AS `key1` from `atguigudb2`.`s1` join `atguigudb2`.`s2` where ((`atguigudb2`.`s1`.`key1` = `atguigudb2`.`s2`.`key1`) and (`atguigudb2`.`s2`.`common_field` is not null))
1 row in set (0.00 sec)
8.分析最佳化器執行計劃：trace(可略)
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
開啟後，可分析如下語句：
SELECT、INSERT、REPLACE、UPDATE、DELETE、EXPLAIN、SET、DECLARE、CASE、IF、RETURN、CALL
測試：執行如下SQL語句
use atguigudb1;
select * from student where id < 10;
最後， 查詢 information_schema.optimizer_trace 就可以知道MySQL是如何執行SQL的 ：
*************************** 1. row ***************************
//第1部分：查詢語句
QUERY: select * from student where id < 10
//第2部分：QUERY欄位對應語句的跟蹤資訊
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `student`.`id` AS `id`,`student`.`stuno` AS `stuno`,`student`.`name` AS `name`,`student`.`age` AS `age`,`student`.`classId` AS `classId` from `student` where (`student`.`id` < 10)"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "(`student`.`id` < 10)",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(`student`.`id` < 10)"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(`student`.`id` < 10)"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "(`student`.`id` < 10)"
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */
},
{
"table_dependencies": [
{
"table": "`student`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`student`",
"range_analysis": {
"table_scan": {
"rows": 3806334,
"cost": 390596
} /* table_scan */,
"potential_range_indexes": [
{
"index": "PRIMARY",
"usable": true,
"key_parts": [
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"skip_scan_range": {
"potential_skip_scan_indexes": [
{
"index": "PRIMARY",
"usable": false,
"cause": "query_references_nonkey_column"
}
] /* potential_skip_scan_indexes */
} /* skip_scan_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "PRIMARY",
"ranges": [
"id < 10"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": true,
"using_mrr": false,
"index_only": false,
"rows": 9,
"cost": 1.84735,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "PRIMARY",
"rows": 9,
"ranges": [
"id < 10"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 9,
"cost_for_plan": 1.84735,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`student`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 9,
"access_type": "range",
"range_details": {
"used_index": "PRIMARY"
} /* range_details */,
"resulting_rows": 9,
"cost": 2.74735,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 9,
"cost_for_plan": 2.74735,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "(`student`.`id` < 10)",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`student`",
"attached": "(`student`.`id` < 10)"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"finalizing_table_conditions": [
{
"table": "`student`",
"original_table_condition": "(`student`.`id` < 10)",
"final_table_condition ": "(`student`.`id` < 10)"
}
] /* finalizing_table_conditions */
},
{
"refine_plan": [
{
"table": "`student`"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}
//第3部分：跟蹤資訊過長時，被截斷的跟蹤資訊的位元組數。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 //丟失的超出最大容量的位元組
//第4部分：執行跟蹤語句的使用者是否有檢視物件的許可權。當不具有許可權時，該列資訊為1且TRACE欄位為空，一般在
呼叫帶有SQL SECURITY DEFINER的檢視或者是儲存過程的情況下，會出現此問題。
INSUFFICIENT_PRIVILEGES: 0 //缺失許可權
1 row in set (0.00 sec)
9.MySQL監控分析檢視-sys schema
9.1 Sys schema檢視摘要
1. 主機相關：以host_summary開頭，主要彙總了IO延遲的資訊。
2. Innodb****相關：以innodb開頭，彙總了innodb buffer資訊和事務等待innodb鎖的資訊。
3. I/o****相關：以io開頭，彙總了等待I/O、I/O使用量情況。
4. 記憶體使用情況：以memory開頭，從主機、執行緒、事件等角度展示記憶體的使用情況
5. 連線與會話資訊：processlist和session相關檢視，總結了會話相關資訊。
6. 表相關：以schema_table開頭的檢視，展示了表的統計資訊。
7. 索引資訊：統計了索引的使用情況，包含冗餘索引和未使用的索引情況。
8. 語句相關：以statement開頭，包含執行全表掃描、使用臨時表、排序等的語句資訊。
9. 使用者相關：以user開頭的檢視，統計了使用者使用的檔案I/O、執行語句統計資訊。
10. 等待事件相關資訊：以wait開頭，展示等待事件的延遲情況。
9.2 Sys schema檢視使用場景
索引情況
#1. 查詢冗餘索引
select * from sys.schema_redundant_indexes;
#2. 查詢未使用過的索引
select * from sys.schema_unused_indexes;
#3. 查詢索引的使用情況
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;
表相關
# 1. 查詢表的訪問量
select table_schema,table_name,sum(io_read_requests+io_write_requests) as io from
sys.schema_table_statistics group by table_schema,table_name order by io desc;
# 2. 查詢佔用bufferpool較多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 檢視錶的全表掃描情況
select * from sys.statements_with_full_table_scans where db='dbname';
語句相關
#1. 監控SQL執行的頻率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 監控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 監控使用了臨時表或者磁碟臨時表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;
IO相關
#1. 檢視消耗磁碟IO的檔案
select file,avg_read,avg_write,avg_read+avg_write as avg_io
from sys.io_global_by_file_by_bytes order by avg_read limit 10;
Innodb 相關
#1. 行鎖阻塞情況
select * from sys.innodb_lock_waits;
風險提示
透過sys庫去查詢時，MySQL會
消耗大量資源去收集相關資訊，嚴重的可能會導致業務請求被阻塞，從而引起故障。建議生產上不要頻繁的去查詢sys或者performance_schema、information_schema來完成監控、巡檢等工作。
只是為了記錄自己的學習歷程，且本人水平有限，不對之處，請指正。