一. 前言
這一篇來詳細(xì)看看 EXPLAIN 各個參數(shù)的含義和擴(kuò)展 , 整理出來便于使用時快速查詢
二 . explain 使用
三. 業(yè)務(wù)實踐
在日常實踐中 , 我們應(yīng)該如何使用 explain 提供的查詢來判斷索引怎么配置呢?
以一個實際業(yè)務(wù)場景為例 : 首先場景里面的數(shù)據(jù)分布都很均衡 , 這就導(dǎo)致設(shè)置的索引在查詢優(yōu)化器的處理下 , 很難產(chǎn)生最好的效果.
先來看一下表結(jié)構(gòu) :
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 '記錄次數(shù)', `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '創(chuàng)建時間', `create_person` varchar(50) NOT NULL DEFAULT '' COMMENT '創(chuàng)建人', `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='會員記錄表'; 復(fù)制代碼
- 需要獲取到記錄次數(shù) (listen_num) > 0 用戶的會員編號 (user_no)
- org_id 只有四種數(shù)據(jù) (A/B/C/D) , 每種數(shù)據(jù)預(yù)計占25% - 30%
- 數(shù)據(jù)是重復(fù)修改的關(guān)系 , 修改后會更新 update_time
基礎(chǔ)信息
// 1. 總記錄數(shù) 4200000 // 2. 不同 org_id 下的記錄數(shù) - 1234567890 : 100萬 - 9876543210 : 100萬 - 8888888888 : 100萬 - 6666666666 : 100萬 - 其他 : 20萬 // 3. 時間周期 > 2022-01 > 2022-12 復(fù)制代碼 3.1 以 user_id 為條件進(jìn)行查找的思路
listen_num 本身沒有創(chuàng)建索引 , 以該字段查肯定會走全表 , 優(yōu)先考慮的思路就是 > user_id 為條件進(jìn)行有序查詢 :
explain select * from user_info where user_id > 69999887 and listen_num > 0 復(fù)制代碼
這里看起來好像萬事大吉 , 你看索引不是生效了嗎 , 只掃描了16行 ,nice!
但是 , 回想一下 B+Tree 的原則 , 在節(jié)點里面搜索條件是由小到大有序排列的 , 而帶了這個 user_id 處 , 實際上已經(jīng)快結(jié)束了 , 查詢優(yōu)化器理所當(dāng)然地選擇了通過 idx_user_id 進(jìn)行查詢
如果以開始ID做查詢條件 ,可以發(fā)現(xiàn)實際上索引沒有生效 , 而類型也是全表
explain select * from user_info where user_id > 10000025 and listen_num > 0 復(fù)制代碼
總結(jié) : 當(dāng)索引字段遍布整個數(shù)據(jù)范圍 , 且查詢很分散的時候 , 在前排序區(qū)間的數(shù)據(jù)可能會放棄使用索引
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 復(fù)制代碼
這里看起來就很不錯了 , 查詢行數(shù)和索引都使用得很理想. 但是這里面會有一個致命的問題 , 如果是大批量數(shù)據(jù)查詢 , 那么這里一定會出現(xiàn)深度分頁的問題
3.3 簡單優(yōu)化通過 orgId 進(jìn)行切割
首先數(shù)據(jù)結(jié)構(gòu)的特點是什么? >> 四個組織分布很平均 , 也就是說如果 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 復(fù)制代碼
初步總結(jié)
通過以上三個案例 , 基本上就可以看出 explain 的基本用法
- 通過 type 判斷比較的類型
- 通過 key 判斷是否使用了自己期望的索引
- 通過 row 判斷這個索引的效果
要記住的一點是 , 索引并不是我們以為的樣子 ,當(dāng)多個索引同時存在的時候 , MySQL 會根據(jù)情況進(jìn)行選擇. 比如 :
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 復(fù)制代碼
如果這里把時間周期拉長 , 那么結(jié)果也會相應(yīng)的轉(zhuǎn)變 :
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 復(fù)制代碼
3.5 連表查詢的關(guān)注點
連表查詢中主要關(guān)注的屬性是 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 復(fù)制代碼
- 在單表時 , filtered 表示索引生效的占比 . 簡單來說 ,比例越高,則索引利用率越高
- 在多表時 , 這個表示次表需要查詢的行數(shù)占比. 也就是被驅(qū)動的表剩余的查詢次數(shù)
explain 的結(jié)果并不能作為最終決策行為 , explain 是執(zhí)行計劃 , 計劃和實際是會存在偏差的, 畢竟 explain 沒有真的執(zhí)行.
哪怕我們最終只需要100行 , 按照 ID 排序的情況下只查幾行 , 實際上執(zhí)行計劃的 row 仍然會很龐大.
總結(jié)
explain 主要作為參考 , 在實際使用中 , 需要更多的經(jīng)驗思考. 可能最終的結(jié)果和explain的不一致.
例如上面的案例 , 按照 explain 的做法 , 用短時間周期最好 ,其次應(yīng)該是 org_id .
但是根據(jù)業(yè)務(wù)場景 ,我會選擇通過 > id 的方式循環(huán)查. 一個是業(yè)務(wù)原因 ,查詢的量大 , 上述兩種方式都不能避免深度翻頁的問題.
作者：AntBlack 鏈接：https://juejin.cn/post/7154744473048580110 來源：稀土掘金 著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。
