gorm 的 preload , join , rows.next , batches粗略对比使用

先新建两个表

CREATE TABLE `a` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;
CREATE TABLE `ae` (
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`a_id` INT(10) NULL DEFAULT NULL,
	`name` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `a_id` (`a_id`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

利用存储过程往两个表中插入100万行数据，开启事务是为了更快插入

delimiter ;;
create procedure idata()
begin
  declare i int;
  set i=1;
  START TRANSACTION;
  while(i<=1000000)do
    insert into a values(i, i);
    set i=i+1;
  end while;

  set i=1;
  while(i<=1000000)do
    insert into ae values(i, i, i);
    set i=i+1;
  end while;
commit;
end;;
delimiter ;
call idata();

分别使用 preload , join , rows.next , batches等多种方式进行查询，preload无法查询关联太多条数据，因为使用 in 查询绑定的参数超过了范围；scan的数据库查询耗时较短，一条一条取出，可以节省内存空间，不过原理暂时没去深究;batches批处理在此次对比中暂时没看出来优势，可能是因为目前只是单纯的查询的关系。

package main

import (
	"fmt"
	"gorm.io/gorm"
	"time"
)

var GDB *gorm.DB

type Ae struct {
	Id   int32  `json:"id"`
	AId  int32  `json:"a_id" gorm:"index"`
	Name string `json:"name" gorm:"size:255"`
}
type A struct {
	Id   int32  `json:"id"`
	Name string `json:"name" gorm:"size:255"`
	Aes  []Ae   `json:"aes" gorm:"foreignKey:AId;references:Id"`
}

const limit = 10000
const offset = 500000

func main() {
	GDB = getDB()
	testPreload()  // offset:0,耗时0.955 offset:500000,1.126
	testJoin()     // offset:0,耗时0.028,数据库查询耗时27ms offset:500000,0.949
	testScan()     // offset:0,耗时0.04,数据库查询耗时7ms offset:500000,0.792
	testBatches()  // offset:0,耗时0.113 offset:500000,2.436
	testBatches2() // offset:0,耗时0.319 offset:500000,0.378
}

func testPreload() {
	nowTime := time.Now().UnixMilli()
	var as []A
	GDB.Model(A{}).Preload("Aes").Offset(offset).Limit(limit).Find(&as)
	wait := float64(time.Now().UnixMilli()-nowTime) / 1000
	fmt.Println("preload")
	fmt.Println(wait)
}

func testJoin() {
	nowTime := time.Now().UnixMilli()
	type result struct {
		Id     int32
		Name   string
		AeId   int32
		AeName string
	}
	var rows []result
	GDB.Model(A{}).
		Select("a.id,a.name,ae.id ae_id,ae.name ae_name").
		Joins("join ae on a.id = ae.a_id").Offset(offset).Limit(limit).Find(&rows)
	wait := float64(time.Now().UnixMilli()-nowTime) / 1000
	fmt.Println("join")
	fmt.Println(rows[0])
	fmt.Println(len(rows))
	fmt.Println(wait)
}

func testScan() {
	nowTime := time.Now().UnixMilli()
	type result struct {
		Id     int32
		Name   string
		AeId   int32
		AeName string
	}
	rows, _ := GDB.Model(A{}).
		Select("a.id,a.name,ae.id ae_id,ae.name ae_name").
		Joins("join ae on a.id = ae.a_id").Offset(offset).Limit(limit).Rows()
	defer rows.Close()

	for rows.Next() {
		var a result
		// ScanRows 方法用于将一行记录扫描至结构体
		GDB.ScanRows(rows, &a)
	}
	wait := float64(time.Now().UnixMilli()-nowTime) / 1000
	fmt.Println("scan")
	fmt.Println(wait)
}

func testBatches() {
	nowTime := time.Now().UnixMilli()
	type row struct {
		Id     int32
		Name   string
		AeId   int32
		AeName string
	}
	var results []row
	// 每次批量处理 100 条
	_ = GDB.Model(A{}).Select("a.id,a.name,ae.id ae_id,ae.name ae_name").
		Joins("join ae on a.id = ae.a_id").Offset(offset).Limit(limit).FindInBatches(&results, limit/100, func(tx *gorm.DB, batch int) error {
		//fmt.Println(results[0], results[len(results)-1])
		// 如果返回错误会终止后续批量操作
		return nil
	})
	wait := float64(time.Now().UnixMilli()-nowTime) / 1000
	fmt.Println("Batches")
	fmt.Println(wait)
}

func testBatches2() {
	nowTime := time.Now().UnixMilli()
	var results []A
	// 每次批量处理 100 条
	_ = GDB.Model(A{}).Preload("Aes").Offset(offset).Limit(limit).FindInBatches(&results, limit/100, func(tx *gorm.DB, batch int) error {
		//fmt.Println(results[0], results[len(results)-1])
		// 如果返回错误会终止后续批量操作
		return nil
	})
	wait := float64(time.Now().UnixMilli()-nowTime) / 1000
	fmt.Println("Batches2")
	fmt.Println(wait)
}