PolarDB:Ejecute instrucciones DDL para crear y eliminar un IMCI dinámicamente

Requisitos previos

Antes de empezar, asegúrese de cumplir estos requisitos:

• Los nodos de almacenamiento de columnas de solo lectura deben estar agregados al clúster. Consulte Agregar un nodo de almacenamiento de columnas de solo lectura.

• El endpoint del clúster debe estar configurado para su estrategia de distribución de solicitudes. Consulte Descripción general de la distribución de solicitudes.

• Debe contar con una conexión al clúster a través del endpoint del clúster. Consulte Conectarse a un clúster.

Tipos de datos y versiones compatibles

A continuación se muestran los tipos de datos compatibles según la versión de PolarDB for MySQL:

Si una tabla o columna ya tiene un comentario, anteponga COLUMNAR=1 (o COLUMNAR=0) al contenido existente. Por ejemplo, COMMENT 'abc' se convierte en COMMENT 'COLUMNAR=1abc'.

Cree un IMCI

Agregue COMMENT 'COLUMNAR=1' a una instrucción ALTER TABLE para crear un IMCI. El alcance depende de dónde agregue el comentario:

• IMCI de tabla: agregue el comentario a ALTER TABLE. El IMCI cubre todas las columnas de tipos de datos compatibles.

• IMCI de columna: agregue el comentario a ALTER TABLE ... MODIFY COLUMN .... El IMCI cubre solo las columnas especificadas.

Si su clúster está conectado a través de Data Management (DMS), no use el proceso de cambio de esquema sin bloqueo para modificar el campo COMMENT.

Cree un IMCI de tabla:

CREATE TABLE t5(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Create an IMCI for the entire table
ALTER TABLE t5 COMMENT 'COLUMNAR=1';

Cree un IMCI de columna:

-- Create an IMCI for specified columns only
ALTER TABLE t5 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=1',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=1';

Elimine un IMCI

Establezca COMMENT 'COLUMNAR=0' para eliminar un IMCI, con la misma sintaxis que se usa al crearlo:

• IMCI de tabla: agregue el comentario a ALTER TABLE

• IMCI de columna: agregue el comentario a ALTER TABLE ... MODIFY COLUMN ...

Elimine un IMCI de columna:

CREATE TABLE t6(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Remove the IMCI from specific columns
ALTER TABLE t6 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=0',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Elimine un IMCI de tabla:

CREATE TABLE t7(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

-- Remove the table-level IMCI
ALTER TABLE t7 COMMENT 'COLUMNAR=0';

Modifique la definición de IMCI

Agregue o quite columnas individuales de un IMCI existente mediante ALTER TABLE ... MODIFY COLUMN ...:

• COMMENT 'COLUMNAR=1' — agrega la columna al IMCI

• COMMENT 'COLUMNAR=0' — quita la columna del IMCI

CREATE TABLE t8(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Add col3 to the IMCI
ALTER TABLE t8 MODIFY COLUMN col3 VARCHAR(200) COMMENT 'COLUMNAR=1';

-- Remove col2 from the IMCI
ALTER TABLE t8 MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Cree un IMCI para la mayoría de las columnas de una tabla

Para cargas de trabajo OLAP, las tablas suelen tener muchas columnas que deben estar cubiertas por un IMCI. En lugar de enumerar cada columna individualmente, establezca COMMENT 'COLUMNAR=1' en la tabla. De manera predeterminada, esto cubre todas las columnas de tipos de datos compatibles. Use COMMENT 'COLUMNAR=0' para excluir columnas específicas.

Ejemplo: Cree un IMCI para todas las columnas excepto col7.

CREATE TABLE t9(
  col1 INT, col2 INT, col3 INT,
  col4 DATETIME, col5 TIMESTAMP,
  col6 CHAR(100), col7 VARCHAR(200),
  col8 TEXT, col9 BLOB
) ENGINE InnoDB;

Ejecute dos instrucciones ALTER TABLE separadas para mejorar el rendimiento:

-- Step 1: Exclude the column that should not be in the IMCI
ALTER TABLE t9 MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

-- Step 2: Create the table-level IMCI
ALTER TABLE t9 COMMENT 'COLUMNAR=1';

Combinar ambas operaciones en una sola instrucción —por ejemplo, ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0' — desencadena el modo de reconstrucción en línea de InnoDB Online DDL, cuyo rendimiento es significativamente menor. Use dos instrucciones separadas en su lugar.

Después de ejecutar las instrucciones, compruebe el resultado con SHOW CREATE TABLE t9 FULL\G. La salida confirma qué columnas están incluidas en el IMCI:

SHOW CREATE TABLE t9 FULL\G
*************************** 1. row ***************************
      Table: t9
Create Table: CREATE TABLE `t9` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` datetime DEFAULT NULL,
  `col5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col6` char(100) DEFAULT NULL,
  `col7` varchar(200) DEFAULT NULL COMMENT 'COLUMNAR=0',
  `col8` text,
  `col9` blob,
  COLUMNAR INDEX  (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col8`,`col9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'COLUMNAR=1'

La columna col7 está excluida del IMCI, como lo indican su COMMENT 'COLUMNAR=0' y su ausencia de la definición de COLUMNAR INDEX.

Cree un IMCI al agregar columnas

Al agregar nuevas columnas con ALTER TABLE ADD COLUMN, incluya COMMENT 'COLUMNAR=1' para agregar automáticamente esas columnas al IMCI.

CREATE TABLE t10(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Add col4 and include it in the IMCI
ALTER TABLE t10 ADD col4 DATETIME DEFAULT NOW() COMMENT 'COLUMNAR=1';

Use INSTANT DDL con tablas IMCI

El comportamiento de INSTANT DDL al agregar o quitar columnas en una tabla con un IMCI depende de la versión de PolarDB for MySQL.

Para versiones anteriores a 8.0.1.1.42 y 8.0.2.2.23:

INSTANT DDL está deshabilitado de manera predeterminada para las tablas IMCI. Sin INSTANT DDL, agregar o quitar una columna requiere reconstruir el IMCI. El IMCI permanece disponible durante la reconstrucción.

Para habilitar INSTANT DDL, use uno de los métodos a continuación:

• Ejecute la instrucción que aparece a continuación en la base de datos:

  SET imci_enable_add_column_instant_ddl = ON

• En la consola de PolarDB, vaya a la página Parameters y establezca loose_imci_enable_add_column_instant_ddl en ON.

Después de habilitar INSTANT DDL, el IMCI se reconstruye de forma asíncrona en segundo plano cuando agrega o quita columnas. El IMCI de tabla no está disponible temporalmente hasta que se complete la reconstrucción. El rendimiento del nodo de almacenamiento de filas de solo lectura no se ve afectado.

Para las versiones 8.0.1.1.42 o posteriores y 8.0.2.2.23 o posteriores:

INSTANT DDL está habilitado de manera predeterminada para las tablas IMCI. Este modo no es compatible con el modo de reconstrucción original. Para usar el modo de reconstrucción, establezca imci_enable_add_column_instant_ddl en OFF y asegúrese de que la tabla tenga una clave principal.

Compruebe el progreso de construcción del IMCI

La creación del IMCI se ejecuta como una operación DDL asincrónica. Después de que la instrucción DDL se completa en el nodo principal, los cambios de metadatos se replican al nodo de almacenamiento de columnas de solo lectura mediante registros de redo. Luego, los hilos en segundo plano comienzan a construir el IMCI de manera concurrente.

Durante este periodo:

• Las consultas OLAP se ejecutan en el nodo de almacenamiento de filas de solo lectura (el IMCI aún no está disponible)

• Después de que el IMCI se construye por completo, las consultas OLAP pasan al nodo de almacenamiento de columnas de solo lectura

Para comprobar si el IMCI está listo, consulte INFORMATION_SCHEMA.IMCI_INDEXES en el nodo de almacenamiento de columnas de solo lectura.

Ejemplo:

CREATE TABLE t11(
  col1 INT, col2 DATETIME, col3 VARCHAR(200)
) ENGINE InnoDB;

ALTER TABLE t11 COMMENT 'COLUMNAR=1';

-- Check IMCI build state
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME = 't11';

Para tablas particionadas, use un patrón LIKE:

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME LIKE '%t1%';

Salida de ejemplo:

+--------+-----------+----------+--------+---------+------+----------+--------+
|TABLE_ID|SCHEMA_NAME|TABLE_NAME|NUM_COLS|PACK_SIZE|ROW_ID|STATE     |MEM_SIZE|
+--------+-----------+----------+--------+---------+------+----------+--------+
|    xxxx| test      | t11      |       3|    65536|     0|RECOVERING|    0   |
+--------+-----------+----------+--------+---------+------+----------+--------+

Para consultar el progreso de construcción detallado, consulte View DDL execution speed and build progress for IMCIs.

Pasos siguientes

• View DDL execution speed and build progress for IMCIs

• Descripción general de la distribución de solicitudes