問題1：MySQL索引類型有哪些區(qū)別是什么譬涡？
MySQL索引類型：
normal：表示普通索引、
unique：表示唯一的暮的，不允許重復(fù)的索引漩勤，如果該字段信息保證不會(huì)重復(fù)感挥，可以設(shè)置為unique。
full text：表示全文搜索的索引越败，用于搜索很長(zhǎng)一篇文章的時(shí)候链快，效果特別好，如果是比較短的文本眉尸，比如一兩行字的普通的normal即可。
索引類型有建立索引的字段內(nèi)容特性來決定巨双，通常normal最常見噪猾。
問題2：實(shí)際過程中，應(yīng)該選取表中哪些字段作為索引筑累？
為使索引的使用效率更高袱蜡，在創(chuàng)建索引時(shí)，必須考慮在哪些字段上創(chuàng)建索引和創(chuàng)建什么類型的索引慢宗，7個(gè)原則如下：
1）選擇唯一索引坪蚁；
唯一性索引的值是唯一的，可以更快速的通過該索引來確定某條記錄镜沽。例如敏晤，學(xué)生表中學(xué)號(hào)是具有唯一性的字段。為該字段建立唯一性索引可以很快的確定某個(gè)學(xué)生的信息缅茉。如果使用姓名的話嘴脾，可能存在同名現(xiàn)象，從而降低查詢速度。
2）為經(jīng)常需要排序译打、分組和聯(lián)合操作的字段建立索引耗拓；
經(jīng)常需要ORDER BY、GROUP BY奏司、DISTINCT和UNION等操作的字段乔询，排序操作會(huì)浪費(fèi)很多時(shí)間。如果為其建立索引韵洋，可以有效地避免排序操作竿刁。
3）為經(jīng)常作為查詢條件的字段建立索引；
如果某個(gè)字段經(jīng)常用來做查詢條件麻献，那么該字段的查詢速度會(huì)影響整個(gè)表的查詢速度们妥。因此，為這樣的字段建立索引勉吻，可以提高整個(gè)表的查詢速度监婶。
4）限制索引的數(shù)目；
索引的數(shù)目不是越多越好齿桃。每個(gè)索引都需要占用磁盤空間惑惶，索引越多，需要的磁盤空間就越大短纵。修改表時(shí)带污，對(duì)索引的重構(gòu)和更新很麻煩。越多的索引香到，會(huì)使更新表變得很浪費(fèi)時(shí)間鱼冀。
5）盡量使用數(shù)據(jù)量少的索引；
如果索引的值很長(zhǎng)悠就，那么查詢的速度會(huì)受到影響千绪。例如，對(duì)一個(gè)CHAR（100）類型的字段進(jìn)行全文檢索需要的時(shí)間肯定要比對(duì)CHAR（10）類型的字段需要的時(shí)間要多梗脾。
6）盡量使用前綴來索引荸型；
如果索引字段的值很長(zhǎng)，最好使用值的前綴來索引炸茧。例如瑞妇，TEXT和BLOG類型的字段，進(jìn)行全文檢索會(huì)很浪費(fèi)時(shí)間梭冠。如果只檢索字段的前面的若干個(gè)字符辕狰，這樣可以提高檢索速度。
7）刪除不在使用或很少使用的索引妈嘹；
表中的數(shù)據(jù)被大量更新柳琢，或者數(shù)據(jù)的使用方式被改變后，原有的一些索引可能不再需要。數(shù)據(jù)庫管理員應(yīng)當(dāng)定期找出這些索引柬脸，將它們刪除他去，從而減少索引對(duì)更新操作的影響。
選擇索引的最終目的是為了使查詢的速度變快倒堕。上面給出的原則是最基本的準(zhǔn)則灾测，但不能拘泥于上面的準(zhǔn)則。根據(jù)應(yīng)用的實(shí)際情況進(jìn)行分析和判斷垦巴，選擇最合適的索引方式媳搪。
問題3： 在使用MySQL索引的時(shí)候, 選擇b-tree還是hash？
- hash索引僅僅能滿足"=","IN"和"<=>"查詢骤宣，不能使用范圍查詢. 比如< , 由于 Hash 索引比較的是進(jìn)行 Hash 運(yùn)算之后的 Hash 值秦爆，所以它只能用于等值的過濾，不能用于基于范圍的過濾憔披，因?yàn)榻?jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系等限，并不能保證和Hash運(yùn)算前完全一樣
- 對(duì)于組合索引，Hash 索引在計(jì)算 Hash 值的時(shí)候是組合索引鍵合并后再一起計(jì)算 Hash 值芬膝，而不是單獨(dú)計(jì)算 Hash 值望门，所以通過組合索引的前面一個(gè)或幾個(gè)索引鍵進(jìn)行查詢的時(shí)候，Hash 索引也無法被利用
- Hash 索引是將索引鍵通過 Hash 運(yùn)算之后锰霜，將 Hash運(yùn)算結(jié)果的 Hash 值和所對(duì)應(yīng)的行指針信息存放于一個(gè) Hash 表中筹误，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個(gè) Hash 鍵值的數(shù)據(jù)的記錄條數(shù)癣缅，也無法從 Hash 索引中直接完成查詢厨剪，還是要通過訪問表中的實(shí)際數(shù)據(jù)進(jìn)行相應(yīng)的比較，并得到相應(yīng)的結(jié)果
- Hash 索引遇到大量Hash值相等的情況后性能并不一定就會(huì)比B-Tree索引高友存。
- B-Tree索引可以被用在像=,>,>=,<,<=和BETWEEN這些比較操作符上丽惶。而且還可以用于LIKE操作符，只要它的查詢條件是一個(gè)不以通配符開頭的常量
- innodb和myisam存儲(chǔ)引擎不能使用hash索引.........
擴(kuò)展：圖找不到了爬立。 - Hash索引：
Hash 索引結(jié)構(gòu)的特殊性，其檢索效率非常高万哪，索引的檢索可以一次定位侠驯，不像B-Tree 索引需要從根節(jié)點(diǎn)到枝節(jié)點(diǎn)，最后才能訪問到頁節(jié)點(diǎn)這樣多次的IO訪問奕巍，所以 Hash 索引的查詢效率要遠(yuǎn)高于 B-Tree 索引吟策。
可能很多人又有疑問了，既然 Hash 索引的效率要比 B-Tree 高很多的止，為什么大家不都用 Hash 索引而還要使用 B-Tree 索引呢檩坚？任何事物都是有兩面性的，Hash 索引也一樣，雖然 Hash 索引效率高匾委，但是 Hash 索引本身由于其特殊性也帶來了很多限制和弊端拖叙，主要有以下這些。
（1）Hash 索引僅僅能滿足”=”,”IN”和”<=>”查詢赂乐，不能使用范圍查詢薯鳍。
由于 Hash 索引比較的是進(jìn)行 Hash 運(yùn)算之后的 Hash 值，所以它只能用于等值的過濾挨措，不能用于基于范圍的過濾挖滤，因?yàn)榻?jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系，并不能保證和Hash運(yùn)算前完全一樣浅役。
（2）Hash 索引無法被用來避免數(shù)據(jù)的排序操作斩松。
由于 Hash 索引中存放的是經(jīng)過 Hash 計(jì)算之后的 Hash 值，而且Hash值的大小關(guān)系并不一定和 Hash 運(yùn)算前的鍵值完全一樣觉既，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運(yùn)算惧盹；
（3）Hash 索引不能利用部分索引鍵查詢。
對(duì)于組合索引奋救，Hash 索引在計(jì)算 Hash 值的時(shí)候是組合索引鍵合并后再一起計(jì)算 Hash 值岭参，而不是單獨(dú)計(jì)算 Hash 值，所以通過組合索引的前面一個(gè)或幾個(gè)索引鍵進(jìn)行查詢的時(shí)候尝艘，Hash 索引也無法被利用演侯。
（4）Hash 索引在任何時(shí)候都不能避免表掃描。
前面已經(jīng)知道背亥，Hash 索引是將索引鍵通過 Hash 運(yùn)算之后秒际，將 Hash運(yùn)算結(jié)果的 Hash 值和所對(duì)應(yīng)的行指針信息存放于一個(gè) Hash 表中，由于不同索引鍵存在相同 Hash 值狡汉，所以即使取滿足某個(gè) Hash 鍵值的數(shù)據(jù)的記錄條數(shù)娄徊，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實(shí)際數(shù)據(jù)進(jìn)行相應(yīng)的比較盾戴，并得到相應(yīng)的結(jié)果寄锐。
（5）Hash 索引遇到大量Hash值相等的情況后性能并不一定就會(huì)比B-Tree索引高。
對(duì)于選擇性比較低的索引鍵尖啡，如果創(chuàng)建 Hash 索引橄仆，那么將會(huì)存在大量記錄指針信息存于同一個(gè) Hash 值相關(guān)聯(lián)。這樣要定位某一條記錄時(shí)就會(huì)非常麻煩衅斩，會(huì)浪費(fèi)多次表數(shù)據(jù)的訪問盆顾，而造成整體性能低下 - B-Tree索引
B-Tree 索引是 MySQL 數(shù)據(jù)庫中使用最為頻繁的索引類型，除了 Archive 存儲(chǔ)引擎之外的其他所有的存儲(chǔ)引擎都支持 B-Tree 索引畏梆。不僅僅在 MySQL 中是如此您宪，實(shí)際上在其他的很多數(shù)據(jù)庫管理系統(tǒng)中B-Tree 索引也同樣是作為最主要的索引類型奈懒，這主要是因?yàn)?B-Tree 索引的存儲(chǔ)結(jié)構(gòu)在數(shù)據(jù)庫的數(shù)據(jù)檢 索中有非常優(yōu)異的表現(xiàn)。
一般來說宪巨， MySQL 中的 B-Tree 索引的物理文件大多都是以 Balance Tree 的結(jié)構(gòu)來存儲(chǔ)的磷杏，也就是所有實(shí)際需要的數(shù)據(jù)都存放于 Tree 的 Leaf Node ，而且到任何一個(gè) Leaf Node 的最短路徑的長(zhǎng)度都是完全相同的揖铜，所以我們大家都稱之為 B-Tree 索引當(dāng)然茴丰，可能各種數(shù)據(jù)庫（或 MySQL 的各種存儲(chǔ)引擎）在存放自己的 B-Tree 索引的時(shí)候會(huì)對(duì)存儲(chǔ)結(jié)構(gòu)稍作改造。如 Innodb 存儲(chǔ)引擎的 B-Tree 索引實(shí)際使用的存儲(chǔ)結(jié)構(gòu)實(shí)際上是 B+Tree 天吓，也就是在 B-Tree 數(shù)據(jù)結(jié)構(gòu)的基礎(chǔ)上做了很小的改造贿肩，在每一個(gè)
Leaf Node 上面出了存放索引鍵的相關(guān)信息之外，還存儲(chǔ)了指向與該 Leaf Node 相鄰的后一個(gè) LeafNode 的指針信息龄寞，這主要是為了加快檢索多個(gè)相鄰 Leaf Node 的效率考慮汰规。
在 Innodb 存儲(chǔ)引擎中，存在兩種不同形式的索引物邑，一種是 Cluster 形式的主鍵索引（ Primary Key ）溜哮，另外一種則是和其他存儲(chǔ)引擎（如 MyISAM 存儲(chǔ)引擎）存放形式基本相同的普通 B-Tree 索引，這種索引在 Innodb 存儲(chǔ)引擎中被稱為 Secondary Index 色解。下面我們通過圖示來針對(duì)這兩種索引的存放形式做一個(gè)比較茂嗓。
圖示中左邊為 Clustered 形式存放的 Primary Key ，右側(cè)則為普通的 B-Tree 索引科阎。兩種 Root Node 和 Branch Nodes 方面都還是完全一樣的述吸。而 Leaf Nodes 就出現(xiàn)差異了。在 Prim中锣笨， Leaf Nodes 存放的是表的實(shí)際數(shù)據(jù)蝌矛，不僅僅包括主鍵字段的數(shù)據(jù)，還包括其他字段的數(shù)據(jù)據(jù)以主鍵值有序的排列错英。而 Secondary Index 則和其他普通的 B-Tree 索引沒有太大的差異入撒，Leaf Nodes 出了存放索引鍵 的相關(guān)信息外，還存放了 Innodb 的主鍵值椭岩。
所以茅逮，在 Innodb 中如果通過主鍵來訪問數(shù)據(jù)效率是非常高的，而如果是通過 Secondary Index 來訪問數(shù)據(jù)的話判哥， Innodb 首先通過 Secondary Index 的相關(guān)信息氮唯，通過相應(yīng)的索引鍵檢索到 Leaf Node之后，需要再通過 Leaf Node 中存放的主鍵值再通過主鍵索引來獲取相應(yīng)的數(shù)據(jù)行姨伟。MyISAM 存儲(chǔ)引擎的主鍵索引和非主鍵索引差別很小，只不過是主鍵索引的索引鍵是一個(gè)唯一且非空 的鍵而已豆励。而且 MyISAM 存儲(chǔ)引擎的索引和 Innodb 的 Secondary Index 的存儲(chǔ)結(jié)構(gòu)也基本相同夺荒，主要的區(qū)別只是 MyISAM 存儲(chǔ)引擎在 Leaf Nodes 上面出了存放索引鍵信息之外瞒渠，再存放能直接定位到 MyISAM 數(shù)據(jù)文件中相應(yīng)的數(shù)據(jù)行的信息（如 Row Number ），但并不會(huì)存放主鍵的鍵值信息
問題4：MySQL Explain
在日常工作中技扼，我們會(huì)有時(shí)會(huì)開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時(shí)間比較久的SQL語句伍玖，找出這些SQL語句并不意味著完事了，些時(shí)我們常常用到explain這個(gè)命令來查看一個(gè)這些SQL語句的執(zhí)行計(jì)劃剿吻，查看該SQL語句有沒有使用上了索引窍箍，有沒有做全表掃描，這都可以通過explain命令來查看丽旅。所以我們深入了解MySQL的基于開銷的優(yōu)化器椰棘，還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細(xì)節(jié)，以及當(dāng)運(yùn)行SQL語句時(shí)哪種策略預(yù)計(jì)會(huì)被優(yōu)化器采用榄笙。
-- 實(shí)際SQL邪狞，查找用戶名為Jefabc的員工select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引，前面加上explain即可
explain select * from emp where name = 'Jefabc';
expain出來的信息有10列茅撞，分別是id帆卓、select_type、table米丘、type剑令、possible_keys、key拄查、key_len吁津、ref、rows靶累、Extra
概要描述：
id:選擇標(biāo)識(shí)符
select_type:表示查詢的類型腺毫。
table:輸出結(jié)果集的表
partitions:匹配的分區(qū)
type:表示表的連接類型
possible_keys:表示查詢時(shí)，可能使用的索引
key:表示實(shí)際使用的索引
key_len:索引字段的長(zhǎng)度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表?xiàng)l件過濾的行百分比
Extra:執(zhí)行情況的描述和說明
下面對(duì)這些字段出現(xiàn)的可能進(jìn)行解釋：
一挣柬、 id
SELECT識(shí)別符潮酒。這是SELECT的查詢序列號(hào)
我的理解是SQL執(zhí)行的順序的標(biāo)識(shí)，SQL從大到小的執(zhí)行
- id相同時(shí)邪蛔，執(zhí)行順序由上至下
- 如果是子查詢急黎，id的序號(hào)會(huì)遞增，id值越大優(yōu)先級(jí)越高侧到，越先被執(zhí)行
- id如果相同勃教，可以認(rèn)為是一組，從上往下順序執(zhí)行匠抗；在所有組中故源，id值越大，優(yōu)先級(jí)越高汞贸，越先執(zhí)行
-- 查看在研發(fā)部并且名字以Jef開頭的員工绳军，經(jīng)典查詢
explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研發(fā)部';
二印机、select_type
示查詢中每個(gè)select子句的類型
(1) SIMPLE(簡(jiǎn)單SELECT，不使用UNION或子查詢等)
(2) PRIMARY(子查詢中最外層查詢门驾，查詢中若包含任何復(fù)雜的子部分射赛，最外層的select被標(biāo)記為PRIMARY)
(3) UNION(UNION中的第二個(gè)或后面的SELECT語句)
(4) DEPENDENT UNION(UNION中的第二個(gè)或后面的SELECT語句，取決于外面的查詢)
(5) UNION RESULT(UNION的結(jié)果奶是，union語句中第二個(gè)select開始后面所有select)
(6) SUBQUERY(子查詢中的第一個(gè)SELECT楣责，結(jié)果不依賴于外部查詢)
(7) DEPENDENT SUBQUERY(子查詢中的第一個(gè)SELECT，依賴于外部查詢)
(8) DERIVED(派生表的SELECT, FROM子句的子查詢)
(9) UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(jié)果不能被緩存聂沙，必須重新評(píng)估外鏈接的第一行)
三秆麸、table
顯示這一步所訪問數(shù)據(jù)庫中表名稱（顯示這一行的數(shù)據(jù)是關(guān)于哪張表的），有時(shí)不是真實(shí)的表名字逐纬，可能是簡(jiǎn)稱蛔屹，例如上面的e，d豁生，也可能是第幾步執(zhí)行的結(jié)果的簡(jiǎn)稱
四兔毒、type
對(duì)表訪問方式，表示MySQL在表中找到所需行的方式甸箱，又稱“訪問類型”育叁。
常用的類型有： ALL、index芍殖、range豪嗽、 ref、eq_ref豌骏、const龟梦、system、NULL（從左到右窃躲，性能從差到好）
ALL：Full Table Scan计贰， MySQL將遍歷全表以找到匹配的行
index: Full Index Scan，index與ALL區(qū)別為index類型只遍歷索引樹
range:只檢索給定范圍的行蒂窒，使用一個(gè)索引來選擇行
ref: 表示上述表的連接匹配條件躁倒，即哪些列或常量被用于查找索引列上的值
eq_ref: 類似ref，區(qū)別就在使用的索引是唯一索引洒琢，對(duì)于每個(gè)索引鍵值秧秉，表中只有一條記錄匹配，簡(jiǎn)單來說衰抑，就是多表連接中使用primary key或者 unique key作為關(guān)聯(lián)條件
const象迎、system: 當(dāng)MySQL對(duì)查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)呛踊，使用這些類型訪問砾淌。如將主鍵置于where列表中完丽，MySQL就能將該查詢轉(zhuǎn)換為一個(gè)常量，system是const類型的特例拇舀，當(dāng)查詢的表只有一行的情況下，使用system
NULL: MySQL在優(yōu)化過程中分解語句蜻底，執(zhí)行時(shí)甚至不用訪問表或索引骄崩，例如從一個(gè)索引列里選取最小值可以通過單獨(dú)索引查找完成。
五薄辅、possible_keys
指出MySQL能使用哪個(gè)索引在表中找到記錄要拂，查詢涉及到的字段上若存在索引站楚，則該索引將被列出脱惰，但不一定被查詢使用（該查詢可以利用的索引，如果沒有任何索引顯示 null）
該列完全獨(dú)立于EXPLAIN輸出所示的表的次序窿春。這意味著在possible_keys中的某些鍵實(shí)際上不能按生成的表次序使用拉一。
如果該列是NULL，則沒有相關(guān)的索引旧乞。在這種情況下蔚润，可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣尺栖，創(chuàng)造一個(gè)適當(dāng)?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢
六嫡纠、Key
key列顯示MySQL實(shí)際決定使用的鍵（索引），必然包含在possible_keys中
如果沒有選擇索引延赌，鍵是NULL除盏。要想強(qiáng)制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX挫以、USE INDEX或者IGNORE INDEX者蠕。
七、key_len
表示索引中使用的字節(jié)數(shù)屡贺，可通過該列計(jì)算查詢中使用的索引的長(zhǎng)度（key_len顯示的值為索引字段的最大可能長(zhǎng)度蠢棱，并非實(shí)際使用長(zhǎng)度，即key_len是根據(jù)表定義計(jì)算而得甩栈，不是通過表內(nèi)檢索出的）
不損失精確性的情況下泻仙，長(zhǎng)度越短越好
八、ref
列與索引的比較量没，表示上述表的連接匹配條件玉转，即哪些列或常量被用于查找索引列上的值
九、rows
估算出結(jié)果集行數(shù)殴蹄，表示MySQL根據(jù)表統(tǒng)計(jì)信息及索引選用情況究抓，估算的找到所需的記錄所需要讀取的行數(shù)
十猾担、Extra
該列包含MySQL解決查詢的詳細(xì)信息,有以下幾種情況：
Using where:不用讀取表中所有信息，僅通過索引就可以獲取所需數(shù)據(jù)刺下，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候绑嘹，表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過濾
Using temporary：表示MySQL需要使用臨時(shí)表來存儲(chǔ)結(jié)果集，常見于排序和分組查詢橘茉，常見 group by ; order by
Using filesort：當(dāng)Query中包含 order by 操作工腋，而且無法利用索引完成的排序操作稱為“文件排序”
-- 測(cè)試Extra的filesort
explain select * from emp order by name;
Using join buffer：改值強(qiáng)調(diào)了在獲取連接條件時(shí)沒有使用索引，并且需要連接緩沖區(qū)來存儲(chǔ)中間結(jié)果畅卓。如果出現(xiàn)了這個(gè)值擅腰，那應(yīng)該注意，根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)能翁潘。
Impossible where：這個(gè)值強(qiáng)調(diào)了where語句會(huì)導(dǎo)致沒有符合條件的行（通過收集統(tǒng)計(jì)信息不可能存在結(jié)果）趁冈。
Select tables optimized away：這個(gè)值意味著僅通過使用索引，優(yōu)化器可能僅從聚合函數(shù)結(jié)果中返回一行
No tables used：Query語句中使用from dual 或不含任何from子句
-- explain select now() from dual;
總結(jié)：
? EXPLAIN不會(huì)告訴你關(guān)于觸發(fā)器拜马、存儲(chǔ)過程的信息或用戶自定義函數(shù)對(duì)查詢的影響情況
? EXPLAIN不考慮各種Cache
? EXPLAIN不能顯示MySQL在執(zhí)行查詢時(shí)所作的優(yōu)化工作
? 部分統(tǒng)計(jì)信息是估算的渗勘，并非精確值
? EXPALIN只能解釋SELECT操作，其他操作要重寫為SELECT后查看執(zhí)行計(jì)劃一膨。
通過收集統(tǒng)計(jì)信息不可能存在結(jié)果
