【群友的瓜】二级索引为什么不存储主键索引的物理地址

【群友的瓜】二级索引为什么不存储主键索引的物理地址

Scroll Down

前言

想起以前在某些群里,一些群友的思路、脑洞比较新奇,偶尔会问出一些让人感到惊讶的问题,对于某些问题,我是有做记录的(遗憾的是没有全记录下来)。

PS:如果我没有特地指明是哪个存储引擎,那就默认是InnoDB。

这里就算开一个坑,讲解下我还记得或者做了记录的一些问题。

另外我本是想直接发聊天记录的截图的,但由于有些问题,提问者花了一定时间才讲解清楚,文字有点长篇大论,所以我就以我自己的简介去简化这些文字,希望不会妨碍到各位理解。

群友的瓜

Q:二级索引为什么不存储主键索引的物理地址?直接根据物理地址定位不就能节省回表的定位成本了吗?

对于这个问题,我看到的第一感受,就是不得不佩服这位群友思路的新奇。
先抛开其它种种因素不谈,我脑海里简单的理了一下思路:

回表之所以成本高,原因就在于每次从二级索引定位到记录以后(假如没有索引覆盖/索引下推),都要重新到聚簇索引根据主键去查找完整记录。

对于一条批量查询的sql而言,如果每一次查询都涉及回表,那么【二级索引定位】->【二级获取的主键id进行聚簇索引定位】->【下一条二级索引定位】->【下一条二级获取的主键id进行聚簇索引定位】-> ...这个一来一回的过程其实就是随机I/O,效率低下。

而如果按照二级索引存储的是聚簇索引记录的物理地址,那就不会产生回表操作了,也就是不用每次获取到主键id之后都要回聚簇索引重新定位完整记录的具体位置;取而代之的是,直接根据物理地址就能定位到记录的具体位置。

如果单纯这么想想,好像这位群友说得确实很有道理。
不过这个想法很快就被其它群友反驳了,其实只要对计算机或MYSQL的其它机制有所了解,很快就能发现这个想法的一些漏洞。

群友关于此问题的解答

  • 我的观点(数据迁移角度):
    • 其实不难想象,我们线上的数据库是肯定要备份,即便不需要备份,哪天数据库需要迁移了,也是需要导出/入库的。
    • 那么问题就来了,如果二级索引存储的是物理地址,那么当数据发生迁移的时候,原来的物理地址到新的环境中就基本gg了,所以该想法不可取。
  • 群友A(页变化角度):
    • 删除数据角度:虽然绝大部分情况下,企业都不会对数据进行物理删除,但并不是100%的企业都这样做。无法避免一些情况要进行物理删除,而物理删除可能会触发页合并,页合并会导致物理地址产生变化。
    • 插入数据角度:当插入一定数据以后,会产生页分裂,页分裂同样会导致物理地址产生变化。
  • 群友B(加锁角度):这群友也是真的牛逼
    • 很简单,就是无法给物理地址加行级锁。
    • 尤其是做插入和更新的时候。因为无论是插入或更新时:数据会经历redo日志、缓冲池(update是buffer pool,insert则是另一个缓冲区)等步骤才得以落盘,而在落盘前,都无法保证二级索引记录的物理地址是否会被其它东西占用。

补充

虽然上述说的直接用物理地址是不行的,但实际上我们可以间接利用,比如MyISAM就是利用文件的物理地址偏移量来回表的,以后有空会补充这一块的讲解。

PS

这个系列里面讲得其实不一定对,但是觉得稍微能开拓下新的思路,就记录下来了,不喜勿喷。

如果您有什么新的想法,亦或是纠正的地方,欢迎在评论区提出,我们可以一起讨论这些问题,进一步提升博客质量。