MySql的语句分析和性能瓶颈分析：explain ，profi

1. mysql的执行计划内容，使用explain来做分析

explain select * from mail_sms_weather limit 10;
+----+-------------+------------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table            | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | SIMPLE      | mail_sms_weather | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 13438 |   100.00 | NULL  |
+----+-------------+------------------+------------+------+---------------+------+---------+------+-------+----------+-------+
mysql> explain update mail_sms_weather set zonecode='010' where mobile ='13001076987';                     
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table            | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | mail_sms_weather | NULL       | range | PRIMARY       | PRIMARY | 33      | const |    1 |   100.00 | Using where |
+----+-------------+------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

上面分别是select和update的explain语句分析
字段含义（主要的）：
select type：SIMPLE表示除去子查询和union之外的查询
type： ：INDEX表示全索引扫描，ALL表示全表扫描，INDEX比ALL快一些
rows：搜索的行数
fileterd：对符合条件（where或者连接）的记录数的百分比做的悲观估算

2. MySql Profile 定位性能瓶颈
   注意profile的内容来自于information_schema.profiling，使用如下

mysql> show profile;
Empty set, 1 warning (0.00 sec)

mysql> set profile=1;
ERROR 1193 (HY000): Unknown system variable 'profile'
mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show profiles;
Empty set, 1 warning (0.00 sec)

mysql> select * from mail_sms_weather limit 1;
+-------------+----------+--------+
| mobile      | zonecode | status |
+-------------+----------+--------+
| 13001002004 | 010      |      1 |
+-------------+----------+--------+
1 row in set (0.00 sec)

mysql> show profiles;
+----------+------------+----------------------------------------+
| Query_ID | Duration   | Query                                  |
+----------+------------+----------------------------------------+
|        1 | 0.00063725 | select * from mail_sms_weather limit 1 |
+----------+------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show profile cpu for query 1;
+--------------------------------+----------+----------+------------+
| Status                         | Duration | CPU_user | CPU_system |
+--------------------------------+----------+----------+------------+
| starting                       | 0.000115 | 0.000000 |   0.000000 |
| Executing hook on transaction  | 0.000019 | 0.000000 |   0.000000 |
| starting                       | 0.000023 | 0.000000 |   0.000000 |
| checking permissions           | 0.000020 | 0.000000 |   0.000000 |
| Opening tables                 | 0.000067 | 0.000000 |   0.000000 |
| init                           | 0.000017 | 0.000000 |   0.000000 |
| System lock                    | 0.000024 | 0.000000 |   0.000000 |
| optimizing                     | 0.000014 | 0.000000 |   0.000000 |
| statistics                     | 0.000039 | 0.000000 |   0.000000 |
| preparing                      | 0.000037 | 0.000000 |   0.000000 |
| executing                      | 0.000082 | 0.000000 |   0.000000 |
| end                            | 0.000013 | 0.000000 |   0.000000 |
| query end                      | 0.000012 | 0.000000 |   0.000000 |
| waiting for handler commit     | 0.000022 | 0.000000 |   0.000000 |
| closing tables                 | 0.000043 | 0.000000 |   0.000000 |
| freeing items                  | 0.000070 | 0.000000 |   0.000000 |
| cleaning up                    | 0.000024 | 0.000000 |   0.000000 |
+--------------------------------+----------+----------+------------+

show profile cpu for query 1; 这个可以查看cpu的性能瓶颈

当业务量上来后，优化语句查询非常重要，可以有效的节省服务器资源，但是也只是在必要的时候去优化，很多思想是在写语句的时候就注意到了