MySQL中count(*)深度解析与性能优化实践案例
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、测试环境搭建</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1.1 建表语句与存储过程</a></li></ul><li><a href="#_label1">二、重新认识count(*)：4个核心疑问解答</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_1">2.1 count(a)与count(*)的区别：是否统计null？</a></li><li><a href="#_lab2_1_2">2.2 MyISAM与InnoDB：count(*)性能天差地别？</a></li><li><a href="#_lab2_1_3">2.3 MySQL 5.7.18+：count(*)为何优先选二级索引？</a></li><li><a href="#_lab2_1_4">2.4 count(1)比count(*)快？谣言！</a></li></ul><li><a href="#_label2">三、3种方法加快count()：从“慢统计”到“快查询”</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_5">3.1 场景1：仅需“大概数据量”→ show table status</a></li><li><a href="#_lab2_2_6">3.2 场景2：需高性能+可接受少量延迟→ Redis计数器</a></li><ul class="third_class_ul"><li><a href="#_label3_2_6_0">步骤1：初始化计数器</a></li><li><a href="#_label3_2_6_1">步骤2：增删数据时同步更新计数器</a></li><li><a href="#_label3_2_6_2">步骤3：查询行数时读Redis</a></li></ul><li><a href="#_lab2_2_7">3.3 场景3：需强一致性→ 计数表（InnoDB）</a></li><ul class="third_class_ul"><li><a href="#_label3_2_7_3">步骤1：创建计数表</a></li><li><a href="#_label3_2_7_4">步骤2：事务中同步增删与计数</a></li><li><a href="#_label3_2_7_5">步骤3：查询行数时读计数表</a></li></ul></ul><li><a href="#_label3">四、总结：count(*)使用与优化指南</a></li><ul class="second_class_ul"></ul></ul></div><p>在日常MySQL开发中，<code>count()</code>函数是统计数据行数的常用工具，但很多开发者对<code>count(*)</code>、<code>count(字段)</code>、<code>count(1)</code>的区别一知半解，也常困惑于不同存储引擎下<code>count(*)</code>的性能差异。本文将结合实际测试案例，从原理到实践，带你彻底搞懂<code>count(*)</code>，并分享3种高效优化<code>count()</code>性能的方案。</p><p class="maodian"><a name="_label0"></a></p><h2>一、测试环境搭建</h2>
<p>为了让所有结论有数据支撑，我们先搭建统一的测试环境——创建3张不同配置的表（InnoDB带索引、MyISAM、InnoDB无二级索引），并插入测试数据。</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1.1 建表语句与存储过程</h3>
<div class="jb51code"><pre class="brush:sql;">-- 切换数据库（需提前创建martin库：create database martin;）
use martin;
-- 1. 创建InnoDB引擎表t1（含主键+二级索引）
drop table if exists t1;
CREATE TABLE `t1` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,-- 允许为null，用于测试count(字段)
`b` int NOT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`), -- 聚簇索引
KEY `idx_a` (`a`), -- 二级索引
KEY `idx_b` (`b`) -- 二级索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 2. 创建批量插入10000条数据的存储过程
drop procedure if exists insert_t1;
delimiter ;;-- 临时修改语句结束符，避免与存储过程中的;冲突
create procedure insert_t1()
begin
declare i int;
set i=1;
while(i<=10000)do
insert into t1(a,b,c,d) values(i,i,i,i);-- 初始数据a无null
set i=i+1;
end while;
end;;
delimiter ;-- 恢复语句结束符
-- 3. 执行存储过程+补充1条a为null的数据
call insert_t1();
insert into t1(a,b,c,d) values (null,10001,10001,10001),(10002,10002,10002,10002);
-- 此时t1共10002行数据，其中1行a为null
-- 4. 创建MyISAM引擎表t2（结构与t1一致，用于对比引擎差异）
drop table if exists t2;
create table t2 like t1;
alter table t2 engine = MyISAM;-- 修改引擎
insert into t2 select * from t1;-- 同步t1数据
-- 5. 创建无二级索引的InnoDB表t3（用于测试索引对count(*)的影响）
drop table if exists t3;
CREATE TABLE `t3` (
`id` int NOT NULL AUTO_INCREMENT,
`a` int DEFAULT NULL,
`b` int NOT NULL,
`c` int DEFAULT NULL,
`d` int DEFAULT NULL,
PRIMARY KEY (`id`)-- 仅聚簇索引
) ENGINE=InnoDB CHARSET=utf8mb4;
insert into t3 select * from t1;</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>二、重新认识count(*)：4个核心疑问解答</h2>
<p class="maodian"><a name="_lab2_1_1"></a></p><h3>2.1 count(a)与count(*)的区别：是否统计null？</h3>
<p>很多人误以为<code>count(字段)</code>和<code>count(*)</code>功能一致，实则关键差异在<strong>是否统计字段为null的行</strong>：</p>
<ul><li><code>count(a)</code>：仅统计<code>a</code>字段不为null的行（若字段有null值，会过滤掉）；</li><li><code>count(*)</code>：统计表中所有行（无论字段是否为null，包括全null的行）。</li></ul>
<p><strong>测试验证</strong>（基于t1表，10002行，1行a为null）：</p>
<div class="jb51code"><pre class="brush:sql;">-- 结果为10001（排除a为null的1行）
select count(a) from t1;
-- 结果为10002（统计所有行）
select count(*) from t1;</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025123009242418.png" /></p>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>2.2 MyISAM与InnoDB：count(*)性能天差地别？</h3>
<p>两种主流引擎对<code>count(*)</code>的处理逻辑完全不同，导致性能差异显著：</p>
<ul><li><strong>MyISAM</strong>：会将表的<strong>总行数存储在磁盘</strong>（仅针对无where子句、无其他列检索的场景），查询时直接读取该值，速度极快；</li><li><strong>InnoDB</strong>：需<strong>临时扫描表/索引</strong>计算行数（因InnoDB支持事务，行数据可能被锁定或版本不同，无法缓存固定行数），速度较慢。</li></ul>
<p><strong>执行计划对比</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 1. MyISAM表t2的count(*)：Extra为Select tables optimized away，核心含义是：MySQL 通过优化逻辑，直接从索引中获取了所需的全部数据，完全无需访问实际的表，因此 “跳过了表的访问步骤”
explain select count(*) from t2;
-- 2. InnoDB表t1的count(*)：type为index，表示 “全索引扫描”，而非 “全表扫描”，Extra为Using index，表示查询所需的所有信息都能从索引中直接获取，完全不需要回表读取行数据
explain select count(*) from t1;</pre></div>
<p>从执行计划可见，MyISAM直接复用预存的行数，而InnoDB需扫描索引计算。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025123009242595.png" /></p>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2.3 MySQL 5.7.18+：count(*)为何优先选二级索引？</h3>
<p>在MySQL 5.7.18之前，InnoDB的<code>count(*)</code>默认扫描<strong>聚簇索引</strong>（主键索引）；而5.7.18之后，优化器会优先选择<strong>最小的二级索引</strong>，原因是：</p>
<ul><li>聚簇索引的叶子节点存储<strong>整行数据</strong>，体积较大；</li><li>二级索引的叶子节点仅存储<strong>主键值</strong>，体积远小于聚簇索引，扫描成本更低。</li></ul>
<p>若表无二级索引（如t3表），则仍会扫描聚簇索引。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025123009242412.png" /></p>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>2.4 count(1)比count(*)快？谣言！</h3>
<p>很多开发者认为<code>count(1)</code>性能优于<code>count(*)</code>，实则两者<strong>结果一致、性能无差异</strong>：</p>
<ul><li><code>count(1)</code>：将“1”视为恒真表达式，统计所有行（与<code>count(*)</code>逻辑一致）；</li><li><code>count(*)</code>：MySQL对其有专门优化，不会展开为所有字段，而是直接统计行数。</li></ul>
<p><strong>执行计划验证</strong>：</p>
<div class="jb51code"><pre class="brush:sql;">-- 两条语句的执行计划完全一致（均扫描二级索引，rows=10002）
explain select count(1) from t1;
explain select count(*) from t1;
</pre></div>
<p>结论：无需纠结<code>count(1)</code>和<code>count(*)</code>，优先用<code>count(*)</code>更符合语义。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025123009242574.png" /></p>
<p class="maodian"><a name="_label2"></a></p><h2>三、3种方法加快count()：从“慢统计”到“快查询”</h2>
<p>当表数据量达百万/千万级时，InnoDB的<code>count(*)</code>会明显变慢，以下3种方案可根据场景选择：</p>
<p class="maodian"><a name="_lab2_2_5"></a></p><h3>3.1 场景1：仅需“大概数据量”→ show table status</h3>
<p>若业务无需精确行数（如后台数据概览），可使用<code>show table status</code>，它直接读取MySQL的表元数据，无需扫描表：</p>
<div class="jb51code"><pre class="brush:sql;">-- 结果中Rows字段即为表的大概行数（t1表约10002行）
show table status like 't1';
</pre></div>
<p><strong>优缺点</strong>：速度极快，但数据可能有误差（误差通常在10%以内）。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025123009242595.png" /></p>
<p class="maodian"><a name="_lab2_2_6"></a></p><h3>3.2 场景2：需高性能+可接受少量延迟→ Redis计数器</h3>
<p>利用Redis的原子操作（INCR/DECR）维护表行数，查询时直接读Redis，避免扫描MySQL表：</p>
<p class="maodian"><a name="_label3_2_6_0"></a></p><h4>步骤1：初始化计数器</h4>
<div class="jb51code"><pre class="brush:sql;">-- 1. 先查询MySQL表的初始行数
select count(*) from t1;-- 结果10002
-- 2. 将初始值写入Redis（key为t1_count，值为10002）
set t1_count 10002;</pre></div>
<p class="maodian"><a name="_label3_2_6_1"></a></p><h4>步骤2：增删数据时同步更新计数器</h4>
<div class="jb51code"><pre class="brush:sql;">-- 插入数据时，Redis计数器+1
insert into t1(a,b,c,d) values (10003,10003,10003,10003);
INCR t1_count;-- Redis命令
-- 删除数据时，Redis计数器-1
delete from t1 where id=10003;
DECR t1_count;-- Redis命令</pre></div>
<p class="maodian"><a name="_label3_2_6_2"></a></p><h4>步骤3：查询行数时读Redis</h4>
<div class="jb51code"><pre class="brush:sql;">-- 直接获取Redis中的值，耗时微秒级
get t1_count;
</pre></div>
<p><strong>优缺点</strong>：性能极高，但存在“Redis与MySQL数据不一致”风险（如插入MySQL成功但Redis更新失败），适合对一致性要求不严格的场景。</p>
<p class="maodian"><a name="_lab2_2_7"></a></p><h3>3.3 场景3：需强一致性→ 计数表（InnoDB）</h3>
<p>用一张InnoDB表专门存储行数，通过<strong>事务</strong>保证“数据操作”与“计数更新”的原子性，彻底解决一致性问题：</p>
<p class="maodian"><a name="_label3_2_7_3"></a></p><h4>步骤1：创建计数表</h4>
<div class="jb51code"><pre class="brush:sql;">-- 创建count_t1表，仅存储t1的行数
create table count_t1 (
table_name varchar(50) not null primary key,-- 表名（可扩展到多表）
count int not null default 0-- 行数
);
-- 初始化t1的计数
insert into count_t1(table_name, count) values ('t1', (select count(*) from t1));</pre></div>
<p class="maodian"><a name="_label3_2_7_4"></a></p><h4>步骤2：事务中同步增删与计数</h4>
<div class="jb51code"><pre class="brush:sql;">-- 插入数据时，在同一事务中更新计数
begin;-- 开启事务
insert into t1(a,b,c,d) values (10003,10003,10003,10003);
update count_t1 set count=count+1 where table_name='t1';
commit;-- 提交事务（要么都成功，要么都失败）
-- 删除数据时同理
begin;
delete from t1 where id=10003;
update count_t1 set count=count-1 where table_name='t1';
commit;</pre></div>
<p class="maodian"><a name="_label3_2_7_5"></a></p><h4>步骤3：查询行数时读计数表</h4>
<div class="jb51code"><pre class="brush:sql;">-- 直接查询计数表，仅扫描1行，速度极快
select count from count_t1 where table_name='t1';
</pre></div>
<p><strong>优缺点</strong>：强一致性、性能好，但需额外维护计数表，适合对数据一致性要求高的核心业务（如订单数统计）。</p>
<p class="maodian"><a name="_label3"></a></p><h2>四、总结：count(*)使用与优化指南</h2>
<ol><li><strong>基础选择</strong>：统计所有行用<code>count(*)</code>，统计非null字段用<code>count(字段)</code>，无需用<code>count(1)</code>；</li><li><strong>引擎差异</strong>：MyISAM适合静态表（行数不变），InnoDB需通过索引优化<code>count(*)</code>；</li><li><strong>优化方案</strong>：<ul><li>概览数据：<code>show table status</code>；</li><li>高性能低一致性：Redis计数器；</li><li>强一致性：InnoDB计数表。</li></ul></li></ol>
<p>掌握以上知识，可避免在MySQL计数场景中踩坑，让统计逻辑既高效又可靠。</p>
頁:
[1]