一错洁、索引? ? ??
使用索引是數(shù)據(jù)庫性能優(yōu)化的必備技能之一秉宿。
1、MySQL常見的四種索引類型：
索引InnoDB引擎MyISAM引擎Memory引擎B-Tree索引支持支持支持HASH索引不支持不支持支持R-Tree索引不支持支持不支持Full-text索引5.6版本后支持支持不支持
2屯碴、四種索引的特點比較：
1）哈希索引
哈希表是一種以鍵 - 值（key-value）存儲數(shù)據(jù)的結(jié)構(gòu)描睦，我們只要輸入待查找的鍵即 key，就可以找到其對應(yīng)的值即 Value导而。哈希的思路很簡單忱叭，把值放在數(shù)組里，用一個哈希函數(shù)把 key 換算成一個確定的位置嗡载，然后把 value 放在數(shù)組的這個位置窑多。不可避免地，多個 key 值經(jīng)過哈希函數(shù)的換算洼滚，會出現(xiàn)同一個值的情況埂息。處理這種情況的一種方法是，拉出一個鏈表遥巴。
優(yōu)點：速度會很快千康，只需要往后追加。
缺點：
（1）Hash 索引僅僅能滿足"=",“IN"和”<=>"查詢铲掐，不能使用范圍查詢拾弃，無法被用來避免數(shù)據(jù)的排序操作。
（2）由于 Hash 索引比較的是進行 Hash 運算之后的 Hash 值摆霉，所以它只能用于等值的過濾豪椿，不能用于基于范圍的過濾奔坟，因為經(jīng)過相應(yīng)的 Hash 算法處理之后的 Hash 值的大小關(guān)系，并不能保證和Hash運算前完全一樣搭盾，所以數(shù)據(jù)庫無法利用索引的數(shù)據(jù)來避免任何排序運算咳秉；也就是因為不是有序的，所以哈希索引做區(qū)間查詢的速度是很慢的鸯隅，比如 Memcached 及其他一些 NoSQL 引擎澜建。
（3）Hash 索引不能利用部分索引鍵查詢。對于組合索引蝌以，Hash 索引在計算 Hash 值的時候是組合索引鍵合并后再一起計算 Hash 值炕舵，而不是單獨計算 Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候跟畅，Hash 索引也無法被利用咽筋。
（4）Hash 索引在任何時候都不能避免表掃描。前面已經(jīng)知道碍彭，Hash 索引是將索引鍵通過 Hash 運算之后晤硕，將 Hash運算結(jié)果的 Hash 值和所對應(yīng)的行指針信息存放于一個 Hash 表中，由于不同索引鍵存在相同 Hash 值庇忌，所以即使取滿足某個 Hash 鍵值的數(shù)據(jù)的記錄條數(shù)，也無法從 Hash 索引中直接完成查詢皆疹，還是要通過訪問表中的實際數(shù)據(jù)進行相應(yīng)的比較，并得到相應(yīng)的結(jié)果略就。
（5）Hash 索引遇到大量Hash值相等的情況后性能變慢。對于選擇性比較低的索引鍵表牢，如果創(chuàng)建 Hash 索引贝次，那么將會存在大量記錄指針信息存于同一個 Hash 值相關(guān)聯(lián)。這樣要定位某一條記錄時就會非常麻煩蛔翅，會浪費多次表數(shù)據(jù)的訪問，而造成整體性能低下山析。
2）B-Tree索引
二叉搜索樹：這個時間復(fù)雜度是 O(log(N))。平衡二叉樹-：它是一 棵空樹或它的左右兩個子樹的高度差的絕對值不超過1笋轨，并且左右兩個子樹都是一棵平衡二叉樹（AVL樹）赊淑。
深度越大仅讽，從磁盤讀數(shù)據(jù)塊需要的尋址時間就越長陶缺。為了讓一個查詢盡量少地讀磁盤，使用“N 叉”樹=》平衡多路查找樹（B樹）（mangodb用的b樹索引）何什。
B 樹定義：
? 根節(jié)點至少有兩個子節(jié)點
? 每個節(jié)點有M-1個key组哩，并且以升序排列
? 位于M-1和M key的子節(jié)點的值位于M-1 和M key對應(yīng)的Value之間? 其它節(jié)點至少有M/2個子節(jié)點
B+ 樹:
? B+樹是對B樹的一種變形樹，它與B樹的差異在于：
? 有k個子結(jié)點的結(jié)點必然有k個關(guān)鍵碼处渣；
? 非葉結(jié)點僅具有索引作用伶贰，跟記錄有關(guān)的信息均存放在葉結(jié)點中。? 樹的所有葉結(jié)點構(gòu)成一個有序鏈表罐栈，可以按照關(guān)鍵碼排序的次序遍歷全部記錄黍衙。
3）RTREE索引
RTREE在mysql很少使用，僅支持geometry數(shù)據(jù)類型荠诬。
4）全文索引
全文索引(也稱全文檢索)是目前搜索引擎使用的一種關(guān)鍵技術(shù)琅翻。它能夠利用分詞技術(shù)等多種算法智能分析出文本文字中關(guān)鍵字詞的頻率及重要性，然后按照一定的算法規(guī)則智能地篩選出我們想要的搜索結(jié)果柑贞。必須使用特有的語法才能使用全文索引進行查詢方椎。
例如，我們想要在article表的title和content列中全文檢索指定的查詢字符串钧嘶，可以如下編寫SQL語句：SELECT * FROM article WHERE MATCH(title, content) AGAINST(‘查詢字符串’)MySQL自帶的全文索引只能對英文進行全文檢索棠众，目前無法對中文進行全文檢索。如果需要對包含中文在內(nèi)的文本數(shù)據(jù)進行全文檢索有决，我們需要采用Sphinx(斯芬克斯)/Coreseek技術(shù)來處理中文闸拿。
3、為什么MySQL選擇B+樹做索引
對于BTREE這種Mysql默認的索引類型书幕，具有普遍的適用性新荤。
1）B+樹的磁盤讀寫代價更低：B+樹的內(nèi)部節(jié)點并沒有指向關(guān)鍵字具體信息的指針，因此其內(nèi)部節(jié)點相對B樹更小台汇，如果把所有同一內(nèi)部節(jié)點的關(guān)鍵字存放在同一盤塊中苛骨，那么盤塊所能容納的關(guān)鍵字數(shù)量也越多，一次性讀入內(nèi)存的需要查找的關(guān)鍵字也就越多励七，相對IO讀寫次數(shù)就降低了智袭。
2）B+樹的查詢效率更加穩(wěn)定：由于非終結(jié)點并不是最終指向文件內(nèi)容的結(jié)點，而只是葉子結(jié)點中關(guān)鍵字的索引掠抬。所以任何關(guān)鍵字的查找必須走一條從根結(jié)點到葉子結(jié)點的路吼野。所有關(guān)鍵字查詢的路徑長度相同两波，導(dǎo)致每一個數(shù)據(jù)的查詢效率相當(dāng)闷哆。
3）B+樹更便于遍歷：由于B+樹的數(shù)據(jù)都存儲在葉子結(jié)點中抱怔，分支結(jié)點均為索引屈留，方便掃庫灌危，只需要掃一遍葉子結(jié)點即可勇蝙，但是B樹因為其分支結(jié)點同樣存儲著數(shù)據(jù)味混，我們要找到具體的數(shù)據(jù)翁锡，需要進行一次中序遍歷按序來掃盗誊，所以B+樹更加適合在區(qū)間查詢的情況，所以通常B+樹用于數(shù)據(jù)庫索引荒适。
4）B+樹更適合基于范圍的查詢：B樹在提高了IO性能的同時并沒有解決元素遍歷的我效率低下的問題刀诬，正是為了解決這個問題陕壹，B+樹應(yīng)用而生。B+樹只需要去遍歷葉子節(jié)點就可以實現(xiàn)整棵樹的遍歷嘶伟。而且在數(shù)據(jù)庫中基于范圍的查詢是非常頻繁的九昧，而B樹不支持這樣的操作或者說效率太低。
小結(jié)：MySQL 默認的存儲引擎選擇 B+ 樹而不是哈习┍穑或者 B 樹的原因：
哈希雖然能夠提供 O(1) 的單數(shù)據(jù)行操作性能展姐，但是對于范圍查詢和排序卻無法很好地支持圾笨，最終導(dǎo)致全表掃描速兔；B 樹能夠在非葉節(jié)點中存儲數(shù)據(jù)涣狗，但是這也導(dǎo)致在查詢連續(xù)數(shù)據(jù)時可能會帶來更多的隨機 I/O穗熬，而 B+ 樹的所有葉節(jié)點可以通過指針相互連接丁溅，能夠減少順序遍歷時產(chǎn)生的額外隨機 I/O窟赏；
使用建議：
以下兩點必須使用 InnoDB：
1）可靠性高或者要求事務(wù)處理涯穷，則使用InnoDB。這個是必須的作煌。2）表更新和查詢都相當(dāng)?shù)念l繁粟誓，并且表鎖定的機會比較大的情況指定InnoDB數(shù)據(jù)引擎的創(chuàng)建鹰服。
對比之下获诈，MyISAM的使用場景：
1）做很多count計算的。如一些日志笼踩，調(diào)查的業(yè)務(wù)表嚎于。2）插入修改不頻繁于购，查詢非常頻繁的肋僧。
4控淡、索引失效
1）對索引字段做函數(shù)操作掺炭，可能會破壞索引值的有序性涧狮，因此優(yōu)化器就決定放棄走樹搜索功能。2）隱式類型轉(zhuǎn)換肤视。
3）隱式字符編碼轉(zhuǎn)換
Eg：兩個表的字符集不同钢颂，一個是 utf8，一個是 utf8mb4尼桶，所以做表連接查詢的時候用不上關(guān)聯(lián)字段的索引泵督。字符集 utf8mb4 是 utf8 的超集小腊，所以當(dāng)這兩個類型的字符串在做比較的時候，MySQL 內(nèi)部的操作是本缠，先把 utf8 字符串轉(zhuǎn)成 utf8mb4 字符集丹锹，再做比較楣黍。這個設(shè)定很好理解租漂，在程序設(shè)計語言里面哩治，做自動類型轉(zhuǎn)換的時候锚扎，為了避免數(shù)據(jù)在轉(zhuǎn)換過程中由于截斷導(dǎo)致數(shù)據(jù)錯誤，也都是“按數(shù)據(jù)長度增加的方向”進行轉(zhuǎn)換的惯疙。
可以通過 explain 檢查 SQL 的執(zhí)行計劃霉颠，以確認是否真正使用了索引。
5朽们、InnoDB和MyISAM是B+樹，有什么區(qū)別苍糠？
根據(jù)葉子節(jié)點的內(nèi)容，索引類型分為主鍵索引和非主鍵索引蚊锹。
主鍵索引也被稱為聚簇索引（clustered index）牡昆。非主鍵索引也被稱為二級索引（secondary index）迁杨，即非聚集索引。
1）MyISAM索引結(jié)構(gòu)（非聚集索引）
2）InnoDB聚簇索引：
InnoDB對主鍵建立聚簇索引。如果你不指定主鍵说墨，InnoDB會用一個具有唯一且非空值的索引來代替姜贡。如果不存在這樣的索引楼咳，,InnoDB 存儲引擎會為每一行生成一個 6 字節(jié)的 ROWID,并以此作為隱藏的主鍵母怜。然后對其建立聚簇索引（主鍵索引）苹熏。
二轨域、索引及索引優(yōu)化? ??
例：mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))engine=InnoDB;
如果語句是 select * from T where ID=500，即主鍵查詢方式，則只需要搜索 ID 這棵 B+ 樹；如果語句是 select * from T where k=5恶座，即普通索引查詢方式搀暑，則需要先搜索 k 索引樹，得到 ID 的值為 500跨琳，再到 ID 索引樹搜索一次自点。這個過程稱為回表。
如果語句是 select ID from T where k between 3 and 5脉让，這時只需要查 ID 的值桂敛，而 ID 的值已經(jīng)在 k 索引樹上了，因此可以直接提供查詢結(jié)果溅潜，不需要回表术唬。也就是說，在這個查詢里面滚澜，索引 k 已經(jīng)“覆蓋了”我們的查詢需求萝招，我們稱為覆蓋索引。（eg: 在一個市民信息表上,將身份證號和名字建立聯(lián)合索引, 高頻請求通過身份證查回姓名就不需要回表了，直接覆蓋索引就可以）
如果用身份證號做主鍵，那么每個二級索引的葉子節(jié)點占用約 20 個字節(jié)，而如果用整型做主鍵，則只要 4 個字節(jié)杂数，如果是長整型（bigint）則是 8 個字節(jié)那伐。顯然养距，主鍵長度越小，普通索引的葉子節(jié)點就越小，普通索引占用的空間也就越小畸陡。
B+ 樹這種索引結(jié)構(gòu)，可以利用索引的“最左前綴”，來定位記錄。這個最左前綴可以是聯(lián)合索引的最左 N 個字段，也可以是字符串索引的最左 M 個字符科侈，遇到范圍查詢(>、<、between橄仍、like)就會停止匹配娩贷。Eg：“where name like ‘張 %’”储笑。這時，你也能夠用上姓名這個索引。利用索引的“最左前綴”，建立一個復(fù)合索引（a，b驱富，c），相當(dāng)于建立了多個索引(a),(a,b),(a,b,c); 在建立聯(lián)合索引的時候，如何安排索引內(nèi)的字段順序變得重要躏将。
MySQL 5.6 引入的索引下推優(yōu)化（index condition pushdown)掸鹅， 可以在索引遍歷過程中矮嫉，對索引中包含的字段先做判斷熟史，直接過濾掉不滿足條件的記錄，減少回表次數(shù)。Eg：有一個需求：檢索出表中“名字第一個字是張妈橄，而且年齡是 10 歲的所有男孩”眷蚓。那么篙贸，SQL 語句是這么寫的：mysql> select * from tuser where name like ‘張%’ and age=10 and ismale=1;
總結(jié)：B+ 樹為了維護索引有序性谎碍，在插入新值或者刪除數(shù)據(jù)的時候需要做必要的維護抢韭，進行頁分裂或者頁合并，直接影響數(shù)據(jù)庫性能刻恭。用自增主鍵瞧省，有利于維護索引，插入數(shù)據(jù)快鳍贾，是追加操作減少頁分裂鞍匾；如果用業(yè)務(wù)中唯一索引列做主鍵，索引長度可能會大骑科，但是避免了搜索兩顆樹候学，避免了回表。根據(jù)業(yè)務(wù)場景選擇怎么建索引纵散。由于覆蓋索引可以減少樹的搜索次數(shù)梳码，顯著提升查詢性能，所以使用覆蓋索引是一個常用的性能優(yōu)化手段伍掀。
