浅谈MySQL 统计行数的 count

MySQL count() 函数我们并不陌生，用来统计每张表的行数。但如果你的表越来越大，且是 InnoDB 引擎的话，会发现计算的速度会越来越慢。在这篇文章里，会先介绍 count() 实现的原理及原因，然后是 count 不同用法的性能分析，最后给出需要频繁改变并需要统计表行数的解决方案。

Count() 的实现

InnoDB 和 MyISAM 是 MySQL 常用的数据引擎，由于两者实现的不同，导致 count() 操作计算的效率也不同。

对于 MyISAM 来说，它把每个表的总行数都存在了磁盘上，因此使用 count(*) 计算时，效率很高直接返回结果。但如果加入了 where 条件，依然会进行搜索，所以效率是不高的。

对于 InnoDB 来说，在进行 count(*) 运算时，会把数据从引擎中一行行读出来，然后累计计数，自然表大了之后，效率就变低了。

那么，为什么 InnoDB 不能像 MyISAM 在表中记录呢？原因就在于 InnoDB 比 MyISAM 多了支持事务的特性，同时也需要一定的取舍。由于 MVCC 的控制，使得 MySQL 具有并发的能力，也就是说对于同一时刻，InnoDB 返回的表的行数是不一定的，事务看到的行数与开启后的一致性视图有关，换句话说，每个事务能看到的数据版本是不一样的，只能一行行拿出来进行判断。

像下面的事务，假设表 t 有 10000 条数据：

Session A Session B Session C
select count(*) from t;
insert into t ();
begin;
insert into t();
select count(*) from t; select count(*) from t; select count(*) from t;
10000; 结果是 10002 结果是 10001

对于 Session A 来说，Session B 未提交不可见，Session C 提交了，但是在 Session A 启动后提交的，也不可见。所以是 10000.

而对于 Session B 而言，Session C 在启动之前提交，自己又插入了一条，所以结果是 10002.

其实 InnoDB 在进行 count(*) 操作时，还是做了优化的，在进行 count(*) 操作时，由于普通索引会保存主键的 id 值，所以会找到最小的那颗普通索引树进行查找，而不是去遍历主键索引树。

在保证逻辑正确的前提下，减少扫描的数据量，是数据库系统设计的通用法则。

另外在使用 show table status 时，也可以查询出行数，而且速度很快，但需要注意的是，该命令是通过索引统计的值来采样估算的。官方文档说误差可以有 40%-50%.

但如果我们真的需要实时的获取的某个表的行数，应该怎么办呢？

手动保存表的数量

用缓存系统来保存计数

对于进行更新的表，可能会想到用缓存系统来支持。比如 Redis 里来保存某个表总行数。

每次插入数据库时，Redis 计数加一，相反则减一，这样看起来读写操作都很快，但会存在一些问题。

缓存系统会丢失更新：

对于 Redis 在内存中的数据，需要定期的同步到磁盘中，但对于 Redis 异常重启，就没有办法了。比如在 Redis 中插入后，Redis 重启，数据没有持久化到硬盘。这时可以在重启 Redis 后，从数据库执行下 count(*) 操作,然后更新到 Redis 中。一次全表扫描还是可行的。

逻辑不精确：

假设一个页面中，需要显示一张表的行数，以及每一条数据。在实现时，可以先从 Redis 取数量，然后从数据库里取记录。

但可能会出现这样的情况：

  1. 数据库查到 100 行结果里有最新插入的记录，而 Redis 计数里少 1.
  2. 数据库查到 100 行结果没有最新的记录，但 Redis 计数却多了 1.
Session A Session B
插入一条数据； T1
读 Redis 计数； T2
从数据库中查记录；
Redis 计数加 1; T3

对于 Session B 来说，在 T2 时刻，会发现 Redis 的数量比数据库少 1 条。

Session A Session B
Redis 计数加 1; T1
读 Redis 计数； T2
从数据库中查记录；
插入一条数据； T3

对于 Session B 来说，在 T2 时刻，会发现 Redis 的数量比数据库多 1 条。

其实产生问题的原因就是因为 Redis 和数据库查记录没有在同一个事务中。

用数据库保存

由于 InnoDB 引擎的支持，MySQL 本身是支持事务的，所以将 Redis 的插入操作换成在数据库的更新操作，就可以利用在RR级别下的事务特性，进而保证数据的精确性。

而且还有一点，由于 redo log 的支持，在 MySQL 发生异常时，是可以保证 crash-safe。

不同 count 用法的执行效率

count() 本身是一个聚合函数，对于返回的结果集，一行行地判断。如果参数不是 NULL 的话，会一直累加，最后返回结果。

所以 count(*), count(id), count(1) 表示都是返回满足条件的结果集总行数。

而 count(字段)，则表示满足条件的数据行里，不为 NULL 的字段。

对于 count(id) 来说，InnoDB 会遍历整张表，把每行 id 取出来，给 server 层。Server 判断 id 是否为空，然后累加。

对于 count(1) 来说，InnoDB 会遍历整张表，但不取值。Server 层会自己放入 1，然后累加。

所以对于 count(1) 的执行会比 count(*) 要快，少了解析数据行以及拷贝字段值的操作。

对于 count(字段) 来说，如果字段定义时是 not null, 会一行行读出，并判断不能为 null，然后累加。如果定义时可以为 null，执行时，需要将值去除，判断不是 null 才累加。

count(*) 除外，专门做了优化，不取值，直接按行累加，并且会找到最小的索引树进行计算。

总结

MySQL count() 函数的执行效率和底层的数据引擎有关。MyISAM 不加 where 条件，查询会很快，但不支持事务。InnoDB 支持事务，由于 MVCC 的实现，导致每次查询都需要一行行的扫描，效率不高。

解决方法可以通过设计外部缓存如 Redis，保存记录。但存在异常重启和数据不准确的情况。可以通过在 InnoDB 中新建一张表，保存记录这样的解决方案。

最后，InnoDB 对 count(*) 做了独立的优化，而其他的 count 操作，则需要额外的操作。

以上就是浅谈MySQL 统计行数的 count的详细内容，更多关于Mysql count的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • Mysql中的count()与sum()区别详细介绍

    首先创建个表说明问题 复制代码 代码如下: CREATE TABLE `result` ( `name` varchar(20) default NULL, `subject` varchar(20) default NULL, `score` tinyint(4) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 插入一些数据, 复制代码 代码如下: insert into result values ('张三','数学',90), ('张三'

  • MySQL中distinct和count(*)的使用方法比较

    首先对于MySQL的DISTINCT的关键字的一些用法: 1．在count 不重复的记录的时候能用到,比如SELECT COUNT( DISTINCT id ) FROM tablename:就是计算talbebname表中id不同的记录有多少条. 2,在需要返回记录不同的id的具体值的时候可以用,比如SELECT DISTINCT id FROM tablename:返回talbebname表中不同的id的具体的值. 3．上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义,比如SE

  • mySQL count多个表的数据实例详解

    mySQL count多个表的数据实例详解

    一.实现需求 最近在做成就系统,成就中有很多维度都和数量有关,比如用户粉丝数达到多少50个,授予 名扬四海 称号,用户点赞达到 100 次,授予 点赞圣手 称号等等. 粉丝数在 user_relation 表 点赞数在 user_praise 表 按照以往的做法,如果需要查询用户的这些数据,又因为数据是在不同的表中,所以往往会分开多条 SQL 查询,比如: SELECT count(*) mysh FROM user_relation WHERE other_uid =123456; SELEC

  • 有关mysql中ROW_COUNT()的小例子

    注:mysql中的ROW_COUNT()可以返回前一个SQL进行UPDATE,DELETE,INSERT操作所影响的行数. MySQL上的测试(数据库版本为:5.1.22): 1.创建数据库表: 复制代码 代码如下: create table t(  id int,  name varchar(50),  address varchar(100),  primary key(id,name)  )engine =InnoDB; 2.插入测试数据: 复制代码 代码如下: insert into t

  • MYSQL中统计查询结果总行数的便捷方法省去count(*)

    MYSQL的关键词 : SQL_CALC_FOUND_ROWS 查看手册后发现此关键词的作用是在查询时统计满足过滤条件后的结果的总数(不受 Limit 的限制) 例如: 复制代码 代码如下: SELECT SQL_CALC_FOUND_ROWS tid FROM cdb_threads WHERE fid=14 LIMIT 1,10; 假设满足条件的有1000条,这里返回10条. 立即使用 复制代码 代码如下: SELECT found_rows() AS rowcount; 则返回的 rowc

  • mysql count提高方法总结

    mysql想必很多程序猿都是不陌生的.很多人在纠结count的用法,怎么样才能查询效果最好.今天来谈谈小编的一些看法,仅供参考. 1.咱们先准备建一个表,并准备好测试数据方便后续步骤测试 以InnoDB引擎表为例 建表语句如下 CREATE TABLE test.test ( a VARCHAR(50) NOT NULL COMMENT 'ddfdf', b VARCHAR(15) NOT NULL COMMENT 'fds', c VARCHAR(20) NOT NULL COMMENT 'a

  • MySQL中聚合函数count的使用和性能优化技巧

    本文的环境是Windows 10,MySQL版本是5.7.12-log 一. 基本使用 count的基本作用是有两个: 统计某个列的数据的数量: 统计结果集的行数: 用来获取满足条件的数据的数量.但是其中有一些与使用中印象不同的情况,比如当count作用一列.多列.以及使用*来表达整行产生的效果是不同的. 示例表如下: CREATE TABLE `NewTable` ( `id` int(11) NULL DEFAULT NULL , `name` varchar(30) NULL DEFAUL

  • MySQL中无过滤条件的count详解

    count(*) 实现 1.MyISAM:将表的总行数存放在磁盘上,针对无过滤条件的查询可以直接返回 如果有过滤条件的count(*),MyISAM也不能很快返回 2.InnoDB:从存储引擎一行行地读出数据,然后累加计数 由于MVCC,在同一时刻,InnoDB应该返回多少行是不确定 样例 假设表t有10000条记录 session A session B session C BEGIN; SELECT COUNT(*) FROM t;(返回10000) INSERT INTO t;(插入一行)

  • MySQL中count(*)、count(1)和count(col)的区别汇总

    前言 count函数是用来统计表中或数组中记录的一个函数,count(*) 它返回检索行的数目, 不论其是否包含 NULL值.最近感觉大家都在讨论count的区别,那么我也写下吧:欢迎留言讨论,话不多说了,来一起看看详细的介绍吧. 1.表结构: dba_jingjing@3306>[rds_test]>CREATE TABLE `test_count` ( -> `c1` varchar(10) DEFAULT NULL, -> `c2` varchar(10) DEFAULT N

  • 浅谈MySQL 统计行数的 count

    MySQL count() 函数我们并不陌生,用来统计每张表的行数.但如果你的表越来越大,且是 InnoDB 引擎的话,会发现计算的速度会越来越慢.在这篇文章里,会先介绍 count() 实现的原理及原因,然后是 count 不同用法的性能分析,最后给出需要频繁改变并需要统计表行数的解决方案. Count() 的实现 InnoDB 和 MyISAM 是 MySQL 常用的数据引擎,由于两者实现的不同,导致 count() 操作计算的效率也不同. 对于 MyISAM 来说,它把每个表的总行数都存在

  • 浅谈mysql执行过程以及顺序

    浅谈mysql执行过程以及顺序

    前言:mysql在我们的开发中基本每天都要面对的,作为开发中的数据的来源,mysql承担者存储数据和读写数据的职责.因为学习和了解mysql是至关重要的,那么当我们在客户端发起一个sql到出现详细的查询数据,这其中究竟经历了什么样的过程?mysql服务端是如何处理请求的,又是如何执行sql语句的?本篇博客将来探讨这个问题: 一:mysql执行过程 mysql整体的执行过程如下图所示: 1.1:连接器 连接器的主要职责就是: ①负责与客户端的通信,是半双工模式,这就意味着某一固定时刻只能由客户端向

  • 浅谈MySQL中的group by

    浅谈MySQL中的group by

    目录 1.前言 2.准备user表 2.1 group by规则 2.2 group by使用 2.3 having使用 2.4 order by与limit 2.5 with rollup 1.前言 MySQL的group by用于对查询的数据进行分组:此外MySQL提供having子句对分组内的数据进行过滤. MySQL提供了许多select子句关键字, 它们在语句中的顺序如下所示: 子句 作用 是否必须/何时使用 select 查询要返回的数据或者表达式 是 from 指定查询的表 否 w

  • 浅谈MySQL之select优化方案

    目录 生活中的例子 慢查询 如何去优化 count limit 最大值最小值min&max 生活中的例子 我们是否看到过在公司中许多查询语句都是select * xxxx 心中的想法肯定是,别人写了select *,那我写吧,省去了不少麻烦事儿 慢查询 首先去思考,最基本的,是否我们使用的数据库插查询语句存在了访问的数据太多 其实大部分性能低的查询往往都可以通过减少访问的数据量来优化的 因为select * 会给服务器带来额外的I/O.内存和cpu的消耗 数据库中慢查询开销的三个指标 相应时间

  • 浅谈MySQL为什么会选错索引

    目录 1.引例 2.优化器的逻辑 3.解决办法 1.引例 首先创建一张表,并对字段a,b分别建立索引: create table t ( id int(11) not null, a int(11) default null, b int(11) default null, primary key (id), key a(a), key b(b) )engine=InnoDB; 然后往表中,插入十万行数据,值按整数递增:(1,1,1).(2,2,2).(3,3,3)… delimiter ;;

  • 浅谈MySQL在cmd和python下的常用操作

    环境配置1:安装mysql,环境变量添加mysql的bin目录 环境配置2:python安装MySQL-Python 请根据自身操作系统下载安装,否则会报c ++ compile 9.0,import _mysql等错误 windows10 64位操作系统可到 http://www.lfd.uci.edu/~gohlke/pythonlibs/ 下载安装MySQL-Python包,至于whl和tar.gz在windows和Linux下的安装方法可查看我的上一篇文章 一 .cmd命令下的操作: 连

  • 浅谈MySQL使用笛卡尔积原理进行多表查询

    MySQL的多表查询(笛卡尔积原理) 先确定数据要用到哪些表. 将多个表先通过笛卡尔积变成一个表. 然后去除不符合逻辑的数据(根据两个表的关系去掉). 最后当做是一个虚拟表一样来加上条件即可. 注意:列名最好使用表别名来区别. 笛卡尔积 Demo: 左,右连接,内,外连接 l 内连接: 要点:返回的是所有匹配的记录. select * from a,b where a.x = b.x ////内连接 l 外连接有左连接和右连接两种. 要点:返回的是所有匹配的记录 外加 每行主表外键值为null的

  • 浅谈MySQL中float、double、decimal三个浮点类型的区别与总结

    下表中规划了每个浮点类型的存储大小和范围: 类型 大小 范围(有符号) 范围(无符号) 用途 ==float== 4 bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值 ==double== 8 bytes (-1.797 693 134 862 315 7 E

  • 浅谈Mysql多表连接查询的执行细节

    浅谈Mysql多表连接查询的执行细节

    先构建本篇博客的案列演示表: create table a(a1 int primary key, a2 int ,index(a2)); --双字段都有索引 create table c(c1 int primary key, c2 int ,index(c2), c3 int); --双字段都有索引 create table b(b1 int primary key, b2 int); --有主键索引 create table d(d1 int, d2 int); --没有索引 insert

  • 浅谈Mysql主键索引与非主键索引区别

    目录 什么是索引 主键索引和普通索引的区别 索引具体采用的哪种数据结构 InnoDB使用的B+ Tree的索引模型,那么为什么采用B+ 树?这和Hash索引比较起来有什么优缺点? B+ Tree的叶子节点都可以存哪些东西? 聚簇索引和非聚簇索引,在查询数据的时候有区别? Index Condition Pushdown(索引下推) 查询优化器 关于索引的题 什么是索引 MySql官方索引的定义:索引(Index)是帮助MySql高效获取数据的数据结构,索引的目的在于提高查询效率,类比字典:实际上

随机推荐