MySQL/MariaDB的 B+ TREE索引

標籤：無法   mariadb   不可   space   sql語句   長度   ash   aci   ref   

在我們CentOS 7+版本之後的內建鏡像中的MariaDB使用的預設引擎是InnoDB引擎：

InnoDB引擎內建的特點：

    1.InnoDB儲存引擎將資料存放區於"資料表空間"中；

    2.支援事務

    3.精細鎖粒度支援；表級鎖、頁級鎖、行級鎖、間隙鎖；

    4.支援叢集索引，主鍵索引以及輔助索引，自適應的Hash索引；

簡要介紹一下事務：

所謂事務：就是一組原子性的SQL查詢或者是一個或多個SQL語句組成的獨立的操作單元；

一個最簡單易懂的事務例子：

    A 借 B 100塊錢，A 得到100，則相應的 B 就會減少100；這就是一個事務，因為不可能 A 借 B100之後，A增加了，而B卻沒減少；

對事務支援效能的測試標準；ACID標準；

    A：原子性、整個事務中所有的操作是一個不可分割的整體，要麼全部成功執行，要麼在某操作執行失敗後全部復原值事務開啟時的狀態；

    C：一致性、資料庫的狀態在執行事務之前和提交事務之後必須要保持資料狀態一致性；

    I：隔離性、獨立性、並發控制的管理機制；

    D：持久性、事務一旦提交，其所作出的所有修改將永久儲存並持久有效；

B+ TREE索引：

    順序儲存，所有的索引資料都存放在分葉節點上，並且每個分葉節點都有順序訪問指標，以此指標指向相鄰的葉子節點。這樣做可以提高區間資料的查詢效率；

適用的情境：

全索引值匹配：精確匹配某個值；

select ... where Name='guo jing';

左首碼匹配：只精確到資料起始位置的一部分；

select ... where Name like 'guo%';

區間資料的連續數值匹配：通常用於BETWEEN ... AND ...環境中；

select ... where Age between 30 and 50;

區間資料的離散值匹配：通常用於IN列表環境或OR列表環境：也是精確值匹配；

select ... where StuID in (1,4,7,10);

精確匹配左列，範圍匹配右側其他列：

select ... wherer StuID > 10 and Name like "a%";

對於覆蓋索引的查詢請求：

不適用的情境：

如果查詢條件不是精確從最左側列開始的，索引無效；

如：對StuID欄位做了索引，select ... where Name like 'a%' and 'StuID' > 10;

如果索引了多列，若跳過索引中的某列，則索引無效；

如：對StuID,Name,Age做索引，select ... where StuID>0 and Age>20;

如果索引了多列，且在查詢語句中對某個列做範圍匹配，則其右側列不能在使用索引最佳化查詢；

如：對StuID,Name,Age做索引，select ... where StuID>0 and Name like 'a%';

    HASH索引：基於HASH表實現的索引；非常適用於值的精確匹配的查詢請求；

注意：

    1.在InnoDB儲存引擎中，建立索引時，只能顯式使用"B+ TREE"索引；

    2.索引中的資料來源於資料表，但資料結構與原資料有很大差異；

查看資料庫的索引：用EXPLAIN語句；

MariaDB [hellodb]> explain select  * from students where StuID<10\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: students         type: rangepossible_keys: PRIMARY          key: PRIMARY      key_len: 4          ref: NULL         rows: 9        Extra: Using where1 row in set (0.00 sec)

id：當前的SELECT查詢語句中，各個SELECT語句的編號；

select_type：查詢類型：

簡單查詢：SIMPLE

複雜查詢：

簡單的子查詢(用於where子句中的子查詢)：SUBQUERY

用於FROM語句中的子查詢：DERIVED

聯集查詢中的第一個查詢：PRIMARY

聯集查詢中其他的查詢：UNION

聯集查詢時產生的暫存資料表查詢：UNION RESULT

table：當前的查詢語句所針對的表；

type：關聯類別型，或稱為訪問類型；也可以理解為MySQL是如何查詢表中的行；

ALL：全表掃描，MySQL將遍曆權標以找到可以匹配的行；

index：全部掃描，與ALL所不同的是index類型只是遍曆索引樹；

range：索引範圍掃描，對索引的掃描從某一個點開始，返回匹配範圍的行；

通常可以基於指定的索引，使用IN列表、BETWEEN ... AND ...或帶有哦"="，"<", ">"的查詢；

ref：使用非唯一索引掃描或者使用唯一索引的左首碼掃描，返回匹配某個單獨的行；

eq_ref：類似ref，區別就是使用唯一索引，對於每一個索引索引值，表中都只有一條記錄匹配；無論是單表查詢還是多表查詢，都是要主鍵或唯一鍵索引作為關聯條件；

const，system：當MYSQL對查詢部分進行最佳化，並轉換為一個常量，使用const類型；

system類型是一個const類型特例，當要查詢的表中只有一行時，使用system類型；

NULL：MySQL在最佳化過程中分解查詢語句，執行時不用訪問表或索引；

possible_keys：為了執行查詢語句，MySQL可能使用哪個索引在表中尋找到記錄；

如果查詢涉及到的欄位上存在索引，則該索引會被列出，但不一定會被查詢使用；

keys：顯示MySQL在查詢過程中實際使用到的索引；如果查詢過程沒有用到任何索引，則此處顯示為"NULL"；

key_len：表示索引中可以被引用的最大的位元組數；可以通過該列計算查詢中使用的索引的長度：

注意：key_len顯示的值通常為索引欄位的最大可能長度，並不是實際的使用長度；因此key_len是根據定義時指定的欄位長度計算得到的，而並不是在表中通過檢索資料得到的；

ref：在利用key欄位所顯示的索引完成查詢操作時所引用的列或常量值；如果都沒有，則顯示為"NULL"；

rows：表示MySQL根據表統計資訊及索引選用的情況，估算的本次檢索所需要尋找索引記錄的過程中需要讀取的表的行數；

Extra：額外資訊，或稱為擴充資訊；

Using where：表示MySQL伺服器將在儲存檢索後在次進行條件過濾；許多的where條件裡涉及到索引中的列並且當MySQL讀取該索引時，就可以被儲存引擎檢驗；

Using index：使用了覆蓋索引進行檢索；

Using tempory：在查詢過程中使用了暫存資料表存放查詢結果集；常見於排序或分組查詢；

Using filesort：MySQL中無法利用索引完成排序；

Using join buffer：強調了在擷取串連條件時沒有使用都索引，並且需要串連緩衝區來儲存中間結果；如果出現了該值，需要根據查詢的具體情況適當的添加索引以提示查詢效能；

Impossible where：如果該值出現，則意味著在查詢時沒有發現合格行；

Select tables optimized away：該值意味著僅通過使用索引來進行查詢，但是最佳化器可能從彙總函式的結果中給出一個可行的最佳化方案；

Using sort-union(...)

Using union(...)

Using intersect(...)

上述情況多出現於在實現查詢的過程中，決定使用不止一個索引時；

filtered：從可選的行中再次過濾之後選擇出最終的查詢結果的比值；可以理解為從多少行中過濾選擇出多少行的比值；

MySQL/MariaDB的 B+ TREE索引