定位慢 SQL

定位慢 SQL 有如下兩種解決方案：

• 查看慢查詢?nèi)罩敬_定已經(jīng)執(zhí)行完的慢查詢
• show processlist 查看正在執(zhí)行的慢查詢

我們一起來了解下這兩種方法的使用場景和使用技巧测秸！

1.通過慢查詢?nèi)罩?br> 如果需要定位到慢查詢楷兽，一般的方法是通過慢查詢?nèi)罩緛聿樵兊模琈ySQL 的慢查詢?nèi)罩居脕碛涗浽?MySQL 中響應(yīng)時間超過參數(shù) long_query_time（單位秒测蹲，默認(rèn)值 10）設(shè)置的值并且掃描記錄數(shù)不小于 min_examined_row_limit（默認(rèn)值 0）的語句础淤，能夠幫我們找到執(zhí)行完的慢查詢崭放，方便我們對這些 SQL 進(jìn)行優(yōu)化。

默認(rèn)情況下鸽凶，慢查詢?nèi)罩局胁粫涗浌芾碚Z句币砂，可通過設(shè)置 log_slow_admin_statements = on 讓管理語句中的慢查詢也會記錄到慢查詢?nèi)罩局小?br> 默認(rèn)情況下，也不會記錄查詢時間不超過 long_query_time 但是不使用索引的語句玻侥，可通過配置 log_queries_not_using_indexes = on 讓不使用索引的 SQL 都被記錄到慢查詢?nèi)罩局校词共樵儠r間沒超過 long_query_time 配置的值）道伟。

使用慢查詢?nèi)罩荆话惴譃樗牟剑洪_啟慢查詢?nèi)罩臼鼓搿⒃O(shè)置慢查詢閥值蜜徽、確定慢查詢?nèi)罩韭窂健⒋_定慢查詢?nèi)罩镜奈募?

• 首先開啟慢查詢?nèi)罩酒币。蓞?shù) slow_query_log 決定是否開啟拘鞋，在 MySQL 命令行下輸入下面的命令：

mysql> set global slow_query_log = on;

默認(rèn)環(huán)境下，慢查詢?nèi)罩臼顷P(guān)閉的矢门。

• 設(shè)置慢查詢時間閥值

mysql> set global long_query_time = 1;

MySQL 中 long_query_time 的值如何確定呢盆色？
線上業(yè)務(wù)一般建議把 long_query_time 設(shè)置為 1 秒灰蛙，如果某個業(yè)務(wù)的 MySQL 要求比較高的 QPS，可設(shè)置慢查詢?yōu)?0.1 秒隔躲。發(fā)現(xiàn)慢查詢及時優(yōu)化或者提醒開發(fā)改寫摩梧。
一般測試環(huán)境建議 long_query_time 設(shè)置的閥值比生產(chǎn)環(huán)境的小耸采，比如生產(chǎn)環(huán)境是 1 秒掂林，則測試環(huán)境建議配置成 0.5 秒婶溯。便于在測試環(huán)境及時發(fā)現(xiàn)一些效率低的 SQL奶是。
甚至某些重要業(yè)務(wù)測試環(huán)境 long_query_time 可以設(shè)置為 0稀轨，以便記錄所有語句履肃。并留意慢查詢?nèi)罩镜妮敵鲎暮В暇€前的功能測試完成后突委，分析慢查詢?nèi)罩久款愓Z句的輸出，重點(diǎn)關(guān)注 Rows_examined（語句執(zhí)行期間從存儲引擎讀取的行數(shù)），提前優(yōu)化术陶。

• 確定慢查詢?nèi)罩韭窂?/li>

慢查詢?nèi)罩镜穆窂侥J(rèn)是 MySQL 的數(shù)據(jù)目錄

mysql> show global variables like "datadir";

• 確定慢查詢?nèi)罩镜奈募?/li>

show global variables like "slow_query_log_file";

根據(jù)上面的查詢結(jié)果獲取慢日志文件已經(jīng)執(zhí)行完的慢查詢：

[root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log

Time: 2019-05-21T09:15:06.255554+08:00

User@Host: root[root] @ localhost []  Id: 8591152

Query_time: 10.000260  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0

SET timestamp=1558401306;
select sleep(10);

這里對上方的執(zhí)行結(jié)果詳細(xì)描述一下：
tail -n5：只查看慢查詢文件的最后 5 行
Time：慢查詢發(fā)生的時間
User@Host：客戶端用戶和 IP
Query_time：查詢時間
Lock_time：等待表鎖的時間
Rows_sent：語句返回的行數(shù)
Rows_examined：語句執(zhí)行期間從存儲引擎讀取的行數(shù)

2.通過 show processlist

有時慢查詢正在執(zhí)行，已經(jīng)導(dǎo)致數(shù)據(jù)庫負(fù)載偏高了腥椒，而由于慢查詢還沒執(zhí)行完，因此慢查詢?nèi)罩具€看不到任何語句候衍。此時可以使用 show processlist 命令判斷正在執(zhí)行的慢查詢寞酿。show processlist 顯示哪些線程正在運(yùn)行。如果有 PROCESS 權(quán)限脱柱，則可以看到所有線程伐弹。否則，只能看到當(dāng)前會話的線程榨为。

如果不使用 FULL 關(guān)鍵字惨好，在 info 字段中只顯示每個語句的前 100 個字符，如果想看語句的全部內(nèi)容可以使用 full 修飾（show full processlist）随闺。

mysql> show processlist\G`

`*************************** 10. row ***************************`

   `Id: 7651833`

   `User: one`

   `Host: 192.168.1.251:52154`

   `db: ops`

   `Command: Query`

   `Time: 3`

  `State: User sleep`

  `Info: select sleep(10)`

`......`

`10 rows in set (0.00 sec)`

這里對上面結(jié)果解釋一下：

    Time：表示執(zhí)行時間
    Info：表示 SQL 語句

我們這里可以通過它的執(zhí)行時間（Time）來判斷是否是慢 SQL日川。

使用 explain 分析慢查詢

分析 SQL 執(zhí)行效率是優(yōu)化 SQL 的重要手段，通過上面講的兩種方法矩乐，定位到慢查詢語句后龄句，我們可以通過 explain、show profile 和 trace 等診斷工具來分析慢查詢散罕。

Explain 可以獲取 MySQL 中 SQL 語句的執(zhí)行計劃分歇，比如語句是否使用了關(guān)聯(lián)查詢、是否使用了索引欧漱、掃描行數(shù)等职抡。可以幫我們選擇更好地索引和寫出更優(yōu)的 SQL 误甚。使用方法：在查詢語句前面加上 explain 運(yùn)行就可以了缚甩。

這也是分析 SQL 時最常用的谱净，也是作者最推薦的一種分析慢查詢的方式。下面我們來看下示例～～

為了便于理解擅威，先創(chuàng)建兩張測試表（方便第 1壕探、2 節(jié)實(shí)驗(yàn)使用），建表及數(shù)據(jù)寫入語句如下：

CREATE DATABASE test;   /* 創(chuàng)建測試使用的database郊丛，名為test */
use test;              /* 使用muke這個database */
drop table if exists t1;        /* 如果表t1存在則刪除表t1 */

CREATE TABLE `t1` (             /* 創(chuàng)建表t1 */
  `id` int(11) NOT NULL auto_increment,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '記錄創(chuàng)建時間',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '記錄更新時間',
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;    

drop procedure if exists insert_t1; /* 如果存在存儲過程insert_t1李请，則刪除 */
delimiter ;;
create procedure insert_t1()        /* 創(chuàng)建存儲過程insert_t1 */
begin
  declare i int;                    /* 聲明變量i */
  set i=1;                          /* 設(shè)置i的初始值為1 */
  while(i<=1000)do                  /* 對滿足i<=1000的值進(jìn)行while循環(huán) */
    insert into t1(a,b) values(i, i); /* 寫入表t1中a、b兩個字段宾袜，值都為i當(dāng)前的值 */
    set i=i+1;                      /* 將i加1 */
  end while;
end;;
delimiter ;                 /* 創(chuàng)建批量寫入1000條數(shù)據(jù)到表t1的存儲過程insert_t1 */
call insert_t1();           /* 運(yùn)行存儲過程insert_t1 */

drop table if exists t2;    /* 如果表t2存在則刪除表t2 */
create table t2 like t1;    /* 創(chuàng)建表t2捻艳，表結(jié)構(gòu)與t1一致 */
insert into t2 select * from t1;   /* 將表t1的數(shù)據(jù)導(dǎo)入到t2 */

下面嘗試使用 explain 分析一條 SQL驾窟，例子如下：

mysql> explain select * from t1 where b=100;

Explain 的結(jié)果各字段解釋如下庆猫，加粗的列為需要重點(diǎn)關(guān)注的項(xiàng)：

• id 查詢編號
• select_type 查詢類型：顯示本行是簡單還是復(fù)雜查詢
• table 涉及到的表
• partitions 匹配的分區(qū)：查詢將匹配記錄所在的分區(qū)。僅當(dāng)使用partition 關(guān)鍵字時才顯示該列绅络。對于非分區(qū)表月培，該值為 NULL。
• type 本次查詢的表連接類型
• possible_keys 可能選擇的索引
• key 實(shí)際選擇的索引
• key_len 被選擇的索引長度：一般用于判斷聯(lián)合索引有多少列被選擇了
• ref 與索引比較的列
• rows 預(yù)計需要掃描的行數(shù)恩急，對 InnoDB 來說杉畜，這個值是估值，并不一定準(zhǔn)確
• filtered 按條件篩選的行的百分比
• Extra 附加信息

下面將列出它們常見的一些值衷恭，可稍微過一遍此叠，不需要完全記下來，對比各種值的區(qū)別随珠。

Type.png

Extra.png

Extra2.png