Colunas geradas - PolarDB: Central de Documentação do Alibaba Cloud

Tipos de coluna

O PolarDB-X oferece suporte a três tipos de colunas geradas. Eles funcionam de forma semelhante à diferença entre uma view e uma materialized view, mas no nível da coluna:

Tipo	Armazenamento	Computado por	Computado quando	Chave de partição?
VIRTUAL	Não armazenado	Nó de dados	A cada leitura	Não
STORED	Armazenado no nó de dados	Nó de dados	INSERT ou UPDATE	Não
LOGICAL	Armazenado no nó de dados	Nó de computação	INSERT ou UPDATE	Sim

VIRTUAL comporta-se como uma view no nível da coluna: o valor é derivado sob demanda e não ocupa espaço de armazenamento. STORED funciona como uma materialized view no nível da coluna: é pré-computado e armazenado, o que acelera as leituras, mas adiciona uma pequena sobrecarga às gravações. LOGICAL é computado no nó de computação, armazenado como uma coluna regular e é o único tipo que pode servir como chave de partição ou referenciar uma função definida pelo usuário (UDF).

Se você omitir o tipo, a coluna assumirá VIRTUAL por padrão.

Sintaxe

col_name data_type [GENERATED ALWAYS] AS (expr)
  [VIRTUAL | STORED | LOGICAL] [NOT NULL | NULL]
  [UNIQUE [KEY]] [[PRIMARY] KEY]
  [COMMENT 'string']

Pré-requisitos

Antes de começar, verifique se você tem:

PolarDB-X Enterprise Edition V5.4.17 ou posterior

Esse requisito de versão aplica-se a colunas geradas, índices em colunas geradas e índices de expressão.

Criar uma coluna gerada

Limitações

As colunas geradas no PolarDB-X compartilham as restrições padrão do MySQL e adicionam várias restrições específicas para ambientes distribuídos.

Compartilhadas com o MySQL

Não há suporte para valores padrão.
Não é possível definir AUTO_INCREMENT em uma coluna gerada, nem uma coluna gerada pode referenciar uma coluna AUTO_INCREMENT.
Funções não determinísticas — como UUID(), CONNECTION_ID() e NOW() — não podem ser usadas em uma expressão de coluna gerada.
Variáveis não podem ser usadas em expressões.
Subconsultas não podem ser usadas em expressões.
Os valores das colunas geradas não podem ser especificados em instruções INSERT ou UPDATE. O banco de dados sempre os computa automaticamente.

Específicas do PolarDB-X

Não é possível adicionar colunas geradas a tabelas com arquivamento de dados frios ativado.
Colunas VIRTUAL e STORED não podem ser usadas como chaves de partição, chaves primárias ou chaves únicas.
Colunas VIRTUAL e STORED não podem referenciar funções armazenadas.
Se um índice secundário global (GSI) incluir colunas geradas VIRTUAL ou STORED, o GSI também deverá incluir todas as colunas referenciadas nas expressões dessas colunas geradas.
Uma coluna gerada LOGICAL não pode referenciar uma coluna gerada VIRTUAL ou STORED em sua expressão.
O tipo de uma coluna gerada LOGICAL e os tipos das colunas referenciadas em sua expressão não podem ser alterados após a criação.
Colunas geradas LOGICAL e suas colunas referenciadas suportam apenas estes tipos de dados:
- Tipos inteiros: BIGINT, INT, MEDIUMINT, SMALLINT, TINYINT
- Tipos de data: DATETIME, DATE, TIMESTAMP (não há suporte para colunas com ON UPDATE CURRENT_TIMESTAMP)
- Tipos de string: CHAR, VARCHAR

Exemplos: colunas VIRTUAL e STORED

Exemplo 1: Computar automaticamente um valor derivado

Crie uma tabela em que a hipotenusa de um triângulo retângulo seja computada como uma coluna gerada:

CREATE TABLE triangle (
  sidea DOUBLE,
  sideb DOUBLE,
  sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
);

INSERT INTO triangle (sidea, sideb) VALUES (1, 1), (3, 4), (6, 8);

SELECT * FROM triangle;

Resultado:

+-------+-------+--------------------+
| sidea | sideb | sidec              |
+-------+-------+--------------------+
|   1.0 |   1.0 | 1.4142135623730951 |
|   3.0 |   4.0 |                5.0 |
|   6.0 |   8.0 |               10.0 |
+-------+-------+--------------------+

Exemplo 2: Coluna gerada em uma tabela particionada

Crie uma tabela particionada com uma coluna gerada VIRTUAL b:

CREATE TABLE `t1` (
  `a` int(11) NOT NULL,
  `b` int(11) GENERATED ALWAYS AS (`a` + 1),
  PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`);

INSERT INTO t1 (a) VALUES (1);

SELECT * FROM t1;

Resultado:

+---+---+
| a | b |
+---+---+
| 1 | 2 |
+---+---+

Exemplos: colunas LOGICAL

As colunas geradas LOGICAL são a escolha ideal quando você precisa de particionamento flexível baseado em um valor derivado ou quando deseja usar uma UDF na expressão.

Exemplo 1: Particionar por uma substring de uma coluna de string

Use os dois últimos caracteres da coluna b como chave de partição:

CREATE TABLE `t2` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` varchar(2) GENERATED ALWAYS AS (SUBSTR(`b`, -2)) LOGICAL,
  PRIMARY KEY (`a`),
  KEY `auto_shard_key_c` USING BTREE (`c`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`c`);

Exemplo 2: Referenciar uma UDF em uma coluna gerada LOGICAL

Primeiro, crie a função definida pelo usuário my_abs:

DELIMITER &&
CREATE FUNCTION my_abs (
  a INT
)
RETURNS INT
BEGIN
  IF a < 0 THEN
    RETURN -a;
  ELSE
    RETURN a;
  END IF;
END&&
DELIMITER ;

Em seguida, crie uma tabela com uma coluna gerada LOGICAL que chama my_abs:

CREATE TABLE `t3` (
  `a` int(11) NOT NULL,
  `b` int(11) GENERATED ALWAYS AS (MY_ABS(`a`)) LOGICAL,
  PRIMARY KEY (`a`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`b`);

INSERT INTO t3 (a) VALUES (1), (-1);

Verifique se as consultas em b são enviadas ao nó de dados:

EXPLAIN SELECT * FROM t3 WHERE b = 1;

+-----------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                     |
+-----------------------------------------------------------------------------------------------------------+
| LogicalView(tables="TEST_000002_GROUP.t3_WHHZ", sql="SELECT `a`, `b` FROM `t3` AS `t3` WHERE (`b` = ?)") |
+-----------------------------------------------------------------------------------------------------------+

SELECT * FROM t3 WHERE b = 1;

+----+------+
| a  | b    |
+----+------+
| -1 |    1 |
|  1 |    1 |
+----+------+

Criar um índice em uma coluna gerada

Limitações

Tipo de índice	VIRTUAL	STORED	LOGICAL
Índice local	Sim	Sim	Sim
Índice global	Não	Não	Sim

Exemplos

Exemplo 1: Índice local em uma coluna VIRTUAL

Este exemplo indexa um campo JSON extraindo-o para uma coluna gerada VIRTUAL. Como colunas JSON não podem ser indexadas diretamente no PolarDB-X (ou MySQL), este padrão é a solução alternativa recomendada:

CREATE TABLE t4 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c JSON,
    g INT AS (c->"$.id") VIRTUAL
) DBPARTITION BY HASH(a);

CREATE INDEX `i` ON `t4`(`g`);

INSERT INTO t4 (c) VALUES
  ('{"id": "1", "name": "Fred"}'),
  ('{"id": "2", "name": "Wilma"}'),
  ('{"id": "3", "name": "Barney"}'),
  ('{"id": "4", "name": "Betty"}');

EXPLAIN EXECUTE SELECT c->>"$.name" AS name FROM t4 WHERE g > 2;

+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id   | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1    | SIMPLE      | t4    | NULL       | range | i             | i    | 5       | NULL | 1    | 100      | Using where |
+------+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+

O plano de execução confirma o uso do índice i.

Exemplo 2: Índice local em uma coluna LOGICAL

CREATE TABLE t5 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32),
    g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);

CREATE INDEX `i` ON `t5`(`g`);

INSERT INTO t5 (c) VALUES
  ('1111'),
  ('1112'),
  ('1211'),
  ('1311');

EXPLAIN EXECUTE SELECT c AS name FROM t5 WHERE g = '11';

+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+
| 1    | SIMPLE      | t5    | NULL       | ref  | i             | i    | 8       | NULL | 4    | 100.00   | Using XPlan, Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------+

Exemplo 3: Índice global em uma coluna LOGICAL

CREATE TABLE t6 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32),
    g char(2) AS (substr(`c`, 2)) LOGICAL
) DBPARTITION BY HASH(a);

CREATE GLOBAL INDEX `g_i` ON `t6`(`g`) COVERING(`c`) DBPARTITION BY HASH(`g`);

INSERT INTO t6 (c) VALUES
  ('1111'),
  ('1112'),
  ('1211'),
  ('1311');

EXPLAIN SELECT c AS name FROM t6 WHERE g = '11';

+---------------------------------------------------------------------------------------------------------------------+
| LOGICAL EXECUTIONPLAN                                                                                               |
+---------------------------------------------------------------------------------------------------------------------+
| IndexScan(tables="TEST_DRDS_000000_GROUP.g_i_J1MT", sql="SELECT `c` AS `name` FROM `g_i` AS `g_i` WHERE (`g` = ?)") |
+---------------------------------------------------------------------------------------------------------------------+

Índice de expressão

Um índice de expressão permite indexar o resultado de uma expressão em vez de uma coluna diretamente. Ao criar o índice, o PolarDB-X converte automaticamente cada entrada da expressão em uma coluna gerada VIRTUAL e constrói o índice nessa coluna.

Limitações

Os índices de expressão estão desativados por padrão. Ative o recurso com:
```
SET GLOBAL ENABLE_CREATE_EXPRESSION_INDEX = TRUE;
```
Não há suporte para índices globais.
Não há suporte para índices únicos.
Não é possível criar índices de expressão em uma instrução CREATE TABLE. Crie a tabela primeiro e depois use ALTER TABLE ou CREATE INDEX.
Remover um índice de expressão com DROP INDEX não exclui as colunas geradas criadas automaticamente. Exclua-as manualmente com ALTER TABLE no modo DRDS ou no modo AUTO.

Exemplos

Exemplo 1: Criar um índice de expressão

Crie a tabela:

CREATE TABLE t7 (
    a BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    c varchar(32)
) DBPARTITION BY HASH(a);

Crie um índice de expressão em substr(c, 2):
```
CREATE INDEX `i` ON `t7`(substr(`c`, 2));
```

Após a criação, o PolarDB-X reescreve a estrutura da tabela para adicionar uma coluna gerada VIRTUAL para cada entrada da expressão:

CREATE TABLE `t7` (
  `a` bigint(20) NOT NULL AUTO_INCREMENT BY GROUP,
  `c` varchar(32) DEFAULT NULL,
  `i$0` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
  PRIMARY KEY (`a`),
  KEY `i` (`i$0`)
) ENGINE = InnoDB dbpartition by hash(`a`);

A coluna i$0 contém o resultado da expressão, e o índice i é construído sobre i$0.

Verifique se o índice é selecionado para consultas na expressão:

EXPLAIN EXECUTE SELECT * FROM t7 WHERE substr(`c`, 2) = '11';

+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1    | SIMPLE      | t7    | NULL       | ref  | i             | i    | 131     | const | 1    | 100      | NULL  |
+------+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+

Exemplo 2: Índice de expressão com múltiplas expressões

Quando um índice inclui múltiplas expressões, o PolarDB-X cria uma coluna gerada separada para cada entrada da expressão (entradas que não são expressões permanecem inalteradas).

Crie o índice:

CREATE INDEX idx ON t8(
  a + 1,
  b,
  SUBSTR(c, 2)
);

Estrutura da tabela resultante:

CREATE TABLE `t8` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` varchar(32) DEFAULT NULL,
  `idx$0` bigint(20) GENERATED ALWAYS AS (`a` + 1) VIRTUAL,
  `idx$2` varchar(32) GENERATED ALWAYS AS (substr(`c`, 2)) VIRTUAL,
  PRIMARY KEY (`a`),
  KEY `idx` (`idx$0`, `b`, `idx$2`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 dbpartition by hash(`a`);

As colunas idx$0 e idx$2 correspondem à primeira e à terceira entradas do índice. A coluna b é uma coluna regular e não precisa de encapsulamento de coluna gerada.

Consultas que filtram pelas três condições se beneficiam do índice:

EXPLAIN EXECUTE SELECT * FROM t8 WHERE a+1=10 AND b=20 AND SUBSTR(c,2)='ab';

+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| id   | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+
| 1    | SIMPLE      | t8    | NULL       | ref  | idx           | idx  | 145     | const,const,const | 1    | 100      | NULL  |
+------+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+-------+

PolarDB:Colunas geradas

Tipos de coluna

Sintaxe

Pré-requisitos

Criar uma coluna gerada

Limitações

Exemplos: colunas VIRTUAL e STORED

Exemplos: colunas LOGICAL

Criar um índice em uma coluna gerada

Limitações

Exemplos

Índice de expressão

Limitações

Exemplos

Próximos passos