生产环境高并发MySQLSQL语句优化案例

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了生产环境高并发MySQLSQL语句优化案例，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3293字，纯文字阅读大概需要5分钟。

内容图文

生产环境高并发MySQLSQL语句优化10条案例声明：本案例为老男孩linux运维实战培训的数据库优化教学案例，如有转载务必保留本版权声明在实际工作中，运维或DBA人员

?,'?,'?,'绉︽矝','寰愬厠');

最终解决方案：有用到boy_title_upper的索引，慢的原因再观察

案例4：分析归类后的慢查询日志如下。

______________________________________________________________________ 004 ___

Count: 378(6.04%)

Time: 2604 s total, 6.888889 s avg, 3 s to 65 s max(7.63%)

95% of Time : 2031 s total, 5.657382 s avg, 3 s to 17 s max

Lock Time (s) : 0 total, 0 avg, 0 to 0 max(0.00%)

95% of Lock : 0 total, 0 avg, 0 to 0 max

Rows sent: 5 avg, 0 to 10 max(3.24%)

Rows examined : 2.81k avg, 92 to 24.91k max(27.61%)

Database: docresource

Users:

ett_oldboy@ 10.0.1.28 : 100.00% (378) of query, 100.00% (6256) of all users

Query abstract:

SELECT h.boy_id ,MAX(h.boy_his_edit_time) AS boy_his_edit_time FROM t_boy_his h WHERE h.boy_his_isteammate=N AND h.boy_his_state=N AND h.boy_his_editor_user_id_encrypt='S' GROUP BY h.boy_id ORDER BY h.boy_his_edit_time DESC LIMIT N,N;

Query sample:

selecth.boy_id ,max(h.boy_his_edit_time) from t_boy_his hwhere h.boy_his_editor_user_id_encrypt='pGVpWQVlYdglTaQ0Z' and h.boy_his_isteammate=1 and h.boy_his_state=1 group by h.boy_id order by h.boy_his_edit_time desc limit 0,4;

最终解决方案：

alter table t_boy_his add index editor_user_iden_docid(boy_his_editor_user_id_encrypt, boy_id);

1,sql改成：SELECT h.boy_id ,MAX(h.boy_his_edit_time) AS boy_his_edit_time FROM t_boy_his h WHERE h.boy_his_isteammate='pGVpWQVlYdglTaQ0Z' AND h.boy_his_state=1 AND h.boy_his_editor_user_id_encrypt='S' GROUP BY h.boy_id ORDER BY null

2，再在程序里，用java对boy_his_edit_time排序。

案例5：分析归类后的慢查询日志如下。

______________________________________________________________________ 005 ___

Count: 211(3.37%)

Time: 1218 s total, 5.772512 s avg, 3 s to 30 s max(3.57%)

95% of Time : 1022 s total, 5.11 s avg, 3 s to 13 s max

Lock Time (s) : 0 total, 0 avg, 0 to 0 max(0.00%)

95% of Lock : 0 total, 0 avg, 0 to 0 max

Rows sent: 2 avg, 0 to 2 max(0.72%)

Rows examined : 3.61k avg, 110 to 19.59k max(19.81%)

Database:

Users:

ett_oldboy@ 10.0.1.28 : 100.00% (211) of query, 100.00% (6256) of all users

Query abstract:

SELECT h.boy_id ,MAX(h.boy_his_edit_time) AS boy_his_edit_time FROM t_boy_his h WHERE h.boy_his_isteammate=N AND h.boy_his_state=N AND h.boy_his_editor_user_id=N GROUP BY h.boy_id ORDER BY h.boy_his_edit_time DESC LIMIT N,N;

Query sample:

selecth.boy_id ,max(h.boy_his_edit_time) as boy_his_edit_time from t_boy_his hwhere h.boy_his_isteammate=1 and h.boy_his_state=1 and h.boy_his_editor_user_id=300000178518 group by h.boy_id order by h.boy_his_edit_time desc limit 0,2;

最终解决方案：此sql由查前面一个sql代替，删除。

案例6：分析归类后的慢查询日志如下。

______________________________________________________________________ 006 ___

Count: 30(0.48%)

Time: 940 s total, 31.333333 s avg, 3 s to 77 s max(2.76%)

95% of Time : 790 s total, 28.214286 s avg, 3 s to 69 s max

Lock Time (s) : 0 total, 0 avg, 0 to 0 max(0.00%)

95% of Lock : 0 total, 0 avg, 0 to 0 max

Rows sent: 1 avg, 1 to 1 max(0.05%)

Rows examined : 10.96k avg, 1.82k to 28.12k max(8.56%)

Database: docresource

Users:

ett_oldboy@ 10.0.1.28 : 100.00% (30) of query, 100.00% (6256) of all users

Query abstract:

内容总结

以上是互联网集市为您收集整理的生产环境高并发MySQLSQL语句优化案例全部内容，希望文章能够帮你解决生产环境高并发MySQLSQL语句优化案例所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端