MySQL一次查询需要几次io

qq_35640866

已于 2023-11-21 14:47:43 修改

阅读量488

点赞数

文章标签： mysql 数据库

于 2023-11-21 14:47:14 首次发布

本文链接：https://blog.csdn.net/qq_35640866/article/details/134530708

版权

本文详细解释了InnoDB存储引擎中主键索引B+树的数据组织方式，包括页结构、叶子节点和非叶子节点的角色，以及如何通过B+树的高度计算存储和查询性能。重点讨论了不同B+树高度对数据存储量的影响以及查询效率.

摘要由CSDN通过智能技术生成

InnoDB中主键索引B+树是如何组织数据、查询数据的，我们总结一下：

InnoDB存储引擎的最小存储单元是页，页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。
索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中，进而在去数据页中查找到需要的数据；

那么回到我们开始的问题，通常一棵B+树可以存放多少行数据？

这里我们先假设B+树高为2，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数*单个叶子节点记录行数。
一个页大小为16KB
上文我们已经说明单个叶子节点（页）中的记录数=16K/1K=16。（这里假设一行记录的数据大小为1k，实际上现在很多互联网业务数据记录大小通常就是1K左右）。

那么现在我们需要计算出非叶子节点能存放多少指针，其实这也很好算，我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。那么可以算出一棵高度为2的B+树，能存放1170*16=18720条这样的数据记录。

根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170x1170x16=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。
高度为4的B+树可以存储 1170x1170x1170x16 = 25625808000 （256亿）
假设1行数据还是1k大小。

如果如果没开启innodb_large_prefix 索引最大是767 bytes 字节
如果开启最大3072bytes 字节。
如果一个索引 767 加上一个指针6字节一个page 可以存储 16384/(676+6) = 约24
三层树 242416 = 9216
四层树 24242416 = 221184 20万
五层 2424242416 = 5308416 5百万
六层 242424242416 =127401984 1.2亿

如果一个索引 3072bytes 加上一个指针6字节一个page 可以存储 16384/(3072+6) = 约5

三层树 5516 = 400
四层树 5*5*5*16 = 2000
五层树 5*5*5*5*16=10000 1w
六层 5*5*5*5*5*16=50000 5w
七层 5*5*5*5*5*5*16 = 250000 25w
八层 5*5*5*5*5*5*5*16 = 1250000 1百万
九层 5*5*5*5*5*5*5*5*16 = 6250000 6百万
10层 5*5*5*5*5*5*5*5*\5*16 = 31250000 3千万
11层 5*5*5*5*5*5*5*5*\5*5*16 = 156250000 1.5亿

最坏的情况需要一层一次io，如果二级索引查询，还需要加上主键查询需要的层数。

参考：
https://mp.weixin.qq.com/s?__biz=MzA3OTc0MzY1Mg==&mid=2247530546&idx=3&sn=c23db123911cd3484d3941854058317c&chksm=9face7f9a8db6eefd46964555d487ca290a39b88e17ed8a35f2252b7a1c84a4d195df21c2954&mpshare=1&scene=24&srcid=0322y65a15eC2JWi7IJbQse5&sharer_sharetime=1647910635358&sharer_shareid=c10b839d8daacc6890cf00f30cce7c78&ascene=14&devicetype=android-31&version=28002be1&nettype=3gnet&abtest_cookie=AAACAA%3D%3D&lang=zh_CN&countrycode=CN&exportkey=n_ChQIAhIQkAX81Xr2l%2FpqiAzeUyDbsRLnAQIE97dBBAEAAAAAABWiOQ4oM6YAAAAOpnltbLcz9gKNyK89dVj0EQBHJCAPvPrQl4PI2tIB5j%2BTx67gMaOAbYKrfcNsy2DIZxhCelCTIED1wAC%2BokxKq0Ij8dx3IUYgByWGmyNCEuOhyP1fjvdnmrF0RVdf%2FD9EvrWSE7WXOUIGJk25oHEbXJtokdUWR6bEfKAH4SPOx7oH0E8Bn275q2vepiYH4YsD9acRREcQr1OaLY%2BIxMvjquz%2Bo88Mcl2TufZM1rMTlMwsk%2BmA%2FzIBS3rDip%2Bei5d2oDyXY2NSqI1wltFdS4mM7Q%3D%3D&pass_ticket=O%2F2gCbjxldM9%2F05DbpNfXM%2BuTDeCcChBsuieDqRPrm2ytvbLiRpCet1JGNIoKdd7Fpxcvz8SrMWlArUG4V56jA%3D%3D&wx_header=3