使用MySQL/Tidb数据库的一些经验【缓慢更新中...】

使用MySQL和Tidb作为存储数据库时遇到的一些经验总结

插入

批量插入

  • 拼接sql语句,一次性发送多条(注意拼接的sql大小,数据库一般都有限制sql语句的大小)
REPLACE INTO

主键会被更新,本质上是先删除再新增

INSERT INTO xxx ON DUPLICATE KEY

没有记录则新增,有则在原有行上更新对应列

插入重复数据

package main

import (
    "database/sql"

    "github.com/sirupsen/logrus"
    "gorm.io/driver/mysql"
    "gorm.io/gorm"
    "gorm.io/gorm/schema"
)


type Camera struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"not null;type:varchar(32)"`
}

func main() {

    db, err := gorm.Open(mysql.Open("root:root@tcp(127.0.0.1:3306)/testDB?charset=utf8mb4&parseTime=true&loc=Local"), &gorm.Config{
        NamingStrategy: schema.NamingStrategy{
            SingularTable: false,
        },
    })
    if err != nil {
        return
    }

    db.AutoMigrate(&Camera{})

    ch := make(chan struct{})
    for i := 0; i < 5; i++ {
        go func(index int) {
            db.Debug().Transaction(func(tx *gorm.DB) error {
                ca := []Camera{}
                if tx.Raw("SELECT * FROM cameras WHERE name='camera_TEST'").Scan(&ca).RowsAffected == 0 {
                    return tx.Create(&Camera{
                        Name: "camera_TEST",
                    }).Error
                }
                return nil
            }, &sql.TxOptions{Isolation: sql.LevelRepeatableRead})
            ch <- struct{}{}
        }(i)
    }

    for i := 0; i < 5; i++ {
        <-ch
    }
}

插入的数据没有unique字段和主键,在高并发场景下,只通过先select查询记录是否存在,不存在则insert的事务插入,很大概率会出现插入了重复的数据。解决方法:
1、数据库事务隔离级别提升到串行级别——(基本不考虑
2、给插入的数据建立unique约束(需要处理duplicate entry 错误

type Camera struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"not null;type:varchar(32);uniqueIndex"`
}

3、应用层来解决,例如插入逻辑统一为队列插入;或者加锁,锁的key为unique的字段,如下就是一个简单的加锁示例

type Camera struct {
    ID   uint   `gorm:"primaryKey"`
    Name string `gorm:"not null"`
}

func main() {

    db, err := gorm.Open(mysql.Open("root:root@tcp(127.0.0.1:3306)/testDB?charset=utf8mb4&parseTime=true&loc=Local"), &gorm.Config{
        NamingStrategy: schema.NamingStrategy{
            SingularTable: false,
        },
    })
    if err != nil {
        return
    }

    rdb := redis.NewFailoverClient(&redis.FailoverOptions{
        MasterName:    "mymaster",
        SentinelAddrs: []string{"127.0.0.1:26379"},
    })
    if err := rdb.Ping().Err(); err != nil {
        panic(err)
    }

    db.AutoMigrate(&Camera{})

    ch := make(chan struct{})

    for i := 0; i < 5; i++ {
        go func(index int) {
            defer func() {
                ch <- struct{}{}
            }()

            if lock := rdb.SetNX("c1", "random", 5*time.Second).Val(); !lock {
                return
            }
            db.Debug().Transaction(func(tx *gorm.DB) error {
                ca := []Camera{}

                if tx.Raw("SELECT * FROM cameras WHERE name IN ('c1')").Scan(&ca).RowsAffected == 0 {
                    var cs []Camera
                    cs = append(cs, Camera{Name: "c1"})
                    return tx.Create(&cs).Error
                }
                return nil
            }, &sql.TxOptions{Isolation: sql.LevelRepeatableRead})
            rdb.Del("c1")
        }(i)
    }

    for i := 0; i < 5; i++ {
        <-ch
    }
}

删除

tidb大数据的删除

在删除大量数据的时候,建议使用 Delete from t where xx limit 5000(xx 建议在满足业务过滤逻辑下,尽量加上强过滤索引列或者直接使用主键选定范围,如 id >= 5000n+m and id <= 5000(n+1)+m 这样的方案,通过循环来删除,用 Affected Rows == 0 作为循环结束条件,这样避免遇到事务大小的限制(否则Tidb会报Transaction too large)。如果一次删除的数据量非常大,这种循环的方式会越来越慢,因为每次删除都是从前向后遍历,前面的删除之后,短时间内会残留不少删除标记(后续会被 GC 掉),影响后面的 Delete 语句。如果有可能,建议把 Where 条件细化。例如如下删除一天的数据:

for i from 0 to 23:
    while affected_rows > 0:
        delete * from t where insert_time >= i:00:00 and insert_time < (i+1):00:00 limit 5000;
        affected_rows = select affected_rows()

软删除和硬删除

目前软删除的做法
  • 在表内加 isDeleted bool
  • 在表内添加 deleted_at timestamp (可以为null) (gorm这类orm框架支持,gorm会自动为其加上索引,可以结合业务考虑删除掉此索引)
  • 将删除数据存储到另一个表内 (看过一些软件是这样实现的,感觉效率高,开发稍微费事点)
设计软删除原则
  • 考虑是否一定需要软删除 ,软删除主要是为了数据恢复已经后续统计等,不是真正意义上的删除(例如产品下架)
  • 考虑数据业务量,读和写的比例
  • 读条件针对where条件复杂 并且业务量较大的建议不要使用deleted_at,可以考虑使用做法3

查询

索引

MySQL的索引

mysql的索引,这里主要指innodb,使用b+树作为底层数据结构,主键索引的b+树中,key为主键值,所有的数据都存储于b+树的叶子节点的value中;而其他非主键的索引,也叫作二级索引(辅助索引),其数据结构也是一个b+树,但是叶子节点存储的值为主键值,所以二级索引查询数据分两步,第一步查到主键值,第二步通过主键查找具体的数据

Tidb的索引

tidb索引的底层数据结构为 LSM-Tree

索引的使用
  • 合理使用索引,不要让索引失效,也不要建立过多索引(索引失效的情况很多,不多赘述
  • 合理覆盖索引进行查询,减少二次寻址
  • 表一定要建主键,通过主键查询速度是最快的
  • 索引过多可能会影响优化器对索引的选择,非必要情况下可以强制使用索引查询force index(idx_name)

其他

  • 不需要的列就不用返回了,能不*就不*
  • join尽量从小表驱动大表进行连接查询
  • 能limit就limit,减少不必要的数据返回

更新

for update

对于数据一致性要求比较高的场景下,要使用好排他锁,尽量对主键/unique索引/普通索引所在字段建立条件查询,这样锁的粒度能控制在行级别。

排查分析

慢日志

tidb慢日志
select query_time, query, user
from information_schema.slow_query
where is_internal = false  -- 排除 TiDB 内部的慢查询 SQL
order by query_time desc;

Explain

查看是否使用了索引

优化

针对mysql

  • 分区表
  • 垂直/水平分表
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值