Go MySQL 高级特性实现指南

Go MySQL 高级特性实现指南

1. 连接池管理

package db

import (
    "time"
    "github.com/jmoiron/sqlx"
)

type DBPool struct {
    Master *sqlx.DB
    Slaves []*sqlx.DB
}

func NewDBPool(masterDSN string, slaveDSNs []string) (*DBPool, error) {
    // 初始化主库连接
    master, err := sqlx.Connect("mysql", masterDSN)
    if err != nil {
        return nil, err
    }

    // 设置主库连接池参数
    master.SetMaxOpenConns(100)        // 最大连接数
    master.SetMaxIdleConns(10)         // 最大空闲连接数
    master.SetConnMaxLifetime(time.Hour) // 连接最大生命周期
    master.SetConnMaxIdleTime(time.Minute * 30) // 空闲连接最大生命周期

    // 初始化从库连接池
    slaves := make([]*sqlx.DB, 0, len(slaveDSNs))
    for _, dsn := range slaveDSNs {
        slave, err := sqlx.Connect("mysql", dsn)
        if err != nil {
            return nil, err
        }
        
        // 设置从库连接池参数
        slave.SetMaxOpenConns(50)
        slave.SetMaxIdleConns(5)
        slave.SetConnMaxLifetime(time.Hour)
        slave.SetConnMaxIdleTime(time.Minute * 30)
        
        slaves = append(slaves, slave)
    }

    return &DBPool{
        Master: master,
        Slaves: slaves,
    }, nil
}

2. ORM 映射实现

package orm

import (
    "reflect"
    "strings"
)

// Model 基础模型接口
type Model interface {
    TableName() string
}

// BaseModel 提供基础字段
type BaseModel struct {
    ID        int64     `db:"id" json:"id"`
    CreatedAt time.Time `db:"created_at" json:"created_at"`
    UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
}

// Tag 标签解析
type Tag struct {
    Name    string
    Options map[string]string
}

// ModelMapper ORM映射器
type ModelMapper struct {
    model     Model
    tableName string
    fields    map[string]*Tag
}

func NewModelMapper(model Model) *ModelMapper {
    m := &ModelMapper{
        model:     model,
        tableName: model.TableName(),
        fields:    make(map[string]*Tag),
    }
    m.parseModel()
    return m
}

func (m *ModelMapper) parseModel() {
    t := reflect.TypeOf(m.model)
    if t.Kind() == reflect.Ptr {
        t = t.Elem()
    }

    for i := 0; i < t.NumField(); i++ {
        field := t.Field(i)
        tag := field.Tag.Get("db")
        if tag == "" || tag == "-" {
            continue
        }

        m.fields[field.Name] = &Tag{
            Name:    tag,
            Options: parseTagOptions(field.Tag),
        }
    }
}

// 生成插入SQL
func (m *ModelMapper) InsertSQL() string {
    var cols, vals []string
    for _, tag := range m.fields {
        cols = append(cols, tag.Name)
        vals = append(vals, "?")
    }
    
    return fmt.Sprintf(
        "INSERT INTO %s (%s) VALUES (%s)",
        m.tableName,
        strings.Join(cols, ","),
        strings.Join(vals, ","),
    )
}

3. 读写分离实现

package db

import (
    "context"
    "math/rand"
)

type DBRouter struct {
    master  *sqlx.DB
    slaves  []*sqlx.DB
    counter uint64
}

func (r *DBRouter) Master() *sqlx.DB {
    return r.master
}

// 随机选择从库
func (r *DBRouter) Slave() *sqlx.DB {
    if len(r.slaves) == 0 {
        return r.master
    }
    return r.slaves[rand.Intn(len(r.slaves))]
}

// 轮询选择从库
func (r *DBRouter) RoundRobinSlave() *sqlx.DB {
    if len(r.slaves) == 0 {
        return r.master
    }
    atomic.AddUint64(&r.counter, 1)
    return r.slaves[r.counter%uint64(len(r.slaves))]
}

// Repository 示例
type UserRepository struct {
    router *DBRouter
}

func (r *UserRepository) Create(ctx context.Context, user *User) error {
    // 写操作使用主库
    return r.router.Master().QueryRowxContext(ctx,
        "INSERT INTO users (name, age) VALUES (?, ?)",
        user.Name, user.Age,
    ).Err()
}

func (r *UserRepository) Get(ctx context.Context, id int64) (*User, error) {
    // 读操作使用从库
    var user User
    err := r.router.Slave().GetContext(ctx, &user,
        "SELECT * FROM users WHERE id = ?", id)
    return &user, err
}

4. 分库分表实现

package sharding

import (
    "fmt"
    "hash/crc32"
)

// 分片配置
type ShardConfig struct {
    DBCount    int // 数据库数量
    TableCount int // 每个库的表数量
}

// 分片路由
type ShardRouter struct {
    config ShardConfig
    pools  map[int]*sqlx.DB // 数据库连接池map
}

// 计算分片位置
func (r *ShardRouter) CalcShardLocation(shardKey string) (dbIndex, tableIndex int) {
    hash := crc32.ChecksumIEEE([]byte(shardKey))
    dbIndex = int(hash % uint32(r.config.DBCount))
    tableIndex = int((hash / uint32(r.config.DBCount)) % uint32(r.config.TableCount))
    return
}

// 获取表名
func (r *ShardRouter) GetTableName(baseTable string, tableIndex int) string {
    return fmt.Sprintf("%s_%d", baseTable, tableIndex)
}

// 获取数据库连接
func (r *ShardRouter) GetDB(dbIndex int) *sqlx.DB {
    return r.pools[dbIndex]
}

// Repository示例
type UserShardRepository struct {
    router *ShardRouter
}

func (r *UserShardRepository) Create(user *User) error {
    dbIndex, tableIndex := r.router.CalcShardLocation(user.UserID)
    db := r.router.GetDB(dbIndex)
    tableName := r.router.GetTableName("users", tableIndex)
    
    _, err := db.Exec(fmt.Sprintf(
        "INSERT INTO %s (user_id, name, age) VALUES (?, ?, ?)",
        tableName,
    ), user.UserID, user.Name, user.Age)
    return err
}

5. 主从复制监控

package replication

import (
    "context"
    "time"
)

// 复制状态
type ReplicationStatus struct {
    MasterFile     string
    MasterPosition int
    SlaveIORunning bool
    SlaveSQLRunning bool
    SecondsBehindMaster int
    LastError       string
}

// 复制监控器
type ReplicationMonitor struct {
    master *sqlx.DB
    slave  *sqlx.DB
}

// 检查主从状态
func (m *ReplicationMonitor) CheckStatus(ctx context.Context) (*ReplicationStatus, error) {
    var status ReplicationStatus

    // 获取主库状态
    err := m.master.QueryRowContext(ctx, "SHOW MASTER STATUS").Scan(
        &status.MasterFile,
        &status.MasterPosition,
    )
    if err != nil {
        return nil, fmt.Errorf("get master status failed: %v", err)
    }

    // 获取从库状态
    err = m.slave.QueryRowContext(ctx, "SHOW SLAVE STATUS").Scan(
        &status.SlaveIORunning,
        &status.SlaveSQLRunning,
        &status.SecondsBehindMaster,
        &status.LastError,
    )
    if err != nil {
        return nil, fmt.Errorf("get slave status failed: %v", err)
    }

    return &status, nil
}

// 监控服务
type MonitorService struct {
    monitor *ReplicationMonitor
    interval time.Duration
}

func (s *MonitorService) Start(ctx context.Context) {
    ticker := time.NewTicker(s.interval)
    defer ticker.Stop()

    for {
        select {
        case <-ctx.Done():
            return
        case <-ticker.C:
            status, err := s.monitor.CheckStatus(ctx)
            if err != nil {
                log.Printf("check replication status failed: %v", err)
                continue
            }
            
            // 检查延迟
            if status.SecondsBehindMaster > 30 {
                log.Printf("replication lag too high: %d seconds", 
                    status.SecondsBehindMaster)
            }
            
            // 检查复制是否正常运行
            if !status.SlaveIORunning || !status.SlaveSQLRunning {
                log.Printf("replication not running, error: %s", 
                    status.LastError)
            }
        }
    }
}

使用示例

func main() {
    // 初始化连接池
    pool, err := NewDBPool(
        "root:123456@tcp(master:3306)/test",
        []string{
            "root:123456@tcp(slave1:3306)/test",
            "root:123456@tcp(slave2:3306)/test",
        },
    )
    if err != nil {
        log.Fatal(err)
    }

    // 初始化读写分离路由
    router := &DBRouter{
        master: pool.Master,
        slaves: pool.Slaves,
    }

    // 初始化分库分表路由
    shardRouter := &ShardRouter{
        config: ShardConfig{
            DBCount:    2,
            TableCount: 4,
        },
        pools: map[int]*sqlx.DB{
            0: pool.Master,
            1: pool.Slaves[0],
        },
    }

    // 初始化主从复制监控
    monitor := &ReplicationMonitor{
        master: pool.Master,
        slaves: pool.Slaves[0],
    }

    // 启动监控服务
    ctx := context.Background()
    monitorService := &MonitorService{
        monitor:  monitor,
        interval: time.Minute,
    }
    go monitorService.Start(ctx)
}