Chinaunix首页 | 论坛 | 博客
  • 博客访问: 480092
  • 博文数量: 111
  • 博客积分: 2332
  • 博客等级: 大尉
  • 技术积分: 1187
  • 用 户 组: 普通用户
  • 注册时间: 2009-08-29 11:22
文章分类

全部博文(111)

文章存档

2013年(9)

2012年(28)

2011年(17)

2010年(28)

2009年(29)

我的朋友

分类: Mysql/postgreSQL

2012-03-16 16:06:03

转自:

最近做的数据量上升了,部分表到了上千条的数据,那个速度真是惨不忍睹啊,一个字“慢”!
分析下SQL的问题吧!手动分析,只能看到网上说的那些优化方法。但是瓶颈在那里呢?可以使用explain 的方式解决,但是还是感觉explain 不够详细。

MySQL5.0.37版本以上支持了,profiling ,据说是Jeremy Cole捐献给MySQL社区版本,呵呵。就说说他的使用吧!

profiling 功能可以了解到sql语句消耗资源的更详细的信息。

show profile 的格式如下:

  1. SHOW PROFILE [type [, type]]
  2.     [FOR QUERY n]
  3.     [LIMIT row_count [OFFSET offset]]

  4. type:
  5.     ALL
  6.   | BLOCK IO
  7.   | CONTEXT SWITCHES
  8.   | CPU
  9.   | IPC
  10.   | MEMORY
  11.   | PAGE FAULTS
  12.   | SOURCE
  13.   | SWAPS

  14. 该方式默认是关闭的。
  15. 可以通过以下语句查看
  16. mysql>select @@profiling;
  17. +————-+
  18. | @@profiling |
  19. +————-+
  20. | 0 |
  21. +————-+
  22. 1 row in set (0.00 sec)

打开功能

mysql>set profiling=1;

执行需要测试的sql 语句:

  1. select inet_ntoa(visit_net) visit_net,sum(totalbytes) as totalbytes,sum(inbytes) as inbytes,sum(totalbytes)-sum(inbytes) as outbytes from
  2.       ( select * from businessflow where userid=9 ) aa
  3.   group by visit_net order by totalbytes DESC limit 0,3

  4. mysql> show profiles\G;

通过指定的Query_ID 来查询指定的sql语句的执行信息:

mysql> show profile for query 1;

  1. +——————————–+———-+
  2. | Status | Duration |
  3. +——————————–+———-+
  4. | starting | 0.000021 |
  5. | checking query cache for query | 0.000085 |
  6. | Opening tables | 0.000036 |
  7. | System lock | 0.000007 |
  8. | Table lock | 0.000071 |
  9. | optimizing | 0.000012 |
  10. | statistics | 0.000013 |
  11. | preparing | 0.000013 |
  12. | executing | 0.000005 |
  13. | Sending data | 0.592819 |
  14. | converting HEAP to MyISAM | 0.026474 |
  15. | Sending data | 0.215715 |
  16. | init | 0.000029 |
  17. | storing result in query cache | 0.000012 |
  18. | optimizing | 0.000005 |
  19. | statistics | 0.000011 |
  20. | preparing | 0.000008 |
  21. | Creating tmp table | 0.000038 |
  22. | executing | 0.000004 |
  23. | Copying to tmp table | 0.235759 |
  24. | converting HEAP to MyISAM | 1.020230 |
  25. | Copying to tmp table on disk | 0.166174 |
  26. | Sorting result | 0.056304 |
  27. | Sending data | 0.000053 |
  28. | end | 0.000004 |
  29. | removing tmp table | 0.003539 |
  30. | end | 0.000008 |
  31. | query end | 0.000004 |
  32. | freeing items | 0.000036 |
  33. | removing tmp table | 0.004107 |
  34. | closing tables | 0.000009 |
  35. | logging slow query | 0.000004 |
  36. | cleaning up | 0.000004 |
  37. +——————————–+———-+
  38. 33 rows in set (0.00 sec)
  39. mysql> show profile cpu for query 1;
  40. +——————————–+———-+———-+————+
  41. | Status | Duration | CPU_user | CPU_system |
  42. +——————————–+———-+———-+————+
  43. | starting | 0.000021 | 0.000000 | 0.000000 |
  44. | checking query cache for query | 0.000085 | 0.000000 | 0.000000 |
  45. | Opening tables | 0.000036 | 0.000000 | 0.000000 |
  46. | System lock | 0.000007 | 0.000000 | 0.000000 |
  47. | Table lock | 0.000071 | 0.000000 | 0.000000 |
  48. | optimizing | 0.000012 | 0.000000 | 0.000000 |
  49. | statistics | 0.000013 | 0.000000 | 0.000000 |
  50. | preparing | 0.000013 | 0.000000 | 0.000000 |
  51. | executing | 0.000005 | 0.000000 | 0.000000 |
  52. | Sending data | 0.592819 | 0.592037 | 0.000000 |
  53. | converting HEAP to MyISAM | 0.026474 | 0.000000 | 0.020002 |
  54. | Sending data | 0.215715 | 0.212014 | 0.008000 |
  55. | init | 0.000029 | 0.000000 | 0.000000 |
  56. | storing result in query cache | 0.000012 | 0.000000 | 0.000000 |
  57. | optimizing | 0.000005 | 0.000000 | 0.000000 |
  58. | statistics | 0.000011 | 0.000000 | 0.000000 |
  59. | preparing | 0.000008 | 0.000000 | 0.000000 |
  60. | Creating tmp table | 0.000038 | 0.000000 | 0.000000 |
  61. | executing | 0.000004 | 0.000000 | 0.000000 |
  62. | Copying to tmp table | 0.235759 | 0.240015 | 0.000000 |
  63. | converting HEAP to MyISAM | 1.020230 | 0.988061 | 0.032002 |
  64. | Copying to tmp table on disk | 0.166174 | 0.160010 | 0.000000 |
  65. | Sorting result | 0.056304 | 0.052004 | 0.008001 |
  66. | Sending data | 0.000053 | 0.000000 | 0.000000 |
  67. | end | 0.000004 | 0.000000 | 0.000000 |
  68. | removing tmp table | 0.003539 | 0.000000 | 0.000000 |
  69. | end | 0.000008 | 0.000000 | 0.000000 |
  70. | query end | 0.000004 | 0.000000 | 0.000000 |
  71. | freeing items | 0.000036 | 0.000000 | 0.000000 |
  72. | removing tmp table | 0.004107 | 0.000000 | 0.008000 |
  73. | closing tables | 0.000009 | 0.000000 | 0.000000 |
  74. | logging slow query | 0.000004 | 0.000000 | 0.000000 |
  75. | cleaning up | 0.000004 | 0.000000 | 0.000000 |
  76. +——————————–+———-+———-+————+
  77. 33 rows in set (0.00 sec)

从上上面可以看到,

  1. | Sending data | 0.592819 |
  2. | converting HEAP to MyISAM | 0.026474 |
  3. | Sending data | 0.215715 |

  4. | Copying to tmp table | 0.235759 |
  5. | converting HEAP to MyISAM | 1.020230 |
  6. | Copying to tmp table on disk | 0.166174 |

以上几项 耗时较长,所以考虑把该表转换为MyISAM 以供查询提高速度。并且提高的值

测试完毕以后 ,关闭参数:

  1. mysql> set profiling=0

再次打开程序,发现等待速度降低。问题有所好转。OK!
参考:http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html






 

阅读(1437) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~