数据库性能优化（一）： ent.io实现水平分表

站长

2024年03月28日 13:44 · 阅读数 4

背景

当系统在快速发展中，数据库表的规模越来越庞大，单表行数可能超百万、千万级别时，为了不影响读写的性能，这时候可以考虑分表了。而ent.io是一个基于Go语言的开源ORM，本文将简介ent.io如何实现水平分表。

横向拆分和纵向拆分

那如何做拆分，通常有两种分法，分别是横向拆分(水平拆分)和纵向拆分(垂直拆分)。

纵向拆分: 将一张表某一条记录的多个字段，拆分到多张表中
横向拆分: 把一张表中的不同的记录分别放到不同的表中

ent.io 水平分表

大致步骤

创建分表规则：可以选择按照某个字段（如用户ID）进行分表，或者根据时间戳等其他条件进行分表
insert: 根据分表字段写入到不同的分表
按时间字段动态更新不同的分表
跨表select: 需要从不同的分表总读取数据聚合返回

接着我们通过一个demo： (按时间字段水平拆分用户表)来详细介绍:

quick demo 快速创建一个User，schema 如下，创建过程参考entgo.io/zh/docs/get… 这里不展开了。

package schema

import (
	"entgo.io/ent"
	"entgo.io/ent/dialect/entsql"
	"entgo.io/ent/schema"
	"entgo.io/ent/schema/field"
	"fmt"
	"time"
)

// User holds the schema definition for the User entity.
type User struct {
	ent.Schema
}

// Fields of the User.
func (User) Fields() []ent.Field {
	return []ent.Field{
		field.Int("age").
			Positive(),
		field.String("name").
			Default("unknown"),
		field.Time("date").Default(time.Now()).Optional(),
	}
}

// Edges of the User.
func (User) Edges() []ent.Edge {
	return nil
}

func (User) Annotations() []schema.Annotation {
	return []schema.Annotation{
		entsql.Annotation{Table: fmt.Sprintf("users_%d", time.Now().Unix())},
	}
}
func (User) Hooks() []ent.Hook {
	return []ent.Hook{}
}

func (User) Indexes() []ent.Index {
	return []ent.Index{
	}
}

ent.io 水平分表

package main

import (
	"context"
	"database/sql"
	entsql "entgo.io/ent/dialect/sql"
	"entgo.io/ent/dialect/sql/schema"
	"fmt"
	"github.com/google/uuid"
	_ "github.com/lib/pq"
	"log"
	"strings"
	"time"
	"tkingo.vip/egs/ent-sharding/ent"
	"tkingo.vip/egs/ent-sharding/ent/user"
)

func main() {
	var (
		ctx       = context.Background()
		startTime = time.Date(2024, time.January, 1, 0, 0, 0, 0, time.UTC)
		endTime   = time.Date(2024, time.March, 30, 23, 59, 59, 0, time.UTC)
	)
	dsn := "host=localhost user=postgres password=123456 dbname=test2 port=5432 sslmode=disable TimeZone=Asia/Shanghai"
	client, err := ent.Open("postgres", dsn)
	if err != nil {
		log.Fatalf("failed opening connection to postgres: %v", err)
	}
	defer client.Close()

	db, err := sql.Open("postgres", "postgres://postgres:123456@localhost/test2?sslmode=disable")
	if err != nil {
		log.Fatal(err)
	}
	db.SetConnMaxLifetime(time.Hour)
	db.SetMaxOpenConns(30)
	defer db.Close()

	// 创建分表
	now := time.Now()
	yearTables := getYearlyTables(now.Year())
	for _, tableName := range yearTables {
		if err := client.Schema.Create(
			context.Background(),
			schema.WithHooks(func(next schema.Creator) schema.Creator {
				return schema.CreateFunc(func(ctx context.Context, tables ...*schema.Table) error {
					var dynamicTables []*schema.Table
					for _, v := range tables {
						nv := v
						nv.Name = tableName
						dynamicTables = append(dynamicTables, nv)
					}
					return next.Create(ctx, dynamicTables...)
				})
			}),
		); err != nil {
			log.Fatalf("failed creating schema resources: %v", err)
		}
	}

	//插入数据: 按不同的月份到更新到不同分表
	var users []ent.User
	februaryTime := time.Date(2024, time.February, 2, 23, 59, 59, 0, time.UTC)
	januaryTime := time.Date(2024, time.January, 2, 23, 59, 59, 0, time.UTC)
	januaryTimeUsers := getMonthlyTable("users", januaryTime)
	februaryUsers := getMonthlyTable("users", februaryTime)
	users = append([]ent.User{}, ent.User{
		Age:  11,
		Name: uuid.NewString(),
		Date: januaryTime,
	})
	err = createBulk(db, januaryTimeUsers, users)
	users = append([]ent.User{}, ent.User{
		Age:  22,
		Name: uuid.NewString(),
		Date: februaryTime,
	})
	err = createBulk(db, februaryUsers, users)

	//更新数据到不同到分表
	_, err = client.User.Update().Where(
		func(s *entsql.Selector) {
			table := entsql.Table(februaryUsers)
			s.Where(entsql.GTE(user.FieldID, 0)).
				From(table)
		}).
		SetAge(33).
		Save(ctx)
	if err != nil {
		log.Fatalf("failed opening connection to postgres: %v", err)
	}
	
	//按起止时间从不同到分表总查询数据
	queryTables := getMonthlyTables(startTime, endTime)
	fmt.Println("queryTables", queryTables)
	s := time.Now()
	for _, queryTable := range queryTables {
		us, err := client.User.Query().Where(func(s *entsql.Selector) {
			s.Where(entsql.LTE(user.FieldDate, time.Now())).
				From(entsql.Table(queryTable)).Select("*")
		}).All(context.Background())
		if err != nil {
			fmt.Println("custom table name failed", err)
		}
		fmt.Println("==================", queryTable, "start")
		fmt.Println(us)
		fmt.Println("==================", queryTable, "end")
	}

	e := time.Now().Sub(s).Seconds()
	fmt.Println("end", e)
}

func createBulk(db *sql.DB, tableName string, users []ent.User) (err error) {
	// 构建批量插入语句的值部分字符串
	values := []string{}
	for _, u := range users {
		dateFormat := u.Date.UTC().Format("2006-01-02 15:04:05-07")
		values = append(values, fmt.Sprintf("(%d, '%s', '%s')", u.Age, u.Name, dateFormat))
	}
	fmt.Println(strings.Join(values, ", "))
	// 构建完整的批量插入语句
	query := fmt.Sprintf("INSERT INTO %s (age,name,date) VALUES %s", tableName, strings.Join(values, ", "))
	fmt.Println(query)
	_, err = db.Exec(query)
	if err != nil {
		fmt.Println("执行插入错误:", err)
		return
	}
	fmt.Println("批量插入成功")
	return nil
}

执行输出

//OUTPUT
(11, '10b28be1-d1b5-45e3-b18f-4dd1548cc374', '2024-01-02 23:59:59+00')
INSERT INTO users_202401 (age,name,date) VALUES (11, '10b28be1-d1b5-45e3-b18f-4dd1548cc374', '2024-01-02 23:59:59+00')
批量插入成功
(22, 'c68df01a-d31a-4451-a26f-22d565879840', '2024-02-02 23:59:59+00')
INSERT INTO users_202402 (age,name,date) VALUES (22, 'c68df01a-d31a-4451-a26f-22d565879840', '2024-02-02 23:59:59+00')
批量插入成功
queryTables [users_202401 users_202402 users_202403]
================== users_202401 start
[User(id=5, age=11, name=10b28be1-d1b5-45e3-b18f-4dd1548cc374, date=Wed Jan  3 07:59:59 2024)]
================== users_202401 end
================== users_202402 start
[User(id=5, age=33, name=c68df01a-d31a-4451-a26f-22d565879840, date=Sat Feb  3 07:59:59 2024)]
================== users_202402 end
================== users_202403 start
[]
================== users_202403 end
end 0.009452834

上例中

使用ent提供的schema.WithHooks，按tablename_年月的格式创建分表: users_202401、users_202402、users_202403 ...
ent暂时没有提供动态表名插入数据，这里作者使用原生替代，并创建2条不同月份的数据插入到不同的分表中(users_202401、users_202402)
更新数据可以使用.Where(func(s *entsql.Selector) { table := entsql.Table(【动态表名】)的方式动态更新不同分表。
最终通过输入start、end 2个time.Time类型数据，遍历查询不同分表的数据。可以看到2步骤插入的数据分表插入到了users_202401、users_202402，3步骤的也成功把users_202402表中，id=5的数据的age字段修改为了33.

优缺点

优势：

提升了读性能：数据被拆分到多个表中，单表行数较少，按时间读取效率更高。
可扩展性：当数据量的增长时，可以更快添加更多的分表，不影响现有逻辑。

缺点：

跨表查询更复杂：比如分页、order-by等：当需要跨多个分表、进行分页查询货排序时。或是需要聚合、代码统计不同的分表。

总结

当系统的数据快速增长，数据库每天以数万数十万的增速，为了不影响读写效率可以考虑分表了。分表通常有横向拆分和纵向拆分，ent.io是一个基于Go语言的开源ORM，文中通过一个按时间字段水平拆分用户表的例子，演示了ent.io实现水平分表的过程。分表提升了读写性能，但也带来了复杂度的提升，如何使用取决于使用场景。

待续，希望对您有所帮助~

数据库性能优化（一）： ent.io实现水平分表

背景

横向拆分和纵向拆分

ent.io 水平分表

优缺点

总结

参考