我们看到了一个简单的查询，MYSQL内部做了24次操作。
另外，看到了一堆query cache的操作，试着把query_cache_size=0，把query_cache关闭，再次测试：
| mysql> show profile FOR QUERY 1; |
| +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000040 | | checking permissions | 0.000007 | | Opening tables | 0.000015 | | System lock | 0.000010 | | init | 0.000061 | | optimizing | 0.000013 | | statistics | 0.000059 | | preparing | 0.000018 | | executing | 0.000004 | | Sending data | 0.000092 | | end | 0.000006 | | query end | 0.000004 | | closing tables | 0.000008 | | freeing items | 0.000020 | | logging slow query | 0.000003 | | cleaning up | 0.000004 | +----------------------+----------+ 16 rows in set (0.00 sec) |
当开启了query_cache的情况下，需要多操作6次，在这个示例里面多化了0.000087s。
查询这条语句对CPU的使用情况：
| mysql> show profile cpu FOR QUERY 1; |
| +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting | 0.000037 | 0.000000 | 0.000000 | | checking permissions | 0.000009 | 0.000000 | 0.000000 | | Opening tables | 0.000014 | 0.000000 | 0.000000 | | System lock | 0.000009 | 0.000000 | 0.000000 | | init | 0.000059 | 0.000000 | 0.000000 | | optimizing | 0.000009 | 0.000000 | 0.000000 | | statistics | 0.000044 | 0.000000 | 0.000000 | | preparing | 0.000015 | 0.000000 | 0.000000 | | executing | 0.000004 | 0.000000 | 0.000000 | | Sending data | 0.000081 | 0.000000 | 0.000000 | | end | 0.000006 | 0.000000 | 0.000000 | | query end | 0.000004 | 0.000000 | 0.000000 | | closing tables | 0.000008 | 0.000000 | 0.000000 | | freeing items | 0.000021 | 0.000000 | 0.000000 | | logging slow query | 0.000004 | 0.000000 | 0.000000 | | cleaning up | 0.000004 | 0.000000 | 0.000000 | +----------------------+----------+----------+------------+ |
查看io及cpu的消耗
| mysql> show profile block io,cpu for query 1; |
| +--------------------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +--------------------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000018 | NULL | NULL | NULL | NULL | | checking query cache for query | 0.000099 | NULL | NULL | NULL | NULL | | Opening tables | 0.000963 | NULL | NULL | NULL | NULL | | System lock | 0.000015 | NULL | NULL | NULL | NULL | | Table lock | 0.000169 | NULL | NULL | NULL | NULL | | optimizing | 0.000020 | NULL | NULL | NULL | NULL | | statistics | 0.000027 | NULL | NULL | NULL | NULL | | preparing | 0.000018 | NULL | NULL | NULL | NULL | | Creating tmp table | 0.000055 | NULL | NULL | NULL | NULL | | executing | 0.000003 | NULL | NULL | NULL | NULL | | Copying to tmp table | 0.704845 | NULL | NULL | NULL | NULL | | Sending data | 0.130039 | NULL | NULL | NULL | NULL | | optimizing | 0.000029 | NULL | NULL | NULL | NULL | | statistics | 0.000029 | NULL | NULL | NULL | NULL | | preparing | 0.000020 | NULL | NULL | NULL | NULL | | Creating tmp table | 0.000142 | NULL | NULL | NULL | NULL | | executing | 0.000003 | NULL | NULL | NULL | NULL | | Copying to tmp table | 0.000086 | NULL | NULL | NULL | NULL | | Sending data | 0.000067 | NULL | NULL | NULL | NULL | | optimizing | 0.000004 | NULL | NULL | NULL | NULL | | statistics | 0.000005 | NULL | NULL | NULL | NULL | | preparing | 0.000005 | NULL | NULL | NULL | NULL | | executing | 0.000002 | NULL | NULL | NULL | NULL | | Sending data | 0.023963 | NULL | NULL | NULL | NULL | | removing tmp table | 0.003420 | NULL | NULL | NULL | NULL | | Sending data | 0.000005 | NULL | NULL | NULL | NULL | | removing tmp table | 0.003308 | NULL | NULL | NULL | NULL | | Sending data | 0.000006 | NULL | NULL | NULL | NULL | | removing tmp table | 0.000007 | NULL | NULL | NULL | NULL | | Sending data | 0.000009 | NULL | NULL | NULL | NULL | | query end | 0.000003 | NULL | NULL | NULL | NULL | | freeing items | 0.000144 | NULL | NULL | NULL | NULL | | storing result in query cache | 0.000011 | NULL | NULL | NULL | NULL | | logging slow query | 0.000003 | NULL | NULL | NULL | NULL | | cleaning up | 0.000006 | NULL | NULL | NULL | NULL | +--------------------------------+----------+----------+------------+--------------+---------------+ 35 rows in set (0.00 sec) |
