1. 索引類(lèi)型

索引可以提升查詢(xún)速度，會(huì)影響where查詢(xún)召庞，以及order by排序。MySQL索引類(lèi)型如下：

• 從索引存儲(chǔ)結(jié)構(gòu)劃分：B Tree索引、Hash索引盼砍、FULLTEXT全文索引、R Tree索引
• 從應(yīng)用層次劃分：普通索引逝她、唯一索引浇坐、主鍵索引、復(fù)合索引
• 從索引鍵值類(lèi)型劃分：主鍵索引黔宛、輔助索引（二級(jí)索引）
• 從數(shù)據(jù)存儲(chǔ)和索引鍵值邏輯關(guān)系劃分：聚集索引（聚簇索引）近刘、非聚集索引（非聚簇索引）

1.1 普通索引

這是最基本的索引類(lèi)型，基于普通字段建立的索引，沒(méi)有任何限制觉渴。
創(chuàng)建普通索引的方法如下：

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

1.2 唯一索引

與"普通索引"類(lèi)似介劫，不同的就是：索引字段的值必須唯一，但允許有空值 案淋。在創(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ù)合索引

單一索引是指索引列為一列的情況黔帕，即新建索引的語(yǔ)句只實(shí)施在一列上；用戶(hù)可以在多個(gè)列上建立索引蹈丸，這種索引叫做組復(fù)合索引（組合索引）成黄。復(fù)合索引可以代替多個(gè)單一索引，相比多個(gè)單一索引復(fù)合索引所需的開(kāi)銷(xiāo)更小白华。
索引同時(shí)有兩個(gè)概念叫做窄索引和寬索引慨默，窄索引是指索引列為1-2列的索引，寬索引也就是索引列超過(guò)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條件建索引霎箍，注意不要過(guò)多使用索引，過(guò)多使用會(huì)對(duì)更新操作效率有很大影響澡为。
• 如果表已經(jīng)建立了(col1漂坏，col2)，就沒(méi)有必要再單獨(dú)建立（col1）媒至；如果現(xiàn)在有(col1)索引顶别，如果查詢(xún)需要col1和col2條件，可以建立(col1,col2)復(fù)合索引拒啰，對(duì)于查詢(xún)有一定提高驯绎。

1.5 全文索引

查詢(xún)操作在數(shù)據(jù)量比較少時(shí)，可以使用like模糊查詢(xún)谋旦，但是對(duì)于大量的文本數(shù)據(jù)檢索剩失，效率很低屈尼。如果使用全文索引，查詢(xún)速度會(huì)比like快很多倍拴孤。在MySQL 5.6 以前的版本脾歧，只有MyISAM存儲(chǔ)引擎支持全文索引，從MySQL 5.6開(kāi)始MyISAM和InnoDB存儲(chǔ)引擎均支持乞巧。
創(chuàng)建全文索引的方法如下：

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

和常用的like模糊查詢(xún)不同涨椒，全文索引有自己的語(yǔ)法格式摊鸡，使用 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
• 全文索引匹配查詢(xún)锨苏，默認(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)贞谓。需要額外開(kāi)辟空間和數(shù)據(jù)維護(hù)工作。

• 索引是物理數(shù)據(jù)頁(yè)存儲(chǔ)葵诈，在數(shù)據(jù)文件中（InnoDB裸弦，ibd文件），利用數(shù)據(jù)頁(yè)(page)存儲(chǔ)作喘。
• 索引可以加快檢索速度理疙，但是同時(shí)也會(huì)降低增刪改操作速度，索引維護(hù)需要代價(jià)泞坦。

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

2.1 二分查找法

二分查找法也叫作折半查找法贰锁，它是在有序數(shù)組中查找指定數(shù)據(jù)的搜索算法赃梧。它的優(yōu)點(diǎn)是等值查詢(xún)、范圍查詢(xún)性能優(yōu)秀李根，缺點(diǎn)是更新數(shù)據(jù)槽奕、新增數(shù)據(jù)、刪除數(shù)據(jù)維護(hù)成本高房轿。

• 首先定位left和right兩個(gè)指針
• 計(jì)算(left+right)/2
• 判斷除2后索引位置值與目標(biāo)值的大小比對(duì)
• 索引位置值大于目標(biāo)值就-1粤攒，right移動(dòng)所森；如果小于目標(biāo)值就+1，left移動(dòng)

舉個(gè)例子夯接，下面的有序數(shù)組有17 個(gè)值焕济，查找的目標(biāo)值是7，過(guò)程如下：
第一次查找：

第一次查找

第二次查找：

第二次查找

第三次查找

第四次查找

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

Hash底層實(shí)現(xiàn)是由Hash表來(lái)實(shí)現(xiàn)的盔几，是根據(jù)鍵值 <key,value> 存儲(chǔ)數(shù)據(jù)的結(jié)構(gòu)晴弃。非常適合根據(jù)key查找value值，也就是單個(gè)key查詢(xún)逊拍，或者說(shuō)等值查詢(xún)上鞠。其結(jié)構(gòu)如下所示：

image.png

從上面結(jié)構(gòu)可以看出，Hash索引可以方便的提供等值查詢(xún)芯丧，但是對(duì)于范圍查詢(xún)就需要全表掃描了芍阎。
Hash索引在MySQL 中Hash結(jié)構(gòu)主要應(yīng)用在Memory原生的Hash索引 、InnoDB 自適應(yīng)哈希索引缨恒。InnoDB自適應(yīng)哈希索引是為了提升查詢(xún)效率谴咸，InnoDB存儲(chǔ)引擎會(huì)監(jiān)控表上各個(gè)索引頁(yè)的查詢(xún)，當(dāng)InnoDB注意到某些索引值訪(fǎng)問(wèn)非常頻繁時(shí)骗露，會(huì)在內(nèi)存中基于B+Tree索引再創(chuàng)建一個(gè)哈希索引岭佳，使得內(nèi)存中的 B+Tree 索引具備哈希索引的功能，即能夠快速定值訪(fǎng)問(wèn)頻繁訪(fǎng)問(wèn)的索引頁(yè)萧锉。
InnoDB自適應(yīng)哈希索引：在使用Hash索引訪(fǎng)問(wèn)時(shí)珊随，一次性查找就能定位數(shù)據(jù)，等值查詢(xún)效率要優(yōu)于B+Tree驹暑。
自適應(yīng)哈希索引的建立使得InnoDB存儲(chǔ)引擎能自動(dòng)根據(jù)索引頁(yè)訪(fǎng)問(wèn)的頻率和模式自動(dòng)地為某些熱點(diǎn)頁(yè)建立哈希索引來(lái)加速訪(fǎng)問(wèn)玫恳。另外InnoDB自適應(yīng)哈希索引的功能，用戶(hù)只能選擇開(kāi)啟或關(guān)閉功能优俘，無(wú)法進(jìn)行人工干涉京办。

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

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

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

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

image.png

B樹(shù)的搜索：從根節(jié)點(diǎn)開(kāi)始惭婿，對(duì)節(jié)點(diǎn)內(nèi)的索引值序列采用二分法查找，如果命中就結(jié)束查找叶雹。沒(méi)有命中會(huì)進(jìn)入子節(jié)點(diǎn)重復(fù)查找過(guò)程财饥，直到所對(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ū)間的訪(fǎng)問(wèn)性能

image.png

相比B樹(shù)谦炒，B+樹(shù)進(jìn)行范圍查找時(shí)贯莺，只需要查找定位兩個(gè)節(jié)點(diǎn)的索引值，然后利用葉子節(jié)點(diǎn)的指針進(jìn)行遍歷即可宁改。而B(niǎo)樹(shù)需要遍歷范圍內(nèi)所有的節(jié)點(diǎn)和數(shù)據(jù)缕探，顯然B+Tree效率高。

2.4 聚簇索引和輔助索引

聚簇索引和非聚簇索引：B+Tree的葉子節(jié)點(diǎn)存放主鍵索引值和行記錄就屬于聚簇索引还蹲；如果索引值和行記錄分開(kāi)存放就屬于非聚簇索引爹耗。
主鍵索引和輔助索引：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ù)量的大小藻懒。通常說(shuō)的主鍵索引就是聚集索引。
InnoDB的表要求必須要有聚簇索引：

• 如果表定義了主鍵视译，則主鍵索引就是聚簇索引
• 如果表沒(méi)有定義主鍵嬉荆，則第一個(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)建輔助索引就是為了提升查詢(xún)效率限番。一個(gè)表InnoDB只能創(chuàng)建一個(gè)聚簇索引，但可以創(chuàng)建多個(gè)輔助索引呀舔。

image.png

2.4.3 非聚簇索引

與InnoDB表存儲(chǔ)不同弥虐，MyISAM數(shù)據(jù)表的索引文件和數(shù)據(jù)文件是分開(kāi)的，被稱(chēng)為非聚簇索引結(jié)構(gòu)媚赖。

image.png

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

3.1 EXPLAIN

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

EXPLAIN SELECT * from user WHERE id < 3;

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

image.png

• SIMPLE ： 表示查詢(xún)語(yǔ)句不包含子查詢(xún)或union
• PRIMARY：表示此查詢(xún)是最外層的查詢(xún)
• UNION：表示此查詢(xún)是UNION的第二個(gè)或后續(xù)的查詢(xún)
• DEPENDENT UNION：UNION中的第二個(gè)或后續(xù)的查詢(xún)語(yǔ)句磨隘，使用了外面查詢(xún)結(jié)果
• UNION RESULT：UNION的結(jié)果
• SUBQUERY：SELECT子查詢(xún)語(yǔ)句
• DEPENDENT SUBQUERY：SELECT子查詢(xún)語(yǔ)句依賴(lài)外層查詢(xún)的結(jié)果缤底。

最常見(jiàn)的查詢(xún)類(lèi)型是SIMPLE布讹，表示我們的查詢(xún)沒(méi)有子查詢(xún)也沒(méi)用到UNION查詢(xún)。

2.type
表示存儲(chǔ)引擎查詢(xún)數(shù)據(jù)時(shí)采用的方式训堆。比較重要的一個(gè)屬性描验，通過(guò)它可以判斷出查詢(xún)是全表掃描還是基于索引的部分掃描。常用屬性值如下坑鱼，從上至下效率依次增強(qiáng)膘流。

• ALL：表示全表掃描，性能最差鲁沥。
• index：表示基于索引的全表掃描呼股，先掃描索引再掃描全表數(shù)據(jù)。
• range：表示使用索引范圍查詢(xún)画恰。使用>彭谁、>=、<允扇、<=缠局、in等等。
• ref：表示使用非唯一索引進(jìn)行單值查詢(xún)考润。
• eq_ref：一般情況下出現(xiàn)在多表join查詢(xún)狭园，表示前面表的每一個(gè)記錄，都只能匹配后面表的一行結(jié)果糊治。
• const：表示使用主鍵或唯一索引做等值查詢(xún)唱矛，常量查詢(xún)。
• NULL：表示不用訪(fǎng)問(wèn)表井辜，速度最快绎谦。

3. possible_keys
表示查詢(xún)時(shí)能夠使用到的索引。注意并不一定會(huì)真正使用粥脚，顯示的是索引名稱(chēng)窃肠。
4. key
表示查詢(xún)時(shí)真正使用到的索引，顯示的是索引名稱(chēng)阿逃。
5. rows
MySQL查詢(xún)優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息铭拧，估算SQL要查詢(xún)到結(jié)果需要掃描多少行記錄。原則上rows是越少效率越高恃锉，可以直觀的了解到SQL效率高低搀菩。
6. key_len
表示查詢(xún)使用了索引的字節(jié)數(shù)量∑仆校可以判斷是否全部使用了組合索引肪跋。
key_len的計(jì)算規(guī)則如下：

• 字符串類(lèi)型
  字符串長(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ù)值類(lèi)型
  TINYINT：1個(gè)字節(jié)
  SMALLINT：2個(gè)字節(jié)
  MEDIUMINT：3個(gè)字節(jié)
  INT谜洽、FLOAT：4個(gè)字節(jié)
  BIGINT、DOUBLE：8個(gè)字節(jié)
• 時(shí)間類(lèi)型
  DATE：3個(gè)字節(jié)
  TIMESTAMP：4個(gè)字節(jié)
  DATETIME：8個(gè)字節(jié)
• 字段屬性
  NULL屬性占用1個(gè)字節(jié)吴叶，如果一個(gè)字段設(shè)置了NOT NULL阐虚，則沒(méi)有此項(xiàng)。

7. Extra
Extra表示很多額外的信息蚌卤，各種操作會(huì)在Extra提示相關(guān)信息实束，常見(jiàn)幾種如下：

• Using where
  表示查詢(xún)需要通過(guò)索引回表查詢(xún)數(shù)據(jù)。
• Using index
  表示查詢(xún)需要通過(guò)索引逊彭，索引就可以滿(mǎn)足所需數(shù)據(jù)咸灿。
• Using filesort
  表示查詢(xún)出來(lái)的結(jié)果需要額外排序，數(shù)據(jù)量小在內(nèi)存侮叮，大的話(huà)在磁盤(pán)避矢，因此有Using filesort建議優(yōu)化。
• Using temprorary
  查詢(xún)使用到了臨時(shí)表囊榜，一般出現(xiàn)于去重审胸、分組等操作。

3.2 回表查詢(xún)

InnoDB索引有聚簇索引和輔助索引锦聊。聚簇索引的葉子節(jié)點(diǎn)存儲(chǔ)行記錄歹嘹，InnoDB必須要有，且只有一個(gè)孔庭。輔助索引的葉子節(jié)點(diǎn)存儲(chǔ)的是主鍵值和索引字段值，通過(guò)輔助索引無(wú)法直接定位行記錄材蛛，通常情況下圆到，需要掃碼兩遍索引樹(shù)。先通過(guò)輔助索引定位主鍵值卑吭，然后再通過(guò)聚簇索引定位行記錄芽淡，這就叫做回表查詢(xún)，它的性能比掃一遍索引樹(shù)低豆赏。
總結(jié)：通過(guò)索引查詢(xún)主鍵值挣菲，然后再去聚簇索引查詢(xún)記錄信息

3.3 覆蓋索引

在MySQL官網(wǎng)，類(lèi)似的說(shuō)法出現(xiàn)在explain查詢(xún)計(jì)劃優(yōu)化章節(jié)掷邦，即explain的輸出結(jié)果Extra字段為Using
index時(shí)白胀，能夠觸發(fā)索引覆蓋。

image.png

3.4 最左前綴原則

復(fù)合索引使用時(shí)遵循最左前綴原則，最左前綴顧名思義挟鸠，就是最左優(yōu)先叉信，即查詢(xún)中使用到最左邊的列，那么查詢(xún)就會(huì)使用到索引艘希，如果從索引的第二列開(kāi)始查找茉盏，索引將失效。

image.png

3.5 LIKE查詢(xún)

面試題：MySQL在使用like模糊查詢(xún)時(shí)枢冤，索引能不能起作用鸠姨？
回答：MySQL在使用Like模糊查詢(xún)時(shí)，索引是可以被使用的淹真，只有把%字符寫(xiě)在后面才會(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查詢(xún)

面試題：如果MySQL表的某一列含有NULL值，那么包含該列的索引是否有效核蘸？
對(duì)MySQL來(lái)說(shuō)巍糯，NULL是一個(gè)特殊的值，從概念上講客扎，NULL意味著“一個(gè)未知值”祟峦，它的處理方式與其他值有些不同。比如：不能使用=徙鱼，<宅楞，>這樣的運(yùn)算符，對(duì)NULL做算術(shù)運(yùn)算的結(jié)果都是NULL袱吆，count時(shí)不會(huì)包括NULL行等厌衙，NULL比空字符串需要更多的存儲(chǔ)空間等。

“NULL columns require additional space in the row to record whether their values are NULL.
 For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

NULL列需要增加額外空間來(lái)記錄其值是否為NULL绞绒。對(duì)于MyISAM表婶希，每一個(gè)空列額外占用一位，四舍五入到最接近的字節(jié)蓬衡。

image.png

雖然MySQL可以在含有NULL的列上使用索引喻杈，但NULL和其他數(shù)據(jù)還是有區(qū)別的，不建議列上允許為
NULL狰晚。最好設(shè)置NOT NULL筒饰，并給一個(gè)默認(rèn)值，比如0和 ‘’ 空字符串等家肯，如果是datetime類(lèi)型龄砰，也可以設(shè)置系統(tǒng)當(dāng)前時(shí)間或某個(gè)固定的特殊值，例如'1970-01-01 00:00:00'。

3.7 索引與排序

MySQL查詢(xún)支持filesort和index兩種方式的排序换棚，filesort是先把結(jié)果查出式镐，然后在緩存或磁盤(pán)進(jìn)行排序操作，效率較低固蚤。使用index是指利用索引自動(dòng)實(shí)現(xiàn)排序娘汞，不需另做排序操作，效率會(huì)比較高夕玩。
filesort有兩種排序算法：雙路排序和單路排序你弦。
雙路排序：需要兩次磁盤(pán)掃描讀取，最終得到用戶(hù)數(shù)據(jù)燎孟。第一次將排序字段讀取出來(lái)禽作，然后排序；第二次去讀取其他字段數(shù)據(jù)揩页。
單路排序：從磁盤(pán)查詢(xún)所需的所有列數(shù)據(jù)旷偿，然后在內(nèi)存排序?qū)⒔Y(jié)果返回。如果查詢(xún)數(shù)據(jù)超出緩存
sort_buffer爆侣，會(huì)導(dǎo)致多次磁盤(pán)讀取操作萍程，并創(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方式的排序告匠。
  • ORDER BY 子句索引列組合滿(mǎn)足索引最左前列

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

• WHERE子句+ORDER BY子句索引列組合滿(mǎn)足索引最左前列

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

• 以下幾種情況，會(huì)使用filesort方式的排序后专。
  • 對(duì)索引列同時(shí)使用了ASC和DESC

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

• WHERE子句和ORDER BY子句滿(mǎn)足最左前綴划鸽，但where子句使用了范圍查詢(xún)（例如>、<、in等）

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

• ORDER BY或者WHERE+ORDER BY索引列沒(méi)有滿(mǎn)足索引最左前列

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

• 使用了不同的索引裸诽，MySQL每次只采用一個(gè)索引嫂用，ORDER BY涉及了兩個(gè)索引

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

• WHERE子句與ORDER BY子句丈冬，使用了不同的索引

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

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

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

4. 查詢(xún)優(yōu)化

4.1 慢查詢(xún)定位

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

SHOW VARIABLES LIKE 'slow_query_log%'

通過(guò)如下命令開(kāi)啟慢查詢(xún)?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：指定慢查詢(xún)的閥值埂蕊，單位秒往弓。如果SQL執(zhí)行時(shí)間超過(guò)閥值，就屬于慢查詢(xún)記錄到日志文件中蓄氧。
log_queries_not_using_indexes：表示會(huì)記錄沒(méi)有使用索引的查詢(xún)SQL函似。前提是slow_query_log的值為ON，否則不會(huì)奏效喉童。

• 查看慢查詢(xún)?nèi)罩?/p>

  • 文本方式查看
    直接使用文本編輯器打開(kāi)slow.log日志即可撇寞。

    
    
    image.png
    
    

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

• 使用mysqldumpslow查看
  MySQL 提供了一個(gè)慢查詢(xún)?nèi)罩痉治龉ぞ適ysqldumpslow泄朴，可以通過(guò)該工具分析慢查詢(xún)?nèi)罩緝?nèi)容重抖。
  在 MySQL bin目錄下執(zhí)行下面命令可以查看該使用格式。

perl mysqldumpslow.pl --help

運(yùn)行如下命令查看慢查詢(xú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 慢查詢(xún)優(yōu)化

4.2.1 索引和慢查詢(xún)

• 如何判斷是否為慢查詢(xún)恨统？
  MySQL判斷一條語(yǔ)句是否為慢查詢(xún)語(yǔ)句，主要依據(jù)SQL語(yǔ)句的執(zhí)行時(shí)間三妈，它把當(dāng)前語(yǔ)句的執(zhí)行時(shí)間跟long_query_time 參數(shù)做比較畜埋，如果語(yǔ)句的執(zhí)行時(shí)間 > long_query_time，就會(huì)把這條執(zhí)行語(yǔ)句記錄到慢查詢(xún)?nèi)罩纠锩娉肫选ong_query_time 參數(shù)的默認(rèn)值是 10s悠鞍，該參數(shù)值可以根據(jù)自己的業(yè)務(wù)需要進(jìn)行調(diào)整。
• 如何判斷是否應(yīng)用了索引模燥？
  SQL語(yǔ)句是否使用了索引咖祭，可根據(jù)SQL語(yǔ)句執(zhí)行過(guò)程中有沒(méi)有用到表的索引，可通過(guò) explain命令分析查看蔫骂，檢查結(jié)果中的 key 值么翰，是否為NULL。
• 應(yīng)用了索引是否一定快辽旋？
  看下面的語(yǔ)句：

select * from user where id>0;

雖然使用了索引浩嫌，但是還是從主鍵索引的最左邊的葉節(jié)點(diǎn)開(kāi)始向右掃描整個(gè)索引樹(shù)檐迟，進(jìn)行了全表掃描，此時(shí)索引就失去了意義码耐。而像 select * from user where id = 2; 這樣的語(yǔ)句追迟，才是我們平時(shí)說(shuō)的使用了索引。它表示的意思是伐坏，我們使用了索引的快速搜索功能怔匣，并且有效地減少了掃描行數(shù)。

查詢(xún)是否使用索引桦沉，只是表示一個(gè)SQL語(yǔ)句的執(zhí)行過(guò)程每瞒；而是否為慢查詢(xún)，是由它執(zhí)行的時(shí)間決定的纯露，也就是說(shuō)是否使用了索引和是否是慢查詢(xún)兩者之間沒(méi)有必然的聯(lián)系剿骨。我們?cè)谑褂盟饕龝r(shí)，不要只關(guān)注是否起作用埠褪，應(yīng)該關(guān)心索引是否減少了查詢(xún)掃描的數(shù)據(jù)行數(shù)浓利，如果掃描行數(shù)減少了，效率才會(huì)得到提升钞速。對(duì)于一個(gè)大表贷掖，不止要?jiǎng)?chuàng)建索引，還要考慮索引過(guò)濾性渴语，過(guò)濾性好苹威，執(zhí)行速度才會(huì)快。

4.2.2 提高索引過(guò)濾性

假如有一個(gè)5000萬(wàn)記錄的用戶(hù)表驾凶，通過(guò)sex='男'索引過(guò)濾后牙甫，還需要定位3000萬(wàn)，SQL執(zhí)行速度也不會(huì)很快调违。其實(shí)這個(gè)問(wèn)題涉及到索引的過(guò)濾性窟哺，比如1萬(wàn)條記錄利用索引過(guò)濾后定位10條、100條技肩、1000條且轨，那他們過(guò)濾性是不同的。索引過(guò)濾性與索引字段虚婿、表的數(shù)據(jù)量殖告、表設(shè)計(jì)結(jié)構(gòu)都有關(guān)系。
案例：

表：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 引入的虛擬列來(lái)實(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;

慢查詢(xún)?cè)蚩偨Y(jié)：
全表掃描：explain分析type屬性all
全索引掃描：explain分析type屬性index
索引過(guò)濾性不好：靠索引字段選型辛蚊、數(shù)據(jù)量和狀態(tài)粤蝎、表設(shè)計(jì)
頻繁的回表查詢(xún)開(kāi)銷(xiāo)：盡量少用select *，使用覆蓋索引

4.3 分頁(yè)查詢(xún)優(yōu)化

4.3.1 一般性分頁(yè)

一般的分頁(yè)查詢(xún)使用簡(jiǎn)單的 limit 子句就可以實(shí)現(xiàn)袋马。limit格式如下：

SELECT * FROM 表名 LIMIT [offset,] rows

第一個(gè)參數(shù)指定第一個(gè)返回記錄行的偏移量初澎，注意從0開(kāi)始；
第二個(gè)參數(shù)指定返回記錄行的最大數(shù)目虑凛；
如果只給定一個(gè)參數(shù)碑宴，它表示返回最大的記錄行數(shù)目；

如果偏移量固定桑谍，返回記錄量對(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é)果：在查詢(xún)記錄時(shí)，返回記錄量低于100條锣披，查詢(xún)時(shí)間基本沒(méi)有變化贞间，差距不大。隨著查詢(xún)記錄量越大雹仿，所花費(fèi)的時(shí)間也會(huì)越來(lái)越多增热。

如果查詢(xún)偏移量變化，返回記錄數(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é)果：在查詢(xún)記錄時(shí)峻仇，如果查詢(xún)記錄量相同，偏移量超過(guò)100后就開(kāi)始隨著偏移量增大票顾，查詢(xún)時(shí)間急劇的增加础浮。（這種分頁(yè)查詢(xún)機(jī)制，每次都會(huì)從數(shù)據(jù)庫(kù)第一條記錄開(kāi)始掃描奠骄，越往后查詢(xún)?cè)铰雇也樵?xún)的數(shù)據(jù)越多，也會(huì)拖慢總查詢(xún)速度含鳞。）

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

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

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

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

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

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