MySQL中count(*)、count(1)、count(列名)的区别与性能优化

先别急着背八股。count(*)、count(1)、count(列名)这个面试题90% 的人能说出“count(列名) 不统计 NULL”但再往下问“InnoDB 里 count() 是怎么执行的”“为什么 count() 和 count(1) 性能没差别”“LEFT JOIN 下 count(主表列) 会出什么问题”很多人就卡住了。这篇文章直接把这三种写法从语法、执行计划、性能、NULL 处理、去重统计到面试追问全部拆完最后还给一套可以直接背的回答模板。建议收藏面试前翻一遍。1. 核心结论速览先把能直接回答的结论放在最前面后面再展开验证。对比项count(*)count(1)count(列名)统计内容统计所有行数统计所有行数统计该列非 NULL 的行数是否忽略 NULL不忽略不忽略忽略 NULL 值是否遍历数据InnoDB 下走最小二级索引或聚簇索引与 count(*) 等价若列上有二级索引可能走二级索引性能表现快InnoDB 优化与 count(*) 相同取决于索引和 NULL 分布使用建议优先使用可用但不建议刻意写仅在需要“该列非空数量”时使用面试时最重要的第一句话在 MySQL InnoDB 中count(*) 和 count(1) 没有性能差异都不会忽略 NULLcount(列名) 会忽略 NULL语义不同不能混用。2. count 的三种写法到底在做什么2.1 count(*)统计所有行count(*)的语义是统计结果集中所有行的数量不管某列是否为 NULL。它取的是“行”的概念而不是“某个列值”。执行时MySQL 会对查询结果集逐行判断“这一行是否存在”只要存在就计数。在 InnoDB 存储引擎中count(*)并不会真的把全部行的所有列数据读出来做判断而是有专门的优化路径如果表上有最小的二级索引优化器会选择遍历这个索引来计数因为二级索引通常比聚簇索引更小IO 更少。如果没有二级索引才会选择遍历主键聚簇索引。2.2 count(1)每行加一个常量count(1)的语义等价于“对每一行放一个常量 1然后统计有多少个 1”。很多人的误解是“count(1) 比 count(*) 快因为只取常量不取列”。这在 MySQL 中是完全错误的。MySQL 的优化器在执行时会把count(1)改写为count(*)的等价形式执行计划和 IO 成本完全一样。从用户角度理解count(1)表示“这行存在就计数”不会因为某一列为 NULL 而跳过这一行。所以无论 count(*) 还是 count(1)统计结果一定等于表的全量行数。2.3 count(列名)只统计非 NULLcount(列名)的语义完全不同它只统计该列不为 NULL 的行数。示例CREATE TABLE user ( id INT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100) NULL ); INSERT INTO user VALUES (1, 张三, zhangsanexample.com), (2, 李四, NULL), (3, 王五, NULL);查询结果SELECT COUNT(*) AS cnt_all, -- 3 COUNT(1) AS cnt_one, -- 3 COUNT(email) AS cnt_email; -- 1count(*)3count(1)3count(email)1因为只有张三的 email 不为 NULL这就是三种写法最本质的差异。2.4 count(列名) 的另一个坑LEFT JOIN 下的重复计数面试进阶题经常这样出假设有两个表CREATE TABLE orders ( id INT PRIMARY KEY, user_id INT ); CREATE TABLE order_items ( id INT PRIMARY KEY, order_id INT, product_name VARCHAR(100) );一个订单对应多个商品明细查询每个订单下的商品数量SELECT o.id, COUNT(oi.id) AS item_count FROM orders o LEFT JOIN order_items oi ON o.order_id oi.order_id GROUP BY o.id;这时count(oi.id)是安全的因为oi.id为主键不会重复。但如果你写成count(oi.product_name)如果product_name为 NULL则该行不计入总数。如果 LEFT JOIN 右侧没有匹配行oi.product_name为 NULL不会计入。如果右侧有多个匹配行每一行都计入存在重复计数问题。而count(*)在 LEFT JOIN 下会把“左侧存在但没有匹配明细”的订单也统计成 1这通常不是你想要的结果。所以写多表 JOIN 的统计 SQL 时优先使用“主表的主键列或明确非 NULL 的列”做 count不要无脑用count(*)。3. 本地实测用 EXPLAIN 看执行计划这一节我们不看纸面理论直接建表、造数据、看执行计划。你们也可以在自己的 MySQL 上跑一遍。3.1 造一张 100 万行的测试表CREATE TABLE test_count ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT, email VARCHAR(100), KEY idx_age (age), KEY idx_email (email) );插入数据让email列存在部分 NULLINSERT INTO test_count (name, age, email) SELECT CONCAT(user, n), FLOOR(1 RAND() * 80), IF(n % 10 0, NULL, CONCAT(user, n, example.com)) FROM ( SELECT a.N b.N * 10 c.N * 100 d.N * 1000 e.N * 10000 f.N * 100000 1 AS n FROM (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) d, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) e, (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) f ) numbers;这个过程会生成 100 万行数据。具体行数取决于笛卡尔积的组合数可以按需调整。3.2 查看 count(*) 和 count(1) 的执行计划EXPLAIN SELECT COUNT(*) FROM test_count; EXPLAIN SELECT COUNT(1) FROM test_count;在 InnoDB 下两条 SQL 的执行计划几乎一致。优化器会优先选择最小的二级索引idx_age或idx_email遍历而不是全表扫描。如果表上没有任何二级索引才会走主键聚簇索引。3.3 查看 count(email) 的执行计划EXPLAIN SELECT COUNT(email) FROM test_count;email上存在二级索引idx_email所以优化器会通过idx_email扫描并判断email是否非 NULL。和 count(*) 的区别在于count(*) 遍历索引页只数行数。count(email) 遍历索引页还需要额外判断 email 列值是否为 NULL。所以对“非空列”建索引可以显著加速count(列名)的查询。3.4 实际执行时间对比在数据量 100 万行、二级索引都存在的情况下可以分别执行SELECT COUNT(*) FROM test_count; SELECT COUNT(1) FROM test_count; SELECT COUNT(email) FROM test_count;结论通常是count(*) 和 count(1) 耗时几乎相同。count(email) 如果 email 列 NULL 比例较高速度可能略快也可能略慢取决于索引体积和判断成本。差距在百万级数据量下并不明显真正的性能瓶颈通常来自 WHERE 条件筛选。注意实际数字建议以本机环境为准不同 MySQL 版本、内存、磁盘类型都会影响最终耗时。不要背网上的“快 20%”“慢 30%”这类结论。4. InnoDB 为什么不能直接返回 count(*)面试官经常会追问MySQL 的 count(*) 为什么不能像查出缓存一样立即返回答案在于 InnoDB 的 MVCC 机制。InnoDB 默认隔离级别是 REPEATABLE READ事务隔离要求不同的连接在不同时刻看到的行数可能不同。例如连接 A 开启事务执行 count(*)看到 100 行。连接 B 插入 1 行并提交。连接 A 再次执行 count(*)仍然看到 100 行。这就意味着 InnoDB 无法像 MyISAM 那样在表元数据里直接存一个“精确行数”字段并随时返回。它必须根据当前事务的可见版本逐行判断哪些数据对当前事务可见。所以 InnoDB 的 count() 是实时遍历统计的没有 O(1) 的捷径。这也是为什么大表 count() 会比较慢。5. 常见性能优化手段面试官问完区别大概率还会问“大表 count 太慢怎么办”建议按下面的顺序回答。5.1 最小二级索引优化这是 MySQL 优化器自动做的不需要人为干预。二级索引只存储索引列和主键值通常比聚簇索引小很多。MySQL 会优先选择最小的可用索引来扫描。所以如果你经常需要 count可以考虑建一个小的二级索引比如ALTER TABLE test_count ADD KEY idx_count (age);之后count(*)很可能走idx_count而不是主键聚簇索引。5.2 使用近似值对超大表如果业务不要求精确值可以使用SHOW TABLE STATUS或information_schema.tables里的行数估算值SELECT TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA test_db AND TABLE_NAME test_count;注意TABLE_ROWS是估算值不精确只能用于监控展示、分页计算等非强一致场景。5.3 使用 Redis 等外部计数器对写入频繁且需要精确计数的场景可以在业务层维护一个计数器插入时 incr删除时 decr定期用 count(*) 校正这种方案不是 MySQL 原生方案但在高并发业务中非常常见。5.4 使用汇总表对实时性要求不高的报表场景可以每隔一段时间把 count 结果写入汇总表查询时直接读取汇总表。5.5 合理使用条件索引如果 count 经常带 WHERE 条件比如 “统计 status1 的行数”可以考虑在 status 列上建索引让优化器快速定位符合条件的索引页。不要直接对整表 count 优化先看 WHERE 条件能不能走索引。6. count 在不同存储引擎下的差异这个知识点面试也爱考。6.1 MyISAMMyISAM 存储引擎会把表的总行数单独存储。不带 WHERE 条件的count(*)可以直接返回速度极快。但 MyISAM 不支持事务写入时锁表现在大部分业务已经不再使用。面试问到 MyISAM 时这样说就可以“MyISAM 将表的总行数存储在表的元数据中所以不带条件的 count(*) 能直接返回但 MyISAM 不支持事务和行级锁在并发写入场景下不适合使用。”6.2 InnoDBInnoDB 因为支持事务和 MVCC无法直接存储精确总行数。必须实时扫描。但这也不全是缺点InnoDB 的 count(*) 支持带 WHERE 条件的统计并且可以利用二级索引优化。7. 高频追问count(distinct 列)面试官问完 count 的三种写法后大概率会再加一句“那 count(distinct 列) 呢”count(distinct 列名)的语义是统计该列去重后的非 NULL 值数量。SELECT COUNT(DISTINCT email) FROM test_count;它会先对 email 列做去重然后统计非 NULL 值。执行计划中通常会出现临时表或 filesort。如果数据量大count(distinct) 会非常慢因为它需要把去重中间结果放到内存或磁盘临时表中。优化手段在列上建索引减少去重比较的数据量。对高基数列考虑用近似去重算法如 HyperLogLog做预统计。对低基数列可以考虑用 group by 子查询优化。8. 开发中的 count 最佳实践8.1 尽量用 count(*)如果你要的是“总行数”不管那一列是否为 NULL都建议写count(*)。MySQL 官方文档也推荐在“只需要行数”的场景下使用count(*)因为优化器对它的处理最成熟。8.2 count(1) 不是灵丹妙药有些老程序员习惯写count(1)认为它更快。在 MySQL 中这个优化早已被优化器抹平没有实际收益。面试时可以强调“count(*) 和 count(1) 语义相同性能相同选择哪一个取决于团队规范。不用为了性能刻意写 count(1)。”8.3 要统计非空值时用 count(列名)只有业务明确需要“某列非 NULL 的数量”时才用 count(列名)。注意与WHERE 列 IS NOT NULL的结果对比SELECT COUNT(column_name) FROM table_name; -- 等价于 SELECT COUNT(*) FROM table_name WHERE column_name IS NOT NULL;两者结果一致但前者写法更简洁。8.4 避免在大表上频繁 count大表 count(*) 会扫描索引页CPU/IO 开销都不低。业务页面上的“总记录数”可以考虑缓存或汇总表。分页场景不要每次请求都 count 一次可以使用缓存 定期失效策略。8.5 LEFT JOIN 场景注意选择 count 字段前面已经提到LEFT JOIN 下 count(*) 会把无匹配行的左表记录也统计进去。正确做法是 count 右表的主键列SELECT o.id, COUNT(oi.id) AS item_count FROM orders o LEFT JOIN order_items oi ON o.id oi.order_id GROUP BY o.id;这样没有明细的订单会返回 0而不是 1。9. 面试回答模板直接背这一段面试时按顺序说在 MySQL InnoDB 中count() 和 count(1) 本质上没有区别都用来统计所有行数不会忽略任何列的值。count(1) 只是对每一行放一个常量 1优化器会将其改写成与 count() 等价的形式。count(列名) 的语义不同它只统计该列不为 NULL 的行数。从执行上看InnoDB 由于 MVCC 和事务隔离机制无法像 MyISAM 一样直接存储总行数必须实时扫描。count(*) 会优先选择最小的二级索引来遍历count(列名) 如果列上有二级索引也会走二级索引但还需要判断 NULL。开发中建议需要统计总行数时用 count(*)需要统计某一列非空数量时用 count(列名)。count(1) 没有额外性能收益更多是团队编码习惯问题。”这套回答覆盖了语义、性能、原理、实践建议四个维度比只背结论要完整得多。10. 小结count(*)、count(1)、count(列名)的差距不是性能上的而是语义上的。真正面试翻车的人不是不知道 count(列名) 忽略 NULL而是说不清 InnoDB 为什么不缓存总行数、执行计划如何优化、LEFT JOIN 下怎么选字段。最后留一个思考题如果改成SELECT COUNT(*) FROM test_count WHERE age 50在只有idx_age索引的情况下执行效率如何欢迎评论区交流。