之前曾经写过一篇文章，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，应当尽可能缩小需要jion的结果集

如果当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将带来不一样的天地。