MySQL占用CPU过高

服务器MySQL占用CPU过高时，应排查的因素包括：

进程列表

排除高并发因素先要找到导致CPU过高的SQL

mysql> SHOW PROCESSLIST;

查找负荷最重的SQL语句，优化该SQL，比如适当建立某字段的索引。

+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:56919 | test | Query   |    0 | NULL  | SHOW PROCESSLIST |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set

字段	描述
Id	进程标识，Kill时使用。
User	当前用户权限范围内的SQL语句
Host	从哪个IP的端口发出的，用来追踪出问题语句的用户。
db	进程目标连接的是哪个数据库
Command	显示当前连接执行的命令：休眠sleep/查询query/连接connect/
Time	状态持续时间秒数
State	使用当前连接的SQL语句的状态
Info	显示SQL语句

休眠的线程

一般而言，休眠Sleep连接过多会严重消耗MySQL服务器资源（CPU和内存），可能会导致MySQL崩溃。

临时文件大小

若进程列表中出现大量Copying to tmp table on disk状态，则明显是由于临时表过大导致MySQL将临时表写入磁盘而影响了整体性能。可通过调整tmp_table_size临时表大小来解决，MySQL中tmp_table_size默认是16MB。

mysql> SHOW VARIABLES LIKE "%tmp_table_size%";
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 18874368 |
+----------------+----------+
1 row in set

临时设置临时表大小

mysql> SET GLOBAL tmp_table_size=33554432;

最大连接数

查看MySQL连接数，看看是否超过了MySQL设置的连接数。

查看MySQL最大连接数

mysql> SHOW VARIABLES LIKE "%max_connections%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 512   |
+-----------------+-------+
1 row in set

当并发连接请求大于默认数据库连接数后，MySQL会出现无法连接1040错误。这是因为访问MySQL还未释放的连接数已经达到了MySQL的上限。

Can not connect to MySQL server. Too many connections

MySQL连接数最大上限为16384，临时修改最大连接数。

mysql> SET GLOBAL max_connections = 1024;

连接超时

wait_timeout用于设置SQL语句睡眠连接超时秒数，若连接超时则会被MySQL自然终止。如果wait_timeout设置过大MySQL中会存在大量的SLEEP进程无法及时释放，会拖累系统性能。若wait_timeout过小，可能会遭遇MySQL has gone away之类的问题。通常而言，wait_timeout设置为10个小时是最佳选择。

MySQL默认wait_timeout为28800秒即8小时，意味着一个连接的空闲时间若超过8小时则会自动断开连接，然后连接池却认为该连接还是有效的。

mysql> SHOW VARIABLES LIKE "%wait_timeout%";
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 50       |
| lock_wait_timeout        | 31536000 |
| wait_timeout             | 28800    |
+--------------------------+----------+
3 rows in set

慢查询日志

打开慢查询日志，将那些执行时间过长且占用资源过多的SQL拿来进行EXPLAIN分析。导致CPU过高多数是GROUP BY、ORDER BY排序问题锁导致，然后慢慢进行优化改进。比如优化INSERT语句、优化GROUP BY语句、优化ORDER BY语句、优化JOIN语句等。

查看慢查询日志开启状态

mysql> SHOW VARIABLES LIKE "%slow_query_log%";
+---------------------+-------------------------------------------------------------+
| Variable_name       | Value                                                       |
+---------------------+-------------------------------------------------------------+
| slow_query_log      | OFF                                                         |
| slow_query_log_file | \MySQL\data\slow.log |
+---------------------+-------------------------------------------------------------+
2 rows in set

临时开启慢查询日志

mysql> SET GLOBAL slow_query_log = 1;

获取慢查询日志的存放路径

mysql> SHOW VARIABLES LIKE "slow_query_log_file

慢查询中的SQL是由long_query_time控制的

mysql> SHOW VARIABLES LIKE "long_query_time%";
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set

临时修改全局的长查询时间微秒限制

mysql> SET GLOBAL long_query_time=4;

设置日志存储方式

mysql> SHOW VARIABLES LIKE "%log_output%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+
1 row in set

日志存储方式	描述
FILE	将日志存入文件
TABLE	将日志存入数据库，日志信息默认写入mysql.slow_log表中。

临时设置日志存储方式

mysql> SET GLOBAL log_output="FILE,TABLE";

查询有多少条慢查询记录

mysql> SHOW GLOBAL STATUS LIKE "%Slow_queries%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 0     |
+---------------+-------+
1 row in set

定时优化文件与索引

定期检查分析优化

分析表

MySQL使用ANALYZE TABLE语句对表进行分析，对表定期分析可以改善性能，应称为常规维护工作的一部分。

ANALYZE TABLE db.tblname[, db.tblname...];

• ANALYZE TABLE语句通过更新表的索引信息对表进行分析改善数据库性能
• ANALYZE TABLE语句分析表时会对表添加一个只读锁，分析期间只能读取表中记录无法写入（插入和更新）记录。
• ANALYZE TABLE语句适于MyISAM和InnoDB存储引擎

mysql> ANALYZE TABLE test.lock_test;
+----------------+---------+----------+----------+
| Table          | Op      | Msg_type | Msg_text |
+----------------+---------+----------+----------+
| test.lock_test | analyze | status   | OK       |
+----------------+---------+----------+----------+
1 row in set

分析结果	含义
Table	分析的表
Op	表示执行的操作：analyze分析/check检查查找/optimize优化
Msg_type	表示信息类型：状态/警告/错误/信息
Msg_text	表示显示信息

检查表

MySQL的CHECK TABLE语句用来检查表

CHECK TABLE db.tblname[, db.tblname...] [option];

• CHECK TABLE语句能够检查InnoDB和MyISAM存储引擎的表是否存在逻辑错误
• CHECK TABLE语句可检查视图是否存在错误
• CHECK TABLE语句的option选项只对MyISAM存储引擎的表有效，对InnoDB存储引擎的表无效。

option选项有五个参数值分别是QUICK、FAST、CHANGED、MEDIUM、EXTENDED，执行效率依次降低。

mysql> CHECK TABLE test.lock_test;
+----------------+-------+----------+----------+
| Table          | Op    | Msg_type | Msg_text |
+----------------+-------+----------+----------+
| test.lock_test | check | status   | OK       |
+----------------+-------+----------+----------+
1 row in set

优化表

MySQL中使用OPTIMIZE TABLE语句来优化表

OPTIMIZE TABLE dbname.tblname[, dbname.tblname];

• OPTIMIZE TABLE语句可以消除删除和更新造成的磁盘碎片从而减少空间的浪费
• OPTIMIZE TABLE语句仅对InnoDB和MyISAM存储引擎有效
• OPTIMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段
• OPTIMIZE TABLE语句执行过程中会给表添加只读锁

随着MySQL的使用含有BLOB和VARCHAR字节的表将变得越来越繁冗，因为这些字段的长度不同对记录进行插入、更新、删除时，会占用不同大小的空间，记录会变成碎片且留下空闲的空间，类似具有碎片的磁盘，会降低性能，需要整理，因此需要优化。

若表使用了TEXT或BLOB数据类型，那么更新、删除等操作后会造成磁盘空间的浪费。因为更新和删除操作后以前分配的磁盘空间不会自动收回。使用OPTIMIZE TABLE语句可以将这些磁盘碎片整理出来以便以后再利用。

mysql> OPTIMIZE
 TABLE test.lock_test;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| test.lock_test | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.lock_test | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set

优化数据库对象

考虑是否是锁问题

调整MySQL服务器参数

参数	描述
key_buffer_size	-
table_cache	-
innodb_buffer_pool_size	-
innodb_log_file_size	-
max_heap_table_size	-