TiDB 与众不同的优化器特点

今天这个文章牵涉到我多年前遇到的一个故障，当时数据库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的时候子查询全表查询。而=的时候子查询极值，父查询用到了索引。这里说一下当年就是因为这个导致了当时的故障。因为在***中无论哪种写法执行计划都是一模一样的。而开发同学一般不知道这些细节。认为在***中可以这样用，那么在MySQL中也一样可以这样用。实际上不是这样的。

下面我们看看TIDB的表现。

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

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

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

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