大家好,欢迎来到IT知识分享网。
Sql索引(index)
定义
索引是一种排好序的快速查找的数据结构,它帮助数据库高效的进行数据的检索。在数据之外,数据库系统还维护着满足特定查找算法的数据结构(额外的存储空间),这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。
一般来说索引本身也很大,不可能全部存储在内存中,因此往往以索引文件的形式存放在磁盘中。目前大多数索引都采用BTree树方式构建。
分类
- 单值索引:一个索引只包括一个列,一个表可以有多个列
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引(联合索引):一个索引同时包括多列
复合索引特性
复合索引最左特性(原则)
在Mysql建立复合索引时有最左前缀的原则。当建立一个复合索引(col1,col2,col3)时,实际上建立了三个索引,即(col1)、(col1,col2)、(col1,col2,col3)。
原理
复合索引的结构是B+树,比如(name,age,sex)的时候,b+树是按照从左到右的顺序来建立搜索树的,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex。当age或者sex字段缺失时,b+树就不知道第一步该查哪个节点,所以只能把名字name的数据都找到,即索引的最左匹配特性。
索引及其扫描类型
type:
- ALL 全表扫描,没有优化,最慢的方式
- index 索引全扫描
- range 索引范围扫描,常用语<,<=,>=,between等操作
- ref 使用非唯一索引扫描或唯一索引前缀扫描,返回单条记录,常出现在关联查询中
- eq_ref 类似ref,区别在于使用的是唯一索引,使用主键的关联查询
- const 当查询是对主键或者唯一键进行精确查询,系统会把匹配行中的其他列作为常数处理
- null MySQL不访问任何表或索引,直接返回结果
- System 表只有一条记录(实际中基本不存在这个情况)
性能排行:
System > const > eq_ref > ref > range > index > ALL
possible_keys:
- 显示可能应用在这张表中的索引
key:
- 真正使用的索引方式
索引的优缺点
优点:
- 索引是数据库优化
- 表的主键会默认自动创建索引
- 大量降低数据库的IO磁盘读写成本,极大提高了检索速度
- 索引事先对数据进行了排序,降低查询是数据排序的成本,降低CPU的消耗
缺点:
- 索引本身也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也要占用空间
- 索引表中的内容,在业务表中都有,数据是重复的,空间是“浪费的”
- 虽然索引大大提高了查询的速度,但反向影响了增、删、改操作的效率。如表中数据变化之后,会造成索引内容不正确,需要更新索引表信息,如果数据量非常巨大,重新创建索引的时间就大大增加
- 随着业务的不断变化,之前建立的索引可能不能满足查询需求,需要消耗我们的时间去更新索引
扩展:索引工作原理
BTree+索引
- 浅蓝色: 磁盘块
- 深蓝色: 数据项
- 黄色: 数据的指针
真实的数据仅在叶子节点中: 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90,99
查找过程: 假如要找29
- 从树根开始,即先把磁盘块1中内容读到内存中,发生一次IO
- 确定29在(17,35)之间,锁定磁盘块1中的P2指针
- 根据P2指针,找到磁盘块3,读取到内存中,发生二次IO
- 29在(26,30)之间,锁定磁盘块3的P2指针
- 通过磁盘3的P2指针,将磁盘块8的内容读取到内存中,发生第三次IO
- 最终找到数据29,查询结束,总共发生三次IO
怎么判断是否创建索引?
创建索引:
- 主键约束默认建立唯一索引
- 频繁出现在where查询条件的字段
- 多表查询中与其它表进行on关联的字段,外键关系
- 单列索引/复合索引的选择? 高并发下倾向于创建复合索引
- 查询中经常用来排序的字段
- 查询中经常用来统计或者分组字段
不创建索引:
- 频繁更新的字段: 每次更新都会影响索引树
- where条件查询中用不到的字段
- 表记录太少
- 经常增删改的表: 更新了表,索引也得更新才行
- 注意: 如果一张表中重复的记录非常多,为它建立索引就没有太大意义
为什么Mysql用B+树做索引而不用B-树或红黑树
- B+树只有叶节点存放数据,其余节点用来索引
- B-树是每个索引节点都会有Data域
从Mysql(InnoDB)的角度来看,B+树是用来充当索引的,一般来说索引非常大,尤其是关系性数据库这种数据量大的索引能达到亿级别,所以为了减少内存的占用,索引也会被存储在磁盘上。
那么Mysql如何衡量查询效率呢?磁盘IO次数。B-树/B+树的特点就是每层节点数目非常多,层数很少,目的就是为了就少磁盘IO次数,但是B-树的每个节点都有data域(指针),这无疑增大了节点大小,说白了增加了磁盘IO次数(磁盘IO一次读出的数据量大小是固定的,单个数据变大,每次读出的就少,IO次数增多,一次IO多耗时),而B+树除了叶子节点其它节点并不存储数据,节点小,磁盘IO次数就少。这是优点之一。
另一个优点是:B+树所有的Data域在叶子节点,一般来说都会进行一个优化,就是将所有的叶子节点用指针串起来。这样遍历叶子节点就能获得全部数据,这样就能进行区间访问。在数据库中基于范围的查询是非常频繁的,而B树不支持这样的遍历操作。
B树相对于红黑树的区别:
平衡二叉树AVL 和红黑树基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候,红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁,进而导致效率低下的情况。为什么会出现这样的情况,我们知道要获取磁盘上数据,必须先通过磁盘移动臂移动到数据所在的柱面,然后找到指定盘面,接着旋转盘面找到数据所在的磁道,最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上,树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定,所以,只要我们通过某种较好的树结构减少树的结构尽量减少树的高度,B树可以有多个子女,从几十到上千,可以降低树的高度。
同时,数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:每次新建节点时,直接申请一个页的空间,这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个node只需一次I/O。
为什么索引快?
- 排序
- 树形结构,类似二分查找
3层的BTree可以表示上百万的数据,如果上百万条数据,查找只需要三次IO,性能提高将是巨大的,如果没有索引每次查找都要发生一次IO,那么总共就需要百万次的IO,显然成本是非常高的。
免责声明:本站所有文章内容,图片,视频等均是来源于用户投稿和互联网及文摘转载整编而成,不代表本站观点,不承担相关法律责任。其著作权各归其原作者或其出版社所有。如发现本站有涉嫌抄袭侵权/违法违规的内容,侵犯到您的权益,请在线联系站长,一经查实,本站将立刻删除。 本文来自网络,若有侵权,请联系删除,如若转载,请注明出处:https://yundeesoft.com/24834.html