性能优化的两个原则：
- 我们通过任务和时间而不是资源来衡量性能。
- 无法测量就无法优化。
完成一项任务所需要的时间分为两部分：执行时间和等待时间。如果优化任务执行时间，最好的办法是通过测量不同子任务花费的时间。对于等待时间相对复杂，可能是系统间接影响。
下面我们主要借助一些工具分析单条查询的效率。
剖析单条查询
使用SHOW PROFILE
开启
profiling参数可以启用SQL剖析。该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等等。 查询是否开启，值为1，则开启：
mysql root@192.168.99.100:afbet> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set
Time: 0.008s
可以通过set profiling=1;和set profiling=0;开启和关闭，注意开启后损耗性能，测试后一定得关闭。如果是session级别，退出session后，会自动关闭。
也可以通过下列命令查询:
mysql root@192.168.99.100:afbet> show variables like '%profil%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set
Time: 0.007s
已经开启，记录数15
当然，如果忘记具体命令使用，可以通过万能的help帮助:help profile
使用
开启后，执行需要分析的指令如，然后show profils：
> show PROFILES
+----------+------------+---------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+---------------------------------------+
| 1 | 0.00012325 | SHOW WARNINGS |
| 2 | 0.0030495 | select count(1) from t_realsports_bet |
+----------+------------+---------------------------------------+
第二条指令就是我们查询指令，然后看详细信息：
> show PROFILE for query 2;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000068 |
| checking permissions | 0.000010 |
| Opening tables | 0.000017 |
| init | 0.000013 |
| System lock | 0.000009 |
| optimizing | 0.000006 |
| statistics | 0.000012 |
| preparing | 0.000011 |
| executing | 0.000004 |
| Sending data | 0.002694 |
| end | 0.000011 |
| query end | 0.000007 |
| closing tables | 0.000009 |
| freeing items | 0.000159 |
| cleaning up | 0.000023 |
+----------------------+----------+
Druation表示持续时间，作为默认输出，可以使用show profile all for query 2获取其他开销细节。profile相关细节可以更灵活地从INFORMATION_SCHEMA中对应的表查询。
>set @query_id=2;
> SELECT STATE, SUM(DURATION) AS Total_R,
-> ROUND(
-> 100 * SUM(DURATION) /
-> (SELECT SUM(DURATION)
-> FROM INFORMATION_SCHEMA.PROFILING
-> WHERE QUERY_ID = @query_id
-> ), 2) AS Pct_R,
-> COUNT(*) AS Calls,
-> SUM(DURATION) / COUNT(*) AS "R/Call"
-> FROM INFORMATION_SCHEMA.PROFILING
-> WHERE QUERY_ID = @query_id
-> GROUP BY STATE
-> ORDER BY Total_R DESC;
+----------------------+----------+-------+-------+--------------+
| STATE | Total_R | Pct_R | Calls | R/Call |
+----------------------+----------+-------+-------+--------------+
| Sending data | 0.002498 | 87.31 | 1 | 0.0024980000 |
| freeing items | 0.000139 | 4.86 | 1 | 0.0001390000 |
| starting | 0.000068 | 2.38 | 1 | 0.0000680000 |
| init | 0.000044 | 1.54 | 1 | 0.0000440000 |
| cleaning up | 0.000021 | 0.73 | 1 | 0.0000210000 |
| Opening tables | 0.000016 | 0.56 | 1 | 0.0000160000 |
| statistics | 0.000011 | 0.38 | 1 | 0.0000110000 |
| end | 0.000011 | 0.38 | 1 | 0.0000110000 |
| preparing | 0.000009 | 0.31 | 1 | 0.0000090000 |
| System lock | 0.000009 | 0.31 | 1 | 0.0000090000 |
| closing tables | 0.000009 | 0.31 | 1 | 0.0000090000 |
| checking permissions | 0.000009 | 0.31 | 1 | 0.0000090000 |
| optimizing | 0.000007 | 0.24 | 1 | 0.0000070000 |
| query end | 0.000006 | 0.21 | 1 | 0.0000060000 |
| executing | 0.000004 | 0.14 | 1 | 0.0000040000 |
+----------------------+----------+-------+-------+--------------+
上面的统计sql来源于《高性能mysql》，可以使用group by，更灵活。
使用SHOW STATUS
SHOW STATUS无法基于时间统计，但可以观察哪些操作代价高，哪些消耗时间较多。如句柄计数器(handle counter)
示例
> FLUSH STATUS;
> select * from t_test_staus where status=0;
> show STATUS where variable_name like 'handler%' or variable_name like 'created%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 2 |
| Handler_commit | 1 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_external_lock | 2 |
| Handler_mrr_init | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 1 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 14551 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 16 |
+----------------------------+-------+
21 rows in set (0.00 sec)
从上述结果可以看到，使用了2个临时表，并没有创建磁盘表。有14551个读操作没有用到索引（Handler_read_rnd_next)。Handler_read_key 值大，说明索引使用高。其他详细说明可以参阅官方文档。
使用EXPLAIN
> explain SELECT t2.id FROM `t_realsports_subbet_selection` t1, `t_realsports_subbet` t2 WHERE t1.selection_id ='1711211453551000003795' AND t1.subBet_id = t2.id AND t2.status = 0
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+--------------------------+
| 1 | SIMPLE | t2 | ALL | PRIMARY | <null> | <null> | <null> | 40365 | Using where |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 304 | test.t2.id,const | 1 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+-------+--------------------------+
2 rows in set
Time: 0.011s
- id：每个被独立执行的操作的标识，表示对象被操作的顺序；ID值大，先被执行；如果相同，执行顺序一般从上到下；
- select_type：数据库引擎将SQL拆分成若干部分的子查询/子操作，每个查询select子句中的查询类型；
- table: 本次子查询所查询的目标数据表;
- type：子查询类型，非常重要的性能衡量点。这个字段项可能显示的值包括：“ALL->index->range->ref->eq_ref->const | system->NULL”这些值所表示的查询性能，从左至右依次增加;
- possible_keys：本次子查询可能使用的索引。如果查询所使用的检索条件可能涉及到多个索引，这里将会列出这些所有的可能性；
- key: 本次子查询最终被选定的执行索引
- key_len： 被选定的索引键的长度；
- ref：表示本次子查询参照的参照条件/参照数据表，参照条件/参照数据表，这个字段的值还可能是一个常量；
- rows： 执行根据目前数据表的实际情况预估的，完成这个子查询需要扫描的数据行数；
- Extra：包含不适合在其他列中显示但十分重要的额外信息 详细说明参考MySQL系列—EXPLAIN 介绍
使用EXPLAIN的局限在于，它只是预估结果，不能使用cache，不能分析insert语句。
参考资料: [1]. 高性能mysql [2]. http://blog.csdn.net/u012758088/article/details/77151548