记录实战开发中第一次优化数据库查询

背景介绍

前几天，团队里的骨干找到我，说在合代码的时候review了一下我的代码，发现有几个接口写的有问题，并告诉我是数据库查询相关的性能问题，于是我开始排查我写的sql

我负责的是后台管理模块，主要就是几个crud，不过还有一个数据看板，其中有几个关键数据，一个是查询日期区间内每天用户注册数量，一个是查询日期区间每天活跃群数量，背后的查询都很简单，一个是统计日期区间内用户表每天新增的行数，一个是统计日期区间内消息表有每天有几个群出现：

// 查询某个区间的注册数量
func (d *DB) queryRegisterCountWithDateSpace(startDate, endDate string) ([]*Model, error) {
	var models []*Model
	_, err := d.session.Select("*").From("user").Where("date_format(created_at,'%Y-%m-%d')>=? and date_format(created_at,'%Y-%m-%d')<=? and user_type=0", startDate, endDate).Load(&models)
	return models, err
}

// 按小时分布查询活跃群组
func (m *managerDB) queryActiveGroupCountByHour(date string) (map[string]int64, error) {
	// 直接查询所有分表，使用UNION ALL
	tableCount := m.ctx.GetConfig().TablePartitionConfig.MessageTableCount
	var unionQueries []string
	var args []interface{}

	for i := 0; i < tableCount; i++ {
		tableName := m.getTableName(i)
		unionQueries = append(unionQueries, fmt.Sprintf("SELECT DISTINCT channel_id, HOUR(created_at) as hour FROM %s WHERE DATE(created_at) = ? AND is_deleted = 0 AND channel_type = 2", tableName))
		args = append(args, date)
	}

	query := strings.Join(unionQueries, " UNION ALL ")

	var results []struct {
		ChannelID string `db:"channel_id"`
		Hour      int    `db:"hour"`
	}
	_, err := m.session.SelectBySql(query, args...).Load(&results)

在正式环境，这两个函数对应的接口甚至跑到了10s的时间量级，于是我开始着手优化。

第一次优化

第一次我简单看了一下，首先想到的是避免使用select (*)，避免全表扫描。因为业务其实只需要注册的数量，并且这个查询只负责把这个日期区间内所有的记录查出来，统计和按照分钟/小时/天分组是在业务层处理的，同时处理只需要create_at字段，因此我将这个sql修改为：

_, err := d.session.Select("created_at").From("user").Where("date_format(created_at,'%Y-%m-%d')>=? and date_format(created_at,'%Y-%m-%d')<=? and user_type=0", startDate, endDate).Load(&models)

这确实显著提高了查询速度（约1倍）从5s左右骤降到3s,但是对于这么简单的业务来说，还是太慢了，于是有了第二次。

重头戏优化

对的，事实上两个接口都有性能问题，那另一个也没有全表扫啊？为什么也这么慢？我一开始是以为因为生产环境的消息表实在太大了（4个分表，每个表500w行左右）每天约有五十万级数据，如果要查一周的活跃群组，就要扫百万级的数据，而数据库的服务器性能只是16核，慢也是合理的，但是我仔细一看，不对，注意看查询的日期约束date_format(created_at,'%Y-%m-%d') 和 DATE(created_at) = ? 这不是教科书级别的索引失效吗？于是我修改为:

// 查询活跃群数量 - 按小时统计
func (m *managerDB) queryActiveGroupCountByHour(date string) ([]ActiveGroupHourResult, error) {
    // 构造日期范围：避免使用DATE()函数以利用索引
    startTime := date + " 00:00:00"
    endTime := date + " 23:59:59"

    // 直接查询所有分表，使用UNION ALL
    tableCount := m.ctx.GetConfig().TablePartitionConfig.MessageTableCount
    var unionQueries []string
    var args []interface{}

    for i := 0; i < tableCount; i++ {
       tableName := m.getTableName(i)
       // 使用范围查询而不是DATE()函数，利用created_at索引
       unionQueries = append(unionQueries, fmt.Sprintf("SELECT DISTINCT channel_id, HOUR(created_at) as hour FROM %s WHERE created_at >= ? AND created_at <= ? AND is_deleted = 0 AND channel_type = 2", tableName))
       args = append(args, startTime, endTime)
    }

// 查询某个区间的注册数量
func (d *DB) queryRegisterCountWithDateSpace(startDate, endDate string) ([]*StatModel, error) {
	// 优化：使用范围查询而不是 date_format() 函数，以利用 created_at 索引
	startTime := startDate + " 00:00:00"
	endTime := endDate + " 23:59:59"

	var models []*StatModel
	_, err := d.session.Select("created_at").From("user").Where("created_at >= ? and created_at <= ? and user_type=0", startTime, endTime).Load(&models)
	return models, err
}

至此，这两个接口现在已经重新回到了500毫秒级别的相应。

我不是没有学过数据库，也不是没有看过相关调优的案例，但是这种代码就是被我写出来了。我赶紧排查了一下，发现原代码中存在多处这种索引失效的情况，我也明白了为什么我会写出这种代码。这份代码在我接手之前，大部分查询就是使用的这种写法，而我当时用的是vibe coding+review的形式，我让ai参考以前的代码风格，就导致了这种结果。而我虽然有这方面的知识，但毕竟从来没有线上经验，自然也没有关注到