mysql中count(*)的效率与优化方法

2015-03-13 21:26:01
mysql优化是个细活,虽然有些通用的原则,但是我们还要针对具体的场景,找到最影响性能的环节。
mysql中,我们经常用 select count(*) from tablename 来返回某个表中的总行数,
或者 select count(*) from tablename where column="abc" 来返回符合某个条件的记录数。

在网上经常能看到有人在讨论这样的问题,
count(*)快还是count(column)快? count(*)和count(0)有啥不同?

其实,关于mysql中count的优化,还是要看具体的场景,比如存储引擎是myisam还是innodb,
字段的属性设置,索引的设置等等,具体场景具体分析,不能一概而论,
但是前提是我们要知道count是怎么工作的。

我们以下面这个表作为测试数据表,这是一个文章表,包含标题、简介、内容、栏目id等字段,
其中id是主键,在栏目id字段上建立索引,存储引擎是MyIsam 。
CREATE TABLE `cms_articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` varchar(2000) DEFAULT NULL,
  `content` mediumtext NOT NULL,
  `columnId` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `columnId` (`columnId`),
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1、count(*)与count(column)的区别
我们先看看这两种写法的作用吧,
 select count(*) from cms_articles where columnId=20   
这句话的意思是,查询出文章表中,栏目id=20的记录数,也就是行数。

 select count(title) from cms_articles where columnId=20  
这里不用 count(*)了,而是用一个具体的字段值,这里用的标题title,
这句话的意思是,查询文章表中,栏目id为20的记录中,title不为NULL的条数。

现在大家应该明白了这两种写法的区别吧,
count(*)返回所有的符合条件的行,
count(colname)返回SELECT语句检索到的行中非NULL值的数目。

知道了两种写法的区别,我们再来看执行效率的问题

mysql> SELECT count(*) FROM cms_articles;
+----------+
| count(*) |
+----------+
|    32385 |
+----------+
1 row in set (0.00 sec)

mysql> select count(title) from cms_articles;
+--------------+
| count(title) |
+--------------+
|        32385 |
+--------------+
1 row in set (0.00 sec)

mysql> SELECT count(`description`) FROM cms_articles;
+----------------------+
| count(`description`) |
+----------------------+
|                32385 |
+----------------------+
1 row in set (0.14 sec)


上面三种查询,都返回文章表中的总记录数,共三万多条数据,
但是第一、二两个查询速度很快, 而第三个查询明显耗时增加,
这是怎么回事呢?

这里我们插一句,由于MySQL独特的存储引擎架构,很多SQL的执行、优化都是在存储引擎层进行的,
这就需要我们对不同的存储引擎有所了解,最主要的两种存储引擎就是MyIsam和 InnoDB。
而在MyIsam引擎中,每个表的行数是直接存储在引擎中的,也就是说,计算表的行数的时候,
不需要一行一行的去计算,只要读取一下引擎中的行数值就行了,所以速度会非常的快。

现在,有了前面的介绍,我们稍微想一想就能想明白,为什么三个查询会有不一样的效率。
SELECT count(*) FROM cms_articles; 
这条查询的意思是,返回cms_articles表的行数, 而在MyIsam表中,这个行数是事先缓存好的,所以返回很快。

select count(title) from cms_articles; 
这条查询的意思是,返回cms_articles表中,title不为NULL的行数,而在表结构中,我们看到title字段是不允许为NULL的,
在这种情况下, MyIsam引擎也会直接返回表的行数,所以也会很快。

SELECT count(`description`) FROM cms_articles; 
这条查询是返回 cms_articles表中,description字段不为NULL的行数,而这个description字段,我们是设置了可以为NULL的
也就是说,结果中结果中可能存在description为NULL的记录,
所以,就没有办法利用MyIsam提前缓存的总行数了,而只能一行行的去计算,效率就会降低。


2、count(*)和count(0)有啥不同?
这两种写法,在效率上应该是等同的,都是计算符合条件的记录数,时间都是花在记录的筛选上。