站長(zhǎng)資訊網(wǎng)本篇文章給大家?guī)?lái)了關(guān)于mysql的相關(guān)知識(shí)，其中主要介紹了關(guān)于慢查詢優(yōu)化的相關(guān)問(wèn)題，包括了利用慢查詢?nèi)罩径ㄎ宦樵僑QL、通過(guò)explain分析慢查詢SQL、修改SQL盡量讓SQL走索引，下面一起來(lái)看一下，希望對(duì)大家有幫助。
程序員必備接口測(cè)試調(diào)試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設(shè)計(jì)、調(diào)試、文檔、自動(dòng)化測(cè)試工具
后端、前端、測(cè)試，同時(shí)在線協(xié)作，內(nèi)容實(shí)時(shí)同步
推薦學(xué)習(xí)：mysql視頻教程
1 慢查詢優(yōu)化思路
當(dāng)發(fā)生慢查詢的時(shí)候，優(yōu)化的思路為：
-
利用慢查詢?nèi)罩径ㄎ宦樵?SQL
-
通過(guò) explain 分析慢查詢 SQL
-
修改 SQL，盡量讓 SQL 走索引
2 慢查詢?nèi)罩?/h2>
MySQL 提供了一個(gè)功能——慢查詢?nèi)罩?，?huì)記錄查詢時(shí)間超過(guò)指定時(shí)間閾值的 SQL 到日志中，便于我們定位慢查詢并且優(yōu)化對(duì)應(yīng)的 SQL 語(yǔ)句。
首先查看 MySQL 中關(guān)于慢查詢相關(guān)的全局變量：
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】慢查詢的時(shí)間閾值 ========================================================================== | 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)罩臼欠耖_(kāi)啟 | slow_query_log_file | /var/lib/mysql/Linux-slow.log |【3】慢查詢?nèi)罩疚募鎯?chǔ)位置 ========================================================================== +----------------------------------------+-------------------------------+ 15 rows in set (0.00 sec)
這里主要關(guān)注三個(gè)變量：
-
long_query_time，慢查詢的時(shí)間閾值，單位秒，如果一個(gè) SQL 語(yǔ)句的執(zhí)行時(shí)間超過(guò)這個(gè)值，那么 MySQL 就認(rèn)定其為慢查詢
-
slow_query_log，慢查詢?nèi)罩竟δ苁欠耖_(kāi)啟，默認(rèn)關(guān)閉，開(kāi)啟后記錄慢查詢
-
slow_query_log_file，慢查詢?nèi)罩疚募拇鎯?chǔ)位置
默認(rèn)慢查詢?nèi)罩竟δ苁顷P(guān)閉的，因此我們需要啟動(dòng)該功能
# 開(kāi)啟慢查詢?nèi)罩?mysql> set global slow_query_log=ON; Query OK, 0 rows affected (0.00 sec) # 設(shè)置慢查詢時(shí)間閾值 mysql> set long_query_time=1; Query OK, 0 rows affected (0.00 sec)
這樣子設(shè)置后，MySQL 重啟會(huì)丟失這些配置，需要在配置文件中修改才會(huì)永久有效。
3 explain
我們可以使用 explain 分析 SQL 語(yǔ)句的執(zhí)行情況，例如：
mysql> explain select sum(1+2);
執(zhí)行結(jié)果如下，可以看到有很多字段
我們主要看看一些重要的字段：
-
select_type 表示查詢語(yǔ)句的查詢類型，包括簡(jiǎn)單查詢、子查詢等等
-
table 表示查詢的表，不一定是存在表，可能是本次查詢中得到的臨時(shí)表
-
type 表示檢索類型，使用全表掃描、還是索引掃描等
-
possible_keys表示可能使用的索引列
-
keys表示查詢中實(shí)際使用的索引列，由查詢優(yōu)化器決定
3.1 select_type 字段
3.2 type 字段
對(duì)于 InnoDB 存儲(chǔ)引擎，type列通常都是all或者index。
關(guān)于 type 字段的值，其從上到下對(duì)應(yīng)的 SQL 的執(zhí)行性能逐漸變差。
3.3 extra 字段
4 慢查詢例子
準(zhǔn)備數(shù)據(jù)，數(shù)據(jù)表結(jié)構(gòu)：
create table user_info_large ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵', `account` VARCHAR(20) NOT NULL COMMENT '用戶賬號(hào)', `name` VARCHAR(20) NOT NULL COMMENT '用戶名', `password` VARCHAR(20) not null COMMENT '用戶密碼', `area` VARCHAR(20) NOT NULL COMMENT '用戶地址', `signature` VARCHAR(50) not null COMMENT '個(gè)性簽名', PRIMARY KEY (`id`) COMMENT '主鍵', UNIQUE (`account`) COMMENT '唯一索引', KEY `index_area_signture` (`area`, `signature`) COMMENT '組合索引' );
隨機(jī)生成 200w 條數(shù)據(jù)
mysql> select count(id) from user_info_large; +-----------+ | count(id) | +-----------+ | 2000000 | +-----------+ 1 row in set (0.38 sec)
截取部分?jǐn)?shù)據(jù)：
執(zhí)行以下 SQL 語(yǔ)句，沒(méi)有使用任何索引字段：
SELECT name from user_info_large ORDER BY name desc limit 0,100000;
Navicat 工具顯示的查詢時(shí)間如下，這并不是 MySQL 真正執(zhí)行 SQL 的時(shí)間，這里面包含了網(wǎng)絡(luò)傳輸?shù)葧r(shí)間：
SQL 具體的查詢時(shí)間可以查看慢查詢?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;
關(guān)于其中一些信息的說(shuō)明：
-
Time：SQL 執(zhí)行的開(kāi)始時(shí)間
-
Query_time：SQL 語(yǔ)句查詢花費(fèi)的時(shí)間，可以看到花費(fèi)了 10 秒鐘
-
Lock_time：等待鎖表的時(shí)間
-
Rows_sent：語(yǔ)句返回的記錄數(shù)
-
Rows_examined：從存儲(chǔ)引擎中返回的記錄數(shù)
正在執(zhí)行的慢查詢是不會(huì)被記錄到慢查詢?nèi)罩镜?，只有等待其?zhí)行完畢才會(huì)記錄到日志中。
我們可以使用 show processlist 查看正在執(zhí)行 SQL 的線程。
再執(zhí)行以下語(yǔ)句，使用索引 account 字段：
SELECT account from user_info_large ORDER BY account desc limit 0,100000;
查看慢查詢?nèi)罩?，并沒(méi)有被記錄下來(lái)。
現(xiàn)在分別使用 explain 查看 SQL 語(yǔ)句的執(zhí)行情況：
explain SELECT name from user_info_large ORDER BY name desc limit 0,100000;
分析情況如下：
可以看到?jīng)]有使用到索引，type 為 ALL 表示全表掃描，效率最差，并且 Extra 也是外部排序。
再看看這條 SQL 語(yǔ)句：
explain SELECT account from user_info_large ORDER BY account desc limit 0,100000;
分析情況如下：
type 為 index，使用了索引，使用的索引字段為 account，Extra 顯示為使用索引排序。
因此，在實(shí)際開(kāi)發(fā)中，我們可以針對(duì)慢查詢的 SQL，使用 explain 分析語(yǔ)句，根據(jù)分析情況以及索引的設(shè)計(jì)，重新設(shè)計(jì) SQL 語(yǔ)句，讓 SQL 語(yǔ)句盡量走索引，走合適的索引。
5 優(yōu)化器與索引
在執(zhí)行 SQL 時(shí)，MySQL 的優(yōu)化器會(huì)根據(jù)情況選擇索引，但并不能保證其執(zhí)行時(shí)間一定最短，我們可以根據(jù)實(shí)際情況使用 force key (index) 讓 SQL 語(yǔ)句強(qiáng)制走某個(gè)索引。
例如，以下語(yǔ)句執(zhí)行后，key 字段為 account，并沒(méi)有走主鍵索引。
explain SELECT count(id) from user_info_large;
如果使用 force key，就可以強(qiáng)制令語(yǔ)句走主鍵索引。
explain SELECT count(id) from user_info_large force key (PRIMARY);
6 總結(jié)
在項(xiàng)目中如果發(fā)現(xiàn)部分 SQL 語(yǔ)句執(zhí)行緩慢，等待查詢時(shí)間長(zhǎng)，可以考慮優(yōu)化慢查詢，具體思路為：
-
通過(guò)慢查詢?nèi)罩径ㄎ?SQL
-
使用 explain 分析 SQL
-
修改 SQL，令其走合適的索引
在使用 explain 時(shí)，我們主要關(guān)注這些字段：
-
type
-
key
-
Extra
在編寫(xiě) SQL 使用索引的時(shí)候，我們盡量注意一下規(guī)則：
-
模糊查詢不要使用通配符 % 開(kāi)頭，例如 like '%abc'
-
使用 or 關(guān)鍵字時(shí)，兩邊的字段都要有索引?；蛘呤褂?union 替代 or
-
使用復(fù)合索引遵循最左原則
-
索引字段不要參加表達(dá)式運(yùn)算、函數(shù)運(yùn)算
推薦學(xué)習(xí)：mysql視頻教程
