一. 索引介紹

1.1 什么是Mysql索引

MySQL官方對于索引的定義：索引是幫助MySQL高效獲取數據的數據結構。

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

簡單理解為“排好序的可以快速查找數據的數據結構”。

1.2 索引數據結構

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

二叉樹數據結構的弊端：當極端情況下，數據遞增插入時，會一直向右插入，形成鏈表，查詢效率會降低。

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

1.3 索引優勢

提高數據檢索的效率，降低數據庫的IO成本。

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

1.4 索引劣勢

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

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

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

1.5 索引使用場景

哪些情況需要創建索引：

主鍵自動建立唯一索引

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

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

多字段查詢下傾向創建組合索引

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

查詢中統計或者分組字段

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

表記錄太少

經常增刪改的表

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

二. 索引分類

2.1 主鍵索引

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

單獨創建和刪除主鍵索引語法：

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

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

2.2 唯一索引

表中的列創建了唯一約束時，數據庫會自動建立唯一索引。

單獨創建和刪除唯一索引語法：

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

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

2.3 單值索引

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

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

單獨創建和刪除單值索引語法：

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

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

2.4 復合索引

即一個索引包含多個列。

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

單獨創建和刪除復合索引語法：

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

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

三. 性能分析

3.1 MySQL常見瓶頸

SQL中對大量數據進行比較、關聯、排序、分組時CPU的瓶頸。

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

3.2 Explain

使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句，從而知道MYSQL是如何處理SQL語句的。可以用來分析查詢語句或是表的結構的性能瓶頸。其作用:

表的讀取順序

哪些索引可以使用

數據讀取操作的操作類型

那些索引被實際使用

表之間的引用

每張表有多少行被優化器查詢

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;

--每張表中添加一條數據
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查詢的序列號，表示查詢中執行select子句或操作表的順序。

id相同時，執行順序由上至下。

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

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

代碼演示：

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

#id不相同時，執行順序是從下往上
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會遞歸執行這些子查詢, 把結果放在臨時表里。(mysql5.7+過后)

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

table字段介紹：

顯示這一行的數據是關于哪張表的。

type字段介紹：

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

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

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

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

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

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

Index：Full Index Scan，index與ALL區別為index類型只遍歷索引樹。這通常比ALL快，因為索引文件通常比數據文件小。也就是說雖然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認為它執行查詢時必須檢查的行數。一般越少越好。

extra字段介紹：

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

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

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

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

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

盡量避免Using filesort!

四. 查詢優化

4.1 索引失效

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

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

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

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

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

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

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

使用or連接時索引失效。

代碼演示：

droptableifexistsstudents;
CREATETABLEstudents(
idINTPRIMARYKEYAUTO_INCREMENTCOMMENT"主鍵id",
snameVARCHAR(24)COMMENT'學生姓名',
ageINTCOMMENT'年齡',
scoreINTCOMMENT'分數',
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;
--不在索引列上做任何計算、函數操作，會導致索引失效而轉向全表掃描。
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 單表查詢優化

代碼演示：

--單表查詢優化
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.重新創建索引這里保證兩個索引之間沒有其他的索引列使key_len效率最高
dropindexidx_ccvonarticle;
altertablearticleaddindexidx_cv(category_id,views);
#5.再次測試
explainselectid,author_id
fromarticle
wherecategory_id=1andcomments>1orderbyviewsdesclimit1;

4.4 關聯查詢優化

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

4.5 排序優化

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

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

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

4.6 分組優化

代碼演示：

droptableifexistsstudents;
CREATETABLEstudents(
idINTPRIMARYKEYAUTO_INCREMENTCOMMENT"主鍵id",
snameVARCHAR(24)COMMENT'學生姓名',
ageINTCOMMENT'年齡',
scoreINTCOMMENT'分數',
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_sas(sname,age,score);
explainselectcount(*),sname
fromstudents
wheresname="小明"andage>22
GROUPBYscore;

4.7 慢查詢日志

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

默認情況下，MySQL數據庫沒有開啟慢查詢日志，需要手動設置參數：

查看是否開啟：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的數據文件夾下/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)
);
--創建存儲過程
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;
--調用存儲過程
callinsert_person(30000);

--慢查詢日志
--查看是否開啟：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;

結果：

注意：非調優場景下，一般不建議啟動改參數，慢查詢日志支持將日志記錄寫入文件，開啟慢查詢日志會或多或少帶來一定的性能影響。

審核編輯：劉清