使用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
order by query_time desc;
Explain
查看是否使用了索引
优化
针对mysql
|