mysql优化思路
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql优化思路,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4029字,纯文字阅读大概需要6分钟。
内容图文
![mysql优化思路](/upload/InfoBanner/zyjiaocheng/494/d813b688c1f14973a4b18f91053f0675.jpg)
+----------+------------+--------------------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------------------+ | 2 | 0.00009200 | set profiling=1 | | 5 | 0.02003525 | select count(*) from user where id>2 | +----------+------------+--------------------------------------+
包含一个query_id和执行时间和query语句
通过query_id可以查看到更详细的信息;
show profile cpu ,block io for query 5;
+----------------------+----------+----------+------------+--------------+---------------+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000170 | 0.000000 | 0.000000 | 0 | 0 | | checking permissions | 0.000019 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000033 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000061 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000021 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.010826 | 0.000000 | 0.000000 | 184 | 0 | | preparing | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 0.008731 | 0.008000 | 0.000000 | 0 | 0 | | end | 0.000020 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000018 | 0.000000 | 0.000000 | 0 | 0 | | closing tables | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000032 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000027 | 0.000000 | 0.000000 | 0 | 0 | +----------------------+----------+----------+------------+--------------+---------------+
对mysql服务器的优化不要一上来就去优化sql语句,应该首先观察全局情况,至少要先搞清楚
问题出在哪,应该使用脚本来观察服务器一段时间(一天或更长)的健康状况,比如cpu,io,进程连接数等
最后才分析具体原因处在哪里;针对解决;
通过 mysqladmin来查看mysql的状态;
mysqladmin -P3306 -uroot -p123456 -h127.0.0.1 -r -i 1 ext |awk -F"|" "BEGIN{ count=0; }"‘{ if($2 ~ /Variable_name/ && ((++count)%20 == 1)){ print "----------|---------|--- MySQL Command Status --|----- Innodb row operation ----|-- Buffer Pool Read --"; print "---Time---|---QPS---|select insert update delete| read inserted updated deleted| logical physical";}else if ($2 ~ /Queries/){queries=$3;}else if ($2 ~ /Com_select /){com_select=$3;}else if ($2 ~ /Com_insert /){com_insert=$3;}else if ($2 ~ /Com_update /){com_update=$3;}else if ($2 ~ /Com_delete /){com_delete=$3;}else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}else if ($2 ~ /Uptime / && count >= 2){ printf("|%6d %6d %6d %6d",com_select,com_insert,com_update,com_delete); printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted); printf("|%10d %11d\n",innodb_lor,innodb_phr);}}‘
还可以在 printf的输出重定向到一个文件,这样就可以通过文件数据进行可视化分析;
mysql优化思路
标签:roc tool 可视化 ted 查询 ops slow update 健康
本文系统来源:http://www.cnblogs.com/zox2011/p/7822831.html
内容总结
以上是互联网集市为您收集整理的mysql优化思路全部内容,希望文章能够帮你解决mysql优化思路所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。