group by与order by null

在post_author及post_date上建个联合索引

mysql> desc wp_posts;
+———————–+———————+——+—–+———————+—————-+
| Field | Type | Null | Key | Default | Extra |
+———————–+———————+——+—–+———————+—————-+
| ID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| post_author | bigint(20) unsigned | NO | | 0 | |
| post_date | datetime | NO | | 0000-00-00 00:00:00 | |
—————————————————————————————————
mysql> create index ind_wp_posts_aut on wp_posts(post_author,post_date);
Query OK, 1336 rows affected (0.13 sec)
Records: 1336 Duplicates: 0 Warnings: 0

来看一下,利用索引有的有序性,消除排序。

mysql> explain select max(post_date),post_author from wp_posts
-> group by post_author \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wp_posts
type: range
possible_keys: NULL
key: ind_wp_posts_aut
key_len: 8
ref: NULL
rows: 2
Extra: Using index for group-by —-这里看到不用Using filesort
1 row in set (0.01 sec)

再看下面一种情况:

mysql> create index ind_wp_posts_idaut on wp_posts(id,post_author);
Query OK, 1336 rows affected (0.12 sec)
Records: 1336 Duplicates: 0 Warnings: 0
mysql> explain select max(post_date),post_author from wp_posts
-> where id > 10 and id < 1000
-> group by post_author \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wp_posts
type: range
possible_keys: PRIMARY,ind_wp_posts_idaut
key: ind_wp_posts_idaut
key_len: 8
ref: NULL
rows: 882
Extra: Using where; Using temporary; Using filesort —-这里用TEMP来进行排序,性能比较差!
1 row in set (0.00 sec)

如果无法利用索引来完成排序,我们可以在后面加个order by null,来消除Using filesort

mysql> explain select max(post_date),post_author from wp_posts
-> where id > 10 and id < 1000
-> group by post_author order by null\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: wp_posts
type: range
possible_keys: PRIMARY,ind_wp_posts_idaut
key: ind_wp_posts_idaut
key_len: 8
ref: NULL
rows: 882
Extra: Using where; Using temporary —-这里没有Using filesort
1 row in set (0.00 sec)

下面来看一下,性能上的差别

mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)

mysql> select max(post_date),post_author from wp_posts
-> where id > 10 and id < 1000
-> group by post_author;
+———————+————-+
| max(post_date) | post_author |
+———————+————-+
| 2009-07-03 12:58:39 | 1 |
+———————+————-+
1 row in set (0.01 sec)

mysql> show profiles;
+———-+————+————————+
| Query_ID | Duration | Query |
+———-+————+————————+
| 1 | 0.00013200 | SELECT DATABASE() |
| 2 | 0.00030900 | show databases |
| 3 | 0.00030400 | show tables |
| 4 | 0.01180000 | select max(post_date),post_author from wp_posts where id > 10 and id < 1000 group by post_author |4 rows in set (0.00 sec)

mysql> show profile cpu,block io for query 4;
+———————-+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+———————-+———-+———-+————+————–+—————+
| starting | 0.000085 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000029 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000062 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.011386 | 0.004999 | 0.006999 | 0 | 0 |
| Sorting result | 0.000044 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000036 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
+———————-+———-+———-+————+————–+—————+
22 rows in set (0.00 sec)

mysql>
mysql>
mysql> select max(post_date),post_author from wp_posts
-> where id > 10 and id < 1000
-> group by post_author order by null;
+———————+————-+
| max(post_date) | post_author |
+———————+————-+
| 2009-07-03 12:58:39 | 1 |
+———————+————-+
1 row in set (0.01 sec)

mysql> show profiles;
+———-+————+—————–+
| Query_ID | Duration | Query
+———-+————+—————–+
|1 | 0.00013200 | SELECT DATABASE()
|2 | 0.00030900 | show databases
|3 | 0.00030400 | show tables
|4 | 0.01180000 | select max(post_date),post_author from wp_posts where id > 10 and id < 1000 group by post_author
|5 | 0.01177700 | select max(post_date),post_author from wp_posts where id > 10 and id < 1000 group by post_author order by null
5 rows in set (0.00 sec)
mysql> show profile cpu,block io for query 5;
+———————-+———-+———-+————+————–+—————+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+———————-+———-+———-+————+————–+—————+
| starting | 0.000097 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000013 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000006 | 0.000000 | 0.000000 | 0 | 0 |
| Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000032 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000065 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.011369 | 0.005999 | 0.004999 | 0 | 0 |
| Sending data | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000031 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
+———————-+———-+———-+————+————–+—————+
21 rows in set (0.00 sec)

从这里可以看出少了Sorting result这一步操作,对性能还是有点影响的。

觉得文章有用?立即: 和朋友一起 共学习 共进步!

猜您喜欢

2 thoughts on “group by与order by null

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

*

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>