当前位置： 移动技术网 > IT编程>数据库>Mysql > MySQL中无过滤条件的count详解

MySQL中无过滤条件的count详解

2019年03月18日  | 移动技术网IT编程  | 我要评论

count(*)

实现

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

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

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

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

样例

假设表t有10000条记录

session a session b session c
begin;
select count(*) from t;（返回10000）
insert into t;（插入一行）
begin;
insert into t（插入一行）;
select count(*) from t;（返回10000） select count(*) from t;（返回10002） select count(*) from t;（返回10001）

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

innodb默认事务隔离级别是rr，通过mvcc实现

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

优化

1、innodb是索引组织表

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

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

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

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

show table status

mysql> show table status\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-01 17:49:07
 update_time: null
 check_time: null
 collation: utf8_general_ci
 checksum: null
 create_options:
 comment:

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

维护计数

缓存

方案

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

缺点

丢失更新

1、redis可能会丢失更新

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

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

逻辑不精确 – 致命

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

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

时序a

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

时刻 session a session b
t1
t2 插入一行数据r;
t3 读取redis计数;
查询最近100条记录;
t4 redis计数+1;

时序b

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

时刻 session a session b
t1
t2 redis计数+1;
t3 读取redis计数;
查询最近100条记录;
t4 插入一行数据r;

数据库

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

时刻 session a session b
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)

  1. innodb引擎会遍历整张表（聚簇索引），但不取值
  2. server层对于返回的每一行，放一个数字1进去，判断是不可能为null，按行累加
  3. count(1)比count(主键id)快，因为count(主键id)会涉及到两部分操作
  • 解析数据行
  • 拷贝字段值

count(*)

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

效率排序

  1. count(字段f) < count(主键id) < count(1) ≈ count(*)
  2. 尽量使用count(*)

样例

mysql> show create table prop_action_batch_reward\g;
*************************** 1. row ***************************
 table: prop_action_batch_reward
create table: create table `prop_action_batch_reward` (
 `id` bigint(20) not null,
 `source` int(11) default null,
 `serial_id` bigint(20) not null,
 `create_time` datetime not null default current_timestamp,
 `user_ids` mediumtext,
 `serial_index` tinyint(4) default '0',
 primary key (`id`),
 unique key `uniq_serial_id_source_index` (`serial_id`,`source`,`serial_index`),
 key `idx_create_time` (`create_time`)
) engine=innodb default charset=utf8

count(字段f)

无索引

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

mysql> explain select count(user_ids) from prop_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> select count(user_ids) from prop_action_batch_reward;
+-----------------+
| count(user_ids) |
+-----------------+
| 17689788 |
+-----------------+
1 row in set (10.93 sec)

有索引

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

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

  • 如果选择idx_create_time，并且返回serial_id字段，这意味着必须回表
mysql> explain select count(serial_id) from prop_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 | using index |
+----+-------------+--------------------------+-------+---------------+-----------------------------+---------+------+----------+-------------+

mysql> select count(serial_id) from prop_action_batch_reward;
+------------------+
| count(serial_id) |
+------------------+
|  17705069 |
+------------------+
1 row in set (5.04 sec)

count(主键id)

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

mysql> explain select count(id) from prop_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 | using index |
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql> select count(id) from prop_action_batch_reward;
+-----------+
| count(id) |
+-----------+
| 17705383 |
+-----------+
1 row in set (4.54 sec)

count(1)

mysql> explain select count(1) from prop_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 | using index |
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql> select count(1) from prop_action_batch_reward;
+----------+
| count(1) |
+----------+
| 17705808 |
+----------+
1 row in set (4.12 sec)

count(*)

mysql> explain select count(*) from prop_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 | using index |
+----+-------------+--------------------------+-------+---------------+-----------------+---------+------+----------+-------------+

mysql> select count(*) from prop_action_batch_reward;
+----------+
| count(*) |
+----------+
| 17706074 |
+----------+
1 row in set (4.06 sec)

参考资料

《mysql实战45讲》

总结

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

您可能感兴趣的文章:

如对本文有疑问， 点击进行留言回复！！

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网