数据库系列5:索引的原理

互联网 2021/4/9 2:25:18

1.基本概念 数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库表中的数据。 1.1 索引的类型 在InnoDB里,索引类型分为三种:普通索引,唯一索引和全文索引。 普通索引:非唯一索引,没有任何限制 唯一索引:要求键值不能重复,主键索引是一…

1.基本概念

数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库表中的数据。

1.1 索引的类型

在InnoDB里,索引类型分为三种:普通索引,唯一索引和全文索引。
普通索引:非唯一索引,没有任何限制
唯一索引:要求键值不能重复,主键索引是一种特殊的唯一索引。主键索引用primary key 创建。
全文索引:针对比较大的数据,比如我们存放的是消息内容,文章的比较大 内容是,如果要like查询效率会很低,此时可以创建全文索引,只有文本类型的字段才可以创建全文索引,比如char,varchar,text等。

1.2 B+树索引

能做索引的有二分查找树,平衡二叉树,红黑树等,但是每个节点只能存一个元素,导致IO浪费,所以B树和B+树是最合适的,但是相比B树,B+树还有如下优点:
几个特点:
1.关键字的数量跟路数是相等的。
2.B+Tree的根节点和枝节点不存储数据,只有叶子节点才存储数据。这样可以使得每个索引节点能存更多的索引,查找范围更广。
3.B+ Tree的每个叶子节点增加了一个指向相邻叶子节点的指针,最后一个数据指向下一个叶子节点的第一个数据,形成一个有序链表的结构。
区间是左闭右开
计算,假如一条记录是16字节,一个叶子节点可以存储10条记录,假设索引字段+指针大小为16字节,则非叶子节点可以存储1000个这样的单元,则深度为2的时候,有1000^2个叶子节点,可以存储的数据为1000100010个。因此一个千万级别的表,查询三次就够了。
** 为什么不用红黑树**
它的特征是从根节点开始最大长度不会超过最短长度的2倍。但是仍然面临每个节点元素太少的问题

1.3索引方式

创建索引有两种方式
1.hash:以KV方式检索数据,也就是说根据索引字段生成Hash码和指针,指针指向数据
Hash的特点是根据hash码直接匹配,查询速度比较快,但是不能排序,不能进行范围查找。
在InnoDB中不能显式创建创建哈希索引,它是InnoDB自动为Buffer Pool中的热点页创建的索引,恶memory引擎也可以使用hash索引。
2.因为B+Tree的特征,广泛用于文件系统和数据库中。

2.索引的落地方式

每张Inno DB 的表有两个文件 .frm和.ibd,MyISAM有三个文件.frm .myd和.myi
有一个是相同的文件.frm,这个是Mysql里表结构定义的文件,其他是存储索引的。
对于MyISAM,myd文件,D代表Data,是MyISAM的数据文件,存放数据记录。一个是myi文件,存放的是索引。也就是说在MyISAM中,索引和数据是两个独立的文件。
在InnoDB中,索引的叶子节点只解存储了数据。这也是为什么说InnoDB中索引即数据的原因。

2.1 聚集索引

一张InnoDB的表可能有很多索引,数据却只有一份,那数据该存在哪个索引的叶子节点上呢?
这里就有了聚集索引(聚簇索引)了,这就是索引键值的逻辑顺序与表数据行的物理存储顺序一样。InnoDB组织数据的方式就是聚集索引组织表。如果说一张表创建了主键索引,那么这个主键索引就是聚集索引。决定数据行的物理存储顺序。那主键之外的索引,会如何记录呢?
InnoDB中,主键索引和辅助索引是有主次之分的。如果有主键索引,那么主键索引就是聚集索引,其他的索引统称为二级索引。
采用的方式是主键索引存储索引和数据,二级索引存储索引和主键值。如下图
在这里插入图片描述

为什么二级索引存主键值而不是地址呢?因为地址可能变化。
如果一张表没有主键怎么办?
策略是:
1.如果定义了主键,那么久选择主键作为聚集索引。
2.如果没有显式定义主键,那么选择第一个不包含NULL值的唯一索引作为主键索引。
3.如果也没有这样的索引,则选择内置的6字节长的RowID作为隐藏的聚集索引,它会随着行记录的写入而递增。

2.2 索引的使用原则

比如我们在user表里给name和phone建立一个联合索引时,索引的结构为
在这里插入图片描述

联合索引在B+树中是复合的数据结构,按照从左到右的顺序来建立搜索树的。如上图,name是有序的,phone是无序的。只有当name相等的时候,phone才是有序的。所以建立联合索引的时候,一定要把常用的放在最左边。

2.3 覆盖索引

回表:非主键索引,先通过索引找到主键索引的键值,然后通过主键值查出索引里没有的数据,这种比基于主键索引的查询多扫描了一颗索引树的方式就叫回表。
图示就是上面的聚集索引的图
在二级索引里,不管是单列索引还是联合索引,如果select的数据列只用从索引中就能取得,不必从数据区中读取,这时候使用的索引就是覆盖索引,这就避免了回表。

2.4 索引条件下推

只适用于二级索引,目标是减少访问表的完整行的读数量从而减少IO操作。
下推的意思就是把过滤的动作在存储引擎做完,而不需要到Server层过滤。比如说给一个表建立了索引(first_name,last_name),假如我们查姓王,并且名子最后一个字为子的,则SQL语句为
select * from user where first_name=‘王’,and last_name like ‘%子’;
这时候有两种执行情况:
第一种:根据联合索引查出所有姓王的二级索引,然后回表,然后将所有数据返回给Server层,在Server层过滤出名字以子结尾的员工。
这样的问题是假如数据有很多,就会将大量的数据丢给Server层。
要知道索引的比较是在存储引擎进行的,而数据记录的比较是在Server层进行的。而当last_name的条件不能用于索引过滤时,Server层不会把last_name的条件传递给存储引擎,所以这时候根据last_name字段过滤的动作,能否在存储引擎做完呢?
这就有了第二种执行方式:根据联合索引查出所有姓王的二级索引数据,然后从二级索引中筛选出last_name以子结尾的索引,然后再回表,到主键索引上查询全部符合条件的数据,返回给Server层。

随时随地学软件编程-关注百度小程序和微信小程序
关于找一找教程网

本站文章仅代表作者观点,不代表本站立场,所有文章非营利性免费分享。
本站提供了软件编程、网站开发技术、服务器运维、人工智能等等IT技术文章,希望广大程序员努力学习,让我们用科技改变世界。
[数据库系列5:索引的原理]http://www.zyiz.net/tech/detail-154098.html

上一篇:HIve SQL学习笔记

下一篇:数据库事务特性

赞(0)

共有 条评论 网友评论

验证码: 看不清楚?
    关注微信小程序
    程序员编程王-随时随地学编程

    扫描二维码或查找【程序员编程王】

    可以随时随地学编程啦!

    技术文章导航 更多>
    扫一扫关注最新编程教程