Innodb count的内部流程，你知道吗？

已于 2022-06-01 15:24:45 修改
阅读量312 收藏 1
点赞数 1
分类专栏： 存储 文章标签： mysql 数据库
于 2021-08-29 14:36:27 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/shida219/article/details/119980315
版权

在Innodb可重复读隔离级别下，对count(字段)、count(主键 id)、count(1)、count(*)四种方案，我们应如何进行选择？

1.原因

1.1为什么有四个count

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

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

根据定义能推断出，使用count(*)、count(主键 id) 和 count(1)获得的结果是相同的，count(字段）因为”字段“可能不为NULL，所以结果存在与其它三个不一致的可能。

虽有4个count，其实对应相同函数。

1.2count有时很慢

很多同学有直观感受，当表比较大时，count速度很慢，有时需要几十秒甚至几分钟，但MyISAM能快速返回值。

主要原因为：

  • MyISAM会保存表的具体行数，因此这段代码在MyISAM存储引擎中执行，只需读出保存好的行数即可。

  • Innodb不保存具体行数，count需遍历全表，一行一行获取。之所以这么做，是因为MVCC需判断对当前的count语句，哪些行可见，哪些行不可见，具体细节可看Innodb事务隔离性实现原理，你了解吗？。

2.count流程

count操作涉及MySQL的Server层和存储引擎层。Server负责判断与计数， Innodb存储引擎负责获取数据集。

在Innodb为啥要刷脏页？里讲过，读取数据时，需要把数据从磁盘读到内存，所以count耗时的节点有如下几个：

  1. 将数据从磁盘读入内存，这会发生了很多次I/O，因此造成了主要的时间消耗

  2. 将数据从内存读出，放入返回数据集。引擎返回数据集会涉及到解析数据行，以及拷贝字段值

  3. server层对数据集进行NULL判断，进行计数操作

由此可分析出影响性能的几个方面：

  1. 从磁盘读入内存的数据量

  2. 是否需要将数值从内存中取出

  3. server层是否需要进行NULL判断

3.性能区别

根据以上几点，我们看一下count(字段)、count(主键 id)、count(1)、count(*)的区别。

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

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

对于 count(字段) 来说：

  • 如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；

  • 如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

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

总结下各个操作：

操作是否取值是否判断备注
count(*)否否最快
count(1)否是
count(主键id)是是可能使用最小的索引树
count(字段)是是字段上无索引时，只能选主键索引

按照效率排序的话，count(字段)<=count(主键 id)<count(1)≈count(*) 。

count(字段)<=count(主键 id)是因为count(主键 id)极有可能选择最小的非聚簇索引，而count(字段)如果“字段”没有索引的话，只能选主键索引，这样加载到内存的数据量就大了很多。另外两者有可能选用同一索引树。

当然，count(主键id)选用最小索引树有一个前提，查询语句中不包含where条件和group by条件。

其实count()、count(1)、count(主键id)可做相同优化，都选用最小索引树、不取值、无需判断的方案，不过目前只有count()做了这个优化。

大家count的时候，尽量选择count(*)即可。

4.优化

count()已是最快的方案了，如果还不满足条件，如何优化count()性能呢？

有两个方案：

  1. 创建一个最小索引，这样count(*)计算时会选择最小索引，减少IO

  2. 利用事务，自己计数。建立计数表，增加或删除数据时，同时更新计数表。

总结

大家对count的了解相对多一些，不过仍有很多小细节值得思考。

资料

  1. innodb中count(*),count(字段)，count(主键id),count(1)的区别

  2. MySQL学习笔记：count(1)、count(*)、count（字段）的区别

  3. MySQL 全表 COUNT(*) 简述

  4. 在MySQL的InnoDB存储引擎中count(*)函数的优化

  5. MySQL InnoDB count()函数

最后

大家如果喜欢我的文章，可以关注我的公众号（程序员麻辣烫）

我的个人博客为：https://shidawuhen.github.io/

图片

往期文章回顾：

  1. 设计模式

  2. 招聘

  3. 思考

  4. 存储

  5. 算法系列

  6. 读书笔记

  7. 小工具

  8. 架构

  9. 网络

  10. Go语言

MySQL 单表可以存200亿数据吗？单表真的只能存2000W，为什么？
AI天才研究院
10-29 672
从Innodb的官方文档中可以看到，其表空间的最大限制为64TB，也就是说，Innodb的单表限制基本上也在64TB左右了，当然这个大小是包括这个表的所有索引等其他相关数据。 而在使用单独表空间存储方式时，每个表的数据以一个单独的文件来存放，这个时候的单表限制，又变成文件系统的大小限制了。
MySQL InnoDB 事务commit逻辑分析
刻舟人的博客
09-24 1155
本文从源码实现介绍MySQL事务提交过程。涉及redo\undolog的写盘、bin log的写盘、事务状态的重置、各种参数的改变、无用undolog的清理等方方面面。
mysql innodb count 优化_在MySQL的InnoDB存储引擎中count(*)函数的优化
weixin_36429993的博客
01-19 416
写这篇文章之前已经看过了很多数据库方面的优化内容，大部分都是加索引、使用事务、要什么select什么等等。然而，只是停留在阅读的层面上，很少有实践，因为没有遇到真实的项目，一切都是纸上谈兵。实践是检验真理的唯一标准，于是就想在数据库上测试一些性能优化的方案，比如索引之类的，但是不想使用假的数据，于是就想着能不能抓取网上的一些数据来作分析，后来自己通过PHP抓取了一些数据(爬取数据博文)，抓了大约1...
MySQL InnoDB count()函数
weixin_34242331的博客
02-12 230
1. 系统版本 MySQL 5.7.25，Ubuntu 16.04 2. count()函数 2.1 count(*) count(*)用于计算数据表的行数。 2.1.1 MyISAM实现方式 MyISAM将数据表的总行数存储在磁盘，当有sql语句调用count(*)时，MyISAM能直接返回表的总行数。但是，当sql语句有WHERE语句限制查询行数时，...
Innodb引擎中Count（*）
weixin_42716620的博客
09-24 710
&#13; select count(*)是MySQL中用于统计记录行数最常用的方法，count方法可以返回表内精确的行数。 在某些索引下是好事，但是如果表中有主键，count(*)的速度就会很慢，特别在千万记录以上的大表。 所以、如果是用Innodb引擎的时候，使用select count（*）语句时，建议采用二级索引速度会比用主键索引更快。 在InnoDB引擎中，当我们通...
mysql innodb count_mysql中innodb表的count
weixin_39958911的博客
01-27 236
其实就一个问题：count(1),count(*),count(主键),count(非主键字段)哪个更快？于是建了一张测试表，插入了600多万条记录。表结构如下：CREATETABLE`test`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`c1`varchar(10)NOTNULL,`c2`varchar(10)NOTNULL,...
mysql innodb count_MySQL InnoDB count()函数-阿里云开发者社区
weixin_28686771的博客
01-20 230
1. 系统版本MySQL 5.7.25，Ubuntu 16.042. count()函数2.1 count(*)count(*)用于计算数据表的行数。2.1.1 MyISAM实现方式MyISAM将数据表的总行数存储在磁盘，当有sql语句调用count(*)时，MyISAM能直接返回表的总行数。但是，当sql语句有WHERE语句限制查询行数时，MyISAM要重新算符合WHERE语句的数据表的行数，并...
InnoDB存储引擎
jacksonary的博客
04-23 532
InnoDB存储引擎 文章目录InnoDB存储引擎1. MySQL 逻辑架构2. 物理存储3. InnoDB 的结构3.1 InnoDB的内存结构3.1.1 Buffer Pool3.1.2 Change Buffer3.1.3 adaptive hash index3.1.4 log buffer3.2 InnoDB 的磁盘结构3.2.1 表Table关于锁共享锁和排他锁意向锁record lockgap locknext-key lock关于 MySQL 中指定属性宽度关于索引3. InnoDB 的排序
“68道 Redis+168道 MySQL”精品面试题（带解析），你背废了吗？
m0_71563599的博客
09-21 195
谈起 Redis 和 MySQL，皆是广大 程 序 猿（媛）朋友面试跳槽必踩的两个坑。那么，关于Redis与MySQL，面试官最爱问哪些问题呢？不知道也不用慌，我已整理了这“68道 Redis+168道 MySQL” 精品面试题（带解析），速来试试你 背 “废” 了吗？
一篇文章带你读懂MySQL的InnoDB存储引擎Buffer Pool原理
cyd_0619的博客
07-11 810
一个不变的原则：网络连接必须让线程来处理 一条SQL语句的执行   MySQL内部的工作线程从一个网络连接中取出来一个SQL语句，然后交给SQL接口去执行SQL语句，SQL接口将SQL语句交给查询解析器去解析SQL，解析完SQL，查询解析器再将SQL语句交给查询优化器，去获取一个最优的执行计划，最后交由执行器，执行器根据这个执行计划去调用存储引擎的接口，完成SQL语句的执行。 一、InnoDB存储引擎 1.1 基于InnoDB存储引擎完成一条更新语句的执行 更新语句：update users set na
关于mysql innodb count(*)速度慢的解决办法
09-10
innodb引擎在统计方面和myisam是不同的，Myisam内置了一个计数器，所以在使用 select count(*) from table 的时候，直接可以从计数器中取出数据。而innodb必须全表扫描一次方能得到总的数量
mysql中innodb表的count
weixin_34220963的博客
11-03 138
其实就一个问题：count(1),count(*),count(主键),count(非主键字段)哪个更快？ 于是建了一张测试表，插入了600多万条记录。表结构如下： CREATETABLE`test`(`id`int(10)unsignedNOTNULLAUTO_INCREMENT,`c1`varchar(10)NOTNULL,`c...
在innodb引擎中，count(*)、count(1)、count(主键)、count(字段)哪个性能最高？
smallmww的博客
10-19 318
总体而言，count(*)和count(1)的性能相对较差，但在某些情况下可能由于优化器的优化而表现得相对较好。count(主键)和count(字段)的性能相对较好，但具体表现取决于主键或字段的类型以及是否有索引等因素。在实际应用中，需要根据具体场景选择合适的计数值来提高性能。在InnoDB引擎中，这四种计数值的效率高低取决于具体的数据库和数据表结构，无法一概而论哪个性能最高。但在某些情况下，由于优化器可能会将1视为常数，因此count(1)可能在性能上略好于count()：统计所有行的数量。
Innodb中count的理解,count(*)存储使用缓存或者事务
进化的深山猿
12-21 9574
首先需要声明，下面的内容主要是基于innodb；myIsam中会单独存储count(*)的值，因此会直接返回，效率最高。 innodb为什么不单独存储count(*)的值 这是因为innodb支持事务和mvcc，同一个时刻，存在多个事务，然后每个事务都有插入或者删除操作，那么这个count(*)的值就没有办法维护了。其实我的观点是innodb完全可以将mvcc用于count(*)的值维护，这样...
mysql的innodb快速count技巧
梦启未来
11-21 859
mysql的innodb因为使用的是聚簇索引，导致count的时候按照索引count，会把聚簇索引里面的数据一起扫描了，导致count起来非常慢，30w的数据，做一次count（*）竟然高达30多秒。 经过网上查询看到一个大佬的测试 https://imysql.cn/2008_06_24_speedup_innodb_count 发现一个技巧，可以额外创建一个字段，字段和主键ID数据一致，然后对该字段建索引，然后再使用count，这样速度就飞起了 ...
mysql innodb 表优化_mysql中 innodb表的count()优化
weixin_42097450的博客
01-27 392
作/译者：叶金荣(imysql#imysql.com)，来源： http://imysql.com，欢迎转载。 起因：在innodb表上做count(*)统计实在是太慢了，因此想办法看能不能再快点。 现象：先来看几个测试案例，如下 一、 sbtest 表上的测试 show create table sbtest\G***********作/译者：叶金荣(imysql#imysql.com>)...
innodb变态分页，COUNT
蓝天的博客
06-12 820
今天跟“校长”研究怎么加快innodb一些操作，弄出以下语句 优化过的count     // 强制使用索引，进行count     //SELECT count(1) FROM xxxxy use index(PRIMARY); 如果数据连续，中间没有断数据，那么可以用以下方法加快分页， 平时如果用limit数据越往后分页查询速度会越慢……     //变态分页     //SELECT *
关于innodb的count优化
weixin_34275734的博客
12-03 123
2019独角兽企业重金招聘Python工程师标准>>> ...
mysql innodb count,innodb中count(*),count(字段)，count(主键id),count(1)的区别
weixin_39530839的博客
03-17 254
count(参数)函数解释count() 是一个聚合函数，对于返回的结果集，一行一行的判断，函数参数不是NULL时累计值就加1，否则不加，最后返回累计值。没有过滤条件时，myisam中把一个表的总数存在磁盘上，执行count(*)直接返回这个数，效率很高。innodb中需要把数据一行一行的从引擎中读出来，然后累计。count()各种用法对比根据函数解释可以知道，count(*),count(1),...
count值是0
最新发布
04-03
### Count 值为 0 的原因及解决办法 #### Oracle 中 `COUNT(*)` 结果为 0 的情况 在 Oracle 数据库中，如果执行 `SELECT COUNT(*) FROM table_name` 返回结果为 0，可能是因为表中的数据存在逻辑上的不可见状态。例如，在某些情况下，未提交的数据或者被删除但尚未清理的记录可能导致统计结果异常[^4]。此外，索引损坏也可能影响计数操作的结果。 对于这种情况，可以尝试以下几种方式来解决问题： 1. **重新计算统计数据**：通过运行 `ANALYZE TABLE table_name COMPUTE STATISTICS` 或者使用更现代的方法 `DBMS_STATS.GATHER_TABLE_STATS('schema', 'table')` 来刷新表的统计信息。 2. **重建索引**：如果有依赖于索引的操作失败，可以通过 `ALTER INDEX index_name REBUILD` 进行修复。 3. **检查事务隔离级别**：确认当前会话是否存在未提交的更改，这些更改可能会隐藏部分数据。 --- #### MySQL InnoDB 中 `COUNT(*)` 性能问题与准确性不足的情况 MySQL 使用 InnoDB 存储引擎时，由于其多版本并发控制 (MVCC) 特性以及存储结构设计，`COUNT(*)` 可能表现出性能较差或结果不够精确的现象。具体表现为当大量数据频繁更新时，InnoDB 不会对每条记录都实时维护全局计数值，而是基于近似估算得出结果[^1]。 针对这一现象，可采取如下措施改善效率并提高精度： - **创建覆盖索引**：利用特定字段建立辅助索引来加速查询过程，比如 `(id)` 字段上构建唯一索引。 - **调整配置参数**：适当修改 `innodb_stats_auto_recalc` 和其他相关选项以确保及时更新内部统计信息。 - **采用缓存机制**：引入 Redis 等外部工具保存总数量副本，减少直接访问数据库的压力。 --- #### Hive 表中 `COUNT(1)` 或 `COUNT(*)` 显示为零的情形及其应对策略 Hive 是一种分布式大数据处理框架，它的工作原理决定了并非每次调用都能立即获取到最新版的真实值。特别是当源文件仅通过 LOAD DATA 方式导入而非 INSERT INTO 插入时，元数据层面上并未同步更新 row counts 记录，从而造成最终呈现出来的总数度量偏低甚至归零[^2]。 以下是几个可行方案用于修正此类偏差： 1. **手动触发分析命令**：运用 `ANALYZE TABLE your_table COMPUTE STATISTICS` 更新整张表格的基础属性描述符； 2. **强制全盘扫描模式**：设置环境变量 `set hive.compute.query.using.stats=false;` 关闭预估功能转而依靠实际遍历完成总计工作流； 3. **转换成 ACID 支持型格式**：将原始资料迁移到支持事务特性的 ORC 文件形式下再做进一步加工运算即可获得更加稳定可靠的反馈效果。 --- #### 数据倾斜引发的潜在隐患及缓解途径 除了上述提到的技术层面因素外，还有一种常见场景也会致使汇总函数输出为空白——即所谓的“数据倾斜”。这通常发生在分组聚合阶段因键分布极度不平衡而导致少数分区承载过多负载进而超时崩溃退出之前未能成功交付任何有效成果给下游消费者知晓[^3]。 为了预防这种状况发生可以从以下几个角度切入考虑改进计划： - 调优 SQL 编写技巧规避不必要的复杂嵌套关系降低整体开销成本； - 利用采样技术预先评估各维度规模差异程度据此分配资源配额比例达到均衡目的； - 配合广播变量传递小尺寸集合成员列表实现高效匹配查找替代传统 JOIN 方法提升响应速度和服务质量水平等等…… --- ### 示例代码片段展示如何优化 Hive 查询语句 ```sql -- 手动启动详尽统计流程 ANALYZE TABLE sales_data PARTITION(year=2023) COMPUTE STATISTICS; -- 修改默认行为禁用快速猜测算法 SET hive.compute.query.using.stats = false; SELECT COUNT(*) AS total_records FROM customer_orders; ```
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

程序员麻辣烫

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值