MySQL---count(*)的那些事

本文详细介绍了MySQL中MyISAM和InnoDB引擎下count(*)的执行原理,比较了count(*)、count(主键id)、count(字段)的效率,并强调了count(*)作为高效选择。同时提到了使用showtablestatus命令的局限性。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >


平常计算一个表的行数,一条select count(*) from table搞定,但是随着系统中记录数越来越多,这条语句执行得也会越来越慢。

count(*)的实现方式

在不同的MySQL引擎中,count(*)有不同的实现方式。

  • MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高。
  • InnoDB引擎执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。

今天说的是没有过滤条件的count(*),如果加了where 条件的话,MyISAM表也是不能返回得这么快的。

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

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

MySQL提供 showtable status 命令快速返回TABLE_ROWS行数,但是TABLE_ROWS和索引一样是通过采样估算计算出来的,官方文档显示误差可能达到40%到50%,因此这个是无法代替count(*)来使用的。

不同的count用法

常用的count用法有count(*)、count(主键id)、count(字段)和count(1),接下来说一说它们之间的差别。

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

  • 对于count(主键id)来说,InnoDB引擎会遍历整张表,把每一行的id值都取出来,返回给server层。server层拿到id后,判断是不可能为空的,就按行累加。
  • 对于count(1)来说,InnoDB引擎遍历整张表,但不取值。server层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。因为count(主键id)从引擎返回id会涉及到解析数据行,以及拷贝字段值的操作,所以count(1)执行得要比count(主键id)快。
  • 对于count(字段)来说:如果这个“字段”是定义为not null的话,一行行地从记录里面读出这个字段,判断不能为null,按行累加;如果这个“字段”定义允许为null,那么执行的时候,判断到有可能是null,还要把值取出来再判断一下,不是null才累加。
  • 对于count(*)来说,MySQL租做了特殊的优化,并不会把全部字段取出来,而且不取值,因为肯定不为null,所以按行累加。

总结:
按照效率排序的话,count(字段)<count(主键id)<count(1)≈count(*),所以尽量使用count(*)提高效率。

### SQL 脚本与 UNION ALL 查询解决方案 在 SQL 查询中,`UNION ALL` 是一种非常强大的工具,用于合并两个或多个 SELECT 语句的结果集。它与 `UNION` 的区别在于,`UNION ALL` 不会去除重复的行,因此性能通常更高。以下是一个完整的解决方案,结合了用户可能的需求和引用中的内容。 #### 问题分析 用户可能需要解决的问题是:如何通过 SQL 查询找到所有好友数最多的人,并且考虑可能存在多个人拥有相同的好友数。此外,还需要理解 `UNION ALL` 的使用场景以及其在复杂查询中的作用。 #### 解决方案 以下是一个完整的 SQL 脚本,用于查找所有好友数最多的人: ```sql SELECT id, SUM(num) AS total_friends FROM ( (SELECT requester_id AS id, COUNT(*) AS num FROM RequestAccepted GROUP BY requester_id) UNION ALL (SELECT accepter_id AS id, COUNT(*) AS num FROM RequestAccepted GROUP BY accepter_id) ) AS combined_friends GROUP BY id HAVING total_friends = ( SELECT MAX(total_friends) FROM ( SELECT id, SUM(num) AS total_friends FROM ( (SELECT requester_id AS id, COUNT(*) AS num FROM RequestAccepted GROUP BY requester_id) UNION ALL (SELECT accepter_id AS id, COUNT(*) AS num FROM RequestAccepted GROUP BY accepter_id) ) AS subquery GROUP BY id ) AS max_friends ); ``` #### 代码解析 1. **子查询部分**: - 第一个子查询统计每个 `requester_id` 的好友数量。 - 第二个子查询统计每个 `accepter_id` 的好友数量。 - 使用 `UNION ALL` 将两个子查询结果合并[^1]。 2. **主查询部分**: - 在合并后的结果集中,按 `id` 分组并计算总的好友数。 - 使用 `HAVING` 子句筛选出好友数等于最大值的所有用户。 3. **嵌套查询**: - 嵌套查询用于找到好友数的最大值,并将其作为过滤条件[^1]。 #### 注意- 如果数据库中存在大量数据,建议为 `requester_id` 和 `accepter_id` 创建索引以优化查询性能[^4]。 - 确保 `RequestAccepted` 表的结构符合预期,例如包含 `requester_id` 和 `accepter_id` 列[^1]。 #### 关于 SQL 注入 在实际开发中,应避免直接拼接用户输入到 SQL 查询中,以防止 SQL 注入攻击。例如,引用中的注入示例展示了如何利用 `load_file` 函数读取文件[^2]。为了防止此类攻击,可以使用参数化查询或预处理语句[^3]。 ```python # 示例:Python 中的参数化查询 import mysql.connector connection = mysql.connector.connect(host='localhost', user='user', password='password', database='db') cursor = connection.cursor() user_input = 'test' query = "SELECT * FROM users WHERE username = %s" cursor.execute(query, (user_input,)) result = cursor.fetchall() ```
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值