Mysql创建索引解决CPU占用高的问题

发布于2016-03-24 14:34:14 更新于2025-08-09 14:37:15
运维
浏览 144
shevechco
手机浏览
评论数 0

通过以前对mysql的操作经验，先将mysql的配置问题排除了，查看msyql是否运行正常，通过查看mysql data目录里面的*.err文件来定位MySQL问题。
简单的分为下面几个步骤来解决这个问题：

1.查看当前MySQL连接进程

mysql运行正常，也有可能是同步设置问题导致
如果mysql运行正常，那就是php的一些sql语句导致问题发现，用root用户进入mysql管理

mysql -u root -p

输入密码回车以后进入数据库，可以使用命令来查看当前mysql进程列表

show processlist

查找负荷最重的 SQL 语句，优化该SQL，比如适当建立某字段的索引。
将 mysql 当前的环境变量输出到文件 output.txt：

mysqld.exe --help >output.txt

2.优化MySQL配置

发现 tmp_table_size 的值是默认的 32M，于是修改 My.ini, 将 tmp_table_size 赋值到 200M:

[mysqld] 
tmp_table_size=200M

然后重启 MySQL 服务

CPU 占用有轻微下降，以前的CPU 占用波形图是 100% 一根直线，现在则在 97%~100%之间起伏。这表明调整 tmp_table_size 参数对 MYSQL 性能提升有改善作用。但问题还没有完全解决。
于是进入 mysql 的 shell 命令行，调用 show processlist, 查看当前 mysql 使用频繁的 sql 语句：

show processlist;

反复调用此命令，发现网站 A 的两个 SQL 语句经常在 process list 中出现，其语法如下：

SELECT t1.pid, t2.userid, t3.count, t1.date 
FROM 
_mydata AS t1 
LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid 
LEFT JOIN _mydata_body AS t2 ON t1.pid=t3.pid 
ORDER BY t1.pid 
LIMIT 0,15

调用 show columns 检查这三个表的结构 :

show columns from _myuser; 
show columns from _mydata; 
show columns from _mydata_body;

3.为字段添加索引

终于发现了问题所在：_mydata 表，只根据 pid 建立了一个 primary key，但并没有为 userid 建立索引。而在这个 SQL 语句的第一个 LEFT JOIN ON 子句中：

LEFT JOIN _myuser AS t3 ON t1.userid=t3.userid

_mydata 的 userid 被参与了条件比较运算。于是我为给 _mydata 表根据字段 userid 建立了一个索引：

ALTER TABLE `_mydata` ADD INDEX ( `userid` )

建立此索引之后，CPU 马上降到了 80% 左右。看到找到了问题所在，于是检查另一个反复出现在 show processlist 中的 sql 语句：

SELECT COUNT(*) FROM _mydata AS t1, _mydata_key AS t2 WHERE t1.pid=t2.pid and t2.keywords = '孔雀'

经检查 _mydata_key 表的结构，发现它只为 pid 建了了 primary key, 没有为 keywords 建立索引，为 _mydata_key 表根据字段 keywords 加上索引:

ALTER TABLE `_mydata_key` ADD INDEX ( `keywords` )

建立此索引之后，CPU立刻降了下来，在 50%~70%之间震荡。
再次调用 show prosslist，网站A 的sql 调用就很少出现在结果列表中了。

4.MySQL CPU占用高问题总结

增加 tmp_table_size 值。mysql 的配置文件中，tmp_table_size 的默认大小是 32M。如果一张临时表超出该大小，MySQL产生一个 The table tbl_name is full 形式的错误，如果你做很多高级 GROUP BY 查询，增加 tmp_table_size 值。这是 mysql 官方关于此选项的
对 WHERE, JOIN, MAX(), MIN(), ORDER BY 等子句中的条件判断中用到的字段,应该根据其建立索引 INDEX。索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。所有的MySQL索引(PRIMARY、UNIQUE和INDEX)在B树中存储。
如果对某些表的所有使用的列是数字型的并且构成某些键的最左面前缀，为了更快，值可以从索引树被检索出来。
假定你发出下列SELECT语句：

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果一个多列索引存在于col1和col2上，适当的行可以直接被取出。如果分开的单行列索引存在于col1和col2上，优化器试图通过决定哪个索引将找到更少的行并来找出更具限制性的索引并且使用该索引取行。

标签
mysql
cpu

MySQL字符集查看和设置方法

MYSQL睡眠连接过多使服务器内存CPU占用过高问题的分析及解决方法

转载注明出处：https://sulao.cn/post/199

今日天气

分类统计

博文归档

15篇

43篇

23篇

9篇

26篇

13篇

1篇

1篇

3篇

热门推荐

热门标签