优化SQL的一般步骤

3/7/2017来源:SQL技巧人气:5035

1,通过show status 命令了解各种SQL的执行频率     比如Com_select 记录执行select查询的操作次数,一次查询只累加1 2,定位执行效率较低的SQL语句       1通过慢查询日志定位,2慢查询在查询结束后才记录,使用show PRocesslist查看 3,通过EXPLAIN分析低效SQL的执行计划      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、确定问题并采取相应的措施

show full processlist