MySQL通过添加索引解决线上数据库服务器压力大问题-创新互联

昨天3月26号线上应用反馈：晚上19：30-19：43以及 20：13到20：21两个时间段所有人包括他们自己建的教室都进不去，cla***oom B套无法登陆，主页无法访问，已有超过10个老师和学生反馈进不去教室的问题

创新互联建站长期为上千多家客户提供的网站建设服务，团队从业经验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

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

当前文章：MySQL通过添加索引解决线上数据库服务器压力大问题-创新互联
URL分享：http://dzwzjz.com/article/cedogj.html