DTeam 团队日志

Doer、Delivery、Dream

以SQL为导向大幅优化Grails GORM性能

冯宇 Posted at — Sep 2, 2018 阅读

之前曾经写过一篇文章,Grails GORM查询总结,介绍了Grails中GORM查询的几种方式。在结语中,我写道:

“虽然在ORM框架中应该尽可能避免使用底层的SQL,因为这会在一定程度上破坏框架的封装性,并且使用不当也会有SQL注入的风险。但是作为开发者,实际最应该熟练掌握的反而是最底层的SQL”

今天就针对这个问题谈一谈我之前的观点,为什么需要熟练掌握SQL反而更重要。

ORM与SQL

ORM框架提供了一种很方便的对象和数据库绑定的功能,使得开发者不需要关心底层的数据库操作,简单的通过Java对象的操作就可以很方便的修改数据库了。为了研究ORM都做了哪些操作,我们有必要把SQL语句打印出来。在logback.groovy文件中添加以下的内容:

final boolean DEBUG_SQL = System.getenv('DEBUG_SQL') as boolean
if (DEBUG_SQL) {
    logger('org.hibernate.SQL', DEBUG)
    logger('org.hibernate.type', TRACE)
} else {
    logger('org.hibernate.SQL', WARN)
}

hibernate的SQL会通过org.hibernate.SQLDEBUG日志级别反馈,参数化查询的参数会通过org.hibernate.typeTRACE级别反馈。

由于hibernate的SQL日志会相当的多,大部分情况下我们可能并不需要看到这么底层的SQL,所以在logback.groovy配置中我们加入了DEBUG_SQL这个环境变量判断,决定是否启用SQL日志打印。终端使用:

# bash环境
DEBUG_SQL=true ./grailsw <task>
# windows cmd终端
set DEBUG_SQL = true
gradlew <task>
# windows powershell终端
$Env:DEBUG_SQL=true
.\gradlew <task>

这种方式运行grails就可以看到hibernate底层的SQL都做了哪些操作。基本上每个ORM都会生成类似于select * from domain where (subquery)这样的SQL,如果有外键关联还会执行select其他表的操作,如果有hasMany还会有join操作。通过了解这些底层的SQL我们就可以有针对性的优化代码了。

一般优化方针

不外乎把握两点原则:

如果当domain中有where条件查询时,应当增加索引。这个在grails的domain文档就有配置方法。另外还有一点需要注意的是,对字段使用函数会导致不命中索引,降低效率。比如我们要查询一个月的数据:

-- 这个查询命中date_created的索引,应该使用这个
WHERE date_created BETWEEN '2018-08-01' AND '2018-09-01';

-- 这个查询会导致date_created索引失效,不建议使用
WHERE date_trunc('month', date_created) = '2018-08-01';

这两个SQL返回结果完全一样,但是下面的SQL在WHERE条件中对字段使用了函数,会导致不命中索引,降低查询性能。此外对于联合索引,那么特别注意字段顺序,比如假设departmentdate_created两个字段有联合索引:

Person.where {
    department == department && dateCreated >= LocalDateTime.now().minusMonth(1)
}

那么上述查询特别注意联合索引的顺序,错误的顺序也会导致索引失效。

对于联合查询(可能产生的SQL中会包含join)也一样,尽可能在where条件中缩小结果集,而不是在查询结果之后再做一次过滤(尽可能使用WHERE条件过滤而不是在HAVING中过滤)。

Bulk操作优化

比如我们要按部门对每个员工生成一个考勤记录表,如果基于GORM实现,很容易想到这样的实现方式:

void genAttendance(List<Department> departmentList) {
    departmentList.each { Department department ->
        List<Person> personList = Person.findByDepartment(department)

        personList.each { Person person ->
            new Attendance(person: person, department: department).save()
        }
    }
}

完全基于ORM的方式来做,代码清晰易懂,没有使用任何SQL。但是当数据量只要一大就很容易发现问题——这里执行效率非常慢。实际测试,当这个person的总量达到一万数量级时,整个方法执行耗时将近3分钟!

那我们有没有办法优化呢?首先可以想到的是transaction bulk批量递交的方案,由之前的每一次save执行一次insert操作,改成一批save之后再insert,不就可以了吗?不过Grails官方并没有这样的参考范例,我在网上找到了一个别人实现的方案,用底层的hibernate的session api处理,具体参考文章在此: http://krixisolutions.com/bulk-insert-grails-gorm/

于是我们参考上述文章实现一个transaction bulk提交的写法:

// 每1000条insert提交一次
int bulk = 1000
Attendance.withSession { Session session ->
        int count = 0
        departmentList.each { Department department ->
            List<Person> personList = Person.findByDepartment(department)

            personList.each { Person person ->
                new Attendance(person: person, department: department).save()
                count++

                if (count % bulk == 0) {
                    session.flush()
                    session.clear()
                }
            }
        }
    }
}

通过手工控制session,我们实现了每1000条记录flush一次session。再次跑一下这个方法,发现10000条记录的插入速度提高到了45秒,速度已经提升了近4倍。还有没有优化空间呢?有!

通过DEBUG_SQL打印一下执行的SQL,我们会发现尽管我们采用了批量提交的方案,但是hibernate产生的SQL可一点没少,每一个save语句大概会执行以下两条SQL:

select * from person where id = xxx;
insert into attendance (person_id, department_id) values(xx, xx);

由于我们在循环save中打开了多个ORM,因此每次insert之前还会伴随相关的select,这么多sql即使我们用批量commit的方式依旧觉得并不快。所以我们进一步往底层考虑——SQL还有批量操作的方案!

-- 一把插入多条自定义的记录
INSERT INTO table VALUES (),(),()...;

-- 从子查询产生的结果批量插入table
INSERT INTO table subquery;

进一步以SQL为导向优化,很容易想到SQL还有以上两种批量插入的方案。使用更底层的SQL我们需要动用Hibernate的HQL接口或者Native SQL接口。我们的场景正好可以用第二种,而HQL接口只支持第二种方案。于是改用HQL方案实现:

Attendance.executeUpdate('''
    INSERT INTO Attendance (person, department)
    SELECT person, person.department
    FROM Person person
    WHERE person.department.id IN :departmentListId
''', [departmentListId: departmentList.collect { it.id }])

再跑一次,于是乎10000条记录400多毫秒执行完毕,相比之前的45秒提升了近百倍速度!

再看一个例子,比如现在想禁用一个部门,并且禁用部门下所有的用户:

List<Person> personList = Person.findByDepartment(department)
personList.each { Person person ->
    person.enable = false
    person.save()
}

同样以SQL为导向进行优化,上一种方案我们等于是每个person对象都执行一次update语句,我们完全可以一条update语句将所有符合条件的person全部禁用,这个需求用criteria就可以实现:

Persion.where {
    department == department
}.updateAll([enable: false])

这次再打印SQL日志就会发现只产生一条update语句。

criteria是Hibernate的type-safe的查询方案,官方鼓励尽可能使用criteria代替HQL, JPQLnative SQL.

结语

本篇文章我们用实际项目中应用举例,列举了以SQL为导向优化GORM查询方案,将带来惊人的效果。所以尽管各种ORM框架可以帮助我们减少手写SQL,但是如果熟练掌握SQL将带来不一样的天地。