建站
咨詢
售后
建站咨詢
新聞中心
為了驗(yàn)證 MySQL 中哪些情況下會(huì)導(dǎo)致索引失效，我們可以借助 explain 執(zhí)行計(jì)劃來分析索引失效的具體場景。
創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價(jià)比隴川網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式隴川網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋隴川地區(qū)。費(fèi)用合理售后完善，十載實(shí)體公司更值得信賴。
explain 使用如下，只需要在查詢的 SQL 前面添加上 explain 關(guān)鍵字即可，如下圖所示：
而以上查詢結(jié)果的列中，我們最主要觀察 key 這一列，key 這一列表示實(shí)際使用的索引，如果為 NULL 則表示未使用索引，反之則使用了索引。
以上所有結(jié)果列說明如下：
- id — 選擇標(biāo)識(shí)符，id 越大優(yōu)先級(jí)越高，越先被執(zhí)行；
- select_type — 表示查詢的類型；
- table — 輸出結(jié)果集的表；
- partitions — 匹配的分區(qū)；
- type — 表示表的連接類型；
- possible_keys — 表示查詢時(shí)，可能使用的索引；
- key — 表示實(shí)際使用的索引；
- key_len — 索引字段的長度；
- ref— 列與索引的比較；
- rows — 大概估算的行數(shù)；
- filtered — 按表?xiàng)l件過濾的行百分比；
- Extra — 執(zhí)行情況的描述和說明。
其中最重要的就是 type 字段，type 值類型如下：
- all — 掃描全表數(shù)據(jù)；
- index — 遍歷索引；
- range — 索引范圍查找；
- index_subquery — 在子查詢中使用 ref；
- unique_subquery — 在子查詢中使用 eq_ref；
- ref_or_null — 對(duì) null 進(jìn)行索引的優(yōu)化的 ref；
- fulltext — 使用全文索引；
- ref — 使用非唯一索引查找數(shù)據(jù)；
- eq_ref — 在 join 查詢中使用主鍵或唯一索引關(guān)聯(lián)；
- const — 將一個(gè)主鍵放置到 where 后面作為條件查詢， MySQL 優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個(gè)常量，如何轉(zhuǎn)化以及何時(shí)轉(zhuǎn)化，這個(gè)取決于優(yōu)化器，這個(gè)比 eq_ref 效率高一點(diǎn)。
創(chuàng)建測試表和數(shù)據(jù)
為了演示和測試那種情況下會(huì)導(dǎo)致索引失效，我們先創(chuàng)建一個(gè)測試表和相應(yīng)的數(shù)據(jù)：
-- 創(chuàng)建表
drop table if exists student;
create table student(
id int primary key auto_increment comment '主鍵',
sn varchar(32) comment '學(xué)號(hào)',
name varchar(250) comment '姓名',
age int comment '年齡',
sex bit comment '性別',
address varchar(250) comment '家庭地址',
key idx_address (address),
key idx_sn_name_age (sn,name,age)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 添加測試數(shù)據(jù)
insert into student(id,sn,name,age,sex,address)
values(1,'cn001','張三',18,1,'高老莊'),
(2,'cn002','李四',20,0,'花果山'),
(3,'cn003','王五',50,1,'水簾洞');
當(dāng)前表中總共有 3 個(gè)索引，如下圖所示：
PS：本文以下內(nèi)容基于 MySQL 5.7 InnoDB 數(shù)據(jù)引擎下。
索引失效情況1：非最左匹配
最左匹配原則指的是，以最左邊的為起點(diǎn)字段查詢可以使用聯(lián)合索引，否則將不能使用聯(lián)合索引。我們本文的聯(lián)合索引的字段順序是 sn + name + age，我們假設(shè)它們的順序是 A + B + C，以下聯(lián)合索引的使用情況如下：
從上述結(jié)果可以看出，如果是以最左邊開始匹配的字段都可以使用上聯(lián)合索引，比如：
- A+B+C
- A+B
- A+C其中：A 等于字段 sn，B 等于字段 name，C 等于字段 age。
其中：A 等于字段 sn，B 等于字段 name，C 等于字段 age。
而 B+C 卻不能使用到聯(lián)合索引，這就是最左匹配原則。
索引失效情況2：錯(cuò)誤模糊查詢
模糊查詢 like 的常見用法有 3 種：
- 模糊匹配后面任意字符：like '張%'
- 模糊匹配前面任意字符：like '%張'
- 模糊匹配前后任意字符：like '%張%'
而這 3 種模糊查詢中只有第 1 種查詢方式可以使用到索引，具體執(zhí)行結(jié)果如下：
索引失效情況3：列運(yùn)算
如果索引列使用了運(yùn)算，那么索引也會(huì)失效，如下圖所示：
索引失效情況4：使用函數(shù)
查詢列如果使用任意 MySQL 提供的函數(shù)就會(huì)導(dǎo)致索引失效，比如以下列使用了 ifnull 函數(shù)之后的執(zhí)行計(jì)劃如下：
索引失效情況5：類型轉(zhuǎn)換
如果索引列存在類型轉(zhuǎn)換，那么也不會(huì)走索引，比如 address 為字符串類型，而查詢的時(shí)候設(shè)置了 int 類型的值就會(huì)導(dǎo)致索引失效，如下圖所示：
索引失效情況6：使用 is not null
當(dāng)在查詢中使用了 is not null 也會(huì)導(dǎo)致索引失效，而 is null 則會(huì)正常觸發(fā)索引的，如下圖所示：
總結(jié)
導(dǎo)致 MySQL 索引失效的常見場景有以下 6 種：
- 聯(lián)合索引不滿足最左匹配原則。
- 模糊查詢最前面的為不確定匹配字符。
- 索引列參與了運(yùn)算。
- 索引列使用了函數(shù)。
- 索引列存在類型轉(zhuǎn)換。
- 索引列使用 is not null 查詢。
分享標(biāo)題：面試突擊：什么情況會(huì)導(dǎo)致MySQL索引失效？
分享網(wǎng)址：http://m.5511xx.com/article/dhcdcge.html
