CREATE INDEX (modo AUTO) - PolarDB - Centro de documentación de Alibaba Cloud

PolarDB-X permite crear índices locales e índices secundarios globales (GSI) en bases de datos en modo AUTO. Utilice CREATE INDEX para los índices locales y CREATE GLOBAL INDEX para agregar un GSI a una tabla existente.

Índices locales

Los índices locales siguen la sintaxis estándar de MySQL. Consulte Instrucción CREATE INDEX en el manual de referencia de MySQL 8.0.

GSI

Un índice secundario global (GSI) almacena los datos del índice en una tabla de índices separada y particionada. A diferencia de un índice local, un GSI abarca todas las particiones de la tabla base. Por lo tanto, las consultas sobre columnas que no son clave de partición evitan los escaneos completos de la tabla.

Cuándo usar un GSI:

Las consultas filtran con frecuencia por columnas que no corresponden a la clave de partición de la tabla base.
El rendimiento de las consultas se degrada porque la base de datos debe escanear todas las particiones.

Cuándo basta con un índice local:

Las consultas siempre filtran por la clave de partición o por un prefijo de esta.
El rendimiento de escritura es prioritario y resulta inaceptable la sobrecarga de mantener una tabla de índices separada.

Para consultar la lista completa de límites de los GSI, consulte How to use global secondary indexes.

Notas de uso

Para utilizar las funciones de particionamiento de nivel 2 en un GSI, su instancia de PolarDB-X debe tener la versión 5.4.17-16952556 o posterior.

Sintaxis

CREATE [UNIQUE]
    GLOBAL INDEX index_name [index_type]
    ON tbl_name (index_sharding_col_name, ...)
    global_secondary_index_option
    [index_option]
    [algorithm_option | lock_option] ...

global_secondary_index_option:

[COVERING (col_name, ...)]
[partition_options]
[VISIBLE | INVISIBLE]

partition_options:

partition_columns_definition
[subpartition_columns_definition]
[subpartition_specs_definition]
partition_specs_definition

partition_columns_definition — clave de partición de nivel 1:

PARTITION BY
      HASH({column_name | partition_func(column_name)}) PARTITIONS partition_count
    | KEY(column_list) PARTITIONS partition_count
    | RANGE({column_name | partition_func(column_name)})
    | RANGE COLUMNS(column_list)
    | LIST({column_name | partition_func(column_name)})
    | LIST COLUMNS(column_list)

subpartition_columns_definition — clave de partición de nivel 2:

SUBPARTITION BY
      HASH({column_name | partition_func(column_name)}) SUBPARTITIONS partition_count
    | KEY(column_list) SUBPARTITIONS partition_count
    | RANGE({column_name | partition_func(column_name)})
    | RANGE COLUMNS(column_list)
    | LIST({column_name | partition_func(column_name)})
    | LIST COLUMNS(column_list)

Funciones de particionamiento compatibles:

YEAR, TO_DAYS, TO_MONTHS, TO_WEEKS, TO_SECOND, UNIX_TIMESTAMP, MONTH, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, SUBSTR, SUBSTRING

partition_specs_definition — especificaciones de partición de nivel 1:

partition_specs_definition:
    hash_partition_list
  | range_partition_list
  | list_partition_list

hash_partition_list:
    ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options]
  | PARTITION partition_name SUBPARTITIONS partition_count [subpartition_specs_definition]

hash_subpartition_list:
    ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
    SUBPARTITION subpartition_name [partition_spec_options]

range_partition_list:
    ( range_partition [, range_partition, ...] )

range_partition:
    PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]
  | PARTITION partition_name VALUES LESS THAN (range_bound_value) [SUBPARTITIONS partition_count] [subpartition_specs_definition]

range_subpartition_list:
    ( range_subpartition [, range_subpartition, ...] )

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]

range_bound_value:
    maxvalue
  | expr
  | value_list

list_partition_list:
    ( list_partition [, list_partition, ...] )

list_partition:
    PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options]
  | PARTITION partition_name VALUES IN (list_bound_value) [SUBPARTITIONS partition_count] [subpartition_specs_definition]

list_subpartition_list:
    ( list_subpartition [, list_subpartition, ...] )

list_subpartition:
    SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]

list_bound_value:
    default
  | value_set

value_set:
    value_list
  | (value_list) [, (value_list), ...]

value_list:
    value [, value, ...]

subpartition_specs_definition — especificaciones de partición de nivel 2:

subpartition_specs_definition:
    hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

partition_spec_options:

[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
[LOCALITY [=] 'dn=storage_inst_id_list']

table_option:

[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
[{CHARSET | CHARACTER SET} [=] charset]
[COLLATE [=] collation]
[TABLEGROUP [=] table_group_id]
[LOCALITY [=] 'dn=storage_inst_id_list']

local_partition_definition:

LOCAL PARTITION BY RANGE (column_name)
[STARTWITH 'yyyy-MM-dd']
INTERVAL interval_count [YEAR | MONTH | DAY]
[EXPIRE AFTER expire_after_count]
[PRE ALLOCATE pre_allocate_count]
[PIVOTDATE pivotdate_func]
[DISABLE SCHEDULE]

pivotdate_func:
    NOW()
  | DATE_ADD(...)
  | DATE_SUB(...)

Parámetros

Parámetro	Obligatorio	Descripción
`UNIQUE`	No	Aplica la restricción de unicidad en las columnas indexadas.
`index_name`	Sí	Nombre del GSI. Debe ser único dentro de la tabla.
`index_type`	No	Tipo de índice, como `USING BTREE`.
`tbl_name`	Sí	Nombre de la tabla base que se va a indexar.
`index_sharding_col_name`	Sí	Columnas de la clave de partición para la tabla de índices. Las consultas que filtran por estas columnas se enrutan directamente a las particiones de índice correspondientes.
`COVERING (col_name, ...)`	No	Columnas adicionales que se almacenan en la tabla de índices. Permite resolver consultas de cobertura sin leer la tabla base. La clave primaria y la clave de fragmentación de la tabla base siempre se incluyen como columnas de cobertura predeterminadas.
`PARTITION BY`	No	Estrategia de particionamiento para la tabla de índices. Es compatible con `HASH`, `KEY`, `RANGE`, `RANGE COLUMNS`, `LIST` y `LIST COLUMNS`.
`SUBPARTITION BY`	No	Estrategia de particionamiento de nivel 2. Requiere la versión 5.4.17-16952556 o posterior de la instancia.
`VISIBLE` / `INVISIBLE`	No	Controla si el optimizador utiliza el índice. El valor predeterminado es `VISIBLE`.
`LOCALITY`	No	Vincula las particiones a nodos de almacenamiento específicos. Formato: `'dn=storage_inst_id_list'`.

Ejemplo

A continuación se muestra un ejemplo donde se crea una tabla, se agrega un GSI en la columna seller_id y luego se comprueba el resultado.

Paso 1: crear la tabla base

CREATE TABLE t_order (
  `id`             bigint(11)   NOT NULL AUTO_INCREMENT,
  `order_id`       varchar(20)  DEFAULT NULL,
  `buyer_id`       varchar(20)  DEFAULT NULL,
  `seller_id`      varchar(20)  DEFAULT NULL,
  `order_snapshot` longtext     DEFAULT NULL,
  `order_detail`   longtext     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY HASH(`order_id`) PARTITIONS 16;

La tabla base se particiona mediante hash en order_id con 16 particiones.

Paso 2: crear el GSI

CREATE GLOBAL INDEX `g_i_seller`
    ON t_order (`seller_id`)
    COVERING (`order_snapshot`)
    PARTITION BY HASH(`seller_id`) PARTITIONS 16;

Este comando crea la tabla de índices g_i_seller, la cual presenta las siguientes características:

Se particiona por seller_id (la clave de partición del índice).
Almacena order_snapshot como columna de cobertura. Así, las consultas sobre seller_id que también requieren order_snapshot no necesitan consultar la tabla base.
Incluye automáticamente id (clave primaria) y order_id (clave de fragmentación de la tabla base) como columnas de cobertura predeterminadas.

Paso 3: comprobar los índices locales

Ejecute SHOW INDEX para consultar los índices locales de la tabla base. Para obtener más información, consulte SHOW INDEX.

SHOW INDEX FROM t_order;

Salida de ejemplo:

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_order_****_00000 |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order_****_00000 |          1 | l_i_order |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

La salida muestra la clave primaria en id y el índice local l_i_order en order_id. Los GSI no aparecen aquí.

Paso 4: comprobar el GSI

Ejecute SHOW GLOBAL INDEX para consultar los GSI. Para obtener más información, consulte SHOW GLOBAL INDEX.

SHOW GLOBAL INDEX FROM t_order;

Salida de ejemplo:

+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA              | TABLE   | NON_UNIQUE | KEY_NAME   | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| ZZY3_DRDS_LOCAL_APP | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

La columna STATUS muestra PUBLIC, lo que significa que el GSI está activo y visible para el optimizador. La columna COVERING_NAMES confirma que id y order_id se incluyen de forma automática.

Paso 5: inspeccionar la estructura de la tabla de índices

SHOW CREATE TABLE g_i_seller;

Salida de ejemplo:

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                  |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_seller | CREATE TABLE `g_i_seller` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(`seller_id`) partitions 16 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

La tabla de índices incluye lo siguiente:

id: clave primaria de la tabla base (sin AUTO_INCREMENT).
order_id: clave de fragmentación de la tabla base (columna de cobertura predeterminada).
seller_id: clave de partición de la tabla de índices.

El índice local l_i_order de la tabla base no se copia a la tabla de índices.

Pasos siguientes

GSI: conozca la arquitectura y el comportamiento interno de los GSI.
How to use global secondary indexes: consulte la lista completa de límites y directrices de uso de los GSI.
CREATE TABLE (DRDS mode): consulte la referencia de sintaxis de las cláusulas de GSI para CREATE TABLE.