基于代价的慢查询优化建议

背景

​ 慢查询是指数据库中查询时间超过指定阈值（美团设置为 100ms）的 SQL，它是数据库的性能杀手，也是业务优化数据库访问的重要抓手。随着业务的高速增长，日均慢查询量已经过亿条，此前因慢查询导致的故障约占数据库故障总数的 10% 以上，而且高级别的故障呈日益增长趋势。因此，对慢查询的优化已经变得刻不容缓。

​ 基于代价的方法使用了和数据库优化器相同的方式，去量化评估所有的可能性，选出的是执行 SQL 耗费代价最小的索引。

基于代价的优化器

SQL执行与优化器

​ 一条 SQL 在 MySQL 服务器中执行流程主要包含：SQL 解析、基于语法树的准备工作、优化器的逻辑变化、优化器的代价准备工作、基于代价模型的优化、进行额外的优化和运行执行计划等部分。

代价模型说明

​ 代价模型将操作分为 Server 层和 Engine（存储引擎）层两类，Server 层主要是CPU 代价，Engine 层主要是 IO 代价。比如 MySQL 从磁盘读取一个数据页的代价io_block_read_cost 为 1，计算符合条件的行代价为 row_evaluate_cost 为 0.2。

除此之外还有：

1、memory_temptable_create_cost (default 2.0) 内存临时表的创建代价。

2、memory_temptable_row_cost (default 0.2) 内存临时表的行代价。

3、key_compare_cost (default 0.1) 键比较的代价，例如排序。

4、disk_temptable_create_cost (default 40.0) 内部 myisam 或 innodb 临时表的创建代价。

5、disk_temptable_row_cost (default 1.0) 内部 myisam 或 innodb 临时表的行代价

​ 在 MySQL 5.7 中，这些操作代价的默认值都可以进行配置。为了计算出方案的总代价，还需要参考一些统计数据，如表数据量大小、元数据和索引信息等。

基于代价的索引选择

依据SQL select * from sync_test1 where name like ‘Bobby%’ and dt > ‘2021-07-06’ 根据代价模型来推演一下代价的计算过程：

● 走全表扫描的代价：io_cost + cpu_cost =（数据页个数 * io_block_read_cost）+ ( 数 据 行 数 * row_evaluate_cost + 1.1) =（data_length / block_size + 1）+ (rows * 0.2 + 1.1) = (9977856 / 16384 + 1) + (105084 * 0.2 +1.1) = 21627.9。

● 走二级索引 IX_name 的代价：io_cost + cpu_cost = ( 预估范围行数 * io_block_read_cost + 1) + ( 数据行数 * row_evaluate_cost + 0.01) = (572 1 + 1) + (5720.2 + 0.01) = 687.41。

● 走二级索引 IX_dt 的代价：io_cost + cpu_cost = ( 预估范围行数 * io_block_read_cost + 1) + ( 数据行数 * row_evaluate_cost + 0.01) = (38698 * 1 +\1) + (38698*0.2 + 0.01) = 46438.61。后端 < 55

● 走二级索引 IX_dt_name 的代价 : io_cost + cpu_cost = ( 预估范围行数 *io_block_read_cost + 1) + ( 数 据 行 数 * row_evaluate_cost + 0.01) =(38292 * 1 + 1) + (38292 * 0.2 + 0.01) = 45951.41。

● 走二级索引 IX_name_dt 的代价：io_cost + cpu_cost = ( 预估范围行数 io_block_read_cost + 1) + ( 数 据 行 数 * row_evaluate_cost + 0.01) =(572 * 1 + 1) + (5720.2 + 0.01) = 687.41。

​ 如果想借助 MySQL 优化器给慢查询计算出最佳索引，那么需要真实地在业务表上添加所有候选索引。对于线上业务来说，直接添加索引的时间空间成本太高，是不可接受的。MySQL 优化器选最佳索引用到的数据是索引元数据和统计数据，所以可以通过给它提供候选索引的这些数据，而非真实添加索引的这种方式来实现。

​ 通过了解MySQL 的代码结构和优化器流程，我们发现是可行的：一部分存在于Server 层的 frm 文件中，比如索引定义；另一部分存在于 Engine 层中，或者通过调用 Engine 层的接口函数来获取，比如索引中某个列的不同值个数、索引占据的页面大小等。

​ 由于MySQL 本身就支持自定义存储引擎，所以索引推荐思路是构建一个支持虚假索引的存储引擎，在它上面建立包含候选索引的空表，再采集样本数据，计算出统计数据提供给优化器，让优化器选出最优索引，整个调用关系如下图所示：

索引推荐实现

​ 因为存储引擎本身并不具备对外提供服务的能力，直接在 MySQL Server 层修改也难以维护，所以我们将整个索引推荐系统拆分成支持虚假索引的 Fakeindex 存储引擎和对外提供服务的 Go-Server 两部分，整体架构图如下：

​ Fakeindex 存储引擎，这是一个轻量级的存储引擎，负责将索引的相关接口透传到 Go-Server 部分。因为它必须采用 C++ 实现，与 Go-Server 间存在跨语言调用的问题，我们使用了 Go 原生的轻量级 RPC 技术 +cgo 来避免引入重量级的 RPC 框架，也不必引入第三方依赖包。函数调用链路如下所示，MySQL优化器调用 Fakeindex 的 C++ 函数，参数转换成 C 语言，然后通过 cgo 调用到Go 语言的方法，再通过 Go 自带的 RPC 客户端向服务端发起调用。

前置校验

排除一些不支持通过添加索引来提高查询效率的场景

提取关键列名

​ MySQL 对排序、聚合、表连接、聚合函数（如 max）也支持使用索引来提高查询效率。我们对 SQL 进行语法树解析，在树节点的 where、join、order by、group by、聚合函数中提取列名，作为索引的候选列。值得注意的是，对于某些SQL，还需结合表结构才能准确地提取。

生成候选索引

​ 将提取出的关键列名进行全排列即包含所有的索引组合，排除一些索引才能得到所有的候选索引。

比如：

1、已经存在的索引，因为 MySQL 支持使用前缀索引；

2、超过最大索引长度 3072 字节限制的索引；

3、一些暂时不支持的索引，如带地理数据类型列的空间索引。

数据采集

​ 直接从业务数据库采集，数据分成元数据、统计数据、样本数据三部分：

● 元数据：即表的定义数据，包括列定义、索引定义，可通过 show create table获取。

● 统计数据：如表的行数、表数据大小、索引大小，可以通过查询 infromation_schema.tables 获取；已存在索引的 cardinality（关键值：即索引列的不同值个数，值越大，索引优化效果越明显），可以通过查询 mysql.innodb_index_stats 表获取。

● 样本数据：候选索引为假索引，采集的统计数据并不包含假索引的数据，这里我们通过采集原表的样本数据来计算出假索引的统计数据

候选索引代价评估

​ 让优化器帮助我们从候选索引中选出最佳索引，主要步骤如下：

1、 建包含候选索引的表：将候选索引塞入原表定义，并把存储引擎改为 Fakeindex，在推荐引擎的 mysqld 上创建表。

2、通过在推荐引擎 mysqld 上 explain format=json SQL，获取优化器选择的索引。

实践验证

用慢查询数据，共 246G、约 3 万个 SQL 模板用例做了一个初步测试。系统基本能覆盖到大部分的慢查询。但还是会出现无效的推荐，

大致原因如下：

● 索引推荐计算出的 Cost 严重依赖样本数据的质量，在当表数据分布不均或数据倾斜时会导致统计数据出现误差，导致推荐出错误索引。

● 索引推荐系统本身存在缺陷，从而导致推荐出错误索引。

● MySQL 优化器自身存在的缺陷，导致推荐出错误索引。

● 在业务添加索引前后增加了索引的有效性验证和效果追踪

有效性验证

通过真正地在样本库上真实执行 SQL，并添加索引来验证其有效性

效果追踪

​ 考虑到使用采样数据验证的局限性，所以当在生产环境索引添加完毕之后，会立即对添加的索引进行效果追踪。一方面通过 explain 验证索引是否被真正用到，以及Cost 是否减小；另一方面用 Flink 实时跟踪该数据库的全量 SQL 访问数据，通过对比索引添加前后，该 SQL 的真实执行时间来判断索引是否有效。如果发现有性能方面的回退，则立即发出告警，周知到 DBA 和研发人员。

仿真环境

​ 当推荐链路出现问题时，直接在线上排查验证问题的话，很容易给业务带来安全隐患，同时也降低了系统的稳定性。对此我们搭建了离线仿真环境，利用数据库备份构建了和生产环境一样的数据源，并完整复刻了线上推荐链路的各个步骤，在仿真环境回放异常案例，复现问题、排查根因，反复验证改进方案后再上线到生产系统，进而不断优化现有系统，提升推荐质量。

慢查询治理运营

要从时间维度的三个方向将慢查询接入索引推荐，推广治理

过去 - 历史慢查询

​ 这类慢查询属于过去产生的，并且一直存在，数量较多，治理推动力不足，可通过收集历史慢查询日志发现，分成两类接入：

● 核心数据库：该类慢查询通常会被周期性地关注，如慢查询周报、月报，可直接将优化建议提前生成出来，接入它们，一并运营治理。

● 普通数据库：可将优化建议直接接入数据库平台的慢查询模块，让研发自助地选择治理哪些慢查询

现在 - 新增慢查询

​ 这类慢查询属于当前产生的，数量较少，属于治理的重点，也可通过实时收集慢查询日志发现，分成两类接入：

● 影响程度一般的慢查询：可通过实时分析慢查询日志，对比历史慢查询，识别出新增慢查询，并生成优化建议，为用户创建数据库风险项，跟进治理。

● 影响程度较大的慢查询：该类通常会引发数据库告警，如慢查询导致数据库Load 过高，可通过故障诊断根因系统，识别出具体的慢查询 SQL，并生成优化建议，及时推送到故障处理群，降低故障处理时长。

未来 - 潜在慢查询

​ 这类查询属于当前还没被定义成慢查询，随着时间推进可能变成演变成慢查询，对于一些核心业务来说，往往会引发故障，属于他们治理的重点，分成两类接入：

● 未上线的准慢查询：项目准备上线而引入的新的准慢查询，可接入发布前的集成测试流水线，Java 项目可通过 agentmain 的代理方式拦截被测试用例覆盖到的 SQL，再通过经验 +explain 识别出慢查询，并生成优化建议，给用户在需求管理系统上创建缺陷任务，解决后才能发布上线。

● 已上线的准慢查询：该类属于当前执行时间较快的 SQL，随着表数据量的增加，会演变成慢查询，最常见的就是全表扫描，这类可通过增加慢查询配置参数 log_queries_not_using_indexes 记录到慢日志，并生成优化建议，为用户创建数据库风险项，跟进治理。

项目运行情况

实践案例主要以新增慢查询为突破点，重点为全表扫描推荐优化建议。灰度测试接入了一小部分业务，共分析了六千多条慢查询，推荐了一千多条高效索引建议。另外也可通过数据库平台自助发起 SQL 优化建议工单，

发展展望

​ 需要提高系统大规模的数据并发处理的能力。另外，当前该系统还是针对单 SQL 的优化，没有考虑维护新索引带来的代价，如占用额外的磁盘空间，使写操作变慢，也没有考虑到 MySQL 选错索引引发其他 SQL 的性能回退。对于业务或者 DBA 来说，更多关心的是整个数据库或者集群层面的优化。

​ 阿里云的 DAS 则是站在全局的角度考量，综合考虑各个因素，输出需要创建的新索引、需要改写的索引、需要删除的索引，实现数据库性能最大化提升，同时最大化降低磁盘空间消耗。未来我们也将不断优化和改进，实现类似基于 Workload 的全局优化。

旁外话

​ 适用于大公司、中小公司不适用，各个项目数据库未打通，另外各个团队未健全。中小公司若出现慢查询问题，建议采用直接模拟优化器方式 优化sql。

其他

​ 本文大致内容是参阅一些别人现行案例，若采用JAVA方式，也可从数据存储结构方向入口。例如数据库为mysql，数据库的主要存储引擎有MyISAM、InnoDB。依据现表结构、索引文件计算出候选索引，再根据binlog(二进制/需进行转换)获取MySQL所有的DML操作以及搭建离线数据库。通过定时任务每日执行，基于代价的计算最佳索引，并对慢查询给出优化意见等。

​ 每个MyISAM表存储在磁盘上的三个文件中 。这些文件的名称以表名开头，并有一个扩展名来指示文件类型 。.frm文件存储表的格式。 .MYD (MYData) 文件存储表的数据。 .MYI (MYIndex) 文件存储索引。
InnoDB存储表时会将表的定义和数据索引等信息分开存储，其中表的定义存储在 .frm 文件中，数据索引存储在 .ibd 文件中。所有的数据都被逻辑地存放在表空间中，表空间（tablespace）是存储引擎中最高的存储逻辑单位，在表空间的下面又包括段（segment）、区（extent）、页（page）。**默认情况下，表空间中的页大小都为 16KB，当然也可以通过改变 innodb_page_size 选项对默认大小进行修改。一个区的大小最小为 1MB，页的数量最少为 64 个。