浅谈MySQL 统计行数的 count
MySQL count() 函数我们并不陌生,用来统计每张表的行数。但如果你的表越来越大,且是 InnoDB 引擎的话,会发现计算的速度会越来越慢。在这篇文章里,会先介绍 count() 实现的原理及原因,然后是 count 不同用法的性能分析,给出需要频繁改变并需要统计表行数的解决方案。
Count() 的实现
InnoDB 和 MyISAM 是 MySQL 常用的数据引擎,由于两者实现的不同,导致 count() 操作计算的效率也不同。
对于 MyISAM 来说,它把每个表的总行数都存在了磁盘上,使用 count() 计算时,效率很高直接返回结果。但如果加入了 where 条件,依然会进行搜索,所以效率是不高的。
对于 InnoDB 来说,在进行 count() 运算时,会把数据从引擎中一行行读出来,然后累计计数,自然表大了之后,效率就变低了。
那么,为什么 InnoDB 不能像 MyISAM 在表中记录呢?原因就在于 InnoDB 比 MyISAM 多了支持事务的特性,也需要一定的取舍。由于 MVCC 的控制,使得 MySQL 具有并发的能力,也就是说对于同一时刻,InnoDB 返回的表的行数是不一定的,事务看到的行数与开启后的一致性视图有关,换句话说,每个事务能看到的数据版本是不一样的,只能一行行拿出来进行判断。
像狼蚁网站SEO优化的事务,假设表 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 取数量,然后从数据库里取记录。
但可能会出现这样的情况
- 数据库查到 100 行结果里有最新插入的记录,而 Redis 计数里少 1.
- 数据库查到 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的资料请关注狼蚁SEO其它相关文章!
编程语言
- 甘肃哪有关键词排名优化购买方式有哪些
- 甘肃SEO如何做网站优化
- 河南seo关键词优化怎么做电话营销
- 北京SEO优化如何做QQ群营销
- 来宾百度关键词排名:提升您网站曝光率的关键
- 卢龙关键词优化:提升您网站排名的策略与技巧
- 山东网站优化的注意事项有哪些
- 四川整站优化怎样提升在搜索引擎中的排名
- 疏附整站优化:提升网站性能与用户体验的全新
- 海南seo主要做什么工作售后服务要做到哪些
- 荣昌百度网站优化:提升您网站的搜索引擎排名
- 河北seo网站排名关键词优化如何做SEO
- 江西优化关键词排名推广售后保障一般有哪些
- 古浪SEO优化:提升你的网站可见性
- 西藏网站排名优化怎么把网站排名在百度首页
- 如何提升阳东百度快照排名:详尽指南