1. 慢查詢介紹

常見面試題：如何從一個(gè)大項(xiàng)目中，迅速定位執(zhí)行速度慢的 SQL 語句？

此時(shí)可以使用慢查詢定位執(zhí)行較慢 SQL 語句

慢查詢就是在日志中記錄運(yùn)行比較慢的 SQL 語句，是指所有執(zhí)行超過 long_query_time 參數(shù)設(shè)定的時(shí)間閾值的 SQL 語句查詢

2. 慢查詢狀態(tài)

# 查看是否開啟慢查詢?nèi)罩?ON 1 開啟 OFF 0 關(guān)閉（默認(rèn)是關(guān)閉的）
show variables like 'slow_query_log';
# 開啟慢查詢?nèi)罩?set global slow_query_log = 1;
# 關(guān)閉慢查詢?nèi)罩?set global slow_query_log = 0;

使用下面命令開啟慢查詢時(shí)，當(dāng)重啟 mysql 服務(wù)后，慢查詢會恢復(fù)到默認(rèn)的關(guān)閉狀態(tài)

set global slow_query_log = 1;

慢查詢狀態(tài)想要永久生效，就必須修改配置文件 my.conf，將下面配置項(xiàng)放入配置文件 my.conf 中，重啟 mysql 服務(wù)即可生效

slow_query_log = 1

3. 慢查詢時(shí)間閾值

上面在介紹慢查詢時(shí)，已經(jīng)說明慢查詢?nèi)罩局杏涗浀氖浅鰰r(shí)間閾值的 SQL 語句

這個(gè)時(shí)間閾值可以通過下面這個(gè)命令查看，默認(rèn)是 10 秒

# 查看當(dāng)前慢查詢時(shí)間閾值
show variables like 'long_query_time';
# 臨時(shí)修改慢查詢時(shí)間閾值 (1 秒)
set long_query_time = 1;

在配置文件 my.conf 中添加配置項(xiàng)，使設(shè)置的時(shí)間閾值永久生效

long_query_time = 2

4. 構(gòu)建大表，測試慢查詢

創(chuàng)建一個(gè)數(shù)據(jù)庫，執(zhí)行下面 SQL

CREATE TABLE dept( /*部門表*/
    deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0,  /*編號*/
    dname VARCHAR(20)  NOT NULL  DEFAULT "", /*名稱*/
    loc VARCHAR(13) NOT NULL DEFAULT "" /*地點(diǎn)*/
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
CREATE TABLE emp(
    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,/*入職時(shí)間*/
    sal DECIMAL(7,2)  NOT NULL,/*薪水*/
    comm DECIMAL(7,2) NOT NULL,/*紅利*/
    deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部門編號*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;
CREATE TABLE salgrade (
    grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
    losal DECIMAL(17,2)  NOT NULL,
    hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;
-- 測試數(shù)據(jù)
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);
# 創(chuàng)建存儲過程
delimiter $$
create function rand_string(n INT)
returns varchar(255) #該函數(shù)會返回一個(gè)字符串
begin
#chars_str定義一個(gè)變量 chars_str,類型是 varchar(100),默認(rèn)值’abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ’;
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 $$
create procedure insert_emp2(in start int(10),in max_num int(10))
begin
declare i int default 0;
#set autocommit =0 把a(bǔ)utocommit設(shè)置成0
set autocommit = 0;
repeat
set i = i + 1;
insert into emp values ((start+i) ,rand_string(6),'SALESMAN',0001,curdate(),2000,400,floor(rand()*5+5));
until i = max_num
end repeat;
commit;
end $$
delimiter ;
# 調(diào)用剛剛寫好的函數(shù), 1800000條記錄,從100001號開始
call insert_emp2(100001,4000000);

查詢 emp 表數(shù)據(jù)，執(zhí)行 1.55 秒

mysql> select * from emp;
+--------+--------+----------+-----+------------+---------+--------+--------+
| empno  | ename  | job      | mgr | hiredate   | sal     | comm   | deptno |
+--------+--------+----------+-----+------------+---------+--------+--------+
| 100002 | BzVfMu | SALESMAN |   1 | 2022-05-15 | 2000.00 | 400.00 |      8 |
| 100003 | wcUtjQ | SALESMAN |   1 | 2022-05-15 | 2000.00 | 400.00 |      7 |
| ... | ... | ... |   ... | ... | ... | ... |   ... |
+--------+--------+----------+-----+------------+---------+--------+--------+
4000000 rows in set (1.55 sec)

# 查看慢查詢?nèi)罩疚募娣盼恢?show variables  like 'slow_query_log_file';

設(shè)置 long_query_time = 1，此時(shí)會生成慢查詢?nèi)罩?，文件?nèi)容如下所示

# Time: 2022-05-15T02:31:37.897427Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 1.548641  Lock_time: 0.000156 Rows_sent: 4000000  Rows_examined: 4000000
SET timestamp=1652581897;
select * from emp;