解决MySQL 服务器进程 CPU占用100%的问题

1、问题现象描述

昨天上线一个活动,有个排行榜的功能,刚开始打开很流畅,晚上的时候突然打开很慢,排行榜基本是打不开,猜想估计是服务器出了问题

2、登录服务器后使用top命令查看资源占用信息

# top

top - 09:38:39 up 31 days, 16:48,  2 users,  load average: 0.46, 0.31, 0.25
Tasks: 179 total,   1 running, 178 sleeping,   0 stopped,   0 zombie
%Cpu(s): 391.1 us,  0.4 sy,  0.0 ni, 84.1 id,  0.4 wa,  0.0 hi,  0.2 si,  0.0 st
KiB Mem : 16267728 total,  2427980 free,  2738212 used, 11101536 buff/cache
KiB Swap:  2097148 total,  2097148 free,        0 used. 13136924 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
28739 mysql     20   0 4111084 875996  10908 S  380.1  5.4   1054:13 mysqld 

果然mysqld进程占用了大部分cpu,因为是4核所以会超过100%,问题定位到mysql数据库

3、登录mysql查看是否存在查询效率低sql语句

mysql> show processlist;

反复使用上述命令,发现有一个sql大量出现在列表中

SELECT
  a.openid,
  b.nickname,
  COUNT(a.openid) AS build_count
FROM
  `build` AS a
  LEFT JOIN `wxuser` AS b
    ON a.openid = b.openid
WHERE lid = 1
GROUP BY a.openid,
  b.nickname,
  b.wid
ORDER BY build_count DESC,
  b.wid ASC

使用show columns查看表结构

mysql> show columns from build;
mysql> show columns from wxuser;

发现问题所在了,wxuser表里openid没有索引,所以马上想到应该增加一个索引

mysql>ALTER TABLE `wxuser` ADD INDEX ind_openid (`openid`)

由于已经存在大量数据,该sql执行了近23分钟,索引增加完成之后,cpu瞬间下降到1%左右

4、最后总结

关于优化

  1. 对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。
  2. 增加 tmp_table_size 值。mysql 的配置文件中,tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小,MySQL产生一个 The table tbl_name is full 形式的错误,如果你做很多高级 GROUP BY 查询,增加 tmp_table_size 值。
  3. 在开发过程中对于查询比较复杂的sql建议通过EXPLAIN分析SQL语句,并建立索引

mysql添加索引命令

1.PRIMARY  KEY(主键索引)
mysql>ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) 
2.UNIQUE(唯一索引)
mysql>ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) 
3.INDEX(普通索引)
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  )
4.FULLTEXT(全文索引)
mysql>ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` )
5.多列索引
mysql>ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  )

PRIMARY, INDEX, UNIQUE 这3种是一类
PRIMARY 主键。 就是唯一且不能为空
INDEX 索引,普通的
UNIQUE 唯一索引。 不允许有重复
FULLTEXT 是全文索引,用于在一篇文章中,检索文本信息的



Mjiong君

爱生活,爱技术,爱分享

You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注