本篇文章給大家?guī)砹岁P于mysql的相關知識，其中主要介紹了關于慢查詢優(yōu)化的相關問題，包括了利用慢查詢?nèi)罩径ㄎ宦樵僑QL、通過explain分析慢查詢SQL、修改SQL盡量讓SQL走索引，下面一起來看一下，希望對大家有幫助。

程序員必備接口測試調(diào)試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設計、調(diào)試、文檔、自動化測試工具
后端、前端、測試，同時在線協(xié)作，內(nèi)容實時同步

推薦學習：mysql視頻教程

1 慢查詢優(yōu)化思路

當發(fā)生慢查詢的時候，優(yōu)化的思路為：

• 利用慢查詢?nèi)罩径ㄎ宦樵?SQL

• 通過 explain 分析慢查詢 SQL

• 修改 SQL，盡量讓 SQL 走索引

2 慢查詢?nèi)罩?/h2>

MySQL 提供了一個功能——慢查詢?nèi)罩?，會記錄查詢時間超過指定時間閾值的 SQL 到日志中，便于我們定位慢查詢并且優(yōu)化對應的 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】慢查詢?nèi)罩臼欠耖_啟 | slow_query_log_file                    | /var/lib/mysql/Linux-slow.log |【3】慢查詢?nèi)罩疚募鎯ξ恢?========================================================================== +----------------------------------------+-------------------------------+ 15 rows in set (0.00 sec)

登錄后復制

這里主要關注三個變量：

• long_query_time，慢查詢的時間閾值，單位秒，如果一個 SQL 語句的執(zhí)行時間超過這個值，那么 MySQL 就認定其為慢查詢

• slow_query_log，慢查詢?nèi)罩竟δ苁欠耖_啟，默認關閉，開啟后記錄慢查詢

• slow_query_log_file，慢查詢?nèi)罩疚募拇鎯ξ恢?/p>

默認慢查詢?nèi)罩竟δ苁顷P閉的，因此我們需要啟動該功能

# 開啟慢查詢?nèi)罩?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 語句的執(zhí)行情況，例如：

mysql> explain select sum(1+2);

執(zhí)行結果如下，可以看到有很多字段

我們主要看看一些重要的字段：

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

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

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

• possible_keys表示可能使用的索引列

• keys表示查詢中實際使用的索引列，由查詢優(yōu)化器決定

3.1 select_type 字段

3.2 type 字段

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

關于 type 字段的值，其從上到下對應的 SQL 的執(zhí)行性能逐漸變差。

3.3 extra 字段

4 慢查詢例子

準備數(shù)據(jù)，數(shù)據(jù)表結構：

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 條數(shù)據(jù)

mysql> select count(id) from user_info_large; +-----------+ | count(id) | +-----------+ |   2000000 | +-----------+ 1 row in set (0.38 sec)

截取部分數(shù)據(jù)：

執(zhí)行以下 SQL 語句，沒有使用任何索引字段：

SELECT name from user_info_large ORDER BY name desc limit 0,100000;

Navicat 工具顯示的查詢時間如下，這并不是 MySQL 真正執(zhí)行 SQL 的時間，這里面包含了網(wǎng)絡傳輸?shù)葧r間：

SQL 具體的查詢時間可以查看慢查詢?nèi)罩荆?/p>

# 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 執(zhí)行的開始時間

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

• Lock_time：等待鎖表的時間

• Rows_sent：語句返回的記錄數(shù)

• Rows_examined：從存儲引擎中返回的記錄數(shù)

正在執(zhí)行的慢查詢是不會被記錄到慢查詢?nèi)罩镜?，只有等待其?zhí)行完畢才會記錄到日志中。

我們可以使用 show processlist 查看正在執(zhí)行 SQL 的線程。

再執(zhí)行以下語句，使用索引 account 字段：

SELECT account from user_info_large ORDER BY account desc limit 0,100000;

查看慢查詢?nèi)罩?，并沒有被記錄下來。

現(xiàn)在分別使用 explain 查看 SQL 語句的執(zhí)行情況：

explain SELECT name from user_info_large ORDER BY name desc limit 0,100000;

分析情況如下：

可以看到?jīng)]有使用到索引，type 為 ALL 表示全表掃描，效率最差，并且 Extra 也是外部排序。

再看看這條 SQL 語句：

explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;

分析情況如下：

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

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

5 優(yōu)化器與索引

在執(zhí)行 SQL 時，MySQL 的優(yōu)化器會根據(jù)情況選擇索引，但并不能保證其執(zhí)行時間一定最短，我們可以根據(jù)實際情況使用 force key (index) 讓 SQL 語句強制走某個索引。

例如，以下語句執(zhí)行后，key 字段為 account，并沒有走主鍵索引。

explain SELECT count(id) from user_info_large;

如果使用 force key，就可以強制令語句走主鍵索引。

explain SELECT count(id) from user_info_large force key (PRIMARY);

6 總結

在項目中如果發(fā)現(xiàn)部分 SQL 語句執(zhí)行緩慢，等待查詢時間長，可以考慮優(yōu)化慢查詢，具體思路為：

• 通過慢查詢?nèi)罩径ㄎ?SQL

• 使用 explain 分析 SQL

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

在使用 explain 時，我們主要關注這些字段：

• type

• key

• Extra

在編寫 SQL 使用索引的時候，我們盡量注意一下規(guī)則：

• 模糊查詢不要使用通配符 % 開頭，例如 like '%abc'

• 使用 or 關鍵字時，兩邊的字段都要有索引?；蛘呤褂?union 替代 or

• 使用復合索引遵循最左原則

• 索引字段不要參加表達式運算、函數(shù)運算

推薦學習：mysql視頻教程