接上篇文章,在排查 OOM 时,侵权规则服务需要将几乎整张表的数据加载到内存中,会产生慢查询,这篇文章来讲解下如何解决
慢查询情况
慢查询 SQL 如下:
SELECT * FROM video_tort_rule_property WHERE state = ?;
video_tort_rule_property 表如下:
已建立的索引如下:
explain 如下:
很明显在聚簇索引进行全表扫描了,大概平均两百多毫秒(线上业务数据库慢查询阈值 100 ms),实际还需要加上网络传输时间,所以大于两百多毫秒
解决方法
无效的方法:
由于 where 中有一个 state = ?
,你可能立刻想到对 state 建立索引来优化慢查询,但这种方法其实是无效的,由于 state 区分度不高,而根据二级索引查找后还需要再回表,带来更高的性能损耗,在 InnoDB 的优化器优化过后估计还是会走聚簇索引进行全表扫描
慢查询优化思路
由一次的全表获取变成多次的获取
注意千万不能使用 SELECT * FROM video_tort_rule_property WHERE state = ? order by id limit (?, ?);
因为众所周知,limit 深度分页也会造成性能问题,所以必须要通过 id 作为游标来实现分批的效果
所以优化后的 SQL 如下
SELECT * FROM video_tort_rule_property WHERE state = ? AND id > ? AND id <= ?
代码如下:
注意:我看代码里没有开启 goroutine 并发获取,其实我认为可以开启 goroutine 去并发执行批量获取的 SQL,因为其实客户端和 MySQL 服务端是有连接池的,网络 IO 可以明显提高(磁盘 IO 应该无法提高把)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
|
// 优化根据state状态获取规则ip
func (d *Dao) GetVideoTortRulePropertyByState(ctx context.Context, state dm.RuleStateEnum) (reply []*dm.VideoTortRuleProperty, err error) {
reply = make([]*dm.VideoTortRuleProperty, 0, 100000)
var (
startId, endId int64
lastProperty = new(dm.VideoTortRuleProperty)
// 每次限制获取 4000 行,由于状态过滤的原因,可能实际少于 4 k 行
limit = int64(4000)
)
orm := apmgorm.WithContext(ctx, d.videoOrm)
// 降序获取该状态下最后一条数据的 id
if err = orm.Where("state = ?", state).Order("id desc").First(lastProperty).Error; err != nil {
if err == gorm.ErrRecordNotFound {
err = nil
return
}
}
endId = lastProperty.Id
for {
// 计算起始 id
startId = int64(0)
if endId >= limit {
startId = endId - limit
}
var (
where []string
params []interface{}
currentProperties []*dm.VideoTortRuleProperty
)
// 根据主键 id 来实现游标获取
where = append(where, " id > ? AND id <= ?")
params = append(params, startId, endId)
where = append(where, "state = ?")
params = append(params, state)
wheres := strings.Join(where, " AND ")
if currentProperties, err = d.VideoTortRuleProperties(ctx, 0, 0, "id ASC", wheres, params...); err != nil {
return
}
if len(currentProperties) > 0 {
reply = append(reply, currentProperties...)
}
if startId <= 0 {
break
}
endId = startId
}
return
}
func (d *Dao) VideoTortRuleProperties(c context.Context, offset, limit int, orderby, where string, params ...interface{}) (res []*dm.VideoTortRuleProperty, err error) {
orm := apmgorm.WithContext(c, d.videoOrm)
orm = orm.Where(where, params...)
if orderby == "" {
orderby = "id desc"
}
orm = orm.Order(orderby)
if limit > 0 {
orm = orm.Limit(limit)
}
if offset > 0 {
orm = orm.Offset(offset)
}
if err = orm.Find(&res).Error; err != nil {
return
}
return
}
|
End