`
dengyll
  • 浏览: 93322 次
社区版块
存档分类
最新评论

count(*)与count(col)对比

oracle 
阅读更多

Masql的MyISAM引擎 count(*)与count(col)对比

不带where的条件下:

使用的是Server version: 5.1.34 MySQL Community Server (GPL)

使用count(*)执行结果

mysql> select count(*) from my_cms_25;                 
+----------+
| count(*) |
+----------+
| 1022711 |
+----------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00027000 | select count(*) from my_cms_25 |
+----------+------------+--------------------------------+

使用count(id)执行结果:

mysql> select count(id) from my_cms_25;
+-----------+
| count(id) |
+-----------+
|   1022711 |
+-----------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00027800 | select count(id) from my_cms_25 |
+----------+------------+---------------------------------+
1 row in set (0.00 sec)

为了保证cache,每次执行一条语句,就把mysql重启动.

mysql> select count(x_rank) from my_cms_25;
+---------------+
| count(x_rank) |
+---------------+
|       1022711 |
+---------------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration   | Query                               |
+----------+------------+-------------------------------------+
|        1 | 0.00029200 | select count(x_rank) from my_cms_25 |
+----------+------------+-------------------------------------+
1 row in set (0.00 sec)

count(*)比其他两种都快, 不管是count(*), count(id)还是count(x_rank) 执行时间差的不是太多.

注: id是primary key, x_rank是非index.

 

 

带where从句:

mysql> select count(*) from my_cms_25 where id > 100000;
+----------+
| count(*) |
+----------+
|   922711 |
+----------+
1 row in set (0.55 sec)

mysql> show profiles;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|        1 | 0.54154100 | select count(*) from my_cms_25 where id > 100000 |
+----------+------------+--------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(id) from my_cms_25 where id > 100000;
+-----------+
| count(id) |
+-----------+
|    922711 |
+-----------+
1 row in set (0.52 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------------+
| Query_ID | Duration   | Query                                             |
+----------+------------+---------------------------------------------------+
|        1 | 0.51955100 | select count(id) from my_cms_25 where id > 100000 |
+----------+------------+---------------------------------------------------+
1 row in set (0.00 sec)

 

mysql> select count(*) from my_cms_25 where id > 100;
+----------+
| count(*) |
+----------+
| 1022611 |
+----------+
1 row in set (0.60 sec)

mysql> show profiles;
+----------+------------+-----------------------------------------------+
| Query_ID | Duration   | Query                                         |
+----------+------------+-----------------------------------------------+
|        1 | 0.59619000 | select count(*) from my_cms_25 where id > 100 |
+----------+------------+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(id) from my_cms_25 where id > 100;
+-----------+
| count(id) |
+-----------+
|   1022611 |
+-----------+
1 row in set (0.57 sec)

mysql> show profiles;
+----------+------------+------------------------------------------------+
| Query_ID | Duration   | Query                                          |
+----------+------------+------------------------------------------------+
|        1 | 0.57422700 | select count(id) from my_cms_25 where id > 100 |
+----------+------------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> select count(x_rank) from my_cms_25 where id > 100;
+---------------+
| count(x_rank) |
+---------------+
|       1022611 |
+---------------+
1 row in set (1.31 sec)

mysql> show profiles;
+----------+------------+----------------------------------------------------+
| Query_ID | Duration   | Query                                              |
+----------+------------+----------------------------------------------------+
|        1 | 1.30890800 | select count(x_rank) from my_cms_25 where id > 100 |
+----------+------------+----------------------------------------------------+
1 row in set (0.00 sec)

带where条件的, 测试的结果count(id) > count(*) > count(x_rank)

就是说,count(*)不一定比count(pk)快.

 

 

对于innodb engine的, 叶兄做了一个测试,自己也做了一个测试,测试结果不相同:

http://imysql.cn/2008_06_24_speedup_innodb_count

http://bugs.mysql.com/bug.php?id=19271

 

使用的版本:Server version: 5.1.30-log Source distribution

mysql> select count(*) from relation;
+----------+
| count(*) |
+----------+
| 3010500 |
+----------+
1 row in set (10.60 sec)

mysql> show profiles;
+----------+-------------+-------------------------------+
| Query_ID | Duration    | Query                         |
+----------+-------------+-------------------------------+
|        1 | 10.59423300 | select count(*) from relation |
+----------+-------------+-------------------------------+
1 row in set (0.01 sec)

 

 

mysql> select count(id) from relation;
+-----------+
| count(id) |
+-----------+
|   3010329 |
+-----------+
1 row in set (3.87 sec)

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 3.87004700 | select count(id) from relation |
+----------+------------+--------------------------------+
1 row in set (0.00 sec)

 

 

mysql> select count(*) from relation where id>100;
+----------+
| count(*) |
+----------+
| 3010158 |
+----------+
1 row in set (1.03 sec)

mysql> show profiles;                
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 3.87004700 | select count(id) from relation             |
|        2 | 1.03079800 | select count(*) from relation where id>100 |
+----------+------------+--------------------------------------------+

 

 

mysql> show profiles;
+----------+------------+--------------------------------------------------+
| Query_ID | Duration   | Query                                            |
+----------+------------+--------------------------------------------------+
|        1 | 3.87004700 | select count(id) from relation                   |
|        2 | 1.03079800 | select count(*) from relation where id>100       |
|        3 | 1.01789300 | select count(id) from relation where id>100      |
|        4 | 1.32853100 | select count(*) from relation where infoid > 100 |
+----------+------------+--------------------------------------------------+

 

 

 

 

测试结果不全相同. 不管带不带where条件时count(pk)是比count(*)快的．

count(secondary key) 是没有count(pk)快的。难道bug fix了在5.1.30?

 

感觉应该是pk的存储结构和secondary key不同.这个和官方说的符合,primary key的scan是最快的原因吧.

 

从mysql query optimizer角度上说, count(*) 需要mysql分析更多的column,这个是有一定的overhead的。

但直接count(pk)就没有分析的这个开销.:)

 

下面是更详细,可以看出慢在那一步:
在myisam engine上:

 

 

 

mysql> select count(*) from my_cms_25;
+----------+
| count(*) |
+----------+
| 1022711 |
+----------+
1 row in set (0.00 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000033 |
| checking query cache for query | 0.000088 |
| Opening tables                 | 0.000014 |
| System lock                    | 0.000005 |
| Table lock                     | 0.000026 |
| init                           | 0.000033 |
| optimizing                     | 0.000013 |
| executing                      | 0.000015 |
| end                            | 0.000004 |
| query end                      | 0.000003 |
| freeing items                  | 0.000024 |
| storing result in query cache | 0.000012 |
| logging slow query             | 0.000003 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
14 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+--------------------------------+
| Query_ID | Duration   | Query                          |
+----------+------------+--------------------------------+
|        1 | 0.00027700 | select count(*) from my_cms_25 |
+----------+------------+--------------------------------+
1 row in set (0.00 sec)

 

 

mysql> select count(id) from my_cms_25;
+-----------+
| count(id) |
+-----------+
|   1022711 |
+-----------+
1 row in set (0.00 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000056 |
| checking query cache for query | 0.000077 |
| Opening tables                 | 0.000014 |
| System lock                    | 0.000005 |
| Table lock                     | 0.000025 |
| init                           | 0.000047 |
| optimizing                     | 0.000014 |
| executing                      | 0.000015 |
| end                            | 0.000004 |
| query end                      | 0.000003 |
| freeing items                  | 0.000027 |
| storing result in query cache | 0.000011 |
| logging slow query             | 0.000004 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+
14 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+---------------------------------+
| Query_ID | Duration   | Query                           |
+----------+------------+---------------------------------+
|        1 | 0.00030500 | select count(id) from my_cms_25 |
+----------+------------+---------------------------------+
1 row in set (0.00 sec)

 

 

innodb engine:

 

mysql> select count(*) from sbtest;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (1.98 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000041 |
| checking query cache for query | 0.000082 |
| Opening tables                 | 0.000015 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000042 |
| init                           | 0.000033 |
| optimizing                     | 0.000012 |
| statistics                     | 0.000014 |
| preparing                      | 0.000010 |
| executing                      | 0.000009 |
| Sending data                   | 1.987284 |
| end                            | 0.000019 |
| query end                      | 0.000010 |
| freeing items                  | 0.000078 |
| storing result in query cache | 0.000016 |
| logging slow query             | 0.000005 |
| cleaning up                    | 0.000008 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+-----------------------------+
| Query_ID | Duration   | Query                       |
+----------+------------+-----------------------------+
|        1 | 1.98768200 | select count(*) from sbtest |
+----------+------------+-----------------------------+
1 row in set (0.00 sec)

 

 

 

mysql> select count(id) from sbtest;
+-----------+
| count(id) |
+-----------+
|   1000000 |
+-----------+
1 row in set (1.99 sec)

mysql> show profile;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000034 |
| checking query cache for query | 0.000079 |
| Opening tables                 | 0.000015 |
| System lock                    | 0.000005 |
| Table lock                     | 0.000042 |
| init                           | 0.000037 |
| optimizing                     | 0.000013 |
| statistics                     | 0.000013 |
| preparing                      | 0.000010 |
| executing                      | 0.000021 |
| Sending data                   | 1.982898 |
| end                            | 0.000021 |
| query end                      | 0.000009 |
| freeing items                  | 0.000089 |
| storing result in query cache | 0.000017 |
| logging slow query             | 0.000006 |
| cleaning up                    | 0.000010 |
+--------------------------------+----------+
17 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+------------------------------+
| Query_ID | Duration   | Query                        |
+----------+------------+------------------------------+
|        1 | 1.98331900 | select count(id) from sbtest |
+----------+------------+------------------------------+
1 row in set (0.00 sec)

 

分享到：
| 数据库系统依然很神秘
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

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

    COUNT(1)看起来与COUNT(*)相似，但实际上它们在内部处理上略有不同。COUNT(1)表示统计每一行，无论该行的任何列的值是否为NULL。尽管在大多数情况下，COUNT(*)和COUNT(1)的结果相同，但理论上COUNT(1)可能会稍快一些...

    informix sql函数使用说明大全

    2. **COUNT(DISTINCT col_name)**：返回列中不同值的数量。 3. **SUM(col_name/expression)**：计算表达式的总和。 4. **SUM(DISTINCT col_name)**：计算不同值的总和。 5. **AVG(col_name/expression)**：计算...

    Informix函数大全

    2. **COUNT(DISTINCT col_name)**：返回指定列中唯一值的数量。 - 例如：`SELECT COUNT(DISTINCT col_name) FROM table_name;` 3. **SUM(col_name/expression)**：返回指定列或表达式的总和。 - 例如：`SELECT ...

    MySQL优化总结-查询总条数

    1、COUNT(*)和COUNT(COL) COUNT(*)通常是对主键进行索引扫描，而COUNT(COL)就不一定了，另外前者是统计表中的所有符合的纪录总数，而后者是计算表中所有符合的COL的纪录数。还有有区别的。 优化总结，对于MyISAM表...

    Microsoft SQL Server常用命令_pdf

    - **语法**: `SELECT COUNT(col1) AS ali1 FROM tab1 WHERE col2 = val1;` - **说明**: 计算表`tab1`中满足`col2 = val1`条件的`col1`的数量。 - **应用场景**: 当需要统计特定条件下某列的记录数量时使用。 **11.2...

    ORACLE培训资料

    - **count**: 统计行数，`count(*)`包括所有行，`count(字段)`排除NULL值的行，`count(常量)`与`count(*)`效果相同。 - **HAVING子句**：用于过滤GROUP BY之后的汇总数据，与WHERE子句不同，HAVING可以用于聚合函数...

    hive 函数大全

    - 示例: `SELECT * FROM table WHERE col1 > 10 AND col2 ;` 2. **逻辑或操作(OR)** - 示例: `SELECT * FROM table WHERE col1 > 10 OR col2 ;` 3. **逻辑非操作(NOT)** - 示例: `SELECT * FROM table WHERE NOT ...

    mysql技巧之select count的区别分析

    (3)select count(col_name)方式 分别使用 select count(group_id) select count(user_id) select count(col_null)  通过上述测试结果可以看到，select count(*)和select count(1)都使用了group_id这个最短

    mysql面试题之联合分布

    当我们在表中对列col1、col2和col3建立一个名为test_col1_col2_col3的联合索引时，实际上创建了三个独立的索引：(col1)、(col1,col2)和(col1,col2,col3)。这种索引结构被称为最左前缀匹配原则，意味着查询语句在使用...

    MySQL常用函数大全

    - **COUNT(col)**: 计算指定列`col`中非空值的数量。 - **MIN(col)**: 返回指定列`col`中的最小值。 - **MAX(col)**: 返回指定列`col`中的最大值。 - **SUM(col)**: 返回指定列`col`中所有值的总和。 - **GROUP_...

    SQL2000_SQL2005_SQL2008语句大全word完整版

    十分好的资源 建议大家一定掌握 例如 -合并处理 SELECT col1, col2=CAST(MIN(col2)as varchar) ... WHEN COUNT(*)>=2 THEN ','+CAST(MAX(col2)as varchar) ELSE '' END FROM tb a GROUP BY col1 DROP TABLE tb

    PLSQL高級課程,技巧

    4. **查询重复记录**：`SELECT col_a, col_b, col_c, COUNT(*) FROM table_a HAVING COUNT(*) > 1 GROUP BY col_a, col_b, col_c`这条查询可以找出`table_a`中具有相同`col_a`, `col_b`, 和 `col_c`值的所有重复记录...

    linux c语言操作数据库(连接sqlite数据库)

    int select_callback(void *data,int col_count,char **col_values,char **col_name){ //每条记录回调一次该函数，有多少条就回调多少次 int i; for(i=0;i<col_count;i++) { printf(“%s=%s\n”,col_name[i]...

    总结数据库工程师面试时出现次数较高的查询语句样题

    SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 HAVING COUNT(*) > 1; ``` 12. **删除重复记录**： ```sql DELETE FROM Table a WHERE a.rowid > ANY(SELECT b.rowid FROM table b WHERE a....

    hive函数大全(举例和目录)

    SELECT * FROM table WHERE col1 = 5 AND col2 = 3; ``` ##### 2. 逻辑或操作 `OR` 用于组合两个条件语句，只要有一个条件为真就返回真。 **示例**： ```sql SELECT * FROM table WHERE col1 = 5 OR col2 = 3; ``` ...

Magicbox
Global site tag (gtag.js) - Google Analytics