總結分享之mysql慢查詢優化的思路

本篇文章給大家帶來了關於的相關知識，其中主要介紹了關於慢查詢優化的相關問題，包括了利用慢查詢紀錄檔定位慢查詢SQL、通過explain分析慢查詢SQL、修改SQL儘量讓SQL走索引，下面一起來看一下，希望對大家有幫助。

程式設計師必備介面測試偵錯工具：

推薦學習：

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 欄位

3.2 type 欄位

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

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

3.3 extra 欄位

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: 2100000
SET 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
使用複合索引遵循最左原則
索引欄位不要參加表示式運算、函數運算

推薦學習：

以上就是總結分享之mysql慢查詢優化的思路的詳細內容，更多請關注TW511.COM其它相關文章！