`
日光倾城。
  • 浏览: 85647 次
  • 性别: Icon_minigender_1
  • 来自: 南京
社区版块
存档分类
最新评论

mysql count(*) count(val) count(1)比较(转载)

阅读更多
今天上网,看到一种说法如下(仅作记录,不做评论):

如果表中没有主键,那么count(1)比count(*)快
如果有主键,那么count(主键,联合主键)比count(*)快
如果表中只有一个字段,count(*)最快

再摘一篇类似的英文:http://www.mysqlperformanceblog.com/2007/04/10/count-vs-countcol/

Looking at how people are using COUNT(*) and COUNT(col) it looks like most of them think they are synonyms and just using what they happen to like, while there is substantial difference in performance and even query result.
Lets look at the following series of examples:

PLAIN TEXT
SQL:
CREATE TABLE `fact` (
  `i` int(10) UNSIGNED NOT NULL,
  `val` int(11) DEFAULT NULL,
  `val2` int(10) UNSIGNED NOT NULL,
  KEY `i` (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1


mysql> SELECT count(*) FROM fact;
+----------+
| count(*) |
+----------+
|  7340032 |
+----------+
1 row IN SET (0.00 sec)


mysql> SELECT count(val) FROM fact;
+------------+
| count(val) |
+------------+
|    7216582 |
+------------+
1 row IN SET (1.17 sec)


mysql> SELECT count(val2) FROM fact;
+-------------+
| count(val2) |
+-------------+
|     7340032 |
+-------------+
1 row IN SET (0.00 sec)



As this is MYISAM table MySQL has cached number of rows in this table. This is why it is able to instantly answer COUNT(*) and
COUNT(val2) queries, but not COUNT(val). Why ? Because val column is not defined as NOT NULL there can be some NULL values in it and so MySQL have to perform table scan to find out. This is also why result is different for the second query.
So COUNT(*) and COUNT(col) queries not only could have substantial performance performance differences but also ask different question.
MySQL Optimizer does good job in this case doing full table scan only if it is needed because column can be NULL.
Now lets try few more queries:
PLAIN TEXT
SQL:
mysql> SELECT count(*) FROM fact WHERE i<10000;
+----------+
| count(*) |
+----------+
|   733444 |
+----------+
1 row IN SET (0.40 sec)

mysql> EXPLAIN SELECT count(*) FROM fact WHERE i<10000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: fact
         type: range
possible_keys: i
          KEY: i
      key_len: 4
          ref: NULL
         rows: 691619
        Extra: USING WHERE; USING INDEX
1 row IN SET (0.00 sec)


mysql> SELECT count(val) FROM fact WHERE i<10000;
+------------+
| count(val) |
+------------+
|     720934 |
+------------+
1 row IN SET (1.29 sec)

mysql> EXPLAIN SELECT count(val) FROM fact WHERE i<10000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: fact
         type: range
possible_keys: i
          KEY: i
      key_len: 4
          ref: NULL
         rows: 691619
        Extra: USING WHERE
1 row IN SET (0.00 sec)

mysql> SELECT count(val2) FROM fact WHERE i<10000;
+-------------+
| count(val2) |
+-------------+
|      733444 |
+-------------+
1 row IN SET (1.30 sec)

mysql> EXPLAIN SELECT count(val2) FROM fact WHERE i<10000 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        TABLE: fact
         type: range
possible_keys: i
          KEY: i
      key_len: 4
          ref: NULL
         rows: 691619
        Extra: USING WHERE
1 row IN SET (0.00 sec)



As you can see even if you have where clause performance for count(*) and count(col) can be significantly different. In fact this example shows just 3 times performance difference because all data fits in memory, for IO bound workloads you frequently can see 10 and even 100 times performance difference in this case.
The thing is count(*) query can use covering index even while count(col) can't. Of course you can extend index to be (i,val) and get query to be index covered again but I would use this workaround only if you can't change the query (ie it is third party application) or in case column name is in the query for reason, and you really need count of non-NULL values.
It is worth to note in this case MySQL Optimizer does not do too good job optimizing the query. One could notice (val2) column is not null so count(val2) is same as count(*) and so the query could be run as index covered query. It does not and both queries have to perform row reads in this case.
PLAIN TEXT
SQL:
mysql> ALTER TABLE fact DROP KEY i, ADD KEY(i,val);
Query OK, 7340032 rows affected (37.15 sec)
Records: 7340032  Duplicates: 0  Warnings: 0


mysql> SELECT count(val) FROM fact WHERE i<10000;
+------------+
| count(val) |
+------------+
|     720934 |
+------------+
1 row IN SET (0.78 sec)
As you can see extending index helps in this case but it makes query about 2 times slower compared to count(*) one. This is probably because index becomes about two times longer in this case.
分享到:
评论

相关推荐

    mysql中count(), group by, order by使用详解

    在MySQL中,`COUNT()`, `GROUP BY`, 和 `ORDER BY` 是三个非常重要的SQL语句组成部分,它们各自承担着不同的职责,同时也常被结合在一起使用以满足复杂的数据查询需求。 `COUNT()` 是一个聚合函数,它用于计算指定...

    PHP+MySql分页显示示例分析.

    1. **数据库连接:**使用`mysql_connect`函数建立与MySQL数据库的连接。 2. **计算总记录数:**使用`COUNT(*)`函数统计表中的记录总数。 3. **计算总页数:**通过`intval`函数计算整数部分,再判断是否有余数来决定...

    MySQL常用命令大全

    - `SELECT column1, COUNT(*) FROM tbl_name GROUP BY column1;` 分组计数。 7. **数据更新** - `UPDATE tbl_name SET column1 = value WHERE condition;` 更新数据。 8. **数据删除** - `DELETE FROM tbl_...

    mysql语法操作

    - `INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);` 插入一行数据。 - `INSERT INTO table_name SELECT * FROM other_table;` 从一个表复制数据到另一个表。 6. **查询操作**: - `SELECT...

    mysql方面的go语言源代码.zip

    )", val1, val2) if err != nil { // 处理错误 } count, _ := result.RowsAffected() // 获取受影响的行数 ``` 7. **事务处理**:在Go中,可以使用`db.Begin()`开始一个事务,然后执行一系列的数据库操作,...

    php笔试题 百度

    **1. 冒泡排序/快速排序** - **冒泡排序**: 是一种简单的排序算法。它重复地走访过要排序的数列,一次比较两个元素,如果他们的顺序错误就把他们交换过来。走访数列的工作是重复地进行直到没有再需要交换,也就是说...

    php/php+ajax+mysql分页

    **1. 分页原理** 分页的核心在于限制每次从数据库中取出的数据量。首先,我们需要确定两个关键参数: - **每页记录数($PageSize)**:决定每一页显示的数据条数。 - **当前页数($CurrentPageID)**:指明用户当前...

    mysql 判断记录是否存在方法比较

    推荐的方法是使用`SELECT 1 FROM tablename WHERE col = val LIMIT 1`。这里的`col = val`应替换为实际的判断条件。当MySQL找到匹配的一条记录后,由于LIMIT 1的存在,它会立即停止搜索,不会继续遍历剩余的行。...

    tidb-manual-cn.pdf

    - **Region Leader Count**:集群中Leader区域的数量。 - **Region Count**:集群中总的区域数量。 - **TiKV Client RPC Duration**:TiKV客户端RPC请求耗时。 - **PD Leader Schedule Time**:PD调度Leader时间...

    php数据库简单语法

    - `SELECT * FROM table_name WHERE col BETWEEN val1 AND val2;` 在范围内筛选。 - `SELECT COUNT(*) AS total FROM table_name;` 统计记录数。 - `SELECT AVG(col) AS avg_value FROM table_name;` 计算平均值...

    PHP分页代码详解教程.pdf

    $page_count = (int)($amount / $PageSize) + 1; } else { $page_count = $amount / $PageSize; } } else { $page_count = 0; } // 构建翻页链接 $page_string = ""; if( $page == 1 ) { $page_string .= "第...

    PHP分页详细讲解(有实例)

    $page_count = 1; // 数据量小于每页数量,只有一页 } else { if ($amount % $PageSize) { $page_count = (int)($amount / $page_size) + 1; // 有余数,页数加一 } else { $page_count = $amount / $page_...

    搜集大乌龟,诌老大的sql经典

    select a.* from tb a where 1 &gt; (select count(*) from tb where name = a.name and val &gt; a.val) order by a.name; ``` 计算同组内比当前行`val`更大的值的数量,如果数量为0,说明当前行是最大值。 同样的,...

    PHP分页显示制作详细讲解

    $page_count = 1; } else { if ($amount % $page_size) { $page_count = (int)($amount / $page_size) + 1; } else { $page_count = $amount / $page_size; } } } else { $page_count = 0; } ``` 6. **...

    MYSQL WHERE语句优化

    - `SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2;` - `SELECT key_part2 FROM tbl_name GROUP BY key_part1;` 17. **索引排序**:某些查询可以通过索引本身进行排序,如: - `SELECT...

    计数器PHP实例代码

    2. **转换数据**:由于`count.txt`中存储的是文本数据,可能包含非数字字符,因此需要使用`intval()`或`floatval()`函数将其转换为整数或浮点数,确保后续计算的准确性。 3. **增加计数值**:通过`+1`操作增加计数值...

    php完美结合mysql数据库记录分页显示.pdf

    $res = mysql_query("SELECT COUNT(*) FROM joke ", $db); $myrow = mysql_fetch_array($res); $numrows = $myrow[0]; ``` 计算总页数: ```php $pages = intval($numrows / $pagesize); if ($numrows % $pagesize) ...

Global site tag (gtag.js) - Google Analytics