ignite系列之8-Ignite索引使用说明

Ignite索引使用说明

1 概述

官方资料地址：https://www.ignite-service.cn/详见：文档-》SQL处理-》3.定义索引 章节

本文章重点说明通过注解方式如何定义和使用索引，并给出配置示例；对于SQL API定义索引请查看上述地址

默认规则：

Ignite会自动为每个缓存的主键和关联键字段创建索引，当在值对象的字段上创建索引时，Ignite会创建一个由索引字段和主键字段组成的组合索引。在SQL的角度，该索引由2列组成：索引列和主键列。

 

2 使用注解配置索引

2.1 一个简单的索引配置示例

 

说明：

1、 @QuerySqlField是Ignite索引注解配置类

2、 上述中类Person名称会被用作表名称；

3、 注解配置说明：

无注解字段                 不是索引字段，也不可查询字段，即在SQL查询中不可访问

@QuerySqlField                               SQL中可查询字段，不会建索引

@QuerySqlField(index = true)                   索引字段

@QuerySqlField(index = true, descending = true)   索引字段，倒序排列（默认为正序排列）

 

2.2 使用示例

Tips： 现有的插件使用方式中，索引类型注册是由插件实现，无需自己注册

定义上述Person类之后，注册缓存配置和使用sql查询示例如下

Jdbc方式查询

 

 

 

2.3 嵌套索引

使用注解，对象的嵌套字段也可以被索引和查询。比如，考虑一个Person对象内部有一个Address对象。

警告

如果在嵌套对象上创建了索引，就不能在这个表上执行UPDATE或者INSERT语句

2.3.1 Person类中嵌套Address类属性

 

Address类：

 

使用示例：

 

注意在SQL语句的WHERE条件中不需要指定address.street，这是因为Address类的字段会被合并到Person中，这样就可以简单地在查询中直接访问Address中的字段。

警告

如果在嵌套对象上创建了索引，就不能在这个表上执行UPDATE或者INSERT语句

 

2.4 组合索引

当查询条件复杂时可以使用多字段索引来加快查询的速度，这时可以用@QuerySqlField.Group注解。如果希望一个字段参与多个组合索引时也可以将多个@QuerySqlField.Group注解加入orderedGroups中。

示例

 

上面的Person类中age字段加入了名为age_salary_idx的组合索引，它的分组序号是0并且降序排列，同一个组合索引中还有一个字段salary,它的分组序号是3并且升序排列。最重要的是salary字段还是一个单列索引(除了orderedGroups声明之外，还加上了index = true)。分组中的order不需要是什么特别的数值，它只是用于分组内的字段排序。

 

2.5使用java实体类或者xml配置索引

官方资料地址：https://www.ignite-service.cn/详见：文档-》SQL处理-》3.定义索引-》3.3.使用查询实体配置索引 章节

 

2.6 QuerySqlField 注解类所有属性

@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD})
public @interface QuerySqlField {
    /**
     * Specifies whether cache should maintain an index for this field or not.
     * Just like with databases, field indexing may require additional overhead
     * during updates, but makes select operations faster.
     * <p>
     * When indexing SPI and indexed field is
     * of type {@code org.locationtech.jts.geom.Geometry} (or any subclass of this class) then Ignite will
     * consider this index as spatial providing performance boost for spatial queries.
     *
     * @return {@code True} if index must be created for this field in database.
     */
    boolean index() default false;

    /**
     * Specifies whether index should be in descending order or not. This property only
     * makes sense if {@link #index()} property is set to {@code true}.
     *
     * @return {@code True} if field index should be in descending order.
     */
    boolean descending() default false;

    /**
     * Specifies whether the specified field can be {@code null}.
     *
     * @return {@code True} if the field is not allowed to accept {@code null} values.
     */
    boolean notNull() default false;

    /**
     * Specifies field precision for variable length types - decimal, string and byte array.
     *
     * @return field precision for variable length types - decimal, string and byte array.
     */
    int precision() default -1;

    /**
     * Specifies scale for a decimal field.
     *
     * @return scale for a decimal field.
     */
    int scale() default -1;

    /**
     * Array of index groups this field belongs to. Groups are used for compound indexes,
     * whenever index should be created on more than one field. All fields within the same
     * group will belong to the same index.
     * <p>
     * Group indexes are needed because SQL engine can utilize only one index per table occurrence in a query.
     * For example if we have two separate indexes on fields {@code a} and {@code b} of type {@code X} then
     * query {@code select * from X where a = ? and b = ?} will use for filtering either index on field {@code a}
     * or {@code b} but not both. For more effective query execution here it is preferable to have a single
     * group index on both fields.
     * <p>
     * For more complex scenarios please refer to {@link QuerySqlField.Group} documentation.
     *
     * @return Array of group names.
     */
    String[] groups() default {};

    /**
     * Array of ordered index groups this field belongs to. For more information please refer to
     * {@linkplain QuerySqlField.Group} documentation.
     *
     * @return Array of ordered group indexes.
     * @see #groups()
     */
    Group[] orderedGroups() default {};

    /**
     * Property name. If not provided then field name will be used.
     *
     * @return Name of property.
     */
    String name() default "";

    /**
     * Index inline size in bytes. When enabled part of indexed value will be placed directly to index pages,
     * thus minimizing data page accesses, thus incraesing query performance.
     * <p>
     * Allowed values:
     * <ul>
     *     <li>{@code -1} (default) - determine inline size automatically (see below)</li>
     *     <li>{@code 0} - index inline is disabled (not recommended)</li>
     *     <li>positive value - fixed index inline</li>
     * </ul>
     * When set to {@code -1}, Ignite will try to detect inline size automatically. It will be no more than
     * {@link CacheConfiguration#getSqlIndexMaxInlineSize()}. Index inline will be enabled for all fixed-length types,
     * but <b>will not be enabled</b> for {@code String}.
     * <p>
     * When index group is used, inline size must be defined in {@link QueryGroupIndex#inlineSize()}. Any value
     * except of {@code -1} defined on a specific column will lead to exception.
     *
     * @return Index inline size in bytes.
     */
    int inlineSize() default QueryIndex.DFLT_INLINE_SIZE;

    /**
     * Describes group of index and position of field in this group.
     * <p>
     * Opposite to {@link #groups()} this annotation gives control over order of fields in a group index.
     * This can be needed in scenarios when we have a query like
     * {@code select * from X where a = ? and b = ? order by b desc}. If we have index {@code (a asc, b asc)}
     * sorting on {@code b} will be performed. Here it is preferable to have index {@code (b desc, a asc)}
     * which will still allow query to search on index using both fields and avoid sorting because index
     * is already sorted in needed way.
     *
     * @see #groups()
     * @see #orderedGroups()
     */
    @Retention(RetentionPolicy.RUNTIME)
    @Target({ElementType.METHOD, ElementType.FIELD})
    @SuppressWarnings("PublicInnerClass")
    public static @interface Group {
        /**
         * Group index name where this field participate.
         *
         * @return Group index name
         */
        String name();

        /**
         * Fields in this group index will be sorted on this attribute.
         *
         * @return Order number.
         */
        int order();

        /**
         * Defines sorting order for this field in group.
         *
         * @return True if field will be in descending order.
         */
        boolean descending() default false;
    }
}

 

 

2.7 .配置索引内联值

索引内联值概念

索引中的每个条目都有恒定的大小，该值是在索引创建期间计算的，称为索引内联值。理想情况下，该值应足以存储序列化形式的整个索引条目。如果值未完全包含在索引中，则Ignite可能需要在索引查找期间执行其他数据页读取，如果启用了持久化，则这可能会降低性能。

 

 

官方资料地址：https://www.ignite-service.cn/详见：文档-》SQL处理-》3.定义索引-》3.4.配置索引内联值 章节