关于美团:MySQL自治平台建设的内核原理及实践下

0 前文回顾

在《MySQL 自治平台建设的内核原理及实际（上）》一文中，咱们次要介绍了数据库的异样发现与诊断方面的内容，在诊断方面常常会碰到一些难以找出根因的 Case。针对这些疑难杂症 Case，通过本篇能够理解到，如何通过内核可观测性以及全量 SQL 来解决这些问题。除此之外，在得出根因后，如何解决异样，如何对 SQL 进行优化，以及如何进行 SQL 治理等相干方面问题，也将在本篇中给予解答。

1 内核可观测性建设

1.1 内核可观测性建设

1.1.1 性能诊断挑战

在自治性能诊断平台的建设过程中，咱们发现如下两大挑战：

1. 很多 SQL 性能抖动的问题找不出根因，比方 SQL 的执行时长莫名其妙的忽然变大，其执行打算良好、扫描跟返回的行数都很少，也没有行锁、MDL 锁相干锁阻塞；查看慢查问日志，也没有哪个字段的耗时比拟高，然而 SQL 的执行时长就是忽然变长，有时候达到几十秒长，而平时往往是几毫秒，各种剖析后找不出起因。
2. 有时候在诊断一些指标异样的根因时，凭借的是不太严格的教训，而不是量化剖析，比方 thread_running 或者 slow_queries 值忽然升高的时候，可能会通过表 information_schema.processlist 查看以后的沉闷会话中线程的状态，看一下状态是不是有行锁或者 MDL 锁之类的阻塞，或者通过慢查问日志里的相干数据来诊断根因。这里的挑战是：咱们看到的是整个 SQL 某个工夫点的刹时状态，或者只是整个 SQL 执行过程中的局部数据，而不是整体的数据，所以得出的根因诊断可能是全面的，兴许一瞬间看到的是行锁，然而大部分工夫被 MDL 锁阻塞。

1.1.2 解决思路

如果应用的是社区版本的 MySQL，基本上都会面临下面两大问题。咱们先从内核的角度剖析一下这两个挑战，对于第一个挑战，次要是对 MySQL 在内核层面执行细节不够理解，比方一条 SQL 执行了 10s，而从内核层面来看的话，这十秒的工夫可能会有几百个步骤组成，查看后可能发现 row 或者 MDL 锁等待时间加起来只有 1 秒，那么其余的 9 秒的耗时在哪里呢？可能产生在这几百个步骤中的任何一个或者多个，所以如果没有这几百个步骤的明细数据，就无奈诊断出忽然呈现的性能问题的根因。

第二个问题跟第一个问题从实质上来说是一样的。因为采集的数据是某个工夫点的快照数据（通过沉闷会话），或者只是局部指标的数据（通过慢查问日志），所以咱们看到的只是全面的信息，而没有方法获取到整个 SQL 的残缺的耗时散布信息。

1.1.3 Wait 耗时量化分析法

在剖析完起因之后，咱们参考了 TSA 的思维，同时联合 MySQL 本身的特点来做基于 Wait 的内核可观测性的建设。从 TSA 能够看出，SQL 执行的总耗时无非就是由 Off-CPU 跟 ON-CPU 两大部分组成，如果 SQL 有耗时长的问题，首先要确认是在 OnCPU 还是在 OffCPU 上耗时多。如果是前者，那么阐明 SQL 自身有问题，比方耗费资源太多（比方有效索引造成扫描行数过多）；如果是后者，那么阐明 SQL 自身没有问题，而是受到烦扰或者系统资源有余，进而造成 OffCPU 层面耗时过多。

无论是何种状况，咱们都须要把 OnCPU 跟 OffCPU 的耗时做进一步的拆分，从而来查看耗时瓶颈点到底在哪里，并且对耗时比拟多的要害代码门路交由内核团队来进行埋点；绝对 MySQL performance_schema 库中统计的 Wait 信息，实现十分轻量，所以对总体的性能的影响很小，并且耗时指标都是 SQL 明细粒度的，而不是相似 performance_schema 中的 SQL 模版级别的聚合数据。

耗时在 OnCPU

在剖析一条 SQL 执行耗时，如果发现像下图一样，耗时散布大部分都在 OnCPU，这阐明 SQL 自身有重大的性能问题（全表扫描、过滤成果不佳或者查问优化器的 bug 等起因），咱们能够把诊断的方向集中在如何优化 SQL 自身就能够了，而不须要去关注其余的方面，如锁阻塞、磁盘或者网络提早等 OffCPU 方面的问题。通过应用 getrusage 办法来获取某条 SQL 的 OnCPU 耗时时长，比方在 SQL 执行前，获取以后线程的 ru_utime、ru_stime 工夫，而后在 SQL 执行结束时再次获取 ru_utime、ru_stime 值，就能够失去 SQL 执行的 CPU Time。

耗时在 OffCPU

如果发现 OnCPU 的耗时占总耗时的比率比拟低，通过 OffCPU 的相干指标发现其占总耗时的比率比拟高，阐明 SQL 自身没有问题，可能是被锁住了、硬件资源有余或者是被内核层面某个内核锁给卡住了。咱们就须要查看到底是 OffCPU 相干的哪个，或者哪些指标耗时比拟高，须要在内核层面对执行过程中的可能的、耗时比拟长的执行代码门路进行埋点，只有获取到了跟性能相干的要害的数据，能力做出精确的判断。

如何抉择适合而全面的 OffCPU 相干的代码门路进行埋点？通过摸索，这里提供了如下几个形式：

1. 剖析 setup_instruments 表中蕴含的要害埋点信息，大抵晓得有哪些要害的指标能够埋点，比方 wait/IO、Mutex 类等，再联合本身的教训来判断哪些指标可能有性能瓶颈的问题。
2. 依据理论疑难 case 来判断抉择哪些 Wait 指标，比方看到 ”Thread xxx has waited at trx0trx.cc line 1193 for 241.00 seconds the semaphore: xxxxx mutex REDO_RSEG created trx0rseg.cc:211, lock var 1″，则对 mutex REDO_RSEG 进行埋点，看到 ”Thread xxx has waited at dict0dict.cc line 1239 for 245.00 seconds the semaphore: xxxxxx Mutex DICT_SYS created dict0dict.cc:1173, lock var 1″ 则对 Mutex DICT_SYS 进行埋点。
3. 源码剖析，在内核层面对 SQL 的执行过程进行逐渐的 Debug，依据教训剖析可能的瓶颈点来埋点；上面举两个例子别离就纯源码的角度来剖析，SQL 在读、写过程中哪些地方可能会造成性能抖动并做埋点。

例子 1，在 SQL 读操作的执行过程中，须要从 buffer pool 中申请内存资源，如果始终满足不了内存资源的申请则始终会循环尝试获取，如果在 2000ms 内还是没有从 Buffer pool 中申请到须要的内存，会打印日志“Difficult to find free blocks in the buffer pool……”，来表明内存很缓和；这种长时间期待内存资源的获取会生产了性能抖动，这个时候就须要在对应的内核代码处进行埋点获取 BP 内存资源等待时间。

例子 2，在 SQL 写操作的执行过程中是须要写 redo log 的，如果 redo log 空间不够，则须要刷盘 redo log 跟 BP 中的脏页，而刷脏页可能是个很耗时的操作，并会引起包含沉闷会话突增、慢查问等性能抖动问题。这个场景跟下面的相似，也须要对源码进行剖析，并且在要害的地位进行埋点来统计刷脏的耗时。

1.1.4 Wait 指标档次图

通过下面的三种形式咱们一直地迭代累计的要害耗时指标，整顿成如下的 OffCPU 跟 OnCPU 两大类的分层架构图。截止目前为止，咱们内核团队在内核层面埋点了 100 多个要害指标来辅助诊断，指标分为 Statement 跟 Wait 两大层级。

Statement

SQL 语句级别的指标，如 QUERY_TIME、Row_time 行锁工夫、ROWS_EXAMINED、ROWS_SENT、Bytes_sent、NO_INDEX_USED、Full_SCAN 等，然而这个层面的数据有余于判断出 SQL 性能问题的根因，比方 QUERY_TIME 很长，然而 ROWS_EXAMINED、ROWS_SENT 都很小，就须要进入下一个 Wait 级别的指标进行进一步的剖析。

Wait

Wait 层面次要是 MySQL 内核层面的指标，比方 Latch（Mutex、rw_lock、sx_lock、cond）的指标，这里须要阐明的是下面的分层图跟 MySQL 自带的 performance_schema 中的 wait type 分层图看上去很像，那为什么不间接应用而须要自研呢？这里次要从如下几个起因且内核团队都很好的解决了：

1. performance_schema 相干的统计信息是自实例启动后总体的 wait 工夫的聚合数据，而更须要的是任何时间段、单 SQL 例子的明细数据，因为诊断的往往是某个工夫点或者时间段的异样 SQL 问题。
2. performance_schema 中其有不少 Bug，除此之外，它只反对 OffCPU 类型的指标，OnCPU 耗时的统计不反对，甚至有些十分重要 OffCPU 的指标，比方 row lock 的 wait 统计信息都不反对。
3. 开启 performance_schema 相干的 wait 后，对总体的性能影响比拟大，而基于内核埋点的实现形式很轻量级，对数据库整体性能影响很小。

2 全量 SQL

全量 SQL，指的是把应用程序或者用户拜访 DB 的所有 SQL 汇合，须要捕捉到这些 SQL 并且发至后端进行剖析。在内核可观测性建设之前，原来采纳的形式是依据 MySQL 的协定，来解析 TCP 报文来提取出 SQL。此种实现形式的缺点也很显著，就是除了 SQL 文本外，其余的能获取到的要害 SQL 指标信息非常少，这样做数据库的故障诊断跟 SQL 性能剖析，会因为短少要害指标而带来挑战。

针对此挑战，咱们的解决方案是：革新为间接从 MySQL 内核吐数据，来作为全量 SQL 的数据起源（后面的内核可观测性剖析介绍可知，单条 SQL 维度上能从内核层面输入 100 多个要害指标，蕴含故障诊断跟 SQL 性能剖析须要的重要指标）。

2.1 实现形式

全量 SQL 其实有好几个实现形式，初版应用了抓 TCP 包的形式，当初逐渐从 TCP 抓包过渡到了基于内核输入 SQL 文本跟要害指标的计划。

2.2 内核实现形式

从内核层面来说，MySQL 对于用户的链接将创立独立的 thd 构造体，所以采集的全量 SQL 对应的 SQL 文本跟要害指标数据，能够存于 thd 构造体中。而后用户线程将 thd 保留的指标数据，复制到一个自定义内存池中（无锁队列）；输出模块有一个线程从内存池中顺次获取保留的信息后，写入全量 SQL 文件。具体流程如下图所示：

2.3 全量 SQL 总体架构

如何把海量信息（日均 PB 级别）上报到后端系统，来赋能故障诊断、SQL 审核、索引优化倡议等场景是一个较大的挑战。咱们首先通过数据采集器（rds-agent）读取 Full SQL File 文件内的内容，再往后端传输。

然而因为数据量特地大，为了节俭存储资源，剖析后选用 Snappy 压缩算法进行压缩，默认状况下压缩成果只有 1～2 倍，为此剖析了 Snappy 的源码，发现如果让类似的 SQL 文本聚合在一起，那么压缩成果会有很大的进步，所以依照 SQL 文本的前 N 个字符（N 取 50，能够依据 SQL 文本的理论状况做调整）进行了排序后再压缩，发现压缩比进步到了 7～8 倍。后端的 SQL 存储分成了两种模式，一种是存明细 SQL，另一种是存聚合后的 SQL 模版信息。

产品展现

采集到的全量 SQL 既能够模版化展现，点击模版后也能够看到明细的 SQL。

3 异样解决

根因剖析后，就须要依据具体根因来进行相应的解决，然而如何平安、牢靠的进行解决其实是一个很大的挑战。具体的策略是依据其操作是“无损”还是“有损”来采取不同的解决策略。

如果是对业务“无损”的操作，比方磁盘空间清理、参数值调整、缺失索引增加等，指标是让操作尽量的自动化。如果是对业务是“有损”的操作，比方须要 Kill 或者限流，把相干操作进行包装，发送相干的聊天群让 DBA 或者用户确认后再进行相干的操作。

对于 MySQL Hang 的状况，高可用团队会进行周期性探测，如果发现主库 MySQL Hang 了，则会主动进行主从切换，如果发现从库 MySQL Hang 了则会进行相应的 MySQL 实例下线替换动作。

3.1 零碎架构

整个异样解决的零碎架构图如下，由“异样发现与剖析”零碎以及 DBA 团队开发的“预案服务”零碎组成，前者提供准确的故障根因，后者依据具体的根因来平安、牢靠的执行对应的预案，使故障疾速复原。

3.2 产品展现

下图是两个异样发现、剖析、给出倡议以及解决的例子：1）异样发现、确认根因后，发现缺索引，零碎会主动调用索引优化倡议零碎给出倡议，用户可一键增加索引；2）对于慢查问或者主从提早问题，剖析根因后提供用户进行限流、Kill、参数调优等操作的倡议，防止异样被进一步放大。

4 索引优化倡议与治理

家喻户晓，很大一部分的数据库的异样都是跟 SQL 性能无关。很显然，日常的 SQL 性能问题的治理就很有必要，一个低成本但效果显著的 SQL 性能优化计划是提供索引优化倡议，并且能够将这种索引优化倡议的能力利用于 SQL 生命周期的三个阶段，这三个阶段下文会进一步论述。

1. 应用程序“公布前”的性能问题 SQL 审核（事先）；
2. SQL 执行过程中的实时性能问题 SQL 发现（事中）；
3. SQL“执行结束”后批量的 SQL 治理（预先）。

4.1 索引优化倡议

建设适宜的索引对 SQL 的性能晋升成果很显著，如何增加适宜的索引是一个有挑战性的工作。索引的增加不能光凭教训，因为索引是否被用到岂但跟表的数据的散布无关，还跟 MySQL 查问优化器的 Cost 逻辑有关系，所以“人肉”进行索引倡议的评估并不是一个好的计划。

解决形式是尽量要基于 MySQL 的 Cost 模型，利用 MySQL 本身提供的查问优化器的能力，来给出最佳的索引；索引优化倡议的建设个别分为三个阶段：1）单 SQL 维度的优化倡议；2）基于 workload 的整体优化倡议；3）索引自保护。这几个阶段的建设须要按程序来进行，并且不可跳跃。上面咱们将别离进行论述。

4.2 单 SQL 索引优化倡议

4.2.1 实现思路

单 SQL 索引优化倡议，就是指输出一条 SQL 语句后，优化倡议零碎给出一个索引优化倡议的过程。想要利用查问优化器自身的能力来实现索引倡议的指标，就必须先要理解 MySQL 的查问优化器是如何工作的。

这里举个简略的单表 SQL 查问的例子，帮忙咱们来初步理解其工作原理。咱们先理解查问优化器是如何从泛滥的候选执行打算中抉择最终的执行打算的；在有了这个根底之后，再来讲是如何利用查问优化器本身的能力来做索引优化倡议。

比方有 SQL 语句“select * from test_db.table1 where c2=3 and c3=4 and c4<‘3’”，别离在字段 id、c2、c3 以及 c4 下面建设了主健索引 Primary 以及三个二级索引 idx_c2、idx_c3 及 dx_c4。假如最终查问优化器从多个候选执行打算中抉择了“执行打算 2”（通过索引 idx_c2 做 index seek 操作来执行 SQL），问题是此 SQL 的候选执行打算有多个，为什么最初选中了通过索引 idx_c2 而不是其余的索引做 index seek 操作的执行打算呢？总的来说，抉择 idx_c2 为索引的执行打算，是因为相比于抉择其余索引生成的执行打算的 Cost 更小。

具体来说，针对 SQL 的两类表拜访形式：1）基于全表扫描的拜访形式；2）非全表扫描的形式（比方 Index_scan、ref 类型或者 range_access 等），下图是这些拜访形式 Cost 计算过程中波及到的查问优化器跟存储引擎的要害函数，指标是假如某个索引存在的状况下，批改这些函数来模仿 Cost 的计算过程，来发现哪个索引存在的状况下对应的 Cost 值最小。

流程中的各种数据拜访形式，对其 Cost 计算形式一一剖析。

1）Table scan 拜访形式

从全表扫描 cost 计算公式能够晓得 Table scan 的 Cost 分为 IO 的 Cost 跟 CPU Cost 两个局部之和，大抵的公式为：IO-cost:#pages in table IO_BLOCK_READ_COST + CPU cost:#records ROW_EVALUATE_COST，其中 IO-cost 是通过 table_scan_cost 来计算的。

这里有两个要害的变量 records 跟 pages in table，别离示意这个表有多少行跟多少 page，这两个变量的值是怎么来的呢？如果应用的是 innodb，那么前者来自于 ha_innobase::info_low（ha_innobase::info），后者来自于 ha_innobase::scan_time()（而这两个函数都是 SQL 层 handler.h 定义的虚函数，不同的存储引擎别离实现之），晓得了这两个变量的值，就晓得具体的 Cost 值了。

2）Index scan 拜访形式

笼罩索引的扫描，从 index_scan_cost 得悉 Index scan 的 Cost 为：O-cost:#(records/keys_per_block) IO_BLOCK_READ_COST+ CPU cost:#records ROW_EVALUATE_COST，因为 IO_BLOCK_READ_COST 跟 ROW_EVALUATE_COST 都是常量，所以须要关注是 keys_per_block（keys_per_block 的计算跟 block_size 等无关）跟 records 这两个变量，也就是说如果晓得了这两个变量的值，就晓得了具体的 Cost 值了。

3）range access 拜访形式

从 multi_range_read_info_const 得悉 range access 的 Cost 为：IO-cost:#records_in_range IO_BLOCK_READ_COST + CPU cost:2#records_in_range* ROW_EVALUATE_COST，因为 IO_BLOCK_READ_COST 跟 ROW_EVALUATE_COST 都是常量，所以只须要晓得 records_in_range 这个变量的值，就能计算其 Cost 了。

4）ref 拜访形式

从 find_best_ref 得悉 ref 拜访形式的 Cost 大抵为：IO-cost:#prefix_rowcount IO_BLOCK_READ_COST + CPU cost:#prefix_rowcount cur_fanout ROW_EVALUATE_COST，须要实现 cur_fanout 这个变量，而这个变量跟 info 中的 rec_per_key（rec_per_key 的意思是，比方对于 select from test_db.table1 where c2=3，table1 中有多少行数据满足 c2=3）有关系，所以实现 rec_per_key 算法后就能够晓得其 Cost 值了。

4.2.2 What_If 索引倡议策略

如何把下面的 Cost 计算形式的实践剖析利用到索引优化倡议中？假如表 table1 除了主键外没有任何索引，索引优化倡议工具最初怎么能给用户举荐出索引 idx_2 呢？对于这个问题能够用到一种称之为 What_If 的策略（在微软的 AutoAdmin“what-if”index analysis utility 中提出），思路就是假如索引 idx_2 存在并且能计算出在索引 idx_2 存在的状况下，此执行打算的 Cost 值，如果发现其 Cost 只有比以后存在的其余执行打算的 Cost 值更小，就举荐这个索引。

然而 MySQL 计算 Cost 的时候，要求索引是实在存在的，为了给出索引优化倡议在生产环境理论去创立各种索引是不可能的。解决的策略就是在非生产环境的数据库实例上创立一个空表，并且增加所有可能被用到的索引（只是一个元数据的 create index 操作而不去真正的向表内增加数据），最初让优化器去本人抉择最佳的索引来生成执行打算，这就是索引优化倡议总体思路。

那么问题来了，在非生产环境下创立的索引只是一个元数据，不蕴含任何数据，如果就这样让查问优化器去计算 Cost 必定是行不通。次要起因是不足下面的剖析的各个拜访形式的要害变量值，所以最要害的如何获取到那些影响 cost 计算的要害的变量信息。

从下面的“实现思路”剖析可知，在计算 Table scan、Index scan、range access 或者 ref 的 cost 的时候，次要是从 scan_time()、records_in_range()、info()这些在 innodb 存储引擎里实现的办法获取相干统计信息，来计算 Cost 大小。而索引没有实在的数据，所以必须要批改存储引擎里 scan_time()、records_in_range()、info()这些跟计算 Cost 相干的办法的实现代码，让其在索引数据不存在的状况下，也能比拟实在地获取到跟计算 Cost 值相干的统计数据。

比方，下面提到的 records、keys_per_block、pages in table、records_in_range、cur_fanout 等这些变量，就须要本人去计算；比方 records 这个变量，发现其是在 Innodb 中 info()的实现，通过 n_rows（records）赋值给 records，所以只须要在 info()中从生产环境的表里获取到实在的行数，赋值给 records 就能够了，其实就是一个元数据的查问而已。

再看一下 ref 类型拜访形式的 Cost 计算过程中用到的 cur_fanout 变量，而这个值是来自 info()中 innodb_rec_per_key 的值，它示意有多少个值满足等值条件，只须要在 info()中实现 innodb_rec_per_key 的计算就能够了。

那么 innodb_rec_per_key 又怎么计算呢？咱们能够对表进行了采样（采样的计划能够参考这篇《Random Sampling for Histogram Construction: How Much is Enough?》论文），采样后的数据对某列统计有多少个不同的值，之后应用采集到的总行数 / 不同值的个数来获取到 innodb_rec_per_key 值。

这里还有一个十分重要的问题，下面的计划是须要批改对应存储引擎的 scan_time()、records_in_range()、info()这些要害的办法，然而 innodb 存储引擎的代码太简单了，批改的难度比拟大，一个比拟好的计划是去批改 federated 存储引擎里的 scan_time()、records_in_range()、info()这些办法，去获取下面不同拜访形式中须要用到的要害变量，因为其实现代码比较简单。

4.2.3 索引倡议整体流程图

把之前的步骤整顿后的索引优化倡议的流程图如下：

1. 从待优化的 SQL 的要害地位，比方 Where、On、Ordr By 等地位提取关键字段信息，利用提取到的字段信息生成候选索引；
2. 通过对波及到的表的数据进行采样来获取查问优化器计算 Cost 时须要用到的统计信息，之后创立不蕴含任何数据的索引；
3. 批改存储引擎的代码从新实现的 scan_time()、records_in_range()、info()这些函数，使得查问优化器能利用数据采样后计算出来的统计信息来比拟实在的获取 Cost 值；
4. 抉择一个 Cost 代价最小的执行打算，而此执行打算背地的索引就是优化倡议工具举荐的索引，整体思路就是这样。

4.2.4 验证与跟踪

通过全量 SQL 等零碎获取待优化的 SQL 语句后，输出索引优化倡议零碎，在给实在的生产环境增加这些索引之前，须要在非生产环境进行验证，看一下增加了相干的索引后性能是否真的有晋升。如果有晋升，那么把索引在生产环境增加后（比方通过开源的改表工具 Ghost 等），须要通过跟踪被影响 SQL 的性能，来查看其执行工夫性能是否真的有所提高。如果有性能有回退，须要及时进行告警。

4.2.5 产品展现

上面的三个图别离展现了提供给用户的索引倡议，以及索引增加前后的执行工夫成果数据比照图。

4.3 基于 Workload 的索引优化倡议

具备了单 SQL 的索引优化倡议能力后，那么在无限的存储空间的限度下（比方某个实例最多只能增加 100G 空间的索引），如果想给某个 DB 或者实例增加索引，增加哪些索引能力使整个数据库的性能晋升达到最大化呢？思路次要是参考了微软在基于 Workload 索引优化倡议方面的一序列相干工作。

4.3.1 总体架构

基于 Workload 的索引优化总体架构如下，总共有 6 大步骤组成，每个步骤对应着一个外围组件。

1）COLUMN GROUP RESTRICTION 组件

如果一个 workload（典型业务周期内蕴含的所有 SQL 总和称之为 workload）蕴含大量的 SQL 模版，那么潜在的索引组合的数量是极其宏大的，从中挑选出让零碎性能晋升最大的索引组合是一个十分艰难并且耗时的过程。所以，咱们须要一种称为列组合限度 (COLUMN GROUP RESTRICTION) 的技术，它的目标是打消大量可能组成索引的列组，因为这些列组最多只能对最终举荐的品质产生很小的边际影响（就是说就算举荐了索引，最初对数据库整体的性能的晋升很无限，比方某个 SQL 只执行了一次，那么给之对应的表加索引就没意义了），所以本步骤的输入是 workload 的一组“乏味”的、对整体性能晋升可能有最大晋升的列组。

那么，怎么样选出这个“乏味”列组呢？咱们须要定义一个叫 CG-Cost (g) 的函数，其值为援用列组合 g 的 workload 中所有查问 SQL 的 Cost 的某个分数值，查问 SQL 的 Cost 能够通过优化器预计的老本（MySQL 中 explain 给出的 Cost）来获取。如果 CG-Cost(g) ≥ f，列组 g 须要保留下来（其中的 0 ≤ f ≤ 1 是预先确定的阈值），否则能够失落这个列组 g。看一个具体的例子：思考上面的表 T (A,B,C,D) 的 workload 由“Q1，… Q10”等 10 个 SQL 组成。如果查问援用该列，则上述矩阵中的单元格蕴含 1，否则蕴含 0。为了简略起见，假如所有查问的老本均为 1 个单位。

假如指定的阈值 f = 0.2，那么工作负载的乏味列组是 {A}、{B}、{C}、{A,B}、{A,C}、{B,C} 和 {A,B,C}，以及各组的 CG-Cost(g) 为 1.0、0.3、0.9、0.3、0.9、0.2、0.2，而 D 相干的列因为 CG-Cost (g) 值小于 0.2 而不被思考，这样就能够在宏大的列组合的状况下排除掉大部分列组合，放慢整个索引抉择的迭代优化的过程。

2）Candidate index selection 组件

单列索引（假如索引）的建设，如果思考 workload 的每一个可能的索引集，那么就会有太多的 what_if 索引须要思考，这样整体的优化过程会十分迟缓。

一个比拟好的思路是为每个 Query 独立确定最佳的索引集，并将属于这些最佳的索引集中的一个或多个索引视为候选索引集。该算法背地的直觉是，如果对于 workload 中的单个查问，该索引不是最佳的索引集的一部分，也不可能是整个 workload 的最佳的索引集的一部分。

这个算法的挑战在于如何为 workload 中的每个查问 Q 确定最佳的索引集呢？其实针对单个查问来确定最佳的索引集这个问题与依据整体的基于 workload 来抉择最佳的索引集这个问题，两者没有实质的不同。所以能够设计一个非凡的 workload，而这个 workload 只蕴含一个查问，之后能够通过下面曾经实现的索引优化倡议工具自身，来为单个 Query 获取最佳的索引集。

单查问获取最佳的索引集，如何为单个 SQL（SQL 模版）获取最佳的索引集呢？一个办法是单个 SQL 模版进行解析，在 SQL 要害的地位比方 where、on、order by、group by、select 等的取出要害的列先创立单个列作为候选索引列，再联合上面的从“Multi-column Index Generation 组件”那节创立进去的多列候选索引，让查问优化器本人去抉择最好的索引。同时这个步骤依附优化器选出来的索引，作为下一轮算法（索引合并）的输出。

3）Index merging 组件

下面的“CANDIDATE SELECTION”组建选出来的索引是专门针对单 SQL 优化的索引集，然而对于整体 workload 来说，这个办法会导致较大的存储和索引保护老本。比方，针对单 SQL 选出的索引都是最佳的，然而这些索引加起来后的存储大小曾经超出了容许的范畴，须要进行一些索引的合并。

如果两个索引的列存在局部重叠，则可能能够合并，比方包含关系的索引 [a,b,c] 跟[a,b] 以及有局部重叠的索引[a,b,c] 跟[c,d}] 的合并，当然[a,b] 跟[c,d}] 是不能合并的。索引合并的次要思维就是采纳现有的一组索引生成一组具备更低存储和保护开销的新索引，同时索引被合并的状况下，SQL 查问的性能能放弃不变或者影响比拟小，索引合并的架构如下：

4）Configuration Enumeration 组件

如果基于 workload 的索引集有 n 个候选索引，然而因为存储大小限度，要求 workload 工具最多只能抉择 k 个索引（个别是受到了存储大小跟 Cost 大小的限度），那么应该如何抉择这个 k 个索引呢？一般来说，原始的暴力枚举算法将枚举大小为 k 或更小的候选索引的所有子集，并抉择总成本最低的一个索引集，这个枚举算法的确保障了最优解。

然而，这在工程上不切实际的。因为对于 n =40 和 k =10 的状况，枚举的索引集的数量切实太宏大，无奈进行详尽的枚举，所以须要一个性能更好的算法的“贪婪算法”，其思维是假如有 n 个 SQL（个别是施行的时候，是 n 个 SQL 模版来代替 n 个 SQL 实例），整个 workload W 示意为 {Q1, .., Qn}。

计划是利用暴力跟贪婪算法的组合，运行 Greedy(m, k)算法枚举索引汇合（m 为通过暴力算法抉择的 m 个最佳索引，k 为抉择的索引的总大小），抉择一组索引，直到选出所需数量的索引或总 Cost 无奈进一步升高，该算法抉择大小为 m（其中 m <= k）的最佳索引集作为“种子”，而后以贪婪算法扩大这个种子，直到抉择了所有 k 个索引，摸索算法就是从所有的候选索引中抉择一个索引，使得加了这个索引后，整体 SQL 的性能晋升成果最大化，尽管从全局角度看未必是晋升最大化的。

5）Multi-column Index Generation 组件

多列索引的抉择有一个比拟大的挑战，就是候选索引切实太多，比方对于表中给定的一组 k 列，最多可能有 k! 多列索引，思考所有排列组合将会显著减少 Configuration（索引）空间，所以须要缩小列组合的搜寻空间。采纳迭代办法来思考多列索引的宽度（列的个数）。在第一次迭代中，只思考单列索引，基于在第一次迭代中抉择的单列索引，在往后的迭代中抉择一组可承受的双列索引。这组“两列索引”与单列索引一起成为第二次迭代的输出。

那么，具体是如何在单列的根底上抉择其余的列组成多列索引呢？这里有多种算法，先应用符号 M (a, b) 来示意列 a 和 b 上的双列索引，其中 a 是双列索引的前导列。多列索引抉择个别有 MC_LEAD、MC_ALL、MC_BASIC 三种算法，这几种算法在实现的时候个别会采纳 MC_LEAD 算法，因为被证实成果最好。

• MC_LEAD：从 CANDIDATE SELECTION 被优化器抉择的索引，再加上某个“indexable column”列，这个“indexable column”列对应的索引不肯定在第一轮迭代中被抉择；
• MC_ALL：从 CANDIDATE SELECTION 被优化器抉择的索引，再加上某个“indexable column”列，这个“indexable column”列对应的索引肯定在第一轮迭代中被抉择，这种条件比拟刻薄，业界相干的测试表明成果明确不如 MC_LEAD 跟 MC_BASIC，尽管多列索引的搜寻空间显著缩小，晋升了总体性能；
• MC_BASIC：则是把单列跟多列索引混在一个迭代周期里建设，而不是先依据单列倡议一个索引，而后再通过迭代的形式在单列的根底上逐步减少索引的宽度。

6）Final Indexes 组件

通过几轮迭代，当同时满足 Cost 跟 Storage Size 的要求后退出迭代优化过程，输入一组最优的索引优化倡议汇合。

4.4 SQL 治理

具备了 SQL 索引优化倡议的能力后，就能够对有性能问题的 SQL 进行三个阶段的治理，1）SQL 公布生产环境前，起到防患于未然、把问题扼杀在摇篮中的作用；2）SQL 生产环境执行过程中，起到实时监控发现正在执行的问题 SQL，疾速止损，比方误删索引的作用；3）SQL 生产环境执行过后，对于执行过的 SQL，基于整体的 Workload 优化策略，通过离线的形式进行批量的危险 SQL 治理。如下图所示：

4.4.1 危险 SQL 审核（事先）

此阶段是程序公布前对潜在的危险 SQL 进行卡点，在公司的 CI/CD 平台集成流水线里增设 SQL 审核卡控点，尽量避免危险 SQL 被带到生产环境引发故障，起防患于未然的作用。

具体来说，指定了一序列的规定，发现危险 SQL 后能够暂停公布，并且给出相应的索引优化倡议来优化 SQL。

4.4.2 产品展现

依据审核规定，展现审核后果与倡议，包含危险提醒跟 SQL 索引优化倡议等内容，审核的规定包含是否全表扫描、扫描行数过多等内容。

4.4.3 性能问题 SQL 实时发现（事中）

此阶段是危险 SQL 的实时发现性能，次要针对在 SQL 运行过程中因为表构造的更改（如索引误删除）、事先审计阶段遗漏掉的性能问题 SQL 等场景，实时地发现有性能问题的 SQL，并且给出优化倡议。对于实时的性能问题 SQL 发现应用了两种发现策略，一种是基于规定的发现策略，另一种通过数据建模的形式来发现策略。

基于规定的危险发现策略，其中的规定包含单位工夫内的慢查问数量、SQL 执行工夫、扫描行数等内容。

基于数据建模的危险发现策略，是通过全量 SQL 获取 SQL 的历史执行时长来建模，再通过 Process List 等数据源，把以后正在执行 SQL 的执行工夫输出模型，从执行工夫的角度来判断是否有异样。

4.4.4 批量 SQL 治理（预先）

此阶段是通过对 SQL 执行的历史记录进行批量剖析，从全局的角度、利用 workload 索引优化倡议的思维提供最佳 SQL 索引优化倡议，批量推送优化倡议给用户进行危险 SQL 的治理。

4.4.5 产品展现

对于批量的 SQL 优化倡议，这里提供了一键审批索引增加的性能，能够更快的让被倡议的索引增加到生产环境上。

5 本文作者

裕锋，来自美团根底研发平台 - 根底技术部，负责美团数据库自治平台的相干工作。

6 参考

• https://github.com/shenyufengdb/sql
• https://github.com/percona/percona-server/blob/release-5.7.41-44
• An Efficient, Cost-Driven Index Selection Tool for Microsoft SQL Server
• plan-stitch-harnessing-the-best-of-many-plans-2
• Random Sampling for Histogram Construction: How Much is Enough?
• AutoAdmin“what-if”index analysis utility
• What is a Self-Driving Database Management System?
• https://www.microsoft.com/en-us/research/publication/self-tuning-database-systems-a-decade-of-progress/
• Automatic Database Management System Tuning Through Large-scale Machine Learning
• Query-based Workload Forecasting for Self-Driving Database Management Systems
• The TSA Method
• https://blog.langchain.dev/langchain-chat/
• https://github.com/hwchase17/langchain
• REAC T: SYNERGIZING REASONING AND ACTING IN LANGUAGE MODELS
• Evaluating the Text-to-SQL Capabilities of Large Language Models
• SQL-PALM: IMPROVED LARGE LANGUAGE MODEL ADAPTATION FOR TEXT-TO-SQL

浏览更多

| 在美团公众号菜单栏对话框回复【2022 年货】、【2021 年货】、【2020 年货】、【2019 年货】、【2018 年货】、【2017 年货】等关键词，可查看美团技术团队历年技术文章合集。

| 本文系美团技术团队出品，著作权归属美团。欢送出于分享和交换等非商业目标转载或应用本文内容，敬请注明“内容转载自美团技术团队”。本文未经许可，不得进行商业性转载或者应用。任何商用行为，请发送邮件至 tech@meituan.com 申请受权。