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

5次阅读

共计 7764 个字符，预计需要花费 20 分钟才能阅读完成。

1 前言
近期随着数据量的增长，数据库 CPU 使用率 100% 报警频繁起来。第一个想到的就是慢 Sql，咱们对未正当使用索引的表退出索引后，问题仍然没有失去解决，深刻排查时，发现在 order by id asc limit n 时，即便 where 条件曾经蕴含了笼罩索引，优化器还是抉择了谬误的索引导致。通过查问大量材料，问题失去了解决。这里将解决问题的思路以及排查过程分享进去，如果有谬误欢送斧正。

2 注释
2.1 环境介绍

2.2 发现问题
22 日开始，收到以下图 1 报警变得频繁起来，因为数据库中会有大数据推数动作，数据库 CPU 偶然报警并没有引起对该问题的器重，直到通过图 2 对整日监控数据分析时，才发现问题的严重性，从 0 点开始，数据库 CPU 频繁被打满。

图 1：报警图

图 2：整日 CPU 监控图

2.3 排查问题
发现问题后，开始排查慢 Sql，发现很多查问未增加适合的索引，通过一轮修复后，问题仍然没有失去解决，在深刻排查时发现了一个奇怪景象，SQL 代码如下（表名曾经替换），比较简单的一个单表查问语句。

SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
    AND id > 2100
ORDER BY
    id 
    LIMIT 500;

看似比较简单的查问，但执行时长均匀在 90s 以上，并且调用频次较高。如图 3 所示。

图 3：慢 Sql 均匀执行时长

开始检查表信息，能够看到表数据量在 2100w 左右。

图 4：数据表状况

排查索引状况，主键为 id，并且有 business_day 与 full_ps_code 的联结索引。


  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_business_day_full_ps_code` (`business_day`,`full_ps_code`)
  ========== 以下索引能够疏忽 ========
  KEY `idx_erp_month_businessday` (`erp`,`month`,`business_day`),
  KEY `idx_business_day_erp` (`business_day`,`erp`),
  KEY `idx_erp_month_ps_plan_id` (`erp`,`month`,`ps_performance_plan_id`),
  ......

通过 Explain 查看执行打算时发现，possible_keys 中蕴含下面的联结索引，而 Key 却抉择了 Primary 主键索引，扫描行数 Rows 为 1700w，简直等于全表扫描。

图 5：执行打算状况

2.4 解决问题
第一次，咱们剖析是，因为 Where 条件中蕴含了 ID，查问分析器认为主键索引扫描行数会少，同时依据主键排序，应用主键索引会更加正当，咱们试着增加以下索引，想要让查问分析器命中咱们新加的索引。

ADD INDEX `idx_test`(`business_day`, `full_ps_code`, `id`) USING BTREE;

再次通过 Explain 语句进行剖析，发现执行打算齐全没变，还是走的主键索引。

explain
SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
    AND id > 2100
ORDER BY
    id 
    LIMIT 500;

图 6：执行打算状况

第二次，咱们通过强制指定索引形式 force index (idx_test)形式，再次剖析执行状况，失去图 7 的后果，同样的查问条件同样的后果，查问时长由 90s->0.49s 左右。问题失去解决

图 7：强制指定索引后执行打算状况

第三次，咱们狐疑是 where 条件中有 ID 导致间接走的主键索引，where 条件中去掉 id，Sql 调整如下，而后进行剖析。仍然没有命中索引，扫描 rows 变成 111342，查问工夫 96s

SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
ORDER BY
    id 
    LIMIT 500

第四次，咱们把 order by 去掉，SQL 调整如下，而后进行剖析。命中了 idx_business_day_full_ps_code 之前建设的联结索引。扫描行数变成 154900，查问时长变为 0.062s，然而发现后果与料想的不统一，产生了乱序

SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
    AND id > 2100
LIMIT 500;

第五次，通过前几次的剖析能够确定，order by 导致查问分析器抉择了主键索引，咱们在 Order by 中减少排序字段，将 Sql 调整如下，同样能够命中咱们之前的联结索引，查问时长为 0.034s，因为先依照主键排序，后果是统一的。相比第四种办法多了一份 filesort，问题得解决。


SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
    AND id > 2100
ORDER BY
    id，full_ps_code
    LIMIT 500;

第六次，咱们思考是不是 Limit 导致的问题，咱们将 Limit 500 调整到 1000，Sql 调整如下，奇观产生了，命中了联结索引，查问时长为 0.316s，后果统一，只不过多返回来 500 条数据。问题失去了解决。通过屡次试验 Limit 大于 695 时就会命中联结索引，查问条件下的数据量是 79963，696/79963 大略占比是 0.0087，猜想当获取数据比超过 0.0087 时，会抉择联结索引，未找到源代码验证此论断。

SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
    AND id > 2100
ORDER BY
    id 
    LIMIT 1000;

通过咱们的验证，其中第 2、5、6 三种办法都能够解决性能问题。为了不影响线上，咱们立刻批改代码，并抉择了 force index 的形式，上线察看一段时间后，数据库 CPU 恢复正常，问题失去了解决。

3 预先剖析

上线后问题失去了解决，同时也留给我了很多疑难。

为什么明明 where 条件中蕴含了联结索引，却未能命中，反而抉择了性能较慢的主键索引？
为什么在 order by 中减少了一个索引其余字段，就能够命中联结索引了呢？
为什么我仅仅是将 limit 限度条件由原来的 500 调大后，也能命中联结索引呢？
这所有的答案都来自 MySQL 的查问优化器。

3.1 查问优化器
查问优化器是专门负责优化查问语句的优化器模块，通过计算剖析收集的各种零碎统计信息，为查问给出最优的执行打算——最优的数据检索形式。

优化器决定如何执行查问的形式是基于一种称为基于代价的优化的办法。5.7 在代价类型上分为 IO、CPU、Memory。内存的代价收集了，然而并没有参加最终的代价计算。Mysql 中引入了两个零碎表，mysql.server_cost 和 mysql.engine_cost，server_cost 对应 CPU 的代价，engine_cost 代表 IO 的代价。

server_cost（CPU 代价）
row_evaluate_cost (default 0.2) 计算符合条件的行的代价，行数越多，此项代价越大
memory_temptable_create_cost (default 2.0) 内存长期表的创立代价
memory_temptable_row_cost (default 0.2) 内存长期表的行代价
key_compare_cost (default 0.1) 键比拟的代价，例如排序
disk_temptable_create_cost (default 40.0) 外部 myisam 或 innodb 长期表的创立代价
disk_temptable_row_cost (default 1.0) 外部 myisam 或 innodb 长期表的行代价
由上能够看出创立长期表的代价是很高的，尤其是外部的 myisam 或 innodb 长期表。

engine_cost（IO 代价）
io_block_read_cost (default 1.0) 从磁盘读数据的代价，对 innodb 来说，示意从磁盘读一个 page 的代价
memory_block_read_cost (default 1.0) 从内存读数据的代价，对 innodb 来说，示意从 buffer pool 读一个 page 的代价
这些信息都能够在数据库中配置，当数据库中未配置时，从 MySql 源代码（5.7）中能够看到以上默认值状况

3.2 代价配置

-- 批改 io_block_read_cost 值为 2
UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
--FLUSH OPTIMIZER_COSTS 失效，只对新连贯无效，老连贯有效。FLUSH OPTIMIZER_COSTS;

3.3 代价计算
代价是如何算进去的呢，通过读 MySql 的源代码，能够找到最终的答案

3.3.1 全表扫描（table_scan_cost）
以下代码摘自 MySql Server（5.7 分支），全表扫描时，IO 与 CPU 的代价计算形式。

double scan_time=
   cost_model->row_evaluate_cost(static_cast<double>(records)) + 1;
// row_evaluate_cost 外围代码
// rows * m_server_cost_constants->row_evaluate_cost() 
// 数据行数 * 0.2 (row_evaluate_cost 默认值) + 1 = CPU 代价
Cost_estimate cost_est= head->file->table_scan_cost();
//table_scan_cost 外围代码
//const double io_cost 
//     = scan_time() * table->cost_model()->page_read_cost(1.0)
// 这部分代价为 IO 局部
//page_read_cost 外围代码
//
//const double in_mem= m_table->file->table_in_memory_estimate();
//
// table_in_memory_estimate 外围逻辑
// 如果表的统计信息中提供了信息, 应用统计信息，如果没有则应用启发式估值计算
//pages=1.0
//
//const double pages_in_mem= pages * in_mem;
//const double pages_on_disk= pages - pages_in_mem;
//
//
// 计算出两局部 IO 的代价之和
//const double cost= buffer_block_read_cost(pages_in_mem) +
//  io_block_read_cost(pages_on_disk);
//
//
//buffer_block_read_cost 外围代码
// pages_in_mem 比例 * 1.0（memory_block_read_cost 的默认值）// blocks * m_se_cost_constants->memory_block_read_cost()
//
//
//io_block_read_cost 外围代码
//pages_on_disk * 1.0（io_block_read_cost 的默认值）//blocks * m_se_cost_constants->io_block_read_cost();
 // 返回 IO 与 CPU 代价
 // 这里减少了个系数调整，起因未知
 cost_est.add_io(1.1);
 cost_est.add_cpu(scan_time);

依据源代码剖析，当表中蕴含 100 行数据时，全表扫描的老本为 23.1，计算逻辑如下

//CPU 代价 = 总数据行数 * 0.2 (row_evaluate_cost 默认值) + 1 
cpu_cost = 100 * 0.2 + 1 等于 21
io_cost = 1.1 + 1.0 等于 2.1
// 总成本 = cpu_cost + io_cost = 21 + 2.1 = 23.1

验证后果如下图

3.3.2 索引扫描（index_scan_cost）

以下代码摘自 MySql Server（5.7 分支），当呈现索引扫描时，是如何进行计算的，外围代码如下

// 外围代码解析
*cost= index_scan_cost(keyno, static_cast<double>(n_ranges),
                         static_cast<double>(total_rows));
cost->add_cpu(cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01)

io 代价计算外围代码

// 外围代码
 const double io_cost= index_only_read_time(index, rows) *
   table->cost_model()->page_read_cost_index(index, 1.0);
// index_only_read_time(index, rows)
// 估算 index 占 page 个数
//page_read_cost_index(index, 1.0)
// 依据 buffer pool 大小和索引大小来估算 page in memory 和 in disk 的比例，计算读一个 page 的代价

cpu 代价计算外围代码

add_cpu(cost_model->row_evaluate_cost(static_cast<double>(total_rows)) + 0.01);
//total_rows 等于索引过滤后的总行数
//row_evaluate_cost 与全表扫描的逻辑相似，// 区别在与一个是 table_in_memory_estimate 一个是 index_in_memory_estimate

3.3.3 其余形式

计算代价的形式有很多，其余形式请参考 MySql 原代码。https://github.com/mysql/mysq…

3.4 深度解析
通过查看 optimizer_trace，能够理解查问优化器是如何抉择的索引。

set optimizer_trace="enabled=on";
-- 如果不设置大小，可能导致 json 输入不全
set OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
SELECT
    *
FROM
    test 
WHERE
    is_delete = 0 
    AND business_day = '2021-12-20' 
    AND full_ps_code LIKE  'xxx%'
    AND id > 0
ORDER BY
    id 
    LIMIT 500;
select * FROM information_schema.optimizer_trace;
set optimizer_trace="enabled=off";

通过剖析 rows_estimation 节点，能够看到通过全表扫描（table_scan）的话的代价是 8.29e6，同时也能够看到该查问能够抉择到主键索引与联结索引，如下图。

上图中全表扫描的代价是 8.29e6，咱们转换成一般计数法为 8290000，如果应用主键索引老本是 3530000，联结索引 185881，最小的应该是 185881 联结索引，也能够看到第一步通过老本剖析的确抉择了咱们的联结索引。

然而为什么还是抉择了主键索引呢？

通过往下看，在 reconsidering_access_paths_for_index_ordering 节点下，发现因为 Order by 导致从新抉择了索引，在下图中能够看到主键索引可用（usable=true），咱们的联结索引为 not_applicable（不实用），意味着排序只能应用主键索引。

接下来通过 index_order_summary 能够看出，执行打算最终被调整，由原来的联结索引改成了主键索引，就是说这个抉择忽视了之前的基于索引老本的抉择。

为什么会有这样的一个选项呢，次要起因如下：
The short explanation is that the optimizer thinks — or should I say hopes — that scanning the whole table (which is already sorted by the id field) will find the limited rows quick enough, and that this will avoid a sort operation. So by trying to avoid a sort, the optimizer ends-up losing time scanning the table.

从这段解释能够看出次要起因是因为咱们应用了 order by id asc 这种基于 id 的排序写法，优化器认为排序是个低廉的操作，所以为了防止排序，并且它认为 limit n 的 n 如果很小的话即便应用全表扫描也能很快执行完，所以它抉择了全表扫描，也就防止了 id 的排序。

5 总结
查问优化器会基于代价来抉择最优的执行打算，但因为 order by id limit n 的存在，MySql 可能会从新抉择一个谬误的索引，疏忽原有的基于代价抉择进去的索引，转而抉择全表扫描的主键索引。这个问题在国内外有大量的用户反馈，BUG 地址 https://bugs.mysql.com/bug.ph…。官网称在 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";

当你也呈现了本篇文章碰到的问题时，能够采纳以下的办法来解决

应用 force index，强制指定索引。
order by 中减少一个联结索引的 key。
扩充 limit 返回的范畴（不举荐，随着数据量的增大，可能还会走回主键索引）
order by (id+0) asc 坑骗查问优化器，让其抉择联结索引。
MySQL 5.7.33 版本以上，能够敞开 prefer_ordering_index 解决。

作者：陈强

正文完