当前位置：首页 > news >正文

golang 高性能的 MySQL 数据导出

news 2025/3/12 23:44:06

- 背景:
- 任务
- 行动
- - - 单元测试
    - 分页查询的问题
    - - 深度分页的耗时情况
  - 优化分页查询的查询性能
  - - 方案1: 偏移分页(查询耗时:4s)
    - 方案2: 快照导出(查询耗时:1.5s)
  - 其他优化
  - - 异步处理+流式对象储存
- 结果

背景:

需求

我在工作的使用收到一个导出数据数据的需求,导出为Excel文件

处理

我先查阅了网上的文档,他们提供的方案都是分页查询的方式
- 不使用一次查询全表数据的原因是如果数据量特别庞大的情况下会消耗 MySQL 的大量内存空间,造成服务器卡顿,影响服务性能

确定分页大小

选择合适的分页大小
- 分页太大的问题: 浪费内存,造成服务器卡顿
- 分页太小: 查询次数太多,导出慢
我从 100 大小开始,每次增加 100, 一直增加到 4000的分页大小
发现导出的性能先递增,到两千后变化就不大了;所以我选择两千的分页大小

bug: 数据导出超时

当测试测的时候出现导出超时的问题,就给我提了一个 bug

问题分析:

测试时未使用任何筛选字段,导出全表数据 200 万条
前端的超时时间 1 分钟(我试了一下,导出全表的数据需要 5 分钟

任务

解决超时问题,即使导出全表数据也不能超时

行动

单元测试

我对代码进行单元测试,看各阶段耗时情况
- 发现查询耗时4min40s
- 数据处理与传输 20s

数据查询耗时占了约 93%; 核心优化数据查询

分页查询的问题

-- 分页查询
SELECT ..... OFFSET m LIMIT n

但是你使用EXPLAIN语句就会发现分页查询是全表扫描,没有走任何索引逻辑

他的逻辑是从数据的第一条开始扫描,把前面扫描到的 m 条数据都丢弃掉然后取有面的 n 条数据
所以 m 越大(page 越大);查询越慢

深度分页的耗时情况

--浅分页(耗时 15ms)
SELECT * FROM users OFFSET 2000 LIMIT 2000--深度分页(耗时 1.12s)
SELECT * FROM users OFFSET 200000 LIMIT 2000

优化分页查询的查询性能

方案1: 偏移分页(查询耗时:4s)

原理是使用 WHERE id>m 代替OFFSET m,查询直接走主键索引确定数据位置

--偏移分页
SELECT .....  WHERE id > m LIMIT n

EXPLAIN的结果是 range 类型

代码示例:

	// 定义分页参数starId := 0pageSize := 2000// 定义查询条件var users []User// 执行分页查询for {result := db.Limit(pageSize).Where("id >= ?", starId).Find(&users)if result.Error != nil {log.Fatal("err5:", result.Error)}if len(users) < pageSize {break}starId = users[len(users)-1].Id}

整个查询耗时 4s (本地测试),查询性能提高 70 倍
导出总耗时: 24s(数据查询耗时:4s; 数据处理与传输耗时 20s)

方案2: 快照导出(查询耗时:1.5s)

因为我之前有做过使用 go 实现 MySQLdump 的项目
项目地址:https://github.dev/dengjiayue/mysqldump/blob/master/mysqldump.go

与这边的数据导出的逻辑非常类似;

我参考我之前 MySQLdump 的导出逻辑进行进一步优化

代码示例:

data, err := db.Query("select * from users")if err != nil {log.Fatal("err1:", err)}defer data.Close()columns, err := data.Columns()if err != nil {log.Printf("[error] %v \n", err)}l := len(columns)val := &User{}valPointer := make([]interface{}, l)valPointer[0] = &val.IdvalPointer[1] = &val.NamevalPointer[2] = &val.TagvalPointer[3] = &val.PhonevalPointer[4] = &val.Create_time//计数num := 0for data.Next() {err := data.Scan(valPointer...)if err != nil {log.Fatal("err5:", err)}}