《MySQL45讲》笔记—索引

文章介绍了数据库索引的作用,包括哈希表、有序数组、二叉树等不同模型。重点讲述了InnoDB存储引擎使用B+树作为索引模型,以及主键索引与非主键索引的区别。文中还提到了覆盖索引、联合索引、最左前缀等概念,并讨论了如何通过索引优化查询性能,如前缀索引和避免索引失效的策略。
摘要由CSDN通过智能技术生成

索引

索引是为了提高数据查询效率,就像书的目录一样。如下图,索引和数据就是位于存储引擎中:
在这里插入图片描述

索引常见模型

哈希表

以键值对存储的数据结构。适用于只有等值查询的场景。
在这里插入图片描述

有序数组

在等值查询和范围查询场景中性能都特别优秀。但是有序数组索引只适用于静态存储引擎,如果需要更新数据的话就成本太高了。
在这里插入图片描述

二叉树

每个节点的左儿子小于父节点,父节点又小于右儿子。二叉树的搜索效率是最高的,但是实际上大多数的数据库存储并不使用二叉树,因为索引不止存在内存中,还要写道磁盘上。一般使用N叉树,这个N取决于数据块的大小。
在这里插入图片描述

数据库发展到今天,跳表、LSM树等数据结构也被应用于引擎设计中,但是数据库底层存储的核心就是基于这些数据模型的。

InnoDB的索引模型

表都是根据主键顺序以索引的形式存放的,这种存储方式称为索引组织表。InnoDB使用了B+树的索引模型,所以数据都是存储在B+树中的。
在这里插入图片描述
索引类型分为主键索引和非主键索引:

  • 主键索引:叶子节点存的是整行数据,也被称为聚簇索引
  • 非主键索引:叶子节点内容是主键的值,也被称为二级索引

基于主键索引和普通索引的查询有什么区别?

  • 如果语句是select *fromTwhere ID=500,即主键查询方式,则只需要搜索ID这棵B+树;
  • 如果语句是select *fromTwhere k=5,即普通索引查询方式,则需要先搜索k索引树,得到ID
    的值为500,再到ID索引树搜索一次。这个过程称为回表。

也就是说,基于非主键索引的查询需要多扫描一棵索引树,所以尽量使用主键查询。

为什么InnoDB选择B+树作为数据结构

B树
  • B树在非叶子节点也要存储数据,** B 树的每个节点都包含数据(索引+记录)**,而用户的记录数据的大小很有可能远远超过了索引数据,这就需要花费更多的磁盘 I/O 操作次数来读到「有用的索引数据」。
  • 而B+树叶子节点才会存放索引+记录,非叶子节点只会存放索引。非叶子节点的索引也会同时存在于子结点中。

相同磁盘I/O次数下,B+可以查到更多节点。而且B+树叶子采用的是双链表,适合于MySQL中常见的基于范围的顺序查找,而B树无法做到这一点。

二叉树

太高了。

hash

hash只适合等值查询,不适合范围查询。

索引维护

  • 自增主键:是指自增列上定义的主键,在建表语句中一般是这么定义的: NOTNULL PRIMARY
    KEY AUTO_INCREMENT。
    插入新纪录的时候可以不指定ID的值,系统会获取当前ID最大值+1作为下一条记录的ID值,也就是说,自增主键的插入数据模式,符合递增插入的场景,每次插入一条新纪录都是追加操作,都不涉及到挪动其他记录也不会触发叶子节点的分裂。

有没有什么场合适合使用业务字段直接做主键呢?有的,比如有些业务场景需求是:
1、 只有一个索引
2、 该索引必须是唯一索引
由于没有其他索引,所以也就不用考虑其他索引的叶子节点大小问题。这个时候就要优先将这个索引设置为主键,尽量使用主键查询,避免每次查询需要搜索两棵树。

覆盖索引

如果执行的语句是select ID fromTwhere k between 3 and 5,这时只需要查ID的值,而ID的值已经在K索引树上了,因此可以直接提供查询结果,不需要回表。也就是说,在这个查询里面,索引k已经覆盖了我们的查询需求。
覆盖索引是指 SQL 中 query 的所有字段,在索引 B+Tree 的叶子节点上都能找得到的那些索引,从二级索引中查询得到记录,而不需要通过聚簇索引查询获得,可以避免回表的操作。

联合索引

建立在多列上的索引称为联合索引。

最左前缀

在这里插入图片描述
可以看到,索引项是按照索引定义里面出现的字段顺序排序的

  • 当你的逻辑需求是查到所有名字是“张三”的人时,可以快速定位到ID4,然后向后遍历得到所有
    需要的结果。
  • 如果你要查的是所有名字第一个字是“张”的人,你的SQL语句的条件是"where name like
    ‘张%’"。这时,你也能够用上这个索引,查找到第一个符合条件的记录是ID3,然后向后遍历,
    直到不满足条件为止。

可以看到,不只是索引的全部定义,只要满足最左前缀,就可以利用索引来加速检索。这个最左
前缀可以是联合索引的最左N个字段,也可以是字符串索引的最左M个字符。

如何索引内字段顺序?

考虑索引的复用能力:第一原则是,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。

索引下推

MySQL 5.6 引入的索引下推优化(index condition pushdown), 可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。

  • 对于这条语句
mysql> select * from tuser where name like '张%' and age=10 and ismale=1

没有下推

在这里插入图片描述
这个过程InnoDB并不会去看age的值,只是按顺序把“name第一个字是’张’”的记录一条条取出来回表。因此,需要回表4次。

有下推

在这里插入图片描述
InnoDB在(name,age)索引内部就判断了age是否等于10,对于不等于10的记录,直接判断并跳过。在这个例子中,只需要对ID4、ID5这两条记录回表取数据判断,就只需要回表2次。

什么时候需要索引?什么时候不需要?

需要索引

  • 字段有唯一性限制的,比如商品编码;
  • 经常用于 WHERE 查询条件的字段,这样能够提高整个表的查询速度,如果查询条件不是一个字段,可以建立联合索引。
  • 经常用于 GROUP BY 和 ORDER BY 的字段,这样在查询的时候就不需要再去做一次排序了,因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的。

不需要索引

  • WHERE 条件,GROUP BY,ORDER BY 里用不到的字段,索引的价值是快速定位,如果起不到定位的字段通常是不需要创建索引的,因为索引是会占用物理空间的。
  • 字段中存在大量重复数据,不需要创建索引,比如性别字段,只有男女,如果数据库表中,男女的记录分布均匀,那么无论搜索哪个值都可能得到一半的数据。在这些情况下,还不如不要索引,因为 MySQL 还有一个查询优化器,查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。
  • 表数据太少的时候,不需要创建索引;
  • 经常更新的字段不用创建索引,比如不要对电商项目的用户余额建立索引,因为索引字段频繁修改,由于要维护 B+Tree的有序性,那么就需要频繁的重建索引,这个过程是会影响数据库性能的。

索引优化

前缀索引优化

使用前缀索引是为了减小索引字段大小,可以增加一个索引页中存储的索引值,有效提高索引的查询速度。在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减小索引项的大小。
不过,前缀索引有一定的局限性,例如:

  • order by 就无法使用前缀索引;
  • 无法把前缀索引用作覆盖索引;

覆盖索引优化

可以建立一个联合索引,即「商品ID、名称、价格」作为一个联合索引。如果索引中存在这些数据,查询将不会再次检索主键索引,从而避免回表。所以,使用覆盖索引的好处就是,不需要查询出包含整行记录的所有信息,也就减少了大量的 I/O 操作。

主键索引自增

建表的时候,默认将主键索引设置为自增的。

索引最好设置为NOT NULL约束

  • 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂,更加难以优化,因为可为 NULL 的列会使索引、索引统计和值比较都更复杂,比如进行索引统计时,count 会省略值为NULL 的行
  • NULL 值是一个没意义的值,但是它会占用物理空间,所以会带来的存储空间的问题。

防止索引失效

避免写出索引失效的查询语句,否则这样的查询效率是很低的。

索引失效的情况
  • 当我们使用左或者左右模糊匹配的时候,也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效;
  • 当我们在查询条件中对索引列做了计算、函数、类型转换操作,这些情况下都会造成索引失效;
  • 联合索引要能正确使用需要遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
  • 在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值