那种count性能最好?
我们以InnoDB存储引擎为例:
count()是什么?
它是一个聚合函数,用来统计符合查询条件的记录中,函数指定的参数不为null的记录有多少个。而参数可以是字段名,也可以是其它任意表达式
- count(1)则表示查询1这个表达式不为null的记录有多少个?时间上,它统计的就是数据库表中记录的条数
count(主键字段)的执行过程?
MySQL的server层会维护一个count变量,并循环向InnoDB读取一条记录,InnoDB会优先遍历二级索引(当有多个二级索引的时候,使用key_len小的),没有二级索引时,才遍历聚簇索引,然后将读取到的记录返回给server层,server层读取其中的主键字段,判断是否为null,不为null则count++。
- 为什么优先遍历二级索引?
因为二级索引保存的信息比聚簇索引少,以叶子结点为例,二级索引的叶子结点保存主键和索引字段,而聚簇索引的叶子结点保存的是主键和所有其它列信息,涉及到的IO成本就比较高。
count(1)的执行过程
MySQL的server层会维护一个count变量,并循环向InnoDB读取一条记录,InnoDB会优先遍历二级索引,没有二级索引时,才遍历聚簇索引,然后将读取到的记录返回给server层(至此和count(主键字段)一样),但是,不同的地方在于,server层拿到记录后,并不会读取其中的任何字段值,因为是count()函数参数是1,不是字段,所以不会读取任何字段,因为1不为null,所以对于每条记录,count都会++
- 因为count(1)不会读取字段值,所以它的效率比count(字段值)要高
count(*)
count(*)和count(1)执行效率一样,因为,count()底层会将参数转化为0,也就是count(*)会将其当做count(0)来看待
count(二级索引字段)
这个时候会遍历二级索引
count(普通字段)
count的是普通字段,也就是该字段没有添加索引,这个时候效率最低,会走全表扫描
总结
- 性能由大到小:
count(*) = count(1) > 遍历二级索引的count(索引字段) > 遍历 主键索引的count(索引字段) > count(普通字段)
为什么要通过遍历的方式来计数?
- InnoDB采用遍历的方式来计数,是因为它支持事务,同一时刻的多个查询,由于MVCC的原因,InnoDB表返回多少行是不确定的
-
但是像MyISAM存储引擎,它不支持事务,所以在每张MyISAM的数据表中都有一个meta信息,它存储了row_count值,记录了表的记录行数,当查询语句不带任何查询条件时,所以直接读取row_count值就是count函数的执行结果,时间复杂度是O(1)效率高于InnoDB
-
当SQL带上查询条件,InnoDB和MyISAM就没有区别了,因为他们都会扫描表来进行记录个数的统计
如何优化count(*)?
- 近似值
通过 使用show table status 或者 explain 命令进行估算,因为他们不会真正去执行,效率比较高,可以得到近似值
- 额外表保存计数值
插入或者删除主表记录时,同时维护额外表的计数字段,进行+1或者-1