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

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