MYSQL单表数据量达到多少时性能会严重下降的问题探讨!

这篇具有很好参考价值的文章主要介绍了MYSQL单表数据量达到多少时性能会严重下降的问题探讨!。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

不知从什么时候开始,有着MySQL单表数据量超过2000万性能急剧下降的说法。

在中国互联网技术圈流传着这么一个说法:MySQL 单表数据量大于 2000 万行,性能会明显下降。事实上,这个传闻据说最早起源于百度。具体情况大概是这样的,当年的 DBA 测试 MySQL性能时发现,当单表的量在 2000 万行量级的时候,SQL 操作的性能急剧下降,因此,结论由此而来。然后又据说百度的工程师流动到业界的其它公司,随之也带去了这个信息,所以,就在业界流传开这么一个说法。

再后来,阿里巴巴《Java 开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB,才推荐进行分库分表。对此,有阿里的黄金铁律支撑,所以,很多人设计大数据存储时,多会以此为标准,进行分表操作。

那这个说法到底有没有理论依据的支撑呢,首先这个性能问题可以说有很多因素组成,比如说硬件以及MYSQL本身的参数设置等等。那今天我们可以从MySQL的索引结构来说说这件事。

凡是有一点MYSQL基础的同学,都知道MYSQL采用B+树作为其索引结构。
mysql 单表性能,mysql,mysql,单表数据量,性能下降
一个问题?
InnoDB一棵B+树可以存放多少行数据?这个问题的简单回答是:约2千万。为什么是这么多呢?因为这是可以算出来的,要搞清楚这个问题,我们先从InnoDB索引数据结构、数据组织方式说起。

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

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

文件系统中一个文件大小只有1个字节,但不得不占磁盘上4KB的空间。
mysql 单表性能,mysql,mysql,单表数据量,性能下降

innodb的所有数据文件(后缀为ibd的文件),他的大小始终都是16384(16k)的整数倍。
mysql 单表性能,mysql,mysql,单表数据量,性能下降

磁盘扇区、文件系统、InnoDB存储引擎都有各自的最小存储单元。
mysql 单表性能,mysql,mysql,单表数据量,性能下降
在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+树的方式组织这些数据。如图所示:
mysql 单表性能,mysql,mysql,单表数据量,性能下降
我们先将数据记录按主键进行排序,分别存放在不同的页中(为了便于理解我们这里一个页中只存放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的记录:
mysql 单表性能,mysql,mysql,单表数据量,性能下降
现在我们清楚了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 b_name,a.NAME 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;

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

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

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

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

接下来我们用hexdump工具,查看表空间文件指定偏移量上的数据:
mysql 单表性能,mysql,mysql,单表数据量,性能下降
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;

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

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

最后来思考一下这个问题:

为什么MySQL的索引要使用B+树而不是其它树形结构?比如B树?

答案是:
因为B树不管叶子节点还是非叶子节点,都会保存数据,这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出),指针少的情况下要保存大量数据,只能增加树的高度,导致IO操作变多,查询性能变低;文章来源地址https://www.toymoban.com/news/detail-703992.html

到了这里,关于MYSQL单表数据量达到多少时性能会严重下降的问题探讨!的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 雅思单词量要达到多少才能达到要求?

    对于刚开始备考雅思的同学来说,雅思单词是考好雅思的基础,雅思单词量要达到多少才能过关呢?下面为大家介绍一下雅思词汇量的要求。 一、雅思词汇量要求 雅思听力词汇 3000~5000左右,雅思听力是以场景展开的,比较高频的场景有租房、看病、旅行、讨论作业等,这

    2024年02月13日
    浏览(43)
  • MySQL运行在docker容器中会损失多少性能

    自从使用docker以来,就经常听说MySQL数据库最好别运行在容器中,性能会损失很多。一些之前没使用过容器的同事,对数据库运行在容器中也是忌讳莫深,甚至只要数据库跑在容器中出现性能问题时,首先就把问题推到容器上。 那么到底会损失多少,性能损失会很多吗? 为此

    2024年02月05日
    浏览(48)
  • 电脑发热发烫,具体硬件温度达到多少度才算异常?

    联想E14 电脑发热发烫,具体硬件温度达到多少度才算异常? 电脑硬件的温度正常范围会因设备类型和使用的具体硬件而有所不同。一般来说,以下是各种硬件的正常温度范围: CPU:正常情况下,CPU的温度应该在45-75度之间,或者更低。如果CPU温度过高大于等于85度以上,长时间

    2024年02月03日
    浏览(40)
  • 软件测试岗位,真正达到月收入1万以上的有多少?

    软件测试工程师工资有多高?我想说拿4k-30k的都有,因为软件测试工程师的薪资水平是受各种因素影响的,比如:所在城市、个人工作能力、技能掌握情况、工作年限等等都有很大关系,这是要根据每个人的具体情况而定的。 记得在某一线城市有位阿姨给自己是程序员的孩子

    2023年04月19日
    浏览(42)
  • 测试数据库性能的工具知多少

    有许多测试数据库性能的工具,以下是一些常用的工具: sysbench : 这是一个跨平台的基准测试工具,可以测试多种系统资源,包括CPU,内存,IO,和并发性。对于数据库测试,sysbench可以用来测试MySQL的性能。 LoadRunner : 这是一个商业性能测试工具,可以模拟数千个用户并发负

    2024年02月11日
    浏览(43)
  • 解决element ui大数据渲染表格时严重卡顿的问题

    element ui中的table表格组件在大量数据渲染的时候渲染会出现严重的卡顿问题,虽然网上也有很多解决方案,比如什么延迟加载,虚拟加载等等,这里为了避免走弯路,直接上终极解决方案大招:趁早换组件、马上换组件、现在就去换组件!!! 推荐组件:umy-ui 官方文档:u

    2024年03月17日
    浏览(54)
  • 三个主流数据库(Oracle、MySQL和SQL Server)的“单表造数

    预设游标10000(必要时)  模拟新增10万条数据    1.1 oracle一条sql数据生成100万条数据   创建测试表 创建测试表 模拟新增3万条数据 开发过程中经常需要测试 SQL 在大量数据集时候的执行效率,这就需要我们在表中插入大量的测试数据,下面介绍如何使用存储过程插入大量的

    2024年02月14日
    浏览(55)
  • 微信小程序中使用uChart更新数据时,图表抖动严重的问题。

    使用前须知: 你已经引入了ucharts 你已经使用ucharts的“演示-微信”部分的代码 直接上解决方法: 当 update: true 时,即你使用 chartData 更新的数据不会使界面重新渲染,只会更新数据 当 update: false 或不传时,即你使用 chartData 更新的数据会让界面重新渲染 思路: 在开发微信小

    2024年02月15日
    浏览(38)
  • 【从删库到跑路】MySQL数据库的查询(单表查询,多表查询,内外连接,联合查询,子查询)

    🎊专栏【MySQL】 🍔喜欢的诗句:更喜岷山千里雪 三军过后尽开颜。 🎆音乐分享【如愿】 大一同学小吉,欢迎并且感谢大家指出我的问题🥰 在项目开发中,在进行数据库表结构设计时,会根据业务需求以及业务模块之间的关系,分析并设计表结构,由于业务之间相互关联

    2024年02月10日
    浏览(48)
  • 通信信道带宽为1Gb/s,端到端时延为10ms。TCP的发送窗口为65535字节。试问:可能达到的最大吞吐量是多少?信道的利用率是多少?

    在Bing和CSDN上转了一圈,答案千奇百怪。很多只给计算,不给解释,对新手不太好理解。本答案参考了网上已有的几乎所有答案,补充了一些基本概念和公式,零基础友好,希望对各位有帮助。 首先,一些基本的概念:  1.通信信道带宽: 即 理论上 信道能够达到的 最大数据

    2024年02月03日
    浏览(53)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包