记录一次数据库CPU被打满的排查过程

1 前言

近期随着数据量的增长，数据库CPU使用率100%报警频繁起来。第一个想到的就是慢Sql，我们对未合理运用索引的表加入索引后，问题依然没有得到解决，深入排查时，发现在 order by id asc limit n时，即使where条件已经包含了覆盖索引，优化器还是选择了错误的索引导致。通过查询大量资料，问题得到了解决。这里将解决问题的思路以及排查过程分享出来，如果有错误欢迎指正。

https://github.com/mysql/mysql-server.git

https://bugs.mysql.com/bug.php?id=97001 。官方称在5.7.33以后版本可以关闭prefer_ordering_index 来解决。如下图所示。

另外在我们日常慢Sql调优时，可以通过以下两种方式，了解更多查询优化器选择过程。

--第一种
explain format=json
sql语句
-------------------------------------------------------------------------
--第二种 optimizer_trace方式
set optimizer_trace="enabled=on";
--如果不设置大小，可能导致json输出不全
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
SQL语句
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

当你也出现了本篇文章碰到的问题时，可以采用以下的方法来解决

1. 使用force index，强制指定索引。
2. order by中增加一个联合索引的key。
3. 扩大limit 返回的范围（不推荐，随着数据量的增大，可能还会走回主键索引）
4. order by (id+0) asc 欺骗查询优化器，让其选择联合索引。
5. MySQL 5.7.33版本以上，可以关闭prefer_ordering_index解决。

作者：陈强