MySQL战记:Count( *)实现之谜与计数策略的选择

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 Tair(兼容Redis),内存型 2GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
简介: 本文深入探讨了MySQL中`count(*)`的不同实现方式,特别是MyISAM和InnoDB引擎的区别,以及各种计数方法的性能比较。同时,文章分析了使用缓存系统(如Redis)与数据库保存计数的优劣,并强调了在高并发场景下保持数据一致性的挑战。

在本篇文章中,我们将深度探讨这些问题,解析MySQL中count(*)的不同实现方式,比较各类计数方法的性能,以及讨论缓存系统与数据库在保存计数方面的优劣。希望你能在这个探索过程中收获启示和乐趣。

首先来看一下这张思维导图,对本文内容有个简单的认识。

count(*) 的实现方式

在不同的 MySQL 引擎中,count() 有不同的实现方式,这里讨论的是没有过滤条件的 count()。

  • MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高;如果加了 where 条件的话,MyISAM 表也是不能返回得这么快的。
  • 而 InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。

为什么 InnoDB 不跟 MyISAM 一样,也把数字存起来呢?

这是因为即使是在同一个时刻的多个查询,由于多版本并发控制(MVCC)的原因,InnoDB 表“应该返回多少行”也是不确定的。

如下案例所示,最后在同一时刻三个会话查询表t的总行数结果不同。

这和 InnoDB 的事务设计有关系,可重复读是它默认的隔离级别,在代码上就是通过多版本并发控制,也就是 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见,因此对于 count(*) 请求来说,InnoDB 只好把数据一行一行地读出依次判断,可见的行才能够用于计算“基于这个查询”的表的总行数。

虽然在 InnoDB 引擎中执行 count(*) 执行需要逐行读取,但是内部还是做了查询优化。 InnoDB 是索引组织表,主键索引树的叶子节点是数据,而二级索引树的叶子节点是主键值。所以,普通索引树比主键索引树小很多。对于 count(*) 这样的操作,遍历哪个索引树得到的结果逻辑上都是一样的。因此,MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一。

除了执行 count(*) 命令得到数据行数,我们还使用过 show table status  命令,该命令用于显示表中当前有多少行,但是需要注意的是,该命令得到的结果是通过采样来估算的,官方文档说误差可能达到 40% 到 50%。所以,show table status 命令显示的行数也不能直接使用。

总结

  • MyISAM 表虽然 count(*) 很快,但是不支持事务;
  • show table status 命令虽然返回很快,但是不准确;
  • InnoDB 表直接 count(*) 会遍历全表,虽然结果准确,但会导致性能问题。

不同的 count 用法

分析 count(*)、count(主键 id)、count(字段) 和 count(1) 等不同用法的性能,有哪些差别?

count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是 NULL,累计值就加 1,否则不加。最后返回累计值。

所以,count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数;而 count(字段),则表示返回满足条件的数据行里面,参数“字段”不为 NULL 的总个数。

对于 count(主键 id) 来说,InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。

count(主键 id) 不会走主键索引,因为普通索引树比主键索引树小很多。假设表中有多个普通索引树,则由优化器来决定走哪个索引。

对于 count(1) 来说,InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。

单看这两个用法的差别的话,你能对比出来,count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行,以及拷贝字段值的操作。

对于 count(字段) 来说:

  1. 如果这个“字段”是定义为 not null 的话,一行行地从记录里面读出这个字段,判断不能为 null,按行累加;
  2. 如果这个“字段”定义允许为 null,那么执行的时候,判断到有可能是 null,还要把值取出来再判断一下,不是 null 才累加。

count(字段) 需要查询出该字段值,只能通过聚簇索引树,所以效率最差。

但是 count(\*) 是例外,并不会把全部字段取出来,而是专门做了优化,不取值。count(*) 肯定不是 null,直接按行累加。

主键 ID肯定非空,为什么优化器不能像优化 count()那样优化count(主键ID) 呢?答案是没必要,不做重复优化,推荐使用 count()。

根据上述分析,按照效率排序的话,count(字段)<count(主键 id)<count(1)≈count(*),所以我建议你,尽量使用 count(*)

有些文章说 count() 性能差,用词不恰当,难道其他几种计数方式就不差了,注意是计数性能差,而不是count()差。关于计数性能差,可以增加缓存,比如说 redis缓存或者本地缓存,但是不能保证完全实时一致。

用缓存系统保存计数

对于更新很频繁的库来说,你可能会第一时间想到,用缓存系统来支持。

你可以用一个 Redis 服务来保存这个表的总行数。这个表每被插入一行 Redis 计数就加 1,每被删除一行 Redis 计数就减 1。这种方式下,读和更新操作都很快,但你再想一下这种方式存在什么问题吗?

没错,缓存系统可能会丢失更新。

Redis 的数据不能永久地留在内存里,所以你会找一个地方把这个值定期地持久化存储起来。但即使这样,仍然可能丢失更新。试想如果刚刚在数据表中插入了一行,Redis 中保存的值也加了 1,然后 Redis 异常重启了,重启后你要从存储 redis 数据的地方把这个值读回来,而刚刚加 1 的这个计数操作却丢失了。

当然了,这还是有解的。比如,Redis 异常重启以后,到数据库里面单独执行一次 count(*) 获取真实的行数,再把这个值写回到 Redis 里就可以了。异常重启毕竟不是经常出现的情况,这一次全表扫描的成本,还是可以接受的。

但实际上,将计数保存在缓存系统中的方式,还不只是丢失更新的问题。即使 Redis 正常工作,这个值还是逻辑上不精确的

Redis 和 MySQL 是两个独立的数据源,我们需要解决并发环境下数据不一致的问题,一般我们都会先更新数据库,再删缓存。

我们查询如下两个时序图:

会话A在 T2时刻执行了插入操作,在 T3时刻会话B读取缓存中的计数,那么此时读取到的计数和会话A事务结束后读取到的计数就会发生不一致。

如果在会话A中调整更新计数操作和插入操作的顺序,那么是否会有所好转呢?

答案还是不行。虽然在 T3 时刻会话B 可以查询到最新的计数,但是无法获取到待插入的数据R。

因为 Redis 和 MySQL 是不同的存储构成的系统,不支持分布式事务,所以没法保证计数的精确性。

在数据库保存计数

根据上面的分析,用缓存系统保存计数有丢失数据和计数不精确的问题。那么,如果我们把这个计数直接放到数据库里单独的一张计数表 C 中,又会怎么样呢?

首先,这解决了崩溃丢失的问题,InnoDB 是支持崩溃恢复不丢数据的。

利用事务来解决时序2 图中的问题,如下所示:

因为MySQL 事务机制和 MVCC,在 T3时刻会话B进行的操作不受会话A 的影响,因为会话A在 T4才提交事务,T2做的修改对会话B不可见。

总结

在不同的存储引擎中,count(*)函数的实现方式不同。我们之前讨论过使用缓存系统来存储计数值存在的问题。现在,我来简洁地解释一下为什么将计数值存储在Redis中不能保证与MySQL表中的数据精确一致。

Redis和MySQL是不同的存储系统,它们不支持分布式事务,因此无法提供精确一致的视图。这就是为什么将计数值存储在Redis中无法确保与MySQL表中数据的一致性。相比之下,将计数值存储在MySQL中可以解决一致性视图的问题。


转载来源:https://juejin.cn/post/7257922419319603256

相关文章
|
1天前
|
存储 运维 安全
云上金融量化策略回测方案与最佳实践
2024年11月29日,阿里云在上海举办金融量化策略回测Workshop,汇聚多位行业专家,围绕量化投资的最佳实践、数据隐私安全、量化策略回测方案等议题进行深入探讨。活动特别设计了动手实践环节,帮助参会者亲身体验阿里云产品功能,涵盖EHPC量化回测和Argo Workflows量化回测两大主题,旨在提升量化投研效率与安全性。
云上金融量化策略回测方案与最佳实践
|
14天前
|
人工智能 自动驾驶 大数据
预告 | 阿里云邀您参加2024中国生成式AI大会上海站,马上报名
大会以“智能跃进 创造无限”为主题,设置主会场峰会、分会场研讨会及展览区,聚焦大模型、AI Infra等热点议题。阿里云智算集群产品解决方案负责人丛培岩将出席并发表《高性能智算集群设计思考与实践》主题演讲。观众报名现已开放。
|
7天前
|
自然语言处理 数据可视化 API
Qwen系列模型+GraphRAG/LightRAG/Kotaemon从0开始构建中医方剂大模型知识图谱问答
本文详细记录了作者在短时间内尝试构建中医药知识图谱的过程,涵盖了GraphRAG、LightRAG和Kotaemon三种图RAG架构的对比与应用。通过实际操作,作者不仅展示了如何利用这些工具构建知识图谱,还指出了每种工具的优势和局限性。尽管初步构建的知识图谱在数据处理、实体识别和关系抽取等方面存在不足,但为后续的优化和改进提供了宝贵的经验和方向。此外,文章强调了知识图谱构建不仅仅是技术问题,还需要深入整合领域知识和满足用户需求,体现了跨学科合作的重要性。
|
1月前
|
存储 人工智能 弹性计算
阿里云弹性计算_加速计算专场精华概览 | 2024云栖大会回顾
2024年9月19-21日,2024云栖大会在杭州云栖小镇举行,阿里云智能集团资深技术专家、异构计算产品技术负责人王超等多位产品、技术专家,共同带来了题为《AI Infra的前沿技术与应用实践》的专场session。本次专场重点介绍了阿里云AI Infra 产品架构与技术能力,及用户如何使用阿里云灵骏产品进行AI大模型开发、训练和应用。围绕当下大模型训练和推理的技术难点,专家们分享了如何在阿里云上实现稳定、高效、经济的大模型训练,并通过多个客户案例展示了云上大模型训练的显著优势。
|
2天前
|
人工智能 容器
三句话开发一个刮刮乐小游戏!暖ta一整个冬天!
本文介绍了如何利用千问开发一款情侣刮刮乐小游戏,通过三步简单指令实现从单个功能到整体框架,再到多端优化的过程,旨在为生活增添乐趣,促进情感交流。在线体验地址已提供,鼓励读者动手尝试,探索编程与AI结合的无限可能。
|
3天前
|
人工智能 自然语言处理 前端开发
从0开始打造一款APP:前端+搭建本机服务,定制暖冬卫衣先到先得
通义灵码携手科技博主@玺哥超carry 打造全网第一个完整的、面向普通人的自然语言编程教程。完全使用 AI,再配合简单易懂的方法,只要你会打字,就能真正做出一个完整的应用。
3224 15
|
6天前
|
Cloud Native Apache 流计算
PPT合集|Flink Forward Asia 2024 上海站
Apache Flink 年度技术盛会聚焦“回顾过去,展望未来”,涵盖流式湖仓、流批一体、Data+AI 等八大核心议题,近百家厂商参与,深入探讨前沿技术发展。小松鼠为大家整理了 FFA 2024 演讲 PPT ,可在线阅读和下载。
3226 10
PPT合集|Flink Forward Asia 2024 上海站
|
20天前
|
人工智能 自然语言处理 前端开发
100个降噪蓝牙耳机免费领,用通义灵码从 0 开始打造一个完整APP
打开手机,录制下你完成的代码效果,发布到你的社交媒体,前 100 个@玺哥超Carry、@通义灵码的粉丝,可以免费获得一个降噪蓝牙耳机。
5888 16
|
1月前
|
缓存 监控 Linux
Python 实时获取Linux服务器信息
Python 实时获取Linux服务器信息
|
2天前
|
消息中间件 人工智能 运维
12月更文特别场——寻找用云高手,分享云&AI实践
我们寻找你,用云高手,欢迎分享你的真知灼见!
341 32