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 > 2100ORDER 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语句进行剖析，发现执行打算齐全没变，还是走的主键索引。
explainSELECT *FROM test WHERE is_delete = 0 AND business_day = '2021-12-20' AND full_ps_code LIKE 'xxx%' AND id > 2100ORDER 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 > 2100LIMIT 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 > 2100ORDER 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 > 2100ORDER 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值为2UPDATE 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 等于 21io_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_estimate3.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 > 0ORDER 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=jsonsql语句---------------------------------------------------------------------------第二种 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解决。
作者：陈强