MySQL高級進階：索引優(yōu)化

一. 索引介紹

1.1 什么是Mysql索引

MySQL官方對于索引的定義：索引是幫助MySQL高效獲取數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)。

MySQL在存儲數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)中還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種引用(指向)表中的數(shù)據(jù)，這樣我們就可以通過數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)的高級查找算法來快速找到我們想要的數(shù)據(jù)。而這種數(shù)據(jù)結(jié)構(gòu)就是索引。

簡單理解為“排好序的可以快速查找數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)”。

1.2 索引數(shù)據(jù)結(jié)構(gòu)

下圖是二叉樹的索引方式：

二叉樹數(shù)據(jù)結(jié)構(gòu)的弊端：當極端情況下，數(shù)據(jù)遞增插入時，會一直向右插入，形成鏈表，查詢效率會降低。

MySQL中常用的的索引數(shù)據(jù)結(jié)構(gòu)有BTree索引(Myisam普通索引)，B+Tree索引（Innodb普通索引），Hash索引(memory存儲引擎)等等。

1.3 索引優(yōu)勢

提高數(shù)據(jù)檢索的效率，降低數(shù)據(jù)庫的IO成本。

通過索引對數(shù)據(jù)進行排序，降低數(shù)據(jù)排序的成本，降低了CPU的消耗。

1.4 索引劣勢

索引實際上也是一張表，保存了主鍵和索引的字段，并且指向?qū)嶓w表的記錄，所以索引也是需要占用空間的。

在索引大大提高查詢速度的同時，卻會降低表的更新速度，在對表進行數(shù)據(jù)增刪改的同時，MySQL不僅要更新數(shù)據(jù)，還需要保存一下索引文件。

每次更新添加了的索引列的字段，都會去調(diào)整因為更新帶來的減值變化后的索引的信息。

1.5 索引使用場景

哪些情況需要創(chuàng)建索引：

主鍵自動建立唯一索引

頻繁作為查詢條件的字段應該創(chuàng)建索引(where 后面的語句)

查詢中與其它表關聯(lián)的字段，外鍵關系建立索引

多字段查詢下傾向創(chuàng)建組合索引

查詢中排序的字段，排序字段若通過索引去訪問將大大提高排序速度

查詢中統(tǒng)計或者分組字段

哪些情況不推薦建立索引:

表記錄太少

經(jīng)常增刪改的表

Where條件里用不到的字段不建立索引

二. 索引分類

2.1 主鍵索引

表中的列設定為主鍵后，數(shù)據(jù)庫會自動建立主鍵索引。

單獨創(chuàng)建和刪除主鍵索引語法：

創(chuàng)建主鍵索引語法： alter table 表名 add primary key (字段);

刪除主鍵索引語法： alter table 表名 drop primary key;

2.2 唯一索引

表中的列創(chuàng)建了唯一約束時，數(shù)據(jù)庫會自動建立唯一索引。

單獨創(chuàng)建和刪除唯一索引語法：

創(chuàng)建唯一索引語法：alter table 表名 add unique 索引名(字段)；

刪除唯一索引語法：drop index 索引名 on 表名;

2.3 單值索引

即一個索引只包含單個列，一個表可以有多個單值索引。

建表時可隨表一起建立單值索引

單獨創(chuàng)建和刪除單值索引語法：

創(chuàng)建單值索引：alter table 表名 add index 索引名(字段);

刪除單值索引：drop index 索引名 on 表名;

2.4 復合索引

即一個索引包含多個列。

建表時可隨表一起建立復合索引

單獨創(chuàng)建和刪除復合索引語法：

創(chuàng)建復合索引：alter table 表名 add index 索引名(字段,字段2);

刪除復合索引：drop index 索引名 on 表名;

三. 性能分析

3.1 MySQL常見瓶頸

SQL中對大量數(shù)據(jù)進行比較、關聯(lián)、排序、分組時CPU的瓶頸。

實例內(nèi)存滿足不了緩存數(shù)據(jù)或排序等需要，導致產(chǎn)生大量的物理IO。查詢數(shù)據(jù)時掃描過多數(shù)據(jù)行，導致查詢效率低。

3.2 Explain

使用EXPLAIN關鍵字可以模擬優(yōu)化器執(zhí)行SQL查詢語句，從而知道MYSQL是如何處理SQL語句的?？梢杂脕矸治霾樵冋Z句或是表的結(jié)構(gòu)的性能瓶頸。其作用:

表的讀取順序

哪些索引可以使用

數(shù)據(jù)讀取操作的操作類型

那些索引被實際使用

表之間的引用

每張表有多少行被優(yōu)化器查詢

EXPLAIN關鍵字使用起來比較簡單： explain + SQL語句：

3.3 Explain重要字段名

建表語句：

CREATETABLEwk1(
idINT(10)AUTO_INCREMENT,
nameVARCHAR(100),
PRIMARYKEY(id)
);
CREATETABLEwk2(
idINT(10)AUTO_INCREMENT,
nameVARCHAR(100),
PRIMARYKEY(id)
);
CREATETABLE`weikai_test`(
`id`intNOTNULL,
`name`varchar(20)DEFAULTNULL,
`sex`varchar(20)DEFAULTNULL
)ENGINE=InnoDBDEFAULTCHARSET=utf8;

--每張表中添加一條數(shù)據(jù)
INSERTINTOwk1(name)VALUES(CONCAT('wk1_',FLOOR(1+RAND()*1000)));

INSERTINTOwk2(content)VALUES(CONCAT('wk2_',FLOOR(1+RAND()*1000)));

INSERTINTOweikai_test(`id`,`name`,`sex`)VALUES(1,'我','男');

id字段介紹：

select查詢的序列號，表示查詢中執(zhí)行select子句或操作表的順序。

id相同時，執(zhí)行順序由上至下。

id不同，如果是子查詢，id的序號會遞增，id值越大優(yōu)先級越高，則先被執(zhí)行。

id相同和不同都存在時，id相同的可以理解為一組，從上往下順序執(zhí)行，所有組中，id值越大，優(yōu)先級越高越先執(zhí)行。

代碼演示：

#id相同時，執(zhí)行順序是從上往下
explainselect*fromwk1,wk2,wk3wherewk1.id=wk2.idandwk2.id=wk3.id;

#id不相同時，執(zhí)行順序是從下往上
explainSELECTwk1.idfromwk1WHEREid=(SELECTwk2.idFROMwk2WHEREid=(SELECTweikai_test.idFROMweikai_testWHEREname="我"))

#id相同和id不同
explainSELECT*FROMwk1WHEREid=(selectwk2.idfromwk2,(select*fromweikai_test)s3wheres3.id=wk2.id);

select_type字段介紹：

查詢的類型，常見值有：

SIMPLE :簡單的 select 查詢,查詢中不包含子查詢或者UNION。

PRIMARY:查詢中若包含任何復雜的子部分，最外層查詢則被標記為Primary。

DERIVED:在FROM列表中包含的子查詢被標記為DERIVED(衍生),MySQL會遞歸執(zhí)行這些子查詢, 把結(jié)果放在臨時表里。(mysql5.7+過后)

SUBQUERY: 在SELECT或WHERE列表中包含了子查詢。

table字段介紹：

顯示這一行的數(shù)據(jù)是關于哪張表的。

type字段介紹：

訪問類型排序(從左往右索引效率越高)：

System：表只有一行記錄（等于系統(tǒng)表），這是const類型的特列，平時不會出現(xiàn)，這個也可以忽略不計。

Const：表示通過索引一次就找到了,const用于比較primary key或者unique索引。因為只匹配一行數(shù)據(jù)，所以很快，如將主鍵置于where列表中，MySQL就能將該查詢轉(zhuǎn)換為一個常量。

eq_ref：唯一性索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見于主鍵或唯一索引掃描。

ref：非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以他應該屬于查找和掃描的混合體。

range：只檢索給定范圍的行,使用一個索引來選擇行。key 列顯示使用了哪個索引 一般就是在你的where語句中出現(xiàn)了between、<、>、in等的查詢這種范圍掃描索引掃描比全表掃描要好，因為它只需要開始于索引的某一點，而結(jié)束語另一點，不用掃描全部索引。

Index：Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數(shù)據(jù)文件小。也就是說雖然all和Index都是讀全表，但index是從索引中讀取的，而all是從硬盤中讀的。

all：Full Table Scan，將遍歷全表以找到匹配的行。

從最好到最差依次是:system>const>eq_ref>ref>range>index>All 。一般來說，最好保證查詢能達到range級別，最好能達到ref。

possible_keys字段介紹：

顯示可能應用在這張表中的索引，一個或多個。查詢涉及到的字段上如果存在索引，則該索引將會被列出來，但不一定會被查詢實際使用上。

key字段介紹：

查詢中實際使用的索引，如果為NULL，則沒有使用索引。

key_len字段介紹：

查詢中實際使用索引的性能，越大越好。

ref字段介紹：

顯示索引的哪一列被使用了。哪些列或常量被用于查找索引列上的值。

rows字段介紹：

rows列顯示MySQL認為它執(zhí)行查詢時必須檢查的行數(shù)。一般越少越好。

extra字段介紹：

一些常見的重要的額外信息：

Using filesort：MySQL無法利用索引完成的排序操作稱為“文件排序”。

Using temporary：Mysql在對查詢結(jié)果排序時使用臨時表，常見于排序order by和分組查詢group by。

Using index：表示索引被用來執(zhí)行索引鍵值的查找，避免訪問了表的數(shù)據(jù)行，效率不錯。

Using where：表示使用了where過濾。

盡量避免Using filesort!

四. 查詢優(yōu)化

4.1 索引失效

最佳左前綴法則：如果索引了多列，要遵循最左前綴法則，指的是查詢從索引的最左前列開始并且不跳過索引中的列。

不在索引列上做任何計算、函數(shù)操作，會導致索引失效而轉(zhuǎn)向全表掃描。

存儲引擎不能使用索引中范圍條件右邊的列。

Mysql在使用不等于時無法使用索引會導致全表掃描。

is null可以使用索引，但是is not null無法使用索引。

like以通配符開頭會使索引失效導致全表掃描。

字符串不加單引號索引會失效。

使用or連接時索引失效。

代碼演示：

droptableifexistsstudents;
CREATETABLEstudents(
idINTPRIMARYKEYAUTO_INCREMENTCOMMENT"主鍵id",
snameVARCHAR(24)COMMENT'學生姓名',
ageINTCOMMENT'年齡',
scoreINTCOMMENT'分數(shù)',
timeTIMESTAMPCOMMENT'入學時間'
);

INSERTINTOstudents(sname,age,score,time)VALUES('小明',22,100,now());
INSERTINTOstudents(sname,age,score,time)VALUES('小紅',23,80,now());
INSERTINTOstudents(sname,age,score,time)VALUES('小綠',24,80,now());
INSERTINTOstudents(sname,age,score,time)VALUES('黑',23,70,now());

--添加復合索引
altertablestudentsaddindexidx_sname_age_score(sname,age,score);

--索引失效情況
explainselect*fromstudentswheresname="小明"andage=22andscore=100;
explainselect*fromstudentswheresname="小明"andage=22;
explainselect*fromstudentswheresname="小明";
explainselect*fromstudentswheresname="小明"andscore=80;
--不在索引列上做任何計算、函數(shù)操作，會導致索引失效而轉(zhuǎn)向全表掃描。
explainselect*fromstudentswhereleft(sname,2)="小明";
--存儲引擎不能使用索引中范圍條件右邊的列。
explainselect*fromstudentswheresname="小明"andage>22andscore=100;
--Mysql在使用不等于時無法使用索引會導致全表掃描。
explainselect*fromstudentswheresname!="小明";
--isnull可以使用索引，但是isnotnull無法使用索引。
explainselect*fromstudentswheresnameisnotnull;
--like以通配符開頭會使索引失效導致全表掃描。
explainselect*fromstudentswheresnamelike"明%";
--字符串不加單引號索引會失效。
explainselect*fromstudentswheresname=123;
--使用or連接時索引失效。
explainselect*fromstudentswheresname="小明"orage=22;

4.2 復合索引練習

4.3 單表查詢優(yōu)化

代碼演示：

--單表查詢優(yōu)化
CREATETABLEIFNOTEXISTSarticle(
idINT(10)PRIMARYKEYAUTO_INCREMENT,
author_idINT(10)NOTNULL,
category_idINT(10)NOTNULL,
viewsINT(10)NOTNULL,
commentsINT(10)NOTNULL,
titleVARBINARY(255)NOTNULL,
contentTEXTNOTNULL
);

INSERTINTOarticle(author_id,category_id,views,comments,title,content)VALUES
(1,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

#1.查詢category_id為1的，且comments大于1的情況下，views最多的id和author_id的信息
explainselectid,author_id
fromarticle
wherecategory_id=1andcomments>1orderbyviewsdesclimit1;
#2.建立索引
altertablearticleaddindexidx_ccv(category_id,comments,views);
#3.再次測試
explainselectid,author_id
fromarticle
wherecategory_id=1andcomments>1orderbyviewsdesclimit1;
#4.重新創(chuàng)建索引這里保證兩個索引之間沒有其他的索引列使key_len效率最高
dropindexidx_ccvonarticle;
altertablearticleaddindexidx_cv(category_id,views);
#5.再次測試
explainselectid,author_id
fromarticle
wherecategory_id=1andcomments>1orderbyviewsdesclimit1;

4.4 關聯(lián)查詢優(yōu)化

內(nèi)連接時，mysql會自動把小結(jié)果集的選為驅(qū)動表，所以大表的字段最好加上索引。左外連接時，左表會全表掃描，所以右邊大表字段最好加上索引，右外連接同理。我們最好保證被驅(qū)動表上的字段建立了索引。

4.5 排序優(yōu)化

盡量避免使用Using FileSort方式排序。

order by語句使用索引最左前列或使用where子句與order by子句條件組合滿足索引最左前列。

where子句中如果出現(xiàn)索引范圍查詢會導致order by索引失效。

4.6 分組優(yōu)化

代碼演示：

droptableifexistsstudents;
CREATETABLEstudents(
idINTPRIMARYKEYAUTO_INCREMENTCOMMENT"主鍵id",
snameVARCHAR(24)COMMENT'學生姓名',
ageINTCOMMENT'年齡',
scoreINTCOMMENT'分數(shù)',
timeTIMESTAMPCOMMENT'入學時間'
);

INSERTINTOstudents(sname,age,score,time)VALUES('小明',22,100,now());
INSERTINTOstudents(sname,age,score,time)VALUES('小紅',23,80,now());
INSERTINTOstudents(sname,age,score,time)VALUES('小綠',24,80,now());
INSERTINTOstudents(sname,age,score,time)VALUES('黑',23,70,now());

--分組優(yōu)化
altertablestudentsaddindexidx_sas(sname,age,score);
explainselectcount(*),sname
fromstudents
wheresname="小明"andage>22
GROUPBYscore;

4.7 慢查詢?nèi)罩?/p>

介紹：MySQL的慢查詢?nèi)罩臼荕ySQL提供的一種日志記錄，他用來記錄在MySQL中響應時間超過閥值的語句，具體指運行時間超過long_query_time值的SQL，則會被記錄到慢查詢?nèi)罩局?。可以由它來查看哪些SQL超出了我們最大忍耐時間值。

默認情況下，MySQL數(shù)據(jù)庫沒有開啟慢查詢?nèi)罩?，需要手動設置參數(shù)：

查看是否開啟：show variables like '%slow_query_log%';

開啟日志：set global slow_query_log = 1;

設置時間: set global long_query_time = 1;

查看時間: SHOW VARIABLES LIKE 'long_query_time%';

查看超時的sql記錄日志：Mysql的數(shù)據(jù)文件夾下/data/...-slow.log;在Navicat中輸入show variables like '%slow_query_log%命令'，就可以得到文件目錄;

代碼演示：

--建表語句
DROPTABLEIFEXISTSperson;
CREATETABLEperson(
PIDint(11)AUTO_INCREMENTCOMMENT'編號',
PNAMEvarchar(50)COMMENT'姓名',
PSEXvarchar(10)COMMENT'性別',
PAGEint(11)COMMENT'年齡',
SALdecimal(7,2)COMMENT'工資',
PRIMARYKEY(PID)
);
--創(chuàng)建存儲過程
createprocedureinsert_person(inmax_numint(10))
begin
declareiintdefault0;
setautocommit=0;
repeat
seti=i+1;
insertintoperson(PID,PNAME,PSEX,PAGE,SAL)values(i,concat('test',floor(rand()*10000000)),IF(RAND()>0.5,'男','女'),FLOOR((RAND()*100)+10),FLOOR((RAND()*19000)+1000));
untili=max_num
endrepeat;
commit;
end;
--調(diào)用存儲過程
callinsert_person(30000);

--慢查詢?nèi)罩?--查看是否開啟：showvariableslike'%slow_query_log%';
showvariableslike'%slow_query_log%';
--開啟日志：setglobalslow_query_log=1;
setglobalslow_query_log=1;
--設置時間:setgloballong_query_time=1;
setgloballong_query_time=3;
--查看時間:SHOWVARIABLESLIKE'long_query_time%';
SHOWVARIABLESLIKE'long_query_time%';

select*fromperson;

結(jié)果：

注意：非調(diào)優(yōu)場景下，一般不建議啟動改參數(shù)，慢查詢?nèi)罩局С謱⑷罩居涗泴懭胛募?，開啟慢查詢?nèi)罩緯蚨嗷蛏賻硪欢ǖ男阅苡绊憽?/p>

審核編輯：劉清