TiDB 与众不同的优化器

作者： xuexiaogang 原文来源：https://tidb.net/blog/00e83347

今天这个文章牵涉到我多年前遇到的一个故障，当时数据库 CPU100%，从而引发我对这个场景的介入。分析问题以后发现是 SQL 写法的不同导致优化器执行的天差地别。而每种数据库的优化器又都不一样。今天我们要做的是 MySQL 和 TiDB 的优化器差异对比。首先申明这没有谁好谁坏，客观描述。这里我引用我自己公众号的一篇中的部分（MySQL 部分来说明）。下面是我原文链接。数据库对比系列之一 (qq.com)其中部分图和数据用了原文，属于自己引用自己。当时没有学习 TiDB，现在学习了 TiDB 以后想对比一下。因为 TiDB 是兼容 MySQL 的，那么看看在这方面是借鉴还是自立门户？今天的实验就带大家看看。

首先在 MySQL 建表，有主键和索引。放入数据。

全表数据不用多 5 条就够演示说明。

接下来同样在 TiDB 中建立一个类似的表 a，也是 id 是主键。从这个界面还是可以看出是 TiDB 的，尽管前面还是 MySQL 的提示符。但是就看书写还是有点区别的。我真想建议官方把命令提示符改成 TiDB>

环境准备完毕。我们先看看在 MySQL 中查询主键的执行计划，可以看到下图红框，他用到了主键。由于 a 列有索引，所以用到了 w1 索引。

做过优化的同学应该知道这就是用到了索引，不去查全部的数据，通过索引估算查到数据有一行。

下面看 TiDB 的执行计划。由于直接查主键索引，执行计划也是估算一行。可以看出两个执行计划的风格，排版也不一样。

执行计划的表现形式可以看出来和 MySQL 不一样，但是效果一样。

这是第一步，我们往下看在极值函数下的表现形式。

由于是极值函数，所以优化器判断最优的了。

那么看看 TiDB 的极值函数，他在 TiKV 节点上用了索引全扫描。可以看出了除了 TiDB 本身的 TiKV 架构不一样，他的实现逻辑是不一样的。

好了，前面都铺垫好了。接下来就是重头戏了。

第三步，请看这两句 SQL，在 MySQL 中。就是括号之前一个是 in 一个是 = 的区别。

Select * from w where a in （select min(a) from w）;

Select * from w where a = ****（select min(a) from w）;

这两句的执行计划天差地别。使用 in 的时候子查询全表查询。而 = 的时候子查询极值，父查询用到了索引。这里说一下当年就是因为这个导致了当时的故障。因为在 Oracle 中无论哪种写法执行计划都是一模一样的。而开发同学一般不知道这些细节。认为在 Oracle 中可以这样用，那么在 MySQL 中也一样可以这样用。实际上不是这样的。

下面我们看看 TIDB 的表现。

TiDB 在使用 in 的时候没有像 MySQL 那样子查询使用了全表，而是执行了类似我们做的极值函数的执行计划，然后父查询用了范围查询。（可能是因为是 in 的关系）

在使用 = 的时候，这个子查询的就当错一个类似的常量了。我们把常量代入看看效果。

结论 TiDB 虽然是兼容 MySQL（就连参数都一模一样），但是优化器可以看出来是自己写的，没有说照抄。他的实现方式是自己独立写的。比起有些包一层的国产来说，TiDB 应该是重写了所有的优化器。

做过数据库的都知道数据库的执行快慢取决于优化器的强大与统计信息的准确。优化器是数据库的大脑，自己写优化器是很难的。因为全是数学知识，很多年前听说 MySQL 的优化器有 25 种算法，PG 的优化器有 50 多种算法。而 Oracle 的优化器有 250 多种算法，之所以优化器强大，是有很多白发苍苍的数学教授将数学的精髓注入了优化器中。这部分是数据库与数据库拉开差距的一个主要指标。有些东西可以抄但是如果自己写那么是需要很大的勇气，更加要的是实力。就以我们这个案例来说，数据库要针对 SQL，识别出怎么执行是最优的（由于对数据的建模不同，把握准确度不同，会产生不同的执行计划）。这些都是优化器所有面对的。目前看来 TiDB 虽然不知道有多少算法，但是就这个场景而言和 Oracle MySQL PG 的都不一样。以前说从一个数据库切换到另外一个数据库，不是数据导出导入这么简单。有的时候 SQL 不去改写是不行的。仅仅一个简单的场景就会产生巨大的差别。