Sintaxe DDL para IMCIs no nível de banco de dados e tabela - PolarDB: Central de Documentação do Alibaba Cloud

Requisitos de versão

O cluster deve executar uma das seguintes versões:

PolarDB for MySQL 8.0.1, versão de revisão 8.0.1.1.45 ou posterior
PolarDB for MySQL 8.0.2, versão de revisão 8.0.2.2.27 ou posterior

Comandos para tabela única

Crie um IMCI no nível de tabela

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

Se você omitir db_name, a instrução terá como alvo o banco de dados da sessão atual. Diferentemente de um índice secundário, não é necessário especificar o nome do índice nem as colunas incluídas, pois o IMCI abrange todas as colunas automaticamente.

Para evitar erros caso a tabela já possua um IMCI, adicione IF NOT EXISTS antes de ON:

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

Por padrão, essa instrução equivale a:

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

A equivalência acima se aplica apenas quando allow_implicit_imci_alter_comment está definido como ON (o padrão). Se definido como OFF , o IMCI será criado sem modificar o comentário da tabela. Para mais detalhes, consulte Criar um IMCI no nível de tabela sem modificar comentários da tabela .

Para configurar um IMCI personalizado em vez de incluir todas as colunas, consulte Usar os atributos estendidos de um IMCI para personalizar o IMCI .

Verifique o status de criação do IMCI

Após criar um IMCI, verifique se ele está pronto executando:

SHOW IMCI INDEXES;

O campo STATE indica o status atual:

Estado	Descrição
`COMMITTED`	O IMCI está pronto e disponível para consultas.
`RECOVERING`	O IMCI está sendo construído a partir dos dados existentes.
`RECOVER_BUILDING`	O IMCI está sendo reconstruído após um evento de recuperação.

Exclua um IMCI no nível de tabela

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

Se você omitir db_name, a instrução terá como alvo o banco de dados da sessão atual. Não é necessário especificar o nome do índice.

Para evitar erros caso o IMCI já tenha sido excluído, adicione IF EXISTS antes de ON:

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

Essa instrução equivale a executar ambos os comandos a seguir:

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

Operações em lote

Use as instruções a seguir para criar ou excluir IMCIs em todas as tabelas de um banco de dados simultaneamente.

Crie IMCIs no nível de tabela em lote

Há suporte para três opções de sintaxe 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>');

Todas as três opções criam um IMCI no nível de tabela para cada tabela em <db_name> que ainda não possua um.

O procedimento armazenado aceita um parâmetro opcional set_implicit. Defina-o como 1 para adicionar o marcador implicit_imci ao banco de dados. Isso faz com que novas tabelas criadas nesse banco recebam automaticamente um IMCI:

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

Exemplo

O exemplo a seguir cria IMCIs para todas as tabelas no banco de dados tpch e verifica o 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)

Exclua IMCIs no nível de tabela em lote

Há suporte para três opções de sintaxe 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>');

Todas as três opções excluem os IMCIs existentes no nível de tabela de cada tabela em <db_name>.

O procedimento armazenado aceita um parâmetro opcional reset_implicit. Defina-o como 1 para remover o marcador implicit_imci do banco de dados. Isso impede que novas tabelas recebam automaticamente um IMCI:

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

Exemplo

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 operações em lote

A coluna Result na saída de uma operação em lote indica o resultado para cada tabela:

Código de resultado	Descrição
`Ok`	A operação foi concluída com sucesso.
`Skip by unsupported`	Não há suporte para a operação nesta tabela.
`Skip by no change`	O alvo já existe ou o status já corresponde. Por exemplo, um IMCI já existe ao tentar criar, ou nenhum IMCI existe ao tentar excluir.
`Skip by concurrent operation`	Um conflito de operação concorrente impediu a execução. Por exemplo, outra instrução DDL mantém um bloqueio de metadados (MDL) na tabela.
`Skip by not found`	O objeto alvo não existe.
`Skip by ACL deny`	A conta atual não possui as permissões necessárias.
`Failed`	A operação falhou.

Criar um IMCI no nível de tabela sem modificar comentários da tabela

Por padrão, CREATE COLUMNAR INDEX ON marca o IMCI atualizando o comentário da tabela (equivalente a COLUMNAR=1 no comentário). Para criar um IMCI sem alterar os comentários da tabela, defina o parâmetro de sessão allow_implicit_imci_alter_comment como OFF.

Parâmetro	Nível	Padrão	Descrição
`allow_implicit_imci_alter_comment`	Sessão	`ON`	Controla se os comentários da tabela são modificados durante a criação de um IMCI. Defina como `OFF` para criar um índice colunar sem atualizar o comentário da tabela.

Quando definido como OFF, o parâmetro se aplica aos três métodos de criação:

CREATE TABLE quando loose_polar_enable_implicit_imci_with_create_table está definido como ON
CREATE COLUMNAR INDEX ON <db>.<table> para criação dinâmica
Criação em lote via CREATE COLUMNAR INDEX FOR TABLES IN <db_name>

Exemplo 1: Criar uma tabela e adicionar automaticamente um IMCI sem modificar o comentário da tabela.

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
*/

Exemplo 2: Adicionar dinamicamente um IMCI a uma tabela existente sem modificar o comentário da tabela.

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
*/

Exemplo 3: Criar IMCIs em lote em várias tabelas sem modificar os comentários das tabelas.

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
*/

Quando usar esta configuração

Use allow_implicit_imci_alter_comment = OFF quando:

Os comentários da tabela precisarem permanecer consistentes entre locatários ou instâncias. Em um ambiente SaaS, por exemplo, a tabela de um grande locatário (com um IMCI) e a tabela de um pequeno locatário (sem um) terão saídas idênticas no SHOW CREATE TABLE, o que simplifica a validação com ferramentas do ecossistema.

Observe as seguintes limitações:

Replicação de log binário: Ao replicar via logs binários, o banco de dados secundário não consegue recriar o IMCI porque o comentário relacionado ao IMCI está ausente no binlog. Para criar IMCIs em um banco de dados secundário, configure o mesmo allow_implicit_imci_alter_comment e as configurações de parâmetros relacionadas tanto no banco de dados primário quanto no secundário.
IMCIs personalizados: Não é possível configurar IMCIs personalizados por meio dessas instruções DDL. Para personalizar um IMCI, consulte Usar os atributos estendidos de um IMCI para personalizar o IMCI.