本文只是整個(gè)系列筆記的第二章：MySQL索引原理和優(yōu)化，只解釋索引相關(guān)概念。

1. 索引類型和索引使用細(xì)節(jié)

索引可以提高查詢效率，影響where查詢和order by排序，它可以從多方面進(jìn)行分類，但是實(shí)際創(chuàng)建時(shí)，是根據(jù)應(yīng)用層次不同，有不同的創(chuàng)建索引的方式，創(chuàng)建語(yǔ)法關(guān)鍵字也不同

1. 從索引存儲(chǔ)結(jié)構(gòu)劃分（底層使用數(shù)據(jù)結(jié)構(gòu)不同）：B tree索引、Hash索引、FULLTEXT全文索引（也是使用倒排索引機(jī)制）、R tree索引
2. 從應(yīng)用層次劃分：普通、唯一、主鍵、復(fù)合索引
3. 從索引鍵值類型(字段類型)劃分：主鍵、輔助（二級(jí)）索引
4. 從數(shù)據(jù)存儲(chǔ)和索引鍵值邏輯關(guān)系劃分：聚簇索引（聚集索引）、非聚簇索引（非聚集索引）

1. 最基本的索引類型，基于普通字段建立的索引，沒有任何限制（唯一性，主鍵等限制都沒有）
2. 創(chuàng)建方法如下（通過(guò)關(guān)鍵字index）：

# 直接對(duì)指定表的指定字段建立索引，單獨(dú)的建立，索引名不可省略
create index <索引名> on tablename(字段名);# tablename是表名
# 用修改表結(jié)構(gòu)的方式，把索引給表加上，索引名可以不指定，會(huì)自動(dòng)生成
alter table tablename add index [索引名字](字段名);
# 創(chuàng)建表的時(shí)候，順便把索引加上，索引名可以省略，會(huì)自動(dòng)生成
create table tablename([....表字段信息],index[索引名字](字段名))
# 刪除指定索引
drop index <索引名> on tablename;
# 查看指定表的所有信息，不指定索引名創(chuàng)建索引，索引名會(huì)自動(dòng)生成，那么如果要?jiǎng)h除，沒有名字很麻煩，可以通過(guò)這個(gè)命令查看
show index from tablename;

查看索引信息show index from tablename;其中Key_name Comment就是索引名,PRIMARY這個(gè)索引，是主鍵索引，自動(dòng)創(chuàng)建的。name是普通索引

1. 和普通索引類似，但是唯一索引的字段值必須唯一，允許有空值，創(chuàng)建或修改表時(shí)追加唯一約束，就會(huì)自動(dòng)創(chuàng)建對(duì)應(yīng)的唯一索引。
2. 手動(dòng)創(chuàng)建唯一索引方法如下（和普通索引相比，只是關(guān)鍵字變了,unique）：

create unique index <索引名> on tablename(字段名);
alter table tablename add unique index[索引名](字段名);
create table tablename([...],unique[索引名](字段名));

1. 特殊的唯一索引，不允許有空值，創(chuàng)建或修改表時(shí)追加主鍵約束就會(huì)自動(dòng)創(chuàng)建，每個(gè)表只能有一個(gè)主鍵。
2. 自動(dòng)創(chuàng)建，方式如下（就是讓表有主鍵）：

# 創(chuàng)建表時(shí)，指定主鍵，主鍵索引會(huì)自動(dòng)創(chuàng)建
create table tablename([...],primary key(字段名))
# 如果原來(lái)表沒有主鍵，修改表結(jié)構(gòu)時(shí)，添加主鍵，會(huì)自動(dòng)創(chuàng)建主鍵索引
alter table tablename add primary key(字段名);

1. 前面都屬于單一索引（索引列為1列），用戶可以在多列上建立索引，叫做復(fù)合索引。在數(shù)據(jù)庫(kù)操作期間所需開銷更小，可以代替多個(gè)單一索引。
2. 有兩個(gè)索引概念，窄索引，寬索引。設(shè)計(jì)索引時(shí)，能用窄索引就不要用寬索引，窄索引往往比寬索引(組合索引)有效

1. 窄索引：索引列為1-2列
2. 寬索引：索引列超過(guò)2列

3. 創(chuàng)建方式如下（和普通索引一模一樣，只是字段名由1個(gè)變?yōu)槎鄠€(gè)）

# 和普通索引語(yǔ)法一樣，只不過(guò)字段名由1個(gè)變?yōu)槎鄠€(gè)
create index <索引名> on tablename(字段名1,字段名2,.....);
# 修改表結(jié)構(gòu)的方式建立
alter table tablename add index [索引名](字段名1,字段名2,.....);
# 創(chuàng)建表的時(shí)候順便
create table tablename([...],index[索引名字](字段名1,字段名2,.....));

4. 注意事項(xiàng)

1. 復(fù)合索引字段有順序，查詢時(shí)如果需要使用此索引，需要按照所有字段順序使用。例如select * from user where name =xx and age = xx，只會(huì)匹配(name,age)組合索引，不匹配(age,name)
2. 索引不是越多越好，根據(jù)where條件建立索引，過(guò)多使用索引對(duì)更新操作效率有很大影響。
3. 如果表已經(jīng)建立(name,age)組合索引，就沒必要單獨(dú)建立(name)索引了，但是(age)依然需要單獨(dú)建立，因?yàn)榻M合索引有順序。反之如果只有(name)，根據(jù)需要可以選擇建立(name,age)組合索引，不懂的看下面的例子。

1. select * from user where age = xx，不會(huì)匹配(name,age)，只會(huì)匹配(age,name)，如果沒有建立(age,name)的需要，可以單獨(dú)建立(age)
2. 反之select * from user where name = xx，會(huì)去匹配(name,age)，無(wú)需單獨(dú)建立(name)

說(shuō)白了，也就面試問(wèn)全文索引了，平常根本用不到。早期會(huì)用lucene做全文檢索，現(xiàn)在主流的是Solr和ElasticSearch搜索引擎

1. 查詢時(shí)，數(shù)據(jù)量如果較少，可以使用like模糊查詢，但是如果數(shù)據(jù)量大（大量文本數(shù)據(jù)），效率非常低
2. 此時(shí)使用全文索引，查詢速度會(huì)比like快很多倍，MySQL5.6版本以前，只有MyISAM存儲(chǔ)引擎支持全文檢索，MySQL5.6開始InnoDB引擎也支持了全文檢索。
3. 創(chuàng)建方式如下，和普通索引比，只是關(guān)鍵字變成了fulltext而已

create fulltext index <索引名> on tablename(字段名);
alter table tablename add fulltext [索引名](字段名);
create table tablename([...],fulltext key [索引名字](字段名));

4. 全文檢索有自己的語(yǔ)法格式，使用match和against關(guān)鍵字，和like模糊查詢不同

# 全文檢索user表name字段中，值是aaa字符的數(shù)據(jù)
select 
	* 
from 
	user 
where
	match(name) against('aaa');

1. 通過(guò)show variables like '%ft%'命令查看和全文檢索有關(guān)的參數(shù)
   可以發(fā)現(xiàn)innodb_ft_max_token_size和innodb_ft_min_token_size這兩個(gè)參數(shù)，表示InnoDB引擎全文檢索的最大和最小字符數(shù)，這里指定的是3-84，也就是說(shuō)，如果你匹配"a"或"aa"這樣的不在3-84個(gè)字符范圍內(nèi)的，是匹配不到的（就算你表里有），因?yàn)椴粔?個(gè)字符，而"aaa"才可以匹配到
   
   另外，上面的ft_boolean_syntax參數(shù)也很重要，它的值是"+“, " " ,”-"…這些符號(hào)，也就是說(shuō)，全文檢索有切詞功能，只有用這些符號(hào)分隔才看做一個(gè)詞，比如用aaa進(jìn)行全文檢索?？梢云ヅ?aaa,aaa a,aaa+a,b+aaa…)，也就是將aaa+a切成aaa和a兩個(gè)詞。但是不可匹配(aaaa,baaa,aaa^a…)這種沒有分隔符，或者不是指定分隔符的。
   
   可以發(fā)現(xiàn)上面的匹配，aaa并沒有匹配出aaaa，因?yàn)槟J(rèn)是等值匹配。我們可以通過(guò)布爾模式（in boolean mode）和一些符號(hào)進(jìn)行非等值通配，例如*號(hào)
   
2. 如果不清楚自己使用的是什么引擎，可以通過(guò)show create table 表名;查看指定表使用的引擎。
   
3. 如果你想通過(guò)set命令改變這個(gè)值，需要修改配置文件，上一個(gè)章節(jié)有步驟這里不在贅述(Binlog文件操作，開啟binlog哪里有寫到)https://blog.csdn.net/grd_java/article/details/123879609
   

2. 索引原理

2.1 使用的數(shù)據(jù)結(jié)構(gòu)和算法

索引是存儲(chǔ)引擎用于快速查找記錄的一種數(shù)據(jù)結(jié)構(gòu)，需要額外開辟空間和數(shù)據(jù)維護(hù)工作

1. 使用物理數(shù)據(jù)頁(yè)存儲(chǔ)，在數(shù)據(jù)文件（InnoDB是在.ibd文件），利用數(shù)據(jù)頁(yè)存儲(chǔ)。
2. 可以加快檢索速度，但是會(huì)降低增刪改速度，索引的維護(hù)也需要代價(jià)

用到的算法和數(shù)據(jù)結(jié)構(gòu)有：二分查找、Hash、B+Tree

1. 二分查找（折半查找）：有序數(shù)組中查找指定數(shù)據(jù)的搜索算法，優(yōu)點(diǎn)：等值查詢、范圍查詢性能優(yōu)秀。缺點(diǎn)：增刪改數(shù)據(jù)時(shí)維護(hù)成本高（數(shù)組，因?yàn)樾枰赜行?，修改時(shí)涉及元素移動(dòng)和數(shù)組擴(kuò)容）。不會(huì)的自己去學(xué)一學(xué)，很簡(jiǎn)單的算法。
2. Hash結(jié)構(gòu)：底層通過(guò)Hash表實(shí)現(xiàn)，根據(jù)<key,value>鍵值對(duì)存儲(chǔ)數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)，根據(jù)key查找對(duì)應(yīng)value值，單個(gè)key查詢（hash索引）和等值查詢一樣，但是范圍查詢就需要全表掃描了。
   為什么是等值呢？因?yàn)楣１硎菙?shù)組+鏈表，或者數(shù)組+鏈表+紅黑樹實(shí)現(xiàn)。它通過(guò)哈希算法，計(jì)算下標(biāo)，獲取到下標(biāo)后，再去數(shù)組指定下標(biāo)的地方存值或取值。那么如果多個(gè)key計(jì)算出的下標(biāo)相同，就按照順序，向當(dāng)前數(shù)組元素指定的鏈表或紅黑樹中添加結(jié)點(diǎn)。所以使用哈希表，一定要盡可能的減少碰撞（算出相同下標(biāo)的情況盡可能少），否則鏈表或紅黑樹層級(jí)過(guò)多，會(huì)影響效率。
   

1. Hash索引在Mysql的Hash結(jié)構(gòu)的主要應(yīng)用是，Memory原生Hash索引、InnoDB自適應(yīng)hash索引。
2. InnoDB自適應(yīng)哈希索引是為了提高查詢效率，InnoDB存儲(chǔ)引擎會(huì)監(jiān)控表上各個(gè)索引頁(yè)的查詢，當(dāng)某些索引值訪問(wèn)非常頻繁時(shí)，會(huì)在內(nèi)存中基于B+Tree索引再創(chuàng)建一個(gè)哈希索引，使內(nèi)存中B+Tree索引具備哈希索引的功能，可以快速定位訪問(wèn)頻繁的索引頁(yè)。
3. InnoDB的自適應(yīng)哈希索引，用戶只能選擇開啟或關(guān)閉，不能進(jìn)行人工干涉。
4. 通過(guò)命令show engine innodb status \G;命令查看innodb 自適應(yīng)hash的狀態(tài)！可以發(fā)現(xiàn)Adaptive hash index是插入到Buffer中，也就是占用Buffer Pool的空間。其中0.00 hash searches/s，0.00 non-hash searches/s。分別表示使用了hash的效率和不使用hash的效率，如果使用hash的效率反而不如不使用hash的效率，請(qǐng)大家關(guān)閉自適應(yīng)hash功能。
   
5. 通過(guò)show variables like '%innodb_adaptive%'命令查看自適應(yīng)hash的相關(guān)參數(shù)，可以通過(guò)set命令控制它們，其中innodb_adaptive_hash_index參數(shù)是控制自適應(yīng)hash是否啟用的，ON代表啟用
   

2.2 B+樹，為什么用B+樹

1. Mysql索引底層使用B+樹實(shí)現(xiàn)，B+樹是B樹的一種（升級(jí)版），所以很多人會(huì)說(shuō)存儲(chǔ)到B樹中。就像，平衡二叉樹是二叉樹的一種，我們一般會(huì)說(shuō)，用的是二叉樹
2. 根據(jù)官方文檔的解釋，大部分索引（PRIMARY KEY、UNIQUE、INDEX、FULLTEXT）存儲(chǔ)到B-tree（B樹中）。例外，空間數(shù)據(jù)類型上的索引使用R樹，內(nèi)存表也支持哈希索引，InnoDB使用反向列表作為FULLTEXT（全文檢索）索引
   
3. 使用B+樹實(shí)現(xiàn)，是因?yàn)樗鼘?duì)其它快速索引數(shù)據(jù)結(jié)構(gòu)，更適合數(shù)據(jù)庫(kù)場(chǎng)景

1. 散列表（Hash表）的優(yōu)點(diǎn)和缺點(diǎn)：雖然等值查詢很快，但是數(shù)據(jù)庫(kù)文件眾多，且大部分?jǐn)?shù)據(jù)不是等值查詢，使用散列表會(huì)浪費(fèi)大量?jī)?nèi)存空間，并且速度得不到體現(xiàn).而且說(shuō)白了算哈希，最后存儲(chǔ)到的是鏈表，一旦鏈表長(zhǎng)度過(guò)長(zhǎng)，需要轉(zhuǎn)換為紅黑樹，而紅黑樹的問(wèn)題就是深度會(huì)過(guò)深，影響效率
   
2. Binary Search Tree二叉搜索樹的優(yōu)點(diǎn)缺點(diǎn)：有序，可以二分的快速檢索目標(biāo)，但是二叉搜索樹，右子樹一定比左子樹大，這就有可能出現(xiàn)以下圖片中的問(wèn)題，深度太深，反而沒有了樹的優(yōu)點(diǎn)，查找變成了和鏈表一樣的效率。硬件層面，如果我要找7，需要磁盤預(yù)讀6次數(shù)據(jù)頁(yè)，也就是頻繁IO6次。
   
3. AVL 平衡二叉樹，它有一個(gè)旋轉(zhuǎn)的效果，當(dāng)最短分支和最長(zhǎng)分支高度差超過(guò)1時(shí)，將發(fā)生旋轉(zhuǎn)，讓它平衡。這是優(yōu)點(diǎn)，也是缺點(diǎn)，優(yōu)點(diǎn)在于，查詢效率的提升。缺點(diǎn)在于，大體量的數(shù)據(jù)，在插入，刪除過(guò)程中，旋轉(zhuǎn)操作會(huì)頻繁發(fā)生，浪費(fèi)資源，影響效率。而需要建立索引的數(shù)據(jù)庫(kù)，動(dòng)輒幾十萬(wàn)條數(shù)據(jù)，非常浪費(fèi)資源。
   
4. 紅黑樹，AVL平衡二叉樹的變種，對(duì)旋轉(zhuǎn)要求降低，最長(zhǎng)子樹層數(shù)不超過(guò)最短子樹2倍即可，當(dāng)然還有其它限制，新插入結(jié)點(diǎn)必須是紅色，任何結(jié)點(diǎn)，不能連續(xù)有兩個(gè)紅色等等。這樣做，它確實(shí)在插入和查詢方面做了平衡，但是二叉樹這種形式本身就有一個(gè)問(wèn)題。數(shù)據(jù)達(dá)到一定量級(jí)，樹的深度會(huì)太深。影響查詢效率。索引需要持久化到硬盤，紅黑樹深度太深，IO次數(shù)增多，也就是內(nèi)存和磁盤數(shù)據(jù)交換就會(huì)多，IO是性能瓶頸，我們必須保證IO盡可能少，而且取數(shù)據(jù)次數(shù)要少（磁盤預(yù)讀盡可能少發(fā)生）
   
5. B樹，性能方面，搜索可能在非葉子結(jié)點(diǎn)結(jié)束，性能逼近二分查找。每個(gè)結(jié)點(diǎn)最多m個(gè)子樹，根結(jié)點(diǎn)至少2個(gè)子樹，分支結(jié)點(diǎn)至少擁有m/2課子樹（除根和葉子結(jié)點(diǎn)都是分支結(jié)點(diǎn)）。所有葉子結(jié)點(diǎn)都在同一層，每個(gè)節(jié)點(diǎn)最多有m-1個(gè)key，升序排列
   

1. 缺點(diǎn)：每個(gè)結(jié)點(diǎn)占用一個(gè)磁盤塊，一個(gè)結(jié)點(diǎn)除了key，記錄子樹的指針，還有data數(shù)據(jù)，每頁(yè)存儲(chǔ)空間有限，如果data較大，每個(gè)節(jié)點(diǎn)存儲(chǔ)key數(shù)量將變少
2. 存儲(chǔ)數(shù)據(jù)量很大時(shí)，會(huì)導(dǎo)致深度較大，增大磁盤IO，影響查詢性能
3. 假設(shè)查找關(guān)鍵字28，先根據(jù)根結(jié)點(diǎn)找磁盤塊1，讀入內(nèi)存，然后比較28，發(fā)現(xiàn)在16-34之間，根據(jù)p2指針找到磁盤塊3讀入內(nèi)存，繼續(xù)比較，根據(jù)p2找磁盤塊8讀入內(nèi)存。共IO3次。

6. B+樹，只在葉子結(jié)點(diǎn)存儲(chǔ)key+數(shù)據(jù)data的B樹，非葉子結(jié)點(diǎn)只存儲(chǔ)key，而不是在每一個(gè)結(jié)點(diǎn)都存儲(chǔ)data。這樣做，每個(gè)結(jié)點(diǎn)可以包含更多結(jié)點(diǎn)的指針key，降低樹的高度，范圍也變成多個(gè)區(qū)間，區(qū)間越多，數(shù)據(jù)檢索越快。并且B+樹葉子結(jié)點(diǎn)，兩兩指針相互連接（為了符合磁盤預(yù)讀特性），順序查詢性能更高
   

1. 為什么層級(jí)變少了，假設(shè)每個(gè)數(shù)據(jù)行（data）1KB，key為100bit，而一個(gè)結(jié)點(diǎn)，按一個(gè)磁盤塊算4KB
2. 那么3層的B樹，一個(gè)結(jié)點(diǎn)需要存儲(chǔ)data，一共可以存4個(gè)，4 * 4 * 4 = 64條數(shù)據(jù)
3. 而3層的B+樹，一個(gè)結(jié)點(diǎn)不需要data，只存key，一個(gè)key100bit，一共存400個(gè)，400 * 400 * 400 = 640000條索引，最后，通過(guò)索引再去檢索數(shù)據(jù)即可（葉子結(jié)點(diǎn)相互連接，順序查找，很快）
4. 上面只是個(gè)例子，粗糙的表達(dá)一下意思。

2.3 聚簇索引和輔助索引（二級(jí)索引）

InnoDB引擎使用的索引。這兩種索引其實(shí)是對(duì)B+樹索引的存儲(chǔ)形式的一種描述，并不是新的索引，我們知道B+樹是葉子結(jié)點(diǎn)存儲(chǔ)數(shù)據(jù)，非葉子結(jié)點(diǎn)只存儲(chǔ)索引。而很多情況下，葉子結(jié)點(diǎn)也有不存儲(chǔ)數(shù)據(jù)的情況，比如MySIAM引擎因?yàn)槭褂梅蔷鄞厮饕?。索引和?shù)據(jù)文件分開存放，葉子結(jié)點(diǎn)存儲(chǔ)的是地址。InnoDB引擎中，如果是非主鍵字段建立索引，葉子結(jié)點(diǎn)就存儲(chǔ)主鍵索引。

1. 請(qǐng)區(qū)分：聚簇索引和二級(jí)索引是InnoDB使用的，非聚簇索引是MyISAM使用的。二級(jí)索引和非聚簇索引不是一個(gè)東西。
2. 聚簇索引：就是索引和數(shù)據(jù)放一起，非聚簇索引：就是索引和數(shù)據(jù)文件分開存放
3. 二級(jí)索引：建立在聚簇索引之上的輔助索引，葉子結(jié)點(diǎn)存放的數(shù)據(jù)是聚簇索引的key。還有索引的字段值（比如對(duì)username做索引，會(huì)將username字段值和對(duì)應(yīng)記錄的主鍵key存儲(chǔ)在葉子結(jié)點(diǎn)。）
4. 主鍵索引：主鍵索引是InnoDB自動(dòng)建立，采用的是聚簇索引的結(jié)構(gòu)，所以說(shuō)主鍵索引就是聚簇索引，但是不可以說(shuō)聚簇索引就是主鍵索引，因?yàn)榉强盏奈ㄒ绘I也可以建立聚簇索引。就算表沒有主鍵也沒有唯一鍵，它也會(huì)生成6為row_id作為key生成聚簇索引。

1. 聚簇索引：一種數(shù)據(jù)存儲(chǔ)方式，按照主鍵順序構(gòu)建B+Tree結(jié)構(gòu)，B+Tree的葉子結(jié)點(diǎn)就是行記錄（數(shù)據(jù)）（就是葉子結(jié)點(diǎn)存主鍵索引值和數(shù)據(jù)，非葉子結(jié)點(diǎn)，只存放主鍵索引值），行記錄和主鍵值緊湊地存儲(chǔ)在一起。意味著InnoDB的主鍵索引就是數(shù)據(jù)表本身，它按主鍵順序存放了整張表的數(shù)據(jù)，占用空間就是整張表數(shù)據(jù)量的大小。通常說(shuō)主鍵索引就是聚簇索引
2. 輔助索引：建立在聚簇索引之上的索引。根據(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è)輔助索引。
3. InnoDB表要求必須有聚簇索引，InnoDB聚簇索引只有一個(gè)，表定義主鍵會(huì)自動(dòng)生成主鍵索引（聚簇索引）。那表沒有主鍵怎么建立聚簇索引呢？如果沒有主鍵，會(huì)選擇唯一鍵（必須是非空的unique，而且是選擇第一個(gè)）。沒有唯一鍵，生成一個(gè)6位row_id作為聚簇索引的key，或者說(shuō)主鍵。
4. 可以理解為，二級(jí)索引，是建立在聚簇索引之上的，它葉子結(jié)點(diǎn)，就是存儲(chǔ)聚簇索引的主鍵，也就是說(shuō)使用二級(jí)索引，需要先查詢到主鍵，然后再根據(jù)查詢到的主鍵，去聚簇索引中查數(shù)據(jù)。
5. 所以二級(jí)索引比聚簇索引要慢是肯定的，但是對(duì)于非主鍵或唯一字段的查詢提升是肯定的。一般對(duì)經(jīng)常查詢，但是又不是主鍵的字段，才會(huì)建立二級(jí)索引。

2.4 非聚簇索引

一定不要將非聚簇索引和二級(jí)索引混淆。聚簇索引是索引和記錄存放在一起，也就是葉子結(jié)點(diǎn)即存放索引，也存放數(shù)據(jù)。而非聚簇索引是索引和記錄分開存放。而二級(jí)索引是建立在聚簇索引之上的一個(gè)輔助索引。葉子結(jié)點(diǎn)存儲(chǔ)的是聚簇索引的key。

1. 非聚簇索引：索引文件只存放索引，主鍵索引就存放主鍵key，輔助鍵索引就存放輔助鍵的key。葉子結(jié)點(diǎn)存放的不是記錄，是數(shù)據(jù)的內(nèi)存地址。
2. 無(wú)論是主鍵索引還是輔助鍵索引，最后都會(huì)通過(guò)葉子結(jié)點(diǎn)存放的內(nèi)存地址，去分開存放的數(shù)據(jù)文件中獲取數(shù)據(jù)。

3. EXPLAIN查詢分析

Explain select * from user where id < 3;

MySQL提供了一個(gè)EXPLAIN命令，可以對(duì)SELECT語(yǔ)句進(jìn)行分析，輸出SELECT執(zhí)行的詳細(xì)信息，供開發(fā)人員有針對(duì)性的優(yōu)化。相當(dāng)于是一個(gè)執(zhí)行計(jì)劃。

1. id：執(zhí)行的一個(gè)序號(hào)，一個(gè)標(biāo)識(shí)，主鍵
2. select_type:SIMPLE：查詢的類型，下面介紹常用的，如果以后遇見沒見過(guò)的，官方文檔查就可以了。

1. SIMPLE：代表簡(jiǎn)單的查詢，不包含子查詢或union聯(lián)合查詢這種復(fù)雜元素
2. PRIMARY：表示此查詢，是最外層的查詢，不包含在嵌套查詢中
3. UNION：表示此查詢，不是最外層的，比如UNION聯(lián)合查詢的第二個(gè)或后續(xù)的查詢（UNION操作符的作用是合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集）。
4. DEPENDENT UNION：表示依賴聯(lián)合，就是UNION的第二層或后續(xù)的查詢，依賴于（使用到了）外層的參數(shù)值等。
5. UNION RESULT：UNION的結(jié)果，聯(lián)合查詢的結(jié)果
6. SUBQUERY：子查詢語(yǔ)句，子查詢里面的sql的類型
7. DEPENDENT SUBQUERY：表示依賴子查詢，就是子查詢，依賴于外層的參數(shù)值等。
8. 下圖是使用UNION聯(lián)合查詢的各查詢類型，外層是PRIMARY，第二個(gè)是UNION類型表示不是最外層的，最后會(huì)有一個(gè)臨時(shí)的，類型是UNION RESULT，負(fù)責(zé)UNION的結(jié)果。
   
9. 子查詢的類型
   
10. 子查詢使用外層查詢的類型
    

3. table:user：查詢的表是哪個(gè)
4. partitions:NULL：分區(qū)
5. type:range：連接類型，存儲(chǔ)引擎查詢數(shù)據(jù)時(shí)采用的方式，可以判斷出查詢時(shí)，是全表掃描，還是基于索引的部分掃描，這些類型，查詢效率由低到高，ALL查詢效率最低，NULL查詢效率最高。下面是常見的，以后遇到?jīng)]見過(guò)的，去查官方文檔就好了。

1. ALL：表示全表掃描，性能最差
   
2. index：基于索引的全表掃描，先掃描索引，再掃描全表數(shù)據(jù)。（先通過(guò)索引獲取順序，然后順序檢索，比直接全表掃描快得多）。但是條件字段必須有索引
   
3. range：使用索引進(jìn)行范圍查找，使用>、>=、<、<=、in等等，條件字段必須有索引，沒索引還是ALL
   
4. ref：使用非唯一索引進(jìn)行單值查詢（普通索引）。
   
5. eq_ref：多數(shù)出現(xiàn)在多表join查詢，前面表的每一個(gè)記錄，只能匹配后面表的一行結(jié)果
   
6. const：常量查詢，表示使用主鍵或唯一索引進(jìn)行等值查詢。內(nèi)部會(huì)進(jìn)行優(yōu)化，優(yōu)化成常量。
   
7. NULL：表示不用訪問(wèn)表，直接出結(jié)果。速度最快。
   

6. possible_keys:PRIMARY：此處查詢可能使用的索引（因?yàn)檫@個(gè)表只有主鍵索引，所以只顯示了PRIMARY），并不一定會(huì)使用，只是列出來(lái)，可能會(huì)使用它們，顯示的是索引名。
7. key:PRIMARY：本次查詢使用到的索引（實(shí)際用到的索引）
8. key_len:4：使用索引時(shí)，用到了索引中多少個(gè)字節(jié)(涉及到的一些算法和公式，后面的章節(jié)會(huì)講)?？梢耘袛嗍褂脧?fù)合索引時(shí)，是全部使用了索引，還是只用了索引的最左部分的部分字段值。key_len計(jì)算規(guī)則如下(下面列出各類型，1個(gè)元素的大小，比如TINYINT類型1個(gè)字節(jié)，key_len:4就代表用了4個(gè)TINYINT索引)：

1. 字符串類型計(jì)算公式如下

1. 字符串長(zhǎng)度和字符集有關(guān)：latin1=1、gbk=2、utf-8=3、utf8mb4=4
2. char(n):n字符集長(zhǎng)度。n表示char類型n這個(gè)字符你規(guī)定的長(zhǎng)度。比如char(20)，utf-8字符集，那么一個(gè)字符就占203=60字節(jié)
3. varchar(n):n*字符集長(zhǎng)度+2字節(jié)

2. 數(shù)值類型計(jì)算公式

1. TINYINT：1個(gè)字節(jié)
2. SMALLINT：2個(gè)字節(jié)
3. MEDIUMINT：3個(gè)字節(jié)
4. INT、FLOAT：4個(gè)字節(jié)
5. BIGINT、DOUBLE：8個(gè)字節(jié)

3. 時(shí)間類型

1. DATE：3個(gè)字節(jié)
2. TIMESTAMP：4個(gè)字節(jié)
3. DATETIME：8個(gè)字節(jié)

4. 字段屬性

1. NULL屬性占用1個(gè)字節(jié)，如果一個(gè)字段設(shè)置了NOT NULL，則沒有此項(xiàng)。

5. 看個(gè)例子
   

9. ref:NULL：引用，有哪些常量、常數(shù)、字段與key一起被使用
10. rows:2：本次查詢掃描了多少行，一定要保證這個(gè)值盡可能小。MySQL查詢優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息，估算SQL查詢到結(jié)果要掃描多少行數(shù)據(jù)。
11. filtered:100.00：過(guò)濾，此處查詢條件過(guò)濾的百分比
12. Extra:Using where：額外信息，會(huì)追加各種關(guān)鍵詞，代表一些特定含義，這個(gè)遇到了去官方文檔查就可以了，后面也會(huì)詳細(xì)介紹一部分，以幫助我們調(diào)優(yōu)。

1. Using where：表示查詢需要通過(guò)回表，以獲取其它數(shù)據(jù)?；乇砭褪钱?dāng)前查到的東西不能滿足，還需要去其它索引查，比如二級(jí)索引，查到主鍵，此時(shí)如果只需要主鍵，就不需要回表了，但是如果還需要其它字段，就得去主鍵索引獲取了。
2. Using index：表示查詢需要索引，但是不需要回表，當(dāng)前索引包含的數(shù)據(jù)就可以滿足，比如主鍵索引，或者通過(guò)二級(jí)索引，但只需要主鍵，例如查詢用戶名為張三的主鍵id值。
3. Using filesort：表示查詢出的結(jié)果，需要額外的排序。數(shù)據(jù)量小可以在內(nèi)存做，但是數(shù)據(jù)量大就得磁盤里做了。所以，如果調(diào)優(yōu)時(shí)發(fā)現(xiàn)這個(gè)，建議趕緊優(yōu)化。
4. Using temprorary：查詢中使用到了臨時(shí)表空間。一般出現(xiàn)在去重，分組等操作。效率也不高，建議能優(yōu)化就優(yōu)化。最好分組去重這種事，交給后端程序員，比如JAVA去處理。因?yàn)樗鼈兛梢酝ㄟ^(guò)一寫算法，快速的在內(nèi)存中去重。
5. 例子
   
   

4. 索引優(yōu)化

4.1 回表查詢和索引覆蓋

InnoDB索引有聚簇索引和二級(jí)索引，聚簇索引必須有且只有一個(gè)，葉子結(jié)點(diǎn)存儲(chǔ)行記錄。二級(jí)索引可以有多個(gè)，建立在聚簇索引之上，葉子結(jié)點(diǎn)存儲(chǔ)主鍵值（聚簇索引的key，默認(rèn)是主鍵，沒有主鍵用非空唯一鍵，還沒有就生成6為row_id作為主鍵）和索引字段值。

1. 通過(guò)二級(jí)索引，無(wú)法直接定位行記錄，只能獲取二級(jí)索引存儲(chǔ)的聚簇索引key（主鍵值）和二級(jí)索引，索引的字段值。
2. 所以此時(shí)如果我們通過(guò)二級(jí)索引，想要獲取到行記錄（需要主鍵和二級(jí)索引字段的其它信息），就需要通過(guò)二級(jí)索引葉子結(jié)點(diǎn)的主鍵key，去聚簇索引定位行記錄。這就是回表查詢。也就是一共掃描兩遍索引（二級(jí)索引+聚簇索引）。
3. 那么如果不需要回表，我們要查的就是聚簇索引的key，或者二級(jí)索引字段，就叫索引覆蓋。
4. Mysql官方文檔沒有索引覆蓋這個(gè)概念的詳細(xì)說(shuō)明，但是SQL-Server有，但是Mysql在explain查詢優(yōu)化章節(jié)（explain輸出結(jié)果Extra字段為Using index時(shí)，可以觸發(fā)索引覆蓋），有和SQL-Server類似的描述。它們都表達(dá)了：“只需要在一棵索引樹上就能獲取SQL所需的所有列數(shù)據(jù)，無(wú)需回表，速度更快”，這就叫索引覆蓋。
5. 實(shí)現(xiàn)索引覆蓋的最常見方法是，將被查詢字段，建立到組合索引。（如果直接通過(guò)聚簇索引就可以查詢，可不要再建立組合索引。）
6. 也就是說(shuō)，優(yōu)化時(shí)，盡量避免回表，而保證索引覆蓋。實(shí)際上就是，盡量保證用Explain生成的執(zhí)行計(jì)劃，Extra字段的值為Using index，使用索引而不進(jìn)行回表

1. 主要就是回表問(wèn)題，只要回表就是一次IO，select a,b,c 和select * 可不是一個(gè)量級(jí)
2. 比如select a,b,c我們有(a,b,c)復(fù)合索引，那么我們可以where a = xxx and b = xxx這樣只IO兩次，甚至一次，最次是3次。
3. 但是select * 可能會(huì)IO4次，甚至直接IO一次走全表掃描。因?yàn)榘芏嗥渌侄?，只要是索引葉子結(jié)點(diǎn)沒有的，就需要回表?；乇砭偷肐O。
4. 因此，不使用select * 大部分原因是為了可以索引覆蓋

4.2 最左前綴（匹配）原則

復(fù)合索引建立時(shí)，會(huì)依次從左到右生成B+樹，比如建立復(fù)合索引(a,b,c)。它會(huì)以a為入口，a的葉子結(jié)點(diǎn)為b的key和自己索引字段，b的葉子結(jié)點(diǎn)為c的key和自己的索引字段。

1. 你可以用(a),(a,b),(a,b,c)來(lái)使用到這個(gè)復(fù)合索引，但是不能以(b),(b,c),( c)這樣的來(lái)使用，因?yàn)樗鼤?huì)找不到入口，因?yàn)槿肟谑莂
2. 使用復(fù)合索引時(shí)，一定要從最左開始，并且連續(xù)，比如(a,b),(a,b,c),(a)，不可以是(a,c)這樣，略過(guò)b的，因?yàn)閎算是c的入口。使用(a,c)來(lái)匹配，則只會(huì)生效(a)而不生效( c)。會(huì)觸發(fā)全表掃描（走了a的索引，還走了全表掃描，還不如直接全表掃描）。
   
3. 如何優(yōu)化復(fù)合索引？

1. 盡量減少回表使用索引覆蓋，比如我們要查詢(a,b,c)，那么select a,b,c from table where a = 1 and b = 1;會(huì)減少一次回表（IO）。先走a，然后走b，b會(huì)保存c，所以無(wú)需再走c。但是如果條件必須要c，無(wú)法避免最后一次回表，也不要強(qiáng)求。
2. 盡量避免使用模糊查詢like；如果讓like走索引，查詢字符不能以通配符%開始，如果需要讓like %abc走索引，需要使用reverse()函數(shù)來(lái)創(chuàng)建函數(shù)索引(不推薦)

select a,b,c from tablename where reverse(f) like reverse('%abc');

4.3 LIKE查詢

MySQL使用like模糊查詢時(shí)，索引是可以起作用的，但是有條件。

1. 首先，我們建立索引，通過(guò)索引字段作為key生成B+樹，此時(shí)如果我們模糊匹配，例如%abc或%abc%，這時(shí)候查索引，根本不知道哪個(gè)索引中間有abc（沒有明確起點(diǎn)值），肯定需要全表掃描，也就沒必要走索引。
   
2. 但是如果是abc%，就會(huì)走索引，因?yàn)檎襛bc開頭的，是完全可以走索引而減少查詢時(shí)間的。而且Mysql 5.7還有了新特性，會(huì)使用索引時(shí)，充分的進(jìn)行過(guò)濾，下沉到存儲(chǔ)引擎去執(zhí)行。對(duì)應(yīng)的Extra為Using index condition,表示進(jìn)行了新機(jī)制的優(yōu)化。
   
3. 5.7版本后，這個(gè)選項(xiàng)可以選擇開啟或關(guān)閉，通過(guò)命令show variables like '%optimizer_switch%'查看,對(duì)應(yīng)參數(shù)為index_condition_pushdown=on;
   

4.4 NULL查詢

如果Mysql表的某一列含有Null值，那么包含該列的索引，5.5之后版本是可以生效的。老版本會(huì)讓索引失效。

1. Null是特殊的，處理方式和其他值不同，比如不可以使用=,<,>這樣的符號(hào)運(yùn)算，對(duì)Null的算數(shù)運(yùn)算結(jié)果都是Null，使用count函數(shù)時(shí)，不會(huì)包括Null行，Null比空字符串需要更多存儲(chǔ)空間等等。
2. Null列需要增加額外空間記錄其值是否為Null。另外MyISAM表中，每一個(gè)空列額外占用一位，四舍五入到最接近的字符。
3. 雖然MySQL可以在含有NULL列上使用索引，但是Null和其它數(shù)據(jù)有區(qū)別，不建議設(shè)置，最好設(shè)置為Not null，并給一個(gè)默認(rèn)值（0，空字符串等），如果是datetime類型，可以設(shè)置為當(dāng)前系統(tǒng)時(shí)間或某個(gè)固定值。

4.5 索引和排序

MySQL支持filesort和index兩種排序方式，filesort是先把結(jié)果查詢出來(lái)，然后在緩存或磁盤進(jìn)行排序，效率低。index是利用索引自動(dòng)實(shí)現(xiàn)排序，不需要額外做排序操作，效率較高。

1. 常見的使用index方式排序的情況,推薦。（通過(guò)explain分析得出的結(jié)論，Extra字段值為Using index。大家最好寫sql時(shí)，都用explain來(lái)觀察下執(zhí)行方案。畢竟情況千千萬(wàn)，我下面給出的結(jié)論很可能不夠用。）

# 假設(shè)只有(id),(id,name)兩個(gè)索引
# Order By子句索引列組合滿足索引最左匹配原則
explain select id from user order by id;//對(duì)應(yīng)(id)、(id,name)等等這樣的索引有效
# where + order by子句索引列組合滿足索引最左匹配原則
explain select id from user where id = 3 order by name;//對(duì)應(yīng)(id,name)索引

2. 使用filesort方式排序的情況（Extra屬性值為Using filesort），不推薦，可以考慮優(yōu)化成index。

# 假設(shè)只有(id),(id,name)兩個(gè)索引
# 對(duì)索引列同時(shí)使用asc和desc
explain select id from user order by id asc,name desc;//對(duì)應(yīng)(id,name)索引，但是會(huì)使用filesort方式
# where + order by滿足最左匹配，但where子句使用范圍查詢（<,>,in等）
explain select id from user where id > 3 order by name;//對(duì)應(yīng)(id,name)索引，但使用范圍查詢，會(huì)使用filesort方式
# order by 或 where + order by索引列沒有滿足最左匹配
explain select id from user order by name;//不匹配(id)或(id,name).只能走filesort
# 使用不同的索引，假設(shè)我們有(name)和(age)兩個(gè)索引。Mysql每次只能采用一個(gè)索引，例如下面order by涉及兩個(gè)索引，會(huì)走filesort
explain select id from user order by name,age;
# 同理，where 和 order by 組合使用了不同索引，也會(huì)filesort
explain select id from user where name = 'tom' order by age;
# where 或者 order by使用表達(dá)式(包括函數(shù)表達(dá)式) 會(huì) filesort
explain select id from user order by abs(age);

3. filesort的兩種排序算法

1. 雙路排序（舊）：需要兩次磁盤掃描讀取，最終得到用戶數(shù)據(jù)，第一次將排序字段取出，然后排序；第二次去讀取其它字段數(shù)據(jù)。
2. 單路排序（新）：從磁盤查詢所有數(shù)據(jù)，然后在內(nèi)存或緩存中排序?qū)⒔Y(jié)果返回。如果出現(xiàn)內(nèi)存空間不夠的情況，會(huì)分批次的加載到內(nèi)存，反而增大IO次數(shù)。
3. 所以單路排序效率較高，但是如果內(nèi)存不夠，反而比雙路排序慢，所以避免單路排序緩存空間不夠，就是可以優(yōu)化的點(diǎn)。
4. 可以通過(guò)少使用select * ，或者增加sort_buffer_size、max_length_for_sort_data容量，讓緩沖區(qū)不那么容易溢出。
   

4.6 索引失效情況總結(jié)

結(jié)合前面的講解，我們可以總結(jié)出索引失效的7種情況。

1. 記住七字口訣：模型，數(shù)空運(yùn)，最快（意思是，運(yùn)輸"模型"，就"數(shù)"，“空運(yùn)"的"最快”）

1. 模（模糊）：模糊查詢LIKE以%開頭
2. 型（類型）：數(shù)據(jù)類型錯(cuò)誤
3. 數(shù)（函數(shù)）：對(duì)索引字段使用內(nèi)部函數(shù)
4. 空（Null）：索引列是NULL，索引列不存儲(chǔ)空值，如果索引列不設(shè)置not null，數(shù)據(jù)庫(kù)會(huì)認(rèn)為索引列存在null值，因此不會(huì)使用索引。最新版的數(shù)據(jù)庫(kù)引擎，null這種情況，不會(huì)失效了
5. 運(yùn)（運(yùn)算）：對(duì)索引列進(jìn)行加減乘除等運(yùn)算
6. 最（最左）：復(fù)合索引不按索引列最左開始查找
7. 快（更快）：全表查找預(yù)計(jì)比索引更快

5. 查詢優(yōu)化

5.1 慢查詢定位

當(dāng)然是通過(guò)慢查詢?nèi)罩緡D，另外，慢查詢?nèi)罩荆皇侵挥涗洸樵僑QL，insert之類的，只要執(zhí)行夠慢，也會(huì)記錄的。只不過(guò)insert這種很少需要優(yōu)化。

1. 開啟慢查詢?nèi)罩?/strong>

# 查看MySQL數(shù)據(jù)庫(kù)是否開啟了慢查詢?nèi)罩竞吐樵內(nèi)罩疚募拇鎯?chǔ)位置
show variables like 'slow_query_log%'
# 設(shè)置慢查詢相關(guān)參數(shù)
set global slow_query_log = ON;# 開啟慢查詢?nèi)罩?/span>
set global slow_query_log_file = 'OAK-slow.log'; # 設(shè)置慢查詢?nèi)罩疚募?/span>
set global log_queries_not_using_indexes = ON; #開啟記錄沒有使用索引的查詢SQL功能。 
set long_query_time = 10; # 設(shè)置慢查詢的時(shí)間閾值，這里是10s，表示記錄執(zhí)行時(shí)間超過(guò)10s的查詢sql將被認(rèn)為是慢查詢，記錄在日志。

perl mysqldumpslow.pl --help

# 顯示"C:\ProgramData\MySQL\Data\OAK-slow.log"日志文件，前5條記錄
perl mysqldumpslow.pl -t 5 -s at "C:\ProgramData\MySQL\Data\OAK-slow.log"

# 表：
create table student(
	int id primary key auto_increment,
	varchar(20) name not null,
	char(1) sex not null,
	int age not null
)
# 造數(shù)據(jù)：還是造20萬(wàn)左右即可，我這里為了方便，只造了5000多
insert into student(name,sex,age) select name,sex,age from student;
# SQL案例：會(huì)進(jìn)行全表掃描
explain select * from student where age = 18 and name like 'zhang%';

alter table 
	student # 為student建立
add # 添加一個(gè)虛擬列，varchar(5) first_name
	first_name varchar(5) 
	generated always as (left(name,5)), # 總是從左到右的取name字段的前5個(gè)字符，比如zhang
add # 順便添加一個(gè)索引
	index(first_name,age);

select * from tablename limit [offset,] rows;
# offset 可省，表示第一個(gè)需要返回的記錄行的偏移量，從0開始算。不指定就默認(rèn)從0開始
# rows 返回記錄行的最大數(shù)目。也就是一頁(yè)多少行。
select * from tablename limit 10,15; # 表示從下標(biāo)為10的記錄開始，返回15條數(shù)據(jù)。

select * from tablename limit 10000,1;//0.005 sec
select * from tablename limit 10000,10;//0.005 sec
select * from tablename limit 10000,100;//0.005 sec
select * from tablename limit 10000,1000;//0.007 sec
select * from tablename limit 10000,10000;//0.02 sec

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

# 將select * 變?yōu)閟elect id，就可以不回表
select id from user limit 10000,100;

# 將 limit 10000,100;變成 limit 10000,1 和 limit 100;
select * from user where
	id >= (
		select id from user
			limit 10000,1 # 偏移量大，執(zhí)行時(shí)間，沒有數(shù)據(jù)量大花時(shí)間
	)
	limit 100;