您好,登录后才能下订单哦!
昨天3月26号线上应用反馈:晚上19:30-19:43以及 20:13到20:21两个时间段所有人包括他们自己建的教室都进不去,cla***oom B套无法登陆,主页无法访问,已有超过10个老师和学生反馈进不去教室的问题
通过监控查看26号00:00到27号11:00之间的监控,查看出现问题时数据库服务器的cpu使用率,负载,内存使用,swap剩余量等状况,发现数据库当时压力特别大,
数据库慢查询日志中出现很多慢SQL,
查看慢查询日志,发现一个SQL在慢查询日志中频繁出现切执行时间较长
# 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的执行计划发现走了全表扫描,扫描了200多万行的数据;
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)
发现该表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)
和开发沟通后在该表上加上以下索引:
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的执行计划,不再走全表扫描,而是走了index_merge,执行时间也大大缩短,
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)
-----------------------------我是分割线-----------------------
等到晚上约课较多的时间再次观察数据库的负载和慢查询日志一切正常
通过此次事故:
① 及时优化慢查询
② 打开参数 log_queries_not_using_indexes 及时发现没有走索引的SQL
③可以采用SQL审核-自主上线平台,解放人力
https://blog.51cto.com/hcymysql/2053798#comment
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。