欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

淺談mysql哪些情況會(huì)導(dǎo)致索引失效

 更新時(shí)間:2021年11月18日 16:22:04   作者:海南小黑仔  
索引在我們使用MySQL數(shù)據(jù)庫(kù)時(shí)可以極大的提高查詢效率,然而,有時(shí)候因?yàn)槭褂蒙系囊恍╄Υ镁蜁?huì)導(dǎo)致索引的失效,無法達(dá)到我們使用索引的預(yù)期效果,本文就介紹一下幾種索引失效的方法

下面有一些培訓(xùn)教學(xué)機(jī)構(gòu)的口訣和我個(gè)人的一些總結(jié):
為了講解以下索引內(nèi)容,我們先建立一個(gè)臨時(shí)的表 test02

CREATE TABLE `sys_user` (
  `id` varchar(64) NOT NULL COMMENT '主鍵',
  `name` varchar(64) DEFAULT NULL COMMENT '名字',
  `age` int(64) DEFAULT NULL COMMENT '年齡',
  `pos` varchar(64) DEFAULT NULL COMMENT '職位',
  PRIMARY KEY (`id`),
  KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用戶表';

這個(gè)表有四個(gè)字段 主鍵 、名字、年齡、職位

下面我們來講解第一個(gè)口訣:
1.全值匹配我最愛
2.最佳左前綴法則(重要)

全值匹配意思就是聯(lián)立的復(fù)合索引的順序和個(gè)數(shù)要和檢索的條件順序和個(gè)數(shù)相同。
最佳左前綴法則是指,如果索引了多列,要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列
下面我們給這個(gè)表建立一個(gè)復(fù)合索引

ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);

以下是我們的檢索語句:

SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';

在這里插入圖片描述

我們通過在檢索語句前面加關(guān)鍵字 EXLAIN,可以知道是否使用的索引

(1)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
(2)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 ;
(3)EXPLAIN SELECT * FROM sys_user WHERE name='小明'  AND pos ='java';

在這里插入圖片描述

在這里插入圖片描述

在這里插入圖片描述

通過展示的結(jié)果我們可以知道,第一個(gè)復(fù)合索引的三個(gè)字段我們都用了,第二個(gè)復(fù)合索引我們只用到兩個(gè)字段,第三個(gè)復(fù)合索引我們只用到一個(gè)字段。三個(gè)語句我們都用到索引,顯然第一種是最優(yōu)的。

我們?cè)倏纯茨姆N情況會(huì)失效:

(4)EXPLAIN SELECT * FROM sys_user WHERE age = 22;
(5)EXPLAIN SELECT * FROM sys_user WHERE pos ='java';
(6)EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos ='java';

在這里插入圖片描述

在這里插入圖片描述

在這里插入圖片描述

以上三種情況都變成了全表掃描,原因是違反了最左左前綴原則,因?yàn)閺?fù)合索引最左邊的是name,當(dāng)檢索條件name沒在前面索引將失效,第一種情況滿足了全值匹配,第二種滿足了兩個(gè)字段name和age,第三種因?yàn)橹粷M足了name,所以索引只用到name。

3.不在索引列上做任何操作(計(jì)算、函數(shù)(自動(dòng)或手動(dòng))類型轉(zhuǎn)換),會(huì)使索引失效轉(zhuǎn)為全表掃描

(7)EXPLAIN SELECT * FROM sys_user WHERE  LEFT(name,1)='小明';

在這里插入圖片描述

第七種情況失效是因?yàn)樗饕凶隽擞?jì)算或者函數(shù)的操作,導(dǎo)致了全表掃描。

4.存儲(chǔ)引擎不能使用索引中范圍條件右邊的列
可能大家關(guān)看上面的文字不知道是什么意思,下面我們執(zhí)行一下查詢語句就清楚了

(8)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age < 22 AND pos ='java';

在這里插入圖片描述

從上圖我們可以知道type變成了范圍級(jí)別,也就是說age<22之后的pos字段的索引失效了。

5.盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致),減少select * 的使用
這個(gè)就是字面意思,查詢具體的字段比查詢*效率更高,下面我們坐一下對(duì)比

(9)EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';
(10)EXPLAIN SELECT name,age,pos FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';

在這里插入圖片描述

在這里插入圖片描述

6.mysql在使用不等于(!= 或者<>)的時(shí)候無法使用索引會(huì)導(dǎo)致全表掃描
(!= 或者<>)通常會(huì)匹配到大量數(shù)據(jù),當(dāng)使用索引的花銷大于全表掃描時(shí),mysql則會(huì)放棄使用索引而選擇全表掃描

(11)EXPLAIN SELECT * FROM sys_user WHERE name !='小明'

在這里插入圖片描述

結(jié)果顯示索引失效導(dǎo)致了全表掃描

7.is null,is not null 也無法使用索引
is null,is not null 通常會(huì)匹配到大量數(shù)據(jù),當(dāng)使用索引的花銷大于全表掃描時(shí),mysql則會(huì)放棄使用索引而選擇全表掃描

(12)EXPLAIN SELECT * FROM sys_user WHERE name is not null

在這里插入圖片描述

8.like以通配符開頭('%abc…')mysql索引會(huì)失效變成全表掃描的操作,(%寫右邊則可以避免索引失效,如果業(yè)務(wù)實(shí)在需要'%abc…%'則可以用覆蓋索引避免索引失效)

(13)EXPLAIN SELECT * FROM sys_user WHERE name like '%明%'
(14)EXPLAIN SELECT * FROM sys_user WHERE name like '明%'
(15)EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%'

在這里插入圖片描述

在這里插入圖片描述

在這里插入圖片描述

從上面的結(jié)果,第一種索引失效,第二種只寫右邊的%則可以避免索引失效,第三種如果業(yè)務(wù)實(shí)在需要‘%abc…%'這種sql,則可以用覆蓋索引解決索引失效的問題

9.字符串不加單引號(hào)索引會(huì)失效

(16)EXPLAIN SELECT * FROM sys_user WHERE name=222;

在這里插入圖片描述

因?yàn)闄z索字符串是必須加單引號(hào),上面用用了222是int類型,mysql在檢索的時(shí)候會(huì)判斷name是varchar的類型會(huì)將222轉(zhuǎn)換為'222'進(jìn)行檢索,索引列發(fā)生了類型轉(zhuǎn)換,故索引失效。

10.少用or,用它連接時(shí)會(huì)索引失效

(16)EXPLAIN SELECT * FROM sys_user WHERE name='小明' or age = 22;

在這里插入圖片描述

到此這篇關(guān)于淺談mysql哪些情況會(huì)導(dǎo)致索引失效的文章就介紹到這了,更多相關(guān)mysql 索引失效內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

最新評(píng)論