profile ，explain,

最新推荐文章于 2024-11-17 08:30:00 发布

转载最新推荐文章于 2024-11-17 08:30:00 发布 · 60 阅读

0 ·

CC 4.0 BY-SA版权

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

本文通过具体案例展示了MySQL中不同查询语句的执行效率对比，并详细分析了查询计划及使用临时表和外部排序的情况。

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

mysql> select count(*) from exam;
+----------+
| count(*) |
+----------+
| 300000 |
+----------+
1 row in set (0.08 sec)

mysql> select count(*) from exam group by category='Q'\G;
*************************** 1. row ***************************
count(*): 150000
*************************** 2. row ***************************
count(*): 150000
2 rows in set (0.21 sec)

mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
。。。。。。
| 5 | 0.00011450 | select count(*) from exam |
| 6 | 0.00014025 | select count(*) from exam group by category='Q' |
+----------+------------+-------------------------------------------------+
6 rows in set (0.00 sec)

mysql> show profiles;
+----------+------------+---------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------------------------+
。。。。。。。
|8 | 0.00044975 | explain select count(*) from exam group by category='Q' |
| 9 | 0.20411375 | select count(*) from exam group by category='Q'//|//这种情况会产生表外排序，以及临时表

| 10 | 0.00014475 | select count(*) from exam group by category='Q'

//9/10.之所以差那么多，是因为第二次在缓存里面提取的数据，所以会快

mysql> show profile cpu,block io for query 6;
+--------------------------------+----------+----------+------------+--------------+---------------+
| 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.000017 | 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.000015 | 0.000000 | 0.000000 | 0 | 0 |
| sending cached result to clien | 0.000024 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
8 rows in set (0.00 sec)

mysql> show profile cpu,block io for query 9;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000009 | 0.000000 | 0.000000 | 0 | 0 |
| checking query cache for query | 0.000084 | 0.000000 | 0.000000 | 0 | 0 |
| checking permissions | 0.000014 | 0.000000 | 0.000000 | 0 | 0 |
| Opening tables | 0.000033 | 0.000000 | 0.000000 | 0 | 0 |
| System lock | 0.000017 | 0.000000 | 0.000000 | 0 | 0 |
| Waiting for query cache lock | 0.000034 | 0.000000 | 0.000000 | 0 | 0 |
| init | 0.000043 | 0.000000 | 0.000000 | 0 | 0 |
| optimizing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 |
| statistics | 0.000030 | 0.000000 | 0.000000 | 0 | 0 |
| preparing | 0.000019 | 0.000000 | 0.000000 | 0 | 0 |
| Creating tmp table | 0.000051 | 0.000000 | 0.000000 | 0 | 0 |
| executing | 0.000010 | 0.000000 | 0.000000 | 0 | 0 |
| Copying to tmp table | 0.203542 | 0.204013 | 0.000000 | 0 | 0 |
| Sorting result | 0.000046 | 0.000000 | 0.000000 | 0 | 0 |
| Sending data | 0.000020 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| removing tmp table | 0.000011 | 0.000000 | 0.000000 | 0 | 0 |
| end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
| query end | 0.000008 | 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.000003 | 0.000000 | 0.000000 | 0 | 0 |
| storing result in query cache | 0.000007 | 0.000000 | 0.000000 | 0 | 0 |
| logging slow query | 0.000003 | 0.000000 | 0.000000 | 0 | 0 |
| cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 |
+--------------------------------+----------+----------+------------+--------------+---------------+
29 rows in set (0.00 sec)

------------------------------------------*****************************--------------------------------

mysql> show profiles;
+----------+------------+-------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------+
| 1 | 0.00014750 | select count(*) from exam |
| 2 | 0.10852875 | select count(*) from exam group by category |
| 3 | 0.00011400 | select count(*) from exam group by category='Q' |//这种情况会产生表外排序，以及临时表
| 4 | 0.00011925 | select count(*) from exam group by category |
+----------+------------+-------------------------------------------------+
4 rows in set (0.00 sec)

mysql> explain select count(*) 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: Using index
1 row in set (0.00 sec)

mysql> explain select count(*) from exam group by category='Q'\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: Using index; Using temporary; Using filesort// |//这种情况会产生表外排序，以及临时表
1 row in set (0.01 sec)

转载于:https://www.cnblogs.com/zhangjun516/archive/2013/01/24/2874441.html