mysql 资源占用过高分析和解决方法

分析和解决方案

1，情况描述：网站发标后，出现卡顿，最后出错。

2，问题排查：最后一个版本的更新是在两个星期前，照理来说，应该不是代码的问题。但是一时找不到问题所在，还是先对代码进行测试。使用排除法，修改数据后，在测试环境，生产环境测试，均正常。既然测试正常，那么排除代码的问题。那怀疑可能是服务器的问题，接下来查看Linux环境的运行情况。

3，linux系统，命令top可以查看系统资源使用情况。最近两次查看，如图一，图二。

图一

图二

由这两个图，可以看出来，mysql的服务几乎把资源耗尽。

ps:发标前的两三分钟，CPU就已经高达140%，在60 - 140震荡。

top命令参数说明

第一行的 load average 表示系统负载，即任务队列的平均长度，3个数值分别统计最近1，5，15分钟的系统平均负载

系统平均负载：单核CPU情况下，0.00 表示没有任何负荷，1.00表示刚好满负荷，超过1侧表示超负荷，理想值是0.7；
多核CPU负载：CPU核数 * 理想值0.7 = 理想负荷，例如：4核CPU负载不超过2.8何表示没有出现高负载。

系统是2核，理想值是2 * 0.7 =1.4 。图一的3.46，图二的7.69，明显大于理想值。

进程信息

K: %CPU = (CPU usage) 上次更新到现在的CPU时间占用百分比
图二的将近200%，两核的最高是200%，因此系统CPU是满负载运行。

因此在图二的时候，系统卡顿了一段时间。如果再多些访问量，系统应该就会出错。

常见原因

系统执行应用提交查询（包括数据修改操作）时需要大量的逻辑读（逻辑 IO，执行查询所需访问的表的数据行数），所以系统需要消耗大量的 CPU 资源以维护从存储系统读取到内存中的数据一致性。

4,mysql分析数据运行状态：show processlist;

在数据库，执行上面语句，如图

可以看出，这个查询语句有问题。state为sending data。单独拿出来在数据库执行，时间有点久。当集中访问时，数据库占用资源飙升。

5，查看NGINX日志，访问量并不多，访问的比较多的链接和show processlist;查询的情况基本吻合。

6，查看系统日志。图二的时候，自动投标部分用了14秒。而测试的时候是2秒（测试时候，只是单独发标）。真实发标时，访问比较多，两种情况的结合，影响变大了。单独发标，或者单独访问多时，影响比较小。

7，解决方案

1,优化数据库语句

2,增加资源，采用缓存，分布式等。这个方案保留。

7.1,优化数据库语句

7.1.1 查看查找项目的查询语句，投标表关联的项目表的项目ID，即投标表的项目ID，pid加上索引（index）。

图：没有索引时

图：加上索引时

由上面两个图可以看出来，加上索引后，访问的时间变为几十毫秒。时间快了15- 20倍。

这样访问首页和“我要投资”页面的时候，系统资源压力大大降低。

7.1.2 查询公告

首页只要显示5条数据，现在是查询出了全部。在数据库执行语句，查询全部时，是100多ms,查询限制5条时，是10多ms，时间快了10倍。

图：content查询修改前。

图：content查询修改后。

运行时间快了好几倍。

7.1.3 查询投资排行榜

换种查询方式，在数据库里执行，由80ms到40ms，时间提高一倍。

由

SELECT sum(money)
        as investSum ,(select lname from P_user p1 where p1.id =
        userId ) as
        lname
        FROM personTransactionDetail 
        where type = 4 and state in (1,3)
        GROUP BY userId,lname ORDER BY sum(money)
        DESC limit 5

改为：

SELECT sum(money)
        as investSum ,lname
        FROM personTransactionDetail a, P_user b
        where  a.userId = b.id and
    type = 4 and state in (1,3)
        GROUP BY userId,lname ORDER BY sum(money)
        DESC limit 5