MySQL学习笔记-count的查询

count()计算一个表的行数

select count(*) from t

 

 

count(*)的实现方式

• MyISAM引擎将一个表的总行数存在磁盘上，一执行count(*)时就直接返回统计计数
• InnoDB引擎执行count()时，由于MACC多版本并发控制的原因，同一时刻有多个查询，可重复隔离级别下，返回的行数是不确定的。需要把数据一行一行从引擎里读出来，然后累积计数虽然数据准确，但会引发性能问题

 

InnoDB相比于MyISAM的优点

在事务支持，并发能力，数据安全上InnoDB引擎表现都较MyISAM表现优良

 

MySQL针对count(*)的优化

InnoDB是索引组织表

主键索引树的叶子节点是数据

普通索引树的叶子节点是主键值

普通索引树主键索引树小很多

对于count(*)的操作，遍历哪个索引树得到的结果逻辑上是一样的。因此MySQL会找到最小的那课树来遍历

• 在保证逻辑结果正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一

注意：

mysql>show table status   ##不能替代select count(*) from t
##因为show table status索引统计的值是采样统计估算而来，误差率较大

 

 

统计表的行数

1.用缓存系统支持（会导致逻辑上不一致）

• 可以用Redis服务来保存这个表的总行数。每个表被插入一行Redis计数就加1，没被删除一行Redis计数就减1，这样的话，读和跟新操作都很快。

• 使用Redis服务支撑，会导致问题

  （1）缓存丢失更新----------永久化存储可解决

  （2）逻辑上不准确----------无解

• 逻辑上不准确无解的原因

一种情况是，查询到的100行结果了吗有最新插入记录，而Redis的计数里面还没加1；

另一种情况是，查到的100结果里没有最新插入的记录，而Redis的计数里面已经加了1；

时刻	会话A	会话B
T1
T2	Redis计数加1；
T3		读Redis计数；
		查询最近100条记录；
T4	插入一行数据R；
T5

1.此图中，会话A在T2时插入一条数据R，会话B在T3查询到数据R，但是没有查询到Redis计数上加一，所以逻辑不一致

2.两个系统，不支持一致性视图

2.将计数保存到数据库里（使用InnoDB的事务来更新数据）

解决的问题

缓存系统里面有丢失数据和计数不精确的风险

数据库没有崩溃丢失数据的风险，使用InnoDB的事务很好的解决了计数更新查询的问题

时刻	会话A	会话B
T1
T2	begin;
	表C中计数值加1；
T3		begin;
		读表C计数值
		查询最近100条记录
		commit;
T4	插入一行数据R
	commit;

当处于InnoDB默认的可重复读隔离级别下

• 会话A先开启了一个事务，将计数值加1，会话B也开启了一个事务，查询计数值，并且查询数据，但这时处于可重复读隔离级别下，会话A的事务还没提交，所以会话A不会影响会话B，所以计数值加1的操作会话B不可见。
• 只有会话A的事务commit提交后，这个更新才会生效

 

 

不同count的用法

• count()是一个聚合函数，对于返回的结果集，一行行判断
• 如果count()函数的参数不是NULL，累计值就加1，否则不加，最后返回累计值

count(*),count(主键 id)和count(1)都表示返回满足条件的结果集的总行数

count(字段)则表示返回满足条件的数据行里面，参数“字段”部位NULL的总个数

count(主键id)：InnoDB引擎会遍历整张表，将每一行的id值取出返回给server层，判断不为空，则按行累加（不一定走主键，哪张表小走哪张表，但速度还是没有count()快，因为它自己还要取出值判断，而count()不用判断）

count(1):InnoDB引擎遍历整张表，但不取值。server层对于返回的每一行放一个数字‘1’进去，判断不为空，按行累加

count(字段)：

• 当‘字段’定义not null时，读出字段，判断不为空累加；
• 当‘字段’定义null时，有可能为空，所以取出判断不为空才累加

count(*)：会被优化，不取出全部字段，直接按行累加（走的主键，主键不可能为空）

所以按照效率的排序：count(字段)<count(主键 id)<count(1)≈count(*)

尽量使用count(*)