go语言sql优化器,golang优化

组件分享之后端组件——基于Golang的SQL解析器sqlparser

近期正在探索前端、后端、系统端各类常用组件与工具，对其一些常见的组件进行再次整理一下，形成标准化组件专题，后续该专题将包含各类语言中的一些常用组件。欢迎大家进行持续关注。

10年积累的网站建设、成都网站设计经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有福田免费网站建设让你可以放心的选择与我们合作。

本节我们分享一个基于Golang的SQL解析器 sqlparser

使用方式如下：

当不同的SQL错误，将返回特定的错误信息，具体可以查看其官方提供的 README

国内重要的 Go 语言项目：TiDB 3.0 GA，稳定性和性能大幅提升

TiDB 是 PingCAP 自主研发的开源分布式关系型数据库，具备商业级数据库的数据可靠性，可用性，安全性等特性，支持在线弹性水平扩展，兼容 MySQL 协议及生态，创新性实现 OLTP 及 OLAP 融合。

TiDB 3.0 版本显著提升了大规模集群的稳定性，集群支持 150+ 存储节点，300+TB 存储容量长期稳定运行。易用性方面引入大量降低用户运维成本的优化，包括引入 Information_Schema 中的多个实用系统视图、EXPLAIN ANALYZE、SQL Trace 等。在性能方面，特别是 OLTP 性能方面，3.0 比 2.1 也有大幅提升，其中 TPC-C 性能提升约 4.5 倍，Sysbench 性能提升约 1.5 倍，OLAP 方面，TPC-H 50G Q15 因实现 View 可以执行，至此 TPC-H 22 个 Query 均可正常运行。新功能方面增加了窗口函数、视图（实验特性）、分区表、插件系统、悲观锁（实验特性）。

截止本文发稿时 TiDB 已在 500+ 用户的生产环境中长期稳定运行，涵盖金融、保险、制造，互联网， 游戏 等领域，涉及交易、数据中台、 历史 库等多个业务场景。不同业务场景对关系型数据库的诉求可用 “百花齐放”来形容，但对关系数据库最根本的诉求未发生任何变化，如数据可靠性，系统稳定性，可扩展性，安全性，易用性等。请跟随我们的脚步梳理 TiDB 3.0 有什么样的惊喜。

3.0 与 2.1 版本相比，显著提升了大规模集群的稳定性，支持单集群 150+ 存储节点，300+TB 存储容量长期稳定运行，主要的优化点如下：

1. 优化 Raft 副本之间的心跳机制，按照 Region 的活跃程度调整心跳频率，减小冷数据对集群的负担。

2. 热点调度策略支持更多参数配置，采用更高优先级，并提升热点调度的准确性。

3. 优化 PD 调度流程，提供调度限流机制，提升系统稳定性。

4. 新增分布式 GC 功能，提升 GC 的性能，降低大集群 GC 时间，提升系统稳定性。

众所周知，数据库查询计划的稳定性对业务至关重要，TiDB 3.0 版本采用多种优化手段提升查询计划的稳定性，如下：

1. 新增 Fast Analyze 功能，提升收集统计信息的速度，降低集群资源的消耗及对业务的影响。

2. 新增 Incremental Analyze 功能，提升收集单调递增的索引统计信息的速度，降低集群资源的消耗及对业务的影响。

3. 在 CM-Sketch 中新增 TopN 的统计信息，缓解 CM-Sketch 哈希冲突导致估算偏大，提升代价估算的准确性，提升查询计划的稳定性。

4. 引入 Skyline Pruning 框架，利用规则防止查询计划过度依赖统计信息，缓解因统计信息滞后导致选择的查询计划不是最优的情况，提升查询计划的稳定性。

5. 新增 SQL Plan Management 功能，支持在查询计划不准确时手动绑定查询计划，提升查询计划的稳定性。

1. OLTP

3.0 与 2.1 版本相比 Sysbench 的 Point Select，Update Index，Update Non-Index 均提升约 1.5 倍，TPC-C 性能提升约 4.5 倍。主要的优化点如下：

1. TiDB 持续优化 SQL 执行器，包括：优化 NOT EXISTS 子查询转化为 Anti Semi Join，优化多表 Join 时 Join 顺序选择等。

2. 优化 Index Join 逻辑，扩大 Index Join 算子的适用场景并提升代价估算的准确性。

3. TiKV 批量接收和发送消息功能，提升写入密集的场景的 TPS 约 7%，读密集的场景提升约 30%。

4. TiKV 优化内存管理，减少 Iterator Key Bound Option 的内存分配和拷贝，多个 Column Families 共享 block cache 提升 cache 命中率等手段大幅提升性能。

5. 引入 Titan 存储引擎插件，提升 Value 值超过 1KB 时性能，缓解 RocksDB 写放大问题，减少磁盘 IO 的占用。

6. TiKV 新增多线程 Raftstore 和 Apply 功能，提升单节点内可扩展性，进而提升单节点内并发处理能力和资源利用率，降低延时，大幅提升集群写入能力。

TiDB Lightning 性能与 2019 年年初相比提升 3 倍，从 100GB/h 提升到 300GB/h，即 28MB/s 提升到 85MB/s，优化点，如下：

1. 提升 SQL 转化成 KV Pairs 的性能，减少不必要的开销。

2. 提升单表导入性能，单表支持批量导入。

3. 提升 TiKV-Importer 导入数据性能，支持将数据和索引分别导入。

4. TiKV-Importer 支持上传 SST 文件限速功能。

RBAC（Role-Based Access Control，基于角色的权限访问控制） 是商业系统中最常见的权限管理技术之一，通过 RBAC 思想可以构建最简单“用户-角色-权限”的访问权限控制模型。RBAC 中用户与角色关联，权限与角色关联，角色与权限之间一般是多对多的关系，用户通过成为什么样的角色获取该角色所拥有的权限，达到简化权限管理的目的，通过此版本的迭代 RBAC 功能开发完成。

IP 白名单功能（企业版特性） ：TiDB 提供基于 IP 白名单实现网络安全访问控制，用户可根据实际情况配置相关的访问策略。

Audit log 功能（企业版特性） ：Audit log 记录用户对数据库所执行的操作，通过记录 Audit log 用户可以对数据库进行故障分析，行为分析，安全审计等，帮助用户获取数据执行情况。

加密存储（企业版特性） ：TiDB 利用 RocksDB 自身加密功能，实现加密存储的功能，保证所有写入到磁盘的数据都经过加密，降低数据泄露的风险。

完善权限语句的权限检查 ，新增 ANALYZE，USE，SET GLOBAL，SHOW PROCESSLIST 语句权限检查。

1. 新增 SQL 方式查询慢查询，丰富 TiDB 慢查询日志内容，如：Coprocessor 任务数，平均/最长/90% 执行/等待时间，执行/等待时间最长的 TiKV 地址，简化慢查询定位工作，提高排查慢查询问题效率，提升产品易用性。

2. 新增系统配置项合法性检查，优化系统监控项等，提升产品易用性。

3. 新增对 TableReader、IndexReader 和 IndexLookupReader 算子内存使用情况统计信息，提高 Query 内存使用统计的准确性，提升处理内存消耗较大语句的效率。

4. 制定日志规范，重构日志系统，统一日志格式，方便用户理解日志内容，有助于通过工具对日志进行定量分析。

5. 新增 EXPLAIN ANALYZE 功能，提升SQL 调优的易用性。

6. 新增 SQL 语句 Trace 功能，方便排查问题。

7. 新增通过 unix_socket 方式连接数据库。

8. 新增快速恢复被删除表功能，当误删除数据时可通过此功能快速恢复数据。

TiDB 3.0 新增 TiFlash 组件，解决复杂分析及 HTAP 场景。TiFlash 是列式存储系统，与行存储系统实时同步，具备低延时，高性能，事务一致性读等特性。 通过 Raft 协议从 TiKV 中实时同步行存数据并转化成列存储格式持久化到一组独立的节点，解决行列混合存储以及资源隔离性问题。TiFlash 可用作行存储系统（TiKV）实时镜像，实时镜像可独立于行存储系统，将行存储及列存储从物理隔离开，提供完善的资源隔离方案，HTAP 场景最优推荐方案；亦可用作行存储表的索引，配合行存储对外提供智能的 OLAP 服务，提升约 10 倍复杂的混合查询的性能。

TiFlash 目前处于 Beta 阶段，计划 2019 年 12 月 31 日之前 GA，欢迎大家申请试用。

未来我们会继续投入到系统稳定性，易用性，性能，弹性扩展方面，向用户提供极致的弹性伸缩能力，极致的性能体验，极致的用户体验。

稳定性方面 V4.0 版本将继续完善 V3.0 未 GA 的重大特性，例如：悲观事务模型，View，Table Partition，Titan 行存储引擎，TiFlash 列存储引擎；引入近似物理备份恢复解决分布数据库备份恢复难题；优化 PD 调度功能等。

性能方面 V4.0 版本将继续优化事务处理流程，减少事务资源消耗，提升性能，例如：1PC，省去获取 commit ts 操作等。

弹性扩展方面，PD 将提供弹性扩展所需的元信息供外部系统调用，外部系统可根据元信息及负载情况动态伸缩集群规模，达成节省成本的目标。

我们相信战胜“未知”最好的武器就是社区的力量，基础软件需要坚定地走开源路线。截止发稿我们已经完成 41 篇源码阅读文章。TiDB 开源社区总计 265 位 Contributor，6 位 Committer，在这里我们对社区贡献者表示由衷的感谢，希望更多志同道合的人能加入进来，也希望大家在 TiDB 这个开源社区能够有所收获。

TiDB 3.0 GA Release Notes：

golang配制高性能sql.DB

有很多教程是关于Go的sql.DB类型和如何使用它来执行SQL数据库查询的。但大多数内容都没有讲述 SetMaxOpenConns() , SetMaxIdleConns() 和 SetConnMaxLifetime()方法， 您可以使用它们来配置sql.DB的行为并改变其性能。

转自：

整理：go语言中文文档：

在本文我将详细解释这些设置的作用，并说明它们所能产生的(积极和消极)影响。

一个sql.DB对象就是一个数据库连接池，它包含“正在用”和“空闲的”连接。一个正在用的连接指的是，你正用它来执行数据库任务，例如执行SQL语句或行查询。当任务完成连接就是空闲的。

当您创建sql.DB执行数据库任务时，它将首先检查连接池中是否有可用的空闲连接。如果有可用的连接，那么Go将重用现有连接，并在执行任务期间将其标记为正在使用。如果池中没有空闲连接，而您需要一个空闲连接，那么Go将创建一个新的连接。

默认情况下，在同一时间打开连接的数量是没有限制(包含使用中+空闲)。但你可以通过SetMaxOpenConns()方法实现自定义限制，如下所示:

在这个示例代码中，连接池现在有5个并发打开的连接数。如果所有5个连接都已经被标记为正在使用，并且需要另一个新的连接，那么应用程序将被迫等待，直到5个连接中的一个被释放并变为空闲。

为了说明更改MaxOpenConns的影响，我运行了一个基准测试，将最大打开连接数设置为1、2、5、10和无限。基准测试在PostgreSQL数据库上执行并行的INSERT语句，您可以在这里找到代码。测试结果:

对于这个基准测试，我们可以看到，允许打开的连接越多，在数据库上执行INSERT操作所花费的时间就越少(打开的连接数为1时，执行速度3129633ns/op，而无限连接：531030ns/op——大约快了6倍)。这是因为允许打开的连接越多，可以并发执行的数据库查询就越多。

默认情况下，sql.DB允许连接池中最多保留2个空闲连接。你可以通过SetMaxIdleConns()方法改变它，如下所示:

从理论上讲，允许池中有更多的空闲连接将提高性能，因为这样就不太可能从头开始建立新连接——因此有助于提升数据库性能。

让我们来看看相同的基准测试，最大空闲连接设置为none, 1,2,5和10:

当MaxIdleConns设置为none时，必须为每个INSERT从头创建一个新的连接，我们可以从基准测试中看到，平均运行时和内存使用量相对较高。

只允许保留和重用一个空闲连接对基准测试影响特别明显——它将平均运行时间减少了大约8倍，内存使用量减少了大约20倍。继续增加空闲连接池的大小会使性能变得更好，尽管改进并不明显。

那么，您应该维护一个大的空闲连接池吗?答案取决于应用程序。重要的是要意识到保持空闲连接是有代价的—它占用了可以用于应用程序和数据库的内存。

还有一种可能是，如果一个连接空闲时间太长，那么它可能会变得不可用。例如，MySQL的wait_timeout设置将自动关闭任何8小时(默认)内未使用的连接。

当发生这种情况时，sql.DB会优雅地处理它。坏连接将自动重试两次，然后放弃，此时Go将该连接从连接池中删除，并创建一个新的连接。因此，将MaxIdleConns设置得太大可能会导致连接变得不可用，与空闲连接池更小(使用更频繁的连接更少)相比，会占有更多的资源。所以，如果你很可能很快就会再次使用，你只需保持一个空闲的连接。

最后要指出的是，MaxIdleConns应该总是小于或等于MaxOpenConns。Go强制执行此操作，并在必要时自动减少MaxIdleConns。

现在让我们看看SetConnMaxLifetime()方法，它设置连接可重用的最大时间长度。如果您的SQL数据库也实现了最大连接生命周期，或者—例如—您希望方便地在负载均衡器后交换数据库，那么这将非常有用。

你可以这样使用它:

在这个例子中，所有的连接都将在创建后1小时“过期”，并且在过期后无法重用。但注意:

从理论上讲，ConnMaxLifetime越短，连接过期的频率就越高——因此，需要从头创建连接的频率就越高。为了说明这一点，我运行了将ConnMaxLifetime设置为100ms、200ms、500ms、1000ms和无限(永远重用)的基准测试，默认设置为无限打开连接和2个空闲连接。这些时间段显然比您在大多数应用程序中使用的时间要短得多，但它们有助于很好地说明行为。

在这些特定的基准测试中，我们可以看到，与无限生存期相比，在100ms生存期时内存使用量增加了3倍以上，而且每个INSERT的平均运行时也稍微长一些。

如果您在代码中设置了ConnMaxLifetime，那么一定要记住连接将过期(随后重新创建)的频率。例如，如果您总共有100个连接，而ConnMaxLifetime为1分钟，那么您的应用程序可能每秒钟杀死和重新创建1.67个连接(平均值)。您不希望这个频率太大，最终会阻碍性能，而不是提高性能。

最后，如果不说明超过数据库连接数量的硬限制将会发生什么，那么本文就不完整了。 为了说明这一点，我将修改postgresql.conf文件，这样总共只允许5个连接(默认是100个)…

然后在无限连接的情况下重新运行基准测试……

一旦达到5个连接的硬限制，数据库驱动程序(pq)立即返回一个太多客户端连接的错误消息，而无法完成INSERT。为了防止这个错误，我们需要将sql.DB中打开连接的最大总数(正在使用的+空闲的)设置为低于5。像这样:

现在，sql.DB在任何时候最多只能创建3个连接，基准测试运行时应该不会出现任何错误。但是这样做需要注意：当达到开放连接数限制，并且所有连接都在使用时，应用程序需要执行的任何新的数据库任务都将被迫等待，直到连接标记为空闲。例如，在web应用程序的上下文中，用户的HTTP请求看起来会“挂起”，甚至在等待数据库任务运行时可能会超时。

为了减轻这种情况，你应该始终在一个上下文中传递。在调用数据库时，启用上下文的方法(如ExecContext())，使用固定的、快速的超时上下文对象。

总结

1、根据经验，应该显式设置MaxOpenConns值。这应该小于数据库和基础设施对连接数量的硬性限制。

2、一般来说，更高的MaxOpenConns和MaxIdleConns值将带来更好的性能。但你应该注意到效果是递减的，连接池空闲连接太多(连接没有被重用，最终会变坏)实际上会导致性能下降。

3、为了降低上面第2点带来的风险，您可能需要设置一个相对较短的ConnMaxLifetime。但你也不希望它太短，导致连接被杀死或不必要地频繁重建。

4、MaxIdleConns应该总是小于或等于MaxOpenConns。

对于中小型web应用程序，我通常使用以下设置作为起点，然后根据实际吞吐量水平的负载测试结果进行优化。

优化SQL有什么方法

在数据库应用系统中编写可执行的SQL语句可以有多种方式实现，但哪一条是最佳方案却难以确定。为了解决这一问题，有必要对SQL实施优化。简单地说，SQL语句的优化就是将性能低下的SQL语句转换成达到同样目的的性能更好的SQL语句。

优化SQL语句的原因

数据库系统的生命周期可以分成: 设计、开发和成品三个阶段。在设计阶段进行优化的成本最低，收益最大。在成品阶段进行优化的成本最高，收益最小。如果将一个数据库系统比喻成一座楼房，在楼房建好后进行矫正往往成本很高而收效很小（甚至可能根本无法矫正），而在楼房设计、生产阶段控制好每块砖瓦的质量就能达到花费小而见效高的目的。

为了获得最大效益，人们常需要对数据库进行优化。数据库的优化通常可以通过对网络、硬件、操作系统、数据库参数和应用程序的优化来进行。根据统计，对网络、硬件、操作系统、数据库参数进行优化所获得的性能提升全部加起来只占数据库应用系统性能提升的40%左右，其余60%的系统性能提升全部来自对应用程序的优化。许多优化专家甚至认为对应用程序的优化可以得到80%的系统性能提升。因此可以肯定，通过优化应用程序来对数据库系统进行优化能获得更大的收益。

对应用程序的优化通常可分为两个方面: 源代码的优化和SQL语句的优化。由于涉及到对程序逻辑的改变，源代码的优化在时间成本和风险上代价很高(尤其是对正在使用中的系统进行优化) 。另一方面，源代码的优化对数据库系统性能的提升收效有限，因为应用程序对数据库的操作最终要表现为SQL语句对数据库的操作。

对SQL语句进行优化有以下一些直接原因：

1. SQL语句是对数据库(数据) 进行操作的惟一途径，应用程序的执行最终要归结为SQL语句的执行，SQL语句的效率对数据库系统的性能起到了决定性的作用。

2. SQL语句消耗了70%～90%的数据库资源。

3. SQL语句独立于程序设计逻辑，对SQL语句进行优化不会影响程序逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。

4. SQL语句可以有不同的写法，不同的写法在性能上的差异可能很大。

5. SQL语句易学，难精通。SQL语句的性能往往同实际运行系统的数据库结构、记录数量等有关，不存在普遍适用的规律来提升性能。

传统的优化方法

SQL程序人员在传统上采用手工重写来对SQL语句进行优化。这主要依靠DBA或资深程序员对SQL语句执行计划的分析，依靠经验，尝试重写SQL语句，然后对结果和性能进行比较以试图找到性能较佳的SQL语句。这种做法存在着以下不足：

1. 无法找出SQL语句的所有可能写法。很可能花费了大量的时间也无法找到性能较佳的SQL语句。即便找到了某个性能较佳的SQL语句也无法知道是否存在性能更好的写法。

2. 非常依赖于人的经验，经验的多寡往往决定了优化后SQL语句的性能。

3. 非常耗时间。重写--校验正确性--比较性能，这一循环过程需要大量的时间。

根据传统的SQL优化工具的功能，人们一般将优化工具分为以下三代产品：

第一代的SQL优化工具是执行计划分析工具。这类工具对输入的SQL语句从数据库提取执行计划，并解释执行计划中关键字的含义。

第二代的SQL优化工具只能提供增加索引的建议，它通过对输入的SQL语句的执行计划的分析来产生是否要增加索引的建议。这类工具存在着致命的缺点——只分析了一条SQL语句就得出增加某个索引的结论，根本不理会(实际上也无法评估到)增加的索引对整体数据库系统性能的影响。

第三代工具是利用人工智能实现自动SQL优化。

人工智能自动SQL优化

随着人工智能技术的发展和在数据库优化领域应用的深入，在20世纪90年代末优化技术取得了突破性的进展，出现了人工智能自动SQL优化。人工智能自动SQL优化的本质就是借助人工智能技术，自动对SQL语句进行重写，找到性能最好的等效SQL语句。LECCO SQL Expert就采用了这种人工智能技术，其SQL Expert支持Oracle、Sybase、MS SQL Server和IBM DB2数据库平台。其突出特点是自动优化SQL语句。除此以外，还可以以人工智能知识库“反馈式搜索引擎”来重写SQL语句，并找出所有等效的SQL语句及可能的执行计划，通过测试运行为应用程序和数据库自动找到性能最好的SQL语句，提供微秒级的计时; 能够优化Web应用程序和有大量用户的在线事务处理中运行时间很短的SQL语句; 能通过比较源SQL和待选SQL的不同之处，为开发人员提供“边做边学式训练”，迅速提高开发人员的SQL编程技能等等。

该工具针对数据库应用的开发和维护阶段提供了数个特别的模块：SQL语法优化器、PL/SQL集成化开发调试环境(IDE)、扫描器、数据库监视器等。其核心模块之一“SQL 语法优化器”的工作原理大致如下：输入一条源SQL语句，“人工智能反馈式搜索引擎”对输入的SQL语句结合检测到的数据库结构和索引进行重写，产生N条等效的SQL语句输出，产生的N条等效SQL语句再送入“人工智能反馈式搜索引擎”进行重写，直至无法产生新的输出或搜索限额满，接下来对输出的SQL语句进行过滤，选出具有不同执行计划的SQL语句(不同的执行计划意味着不同的执行效率)，最后，对得到的SQL语句进行批量测试，找出性能最好的SQL语句（参见下图）。

图 人工智能自动SQL优化示意图

LECCO SQL Expert不仅能够找到最佳的SQL语句，它所提供的“边做边学式训练”还能够教会开发人员和数据库管理员如何写出性能最好的SQL语句。LECCO SQL Expert的SQL语句自动优化功能使SQL的优化变得极其简单，只要能够写出SQL语句，它就能帮开发人员找到最好性能的写法。

小 结

SQL语句是数据库应用中一个非常关键的部分，它执行性能的高低直接影响着应用程序的运行效率。正因为如此，人们在SQL语句的优化上投入了很大的精力，出现了许多SQL语句优化工具。随着人工智能等相关技术的日益成熟, 肯定还会有更多更好的工具出现，这将会给开发人员提供更多的帮助。