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

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: