论Mysql中count(1)和count(*)在myisam和innodb中的效率问题

2020-06-28 15:09:00 1104 技术小虫有点萌

论Mysql中count(1)和count(*)在myisam和innodb中的效率问题

记得我刚入行的时候,有次review我的代码,一位同事提出应该用count(1) 代替count(*),因为效率很高。因为当时是review,也并没有给出数据,到底高多少,后来,本小虫本着实事求是的态度,认真的实践了一下,多年以后再次遇到和人讨论这个问题。我觉得我应该用更直观的方式呈现结论,于是就写了这篇文章,不足之处请留言改正

创建两个表

create table tm (
    id int(11),
    name varchar(20)
) engine = myisam;

create table ti (
                    id int(11),
                    name varchar(20)
) engine = innodb;


各插入1310720条数据,数据内容一毛一样

insert into ti select * from tm ;

  • 接下来最一个最简单的查询
image
image
image
image

从结果发现innodb 和myisam explain出来的结果不一样(注:字段无null值),很明显,针对无where查询,innodb会对表进行全表扫描确定行数,MyISAM 比较简单粗暴,直接读取数据表保存的行记录并返回,因此效率很高; 此时我不禁会问一个why? 查看官方文档,内容如下: InnoDB does not keep an internal count of rows in a table because concurrent transactions might “see” different numbers of rows at the same time. Consequently, SELECT COUNT(*) statements only count rows visible to the current transaction.; 我菜菜的翻译一下就是 innodb不会在内部维护一个count字段,因为事务会导致在统一时间返回不同的计数,计数只统计当前食物中可见的行数,但是,作为引擎标兵的innodb不论怎样都会优化一下的

Prior to MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by scanning the clustered index. As of MySQL 5.7.18, InnoDB processes SELECT COUNT(*) statements by traversing the smallest available secondary index unless an index or optimizer hint directs the optimizer to use a different index. If a secondary index is not present, the clustered index is scanned.

在 MySQL 5.7.18 版本之前, InnoDB 引擎通过扫描聚簇索引来处理 SELECT COUNT( * ) 语句,在 MySQL 5.7.18 版本中,如果存在一个更小的二级索引, InnoDB 会横向读取这个来执行 SELECT COUNT(*) 语句。

再看一下对myisam的解释 For MyISAM tables, COUNT(*) is optimized to return very quickly if the SELECT retrieves from one table, no other columns are retrieved, and there is no WHERE clause This optimization only applies to MyISAM tables, because an exact row count is stored for this storage engine and can be accessed very quickly. COUNT(1) is only subject to the same optimization if the first column is defined as NOT NULL. myisam 使用count(*) 的时候,会快速返回结果,前提是没有返回其他列,并且没有where 条件,为什么会比较快的返回结果呢?因为单独有一行存储了行数,但是 count(1) 仅仅适用与非空字段,这个问题马上讨论,

  • count(*) 和count(1) 和count(column)到底有没有差别
    • myisma

select count(*) from tm [2020-06-28 13:42:09] 1 row retrieved starting from 1 in 78 ms (execution: 5 ms, fetching: 73 ms)

select count(1) from tm [2020-06-28 13:49:29] 1 row retrieved starting from 1 in 56 ms (execution: 5 ms, fetching: 51 ms) 针对上面语句,可以看出,在myisam 没有子句的时候两者是没差别的,因为两者都会从表级的meta信息中获取row_count值

那么count(column)表现如何呢

image
image

未加索引的时候使用了全表扫描,查询效率比上面差了60倍左右 select count(id) from tm [2020-06-28 14:06:15] 1 row retrieved starting from 1 in 317 ms (execution: 294 ms, fetching: 23 ms)

增加索引 alter table tm add index name(name); 查询结果

image
image

使用了索引,如果是黄金索引,不会回表查,直接根据索引计数,返回结果,效率比上面要高。这里也反映一个问题,如果有索引,需要计数,尽量count 索引列;

  • innodb 其实通过上面也可以得出结果

count(索引)>count(*)>count(column) 原因如下图

image
image

count(*) 是怎么用到了name这个索引呢,很明显是经过mysql优化器优化了,比起 colunm(索引)多了一步指定字段。

还有一个count(column) 会过滤null值的 INSERT INTO test.tm (id, name) VALUES (1, null); INSERT INTO test.tm (id, name) VALUES (1, null);

image
image
image
image

有不足之处欢迎指正

本文使用 mdnice 排版