PolarDB:Execute instruções DDL para criar e excluir dinamicamente um IMCI

Pré-requisitos

Antes de começar, verifique se:

• Nós somente leitura de column store foram adicionados ao cluster. Consulte Adicionar um nó somente leitura de column store.

• Um endpoint de cluster está configurado para a estratégia de distribuição de requisições. Consulte Visão geral da distribuição de requisições.

• Uma conexão com o cluster via endpoint de cluster foi estabelecida. Consulte Conectar-se a um cluster.

Tipos de dados e versões compatíveis

Os tipos de dados a seguir são compatíveis conforme a versão do PolarDB for MySQL:

Se uma tabela ou coluna já tiver um comentário, adicione COLUMNAR=1 (ou COLUMNAR=0) antes do conteúdo existente. Por exemplo, COMMENT 'abc' torna-se COMMENT 'COLUMNAR=1abc'.

Criar um IMCI

Adicione COMMENT 'COLUMNAR=1' a uma instrução ALTER TABLE para criar um IMCI. O escopo depende do local onde o comentário é inserido:

• IMCI no nível da tabela: insira o comentário na instrução ALTER TABLE. O IMCI abrange todas as colunas com tipos de dados compatíveis.

• IMCI no nível da coluna: insira o comentário na instrução ALTER TABLE ... MODIFY COLUMN .... O IMCI abrange apenas as colunas especificadas.

Se o cluster estiver conectado por meio do Data Management (DMS), não use o processo de alteração de esquema sem bloqueio para modificar o campo COMMENT .

Criar um IMCI no nível da tabela:

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';

Criar um IMCI no nível da coluna:

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

Excluir um IMCI

Defina COMMENT 'COLUMNAR=0' para remover um IMCI, usando a mesma sintaxe da criação:

• Nível da tabela: adicione o comentário à instrução ALTER TABLE

• Nível da coluna: adicione o comentário à instrução ALTER TABLE ... MODIFY COLUMN ...

Excluir um IMCI no nível da coluna:

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';

Excluir um IMCI no nível da tabela:

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';

Modificar a definição do IMCI

Adicione ou remova colunas individuais de um IMCI existente usando ALTER TABLE ... MODIFY COLUMN ...:

• COMMENT 'COLUMNAR=1': adiciona a coluna ao IMCI

• COMMENT 'COLUMNAR=0': remove a coluna do 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';

Criar um IMCI para a maioria das colunas de uma tabela

Em cargas de trabalho OLAP, as tabelas frequentemente possuem muitas colunas que devem ser cobertas por um IMCI. Em vez de listar cada coluna individualmente, defina COMMENT 'COLUMNAR=1' no nível da tabela — isso cobre todas as colunas de tipos de dados compatíveis por padrão — e use COMMENT 'COLUMNAR=0' para excluir colunas específicas.

Exemplo: Crie um IMCI para todas as colunas, exceto 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;

Execute duas instruções ALTER TABLE separadas para obter melhor desempenho:

-- 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 as operações em uma única instrução — por exemplo, ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0' — aciona o modo de reconstrução online do InnoDB Online DDL, que apresenta desempenho significativamente inferior. Use duas instruções separadas.

Após executar as instruções, verifique o resultado com SHOW CREATE TABLE t9 FULL\G. A saída confirma quais colunas estão incluídas no 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'

A coluna col7 é excluída do IMCI, conforme indicado pelo seu COMMENT 'COLUMNAR=0' e pela ausência na definição do COLUMNAR INDEX.

Criar um IMCI ao adicionar colunas

Ao adicionar novas colunas com ALTER TABLE ADD COLUMN, inclua COMMENT 'COLUMNAR=1' para adicioná-las automaticamente ao 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';

Usar INSTANT DDL com tabelas IMCI

O comportamento do INSTANT DDL ao adicionar ou remover colunas em uma tabela com IMCI depende da versão do PolarDB for MySQL.

Para versões anteriores a 8.0.1.1.42 e 8.0.2.2.23:

O INSTANT DDL vem desativado por padrão para tabelas IMCI. Sem o INSTANT DDL, adicionar ou remover uma coluna exige a reconstrução do IMCI. O IMCI permanece disponível durante a reconstrução.

Para ativar o INSTANT DDL, use um dos métodos a seguir:

• Execute a seguinte instrução no banco de dados:

  SET imci_enable_add_column_instant_ddl = ON

• No PolarDB console, acesse a página Parameters e defina loose_imci_enable_add_column_instant_ddl como ON.

Após ativar o INSTANT DDL, o IMCI é reconstruído de forma assíncrona em segundo plano quando você adiciona ou remove colunas. O IMCI no nível da tabela fica temporariamente indisponível até a conclusão da reconstrução. O desempenho dos nós somente leitura de row store não é afetado.

Para versões 8.0.1.1.42 ou posteriores e 8.0.2.2.23 ou posteriores:

O INSTANT DDL vem ativado por padrão para tabelas IMCI. Este modo não é compatível com o modo de reconstrução original. Para usar o modo de reconstrução, defina imci_enable_add_column_instant_ddl como OFF e garanta que a tabela possua uma chave primária.

Verificar o progresso de construção do IMCI

A criação do IMCI ocorre como uma operação DDL assíncrona. Após a conclusão da instrução DDL no nó primário, as alterações de metadados são replicadas para o nó somente leitura de column store via Redo logs, e threads em segundo plano iniciam a construção do IMCI simultaneamente.

Durante esse período:

• Consultas OLAP são executadas no nó somente leitura de row store (IMCI ainda não disponível)

• Após a construção completa do IMCI, as consultas OLAP passam a usar o nó somente leitura de column store

Para verificar se o IMCI está pronto, consulte INFORMATION_SCHEMA.IMCI_INDEXES no nó somente leitura de column store.

Exemplo:

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 tabelas particionadas, use um padrão LIKE:

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

Saída de exemplo:

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

Para obter detalhes sobre o progresso da construção, consulte Visualize velocidade de execução de DDL e progresso de construção para IMCIs.

Próximos passos

• Visualize velocidade de execução de DDL e progresso de construção para IMCIs

• Visão geral da distribuição de requisições