- 浏览: 85647 次
- 性别:
- 来自: 南京
文章分类
最新评论
-
柏新星:
修改 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 2797alter table 外键所在的表名 add const ... -
MySQL CURDATE() 函数
2010-10-19 17:29 806定义和用法 CURDATE() 函数返回当前的日期。 语法 ... -
SQL Date 函数
2010-10-19 17:26 745SQL 日期 当我们处理日期时,最难的任务恐怕是确保所插入 ... -
SQL Date 函数
2010-10-19 17:21 599SQL 日期 当我们处理日期时,最难的任务恐怕是确保所插入 ... -
SQL Date 函数
2010-10-19 17:19 510vdsvdsdvsvsdvdsvdsvdsv -
SQL Date 函数
2010-10-19 17:19 725vdsvdsdvsvsdvdsvdsvdsv -
SQL Date 函数
2010-10-19 17:18 771vdsvdsvvdsvdsdvsvsdvds -
MySQL CURDATE() 函数
2010-10-19 17:18 1059定义和用法 CURDATE() 函数返回当前的日期。 语法 ... -
sql多表查询
2010-10-19 17:14 724当以其中的一个表有两个外键指向另一个表的主键时,我们怎样从两个 ... -
mysql数据类型介绍
2010-10-19 17:13 617<div> <div style=&quo ... -
mysql数据类型介绍
2010-10-19 17:11 604SET(VALUE1,….) 可达8 最大可达64个不同的值。 ... -
mysql数据类型介绍
2010-10-19 17:11 708SET(VALUE1,….) 可达8 最大可达64个不同的值。 ... -
mysql group by 和order by 执行顺序
2010-10-19 17:09 6459Mysql 中group by 和 order by的顺序问题 ... -
千万级的mysql数据库与优化方法
2010-10-19 17:08 1164对查询进行优化,应尽量避免全表扫描,首先应考虑在 where ... -
sql语句优化原则与百万数据优化方案
2010-10-19 17:03 8801、使用索引来更快地遍历表。 缺省情况下建立的索引是非群集索引 ... -
mysql中的if条件语句用法
2010-10-19 16:59 1282· IF(expr1,expr2,expr3) 如果 expr ... -
mysql 5.0存储过程学习总结
2010-10-19 16:56 658mysql存储过程的创建,删除,调用及其他常用命令 mysq ... -
mysql常用命令
2010-10-19 16:43 6321、安装mysql服务。dos窗口进入myasql安装bin目 ...
相关推荐
在MySQL中,`COUNT()`, `GROUP BY`, 和 `ORDER BY` 是三个非常重要的SQL语句组成部分,它们各自承担着不同的职责,同时也常被结合在一起使用以满足复杂的数据查询需求。 `COUNT()` 是一个聚合函数,它用于计算指定...
1. **数据库连接:**使用`mysql_connect`函数建立与MySQL数据库的连接。 2. **计算总记录数:**使用`COUNT(*)`函数统计表中的记录总数。 3. **计算总页数:**通过`intval`函数计算整数部分,再判断是否有余数来决定...
- `SELECT column1, COUNT(*) FROM tbl_name GROUP BY column1;` 分组计数。 7. **数据更新** - `UPDATE tbl_name SET column1 = value WHERE condition;` 更新数据。 8. **数据删除** - `DELETE FROM tbl_...
- `INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);` 插入一行数据。 - `INSERT INTO table_name SELECT * FROM other_table;` 从一个表复制数据到另一个表。 6. **查询操作**: - `SELECT...
)", val1, val2) if err != nil { // 处理错误 } count, _ := result.RowsAffected() // 获取受影响的行数 ``` 7. **事务处理**:在Go中,可以使用`db.Begin()`开始一个事务,然后执行一系列的数据库操作,...
**1. 冒泡排序/快速排序** - **冒泡排序**: 是一种简单的排序算法。它重复地走访过要排序的数列,一次比较两个元素,如果他们的顺序错误就把他们交换过来。走访数列的工作是重复地进行直到没有再需要交换,也就是说...
**1. 分页原理** 分页的核心在于限制每次从数据库中取出的数据量。首先,我们需要确定两个关键参数: - **每页记录数($PageSize)**:决定每一页显示的数据条数。 - **当前页数($CurrentPageID)**:指明用户当前...
推荐的方法是使用`SELECT 1 FROM tablename WHERE col = val LIMIT 1`。这里的`col = val`应替换为实际的判断条件。当MySQL找到匹配的一条记录后,由于LIMIT 1的存在,它会立即停止搜索,不会继续遍历剩余的行。...
- **Region Leader Count**:集群中Leader区域的数量。 - **Region Count**:集群中总的区域数量。 - **TiKV Client RPC Duration**:TiKV客户端RPC请求耗时。 - **PD Leader Schedule Time**:PD调度Leader时间...
- `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;` 计算平均值...
$page_count = (int)($amount / $PageSize) + 1; } else { $page_count = $amount / $PageSize; } } else { $page_count = 0; } // 构建翻页链接 $page_string = ""; if( $page == 1 ) { $page_string .= "第...
$page_count = 1; // 数据量小于每页数量,只有一页 } else { if ($amount % $PageSize) { $page_count = (int)($amount / $page_size) + 1; // 有余数,页数加一 } else { $page_count = $amount / $page_...
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val) order by a.name; ``` 计算同组内比当前行`val`更大的值的数量,如果数量为0,说明当前行是最大值。 同样的,...
$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. **...
- `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...
2. **转换数据**:由于`count.txt`中存储的是文本数据,可能包含非数字字符,因此需要使用`intval()`或`floatval()`函数将其转换为整数或浮点数,确保后续计算的准确性。 3. **增加计数值**:通过`+1`操作增加计数值...
$res = mysql_query("SELECT COUNT(*) FROM joke ", $db); $myrow = mysql_fetch_array($res); $numrows = $myrow[0]; ``` 计算总页数: ```php $pages = intval($numrows / $pagesize); if ($numrows % $pagesize) ...