作为后端开发基本会遇到一个问题,就是要获取一表的所有记录总数
语句也很简单,有下面几种
语句A:
SELECT COUNT(*) FROM 表名
语句B:
SELECT COUNT(id) FROM 表名
语句C:
SELECT COUNT(1) FROM 表名
语句D:
SELECT COUNT(字段名) FROM 表名
那么,对于count(*),count(1),count(主键id)和count(某个字段),到底哪个语句性能更高?更推荐使用那种语句呢?工作机制又是什么呢?
一会说,先铺垫铺垫
不同的存储引擎count(*)的原理不一样
-
对于MyISAM: 存储引擎会把每个表的总行数保存在磁盘上,执行查询的时候,直接返回对应的行数,效率超级快,(咱么只聊没有where条件的count(*))
-
对于InnoDB:执行count(*)时候,需要把数据一行行从引擎中读取,然后累加
所以,InnoDB中,表越大,count(*)越慢
为何INNODB不把表行总数存起来呢?
事务!事务!还是他妈的事务!
因为InnoDB有MVCC(多版本并发控制)机制,所以对于多个事务来说,不同时间段读取的数据都不一样,不同的事务读取的表记录数也可能是不一样,所以,存行数没意义啊
故而,InnoDB只能一行行的针对不同事务的查询,通过累加的方式来计算总行数
首先记住:
InnoDB是索引来组织表,
主键索引的叶子节点存的是数据
而普通索引的叶子节点存储主键的值(地址)
这说明什么?
说明普通索引(辅助索引)比主键索引小!(通常哈,特别情况不谈)
而InnoDB会优先选择一个较小的索引来做计算
比如我有一张工商信息表company_base_info,里面有主键id索引,税号social_code唯一索引,
那么我执行 select count(*) from company_base_info
他会走主键id还是税号social_code 呢?
运行一下嘛:
desc select count(*) from company_base_info
那么,问题来了
那么对于一张大表,上千万的大表,怎么快速获取对应的行记录数呢?
我总不能每次都去执行count(*)吧,一条就是好几秒钟,数据库早晚得崩盘啊
1. 缓存
用redis来保存表的总行数,如果表有插入,redis对应值+1,有删除,那么redis对应的值-1
看起来很合理对吧? 有没有问题?当然!
1. redis可能会宕机吧?
会,那怎么办?
redis会持久化的,没问题,
但是如果恰好在redis异常重启的时候,mysql对应的表发生了1000笔插入呢?
持久化的值是不是不准了?咋整?
那我就每次redis异常重启的时候,重新执行select count(*)
没毛病!可以接受
2.数据的不一致性
需求:
我要获取表的记录总数,并且,获取最近修改的10条数据
如果在查询的过程中,恰好有一条记录插入,会发生什么?
那么我们来看看流程
假设当前表有100条数据
时刻 | 操作A | 操作B |
---|---|---|
T1 | ||
T2 | 插入一行数据 | |
T3 | 读取redis中总行数 = 100(错了),获取最近修改的10条记录(正确) | |
T4 | Redis记录的总行数+1 = 101 |
所以,请求量大的系统中,总行数这个值,理论上还是无法做到完全一致性
因为插入一行数据和reids计数值+1 这两个操作无法做到原子性,
当然你代码里加锁可以解决!
而且,获取记录总数,一般不是什么硬性业务逻辑,有点出入可以理解
但是,我们能不能做到严谨一些呢?
2. 用数据库保存计数
因为InnoDB天生带事务啊!
好活啊!
老子把插入和总行数累加扔事务里,实现原子性操作不就得了?
对于表B,我们新建一个表B_nums 保存B的总行数,
流程:
时刻 | 操作A | 操作B |
---|---|---|
T1 | ||
T2 | 开启事务: B_nums计数值+1 | |
T3 | 开启事务:读取B_nums的计数值,查询最近修改的10条记录,提交事务 | |
T4 | 插入一行数据R,提交事务 |
想想,想想,在T3时刻,数据逻辑是不是一致了?
完美的一批呀
最后,看看count(*) count(1) count(字段)有什么区别
count(主键ID):
InnoDB引擎会遍历整张表,把每一行的id值都取出来,返回给server 层。server层拿到id后,发现是不可能为空的,就按行累加。
count(1)
,InnoDB引擎遍历整张表,但不取值。server层对于返回的每一行,放一个数 字“1”进去,发现也是是不可能为空的,按行累加
因为count(id)要逐层去解析数据行,可能还要拷贝字段值等操作,所以count(1)相对较好
count(某一个字段)
这就分为两种情况了。
如果字段值不允许null:
一行一行从引擎中取出数据,然后累加
如果字段值允许为null:
一行一行从引擎中取出数据,然后判断是否为null,然后累加
count(*)
这玩意他妈流弊!
并不会把全部字段取出来,而是专门做了优化,不取值。
count(*)肯定不 是null,按行累加
按照效率排序的话,count(字段)<count(主键id)<count(1)≈count(*),所以我建议 你,尽量使用count( * )。