關於 MySQL 慢查詢最佳化的一些思路與知識點

1 慢查詢最佳化思路

當發生慢查詢的時候，最佳化的思路為：

利用慢查詢日誌定位慢查詢 SQL

透過 explain 分析慢查詢 SQL

修改 SQL，儘量讓 SQL 走索引

2 慢查詢日誌

MySQL 提供了一個功能——慢查詢日誌，會記錄查詢時間超過指定時間閾值的 SQL 到日誌中，便於我們定位慢查詢並且最佳化對應的 SQL 語句。

首先檢視 MySQL 中關於慢查詢相關的全域性變數：

mysql> show global variables like ‘%quer%’；+————————————————————+————————————————-+| Variable_name | Value |+————————————————————+————————————————-+| binlog_rows_query_log_events | OFF || ft_query_expansion_limit | 20 || have_query_cache | YES || log_queries_not_using_indexes | OFF || log_throttle_queries_not_using_indexes | 0 |==========================================================================| long_query_time | 10。000000 | 【1】慢查詢的時間閾值==========================================================================| query_alloc_block_size | 8192 || query_cache_limit | 1048576 || query_cache_min_res_unit | 4096 || query_cache_size | 16777216 || query_cache_type | OFF || query_cache_wlock_invalidate | OFF || query_prealloc_size | 8192 |==========================================================================| slow_query_log | OFF | 【2】慢查詢日誌是否開啟| slow_query_log_file | /var/lib/mysql/Linux-slow。log | 【3】慢查詢日誌檔案儲存位置==========================================================================+————————————————————+————————————————-+15 rows in set （0。00 sec）複製程式碼

這裡主要關注三個變數：

long_query_time，慢查詢的時間閾值，單位

秒

，如果一個 SQL 語句的執行時間超過這個值，那麼 MySQL 就認定其為慢查詢

slow_query_log，慢查詢日誌功能是否開啟，預設關閉，開啟後記錄慢查詢

slow_query_log_file，慢查詢日誌檔案的儲存位置

預設慢查詢日誌功能是關閉的，因此我們需要啟動該功能

# 開啟慢查詢日誌mysql> set global slow_query_log=ON；Query OK， 0 rows affected （0。00 sec）# 設定慢查詢時間閾值mysql> set long_query_time=1；Query OK， 0 rows affected （0。00 sec）複製程式碼

這樣子設定後，MySQL 重啟會丟失這些配置，需要在配置檔案中修改才會永久有效。

3 explain

我們可以使用 explain 分析 SQL 語句的執行情況，例如：

mysql> explain select sum（1+2）；複製程式碼

執行結果如下，可以看到有很多欄位

我們主要看看一些重要的欄位：

select_type 表示查詢語句的查詢型別，包括簡單查詢、子查詢等等

table 表示查詢的表，不一定是存在表，可能是本次查詢中得到的臨時表

type 表示檢索型別，使用全表掃描、還是索引掃描等

possible_keys表示可能使用的索引列

keys表示查詢中實際使用的索引列，由查詢最佳化器決定

3。1 select_type 欄位

值

解釋

SIMPLE

簡單查詢，沒有使用 UNION / 子查詢

PRIMARY

包含子查詢/UNION，最外層的查詢被標記為 PRIMAY

UNION

UNION 中的第二個或之後的查詢

DEPENDENT UNION

UNION 中的第二個或之後的查詢取決於外面的查詢

UNION RESULT

UNION 的結果

SUBQUERY

子查詢中的第一個查詢

DEPENDENT SUBQUERY

子查詢中的第一個查詢，取決於外面的查詢

DERIVED

用到派生表的查詢，即 from 子句中的子查詢

MATERIALIZED

物化子查詢

UNCACHEABLE SUBQUERY

結果集無法快取的子查詢，必須重新評估外部查詢的每一行

UNCACHEABLE UNION

UNION 中的第二個或者後面的語句屬於不可快取的子查詢

3。2 type 欄位

對於 InnoDB 儲存引擎，type列通常都是all或者index。

關於 type 欄位的值，其從上到下對應的 SQL 的執行效能逐漸變差。

值

解釋

system

查詢物件表只有一行資料，只用於 MyISAM 和 Memory 引擎的表，最壞的情況

const

基於主鍵或唯一索引查詢，最多返回一條結果

eq_ref

表連線時

基於主鍵或非 NULL 的

唯一索引

完成掃描

ref

基於

普通索引

的

等值查詢

，或者

表間等值連線

fulltext

全文檢索，只對 MyISAM 引擎有效

ref_or_null

表連線型別是 ref，但進行掃描的索引列中可能包含 NULL

index_merge

利用多個索引

unique_subquery

子查詢中使用唯一索引

index_subquery

子查詢中使用普通索引

range

利用索引進行範圍查詢

index

全索引掃描

ALL

全表掃描

3。3 extra 欄位

值

解釋

Using filesort

使用外部排序而不是索引排序，資料量小時從記憶體排序，否則需要在磁碟完成排序

Using temporary

建立一個臨時表儲存結構，通常發生在對沒有索引的列進行 group by

Using index

使用覆蓋索引

Using where

使用 where 語句處理結果

Impossible WHERE

對 where 子句判斷的結果總是 false 而不能選擇任何資料

Using join buffer （Block Nested Loop）

關聯查詢中，被驅動表的關聯欄位沒有索引

Using index condition

先條件過濾索引，在查資料

Select tables optimized away

使用某些

聚合函式

訪問存在索引的某個欄位

4 慢查詢例子

準備資料，資料表結構：

create table user_info_large （ `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT ‘主鍵’， `account` VARCHAR（20） NOT NULL COMMENT ‘使用者賬號’， `name` VARCHAR（20） NOT NULL COMMENT ‘使用者名稱’， `password` VARCHAR（20） not null COMMENT ‘使用者密碼’， `area` VARCHAR（20） NOT NULL COMMENT ‘使用者地址’， `signature` VARCHAR（50） not null COMMENT ‘個性簽名’， PRIMARY KEY （`id`） COMMENT ‘主鍵’， UNIQUE （`account`） COMMENT ‘唯一索引’， KEY `index_area_signture` （`area`， `signature`） COMMENT ‘組合索引’）；複製程式碼

隨機生成 200w 條資料

mysql> select count（id） from user_info_large；+——————-+| count（id） |+——————-+| 2000000 |+——————-+1 row in set （0。38 sec）複製程式碼

擷取部分資料：

執行以下 SQL 語句，沒有使用任何索引欄位：

SELECT name from user_info_large ORDER BY name desc limit 0，100000；複製程式碼

Navicat 工具顯示的查詢時間如下，這並不是 MySQL 真正執行 SQL 的時間，這裡麵包含了網路傳輸等時間：

SQL 具體的查詢時間可以檢視慢查詢日誌：

# Time： 2022-09-26T13：44：18。405459Z# User@Host： root［root］ @ ［ip］ Id： 1893# Query_time： 10。162999 Lock_time： 0。000113 Rows_sent： 100000 Rows_examined： 2100000SET timestamp=1664199858；SELECT name from user_info_large ORDER BY name desc limit 0，100000；複製程式碼

關於其中一些資訊的說明：

Time：SQL 執行的開始時間

Query_time：SQL 語句查詢花費的時間，可以看到花費了 10 秒鐘

Lock_time：等待鎖表的時間

Rows_sent：語句返回的記錄數

Rows_examined：從儲存引擎中返回的記錄數

正在執行的慢查詢是不會被記錄到慢查詢日誌的，只有等待其執行完畢才會記錄到日誌中。

我們可以使用 show processlist 檢視正在執行 SQL 的執行緒。

再執行以下語句，使用索引 account 欄位：

SELECT account from user_info_large ORDER BY account desc limit 0，100000；複製程式碼

檢視慢查詢日誌，並沒有被記錄下來。

現在分別使用 explain 檢視 SQL 語句的執行情況：

explain SELECT name from user_info_large ORDER BY name desc limit 0，100000；複製程式碼

分析情況如下：

可以看到沒有使用到索引，type 為 ALL 表示全表掃描，效率最差，並且 Extra 也是外部排序。

再看看這條 SQL 語句：

explain SELECT account from user_info_large ORDER BY account desc limit 0，100000；複製程式碼

分析情況如下：

type 為 index，使用了索引，使用的索引欄位為 account，Extra 顯示為使用索引排序。

因此，在實際開發中，我們可以針對慢查詢的 SQL，使用 explain 分析語句，根據分析情況以及索引的設計，重新設計 SQL 語句，讓 SQL 語句儘量走索引，走合適的索引。

5 最佳化器與索引

在執行 SQL 時，MySQL 的最佳化器會根據情況選擇索引，但並不能保證其執行時間一定最短，我們可以根據實際情況使用 force key （index） 讓 SQL 語句強制走某個索引。

例如，以下語句執行後，key 欄位為 account，並沒有走主鍵索引。

explain SELECT count（id） from user_info_large；複製程式碼

如果使用 force key，就可以強制令語句走

主鍵索引

。

explain SELECT count（id） from user_info_large force key （PRIMARY）；複製程式碼

6 總結

在專案中如果發現部分 SQL 語句執行緩慢，等待查詢時間長，可以考慮最佳化慢查詢，具體思路為：

透過慢查詢日誌定位 SQL

使用 explain 分析 SQL

修改 SQL，令其走合適的索引

在使用 explain 時，我們主要關注這些欄位：

type

key

Extra

在編寫 SQL 使用索引的時候，我們儘量注意一下規則：

模糊查詢不要使用萬用字元 % 開頭，例如 like ‘%abc’

使用 or 關鍵字時，兩邊的欄位都要有索引。或者使用 union 替代 or

使用複合索引遵循最左原則

索引欄位不要參加表示式運算、函式運算

作者：碼指星斗

連結：https：//juejin。cn/post/7147941447029751822

著作權歸作者所有。商業轉載請聯絡作者獲得授權，非商業轉載請註明出處。