總結分享之mysql慢查詢優化的思路-mysql教程-PHP中文網

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

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

推薦學習：mysql影片教學

#1 慢查詢最佳化思路

當發生慢查詢的時候，最佳化的想法為：

利用慢查詢日誌定位慢查詢SQL
透過explain 分析慢查詢SQL
修改SQL，盡量讓SQL 走索引

2 慢查詢日誌

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

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

mysql> show global variables like &#39;%quer%&#39;;
+----------------------------------------+-------------------------------+
| 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);

登入後複製

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

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

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

select_type 表示查詢語句的查詢類型，包括簡單查詢、子查詢等等
table 表示查詢的表，不一定是存在表，可能是本次查詢中得到的臨時表
type 表示檢索類型，使用全表掃描、或索引掃描等
possible_keys表示可能使用的索引列
keys表示查詢中實際使用的索引列，由查詢最佳化器決定

3.1 select_type 欄位

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

3.2 type 欄位

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

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

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

3.3 extra 欄位

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

##4 慢查詢範例

準備數據，資料表結構：

create table user_info_large (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT &#39;主键&#39;,
`account` VARCHAR(20) NOT NULL COMMENT &#39;用户账号&#39;,
`name` VARCHAR(20) NOT NULL COMMENT &#39;用户名&#39;,
`password` VARCHAR(20) not null COMMENT &#39;用户密码&#39;,
`area` VARCHAR(20) NOT NULL COMMENT &#39;用户地址&#39;,
`signature` VARCHAR(50) not null COMMENT &#39;个性签名&#39;,
PRIMARY KEY (`id`) COMMENT &#39;主键&#39;,
UNIQUE (`account`) COMMENT &#39;唯一索引&#39;,
KEY `index_area_signture` (`area`,  `signature`)  COMMENT &#39;组合索引&#39;
);

登入後複製

隨機產生200w 資料

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

登入後複製

截取部分資料：

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

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

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

登入後複製

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

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

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;

登入後複製

分析情況如下：

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

可以看到没有使用到索引，type 为 ALL 表示全表扫描，效率最差，并且 Extra 也是外部排序。

再看看这条 SQL 语句：

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

登入後複製

分析情况如下：

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

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;

登入後複製

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

如果使用 force key，就可以强制令语句走主键索引。

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

登入後複製

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

6 总结

在项目中如果发现部分 SQL 语句执行缓慢，等待查询时间长，可以考虑优化慢查询，具体思路为：

通过慢查询日志定位 SQL
使用 explain 分析 SQL
修改 SQL，令其走合适的索引

在使用 explain 时，我们主要关注这些字段：

type
key
Extra

在编写 SQL 使用索引的时候，我们尽量注意一下规则：

模糊查询不要使用通配符 % 开头，例如 like '%abc'
使用 or 关键字时，两边的字段都要有索引。或者使用 union 替代 or
使用复合索引遵循最左原则
索引字段不要参加表达式运算、函数运算

推荐学习：mysql视频教程

以上是總結分享之mysql慢查詢優化的思路的詳細內容。更多資訊請關注PHP中文網其他相關文章！