不是慢SQL的锅！一次数据库CPU被打满的诡异排查…… - 运维 - dbaplus社群：围绕Data、Blockchain、AiOps的企业级专业社群。技术大咖、原创干货，每天精品原创文章推送，每周

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

气势凌人的打火机 · Error response from ...· 11 月前 ·

幸福的开水瓶 · 巧用Access ...· 1 年前 ·

完美的皮带 · django学习六--操作model创建数据 ...· 2 年前 ·

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

图1 报警图

图2 整日 CPU 监控图


   
    
     SELECT


   
    
     FROM


   
    
     test


   
    
     WHERE


   
    is_delete =
    
     0


   
    
     AND
    
    business_day =
    
     '2021-12-20'


   
    
     AND
    
    full_ps_code
    
     LIKE
    
    
     'xxx%'

id

看似比较简单的查询，但执行时长平均在 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 执行计划情况

图6 执行计划情况

第二次，我们通过强制指定索引方式 force index (idx_test) 方式，再次分析执行情况，得到图 7 的结果，同样的查询条件同样的结果，查询时长由 90s->0.49s 左右。问题得到解决

图7 强制指定索引后执行计划情况

第三次，我们怀疑是 where 条件中有 ID 导致直接走的主键索引，where 条件中去掉 id，Sql 调整如下，然后进行分析。依然没有命中索引，扫描 rows 变成 111342，查询时间 96s

查询优化器是专门负责优化查询语句的优化器模块，通过计算分析收集的各种系统统计信息，为查询给出最优的执行计划 —— 最优的数据检索方式。

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

server_cost（CPU 代价）


   
    
     --修改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;


   
    
     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

验证结果如下图

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）其他方式

计算代价的方式有很多，其他方式请参考 MySql 原代码。https://github.com/mysql/mysql-server.git


   
    
     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%'

id


   
    
     select
    
    *
    
     FROM
    
    information_schema.optimizer_trace;


   
    
     set
    
    optimizer_trace=
    
     "enabled=off"
    
    ;

通过分析 rows_estimation 节点，可以看到通过全表扫描（table_scan）的话的代价是 8.29e6，同时也可以看到该查询可以选择到主键索引与联合索引，如下图。

上图中全表扫描的代价是 8.29e6，我们转换成普通计数法为 8290000，如果使用主键索引成本是 3530000，联合索引 185881，最小的应该是 185881 联合索引，也可以看到第一步通过成本分析确实选择了我们的联合索引。

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.


   
    
     --第一种


   
    
     explain
    
    
     format
    
    =
    
     json


   
    
     -------------------------------------------------------------------------


   
    
     --第二种 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 解决。