MySQL中无过滤条件的count详解
作者:点滴积累 发布时间:2024-01-22 13:06:20
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)
InnoDB引擎会遍历整张表(聚簇索引),但不取值
Server层对于返回的每一行,放一个数字1进去,判断是不可能为NULL,按行累加
count(1)比count(主键ID)快,因为count(主键ID)会涉及到两部分操作
解析数据行
拷贝字段值
count(*)
count(*)不会把所有值都取出来,而是专门做了优化,不取值,因为『*』肯定不为NULL,按行累加
不取值:InnoDB返回一个空行,告诉Server层不是NULL,可以计数
效率排序
count(字段F) < count(主键ID) < count(1) ≈ count(*)
尽量使用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讲》
来源:http://zhongmingmao.me/2019/02/07/mysql-innodb-pure-count/
猜你喜欢
- 本文实例总结了Python中numpy模块常见用法。分享给大家供大家参考,具体如下:import numpy as nparr = np.a
- 1、残差连接是目前常用的组件,解决了大规模深度学习模型梯度消失和瓶颈问题。通常,在10层以上的模型中追加残差连接可能有帮助。from ker
- 今天介绍一下 go语言的并发机制以及它所使用的CSP并发模型CSP并发模型CSP模型是上个世纪七十年代提出的,用于描述两个独立的并发实体通过
- 我们都知道在9i之前,要想获得建表和索引的语句是一件很麻烦的事。我们通常的做法都是通过export with rows=no来得到,但它的输
- open 遍历一个大日志文件使用 readlines() 还是 readline() ?总体上 readlines() 不慢于python
- 在目标检测中一个很重要的问题就是NMS及IOU计算,而一般所说的目标检测检测的box是规则矩形框,计算IOU也非常简单,有两种方法:1. 两
- 前言硬要说这篇文章怎么来的,那得先从那几个吃野味的人开始说起…… 前天睡醒:假期还有几天;昨天睡醒:假期还有十几天;今天睡醒:假期还有一个月
- 前言左思右想没有头绪时,刚好看到一篇介绍Pygame制作飞机大战的文章。文章写的不错,文中代码拿来就能跑。有了!要不直接把飞机大战改成接兔子
- 第一种方法、浏览器设置兼容模式打开IE,点击“工具”菜单,选择“兼容性视图设置”,勾选“在兼容性视图中显示所有网站”选项即可。第二种方法、在
- (一)什么是json:JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式。易于人阅读和编写。同时也
- 一、安装cuda1、在英伟达官网下载最新版的cuda驱动https://developer.nvidia.com/zh-cn/cuda-do
- 通过python处理光斑图像1 相关包与图像读取首先需要科学计算必备包numpy和画图包matplotlib.pyplot,我们通过后者进行
- 前言最近整理图片发现,好多图片都非常相似,于是写如下代码去删除,有两种方法:注:第一种方法只对于连续图片(例一个视频里截下的图片)准确率也较
- K-Means聚类算法演示及可视化展示#导入包from sklearn.cluster import KMeansX = [[0.0888,
- 模型事件Laravel 模型事件允许你监听模型生命周期内的事件, 并且通过这个事件去做一些模型通用性的东西, 例如检查用户修改了那个字段,
- optim.Adam()解读torch.optim是一个实现了多种优化算法的包,大多数通用的方法都已支持,提供了丰富的接口调用,未来更多精炼
- 需求:(1) 获取你对象chrome前一天的浏览记录中的所有网址(url)和访问时间,并存在一个txt文件中(2)将这个txt文件发送给指定
- 最近在一个项目中遇到一个查询页面,其中一个查询条件是根据选择的年份、月以及周数显示选择的该周从几号到几号,这样一个需求。在网上搜
- 下面我们学习if语句,输入下面的代码,确保能够正确运行。people = 20 cats = 30 dogs = 15 if people
- 上一次很多朋友写文字屏蔽说到要用正则表达,其实不是我不想用(我正则用得不是很多,看过我之前爬虫的都知道,我直接用BeautifulSoup的