兴国资源网

MySQL中无过滤条件的count详解
站长资源 2024/11/13 佚名
2 0
兴国资源网 Design By www.nnzcdc.com

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讲》

总结

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

兴国资源网 Design By www.nnzcdc.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！
兴国资源网 Design By www.nnzcdc.com
www.nnzcdc.com 兴国资源网
39,976影音资源
44,792技术资源
21,817软件资源
651,128站长资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接
兴国资源网 Design By www.nnzcdc.com