MySQL数据库索引介绍 (2)

B+ 树的阶数是等于键的数量的,列如我们的B+ 树中每个节点可以存储3个键,3层B+ 树可以存储3*3*3=9个数据。所以如果我们的B+ 树一个节点可以存储1000个键值,那么3层B+ 树可以存储1000*1000*1000=10亿个数据。而一般节点是常驻内存的,所以一般我们查找出10亿数据,只需要2次磁盘IO。

因为B+ 树索引的所有数据均存储在叶子节点leaf node ,而且数据是按照顺序排列的。那么B+ 树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。而B 树因为数据分散在各个节点,要实现这一点是很不容易的。

而且B+ 树中各个页之间也是通过双向链表连接的,叶子节点找那个的数据是通过单向链表连接的。其实在B 树中我们也可以多各个节点加上链表。其实这些不是它们之间的区别,是因为在mysql的innodb存储引擎中,索引及时这样存储的。也就是说B+ 树索引就是innodb中 B+ 树索引真正的实现方式,准确的说应该是聚集索引。

在innodb中,我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

七、B+ 树分类

聚集索引、聚簇索引、主键索引:

以主键字段值为key构建的B+ 树,改B+ 树的叶子节点放的是主键值与本行完整的记录。

即:表中的数据都聚聚在叶子节点中,所以称之为聚集索引。

非聚集索引、非聚簇索引、辅助索引、二级索引:

以非主键字段值为key构成的B+ 树,改B+ 树的叶子节点放的是key与其主键对应的字段值。

补充:一张innodb 存储引擎中有且只能有一张聚集索引,但是可以有多个非聚集索引(辅助索引)

聚集索引负责聚集整张表的所有的数据,而辅助索引是专门提速来用

八、覆盖了索引、回表操作

覆盖了索引:在命中了索引的基础上,只在本索引树的叶子节点就找到了我们想要的数据。

回表操作:在命中了辅助索引的基础上,在辅助索引的叶子节点并没有找想要的数据,需要拿到对应的主键字段值去聚集索引去找。

举例:假设我有一张表,我们这张表是以ID字段为基础创建的主键索引,我是以name字段创建的辅助索引

主键索引——》id字段

辅助索引——》name字段

select name, age, gender from user where name=\'yang\'; 这个命中了辅助索引,那么就顺着索引树从根节点一路找下去,找到叶子节点后,叶子节点放的是 ‘yang’ 这个人名以及它的主键字段值 但是我要的是name age gender ,我要的不是id值和主键值,如果我要的是主键值和id值那么我就不需要去其他地方找了——》这就叫覆盖了索引。 因为在自己本索引树的,本索引树的就能找到自己想要的数据,就不需要去别的地方找 但是这并不是我想要的,那么我要拿着我 ‘yang’的主键字段值假如是3,就回过头继续去主键索引树根节点继续找到我想要的叶子节点的数据,那里面所有数据都有——》这就叫回表操作

提问:

1、命中了辅助索引的前提下能不能覆盖了索引?

可能是!向这种情况下就是select name, id from user where name=\'yang\'; \'

这就不是select name, age, gender from user where name=\'yang\';

2、如果命中了主键索引是否覆盖了索引?

一定是!·select name, age, gender from user where id=3;

所以一个SQL语句查询的字段尽量不要想写什么就写什么甚至是 * ,要尽量覆盖了索引。

九、索引管理

MySQL常用的索引分类

聚集索引:即主键索引,primary key 用途: 1.加速查找 2.约束(不为空,不能重复) 辅助索引: 唯一索引:unique 用途: 1.加速查找 2.约束(不能重复) 普通索引:index 用途: 1.加速查找

创建聚集索引

alter table 表名 add prmary key 表名(字段名); alter table 表名 drop primary key;——>删除

创建唯一索引

alter table 表名 add unique key 表名(字段名); 没有写索引名 show create table 表名; 查看 alter table 表名 drop index 索引名;——>删除

创建普通索引

创建表时 create table 表名( id int primary key auto_increment, class_name varchar(10) unique, name varchar(16), age int ); 创建表后 create index 索引名 on 表名(字段名); drop index 索引名 on 表名;——>删除 十、联合索引最左前缀匹配原则 create index zz on t1(id, name,age) 例如:有这样的数据 id name age gender email 1 yang1 18 male yy@qq.com 2 yang2 28 female yy@qq.com 3 yang3 38 male yy@qq.com 4 yang4 48 female yy@qq.com 我现在对它们建联合索引,那么我每条记录提取的key就是 id,name,value对应的是该条记录,列如: 1,yang1,18 ————>对应 1 yang1 18 male yy@qq.com 2,yang2, 28 ————>对应 2 yang2 28 female yy@qq.com

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zzxdjy.html