TiDB 实战优化之 SQL 常见问题与优化案例

2022 年 7 月 11 日
本文字数：5500 字
阅读完需：约 18 分钟

原文来源：https://tidb.net/blog/90a37e62

我今天分享的内容高度融合了很多跟 TiDB 相关内容。其实我在积极地助力 TiDB 在贝壳找房落地，因为我发现一个特点，如果我说开源解决方案很多，比如四层建模，都是离线 T+1，但很多需求是实时的。在这种情况下如果用服务链条、Spark、 Doris 等新技术，我会发现建模很耗精力，还是需要做一套解决方案把所有数据放在一起，并且应用在场景下，做出真正的数据挖掘层次。我现在所处的场景，不是 TiDB 的主攻场景，比如替换 MySQL 集群等，而因为我既是数据的使用方，也是数据的建设方，同时还是数据运维方，都是我的团队负责。这时候我就发现一个问题，既然大数据需要很多解决方案，怎么 All in？All in 哪个产品？目前我们的解决方式有 Flink、Spark、Kylin，TiDB 更向前——用在接入层，解决一切应用的场景。

这是 TiDB SQL 的解析过程。不论是什么产品，一旦到 SQL（也就是到底层）你会发现，所有的产品都非常的相似，都会经历解析 - 语法解析 - 语义解析 - 执行计划 - 逻辑计划。那么 TiDB 基于什么呢？基于很多 statistics 和 organizer 做优化器迭代。其实我个人认为，这个时代站在了很多巨人的肩膀上，可以少走弯路。Oracle 早年前是 RBO，基于规则必须走索引，否则就报错。后来发现，走索引不是最对的，才有优化器。TiDB SQL 解析过程当中，在生成物理执行计划时候，会收集所有统计信息反馈，并反补下一次解析，形成逐渐迭代的过程。所以我强烈推荐大家在实际的生产过程中，把 SQL 解析的每个步骤打成系列。

执行计划每一步怎么走，如何优化，优化的过程怎样，主要是看 Prior Organizer。整个优化器怎么去做，SQL 返回来之后如何解析、验证、调度，这是在 TiDB 分布式环节必须要有的。给大家一个结论参考：所有的开源圈当中，大家都在玩命迭代开发，实现客户的吐槽，自己变得更健壮。那么我其实强烈建议大家可以向一些优秀的产品靠一下，看一看 Oracle 怎么走。Oracle 其实走的是很前沿的，它完全是运营商级别的，大国企不允许出错。Oracle 有个特别厉害的地方，SQL 全部量化到统计信息、模型和反馈，所有参数级的调度，都到底层 Trace，包 Dtrace 和跟踪。TiDB 也是基于统计信息反馈迭代，这是一样的。

我一次性罗列了一些 SQL 常见问题和解决办法。比较常见的有 order by + limit，如果数据过多不可能全部打出来，我一定会做排序或者加 limit 模型。这里面有很多问题，比如 SQL 语句不加 limit 时候走索引，加 limit 的时候走 table。这个当然是一些 bugs，不应该从下面执行。TiDB 好就好在它不停迭代，row count 不准确这个问题已经在 3.0.0 rc1 中修复了。在这里对开源社区有个建议，当你发现一个问题很痛苦，困扰你很久的时，不要闷着，直接在社区里问有没有新版本可以修复，如果能直接打 fix 就好了。我个人认为产品没有完美的，但是解决方案一定有一个最适合你的，也给原厂一些时间迭代。

SQL 较简单时执行计划不准确，选错索引、不走索引，或者执行计划不稳定非常令人头疼，应该以预防为主。这种问题出现的原因可能有：统计信息过旧、query feedback 调整统计信息幅度过大（2.1.5 已修复）、直方图信息不准确、CMSketch 信息不准确。这些都是基于统计信息驱动的执行计划生成，我们应该在预演中多跑跑，在跑稳之后再上核心业务，预防出现峰值现场。

还有 SQL 支持不完善的地方，比如说 where 条件与列数据不一致时存在执行计划不符合预期的地方。这个如果类比一下 Oracle，相当于引式转换，各种结构不一样导致信息对不上。解决这个问题我们试了很多方式，比如说和开发小伙伴沟通，让他们的理论跟我们一致，最后发现其实真正在互联网圈高频迭代的情况下很难落地。最后，我们尝试过一次性 SQL 代码审核总查改写，提交代码之后，我再用机器方式，改你的代码。还有标量子查询执行缓慢；count(*) 的时候可以走索引，但是走的扫描全表；执行结果不稳定，结果出错。

最后总结一下问题，CBO （Cost Based Organizer）模型基于成本，只要基于成本就有统计信息。如果 cost 模型的估算值不准确，比如数据量估算不准确、开销计算不准确、代价估算过高或过低，都可能导致问题。统计信息不准确，包括统计信息过期、pseudo 统计信息、直方图信息、CMSketch、多列关系选择性、query feedback。还有一些功能不完善的问题，比如函数索引、函数下推、逆序索引、multi-index scan。目前 TiDB 有一个非常优秀的优化层，SQL 优化小伙伴在不断迭代，我也希望大家可以和原厂一起迭代。

展开几个焦点，统计信息是基于 CPU 来做的，所以 TiDB 优化器根据统计信息来选择最优的执行计划。我列了几个参数，大家可以关注一下，比如说系统级的统计信息 tidb_auto_analyze_ratio，默认值 0.5，是自动更新的阈值。大家知道，一旦数据量过大，统计信息采集就非常耗资源。但是这些参数适不适你，是需要根据具体的业务场景调试的。比如说你做快递行业的，收件、快递只能晚上去派发，所以高峰期可能是在夜间。还有一些参数，下面都有对应的值，大家可以去参照设计。自动统计信息收集方面，TiDB 是 60 秒，如果 60 秒内这个表没有别的修改，就不采集，这是符合我们以尽量少的代价去对表进行采集的规则的。当然横向对比，这个条件还是有点重的，如果我一分钟内没改表，但下一秒变化极大呢？在这方面 Oracle 还有百分比、热度情况等各种各样的、更细度的数据量化，然后做迭代，判断它到底是不是自动采集。表格如果小于一千行数据也没有意义，它也不会进行大的优化调整。

关于执行计划的调整我有些建议和方法，比如说可以加 hint，试一试 TiDB_SMJ (t1,t2)、TiDB_INTL (t1,t2) - TiDB_INTL (inner table)、TiDB_HJ (t1,t2) 这 3 个参数，join order 也可以用这种方式。你可以选择 use index ()，ignore index ()，这时候加 hint，进行对索引进行干预。这一页列出来的所有参数对于 SQL 优化都有效，但是大家可以猜一下，这个优化的收益百分比多少？改变参数、改变内容，如果这个参数中跟执行计划有关的占 20%，参数带来的只有 10%。所以要做架构侧的优化，做预分区、表级和基于业务调整，收益几乎在 40% 以上。

SQL 优化还有些其他的改进方法，比如说快速增量统计信息 Fast | INREMENTAL ANALYZE，适用于全量统计信息采集比较慢，但希望在高峰期时候不要有干扰的时候；还有获取列之间的数据相关性的时候，业务信息相关性和直方图如果相关性不够的话，提供基于用户可配参数的启发式方式；3.0.0 - beta.1 版本中，join reorder 这个方式也做了一定的算法提速；其他还有诸如 Cascades 修改，包括优化 cost 模型这些，整个产品团队都付出了很多时间和精力。我们在 SQL 里发现问题时候，首先看优化器有没有最新迭代。对于别的小伙伴遇到的问题，我会进行打包，并且提前预防。

其他一些基础概念也有需要大家注意的地方，比如约束。 DBMS 中主键和唯一键都是表中数据的唯一约束，外键约束两个关系的相关性。但在 TiDB 中需要注意：

主键必须在建表时声明，目前版本还不能为已有的表添加、修改或删除主键。这个我不是很能理解，我建个表想后续再添加主键，主键有业务性和非业务性的，这于对架构侧要求很高，非常麻烦。当然也是互联网圈当中有些特定场景就差主键，可以单独设计，但在一些灵活的应用场景中，这个就比较别扭。
TiDB 唯一键可以在新建表时添加，也可以在已有表中进行添加或者删除操作。
外键 TiDB 目前还不支持，要去掉表结构中创建外键的相关语句。给大家讲一个故事，以前我们在进行第三层范式设计的时候，往往采用外键强约束的方式，我们对数据库层集中处理层是一种伤害，因为校验没有在应用层，应用层是比较廉价的，而 DB 层是非常昂贵的。变相来说，前期设计的时候有外键参照，但是在物理部署的时候，把所有外键全部删除，业务侧保证依赖关系。我只能把它理解为这是优化方案做到极致，只可以在业务侧实现。但我们在一些应用场景里对外键是有需求的，所以我也期待着后续 TiDB 能支持外键。

在互联网场景当中，写入热点是非常普遍的，而且最难场景是热点漂移。热点漂移是什么意思？就是此时此刻的热点，过了一段时间后它就不再是热点了，但是我要解决的是此时此刻的热点问题。热点产生的原因一般是因为批量计算的回写，TiKV 是一个按 range 切分的 KV 系统，KV 的 Key 决定了写入位置在哪个 Region，Key 的值取决于两种情况：一个是主键，如果是 int 和 bigint，key 就是主键；第二种情况是 TiDB 为表创建隐藏列，比如 _tidb_rowid，key 是该隐藏列。

那么怎么打散呢？比如说用 SHARD_ROW_ID_BITS 来设置隐藏列 _tide_rowid 分片数量的 bit 位数，默认值为 0，即 2∧0=1 个分片。其实大家解决的方式都大同小异，所以大家更应该经常交流。写热点不管是在 Redis 还是在大数据场景当中都是非常普遍的，解决方式基本就这么几招：或是在业务侧做打散，比如说前置 ID 或 random；或是做一些业务侧的改造，在写入的时候就打散了；或是在技术侧建表时候做一些产品侧打散动作；再不济，就是用参数强改。只要是分布式的计算框架和分布式的存储框架，都是按照 Region 这种方式去打散的。

当然还有 Partitioned Table，也就是分区表技术，我很期待 TiDB 小伙伴们，能从 partition 这个角度把热点打散做得得更好、更稳定一些。Partition 对用户极其友好，我认为这是跟 MySQL 比，很重要的一个亮点。TiDB 既是分布式，又是关系型，DBA 在优化方面更有话语权一些，而且对用户更方便。说到这，我给大家的建议是多多积累别人和自己遇到的问题，以应对线上出现的各种情况。

下面我们来讲一讲锁。对于写代码的人，锁是一定避不开的。我推荐大家，多掌握一些基础能力，包括建模能力、设计能力，才能更好地理解某些基本功能。锁分为乐观锁和悲观锁，两种锁各有利弊，但是大家在平时写代码的过程中还是用悲观锁多一些。为什么呢？因为乐观锁在写表、建表和逻辑关系的时候，在列层面打得是 timestamp。我倾向于在能把业务需求盘点清楚的情况下，尝试用乐观锁，它更简单一些，不会带来加锁开销，对性能有好处。尤其在大规模数据使用场景下，锁出现问题是不可逆的。前期如果用比较高级别锁的话，后续遇到问题就会比较麻烦一些。悲观锁其实对开发框架并不友好，改造逻辑变化相对技术成本、开发成本比较高。乐观锁在事务冲突的情况下，会带来性能下降，事务失败率较高。在代老师广告业务的场景下，我在想是不是可以开 focus，性能飙升几十倍甚至几百倍，但是需要评估好对公司的成本消耗模型到底是多少。比如说消耗模型当中有 1% 消耗，但这个动作对客户好，在客户占了便宜的同时，我的价位得到质的飞跃。当然这个难就难在，如果我们单纯是一个支持部门或运维、优化部门，可能推不动。我建议往前走一步，我自己也是这么做的。我每次做方案的时候，都会强烈把业务方全都约过来聊，我会告诉他们这个方案的优劣势对比，然后我也会有倾向性地告诉他，只要做这个动作会给性能带来非常大地提升。你一定能说服你的业务方，然后采用最佳的架构而非技术手段来提高性能，最后你就能做到，你比业务还懂业务。

总结一下乐观锁对于开发侧的影响，比如说 affected rows 不可信，SQL 返回结果不可信，需要校验事务提交请求的返回值，这是硬伤。需要尽量避免冲突业务场景，通过业务来解决冲突，而不要让数据库不断的重试，可以通过关闭乐观事务的自动重试解决这个问题。一定要捕获数据库提交请求的异常，进行业务重试或处理。只要用乐观锁，都需要跟业务开发团队对接，指标优化本质上都是往前走，别在后面。

处理秒杀场景的最佳实践是将计数器功能转移到缓存中实现。秒杀场景一定是把所有的机器和设备全部扑上去，以保证硬件和性能，但是又不能用太硬的手段，比如悲观锁，否则一定达不到秒杀场景的性能要求。所以在这个场景中，业务需求提给你的时候，就必须做乐观锁的实践。谈优化的时候，你必须清楚，需求方的真实需求是什么，文字比较多大家可以具体看下面的图：

我最后列了一张图，是 benchmark 流程。这个流程大家都很熟，我把它列出来就是希望大家在压测时有完整的测试方案。我建议大家在选型的时候，可以把所有产品都并行测试一下。当真正压测的时候，你一定会考虑到把所有节点、硬件、模型卡都压满，在这个过程中，你需要知道业务特性，拿出最核心的场景，按照 benchmark 方式来对比。当你经历过，或者带领的团队经历过会发现，一次性的 Bug 全出来了。既然是压测，测试场景就是各种极端场景，你会发现选型的产品就是满足不了你的场景，这才叫真正好的测试场景。满足不了场景怎么办？就需要我们做一个 merge DB，或者从优化角度来说，就必须折衷地从架构侧去改，策略就不再依赖产品，而是依赖于自己的能力：建表及业务设计的时候，怎么修改性能指标。当指标达到要求的 80%，这个产品就可用了，并且在实际使用过程当中，你就发现这个产品非常好用，因为错误全部经历过。所以一定要走这个过程，POC 的时候测一下。在测试中把测试目标和测试用例定好，测试用例定的高一些，基准性测试、拓展性测试、产品附加测试出个报告，用这个报告和其他产品横向对比。这个测试，相信 TiDB 原厂也会积极地协助，原厂一定是对自己产品充分有信心的。当然 TiDB 原厂除了充分有信心之外还有初心，如果你真的考虑使用 TiDB，他们一定会帮你搞定。