MySQL性能报告是什么？

我们怎么知道SQL执行效率的高低呢？

1、直观的方法是看客户端返回的执行时间。

2、还有更精准的做法是，就是查看性能报告。我们需要打开性能分析开关，就能看到MySQL对每一条执行计划的详细报告（该表有一百万行数据。）：

MySQL [test]> set @@profiling = 1;
MySQL [test]> select * from test.tfr order by tf_id limit 10,30;
MySQL [test]> select * from test.tfr order by tf_id limit 10,30;
MySQL [test]> show profiles;
+----------+------------+---------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                 |
+----------+------------+---------------------------------------------------------------------------------------+
|        1 | 0.29652100 | select * from test.tfr order by tf_id limit 10,30 |
|        2 | 0.00013350 | select * from test.tfr order by tf_id limit 10,30 |
+----------+------------+---------------------------------------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

对于同一条语句，第二次查询明显比第一次快，因为MySQL缓存了查询。我们看下第二条SQL语句执行的细节：

MySQL [test]> show profile for query 1;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000042 |
| Waiting for query cache lock   | 0.000005 |
| starting                       | 0.000004 |
| checking query cache for query | 0.000068 |
| checking permissions           | 0.000008 |
| Opening tables                 | 0.000019 |
| init                           | 0.000030 |
| System lock                    | 0.000022 |
| Waiting for query cache lock   | 0.000004 |
| System lock                    | 0.000021 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000016 |
| preparing                      | 0.000012 |
| Sorting result                 | 0.000006 |
| executing                      | 0.000004 |
| Sending data                   | 0.000016 |
| Creating sort index            | 0.292657 |
| end                            | 0.000018 |
| query end                      | 0.000013 |
| closing tables                 | 0.000013 |
| freeing items                  | 0.000012 |
| Waiting for query cache lock   | 0.000004 |
| freeing items                  | 0.000027 |
| Waiting for query cache lock   | 0.000005 |
| freeing items                  | 0.000004 |
| storing result in query cache  | 0.000006 |
| cleaning up                    | 0.000005 |
+--------------------------------+----------+
27 rows in set, 1 warning (0.00 sec)

MySQL [test]> show profile for query 2;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000042 |
| Waiting for query cache lock   | 0.000005 |
| starting                       | 0.000004 |
| checking query cache for query | 0.000008 |
| checking privileges on cached  | 0.000005 |
| checking permissions           | 0.000013 |
| sending cached result to clien | 0.000026 |
| cleaning up                    | 0.000004 |
+--------------------------------+----------+
8 rows in set, 1 warning (0.00 sec)

MySQL执行计划就是在一条SELECT语句前放上关键字EXPLAIN，MySQL解释它将如何处理SELECT，提供有关表如何联合和以什么次序联合的信息。

MySQL EXPLAIN是什么？