第 3 章 查詢擷取分析

catkin_ws發表於2020-12-28

第 3 章 查詢擷取分析

1、查詢優化

1.1、MySQL 優化原則

mysql 的調優大綱

  1. 慢查詢的開啟並捕獲
  2. explain+慢SQL分析
  3. show profile查詢SQL在Mysql伺服器裡面的執行細節和生命週期情況
  4. SQL資料庫伺服器的引數調優

永遠小表驅動大表，類似巢狀迴圈 Nested Loop

  1. EXISTS 語法：
    • SELECT ... FROM table WHERE EXISTS(subquery)
    • 該語法可以理解為：將查詢的資料，放到子查詢中做條件驗證，根據驗證結果（TRUE或FALSE）來決定主查詢的資料結果是否得以保留。
  2. EXISTS(subquery) 只返回TRUE或FALSE，因此子查詢中的SELECT *也可以是SELECT 1或其他，官方說法是實際執行時會忽略SELECT清單，因此沒有區別
  3. EXISTS子查詢的實際執行過程可能經過了優化而不是我們理解上的逐條對比，如果擔憂效率問題，可進行實際檢驗以確定是否有效率問題。
  4. EXISTS子查詢往往也可以用條件表示式、其他子查詢或者JOIN來替代，何種最優需要具體問題具體分析

image-20200805101726632

結論：

  1. 永遠記住小表驅動大表
  2. 當 B 表資料集小於 A 表資料集時，使用 in
  3. 當 A 表資料集小於 B 表資料集時，使用 exist

in 和 exists 的用法

  • tbl_emp 表和 tbl_dept 表
mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

mysql> select * from tbl_dept;
±—±---------±-------+
| id | deptName | locAdd |
±—±---------±-------+
| 1 | RD | 11 |
| 2 | HR | 12 |
| 3 | MK | 13 |
| 4 | MIS | 14 |
| 5 | FD | 15 |
±—±---------±-------+
5 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • in 的寫法
mysql> select * from tbl_emp e where e.deptId in (select id from tbl_dept);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • exists 的寫法
mysql> select * from tbl_emp e where exists (select 1 from tbl_dept d where e.deptId = d.id);
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
+----+------+--------+
7 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

1.2、ORDER BY 優化

ORDER BY子句，儘量使用Index方式排序，避免使用FileSort方式排序

建立表

  • 建表 SQL
create table tblA(
    #id int primary key not null auto_increment,
    age int,
    birth timestamp not null
);

insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());

create index idx_A_ageBirth on tblA(age, birth);

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • tblA 表中的測試資料
mysql> select * from tblA;
+------+---------------------+
| age  | birth               |
+------+---------------------+
|   22 | 2020-08-05 10:36:32 |
|   23 | 2020-08-05 10:36:32 |
|   24 | 2020-08-05 10:36:32 |
+------+---------------------+
3 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • tbl 中的索引
mysql> SHOW INDEX FROM tblA;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tblA  |          1 | idx_A_ageBirth |            1 | age         | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |               |
| tblA  |          1 | idx_A_ageBirth |            2 | birth       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

CASE1：能使用索引進行排序的情況

  • 只有帶頭大哥 age
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> EXPLAIN SELECT * FROM tblA where birth>‘2016-01-28 00:00:00’ order by age;
±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using where; Using index |
±—±------------±------±------±--------------±---------------±--------±-----±-----±-------------------------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 帶頭大哥 age + 小弟 birth
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by age,birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • mysql 預設升序排列，全升序或者全降序，都扛得住
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth ASC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM tblA ORDER BY age DESC, birth DESC;
±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
| 1 | SIMPLE | tblA | index | NULL | idx_A_ageBirth | 9 | NULL | 3 | Using index |
±—±------------±------±------±--------------±---------------±--------±-----±-----±------------+
1 row in set (0.01 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

CASE2：不能使用索引進行排序的情況

  • 帶頭大哥 age 掛了
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 小弟 birth 居然敢在帶頭大哥 age 前面
mysql> EXPLAIN SELECT * FROM tblA where age>20 order by birth,age;
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys  | key            | key_len | ref  | rows | Extra                                    |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
|  1 | SIMPLE      | tblA  | index | idx_A_ageBirth | idx_A_ageBirth | 9       | NULL |    3 | Using where; Using index; Using filesort |
+----+-------------+-------+-------+----------------+----------------+---------+------+------+------------------------------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • mysql 預設升序排列，如果全升序或者全降序，都 ok ，但是一升一降 mysql 就扛不住了
mysql> EXPLAIN SELECT * FROM tblA ORDER BY age ASC, birth DESC;
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key            | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | tblA  | index | NULL          | idx_A_ageBirth | 9       | NULL |    3 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+----------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

結論

  1. MySQL支援二種方式的排序，FileSort和Index，Index效率高，它指MySQL掃描索引本身完成排序，FileSort方式效率較低。
  2. ORDER BY滿足兩情況（最佳左字首原則），會使用Index方式排序
    • ORDER BY語句使用索引最左前列
    • 使用where子句與OrderBy子句條件列組合滿足索引最左前列
  3. 儘可能在索引列上完成排序操作，遵照索引建的最佳左字首

如果未在索引列上完成排序，mysql 會啟動 filesort 的兩種演算法：雙路排序和單路排序

  1. 雙路排序
    • MySQL4.1之前是使用雙路排序，字面意思是兩次掃描磁碟，最終得到資料。讀取行指標和將要進行orderby操作的列，對他們進行排序，然後掃描已經排序好的列表，按照列表中的值重新從列表中讀取對應的資料傳輸
    • 從磁碟取排序欄位，在buffer進行排序，再從磁碟取其他欄位。
  2. 單路排序
    • 取一批資料，要對磁碟進行兩次掃描，眾所周知，I/O是很耗時的，所以在mysql4.1之後，出現了改進的演算法，就是單路排序。
    • 從磁碟讀取查詢需要的所有列，按照將要進行orderby的列，在sort buffer對它們進行排序，然後掃描排序後的列表進行輸出，它的效率更快一些，避免了第二次讀取資料，並且把隨機IO變成順序IO，但是它會使用更多的空間，因為它把每一行都儲存在記憶體中了。
  3. 結論及引申出的問題：
    • 由於單路是改進的演算法，總體而言好過雙路
    • 在sort_buffer中，方法B比方法A要多佔用很多空間，因為方法B是把所有欄位都取出，所以有可能取出的資料的總大小超出了sort_buffer的容量，導致每次只能取sort_buffer容量大小的資料，進行排序（建立tmp檔案，多路合併），排完再取取sort_buffer容量大小，再排…… 從而會導致多次I/O。
    • 結論：本來想省一次I/O操作，反而導致了大量的/O操作，反而得不償失。
  4. 更深層次的優化策略：
    • 增大sort_buffer_size引數的設定
    • 增大max_length_for_sort_data引數的設定

遵循如下規則，可提高Order By的速度

  1. Order by時select *是一個大忌，只Query需要的欄位，這點非常重要。在這裡的影響是：
    • 當Query的欄位大小總和小於max_length_for_sort_data，而且排序欄位不是TEXT|BLOB型別時，會用改進後的演算法——單路排序，否則用老演算法——多路排序。
    • 兩種演算法的資料都有可能超出sort_buffer的容量，超出之後，會建立tmp檔案進行合併排序，導致多次I/O，但是用單路排序演算法的風險會更大一些，所以要提高sort_buffer_size。
  2. 嘗試提高 sort_buffer_size不管用哪種演算法，提高這個引數都會提高效率，當然，要根據系統的能力去提高，因為這個引數是針對每個程式的
  3. 嘗試提高max_length_for_sort_data提高這個引數，會增加用改進演算法的概率。但是如果設的太高，資料總容量超出sort_buffer_size的概率就增大，明顯症狀是高的磁碟I/O活動和低的處理器使用率。

Order By 排序索引優化的總結

image-20200805111725731

1.3、GROUP BY 優化

group by關鍵字優化

  1. group by實質是先排序後進行分組，遵照索引的最佳左字首
  2. 當無法使用索引列，增大max_length_for_sort_data引數的設定+增大sort_buffer_size引數的設定
  3. where高於having，能寫在where限定的條件就不要去having限定了
  4. 其餘的規則均和 order by 一致

2、慢查詢日誌

2.1、慢查詢日誌介紹

慢查詢日誌是什麼？

  1. MySQL的慢查詢日誌是MySQL提供的一種日誌記錄，它用來記錄在MySQL中響應時間超過閥值的語句，具體指執行時間超過long_query_time值的SQL，則會被記錄到慢查詢日誌中。
  2. long_query_time的預設值為10，意思是執行10秒以上的SQL語句會被記錄下來
  3. 由他來檢視哪些SQL超出了我們的最大忍耐時間值，比如一條sql執行超過5秒鐘，我們就算慢SQL，希望能收集超過5秒的sql，結合之前explain進行全面分析。

2.2、慢查詢日誌開啟

怎麼玩？

說明：

  1. 預設情況下，MySQL資料庫沒有開啟慢查詢日誌，需要我們手動來設定這個引數。
  2. 當然，如果不是調優需要的話，一般不建議啟動該引數，因為開啟慢查詢日誌會或多或少帶來一定的效能影響。慢查詢日誌支援將日誌記錄寫入檔案

檢視是否開啟及如何開啟

  • 檢視慢查詢日誌是否開啟：
    • 預設情況下slow_query_log的值為OFF，表示慢查詢日誌是禁用的
    • 可以通過設定slow_query_log的值來開啟
    • 通過SHOW VARIABLES LIKE '%slow_query_log%';檢視 mysql 的慢查詢日誌是否開啟
mysql> SHOW VARIABLES LIKE '%slow_query_log%';
+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | OFF                           |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
+---------------------+-------------------------------+
2 rows in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 如何開啟開啟慢查詢日誌：
    • set global slow_query_log = 1;開啟慢查詢日誌
    • 使用set global slow_query_log=1開啟了慢查詢日誌只對當前資料庫生效，如果MySQL重啟後則會失效。
mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.07 sec)

mysql> SHOW VARIABLES LIKE ‘%slow_query_log%’;
±--------------------±------------------------------+
| Variable_name | Value |
±--------------------±------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/Heygo-slow.log |
±--------------------±------------------------------+
2 rows in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

  • 如果要永久生效，就必須修改配置檔案my.cnf（其它系統變數也是如此）

    • 修改my.cnf檔案，[mysqld]下增加或修改引數：slow_query_log和slow_query_log_file後，然後重啟MySQL伺服器。
    • 也即將如下兩行配置進my.cnf檔案
    [mysqld]
slow_query_log =1
slow_query_log_file=/var/lib/mysql/Heygo-slow.log
    • 1
    • 2
    • 3
    • 關於慢查詢的引數slow_query_log_file，它指定慢查詢日誌檔案的存放路徑，系統預設會給一個預設的檔案host_name-slow.log（如果沒有指定引數slow_query_log_file的話）

那麼開啟慢查詢日誌後，什麼樣的SQL參會記錄到慢查詢裡面？

  • 這個是由引數long_query_time控制，預設情況下long_query_time的值為10秒，命令：SHOW VARIABLES LIKE 'long_query_time%';檢視慢 SQL 的閾值
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.01 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 可以使用命令修改，也可以在my.cnf引數裡面修改。
  • 假如執行時間正好等於long_query_time的情況，並不會被記錄下來。也就是說，在mysql原始碼裡是判斷大於long_query_time，而非大於等於。

2.3、慢查詢日誌示例

案例講解

  • 檢視慢 SQL 的閾值時間，預設閾值時間為 10s
mysql> SHOW VARIABLES LIKE 'long_query_time%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 設定慢 SQL 的閾值時間，我們將其設定為 3s
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)
  • 1
  • 2
  • 為什麼設定後閾值時間沒變？
    • 需要重新連線或者新開一個回話才能看到修改值。
    • 檢視全域性的 long_query_time 值：show global variables like 'long_query_time';發現已經生效
mysql> set global long_query_time=3;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW VARIABLES LIKE ‘long_query_time%’;
±----------------±----------+
| Variable_name | Value |
±----------------±----------+
| long_query_time | 10.000000 |
±----------------±----------+
1 row in set (0.00 sec)

mysql> show global variables like ‘long_query_time’;
±----------------±---------+
| Variable_name | Value |
±----------------±---------+
| long_query_time | 3.000000 |
±----------------±---------+
1 row in set (0.00 sec)

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

  • 記錄慢 SQL 以供後續分析

    • 懟個 select sleep(4); 超過 3s ，肯定會被記錄到日誌中
    mysql> select sleep(4); 
+----------+
| sleep(4) |
+----------+
|        0 |
+----------+
1 row in set (4.00 sec)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 慢查詢日誌檔案在 /var/lib/mysql/ 下，字尾為 -slow.log
[root@Heygo mysql]# cd /var/lib/mysql/
[root@Heygo mysql]# ls -l
總用量 176156
-rw-rw----. 1 mysql mysql       56 8月   3 19:08 auto.cnf
drwx------. 2 mysql mysql     4096 8月   5 10:36 db01
-rw-rw----. 1 mysql mysql     7289 8月   3 22:38 Heygo.err
-rw-rw----. 1 mysql mysql      371 8月   5 12:58 Heygo-slow.log
-rw-rw----. 1 mysql mysql 79691776 8月   5 10:36 ibdata1
-rw-rw----. 1 mysql mysql 50331648 8月   5 10:36 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 8月   3 19:08 ib_logfile1
drwx------. 2 mysql mysql     4096 8月   3 19:08 mysql
srwxrwxrwx. 1 mysql mysql        0 8月   3 22:38 mysql.sock
drwx------. 2 mysql mysql     4096 8月   3 19:08 performance_schema
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 檢視慢查詢日誌中的內容
[root@Heygo mysql]# cat Heygo-slow.log 
/usr/sbin/mysqld, Version: 5.6.49 (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 200805 12:58:01
# User@Host: root[root] @ localhost []  Id:    11
# Query_time: 4.000424  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1596603481;
select sleep(4);
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

  • 查詢當前系統中有多少條慢查詢記錄：show global status like '%Slow_queries%';

  • mysql> show global status like '%Slow_queries%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    配置版的慢查詢日誌

    在 /etc/my.cnf 檔案的 [mysqld] 節點下配置

    slow_query_log=1；
slow_query_log_file=/var/lib/mysql/Heygo-slow.log 
long_query_time=3；
log_output=FILE
    • 1
    • 2
    • 3
    • 4

    日誌分析命令 mysqldumpslow

    mysqldumpslow是什麼？

    在生產環境中，如果要手工分析日誌，查詢、分析SQL，顯然是個體力活，MySQL提供了日誌分析工具mysqldumpslow。

    檢視 mysqldumpslow的幫助資訊

    [root@Heygo mysql]# 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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    mysqldumpshow 引數解釋

    1. s：是表示按何種方式排序
    2. c：訪問次數
    3. l：鎖定時間
    4. r：返回記錄
    5. t：查詢時間
    6. al：平均鎖定時間
    7. ar：平均返回記錄數
    8. at：平均查詢時間
    9. t：即為返回前面多少條的資料
    10. g：後邊搭配一個正則匹配模式，大小寫不敏感的

    常用引數手冊

    1. 得到返回記錄集最多的10個SQL

      mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log
      • 1

    2. 得到訪問次數最多的10個SQL

      mysqldumpslow -s c- t 10/var/lib/mysql/Heygo-slow.log
      • 1

    3. 得到按照時間排序的前10條裡面含有左連線的查詢語句

      mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/Heygo-slow.log
      • 1

    4. 另外建議在使用這些命令時結合 | 和more使用，否則有可能出現爆屏情況

      mysqldumpslow -s r -t 10 /var/lib/mysql/Heygo-slow.log | more
      • 1

    3、批量資料指令碼

    建立表

    • 建表 SQL
    CREATE TABLE dept
(
    deptno int unsigned primary key auto_increment,
    dname varchar(20) not null default "",
    loc varchar(8) not null default ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;

    CREATE TABLE emp
    (
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    ename varchar(20) not null default “”,
    job varchar(9) not null default “”,
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
    )ENGINE=INNODB DEFAULT CHARSET=utf8;

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19

    設定引數

    • 建立函式，假如報錯：This function has none of DETERMINISTIC………

    • 由於開啟過慢查詢日誌，因為我們開啟了bin-log，我們就必須為我們的function指定一個引數。

      • log_bin_trust_function_creators = OFF ，預設必須為 function 傳遞一個引數
      mysql> show variables like 'log_bin_trust_function_creators'; 
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)
      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 通過 set global log_bin_trust_function_creators=1;我們可以不用為 function 傳參
      mysql> set global log_bin_trust_function_creators=1; 
Query OK, 0 rows affected (0.00 sec)

    mysql> show variables like ‘log_bin_trust_function_creators’;
    ±--------------------------------±------+
    | Variable_name | Value |
    ±--------------------------------±------+
    | log_bin_trust_function_creators | ON |
    ±--------------------------------±------+
    1 row in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

  • 這樣新增了引數以後，如果mysqld重啟，上述引數又會消失，永久方法在配置檔案中修改‘

    • windows下：my.ini --> [mysqld] 節點下加上 log_bin_trust_function_creators=1
    • linux下：/etc/my.cnf --> [mysqld] 節點下加上 log_bin_trust_function_creators=1

  • 建立函式，保證每條資料都不同

    • 隨機產生字串的函式
    delimiter $$ # 兩個 $$ 表示結束
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    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 $$
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 隨機產生部門編號的函式
    delimiter $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    建立儲存過程

    • 建立往emp表中插入資料的儲存過程
    delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
        until i=max_num
        end repeat;
    commit;
end $$
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 建立往dept表中插入資料的儲存過程
    delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
        until i=max_num
        end repeat;
    commit;
end $$
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12

    呼叫儲存過程

    • 向 dept 表中插入 10 條記錄
    DELIMITER ;
CALL insert_dept(100, 10);
    • 1
    • 2
    mysql> select * from dept;
+--------+---------+--------+
| deptno | dname   | loc    |
+--------+---------+--------+
|    101 | aowswej | syrlhb |
|    102 | uvneag  | pup    |
|    103 | lps     | iudgy  |
|    104 | jipvsk  | ihytx  |
|    105 | hrpzhiv | vjb    |
|    106 | phngy   | yf     |
|    107 | uhgd    | lgst   |
|    108 | ynyl    | iio    |
|    109 | daqbgsh | mp     |
|    110 | yfbrju  | vuhsf  |
+--------+---------+--------+
10 rows in set (0.00 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 向 emp 表中插入 50w 條記錄
    DELIMITER ;
CALL insert_emp(100001, 500000);
    • 1
    • 2
    mysql> select * from emp limit 20;
+----+--------+-------+----------+-----+------------+---------+--------+--------+
| id | empno  | ename | job      | mgr | hiredate   | sal     | comm   | deptno |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
|  1 | 100002 | ipbmd | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
|  2 | 100003 | bfvt  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    107 |
|  3 | 100004 |       | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    109 |
|  4 | 100005 | cptas | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
|  5 | 100006 | ftn   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    108 |
|  6 | 100007 | gzh   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
|  7 | 100008 | rji   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
|  8 | 100009 |       | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    106 |
|  9 | 100010 | tms   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
| 10 | 100011 | utxe  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
| 11 | 100012 | vbis  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    104 |
| 12 | 100013 | qgfv  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    104 |
| 13 | 100014 | wrvb  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    105 |
| 14 | 100015 | dyks  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    109 |
| 15 | 100016 | hpcs  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    101 |
| 16 | 100017 | fxb   | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    108 |
| 17 | 100018 | vqxq  | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
| 18 | 100019 | rq    | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    102 |
| 19 | 100020 | l     | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    106 |
| 20 | 100021 | lk    | salesman |   1 | 2020-08-05 | 2000.00 | 400.00 |    100 |
+----+--------+-------+----------+-----+------------+---------+--------+--------+
20 rows in set (0.00 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26

    4、Show Profile

    Show Profile 是什麼？

    1. 是mysql提供可以用來分析當前會話中語句執行的資源消耗情況。可以用於SQL的調優測量
    2. 官網：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
    3. 預設情況下，引數處於關閉狀態，並儲存最近15次的執行結果

    分析步驟

    檢視是當前的SQL版本是否支援Show Profile

    • show variables like ‘profiling%’; 檢視 Show Profile 是否開啟
    mysql> show variables like 'profiling%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+
2 rows in set (0.01 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    開啟功能 Show Profile ，預設是關閉，使用前需要開啟

    • set profiling=on; 開啟 Show Profile
    mysql> set profiling=on; 
Query OK, 0 rows affected, 1 warning (0.00 sec)

    mysql> show variables like ‘profiling%’;
    ±-----------------------±------+
    | Variable_name | Value |
    ±-----------------------±------+
    | profiling | ON |
    | profiling_history_size | 15 |
    ±-----------------------±------+
    2 rows in set (0.00 sec)

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11

    執行SQL

    • 正常 SQL
    select * from tbl_emp;
select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
    • 1
    • 2
    • 3
    • 慢 SQL
    select * from emp group by id%10 limit 150000;
select * from emp group by id%10 limit 150000;
select * from emp group by id%10 order by 5;
    • 1
    • 2
    • 3

    檢視結果

    • 通過 show profiles; 指令檢視結果
    mysql> show profiles;
+----------+------------+----------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                |
+----------+------------+----------------------------------------------------------------------+
|        1 | 0.00052700 | show variables like 'profiling%'                                     |
|        2 | 0.00030300 | select * from tbl_emp                                                |
|        3 | 0.00010650 | select * from tbl_emp e inner join tbl_dept d on e.'deptId' = d.'id' |
|        4 | 0.00031625 | select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id     |
|        5 | 0.00042100 | select * from tbl_emp e left join tbl_dept d on e.deptId = d.id      |
|        6 | 0.38621875 | select * from emp group by id%20 limit 150000                        |
|        7 | 0.00014900 | select * from emp group by id%20 order by 150000                     |
|        8 | 0.38649000 | select * from emp group by id%20 order by 5                          |
|        9 | 0.06782700 | select COUNT(*) from emp                                             |
|       10 | 0.35434400 | select * from emp group by id%10 limit 150000                        |
+----------+------------+----------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16

    診斷SQL

    • show profile cpu, block io for query SQL編號; 檢視 SQL 語句執行的具體流程以及每個步驟花費的時間
    mysql> show profile cpu, block io for query 2;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000055 | 0.000000 |   0.000000 |            0 |             0 |
| checking permissions | 0.000007 | 0.000000 |   0.000000 |            0 |             0 |
| Opening tables       | 0.000011 | 0.000000 |   0.000000 |            0 |             0 |
| init                 | 0.000024 | 0.000000 |   0.000000 |            0 |             0 |
| System lock          | 0.000046 | 0.000000 |   0.000000 |            0 |             0 |
| optimizing           | 0.000018 | 0.000000 |   0.000000 |            0 |             0 |
| statistics           | 0.000008 | 0.000000 |   0.000000 |            0 |             0 |
| preparing            | 0.000019 | 0.000000 |   0.000000 |            0 |             0 |
| executing            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| Sending data         | 0.000089 | 0.000000 |   0.000000 |            0 |             0 |
| end                  | 0.000004 | 0.000000 |   0.000000 |            0 |             0 |
| query end            | 0.000003 | 0.000000 |   0.000000 |            0 |             0 |
| closing tables       | 0.000005 | 0.000000 |   0.000000 |            0 |             0 |
| freeing items        | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
| cleaning up          | 0.000006 | 0.000000 |   0.000000 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 引數備註：
    1. ALL：顯示所有的開銷資訊
    2. BLOCK IO：顯示塊IO相關開銷
    3. CONTEXT SWITCHES：上下文切換相關開銷
    4. CPU：顯示CPU相關開銷資訊
    5. IPC：顯示傳送和接收相關開銷資訊
    6. MEMORY：顯示記憶體相關開銷資訊
    7. PAGE FAULTS：顯示頁面錯誤相關開銷資訊
    8. SOURCE：顯示和Source_function，Source_file，Source_line相關的開銷資訊
    9. SWAPS：顯示交換次數相關開銷的資訊

    日常開發需要注意的結論

    1. converting HEAP to MyISAM：查詢結果太大，記憶體都不夠用了往磁碟上搬了。
    2. Creating tmp table：建立臨時表，mysql 先將拷貝資料到臨時表，然後用完再將臨時表刪除
    3. Copying to tmp table on disk：把記憶體中臨時表複製到磁碟，危險！！！
    4. locked：鎖表

    舉例

    奇了怪了。。。老師的慢 SQL 我怎麼復現不了，下面是老師的例子

    image-20200805143307302

    5、全域性查詢日誌

    永遠不要在生產環境開啟這個功能。

    配置啟用全域性查詢日誌

    • 在mysql的my.cnf中，設定如下：
    # 開啟
general_log=1

    記錄日誌檔案的路徑

    general_log_file=/path/logfile

    輸出格式

    log_output=FILE

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

    編碼啟用全域性查詢日誌

    • 執行如下指令開啟全域性查詢日誌
    set global general_log=1;
set global log_output='TABLE';
    • 1
    • 2
    • 此後，你所執行的sql語句，將會記錄到mysql庫裡的general_log表，可以用下面的命令檢視
    select * from mysql.general_log;
    • 1
    mysql> select * from mysql.general_log;
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| event_time          | user_host                 | thread_id | server_id | command_type | argument                                      |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
| 2020-08-05 14:41:07 | root[root] @ localhost [] |        14 |         0 | Query        | select * from emp group by id%10 limit 150000 |
| 2020-08-05 14:41:12 | root[root] @ localhost [] |        14 |         0 | Query        | select COUNT(*) from emp                      |
| 2020-08-05 14:41:30 | root[root] @ localhost [] |        14 |         0 | Query        | select * from mysql.general_log               |
+---------------------+---------------------------+-----------+-----------+--------------+-----------------------------------------------+
3 rows in set (0.00 sec)
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

相關文章