热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

【精华】mysql到底能够存储多少数据

一个问题?InnoDB一棵B+树可以存放多少行数据?这个问题的简单回答是:约2千万。为什么是这么多呢?因为这是可以算出来的,要搞清楚这个问题,我们先从InnoDB索引数据结构、数据

InnoDB一棵B+树可以存放多少行数据?这个问题的简单回答是:约2千万。为什么是这么多呢?因为这是可以算出来的,要搞清楚这个问题,我们先从InnoDB索引数据结构、数据组织方式说起。

我们都知道计算机在存储数据的时候,有最小存储单元,这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区,一个扇区的大小是512字节,而文件系统(例如XFS/EXT4)他的最小单元是块,一个块的大小是4k,而对于我们的InnoDB存储引擎也有自己的最小储存单元——页(Page),一个页的大小是16K。

下面几张图可以帮你理解最小存储单元:

文件系统中一个文件大小只有1个字节,但不得不占磁盘上4KB的空间。

 

innodb的所有数据文件(后缀为ibd的文件),他的大小始终都是16384(16k)的整数倍。

 

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。

 

在MySQL中我们的InnoDB页的大小默认是16k,当然也可以通过参数设置:

  •  
mysql> show variables like "innodb_page_size";+------------------+-------+| Variable_name  | Value |+------------------+-------+| innodb_page_size | 16384 |+------------------+-------+1 row in set (0.00 sec)

数据表中的数据都是存储在页中的,所以一个页中能存储多少行数据呢?假设一行数据的大小是1k,那么一个页可以存放16行这样的数据。

如果数据库只按这样的方式存储,那么如何查找数据就成为一个问题,因为我们不知道要查找的数据存在哪个页中,也不可能把所有的页遍历一遍,那样太慢了。所以人们想了一个办法,用B+树的方式组织这些数据。如图所示:

 

我们先将数据记录按主键进行排序,分别存放在不同的页中(为了便于理解我们这里一个页中只存放3条记录,实际情况可以存放很多),除了存放数据的页以外,还有存放键值+指针的页,如图中page number=3的页,该页存放键值和指向数据页的指针,这样的页由N个键值+指针组成。当然它也是排好序的。这样的数据组织形式,我们称为索引组织表。现在来看下,要查找一条数据,怎么查?

  •  
select * from user where id=5;

这里id是主键,我们通过这棵B+树来查找,首先找到根页,你怎么知道user表的根页在哪呢?其实每张表的根页位置在表空间文件中是固定的,即page number=3的页(这点我们下文还会进一步证明),找到根页后通过二分查找法,定位到id=5的数据应该在指针P5指向的页中,那么进一步去page number=5的页中查找,同样通过二分查询法即可找到id=5的记录:

5 zhao2 27

现在我们清楚了InnoDB中主键索引B+树是如何组织数据、查询数据的,我们总结一下:

1、InnoDB存储引擎的最小存储单元是页,页可以用于存放数据也可以用于存放键值+指针,在B+树中叶子节点存放数据,非叶子节点存放键值+指针。

2、索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中,进而在去数据页中查找到需要的数据;

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

这里我们先假设B+树高为2,即存在一个根节点和若干个叶子节点,那么这棵B+树的存放总记录数为:根节点指针数*单个叶子节点记录行数。

上文我们已经说明单个叶子节点(页)中的记录数=16K/1K=16。(这里假设一行记录的数据大小为1k,实际上现在很多互联网业务数据记录大小通常就是1K左右)。

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

根据同样的原理我们可以算出一个高度为3的B+树可以存放:1170117016=21902400条这样的记录。所以在InnoDB中B+树高度一般为1-3层,它就能满足千万级的数据存储。在查找数据时一次页的查找代表一次IO,所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

怎么得到InnoDB主键索引B+树的高度?

上面我们通过推断得出B+树的高度通常是1-3,下面我们从另外一个侧面证明这个结论。在InnoDB的表空间文件中,约定page number为3的代表主键索引的根页,而在根页偏移量为64的地方存放了该B+树的page level。如果page level为1,树高为2,page level为2,则树高为3。即B+树的高度=page level+1;下面我们将从实际环境中尝试找到这个page level。

在实际操作之前,你可以通过InnoDB元数据表确认主键索引根页的page number为3,你也可以从《InnoDB存储引擎》这本书中得到确认。

  •  
SELECT [b.name](http://b.name/), [a.name](http://a.name/), index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0;

执行结果:

可以看出数据库dbt3下的customer表、lineitem表主键索引根页的page number均为3,而其他的二级索引page number为4。关于二级索引与主键索引的区别请参考MySQL相关书籍,本文不在此介绍。

下面我们对数据库表空间文件做相关的解析:

 

因为主键索引B+树的根页在整个表空间文件中的第3个页开始,所以可以算出它在文件中的偏移量:16384*3=49152(16384为页大小)。

另外根据《InnoDB存储引擎》中描述在根页的64偏移量位置前2个字节,保存了page level的值,因此我们想要的page level的值在整个文件中的偏移量为:16384*3+64=49152+64=49216,前2个字节中。

接下来我们用hexdump工具,查看表空间文件指定偏移量上的数据:

 

linetem表的page level为2,B+树高度为page level+1=3;

region表的page level为0,B+树高度为page level+1=1;

customer表的page level为2,B+树高度为page level+1=3;

这三张表的数据量如下:

 

总结:

lineitem表的数据行数为600多万,B+树高度为3,customer表数据行数只有15万,B+树高度也为3。可以看出尽管数据量差异较大,这两个表树的高度都是3,换句话说这两个表通过索引查询效率并没有太大差异,因为都只需要做3次IO。那么如果有一张表行数是一千万,那么他的B+树高度依旧是3,查询效率仍然不会相差太大。

region表只有5行数据,当然他的B+树高度为1。

最后回顾一道面试题

有一道MySQL的面试题,为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树?

现在这个问题的复杂版本可以参考本文;

他的简单版本回答是:

因为B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;

小结

本文从一个问题出发,逐步介绍了InnoDB索引组织表的原理、查询方式,并结合已有知识,回答该问题,结合实践来证明。当然为了表述简单易懂,文中忽略了一些细枝末节,比如一个页中不可能所有空间都用于存放数据,它还会存放一些少量的其他字段比如page level,index number等等,另外还有页的填充因子也导致一个页不可能全部用于保存数据。关于二级索引数据存取方式可以参考MySQL相关书籍,他的要点是结合主键索引进行回表查询。

参考资料:

姜承尧 《MySQL技术内幕:InnoDB存储引擎》

姜承尧 http://www.innomysql.com/查看-innodb表中每个的索引高度


推荐阅读
  • 在关系型数据库中,数据约束是指在向数据表中插入数据时必须遵循的限制条件。在MySQL和MariaDB中,常见的数据约束包括主键约束、唯一键约束、外键约束以及非空约束等。这些约束确保了数据的完整性和一致性,是数据库管理中的重要组成部分。通过合理设置和使用这些约束,可以有效防止数据冗余和错误,提升数据库的可靠性和性能。 ... [详细]
  • 针对MySQL Undo空间满载及Oracle Undo表空间溢出的问题,本文详细探讨了其原因与解决策略。首先,通过启动SQL*Plus并以SYS用户身份登录数据库,查询当前数据库的UNDO表空间名称,确认当前状态。接着,分析导致Undo空间满载的常见原因,如长时间运行的事务、频繁的更新操作等,并提出相应的解决方案,包括调整Undo表空间大小、优化事务管理、定期清理历史数据等。最后,结合实际案例,提供具体的实施步骤和注意事项,帮助DBA有效应对这些问题。 ... [详细]
  • 本文详细介绍了使用 Python 进行 MySQL 和 Redis 数据库操作的实战技巧。首先,针对 MySQL 数据库,通过 `pymysql` 模块展示了如何连接和操作数据库,包括建立连接、执行查询和更新等常见操作。接着,文章深入探讨了 Redis 的基本命令和高级功能,如键值存储、列表操作和事务处理。此外,还提供了多个实际案例,帮助读者更好地理解和应用这些技术。 ... [详细]
  • 如何使用mysql_nd:Python连接MySQL数据库的优雅指南
    无论是进行机器学习、Web开发还是爬虫项目,数据库操作都是必不可少的一环。本文将详细介绍如何使用Python通过 `mysql_nd` 库与 MySQL 数据库进行高效连接和数据交互。内容涵盖以下几个方面: ... [详细]
  • 本文详细探讨了MySQL数据库实例化参数的优化方法及其在实例查询中的应用。通过具体的源代码示例,介绍了如何高效地配置和查询MySQL实例,为开发者提供了有价值的参考和实践指导。 ... [详细]
  • MyISAM和InnoDB是MySQL中最为广泛使用的两种存储引擎,每种引擎都有其独特的优势和适用场景。MyISAM引擎以其简单的结构和高效的读取速度著称,适用于以读操作为主、对事务支持要求不高的应用。而InnoDB引擎则以其强大的事务处理能力和行级锁定机制,在需要高并发写操作和数据完整性的场景下表现出色。选择合适的存储引擎应综合考虑业务需求、性能要求和数据一致性等因素。 ... [详细]
  • 阿里云MySQL与Oracle数据库的主从复制技术详解 ... [详细]
  • 本文探讨了如何在C#应用程序中通过选择ComboBox项从MySQL数据库中检索数据值。具体介绍了在事件处理方法 `comboBox2_SelectedIndexChanged` 中可能出现的常见错误,并提供了详细的解决方案和优化建议,以确保数据能够正确且高效地从数据库中读取并显示在界面上。此外,还讨论了连接字符串的配置、SQL查询语句的编写以及异常处理的最佳实践,帮助开发者避免常见的陷阱并提高代码的健壮性。 ... [详细]
  • 在Android应用开发中,实现与MySQL数据库的连接是一项重要的技术任务。本文详细介绍了Android连接MySQL数据库的操作流程和技术要点。首先,Android平台提供了SQLiteOpenHelper类作为数据库辅助工具,用于创建或打开数据库。开发者可以通过继承并扩展该类,实现对数据库的初始化和版本管理。此外,文章还探讨了使用第三方库如Retrofit或Volley进行网络请求,以及如何通过JSON格式交换数据,确保与MySQL服务器的高效通信。 ... [详细]
  • Objective-C 中的委托模式详解与应用 ... [详细]
  • 数据库多表联合查询:内连接与外连接详解
    在数据库的多表查询中,内连接和外连接是两种常用的技术手段。内连接用于检索多个表中相互匹配的记录,即只有当两个表中的记录满足特定的连接条件时,这些记录才会被包含在查询结果中。相比之下,外连接则不仅返回匹配的记录,还可以选择性地返回不匹配的记录,具体取决于左外连接、右外连接或全外连接的选择。本文将详细解析这两种连接方式的使用场景及其语法结构,帮助读者更好地理解和应用多表查询技术。 ... [详细]
  • 作为软件工程专业的学生,我深知课堂上教师讲解速度之快,很多时候需要课后自行消化和巩固。因此,撰写这篇Java Web开发入门教程,旨在帮助初学者更好地理解和掌握基础知识。通过详细记录学习过程,希望能为更多像我一样在基础方面还有待提升的学员提供有益的参考。 ... [详细]
  • 本文详细介绍了如何安全地手动卸载Exchange Server 2003,以确保系统的稳定性和数据的完整性。根据微软官方支持文档(https://support.microsoft.com/kb833396/zh-cn),在进行卸载操作前,需要特别注意备份重要数据,并遵循一系列严格的步骤,以避免对现有网络环境造成不利影响。此外,文章还提供了详细的故障排除指南,帮助管理员在遇到问题时能够迅速解决,确保整个卸载过程顺利进行。 ... [详细]
  • 在Ubuntu系统中配置Python环境变量是确保项目顺利运行的关键步骤。本文介绍了如何将Windows上的Django项目迁移到Ubuntu,并解决因虚拟环境导致的模块缺失问题。通过详细的操作指南,帮助读者正确配置虚拟环境,确保所有第三方库都能被正确识别和使用。此外,还提供了一些实用的技巧,如如何检查环境变量配置是否正确,以及如何在多个虚拟环境之间切换。 ... [详细]
  • 本文介绍了一种利用Dom4j库和JFileChooser组件在Java中实现XML文件自定义路径导出的方法。通过创建一个Document对象并设置根元素,结合JFileChooser选择目标路径,实现了灵活的XML文件导出功能。具体步骤包括初始化Document对象、构建XML结构以及使用JFileChooser选择保存路径,确保用户能够方便地将生成的XML文件保存到指定位置。 ... [详细]
author-avatar
广大华软11级软测1班支部
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有