mysql 5.7中 count(0) count(*) count(主键) count(非空字段)效率比较

mysql count(0) count(*) count(主键) count(非空字段) 效率比较

 

写代码的时候经理在背后说了一句count(0)的效率高于count(*) ，索性全部测试了一下

 

结论：1、count(0)效率等于count(*)等于count(主键)等于count(非空字段)

但是 唯一的区别就是count只会统计非空字段

 

系统配置

processor	: 0
vendor_id	: GenuineIntel
cpu family	: 6
model		: 85
model name	: Intel(R) Xeon(R) Platinum 8163 CPU @ 2.50GHz
stepping	: 4
microcode	: 0x1
cpu MHz		: 2499.986
cache size	: 33792 KB
physical id	: 0
siblings	: 1
core id		: 0
cpu cores	: 1
apicid		: 0
initial apicid	: 0
fpu		: yes
fpu_exception	: yes
cpuid level	: 13
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 ss syscall nx pdpe1gb rdtscp lm constant_tsc rep_good nopl eagerfpu pni pclmulqdq ssse3 fma cx16 pcid sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand hypervisor lahf_lm abm 3dnowprefetch fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm mpx avx512f rdseed adx smap avx512cd xsaveopt xsavec xgetbv1
bugs		:
bogomips	: 4999.97
clflush size	: 64
cache_alignment	: 64
address sizes	: 46 bits physical, 48 bits virtual
power management:

MemTotal:        2048212 kB
MemFree:           74828 kB
MemAvailable:    1449236 kB
Buffers:           11572 kB
Cached:          1472124 kB
SwapCached:            0 kB
Active:          1460916 kB
Inactive:         432892 kB
Active(anon):     410588 kB
Inactive(anon):     2200 kB
Active(file):    1050328 kB
Inactive(file):   430692 kB
Unevictable:           0 kB
Mlocked:               0 kB
SwapTotal:             0 kB
SwapFree:              0 kB
Dirty:                 0 kB
Writeback:             0 kB
AnonPages:        410164 kB
Mapped:            37160 kB
Shmem:              2672 kB
Slab:              61228 kB
SReclaimable:      50132 kB
SUnreclaim:        11096 kB
KernelStack:        2272 kB
PageTables:         3200 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:     1024104 kB
Committed_AS:     705104 kB
VmallocTotal:   34359738367 kB
VmallocUsed:           0 kB
VmallocChunk:          0 kB
HardwareCorrupted:     0 kB
AnonHugePages:    374784 kB
CmaTotal:              0 kB
CmaFree:               0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:       53120 kB
DirectMap2M:     2043904 kB
DirectMap1G:           0 kB

 

表结构

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` char(50) NOT NULL,
  `text` varchar(50) DEFAULT NULL,
  `userid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20000001 DEFAULT CHARSET=latin1

 

创建模拟数据

 

DELIMITER $$

USE `test`$$

DROP PROCEDURE IF EXISTS `quickInsert`$$

CREATE DEFINER=`root`@`%` PROCEDURE `quickInsert`()
BEGIN
	
	DECLARE i INT;
	SET i =0;
	START TRANSACTION;  
	WHILE i < 10000000 DO
	IF i MOD 2 = 0 THEN
	INSERT INTO test (NAME,TEXT,userid) VALUES (MD5(RAND()*1000),NULL,RAND()*500);
	ELSE 
		INSERT INTO test (NAME,TEXT,userid) VALUES (MD5(RAND()*1000),MD5(RAND()*3000),RAND()*500);
	END IF;
	SET i = i+1;
	END WHILE;
	COMMIT;
    END$$

DELIMITER ;

 

关闭缓存

 

query_cache_size =  0

mysql vserion

5.7.21-0ubuntu0.16.04.1-log

数据大小 1千万条 25~30G

CALL quickInsert();  #插入数据

count(0)测试

• 14.030s
• 17.094s
• 17.391s
• 17.398s
• 17.081s

count(*)测试

• 17.297s
• 17.403s
• 17.296s
• 16.965s
• 17.389s

排除系统波动，基本上没有太大差别

count(主键)

• 16.980s
• 16.982s
• 17.405s
• 17.229s
• 17.095s

count(非空字段) 开始出现大量的不确定的时间，所以多测试了多次

• 17.009s
• 16.902s
• 16.993s
• 14.607s
• 12.843s
• 17.404s
• 17.413s
• 17.397s
• 16.898s

可以看出基本上没有太大的区别，出现的波动，我琢磨了一下可能是阿里云上的这台服务器为突发性能的，可能不是太稳定