Mysql count

losetowin 发布于:2019-3-3 11:45 分类:技术  有 1041 人浏览,获得评论 0 条 标签: mysql count 

本文地址:http://www.dutycode.com/msyql_count.html
除非注明,文章均为 www.dutycode.com 原创,欢迎转载!转载请注明本文地址,谢谢。

Mysql Count(*)

Count(*)的实现方式

MyISAM

  • 把一张表的总行数存储在磁盘上,这样count(*)时直接返回总行数,效果很高
  • 但如果增加where条件,就不会这么快了,因为无法直接读取磁盘的总行数

InnoDb

  • count(*)时,需要把数据一行行的从引擎中读出来,然后累计计数。
  • 为什么不可以用MyISAM的方式将总行数存储到磁盘上?

    • Innodb默认的隔离级别是 不可重复读,为了实现这个,使用了MVCC多版本并发实现
    • 由于MVCC多版本并发实现的原因,每一行记录都要判断自己是否对当前会话可见,可见才能被查询出来,所以即使在同一时刻,InnoDB表对应该返回多少行是不确定的,也就是说不同会话(事务)下,行数可能不一样,所以没法直接将总行数存储到磁盘上
  • MYSQL在Innodb下对count(*)查询的优化方式

    • Innodb是索引组织表

      • 堆组织表:数据和索引分开,先找索引,后查询数据;索引记录的是数据所在的rowid
      • 索引组织表:行数据以索引形式存在,即找到了索引,就找到了行数据
    • 索引组织表下,主键索引的叶子结点是数据,而普通索引的叶子结点是主键值,所以,普通索引要比主键索引小很多。 在count(*)操作时,对mysql而言,遍历哪个索引树得到的结果是一样的,所以,Mysql优化器会选择代价最小的那个。也就是最小的树来进行遍历。

    • 数据库设计的通用法则之一: 在保证逻辑正确的前提下,尽量减少数据扫描数量

show table status

  • show table status中有个TABLE_ROWS不能用于替代count(*)
  • TABLE_ROWS的数据时Mysql索引通过采样估算得到,误差比较大。官方说法在40%-50%

不同count的用法和区别

count()语义

  • count()是一个聚合函数,对于返回的结果集,一行行做判断,如果count()的参数不为NULL,则加一,最终返回结果值
  • 即count(1), count(主键ID), count(*)返回的是返回符合条件的结果集的总行数,而count(字段)返回的是符合条件的结果集中,字段不为NULL的行数

count(主键ID)

  • InnoDB会遍历整张表,把每一行的ID都拿出来,返给server,server拿到ID之后,判断ID不为空的,进行累加

count(1)

  • InnoDB同样会遍历整张表,但不取值,server对返回的每一行,放一个1进去,判断不为空,进行累加
  • count(1)的执行速度比count(主键ID)快,因为不需要将数据返回给server端,也就不需要解析数据行和字段拷贝操作

count(字段)

  • 如果字段定义为not null,在查询时,一行行的读出字段,判断不为空,按行累加。PS:在查询时,引擎已从表结构知道字段非空
  • 如果字段定义为允许null,在查询时,一行行的读出字段, 字段值可能为空,所以还需要判断值是否为null,如果不为null,则累加
  • 即: server要什么字段,Innodb引擎就返回什么字段

count(*)

  • 比较特殊,并不会把所有字段取出来
  • Innodb做过针对性优化,查询时,不取值。因为数据一定非空,只需要把行数做累加即可

性能对比

  • count(*)≈count(1)>count(主键ID)>count(字段)

补充

  • 为什么count(主键ID)的时候,还需要取值?

    • Mysql并未对此做优化,即需要取值
    • 实际上,主键ID一定非空,如果优化的话,Mysql可以不将字段取出,而直接把符合条件的数据做累加即可
    • 同样,对count(字段),如果字段not null的时候,应该也可以这么做

版权所有:《攀爬蜗牛》 => 《Mysql count
本文地址:https://www.dutycode.com/msyql_count.html
除非注明,文章均为 《攀爬蜗牛》 原创,欢迎转载!转载请注明本文地址,谢谢。