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

最新推荐文章于 2025-06-14 18:06:36 发布

转载最新推荐文章于 2025-06-14 18:06:36 发布 · 86 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/zhangjun516/archive/2013/01/25/2876411.html

本文通过具体的MySQL查询案例展示了如何利用索引进行查询优化，包括使用explain分析查询计划、对比不同查询方式的效率以及展示索引对于提高查询速度的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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> 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)

转载于:https://www.cnblogs.com/zhangjun516/archive/2013/01/25/2876411.html