一. 前言

上一篇整理完了 MySQL 的性能優(yōu)化方式 , 其中最常用的就是 explain .

這一篇來(lái)詳細(xì)看看 explain 中各個(gè)參數(shù)的含義和擴(kuò)展 , 整理出來(lái)便于使用時(shí)快速查詢

二 . explain 使用

三. 業(yè)務(wù)實(shí)踐

在日常實(shí)踐中 , 我們應(yīng)該如何使用 explain 提供的查詢來(lái)判斷索引怎么配置呢?

以一個(gè)實(shí)際業(yè)務(wù)場(chǎng)景為例 : 首先場(chǎng)景里面的數(shù)據(jù)分布都很均衡 , 這就導(dǎo)致設(shè)置的索引在查詢優(yōu)化器的處理下 , 很難產(chǎn)生最好的效果.

先來(lái)看一下表結(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 '會(huì)員ID',
  `user_no` bigint(20) NOT NULL DEFAULT '0' COMMENT '會(huì)員編號(hào)',
  `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)建時(shí)間',
  `create_person` varchar(50) NOT NULL DEFAULT '' COMMENT '創(chuàng)建人',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新時(shí)間',
  `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='會(huì)員記錄表';

• 需要獲取到記錄次數(shù) (listen_num) > 0 用戶的會(huì)員編號(hào) (user_no)
• org_id 只有四種數(shù)據(jù) (A/B/C/D) , 每種數(shù)據(jù)預(yù)計(jì)占25% - 30%
• 數(shù)據(jù)是重復(fù)修改的關(guān)系 , 修改后會(huì)更新 update_time

基礎(chǔ)信息

// 1. 總記錄數(shù) 4200000

// 2. 不同 org_id 下的記錄數(shù)
- 1234567890 : 100萬(wàn)
- 9876543210 : 100萬(wàn)
- 8888888888 : 100萬(wàn)
- 6666666666 : 100萬(wàn)
- 其他 : 20萬(wàn)

// 3. 時(shí)間周期 
> 2022-01
> 2022-12

3.1 以 user_id 為條件進(jìn)行查找的思路

listen_num 本身沒(méi)有創(chuàng)建索引 , 以該字段查肯定會(huì)走全表 , 優(yōu)先考慮的思路就是 > user_id 為條件進(jìn)行有序查詢 :

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

這里看起來(lái)好像萬(wàn)事大吉 , 你看索引不是生效了嗎 , 只掃描了16行 ,nice!

但是 , 回想一下 B+Tree 的原則 , 在節(jié)點(diǎn)里面搜索條件是由小到大有序排列的 , 而帶了這個(gè) user_id 處 , 實(shí)際上已經(jīng)快結(jié)束了 , 查詢優(yōu)化器理所當(dāng)然的選擇了通過(guò) idx_user_id 進(jìn)行查詢 

如果以開(kāi)始ID做查詢條件 ,可以發(fā)現(xiàn)實(shí)際上索引沒(méi)有生效 , 而類(lèi)型也是全表

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

總結(jié) : 當(dāng)索引字段遍布整個(gè)數(shù)據(jù)范圍 , 且查詢很分散的時(shí)候 , 在前排序區(qū)間的數(shù)據(jù)可能會(huì)放棄使用索引

3.2 以更新時(shí)間為查詢條件

既然二級(jí)索引里面是有序 , 那么以時(shí)間作為查詢條件是不是最好的 ?

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

這里看起來(lái)就很不錯(cuò)了 , 查詢行數(shù)和索引都使用的很理想. 但是這里面會(huì)有一個(gè)致命的問(wèn)題 , 如果是大批量數(shù)據(jù)查詢 , 那么這里一定會(huì)出現(xiàn)深度分頁(yè)的問(wèn)題

3.3 簡(jiǎn)單優(yōu)化通過(guò) orgId 進(jìn)行切割

首先數(shù)據(jù)結(jié)構(gòu)的特點(diǎn)是什么? >> 四個(gè)組織分布很平均 , 也就是說(shuō)如果 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

初步總結(jié)

通過(guò)以上三個(gè)案例 , 基本上就可以看出 explain 的基本用法

• 通過(guò) type 判斷比較的類(lèi)型
• 通過(guò) key 判斷是否使用了自己期望的索引
• 通過(guò) row 判斷這個(gè)索引的效果

3.4 多索引條件的抉擇

要記住的一點(diǎn)是 , 索引并不是我們以為的樣子 ,當(dāng)多個(gè)索引同時(shí)存在的時(shí)候 , MySQL 會(huì)根據(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

如果這里把時(shí)間周期拉長(zhǎng) , 那么結(jié)果也會(huì)相應(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

3.5 連表查詢的關(guān)注點(diǎn)

連表查詢中主要關(guān)注的屬性是 filtered , 來(lái)實(shí)際來(lái)看看這個(gè)屬性 :

// org 是個(gè)很簡(jiǎn)單的表 , org_id 即對(duì)于其ID
EXPLAIN SELECT *  FROM user_info as u , org as o WHERE org_id = "123" and u.org_id = o.id

• 在單表時(shí) , filtered 表示索引生效的占比 . 簡(jiǎn)單來(lái)說(shuō) ,比例越高,則索引利用率越高
• 在多表時(shí) , 這個(gè)表示次表需要查詢的行數(shù)占比. 也就是被驅(qū)動(dòng)的表剩余的查詢次數(shù)

四. 深入問(wèn)題

4.1 explain 的結(jié)果能作為最終決策嗎?

explain 的結(jié)果并不能作為最終決策行為 , explain 是執(zhí)行計(jì)劃 , 計(jì)劃和實(shí)際是會(huì)存在偏差的, 畢竟 explain 沒(méi)有真的執(zhí)行.

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

總結(jié)

explain 主要作為參考 , 在實(shí)際使用中 , 需要更多的經(jīng)驗(yàn)思考. 可能最終的結(jié)果和explain的不一致.

例如上面的案例 , 按照 explain 的做法 , 用短時(shí)間周期最好 ,其次應(yīng)該是 org_id .

但是根據(jù)業(yè)務(wù)場(chǎng)景 ,我會(huì)選擇通過(guò) > id 的方式循環(huán)查. 一個(gè)是業(yè)務(wù)原因 ,查詢的量大 , 上述兩種方式都不能避免深度翻頁(yè)的問(wèn)題.

到此這篇關(guān)于MySQL中explain使用快速查詢手冊(cè)的文章就介紹到這了,更多相關(guān)MySQL explain快速查詢手冊(cè)內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

參考 :

<高性能MySQL>

<MySQL 是怎樣運(yùn)行的：從根兒上理解 MySQL>

最新評(píng)論