聊聊MySQL的COUNT(*)的性能

前言

基本职场上的程序员用来统计数据库表的行数都会使用count(*)，count(1)或者count(主键)，那么它们之间的区别和性能你又是否了解呢？

其实程序员在开发的过程中，在一张大表上统计总行数是非常耗时的一个操作，那么我们应该用哪个方法统计会更快呢？

接下来我们就来聊一聊MySQL中统计总行数的方法和性能。

count(*)，count(1)，count(主键)哪个更快？

1、建表并且插入1000万条数据进行实验测试：

# 创建测试表
CREATE TABLE `t6` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `status` tinyint(4) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 创建存储过程插入1000w数据
CREATE PROCEDURE insert_1000w()
BEGIN
  DECLARE i INT;
  SET i=1;
  WHILE i<=10000000 DO
    INSERT INTO t6(name,status) VALUES('god-jiang-666',1);
    SET i=i+1;
  END WHILE;
END;

#调用存储过程，插入1000万行数据
call insert_1000w();

2、分析实验结果

# 花了0.572秒
select count(*) from t6;

# 花了0.572秒
select count(1) from t6;

# 花了0.580秒
select count(id) from t6;

# 花了0.620秒
select count(*) from t6 force index (primary);

从上面的实验我们可以得出，count(*)和count(1)是最快的，其次是count(id)，最慢的是count使用了强制主键的情况。

下面我们继续测试一下它们各自的执行计划：

explain select count(*) from t6;
show warnings;

explain select count(1) from t6;
show warnings;

explain select count(id) from t6;
show warnings;

explain select count(*) from t6 force index (primary);
show warnings;

从上面的实验可以得出这三点：

  1. count(*)被MySQL查询优化器改写成了count(0)，并选择了idx_status索引
  2. count(1)和count(id)都选择了idx_statux索引
  3. 加了force index(primary)之后，走了强制索引

这个idx_status就是相当于是二级辅助索引树，目的就是为了说明： InnoDB在处理count(*)的时候，有辅助索引树的情况下，会优先选择辅助索引树来统计总行数。

为了验证count(*)会优先选择辅助索引树这个结论，我们继续来看看下面的实验：

# 删除idx_status索引，继续执行count(*)
alter table t6 drop index idx_status;

explain select count(*) from t6;

从以上实验可以得出，删除了idx_status这个辅助索引树，count(*)就会选择走主键索引。所以结论：count(*)会优先选择辅助索引，假如没有辅助索引的存在，就会走主键索引。

为什么count(*)会优先选择辅助索引？

在MySQL5.7.18之前，InnoDB通过扫描聚集索引来处理count(*)语句。

从MySQL5.7.18开始，InnoDB通过遍历最小的可用二级索引来处理count(*)语句。如果不存在二级索引，则扫描聚集索引。

新版本为何会使用二级索引来处理count(*)呢？

因为InnoDB二级索引树的叶子节点上存放的是主键，而主键索引树的叶子节点存放的是整行数据，所以二级索引树比主键索引树小。因此查询优化器基于成本考虑，优先选择的是二级索引。所以索引count(*)快于count(主键)。

总结

这篇文章的结论就是count(*)=count(1)>count(id)。

为什么count(id)走了主键索引还会更慢呢？因为count(id)需要取出主键，然后判断不为空，再累加，代价更高。

count(*)是会总计出所有NOT NULL和NULL的字段，而count(id)是不会统计NULL字段的，所以我们在建表的尽量使用NOT NULL并且给它一个默认是空即可。

最后，在以后总计数据库表的总行数的时候，可以大胆的使用count(*)或者count(1)。

参考资料

  • 《高性能MySQL》（第三版）第六章优化COUNT()查询
  • 《MySQL实战45讲》林晓斌

到此这篇关于聊聊MySQL的COUNT(*)的性能的文章就介绍到这了,更多相关MySQL COUNT(*)内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • MYSQL中统计查询结果总行数的便捷方法省去count(*)

    MYSQL的关键词 : SQL_CALC_FOUND_ROWS 查看手册后发现此关键词的作用是在查询时统计满足过滤条件后的结果的总数(不受 Limit 的限制) 例如: 复制代码 代码如下: SELECT SQL_CALC_FOUND_ROWS tid FROM cdb_threads WHERE fid=14 LIMIT 1,10; 假设满足条件的有1000条,这里返回10条. 立即使用 复制代码 代码如下: SELECT found_rows() AS rowcount; 则返回的 rowc

  • 关于mysql innodb count(*)速度慢的解决办法

    innodb引擎在统计方面和myisam是不同的,Myisam内置了一个计数器,所以在使用 select count(*) from table 的时候,直接可以从计数器中取出数据.而innodb必须全表扫描一次方能得到总的数量.要初步解决这个问题,需要做不同于myisam的一些工作: 1.使用第二索引(一般不使用主键索引),并且添加where条件,如: 复制代码 代码如下: select count(*) from product where comp_id>=0 ; show index f

  • MySQL中count(*)、count(1)和count(col)的区别汇总

    前言 count函数是用来统计表中或数组中记录的一个函数,count(*) 它返回检索行的数目, 不论其是否包含 NULL值.最近感觉大家都在讨论count的区别,那么我也写下吧:欢迎留言讨论,话不多说了,来一起看看详细的介绍吧. 1.表结构: dba_jingjing@3306>[rds_test]>CREATE TABLE `test_count` ( -> `c1` varchar(10) DEFAULT NULL, -> `c2` varchar(10) DEFAULT N

  • MySQL中distinct和count(*)的使用方法比较

    首先对于MySQL的DISTINCT的关键字的一些用法: 1．在count 不重复的记录的时候能用到,比如SELECT COUNT( DISTINCT id ) FROM tablename:就是计算talbebname表中id不同的记录有多少条. 2,在需要返回记录不同的id的具体值的时候可以用,比如SELECT DISTINCT id FROM tablename:返回talbebname表中不同的id的具体的值. 3．上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义,比如SE

  • 聊聊MySQL的COUNT(*)的性能

    前言 基本职场上的程序员用来统计数据库表的行数都会使用count(*),count(1)或者count(主键),那么它们之间的区别和性能你又是否了解呢? 其实程序员在开发的过程中,在一张大表上统计总行数是非常耗时的一个操作,那么我们应该用哪个方法统计会更快呢? 接下来我们就来聊一聊MySQL中统计总行数的方法和性能. count(*),count(1),count(主键)哪个更快? 1.建表并且插入1000万条数据进行实验测试: # 创建测试表 CREATE TABLE `t6` ( `id`

  • MySQL中count()和count(1)有何区别以及哪个性能最好详解

    MySQL中count()和count(1)有何区别以及哪个性能最好详解

    目录 前言 哪种 count 性能最好? 为什么要通过遍历的方式来计数? 如何优化 count(*)? *第一种,近似值* 第二种,额外表保存计数值 总结 前言 当我们对一张数据表中的记录进行统计的时候,习惯都会使用 count 函数来统计,但是 count 函数传入的参数有很多种,比如 count(1).count(*).count(字段) 等. 到底哪种效率是最好的呢?是不是 count(*) 效率最差? 我曾经以为 count(*) 是效率最差的,因为认知上 selete * from t

  • 5招带你轻松优化MySQL count(*)查询性能

    目录 前言 1 count(*)为什么性能差 2 如何优化count(*)性能 2.1 增加redis缓存 2.2 加二级缓存 2.3 多线程执行 2.4 减少join的表 2.5 改成ClickHouse 3 count的各种用法性能对比 前言 最近我在公司优化过几个慢查询接口的性能,总结了一些心得体会拿出来跟大家一起分享一下,希望对你会有所帮助. 我们使用的数据库是Mysql8,使用的存储引擎是Innodb.这次优化除了优化索引之外,更多的是在优化count(*). 通常情况下,分页接口一般

  • MySQL中count(*)执行慢的解决方案

    MySQL中count(*)执行慢的解决方案

    目录 一. count(*) 的实现方式 1.实现方式比较 2.为什么InnoDB不像MyISAM一样,也把数字存起来 3.小结 二.计数方法 1.用缓存系统保存计数 2.在数据库保存计数 三.不同的 count 用法 1. count(主键 id) 2.count(1) 3.count(字段) 4.count(*) 前言: 在开发工作中,经常需要计算一个表的行数,比如一个内容系统审核记录总数.这时候我们最先想到是一条 select count(*) from my_table;语句.但是,随着

  • 利用MySQL系统数据库做性能负载诊断的方法

    某大师曾说过,像了解自己的老婆 一样了解自己管理的数据库,个人认为包含了两个方面的了解: 1,在稳定性层面来说,更多的是关注高可用.读写分离.负载均衡,灾备管理等等high level层面的措施(就好比要保证生活的稳定性) 2,在实例级别的来说,需要关注内存.IO.网络,热点表,热点索引,top sql,死锁,阻塞,历史上执行异常的SQL(好比生活品质细节)MySQL的performance_data库和sys库提供了非常丰富的系统日志数据,可以帮助我们更好地了解非常细节的,这里简单地列举出来了

  • 聊聊MySQL中的存储引擎

    基础知识 在关系型数据库中每一个数据表相当于一个文件,而不同的存储引擎则会构建出不同的表类型. 存储引擎的作用是规定数据表如何存储数据,如何为存储的数据建立索引以及如何支持更新.查询等技术的实现. 在Oracle以及SqlServer等数据库中只支持一种存储引擎,故其数据存储管理机制都是一样的,而MySQL中提供了多种存储引擎,用户可以根据不同的需求为数据表选择不同的存储引擎,用户也可以根据自己的需要编写自己的存储引擎. 如处理文本文件可使用txt类型,处理图片可使用png类型 存储引擎 在My

  • MySQL 如何分析查询性能

    查询优化.索引优化和表设计优化是环环相扣的.如果你有丰富的编写MySQL查询语句的经验,你就会知道如何设计表和索引来支持有效的查询.同样的,知晓表设计同样有助于了解表结构如何对查询语句产生影响.因此,即便表设计和索引都设计得很好,但如果查询语句写得很糟糕,那查询的性能也会很糟糕. 在尝试编写快速的查询语句前,务必记住快速都是基于响应时间进行评估的.查询语句是一组由多个子任务组成的大任务,每一个子任务都会消耗时间.为了优化查询,我们需要尽可能地减少子任务的数量,或者让子任务执行得更快. 注:有些时

  • 一文解答为什么MySQL的count()方法这么慢

    一文解答为什么MySQL的count()方法这么慢

    目录 前言 count()的原理 各种count()方法的原理 允许粗略估计行数的场景 必须精确估计行数的场景 总结 前言 mysql用count方法查全表数据,在不同的存储引擎里实现不同,myisam有专门字段记录全表的行数,直接读这个字段就好了.而innodb则需要一行行去算. 比如说,你有一张短信表(sms),里面放了各种需要发送的短信信息. sms建表sql: sms表; 需要注意的是state字段,为0的时候说明这时候短信还未发送. 此时还会有一个异步线程不断的捞起未发送(state=

  • MySQL select count(*)计数很慢优化方案

    MySQL select count(*)计数很慢优化方案

    目录 前言 1. MyISAM存储引擎计数为什么这么快? 2. 能不能手动实现统计总行数 3. InnoDB引擎能否实现快速计数 4. 四种计数方式的性能差别 前言 在日常开发工作中,我经常会遇到需要统计总数的场景,比如:统计订单总数.统计用户总数等.一般我们会使用MySQL 的count函数进行统计,但是随着数据量逐渐增大,统计耗时也越来越长,最后竟然出现慢查询的情况,这究竟是什么原因呢?本篇文章带你一下学习一下. 1. MyISAM存储引擎计数为什么这么快? 我们总有个错觉,就是感觉MyIS

  • 详细解读MySQL中COUNT函数的用法

    MySQL的COUNT函数是最简单的功能,非常有用的计算,预计由一个SELECT语句返回的记录数. 要了解COUNT函数考虑的EMPLOYEE_TBL的的表具有以下记录: mysql> SELECT * FROM employee_tbl; +------+------+------------+--------------------+ | id | name | work_date | daily_typing_pages | +------+------+------------+----

随机推荐