大纲内容
- 索引的数据结构
- 主键索引,普通索引,唯一索引的区别
- 联合索引
- 前缀索引
读书才能够系统的学习,听别人偶尔的两句断章取义的结论,远远不能够支撑设计一套系统。:
博主建立了一个群, 有兴趣的可以一起一起分享问题, 解决问题, 分享面试题, 内推。
索引的好处:比如一本书的目录,如果存在目录,找某个章节是很快的,如果不存在目录,即一页一页的找对应的章节,此处目录比做索引。
索引的数据结构
哈希表
以key-value的形式存储数据。将key(新增/更新数据)进行hash算法运算,计算出哈希表的索引下标,如果索引下标为空,则直接插入,作为链表的头结点,如果索引下标有值,说明有哈希冲突,则通过尾插法插入结点。
查询操作:先通过hash算法获取哈希表的索引下标,如果索引下标存在,则按链表的顺序从中查询,对索引的key进行依次hash计算就可以定位到数据存储的位置。
缺点:哈希表只能做等值查询,链表中无法判断是否有序,用哈希表做范围查找的效率很慢。
有序数组
优点:查询用二分查找,时间复杂度为log(n),适用于静态存储,不会有新增的操作。
缺点:进行插入时,如果是插入中间元素,会挪动后续的所有数组元素。
B+树
索引结构为B+树,它是排好序的数据结构。主键索引中,非叶子结点存储的是索引值,叶子结点存储的是行数据,普通索引中,叶子结点存储的是索引值和主键ID,同时叶子结点间是链表组成。
主键索引和普通索引的区别是什么?
非叶子节点:主键索引和普通索引存储的都是索引值。
叶子结点:主键索引存储的是行数据,而普通索引存储的是普通索引值+主键ID,如果用普通索引值进行搜索,会触发回表操作,即多访问一次索引树的次数。如果想减少回表操作,将select * 改为普通索引树中存储的字段即可。
假设普通索引name, 固普通索引树上叶子结点存储的是name值+主键ID
将select * 改为 -->select id,name 可减少回表次数,只要普通索引树上存在的字段查询可减少回表,不存在的字段才需要回表。
如果想拿到更多的字段,同时想减少回表操作,可用联合索引,因联合索引上存储的更多的索引值+主键ID。
为什么建议使用自增主键,同时要用整型数据类型?
在MySQL中,char占用3n个字节,varchar占用3n+2个字节,bigint占用8字节,tinyint占用1字节,如果用字符串数据类型,在普通索引树中存储的数量肯定比整型存储的数量要少很多,在内存维度上,用整型数据类型更划算。因为B+树是帮我们排序好的数据结构,如果非自增的数据类型,MySQL会帮我们手动排序,同时会造成页分裂,页分裂会带来拷贝数据集的成本。
- 如果没有设置主键索引,MySQL会帮我们找到第一个普通索引为主键索引。
- 如果没有设置主键索引,并且没有普通索引,MySQL会帮我们设置一个隐式主键索引。
主键索引,普通索引,唯一索引的区别
假设有一张用户表user,其中有身份证字段id_card,假设业务给我们做了唯一性校验,当前SQL该如何加索引?
SQL语句:
select * from user where id_card="422112121321322321xx";
查询
主键索引
不适合,当前id_card是字符串,我们推荐使用整型数据类型,同时支持自增主键,
字符串varchar占用3n+2个字节,而整型bigint占用8个字节,站在内存维度,
由于普通索引树中存储的是当前索引值+主键ID,固可以在普通索引树上存储更多的节点。
B+树的索引是帮我们排好序的数据结构,由于是字符串,固MySQL还要帮我们排序字符串,
会带来页分裂导致数据集拷贝的风险,额外带来了性能消耗,固主键索引适合是整型且自增的类型。
普通索引
适合,普通索引叶子节点存储的是当前索引值和主键ID,通过id_card的索引值,
树的根节点通过二分查找,找到对应的叶子结点,如果找到符合条件的索引值,
还是会继续往后查询,当遍历到第一个不为当前索引值的节点时,遍历结束,
将当前值所在的对应的数据页加载进内存中。
特殊场景。(InnoDB在读取磁盘数据文件时,都是以数据页为单位读取到内存中)。
唯一索引
适合,唯一索引有唯一性约束,当遍历到第一个符合当前索引值时,查询便结束,
不会往下记录直至第一个不为当前索引值。
针对于查询而言,普通索引和唯一索引的区别:普通索引会找到第一个不为当前索引值的数据为止,而唯一索引因为有唯一性校验,找到即结束。
新增/更新
唯一索引
假设数据页不在内存中,涉及到唯一索引字段的新增/更新,比如name为唯一索引,name=“peter”,
InnoDB会先去磁盘数据文件中找到当前值可能对应的数据页中,并把数据页加载进内存,
在内存中判断是否存在peter字段,如果不存在,则可以插入,如果存在,则抛出异常。
假设数据页在内存中,则判断是否存在冲突,如果没有冲突,则插入。
- 由于唯一性校验,唯一索引进行了一次磁盘IO,把数据页加载进内存中。
普通索引
假设数据页不在内存中,开启change buffer,可以减少磁盘的读操作
将当前新增/更新操作记录到change buffer中。
假设数据页在内存中,则直接新增/更新到对应的位置中。
针对于新增/更新而言,假设数据页不在内存中,唯一索引会去磁盘文件中加载对应的数据页,进行唯一性校验,而普通索引会因为有change buffer的开启带来减少一次磁盘的读操作,将更新/新增记录先记录至change buffer中。
什么是change buffer?
change buffer:只针对于普通索引有效,同时数据页不在内存中。
- 如果数据页在内存中,若是唯一索引,则直接进行唯一性校验,若不存在冲突,则直接DML操作,若是普通索引,则直接DML操作。
- 如果数据页不在内存中,针对于普通索引而言,会把对应的新增/更新操作都记录到change buffer中去,目的是为了减少磁盘读操作,提高操作效率,因为读取磁盘是最消耗时间成本之一。
可以理解:change buffer就是一个伪数据页。
change buffer的适用场景
写多读少的场景,涉及到读,还是会去查询磁盘中对应的数据页,加载进内存中,与伪数据页(change buffer)进行对比,会造成merge操作,将最新结果集返回给执行器。如果在写少读多的场景,涉及到读还是将查询到的数据页加载进内存中,无法发挥change buffer的优势,同时还带来维护成本,change buffer的优点就是为了在写操作时避免去读磁盘操作。
存储在change buffer中的文件,何时同步进磁盘?
在查询时,会将老数据页与新数据页进行merge操作,同步进磁盘中,在系统空闲时,会同步进磁盘中,在数据库正常关闭时,会同步进磁盘中。
个人理解:这个同步进磁盘中,都是同步进redo log文件中,最终redo log会flush进磁盘数据文件中,因为没有看过源码, 我初步猜想,redolog是避免了频繁写磁盘,而change buffer是避免了频繁读磁盘,redolog中存储的数据页和change buffer,change buffer中存储不在内存中的数据页,都是未同步进磁盘的脏页数据,而change buffer页属于一种伪数据页,最终数据都是被redolog给flush进磁盘数据文件中。
insert into t(id,k) values(id1,k1),(id2,k2);
站在k是普通索引的维度来分析,k1所在的数据页D1在内存中,k2所在的数据页D2不在内存中。
1:因为k1所在的数据页D1在内存中,固直接在D1中按从值的小到大,顺序插入。
2:k2的数据页不在内存中,此时又开启了change buffer,会减少磁盘读操作,将新增操作先记录到伪D2中,此时内存中有一个changebuffer,有一个真数据页。
3:将上述一致同步进redolog中,最终事务提交,等系统空闲时同步进数据磁盘中。
结论:同步进change buffer中的数据,通过redo log日志来写入磁盘。
此时 select * from user where k in (k1,k2)
只有真的在查询D2数据页时,才会将数据页加载进内存中,执行引擎每次读取磁盘,都是以数据页为单位加载进内存中。
1:读D1的时候,由于D1在内存中,所以直接从内存中返回。
2:读D2的时候,由于内存中存储的是伪数据页change buffer,此时读磁盘时,会拿原数据页和伪数据页进行merge,返回最新数据给执行器。
change buffer和redolog有什么关联吗?
redolog中包括了change buffer和数据页,redolog是为了避免频繁写磁盘,而change buffer有利于普通索引的修改/新增,目的是为了避免频繁读磁盘。
如果没有change buffer, 正常的流程如下
1:先读取该值所在的数据页,加载进内存中,在内存中找到对应的行,进行修改操作,同时生成undolog日志版本链。
2:再将修改的操作写进redolog中,当前redolog处于prepare状态,再写入binlog中,再修改redolog状态为commit状态。
3:等空闲的时候,内存中的数据同步进数据磁盘文件中,同步checkPoint redolog文件。
什么时候用change buffer?
对于唯一索引,所有的更新操作都要先判断是否违反唯一性约束,比如要插入一条name =“peter”,当前name是唯一索引,就要判断name="peter"是否已经存在,所以查询之前,必须要磁盘IO一次加载数据页进内存中,既然存在数据页,还要change buffer有什么用?
对于普通索引,change buffer能避免频繁读操作,change buffer用的是buffer bool的内存,一般设置为buffer pool的40%。
联合索引
假设当前联合索引:
KEY 'idx_name_age_position'('name','age','position') 姓名-年龄-环境
等同于建立了(‘name’),(‘name’,‘age’),(‘name’,‘age’,‘position’)三个索引。
有人可能会问: 为什么不直接建立三个索引(‘name’),(‘age’),(‘position’)三个索引呢?维护索引也是需要成本的,站在数量维度分析,能维护一个索引,为什么要维护三个索引,同时联合索引存在更大几率较少回表,可选的字段更多。
联合索引的最左前缀原则
可以是联合索引的最左的N个字段, 也可以是首个name索引的最左N个字符。
- 必须遵循最左边的顺序索引元素。比如顺序name,age,position。当然优化器可以帮我们解决排序问题,当用到age,name时,MySQL能帮我们排序称name,age两个索引,当用name,position,中间断开了,不遵循最左前缀原则,只能用到name索引。
- 建立联合索引时,要考虑到复用能力,能用联合索引就用联合索引,若必须要用age搜索,则单独给age加一个索引字段。
- 在覆盖索引中,最左边第一个索引元素一定能保证当前索引树中是有序的,如果不遵循最左前缀原则,索引是会失效的。若优化器能顺序优化对应的字段,比如age,name,优化成name,age,也是可以走索引的。
如果没有遵循最左前缀原则的数据, 会怎么处理呢?
select * from user where name like "peter%" and age=10 and position="dev";
当联合索引的第一个字段用了范围查询,即只会走第一个索引。
在无索引下推的版本,会查询到name=peter1,peter2,peter3然后一条一条的回表,在MySQL5.6版本之前,都是根据name like "peter%"条件一条一条的回表查询,然后判断对比,此时回表了三次。
索引下推:在索引遍历中,先对索引中所有的字段先进行判断,过滤掉不符合条件的数据,然后再进行回表,目的是为了减少回表的次数,此时回表了1次。
为什么like有索引下推,而范围查询没有?
个人理解:like已经帮我们的范围给缩小了,而MySQL会觉得范围查询的数据集太大了。
前缀索引
如果没有加索引,MySQL会走全表扫描,MySQL支持给字符串加前缀索引。
默认创建的字符串索引是没有指定长度的,默认包含整个字符串。
alter table user add index idx(name)
-- 若是peter,则索引树上的值是peter
alter table user add index idx(name(3))
-- 若是pter,则索引树上的值是pet
优点:字符串索引使用索引前缀,可以在索引树上存储更多的数据,因为前缀索引占用内存会相当于小,但感觉没有什么实际用处。
缺点:增加回表的次数,使用前缀索引无法用到覆盖索引对查询的优化功能。