当前位置：Gxlcms > 数据库问题 > MySQL count知多少

MySQL count知多少

时间：2021-07-01 10:21:17 帮助过：26人阅读

创建连续数表 */ 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 < cnt DO 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(主键) < count(1) ≈ 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

但是查询效

< 上一篇
mysql语句执行顺序

下一篇 >
读取数据库pymysql

人气教程排行

229次 1 oracle 用户解锁和修改用户密码

229次 2 解决：安装SQL Server 2008 Native Client遇到错误（在Navicat premium新建sqlserver连接时需要）：An error occurred during ...HRESULT: 0x80070422（注意尾部的错误号）

229次 3 rocksdb 编译步骤

229次 4 adb命令开关蓝牙及NFC

229次 5 解决数据库连接错误您在wp-config.php文件中提供的数据库用户名和密码可能不正确，或者无法连接到localhost上的数据库服务器，这意味着您的主机数据库服务器已停止工作。

229次 6 使用java自动填充，实现mysql的创建修改时间的自动填充

229次 7 Linux mount挂载磁盘报错 mount: wrong fs type, bad option, bad superblock on /dev/vdb

228次 8 关于mysql创建数据库中字符集和排序规则的选择

228次 9 Linux设置Mysql开机自启动服务

227次 10 SQL2008：WITH MOVE 子句可用于重新定位一个或多个文件

227次 11 图数据库Neo4j在GIS系统的应用

227次 12 SpringBoot配置 druid 数据源配置慢SQL记录

227次 13 SQL state [72000]; error code [1013]; ORA-03111: 通信通道收到中断; java.sql.SQLException: ORA-01745: 无效的主机/绑定变量名;java.sql.SQLException: ORA-01013: 用户请求取消当前的操作

227次 14 docker-compose启动MySQL并配置远程登录

225次 15 Mysql安装（for mac）

225次 16 kettle将图片转换至二进制存储至数据库

225次 17 adb的安装及配置

224次 18 使用PHP控制MODBUS-RTU设备

224次 19 MySQL 查询时间差值大于某一个值的记录

224次 20 MongoDB内存配置 --wiredTigerCacheSizeGB