索引用来做什么
数据库索引的出现用来提高查询效率。像查字典的目录一样,根据目录去拿到相应的内容能大大提高查询效率。
索引是什么
索引是一种有序的数据结构。可以用B+Tree来实现。对应mysql的内核,事务和索引都是在存储引擎层实现的。
为什么用B+Tree来实现呢?
我们以下分析用于提高读写效率的数据结构
哈希表
哈希表构成
哈希表是一种哈希结构,存的是键 - 值(key-value)对。
前面是数组,后面跟一个链表。
哈希表查询
Key value都放在链表里面,用key算出的哈希值放在数组里。当需要用key查value时,先调用哈希函数算出哈希值,找到指定数组,再用key去遍历后面的链表,因为链表中同时包含key value,就可以找到指定的value了。
哈希表优缺点
优点:哈希表在数组中的顺序并不是递增的,是由哈希函数算出来的。所以追加新数据的时候执行效率快,只需要在后面追加就可以了。
缺点:由于不是递增的,所以哈希索引做区间查询效率慢,不适合区间查询(需要全表遍历),适合适合等值查询,等值查询时间复杂度为O(1)。
适用场景
hash表这种结构适用只有等值查询的场景
比如Memchached引擎以及一些NoSql引擎(Redis)
有序数组
有序数组查询
有序数组按照某顺序依次进行保存,所以
有序数组在等值查询和范围查询场景中的性能都非常优秀。
当等值查询的时候,采用二分法,时间复杂度为O(Log(N))
当区间查询的时候,采用二分法查询到区间开始位置,从该位置向后遍历直至找到结尾位置。
有序数组优缺点
如果仅看查询效率,有序数组是最好的结构。
但是当对存储进行增删的时候,二分查找到定位后挪动后面的所有数据,消耗成本大。
有序数组适用场景
有序数组适用于静态存储引擎场景。也就是,静态数组静态数据。
搜索树
搜索树特点
二叉搜索树的特点是:父节点左子树所有结点的值小于父节点的值,右子树所有结点的值大于父节点的值
为了维持 O(log(N)) 的查询复杂度,你就需要保持这棵树是平衡二叉树(概念之后补)。为了做这个保证,更新的时间复杂度也是 O(log(N))。
二叉树也会畸变成单链表,所以才有了AVL树通过旋转的方式来维持树的平衡,但后来发现大量的旋转实在是性能不好,所以有了红黑树。都是二叉树,只是约束条件不一样。没有最好的方法只有更适合的方法,要维持某一方面的优势需要牺牲另一方面的优势,就看如何选择了。
搜索树查询
为什么数据库存储使用b+树而不是二叉树?
因为二叉树树高过高,需要高成本的磁盘 IO。每次查询都需要访问过多节点,即访问数据块过多,而从磁盘随机读取数据块过于耗时。
索引不止存在内存中,还要写到磁盘上,所以实际上大多数的数据库存储并不使用二叉树。
搜索树每访问一层就读取一个数据块,不同的数据块之间切换需要进行磁盘IO(耗时),所以层数决定了访问数据块的多少。为了让一个查询尽量少地读磁盘,就必须让查询过程访问尽量少的数据块。那么,我们就不应该使用二叉树,而是要使用“N 叉”树。这里,“N 叉”树中的“N”取决于数据块的大小。
N叉树如何进行计算
MySql默认一个节点的长度为16K,当在mysql的InnoDB引擎中申明一个整数字段的索引,那么一个整数(bigint)字段索引的长度为 8B,另外每个索引还跟着6B的指向其子树的指针;所以16K/14B ≈ 1170,也就是说,一个整数字段的索引,N约等于1200 。
也就是说,树的第四层可以存到1200的三次方,17亿个值。由于树根的数据块总是在内存中,所以一个 10 亿行的表上一个整数字段的索引,查找一个值最多只需要访问 3 次磁盘。而且树的第二层也很大概率在内存中,所以访问磁盘的次数可以更少。
搜索树适用场景
N 叉树由于在读写上的性能优点,以及适配磁盘的访问模式,已经被广泛应用在数据库引擎中了。对于最广泛应用的InnoDB引擎来说,每一个索引在存储引擎中对应一棵B+树
数据库技术发展到今天,跳表、LSM 树等数据结构也被用于引擎设计中 。
怎么给字段加索引
当有一个表T要给列K加索引,建表语句如下:
mysql> create table T(
id int primary key,
k int not null,
name varchar(16),
index (k))
engine=InnoDB;
关于 InnoDB 的表结构:
1.在 InnoDB 中,每一张表其实就是多个 B+ 树,即一个主键索引树和多个非主键索引树。
2.执行查询的效率,使用主键索引 > 使用非主键索引 > 不使用索引。
3.如果不使用索引进行查询,则从主索引 B+ 树的叶子节点进行遍历。
索引如何工作
每一张表其实就是好几个B+树(每一个索引是一个B+树),树结点的key值就是某一行的主键,value是该行的其他数据。新建索引就是新增一个B+树,查询不走索引就是遍历主B+树。
聚簇索引和非聚簇索引
如上所示,索引分为两种类型:聚簇索引(主键索引)和非聚簇索引(非主键索引)
- 主键索引: key:主键的值,value:整行数据
- 非主键索引(二级索引): key:索引列的值, value:主键的值。
注1:MySQL中聚簇索引一般是主键,没有主键一般是第一个非空字段,没有非空字段会有一个隐式的自增长的值做聚簇索引。
注2: B+树主键索引的叶子结点,应该是页,每个页包含多个行
注3: 叶子节点是双向链表形式的页,单个页内部是顺序链表,链表每个节点是一行数据
基于主键索引和普通索引的查询有什么区别?
如果语句是 select * from T where ID=500,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;
如果语句是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。
也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,我们在应用中应该尽量使用主键查询。
索引如何维护
B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。
- 如果插入新的行 ID 在页最后,则只需要在最后一条记录后面插入一个新记录。
- 如果新插入的 ID 值在中间,就相对麻烦了,需要逻辑上挪动后面的数据,空出位置。
页分裂
- 如果 该ID所在的数据页已经满了,根据 B+ 树的算法(b+树的语法设计规则,如果最大阶数为n那么当一个节点中存放的数据页达到n的时候,b+就会自平衡,保持他的属性),这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。这也是索引推荐自增的原因。
- 除了性能外,页分裂操作还影响数据页的利用率。原本放在一个页的数据,插入了一条数据,现在分到两个页中,整体空间利用率降低大约 50%。
所以,
频繁插入和修改的列最好不要建索引;推荐使用自增主键,就可以保证新的ID一定是在叶子节点最右边,不会影响前面的数据。(防止页分裂)
页合并
当相邻两个页由于删除了数据,利用率很低之后,会将数据页做合并。合并的过程,可以认为是分裂过程的逆过程。所以,很多数据表删除数据都是逻辑删除而非物理删除(防止页合并)。
b+树,原本的规则,插入数据是会可能导致分裂的,因为一个节点只能存储m个元素,超过m个,就会分裂,这里老师讲的不会触发叶子节点的分裂,主要是因为,innoDB对于新插入操作,做了特殊处理,如果判断需要发生分裂时,先判断左(右)兄弟节点是否还有空位,如果有就把最左(右)边的数据迁移到左(右)节点,然后更新维护索引页,最后当前节点插入,从而阻止了分裂发生,如果左右都满了,还是会发生分裂
哪些场景下应该使用自增主键,而哪些场景下不应该?
自增主键是指自增列上定义的主键,在建表语句中一般是这么定义的: NOT NULL PRIMARY KEY AUTO_INCREMENT。插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
从性能和存储空间方面考量,自增主键往往是更合理的选择。
性能层面
- 自增主键的插入数据模式,正符合了我们前面提到的递增插入的场景。每次插入一条新记录,都是追加操作,都不涉及到挪动其他记录,也不会触发叶子节点的分裂。
- 而有业务逻辑的字段做主键,则往往不容易保证有序插入,这样写数据成本相对较高。
存储空间层面
- 二级索引存储的值是主键,如果使用业务字段占用大小不好控制,如果业务字段过长可能会导致二级索引占用空间过大,利用率不高。
- 由于每个非主键索引的叶子节点上都是主键的值。主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间也就越小(如果用身份证号做主键,那么每个二级索引的叶子节点占用约 20 个字节,而如果用整型做主键,则只要 4 个字节,如果是长整型(bigint)则是 8 个字节。)
什么场景适合用业务字段直接做主键的呢?KV 场景
- 只有一个索引;
- 该索引必须是唯一索引。
这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则,直接将这个索引设置为主键,可以避免每次查询需要搜索两棵树。
如果重建(删除,新建)主键索引,会同时去修改普通索引对应的主键索引,性能消耗比较大。
删除重建普通索引貌似影响不大,不过要注意在业务低谷期操作,避免影响业务。
索引的查询-回表
当我们有一个查询是根据索引查询k的值在3到5之间的数据时,mysql内核是这样执行的:
- 在 k 索引树上找到 k=3 的记录,取得 ID = 300;
- 再到 ID 索引树查到 ID=300 对应的 R3;
- 在 k 索引树取下一个值 k=5,取得 ID=500;
- 再回到 ID 索引树查到 ID=500 对应的 R4;
- 在 k 索引树取下一个值 k=6,不满足条件,循环结束。
以上从非主键索引树回到主键索引树的过程叫做回表
这个查询过程读了 k 索引树的 3 条记录(步骤 1、3 和 5),回表了两次(步骤 2 和 4)。
注:取下一个值的过程,由于叶子结点是由链表串起来的,实际上是找的下一个指针指向的位置。
如何经过索引的优化拒绝回表的过程?
覆盖索引
使用覆盖索引可以减少树的搜索次数,显著地提高查询性能。覆盖索引是一个常用的性能优化手段。
当二级索引查询结果仅仅是主键(或者所要的信息已经存在于这棵树的叶子结点上),此时不需要回表查主键索引–这样的非主键索引称为覆盖索引
比如:如果执行的语句是 select ID from T where k between 3 and 5,这时只需要查 ID 的值,而 ID 的值已经在 k 索引树上了,因此可以直接提供查询结果,不需要回表。
在一个人员信息表,有必要将证件号和姓名建立联合索引吗?
当访问频次不高时,没有必要。因为可以根据证件号唯一找到一个人,这样只需要将证件号建立索引,再通过证件号索引进行回表查询姓名即可。建立联合索引将发生空间的浪费。
当有高频的访问请求的时候就有意义了。给id_card 和 name建立联合索引后,name的值也会被保存在id_card索引树的节点上,这样根据给定id_card的值找到的对应行时,就可以直接获取到name了,而不需要拿着对应的主键再进行回表操作。这将会提升效率,但是也会带来字段维护的代价,所以建立冗余索引来支持覆盖索引需要权衡考虑。
最左前缀原则
最左前缀原则解决的问题
单独为一个不频繁的请求创建一个索引,有点浪费;
不创建索引的话,虽然查询频率不高,但又不能让它全表扫描;
以上这种问题,B+ 树这种索引结构,可以利用索引的“最左前缀”,来定位记录。
最左前缀原则是什么
联合索引先根据第一个字段排序,如果第一个字段有相同的,就按照第二个字段排序,注意,这里仅仅有相同的第一个字段情况下,才会根据第二个字段排序。
有(姓名,年龄)联合索引如下图:
- 当我们要查所有名字是“张三”的人时,可以快速定位到 ID4,然后向后遍历得到所有需要的结果。
- 当要查的是所有名字第一个字是“张”的人,也能够用上这个索引,查找到第一个符合条件的记录是 ID3,然后向后遍历,直到不满足条件为止。
由如上例子可以看出:索引项是按照索引定义里面出现的字段顺序排序的。只要满足最左前缀,就可以利用索引来加速检索。这个最左前缀可以是联合索引的最左 N 个字段,也可以是字符串索引的最左 M 个字符。
为什么可以用最左前缀原则
MYSQL做词法分析语法分析的时候是通过建立最左子树来建立语法树的,解析的过程也是从左到右所以遵循最左前缀的原则。
在建立联合索引的时候,如何安排索引内的字段顺序。
- 如果能通过调整顺序少维护一个索引,那么此顺序优先考虑