概述
在数据库当中,索引就跟树的目录一样用来加快数据的查找速度,对于一个SQL查询操作,根据索引快速过滤掉不符合要求的数据并定位到符合要求的数据,从而不需要扫描整个表来获取所需的数据。
在innodb存储引擎中,主要是基于B +树来实现索引,在非叶子节点存放索引关键字,在叶子节点存放数据记录或者主键索引(或者说是聚簇索引)中的主键值,所有的数据记录都在同一层,叶子节点,即数据记录直接之间通过指针相连,构成一个双向链表,从而可以方便地遍历到所有的或者某一范围的数据记录。
B树,B +树
B树和B +树都是多路平衡搜索树,通过在每个节点存放更多的关键字和通过旋转,分裂操作来保持树的平衡来降低树的高度,从而减少数据检索的磁盘访问量。
B +树相对于B树的一个主要的不同点是B +的叶子节点通过指针前后相连,具体为通过双向链表来前后相连,所以非常适合执行范围查找。具体可以参考:
数据结构,树(三):多路搜索树B树,B +树
innodb存储引擎的聚簇和非聚簇索引都是基于B +树实现的。
主键索引
innodb存储引擎使用主键索引作为表的聚簇索引,聚簇索引的特点是非叶子节点存放主键作为查找关键字,叶子节点存放实际的数据记录本身(也称为数据页),从左到右以关键字的顺序,存放数据记录,故聚簇索引其实就是数据存放的方式,所以每个表只能存在一个聚簇索引,innodb存储引擎的数据表也称为索引组织表。结构如下:(图片引自《MySQLmysql技术内幕:innodb存储引擎》)
在查询当中,如果是通过主键来查找数据,即使用解释分析SQL的关键显示主时,查找效率是最高的,因为叶子节点存放的就是数据记录本身,所有可以直接返回,而不需要像非聚簇索引一样需要通过额外回表查询(在主键索引中)获取数据记录。
其次是对于按排序操作,不管是正序ASC还是逆序DESC,如果命令的列是主键,则由于主键索引对应的B +树本身是有序的,故存储引擎返回的数据就是已经根据主键有序的,不需要在MySQL服务器层再进行排序,提高了性能,如果通过解释分析SQL时,额外的显示使用filesort则说明需要在MySQL服务器层进行排序,此时可能需要使用临时表或者外部文件排序,这种情况一般需要想办法优化。
对于基于主键的范围查找,由于聚簇索引的叶子节点已经根据主键的顺序,使用双向链表进行了相连,故可以快速找到某一范围的数据记录。
辅助索引
辅助索引也称为二级索引,是一种非聚簇索引,一般是为了提高某些查询的效率而设计的,即使用该索引列查询时,通过辅助索引来避免全表扫描。由于辅助索引不是聚簇索引,每个表可以存在多个辅助索引,结构如下:
辅助索引的非叶子节存放索引列的关键字,叶子节点存放对应聚簇索引(或者说是主键索引)的主键值,即通过辅助索引定位到需要的数据后,如果不能通过索引覆盖所需列,即通过该辅助索引列来获取该次查询所需的所有数据列,则需要通过该对应聚簇索引的主键值定位到在聚簇索引中的主键,然后再通过该主键值在聚簇索引中找到对应的叶子页,从而获取到对应的数据记录,所以整个过程涉及到先在辅助索引中查找,再在聚簇索引(即主键索引)中查找(回表查询)两个过程。
举个例子:
辅助索引对应的B +树的高度为3,则需要3次磁盘IO来定位到叶子节点,其中叶子节点包含对应聚簇索引的某个主键值;
然后通过叶子节点的对应聚簇索引的主键值,在聚簇索引中找到对应的数据记录,即如果聚簇索引对应的B +树高度也是3,则也需要3次磁盘IO来定位到聚簇索引的叶子页,从而在该叶子页中获取实际的数据记录。
以上过程总共需要进行6次磁盘IO。故如果需要回表查询的数据行较多,则所需的磁盘IO将会成倍增加,查询性能会下降,所以需要在过滤程度高,即重复数据少的列来建立辅助索引。
基数:索引列的数据重复度
由以上分析可知,通过辅助索引进行查询时,如果需要回表查询并且查询的数据行较多时,需要大量的磁盘IO来获取数据,故这种索引不但没有提供查询性能,反而会降低查询性能,并且MySQL优化器在需要返回较多数据行时,也会放弃使用该索引,直接进行全表扫描。所以辅助索引所选择的列需要是重复度低的列,即一般查询后只需要返回一两行数据。如果该列存在太多的重复值,则需要考虑放弃在该列建立辅助索引。
具体可以通过:显示指数从数据表,的基数的值来判断:
