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

0 前文回顾

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

1 内核可观测性建设

1.1 内核可观测性建设

1.1.1 性能诊断挑战

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

很多SQL性能抖动的问题找不出根因，比方SQL的执行时长莫名其妙的忽然变大，其执行打算良好、扫描跟返回的行数都很少，也没有行锁、MDL锁相干锁阻塞；查看慢查问日志，也没有哪个字段的耗时比拟高，然而SQL的执行时长就是忽然变长，有时候达到几十秒长，而平时往往是几毫秒，各种剖析后找不出起因。
有时候在诊断一些指标异样的根因时，凭借的是不太严格的教训，而不是量化剖析，比方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相干的代码门路进行埋点？通过摸索，这里提供了如下几个形式：

剖析setup_instruments表中蕴含的要害埋点信息，大抵晓得有哪些要害的指标能够埋点，比方wait/IO、Mutex类等，再联合本身的教训来判断哪些指标可能有性能瓶颈的问题。
依据理论疑难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进行埋点。
源码剖析，在内核层面对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分层图看上去很像，那为什么不间接应用而须要自研呢？这里次要从如下几个起因且内核团队都很好的解决了：

performance_schema相干的统计信息是自实例启动后总体的wait工夫的聚合数据，而更须要的是任何时间段、单SQL例子的明细数据，因为诊断的往往是某个工夫点或者时间段的异样SQL问题。
performance_schema中其有不少Bug，除此之外，它只反对OffCPU类型的指标，OnCPU耗时的统计不反对，甚至有些十分重要OffCPU的指标，比方row lock的wait统计信息都不反对。
开启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生命周期的三个阶段，这三个阶段下文会进一步论述。

应用程序“公布前”的性能问题SQL审核（事先）；
SQL执行过程中的实时性能问题SQL发现（事中）；
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 索引倡议整体流程图

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

从待优化的SQL的要害地位，比方Where、On、Ordr By等地位提取关键字段信息，利用提取到的字段信息生成候选索引；
通过对波及到的表的数据进行采样来获取查问优化器计算Cost时须要用到的统计信息，之后创立不蕴含任何数据的索引；
批改存储引擎的代码从新实现的scan_time()、records_in_range()、info()这些函数，使得查问优化器能利用数据采样后计算出来的统计信息来比拟实在的获取Cost值；
抉择一个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申请受权。