PolarDB:Create and use GSIs

Definir un GSI al crear la tabla

El siguiente ejemplo crea la tabla t_order con sharding basado en order_id. Además, define g_i_seller como un GSI en línea con sharding basado en seller_id:

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`),
  GLOBAL INDEX `g_i_seller`(`seller_id`) COVERING (`id`, `order_id`, `buyer_id`, `order_snapshot`) dbpartition BY hash(`seller_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 dbpartition BY hash(`order_id`);

Agregar un GSI a una tabla existente

El siguiente ejemplo agrega un GSI único llamado g_i_buyer sobre la columna buyer_id, con sharding tanto de base de datos como de tabla:

CREATE UNIQUE GLOBAL INDEX `g_i_buyer` ON `t_order`(`buyer_id`)
    COVERING(`seller_id`, `order_snapshot`)
    dbpartition BY hash(`buyer_id`) tbpartition BY hash(`buyer_id`) tbpartitions 3

Una vez creado el GSI, PolarDB-X comprueba los datos del índice antes de finalizar la instrucción DDL. Para comprobar o reparar manualmente estos datos, ejecute CHECK GLOBAL INDEX.

Selección automática de índices

PolarDB-X selecciona automáticamente el índice de cobertura de menor costo para las consultas en tablas con GSI. Use EXPLAIN para comprobar qué índice elige el optimizador:

EXPLAIN SELECT t_order.id, t_order.order_snapshot FROM t_order WHERE t_order.seller_id = 's1';

Resultado del plan de ejecución:

IndexScan(tables="g_i_seller_sfL1_2", sql="SELECT `id`, `order_snapshot` FROM `g_i_seller` AS `g_i_seller` WHERE (`seller_id` = ?)")

El optimizador elige g_i_seller porque todas las columnas consultadas (id, order_snapshot y seller_id) están cubiertas por el índice. Además, la condición de igualdad de seller_id coincide con la clave de shard del índice, lo que elimina la búsqueda en la tabla y el examen completo de la partición.

Si una consulta requiere columnas que no están en el índice, PolarDB-X primero busca las claves primarias y las claves de shard de la tabla base en el GSI. Después, obtiene las columnas faltantes de la tabla base. Para más detalles, consulte INDEX HINT .

Forzar un índice específico

Utilice cualquiera de las siguientes sintaxis para anular la selección automática de índices:

FORCE INDEX:

FORCE INDEX({index_name})

Ejemplo:

SELECT a.order_id FROM t_order a FORCE INDEX(g_i_seller) WHERE a.buyer_id = 123;

Sugerencia TDDL:

/*+TDDL:INDEX({table_name/table_alias}, {index_name})*/

Ejemplo:

/*+TDDL:index(a, g_i_buyer)*/ SELECT * FROM t_order a WHERE a.buyer_id = 123

Ignorar o sugerir un índice

IGNORE INDEX: excluye un índice específico de la evaluación.

IGNORE INDEX({index_name},...)

Ejemplo:

SELECT t_order.id, t_order.order_snapshot FROM t_order IGNORE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

USE INDEX: sugiere un índice preferido.

USE INDEX({index_name},...)

Ejemplo:

SELECT t_order.id, t_order.order_snapshot FROM t_order USE INDEX(g_i_seller) WHERE t_order.seller_id = 's1';

Limitaciones al crear GSI

• No es posible crear GSI en tablas sin particiones ni en tablas de difusión.

• Los GSI únicos no son compatibles con índices de prefijo.

• Toda tabla de índice debe tener un nombre explícito.

• Es obligatorio especificar una regla de sharding de base de datos, o bien reglas de sharding de base de datos y de tabla. No se admite especificar únicamente la regla de sharding de tabla.

• Las claves de índice de una tabla de índice deben incluir todas sus claves de shard.

• Una misma columna no puede funcionar simultáneamente como clave de índice y como columna de cobertura.

• De forma predeterminada, una tabla de índice contiene las columnas de clave primaria y todas las columnas de clave de shard de la tabla base. Las columnas que no se definen como claves de índice se utilizan como columnas de cobertura.

• En modo DRDS: si todas las columnas de índice de un índice local de la tabla base están presentes en la tabla de índice, dicho índice local se agrega automáticamente a la tabla de índice.

• Si no existen índices locales en las columnas de clave de índice de un GSI, se crea automáticamente un índice local para cada una de ellas.

• Un GSI creado sobre varias columnas genera automáticamente un índice compuesto que abarca todas las columnas de clave de índice.

• El parámetro Length solo especifica la longitud del prefijo de una clave de sharding para crear un índice local.

Limitaciones de ALTER TABLE

La siguiente matriz muestra qué operaciones de columna de ALTER TABLE son compatibles con las tablas que tienen GSI.

Compatible\*: aplica únicamente para instancias que admiten cambios de tipo de columna sin bloqueo.

Notas adicionales sobre ALTER TABLE:

• Para cambiar el nombre de un GSI, elimínelo con DROP INDEX y vuelva a crearlo. Cambiar el nombre directamente puede degradar su rendimiento. Si necesita asistencia, contact us.

• Si una columna pertenece a varios tipos de columna en la matriz anterior y la operación no es compatible con alguno de ellos, no se podrá ejecutar.

A continuación se muestran las operaciones de gestión de índices mediante ALTER TABLE:

Limitaciones de ALTER GSI TABLE

• No es posible ejecutar instrucciones DDL ni DML directamente sobre un GSI.

• Las instrucciones DML con NODE HINT no pueden actualizar la tabla base y los GSI de forma simultánea.

Otras instrucciones compatibles

Las siguientes instrucciones funcionan en tablas que cuentan con GSI:

¿Por qué aparece el error "Does not support create Global Secondary Index on single or broadcast table"?

Este error indica que la tabla de destino no tiene particiones o es una tabla de difusión, por lo que no admite GSI.

Compruebe cómo se creó la tabla:

• Tabla sin particiones (modo DRDS): se creó en una sola base de datos y sin sharding. Consulte CREATE TABLE (DRDS mode).

• Tabla sin particiones (modo AUTO): se creó con la palabra clave SINGLE. Consulte CREATE TABLE (AUTO mode).

• Tabla de difusión: se creó con la palabra clave BROADCAST. Existe una copia idéntica de la tabla en cada nodo de datos. Consulte CREATE TABLE (DRDS mode) o CREATE TABLE (AUTO mode).

Los GSI solo se pueden crear en tablas con particiones que no sean de difusión.