MySQL通過添加索引解決線上數(shù)據(jù)庫服務(wù)器壓力大問題

昨天3月26號線上應(yīng)用反饋：晚上19：30-19：43以及 20：13到20：21兩個時間段所有人包括他們自己建的教室都進(jìn)不去，cla***oom B套無法登陸，主頁無法訪問，已有超過10個老師和學(xué)生反饋進(jìn)不去教室的問題

通過監(jiān)控查看26號00:00到27號11:00之間的監(jiān)控，查看出現(xiàn)問題時數(shù)據(jù)庫服務(wù)器的cpu使用率，負(fù)載，內(nèi)存使用，swap剩余量等狀況，發(fā)現(xiàn)數(shù)據(jù)庫當(dāng)時壓力特別大，

數(shù)據(jù)庫慢查詢?nèi)罩局谐霈F(xiàn)很多慢SQL，

查看慢查詢?nèi)罩荆l(fā)現(xiàn)一個SQL在慢查詢?nèi)罩局蓄l繁出現(xiàn)切執(zhí)行時間較長

# User@Host: cms[cms] @  [172.17.43.24]
# Query_time: 10.252490  Lock_time: 0.000052 Rows_sent: 1  Rows_examined: 2345869
SET 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 userdefin
e9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from p
articipant participan0_ where (participan0_.conferenceid=2680447 )and(participan0_.pin='1219' );
# User@Host: cms[cms] @  [172.17.43.25]
# Query_time: 10.297055  Lock_time: 0.000050 Rows_sent: 1  Rows_examined: 2345869
SET 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 userdefin
e9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from p
articipant participan0_ where (participan0_.conferenceid=2697493 )and(participan0_.pin='1492' );
# User@Host: cms[cms] @  [172.17.43.25]
# Query_time: 10.319839  Lock_time: 0.000048 Rows_sent: 1  Rows_examined: 2345869
SET 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 userdefin
e9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from p
articipant participan0_ where (participan0_.conferenceid=2680355 )and(participan0_.pin='9590' );
# User@Host: cms[cms] @  [172.17.43.24]
# Query_time: 10.163372  Lock_time: 0.000063 Rows_sent: 1  Rows_examined: 2345872
SET 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 userdefin
e9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from p
articipant participan0_ where (participan0_.conferenceid=2731041 )and(participan0_.pin='1506' );
# User@Host: cms[cms] @  [172.17.43.24]
# Query_time: 9.950549  Lock_time: 0.000073 Rows_sent: 1  Rows_examined: 2345881
SET 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 userdefin
e9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from p
articipant participan0_ where (participan0_.conferenceid=2682013 )and(participan0_.pin='6086' );
# User@Host: cms[cms] @  [172.17.43.25]
# Query_time: 9.992145  Lock_time: 0.000051 Rows_sent: 1  Rows_examined: 2345879
SET 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 userdefin
e9_19_, participan0_.userdefine2 as userdefine10_19_, participan0_.userdefine3 as userdefine11_19_, participan0_.userdefine4 as userdefine12_19_ from p
articipant participan0_ where (participan0_.conferenceid=2697493 )and(participan0_.pin='1103' );

查看該SQL的執(zhí)行計劃發(fā)現(xiàn)走了全表掃描，掃描了200多萬行的數(shù)據(jù)；

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)

發(fā)現(xiàn)該表where條件的列上沒有索引：

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         |     2384122 |     NULL | NULL   |      | BTREE      |         |               |
+-------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

和開發(fā)溝通后在該表上加上以下索引：

mysql> ALTER  TABLE  `participant`  ADD  INDEX index_conferenceid (`conferenceid`); 
Query OK, 0 rows affected (9.16 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> ALTER  TABLE  `participant`  ADD  INDEX index_pin (`pin`);
Query OK, 0 rows affected (6.96 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看該表的索引

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         |     2360697 |     NULL | NULL   |      | BTREE      |         |               |
| participant |          1 | index_conferenceid |            1 | conferenceid | A         |         199 |     NULL | NULL   | YES  | BTREE      |         |               |
| participant |          1 | index_pin          |            1 | pin          | A         |         199 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------------+------------+--------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

再次查看該SQL的執(zhí)行計劃，不再走全表掃描，而是走了index_merge,執(zhí)行時間也大大縮短，

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)

-----------------------------我是分割線-----------------------

等到晚上約課較多的時間再次觀察數(shù)據(jù)庫的負(fù)載和慢查詢?nèi)罩疽磺姓?/span>

通過此次事故：

① 及時優(yōu)化慢查詢

② 打開參數(shù) log_queries_not_using_indexes  及時發(fā)現(xiàn)沒有走索引的SQL

③可以采用SQL審核-自主上線平臺，解放人力

https://blog.51cto.com/hcymysql/2053798#comment