自学内容网 自学内容网
自学内容网 > 正文

Wireshark 分析SQL 批量插入慢的问题

🕗 发布于 2024-11-17 08:37 wireshark  sql  测试工具  

有一个数据导入程序需要导入大量的数据，使用 Spring JdbcTemplate 的批量操作功能进行数据批量导入，但是发现性能非常差，和普通的单条 SQL 执行性能差不多。

创建一个表：

CREATE TABLE `testuser` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

使用 JdbcTemplate 的 batchUpdate 方法，批量插入 10000 条记录到 testuser 表。

@RunWith(SpringRunner.class)
@SpringBootTest
@Slf4j
public class JDBCTest {

    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Test
    public void test() {

        long begin = System.currentTimeMillis();

        String sql = "INSERT INTO `testuser` (`name`) VALUES (?)";

        //使用JDBC批量更新
        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {

                log.info("批次：{}", i);

                //第一个参数(索引从1开始)，也就是name列赋值
                preparedStatement.setString(1, "usera" + i);
            }

            @Override
            public int getBatchSize() {
                //批次大小为10000
                return 10000;
            }
        });

        log.info("cost : {} ms", System.currentTimeMillis() - begin);
    }
}

执行程序后可以看到，1 万条数据插入耗时 93 秒：

2024-11-16 22:41:20.688  INFO 18043 --- [main] com.redis.demo.JDBCTest                  : cost : 93678 ms

对于批量操作，我们希望程序可以把多条 insert SQL 语句合并成一条，或至少是一次性提交多条语句到数据库，以减少和 MySQL 交互次数、提高性能。

那么，我们的程序是这样运作的吗？

打开 Wireshark，启动后选择需要捕获的网卡（因为我连接的是远程服务器的 MySQL，这里选择 utun4，如果是本地，选择loopback）

Wireshark是一个非常流行的网络封包分析工具，它能够捕获各种网络数据包并显示其详细信息。

在这里插入图片描述

然后，Wireshark 捕捉这个网卡的所有网络流量，在上方的显示过滤栏输入 tcp.port == 3306，来过滤出所有 3306 端口的 TCP 请求。

可以看到，程序运行期间和 MySQL 有大量交互。

因为 Wireshark 直接把 TCP 数据包解析为了 MySQL 协议，所以下方窗口可以直接显示 MySQL 请求的 SQL 查询语句。

我们看到，testuser 表的每次 insert 操作，插入的都是一行记录：

在这里插入图片描述

这就说明，我们的程序并不是在做批量插入操作，和普通的单条循环插入没有区别。

调试程序进入PreparedStatement 源码查看。

在这里插入图片描述

红线中判断了 rewriteBatchedStatements 参数是否为 true，是才会开启批量的优化。

优化方式有 2 种：

  • 优先把 insert 语句优化为一条语句，也就是 executeBatchedInserts 方法；
  • 再尝试把 insert 语句优化为多条语句一起提交，也就是 executePreparedBatchAsMultiStatement 方法。

实现批量提交优化的关键，在于 rewriteBatchedStatements 参数，我们修改连接字符串，并将其值设置为 true。

spring.datasource.url=jdbc:mysql://ip:3306/db?useUnicode=true&characterEncoding=utf-8&serverTimezone=UTC&useSSL=false&rewriteBatchedStatements=true

重新按照之前的步骤打开 Wireshark 验证，可以看到：

  • 这次的 insert 语句被拼装成了一个 SQL；
  • 这个 TCP 包因为太大被分割成了 113 个片段传输，#402 请求是最后一个片段，其实际内容是 insert 语句的最后一部分内容。
    在这里插入图片描述

为了查看整个 TCP 连接的所有数据包，可以在请求上点击右键，选择 Follow->TCP Stream。

可以看到从 MySQL 认证开始到 insert 语句的所有数据包的内容：

在这里插入图片描述在这里插入图片描述

查看最开始的握手数据包可以发现，TCP 的最大分段大小（MSS）是 1424 字节，而我们的 MySQL 超长 insert 的数据一共 138933 字节，因此被分成了 113 段传输，其中最大的一段是 1360 字节，低于 MSS 要求的 1424 字节。

在这里插入图片描述

最后可以看到插入 1 万条数据仅耗时 1915 毫秒，性能提升了 50 倍：

2024-11-16 20:49:53.875  INFO 12157 --- [main] com.redis.demo.JDBCTest                  : took : 1915 ms

参考资料：
《Java 业务开发错误 100 例》


原文地址：https://blog.csdn.net/yqq962464/article/details/143824535

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024