MySQL目前提供了以下4种索引。
- B-Tree 索引:最常见的索引类型,大部分引擎都支持B树索引。
- HASH 索引:只有Memory引擎支持,使用场景简单。
- R-Tree 索引(空间索引):空间索引是MyISAM的一种特殊索引类型,主要用于地理空间数据类型。
- Full-text (全文索引):全文索引也是MyISAM的一种特殊索引类型,主要用于全文索引,InnoDB从MYSQL5.6版本提供对全文索引的支持。
衍生索引:主键,唯一索引,组合索引,前缀索引等。hash索引适合key-value查询(等值查询),不适合范围查询。
索引 | MYISAM引擎 | InnoDB引擎 | Memory引擎 |
---|---|---|---|
B-tree索引 | 支持 | 支持 | 支持 |
full-text索引 | 支持 | - | - |
hash索引 | - | - | 支持 |
R-tree索引 | 支持 | - | - |
通常我们所说的索引是指B-Tree索引,它是目前关系型数据库中查找数据最为常用和有效的索引,大多数存储引擎都支持这种索引。使用B-Tree这个术语,是因为MySQL在CREATE TABLE或其它语句中使用了这个关键字,但实际上不同的存储引擎可能使用不同的数据结构,比如InnoDB就是使用的B+Tree。
B+Tree中的B是指balance,意为平衡。需要注意的是,B+树索引并不能找到一个给定键值的具体行,它找到的只是被查找数据行所在的页,接着数据库会把页读入到内存,再在内存中进行查找,最后得到要查找的数据。
在介绍B+Tree前,先了解一下二叉查找树,它是一种经典的数据结构,其左子树的值总是小于根的值,右子树的值总是大于根的值,如下图①。如果要在这课树中查找值为5的记录,其大致流程:先找到根,其值为6,大于5,所以查找左子树,找到3,而5大于3,接着找3的右子树,总共找了3次。同样的方法,如果查找值为8的记录,也需要查找3次。所以二叉查找树的平均查找次数为(3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3次,而顺序查找的话,查找值为2的记录,仅需要1次,但查找值为8的记录则需要6次,所以顺序查找的平均查找次数为:(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3次,因为大多数情况下二叉查找树的平均查找速度比顺序查找要快。