作者：shuaibing90\来源：https://www.xysycx.cn/article...什么是索引？索引是一种辅助存储引擎高效获取数据的数据结构。很多人形象的说索引就是数据的目录，方便存储引擎快速定位数据。索引的分类我们经常从以下几个方面对索引进行分类从“数据结构的角度”对索引进行分类B+treeHashFull-texts索引从“物理存储的角度”对索引进行分类从集群索引的角度进行二级索引（辅助索引）《索引字段特征》分类主键索引唯一索引普通索引前缀索引从《组成索引的字段数角度》分类单列索引联合索引（复合索引）数据结构角度下表是常用的存储方式engineforMySQLInnoDB、MyISAM和Memory支持的索引类型在实际使用中，InnoDB作为MySQL建表时的默认存储引擎。横向看上表可以知道，B+树是MySQL中存储引擎使用最广泛的索引类型。这里简单说一下B+树、Hash和红黑树的区别。另外，MySQL系列面试题和答案都整理好了。微信搜索Java技术栈，后台发送：面试，可以在线阅读。B+tree和B-tree1970年，R.Bayer和E.Mccreight提出了一种适合外查找的平衡多叉树——B-tree，磁盘管理系统中的目录管理，数据库系统中的索引组织，大多采用B树的数据结构。--DataStructureCLanguageEditionSecondEditionYanWeiminB+tree是B-Tree的变种。（哦，对了，B-tree叫B树，不叫B-minus树。。。）B+tree只在叶子节点存储数据，而B-tree也在非叶子节点存储数据，有这里有疑问可以在下面的链接中插入数据自己测试一下。B树：https://www.cs.usfca.edu/~galles/visualization/BTree.htmlB+tree：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html因此，B+tree单节点数更小，相同磁盘IO下可以查询更多节点。另外，B+tree叶子节点采用单链表链接，适用于MySQL中常见的基于范围的顺序检索场景，而B-tree做不到这一点。对于B+树和红黑树，对于一个有N个叶子节点的B+树，搜索复杂度为“O(logdN)，其中d是指度，指的是B+树的度数”，表示最大个数ofthenodeallowedchildnodes在实际使用中，d的值大于100，即使数据达到千万级别，B+tree的高度依然维持在3-4左右，保证了可以找到3-4个磁盘I/O目标数据后。从上图可以看出，红黑树是一棵二叉树，一个节点的子节点个数最多为2个，也就是说它的搜索复杂度是“O(logN)”，这就多了高于B+树。因此，红黑树需要更多的磁盘I/O来检索目标数据。B+树索引和Hash表范围查询是MySQL数据库常见的场景，而Hash表不适合做范围查询，Hash表更适合做等值查询。此外，哈希表还存在哈希函数选择、哈希值冲突等问题。基于这些原因，B+树索引比哈希表索引具有更广泛的适用场景。从物理存储的角度来看，MySQL中常用的两种存储引擎对索引的处理方式不同。InnoDB索引首先看InnoDB存储引擎中的索引。InnoDB表的索引根据叶子节点是否存储完整的表数据分为聚簇索引和二级索引。全表数据存储在聚簇索引中。聚簇索引以外的索引称为二级索引。下面结合实例介绍两种索引。createtableworkers(idint(11)notnullauto_incrementcomment'员工工号',namevarchar(16)notnullcomment'员工姓名',salesint(11)defaultnullcomment'员工销售业绩',主键(id))引擎InnoDBAUTO_INCREMENT=10默认字符集=utf8;insertintoworkers(id,name,sales)values(1,'江南',12744);insertintoworkers(id,name,sales)values(3,'Whereareyounow?',14082);insertintoworkers(id,name,sales)values(7,'路明非',14738);insertintoworkers(id,name,sales)values(8,'LuGuichen',7087);insertintoworkers(id,name,sales)values(11,'Himeno',8565);insertintoworkers(id,name,sales)values(15,'Caesar',8501);insertintoworkers(id,name,sales)values(20,'画梨衣',7890);我们现在正在我们自己的测试数据库中创建一个包含销售人员信息的测试表。workers包含三个字段：id（主键）、name、sales，指定该表的存储引擎为InnoDB。那么在插入8条数据的例子中，workers表的聚簇索引是建立在字段id上进行精确模拟的，我们先将主键id插入到b+tree中得到下图，然后根据这个图，我画了高清版的。从图中可以看出，聚簇索引的每个叶子节点存储了完整的一行表数据，叶子节点之间使用单向链表以id列为增量连接起来，可以方便顺序检索。InnoDB表必须有聚集索引。默认情况下，聚集索引建立在主键字段上。在没有主键字段的情况下，表的第一个NOTNULL唯一索引将被建立为聚集索引。前两个如果都不是，InnoDB会自动生成一个隐式自增id列，并在这个列上创建聚簇索引。然后看二级索引。以刚才的workers表为例，我们添加一个二级索引index_namealtertableworkersaddindexindex_name(name)到name字段；我们还画了二级索引index_name的B+树示意图，可以看到二级索引的叶子节点并没有存储完整的一行表数据，而是存储了聚簇索引所在列的值，即即，workers表中id列的值。在这两张图中，B+tree的度都设置为3，主要是为了方便演示。在实际的B+树索引中，树的度数通常大于100。说到聚簇索引和二级索引，就不得不提到“回表查询”。由于二级索引的叶子节点不存储完整的表数据，通过二级索引查询聚簇索引的列值后，需要返回到局促索引，即表数据本身，以获得进一步的数据。比如我们要查询workers表中姓吕归臣的人select*fromworkerswherename='吕归臣';这条SQL通过name='吕归臣'条件在二级索引index_name中查询主键id=8，然后返回id=8条件的聚簇索引查询得到完整的数据。显然，回表需要额外的B+tree查找过程，必然会增加查询时间。需要注意的是，通过二级索引查询时，回表并不是必须的过程。当Query的所有字段都可以在二级索引中找到时，就不需要回表了。MySQL此时称二级索引为覆盖索引或“索引覆盖”被触发。selectid,namefromworkerswherename='LuGuichen';这条SQL只查询了id和name，二级索引已经包含了Query需要的所有字段，所以不需要回表查询。说明selectid,namefromworkerswherename='LuGuichen';使用explain查看这条SQL的执行计划。在执行计划的Extra字段中，出现Usingwhere；usingindex表示查询触发了索引index_name的索引覆盖，而for索引已经通过where进行了过滤，这里不需要回表。对比一下，查询explainselectid,name,salesfromworkerswherename='LuGuichen';Extra是UsingIndexCondition，也就是说会先过滤索引，所有满足索引条件的数据都会被找出来过滤索引后。行，然后使用WHERE子句中的其他条件来过滤这些数据行。IndexConditionPushdown(ICP)是MySQL5.6及以上版本的新特性。它是一种在存储引擎层使用索引过滤数据的优化方式。启用ICP时的执行计划中包含Usingindexconditionflag，表示优化器使用ICP优化数据访问。如果对此感兴趣，可以查看相应的官方文档和技术博客。这次我们简化一下来理解，先不考虑ICP对数据访问的优化。当关闭ICP时，Index只是数据访问的一种访问方式。存储引擎通过索引回表得到的数据会传递给MySQLServer层进行WHERE条件过滤。Extra是Usingwhere只是提醒我们MySQL会使用where子句来过滤结果集。这通常发生在MySQL服务器，而不是存储引擎层。一般发生在无法进行索引扫描或者进行了索引扫描，但是有些查询条件没有包含在索引中。这表示没有触发索引覆盖，执行回表查询。MyISAM索引说完InnoDB索引，我们来看一下MyISAM索引。存储在MyISAM存储引擎中的表没有聚簇索引。MyISAM表中主键索引和非主键索引的结构是一样的。从上图可以看出，它们的叶子节点不存储表数据，而是在节点中存储表数据的地址，所以MyISAM表可以没有主键。MyISAM表的数据和索引是分离的，分开存储。MyISAM表中主键索引和非主键索引的区别在于主键索引B+树上的键必须满足主键的限制，非主键索引B上的键+tree只需要满足对应字段的特征即可。从索引字段特性来看，索引“主键索引”是建立在主键字段上的索引。一张表最多只能有一个主键索引。索引是唯一索引。一个表可以有多个唯一索引。索引列值允许为空。我们演示创建索引createtablepersons(idint(11)notnullauto_incrementcomment'主键id',enoint(11)comment'工号',eidint(11)comment'身份证号',veidint(11)comment'virtualIDnumber',namevarchar(16)comment'name',primarykey(id)comment'primarykeyindex',UNIQUEkey(eno)comment'enouniqueindex',UNIQUEkey(eid)comment'eid唯一索引')engine=InnoDBauto_increment=1000defaultcharset=utf8;altertablepersonsadduniqueindexindex_veid(veid)comment'veiduniqueindex';通过showindexfrompersons;命令，我们可以看到已经成功创建了三个唯一索引。普通索引主键索引和唯一索引要求字段为主键或唯一字段，而建立在普通字段上的索引称为普通索引，既不要求字段为主键也不要求字段唯一。前缀索引前缀索引是指在字符类型字段的前几个字符或二进制类型字段的前几个字节上建立索引，而不是在整个字段上建立索引。例如，您可以在persons表的name(varchar(16))字段中索引name的前5个字符。在persons(name(5))comment'prefixindex'上创建索引index_name；显示人员索引；可以在charvarcharbinaryvarbinary类型的列上建立前缀索引，可以大大减少索引占用的存储空间，提高索引的查询效率。从索引的列数来看，建立在单列上的索引就是单列索引。以上演示都是建立在多列上的单列索引。它被称为联合索引（compositeindex）。演示联合索引createindexindex_id_nameonworkers(id,name)comment'Compositeindex';此语句在我们的演示表workers中创建两个字段id和name的联合索引。使用showindex命令可以查看索引的详细信息。运行后的结果如下：虽然在详细信息中列出了两个关于联合索引的条目，但并不代表联合索引建立了多个索引。联合索引是一个索引结构。这两个表项代表了组合索引中字段的具体信息，按照建索引时的写入顺序排序。同样，我们看一下组合索引的B+tree示意图。从图中我们可以看出，组合索引的非叶子节点保存了两个字段的值作为B+树的键值。在B+树上插入数据时，先按字段id比较，id相同的情况下，按字段名比较。近期热点文章推荐：1.1,000+Java面试题及答案（2021最新版）2.别在满屏的if/else中，试试策略模式，真的很好吃！！3.操！Java中xx≠null的新语法是什么？4、SpringBoot2.5发布，深色模式太炸了！5.《Java开发手册（嵩山版）》最新发布，赶快下载吧！感觉不错，别忘了点赞+转发！
