如何在JPA实体中定义非聚集索引INCLUDE子句？

我有如下JPA实体：

@Entity
@Table(indexes = {
        @Index(name = "myModel_name_index", columnList = "name")
})
public class MyModel implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    private boolean active;
    @Column(unique = true,nullable = false)
    private String value;
    @Column(nullable = false)
    private String name;
    private String description;
    private BigDecimal price;
}

当Spring Data JPA向Microsoft SQL Server发送查询select count(*) from myModell where name='anything';时，myModel_name_index索引会被正常使用。
但发送查询select name, active, description,price from myModell where name='anything';时，该索引不会被使用！
执行计划显示需创建包含INCLUDE子句的索引：

create nonclustered index myModel_name_index on myModell(name) include(active, description,price)

请问如何在JPA实体中定义该INCLUDE子句？有哪些可行方案？

方案1：使用Hibernate扩展注解（推荐） #

Hibernate从5.2版本开始，为@Index注解提供了include属性，可直接指定要包含的列，适配SQL Server的INCLUDE子句。修改实体类的索引定义即可：

@Entity
@Table(indexes = {
        @Index(name = "myModel_name_index", columnList = "name", include = {"active", "description", "price"})
})
public class MyModel implements Serializable {
    // 实体属性保持不变
}

注意：这是Hibernate特有的扩展，若切换到其他JPA实现（如EclipseLink），该属性会失效。

方案2：使用原生SQL脚本初始化索引 #

若不想依赖Hibernate扩展，可通过原生SQL脚本在应用启动时创建索引：

1. 在src/main/resources下创建SQL脚本（如schema.sql），写入索引创建语句：

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = 'myModel_name_index' AND object_id = OBJECT_ID('myModell'))
BEGIN
    create nonclustered index myModel_name_index on myModell(name) include(active, description,price);
END

2. 在application.properties（或application.yml）中配置Spring Boot自动执行脚本：

spring.sql.init.mode=always
spring.sql.init.schema-locations=classpath:schema.sql

添加判断逻辑是为了避免重复创建索引导致报错。

方案3：使用ORM XML映射文件 #

若偏好XML配置而非注解，可创建META-INF/orm.xml映射文件定义索引：

<?xml version="1.0" encoding="UTF-8"?>
<entity-mappings xmlns="http://xmlns.jcp.org/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence/orm http://xmlns.jcp.org/xml/ns/persistence/orm_2_2.xsd"
                 version="2.2">
    <entity class="com.yourpackage.MyModel">
        <table name="myModell">
            <index name="myModel_name_index" column-list="name">
                <include column="active"/>
                <include column="description"/>
                <include column="price"/>
            </index>
        </table>
    </entity>
</entity-mappings>

之后在persistence.xml或Spring配置中指定该映射文件的位置即可。

内容的提问来源于stack exchange，提问作者István Scheer