可以根据底层数据结构、索引树中叶子节点中保存的内容、索引建立的位置、索引中包含列个数的不同来分类。
1. 从底层数据结构上来说,MySQL有Hash索引和BTree索引:
1. Hash索引:底层数据结构为哈希表。哈希索引的优点是对单条记录的查找效率高,缺点是:
- 不适合范围查询(>、<),只能等值查找(查找==、<=>、in)
- 不适合排序,数据库无法利用索引来提高排序性能。
- 遇到大量Hash冲突的时候,Hash索引的性能不一定有BTree高。
- ……(还有很多)
2. B+Tree索引:底层数据结构为B+树。B+树作为一种多路平衡查找树,尽管单条记录查找效率不如Hash索引,但它在排序和搜索方面的效率更高(而日常中是不仅仅只有单条记录查找的)。
MyISAM引擎中和InnoDB引擎使用的都是B+Tree索引。
建议:当大多数需求为单条记录的查询时,推荐使用哈希索引,其余大部分情况下建议使用B+Tree索引。
思考:为什么数据库系统多采用B+树而不是B树作为索引呢?主要有两点:
- B+树的内部节点存储的是对数据的索引(B树的内部节点存储数据),这使得一个磁盘块能存储更多的元素,减少了磁盘IO次数。(数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点需要一次I/O就可以完全载入 )
- B+树的叶子节点存储数据,且叶子节点被一个链表连接起来,在遍历元素或进行范围查找时很方便(B树则需要从整棵树开始查找)。
2. 从索引树叶子节点中保存的内容上来说,分为聚簇索引以及非聚簇索引(又称为二级索引):
| |
3. 从索引建立的位置上来说分为主健索引和普通索引(又称辅助索引、副索引):
1. 主健索引:建立在主键上的索引,在一张表中只能存在一个。
2. 普通索引:建立在任意健上的索引,不要求key是唯一的。
主健索引和副索引在InnoDB和MyISAM中都存在,且InnoDB引擎中主键索引使用的是聚簇索引,MyISAM引擎中使用的是非聚簇索引。
4. 从索引中包含的列(字段)个数上来说,分为单列索引和组合索引(又称为联合索引):
1. 单列索引:索引中只包含一列,用于查询索引树的key只有一个字段。
2. 联合索引:索引中包含多个列,用于查询索引树的key包含多个字段,在使用联合索引进行查询时必须满足最左前缀原则才能使得查询按照B+树的方式来查询(每一层只需要读入一个节点),否则需要读取整个索引树才能完成查询(每一层读取全部节点)。
原因请看后面索引优化-最左前缀原则一节
不管在什么引擎中,单列索引还是联合索引是由程序员指定的。
例子:单列:create index NAME on user(name),联合:create index NAME_AGE on user(name, age)
补充:在实际开发中,在所需要查询的多个字段上建立联合索引是一种常见的索引优化方式。
5. 此外从功能性上来看还有唯一索引、全文索引。
- 唯一索引:要求索引值唯一(主键索引即是一种唯一索引)
- 全文索引:
InnoDB和MyISAM引擎中索引的区别
区别主要在于InnoDB采用的聚簇索引,而MyISAM采用的是非聚簇索引。
- 对于InnoDB:主键索引是聚簇索引,普通索引为非聚簇索引。
- 对于MyISAM:主健索引、普通索引都是非聚簇索引。MyISAN的主键索引和副索引索引树叶子节点保存的都是指向数据的指针,但主键索引要求key唯一,副索引无要求。