MySQL通过添加索引解决线上数据库服务器压力大问题
发表于:2024-10-07 作者:千家信息网编辑
千家信息网最后更新 2024年10月07日,昨天3月26号线上应用反馈:晚上19:30-19:43以及 20:13到20:21两个时间段所有人包括他们自己建的教室都进不去,cla***oom B套无法登陆,主页无法访问,已有超过10个老师和学生
千家信息网最后更新 2024年10月07日MySQL通过添加索引解决线上数据库服务器压力大问题
昨天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: 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' );# User@Host: 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' );# User@Host: 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' );# User@Host: 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' );# User@Host: 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' );# User@Host: 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' );
查看该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
数据
查询
数据库
索引
日志
时间
问题
再次
教室
监控
服务器
服务
频繁
两个
主页
之间
事故
人力
使用率
内存
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全大讲堂市场监管局
重大活动网络安全保障ppt
必火网络安全培训ppt
2015国家网络安全周
厦门教育局网络安全宣传周
软件开发个体户需要上税吗
国际关系学院网络安全考研数据
政府部门用的网络安全员职责
小学生网络安全法写作
数据库中的rs关系指什么
收银软件开发投资
java软件开发外包机构
云顶之弈手游全球服服务器
适合计算机网络技术笔记本
贵州直销系统软件开发
信息化和网络安全心得
dell服务器二手怎么样
汕头教师网络安全宣讲
网络服务器软件下载
C 连接另外一台电脑数据库
工业网络技术专业有编程吗
服务器阵列卡电池作用
数据库管理系统类型
中国网络技术创新发展
火力全开服务器怎么开车
网络安全与教育专业
梦幻西游转服务器的套路
设计商业集团数据库
安徽交友软件开发大概要多少钱
河北职业软件开发平台