Q1:數(shù)據(jù)庫有哪些索引？優(yōu)缺點是什么？

1.B樹索引：大多數(shù)數(shù)據(jù)庫采用的索引（innoDB采用的是b+樹）。能夠加快訪問數(shù)據(jù)的速度，尤其是范圍數(shù)據(jù)的查找非?？?。缺點是只能從索引的最左列開始查找，也不能跳過索引中的列，如果查詢中有某個列用到了范圍查詢，則右邊所有列都無法使用索引優(yōu)化查找。

2.哈希索引：基于哈希表實現(xiàn)。在MySQL中，只有Memory引擎顯式的支持哈希搜索。哈希查找的速度非?？欤Ｋ饕话Ｖ岛托兄羔?，不存儲字段值，所以不能用索引中的值來避免讀取行，也不能進行排序。由于哈希索引使用的是索引列的全部內容來計算哈希值的，所以不支持部分所有列匹配查找。哈希只支持等值比較，不支持任何范圍查詢。一旦哈希沖突很多的話，維護成本非常高。innoDB支持“自適應哈希索引”（adaptive hash index)。

3.全文索引：全文索引是一種特殊類型的索引，它查找的是文本中的關鍵字，而不是比較索引的值。最初只能在MyISAM上使用，5.6.24以后innoDB也支持了全文索引。全文索引的查詢要使用Match....against，在相同的列上同時創(chuàng)建全文搜索和基于值的B-Tree索引不會有沖突。

4.空間數(shù)據(jù)索引（R-tree索引），MyISAM支持R樹索引，好處是無需前綴查詢，會從所有緯度來索引數(shù)據(jù)，可以用作地理數(shù)據(jù)的存儲；缺點是必須使用MySQL的GIS相關函數(shù)如MBRCONTAINS( )等來維護數(shù)據(jù)，但由于MySQL中的GIS并不完善，因此大多數(shù)人不會使用這個特性。

Q2：為什么不實用二叉查找樹或者紅黑樹作為數(shù)據(jù)庫索引。

二叉樹在處理海量數(shù)據(jù)時，樹的高度太高，雖然索引效率很高，達到logN，但會進行大量磁盤io，得不償失。而且刪除或者插入數(shù)據(jù)可能導致數(shù)據(jù)結構改變變成鏈表，需要增進平衡算法。而紅黑樹，插入刪除元素的時候會進行頻繁的變色和旋轉（左旋，右旋），很浪費時間。但是當數(shù)據(jù)量很小的時候，完全可以放入紅黑樹中，此時紅黑樹的時間復雜性比b樹低。因此，綜上考慮，數(shù)據(jù)庫最后選擇了b樹作為索引。

Q3:B tree和B+ tree應用場景：

1.B樹常用于文件系統(tǒng)，和少部分數(shù)據(jù)庫索引，比如mongoDB。

2.B+樹主要用于mysql數(shù)據(jù)庫索引。

Q4:B+ tree對比B tree的優(yōu)點

B樹的每個節(jié)點除了存儲指向 子節(jié)點的索引外，還要存儲data域，因此單一節(jié)點指向子節(jié)點的索引并不是很多，樹的高度較高，磁盤io次數(shù)較多。B+樹的高度更低，且所有data都存儲在葉子節(jié)點，葉子節(jié)點都處于同一層，因此查詢性能穩(wěn)定，便于范圍查找。

Q5:多列排序時使用索引的坑

A key_part specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

根據(jù)Mysql文檔的說明，創(chuàng)建索引的時候，可以加上asc或者desc，例如：add index idx(a asc,b desc).但是實際Mysql是會忽略的（好坑。。。）好像8.0版本之后支持desc了。

這會有什么影響呢？

假如有列test1和test2，都是int類型。

我們創(chuàng)建索引``idx1(test1,test2)，
假如我們要按test1和test2排序，例如SQL

explain select * from table order by test1 ,test2 limit 1;

可以使用索引的排序：

• order by test1
• order by test1 desc
• order by test1,test2
• order by test1 desc,test2 desc
  

不可以使用索引的排序：

• order by test1,test2,desc
• order by test1 desc,test2
  

因為索引不支持desc，所以多列的索引是按全部列的升序存儲的。所以只排序一列，全部列升序，全部列降序，都能用索引。但是第一列用升序，第二列用降序，或者第一列降序，第二列用升級，都不能使用索引。

以上就是Mysql索引常見問題匯總的詳細內容，更多關于MySQL 索引的資料請關注腳本之家其它相關文章！

最新評論