索引失效

準(zhǔn)備數(shù)據(jù)：

CREATE TABLE `dept` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`deptName` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	ceo INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

CREATE TABLE `emp` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`empno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`deptId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
	#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `t_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1;

1、計(jì)算、函數(shù)導(dǎo)致索引失效

-- 顯示查詢分析
EXPLAIN SELECT * FROM emp WHERE emp.name  LIKE 'abc%';
EXPLAIN SELECT * FROM emp WHERE LEFT(emp.name,3) = 'abc'; --索引失效

2、LIKE以%開(kāi)頭索引失效

EXPLAIN SELECT * FROM emp WHERE name LIKE '%ab%'; --索引失效

拓展：Alibaba《Java開(kāi)發(fā)手冊(cè)》

【強(qiáng)制】頁(yè)面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來(lái)解決。

3、不等于(!= 或者<>)索引失效

EXPLAIN SELECT * FROM emp WHERE emp.name = 'abc' ;
EXPLAIN SELECT * FROM emp WHERE emp.name <> 'abc' ; --索引失效

4、IS NOT NULL 和 IS NULL

EXPLAIN SELECT * FROM emp WHERE emp.name IS NULL;
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL; --索引失效

注意：當(dāng)數(shù)據(jù)庫(kù)中的數(shù)據(jù)的索引列的NULL值達(dá)到比較高的比例的時(shí)候，即使在IS NOT NULL 的情況下 MySQL的查詢優(yōu)化器會(huì)選擇使用索引，此時(shí)type的值是range（范圍查詢）

-- 將 id>20000 的數(shù)據(jù)的 name 值改為 NULL
UPDATE emp SET `name` = NULL WHERE `id` > 20000;

-- 執(zhí)行查詢分析，可以發(fā)現(xiàn) IS NOT NULL 使用了索引
-- 具體多少條記錄的值為NULL可以使索引在IS NOT NULL的情況下生效，由查詢優(yōu)化器的算法決定
EXPLAIN SELECT * FROM emp WHERE emp.name IS NOT NULL;

測(cè)試完將name的值改回來(lái)

UPDATE emp SET `name` = rand_string(6) WHERE `id` > 20000;

5、類(lèi)型轉(zhuǎn)換導(dǎo)致索引失效

EXPLAIN SELECT * FROM emp WHERE name='123'; 
EXPLAIN SELECT * FROM emp WHERE name= 123; --索引失效

6、全值匹配我最?lèi)?ài)

準(zhǔn)備：

-- 首先刪除之前創(chuàng)建的索引
CALL proc_drop_index("atguigudb","emp");

問(wèn)題：為以下查詢語(yǔ)句創(chuàng)建哪種索引效率最高

-- 查詢分析
EXPLAIN SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 執(zhí)行SQL
SELECT * FROM emp WHERE emp.age = 30 and deptid = 4 AND emp.name = 'abcd';
-- 查看執(zhí)行時(shí)間
SHOW PROFILES;

創(chuàng)建索引并重新執(zhí)行以上測(cè)試：

-- 創(chuàng)建索引：分別創(chuàng)建以下三種索引的一種，并分別進(jìn)行以上查詢分析
CREATE INDEX idx_age ON emp(age);
CREATE INDEX idx_age_deptid ON emp(age,deptid);
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

結(jié)論：可以發(fā)現(xiàn)最高效的查詢應(yīng)用了聯(lián)合索引 idx_age_deptid_name

7、最佳左前綴法則

準(zhǔn)備：

-- 首先刪除之前創(chuàng)建的索引
CALL proc_drop_index("atguigudb","emp");
-- 創(chuàng)建索引
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);

問(wèn)題：以下這些SQL語(yǔ)句能否命中 idx_age_deptid_name 索引，可以匹配多少個(gè)索引字段

測(cè)試：

• 如果索引了多列，要遵守最左前綴法則。即查詢從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列。
• 過(guò)濾條件要使用索引，必須按照索引建立時(shí)的順序，依次滿足，一旦跳過(guò)某個(gè)字段，索引后面的字段都無(wú)法被使用。

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.name = 'abcd' ;
-- EXPLAIN結(jié)果：
-- key_len：5 只使用了age索引
-- 索引查找的順序?yàn)?age、deptid、name，查詢條件中不包含deptid，無(wú)法使用deptid和name索引

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN結(jié)果：
-- type： ALL， 執(zhí)行了全表掃描
-- key_len： NULL， 索引失效
-- 索引查找的順序?yàn)?age、deptid、name，查詢條件中不包含age，無(wú)法使用整個(gè)索引

EXPLAIN SELECT * FROM emp WHERE emp.age = 30 AND emp.deptid=1 AND emp.name = 'abcd';
-- EXPLAIN結(jié)果：
-- 索引查找的順序?yàn)?age、deptid、name，匹配所有索引字段

EXPLAIN SELECT * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' AND emp.age = 30;
-- EXPLAIN結(jié)果：
-- 索引查找的順序?yàn)?age、deptid、name，匹配所有索引字段

8、索引中范圍條件右邊的列失效

準(zhǔn)備：

-- 首先刪除之前創(chuàng)建的索引
CALL proc_drop_index("atguigudb","emp");

問(wèn)題：為以下查詢語(yǔ)句創(chuàng)建哪種索引效率最高

EXPLAIN SELECT * FROM emp WHERE emp.age=30 AND emp.deptId>1000 AND emp.name = 'abc'; 

測(cè)試1：

-- 創(chuàng)建索引并執(zhí)行以上SQL語(yǔ)句的EXPLAIN
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,`name`);
-- key_len：10， 只是用了 age 和 deptid索引，name失效

注意：當(dāng)我們修改deptId的范圍條件的時(shí)候，例如deptId>100，那么整個(gè)索引失效，MySQL的優(yōu)化器基于成本計(jì)算后認(rèn)為沒(méi)必要使用索引了，所以就進(jìn)行了全表掃描。（注意：因?yàn)楸碇械臄?shù)據(jù)是隨機(jī)生成的，因此實(shí)際測(cè)試中根據(jù)具體數(shù)據(jù)的不同測(cè)試的結(jié)果也會(huì)不一樣，最終是否使用索引由優(yōu)化器決定）

測(cè)試2：

-- 創(chuàng)建索引并執(zhí)行以上SQL語(yǔ)句的EXPLAIN（將deptid索引的放在最后）
CREATE INDEX idx_age_name_deptid ON emp(age,`name`,deptid);
-- 使用了完整的索引

補(bǔ)充：以上兩個(gè)索引都存在的時(shí)候，MySQL優(yōu)化器會(huì)自動(dòng)選擇最好的方案文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-508057.html

到了這里，關(guān)于面試官：講講MySql索引失效的幾種情況的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！