认识索引(三)
上一节我们简单学习了索引原理的前置知识:二分查找算法、Hash结构和B+树结构。我们了解了这些知识之后,本节就要来讲一讲索引中的聚簇索引和非聚簇索引、主键索引和辅助索引(二级索引)。
简单回顾
Q:简单的说说在数据库索引应用中为什么不用Hash结构、B树结构,而是要采用B+树结构呢?
A:1. 首先我们知道Hash结构索引的基本结构是比较适合等值查询,而它的范围查询是需要进行全表扫描的,但数据库表中进行全表扫描是禁止的(直接开除,和redis中使用keys * 是一样的下场)。
2.B树结构虽然可以实现范围查询,但是它其实要遍历范围区间上的所有节点的,而树的遍历又不是所有节点只遍历一次的,总会有非叶子节点被访问多次,那么再整个遍历的效率上就非常低下,而范围查询在日常使用场景下又是使用极其频繁的,所以使用B树效率太低,不适合使用。
3.B+树可以使用主要的原因是它的数据都存储在叶子节点上,而相互的叶子节点都能被指针串联起来,那么它在进行区间遍历的时候,就无限接近于一个链表的遍历,那么一次就可以查出,效率大大提高,并且由于非叶子节点只存索引值,没有存data数据,那么可以在上面搜的时候减少了磁盘IO。所以索引比较适合使用B+Tree结构。当然InnoDB的自适应哈希索引同时借鉴了B+Tree结构和Hash结构,有兴趣的小伙伴可以自行深入研究。
聚簇索引
首先讲到聚簇索引,就要了解为什么产生这个概念?或者说聚簇索引和非聚簇索引有什么区别?
聚簇索引和非聚簇索引:B+树的叶子节点存放主键索引(index)和行记录(data)就属于聚簇索引;如果索引值和行记录分开存放就属于非聚簇索引。
通俗的讲:将数据存储和索引放在一块,找到索引也就找到了数据。data和index在一起的是聚簇索引,不在一起的是非聚簇索引。
在InnoDB中,主键索引采用就是聚簇索引结构存储。
- 聚簇索引(聚集索引)
聚簇索引是一种数据存储的方式,InnoDB的聚簇索引就是按照主键顺序构建B+树结构。B+树的叶子节点就是行记录(那就那一行行数据记录),行记录和主键值紧凑的存在一起。也就意味着InnoDB的主键索引就是数据表本身,它按照主键顺序存放了整张表,占用的空间就是整张表的数据量的大小。
通常来说在InnoDB引擎下的主键索引就是聚簇索引。
InnoDB的表要求必须要有聚簇索引:
- 如果表定义了主键,则主键索引就是聚簇索引
- 如果表没有定义主键,则第一个非空的unique(非空)列作为聚簇索引
- 如果上面两个都没有满足,那么InnoDB自己本身会建立一个row-id作为聚簇索引
聚簇索引性能最好而且具有唯一性,所以是很珍贵的,必须要慎重设置。一般要根据这个表最常用的SQL查询方式来进行选择,某一个字段作为聚簇索引,或组合聚簇索引,这个要看实际情况。
总之一句话,我们的最终目的就是在获取相同结果集的情况下,尽可能的减少IO
- 辅助索引
InnoDB辅助索引,也被成为二级索引,是根据索引列构建B+Tree结构。但在B+Tree的叶子节点中只存储了 索引列和主键的信息。二级索引(因为在InnoDB中非聚簇索引都是二级索引)占用的空间会比聚簇索引小很多,通常创建辅助索引就是为了提升查询的效率。
一个表在InnoDB引擎下只能创建一个聚簇索引,但可以创建多个非聚簇索引(辅助索引)。
这里还要提一个Mysql 的另一个引擎MyISAM。与InnoDB不同的是,MyISAM数据表的索引文件和数据文件都是分开的,被称为非聚簇索引结构。其实看下面这个对比图就知道了。
InnoDB采用聚簇索引,将主键id放到B+树中,行数据(data)放到叶子节点。
- 如果使用“where id = 14"这样的条件查询,按照B+树的搜索方式,查到对应叶子节点,然后获取行数据。
- 如果采用name名字进行查询,where name = Ellison, 那就需要两步走:
1. 在辅助索引B+树上查到name,到达对应的叶子节点获取对应的data,也就是主键。
2. 使用主键在主索引B+树中,再次使用B+树搜索,最终到达叶子节点,查到行数据。
所以日常的开发中,一般内部传值最好用主键id去查,聚簇索引一次OK。一般主键默认自增也就可以理解了。
总结
因为有上一节索引常用结构的前置知识的铺垫,本节主要简单讲解大家关心的聚簇索引和辅助索引,希望能给小伙伴们一些收获。下一节将会开启一个新篇章,主要是认识Sql调优神器 EXPLAIN,敬请期待。