B tree 索引分类:数据库中的B+树索引可以分为聚集索引(clustered index)也叫聚簇索引和辅助索引(secondary index)也叫非聚集索引，聚集索引和辅助索引都是一颗B+树，主要区别是叶子节点存储的信息是否是完整的数据信息;
一张表只能有一个聚集索引,因为聚集索引上存储了整张表的数据；(在磁盘文件的表现上是存储在一个文件上的)
MyIsam 和innodb B tree索引的区别
btree 索引不同的存储引擎下是有所区别的
主键索引对比
myisam下的主键索引
主要特点：myisam的主键索引的叶子节点存储的是数据的磁盘文件地址，也就是不存储真实的数据信息，所以可以说myisam的主键索引并不是聚集索引；
根据主键索引查询数据先根据B+树 定位到数据的叶子节点上，取数据的磁盘地址，去文件中获取对应的数据.
innodb 的主键索引
主要特点：innodb 的主键索引的叶子节点存储的是真实的数据列，所以innodb存储引擎的主键索引是聚集索引;
根据主键 查询时根据B+树查询到叶子节点的数据，直接获取对应的数据;
非主键索引对比
myisam下的辅助索引
主要特点: myisam的非主键索引的叶子节点存储的是数据的磁盘文件地址;
根据非主键Id 查询时和主键查询一样都要根据最终查询的文件地址去定位最终的数据;
innodb 的辅助索引
主要特点:innodb 的辅助索引的叶子节点存储的是数据的主键Id;
根据 辅助索引查询时，最终得到数据的主键id，拿到数据Id后 去聚簇索引（主键索引）执行回表操作，获取要查询的数据.
索引的使用
联合索引:对表上的多个字段值进行索引,和创建单值索引的唯一区别是指定了多个字段;
构建B+树是一定要排序的，而 有多个字段的联合索引在构建B+树的时候，会根据建立的索引的字段先后的排序构建，比如有3个索引字段，先根据第一个字段的排序构建，当出现第一个字段相等的数据时，就根据第二个进行排序，当第二个字段也相等了，就根据第三个进行排序，依次类推。所以区分度越大的字段越应该在构建联合索引字段顺序的时候放到前面的位置
alert table user add key (user_name,age);
聚簇索引和非聚簇索引的查询过程
假设user 表主键索引为 id ,辅助索引为联合索引 user_name,age
聚簇索引查询过程
select * from user where id =20;
此sql语句执行的时候走聚簇索引，从B+树的上层节点，一层一层的向下查找，根据左右对比数据的大小，最终在叶子节点查找到对应的索引数据，并取出索引关联的数据返回查询结果;
非聚簇索引查询过程
select * from user where user_name = '张三'
此sql语句执行的时候mysql会执行user_name，age 的非聚簇索引，非聚簇索引的B+ 树搜索数据，一层一层的向下查找，查找到叶子节点的时候，如果查询的字段通过非聚簇树上就已经能得到了，那么就从非聚簇索引树上返回数据结果，如果要的是完整的数据，那么就从非聚簇索引树上拿到 主键 id，再去包含了所有数据的聚簇索引上获取到需要的数据并返回结果.
回表:因为非聚簇索引的叶子节点的数据存储的是主键id，所以查询走了非聚簇索引后，还需要拿这个id集合去聚簇索引中再查询拿到全部的数据，这个过程叫回表.
覆盖索引:指只从联合索引中就能拿到想要的记录，而不是再次回表从聚集索引中查询，提高了查询效率;
比如建立了2个索引字段,如果查询
//因为查询所有字段，单查询联合索引得出的信息不足以返回，需要回表;
select * from user where user_name = '张三' and age > 10;
//查询的数据从联合索引中就能获取到了，执行了覆盖索引不需要回表
select user_name ,age where user_name = '张三' and age > 10;
最左匹配原则:使用联合索引查询的时候，不能跳过前面的联合索引字段，必须从左边进行匹配，联合索引只有第一个字段是有序的，直接根据第二个字段，或者跳过字段，并不能保证连续，所以不能走索引；
索引下推(Index Condtion PushDown(ICP)):mysql 5.6+ 支持的索引查询优化功能;
举例说明：
//新建user 表，一个主键id索引，一个 user_name ,age address 的联合索引;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`user_name` varchar(10) DEFAULT NULL,
`address` varchar(60) DEFAULT NULL,
`age` int(2) DEFAULT NULL,
`telphone` char(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY (`user_name`,`age`,`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
插入数据
insert into user(`user_name`,`address`,`age`,`telphone`) VALUES('张三','北京市的张三',10,'15825478585'),('张三','天津市的张三',10,'15825478586'),('张三','石家庄市的张三',10,'15825478588'),('张三','济南市的张三',10,'15825478589')
执行查询
select * from user where user_name = '张三' and age = 10 and address like '%庄市%';
如果没有索引下推的优化，根据最左匹配原则 user_name 和 age 这两个字段的查询是走索引的，like 是无法走索引，所以会查询出前2个字段的查询结果，再去对like 进行过滤；
索引下推的优化是将where条件中的不能走到索引的字段的过滤，放到索引层,在索引中取出的时候就完成了where条件的过滤，用过滤完后的id，去回表等操作，大大提高了效率；
执行explain 后的结果,可以在Extra 看到Usering where
EXPLAIN select * from user where user_name = '张三' and age = 10 and address like '%庄市%';
image.png
multi-Range read(MRR):mysql5.6版本后的优化，此优化的目的是减少磁盘的随机访问.
MRR 优化的主要是在为了减少磁盘的随机访问,将随机访问转化为顺序的数据访问;
当没有开启MRR优化的时候，一次走了辅助索引的查询，再从索引中取到id 后，再去查询聚簇索引的时候是随机的，无顺序的，查询的时候导致磁盘的随机读和无序性，性能比较差；
而开启了MRR优化的时候;
将查询得到的索引键值放到一个缓存中，缓存中的数据是根据辅助索引的键值升序;
将缓存中的键值根据ROWID 进行排序；
根据ROWID 的排序来访问实际的数据文件;
mrr 原理图
其实就是尽量按照磁盘的存储顺序读取，减少磁盘随机读的性能消耗，充分利用磁盘预读;
索引下推开启
网友评论