索引的选择
索引(Index)是帮助MySQL高效获取数据的数据结构,可以理解为快速查找排好序的一种数据结构。Mysql索引主要三种结构:Hash索引、有序数据索引、B+Tree索引,本篇文章针对InnoDB 引擎中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)两棵树的示例示意图如下。
图一 主键索引的结构树
图二 非主键索引的结构树
非叶子节点存储索引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 反而多此一举,唯一索引更加适合
原文:https://www.cnblogs.com/baih/p/14758295.html