Case study of MySQLSQL statement optimization with high concurrency in the production environment

Source: Internet
Author: User
10 cases of high-concurrency MySQLSQL statement optimization in the production environment: This case is a database optimization teaching case for the linux O & M training of Old Boys. If any reposted, you must retain this copyright statement in your actual work, O & M or DBA personnel

10 cases of high-concurrency MySQLSQL statement optimization in the production environment: This case is a database optimization teaching case for the linux O & M training of Old Boys. If any reposted, you must retain this copyright statement in your actual work, O & M or DBA personnel

?, '?, '?, 'Two', 'Atlas taobao ');

Final Solution: Use the boy_title_upper index to check the cause of slowness.

Case 4: analyze the slow query logs after classification as follows.

__________________________________________________________________________ 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.81 k avg, 92 to 24.91 k 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 = 'pgvpwqvlydgl1_0z' 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;

Final Solution:

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

1. Change the SQL statement to 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 = 'pgvpwqvlydgl1_0z' AND h. boy_his_state = 1 AND h. boy_his_editor_user_id_encrypt ='s 'group BY h. boy_id order by null

2. Sort boy_his_edit_time in java.

Case 5: analyze the slow query logs after classification as follows.

__________________________________________________________________________ 005 ___

Count: 211 (3.37%)

Time: 1218 seconds 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.61 k avg, 110 to 19.59 k 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;

Final Solution: This SQL statement is replaced by the previous SQL statement and deleted.

Case 6: analyze the slow query logs after classification as follows.

__________________________________________________________________________ 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.96 k avg, 1.82 k to 28.12 k 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:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.