建设网站的目的及功能定位,wordpress 分类链接,应用商店下载安装电脑,洛阳有做网站开发的吗谣言
互联网上有传言说我们应该避免在单个 MySQL 表中有超过 2000 万行。否则#xff0c;表的性能会下降#xff0c;当它超过软限制时#xff0c;你会发现 SQL 查询比平时慢得多。这些判断是在多年前使用HDD硬盘存储时做出的。我想知道在2023年对于基于SSD的MySQL数据库来说… 谣言
互联网上有传言说我们应该避免在单个 MySQL 表中有超过 2000 万行。否则表的性能会下降当它超过软限制时你会发现 SQL 查询比平时慢得多。这些判断是在多年前使用HDD硬盘存储时做出的。我想知道在2023年对于基于SSD的MySQL数据库来说这是否仍然成立如果成立原因是什么 环境
· 数据库 MySQL 版本8.0.25 实例类型AWS db.r5.large (2vCPUs, 16GiB RAM) EBS 存储类型通用 SSD (gp2)· 测试客户端 Linux 内核版本6.1 实例类型AWS t2.micro (1 vCPU1GiB 内存
实验设计
创建具有相同模式但大小不同的表。我创建了9个表分别包含10万、20万、50万、100万、200万、500万、1000万、2000万、3000万、5000万和6000万行。
1.创建几个具有相同模式的表
CREATE TABLE row_test(id int NOT NULL AUTO_INCREMENT,person_id int NOT NULL,person_name VARCHAR(200),insert_time int,update_time int,PRIMARY KEY (id),KEY query_by_update_time (update_time),KEY query_by_insert_time (insert_time)
);2. 插入不同行的表格。我使用测试客户端和复制来创建这些表。脚本可以在这里找到。
# test client
INSERT INTO {table} (person_id, person_name, insert_time, update_time) VALUES ({person_id}, {person_name}, {insert_time}, {update_time})# copy
create table new-table like table
insert into (person_id, person_name, insert_time, update_time)
select person_id, person_name, insert_time, update_time fromperson_id、person_name、insert_time 和 update_time 的值是随机的。
3.使用测试客户端执行以下sql查询来测试性能。脚本可以在这里找到。
select count(*) from table -- full table scan
select count(*) from table where id 12345 -- query by primary key
select count(*) from table where insert_time 12345 -- query by index
select * from table where insert_time 12345 -- query by index, but cause 2-times index tree lookup4.查看innodb缓冲池状态
SHOW ENGINE INNODB STATUS
SHOW STATUS LIKE innodb_buffer_pool_page%5.每次在表上测试完一定要重启数据库刷新 innodb 缓冲池以避免读取旧缓存并得到错误结果 结果
查询 1select count(*) from table 这种查询会造成全表扫描这是MySQL不擅长的。
No-cache round第一轮当缓冲池中没有缓存数据时第一次执行查询。 Cache roundOther round当缓冲池中已经有数据缓存时执行查询通常在第一次执行之后。
几个观察
1.第一次执行的查询运行时间比后面的要长 原因是MySQL使用了innodb_buffer_pool来缓存数据页。在第一次执行之前缓冲池是空的它必须进行大量的磁盘 I/O 才能从 .idb 文件加载表。但第一次执行后数据存储在缓冲池中后续执行可以通过内存计算得到结果避免磁盘I/O速度更快。该过程称为MySQL 缓冲池预热。
2.select count(*) from table将尝试将整个表加载到缓冲池 我比较了实验前后的 innodb_buffer_pool 统计数据。运行查询后如果缓冲池足够大缓冲池使用变化等于表大小。否则只有部分表会缓存在缓冲池中。原因是查询select count(*) from table会做全表扫描一行一行地统计行数。如果没有缓存这需要将完整表加载到内存中。为什么因为 Innodb 支持事务它不能保证事务在不同时间看到同一张表。全表扫描是获得准确行数的唯一安全方法。
3.如果缓冲池不能容纳全表查询延迟会爆发 我注意到 innodb_buffer_pool 大小会对查询性能产生很大影响因此我尝试在不同的配置下运行查询。当使用 11G 的缓冲池时查询延迟的突增发生在表大小达到 50M 时。然后将缓冲池大小减小为 7G查询延迟的突增发生在表大小为 30M 时。最后将缓冲池大小减小到 3G查询运行时间的突增发生在表大小为 20M 时。很明显如果表中的数据无法被缓存到缓冲池中执行select count(*) from table就需要进行昂贵的磁盘 I/O 操作来加载数据从而导致查询运行时间的突增。
4. 在不缓存的情况下查询运行时间与表大小呈线性关系与缓冲池大小无关。 无缓存循环运行时间由磁盘 I/O 决定与缓冲池大小无关。select count(*)使用相同 IOPS 的存储磁盘预热缓冲池没有区别。
5. 如果表无法完全缓存在缓冲池中那么无缓存轮和有缓存轮之间的查询运行时间差是恒定的。
同时注意到尽管如果表无法完全缓存在缓冲池中会导致查询运行时间的突增但运行时间是可预测的。无缓存轮运行时间和有缓存轮运行时间之间的差值是恒定的无论表的大小如何。原因是表的部分数据被缓存在缓冲池中这个差值表示了从缓冲池而不是磁盘进行查询所节省的时间。
查询 2、3select count(*) from table where index_column 12345 此查询使用索引。由于不是范围查询只需要在B树的路径中从上到下查找页面并将这些页面缓存到innodb缓冲池中即可。
我创建的表的 B 树的深度都是 3导致 3-4 次 I/O 来预热缓冲区平均耗时 4-6ms。之后如果我再次运行相同的查询它会直接从内存中查找结果即 0.5ms等于网络 RTT。如果缓存页面长时间未命中并从缓冲池中逐出则必须再次从磁盘加载该页面这最多需要 4 次磁盘 I/O。
查询 4select * from table where index_column 12345 此查询导致 2 次索引查找。由于select *需要查询获取不包含在索引中的person_name, person_id因此在查询执行期间数据库引擎必须查找 2 个 B 树。它首先查找insert_timeB 树以获取目标行的主键然后查找主键 B 树以获取该行的完整数据如下图所示 这就是我们在生产中应该避免的原因select *。并且在实验中数据证实此查询加载的页面块比查询 2 或 3 多 2 倍最多为 8。平均查询运行时间为 6-10 毫秒也是查询 2 或 3 的 1.5 到 2 倍。 谣言是怎么来的 首先我们需要知道innodb索引页的物理结构。默认页面大小为 16k由页眉、系统记录、用户记录、页面导向器和尾部组成。将只剩下 15-14k 来存储免费数据。
假设您使用 INT 作为主键4 字节每行 1KB 的有效负载。每个叶页可以存储 15 行它将是 4812 字节使其成为指向该页的指针。因此每个非叶页最多可以容纳 15k / 12 字节 1280 个指针。如果你有一个 4 层的 B 树它最多可以容纳 1280*1280*15 24.6M 行数据。
回到 HDD 占据市场主导地位且 SSD 对于数据库而言过于昂贵的时代4 次随机 I/O 可能是我们可以容忍的最坏情况而使用 2 次索引树查找的查询甚至会使情况变得更糟。当时的工程师想要控制索引树的深度不希望它们长得太深。现在SSD越来越流行随机I/O比以前便宜了我们可以回顾一下10年前的规则。
顺便说一句5层B树可以容纳1280*1280*1280*15 31.4B行数据超过了INT所能容纳的最大数量。对每行大小的不同假设将导致不同的软限制小于或大于 20M。例如在我的实验中每行大约 816 字节我使用utf8mb4字符集所以每个字符占用 4 个字节4 层 B 树可以容纳的软限制是 29.5M。 结论 Innodb_buffer_pool 大小/表大小决定是否会出现性能下降。 一个更有意义的指标来判断是否需要拆分MySQL表是查询运行时间与缓冲池命中率的比值。如果查询总是命中缓冲池就不会有性能问题。2000万行只是基于经验的一个值。 除了拆表增加InnoDB缓冲池大小或数据库内存也是一个选择。 在生产环境中如果可能的话尽量避免使用select *因为在最坏的情况下会导致索引树的两次查找。 个人观点考虑到SSD现在的普及2000万行并不是MySQL表的一个非常有效的软限制。 来源Yishengs blog
更多技术干货请关注公号“云原生数据库”
squids.cn基于公有云基础资源提供云上 RDS云备份云迁移SQL 窗口门户企业功能
帮助企业快速构建云上数据库融合生态。
