- 浏览: 83825 次
- 性别:
- 来自: 南京
文章分类
最新评论
-
柏新星:
修改 Include/Image.class.php 第34行 ...
Call to undefined function image_type_to_extension -
ocaicai:
ALTER TABLE `image` ADD
CONS ...
mysql给已存在的表添加外键 -
xbm376:
楼主把问题想复杂了吧? 不过倒是跟你学了些新的sql语法
mysql group by 和order by 执行顺序
今天上网,看到一种说法如下(仅作记录,不做评论):
如果表中没有主键,那么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.
如果表中没有主键,那么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给已存在的表添加外键
2010-12-09 12:15 2764alter table 外键所在的表名 add const ... -
MySQL CURDATE() 函数
2010-10-19 17:29 784定义和用法 CURDATE() 函数返回当前的日期。 语法 ... -
SQL Date 函数
2010-10-19 17:26 721SQL 日期 当我们处理日期时,最难的任务恐怕是确保所插入 ... -
SQL Date 函数
2010-10-19 17:21 571SQL 日期 当我们处理日期时,最难的任务恐怕是确保所插入 ... -
SQL Date 函数
2010-10-19 17:19 475vdsvdsdvsvsdvdsvdsvdsv -
SQL Date 函数
2010-10-19 17:19 659vdsvdsdvsvsdvdsvdsvdsv -
SQL Date 函数
2010-10-19 17:18 725vdsvdsvvdsvdsdvsvsdvds -
MySQL CURDATE() 函数
2010-10-19 17:18 1022定义和用法 CURDATE() 函数返回当前的日期。 语法 ... -
sql多表查询
2010-10-19 17:14 706当以其中的一个表有两个外键指向另一个表的主键时,我们怎样从两个 ... -
mysql数据类型介绍
2010-10-19 17:13 598<div> <div style=&quo ... -
mysql数据类型介绍
2010-10-19 17:11 574SET(VALUE1,….) 可达8 最大可达64个不同的值。 ... -
mysql数据类型介绍
2010-10-19 17:11 677SET(VALUE1,….) 可达8 最大可达64个不同的值。 ... -
mysql group by 和order by 执行顺序
2010-10-19 17:09 6430Mysql 中group by 和 order by的顺序问题 ... -
千万级的mysql数据库与优化方法
2010-10-19 17:08 1139对查询进行优化,应尽量避免全表扫描,首先应考虑在 where ... -
sql语句优化原则与百万数据优化方案
2010-10-19 17:03 8561、使用索引来更快地遍历表。 缺省情况下建立的索引是非群集索引 ... -
mysql中的if条件语句用法
2010-10-19 16:59 1253· IF(expr1,expr2,expr3) 如果 expr ... -
mysql 5.0存储过程学习总结
2010-10-19 16:56 640mysql存储过程的创建,删除,调用及其他常用命令 mysq ... -
mysql常用命令
2010-10-19 16:43 6101、安装mysql服务。dos窗口进入myasql安装bin目 ...
相关推荐
/* function IsSame(val1,val2) */ /* 验证密码匹配 */ /* function CheckEmail(val,mode) */ /* 功能说明:验证Email */ /* function CheckValHeight(val,min,max,mode) */ /* 功能说明:验证用户身高 */ /* ...
/* function IsSame(val1,val2) */ /* 验证密码匹配 */ /* function CheckEmail(val,mode) */ /* 功能说明:验证Email */ /* function CheckValHeight(val,min,max,mode) */ /* 功能说明:验证用户身高 */ /* ...
mysql中order by 排序查询、asc升序、desc降序,group by 分组查询、having 只能用于group by子句、作用于组内,having条件子句可以直接跟函数表达式。使用group by 子句的查询语句需要使用聚合函数。
【作 用】: mysql数据库操作类 【作 者】: Riyan 【版 本】: version 2.0 【修改日期】: 2010/02/11 ※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※ ※※※※※※※※ **/ class ...
哈希表的实现(注:计算对应变量的哈希值需要重载hashTable::hash_val函数),参考实现如下 #include #include using namespace std; /*注:functional里面定义了求哈希值的函数,这里的函数可以不用了*/ namespace ...
c代码-2.给定一个排序链表,删除所有重复的元素,使得每个元素只出现一次。... * int val; * struct ListNode *next; * }; */ struct ListNode* deleteDuplicates(struct ListNode* head){ }
1.以S属性的语法制导定义为基础,将下表的语义规则嵌套在语法分析的过程中,即实现语法制导的翻译过程。 产 生 式 语 义 规 则 L E n print (E.val) E E1 + T E.val := E1 .val + T.val E T E.val := ...
该代码根据已生成的xml,制作VOC2007数据集中的trainval.txt;train.txt;test.txt和val.txt,可自行设置占比
/*$#################################################$*/ /* 程序功能:输入验证 */ /* 函数名称: */ /* function CheckData(valname,val,valimode,limitlen) */ /* 功能说明:验证字符串数据 */ /* function ...
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等,则返回resultN,否则返回default IF(test,t,f) 如果test是真,返回t;否则返回f IFNULL(arg1,arg2) 如果arg1不是空,返回arg1,否则...
VALA编程手册——学习VALA语言的简明手册,是VALA的简易入门教程。希望大家喜欢。
CMMI关于VAL项目过程管理的教材,CMMI三级教材
VAL3应用培训level 1+ CN.pdf
在压缩感知中利用TVAL3算法对二维图像进行重构 在压缩感知中利用TVAL3算法对二维图像进行重构
给出一个所有元素以升序排序的单链表,将它转换成一棵高度平衡的二分查找树 您在真实的面试中是否遇到过这个题?... * this->val = val; * this->next = NULL; * } * } * Definition of TreeNode: * class
–按某一字段分组取最大(小)值所在行的数据 代码如下: /* 数据如下: nameval memo a 2 a2(a的第二个值) a 1 a1–a的第一个值 a 3 a3:a的第三个值 b 1 b1–b的第一个值 b 3 b3:b的第三个值 b 2 b2b2b2b2 b 4 b4b4 b ...
压缩感知,TVAL3的算法,用于单像素相机的图像重构,国外某位大牛写的
这是微软coco数据集中的验证数据数据集2014:val2014.zip,大约包括41K张图片,但不包括这些数据的标签文件。若需要后者,请下载我的“annotations_trainval2014.zip百度云分享”资源