深入理解Go SQL内部机制

引言

在本文中，我们将探索Go的SQL和MySQL驱动程序的内部工作原理，揭示优化数据库交互和最小化错误的关键见解。这些见解来自对Go SQL标准库和Go MySQL驱动程序源代码的深入研究。

主要关键点：

预处理语句：高效且安全的SQL执行
连接生命周期：资源使用和连接池维护
实际问题：解决微服务架构中的常见挑战

在下文中，我们通过内联方式阐释Go概念。小写术语表示包名，大写术语表示类型名。括号表示方法，没有括号则表示类型。例如，sql.Stmt指的是sql包中的Stmt类型，而Stmt.QueryContext()表示Stmt类型上的QueryContext方法。

为什么这很重要

深入理解SQL和MySQL驱动程序在Go中的工作方式对构建高性能应用至关重要。通过深入研究预处理语句和连接生命周期等概念，我们可以优化数据库交互并有效处理故障。这些知识使开发人员能够编写更好的代码，并最大限度地减少数据库问题导致的错误。

预处理语句

预处理语句是由DBMS解析和保存的SQL，通常包含占位符但没有实际参数值。之后，该语句可以使用一组参数值执行。

预处理语句的优势是：

效率：可以重复使用而无需重新编译
安全性：减少或消除SQL注入攻击

虽然出于安全目的使用预处理语句确实是个好理由，但说实话，它们使用的主要动机是效率，正如我将在本文其余部分进一步解释的那样。

预处理语句生命周期

当我们想要在预处理语句上执行查询时，需要执行以下步骤：

语句准备：DB.Prepare()方法准备SQL语句并返回一个sql.Stmt对象
查询执行：当使用Stmt.Query()执行查询时，驱动程序从连接池中检索连接，将SQL语句发送到数据库服务器，并等待结果

语句准备

要准备语句，客户端从连接池中获取连接并将查询发送到DBMS，DBMS解析查询并将语句ID返回给客户端，然后客户端构建sql.Stmt对象。

查询执行

要使用Stmt.QueryContext()执行查询，客户端从池中检索连接。预处理语句的范围仅限于创建它的连接。因此，sql.Stmt必须验证连接是否已准备好。如果没有，它会自动准备连接，发送参数，并返回sql.Rows。随后，我们使用Rows.Scan()检索行数据。调用Rows.Close()时释放连接。

我们在这里获得效率，因为每次需要运行查询时都不需要发送整个查询字符串，DBMS也不需要解析查询。如果QPS（每秒查询数）很高，先准备它，然后执行它是有意义的。

到目前为止，我已经概述了使用连接的两个阶段：获取和释放它们。

让我们先讨论释放连接，然后再讨论如何获取连接。

释放连接

一旦查询执行完成并处理结果集，连接就返回到连接池中，允许其他查询重用它。

释放SQL连接的关键规则：

在Go中释放SQL连接：检查最大打开和空闲连接，并处理挂起的请求。

// 设置最大打开连接数
db.SetMaxOpenConns(100)
// 设置最大空闲连接数
db.SetMaxIdleConns(100)

如果当前打开的连接数超过了DB.SetMaxOpenConns(int)允许的最大值，空闲连接会立即关闭而不返回到空闲列表
如果有挂起的连接请求，空闲连接会转发给该请求；否则，如果挂起的连接数低于DB.SetMaxIdleConns(int)设置的最大空闲连接数，连接会被追加到空闲连接列表中以供后续使用
否则，连接将立即关闭，DBStats.MaxIdleClosed指标增加。

Go不会无限期保留空闲连接；它会定期清理它们。连接清理作业会关闭空闲时间超过最大空闲时间的连接，以及生命周期超过最大生命周期的连接。这个过程确保空闲连接得到及时清理。两个指标DBStats.MaxIdleTimeClosed和DBStats.MaxLifetimeClosed表示这种行为。

在内部，三个测量指标代表这些连接：

DBStats.Idle：显示可用的空闲连接数
DBStats.InUse：表示当前运行查询的连接数
DBStats.OpenConnections：这两个指标的总和

获取连接

现在，让我们深入研究管理数据库连接的过程。

在Go中获取/抓取SQL连接：重用现有连接、打开新连接和管理连接到期

连接重用尝试

当使用Go时，它会尝试重用空闲列表中的连接。它检查每个连接的生命周期。如果连接的生命周期超过由DB.SetConnMaxLifetime(time.Duration)设置的最大值，它会关闭连接并返回driver.ErrBadConn。在两次重试后，如果错误仍然存在，它会尝试打开新连接。在返回连接之前，它会使用其DB驱动程序（例如MySQL驱动程序）重置连接，以验证连接的活跃性。

处理连接稀缺

如果我们用完了空闲连接或被明确指示不使用连接，我们有两个选择：

建立新连接
请求连接并等待直到连接池中有可用连接

在这种情况下，它会重置连接。在等待连接可用时，两个额外的指标会增加：DBStats.WaitCount和DBStats.WaitDuration。

实际问题

我们有一些使用Go编程语言开发的微服务。这些微服务使用MariaDB服务集群进行数据持久化。我们使用ProxySQL来为这些服务提供高可用性。

ProxySQL和MariaDB集群的服务架构

如果我们无法从DBMS获取数据，我们会向客户端响应内部错误。故障可能出现在以下链接之一：

应用服务器和ProxySQL集群之间
ProxySQL服务和MariaDB服务之间

如果这些链接中的任何一个失败，都可能导致运行查询时出错，从而增加内部错误率。

WaitCount增加问题

一个奇怪的现象是，根据我们的指标，打开的连接数只有最大限制的一半。

打开的连接数稳定在15左右

因此，等待计数不应该增加，但它确实增加了。如果等待持续时间不保持稳定，它可能会影响响应时间。

waitCount 在增加

突然增加的请求会导致连接使用增加，由于采样间隔（每10-15秒），这种情况往往不会立即显现。例如，虽然一个图表显示稳定的打开连接，但另一个可能显示等待计数激增，表明请求增加。仅60个额外的同时请求就会导致空闲连接饱和，造成队列。仅依赖单一指标可能导致误解；考虑多个指标对准确性至关重要。

要解决此问题，请考虑增加最大打开连接数（DB.SetMaxOpenConns(int)）。如果由于应用程序实例总数超过总允许的最大连接数而无法提高此限制，你可能需要在增加最大打开连接数之前减少应用程序实例的数量。

内部错误增加

我的探索发现，虽然Go的SQL标准库尝试重置连接以进行可用性检查，但它并不能完全修复无效连接错误。例如意外EOF等错误会影响响应时间，增加内部错误，并使用户不满意。调查显示，这个问题是由DBMS相关服务器参数（如ProxySQL或MariaDB）调整不当造成的。

Error #01: invalid connection
[mysql] 2024/03/29 16:49:43 packets.go:37: unexpected EOF

MySQL在wait_timeout后终止空闲连接，但ProxySQL没有收到通知，导致错误假设连接状态。

要解决这个问题，需要将mysql-ping_interval_server_msec减少到与wait_timeout相匹配或更低。这会提示ProxySQL检查空闲连接并避免将请求重定向到已终止的连接。

使用这些设置还将完全解决ProxySQL的以下警告。

mariadb3 | 2024–03–28 21:07:34 591 [Warning] Aborted connection 591 to db: 'mydb' user: 'app' host: '172.25.0.5' (Got timeout reading communication packets)
Then it gets the following error when the app tries to open new connection:
proxysql | 2024–03–28 21:08:56 MySQL_Session.cpp:1694:handler_again___status_PINGING_SERVER(): [ERROR] Detected a broken connection while during ping on (1,mariadb2,3306,604) , FD (Conn:80 , MyDS:80) , user app , last_used 0ms ago : 2006, MySQL server has gone away

ProxySQL不会关闭连接，从而导致记录上述错误。

默认情况下，连接不会根据其年龄而关闭。当达到mysql-connection_max_age_ms时，连接会简单地断开，而不会向服务器发送COM_QUIT命令，因此这可能会导致mysql服务器日志中显示中止连接警告（这种行为是故意的）。

最大连接数

考虑这种情况：如果ProxySQL中的max_connections值设置为99，而我们在应用程序中将最大打开连接配置为100，测试显示99.5%的正常运行时间，有0.5%的响应导致内部错误。

ProxySQL会记录以下警告：

proxysql  | 2024-03-30 19:38:05 MySQL_Session.cpp:5504:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [WARNING] User 'app' has exceeded the 'max_user_connections' resource (current value: 99)

这种情况强调了准确调整参数的重要性。

建议

客户端建议

Go-SQL-Driver的自述文件中提到了一些很好的建议：

db.SetConnMaxLifetime() 是必需的，用于确保驱动程序在连接被 MySQL 服务器、操作系统或其他中间件关闭之前安全地关闭连接。由于一些中间件会在 5 分钟后关闭空闲连接，我们建议将超时时间设置为小于 5 分钟。这个设置也有助于负载均衡和更改系统变量。
db.SetMaxOpenConns() 强烈建议设置，用于限制应用程序使用的连接数量。没有推荐的具体限制数字，因为这取决于应用程序和 MySQL 服务器的具体情况。
db.SetMaxIdleConns() 建议设置为与 db.SetMaxOpenConns() 相同的值。当它小于 SetMaxOpenConns() 时，连接的开启和关闭频率可能会比您预期的要高得多。空闲连接可以通过 db.SetConnMaxLifetime() 来关闭。如果您想更快地关闭空闲连接，从 Go 1.15 版本开始，您可以使用 db.SetConnMaxIdleTime()。

配置建议：

// 设置连接最大生存期（小于wait_time设置）
db.SetConnMaxLifetime(...)
// 设置与ConnMaxLifetime相同
db.SetConnMaxIdleTime(...)
// 最大连接数限制除以pod数量
db.SetMaxOpenConns(...)
// 与MaxOpenConns相同
db.SetMaxIdleConns(...)

通过执行指定的查询，您将发现max_connections设置：

ProxySQL Admin> select hostgroup_id, hostname, max_connections from mysql_servers;
+--------------+----------+-----------------+
| hostgroup_id | hostname | max_connections |
+--------------+----------+-----------------+
| 0            | mariadb1 | 20              |
| 1            | mariadb2 | 300             |
| 1            | mariadb3 | 300             |
+--------------+----------+-----------------+

ProxySQL Admin> select username, max_connections from mysql_users;
+----------+-----------------+
| username | max_connections |
+----------+-----------------+
| root     | 25              |
| app      | 300             |
+----------+-----------------+

ProxySQL Admin> select * from global_variables where variable_name in ('mysql-wait_timeout') order by variable_name;
+--------------------+----------------+
| variable_name      | variable_value |
+--------------------+----------------+
| mysql-wait_timeout | 60000          |
+--------------------+----------------+

mysql> show variables where Variable_name in ('max_connections','wait_timeout');
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 300   |
| wait_timeout    | 600   |
+-----------------+-------+

根据这些值，我们应该将连接的最大生存期设置为最多60秒。如果我们运行一个应用程序的3个实例，则最大打开连接数应设置为100。

db.SetConnMaxLifetime(60 * time.Second)
db.SetConnMaxIdleTime(60 * time.Second)
db.SetMaxOpenConns(100)
db.SetMaxIdleConns(100)

服务器端建议

根据wait_timeout调整mysql-ping_interval_server_msec。它应该小于或等于wait_timeout，而不是更大。对已经被MySQL服务器终止的连接进行ping是没有意义的。

示例配置查询：

-- 查询服务器配置
ProxySQL Admin> select * from global_variables where variable_name in ('mysql-ping_interval_server_msec') order by variable_name;
+---------------------------------+----------------+
| variable_name                   | variable_value |
+---------------------------------+----------------+
| mysql-ping_interval_server_msec | 120000         |
+---------------------------------+----------------+

返回的结果表明配置设置不正确。适当的值应该小于60000毫秒（60秒）。值得一提的是，ProxySQL的默认值是10000毫秒（10秒）。

动手实践

要开始，克隆源代码仓库。确保系统上安装了Go、K6和Docker。

git clone git@github.com:empire/db-prepared-stmt.git
cd galera
docker compose up --remove-orphans

集群启动后，执行以下命令运行服务：

go run .

要模拟样本负载，你可以使用k6命令或运行run_watched.sh脚本。为了方便监控MySQL资源和进程列表，我创建了一个名为monitor.sh的脚本。你可以使用命令watch -n 1 ./monitor.sh执行它。

如果需要自定义配置，打开galera/docker-compose.yml和galera/proxysql/proxysql.cnf文件。对变量进行必要的更改，然后重启Docker Compose命令。

结论

理解Go的SQL和MySQL驱动程序的内部工作原理使开发人员能够优化数据库交互并有效解决潜在故障。通过利用预处理语句、管理连接生命周期和应用最佳实践，开发人员可以提高应用程序性能并最小化内部错误。

我的探索揭示了有效资源管理和故障排除技术的关键见解，特别是在分布式系统中。通过实施推荐的配置并及时了解数据库技术，开发人员可以确保应用程序的平稳运行并提供卓越的用户体验。