使用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

• 分区表
• 垂直/水平分表