- 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或者连接）的记录数的百分比做的悲观估算
- 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的性能瓶颈
当业务量上来后，优化语句查询非常重要，可以有效的节省服务器资源，但是也只是在必要的时候去优化，很多思想是在写语句的时候就注意到了
网友评论