mysql一张表能存多少条数据不影响性能_MySQL count知多少

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

01

   准备工作

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

/* 创建连续数表 */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

02

   查询整表数据量的方法

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

• 查询大致数据量，可以查统计信息，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 |+--------+------------+----------+--------------+-------------+-----------+-------------+----------+