1,什么是索引，為什么要使用索引？
索引是幫助Mysql高效獲取資料的排好序的資料結構，建立索引可以幫助我們快速檢索我們需要的資訊，減少磁盤的I/O次數，加快檢索速度，索引的資料結構包括：二叉樹，紅黑樹，Hash表，B-樹等，
2,資料查詢的方式
二叉查找樹的方式查找資訊
普通情況下，在一個二叉樹中查找到5非常快速，只需要2步，但在某些極端情況下，如對于順序插入資訊的二叉樹，查找到我們需要的資訊就非常麻煩了，
紅黑樹
紅黑樹是一種自平衡二叉查找樹，就是會自動平衡更新根節點和其它節點的位置，也就是說根節點和其它節點的位置是不固定，會根據插入的資訊進行動態改變的，
按照1~5的順序插入資料后，會重新排列分支節點的位置，
對于少量資料使用紅黑樹是完全可以的，但是一個專案的資料庫中存盤的資料都是10萬條以上，再使用紅黑樹，樹的度又會變得很大了，這個時候就可以使用B 樹了，
B 樹
B樹是一種多路自平衡搜索樹，它是一種特殊的平衡二叉樹，但B樹允許每個節點有更多的子節點，B樹示意圖如下：
B樹的特點：
（1）所有鍵值分布在整個樹中（2）任何關鍵字出現且只出現在一個節點中
（3）搜索有可能在非葉子節點結束（4）在關鍵字全集內做一次查找，性能逼近二分查找演算法
比如我們想要找到主鍵為28的資料，程序如下：
1，根據根節點找到磁盤塊1,進行一次I/O(磁盤的輸入和輸出)操作;
2,比較關鍵字28在區間(17,35),發現位于兩者之間，找到P2指標;
3,根據P2指標找到磁盤塊3，再進行一次I/O操作；
4,比較關鍵字28在區間(26,30),發現位于兩者之間，找到磁盤塊3的P2指標;
5,根據P2指標找到磁盤塊8，再進行一次I/O操作；
6，在磁盤塊8找到主鍵為28的資料，
在上面的程序中，只進行了3次磁盤I/O操作和3次記憶體查找操作，與二叉樹和紅黑樹相比，對磁盤和記憶體讀寫次數減少了很多，不僅如此，B樹中的各個節點都是可以橫向擴展的，也就是說，不管有多少條資料，獲取我們想要的資料對磁盤和記憶體的讀寫不超過四次，而B樹四層的深度就可以滿足百億左右資料的使用了，
雖然B樹很不錯，但由于資料的鍵和值都儲存在B樹中，而有些資料中不僅僅包含數字，還可能包含大文本或是圖片的二進制檔案，那么這個二叉樹立馬就大了起來，而一個索引一般情況下也只有16K(可以將索引看做是表中特殊的頁，Mysql中一個頁大小只有16K)，導致原本能夠容納上億資訊量的B樹由于資料過多無法橫向擴展，B+樹應運而生，
B+樹
B+樹是在B-樹基礎上的一種優化，使其更適合實作外存盤索引結構，在B+樹中，所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存盤key值資訊，這樣可以大大加大每個節點存盤的key值數量，降低B+樹的深度，B+樹示意圖如下：
所有資料記錄節點都是按照鍵值大小順序存放在同一層的葉子節點上，而非葉子節點上只存盤key值資訊，這是B+樹與B樹最大的區別，并且B+樹的葉子結點都是以雙向鏈表的方式連接的，因此對整棵樹的遍歷只需要一次線性遍歷葉子結點即可，而且由于資料順序排列并且相連，所以便于區間查找和搜索，而B樹則需要進行每一層的遞回遍歷，相鄰的元素可能在記憶體中不相鄰，葉子節點中的data區域存盤的不是和key值有關的資料表的資訊，而是資訊所在的磁盤地址，現在大部分資料庫的索引型別都是B+樹型別，
3.索引的本質
索引就是一顆B+樹，一種為了磁盤或是其它直接存取輔助設計的一種平衡查找樹，所有記錄節點按照鍵值順序存放在同一層的葉子節點上，B+樹的根節點存放在記憶體中，而非葉子節點和葉子節點存放在磁盤中，所以查找一條資訊最多只需要進行3次磁盤I/O操作，
4，索引的分類
4.1、從資料結構
4.1.1 全文索引
FULLTEXT INDEX 是針對一篇文章或是字符的索引，全文索引和sql陳述句“where xx like xxx%”功能差不多，但是在大量的資料面前，使用全文索引查詢速度更快，
目前，Mysql版本在5.6以上的版本，InnoDB和MyISAM存盤引擎都支持全文索引，5.6以下版本只有MyISAM支持全文索引，
那么多少條資訊可以使用全文索引呢，
MySQL 中的全文索引，有兩個變數，最小搜索長度和最大搜索長度，對于長度小于最小搜索長度和大于最大搜索長度的詞語，都不會被索引，通俗點就是說，想對一個詞語使用全文索引搜索，那么這個詞語的長度必須在以上兩個變數的區間內，我們可以通過以下陳述句進行查詢，
show variables like '%ft%';
MyISAM的最小和最大長度分別為4，84；而InnoDB的為3，84；只有長度在這個區間中的資訊才能正常使用全文索引，反之則不會獲取到資訊，
雖然我們能夠使用全文索引，但是MySQL的全文索引效率很低，一般我們通過都第三方工具代替，如Elasticsearch，
//創建全文索引 create fulltext index getcontent on account(content); //洗掉全文索引 drop index getcontent on account; alter table account drop index getcontent;
4.1.2 B+樹索引
這類索引是各種資料庫中最常見的索引結構，也是InnoDB使用的索引，
4.1.3 Hash索引
哈希索引（hash index）基于哈希表實作，只有精確匹配索引所有列的查詢才有效，對于每一行資料，存盤引擎都會對所有的索引列計算一個哈希碼（hash code），哈希碼是一個較小的值，并且不同鍵值的行計算出來的哈希碼也不一樣.由于生成的哈希碼是隨機的，所以無法進行范圍查找，只能進行等值比較查詢，也就是只能進行=,!=,in,not in等操作，
4.1.4 R樹索引
R樹索引也被稱為空間樹索引，用于解決索引地理坐標，很好的解決了在高維空間搜索等問題，但在MySQL中不存在，其它資料庫存在如SQLite，
4.2、從邏輯角度
4.2.1 非空索引
要求資料不能為慷訓是null，
alter table account add constraint CN_Cons check( content is not null)
4.2.2 唯一索引
要求資料內容不重復，但是可以為慷訓是NULL,但只能有一個NULL和一個空，
creatre unique index con account(content)
4.2.3 主鍵索引
是一種特殊的唯一索引，并且不能有空值
4.2.4 復合索引
用戶可以在多個列上建立索引,叫復合索引，
create index ico on table1(id,name,content);
對于復合索引,在查詢使用時,最好將條件順序按找索引的順序,這樣效率最高;
select * from account where id= A and name= B and content= C;//會使用復合索引 select * from account where user= B and content= C;//不會使用復合索引
復合索引遵循最左匹配原則，即 where id=A and name=B可以用到索引，而 where name=B and content=C是不會用到復合索引的，因為B+樹是按照資料庫欄位從左到右的順序來建立搜索樹的，B+樹會優先比較id來確定下一步的所搜方向，如果id相同再依次比較name和content，最后得到檢索的資料；但當查詢陳述句沒有id的資料的時候，B+樹就不知道下一步該查哪個節點，因為建立搜索樹的時候id就是第一個比較因子，必須要先根據id來搜索才能知道下一步去哪里查詢，而如果缺少中間查詢欄位user時，資料庫會先把id相同的資訊查詢到，然后在匹配content是C的資料，
4.3、從物理存盤角度
4.3.1 聚簇索引
聚簇索引主要用于InnoDB表，資料表中的資料就是按順序存盤的，在物理上是連續的，將資料存盤與索引放到了一塊，因此一個表只能有一個聚簇索引，
我們在建立資料表時，會生成兩個索引，主鍵索引和輔助索引；其中主鍵索引是根據主鍵自動生成的，如果沒有主鍵索引，會自動將資料表中第一個唯一且非空的索引作為主鍵索引，如果還是沒有會自動創建一個隱藏的名字為“GEN_CLUST_INDEX ”的聚簇索引，總之，一個 InnoDB 表只有一個聚簇索引，而且一定會有一個聚簇索引，如果不存在，Innodb 存盤引擎會自動添加一個，而輔助索引則是我們人為建立的索引，也稱為非聚簇索引，
通過其它欄位建立的輔助索引的每條記錄中都包含了該條記錄的主鍵，主鍵索引的葉子節點包含了該主鍵所對應的資料資訊，當我們根據某些條件查詢資料時，會在輔助索引中獲取滿足查詢條件的主鍵，然后使用主鍵檢索到與之對應的葉子節點取得資料，
為什么輔助索引要使用主鍵作為‘指標’，而不用磁盤地址值作為’指標‘呢？
那是因為我們在進行行移動或者分裂資料頁時(資料存盤的位置發生變化)，不需要更新輔助索引中的這個‘指標’，不管這個主鍵B+樹的節點如何變化，輔助索引樹都不受影響，
4.3.2 非聚簇索引
非聚簇索引主要用于MyISAM表，將資料存盤與索引分開儲存，非聚簇索引中也有主鍵索引和輔助索引，兩個索引的儲存位置是獨立的，通過輔助鍵檢索無需訪問主鍵的索引樹，雖然兩個索引的結構看起來差不多，但是主鍵索引的葉子節點儲存的是主鍵資訊，而輔助索引的葉子節點儲存的是輔助鍵，對于資料表來書沒有任何差別，都是通過葉子節點直接獲取到資料資訊，所以對于MyISAM表，主鍵不是必須的，
4.3.3 區別
從上面來看，非聚簇索引比聚簇索引看起來要方便，非聚簇索引只要檢索一次，而聚簇索引要檢索兩次，那么我們為什么還要使用聚簇索引呢？
這是因為聚簇索引中的主鍵索引和人為建立的輔助索引(非聚簇索引)都儲存在一起的，當我們第一次訪問這個表時，資料表的所有索引都已經加載到Buffer(緩沖區)了，之后再根據其它欄位查詢訪問時，會在記憶體中完成，不必訪問磁盤，節省了大量時間，而且按順序存盤的資料表要進行排序時，使用聚簇索引更加適合，同理，取出一定范圍資料的時候，要使用聚簇索引，
當然，聚簇索引也有一定的缺點：
1.因為資料表的資料存盤地址都是順序的，所以更新聚簇索引列的代價很高，會強制將每個被更新的資料行移動到新的位置，
2.聚簇索引可能導致全表掃描變慢，尤其是行比較稀疏，或者由于頁分裂導致資料存盤不連續的時候，
3，如果主鍵比較大的話，那輔助索引將會變的更大，因為輔助索引的葉子存盤的是主鍵值；過長的主鍵值，會導致非葉子節點占用占用更多的物理空間，
5，使用場景
1、不要索引資料量不大的表，對于小表來講，表掃描的成本并不高，
2、不要設定過多的索引，在沒有聚集索引的表中，最大可以設定249個非聚集索引，過多的索引首先會帶來更大的磁盤空間，而且在資料發生修改時，對索引的維護是特別消耗性能的，
3、合理應用復合索引，有某些情況下可以考慮創建包含所有輸出列的覆寫索引，
4、對經常使用范圍查詢的欄位，可能考慮聚集索引，
5、避免對不常用的列，邏輯性列，大欄位列創建索引，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/117954.html
標籤：MySQL
上一篇：MySQL下的DB Link