MySQL中无过滤条件的count详解

2023-08-29 05:21:04 224

count(*)

实现

1、MyISAM：将表的总行数存放在磁盘上，针对无过滤条件的查询可以直接返回

如果有过滤条件的count(*)，MyISAM也不能很快返回

2、InnoDB：从存储引擎一行行地读出数据，然后累加计数

由于MVCC，在同一时刻，InnoDB应该返回多少行是不确定

样例

假设表t有10000条记录

sessionA	sessionB	sessionC
BEGIN;
SELECTCOUNT(*)FROMt;（返回10000）
		INSERTINTOt;（插入一行）
	BEGIN;
	INSERTINTOt（插入一行）;
SELECTCOUNT(*)FROMt;（返回10000）	SELECTCOUNT(*)FROMt;（返回10002）	SELECTCOUNT(*)FROMT;（返回10001）

最后时刻三个会话同时查询t的总行数，拿到的结果却是不同的

InnoDB默认事务隔离级别是RR，通过MVCC实现

每个事务都需要判断每一行记录是否对自己可见

优化

1、InnoDB是索引组织表

聚簇索引树：叶子节点是数据
二级索引树：叶子节点是主键值

2、二级索引树占用的空间比聚簇索引树小很多

3、优化器会在保证逻辑正确的前提下，遍历最小的索引树，尽量减少扫描的数据量

针对无过滤条件的count操作，无论遍历哪一颗索引树，效果都是一样的
优化器会为count(*)选择最优的索引树

showtablestatus

mysql>SHOWTABLESTATUS\G;
***************************1.row***************************
Name:t
Engine:InnoDB
Version:10
Row_format:Dynamic
Rows:100256
Avg_row_length:47
Data_length:4734976
Max_data_length:0
Index_length:5275648
Data_free:0
Auto_increment:NULL
Create_time:2019-02-0117:49:07
Update_time:NULL
Check_time:NULL
Collation:utf8_general_ci
Checksum:NULL
Create_options:
Comment:

SHOWTABLESTATUS同样通过采样来估算（非常不精确），误差能到40%~50%

维护计数

缓存

方案

用Redis来保存表的总行数（无过滤条件）
这个表每插入一行，Redis计数+1，每删除一行，Redis计数-1

缺点

丢失更新

1、Redis可能会丢失更新

2、解决方案：Redis异常重启后，到数据库执行一次count(*)

异常重启并不常见，这时全表扫描的成本是可以接受的

逻辑不精确–致命

1、场景：显示操作记录的总数和最近操作的100条记录

2、Redis和MySQL是两个不同的存储系统，不支持分布式事务，因此无法拿到精确的一致性视图

时序A

sessionB在T3时刻，查到的100行结果里面有最新插入的记录，但Redis还没有+1，逻辑不一致

时刻	sessionA	sessionB
T1
T2	插入一行数据R;
T3		读取Redis计数; 查询最近100条记录;
T4	Redis计数+1;

时序B

sessionB在T3时刻，查到的100行结果里面没有最新插入的记录，但Redis已经+1，逻辑不一致

时刻	sessionA	sessionB
T1
T2	Redis计数+1;
T3		读取Redis计数; 查询最近100条记录;
T4	插入一行数据R;

数据库

把计数值放到数据库单独的一张计数表C中
利用InnoDB的crash-safe的特性，解决了崩溃丢失的问题
利用InnoDB的支持事务的特性，解决了一致性视图的问题
sessionB在T3时刻，sessionA的事务还未提交，表C的计数值+1对自己不可见，逻辑一致

时刻	sessionA	sessionB
T1
T2	BEGIN; 表C中的计数值+1;
T3		BEGIN; 读表C计数值; 查询最新100条记录; COMMIT;
T4	插入一行数据R; COMMIT;

count的性能

语义

1、count()是一个聚合函数，对于返回的结果集，一行一行地进行判断

如果count函数的参数值不是NULL，累计值+1，否则不加，最后返回累计值

2、count(字段F)

字段F有可能为NULL
表示返回满足条件的结果集里字段F不为NULL的总数

3、count(主键ID)、count(1)、count(*)

不可能为NULL
表示返回满足条件的结果集的总数

4、Server层要什么字段，InnoDB引擎就返回什么字段

count(*)例外，不返回整行，只返回空行

性能对比

count(字段F)

1、如果字段F定义为不允许为NULL，一行行地从记录里读出这个字段，判断通过后按行累加

通过表结构判断该字段是不可能为NULL

2、如果字段F定义为允许NULL，一行行地从记录里读出这个字段，判断通过后按行累加

通过表结构判断该字段是有可能为NULL
判断该字段值是否实际为NULL

3、如果字段F上没有二级索引，只能遍历整张表（聚簇索引）

4、由于InnoDB必须返回字段F，因此优化器能做出的优化决策将减少

例如不能选择最优的索引来遍历

count(主键ID)

InnoDB会遍历整张表（聚簇索引），把每一行的id值取出来，返回给Server层
Server层拿到id后，判断为不可能为NULL，然后按行累加
优化器可能会选择最优的索引来遍历

count(1)

InnoDB引擎会遍历整张表（聚簇索引），但不取值
Server层对于返回的每一行，放一个数字1进去，判断是不可能为NULL，按行累加
count(1)比count(主键ID)快，因为count(主键ID)会涉及到两部分操作

解析数据行
拷贝字段值

count(*)

count(*)不会把所有值都取出来，而是专门做了优化，不取值，因为『*』肯定不为NULL，按行累加
不取值：InnoDB返回一个空行，告诉Server层不是NULL，可以计数

效率排序

count(字段F)
尽量使用count(*)

样例

mysql>SHOWCREATETABLEprop_action_batch_reward\G;
***************************1.row***************************
Table:prop_action_batch_reward
CreateTable:CREATETABLE`prop_action_batch_reward`(
`id`bigint(20)NOTNULL,
`source`int(11)DEFAULTNULL,
`serial_id`bigint(20)NOTNULL,
`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMP,
`user_ids`mediumtext,
`serial_index`tinyint(4)DEFAULT'0',
PRIMARYKEY(`id`),
UNIQUEKEY`uniq_serial_id_source_index`(`serial_id`,`source`,`serial_index`),
KEY`idx_create_time`(`create_time`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8

count(字段F)

无索引

user_ids上无索引，而InnoDB又必须返回user_ids字段，只能遍历聚簇索引

mysql>EXPLAINSELECTCOUNT(user_ids)FROMprop_action_batch_reward;
+----+-------------+--------------------------+------+---------------+------+---------+------+----------+-------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+--------------------------+------+---------------+------+---------+------+----------+-------+
|1|SIMPLE|prop_action_batch_reward|ALL|NULL|NULL|NULL|NULL|16435876|NULL|
+----+-------------+--------------------------+------+---------------+------+---------+------+----------+-------+

mysql>SELECTCOUNT(user_ids)FROMprop_action_batch_reward;
+-----------------+
|count(user_ids)|
+-----------------+
|17689788|
+-----------------+
1rowinset(10.93sec)

有索引

1、serial_id上有索引，可以遍历uniq_serial_id_source_index

2、但由于InnoDB必须返回serial_id字段，因此不会遍历逻辑结果等价的更优选择idx_create_time

如果选择idx_create_time，并且返回serial_id字段，这意味着必须回表

mysql>EXPLAINSELECTCOUNT(serial_id)FROMprop_action_batch_reward;
+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+
|1|SIMPLE|prop_action_batch_reward|index|NULL|uniq_serial_id_source_index|15|NULL|16434890|Usingindex|
+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+

mysql>SELECTCOUNT(serial_id)FROMprop_action_batch_reward;
+------------------+
|count(serial_id)|
+------------------+
|17705069|
+------------------+
1rowinset(5.04sec)

count(主键ID)

优化器选择了最优的索引idx_create_time来遍历，而非聚簇索引

mysql>EXPLAINSELECTCOUNT(id)FROMprop_action_batch_reward;
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|1|SIMPLE|prop_action_batch_reward|index|NULL|idx_create_time|5|NULL|16436797|Usingindex|
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql>SELECTCOUNT(id)FROMprop_action_batch_reward;
+-----------+
|count(id)|
+-----------+
|17705383|
+-----------+
1rowinset(4.54sec)

count(1)

mysql>EXPLAINSELECTCOUNT(1)FROMprop_action_batch_reward;
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|1|SIMPLE|prop_action_batch_reward|index|NULL|idx_create_time|5|NULL|16437220|Usingindex|
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql>SELECTCOUNT(1)FROMprop_action_batch_reward;
+----------+
|count(1)|
+----------+
|17705808|
+----------+
1rowinset(4.12sec)

count(*)

mysql>EXPLAINSELECTCOUNT(*)FROMprop_action_batch_reward;
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+
|1|SIMPLE|prop_action_batch_reward|index|NULL|idx_create_time|5|NULL|16437518|Usingindex|
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql>SELECTCOUNT(*)FROMprop_action_batch_reward;
+----------+
|count(*)|
+----------+
|17706074|
+----------+
1rowinset(4.06sec)

参考资料

《MySQL实战45讲》

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。

MySQL中无过滤条件的count详解

热门推荐

随机推荐