打开分析
set profiling=1;
run your sql1;
run your sql2;
查看sql1,sql2的语句分析
show profiles;
show profile for query 1; //查看sql1的具体分析 +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000164 | | checking permissions | 0.000026 | | Opening tables | 0.000039 | | After opening tables | 0.000023 | | System lock | 0.000022 | | Table lock | 0.000026 | | init | 0.000093 | | optimizing | 0.000049 | | statistics | 0.000208 | | preparing | 0.000051 | | executing | 0.000021 | | Sending data | 0.666047 | | end | 0.000081 | | query end | 0.000026 | | closing tables | 0.000023 | | Unlocking tables | 0.000038 | | freeing items | 0.000040 | | updating status | 0.000041 | | logging slow query | 0.000077 | | cleaning up | 0.000048 | +----------------------+----------+ 20 rows in set (0.00 sec)
show profile ALL for query 1; //查看sql1相关的所有分析【主要看i/o与cpu,下边分析中有各项意义介绍】 +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+ | Status | Duration | CPU_user | CPU_system | Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out | Messages_sent | Messages_received | Page_faults_major | Page_faults_minor | Swaps | Source_function | Source_file | Source_line | +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+ | starting | 0.000164 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | NULL | NULL | NULL | | checking permissions | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | check_access | sql_parse.cc | 6694 | | Opening tables | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 3941 | | After opening tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | open_tables | sql_base.cc | 4189 | | System lock | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 339 | | Table lock | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_lock_tables | lock.cc | 344 | | init | 0.000093 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3794 | | optimizing | 0.000049 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1212 | | statistics | 0.000208 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1580 | | preparing | 0.000051 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | optimize_inner | sql_select.cc | 1606 | | executing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | exec_inner | sql_select.cc | 3436 | | Sending data | 0.666047 | 0.696894 | 0.042993 | 1363 | 63 | 512 | 12504 | 0 | 0 | 0 | 23 | 0 | exec_inner | sql_select.cc | 3618 | | end | 0.000081 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_select | sql_select.cc | 3829 | | query end | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_execute_command | sql_parse.cc | 6241 | | closing tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | close_thread_tables | sql_base.cc | 717 | | Unlocking tables | 0.000038 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_unlock_tables | lock.cc | 426 | | freeing items | 0.000040 | 0.001000 | 0.000000 | 3 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | mysql_parse | sql_parse.cc | 8035 | | updating status | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2382 | | logging slow query | 0.000077 | 0.000000 | 0.000000 | 0 | 0 | 0 | 8 | 0 | 0 | 0 | 0 | 0 | log_slow_statement | sql_parse.cc | 2486 | | cleaning up | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | dispatch_command | sql_parse.cc | 2406 | +----------------------+----------+----------+------------+-------------------+---------------------+--------------+---------------+---------------+-------------------+-------------------+-------------------+-------+-----------------------+---------------+-------------+ 20 rows in set (0.00 sec)
关闭分析
set profiling=0;
只分析CPU,BLOCK IO
SHOW profile CPU,BLOCK IO FOR query 1; +----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000164 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000039 | 0.000000 | 0.000000 | 0 | 0 | | After opening tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000022 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000093 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000049 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000208 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000051 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.666047 | 0.696894 | 0.042993 | 512 | 12504 | | end | 0.000081 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000023 | 0.000000 | 0.000000 | 0 | 0 | | Unlocking tables | 0.000038 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000040 | 0.001000 | 0.000000 | 0 | 0 | | updating status | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000077 | 0.000000 | 0.000000 | 0 | 8 | | cleaning up | 0.000048 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+ 20 rows in set (0.00 sec)
浙公网安备 33010602011771号