首页 > 数据库技术 > 详细

MYSQL-索引的选择

时间:2021-05-12 14:51:02      阅读:27      评论:0      收藏:0      [点我收藏+]

索引的选择

 

 

 

  

 

 

索引的定义

索引(Index)是帮助MySQL高效获取数据的数据结构,可以理解为快速查找排好序的一种数据结构。Mysql索引主要三种结构:Hash索引、有序数据索引、B+Tree索引,本篇文章针对InnoDB 引擎中B+Tree的索引展开

 

B+Tree的特点

InnoDB 使用了 B+ 树索引模型,在 InnoDB 中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。所以数据都是存储在 B+ 树中的。所以每一个索引在 InnoDB 里面对应一棵 B+ 树。

通过test表说明

create table test(id int primary key, age int, name varchar(16),index (age));

 技术分享图片

 

 

表中 R1~R5 的 (ID,age) 值分别为 (10,1)、(20,2)、(30,3)、(50,5) 和 (60,6)两棵树的示例示意图如下。

技术分享图片

 

 

 

 

图一 主键索引的结构树

 

 

 技术分享图片

 

 

 

 

图二  非主键索引的结构树

 

B+树的结构特点

非叶子节点存储索引key,叶子节点存储key+data,每个叶子节点增加一个指向相邻叶子节点的指针,就形成了带有顺序访问指针的B+Tree。这样就提高了区间访问性能:如果要查询key为从2到20所有数据记录,当找到2后,只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点,提到了区间查询效率(无需返回上层父节点重复遍历查找减少IO操作)。

主键索引和非主键索引(唯一索引、普通索引等)的结构区别

主键索引叶子节点存储的是key+整行的数据;非主键索引叶子节点存储的是key+主键的值。

基于主键索引和普通索引的查询的区别

如果是select * from T where ID=50,即主键查询方式,则只需要搜索 ID 这棵 B+ 树;

如果是 select * from T where k=5,即普通索引查询方式,则需要先搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程称为回表。

也就是说,基于非主键索引的查询需要多扫描一棵索引树。因此,在应用中应该尽量使用主键查询。

 

 

主键索引的选择

B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护。以图一为例,如果插入新的行 ID 值为 700,则只需要在 R5 的记录后面插入一个新记录。如果新插入的 ID 值为 400,则需要逻辑上挪动后面的数据,空出位置,比较麻烦。而如果 R5 所在的数据页已经满了,根据 B+ 树的算法,这时候需要申请一个新的数据页,然后挪动部分数据过去。这个过程称为页分裂。在这种情况下,性能自然会受影响。页分裂操作还影响数据页的利用率。

 

自增主键

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

 

业务逻辑的字段做主键

首先不容易保证有序插入,这样写数据性能成本相对较高。从存储空间的角度来看。由于每个非主键索引的叶子节点上都是主键的值。如果用比较长的字段做主键,那么每个二级索引的叶子节点占用的字节也会更长。

 

总结:在业务场景只需要一个索引,且要保证唯一性的情况下,用业务字段做主键是比较合适;反之如果不适合上述条件,从性能和存储的角度考虑,使用自增主键更加合理。

 

联合索引的选择

覆盖索引的概念

以图二为例,select id from test where age > 3;

因为id的值已经在age的索引树上了,所以可以直接提供查询结果,不用回表,对于这种索引已覆盖了查询需求的,成为覆盖索引,由于覆盖索引可以减少树的搜索次数,提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。

用以下用户表举例

CREATE TABLE `user` ( `id` int(11), `id_card` varchar(16), `name` varchar(12), `age` int(6), `sex` varchar(4), PRIMARY KEY (`id`), KEY `phone_name` (`phone`,`name`), KEY `name_age_sex` (`name`,`age`,`sex`))

技术分享图片

 

 

 

如果业务场景经常性的需要根据手机号查询用户的姓名,这种情况下,可以建立phone和name的联合索引,phone_name联合索引也是覆盖索引,包含了该经常查询的需求,不需要消耗额外的性能去回表查询。

索引的最左前缀原则

最左优先,索引项按照索引定义里面出现的字段顺序排序。

如上诉索引name_age_sex 可以看做是name单独索引name_age联合索引和name_age_sex联合索引,应以最高频优先进行字段顺序排序。

因此,联合索引的选择应根据业务场景需求,综合考虑性能存储来进行定义。

唯一索引和普通索引的选择

二者的查询区别

select id from test where age=3

对于普通索引,查询到(3,30)时,需要继续向下查询,直到碰到age不等于3为止

对于唯一索引,查询到(3,30)时,可以直接结束查询

从这里看,唯一索引查询更快,但是此处的性能差距很小

因为InnoDB 的数据是按数据页为单位来读写的。当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在 InnoDB 中,每个数据页的大小默认是 16KB。因为引擎是按页读写的,所以说,当找到 age=3 的记录的时候,它所在的数据页就都在内存里了。那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。

所以从查询来看,二者的差别不大

change buffer概念

当需要更新一个数据页时,如果数据页在内存中就直接更新,而如果这个数据页还没有在内存中的话,在不影响数据一致性的前提下,InnoDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性,显然,如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。

二者的更新区别

对于唯一索引来说,所有的更新操作都要先判断这个操作是否违反唯一性约束,要将数据页读入内存判断。而这时直接更新内存会更快,就没必要使用 change buffer 了。所以change buffer不适用唯一索引,适用普通索引

用以下测试表说明

create table test1(id int primary key,name  VARCHAR(8) );

 技术分享图片

 

 

对于要插入一条数据(7,’hh’)来说,有两种场景

第一种场景:记录更新的目标页在内存中

对于唯一索引,找到6和8之间的位置,判断是否符合唯一性,插入结束

对于普通索引,找到6和8之间的位置,插入结束

此处两种索引的区别是一个判断,差距不大

第二种场景: 记录更新的目标页不在内存中

对于唯一索引,找到并将数据页读到内存,找到6和8之间的位置,判断是否符合唯一性,插入结束

对于普通索引,则是将更新记录进change buffer,插入就结束了

结论

将数据页读入内存是比较消耗性能的,可以看出,change buffe减少了对磁盘的访问,性能提升可观,在change buffer更新进原始数据页前,change buffer记录的变更越多,效果就越明显。

因此,对于写多读少的业务来说,此时选择普通索引结合change buffer的使用效果明显,反之对于写后立即读的场景且要求唯一性,因为写后马上要访问数据页,此时change buffer 反而多此一举,唯一索引更加适合

MYSQL-索引的选择

原文:https://www.cnblogs.com/baih/p/14758295.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!