之前曾经写过一篇文章，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.SQL的DEBUG日志级别反馈，参数化查询的参数会通过org.hibernate.type的TRACE级别反馈。

由于 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 我们就可以有针对性的优化代码了。

一般优化方针

不外乎把握两点原则:

• where 条件命中索引
• 如果有 join，应当尽可能缩小需要 join 的结果集

如果当 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条件中对字段使用了函数，会导致不命中索引，降低查询性能。此外对于联合索引，那么特别注意字段顺序，比如假设department和date_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, JPQL和 native SQL.

结语

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