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

图：投资排行榜修改前

图：投资排行榜修改后

由200多ms到70ms,时间快了3倍。

连接查询的效率比子查询的高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。

因此，尽量用连接查询。

参考：子查询和关联查询 效率 ，表连接查询与where后使用子查询的性能分析 

结合自己的实验，和别人的经历，确实是连接查询的效率比子查询的高。

7.1.4 自动投标插入方面，先保留修改，看后面执行情况。

后续

修改后的运行情况：发标前，mysql服务，cpu的占用2%左右。发标中，不超过10%。自动投标部分，用时2m，正常了。

经过上述调整后，系统运行正常了。

对于访问比较频繁的首页的每个连接做了数据库查询优化后，访问数据都有所提升，因此效率大大提高了。

现在的网站主要的压力都来自于数据库，频繁的数据库访问经常会使服务器死机。良好的数据库设计和优化查询语句是很重要的。

网站架构先是小型架构，逐渐演变成大型架构的。大型网站由小型网站演化而来，随着业务发展，逐渐发展。