MySQL中explain使用快速查詢手冊

2022-10-18 14:02:51

一. 前言

上一篇整理完了 MySQL 的效能優化方式 , 其中最常用的就是 explain .

這一篇來詳細看看 explain 中各個引數的含義和擴充套件 , 整理出來便於使用時快速查詢

二 . explain 使用

三. 業務實踐

在日常實踐中 , 我們應該如何使用 explain 提供的查詢來判斷索引怎麼設定呢?

以一個實際業務場景為例 : 首先場景裡面的資料分佈都很均衡 , 這就導致設定的索引在查詢優化器的處理下 , 很難產生最好的效果.

先來看一下表結構 :

CREATE TABLE `user_info` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主鍵id',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '會員ID',
  `user_no` bigint(20) NOT NULL DEFAULT '0' COMMENT '會員編號',
  `open_id` varchar(128) NOT NULL DEFAULT '' COMMENT '外部ID',
  `org_id` varchar(128) NOT NULL DEFAULT '0' COMMENT '組織ID',
  `listen_num` int(11) NOT NULL DEFAULT '0' COMMENT '記錄次數',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立時間',
  `create_person` varchar(50) NOT NULL DEFAULT '' COMMENT '建立人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時間',
  `update_person` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_org_id_open_id` (`org_id`,`open_id`) USING BTREE,
  KEY `idx_create_time` (`create_time`) USING BTREE,
  KEY `idx_update_time` (`update_time`) USING BTREE
) COMMENT='會員記錄表';

需要獲取到記錄次數 (listen_num) > 0 使用者的會員編號 (user_no)
org_id 只有四種資料 (A/B/C/D) , 每種資料預計佔25% - 30%
資料是重複修改的關係 , 修改後會更新 update_time

基礎資訊

// 1. 總記錄數 4200000

// 2. 不同 org_id 下的記錄數
- 1234567890 : 100萬
- 9876543210 : 100萬
- 8888888888 : 100萬
- 6666666666 : 100萬
- 其他 : 20萬

// 3. 時間週期 
> 2022-01
> 2022-12

3.1 以 user_id 為條件進行查詢的思路

listen_num 本身沒有建立索引 , 以該欄位查肯定會走全表 , 優先考慮的思路就是 > user_id 為條件進行有序查詢 :

explain select * from user_info where user_id > 69999887 and listen_num > 0

這裡看起來好像萬事大吉 , 你看索引不是生效了嗎 , 只掃描了16行 ,nice!

但是 , 回想一下 B+Tree 的原則 , 在節點裡面搜尋條件是由小到大有序排列的 , 而帶了這個 user_id 處 , 實際上已經快結束了 , 查詢優化器理所當然的選擇了通過 idx_user_id 進行查詢

如果以開始ID做查詢條件 ,可以發現實際上索引沒有生效 , 而型別也是全表

explain select * from user_info where user_id > 10000025 and listen_num > 0

總結 : 當索引欄位遍佈整個資料範圍 , 且查詢很分散的時候 , 在前排序區間的資料可能會放棄使用索引

3.2 以更新時間為查詢條件

既然二級索引裡面是有序 , 那麼以時間作為查詢條件是不是最好的 ?

EXPLAIN SELECT *  FROM user_info 
WHERE update_time > "2022-08-03 01:04:55" AND update_time < "2022-09-03 01:04:55" AND listen_num > 0 LIMIT 100

這裡看起來就很不錯了 , 查詢行數和索引都使用的很理想. 但是這裡面會有一個致命的問題 , 如果是大批次資料查詢 , 那麼這裡一定會出現深度分頁的問題

3.3 簡單優化通過 orgId 進行切割

首先資料結構的特點是什麼? >> 四個組織分佈很平均 , 也就是說如果 org_id 生效 ,我們至少可以只儲存四分之一的查詢量

EXPLAIN SELECT *  FROM user_info WHERE org_id = "123" and update_time > "2022-08-03 01:04:55" AND update_time < "2022-09-03 01:04:55" and listen_num > 0 LIMIT 100

初步總結

通過以上三個案例 , 基本上就可以看出 explain 的基本用法

通過 type 判斷比較的型別
通過 key 判斷是否使用了自己期望的索引
通過 row 判斷這個索引的效果

3.4 多索引條件的抉擇

要記住的一點是 , 索引並不是我們以為的樣子 ,當多個索引同時存在的時候 , MySQL 會根據情況進行選擇. 比如 :

EXPLAIN SELECT *  FROM user_info 
WHERE org_id = "1234567890" and update_time > "2022-08-03 01:04:55" AND update_time < "2022-08-04 01:04:55" 
and listen_num > 0 LIMIT 100

如果這裡把時間週期拉長 , 那麼結果也會相應的轉變 :

EXPLAIN SELECT *  FROM user_info 
WHERE org_id = "1234567890" and update_time > "2022-08-03 01:04:55" AND update_time < "2022-09-04 01:04:55"
and listen_num > 0 LIMIT 100

3.5 連表查詢的關注點

連表查詢中主要關注的屬性是 filtered , 來實際來看看這個屬性 :

// org 是個很簡單的表 , org_id 即對於其ID
EXPLAIN SELECT *  FROM user_info as u , org as o WHERE org_id = "123" and u.org_id = o.id

在單表時 , filtered 表示索引生效的佔比 . 簡單來說 ,比例越高,則索引利用率越高
在多表時 , 這個表示次表需要查詢的行數佔比. 也就是被驅動的表剩餘的查詢次數

四. 深入問題

4.1 explain 的結果能作為最終決策嗎?

explain 的結果並不能作為最終決策行為 , explain 是執行計劃 , 計劃和實際是會存在偏差的, 畢竟 explain 沒有真的執行.

哪怕我們最終只需要100行 , 按照 ID 排序的情況下只查幾行 , 實際上執行計劃的 row 仍然會很龐大.

總結

explain 主要作為參考 , 在實際使用中 , 需要更多的經驗思考. 可能最終的結果和explain的不一致.

例如上面的案例 , 按照 explain 的做法 , 用短時間週期最好 ,其次應該是 org_id .

但是根據業務場景 ,我會選擇通過 > id 的方式迴圈查. 一個是業務原因 ,查詢的量大 , 上述兩種方式都不能避免深度翻頁的問題.

到此這篇關於MySQL中explain使用快速查詢手冊的文章就介紹到這了,更多相關MySQL explain快速查詢手冊內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

參考 :

<高效能MySQL>

<MySQL 是怎樣執行的：從根兒上理解 MySQL>