InnoDB表统计效率count(主键)

作者：俏君woo_267 | 来源：互联网 | 2023-06-02 19:56

统计一个表的数据量是经常遇到的需求，但是不同的表设计及不同的写法，统计性能差别会有较大的差异，下面就简单通过实验进行测试(大家测试的时候注意缓存的情况，否则影响测试结果）。1、准

统计一个表的数据量是经常遇到的需求，但是不同的表设计及不同的写法，统计性能差别会有较大的差异，下面就简单通过实验进行测试(大家测试的时候注意缓存的情况，否则影响测试结果）。

1、准备工作

为了后续测试工作的进行，先准备几张用于测试的表及数据，为了使测试数据具有参考意义，建议测试表的数据量大一点，以免查询时间太小，因此，可以继续使用之前常用的连续数生成大法，如下：

/* 创建连续数表 */
CREATE TABLE nums(id INT primary key);
/* 生成连续数的存储过程,优化过后的 */
DELIMITER $$
CREATE PROCEDURE `sp_createNum`(cnt INT )
BEGIN
DECLARE i INT DEFAULT 1;
TRUNCATE TABLE nums;
INSERT INTO nums SELECT i;
WHILE i BEGIN
INSERT INTO nums SELECT id + i FROM nums WHERE id + i<=cnt;
SET i = i*2;
END;
END WHILE;
END$$
DELIMITER ;

生成数据，本次准备生成1kw条记录

/* 调用存储过程 */
mysql> call sp_createNum(10000000);
Query OK, 1611392 rows affected (32.07 sec)

如果逐条循环，那时间相当长，大家可以自行测试，参考链接效率提升16800倍的连续整数生成方法

1.1 创建innodb表

生成3张表innodb表，如下：

nums_1表只有字符串主键字段

/* 生成只有一个字符串类型字段主键的表nums_1 */
mysql> create table nums_1 (p1 varchar(32) primary key ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
/* 导入数据,将id通过md5函数转换为字符串 */
mysql> insert into nums_1 select md5(id) from nums;
Query OK, 10000000 rows affected (1 min 12.63 sec)
Records: 10000000 Duplicates: 0 Warnings: 0

nums_2表有5个字段，其中主键为字符串类型字段的p1，其他字段为整型的id，非空的c1,可为空的c2,可为空的c3。

其中c1,c2字段内容完全一致，差别是字段约束不一样（c1不可为空，c2可为空），c3与c1,c2的差别在于c1中aa开头的值在c3中为null,其他内容一样。

/* 创建表nums_2 */
mysql> create table nums_2(p1 varchar(32) primary key ,id int ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=innodb;
Query OK, 0 rows affected (1.03 sec)
/*导入数据 */
mysql> insert into nums_2(id,p1,c1,c2,c3) select id,md5(id),left(md5(id),10),left(md5(id),10),if(,left(md5(id),10) like 'aa%',null,,left(md5(id),10)) from nums;
Query OK, 10000000 rows affected (5 min 6.68 sec)
Records: 10000000 Duplicates: 0 Warnings: 0

nums_3表的内容与nums_2完全一样，区别在于主键字段不一样，c3表为整型的id

/* 创建表nums_3 */
mysql> create table nums_3(p1 varchar(32) ,id int primary key ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
/* 因为内容完全一致，直接从nums_2 中导入 */
mysql> insert into nums_3 select * from nums_2;
Query OK, 10000000 rows affected (3 min 18.81 sec)
Records: 10000000 Duplicates: 0 Warnings: 0

1.2 创建MyISAM引擎表

再创建一张MyISAM的表，表结构及内容均与nums_2也一致，只是引擎为MyISAM。

/* 创建MyISAM引擎的nums_4表*/
mysql> create table nums_4(p1 varchar(32) not null primary key ,id int ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
/* 直接从nums_2表导入数据 */
mysql> insert into nums_4 select * from nums_2;
Query OK, 10000000 rows affected (3 min 16.78 sec)
Records: 10000000 Duplicates: 0 Warnings: 0

2、查询一张表数据量的方法

查询一张表的数据量有如下几种：

查询大致数据量，可以查统计信息，2.1中会介绍具体方法

精确查找数据量，则可以通过count(主键字段），count(*), count(1) [这里的1可以替换为任意常量]

2.1 非精确查询

如果只是查一张表大致有多少数据，尤其是很大的表只是查询其表属于什么量级的（百万、千万还是上亿条），可以直接查询统计信息，查询方式有如下几种：

查询索引信息，其中Cardinality 为大致数据量（查看主键PRIMARY行的值，如果为多列的复合主键，则查看最后一列的Cardinality 值）

查看表状态，其中Rows为大致数据量

直接查看STATISTICS或TABLES表，内容与查看索引信息或表状态类似，其中TABLE_ROWS的内容为大致的数据量

注意：

innodb引起的表通过以上3种方式均可查询对应表的大致数据量，且结果相同，因为均是取自相同的统计信息

MyISAM表的结果是精确值（表数据量，不包含其他字段）

2.2 精确查找

因为2.1中innodb的表查询的结果都是统计值，非准备值，实际工作中大多数情况下需要统计精确值，那么查询精确值的方法有如下几种，且所有引擎的表都适用。

count(主键）

mysql> select count(p1) from nums_2;
+-----------+
| count(p1) |
+-----------+
| 10000000 |
+-----------+
1 row in set (1.60 sec)

count(1)

其中的1可以是任意常量，例如 count(2),count('a‘）等

mysql> select count(1) from nums_2;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)

count(*)

mysql> select count(*) from nums_2;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.52 sec)

3、 count的性能对比

对比 count(主键） count(1) count(*) count（非空字段） count（可为空字段）性能对比

3.1 MyISAM引擎表

3.1.1 查询整张表数据量

如果想精确查询一张MyISAM表的数据量，使用 count(主键） count(1) count(*) 效率均一致，直接查出准确结果，耗时几乎为0s

mysql> select count(p1) from nums_4;
+-----------+
| count(p1) |
+-----------+
| 10000000 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(1) from nums_4;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from nums_4;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)

执行计划也均一致，可以看出没有通过主键或其他索引扫描的方式统计

小结：

MyISAM的方法查整表数据量效率情况为 count(主键）= count(1) = count(*)

3.1.2 查询部分数据

查询部分数据的时候则无法直接从统计信息获取，因此耗时情况大致如下：

mysql> select count(p1) from nums_4 where p1 like 'aa%';
+-----------+
| count(p1) |
+-----------+
| 39208 |
+-----------+
1 row in set (0.14 sec)
mysql> select count(1) from nums_4 where p1 like 'aa%';
+----------+
| count(1) |
+----------+
| 39208 |
+----------+
1 row in set (0.13 sec)
mysql> select count(*) from nums_4 where p1 like 'aa%';
+----------+
| count(*) |
+----------+
| 39208 |
+----------+
1 row in set (0.13 sec)

执行计划其实均一样：

小结： MyISAM引擎表统计部分数据的时候直接得出数据量，也许扫描数据进行统计，几种写法效率相近。

3.2 innodb引擎表

innodb引擎因为要支持MVCC，因此不能整表数据量持久化保存，每次查询均需遍历统计，但是不同的写法，查询效率是有差别的，后面将进行不同维度进行对比。

3.2.1 不同写法的性能对比

通过 count(主键),count(1) , count(*) 对比查询效率

mysql> select count(p1) from nums_2 ;
+-----------+
| count(p1) |
+-----------+
| 10000000 |
+-----------+
1 row in set (1.68 sec)
mysql> select count(1) from nums_2 ;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.37 sec)
mysql> select count(*) from nums_2 ;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.38 sec)

简单的对比发现，查询性能结果为 count(主键)

但是查看执行计划都是如下情况

但是查询效率不一样，原因在于统计的方式不一样，如下：

count(主键)：innodb引擎根据对应的索引遍历整张表，把每一行的主键值都取出来，返回给 server 层。server 层拿到主键字段后，判断是不为空的（此处其实可以优化），就按行累加。

count(1)：也是遍历整张表，因为每行的结果都是1（非空），所以可以直接计数，无需判断是否为空。

count(*): innodb引擎做了优化处理的，此种方式和count(1)类似，直接按行累计统计

3.2.2 主键字段类型不同性能对比
nums_2与nums_3内容相同，区别在于num_3的主键字段是整型的id字段，现在对比主键字段不同时查询性能的差别，
mysql> select /* SQL_NO_CACHE */count(1) from nums_2;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (2.02 sec)
mysql> select /* SQL_NO_CACHE */count(1) from nums_3;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.69 sec)
测试发现，相同内容数据的表表主键不同，性能存在差异，且，查询时主键（索引）字段类型小的时候查询效率更好。
注：如果nums_2的id字段上添加索引后，会发现查询会走id的索引，原因在于主键索引（聚集索引）的类型是varchar(32),而id是int,索引的大小不一样，走整型的索引IO开销会少。
因此，建议MySQL的主键使用自增id作为主键（优势不仅在数据统计上，有机会在讲解）。

3.2.3 表大小不同的对比
准备工作中的nums_1 与nums_3差别在于主键都是整型的id 但是nums_3的字段更多，也就是说表更大，查询效率对比如下：
mysql> select /* SQL_NO_CACHE */count(1) from nums_1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.61 sec)
mysql> select /* SQL_NO_CACHE */count(1) from nums_3;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.67 sec)
查询时间仅供参考，取决于机器性能。
由此可见表大小不同，查询效率也不同，表越小查询效率越高。

3.2.4 count(普通字段）
因为nums_3表的c2字段允许为空，但是内容均不为空，c3字段允许为空，但是存在内容为空的情况。现在将nums_3表的c2,c3字段分别统计，查看结果（先添加索引，提高查询性能）
mysql> select count(c2) from nums_3 ;
+-----------+
| count(c2) |
+-----------+
| 10000000 |
+-----------+
1 row in set (1.69 sec)
mysql> select count(c3) from nums_3 ;
+-----------+
| count(c3) |
+-----------+
| 9960792 |
+-----------+
1 row in set (1.73 sec)
因为c3字段有存在null的值，索引统计c3行数的时候会忽略null值的行。

4、总结
以上通过对比MyISAM引擎及InnoDB引擎表通过不同写法的统计效率进行对比，可以得到如下结论：

MyISAM表统计整表行数可以直接取出，效率最高，但是MyISAM表不支持事务

InnoDB表统计效率 count(主键)

MySQL建议设置自增字段类型的主键

表大小越小，查询统计效率越高

统计一个表的数据量是经常遇到的需求，但是不同的表设计及不同的写法，统计性能差别会有较大的差异，下面就简单通过实验进行测试(大家测试的时候注意缓存的情况，否则影响测试结果）。

1、准备工作
为了后续测试工作的进行，先准备几张用于测试的表及数据，为了使测试数据具有参考意义，建议测试表的数据量大一点，以免查询时间太小，因此，可以继续使用之前常用的连续数生成大法，如下：
/* 创建连续数表 */
CREATE TABLE nums(id INT primary key);
/* 生成连续数的存储过程,优化过后的 */
DELIMITER $$
CREATE PROCEDURE `sp_createNum`(cnt INT )
BEGIN
DECLARE i INT DEFAULT 1;
TRUNCATE TABLE nums;
INSERT INTO nums SELECT i;
WHILE i BEGIN
INSERT INTO nums SELECT id + i FROM nums WHERE id + i<=cnt;
SET i = i*2;
END;
END WHILE;
END$$
DELIMITER ;
生成数据，本次准备生成1kw条记录
/* 调用存储过程 */
mysql> call sp_createNum(10000000);
Query OK, 1611392 rows affected (32.07 sec)
如果逐条循环，那时间相当长，大家可以自行测试，参考链接效率提升16800倍的连续整数生成方法

1.1 创建innodb表
生成3张表innodb表，如下：
nums_1表只有字符串主键字段
/* 生成只有一个字符串类型字段主键的表nums_1 */
mysql> create table nums_1 (p1 varchar(32) primary key ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
/* 导入数据,将id通过md5函数转换为字符串 */
mysql> insert into nums_1 select md5(id) from nums;
Query OK, 10000000 rows affected (1 min 12.63 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
nums_2表有5个字段，其中主键为字符串类型字段的p1，其他字段为整型的id，非空的c1,可为空的c2,可为空的c3。
其中c1,c2字段内容完全一致，差别是字段约束不一样（c1不可为空，c2可为空），c3与c1,c2的差别在于c1中aa开头的值在c3中为null,其他内容一样。
/* 创建表nums_2 */
mysql> create table nums_2(p1 varchar(32) primary key ,id int ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=innodb;
Query OK, 0 rows affected (1.03 sec)
/*导入数据 */
mysql> insert into nums_2(id,p1,c1,c2,c3) select id,md5(id),left(md5(id),10),left(md5(id),10),if(,left(md5(id),10) like 'aa%',null,,left(md5(id),10)) from nums;
Query OK, 10000000 rows affected (5 min 6.68 sec)
Records: 10000000 Duplicates: 0 Warnings: 0
nums_3表的内容与nums_2完全一样，区别在于主键字段不一样，c3表为整型的id
/* 创建表nums_3 */
mysql> create table nums_3(p1 varchar(32) ,id int primary key ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=innodb;
Query OK, 0 rows affected (0.01 sec)
/* 因为内容完全一致，直接从nums_2 中导入 */
mysql> insert into nums_3 select * from nums_2;
Query OK, 10000000 rows affected (3 min 18.81 sec)
Records: 10000000 Duplicates: 0 Warnings: 0

1.2 创建MyISAM引擎表
再创建一张MyISAM的表，表结构及内容均与nums_2也一致，只是引擎为MyISAM。
/* 创建MyISAM引擎的nums_4表*/
mysql> create table nums_4(p1 varchar(32) not null primary key ,id int ,c1 varchar(10) not null, c2 varchar(10),c3 varchar(10)) engine=MyISAM;
Query OK, 0 rows affected (0.00 sec)
/* 直接从nums_2表导入数据 */
mysql> insert into nums_4 select * from nums_2;
Query OK, 10000000 rows affected (3 min 16.78 sec)
Records: 10000000 Duplicates: 0 Warnings: 0

2、查询一张表数据量的方法
查询一张表的数据量有如下几种：
查询大致数据量，可以查统计信息，2.1中会介绍具体方法
精确查找数据量，则可以通过count(主键字段），count(*), count(1) [这里的1可以替换为任意常量]

2.1 非精确查询
如果只是查一张表大致有多少数据，尤其是很大的表只是查询其表属于什么量级的（百万、千万还是上亿条），可以直接查询统计信息，查询方式有如下几种：
查询索引信息，其中Cardinality 为大致数据量（查看主键PRIMARY行的值，如果为多列的复合主键，则查看最后一列的Cardinality 值）
mysql> show index from nums_2;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| nums_2 | 0 | PRIMARY | 1 | p1 | A | 9936693 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
查看表状态，其中Rows为大致数据量
mysql> show table status like 'nums_2';
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| nums_2 | InnoDB | 10 | Dynamic | 9936693 | 111 | 1105182720 | 0 | 2250178560 | 4194304 | NULL | 2020-04-04 19:31:34 | NULL | NULL | utf8_general_ci | NULL | | |
+--------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)
直接查看STATISTICS或TABLES表，内容与查看索引信息或表状态类似，其中TABLE_ROWS的内容为大致的数据量
mysql> select * from information_schema.tables where table_schema='testdb' and table_name like 'nums_2';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
| def | testdb | nums_2 | BASE TABLE | InnoDB | 10 | Dynamic | 9936693 | 111 | 1105182720 | 0 | 2250178560 | 4194304 | NULL | 2020-04-04 19:31:34 | NULL | NULL | utf8_general_ci | NULL | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------------+
1 row in set (0.00 sec)
注意：

innodb引起的表通过以上3种方式均可查询对应表的大致数据量，且结果相同，因为均是取自相同的统计信息

MyISAM表的结果是精确值（表数据量，不包含其他字段）

mysql> select * from information_schema.tables where table_schema='testdb' and table_name like 'nums_4';
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE | ENGINE | VERSION | ROW_FORMAT | TABLE_ROWS | AVG_ROW_LENGTH | DATA_LENGTH | MAX_DATA_LENGTH | INDEX_LENGTH | DATA_FREE | AUTO_INCREMENT | CREATE_TIME | UPDATE_TIME | CHECK_TIME | TABLE_COLLATION | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------+
| def | testdb | nums_4 | BASE TABLE | MyISAM | 10 | Dynamic | 10000000 | 75 | 759686336 | 281474976710655 | 854995968 | 0 | NULL | 2020-04-04 19:20:23 | 2020-04-04 19:21:45 | 2020-04-04 19:23:45 | utf8_general_ci | NULL | | |
+---------------+--------------+------------+------------+--------+---------+------------+------------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------------+
1 row in set (0.00 sec)

2.2 精确查找
因为2.1中innodb的表查询的结果都是统计值，非准备值，实际工作中大多数情况下需要统计精确值，那么查询精确值的方法有如下几种，且所有引擎的表都适用。
count(主键）
mysql> select count(p1) from nums_2;
+-----------+
| count(p1) |
+-----------+
| 10000000 |
+-----------+
1 row in set (1.60 sec)
count(1)
其中的1可以是任意常量，例如 count(2),count('a‘）等
mysql> select count(1) from nums_2;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.45 sec)
count(*)
mysql> select count(*) from nums_2;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.52 sec)

3、 count的性能对比
对比 count(主键） count(1) count(*) count（非空字段） count（可为空字段）性能对比

3.1 MyISAM引擎表

3.1.1 查询整张表数据量
如果想精确查询一张MyISAM表的数据量，使用 count(主键） count(1) count(*) 效率均一致，直接查出准确结果，耗时几乎为0s
mysql> select count(p1) from nums_4;
+-----------+
| count(p1) |
+-----------+
| 10000000 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(1) from nums_4;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from nums_4;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (0.00 sec)
执行计划也均一致，可以看出没有通过主键或其他索引扫描的方式统计
mysql> explain select count(*) from nums_4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(p1) from nums_4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(1) from nums_4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
小结：
MyISAM的方法查整表数据量效率情况为 count(主键）= count(1) = count(*)

3.1.2 查询部分数据
查询部分数据的时候则无法直接从统计信息获取，因此耗时情况大致如下：
mysql> select count(p1) from nums_4 where p1 like 'aa%';
+-----------+
| count(p1) |
+-----------+
| 39208 |
+-----------+
1 row in set (0.14 sec)
mysql> select count(1) from nums_4 where p1 like 'aa%';
+----------+
| count(1) |
+----------+
| 39208 |
+----------+
1 row in set (0.13 sec)
mysql> select count(*) from nums_4 where p1 like 'aa%';
+----------+
| count(*) |
+----------+
| 39208 |
+----------+
1 row in set (0.13 sec)
执行计划其实均一样：
mysql> explain select count(1) from nums_4 where p1 like 'aa%';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
| 1 | SIMPLE | nums_4 | NULL | range | PRIMARY | PRIMARY | 98 | NULL | 42603 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+-------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
小结： MyISAM引擎表统计部分数据的时候直接得出数据量，也许扫描数据进行统计，几种写法效率相近。

3.2 innodb引擎表
innodb引擎因为要支持MVCC，因此不能整表数据量持久化保存，每次查询均需遍历统计，但是不同的写法，查询效率是有差别的，后面将进行不同维度进行对比。

3.2.1 不同写法的性能对比
通过 count(主键),count(1) , count(*) 对比查询效率
mysql> select count(p1) from nums_2 ;
+-----------+
| count(p1) |
+-----------+
| 10000000 |
+-----------+
1 row in set (1.68 sec)
mysql> select count(1) from nums_2 ;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.37 sec)
mysql> select count(*) from nums_2 ;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (1.38 sec)
简单的对比发现，查询性能结果为 count(主键)
但是查看执行计划都是如下情况
mysql> explain select count(p1) from nums_2;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | nums_2 | NULL | index | NULL | PRIMARY | 98 | NULL | 9936693 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec
但是查询效率不一样，原因在于统计的方式不一样，如下：

count(主键)：innodb引擎根据对应的索引遍历整张表，把每一行的主键值都取出来，返回给 server 层。server 层拿到主键字段后，判断是不为空的（此处其实可以优化），就按行累加。

count(1)：也是遍历整张表，因为每行的结果都是1（非空），所以可以直接计数，无需判断是否为空。

count(*): innodb引擎做了优化处理的，此种方式和count(1)类似，直接按行累计统计

3.2.2 主键字段类型不同性能对比
nums_2与nums_3内容相同，区别在于num_3的主键字段是整型的id字段，现在对比主键字段不同时查询性能的差别，
mysql> select /* SQL_NO_CACHE */count(1) from nums_2;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (2.02 sec)
mysql> select /* SQL_NO_CACHE */count(1) from nums_3;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.69 sec)
测试发现，相同内容数据的表表主键不同，性能存在差异，且，查询时主键（索引）字段类型小的时候查询效率更好。
注：如果nums_2的id字段上添加索引后，会发现查询会走id的索引，原因在于主键索引（聚集索引）的类型是varchar(32),而id是int,索引的大小不一样，走整型的索引IO开销会少。
因此，建议MySQL的主键使用自增id作为主键（优势不仅在数据统计上，有机会在讲解）。

3.2.3 表大小不同的对比
准备工作中的nums_1 与nums_3差别在于主键都是整型的id 但是nums_3的字段更多，也就是说表更大，查询效率对比如下：
mysql> select /* SQL_NO_CACHE */count(1) from nums_1;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.61 sec)
mysql> select /* SQL_NO_CACHE */count(1) from nums_3;
+----------+
| count(1) |
+----------+
| 10000000 |
+----------+
1 row in set (1.67 sec)
查询时间仅供参考，取决于机器性能。
由此可见表大小不同，查询效率也不同，表越小查询效率越高。

3.2.4 count(普通字段）
因为nums_3表的c2字段允许为空，但是内容均不为空，c3字段允许为空，但是存在内容为空的情况。现在将nums_3表的c2,c3字段分别统计，查看结果（先添加索引，提高查询性能）
mysql> select count(c2) from nums_3 ;
+-----------+
| count(c2) |
+-----------+
| 10000000 |
+-----------+
1 row in set (1.69 sec)
mysql> select count(c3) from nums_3 ;
+-----------+
| count(c3) |
+-----------+
| 9960792 |
+-----------+
1 row in set (1.73 sec)
因为c3字段有存在null的值，索引统计c3行数的时候会忽略null值的行。

4、总结
以上通过对比MyISAM引擎及InnoDB引擎表通过不同写法的统计效率进行对比，可以得到如下结论：

MyISAM表统计整表行数可以直接取出，效率最高，但是MyISAM表不支持事务

InnoDB表统计效率 count(主键)

MySQL建议设置自增字段类型的主键

表大小越小，查询统计效率越高

mysql

缓存

int

key

default

insert

select

char

md5

写下你的评论吧 !

吐个槽吧,看都看了

会员登录 | 用户注册

推荐阅读

default
VScode格式化文档换行或不换行的设置方法

本文介绍了在VScode中设置格式化文档换行或不换行的方法，包括使用插件和修改settings.json文件的内容。详细步骤为：找到settings.json文件，将其中的代码替换为指定的代码。 ... [详细]

蜡笔小新   2023-12-14 17:15:38

char
二叉树层序创建问题的解决方法

本文介绍了解决二叉树层序创建问题的方法。通过使用队列结构体和二叉树结构体，实现了入队和出队操作，并提供了判断队列是否为空的函数。详细介绍了解决该问题的步骤和流程。 ... [详细]

蜡笔小新   2023-12-13 18:20:50

js
VB.NET在线急等问题解决方法，如何统计数据库字段下的数据并显示在文本框里？

本文介绍了一个在线急等问题解决方法，即如何统计数据库中某个字段下的所有数据，并将结果显示在文本框里。作者提到了自己是一个菜鸟，希望能够得到帮助。作者使用的是ACCESS数据库，并且给出了一个例子，希望得到的结果是560。作者还提到自己已经尝试了使用"select sum(字段2) from 表名"的语句，得到的结果是650，但不知道如何得到560。希望能够得到解决方案。 ... [详细]

蜡笔小新   2023-12-13 15:15:30

js
高质量SQL书写的30条建议

本文提供了30条关于优化SQL的建议，包括避免使用select *，使用具体字段，以及使用limit 1等。这些建议是基于实际开发经验总结出来的，旨在帮助读者优化SQL查询。 ... [详细]

蜡笔小新   2023-12-13 13:24:33

go
Which is more efficient: char str[] or char *str?

This article discusses the efficiency of using char str[] and char *str and whether there is any reason to prefer one over the other. It explains the difference between the two and provides an example to illustrate their usage. ... [详细]

蜡笔小新   2023-12-12 10:13:35

go
logistic回归（线性和非线性）的开发笔记

本文由编程笔记#小编为大家整理，主要介绍了logistic回归（线性和非线性）相关的知识，包括线性logistic回归的代码和数据集的分布情况。希望对你有一定的参考价值。 ... [详细]

蜡笔小新   2023-12-14 21:40:43

go
CSS3选择器的使用方法详解，提高Web开发效率和精准度

本文详细介绍了CSS3新增的选择器方法，包括属性选择器的使用。通过CSS3选择器，可以提高Web开发的效率和精准度，使得查找元素更加方便和快捷。同时，本文还对属性选择器的各种用法进行了详细解释，并给出了相应的代码示例。通过学习本文，读者可以更好地掌握CSS3选择器的使用方法，提升自己的Web开发能力。 ... [详细]

蜡笔小新   2023-12-14 14:37:52

js
关于cuowu类的错误提示和使用AdjustmentListener的问题

本文讨论了一个关于cuowu类的问题，作者在使用cuowu类时遇到了错误提示和使用AdjustmentListener的问题。文章提供了16个解决方案，并给出了两个可能导致错误的原因。 ... [详细]

蜡笔小新   2023-12-13 22:09:56

go
游标的使用笔记

本文介绍了游标的使用方法，并以一个水果供应商数据库为例进行了说明。首先创建了一个名为fruits的表，包含了水果的id、供应商id、名称和价格等字段。然后使用游标查询了水果的名称和价格，并将结果输出。最后对游标进行了关闭操作。通过本文可以了解到游标在数据库操作中的应用。 ... [详细]

蜡笔小新   2023-12-13 15:24:30

grid
CF：3D City Model（小思维）问题解析和代码实现

本文通过解析CF：3D City Model问题，介绍了问题的背景和要求，并给出了相应的代码实现。该问题涉及到在一个矩形的网格上建造城市的情景，每个网格单元可以作为建筑的基础，建筑由多个立方体叠加而成。文章详细讲解了问题的解决思路，并给出了相应的代码实现供读者参考。 ... [详细]

蜡笔小新   2023-12-13 14:17:11

default
用SQL语句怎么把已存在的列加上IDENTITY(1,1)属性

ALTERTABLE通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。语法ALTERTABLEtable{[ALTERCOLUMNcolu ... [详细]

蜡笔小新   2023-12-13 09:49:28

default
Java学习笔记之面向对象编程（OOP）

本文介绍了Java学习笔记中的面向对象编程（OOP）内容，包括OOP的三大特性（封装、继承、多态）和五大原则（单一职责原则、开放封闭原则、里式替换原则、依赖倒置原则）。通过学习OOP，可以提高代码复用性、拓展性和安全性。 ... [详细]

蜡笔小新   2023-12-13 08:44:30

cmd
Go Cobra命令行工具入门教程

本文介绍了Go语言实现的命令行工具Cobra的基本概念、安装方法和入门实践。Cobra被广泛应用于各种项目中，如Kubernetes、Hugo和Github CLI等。通过使用Cobra，我们可以快速创建命令行工具，适用于写测试脚本和各种服务的Admin CLI。文章还通过一个简单的demo演示了Cobra的使用方法。 ... [详细]

蜡笔小新   2023-12-12 20:02:41

cmd
Kotlin中扩展函数的惯用用法及其合理性

本文讨论了Kotlin中扩展函数的一些惯用用法以及其合理性。作者认为在某些情况下，定义扩展函数没有意义，但官方的编码约定支持这种方式。文章还介绍了在类之外定义扩展函数的具体用法，并讨论了避免使用扩展函数的边缘情况。作者提出了对于扩展函数的合理性的质疑，并给出了自己的反驳。最后，文章强调了在编写Kotlin代码时可以自由地使用扩展函数的重要性。 ... [详细]

蜡笔小新   2023-12-12 19:17:21

go
欢乐的票圈重构之旅——RecyclerView的头尾布局增加

项目重构的Git地址：https:github.comrazerdpFriendCircletreemain-dev项目同步更新的文集：http:www.jianshu.comno ... [详细]

蜡笔小新   2023-12-11 19:09:56

俏君woo_267

这个家伙很懒，什么也没留下！

Tags | 热门标签

triggers

lua

merge

blob

hashcode

select

数组

split

process

integer

char

version

instance

go

default

js

vbscript

grid

spring

uml

hashset

loops

ascii

case

cmd

heap

expression

foreach

php5

typescript

RankList | 热门文章

1如何修改刀库的初始刀具数？

2Linux_ubuntu系统下多个终端合并在一个窗口

3计算机系统安全2018【3】第五章：自主访问控制和强制访问控制比较

4服务器03系统打印机,关于打印机以及相关服务器在win 2003中的配置(2)

5老公频繁不归家，以工作忙为借口，该如何修复婚姻？

6如果MAC应用无法打开或文件损坏的时候该怎么办?

7壹点壹滴完成数千万元PreA轮融资，预计明年实现盈利

8支持海量安卓应用！国产操作系统银河麒麟V10

920201126 反思

10linux篇tomcat:Cannot find /usr/local/tomcat1/bin/setclasspath.sh

11AI图片怎么做成电影胶片风格? 图片胶片效果ai的实现方法

12todesk苹果电脑怎么使用

13OSG三维渲染引擎编程学习之六：“第一章：OSG介绍” 之 “1.6 编译环境安装”

14互动部的兄弟姐妹们太强太敬业了

15数据资产纳入国资保值增值考核

InnoDB表统计效率count(主键)