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

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

推薦學習：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 欄位

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视频教程

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