hibernate - 尽管没有关联，但调用.list()时，SQL每行运行一次

Question

我正在使用grails 2.5.3

我有一个像这样的配置:

grails {
    hibernate {
        cache.queries = true
        cache.use_second_level_cache = true
        cache.use_query_cache = true
        cache.provider_class =  'net.sf.ehcache.hibernate.SingletonEhCacheProvider'

我有类似的域类:

class AuthorizedDevice implements JSONFormat {
    int id
    String authKey = generateAuthKey()
    String owner
    String name
    String permittedUsers
    String userAgent
    Date   lastVisit
    Date   lastInitialized
    String lastUser
    String lastIpAddress
    Date   dateCreated
    boolean enabled = true
    String notes

    static constraints = {
        authKey(blank: false, unique: true, maxSize: 8)
        owner(blank: false, validator: GormValidators.isCorpUserName)
        name(blank: false, unique: 'owner')
        permittedUsers(nullable: true, validator: permittedUsersValidator)
        userAgent(nullable: true, maxSize: 500)
        lastVisit(nullable: true)
        lastInitialized(nullable: true)
        lastUser(nullable: true, maxSize: 50)
        lastIpAddress(nullable: true, maxSize: 50)
        notes(nullable: true, maxSize: 500)
    }

    def auditService
    def afterInsert() {auditService.noteDeviceChange('Created Device', id)}
    def afterUpdate() {auditService.noteDeviceChange('Updated Device', id)}
    def afterDelete() {auditService.noteDeviceChange('Deleted Device', null)} // Not allowed by GUI, but just in case.


    public Object formatForJSON() {
        return [
                id: id,
                authKey: authKey,
                owner: owner,
                name: name,
                permittedUsers: permittedUsers,
                userAgent: userAgent,
                lastVisit: lastVisit,
                lastInitialized: lastInitialized,
                lastUser: lastUser,
                lastIpAddress: lastIpAddress,
                enabled: enabled,
                notes: notes
        ]
    }

    //------------------
    // Implementation
    //------------------
    private String generateAuthKey() {
         ....
    }

    static permittedUsersValidator = {String val, Object obj, Errors errors ->
        if (!val || val.trim().equals('*')) return
        val.split(',').each {
            if (!getCorprUser(it.trim())) {
                errors.rejectValue('permittedUsers', '',
                        "Unknown User ${it}.  Use a comma-delimited list of usernames or  * to indicate all users."
                )
            }
        }
    }

}

我建立一个设备列表，如:

def devices = AuthorizedDevice.list()

我注意到，每次调用AuthorizedDevice.list()时，GORM / hibernate都会对表中的每一行进行一个SQL查询。

在模型字段中，我们没有任何关联，这些关联会生成N + 1个查询。

即使没有关联，有人知道是什么促使这种N + 1行为吗？

第一次调用.list()时，以下SQL仅运行一次:

select
    this_.id as id4_0_,
    this_.version as version4_0_,
    this_.auth_key as auth3_4_0_,
    this_.date_created as date4_4_0_,
    this_.enabled as enabled4_0_,
    this_.last_initialized as last6_4_0_,
    this_.last_ip_address as last7_4_0_,
    this_.last_user as last8_4_0_,
    this_.last_visit as last9_4_0_,
    this_.name as name4_0_,
    this_.notes as notes4_0_,
    this_.owner as owner4_0_,
    this_.permitted_users as permitted13_4_0_,
    this_.user_agent as user14_4_0_ 
from
    authorized_device this_

此后每次调用.list()时，都会对表中的每一行运行此SQL:

select
    authorized0_.id as id4_0_,
    authorized0_.version as version4_0_,
    authorized0_.auth_key as auth3_4_0_,
    authorized0_.date_created as date4_4_0_,
    authorized0_.enabled as enabled4_0_,
    authorized0_.last_initialized as last6_4_0_,
    authorized0_.last_ip_address as last7_4_0_,
    authorized0_.last_user as last8_4_0_,
    authorized0_.last_visit as last9_4_0_,
    authorized0_.name as name4_0_,
    authorized0_.notes as notes4_0_,
    authorized0_.owner as owner4_0_,
    authorized0_.permitted_users as permitted13_4_0_,
    authorized0_.user_agent as user14_4_0_
from
    authorized_device authorized0_
where
    authorized0_.id=?

Answer 1

https://dzone.com/articles/pitfalls-hibernate-second-0

If a query has cached results, it returns a list of entity Id's, that is then resolved against the second level cache. If the entities with those Ids where not configured as cacheable or if they have expired, then a select will hit the database per entity Id.

For example if a cached query returned 1000 entity Ids, and non of those entities where cached in the second level cache, then 1000 selects by Id will be issued against the database.

The solution to this problem is to configure query results expiration to be aligned with the expiration of the entities returned by the query.

在您的情况下，解决方案可能只是添加到AuthorizedDevice:

static mapping = { cache true } 

为了在域类AuthorizedDevice上启用二级缓存(默认情况下，hibernate不启用它)。

因为这似乎是您提供的第一个sql日志的结果:

select
    this_.
...
from

在缓存(查询缓存)中。因此它不会执行两次。在我给出的链接中，解释了结果被缓存为实体ID的列表。但是并不是每个实体的所有数据都在缓存中，只有ids。
然后，在对.list()的另一次调用期间，hibernate将在高速缓存中具有这些id，并尝试在第二级高速缓存中检索相应的实体，但是它失败，然后针对这些ID中的每一个，hibernate进行查询。