1、执行计划
运行执行计划 可以很明显知道SQL有无走索引,及执行过程。
如:
explain select sql_no_cache a.ID,a.MANAGER_NAME,a.MOBILE,a.MANAGER_PHOTO,a.MANAGER_CORP,(ifnull(b.integral_sum,0)+ifnull(c.USER_SCORE,0)+ifnull(d.USER_SCORE,0)) as sumscore from tb_yytmanager a LEFT JOIN tb_yyt_integral b ON a.ID = b.userid LEFT JOIN tb_yyt_userank c ON a.MOBILE=c.USER_MOBILE LEFT JOIN tb_yyt_userank d ON a.MOBILE=d.USER_MOBILE WHERE a.act_type=0 AND c.SCORE_TYPE=2 AND d.SCORE_TYPE=3 group by a.ID order by sumscore desc limit 10;
+----+-------------+-------+------+----------------------------+--------------+---------+--------------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+----------------------------+--------------+---------+--------------+------+----------------------------------------------------+
| 1 | SIMPLE | c | ref | scoretype,index_SCORE_TYPE | scoretype | 5 | const | 2092 | Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY,index_mobile | index_mobile | 153 | func | 1 | Using index condition; Using where |
| 1 | SIMPLE | b | ref | index_userid | index_userid | 9 | support.a.ID | 1 | NULL |
| 1 | SIMPLE | d | ALL | scoretype,index_SCORE_TYPE | NULL | NULL | NULL | 4166 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+----------------------------+--------------+---------+--------------+------+----------------------------------------------------+
4 rows in set (0.00 sec)
ALL; 表示全表扫描
2、profile
profile 可以很清楚的知道每个过程消耗的时间是多少,从而给出优化方向
2.1 要使用PROFILE ,应先打开profile选项,
mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling | ON |
+---------------+-------+
1 row in set (0.00 sec)
若没有打开,可用下面命令 打开
SQL > SET profiling=1;
2.2 执行你的SQL
2.3 使用下面命令查看
mysql> show profile;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000159 |
| checking permissions | 0.000009 |
| checking permissions | 0.000003 |
| checking permissions | 0.000003 |
| checking permissions | 0.000006 |
| Opening tables | 0.000037 |
| init | 0.000066 |
| System lock | 0.000014 |
| optimizing | 0.000027 |
| statistics | 0.000155 |
| preparing | 0.000053 |
| Creating tmp table | 0.000036 |
| Sorting result | 0.000006 |
| executing | 0.000004 |
| Sending data | 2.208361 |
| Creating sort index | 0.000619 |
| end | 0.000009 |
| removing tmp table | 0.000363 |
| end | 0.000007 |
| query end | 0.000008 |
| closing tables | 0.000017 |
| freeing items | 0.000027 |
| logging slow query | 0.000070 |
| cleaning up | 0.000015 |
+----------------------+----------+
24 rows in set, 1 warning (0.00 sec)
从上可知耗时最多的在Sending data 阶段,这样就可以有针对性的优化了。