索引的类型
MySQL数据库中的索引具体有以下几种类型
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 根据表中的主键建立的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免表中某一个字段的数据值重复(在建立表的时,加入唯一约束以后,会自动的创建唯一索引) | 可以有多个 | UNIQUE |
常规索引 | 快速定位表中的特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是表中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,可以进行如下的分类:
分类 | 含义 | 特点 |
---|---|---|
聚集索引(Clustered Index) | 索引结构的叶子节点保存了行数据(一般是采用主键作为聚集索引,id作为键值,key下面存放的就是该id所属于的行数据) | 必须有,而且只有一个 |
二级索引/辅助索引/非聚集索引(Secondary Index) | 索引结构的叶子节点关联的是对应的主键 | 可以有多个 |
聚集索引的选取规则:
①如果有主键,则主键索引就是聚集索引;
②如果不存在主键,就使用第一个唯一索引作为聚集索引
③如果表没有主键,也没有适合的唯一索引,则InnoDB会自动生成一个rowid作为隐藏
而对于这两种索引的结构,查询的过程如下:
①对于聚集索引,根据where后面的条件在树中进行判断查询,因为每个叶子节点下面挂着的就是对应的行数据,所以当查找到对应的id以后,就可以获取所有的相关的数据
②但是以上的步骤只是针对于聚集索引的,可以直接一下子就查找到相应的数据。但是当查询的条件变成了二级索引以后,查询的流程就会有一些变化。
查找数据的时候,根据二级字段的内容进行数据的查找,然后查找到的数据是该字段所处的id值,如果这个时候想要查询的信息并没有完全的进行查询出来的时候,就需要根据获取的主键id返回到聚集索引的位置,进行信息数据的查询,而这个过程叫做“回表查询”。
所以对于两种根据不同索引类型在数据查找的时候,一种根据聚集索引查找一种根据二级索引查找的时候,虽然都是使用了索引进行查询数据,但是由于二级索引可能会涉及到回表查询的情况,所以这时候性能相比就会差了许多
疑问
InnoDB主键索引的B+Tree高度有多高?
因为这道题中问的是主键索引的B+树的高度,而对于起结构式如上图所式。而主键索引是聚集索引,那么叶子节点中挂着的是行数据。而每个节点最终在磁盘上存放的位置是在页中。一页固定的内存大小是16k,也就以为一个节点可以存储的key和指针是有限的。所以对于对于其高度的求取,可以先进行假设,一行数据的内存大小是1k,,那么一页就最多可以存储16行这样子的数据。InnoDB的指针占用了6字节的空间(这个大小是固定的),而对于key占用的自己取决于主键的类型,如果主键使用的类型为bigint,key占用的字节数为8。那么接下来就可以估算高度为2,对于可以存储的数据量进行计算,对于非叶子节点来说:n*8+(n+1)6=161024 (n表示该节点存储的key的数量) 【16 * 10254个字节】这个时候根据公式就可以得到,每个节点可以存储的key为1170,那么指针的数目就为1171个指针,每个指针就指向一个叶子节点。那么1171个指针,每个叶子节点最多存储16行数据的话,1171 * 16=18736 这么多的数据量,但这只是高度为2的时候存储的数据量。而随着高度的增加,会发现即使存储的数据量有两千多万条,数的高度还是只有3层,所以检索效率很高。但是对于在InnoDB中存储了上亿的数目的时候,其实就需要使用分库分表来进行操作了