前言

??本文是《java拉勾高薪訓(xùn)練營(yíng)》中的mysql章節(jié)的內(nèi)容回顧復(fù)習(xí)窖张，主要是對(duì)MySql的索引原理進(jìn)行復(fù)習(xí)整理，以便日骋献蹋回顧

1 索引類型

??索引可以提升查詢速度宿接，會(huì)影響where查詢，以及order by排序
??MySQL索引類型如下：

■ 從索引存儲(chǔ)結(jié)構(gòu)劃分：B Tree索引辕录、Hash索引睦霎、FULLTEXT全文索引、R Tree索引
■ 從應(yīng)用層次劃分：普通索引踏拜、唯一索引碎赢、主鍵索引、復(fù)合索引
■ 從索引鍵值類型劃分：主鍵索引速梗、輔助索引（二級(jí)索引）
■ 從數(shù)據(jù)存儲(chǔ)和索引鍵值邏輯關(guān)系劃分：聚集索引（聚簇索引）肮塞、非聚集索引（非聚簇索引）

1.1 普通索引

??最基本的索引類型，基于普通字段創(chuàng)建的索引姻锁，沒有任何限制枕赵。
??創(chuàng)建普通索引的方法如下：

■ CREATE INDEX <索引的名字> ON tablename (字段名);
■ ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
■ CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

1.2 唯一索引

??與"普通索引"類似，不同的就是：索引字段的值必須唯一拷窜，但允許有空值 。
??在創(chuàng)建或修改表時(shí)追加唯一約束月帝，就會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的唯一索引簸搞。
??創(chuàng)建唯一索引的方法如下：

■ CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
■ ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
■ CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

1.3 主鍵索引

??它是一種特殊的唯一索引，不允許有空值般卑。
??在創(chuàng)建或修改表時(shí)追加主鍵約束即可饲梭，每個(gè)表只能有一個(gè)主鍵。
??創(chuàng)建主鍵索引的方法如下：

■ CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );
■ ALTER TABLE tablename ADD PRIMARY KEY (字段名);

1.4 復(fù)合索引

??單一索引是指索引列為一列的情況茫死，即新建索引的語句只實(shí)施在一列上吧雹；用戶可以在多個(gè)列上建立索引妒潭，這種索引叫做組復(fù)合索引（組合索引）悴能。
??復(fù)合索引可以代替多個(gè)單一索引，相比多個(gè)單一索引復(fù)合索引所需的開銷更小雳灾。
??索引同時(shí)有兩個(gè)概念叫做窄索引和寬索引漠酿，窄索引是指索引列為1-2列的索引，寬索引也就是索引列超過2列的索引谎亩，設(shè)計(jì)索引的一個(gè)重要原則就是能用窄索引不用寬索引炒嘲，因?yàn)檎饕冉M合索引更有效。
??創(chuàng)建組合索引的方法如下：

■ CREATE INDEX <索引的名字> ON tablename (字段名1匈庭，字段名2...);
■ ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1夫凸，字段名2...);
■ CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

??復(fù)合索引使用注意事項(xiàng)：

■ 何時(shí)使用復(fù)合索引阱持，要根據(jù)where條件建索引夭拌，注意不要過多使用索引，過多使用會(huì)對(duì)更新操作效率有很大影響衷咽。
■ 如果表已經(jīng)建立了(col1鸽扁，col2)，就沒有必要再單獨(dú)建立（col1）兵罢；
■ 如果現(xiàn)在有(col1)索引献烦，如果查詢需要col1和col2條件，可以建立(col1,col2)復(fù)合索引卖词，對(duì)于查詢有一定提高巩那。

1.5 全文索引

??查詢操作在數(shù)據(jù)量比較少時(shí)，可以使用like模糊查詢此蜈，但是對(duì)于大量的文本數(shù)據(jù)檢索即横，效率很低。如果使用全文索引裆赵，查詢速度會(huì)比like快很多倍东囚。
??在MySQL 5.6 以前的版本，只有MyISAM存儲(chǔ)引擎支持全文索引战授，從MySQL 5.6開始MyISAM和InnoDB存儲(chǔ)引擎均支持页藻。
??創(chuàng)建全文索引的方法如下：

■ CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
■ ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
■ CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

??和常用的like模糊查詢不同，全文索引有自己的語法格式植兰，使用 match 和 against 關(guān)鍵字份帐，比如

 select * from user where match(name) against('aaa');  

??全文索引使用注意事項(xiàng)：

??■ 全文索引必須在字符串、文本字段上建立楣导。
??■ 全文索引字段值必須在最小字符和最大字符之間的才會(huì)有效废境。（innodb：3-84；myisam：4-84）
??■ 全文索引字段值要進(jìn)行切詞處理，按syntax字符進(jìn)行切割噩凹，例如b+aaa巴元，切分成b和aaa
??■ 全文索引匹配查詢，默認(rèn)使用的是等值匹配驮宴，例如a匹配a逮刨，不會(huì)匹配ab,ac。如果想匹配可以在布爾模式下搜索a*

 select * from user where match(name) against('a*' in boolean mode);  

2 索引原理

??MySQL官方對(duì)索引定義：是存儲(chǔ)引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)幻赚。需要額外開辟空間和數(shù)據(jù)維護(hù)工作禀忆。

??■ 索引是物理數(shù)據(jù)頁存儲(chǔ)，在數(shù)據(jù)文件中（InnoDB落恼，ibd文件）箩退，利用數(shù)據(jù)頁(page)存儲(chǔ)。
??■ 索引可以加快檢索速度佳谦，但是同時(shí)也會(huì)降低增刪改操作速度戴涝，索引維護(hù)需要代價(jià)。

??索引涉及的理論知識(shí)：二分查找法钻蔑、Hash和B+Tree

2.1 二分查找法

??二分查找法也叫作折半查找法啥刻，它是在有序數(shù)組中查找指定數(shù)據(jù)的搜索算法。它的優(yōu)點(diǎn)是等值查詢咪笑、范圍查詢性能優(yōu)秀可帽，缺點(diǎn)是更新數(shù)據(jù)、新增數(shù)據(jù)窗怒、刪除數(shù)據(jù)維護(hù)成本高映跟。

2.2 Hash結(jié)構(gòu)

??Hash底層實(shí)現(xiàn)是由Hash表來實(shí)現(xiàn)的，是根據(jù)鍵值 <key,value> 存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)扬虚。非常適合根據(jù)key查找value值努隙，也就是單個(gè)key查詢，或者說等值查詢。其結(jié)構(gòu)如下所示：

hash底層原理

??從上面結(jié)構(gòu)可以看出，Hash索引可以方便的提供等值查詢热监，但是對(duì)于范圍查詢就需要全表掃描了。
??Hash索引在MySQL 中Hash結(jié)構(gòu)主要應(yīng)用在Memory原生的Hash索引 躬存、InnoDB 自適應(yīng)哈希索引。
??InnoDB提供的自適應(yīng)哈希索引功能強(qiáng)大舀锨，接下來重點(diǎn)描述下InnoDB 自適應(yīng)哈希索引岭洲。

??InnoDB自適應(yīng)哈希索引是為了提升查詢效率，InnoDB存儲(chǔ)引擎會(huì)監(jiān)控表上各個(gè)索引頁的查詢雁竞，當(dāng)InnoDB注意到某些索引值訪問非常頻繁時(shí)钦椭，會(huì)在內(nèi)存中基于B+Tree索引再創(chuàng)建一個(gè)哈希索引，使得內(nèi)存中的 B+Tree 索引具備哈希索引的功能碑诉，即能夠快速定值訪問頻繁訪問的索引頁

??InnoDB自適應(yīng)哈希索引：在使用Hash索引訪問時(shí)彪腔，一次性查找就能定位數(shù)據(jù)，等值查詢效率要優(yōu)于B+Tree进栽。
??自適應(yīng)哈希索引的建立使得InnoDB存儲(chǔ)引擎能自動(dòng)根據(jù)索引頁訪問的頻率和模式自動(dòng)地為某些熱點(diǎn)頁建立哈希索引來加速訪問德挣。
??另外InnoDB自適應(yīng)哈希索引的功能，用戶只能選擇開啟或關(guān)閉功能快毛，無法進(jìn)行人工干涉格嗅。

show engine innodb status \G;   
show variables like '%innodb_adaptive%';  

2.3 B+Tree結(jié)構(gòu)

??MySQL數(shù)據(jù)庫索引采用的是B+Tree結(jié)構(gòu)，在B-Tree結(jié)構(gòu)上做了優(yōu)化改造唠帝。
??B-Tree結(jié)構(gòu)

■ 索引值和data數(shù)據(jù)分布在整棵樹結(jié)構(gòu)中
■ 每個(gè)節(jié)點(diǎn)可以存放多個(gè)索引值及對(duì)應(yīng)的data數(shù)據(jù)
■ 樹節(jié)點(diǎn)中的多個(gè)索引值從左到右升序排列

B-樹結(jié)構(gòu)

??B樹的搜索：從根節(jié)點(diǎn)開始屯掖，對(duì)節(jié)點(diǎn)內(nèi)的索引值序列采用二分法查找，如果命中就結(jié)束查找襟衰。沒有命中會(huì)進(jìn)入子節(jié)點(diǎn)重復(fù)查找過程贴铜，直到所對(duì)應(yīng)的的節(jié)點(diǎn)指針為空，或已經(jīng)是葉子節(jié)點(diǎn)了才結(jié)束瀑晒。

??B+Tree結(jié)構(gòu)

■ 非葉子節(jié)點(diǎn)不存儲(chǔ)data數(shù)據(jù)绍坝，只存儲(chǔ)索引值，這樣便于存儲(chǔ)更多的索引值
■ 葉子節(jié)點(diǎn)包含了所有的索引值和data數(shù)據(jù)
■ 葉子節(jié)點(diǎn)用指針連接苔悦，提高區(qū)間的訪問性能

B+樹結(jié)構(gòu)

??相比B樹轩褐，B+樹進(jìn)行范圍查找時(shí)，只需要查找定位兩個(gè)節(jié)點(diǎn)的索引值玖详，然后利用葉子節(jié)點(diǎn)的指針進(jìn)行遍歷即可把介。而B樹需要遍歷范圍內(nèi)所有的節(jié)點(diǎn)和數(shù)據(jù)，顯然B+Tree效率高竹宋。

2.4 聚簇索引和輔助索引

??聚簇索引和非聚簇索引：B+Tree的葉子節(jié)點(diǎn)存放主鍵索引值和行記錄就屬于聚簇索引劳澄；如果索引值和行記錄分開存放就屬于非聚簇索引。
??主鍵索引和輔助索引：B+Tree的葉子節(jié)點(diǎn)存放的是主鍵字段值就屬于主鍵索引蜈七；如果存放的是非主鍵值就屬于輔助索引（二級(jí)索引）秒拔。

在InnoDB引擎中，主鍵索引采用的就是聚簇索引結(jié)構(gòu)存儲(chǔ)飒硅。

2.4.1 聚簇索引（聚集索引）

??聚簇索引是一種數(shù)據(jù)存儲(chǔ)方式砂缩，InnoDB的聚簇索引就是按照主鍵順序構(gòu)建 B+Tree結(jié)構(gòu)。
??B+Tree的葉子節(jié)點(diǎn)就是行記錄三娩，行記錄和主鍵值緊湊地存儲(chǔ)在一起庵芭。 這也意味著 InnoDB 的主鍵索引就是數(shù)據(jù)表本身，它按主鍵順序存放了整張表的數(shù)據(jù)雀监，占用的空間就是整個(gè)表數(shù)據(jù)量的大小双吆。
??通常說的主鍵索引就是聚集索引眨唬。

??InnoDB的表要求必須要有聚簇索引：

■ 如果表定義了主鍵，則主鍵索引就是聚簇索引
■ 如果表沒有定義主鍵好乐，則第一個(gè)非空unique列作為聚簇索引
■ 否則InnoDB會(huì)從建一個(gè)隱藏的row-id作為聚簇索引

2.4.2 輔助索引

??InnoDB輔助索引匾竿，也叫作二級(jí)索引，是根據(jù)索引列構(gòu)建 B+Tree結(jié)構(gòu)蔚万。但在 B+Tree 的葉子節(jié)點(diǎn)中只存了索引列和主鍵的信息岭妖。
??二級(jí)索引占用的空間會(huì)比聚簇索引小很多， 通常創(chuàng)建輔助索引就是為了提升查詢效率反璃。
??一個(gè)表InnoDB只能創(chuàng)建一個(gè)聚簇索引昵慌，但可以創(chuàng)建多個(gè)輔助索引

聚簇索引和非聚簇索引

2.4.3 非聚簇索引

??與InnoDB表存儲(chǔ)不同，MyISAM數(shù)據(jù)表的索引文件和數(shù)據(jù)文件是分開的淮蜈，被稱為非聚簇索引結(jié)構(gòu)斋攀。

在這里插入圖片描述

3 索引分析與優(yōu)化

3.1 EXPLAIN

??MySQL 提供了一個(gè) EXPLAIN 命令，它可以對(duì) SELECT 語句進(jìn)行分析梧田，并輸出 SELECT 執(zhí)行的詳細(xì)信息蜻韭，供開發(fā)人員有針對(duì)性的優(yōu)化。例如：

EXPLAIN SELECT * from user WHERE id < 3;  

EXPLAIN 命令的輸出內(nèi)容大致如下：

在這里插入圖片描述

（1）select_type
?? 表示查詢的類型柿扣。常用的值如下：

■ SIMPLE ： 表示查詢語句不包含子查詢或union
■ PRIMARY：表示此查詢是最外層的查詢
■ UNION：表示此查詢是UNION的第二個(gè)或后續(xù)的查詢
■ DEPENDENT UNION：UNION中的第二個(gè)或后續(xù)的查詢語句肖方，使用了外面查詢結(jié)果
■ UNION RESULT：UNION的結(jié)果
■ SUBQUERY：SELECT子查詢語句
■ DEPENDENT SUBQUERY：SELECT子查詢語句依賴外層查詢的結(jié)果。

（2）type

??表示存儲(chǔ)引擎查詢數(shù)據(jù)時(shí)采用的方式未状。比較重要的一個(gè)屬性俯画，通過它可以判斷出查詢是全表掃描還是基于索引的部分掃描。
??常用屬性值如下司草，從上至下效率依次增強(qiáng)艰垂。

■ ALL：表示全表掃描，性能最差埋虹。
■ index：表示基于索引的全表掃描猜憎，先掃描索引再掃描全表數(shù)據(jù)。
■ range：表示使用索引范圍查詢搔课。使用>胰柑、>=、<爬泥、<=柬讨、in等等。
■ ref：表示使用非唯一索引進(jìn)行單值查詢袍啡。
■ eq_ref：一般情況下出現(xiàn)在多表join查詢踩官，表示前面表的每一個(gè)記錄，都只能匹配后面表的一行結(jié)果境输。
■ const：表示使用主鍵或唯一索引做等值查詢蔗牡，常量查詢颖系。
■ NULL：表示不用訪問表，速度最快辩越。

（3）possible_keys
??表示查詢時(shí)能夠使用到的索引集晚。注意并不一定會(huì)真正使用，顯示的是索引名稱区匣。
（4）key
??表示查詢時(shí)真正使用到的索引，顯示的是索引名稱蒋院。
（5）rows
??MySQL查詢優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息亏钩，估算SQL要查詢到結(jié)果需要掃描多少行記錄。
??原則上rows是越少效率越高欺旧，可以直觀的了解到SQL效率高低姑丑。
（6）key_len
??表示查詢使用了索引的字節(jié)數(shù)量〈怯眩可以判斷是否全部使用了組合索引栅哀。
key_len的計(jì)算規(guī)則如下：

■ 字符串長(zhǎng)度跟字符集有關(guān)：latin1=1、gbk=2称龙、utf8=3留拾、utf8mb4=4
??char(n)：n*字符集長(zhǎng)度
??varchar(n)：n * 字符集長(zhǎng)度 + 2字節(jié)
■ 數(shù)值類型
??TINYINT：1個(gè)字節(jié)
??SMALLINT：2個(gè)字節(jié)
??MEDIUMINT：3個(gè)字節(jié)
??INT、FLOAT：4個(gè)字節(jié)
??BIGINT鲫尊、DOUBLE：8個(gè)字節(jié)
■ 時(shí)間類型
??DATE：3個(gè)字節(jié)
??TIMESTAMP：4個(gè)字節(jié)
??DATETIME：8個(gè)字節(jié)
■ 字段屬性
??NULL屬性占用1個(gè)字節(jié)痴柔，如果一個(gè)字段設(shè)置了NOT NULL，則沒有此項(xiàng)

（7）Extra
??Extra表示很多額外的信息疫向，各種操作會(huì)在Extra提示相關(guān)信息咳蔚，常見幾種如下：

■ Using where
??表示查詢需要通過索引回表查詢數(shù)據(jù)。
■ Using index
??表示查詢需要通過索引搔驼，索引就可以滿足所需數(shù)據(jù)谈火。
■ Using filesort
??表示查詢出來的結(jié)果需要額外排序，數(shù)據(jù)量小在內(nèi)存舌涨，大的話在磁盤糯耍，因此有Using filesort，建議優(yōu)化囊嘉。
■ Using temprorary
??查詢使用到了臨時(shí)表谍肤，一般出現(xiàn)于去重、分組等操作哗伯。

3.2 回表查詢

??在之前介紹過荒揣，InnoDB索引有聚簇索引和輔助索引。聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄焊刹，InnoDB必須要有系任，且只有一個(gè)恳蹲。
??輔助索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值和索引字段值，通過輔助索引無法直接定位行記錄俩滥，通常情況下嘉蕾，需要掃碼兩遍索引樹。
??先通過輔助索引定位主鍵值霜旧，然后再通過聚簇索引定位行記錄错忱，這就叫做回表查詢，它的性能比掃一遍索引樹低挂据。
??總結(jié)：通過索引查詢主鍵值以清，然后再去聚簇索引查詢記錄信息

3.3 覆蓋索引

[圖片上傳失敗...(image-96dd90-1600651655525)]

??MySQL官網(wǎng)，表達(dá)了：只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)崎逃，無需回表掷倔，速度更快，這就叫做索引覆蓋个绍。
??實(shí)現(xiàn)索引覆蓋最常見的方法就是：將被查詢的字段勒葱，建立到組合索引。

3.4 最左前綴原則

??復(fù)合索引使用時(shí)遵循最左前綴原則巴柿，最左前綴顧名思義凛虽，就是最左優(yōu)先，即查詢中使用到最左邊的列广恢，那么查詢就會(huì)使用到索引涩维，如果從索引的第二列開始查找，索引將失效袁波。

在這里插入圖片描述

3.5 LIKE查詢

??面試題：MySQL在使用like模糊查詢時(shí)瓦阐，索引能不能起作用？
??回答：MySQL在使用Like模糊查詢時(shí)篷牌，索引是可以被使用的睡蟋，只有把%字符寫在后面才會(huì)使用到索引。

select * from user where name like '%o%'; //不起作用 
select * from user where name like 'o%'; //起作用 
select * from user where name like '%o'; //不起作用

3.6 NULL查詢

??面試題：如果MySQL表的某一列含有NULL值枷颊，那么包含該列的索引是否有效戳杀？
??對(duì)MySQL來說，NULL是一個(gè)特殊的值夭苗，從概念上講信卡，NULL意味著“一個(gè)未知值”，它的處理方式與其他值有些不同题造。
??比如：不能使用=傍菇，<，>這樣的運(yùn)算符界赔，對(duì)NULL做算術(shù)運(yùn)算的結(jié)果都是NULL丢习，count時(shí)不會(huì)包括NULL行等牵触，NULL比空字符串需要更多的存儲(chǔ)空間等。
??NULL列需要增加額外空間來記錄其值是否為NULL咐低。對(duì)于MyISAM表揽思，每一個(gè)空列額外占用一位，四舍五入到最接近的字節(jié)
??雖然MySQL可以在含有NULL的列上使用索引见擦，但NULL和其他數(shù)據(jù)還是有區(qū)別的钉汗，不建議列上允許為NULL。最好設(shè)置NOT NULL鲤屡，并給一個(gè)默認(rèn)值损痰，比如0和 ‘’ 空字符串等，如果是datetime類型执俩，也可以設(shè)置系統(tǒng)當(dāng)前時(shí)間或某個(gè)固定的特殊值，例如'1970-01-01 00:00:00'癌刽。

3.7 索引與排序

??MySQL查詢支持filesort和index兩種方式的排序役首。
??filesort是先把結(jié)果查出，然后在緩存或磁盤進(jìn)行排序操作显拜，效率較低衡奥。
??index是指利用索引自動(dòng)實(shí)現(xiàn)排序，不需另做排序操作远荠，效率會(huì)比較高矮固。
??filesort有兩種排序算法：雙路排序和單路排序。

??■ 雙路排序：需要兩次磁盤掃描讀取譬淳，最終得到用戶數(shù)據(jù)档址。第一次將排序字段讀取出來，然后排序邻梆；第二次去讀取其他字段數(shù)據(jù)守伸。
??■ 單路排序：從磁盤查詢所需的所有列數(shù)據(jù)，然后在內(nèi)存排序?qū)⒔Y(jié)果返回浦妄。如果查詢數(shù)據(jù)超出緩存sort_buffer尼摹，會(huì)導(dǎo)致多次磁盤讀取操作，并創(chuàng)建臨時(shí)表剂娄，最后產(chǎn)生了多次IO蠢涝，反而會(huì)增加負(fù)擔(dān)。
??■ 解決方案：少使用select *阅懦；增加sort_buffer_size容量和max_length_for_sort_data容量和二。

??如果我們Explain分析SQL，結(jié)果中Extra屬性顯示Using filesort耳胎，表示使用了filesort排序方式儿咱，需要優(yōu)化庭砍。
??如果Extra屬性顯示Using index時(shí)，表示覆蓋索引混埠，也表示所有操作在索引上完成怠缸，也可以使用index排序方式，建議大家盡可能采用覆蓋索引钳宪。
??以下幾種情況揭北，會(huì)使用index方式的排序。
??（1）ORDER BY 子句索引列組合滿足索引最左前列

explain select id from user order by id; //對(duì)應(yīng)(id)吏颖、(id,name)索引有效  

??（2）WHERE子句+ORDER BY子句索引列組合滿足索引最左前列

explain select id from user where age=18 order by name; //對(duì)應(yīng) (age,name)索引  

??以下幾種情況搔体，會(huì)使用filesort方式的排序。
??（1）對(duì)索引列同時(shí)使用了ASC和DESC

 explain select id from user order by age asc,name desc; //對(duì)應(yīng)(age,name)索引  

??（2）WHERE子句和ORDER BY子句滿足最左前綴半醉，但where子句使用了范圍查詢（例如>疚俱、<、in等）

 explain select id from user where age>10 order by name; //對(duì)應(yīng)(age,name)索引

??（3）ORDER BY或者WHERE+ORDER BY索引列沒有滿足索引最左前列

explain select id from user order by name; //對(duì)應(yīng)(age,name)索引

??（4）使用了不同的索引缩多，MySQL每次只采用一個(gè)索引呆奕，ORDER BY涉及了兩個(gè)索引

 explain select id from user order by name,age; //對(duì)應(yīng)(name)、(age)兩個(gè)索引

??（5）WHERE子句與ORDER BY子句衬吆，使用了不同的索引

 explain select id from user where name='tom' order by age; //對(duì)應(yīng) (name)梁钾、(age)索引

??（6）WHERE子句或者ORDER BY子句中索引列使用了表達(dá)式，包括函數(shù)表達(dá)式

explain select id from user order by abs(age); //對(duì)應(yīng)(age)索引

4 查詢優(yōu)化

4.1 慢查詢定位

4.1.1 開啟慢查詢?nèi)罩?/h3>

??查看 MySQL 數(shù)據(jù)庫是否開啟了慢查詢?nèi)罩竞吐樵內(nèi)罩疚募拇鎯?chǔ)位置的命令如下：

 SHOW VARIABLES LIKE 'slow_query_log%'

??通過如下命令開啟慢查詢?nèi)罩荆?/p>

 SET global slow_query_log = ON;   
 SET global slow_query_log_file = 'OAK-slow.log';   
 SET global log_queries_not_using_indexes = ON;   
 SET long_query_time = 10;  

??long_query_time：指定慢查詢的閥值逊抡，單位秒姆泻。如果SQL執(zhí)行時(shí)間超過閥值，就屬于慢查詢記錄到日志文件中冒嫡。
??log_queries_not_using_indexes：表示會(huì)記錄沒有使用索引的查詢SQL拇勃。前提是slow_query_log的值為ON，否則不會(huì)奏效

4.1.2 查看慢查詢?nèi)罩?/h3>

??（1）文本方式查看
??直接使用文本編輯器打開slow.log日志即可孝凌。

在這里插入圖片描述

■ time：日志記錄的時(shí)間
■ User@Host：執(zhí)行的用戶及主機(jī)
■ Query_time：執(zhí)行的時(shí)間
■ Lock_time：鎖表時(shí)間
■ Rows_sent：發(fā)送給請(qǐng)求方的記錄數(shù)潜秋，結(jié)果數(shù)量
■ Rows_examined：語句掃描的記錄條數(shù) SET
■ timestamp：語句執(zhí)行的時(shí)間點(diǎn)
■ select....：執(zhí)行的具體的SQL語句

（2）使用mysqldumpslow查看
??MySQL 提供了一個(gè)慢查詢?nèi)罩痉治龉ぞ適ysqldumpslow，可以通過該工具分析慢查詢?nèi)罩緝?nèi)容胎许。
??在 MySQL bin目錄下執(zhí)行下面命令可以查看該使用格式峻呛。

 perl mysqldumpslow.pl --help  

運(yùn)行如下命令查看慢查詢?nèi)罩拘畔ⅲ?/p>

 perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log  

??除了使用mysqldumpslow工具，也可以使用第三方分析工具辜窑，比如pt-query-digest钩述、mysqlsla等。

4.2 慢查詢優(yōu)化

4.2.1 索引和慢查詢

??（1）如何判斷是否為慢查詢穆碎？
??MySQL判斷一條語句是否為慢查詢語句牙勘，主要依據(jù)SQL語句的執(zhí)行時(shí)間，它把當(dāng)前語句的執(zhí)行時(shí)間跟 long_query_time 參數(shù)做比較，如果語句的執(zhí)行時(shí)間 > long_query_time方面，就會(huì)把這條執(zhí)行語句記錄到慢查詢?nèi)罩纠锩妗?br> ??long_query_time 參數(shù)的默認(rèn)值是 10s放钦，該參數(shù)值可以根據(jù)自己的業(yè)務(wù)需要進(jìn)行調(diào)整。
??（2）如何判斷是否應(yīng)用了索引恭金？
?? SQL語句是否使用了索引操禀，可根據(jù)SQL語句執(zhí)行過程中有沒有用到表的索引，可通過 explain命令分析查看横腿，檢查結(jié)果中的 key 值颓屑，是否為NULL。
??（3）應(yīng)用了索引是否一定快耿焊？
??下面我們來看看下面語句的 explain 的結(jié)果揪惦，你覺得這條語句有用上索引嗎？比如

 select * from user where id>0;  

??雖然使用了索引罗侯，但是還是從主鍵索引的最左邊的葉節(jié)點(diǎn)開始向右掃描整個(gè)索引樹器腋，進(jìn)行了全表掃描，此時(shí)索引就失去了意義钩杰。
??（4）總結(jié)
??查詢是否使用索引纫塌，只是表示一個(gè)SQL語句的執(zhí)行過程；而是否為慢查詢榜苫，是由它執(zhí)行的時(shí)間決定的护戳，也就是說是否使用了索引和是否是慢查詢兩者之間沒有必然的聯(lián)系翎冲。
??我們?cè)谑褂盟饕龝r(shí)垂睬，不要只關(guān)注是否起作用，應(yīng)該關(guān)心索引是否減少了查詢掃描的數(shù)據(jù)行數(shù)抗悍，如果掃描行數(shù)減少了驹饺，效率才會(huì)得到提升。對(duì)于一個(gè)大表缴渊，不止要?jiǎng)?chuàng)建索引赏壹，還要考慮索引過濾性，過濾性好衔沼，執(zhí)行速度才會(huì)快蝌借。

4.2.2 提高索引過濾性

??假如有一個(gè)5000萬記錄的用戶表，通過sex='男'索引過濾后指蚁，還需要定位3000萬菩佑，SQL執(zhí)行速度也不會(huì)很快。
??其實(shí)這個(gè)問題涉及到索引的過濾性凝化，比如1萬條記錄利用索引過濾后定位10條稍坯、100條、1000條搓劫，那他們過濾性是不同的瞧哟。
??索引過濾性與索引字段混巧、表的數(shù)據(jù)量、表設(shè)計(jì)結(jié)構(gòu)都有關(guān)系勤揩。
??下面我們看一個(gè)案例：

表：student 
字段：id,name,sex,age 
造數(shù)據(jù)：insert into student (name,sex,age) select name,sex,age from student; 
SQL案例：select * from student where age=18 and name like '張%';（全表掃 描）

優(yōu)化1

alter table student add index(name); //追加name索引

優(yōu)化2

alter table student add index(age,name); //追加age,name索引

優(yōu)化3

??可以看到咧党，index condition pushdown 優(yōu)化的效果還是很不錯(cuò)的。再進(jìn)一步優(yōu)化雄可，我們可以把名字的第一個(gè)字和年齡做一個(gè)聯(lián)合索引凿傅，這里可以使用 MySQL 5.7 引入的虛擬列來實(shí)現(xiàn)。

//為user表添加first_name虛擬列数苫，以及聯(lián)合索引(first_name,age) 
alter table student add first_name varchar(2) generated always as (left(name, 1)), add index(first_name, age); 
explain select * from student where first_name='張' and age=18;

??慢查詢?cè)蚩偨Y(jié)

■ 全表掃描：explain分析type屬性all
■ 全索引掃描：explain分析type屬性index
■ 索引過濾性不好：靠索引字段選型聪舒、數(shù)據(jù)量和狀態(tài)、表設(shè)計(jì)
■ 頻繁的回表查詢開銷：盡量少用select *虐急，使用覆蓋索引

4.3 分頁查詢優(yōu)化

4.3.1 一般性分頁

??一般的分頁查詢使用簡(jiǎn)單的 limit 子句就可以實(shí)現(xiàn)箱残。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows  

■ 第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量，注意從0開始止吁；
■ 第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目被辑；
■ 如果只給定一個(gè)參數(shù)，它表示返回最大的記錄行數(shù)目敬惦；

??思考1：如果偏移量固定盼理，返回記錄量對(duì)執(zhí)行時(shí)間有什么影響？

 select * from user limit 10000,1;
 select * from user limit 10000,10; 
 select * from user limit 10000,100; 
 select * from user limit 10000,1000; 
 select * from user limit 10000,10000;

?? 結(jié)果：在查詢記錄時(shí)俄删，返回記錄量低于100條宏怔，查詢時(shí)間基本沒有變化，差距不大畴椰。隨著查詢記錄 量越大臊诊，所花費(fèi)的時(shí)間也會(huì)越來越多。

??思考2：如果查詢偏移量變化斜脂，返回記錄數(shù)固定對(duì)執(zhí)行時(shí)間有什么影響抓艳？

 select * from user limit 1,100;
 select * from user limit 10,100; 
 select * from user limit 100,100;
 select * from user limit 1000,100; 
 select * from user limit 10000,100;

?? 結(jié)果：在查詢記錄時(shí)，如果查詢記錄量相同帚戳，偏移量超過100后就開始隨著偏移量增大玷或，查詢時(shí)間急劇的增加。（這種分頁查詢機(jī)制片任，每次都會(huì)從數(shù)據(jù)庫第一條記錄開始掃描偏友，越往后查詢?cè)铰也樵兊臄?shù)據(jù)越多蚂踊，也會(huì)拖慢總查詢速度约谈。）

4.3.2 分頁優(yōu)化方案

??第一步：利用覆蓋索引優(yōu)化

select * from user limit 10000,100; 
select id from user limit 10000,100;

??第二步：利用子查詢優(yōu)化

select * from user limit 10000,100; 
select * from user where id>= (select id from user limit 10000,1) limit 100;

?? 原因：使用了id做主鍵比較(id>=)，并且子查詢使用了覆蓋索引進(jìn)行優(yōu)化。