注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

堆表(HOT)和索引组织表(IOT)的区别

MySQL InnoDB 彭东稳 1333次浏览 已收录 0个评论

一、HOT和IOT的起源

堆组织表的存储速度因为不用考虑排序,所以存储速度会比较快。但是要查找符合某个条件的记录,就必须得读取全部的记录以便筛选。而这个时候为了加快查询速度,索引就出现了,索引是针对少量特定字段的值拿出来进行排序存储,并记录在表中的位置,而因为索引是有序的,所以就会很容易通过索引查询到具体的记录位置(普遍使用二分查找法),然后再根据记录位置直接从表中读取该记录。同时因为索引的字段较少,所以索引通常会比其基表小得多。

从上面通过索引访问表记录的方式可以看出,当要访问的数据量较大时,通过每一条记录的位置去访问原始记录,每一条符合条件的记录都需要经过索引访问后再访问基表这样一个复杂的过程,这会花费很多时间。同样,如果不经过索引而直接查询表,也可能因为表字段太多,记录较大的情况下把全部的数据读取进来,这也会花费很多时间。

那怎么办呢?这个时候就会想到,如果表中数据本身就是有序的,这样查询表的时候就可以快速的找到符合条件的记录位置,而很容易判断符合条件记录的位置,这样只需要读取一小部分数据出来就可以了,不需要全表记录都读取出来进行判断。索引组织表就这样产生了,当然索引表中插入,更新的时候可能会因为需要排序而将数据重组,这时候数据插入或更新速度会比堆组织表慢一些。如果堆组织表上有索引,那么对堆组织表的插入也会因为要修改索引而变慢。

二、HOT和IOT的比较

所以,堆表的特点就是索引和数据分开,所有索引都是二级索引,或叫辅助索引。所以主键索引也是二级索引,没有完整记录,区别只有唯一或非唯一。索引中存储的是key与指针,指针指向具体数据记录。当然,查找key的算法都是一样的,使用二分查找,也叫书签查找。

跟索引组织表相比,堆表有什么好处呢?其实主要就是通过主键或二级索引查询,开销是一样的。都是通过先找到key,然后定位到数据。而索引组织表,由于二级索引是指向主键,所以查询二级索引需要先定位到key,然后拿到主键id,还要根据主键id再次通过二分查找定位到真正的数据页。当然,索引组织表通过主键查询开销与堆是一样的。从索引组织表的工作方式可以看出,索引组织表必须要有主键,如果非显式创建,InnoDB存储引擎会默认创建一个ROWID当做主键;而堆表则无强制要求。

这就是经常有文章说MyISAM比InnoDB快的原因吧,但这个说法并不完全正确,索引组织表由于索引项和数据存储在一起,且InnoDB聚集索引各个叶子节点之间都是同过双向链表组织,且都是根据主键逻辑顺序存放,所以无论是基于主键的等值查询还是范围查询都能大大节省磁盘访问时间。特别对于范围查询,只需要定位到开始key的位置,就可以顺着这个位置扫描到结束key。如下SQL语句:

根据主键查时,假设主键b加树高度是3,每个叶子节点能存储100条记录,1000条记录需要10个页。那么这个查询只需要大概 2 + 9次io开销。

当然了,有序的代价也是很大的,涉及数据重组,也就是索引页分裂与均衡。另外,如果对于索引组织表范围查询走的是二级索引,那么开销是多大呢?

如下SQL语句,同样是查询二级索引1000条记录。

这里假设二级索引b加树高度也是3,假设每个页子节点能存储200条键值对,这里的开销就变成了3 + 4 + 3000次IO开销了(其中3是定位到开始值的数据页,4是扫描二级索引1000条数据需要扫描的页,3000 = 3*1000表示回表到主键1000次产生的IO)。这种方式也称之为回表,从二级索引回到主键。所以在这种情况下,InnoDB很多时候都选择了直接扫描主键,也就是全表扫描,其代价可能比回表开销更小,比如在表数据页小于回表所产生的io次数时,因为全表扫描每个页也就一次io而已。这在MySQL 5.6之前都是这么工作的,但在MySQL 5.6有了MRR技术后,这种情况就改善了很多。

MRR简单来说就是为每个线程开辟了一块内存空间,由read_rnd_buffer_size参数设置,默认32M,把二级索引查询结果放到这块空间,并且做排序,然后利用排序后的数据进行回表,就是顺序的了。如果mrr内存够大,那么最快可以一次回表就拿到所有数据。当然mrr优化主要是针对磁盘的,磁盘越慢效果越好。如果你的buffer pool可以装下所有表数据,那么MRR效果其实也不大了,但一般这种情况很少。

那么堆表的缺点是什么呢?

这里我们想一下,由于堆表所有索引key都是通过指针指向数据记录,如果这行记录发生了更新,并且不能原地更新,需要进行迁移,那么就会发生一个情况,堆表所有索引都需要修改,指向新的位置。而索引组织表就不需要这个开销,只有当主键发生更改才需要对应修改其他二级索引,通常主键也不会更新,所以这也就是索引组织表更新效率更高的由来。

但一般使用堆表的数据库都不会直接这么更新,代价太大了。而是会在数据页预留一些空间,当遇见不能原地更新的记录时,就会在这个页中新插入一条更新后的记录,然后在这个页中做一个指针把老记录指向新纪录,这样就不用更新索引信息了。当然,如果你这个页中没有空闲空间可以插入新的记录,那么还是需要做行迁移,然后更新所有索引。


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (0)or分享 (0)
关于作者:

您必须 登录 才能发表评论!