昨天3月26號線上應用反饋：晚上19：30-19：43以及 20：13到20：21兩個時間段所有人包括他們自己建的教室都進不去，cla***oom B套無法登陸，主頁無法訪問，已有超過10個老師和學生反饋進不去教室的問題
創(chuàng)新互聯(lián)建站長期為上千多家客戶提供的網站建設服務，團隊從業(yè)經驗10年，關注不同地域、不同群體，并針對不同對象提供差異化的產品和服務；打造開放共贏平臺，與合作伙伴共同營造健康的互聯(lián)網生態(tài)環(huán)境。為房縣企業(yè)提供專業(yè)的網站制作、成都網站建設，房縣網站改版等技術服務。擁有十多年豐富建站經驗和眾多成功案例,為您定制開發(fā)。通過監(jiān)控查看26號00:00到27號11:00之間的監(jiān)控，查看出現(xiàn)問題時數(shù)據(jù)庫服務器的cpu使用率，負載，內存使用，swap剩余量等狀況，發(fā)現(xiàn)數(shù)據(jù)庫當時壓力特別大，
數(shù)據(jù)庫慢查詢日志中出現(xiàn)很多慢SQL，
查看慢查詢日志，發(fā)現(xiàn)一個SQL在慢查詢日志中頻繁出現(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ù)庫的負載和慢查詢日志一切正常
通過此次事故：
① 及時優(yōu)化慢查詢
② 打開參數(shù) log_queries_not_using_indexes 及時發(fā)現(xiàn)沒有走索引的SQL
③可以采用SQL審核-自主上線平臺，解放人力
https://blog.51cto.com/hcymysql/2053798#comment
另外有需要云服務器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。
重慶分公司
重慶分公司
