Sintaxis DDL para IMCI a nivel de base de datos y de tabla - PolarDB

Utilice las instrucciones DDL de esta página para crear y eliminar In-Memory Column Indexes (IMCI) a nivel de tabla en clústeres de PolarDB for MySQL.

Requisitos de versión

El clúster debe ejecutar una de las siguientes versiones:

PolarDB for MySQL 8.0.1, versión de revisión 8.0.1.1.45 o posterior
PolarDB for MySQL 8.0.2, versión de revisión 8.0.2.2.27 o posterior

Comandos para una sola tabla

Crear un IMCI a nivel de tabla

CREATE COLUMNAR INDEX ON <db_name>.<table_name>;
CREATE COLUMNAR INDEX ON <table_name>;

Si se omite db_name, la instrucción se aplica a la base de datos de la sesión actual. A diferencia de un índice secundario, no es necesario especificar un nombre de índice ni las columnas incluidas; el IMCI abarca automáticamente todas las columnas.

Para evitar un error cuando la tabla ya cuenta con un IMCI, agregue IF NOT EXISTS antes de ON:

CREATE COLUMNAR INDEX IF NOT EXISTS ON <db_name>.<table_name>;

De forma predeterminada, esta instrucción equivale a lo siguiente:

ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=1 <OLD_COMMENT>';

La equivalencia anterior solo aplica cuando allow_implicit_imci_alter_comment está establecido en ON (valor predeterminado). Si se establece en OFF , el IMCI se crea sin modificar el comentario de la tabla. Para más detalles, consulte Crear un IMCI a nivel de tabla sin modificar los comentarios de la tabla .

Para configurar un IMCI personalizado en lugar de incluir todas las columnas, consulte Usar los atributos extendidos de un IMCI para personalizarlo .

Comprobar el estado de creación del IMCI

Después de crear un IMCI, compruebe si está listo mediante la ejecución del siguiente comando:

SHOW IMCI INDEXES;

El campo STATE indica el estado actual:

Estado	Descripción
`COMMITTED`	El IMCI está listo y disponible para las consultas.
`RECOVERING`	El IMCI se está construyendo a partir de los datos existentes.
`RECOVER_BUILDING`	El IMCI se está reconstruyendo después de un evento de recuperación.

Eliminar un IMCI a nivel de tabla

DROP COLUMNAR INDEX ON <db_name>.<table_name>;
DROP COLUMNAR INDEX ON <table_name>;

Si se omite db_name, la instrucción se aplica a la base de datos de la sesión actual. No es necesario especificar el nombre del índice.

Para evitar un error cuando el IMCI ya se eliminó, agregue IF EXISTS antes de ON:

DROP COLUMNAR INDEX IF EXISTS ON <db_name>.<table_name>;

Esta instrucción equivale a ejecutar las dos siguientes:

ALTER TABLE <db_name>.<table_name> COMMENT 'COLUMNAR=0 <OLD_COMMENT>';
ALTER TABLE <db_name>.<table_name> COMMENT '<OLD_COMMENT>';

Operaciones por lotes

Utilice las siguientes instrucciones para crear o eliminar IMCI en todas las tablas de una base de datos de forma simultánea.

Crear IMCI a nivel de tabla por lotes

Se admiten tres opciones de sintaxis equivalentes:

-- Option 1
CREATE COLUMNAR INDEX FOR TABLES IN <db_name>;

-- Option 2
CREATE COLUMNAR INDEX FOR TABLES FROM <db_name>;

-- Option 3: stored procedure (dbms_imci package)
-- dbms_imci: database package for managing IMCI operations
-- add_columnar_index: creates IMCIs for all tables in the specified database
CALL dbms_imci.add_columnar_index('<db_name>');

Las tres opciones crean un IMCI a nivel de tabla para cada tabla de <db_name> que aún no cuente con uno.

El procedimiento almacenado acepta el parámetro opcional set_implicit. Establézcalo en 1 para agregar el marcador implicit_imci a la base de datos. Esto hace que las nuevas tablas creadas en dicha base de datos reciban automáticamente un IMCI:

CALL dbms_imci.add_columnar_index('<db_name>', 1);

Ejemplo

A continuación se muestra cómo crear IMCI para todas las tablas de la base de datos tpch y comprobar el resultado:

mysql> SHOW TABLES IN tpch;
+--------------------+
| Tables_in_tpch     |
+--------------------+
| customer           |
| lineitem           |
| nation             |
| orders             |
| part               |
| partsupp           |
| region             |
| revenue0           |
| supplier           |
+--------------------+
9 rows in set (0.01 sec)

mysql> SHOW IMCI INDEXES;
Empty set (0.02 sec)

mysql> CREATE COLUMNAR INDEX FOR TABLES IN tpch;
+------------+--------+
| Table_Name | Result |
+------------+--------+
| customer   | Ok     |
| lineitem   | Ok     |
| nation     | Ok     |
| orders     | Ok     |
| part       | Ok     |
| partsupp   | Ok     |
| region     | Ok     |
| supplier   | Ok     |
+------------+--------+
8 rows in set (0.56 sec)

-- Check the STATE field. COMMITTED means the IMCI is ready.
-- RECOVERING and RECOVER_BUILDING mean it is still being built.
mysql> SHOW IMCI INDEXES;
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID  | STATE            | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536   | COMMITTED        | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 0         | 65536     | 0       | RECOVERING       | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 64        | 65536     | 4194304 | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 0         | 65536     | 0       | RECOVER_BUILDING | 2024-09-24 18:50:15 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+---------+------------------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (0.07 sec)

Eliminar IMCI a nivel de tabla por lotes

Se admiten tres opciones de sintaxis equivalentes:

-- Option 1
DROP COLUMNAR INDEX FOR TABLES IN <db_name>;

-- Option 2
DROP COLUMNAR INDEX FOR TABLES FROM <db_name>;

-- Option 3: stored procedure (dbms_imci package)
-- dbms_imci: database package for managing IMCI operations
-- drop_columnar_index: deletes IMCIs from all tables in the specified database
CALL dbms_imci.drop_columnar_index('<db_name>');

Las tres opciones eliminan los IMCI a nivel de tabla existentes en cada tabla de <db_name>.

El procedimiento almacenado acepta el parámetro opcional reset_implicit. Establézcalo en 1 para quitar el marcador implicit_imci de la base de datos. De este modo, las nuevas tablas dejarán de recibir un IMCI de forma automática:

CALL dbms_imci.drop_columnar_index('<db_name>', 1);

Ejemplo

mysql> SHOW IMCI INDEXES;
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| TABLE_ID | SCHEMA_NAME | TABLE_NAME | NUM_COLS | NUM_PACKS | PACK_SIZE | ROW_ID    | STATE     | STATE_UPDATE_AT     | CHECKPOINT_DATADIR          | WRITE_POLICY |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
| 1080     | tpch        | region     | 3        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1080_258 | Tradeoff     |
| 1081     | tpch        | nation     | 4        | 1         | 65536     | 65536     | COMMITTED | 2024-09-24 18:50:16 | ./imci_1/imci_chkp_1081_254 | Tradeoff     |
| 1082     | tpch        | part       | 9        | 306       | 65536     | 20054016  | COMMITTED | 2024-09-24 18:50:30 | ./imci_1/imci_chkp_1082_256 | Tradeoff     |
| 1083     | tpch        | supplier   | 7        | 16        | 65536     | 1048576   | COMMITTED | 2024-09-24 18:50:34 | ./imci_1/imci_chkp_1083_259 | Tradeoff     |
| 1084     | tpch        | partsupp   | 5        | 1221      | 65536     | 80019456  | COMMITTED | 2024-09-24 18:51:15 | ./imci_1/imci_chkp_1084_257 | Tradeoff     |
| 1085     | tpch        | customer   | 8        | 229       | 65536     | 15007744  | COMMITTED | 2024-09-24 18:50:28 | ./imci_1/imci_chkp_1085_252 | Tradeoff     |
| 1086     | tpch        | orders     | 9        | 2289      | 65536     | 150011904 | COMMITTED | 2024-09-24 18:51:23 | ./imci_1/imci_chkp_1086_255 | Tradeoff     |
| 1087     | tpch        | lineitem   | 15       | 9156      | 65536     | 600047616 | COMMITTED | 2024-09-24 18:54:16 | ./imci_1/imci_chkp_1087_253 | Tradeoff     |
+----------+-------------+------------+----------+-----------+-----------+-----------+-----------+---------------------+-----------------------------+--------------+
8 rows in set, 1 warning (1.09 sec)

mysql> CALL dbms_imci.drop_columnar_index('tpch');
+-------------+------------+--------+
| Object_Name | Operation  | Result |
+-------------+------------+--------+
| customer    | drop_index | Ok     |
| lineitem    | drop_index | Ok     |
| nation      | drop_index | Ok     |
| orders      | drop_index | Ok     |
| part        | drop_index | Ok     |
| partsupp    | drop_index | Ok     |
| region      | drop_index | Ok     |
| supplier    | drop_index | Ok     |
+-------------+------------+--------+
8 rows in set (0.33 sec)

mysql> SHOW IMCI INDEXES;
Empty set (0.27 sec)

Códigos de resultado de las operaciones por lotes

La columna Result en la salida de una operación por lotes indica el resultado obtenido para cada tabla:

Código de resultado	Descripción
`Ok`	La operación finalizó correctamente.
`Skip by unsupported`	La operación no es compatible con esta tabla.
`Skip by no change`	El destino ya existe o el estado ya coincide. Por ejemplo, ya existe un IMCI al intentar crearlo, o no hay ningún IMCI al intentar eliminarlo.
`Skip by concurrent operation`	Un conflicto con una operación simultánea impidió la ejecución. Por ejemplo, otra instrucción DDL retiene un bloqueo de metadatos (MDL) sobre la tabla.
`Skip by not found`	El objeto de destino no existe.
`Skip by ACL deny`	La cuenta actual carece de los permisos obligatorios.
`Failed`	La operación finalizó con errores.

Crear un IMCI a nivel de tabla sin modificar los comentarios de la tabla

De forma predeterminada, CREATE COLUMNAR INDEX ON marca el IMCI al actualizar el comentario de la tabla (equivale a COLUMNAR=1 en el comentario). Para crear un IMCI sin alterar los comentarios de la tabla, establezca el parámetro de sesión allow_implicit_imci_alter_comment en OFF.

Parámetro	Nivel	Valor predeterminado	Descripción
`allow_implicit_imci_alter_comment`	Sesión	`ON`	Controla si se modifican los comentarios de la tabla al crear un IMCI. Establézcalo en `OFF` para crear un índice columnar sin actualizar el comentario de la tabla.

Cuando se establece en OFF, el parámetro aplica a las tres rutas de creación:

CREATE TABLE cuando loose_polar_enable_implicit_imci_with_create_table está establecido en ON
CREATE COLUMNAR INDEX ON <db>.<table> para la creación dinámica
Creación por lotes mediante CREATE COLUMNAR INDEX FOR TABLES IN <db_name>

Ejemplo 1: Cree una tabla y agregue automáticamente un IMCI sin modificar el comentario de la tabla.

SET allow_implicit_imci_alter_comment = OFF;
SET GLOBAL polar_enable_implicit_imci_with_create_table = ON;
CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));

SHOW CREATE TABLE t1 FULL;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

Ejemplo 2: Agregue dinámicamente un IMCI a una tabla existente sin modificar su comentario.

CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));

SET allow_implicit_imci_alter_comment = OFF;
CREATE COLUMNAR INDEX ON test.t1;

SHOW CREATE TABLE t1 FULL;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

Ejemplo 3: Cree IMCI por lotes en varias tablas sin modificar sus comentarios.

CREATE TABLE t1 (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE t2 (id INT PRIMARY KEY, code DOUBLE);

SET allow_implicit_imci_alter_comment = OFF;
CREATE COLUMNAR INDEX FOR TABLES IN test;
/*
Table_Name  Result
t1          Ok
t2          Ok
*/

SHOW CREATE TABLE t1 FULL;
/*
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

SHOW CREATE TABLE t2 FULL;
/*
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `code` double DEFAULT NULL,
  PRIMARY KEY (`id`),
  COLUMNAR INDEX (`id`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

Cuándo usar esta configuración

Use allow_implicit_imci_alter_comment = OFF en los siguientes casos:

Los comentarios de las tablas deben mantenerse consistentes entre distintos tenants o instancias. En un entorno SaaS, por ejemplo, la tabla de un tenant grande (con IMCI) y la de uno pequeño (sin IMCI) generarán una salida idéntica en SHOW CREATE TABLE, lo cual simplifica la validación con herramientas del ecosistema.

Tenga en cuenta las siguientes limitaciones:

Replicación de registros binarios: Al replicar mediante registros binarios, la base de datos secundaria no puede recrear el IMCI porque el comentario asociado no se incluye en el registro binario. Para crear IMCI en una base de datos secundaria, configure el mismo valor de allow_implicit_imci_alter_comment y de los parámetros relacionados tanto en la base de datos principal como en la secundaria.
IMCI personalizados: Estas instrucciones DDL no permiten configurar IMCI personalizados. Para personalizar un IMCI, consulte Usar los atributos extendidos de un IMCI para personalizarlo.