group by,distinct的使用（30万数据测试）

category d的字段是加索引的。
mysql> select name,category,id from exam group by category;
+--------------+----------+----+
| name | category | id |
+--------------+----------+----+
| hcSvgzyCRKj | Q | 1 |
| XaQrpYuOxkLM | T | 2 |
+--------------+----------+----+
2 rows in set (0.72 sec)

mysql> explain select name,category,id from exam group by category\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: exam
type: index
possible_keys: NULL
key: category
key_len: 1
ref: NULL
rows: 300205
Extra:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show profiles;
+----------+------------+--------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------------------------------------------------+
| 1 | 0.71869025 | select name,category,id from exam group by category |
| 2 | 0.00043475 | explain select name,category,id from exam group by category |
| 3 | 0.00041575 | explain select name,category,id from exam group by category |
+----------+------------+--------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> show profile cpu,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000077 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000045 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000018 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000039 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000040 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000028 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Sorting result | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.718180 | 0.504032 | 0.036002 | 38048 | 0 |
| end | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| closing tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000016 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000028 | 0.000000 | 0.000000 | 0 | 0 |
| freeing items | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
25 rows in set (0.00 sec)
--------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++distinct++++++++++++++++++++++++++++++++++++++++

 

 

mysql> select distinct category from exam;
+----------+
| category |
+----------+
| Q |
| T |
+----------+
2 rows in set (0.01 sec)

mysql> show profile cpu ,block io for query 1;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000035 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000015 | 0.000000 | 0.000000 | 0 | 0 |
| checking privileges on cached | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000025 | 0.000000 | 0.000000 | 0 | 0 |
| sending cached result to clien | 0.000041 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000008 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
8 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+------------------------------------+
| 1 | 0.00015025 | select distinct category from exam |
+----------+------------+------------------------------------+
1 row in set (0.00 sec)

mysql> explain select distinct category from exam\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: exam
type: range
possible_keys: NULL
key: category
key_len: 1
ref: NULL
rows: 7
Extra: Using index for group-by
1 row in set (0.00 sec)