Mysql优化-CPU使用率达百分之400 处理

最近观察一个已上线系统，通过日志发现，总是会打印一个错误日志：页面响应时间超过1秒。

1.定位问题，页面响应慢一般有两种种可能

    - 代码逻辑问题

    - sql效率问题

接下来是实际解决问题的方法：

首先是先定位问题，通过日志打印和代码review发现代码本身不存在很复杂的业务逻辑。就是将数据库中数据select出来，返回给前端，因此代码逻辑存在问题的可能性很低。

可能是sql的查询效率低，通过explain 分析执行的sql后发现，索引也生效了。

那么此时，该段代码和sql都没有问题，就想想看是不是Mysql服务器出了问题。

登录到Mysql服务器上之后发现，果然CPU的使用率已经达到了百分之400。

那么CPU负载过高的原因是什么呢？

常见原因：

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

说明：大量的行锁冲突、行锁等待或后台任务也有可能会导致实例的CPU使用率过高，但这些情况概率比较低，本文不做讨论。

接下来就是寻找问题到底是哪个问题SQL导致。

一、定位问题SQL

数据库超频，首要原因分析为执行SQL导致，先在占用率高的时候定位正在执行的SQL。

# 进入数据库链接工具，或者直接进入MYSQL命令客户端。

# 查询正在执行的SQL

select * from information_schema.`PROCESSLIST` where info is not null;



+-------+------+-----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| ID    | USER | HOST                  | DB           | COMMAND | TIME | STATE        | INFO                                                                                                                                                                                                                                           |

+-------+------+-----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| 17757 | root | localhost             | NULL         | Query   |    0 | executing    | select * from information_schema.`PROCESSLIST` where info is not null                                                                                                                                                                          |

| 17753 | root | 192.168.219.104:40424 | db_iguidance | Query   |    0 | Sending data | SELECT  XXXXX from  XXXX where XXXXX where state = 1 or state = 2 |

+-------+------+-----------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 

· 根据COMMAND、TIME、STATE、INFO 三个字段查询SQL信息。

· COMMAND - 执行的数据操作类型 （QUERY）

· TIME - 执行的时间

· STATE - 执行的状态

· INFO - 执行的具体SQL

发现很长一段时间，查询的STATE 都处在 "Sending data" 的状态

查询一下 "Sending data" 状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括"收集 + 发送" 数据。

这里的关键是为什么要收集数据，原因在于：mysql使用"索引"完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。

二、使用explain查看sql使用索引过程

explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上explain就可以了，下面是一个使用explain查询SQL的例子：

explain select * from user_info where tel = '17000000000';

具体使用方法，后续会专门写一篇文章。暂时请各位自行百度。

至此，我已经找到了那个导致CPU超频的罪魁祸首 SQL "SELECT  XXXXX from  test  where xxx and state = 1 or state = 2"。

该"test"表是建过索引的，但是expain分析后，发现依然是全表扫描。没有走索引，原因是 where 之后 "state =1 or state = 2" 该条件，导致索引失效。

最终解决方案是，去掉该查询提交，在内存中过滤出符合条件的结果。

以空间换取时间的思路。最终 mysql使用率从百分之400 回归到正常。

回到我们最初的问题页面响应慢的问题也得以解决。

参考链接：https://www.cnblogs.com/osheep/p/11294215.html