MySQL底層概述—7.最佳化原則及慢查詢

东阳马生架构發表於2024-12-01
MySql

大綱

1.Explain概述

2.Explain詳解

3.索引最佳化資料準備

4.索引最佳化原則詳解

5.慢查詢設定與測試

6.慢查詢SQL最佳化思路

1.Explain概述

使用Explain關鍵字可以模擬查詢最佳化器來執行SQL查詢語句，從而知道MySQL是如何處理SQL語句的，從而分析出查詢語句和表結構的效能瓶頸。

MySQL查詢過程：

MySQL底層概述—7.最佳化原則及慢查詢
MySQL底層概述—7.最佳化原則及慢查詢

透過Explain可以獲得以下資訊：

一.表的讀取順序

二.資料讀取操作的操作型別

三.哪些索引可以被使用

四.哪些索引真正被使用

五.表的直接引用

六.每張表的有多少行被最佳化器查詢了

Explain使用方式：Explain + SQL語句，透過執行Explain可以獲得SQL語句執行的相關資訊。

EXPLAIN SELECT * FROM L1;
MySQL底層概述—7.最佳化原則及慢查詢

2.Explain詳解

(1)資料準備

(2)ID欄位說明

(3)select_type和table欄位說明

(4)type欄位說明

(5)possible_keys與key說明

(6)key_len欄位說明

(7)ref欄位說明

(8)rows欄位說明

(9)filtered欄位說明

(10)extra欄位說明

(1)資料準備

-- 建立資料庫
CREATE DATABASE test CHARACTER SET 'utf8';


-- 建立表
CREATE TABLE L1(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L2(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L3(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );
CREATE TABLE L4(id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) );


-- 每張表插入3條資料
INSERT INTO L1(title) VALUES('test001'),('test002'),('test003');
INSERT INTO L2(title) VALUES('test004'),('test005'),('test006');
INSERT INTO L3(title) VALUES('test007'),('test008'),('test009');
INSERT INTO L4(title) VALUES('test010'),('test011'),('test012');

(2)ID欄位說明

ID欄位代表SELECT查詢的序列號，它是一組數字，表示的是查詢或操作表的順序。

一.ID相同，執行順序就是由上至下

EXPLAIN SELECT * FROM  L1,L2,L3 WHERE L1.id=L2.id AND L2.id = L3.id;
MySQL底層概述—7.最佳化原則及慢查詢

二.ID不同，如果有子查詢，ID號會遞增

ID值越大優先順序越高，越先被執行。

EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'test007')
);
MySQL底層概述—7.最佳化原則及慢查詢

(3)select_type和table欄位說明

select_type表示查詢型別，主要用於區別普通查詢還是子查詢等，table表示被操作的表。

一.SIMPLE：簡單的SELECT查詢，查詢中不包含子查詢或者UNION

EXPLAIN SELECT * FROM L1 where id = 1;
MySQL底層概述—7.最佳化原則及慢查詢

二.PRIMARY：在有子查詢的情況下，最外層被標記為PRIMARY

三.SUBQUERY：在SELECT或WHERE列表中包含了子查詢

EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title = 'test08')
);
MySQL底層概述—7.最佳化原則及慢查詢

四.UNION：UNION連線的兩個SELECT查詢

在使用UNION時，左邊的表的select_type是DERIVED，右邊的表的select_type是UNION。

五.DERIVED：在FROM列表中包含的子查詢被標記為DERIVED派生表

MySQL會遞迴執行這些被標記為DERIVED的子查詢，然後把結果放到臨時表中。

六.UNION RESULT：UNION的結果

EXPLAIN SELECT * FROM (SELECT * FROM L3 UNION SELECT * FROM L4) a;
MySQL底層概述—7.最佳化原則及慢查詢

(4)type欄位說明

type欄位表示的是連線型別，描述了找到所需資料而使用的掃描方式。

下面給出各種連線型別，按照從最好型別到最差型別進行排序：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

一般來說，需要保證查詢至少達到range級別，最好能達到ref級別，否則就要就行SQL的最佳化調整。

下面介紹type欄位不同值表示的含義：

一.system

表示表中僅有一行資料，這是const連線型別的一個特例，很少出現。

二.const

表示命中主鍵索引(primary key)或唯一索引(unique)，透過主鍵索引或唯一索引一次就找到了資料。因為只匹配一條記錄，所以被連線的部分是一個常量。如果將主鍵放在where條件中，MySQL就能將該查詢轉換為一個常量。這種型別非常快，例如以下查詢：

EXPLAIN SELECT * FROM L1 WHERE id = 3;
  
-- 為L1表的title欄位新增唯一索引
ALTER TABLE L1 ADD UNIQUE(title);
EXPLAIN SELECT * FROM L1 WHERE title = 'test001';
MySQL底層概述—7.最佳化原則及慢查詢

三.eq_ref

表示的是使用了唯一索引。比如連表查詢中，對於前一個表中的每一行，後表只有一行被掃描。除了system和const型別之外，這是最好的連線型別。只有在連表時使用的索引都是主鍵或唯一索引時，才會出現這種型別，例如以下查詢：

EXPLAIN SELECT L1.id,L1.title FROM L1 LEFT JOIN L2 ON L1.id = L2.id;
MySQL底層概述—7.最佳化原則及慢查詢

四.ref

表示使用了普通索引，即非唯一性索引。比如連表時對於前表的每一行，後表可能有多於一行的資料被掃描，例如以下查詢：

-- 為L1表的title欄位新增普通索引
ALTER TABLE L1 ADD INDEX idx_title (title);
EXPLAIN SELECT * FROM L1 INNER JOIN L2 ON L1.title = L2.title;
-- 如果L1表的title欄位沒有唯一索引，只有普通索引，如下查詢也是ref
EXPLAIN SELECT * FROM L1 WHERE title = 'test001';
MySQL底層概述—7.最佳化原則及慢查詢

五.range

表示的是進行了索引上的範圍查詢，檢索了給定範圍的行，比如between、in函式、>都是典型的範圍查詢，例如以下查詢：

EXPLAIN SELECT * FROM L1 WHERE L1.id BETWEEN 1 AND 10;
MySQL底層概述—7.最佳化原則及慢查詢

注意：當in函式中的資料很大時，可能會導致效率下降，最終不走索引。

六.index

當可以使用索引覆蓋，但需要掃描全部索引記錄時，則type為index。

當需要執行全表掃描，且需要對主鍵進行排序時，則type也為index。

所以如果type的值等於index，那麼就需要進行最佳化了。因為出現index表示沒有透過索引進行過濾，需要掃描索引的全部資料。index會遍歷掃描索引樹，比ALL快一些。如果索引檔案過大，index的速度還是會很慢的。

總結：

當遍歷二級索引不需要回表或者主鍵排序全表掃描時，type就為index。

注意：

使用索引進行排序分組時，可能會出現這種type值為index的情況。比如進行統計操作時，會出現type值為index的情況。

EXPLAIN SELECT * FROM L2 GROUP BY id ORDER BY id;
-- 該count查詢需要透過掃描索引上的全部資料來計數
EXPLAIN SELECT count(*) FROM L2;
MySQL底層概述—7.最佳化原則及慢查詢

七.ALL

表示沒有使用到任何索引，連表查詢時對於前表的每一行，後表都要被全表掃描。

EXPLAIN SELECT * FROM L3 inner join L4 on L3.title = L4.title;
MySQL底層概述—7.最佳化原則及慢查詢

總結各類type型別的特點：

system：不進行磁碟IO，查詢系統表，僅僅返回一條資料。

const：查詢主鍵索引，最多返回1條或0條資料，屬於精確查詢。

eq_ref：查詢唯一性索引，返回資料最多一條，屬於精確查詢。

ref：查詢非唯一性索引，返回匹配的多條資料，屬於精確查詢。

range：查詢索引中給定範圍的行，屬於範圍查詢(>、<、in、between)。

index：使用了索引但掃描全部了，比all快，因索引檔案比資料檔案小。

index：比如遍歷二級索引不需要回表或者主鍵排序全表掃描。

all：不使用任何索引，直接進行全表掃描。

(5)possible_keys與key說明

一.possible_keys

表示可能用於查詢的表上的索引。查詢涉及到的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用。

二.key

表示實際使用的索引。若為null，則表示沒有使用到索引或索引失效。查詢中若使用了覆蓋索引，則該索引僅出現在key列表中。

情形一：理論和實際都用到了索引

EXPLAIN SELECT * FROM L1 WHERE id = 1;
MySQL底層概述—7.最佳化原則及慢查詢

情形二：理論上沒有使用索引，但實際上使用了

EXPLAIN SELECT L3.id FROM L3;
MySQL底層概述—7.最佳化原則及慢查詢

情形三：理論和實際上都沒有使用索引

EXPLAIN SELECT * FROM L3 WHERE title = 'test007';
MySQL底層概述—7.最佳化原則及慢查詢

(6)key_len欄位說明

表示索引中使用的位元組數，透過該列可以計算查詢中使用索引的長度。key_len欄位能夠幫我們檢查是否充分利用了索引，ken_len越長越好，說明索引使用的越充分。

一.建立表

CREATE TABLE L5(
    a INT PRIMARY KEY,
    b INT NOT NULL,
    c INT DEFAULT NULL,
    d CHAR(10) NOT NULL
);

二.使用EXPLAIN進行測試

-- 下面的查詢只用到了主鍵a的索引
EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;

觀察key_len的值，用到了主鍵索引，是int型別的，所以key_len是4位元組。

MySQL底層概述—7.最佳化原則及慢查詢

三.為b欄位新增索引，進行測試

ALTER TABLE L5 ADD INDEX idx_b(b);
-- 執行SQL，這次將b欄位也作為條件
EXPLAIN SELECT * FROM L5 WHERE a > 1 AND b = 1;

ken_len還是4。

MySQL底層概述—7.最佳化原則及慢查詢

四.為c、d欄位新增聯合索引，然後進行測試

ALTER TABLE L5 ADD INDEX idx_c_b(c,d);
EXPLAIN SELECT * FROM L5 WHERE c = 1 AND d = 'A';
MySQL底層概述—7.最佳化原則及慢查詢

c欄位是int型別4個位元組，d欄位char(10)代表的是10個字元30個位元組。因為資料庫的字符集是utf8，一個字元3個位元組。d欄位是char(10)代表的是10個字元相當30個位元組。多出的一個位元組用來表示是聯合索引。

下面這個例子雖然使用了聯合索引，但沒充分利用索引，還有最佳化空間。因為可以根據ken_len的長度推測出該聯合索引只使用一部分。

EXPLAIN SELECT * FROM L5 WHERE c = 1;
MySQL底層概述—7.最佳化原則及慢查詢

(7)ref欄位說明

表示的是顯示索引的哪一列被使用了，如果可能的話，最好是一個常數。表示的是哪些列或常量被用於查詢索引列上的值。

如下的"L1.id=1"中，由於1是常量，所以ref = const，此時的ref = const表示著查詢過程中使用到了常量。

EXPLAIN SELECT * FROM L1 WHERE L1.id = 1;
MySQL底層概述—7.最佳化原則及慢查詢

(8)rows欄位說明

表示MySQL為了找到所需的記錄，一共訪問了多少行(預估的)。L3中的title沒有新增索引，所以L3中有3條記錄，就需要訪問3條記錄。

EXPLAIN SELECT * FROM L3,L4 WHERE L3.id = L4.id AND L3.title LIKE 'test007';
MySQL底層概述—7.最佳化原則及慢查詢

需要注意的是rows只是一個估算值，並不準確。所以rows行數過大的問題並不值得過多考慮，主要分析的還是索引是否使用正確了。

(9)filtered欄位說明

它指返回結果的行佔需要讀到的行(rows列的值)的百分比。

(10)extra欄位說明

Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細資訊。

一.準備資料

CREATE TABLE users (
    uid INT PRIMARY KEY AUTO_INCREMENT,
    uname VARCHAR(20),
    age INT(11)
);
INSERT INTO users VALUES(NULL, 'lisa', 10);
INSERT INTO users VALUES(NULL, 'lisa', 10);
INSERT INTO users VALUES(NULL, 'rose', 11);
INSERT INTO users VALUES(NULL, 'jack', 12);
INSERT INTO users VALUES(NULL, 'sam', 13);

二.Using filesort(需要進行檔案排序)

執行結果Extra為Using filesort，說明得到所需結果集，需要對所有記錄進行檔案排序。表示執行的SQL語句效能極差，需要進行最佳化。

下面就是在一個沒有建立索引的列上進行order by，此時會觸發filesort。最佳化方案是，在order by的列上新增索引，避免每次查詢都全量排序。

EXPLAIN SELECT * FROM users ORDER BY age;
MySQL底層概述—7.最佳化原則及慢查詢

三.Using temporary

表示使用了臨時表來儲存結果集，常見於排序和分組查詢。

EXPLAIN SELECT COUNT(*),uname FROM users GROUP BY uname;
MySQL底層概述—7.最佳化原則及慢查詢

四.Using where

表示使用了全表掃描或者在查詢時使用索引的情況下，還有查詢條件不在索引欄位中需要回表。

注意一：返回所有記錄的SQL，不使用where條件過濾資料，大機率不符合預期，這類SQL往往需要進行最佳化。

注意二：使用了where條件的SQL，並不代表不需要最佳化，往往需要配合explain結果中的type(連線型別)來綜合判斷。例如下面查詢的age未設定索引，所以返回的type為ALL，仍有最佳化空間，可建立索引最佳化查詢。

EXPLAIN SELECT * FROM users WHERE age = 10;
MySQL底層概述—7.最佳化原則及慢查詢

五.Using index

表示直接訪問索引就能獲取所需資料(覆蓋索引)，不需要回表。

-- 為uname建立索引
ALTER TABLE users ADD INDEX idx_uname(uname);
EXPLAIN SELECT uid,uname FROM users WHERE uname='lisa';
MySQL底層概述—7.最佳化原則及慢查詢

六.Using join buffer

表示使用了連線快取，還會顯示join連線查詢時使用的演算法。

EXPLAIN SELECT * FROM users u1 LEFT JOIN 
    (SELECT * FROM users WHERE age = 1) u2 ON u1.age = u2.age;
MySQL底層概述—7.最佳化原則及慢查詢

Using join buffer(Block Nested Loop)說明，需要進行巢狀迴圈計算。這裡每個表都有五條記錄，內外表查詢的type都為ALL。兩個表透過欄位age進行關聯，且age欄位未建立索引。

七.Using index condition

表示的是使用了索引，但是隻使用了索引的一部分。一般發生在使用聯合索引時，需要回表查詢。

EXPLAIN SELECT * FROM L5 WHERE c > 10 AND d = '';
MySQL底層概述—7.最佳化原則及慢查詢

八.Extra主要指標的含義總結

using index：查詢時使用了覆蓋索引的時候就會出現，不需要回表。

using where：查詢時使用索引的情況下需要回表或全表掃描。

using index condition：查詢時使用了索引但只用一部分索引需要回表。

Using filesort：在一個沒有建立索引的列上order by，發生檔案排序。

Using temporary：使用了臨時表儲存結果集，常見於排序和分組查詢。

當遍歷二級索引不需要回表或者主鍵排序全表掃描時，type就為index。

查詢非唯一性索引，返回匹配的多條資料，type就為ref。

查詢唯一性索引，返回匹配的資料最多一條，type就為eq_ref。

查詢索引中給定範圍的行，type就為range。

3.索引最佳化資料準備

(1)建立資料庫、表，插入資料

create database idx_optimize character set 'utf8';
CREATE TABLE users(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(20) NOT NULL COMMENT '姓名',
    user_age INT NOT NULL DEFAULT 0 COMMENT '年齡',
    user_level VARCHAR(20) NOT NULL COMMENT '使用者等級',
    reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '註冊時間'
);
INSERT INTO users(user_name,user_age,user_level,reg_time)
VALUES('tom',17,'A',NOW()),('jack',18,'B',NOW()),('lucy',18,'C',NOW());

(2)建立聯合索引

ALTER TABLE users ADD INDEX idx_nal (user_name,user_age,user_level) USING BTREE;

4.索引最佳化原則詳解

(1)最左側列匹配和最左字首匹配法則

(2)不要在索引列上做任何計算

(3)範圍之後全失效

(4)避免使用is null、is not null、!= 、or

(5)like以%開頭會使索引失效

(6)索引最佳化原則總結

(1)最左側列匹配和最左字首匹配法則

如果建立的是聯合索引，就要遵循該法則。where後面的條件需從索引的最左側列開始，且不能跳過索引中的列。如果where只匹配一個列，那麼該列在索引最左側，且只匹配字首欄位。

一.最左側列匹配和最左字首匹配的場景

場景1：按照索引欄位順序使用，三個欄位都使用了索引，沒有問題。

EXPLAIN SELECT * FROM users 
WHERE user_name = 'tom' AND user_age = 17 AND user_level = 'A';
MySQL底層概述—7.最佳化原則及慢查詢

場景2：直接跳過user_name使用索引欄位，索引無效，未使用到索引。

EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';
MySQL底層概述—7.最佳化原則及慢查詢

場景3: 不按照建立聯合索引的順序，使用索引。

EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_name = 'tom' AND user_level = 'A';
MySQL底層概述—7.最佳化原則及慢查詢

where後面查詢條件順序是user_age、user_level、user_name，這與建立的索引順序user_name、user_age、user_level不一致。為什麼還是使用了索引，原因是MySQL底層最佳化器對其進行了最佳化。

場景4：只要包含最左側欄位，索引就可以生效

但從key_len可知只是用到索引的一部分。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom';
MySQL底層概述—7.最佳化原則及慢查詢

二.最左側列匹配和最左字首匹配的原理

InnoDB建立聯合索引的規則是：

首先會對聯合索引最左邊的欄位進行排序，例子中是user_name。在第一個欄位的基礎之上再對第二個欄位進行排序，例子中是user_age。所以最佳左字首原則其實是和B+樹的結構有關係，最左欄位肯定是有序的，第二個欄位則是無序的。

聯合索引的排序方式是：

先按第一個欄位進行排序，如果第一個欄位相等再根據第二個欄位排序。所以如果直接使用第二個欄位user_age通常是使用不到索引的。

(2)不要在索引列上做任何計算

不要在索引列上做任何操作，否則會導致索引失效，從而轉向全表掃描。比如計算、使用函式、自動或手動進行型別轉換(字串不加雙引號)。

一.插入資料

INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES('11223344',22,'D',NOW());

場景1：使用系統函式left()函式，對user_name進行操作

EXPLAIN SELECT * FROM users WHERE LEFT(user_name, 6) = '112233';
MySQL底層概述—7.最佳化原則及慢查詢

場景2：字串不加單引號(隱式型別轉換)

對於varchar型別的欄位，如果查詢時不加單引號就會進行隱式轉換，導致索引失效轉向全表掃描。

EXPLAIN SELECT * FROM users WHERE user_name = 11223344;
MySQL底層概述—7.最佳化原則及慢查詢

(3)範圍之後全失效

where條件中如果有範圍條件，並且範圍條件之後還有其他過濾條件，那麼範圍條件之後的列就都將會索引失效。

場景1：條件單獨使用user_name時，type=ref、key_len=62。

-- 條件只有一個 user_name
EXPLAIN SELECT * FROM users WHERE user_name = 'tom';
MySQL底層概述—7.最佳化原則及慢查詢

場景2：條件增加一個user_age(使用常量等值)，type= ref、key_len = 66。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17;
MySQL底層概述—7.最佳化原則及慢查詢

場景3：使用全值匹配，type = ref、key_len = 128，索引都利用上了。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17 AND user_level = 'A';
MySQL底層概述—7.最佳化原則及慢查詢

場景4：使用範圍條件時，avg > 17、type = range、key_len = 66。與場景3比較，可發現user_level索引沒用上。

EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age > 17 AND user_level = 'A';
MySQL底層概述—7.最佳化原則及慢查詢

(4)避免使用is null、is not null、!= 、or

一.使用is null會使索引失效

EXPLAIN SELECT * FROM users WHERE user_name IS NULL;
MySQL底層概述—7.最佳化原則及慢查詢

Impossible Where：表示where條件不成立，不能返回任何行。

二.使用is not null會使索引失效

EXPLAIN SELECT * FROM users WHERE user_name IS NOT NULL;
MySQL底層概述—7.最佳化原則及慢查詢

三.使用!=和or會使索引失效

EXPLAIN SELECT * FROM users WHERE user_name != 'tom';
EXPLAIN SELECT * FROM users WHERE user_name = 'tom' or user_name = 'jack';
MySQL底層概述—7.最佳化原則及慢查詢

(5)like以%開頭會使索引失效

一.like查詢中%出現在左邊則索引失效，%出現在右邊索引未失效

場景1：兩邊都有%或者%在左邊，索引都會失效

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';
EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';
MySQL底層概述—7.最佳化原則及慢查詢

場景2：%在右邊，索引生效

EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';
MySQL底層概述—7.最佳化原則及慢查詢

二.解決%出現在左邊索引失效的方法——使用覆蓋索引

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';
EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';
MySQL底層概述—7.最佳化原則及慢查詢

對比場景1可以知道：透過使用覆蓋索引type = index，並且extra = Using index，從原來的全表掃描變成了全索引掃描，也就是索引的全表掃描。

三.like失效的原理

原理一：%號在右

由於B+樹的索引順序，是按照首字母的大小進行排序，而%號在右時的匹配又會匹配首字母，所以能在B+樹上進行有序的查詢。也就是查詢出首字母符合要求的資料，所以%號在右可以用到索引。

原理二：%號在左是匹配字串尾部的資料

由於尾部的字母是沒有順序的，所以不能按索引順序查詢，用不到索引。

原理三：兩個%%號

這個是查詢任意位置的字母滿足條件即可。只有首字母是進行索引排序的，其他位置的字母都是相對無序的，所以查詢任意位置的字母是用不上索引的。

(6)索引最佳化原則總結

一.最左側列匹配和最左字首匹配

二.索引列上不計算不轉換

三.範圍之後全失效

四.最好使用覆蓋索引

五.!=、is null、is not null、or會索引失效

六.like百分號加右邊，加左邊導致索引失效的解決方法是使用覆蓋索引

5.慢查詢設定與測試

(1)慢查詢介紹

(2)慢查詢引數

(3)慢查詢配置方式

(4)慢查詢測試

(5)慢日誌內容

(1)慢查詢介紹

MySQL的慢查詢全名是慢查詢日誌，是MySQL提供的一種日誌記錄。慢查詢日誌會記錄在MySQL中響應時間超過閾值的語句。MySQL資料庫預設不啟動慢查詢日誌，需要手動來設定這個引數。

如果不是調優需要的話，一般不建議啟動該引數。因為開啟慢查詢日誌會或多或少帶來一定的效能影響，慢查詢日誌支援將日誌記錄寫入檔案和資料庫表。

(2)慢查詢引數

執行下面的語句

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+


mysql> show variables like '%long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

MySQL慢查詢的相關引數解釋：

一.slow_query_log：是否開啟慢查詢日誌。

二.slow-query-log-file：慢查詢日誌儲存路徑。

三.long_query_time：慢查詢閾值，查詢時間多於設定閾值則記錄日誌。

(3)慢查詢配置方式

一.預設情況下slow_query_log的值為OFF，表示慢查詢日誌是禁用的

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+

二.可以透過設定slow_query_log的值來開啟

mysql> set global slow_query_log=1;

三.set global slow_query_log=1開啟慢查詢日誌當前生效重啟失效

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

-- 編輯配置
vim /etc/my.cnf


-- 新增如下內容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/ruyuan-slow.log


-- 重啟MySQL
service mysqld restart


mysql> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |
+---------------------+--------------------------------+

四. 開啟了慢查詢日誌後，什麼樣的SQL才會記錄到慢查詢日誌裡

這個由引數long_query_time控制，預設long_query_time的值為10秒。

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+


mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)


mysql>  show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

五.修改變數long_query_time，但查詢值還是10

執行命令set global long_query_time=1後，需要重新連線或者開啟新開會話才能看到修改值。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

六.log_output引數是指定日誌的儲存方式

log_output=FILE表示將日誌存入檔案，預設值是FILE。log_output=TABLE表示將日誌存入資料庫，這樣日誌資訊就會被寫入到mysql.slow_log表中。

MySQL資料庫可以同時支援兩種日誌儲存方式，配置的時候以逗號隔開即可，如：log_output='FILE,TABLE'。

日誌記錄到系統的專用日誌表中，要比記錄到檔案耗費更多的系統資源。因此如果啟用慢查詢日誌+獲得更高系統效能，則建議優先記錄到檔案。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

七.開啟系統變數讓未使用索引的查詢也被記錄到慢查詢日誌中

這個系統變數就是log-queries-not-using-indexes，所以在進行調優時，可以開啟這個選項。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+


mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)


mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

(4)慢查詢測試

一.執行test_index.sql指令碼，監控慢查詢日誌內容

[root@localhost mysql]# tail -f /var/lib/mysql/test-slow.log 
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

二. 執行下面的SQL，執行超時(超過1秒)我們去檢視慢查詢日誌

SELECT * FROM test_index WHERE 
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931'
OR dname = 'name4000' OR dname = 'name6600' ;

(5)慢日誌內容

我們得到慢查詢日誌後，最重要的一步就是去分析這個日誌。先來看慢日誌裡到底記錄了哪些內容，如下是慢日誌裡其中一條記錄，可以看到有時間戳、使用者、查詢時長及具體的SQL等資訊。

Time                 Id Command    Argument
# Time: 2022-02-23 T03:55:15. 336037Z
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 2.375219  Lock_time: 0.000137 Rows_sent: 3  Rows_examined: 5000000
use db4;
SET timestamp=1645588515;
SELECT * FROM test_index WHERE  hobby = '20009961' OR hobby = '10009941' OR hobby = '30009961' OR dname = 'name4001' OR dname = 'name6601';

Time：執行時間；

Users：使用者資訊；

Query_time：查詢時長；

Lock_time：等待鎖時長；

Rows_sent：結果行統計數量；

Rows_examined：掃描的行數；

6.慢查詢SQL最佳化思路

(1)SQL效能下降的原因

(2)慢查詢最佳化思路

(1)SQL效能下降的原因

導致SQL執行效能下降的原因可體現在以下兩方面：

一.等待時間長

鎖表導致查詢一直處於等待狀態。

二.執行時間長

查詢語句沒最佳化、索引失效、關聯查詢太多join、機器及引數沒調優。

(2)慢查詢最佳化思路

一.優先選擇最佳化高併發執行的SQL

因為高併發的SQL出現問題帶來後果更嚴重，比如下面兩種情況：SQL1每小時執行10000次，每次20個IO，最佳化後每次18個IO，每小時節省2萬次IO；SQL2每小時10次，每次20000個IO，每次最佳化減少2000個IO，每小時節省2萬次IO。此時SQL2更難最佳化，SQL1更好最佳化。但是第一種屬於高併發SQL，更急需最佳化，因為成本更低。

二.定位最佳化物件的效能瓶頸

在去最佳化SQL時，選擇最佳化分方向有三個：

方向1：IO，資料訪問消耗了太多時間，檢視是否正確使用索引。

方向2：CPU，資料運算花費了太多時間，資料的運算分組、排序是不是有問題。

方向3：網路頻寬，加大網路頻寬。

三.明確最佳化目標

根據資料庫當前狀態、當前SQL的具體功能，來確定最好情況下消耗的資源和最差情況下消耗的資源。因為最佳化的結果只有一個，即給使用者一個好的體驗。

四.從explain執行計劃入手

只有explain能告訴我們當前SQL的執行狀態。

五.永遠用小的結果集驅動大的結果集

小的資料集驅動大的資料集，減少內層表讀取次數。

//類似於巢狀迴圈
for (int i = 0; i < 5; i++) {
    for (int i = 0; i < 1000; i++) {


    }
}

六.儘可能在索引中完成排序

排序操作用得比較多，所以order by後面的欄位儘量使用上索引。因為索引本來就是排好序的，所以速度很快。沒有索引的話，就需要從表中拿資料，在記憶體中進行排序。如果記憶體空間不夠還會發生臨時檔案落盤操作。

七.只獲取自己需要的列

不要使用select *，因為select * 很可能不使用索引，而且資料量過大。

八.只使用最有效的過濾條件

where後面的條件並非越多越好，應該用最短的路徑訪問到資料。

九.儘可能避免複雜的join和子查詢

每條SQL的JOIN操作建議不要超過三張表。將複雜的SQL，拆分成多個小的SQL，單個表執行，然後對獲取的結果在程式中進行封裝。因為如果join佔用的資源比較多，會導致其他程序等待時間變長。

十.合理設計並利用索引

也就是合理判斷是否需要建立索引，以及合理選擇合適索引。

(3)如何判定是否需要建立索引

一.頻繁作為查詢條件的欄位應該建立索引

二.唯一性太差的欄位不適合單獨建立索引，即使它頻繁作為查詢條件

唯一性太差的欄位主要是指哪些呢？如狀態欄位、型別欄位等。這些欄位中的資料可能總共就是那麼幾個幾十個數值重複使用。當一條Query所返回的資料超過了全表的15%時，就不應該再使用索引掃描來完成這個Query了。

三.更新非常頻繁的欄位不適合建立索引

因為索引中的欄位被更新時，不僅要更新表的資料，還要更新索引資料。

四.不會出現在WHERE子句中的欄位不該建立索引

(4)如何選擇合適索引

一.單鍵索引，儘量選擇針對當前Query過濾性更好的索引。

二.聯合索引，當前查詢中過濾性最好的欄位在索引欄位順序中排列靠前。

相關文章