MySQL使用profile分析SQL执行状态

发布时间:2017-6-29 10:35:11编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"MySQL使用profile分析SQL执行状态",主要涉及到MySQL使用profile分析SQL执行状态方面的内容,对于MySQL使用profile分析SQL执行状态感兴趣的同学可以参考一下。

打开profile

  1. mysql> select @@have_profiling;  
  2. +------------------+  
  3. | @@have_profiling |  
  4. +------------------+  
  5. | YES              |  
  6. +------------------+  
  7. 1 row in set (0.00 sec)  
  8.   
  9. mysql> select @@profiling;  
  10. +-------------+  
  11. | @@profiling |  
  12. +-------------+  
  13. |           0 |  
  14. +-------------+  
  15. 1 row in set (0.00 sec)  
  16.   
  17. mysql> set session profiling=1;  
  18. Query OK, 0 rows affected (0.00 sec)  
  19.   
  20. mysql> select @@profiling;  
  21. +-------------+  
  22. | @@profiling |  
  23. +-------------+  
  24. |           1 |  
  25. +-------------+  
  26. 1 row in set (0.00 sec)  

使用profile分析SQL,可以看到执行两次后,Send data和sending cached result to clien执行效率的变化

  1. mysql> select count(*) from sakila.payment;  
  2. +----------+  
  3. | count(*) |  
  4. +----------+  
  5. |    16049 |  
  6. +----------+  
  7. 1 row in set (0.03 sec)  
  8.   
  9. mysql> show profiles;  
  10. +----------+------------+-------------------------------------+  
  11. | Query_ID | Duration   | Query                               |  
  12. +----------+------------+-------------------------------------+  
  13. |        1 | 0.00020400 | select @@profiling                  |  
  14. |        2 | 0.00008900 | select count(*) from payment        |  
  15. |        3 | 0.00006800 | show databaes                       |  
  16. |        4 | 0.02102800 | show databases                      |  
  17. |        5 | 0.02847600 | select count(*) from sakila.payment |  
  18. +----------+------------+-------------------------------------+  
  19. 5 rows in set (0.00 sec)  
  1. mysql> show profile for query 5;  
  2. +--------------------------------+----------+  
  3. | Status                         | Duration |  
  4. +--------------------------------+----------+  
  5. | starting                       | 0.000030 |  
  6. | Waiting for query cache lock   | 0.000005 |  
  7. | checking query cache for query | 0.000043 |  
  8. | checking permissions           | 0.000007 |  
  9. | Opening tables                 | 0.000027 |  
  10. | System lock                    | 0.000010 |  
  11. | Waiting for query cache lock   | 0.000010 |  
  12. | init                           | 0.000000 |  
  13. | optimizing                     | 0.023255 |  
  14. | statistics                     | 0.000118 |  
  15. | preparing                      | 0.000041 |  
  16. | executing                      | 0.000033 |  
  17. | Sending data                   | 0.003833 |  
  18. | end                            | 0.000054 |  
  19. | query end                      | 0.000045 |  
  20. | closing tables                 | 0.000045 |  
  21. | freeing items                  | 0.000072 |  
  22. | Waiting for query cache lock   | 0.000033 |  
  23. | freeing items                  | 0.000785 |  
  24. | Waiting for query cache lock   | 0.000016 |  
  25. | freeing items                  | 0.000002 |  
  26. | storing result in query cache  | 0.000005 |  
  27. | logging slow query             | 0.000003 |  
  28. | cleaning up                    | 0.000004 |  
  29. +--------------------------------+----------+  
  30. 24 rows in set (0.00 sec)  
  31.   
  32. mysql> select count(*) from sakila.payment;  
  33. +----------+  
  34. | count(*) |  
  35. +----------+  
  36. |    16049 |  
  37. +----------+  
  38. 1 row in set (0.00 sec)  
  39.   
  40. mysql> show profiles;  
  41. +----------+------------+-------------------------------------+  
  42. | Query_ID | Duration   | Query                               |  
  43. +----------+------------+-------------------------------------+  
  44. |        1 | 0.00020400 | select @@profiling                  |  
  45. |        2 | 0.00008900 | select count(*) from payment        |  
  46. |        3 | 0.00006800 | show databaes                       |  
  47. |        4 | 0.02102800 | show databases                      |  
  48. |        5 | 0.02847600 | select count(*) from sakila.payment |  
  49. |        6 | 0.00006900 | select count(*) from sakila.payment |  
  50. +----------+------------+-------------------------------------+  
  51. 6 rows in set (0.00 sec)  
  52.   
  53. mysql> show profile for query 6;  
  54. +--------------------------------+----------+  
  55. | Status                         | Duration |  
  56. +--------------------------------+----------+  
  57. | starting                       | 0.000029 |  
  58. | Waiting for query cache lock   | 0.000004 |  
  59. | checking query cache for query | 0.000007 |  
  60. | checking privileges on cached  | 0.000004 |  
  61. | checking permissions           | 0.000008 |  
  62. | sending cached result to clien | 0.000012 |  
  63. | logging slow query             | 0.000002 |  
  64. | cleaning up                    | 0.000003 |  
  65. +--------------------------------+----------+  
  66. 8 rows in set (0.00 sec)  

sending data比较耗费cpu

  1. mysql> select min(seq) seq,state,count(*) numb_ops,  
  2.     ->     round(sum(duration),5) sum_dur, round(avg(duration),5) avg_dur,  
  3.     ->     round(sum(cpu_user),5) sum_cpu, round(avg(cpu_user),5) avg_cpu  
  4.     ->     from information_schema.profiling  
  5.     ->     where query_id = 5  
  6.     ->     group by state  
  7.     ->     order by seq;  
  8. +------+--------------------------------+----------+---------+---------+---------+---------+  
  9. | seq  | state                          | numb_ops | sum_dur | avg_dur | sum_cpu | avg_cpu |  
  10. +------+--------------------------------+----------+---------+---------+---------+---------+  
  11. |    2 | starting                       |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |  
  12. |    3 | Waiting for query cache lock   |        4 | 0.00006 | 0.00002 | 0.00100 | 0.00025 |  
  13. |    4 | checking query cache for query |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |  
  14. |    5 | checking permissions           |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |  
  15. |    6 | Opening tables                 |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |  
  16. |    7 | System lock                    |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |  
  17. |    9 | init                           |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |  
  18. |   10 | optimizing                     |        1 | 0.02326 | 0.02326 | 0.00000 | 0.00000 |  
  19. |   11 | statistics                     |        1 | 0.00012 | 0.00012 | 0.00000 | 0.00000 |  
  20. |   12 | preparing                      |        1 | 0.00004 | 0.00004 | 0.00000 | 0.00000 |  
  21. |   13 | executing                      |        1 | 0.00003 | 0.00003 | 0.00000 | 0.00000 |  
  22. |   14 | Sending data                   |        1 | 0.00383 | 0.00383 | 0.00300 | 0.00300 |  
  23. |   15 | end                            |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |  
  24. |   16 | query end                      |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |  
  25. |   17 | closing tables                 |        1 | 0.00005 | 0.00005 | 0.00000 | 0.00000 |  
  26. |   18 | freeing items                  |        3 | 0.00086 | 0.00029 | 0.00000 | 0.00000 |  
  27. |   23 | storing result in query cache  |        1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |  
  28. |   24 | logging slow query             |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |  
  29. |   25 | cleaning up                    |        1 | 0.00000 | 0.00000 | 0.00000 | 0.00000 |  
  30. +------+--------------------------------+----------+---------+---------+---------+---------+  
  31. 19 rows in set (0.00 sec)  
  32.   
  33. mysql> show profile cpu for query 5;  
  34. +--------------------------------+----------+----------+------------+  
  35. | Status                         | Duration | CPU_user | CPU_system |  
  36. +--------------------------------+----------+----------+------------+  
  37. | starting                       | 0.000030 | 0.000000 |   0.000000 |  
  38. | Waiting for query cache lock   | 0.000005 | 0.000000 |   0.000000 |  
  39. | checking query cache for query | 0.000043 | 0.000000 |   0.000000 |  
  40. | checking permissions           | 0.000007 | 0.000000 |   0.000000 |  
  41. | Opening tables                 | 0.000027 | 0.000000 |   0.000000 |  
  42. | System lock                    | 0.000010 | 0.000000 |   0.000000 |  
  43. | Waiting for query cache lock   | 0.000010 | 0.001000 |   0.000000 |  
  44. | init                           | 0.000000 | 0.000000 |   0.000000 |  
  45. | optimizing                     | 0.023255 | 0.000000 |   0.000000 |  
  46. | statistics                     | 0.000118 | 0.000000 |   0.000000 |  
  47. | preparing                      | 0.000041 | 0.000000 |   0.000000 |  
  48. | executing                      | 0.000033 | 0.000000 |   0.000000 |  
  49. | Sending data                   | 0.003833 | 0.002999 |   0.001000 |  
  50. | end                            | 0.000054 | 0.000000 |   0.000000 |  
  51. | query end                      | 0.000045 | 0.000000 |   0.000000 |  
  52. | closing tables                 | 0.000045 | 0.000000 |   0.000000 |  
  53. | freeing items                  | 0.000072 | 0.000000 |   0.000000 |  
  54. | Waiting for query cache lock   | 0.000033 | 0.000000 |   0.000000 |  
  55. | freeing items                  | 0.000785 | 0.000000 |   0.000000 |  
  56. | Waiting for query cache lock   | 0.000016 | 0.000000 |   0.000000 |  
  57. | freeing items                  | 0.000002 | 0.000000 |   0.000000 |  
  58. | storing result in query cache  | 0.000005 | 0.000000 |   0.000000 |  
  59. | logging slow query             | 0.000003 | 0.000000 |   0.000000 |  
  60. | cleaning up                    | 0.000004 | 0.000000 |   0.000000 |  
  61. +--------------------------------+----------+----------+------------+  
  62. 24 rows in set (0.00 sec)  
  63.   
  64. mysql> show profile block io for query 5;  
  65. +--------------------------------+----------+--------------+---------------+  
  66. | Status                         | Duration | Block_ops_in | Block_ops_out |  
  67. +--------------------------------+----------+--------------+---------------+  
  68. | starting                       | 0.000030 |            0 |             0 |  
  69. | Waiting for query cache lock   | 0.000005 |            0 |             0 |  
  70. | checking query cache for query | 0.000043 |            0 |             0 |  
  71. | checking permissions           | 0.000007 |            0 |             0 |  
  72. | Opening tables                 | 0.000027 |            0 |             0 |  
  73. | System lock                    | 0.000010 |            0 |             0 |  
  74. | Waiting for query cache lock   | 0.000010 |            0 |             0 |  
  75. | init                           | 0.000000 |            0 |             0 |  
  76. | optimizing                     | 0.023255 |            0 |             0 |  
  77. | statistics                     | 0.000118 |            0 |             0 |  
  78. | preparing                      | 0.000041 |            0 |             0 |  
  79. | executing                      | 0.000033 |            0 |             0 |  
  80. | Sending data                   | 0.003833 |            0 |             0 |  
  81. | end                            | 0.000054 |            0 |             0 |  
  82. | query end                      | 0.000045 |            0 |             0 |  
  83. | closing tables                 | 0.000045 |            0 |             0 |  
  84. | freeing items                  | 0.000072 |            0 |             0 |  
  85. | Waiting for query cache lock   | 0.000033 |            0 |             0 |  
  86. | freeing items                  | 0.000785 |            0 |             0 |  
  87. | Waiting for query cache lock   | 0.000016 |            0 |             0 |  
  88. | freeing items                  | 0.000002 |            0 |             0 |  
  89. | storing result in query cache  | 0.000005 |            0 |             0 |  
  90. | logging slow query             | 0.000003 |            0 |             0 |  
  91. | cleaning up                    | 0.000004 |            0 |             0 |  
  92. +--------------------------------+----------+--------------+---------------+  
  93. 24 rows in set (0.00 sec)  

再看同结构数据的MyISAM表

  1. mysql> create table payment_myisam like payment;  
  2. Query OK, 0 rows affected (0.03 sec)  
  3.   
  4. mysql> alter table payment_myisam engine=myisam;  
  5. Query OK, 0 rows affected (0.03 sec)  
  6. Records: 0  Duplicates: 0  Warnings: 0  
  7.   
  8. mysql> show create table payment_myisam\G  
  9. *************************** 1. row ***************************  
  10.        Table: payment_myisam  
  11. Create Table: CREATE TABLE `payment_myisam` (  
  12.   `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,  
  13.   `customer_id` smallint(5) unsigned NOT NULL,  
  14.   `staff_id` tinyint(3) unsigned NOT NULL,  
  15.   `rental_id` int(11) DEFAULT NULL,  
  16.   `amount` decimal(5,2) NOT NULL,  
  17.   `payment_date` datetime NOT NULL,  
  18.   `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,  
  19.   PRIMARY KEY (`payment_id`),  
  20.   KEY `idx_fk_staff_id` (`staff_id`),  
  21.   KEY `idx_fk_customer_id` (`customer_id`),  
  22.   KEY `fk_payment_rental` (`rental_id`)  
  23. ) ENGINE=MyISAM DEFAULT CHARSET=utf8  
  24. 1 row in set (0.00 sec)  
  25.   
  26. mysql> insert into payment_myisam select * from payment;  
  27. Query OK, 16049 rows affected (0.10 sec)  
  28. Records: 16049  Duplicates: 0  Warnings: 0  
  1. mysql> select count(*) from payment_myisam;  
  2. +----------+  
  3. | count(*) |  
  4. +----------+  
  5. |    16049 |  
  6. +----------+  
  7. 1 row in set (0.01 sec)  
  8.   
  9. mysql> show profiles;  
  10.                                                                                                                                                                           
  11. |       23 | 0.09449600 | insert into payment_myisam select * from payment                                                                                                                                                                                                                                        |  
  12. |       24 | 0.00023500 | select count(*) from payment_myisam                                                                                                                                                                                                                                                     |  
  13.   
  14. 15 rows in set (0.00 sec)  
  1. mysql> show profile for query 24;  
  2. +--------------------------------+----------+  
  3. | Status                         | Duration |  
  4. +--------------------------------+----------+  
  5. | starting                       | 0.000026 |  
  6. | Waiting for query cache lock   | 0.000003 |  
  7. | checking query cache for query | 0.000037 |  
  8. | checking permissions           | 0.000006 |  
  9. | Opening tables                 | 0.000015 |  
  10. | System lock                    | 0.000009 |  
  11. | Waiting for query cache lock   | 0.000019 |  
  12. | init                           | 0.000011 |  
  13. | optimizing                     | 0.000006 |  
  14. | executing                      | 0.000006 |  
  15. | end                            | 0.000000 |  
  16. | query end                      | 0.000001 |  
  17. | closing tables                 | 0.000008 |  
  18. | freeing items                  | 0.000019 |  
  19. | Waiting for query cache lock   | 0.000008 |  
  20. | freeing items                  | 0.000013 |  
  21. | Waiting for query cache lock   | 0.000008 |  
  22. | freeing items                  | 0.000008 |  
  23. | storing result in query cache  | 0.000015 |  
  24. | logging slow query             | 0.000008 |  
  25. | cleaning up                    | 0.000009 |  
  26. +--------------------------------+----------+  

可以看到在count操作MyISAM表时没有sending data这个状态,和Innodb不同,MyISAM不需要访问表数据,所以count操作比Innodb快。

原文链接:http://blog.csdn.net/staricqxyz/article/details/18943001



上一篇:【数据库
下一篇:iOS Hacker dumpdecrypted脱壳

相关文章

相关评论

本站评论功能暂时取消,后续此功能例行通知。

一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!

二、互相尊重,对自己的言论和行为负责。

好贷网好贷款