mysql数据库cpu占用高 解决MySQL 服务器进程 CPU占用100%的问题

1、问题现象描述

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

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

# toptop - 09:38:39 up 31 days, 16:48, 2 users, load average: 0.46, 0.31, 0.25Tasks: 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 stKiB Mem : 16267728 total, 2427980 free, 2738212 used, 11101536 buff/cacheKiB Swap: 2097148 total, 2097148 free, 0 used. 13136924 avail Mem  PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND28739 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_countFROM `build` AS a LEFT JOIN `wxuser` AS b ON a.openid = b.openidWHERE lid = 1GROUP BY a.openid, b.nickname, b.widORDER 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、最后总结

关于优化

对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。在开发过程中对于查询比较复杂的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 是全文索引，用于在一篇文章中，检索文本信息的