前 言
?? 作者簡(jiǎn)介：半舊518，長(zhǎng)跑型選手，立志堅(jiān)持寫10年博客，專注于java后端
?? 專欄簡(jiǎn)介：mysql基礎(chǔ)、進(jìn)階，主要講解mysql數(shù)據(jù)庫(kù)sql刷題、進(jìn)階知識(shí)，包括索引、數(shù)據(jù)庫(kù)調(diào)優(yōu)、分庫(kù)分表等
?? 文章簡(jiǎn)介：本文將介紹索引失效的10種情況及原理，絕對(duì)不需要死記硬背，建議收藏備用。
?? 相關(guān)推薦：

• MySql進(jìn)階索引篇01——深度講解索引的數(shù)據(jù)結(jié)構(gòu)：B+樹
• Mysql進(jìn)階索引篇02——InnoDB存儲(chǔ)引擎的數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)
• Mysql進(jìn)階索引篇03——2個(gè)新特性，11+7條設(shè)計(jì)原則教你創(chuàng)建索引
• Mysql進(jìn)階優(yōu)化篇01——四萬字詳解數(shù)據(jù)庫(kù)性能分析工具(深入、全面、詳細(xì)，收藏備用)
  
• 大廠SQL面試真題大全

1?? 數(shù)據(jù)庫(kù)調(diào)優(yōu)的場(chǎng)景

上一篇mysql進(jìn)階優(yōu)化篇，我們介紹了數(shù)據(jù)庫(kù)的性能分析工具，知道了怎么發(fā)現(xiàn)數(shù)據(jù)庫(kù)的性能問題，這一篇博客我們將介紹索引失效的10種情況及原理

先來了解下可能需要進(jìn)行數(shù)據(jù)庫(kù)優(yōu)化的場(chǎng)景。

• 索引失效(按準(zhǔn)則編寫、調(diào)整sql)
• 沒有充分利用到索引(建立索引)
• 關(guān)聯(lián)查詢太多的JOIN(JOIN查詢性能與表的數(shù)目成指數(shù)相關(guān)，一般不超過三張，否則需要進(jìn)行sql優(yōu)化或者進(jìn)行反范式化設(shè)計(jì)，增加必要的冗余)
• 服務(wù)器調(diào)優(yōu)與各個(gè)參數(shù)的設(shè)置，如緩存、線程數(shù)等(修改my.conf)
• 數(shù)據(jù)過高(已經(jīng)在軟件層面充分調(diào)優(yōu)，但仍不能面對(duì)高并發(fā)場(chǎng)景，可以考慮分表分庫(kù)分散服務(wù)器壓力)

接下來我們介紹下sql查詢優(yōu)化。雖然sql查詢優(yōu)化技術(shù)很多，但是大致不離 物理查查詢優(yōu)化 和 邏輯查詢優(yōu)化 兩大塊。

• 物理查詢優(yōu)化：通過索引和表連接方式進(jìn)行優(yōu)化
• 邏輯查詢優(yōu)化：通過sql語句的等價(jià)代換，實(shí)現(xiàn)數(shù)據(jù)庫(kù)查詢的優(yōu)化。

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

學(xué)員表插50萬 條， 班級(jí)表插1萬條。

(1)建表

#班級(jí)表
CREATE TABLE `class` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`className` VARCHAR(30) DEFAULT NULL,
`address` VARCHAR(40) DEFAULT NULL,
`monitor` INT NULL ,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

#學(xué)員表
CREATE TABLE `student` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`stuno` INT NOT NULL ,
`name` VARCHAR(20) DEFAULT NULL,
`age` INT(3) DEFAULT NULL,
`classId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
#CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

(2)設(shè)置參數(shù)

命令開啟：允許創(chuàng)建函數(shù)設(shè)置：

set global log_bin_trust_function_creators=1;   
# 不加global只是當(dāng)前窗口有效。

(3)創(chuàng)建函數(shù)

保證每條數(shù)據(jù)都不同。

#隨機(jī)產(chǎn)生字符串
DELIMITER //
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN  
DECLARE chars_str VARCHAR(100) DEFAULT
'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
DECLARE return_str VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
WHILE i < n DO 
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)); 
SET i = i + 1;
END WHILE;
RETURN return_str;
END //
DELIMITER ;

#假如要?jiǎng)h除
#drop function rand_string;

隨機(jī)產(chǎn)生班級(jí)編號(hào)

#用于隨機(jī)產(chǎn)生多少到多少的編號(hào)
DELIMITER //
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN 
DECLARE i INT DEFAULT 0; 
SET i = FLOOR(from_num +RAND()*(to_num - from_num+1))  ;
RETURN i; 
END //
DELIMITER ;

#假如要?jiǎng)h除
#drop function rand_num;

(4)創(chuàng)建存儲(chǔ)過程

創(chuàng)建往stu表中插入數(shù)據(jù)的存儲(chǔ)過程

#創(chuàng)建往stu表中插入數(shù)據(jù)的存儲(chǔ)過程
DELIMITER //
CREATE PROCEDURE insert_stu(  START INT , max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;   #設(shè)置手動(dòng)提交事務(wù)
	REPEAT  #循環(huán)
	SET i = i + 1;  #賦值
	INSERT INTO student (stuno, name ,age ,classId ) VALUES
	((START+i),rand_string(6),rand_num(1,50),rand_num(1,1000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;  #提交事務(wù)
END //
DELIMITER ;

#假如要?jiǎng)h除
#drop PROCEDURE insert_stu;

創(chuàng)建往class表中插入數(shù)據(jù)的存儲(chǔ)過程

#執(zhí)行存儲(chǔ)過程，往class表添加隨機(jī)數(shù)據(jù)
DELIMITER //
CREATE PROCEDURE `insert_class`( max_num INT )
BEGIN 
	DECLARE i INT DEFAULT 0; 
	SET autocommit = 0;  
	REPEAT 
	SET i = i + 1; 
	INSERT INTO class ( classname,address,monitor ) VALUES
	(rand_string(8),rand_string(10),rand_num(1,100000)); 
	UNTIL i = max_num 
	END REPEAT; 
	COMMIT;
END //
DELIMITER ;

#假如要?jiǎng)h除
#drop PROCEDURE insert_class;

(5)調(diào)用存儲(chǔ)過程

往class表添加1萬條數(shù)據(jù)

#執(zhí)行存儲(chǔ)過程，往class表添加1萬條數(shù)據(jù) 
CALL insert_class(10000);

往stu表添加50萬條數(shù)據(jù),這個(gè)時(shí)間會(huì)稍微有點(diǎn)長(zhǎng)，請(qǐng)耐心等待喲。

#執(zhí)行存儲(chǔ)過程，往stu表添加50萬條數(shù)據(jù) 
CALL insert_stu(100000,500000);

查詢下數(shù)據(jù)是否插入成功。

SELECT COUNT(*) FROM class;
SELECT COUNT(*) FROM student;

(6)刪除某表上的索引

創(chuàng)建刪除索引存儲(chǔ)過程。這是為了方便我們的學(xué)習(xí)，因?yàn)槲覀冊(cè)谘菔灸硞€(gè)索引的效果時(shí)，可能需要?jiǎng)h除其它索引，如果需要一個(gè)個(gè)手工刪除，就太費(fèi)勁了。

DELIMITER //
CREATE  PROCEDURE `proc_drop_index`(dbname VARCHAR(200),tablename VARCHAR(200))
BEGIN
   DECLARE done INT DEFAULT 0;
   DECLARE ct INT DEFAULT 0;
   DECLARE _index VARCHAR(200) DEFAULT '';
   DECLARE _cur CURSOR FOR  SELECT  index_name  FROM
information_schema.STATISTICS  WHERE table_schema=dbname AND table_name=tablename AND
seq_in_index=1 AND  index_name <>'PRIMARY' ;
#每個(gè)游標(biāo)必須使用不同的declare continue handler for not found set done=1來控制游標(biāo)的結(jié)束
   DECLARE  CONTINUE HANDLER FOR NOT FOUND set done=2 ;   
#若沒有數(shù)據(jù)返回,程序繼續(xù),并將變量done設(shè)為2
    OPEN _cur;
    FETCH _cur INTO _index;
    WHILE _index<>'' DO
       SET @str = CONCAT("drop index " , _index , " on " , tablename );
       PREPARE sql_str FROM @str ;
       EXECUTE sql_str;
       DEALLOCATE PREPARE sql_str;
       SET _index='';
       FETCH _cur INTO _index;
    END WHILE;
 CLOSE _cur;
END //
DELIMITER ;

執(zhí)行存儲(chǔ)過程(可以先保留不執(zhí)行)

CALL proc_drop_index("dbname","tablename");

3??索引失效的情況

這里我們以InnoDB的B+樹的索引結(jié)構(gòu)作為講解的重點(diǎn)，講解索引失效的案例(3.1講解索引最佳的實(shí)踐)。之所以會(huì)出現(xiàn)索引失效的情況，其實(shí)是因?yàn)槲覀兊膬?yōu)化器經(jīng)過了成本開銷的計(jì)算，決定不用索引。用不用索引都是優(yōu)化器說了算，Sql語句是否會(huì)使用索引，跟數(shù)據(jù)庫(kù)版本、數(shù)據(jù)量和數(shù)據(jù)選擇度都有關(guān)系。

3.1 全值匹配我最愛(索引最佳)

全值匹配可以充分的利用組合索引。

在沒有建立索引時(shí)會(huì)進(jìn)行數(shù)據(jù)查詢速度會(huì)比較慢。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

SQL_NO_CACHE表示不使用查詢緩存。

下圖是在沒有創(chuàng)建索引的情況下，第一條sql的執(zhí)行效果。其查詢時(shí)間是0.048s。

下面建立下索引。

CREATE INDEX idx_age ON student(age);

CREATE INDEX idx_age_classid ON student(age,classId);

CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);

??Q 上面三個(gè)索引有什么區(qū)別，為什么這么建立索引？
上面建立索引是與三條sql的使用場(chǎng)景對(duì)應(yīng)的，遵守了全值匹配的規(guī)則，就是說建立幾個(gè)復(fù)合索引字段，最好就用上幾個(gè)字段。且按照順序來用。

再次執(zhí)行查詢sql，就可以使用到索引idx_age。并且其查詢耗時(shí)會(huì)變短為0.024s。

執(zhí)行如下sql。選擇的索引則是:idx_age_classid。思考下為什么？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;

這是因?yàn)槲覀冊(cè)跇?gòu)建索引idx_age_classid的B+樹時(shí)，會(huì)先按照age排序，在按照calssId排序，對(duì)于這個(gè)sql來說，更加高效。

但是上面的索引可能不生效哦，在數(shù)據(jù)量較大的情況下，我們進(jìn)行全值匹配SELECT *，優(yōu)化器可能經(jīng)過計(jì)算發(fā)現(xiàn)，我們使用索引查詢所有的數(shù)據(jù)后，還需要對(duì)查找到的數(shù)據(jù)進(jìn)行回表操作，性能還不如全表掃描。這里我們沒有造這么多數(shù)據(jù)，所以就不演示效果咯。

3.2 不遵守最左前綴匹配原則

運(yùn)行如下sql。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name='abcd';

將使用索引idx_age。

下面的sql不會(huì)使用索引，因?yàn)槲覜]沒有創(chuàng)建classId或者name的索引。或者

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.name='abcd';

Q:為什么不會(huì)使用idx_age_classid索引？
索引idx_age_classid的B+樹會(huì)先使用age排序，在使用classId給age相同的數(shù)據(jù)排序，這個(gè)索引根本用不上喲。這就是下面的最左前綴原則。

在 MySQL 建立聯(lián)合索引時(shí)會(huì)遵守最佳左前綴匹配原則，即最左優(yōu)先，在檢索數(shù)據(jù)時(shí)從聯(lián)合索引的最左邊開始匹配。

MySQL 可以為多個(gè)字段創(chuàng)建索引，一個(gè)索引可以包括 16 個(gè)字段，對(duì)于多列字段，過濾條件要使用所以那必須按照索引建立時(shí)的順序，依次滿足，一旦跳過某個(gè)字段，索引后面的字段都無法使用。如果查詢條件中沒有使用這些字段中的第一個(gè)字段時(shí)，多列索引不會(huì)被使用。

??拓展：Alibaba《Java開發(fā)手冊(cè)》
索引文件具有 B-Tree 的最左前綴匹配特性，如果左邊的值未確定，那么無法使用此索引。

下面的sql查詢就是遵守這一原則的正確打開方式。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age = 30 AND student.classId=4 AND student.name='abcd';

思考：下面sql會(huì)不會(huì)使用索引呢？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classId=4 AND student.age = 30 AND student.name='abcd';

答案是會(huì)！因?yàn)閮?yōu)化器會(huì)執(zhí)行優(yōu)化的哦，會(huì)調(diào)整查詢條件的順序。不過在開發(fā)過程中我們還是要保持良好的開發(fā)習(xí)慣喲。

思考：刪去索引idx_age_classid和idx_age,只保留idx_age_classid_name

DROP INDEX idx_age_classid ON student;
DROP INDEX idx_age ON student;

執(zhí)行如下sql，會(huì)不會(huì)使用索引？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE  student.age = 30 AND student.name='abcd';

答案是會(huì)，但是只會(huì)用一部分。看看執(zhí)行結(jié)果。

使用了idx_age_classid_name，但是key_len是5，也就是說只使用了age部分的排序，因?yàn)閍ge是int類型，4個(gè)字節(jié)加上null值列表一共5個(gè)字節(jié)哦。想想就知道，B+樹是先按照age排序，再按照classid排序，最后按照name排序，因此不能跳過classId的排序直接就使用name的排序哦。

3.3 不按照遞增順序插入主鍵

對(duì)于一個(gè)使用 InnoDB 存儲(chǔ)引擎的表來說，在我們沒有顯式的創(chuàng)建索引時(shí)，表中的數(shù)據(jù)實(shí)際上都是存儲(chǔ)在 聚簇索引 的葉子節(jié)點(diǎn)的。而記錄又是存儲(chǔ)在數(shù)據(jù)頁中，數(shù)據(jù)頁和記錄又是按照 記錄主鍵值從小到大 的順序進(jìn)行排序，所以如果我們 插入 的記錄的 主鍵是依次增大 的話，那我們每插滿一個(gè)數(shù)據(jù)頁就換到下一個(gè)數(shù)據(jù)頁繼續(xù)插，而如果我們插入的 主鍵值忽大忽小 的話，就比較麻煩了，假設(shè)某個(gè)數(shù)據(jù)頁存儲(chǔ)的記錄已經(jīng)滿了，它存儲(chǔ)的主鍵值在 1~100 之間：

如果此時(shí)再插入一條主鍵值為 9 的記錄，那它插入的位置就如下圖:

可這個(gè)數(shù)據(jù)頁已經(jīng)滿了，再插進(jìn)來咋辦呢?我們需要把當(dāng)前 頁面分裂 成兩個(gè)頁面，把本頁中的一些記錄移動(dòng)到新創(chuàng)建的這個(gè)頁中。頁面分裂和記錄移位意味著什么？意味著：性能損耗！所以如果我們想盡量避免這樣無謂的性能損耗，最好讓插入的記錄的 主鍵值依次遞增 ，這樣就不會(huì)發(fā)生這樣的性能損耗了。 所以我們建議：讓主鍵具有 AUTO_INCREMENT ，讓存儲(chǔ)引擎自己為表生成主鍵，而不是我們手動(dòng)插入

我們自定義的主鍵列 id 擁有 AUTO_INCREMENT 屬性，在插入記錄時(shí)存儲(chǔ)引擎會(huì)自動(dòng)為我們填入自增的主鍵值。這樣的主鍵占用空間小，順序?qū)懭?，減少頁分裂。

??Tips：
我們一般將主鍵策略設(shè)置為自動(dòng)遞增AUTO_INCREMENT哦！(核心業(yè)務(wù)表除外，后面會(huì)介紹這種情況)

3.4 計(jì)算、函數(shù)、類型轉(zhuǎn)換（自動(dòng)或手動(dòng)）導(dǎo)致索引失效

思考：這兩條 sql 哪種寫法更好？

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

從執(zhí)行結(jié)果上說，上面sql執(zhí)行結(jié)果沒有區(qū)別。但是從運(yùn)行效率上說，第1條sql比之后的要好，因?yàn)榈谝粭l可以使用上索引！而因?yàn)榈诙l使用了函數(shù)，即使建立索引也會(huì)導(dǎo)致索引失效。

為何使用函數(shù)時(shí)優(yōu)化器會(huì)使索引失效呢？您想想，我們只是對(duì)student.name字段建立了索引，但并沒有對(duì)LEFT(student.name，3)建立索引，使用函數(shù)后的關(guān)鍵字跟我們建立的B+樹可對(duì)應(yīng)不來，怎么能使用B+樹優(yōu)化查詢呢？

3.5 類型轉(zhuǎn)換導(dǎo)致索引失效

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

# 使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

name = 123 發(fā)生類型轉(zhuǎn)換，索引失效，原因與使用函數(shù)也一樣，其實(shí)類型轉(zhuǎn)換就是使用了隱式的類型轉(zhuǎn)換函數(shù)。

3.6 范圍條件右邊的列索引失效

我們先調(diào)用下前面準(zhǔn)備的存儲(chǔ)過程刪除除主鍵索引外的其它索引。

CALL proc_drop_index('atguigu_db2','student');
SHOW INDEX FROM student;

創(chuàng)建聯(lián)合索引。

CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

執(zhí)行查詢。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.classId>20 AND student.name = 'abc' ;

執(zhí)行結(jié)果如下。

注意到key_len是10，說明值使用到了idx_age_classId_name索引中的age與classId部分，而name則沒有用上。這是因?yàn)閏lassId>20是范圍查詢，導(dǎo)致其右邊的列索引失效。

如果想要完全使用到索引，需要按如下方式創(chuàng)建索引：先寫等值查詢的列，再寫范圍查詢的列。

create index idx_age_name_classid on student(age,name,classid); 

??Q：為什么條件查詢會(huì)導(dǎo)致范圍條件后面的列索引失效？
比如說有三個(gè)字段 a b c，建立復(fù)合索引a_b_c
此時(shí)葉子節(jié)點(diǎn)的數(shù)據(jù)排序后可能為
(a=1 b=1 c=1) (a=1 b=2 c=1) (a=1 b=2 c=3)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
(a=3 b=0 c=1) (a=3 b=3 c=5) (a=3 b=8 c=6)
假設(shè)查找 select a,b,c from table where a = 2 and b = 5 and c = 2
此時(shí)先根據(jù)a = 2找到第二行的四條數(shù)據(jù)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根據(jù)b=5查到兩條
(a=2 b=5 c=1) (a=2 b=5 c=2)
最后根據(jù)c=2查到目標(biāo)數(shù)據(jù)
(a=2 b=5 c=2)
接下來 假設(shè)使用了范圍條件
select a,b,c from table where a = 2 and b >1 and c = 2
此時(shí)先根據(jù)a = 2找到第二行的四條數(shù)據(jù)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
然后根據(jù)b>1查到四條數(shù)據(jù)
(a=2 b=2 c=3) (a=2 b=2 c=5) (a=2 b=5 c=1) (a=2 b=5 c=2)
此時(shí)要查找c=2了 但是我們發(fā)現(xiàn) 這四條數(shù)據(jù)的c分別是
3,5,1,2 是無序的 所以索引失效了
總結(jié)：
因?yàn)榍耙粋€(gè)條件相同的情況下，后續(xù)列才會(huì)是有序的。

??Tips：
應(yīng)用開發(fā)中范圍查詢，例如：金額查詢，日期查詢往往都是范圍查詢。應(yīng)將查詢條件放置where語句最后。（創(chuàng)建的聯(lián)合索引中，務(wù)必把范圍設(shè)計(jì)到的字段寫在最后）

3.7 不等于（!= 或者 <>）索引失效

為name字段創(chuàng)建索引

CREATE INDEX idx_name ON student(NAME);

查看索引是否失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

執(zhí)行結(jié)果如下。

沒有失效！?。∵@個(gè)原因還不是特別明確，可能mysql高版本中優(yōu)化器又做了升級(jí)(畢竟不等于不過是等于的取反，確實(shí)可以實(shí)現(xiàn)優(yōu)化)？筆者的mysql版本為8.2.06，如果有知道的大佬可以在評(píng)論區(qū)留言討論。不過在實(shí)際生產(chǎn)或者面試中，這仍然可以作為一種需要關(guān)注的特殊情形。

3.8 is null可以使用索引，is not null無法使用索引

原因和原理一模一樣。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

同樣的，在低版本中索引會(huì)失效，高版本中，索引也不會(huì)失效哦。

??結(jié)論：最好在設(shè)計(jì)數(shù)據(jù)庫(kù)的時(shí)候就將 字段設(shè)置為 NOT NULL 約束。比如可以將 INT 類型的字段，默認(rèn)設(shè)置為 0。將字符串的默認(rèn)值設(shè)置為空字符串（“”）。
擴(kuò)展：同理，在查詢中使用 not like 也無法使用索引，導(dǎo)致全表掃描

3.9 like 以通配符 % 開頭索引失效

在使用 LIKE 關(guān)鍵字進(jìn)行查詢的查詢語句中，如果匹配字符串的第一個(gè)字符為“%”，索引就不會(huì)其作用。只有“%”不在第一個(gè)位置，索引才會(huì)起作用。

使用到索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%'; 

未使用到索引.

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

想想那顆b+樹，前面模糊了那么排序還有什么用？

??拓展：Alibaba《Java 開發(fā)手冊(cè)》
【強(qiáng)制】頁面搜索嚴(yán)禁左模糊或者全模糊，如果需要請(qǐng)走搜索引擎來解決。

3.10 OR前后存在非索引的列

在WHERE 子句中，如果在 OR 前的條件列進(jìn)行了索引，而在 OR 后的條件列沒有進(jìn)行索引，那么索引會(huì)失效。也就是說，OR 前后的兩個(gè)條件中的列都是索引時(shí)，查詢中才使用索引。

因?yàn)?OR 的含義就是兩個(gè)只要滿足一個(gè)即可，因此 只有一個(gè)條件列進(jìn)行了索引是沒有意義的，只要有條件列沒有進(jìn)行索引，就會(huì)進(jìn)行全表掃描，因此索引的條件列也會(huì)失效。

查詢語句使用 OR 關(guān)鍵字的情況：

#清除現(xiàn)有的索引
CALL proc_drop_index('mymysql', 'student')

# 創(chuàng)建索引
CREATE INDEX idx_age ON student(age);

# 未使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

這是因?yàn)閛r連接的查詢條件都需要查詢，如果一個(gè)使用索引，一個(gè)不用索引全表掃描，索引根本起不到優(yōu)化性能的作用。還不如只進(jìn)行一次全表掃描呢。

解決方式是給未使用索引的列創(chuàng)建索引。

# 再創(chuàng)建一個(gè)索引
CREATE INDEX idx_cid ON student(classid);

#使用到索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

又翻車了·。。?？磥磉@個(gè)情況還是得特別小心啊。

再來。

EXPLAIN SELECT SQL_NO_CACHE  age,classid FROM student WHERE age = 10 OR classid = 100;

總結(jié)：沒事別用select *。

3.11 數(shù)據(jù)庫(kù)和表的字符集不匹配

統(tǒng)一使用 utf8mb4（5.5.3版本以上支持）兼容性更好，統(tǒng)一字符集可以避免由于字符集轉(zhuǎn)換產(chǎn)生的亂碼。不同的 字符集 進(jìn)行比較前需要進(jìn)行 轉(zhuǎn)換 會(huì)造成索引失效。

4.索引一般性建議

假設(shè)，index(a,b,c)，下面羅列了一些值得被注意的索引應(yīng)用場(chǎng)景。

??建議
對(duì)于單列索引，盡量選擇針對(duì)當(dāng)前 query 過濾性更好的索引
在選擇組合索引的時(shí)候，當(dāng)前 query 中過濾性最好的字段在索引字段順序中，位置越靠前越好
在選擇組合索引的時(shí)候，盡量選擇能夠包含當(dāng)前 query 中的 where 子句中更多字段的索引
在選擇組合索引的時(shí)候，如果某個(gè)字段可能出現(xiàn)范圍查詢時(shí)，盡量把這個(gè)字段放在索引次序的最后面。
總之，書寫 SQL 語句時(shí)，盡量避免造成索引失效的情況。

工欲善其事，必先利其器”。要想成為工作上的數(shù)據(jù)庫(kù)高手，面試時(shí)的題霸，獨(dú)步江湖，就必須拿到一份"武林秘籍"。

我個(gè)人強(qiáng)推?？途W(wǎng)：找工作神器|大廠java面經(jīng)匯總|超全筆試題庫(kù)

推薦理由：
1.刷題題庫(kù)，題目特別全面，刷爆筆試再也不擔(dān)心

鏈接： 找工作神器|大廠java面經(jīng)匯總|超全筆試題庫(kù)
2.超全面試題、成體系、高質(zhì)量，還有AI模擬面試黑科技

鏈接： 工作神器|大廠java面經(jīng)匯總|超全筆試題庫(kù)
3.超多面經(jīng)，大廠面經(jīng)很多

4.內(nèi)推機(jī)會(huì)，大廠招聘特別多

鏈接： 找工作神器|大廠java面經(jīng)匯總|超全筆試題庫(kù)
5.大廠真題，直接拿到大廠真實(shí)題庫(kù)，而且和許多大廠都有直接合作，題目通過率高有機(jī)會(huì)獲得大廠內(nèi)推資格。

鏈接： 找工作神器|大廠java面經(jīng)匯總|超全筆試題庫(kù)文章來源地址http://www.zghlxwxcb.cn/news/detail-782195.html

到了這里，關(guān)于Mysql進(jìn)階優(yōu)化篇02——索引失效的10種情況及原理的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！