- 浏览: 90358 次
文章分类
最新评论
-
307483857:
微软不给他们钱,他们也会这么干的,,,这不明摆着的事情吗,不支 ...
硬件厂商正配合微软强制我们使用WIN7,WIN8! -
shishangq:
晕,好多种关系都没用过。。。
Hibernate映射解析——七种映射关系 -
hongqiang:
不错@~写的很详细
Hibernate映射解析——七种映射关系 -
lucky16:
还是比较详细的...
Hibernate映射解析——七种映射关系 -
巴巴米:
用的竟然是rose吗。。好久没见过这个东西了。
Hibernate映射解析——七种映射关系
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)
发表评论
-
Oracle SQL*PLUS与用户操作相关的常用命令
2012-04-22 15:31 7071.增加数据库用户:(user01/pwd) [sq ... -
数据库系统依然很神秘
2012-04-22 15:30 815最先听到“数据库”这三个字是大一时,在没有接触一门技术前,总 ... -
处理百万级以上的数据处理
2012-04-20 21:24 1134处理百万级以上的数据提高查询速度的方法: 1.应尽量 ... -
ORACLE常用傻瓜問題1000問(之五)
2012-04-21 21:41 618大家在應用ORACL ... -
ORACLE常用傻瓜問題1000問(之四)
2012-04-21 21:41 612大家在應用ORACLE的時候可能會遇到很多看起來不難 ... -
ORACLE常用傻瓜問題1000問(之三)
2012-04-21 21:41 585大家在應用ORACLE的時候可能會遇到很多看起來 ... -
ORACLE常用傻瓜問題1000問(之二)
2012-04-21 21:41 708大家在應用ORACLE的時候可能會遇到很多看 ... -
ORACLE常用傻瓜問題1000問(之一)
2012-04-21 21:40 675大家在應用ORACLE的時候可能會遇到很多看起來 ... -
java.lang.ClassCastException: oracle.sql.CLOB
2012-04-18 00:11 865oracle.sql.CLOB clob =null; ... -
Oracle看重Java疏远Sun Cloud
2012-04-18 00:10 682Oracle发布的关于Java和Sun Cloud公共计 ... -
How To Install Oracle Java 7 (JDK) In Ubuntu(or linuxdeepin12)
2012-04-18 00:09 1001As you probably know, Oracle ... -
oracle Java源定时执行exe程序
2012-04-18 00:08 1400create or replace and compil ... -
java-oracle 调用程序包
2012-04-18 00:08 1082import java.sql.CallableSta ... -
Flash Recovery Area空间不足导致数据库不能打开或hang住
2012-04-16 21:56 1023ORA-16014错误解决办法 1.问题以及解决过程 SQL& ... -
SQL 优化34条建议
2012-04-16 21:56 722SQL 优化34条建议(1) 选择最有效率的表名顺序(只在基 ... -
Oracle SQL的优化
2012-04-16 21:55 757SQL的优化应该从5个方面进行调整:1.去掉不必要的 ... -
Oracle SQL Loader
2012-04-16 21:54 720一:SQL Loader 的特点 oracle自己带了很多的工 ... -
Oracle 检查命中率的SQL
2012-04-16 21:53 810在数据库启动2小时后,可以通过以下SQL来测试数据库 ...
相关推荐
count()函数是用来统计表中记录的一个函数,返回匹配条件的行数,下面这篇文章主要给大家总结介绍了关于MySQL中count(*)、count(1)和count(col)的区别,文中通过示例代码介绍的非常详细,需要的朋友可以参考下。
设表名为X,字段为C1,C2,C3,.... ...SELECT COUNT(*) AS RowNumber,COL1,COL2 FROM TABLE1 T1 WHERE (SELECT COUNT(*) FROM TABLE1 T2 WHERE T1.COL1 = T2.COL1 AND T1.COL2 = T2.COL2)>1 GROUP BY COL1,COL2
1、COUNT(*)和COUNT(COL) COUNT(*)通常是对主键进行索引扫描,而COUNT(COL)就不一定了,另外前者是统计表中的所有符合的纪录总数,而后者是计算表中所有符合的COL的纪录数。还有有区别的。 优化总结,对于MyISAM表...
(3)select count(col_name)方式 分别使用 select count(group_id) select count(user_id) select count(col_null) 通过上述测试结果可以看到,select count(*)和select count(1)都使用了group_id这个最短
十分好的资源 建议大家一定掌握 例如 -合并处理 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
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]...
min=mt[j].row*mt[j].col*mt[j+step].col +rows[j][0].cost+rows[j+1][step-1].cost;//__page_break__ mid=j; for(i=1;i;i++) { temp=rows[j][i].cost+rows[j+i+1][step-i-1].cost +mt[j].row*mt[j+i].col*mt[j+step...
count = 1 + movingCountCore(threshold, rows, cols, row + 1, col, visited) + movingCountCore(threshold, rows, cols, row, col + 1, visited) + movingCountCore(threshold, rows, cols, row - 1, col, ...
select count(*) as id from User1 2、每页显示几条记录 由自己规定 3、得出一共能分为几页 (allCol+ipage-1)/ipage 4、当前页为第几页 默认值为1,随着变量的变化而变化 5、每页的开始位置和结束位置 指的是结果...
else if(k==m_col*(m_row-1)) { scan_mine(k+1); scan_mine(k-m_col); scan_mine(k-m_col+1); } else if(k==m_col*m_row-1) { scan_mine(k-1); scan_mine(k-m_col); scan_mine(k-m_col-1); } else if(k...
可以完成对分组的数据进行增加排序,partition by可以与以上三个函数联合使用。 利用partition by按照班级名称分组,学生id排序 select s.id, s.name, cid, c.name, row_number() over(partition by c.name order ...
int count = 0, row1, col1; srand(time_t(0));//time do{ row = rand() % 10; col = rand() % 10; printf("%d,%d\n", row, col); if (arr[row][col] == 0){//排除重复的可能 arr[row][col] = -1; ...
总数:select count * as totalcount from table1 求和:select sum(field1) as sumvalue from table1 平均:select avg(field1) as avgvalue from table1 最大:select max(field1) as maxvalue from table1 ...
count <= count + 1; end end always @(posedge clk_500khz or negedge sys_rst_n) if(!sys_rst_n) begin key_col; state; end else begin case (state) 0: begin key_col[3:0]; ...
$col_width = floor(100/$num_products_count); } else { $col_width = floor(100/SHOW_PRODUCT_INFO_COLUMNS_NEW_PRODUCTS); } 改为 $col_width = floor(100/$num_products_count-1); } else { $col_width = floor...
PDOStatement::columnCount PDOStatement::columnCount — 返回结果集中的列数。(PHP 5 >= 5.1.0, PECL pdo >= 0.2.0) 说明 语法 int PDOStatement::columnCount ( void ) 使用PDOStatement::columnCount()返回由 ...
调色板程序及其结果1.嵌入秘密信息 clc; clear; %读入载体图像 [x,map] = imread('lenaindex.bmp','bmp'); wx=x;...[row col]=size(wx);...[msg,count]=fread(msgfid);...count=24;...for i=1:count ...[row col]=ran
RETURNS @returnTable table(id int, col_Value varchar(50)) AS BEGIN declare @thisSplitStr varchar(50) declare @thisSepIndex int declare @lastSepIndex int declare @i int set @lastSepIndex = 0 set @i ...
cell sheet Cells[rowIndex col + columnIndex]; cell PutValue dt Columns[col] ColumnName ; } } private void AddBody DataTable dt int rowIndex int columnIndex { for int r 0; r < dt Rows Count...