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: 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
- 使用複合索引遵循最左原則
- 索引字段不要參加表達式運算、函數運算
作者：碼指星鬥
連結：https://juejin.cn/post/7147941447029751822
來源：稀土掘金
著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。