MySQL solves the stress problem of online database server by adding an index

Yesterday on-line app feedback night19:30-19:43 and 20:13 to 20:21 two time periods everyone, including our own classrooms, can't get in,

classroom b Set Unable to login, homepage inaccessible, More than 10 teachers and students have received feedback on not going to the classroom.

By monitoring the database server's CPU usage, load,io, memory usage, swap remainder, etc., when the problem occurred, the database was found to be under a very high pressure,

Number of database connections:

Database Slow query also appears a lot

Look at the slow query log and find that a SQL frequently has a long cut execution time in the slow query log

# [email protected]: cms[cms] @  [172.17.43.24]# query_time: 10.252490   Lock_time: 0.000052 Rows_sent: 1  Rows_examined: 2345869SET  Timestamp=1522065887;select participan0_.id as id19_, participan0_.conferenceid as  conferen2_19_, participan0_.name as name19_, participan0_.phone as phone19 _, participan0_.pin as pin19_, participan0_.email as email19_, participan0_ . mobile as mobile19_, participan0_.valid as valid19_, participan0_.userdefine1  as userdefine9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_. userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_  from participant participan0_ where  (participan0_.conferenceid=2680447 ) and ( Participan0_.pin= ' 1219 '  );# [email protected]: cms[cms] @  [172.17.43.25]# query_time:  10.297055  lock_time: 0.000050 rows_sent: 1  rows_examined: 2345869set  timestamp=1522065887;select participan0_.id as id19_, participan0_.conferenceid  as conferen2_19_, participan0_.name as name19_, participan0_.phone as  phone19_, participan0_.pin as pin19_, participan0_.email as email19_,  participan0_.mobile as mobile19_, participan0_.valid as valid19_,  Participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10 _19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as  userdefine12_19_ from participant participan0_ where  (participan0_.conferenceid= 2697493 ) and (participan0_.pin= ' 1492 '  );# [email protected]: cms[cms] @  [172.17.43.25]# query_time:  10.319839  lock_time: 0.000048 rows_sent: 1  rows_examined: 2345869set  timestamp=1522065887;select participan0_.id as id19_, participan0_.conferenceid  as conferen2_19_, participan0_.name as name19_, participan0_.phone as  phone19_, participan0_.pin as pin19_, participan0_.email as email19_,  participan0_.mobile as mobile19_, participan0_.valid as valid19_,  Participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10 _19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as  userdefine12_19_ from participant participan0_ where  (participan0_.conferenceid= 2680355 ) and (participan0_.pin= ' 9590 '  );# [email protected]: cms[cms] @  [172.17.43.24]# query_time:  10.163372  lock_time: 0.000063 rows_sent: 1  rows_examined: 2345872set  timestamp=1522065887;select participan0_.id as id19_, participan0_.conferenceid  as conferen2_19_, participan0_.name as name19_, participan0_.phone as  phone19_, participan0_.pin as pin19_, participan0_.email as email19_,  participan0_.mobile as mobile19_, participan0_.valid as valid19_,  Participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10 _19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as  userdefine12_19_ from participant participan0_ where  (participan0_.conferenceid= 2731041 ) and (participan0_.pin= ' 1506 ' );# [email protected]: cms[cms] @  [172.17.43.24]# query_time:  9.950549  lock_time: 0.000073 rows_sent: 1  rows_examined: 2345881set  timestamp=1522065887;select participan0_.id as id19_, participan0_.conferenceid  as conferen2_19_, participan0_.name as name19_, participan0_.phone as  phone19_, participan0_.pin as pin19_, participan0_.email as email19_,  participan0_.mobile as mobile19_, participan0_.valid as valid19_,  Participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10 _19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as  userdefine12_19_ from participant participan0_ where  (participan0_.conferenceid= 2682013 ) and (participan0_.pin= ' 6086'  );# [email protected]: cms[cms] @  [172.17.43.25]# query_time:  9.992145  lock_time: 0.000051 rows_sent: 1  rows_examined: 2345879set  timestamp=1522065887;select participan0_.id as id19_, participan0_.conferenceid  as conferen2_19_, participan0_.name as name19_, participan0_.phone as  phone19_, participan0_.pin as pin19_, participan0_.email as email19_,  participan0_.mobile as mobile19_, participan0_.valid as valid19_,  Participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10 _19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as  userdefine12_19_ from participant participan0_ where  (participan0_.conferenceid= 2697493 ) and (participan0_.pin= ' 1103 '  ); 

View the execution plan of the SQL found a full table scan, scanned more than 2 million rows of data;

Mysql> explain select participan0_.id as id19_, participan0_.conferenceid  as conferen2_19_, participan0_.name as name19_, participan0_.phone as  phone19_, participan0_.pin as pin19_, participan0_.email as email19_,  participan0_.mobile as mobile19_, participan0_.valid as valid19_,   Participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as userdefine10 _19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as  userdefine12_19_ from participant participan0_ where  (participan0_.conferenceid= 2724963 ) and (participan0_.pin= ' 5476 '  ), +----+-------------+--------------+------+---------------+--- ---+---------+------+---------+-------------+| id | select_type | table         | type | possible_keys | key  | key_len | ref   | rows    | extra       |+----+-------------+ --------------+------+---------------+------+---------+------+---------+-------------+|  1 |  simple      | participan0_ | all  | null           | NULL | NULL     | null | 2042005 | using where |+----+-------------+--------------+-- ----+---------------+------+---------+------+---------+-------------+1 row in set  (0.02  SEC)

There is no index on the column that finds the table where condition:

mysql> show index from participant;+-------------+------------+----------+------------- -+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+|  Table       | non_unique | key_name | seq_in_index  | column_name | collation | cardinality | sub_part | packed  | null | index_type | comment | index_comment |+-------------+-- ----------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------- -----+---------+---------------+| participant |           0 | PRIMARY  |             1 | id          | A     &nbSp;    |     2384122 |     null  | NULL   |      | BTREE       |         |                |+-------------+------------+----------+--------------+------- ------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row  in set  (0.00 SEC)

and after communication, add the following index to the table:

mysql> alter  table   ' participant '    ADD  INDEX index_conferenceid  (' Conferenceid ');  query ok, 0 rows  affected  (9.16 sec) records: 0  duplicates: 0  warnings:  0mysql> alter  table   ' participant '   add  index index_pin   (' pin '); query ok, 0 rows affected  (6.96 sec) records: 0  duplicates:  0  warnings: 0 

View the execution plan for the SQL again:

mysql>  explain select participan0_.id as id19_, participan0_. Conferenceid as conferen2_19_, participan0_.name as name19_, participan0_.phone  as phone19_, participan0_.pin as pin19_, participan0_.email as  Email19_, participan0_.mobile as mobile19_, participan0_.valid as valid19_,  participan0_.userdefine1 as userdefine9_19_, participan0_.userdefine2 as  Userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4  as userdefine12_19_ from participant participan0_ where  (participan0_. conferenceid=2724963 ) and (participan0_.pin= ' 5476 '  ) +----+-------------+--------------+------------- +------------------------------+------------------------------+---------+------+------+------------------------ ------------------------------------+| id | select_type | table        | type         | possible_keys                 | key                           |  key_len | ref  | rows | Extra                                                         |+----+-------------+--------------+-- -----------+------------------------------+------------------------------+---------+------+------+------------- -----------------------------------------------+|  1 | simple      |  Participan0_ | index_merge | index_conferenceid,index_pin | index_conferenceid, index_pin | 5,7     | null |    1 |  using intersect (Index_conferenceid,index_pin)  using where |+----+-------------+---- ----------+-------------+------------------------------+------------------------------+---------+------+------+ ------------------------------------------------------------+1 row in set  (0.01 SEC)

-----------------------------I'm a split line-----------------------

Wait until the night is about a lot more time to observe the database load and slow query log

MySQL solves the stress problem of online database server by adding an index