区块链技术博客
www.b2bchain.cn

基于B+树聊聊MySQL的其它知识点

这篇文章主要介绍了基于B+树聊聊MySQL的其它知识点的讲解,通过具体代码实例进行17696 讲解,并且分析了基于B+树聊聊MySQL的其它知识点的详细步骤与相关技巧,需要的朋友可以参考下https://www.b2bchain.cn/?p=17696

本文实例讲述了2、树莓派设置连接WiFi,开启VNC等等的讲解。分享给大家供大家参考文章查询地址https://www.b2bchain.cn/7039.html。具体如下:

目录

      • 什么是回表查询?
      • 什么是覆盖索引?
      • 联合索引的最左前缀匹配原则
      • 什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?
      • 为什么主键通常建议使用自增id呢?

什么是回表查询?

所谓的回表查询,是指先定位主键值,再定位行记录,性能上较之直接查询索引树定位行记录更慢。

1. 建表

CREATE TABLE `user` (   `id` int(11) NOT NULL,   `name` varchar(20) DEFAULT NULL,   `sex` varchar(5) DEFAULT NULL,   `age` tinyint DEFAULT NULL,   `height` tinyint DEFAULT NULL,   `weight` tinyint DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;  insert into user (id, name, sex, age, height, weight) values (1,'liuyang','男', 1, 2, 7); insert into user (id, name, sex, age, height, weight) values (2,'liuyang','女', 2, 1, 8); insert into user (id, name, sex, age, height, weight) values (2,'liuyue','女', 4, 3, 1); insert into user (id, name, sex, age, height, weight) values (3,'liuxing','男', 1, 5, 2); insert into user (id, name, sex, age, height, weight) values (3,'liuxing','女', 5, 6, 7); 

2. 分析下面两个查询

explain select id,name from user where name='liuyang' 

基于B+树聊聊MySQL的其它知识点

explain select id,name,sex from user where name='liuyang' 

基于B+树聊聊MySQL的其它知识点

通过explain可以看出当我们增加了sex字段做查询时extra为NULL,意味着本次查询进行了“回表”操作,我们知道innodb采用B+树聚集索引,主键和数据绑定在一起,主键索引b+树的叶子节点存储了数据信息,而普通索引叶子节点存储的是主键值。因此,我们可以得知当通过普通索引查询时无法直接定位行记录,通常情况下,需要扫描两遍索引树。

如非聚集索引的查询方式,就是回表查询。

那么,问题来了,非聚集索引一定会查询多次吗?

正常情况肯定是的,但是如果想要提升性能,通过覆盖索引,也可以只查询一次就获得结果。

什么是覆盖索引?

覆盖索引是指,查询的数据列只用从索引中就能够取得,不必从数据表中读取。换句话说一个索引覆盖所有需要查询的字段的值,无需回表查询,速度更快。

如何实现覆盖索引?

办法:将被查询的字段建立到联合索引中

接我们上面的例子,因为我们对name字段建立了普通索引,且基于name的索引叶子节点存有主键id值,因此满足了在一颗索引树上获得sql所需的所有列数据这一条件,通过观察extra也可发现是Using Index无需回表。

select id,name from user where name='liuyang' 

第二个例子,因为sex并没有被建立到联合索引中,且在name索引树上也无法直接获得,因此只能通过回表查询,两次扫描索引树,效率更低。

explain select id,name,sex from user where name='liuyang' 

针对第二个例子,我们将sex建立到联合索引中去。

ALTER TABLE `user`  DROP INDEX `name`, ADD INDEX `idx_name_sex`(`name`, `sex`); 

基于B+树聊聊MySQL的其它知识点

再次执行查询,可以看到extra已经变为Using index了,命中了索引覆盖无需回表。

注意:

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值,而哈希索引、空间索引(R-Tree)和全文索引等都不存储索引列的值,所以MySQL只能使用B-Tree索引做覆盖索引。

联合索引的最左前缀匹配原则

在MySQL数据库中不仅可以对某一列建立索引,还可以对多列建立一个联合索引,而联合索引存在一个最左前缀匹配原则的概念,如果基于B+树来理解这个最左前缀匹配原则,相对来说就会容易很很多了。

用上面的表创建一个联合索引:

create index idx_obj on user(age asc, height asc, weight asc) 

索引的数据结构是一颗B+树,B+树优化查询效率的其中一个因素就是对数据进行了排序,那么我们在创建 idx_obj 这个索引的时候,也就相当于创建了一颗B+树索引,而这个索引就是依据联合索引的成员来进行排序,这里是age,height,weight。 B+tree请看上一篇博客

InnoDB中只要有主键被定义,那么主键列被作为一个聚簇索引,而其它索引都将被作为非聚簇索引,所以,这个索引是一个非聚簇索引。聚集索引和非聚集索引请看上一篇博客

所以根据这些我们可以得出结论:

  • idx_obj 这个索引会根据 age, height, weight进行排序
  • idx_obj 这个索引是一个非聚簇索引,查询时需要回表

根据这两个结论,首先需要了解的就是,如何排序?

单列排序很简单,比大小嘛,谁都会,但是多列排序是基于什么原则的呢(重点)?

实际上在MySQL中,联合索引的排序有这么一个原则,从左往右依次比较大小,就拿刚才建立的索引举例子,他会先去比较age的大小,如果age的大小相同,那么比较height的大小,如果height也无法比较大小, 那么就比较weight的大小,最终对这个索引进行排序。

先来一个图:
基于B+树聊聊MySQL的其它知识点

注意:此时由于是非聚簇索引,所以叶子节点不在有数据,而是存了一个主键索引,最终会通过主键索引来回表查询数据。

我们先写一个查询语句

SELECT * FROM user WHERE age=1 and height = 2 and weight = 7 

毋庸置疑,这条语句一定会走idx_obj这个索引。

那么我们再看一个语句:

SELECT * FROM user WHERE height=2 and weight = 7 

思考一下,这条SQL会走索引吗?

答案是否定的,那么我们分析的方向就是,为什么这条语句不会走索引。

上文中我们提到了一个多列的排序原则,是从左到右进行比较然后排序的,而我们的idx_obj这个索引从左到右依次是age,height,weight,所以当我们使用height和weight来作为查询条件时,由于age的缺失,那么就无法从age来进行比较了。

看到这里可能有小伙伴会有疑问,那**如果直接用height和weight来进行比较不可以吗?**显然是不可以的,可以举个例子,我们把缺失的这一列写作一个问号,那么这条语句的查询条件就变成了 ?27,那么我们从这课B+树的根节点开始,根节点上有127和365,那么以height和weight来进行比较的话,走的一定是127这一边,但是如果缺失的列数字是大于3的呢?比如427,527,627,那么如果走索引来查询数据,将会丢失数据,错误查询。所以这种情况下是绝对不会走索引进行查询的。这就是最左前缀匹配原则的成因。

  1. 最左前缀匹配原则,MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引,d是无法使用索引的,如果建立(a,b,d,c)的索引则都可以使用到,a、b、d的顺序可以任意调整。
  2. =和in可以乱序,比如 a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序,MySQL的查询优化器会帮你优化成索引可以识别的形式。

根据我们了解的可以得出结论:

只要无法进行排序比较大小的,就无法走联合索引。

可以再看几个语句:

SELECT * FROM user WHERE age=1 and height = 2 

这条语句是可以走idx_obj索引的,因为它可以通过比较 (12?<365)。

SELECT * FROM user WHERE age=1 and weight=7 

这条语句也是可以走idx_obj索引的,因为它也可以通过比较(1?7<365),走左子树,但是实际上weight并没有用到索引,因为根据最左匹配原则,如果有两页的age都等于1,那么会去比较height,但是height在这里并不作为查询条件,所以MySQL会将这两页全都加载到内存中进行最后的weight字段的比较,进行扫描查询。

SELECT * FROM user where age>1 

这条语句不会走索引,但是可以走索引。这句话是什么意思呢?这条SQL很特殊,由于其存在可以比较的索引,所以它走索引也可以查询出结果,但是由于这种情况是范围查询并且是全字段查询,如果走索引,还需要进行回表,MySQL查询优化器就会认为走索引的效率比全表扫描还要低,所以MySQL会去优化它,让他直接进行全表扫描。

SELECT * FROM user WEHRE age=1 and height>2 and weight=7 

这条语句是可以走索引的,因为它可以通过age进行比较,但是weight不会用到索引,因为height是范围查找,与第二条语句类似,如果有两页的height都大于2,那么MySQL会将两页的数据都加载进内存,然后再来通过weight匹配正确的数据。

什么情况下会发生明明创建了索引,但是执行的时候并没有通过索引呢?

**科普时间:**查询优化器 一条SQL语句的查询,可以有不同的执行方案,至于最终选择哪种方案,需要通过优化器进行选择,选择执行成本最低的方案。

在一条单表查询语句真正执行之前,MySQL的查询优化器会找出执行该语句所有可能使用的方案,对比之后找出成本最低的方案。这个成本最低的方案就是所谓的执行计划。

优化过程大致如下:

1、根据搜索条件,找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价,找出成本最低的那一个 。

为什么主键通常建议使用自增id呢?

1.建议使用有序的自增ID作为主键

mysql 在底层是以数据页为单位来存储数据的,一个数据页大小默认为 16k,当然你也可以自定义大小,也就是说如果一个数据页存满了,mysql 就会去申请一个新的数据页来存储数据。

  • 如果主键为自增 id 的话,mysql 在写满一个数据页的时候,直接申请另一个新数据页接着写就可以了。
  • 如果主键是非自增 id,为了确保索引有序,mysql 就需要将每次插入的数据都放到合适的位置上。

当往一个快满或已满的数据页中插入数据时,新插入的数据会将数据页写满,mysql 就需要申请新的数据页,并且把上个数据页中的部分数据挪到新的数据页上。这就造成了页分裂,这个大量移动数据的过程是会严重影响插入效率的。

2.在满足业务需求的情况下,尽量使用占空间更小的主键

  • 主键占用空间越大,每个页存储的主键个数越少,路树就越少,B+树的深度会变高,导致IO次数会变多。
  • 普通索引的叶子节点上保存的是主键 id 的值,如果主键 id 占空间较大的话,那将会成倍增加 mysql 空间占用大小。

参考文章
参考文章
参考文章

本文转自互联网,侵权联系删除基于B+树聊聊MySQL的其它知识点

赞(0) 打赏
部分文章转自网络,侵权联系删除b2bchain区块链学习技术社区 » 基于B+树聊聊MySQL的其它知识点
分享到: 更多 (0)

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

b2b链

联系我们联系我们