慢查询优化

Ayang 收录于数据库

2024-04-10 约 854 字

接上篇文章，在排查 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