MySQL系列(七)——count()探究

计算一个表的行数SQL语句select count(*) from t ,随着系统中记录数越来越多,这条语句执行得也会越来越慢。此篇旨在说明count(*)语句到底是怎样实现的,以及MySQL为什么会这么实现。如果应用中有这种频繁变更并需要统计表行数的需求,业务设计上可以怎么做。

我的新书《LangChain编程从入门到实践》 已经开售!推荐正在学习AI应用开发的朋友购买阅读!
LangChain编程从入门到实践

count(*)的实现方式

  1. MyISAM引擎把一个表的总行数存在了磁盘上,因此执行count(*)的时候会直接返回这个数,效率很高;
  2. InnoDB引擎执行count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
  3. 注意:如果加了where 条件的话,MyISAM表也是不能返回得这么快的。

假设表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,每一行记录都要判断自己是否对这个会话可见,因此对于count(*)请求来说,InnoDB只好把数据一行一行地读出依次判断,可见的行才能够用于计算“基于这个查询”的表的总行数。

优化

  1. InnoDB是索引组织表
    • 聚簇索引树:叶子节点是数据
    • 二级索引树:叶子节点是主键值
  2. 二级索引树占用的空间比聚簇索引树小很多
  3. 在保证逻辑正确的前提下,尽量减少扫描的数据量,是数据库系统设计的通用法则之一
    • 针对无过滤条件的count操作,无论遍历哪一颗索引树,效果都是一样的
    • 优化器会为count(*)选择最优的索引树

show table status

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> show table status like 't'\G;
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 100256
Avg_row_length: 36
Data_length: 3686400
Max_data_length: 0
Index_length: 3178496
Data_free: 0
Auto_increment: NULL
Create_time: 2020-02-11 10:00:27
Update_time: NULL
Check_time: NULL
Collation: utf8_unicode_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.04 sec)

维护计数

用缓存系统保存计数

  1. 用Redis来保存表的总行数(无过滤条件)
  2. 这个表每插入一行,Redis计数+1,每删除一行,Redis计数-1
  3. 缺点:
  • 丢失更新
    • Redis可能会丢失更新
    • 解决方案:Redis异常重启后,到数据库执行一次count(*),异常重启并不常见,这时全表扫描的成本是可以接受的
  • 逻辑不精确 – 致命
    • 在并发系统里面,无法精确控制不同线程的执行时刻的,即使Redis正常工作,这个计数值还是逻辑上不精确的
    • Redis和MySQL是两个不同的存储系统,_不支持分布式事务_,因此无法拿到精确的一致性视图

在数据库保存计数

  1. 把计数值放到数据库单独的一张计数表C中
  2. 利用InnoDB的crash-safe的特性,解决了崩溃丢失的问题
  3. 利用InnoDB的支持事务的特性,解决了一致性视图的问题
  4. 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的性能

分析性能差别的几个原则:

  • server层要什么就给什么;

  • InnoDB只给必要的值;

  • 现在的优化器只优化了count(*)的语义为“取行数”,其他“显而易见”的优化并没有做。

count(字段F)

  1. 如果字段F定义为不允许为NULL,一行行地从记录里读出这个字段,判断通过后按行累加,通过表结构判断该字段是不可能为NULL
  2. 如果字段F定义为允许NULL,一行行地从记录里读出这个字段,判断通过后按行累加
    • 通过表结构判断该字段是有可能为NULL
    • 判断该字段值是否实际为NULL
  3. 如果字段F上没有索引,只能遍历整张表(聚簇索引)
  4. _由于InnoDB必须返回字段F,因此优化器能做出的优化决策将减少_,例如不能选择最优的索引来遍历

count(主键ID)

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

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系列(七)——count()探究

https://liduos.com/mysql-function-count.html

作者

莫尔索

发布于

2020-10-20

更新于

2024-05-24

许可协议

评论