打开profile
mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES |
+------------------+
1 row in set (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> set session profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
使用profile分析SQL，可以看到执行两次后,Send data和sending cached result to clien执行效率的变化
mysql> select count(*) from sakila.payment;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.03 sec)
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------+
| 1 | 0.00020400 | select @@profiling |
| 2 | 0.00008900 | select count(*) from payment |
| 3 | 0.00006800 | show databaes |
| 4 | 0.02102800 | show databases |
| 5 | 0.02847600 | select count(*) from sakila.payment |
本栏目更多精彩内容：http://www.bianceng.cn/database/MySQL/
+----------+------------+-------------------------------------+
5 rows in set (0.00 sec)
mysql> show profile for query 5;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000030 |
| Waiting for query cache lock | 0.000005 |
| checking query cache for query | 0.000043 |
| checking permissions | 0.000007 |
| Opening tables | 0.000027 |
| System lock | 0.000010 |
| Waiting for query cache lock | 0.000010 |
| init | 0.000000 |
| optimizing | 0.023255 |
| statistics | 0.000118 |
| preparing | 0.000041 |
| executing | 0.000033 |
| Sending data | 0.003833 |
| end | 0.000054 |
| query end | 0.000045 |
| closing tables | 0.000045 |
| freeing items | 0.000072 |
| Waiting for query cache lock | 0.000033 |
| freeing items | 0.000785 |
| Waiting for query cache lock | 0.000016 |
| freeing items | 0.000002 |
| storing result in query cache | 0.000005 |
| logging slow query | 0.000003 |
| cleaning up | 0.000004 |
+--------------------------------+----------+
24 rows in set (0.00 sec)
mysql> select count(*) from sakila.payment;
+----------+
| count(*) |
+----------+
| 16049 |
+----------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------+
| 1 | 0.00020400 | select @@profiling |
| 2 | 0.00008900 | select count(*) from payment |
| 3 | 0.00006800 | show databaes |
| 4 | 0.02102800 | show databases |
| 5 | 0.02847600 | select count(*) from sakila.payment |
| 6 | 0.00006900 | select count(*) from sakila.payment |
+----------+------------+-------------------------------------+
6 rows in set (0.00 sec)
mysql> show profile for query 6;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000029 |
| Waiting for query cache lock | 0.000004 |
| checking query cache for query | 0.000007 |
| checking privileges on cached | 0.000004 |
| checking permissions | 0.000008 |
| sending cached result to clien | 0.000012 |
| logging slow query | 0.000002 |
| cleaning up | 0.000003 |
+--------------------------------+----------+
8 rows in set (0.00 sec)