问题：服务器MySQL占用CPU过高，出现陡然暴增，直逼100%。
常见原因
MySQL执行应用提交的CURD操作时需要大量的逻辑读（逻辑IO，执行查询所需访问的表的数据行数），系统需消耗大量CPU资源以维护从存储系统读取到内存中的数据一致性。当大量行锁冲突、行锁等待、后台任务则可能导致实例的CPU使用率过高。
简化模型
单位时间CPU资源消耗 = 查询执行平均成本 x 单位时间执行查询数量
total_lgc_io = avg_lgc_io x QPS
| 参数 | 描述 |
|---|---|
| total_lgc_io | 实例的CPU资源在单位时间内能够处理的逻辑IO的总量 |
| avg_lgc_io | 执行每条查询平均所需的逻辑IO |
| QPS | 应用负载，每秒查询率（Queries-per-Second），单位时间内所处理多少流量，单位【请求数/秒】。 |
性能监控
查看服务器硬件状态
执行top命令查看服务器CPU状态
$ top
41025 mysql 20 0 9805m 4.2g 8128 S 1.7 6.6 38246:01 mysqld
使用free -m命令查询内存剩余空间
$ free -m
total used free shared buff/cache available
Mem: 7551 1272 166 0 6113 5930
Swap: 8191 0 8191
查看CPU飙升的MySQL进程
$ top -H -p <mysqld pid>
查看MySQL服务器当前状态
mysql > SHOW STATUS;
消耗对象
MySQL导致CPU消耗过大，首先需要明确谁在消耗MySQL，一般而言消耗CPU的对象可分为用户us、系统sy、IO等待wa、软硬终端ni & si、空闲id这五部分，其中系统和软硬终端难以改变。
- 用户空间：用户空间消耗CPU是因为各种逻辑运算以及大量事务（TPS, Transctions Per Second），比如函数/排序/类型转换/逻辑IO访问等等。
- IO等待：等待IO请求的完成，此时CPU实际上是空闲。
用户空间和IO等待会消耗大部分CPU，进而造成吞吐量下降、查询响应时间增减、慢查询增加，当然如果MySQL的并发陡增，也会产生上述影响。
如果减少CPU消耗，可以通过减少等待、减少IO的量、提升IO处理能、减少计算、提升CPU另外实现。
- 减少等待
减少等待需要减少IO的量，可通过对SQL添加合适的索引，以减少扫描的行数，需提前平衡索引的正收益和维护开销成本，进而以空间换取时间。
- 提升IO处理能力
提升IO处理能可通过增加内存、增加存储等方式实现。
升级CPU
减少计算
减少逻辑运算量
- 避免使用函数，将运算转移至扩展的应用服务器中实现。
- 减少排序则有利于利用索引获取有序数据或避免不必要的排序。
- 禁止类型转换，使用合适类型并保证传入数据类型于数据库字段类型绝对一致。
- 尽量使用简单类型，尽量避免使用复杂类型，降低由复杂类型带来的附加运算。
减少逻辑IO量
- 添加索引优化索引，调整组合索引字段顺序去除选择性很差的索引字段等，以减少不必要的表扫描。
- 数据表进行合理拆分适度冗余，将很少使用的大字段拆分到独立表，将以非常频繁的小字段冗余到引用表。
- 调整SQL写法以充分利用现有索引，进而避免不必要的扫描、排序等其它操作。
- 字段数据类型以够用为准，尽量避免不必使用的大字段。
减少查询请求量
- 适当缓存以降低缓存数据粒度，对静态并被频繁请求的数据进行适当缓存。
- 优化实现尽量去掉不必要的重复请求
- 合理需求评估需求产出比
查看线程
以CPU使用率为100%为例，QPS应用负载过高、查询执行成本过高（查询访问表数据行数多）都会导致CPU使用率高，其中查询访问表数据行数多是非常常见的。
- 应用负载过高
数据库实例每秒执行的查询次数高（QPS），同时查询简单执行效率高，没有出现慢查询，QPS和CPU使用率曲线变化吻合。这种情况使用SQL查询优化余地不大，建议升级实例规则增加CPU资源。
- 查询执行成本高
数据库实例的每秒执行查询次数（QPS）不高，查询执行效率低，执行时需扫描大量表中数据，优化余地较大。表现为慢查询、QPS和CPU使用率曲线变化不吻合。
如何定位执行效率低的查询呢？通过查询当前MySQL服务器的线程列表proceslist，观察连接的线程状态来判断。也可以通过查询MySQL系统表information_schema.processlist来观察。
mysql> SHOW PROCESSLIST;
mysql> SHOW FULL PROCESSLIST;
查找负荷最重的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语句 |
对于查询时间长（Time）长、运行状态（State）为Sending data、Copying to tmp table、Copying to tmp table on disk、Sorting result，Using filesort等都可能是具有性能问题的SQL查询。
慢查询起因包括全表扫描，扫描数据量过大、内存排序、磁盘排序、锁争用等待等，表现出连接线程的执行状态State会是
- Sending data
表示正在从表中查询数据，如果查询条件没有适当索引，则会导致SQL执行时间过长。 - Copying to tmp table on disk
出现这种情况通常是由于临时结果集太大，已经超过数据库规定的临时内存大小，因此需要通过拷贝临时结果集到磁盘上进行保存，此时需要对SQL查询条数进行优化，比如分页处理。 - Sorting result, Using filesort
出现这种情况表示SQL正在执行排序操作，排序操作会引起较大的CPU消耗，优化的方式是为排序字段添加索引以消除排序缓存，或缩小排序的结果集。
挑选出查询效率低的SQL，使用EXPLAIN命令分析查询语句性能。
同样有可以使用MySQL内部表来定义有问题的SQL语句
SELECT
a.thread_id,
b.user,
b.host,
b.db,
b.command,
b.time,
b.state,
b.info
FROM
performance_schema.threads AS a,
information_schema.processlist AS b
WHERE
a.processlist_id = b.id;
休眠线程
processlist中出现大量运行状态State为Sleep的线程，怎么办？为什么呢？跟CPU陡然暴增有关系吗？
sleeping
Sleep休眠连接表示没有主动关闭释放资源的连接，过多会严重消耗MySQL服务器资源（CPU和内存），可能会导致MySQL崩溃。
MySQL进程中存在大量sleep休眠连接，出现原因可能是以下情况：
- 使用了太多持久连接，注意高并发系统下不适合使用持久连接。
- 程序中没有及时关闭MySQL连接
- 数据查询不够优化过度耗时
从MySQL内部表information_schema.processlist表中查询中处于Sleep的进程
SELECT * FROM information_schema.processlist WHERE 1=1 AND Command = "Sleep";
可通过设置睡眠链接超时秒数wait_timeout，达到连接睡眠超时MySQL自然终止。
mysql> SHOW GLOBAL VARIABLES LIKE "wait_timeout";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout | 28800 |
+---------------+-------+
1 row in set
默认MySQL休眠超时为8小时，当wait_timeout设置过大体现为MySQL中存在大量Sleep线程无法及时释放，拖累系统性能，如果设置过小则会出现MySQL has gone away之类的问题。
mysql > SET GLOBAL wait_timeout=100;
等待超时
wait_timeout用于设置Sleep睡眠连接线程超时秒数，若超时则会被MySQL自然终止连接。如果wait_timeout设置过大MySQL中会存在大量的Sleep进程无法及时释放，会拖累系统性能。若wait_timeout过小，可能会遭遇MySQL has gone away之类的问题。通常而言，wait_timeout设置为10是最佳选择。
MySQL默认wait_timeout为28800秒即8小时，意味着一个连接的空闲时间若超过8小时则会自动断开，问题是连接池却还认为该连接还是有效的。观察阿里云的MySQL上默认设置为7200秒（2小时）。
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
临时文件
若proceslist中出现大量Copying to tmp table on disk状态，则明显是由于临时表过大导致MySQL将临时表写入磁盘而影响了整体性能，究其根本是因为临时结果集太大，也就是一次性查询出来的结果过多而造成的。
Copying to tmp table on disk
MySQL中临时表大小tmp_table_size默认为32MB，如果临时表超出系统默认则MySQL会产生一个The table tblname is full的错误信息，可通过调整tmp_table_size临时表大小来解决。
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;
阻塞查询
proceslist出现大量Waiting for table flush运行状态的SQL，后续查询会被阻塞而没有返回结果。
Waiting for table flush
生产环境很多时候可能不是lock table read引起的堵塞，而是由于慢查询导致flush table而一直无法关闭该表，因而产生连接一直处于等待状态。
出现Waiting for table flush时首先需要找到哪些表被lock住或哪些慢查询导致flush table一直在等待状态而无法关闭。然后kill掉对应的线程即可。
- 慢查询导致
对于慢查询引起的线程处于Waiting for table flush的可以通过查询SHOW PROCESSLIST中Time字段值最大的线程，甄别后Kill掉以去除一起阻塞的源头SQL。有种规律是线程Time列值必定比被阻塞的线程要高，因此能过滤掉很多记录。
-
lock table read导致
lock table read导致会话可能是处于Sleeping休眠状态，此时即使使用SHOW ENGINE innodb STATUS命令输出信息也不会出现。使用SHOW OPEN TABLES WHERE in_use>=1虽然能够找到被Lock的表，但仍然无法定位到具体的连接线程，可通过使用inntop工具定位到锁住的表和对应的线程。
锁等待
若proceslist中出现大量Waiting for table metadadta lock的运行状态，应该如何处理呢？
Waiting for table metadata lock
当MySQL在进行ALTER TABLE等DDL操作时，有时会出现Waiting for table metadata lock的等待场景，一旦ALTER TABLE修改表结构的操作停滞在Waiting for table metadata lock的运行状态，后续对该表的任何读写操作都将无法进行，因为后续操作会在Opening tables阶段进入到Waiting for table metadata lock的锁等待队列。如果是产品环境核心表出现这种锁等待队列将会造成灾难性的后果。
通过ALTER TABLE产生Waiting for table metadata lock的原因
- 长事务运行时阻塞DDL，继而阻塞所有同表的后续操作。
当表中正在进行读写操作时，此时修改表结构ALTER TABLE语句将无法获取到metadata表的元数据独占锁，因此会进行等待。
解决方法：直接KILL掉DDL操作所在的SESSION。
- 未提交的事务阻塞DDL，继而阻塞所有当前表的后续操作。
当表上存在未提交的事务时，可在information_schema.innodb_trx表中查到。在事务没有完成之前，表上的锁时不会释放的，此时若修改表结构ALTER TABLE同样获取不到metadata元数据的独占锁。
解决方法：SELECT * FROM information_schema.innodb_trx找到未提交事务的SID，然后KILL掉，让其回滚。
- 在线程列表中未发现当前表的任何操作，在
information_schema.innodb_trx表中也没有发现任何进行中的事务。此时可能是因为在一个显式的事务中，对当前表进行了一个失败的操作，比如查询到一个不存在的字段。虽然此时事务还没有开始，但失败语句获取到的锁依然有效，且没有释放。
官方说法：除了语法错误，其它错误语句获取到的锁在事务提交或回滚之前仍然不会释放掉。
结局方案：从performance_schema.events_statements_current表中查到失败的语句的SID然后KILL掉SESSION，即可KILL掉DDL所在的SESSION。
总之，修改表结构ALTER TABLE的DDL操作时很危险的，因为有未提交事务或长事务的存在。所以在操作之前最好确认对需要操作的表没有任何进行中的操作，比如没有未提交的事务，也没有显式事务中的报错语句。如果有ALTER TABLE的维护任务，在无人监管的时候运行，最好通过lock_wait_timeout设置好超时时间，以避免长时间的metadata锁等待。
获取默认的锁定等待时长lock_wait_timeout
mysql> SHOW GLOBAL VARIABLES LIKE "lock_wait_timeout";
+-------------------+----------+
| Variable_name | Value |
+-------------------+----------+
| lock_wait_timeout | 31536000 |
+-------------------+----------+
1 row in set
最大连接数
CPU暴增，连接数同时陡然暴增，是什么原因？
查看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;
慢查询日志
慢查询起因包括全表扫描，扫描数据量过大、内存排序、磁盘排序、锁争用等待等，表现出连接线程的执行状态State会是
- Sending data
表示正在从表中查询数据，如果查询条件没有适当索引，则会导致SQL执行时间过长。 - Copying to tmp table on disk
出现这种情况通常是由于临时结果集太大，已经超过数据库规定的临时内存大小，因此需要通过拷贝临时结果集到磁盘上进行保存，此时需要对SQL查询条数进行优化，比如分页处理。 - Sorting result, Using filesort
出现这种情况表示SQL正在执行排序操作，排序操作会引起较大的CPU消耗，优化的方式是为排序字段添加索引以消除排序缓存，或缩小排序的结果集。
打开慢查询日志，将那些执行时间过长且占用资源过多的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 > SHOW INDEX FROM tblname;
通过SHOW INDEX FROM tblname命令查看指定表结构是否含有索引，添加索引时需要注意的是，在使用LIKE模糊查询时，如果其后是常量 + %即%不在第一个字符位置，索引才会生效。
定期检查分析优化
分析表
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 | - |
