1,通过show status 命令了解各种SQL的执行频率

比如Com_select 记录执行select查询的操作次数,一次查询只累加1

2,定位执行效率较低的SQL语句

  1通过慢查询日志定位,2慢查询在查询结束后才记录,使用show processlist查看

3,通过EXPLAIN分析低效SQL的执行计划

file

file

4,通过show profile 分析SQL(>mysql5.0.37)

mysql> select @@have_profiling;
+------------------+
| @@have_profiling |
+------------------+
| YES              |
+------------------+
1 row in set (0.00 sec)  

表示支持

mysql> select @@profiling;
+-------------+
| @@profiling | 
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)    

默认关闭 可以set profiling=1;开启

mysql> show profiles;
+----------+------------+--------------------------------------------+
| Query_ID | Duration   | Query                                      |
+----------+------------+--------------------------------------------+
|        1 | 0.02342575 | select * from br_member where user_id<1000 |
+----------+------------+--------------------------------------------+
1 row in set (0.00 sec)
mysql> show profile for query 1;(这里的1 代表show profiles查出的query_id的值)
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000115 |
| checking permissions | 0.000013 |
| Opening tables       | 0.000044 |
| System lock          | 0.000025 |
| init                 | 0.000097 |
| optimizing           | 0.000019 |
| statistics           | 0.000084 |
| preparing            | 0.000020 |
| executing            | 0.000005 |
| Sending data         | 0.022893 |
| end                  | 0.000012 |
| query end            | 0.000006 |
| closing tables       | 0.000013 |
| freeing items        | 0.000071 |
| logging slow query   | 0.000005 |
| cleaning up          | 0.000004 |
+----------------------+----------+
16 rows in set (0.00 sec)

在获取到最消耗时间的线程状态以后,mysql支持进一步选择all,cpu,block io,context,switch,page faults等明细类型来查看mysql在使用什么资源上耗费了过高的时间,如下选择查看cpu的消耗时间

mysql> show profile cpu for query 1;
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000115 | 0.000000 |   0.000000 |
| checking permissions | 0.000013 | 0.000000 |   0.000000 |
| Opening tables       | 0.000044 | 0.000000 |   0.000000 |
| System lock          | 0.000025 | 0.000000 |   0.000000 |
| init                 | 0.000097 | 0.000000 |   0.000000 |
| optimizing           | 0.000019 | 0.000000 |   0.000000 |
| statistics           | 0.000084 | 0.000000 |   0.000000 |
| preparing            | 0.000020 | 0.000000 |   0.000000 |
| executing            | 0.000005 | 0.000000 |   0.000000 |
| Sending data         | 0.022893 | 0.015600 |   0.000000 |
| end                  | 0.000012 | 0.000000 |   0.000000 |
| query end            | 0.000006 | 0.000000 |   0.000000 |
| closing tables       | 0.000013 | 0.000000 |   0.000000 |
| freeing items        | 0.000071 | 0.000000 |   0.000000 |
| logging slow query   | 0.000005 | 0.000000 |   0.000000 |
| cleaning up          | 0.000004 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+
16 rows in set (0.00 sec)

5、通过trace分析优化器如何选择执行计划

6、确定问题并采取相应的措施

7、show full processlist

查看现在mysql所有进程 看看是不是有大量sleep的进程 有的话说明不正常
kill + 进程号 可以强制关闭进程