共计 5330 个字符，预计需要花费 14 分钟才能阅读完成。
优化器在数据库中始终位于至关重要的地位，性能调优也经常须要围绕优化器来进行。作为数据库厂商，咱们心愿在各类简单的业务场景中，TiDB 都可能给出比拟现实的执行打算，因而在优化器和执行器上做了十分多的工作和致力，然而选错执行打算或者索引的状况依然是日常中最为常见的一个问题。
优化器无关的问题能够简略归结为两种：
- 统计信息精确的状况下给出了谬误的执行打算。
- 另一类则是在统计信息过期的状况下给错了执行打算。
选错索引是其中比拟常见的一种状况，用户心愿增加索引来减速查问速度，某些状况下，优化器可能会走到全表扫的物理执行打算或者选错索引使得理论执行成果进化成全表扫的状况。
针对上述情况，咱们须要从更宏观的层面来度量优化器的执行打算和索引抉择的性能，评估在优化器上做的改良工作是否切实起到冀望的成果。
为什么咱们要开发 Horoscope?
为了测量优化器和执行器，从去年开始咱们构建了 daily benchmark 平台 perf.pingcap.com，笼罩常见的几种简单查问的测试场景，蕴含 TPC-H、TPC-DS、Star Schema Benchmark 等，跟踪每天开发分支上这些查问的执行速度状况。
通过 daily benchmark，咱们观测和定位到了若干次性能晋升以及性能回退的状况。有些晋升或者回退是优化器组件上的优化导致的，有些则是 TiDB 其余组件，或者存储层引发的。
尽管 daily benchmark 可能观测到性能改良或者回退，然而对于以下几个问题它却大刀阔斧：
- 以后抉择的执行打算是否最优？选择率预计是否精确？
- 是否抉择到了正确的索引？
- 现有的启发算法是否应答统计信息肯定水平的过期？
因而，咱们须要另外一种更零碎的测试工具，用于优化器的测量。
Horoscope 是如何做的?
要测量优化器，咱们须要：
- 定义优化器的性能指标
- 遍历执行打算空间
- 数据集以及查问生成
定义优化器的性能指标
这里咱们参考“OptMark: A Toolkit for Benchmarking Query Optimizers”给出的办法来度量优化器有效性。简略地讲某个查问的有效性指标，是指在可遍历的执行打算空间中，优化器选出的默认执行打算的执行工夫比其余的执行打算的执行工夫更快的比例。
例如 100% 能够解释为默认执行打算的执行工夫比其余执行打算的执行工夫都更快，50% 解释为有一半的执行打算要比默认执行打算更快。
遍历执行打算空间
因为须要一种形式可能让 TiDB 依照咱们所指定的物理执行打算来理论执行查问，为此咱们在 TiDB 中增加了 nth_plan(n) 这个 SQL hint。
当查问语句提交到 TiDB 后，TiDB 会为搜寻空间中的每个执行打算绑定一个固定的序号，通过这个序号咱们就能指定优化器去抉择哪一个执行打算。
nth_plan 的序号从 1 开始递增，当其超出优化器对该条查问的搜寻空间时，查问返回会产生一个 warning 来提醒以后曾经实现了搜寻空间上的遍历。
TiDB(root@127.0.0.1:test) > explain select /*+ nth_plan(1) */ * from t where a = 1 and b > 0 and b < 10;
+-------------------------+----------+-----------+---------------+----------------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+----------------------------------------------------+
| TableReader_7 | 0.25 | root | | data:Selection_6 |
| └─Selection_6 | 0.25 | cop[tikv] | | eq(hehe.t.a, 1), gt(hehe.t.b, 0), lt(hehe.t.b, 10) |
| └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+----------------------------------------------------+
3 rows in set (0.00 sec)
TiDB(root@127.0.0.1:test) > explain select /*+ nth_plan(2) */ * from t where a = 1 and b > 0 and b < 10;
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
| IndexLookUp_11 | 0.25 | root | | |
| ├─IndexRangeScan_8(Build) | 10.00 | cop[tikv] | table:t, index:idx_a(a) | range:[1,1], keep order:false, stats:pseudo |
| └─Selection_10(Probe) | 0.25 | cop[tikv] | | gt(hehe.t.b, 0), lt(hehe.t.b, 10) |
| └─TableRowIDScan_9 | 10.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+-------------------------+---------------------------------------------+
4 rows in set (0.00 sec)数据集以及查问生成
互联网上有很多凋谢的数据集，也有一些 benchmark 提供了 dbgen 工具用来随机结构数据集，咱们比拟偏向于抉择实在数据集，因而咱们抉择了 IMDB 数据集来进行测试。
有了数据集，咱们须要在其上结构一些查问。为了测试索引抉择问题，参考 Manuel Rigger 的“Testing Database Engines via Pivoted Query Synthesis”论文中的思路，Horoscope 会在某些表中随机抉择一行数据作为 pivot row 去构建查问，使得查问返回的后果会蕴含这些抉择的行。通过这种形式，咱们能保障生成的查问是更具意义。
例如针对索引抉择问题，查问结构的流程如下所示，通过在有索引笼罩的列上结构条件来测试是否选对了索引。
例如会生成如下的查问：
开始测量
咱们事后导入了一份 IMDB 数据集到 imdb 数据库中，能够通过如下命令应用 Join Order Benchmark 的查问度量有效性指标。
$ git clone https://github.com/chaos-mesh/horoscope.git
$ cd horoscope && make
$ ./bin/horo --round 4 -d root:@tcp(localhost:4000)/imdb?charset=utf8 bench -p -w benchmark/job通过漫长的期待，在测量完结时 Horoscope 会出入一份测试报告：
ID 列标识查问的名称，#PLAN SPACE 是这条查问以后 TiDB 的搜寻空间，DEFAULT EXECUTION TIME 记录了默认执行打算的执行工夫（通过中值以及上下界偏差比例给出），BEST PLAN EXECUTION TIME 给出最优的执行打算的执行工夫，EFFECTIVENESS 算出该条查问优化器的有效性，BETTER OPTIMAL PLANS 给出更优的执行打算的 ID 以及对应执行工夫和默认执行打算执行工夫的占比。
咱们应用 Horoscope 测量了不同数量级的 TPC-H，并且 IMDB 数据集上针对索引抉择生成了一些查问来测试。咱们也在 Github 上创立了一个我的项目来跟踪这些问题的停顿：https://github.com/orgs/pingcap/projects/29
相比于 TPC-H，Horoscope 在 IMDB 的数据集和查问上发现了更多更优的执行打算，但因为 IMDB 数据是动态的，当想验证统计信息过期场景下优化器的状况时比拟艰难。
为此 Horoscope 提供了将数据依照某个字段进行切分而后导出的性能，通过分批次插入数据，提供了数据更新状况下的优化器测试场景。
数据切片和按切片更新数据
实在数据集上的数据分布往往具备歪斜的特色，而这种歪斜的性质对于优化器也更有挑战。
以 IMDB 为例，数据在 title.produciton_year 上产生了歪斜，越靠后的年份，所关联的数据行数越多。咱们通过对数据集在 title.prodution_year 上将数据集切分成一块块不均等的切片，再进行分批导入，能够模仿数据批改所引发的统计信息过期对于优化器的影响。
切分的过程如下：
- 将各个表之间的关系通过主外键进行关联，结构出一张无向图；
- 抉择某个表上的字段，查问出其上不同的值；
- 以这个字段的值作为线索，结构查问语句，在无向图上串联不同表上能关联到的数据，导出到切片文件中；
- 标记串联过的数据，后续切片疏忽已标记过的数据。
在 IMDB 上，咱们抉择 title.produciton_year 进行数据切分，切分后每个切片文件的大小如下图所示。
约有一半的数据集中在最初 20 份切片中，越往后导入数据的批改行增速越快，统计信息的过期速度也愈快。
咱们设计了 2 个对照试验，试验开始之前事后导入切片 0 到切片 124 的数据，并从切片 125 开始，每导入一个切片，测量一轮各查问的有效性指标。
在第一组试验中咱们敞开了 auto analyze 和 feedback，第二组敞开了 auto analyze 但会关上 feedback。而后让 Horoscope 随机生成一批简略查问，在失去数据后咱们别离绘制了有效性指标的比例曲线以及散点图。
曲线上的点示意有效性指标大于横坐标数值的查问的比例。从数据上看，当关上 feedback 时，有 50.77% 查问的有效性指标超过了 80%，即对于一半以上的查问优化器抉择到了较优的执行打算。而当敞开 feedback 时，这个比例只有 38.70%。这和咱们通常所认为的 feedback 可能肯定水平抵制统计信息过期相符。
另外从散点图上看会发现关上 feedback 也有可能会让优化器抉择到更差的执行打算。例如上面的这条 SQL，feedback 机制反而使优化器抉择到了更差的执行打算，这些能够作为 bad case 来具体分析。
SELECT *
FROM title
WHERE (title.id IS NOT NULL
AND title.title!="(#1.69)"
AND title.imdb_index IS NULL
AND title.kind_id<8
AND title.production_year!=1974
AND title.imdb_id IS NULL
AND title.phonetic_code IS NULL
AND title.episode_of_id>184590
AND title.season_nr IS NULL
AND title.episode_nr IS NULL
AND title.series_years IS NULL
AND title.md5sum<="7cf95ddbd379fdb3e530e0721ff61494")
LIMIT 100
后记
Horoscope 还能够做更多的事件，例如当版本升级时，能够用 Horoscope 来测试执行打算会不会变动，如果变动了，是否产生了回退。
用户线上的数据通常非常敏感，咱们外部积攒了比拟多的有统计信息和 schema 但无理论数据的用例集，通过 Horoscope 咱们当初心愿可能将这些用例集利用起来，裁减优化器测试用例，来帮忙优化器的开发者们决策一些优化策略是否要合并到下一版本中。
此外，Horoscope 也提供了一种测试优化器正确性的路径。咱们正在打算让 Horoscope 生成更简单的查问，通过比对每个物理执行打算的后果来验证优化器实现的正确性。
优化器的工作是个长期且难度十分大的事件，优化器的测试也是如此，如果您有更多更好的对于优化器或者其余组件的优化以及测试的办法或者思路，欢送在 TiDB 社区中和咱们进行交换。
