和count(*)和count(主键字段)有什么区别？)
一、三种写法分别是什么意思1.COUNT(*)SELECT COUNT(*) FROM users;表示统计结果集中的行数不管这一行的字段是否为NULL。这是最标准、最清晰的“统计行数”写法。2.COUNT(1)SELECT COUNT(1) FROM users;1是一个永远不为NULL的常量。对于结果集中的每一行MySQL 都可以得到一个非NULL的1所以最终统计的仍然是行数每行 - 计算出 1 - 计数加一因此对 InnoDB 来说COUNT(*) COUNT(1)MySQL 官方明确说明这两种写法处理方式相同没有性能差异。3.COUNT(主键字段)SELECT COUNT(id) FROM users;它统计的是id字段不为NULL的行数。如果id是真正的主键那么主键必然是NOT NULL因此COUNT(*) COUNT(1) COUNT(id)通常得到相同结果。二、举个例子表中有三行idnameremark1张三正常2李四NULL3王五正常执行SELECT COUNT(*) AS c1, COUNT(1) AS c2, COUNT(id) AS c3, COUNT(remark) AS c4 FROM users;结果是c1 3 c2 3 c3 3 c4 2原因COUNT(*)统计所有行COUNT(1)1永不为NULL统计所有行COUNT(id)id是主键全部非NULLCOUNT(remark)忽略remark IS NULL的那一行MySQL 中COUNT(expr)统计的是表达式不为NULL的数量而COUNT(*)统计行数。三、哪个性能最好对 InnoDB 来说通常结论是COUNT(*) COUNT(1)性能基本相同。如果主键字段是id BIGINT PRIMARY KEY那么COUNT(id)结果通常也相同但没有必要为了性能特意使用它。实际开发中推荐SELECT COUNT(*) FROM users;原因是语义最清晰表达的是“统计行数”MySQL 对它有明确的优化路径不会因为字段是否允许NULL而产生误差MySQL 官方说明InnoDB 会按照当前事务可见性统计记录并且对于没有额外WHERE、GROUP BY的COUNT(*)通常会遍历可用的最小二级索引没有二级索引时才扫描聚簇索引。所以实际性能主要取决于是否有合适的WHERE条件是否有合适的索引索引大小数据是否已经在 Buffer Pool 中当前事务可见的数据量表中数据规模而不是取决于*、1还是主键字段。四、一个容易出错的场景LEFT JOIN考虑SELECT u.id, COUNT(*), COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id;假设用户张三没有订单。LEFT JOIN仍然会保留张三这一行只是订单表字段全部为NULLu.id o.id 1 NULL因此COUNT(*) 1 COUNT(o.id) 0这里两者含义不同COUNT(*)统计连接结果中的行数COUNT(o.id)统计真正匹配到订单的数量因此统计子表数量时经常应该写COUNT(o.id)而不是COUNT(*)五、MyISAM 的特殊情况如果使用 MyISAM且查询是SELECT COUNT(*) FROM users;没有WHERE条件时MyISAM 可以利用存储的行数信息快速返回结果。但 InnoDB 不保存一个可以直接返回的精确总行数因为不同事务可能看到不同的数据版本。不过现在绝大多数业务表使用 InnoDB所以通常记住 InnoDB 的结论即可。
一、三种写法分别是什么意思1.COUNT(*)SELECT COUNT(*) FROM users;表示统计结果集中的行数不管这一行的字段是否为NULL。这是最标准、最清晰的“统计行数”写法。2.COUNT(1)SELECT COUNT(1) FROM users;1是一个永远不为NULL的常量。对于结果集中的每一行MySQL 都可以得到一个非NULL的1所以最终统计的仍然是行数每行 - 计算出 1 - 计数加一因此对 InnoDB 来说COUNT(*) COUNT(1)MySQL 官方明确说明这两种写法处理方式相同没有性能差异。3.COUNT(主键字段)SELECT COUNT(id) FROM users;它统计的是id字段不为NULL的行数。如果id是真正的主键那么主键必然是NOT NULL因此COUNT(*) COUNT(1) COUNT(id)通常得到相同结果。二、举个例子表中有三行idnameremark1张三正常2李四NULL3王五正常执行SELECT COUNT(*) AS c1, COUNT(1) AS c2, COUNT(id) AS c3, COUNT(remark) AS c4 FROM users;结果是c1 3 c2 3 c3 3 c4 2原因COUNT(*)统计所有行COUNT(1)1永不为NULL统计所有行COUNT(id)id是主键全部非NULLCOUNT(remark)忽略remark IS NULL的那一行MySQL 中COUNT(expr)统计的是表达式不为NULL的数量而COUNT(*)统计行数。三、哪个性能最好对 InnoDB 来说通常结论是COUNT(*) COUNT(1)性能基本相同。如果主键字段是id BIGINT PRIMARY KEY那么COUNT(id)结果通常也相同但没有必要为了性能特意使用它。实际开发中推荐SELECT COUNT(*) FROM users;原因是语义最清晰表达的是“统计行数”MySQL 对它有明确的优化路径不会因为字段是否允许NULL而产生误差MySQL 官方说明InnoDB 会按照当前事务可见性统计记录并且对于没有额外WHERE、GROUP BY的COUNT(*)通常会遍历可用的最小二级索引没有二级索引时才扫描聚簇索引。所以实际性能主要取决于是否有合适的WHERE条件是否有合适的索引索引大小数据是否已经在 Buffer Pool 中当前事务可见的数据量表中数据规模而不是取决于*、1还是主键字段。四、一个容易出错的场景LEFT JOIN考虑SELECT u.id, COUNT(*), COUNT(o.id) FROM users u LEFT JOIN orders o ON u.id o.user_id GROUP BY u.id;假设用户张三没有订单。LEFT JOIN仍然会保留张三这一行只是订单表字段全部为NULLu.id o.id 1 NULL因此COUNT(*) 1 COUNT(o.id) 0这里两者含义不同COUNT(*)统计连接结果中的行数COUNT(o.id)统计真正匹配到订单的数量因此统计子表数量时经常应该写COUNT(o.id)而不是COUNT(*)五、MyISAM 的特殊情况如果使用 MyISAM且查询是SELECT COUNT(*) FROM users;没有WHERE条件时MyISAM 可以利用存储的行数信息快速返回结果。但 InnoDB 不保存一个可以直接返回的精确总行数因为不同事务可能看到不同的数据版本。不过现在绝大多数业务表使用 InnoDB所以通常记住 InnoDB 的结论即可。
