# class3-3

[TOC]

# MySQL 索引

# 索引分类

从索引结构上讲:b-tree、哈希、空间数据索引(r-tree)、全文索引等。

◆ 从索引种类上讲:普通索引、唯一索引、主键索引、组合索引等。

◆ 从存储结构上讲:innodb 索引对数据存储方式还分为聚簇索引和非聚簇索引。

◆ 从索引优化上讲:覆盖索引、索引下推、innodb 的自适应哈希索引等。

# 索引的优点

◆ 索引是查询性能优化的最有效手段。

◆ 索引大大减少了服务器需要扫描的数据行数。

◆ 索引可以将随机 i / o 变成顺序 i / o。

◆ 索引可以帮助服务器避免排序和临时表。

# 索引使用条件

◆ 数据量少的表不适合使用索引,因为全表扫描要比索引更快,所以你加上索引也体现不到索引的价值,还会增加 cpu 的 i/o。

◆ 对于中到大型的数据表,使用索引优化就非常有效,但是也不是每个字段都要建立索引,一般一个表里的索引不要超过五个,还有就是能使用联合索引的尽量使用联合索引,而不是单个索引。

◆ 更新字段比较频繁的表不适合索引,因为大部分都是 innodb 存储引擎,这种存储引擎使用的 b+tree 索引结构,为了保证 b+tree 的平衡性,所以每次插入、更新、删除等操作都需要维护树的平衡性,所以为了减少磁盘的 i / o 的开销,提升写操作的速度,频繁更新的字段不应该建立索引。

https://www.yanghaihua.com/content/2019-08-29/1054.shtml

# 三种索引

索引名称: B+ 位图 Hash
名称 B+树索引 位图索引 哈希索引
结构 B+树 位图 哈希表
适用范围 全值匹配、范围匹配、匹配最左前缀、匹配列前缀、精确匹配某一列并范围匹配另外一列、只访问索引的查询 大量相同数据 不需要做排序、范围查询
不适用范围 后缀匹配、不是按照索引的最左列开始查找、跳过索引中的列、如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查询 大量数据不同 排序、范围查询
有益的例子 Where x=123、Where 45<x<123、where x LIKE ‘J%’ 、where x=123(建立了<x,y>索引,只适用了x)、where x=123 and 45<y<123 、 select x where x = 123 性别:男,女 Where x=123
错误使用的例子 where x LIKE ‘%J’ 、Where z=123 and 45<y<123 ( 建立了<x,y> 索引,却只使用了y)、where x=123,z=456( 对 <x,y,z> 建立了索引,却只是用了x和z)、where 123<x<456 and y = 789 (x适用了范围匹配,后面的y不能使用索引) 身份证号 Where 45<x<123

#

# 补充:为什么说B+树比B树更适合数据库索引?

B+树是上世纪70年代针对硬盘和单核处理器设计的,为了减少机械硬盘的寻道次数,它采用了多叉树结构,降低了索引结构的深度,IO读写次数减少。

熟悉数据结构的同学都知道,B树也是多叉树结构,一种自平衡的树,而且B+树是从B树演化而来的,那么为什么不使用B+树的前身B树呢?一些资料也表明B树也适用于读写相对大的数据块的存储系统,例如磁盘。下面来看下用B树做索引的结构:

b tree

上图小红方块表示文件内容在硬盘中的存储位置。B树相比B+树的一个主要区别就在于B树的分支节点上存储着数据,而B+树的分支节点只是叶子节点的索引而已。

从上面比较B+树和B树的结构,可以得出为什么使用B+树做索引的一些原因(其实网上写B+树索引谈到的大都是以下这些原因):

1. B+树的磁盘读取代价低

B+-tree的内部节点并没有指向关键字具体信息的指针,换句话说,即分支节点没有存储数据,因此其内部节点相对B 树更小。如果把所有同一内部节点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2. B+树的查询效率更加稳定

在B+树中,由于分支节点并不是最终指向文件内容的节点,分支节点只是叶子节点的索引,所以对于任意关键字的查找都必须从根节点走到分支节点,所有关键字查询路径长度相同,每个数据查询效率相当。而对于B树而言,其分支节点上也保存有数据,对于每一个数据的查询所走的路径长度是不一样的,效率也不一样。

3. B+树便于执行扫库操作

由于B+树的数据都存储在叶子节点上,分支节点均为索引,方便扫库,只需扫一遍叶子即可。但是B树在分支节点上都保存着数据,要找到具体的顺序数据,需要执行一次中序遍历来查找。所以B+树更加适合范围查询的情况,在解决磁盘IO性能的同时解决了B树元素遍历效率低下的问题

作者:pjmike_pj 链接:https://juejin.cn/post/6844903760423026702 来源:掘金 著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。

Last Updated: 1/16/2021, 9:27:12 AM