在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这一步操作，对性能还是有点影响的。

猜您喜欢

• MySql sql优化之order by desc/asc limit M
• MySQL中无GROUP BY直接HAVING的问题
• 随机获取N条数据库记录的四个方法
• MySQL ORDER BY 的实现分析
• MySQL Order By Rand()效率
• 关于随机抽取order By Rand()的效率问题和改进写法！