bitscn.com
mysql sql tuning：profile定位单条query性能瓶颈
当生了病的query来就诊时，如果身为医生的我们胡庸医乱用虎狼药
不仅于事无补，还浪费了大量的人力和时间成本，甚至会拖垮服务器
所以，我们在接受优化一条sql，第一件事情便是要明白query病在哪里？
是io？是cpu?只有明白瓶颈在哪里，方可对症下药，也才能药到病除
而mysql query profiler是一个使用非常方便的query诊断工具，5.0引入。5.1ga版嵌入
这个工具详细呈现了sql在整个生命周期的每个动作，这和oracle开启1046事件类似
我们可以很清晰地明白该sql是在数据存取还是运算(排序或分组等)上花费得多
那么我们就不会很盲目地看到order by就去tuning sort buffer而忽略sorting result时间是如此之少
profile语法：
[plain] show profile [type [, type] ... ] [for query n] [limit row_count [offset offset]] type: all | block io | context switches | cpu | ipc | memory | page faults | source | swaps
注解：
默认输出结果只会展示status和duration，我们可以指定type来扩展输出
我比较常用的是外加cpu和block io来输出cpu和io的负载，其实这些已经够了
默认profile是关闭的，通过profiling参数控制，为session级
开启：set profiling=1
关闭：set profiling=0
查询：select @@profiling
show profiles保存的query条数由参数profiling_history_size控制，默认是15，超过了会把前面的剔掉
[plain] mysql> set profiling=1; mysql> select @@profiling; +-------------+ | @@profiling | +-------------+ | 1 | +-------------+ mysql> select * from employees.t order by first_name; mysql> show profiles; +----------+------------+-----------------------------------------------+ | query_id | duration | query | +----------+------------+-----------------------------------------------+ | 1 | 0.21138800 | show create table employees.t | | 2 | 8.21691600 | select * from employees.t order by first_name | +----------+------------+-----------------------------------------------+ 2 rows in set (0.00 sec) mysql> show profile cpu,block io for query 2; +----------------------+----------+----------+------------+--------------+---------------+ | status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000160 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | opening tables | 0.000055 | 0.000000 | 0.000000 | 0 | 0 | | system lock | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000050 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000145 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000118 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | sorting result | 2.838465 | 1.396087 | 1.140071 | 0 | 0 | | sending data | 0.928078 | 0.544034 | 0.056003 | 0 | 0 | | end | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000011 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 4.449672 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 17 rows in set (0.00 sec)
相同的信息我们还可以从information_schema里面输出，并且我们还可以对时间进行排序
因为profile默认是按执行顺序排序的，而实际上我们更关心的是花费了多少时间，这才能方便知道哪些开销较大
[plain] mysql> select state, format(duration, 6) as duration -> from information_schema.profiling -> where query_id = 2 order by duration desc; +----------------------+----------+ | state | duration | +----------------------+----------+ | freeing items | 4.449672 | | sorting result | 2.838465 | | sending data | 0.928078 | | starting | 0.000160 | | statistics | 0.000145 | | preparing | 0.000118 | | opening tables | 0.000055 | | init | 0.000050 | | system lock | 0.000033 | | end | 0.000026 | | optimizing | 0.000026 | | checking permissions | 0.000026 | | closing tables | 0.000021 | | logging slow query | 0.000014 | | query end | 0.000011 | | executing | 0.000011 | | cleaning up | 0.000005 | +----------------------+----------+ 17 rows in set (0.00 sec)
bitscn.com