CRIE INDEX (modo AUTO) - PolarDB: Central de Documentação do Alibaba Cloud

O PolarDB-X permite criar índices locais e índices secundários globais (GSIs) em bancos de dados no modo AUTO. Use CREATE INDEX para índices locais e CREATE GLOBAL INDEX para adicionar um GSI a uma tabela existente.

Índices locais

Os índices locais seguem a sintaxe padrão do MySQL. Consulte a Instrução CREATE INDEX no manual de referência do MySQL 8.0.

GSIs

Um índice secundário global (GSI) armazena dados de índice em uma tabela de índice separada e particionada. Diferentemente de um índice local, o GSI abrange todas as partições da tabela base. Assim, consultas em colunas que não são chave de partição evitam varreduras completas na tabela.

Quando usar um GSI:

Consultas filtram frequentemente por colunas diferentes da chave de partição da tabela base
O desempenho da consulta cai porque o banco de dados precisa verifique todas as partições

Quando um índice local é suficiente:

As consultas sempre filtram pela chave de partição ou por um prefixo dela
O throughput de escrita é prioritário e a sobrecarga de manter uma tabela de índice separada é inaceitável

Para obter a lista completa de limites dos GSIs, consulte Como usar índices secundários globais.

Observações de uso

Para usar recursos de partição de nível 2 em um GSI, sua instância do PolarDB-X deve estar na versão 5.4.17-16952556 ou posterior.

Sintaxe

CREATE [UNIQUE]
    GLOBAL INDEX index_name [index_type]
    ON tbl_name (index_sharding_col_name, ...)
    global_secondary_index_option
    [index_option]
    [algorithm_option | lock_option] ...

global_secondary_index_option:

[COVERING (col_name, ...)]
[partition_options]
[VISIBLE | INVISIBLE]

partition_options:

partition_columns_definition
[subpartition_columns_definition]
[subpartition_specs_definition]
partition_specs_definition

partition_columns_definition — chave de partição de nível 1:

PARTITION BY
      HASH({column_name | partition_func(column_name)}) PARTITIONS partition_count
    | KEY(column_list) PARTITIONS partition_count
    | RANGE({column_name | partition_func(column_name)})
    | RANGE COLUMNS(column_list)
    | LIST({column_name | partition_func(column_name)})
    | LIST COLUMNS(column_list)

subpartition_columns_definition — chave de partição de nível 2:

SUBPARTITION BY
      HASH({column_name | partition_func(column_name)}) SUBPARTITIONS partition_count
    | KEY(column_list) SUBPARTITIONS partition_count
    | RANGE({column_name | partition_func(column_name)})
    | RANGE COLUMNS(column_list)
    | LIST({column_name | partition_func(column_name)})
    | LIST COLUMNS(column_list)

Funções de partição compatíveis:

YEAR, TO_DAYS, TO_MONTHS, TO_WEEKS, TO_SECOND, UNIX_TIMESTAMP, MONTH, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, SUBSTR, SUBSTRING

partition_specs_definition — especificações de partição de nível 1:

partition_specs_definition:
    hash_partition_list
  | range_partition_list
  | list_partition_list

hash_partition_list:
    ( hash_partition [, hash_partition, ...] )

hash_partition:
    PARTITION partition_name [partition_spec_options]
  | PARTITION partition_name SUBPARTITIONS partition_count [subpartition_specs_definition]

hash_subpartition_list:
    ( hash_subpartition [, hash_subpartition, ...] )

hash_subpartition:
    SUBPARTITION subpartition_name [partition_spec_options]

range_partition_list:
    ( range_partition [, range_partition, ...] )

range_partition:
    PARTITION partition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]
  | PARTITION partition_name VALUES LESS THAN (range_bound_value) [SUBPARTITIONS partition_count] [subpartition_specs_definition]

range_subpartition_list:
    ( range_subpartition [, range_subpartition, ...] )

range_subpartition:
    SUBPARTITION subpartition_name VALUES LESS THAN (range_bound_value) [partition_spec_options]

range_bound_value:
    maxvalue
  | expr
  | value_list

list_partition_list:
    ( list_partition [, list_partition, ...] )

list_partition:
    PARTITION partition_name VALUES IN (list_bound_value) [partition_spec_options]
  | PARTITION partition_name VALUES IN (list_bound_value) [SUBPARTITIONS partition_count] [subpartition_specs_definition]

list_subpartition_list:
    ( list_subpartition [, list_subpartition, ...] )

list_subpartition:
    SUBPARTITION subpartition_name VALUES IN (list_bound_value) [partition_spec_options]

list_bound_value:
    default
  | value_set

value_set:
    value_list
  | (value_list) [, (value_list), ...]

value_list:
    value [, value, ...]

subpartition_specs_definition — especificações de partição de nível 2:

subpartition_specs_definition:
    hash_subpartition_list
  | range_subpartition_list
  | list_subpartition_list

partition_spec_options:

[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
[LOCALITY [=] 'dn=storage_inst_id_list']

table_option:

[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'string']
[{CHARSET | CHARACTER SET} [=] charset]
[COLLATE [=] collation]
[TABLEGROUP [=] table_group_id]
[LOCALITY [=] 'dn=storage_inst_id_list']

local_partition_definition:

LOCAL PARTITION BY RANGE (column_name)
[STARTWITH 'yyyy-MM-dd']
INTERVAL interval_count [YEAR | MONTH | DAY]
[EXPIRE AFTER expire_after_count]
[PRE ALLOCATE pre_allocate_count]
[PIVOTDATE pivotdate_func]
[DISABLE SCHEDULE]

pivotdate_func:
    NOW()
  | DATE_ADD(...)
  | DATE_SUB(...)

Parâmetros

Parâmetro	Obrigatório	Descrição
`UNIQUE`	Não	Impõe unicidade nas colunas indexadas.
`index_name`	Sim	Nome do GSI. Deve ser único na tabela.
`index_type`	Não	Tipo de índice, como `USING BTREE`.
`tbl_name`	Sim	Nome da tabela base a indexar.
`index_sharding_col_name`	Sim	Coluna(s) de chave de partição da tabela de índice. Consultas com filtros nessas colunas são roteadas diretamente para as partições de índice correspondentes.
`COVERING (col_name, ...)`	Não	Colunas adicionais armazenadas na tabela de índice. Permite consultas de cobertura sem leitura da tabela base. A chave primária e a chave de shard da tabela base são sempre incluídas como colunas de cobertura padrão.
`PARTITION BY`	Não	Estratégia de particionamento da tabela de índice. Compatível com `HASH`, `KEY`, `RANGE`, `RANGE COLUMNS`, `LIST` e `LIST COLUMNS`.
`SUBPARTITION BY`	Não	Estratégia de particionamento de nível 2. Requer versão da instância 5.4.17-16952556 ou posterior.
`VISIBLE` / `INVISIBLE`	Não	Controla se o otimizador usa o índice. O padrão é `VISIBLE`.
`LOCALITY`	Não	Vincula partições a nós de armazenamento específicos. Formato: `'dn=storage_inst_id_list'`.

Exemplo

O exemplo a seguir cria uma tabela, adiciona um GSI na coluna seller_id e verifica o resultado.

Etapa 1: Criar a tabela base

CREATE TABLE t_order (
  `id`             bigint(11)   NOT NULL AUTO_INCREMENT,
  `order_id`       varchar(20)  DEFAULT NULL,
  `buyer_id`       varchar(20)  DEFAULT NULL,
  `seller_id`      varchar(20)  DEFAULT NULL,
  `order_snapshot` longtext     DEFAULT NULL,
  `order_detail`   longtext     DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `l_i_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
  PARTITION BY HASH(`order_id`) PARTITIONS 16;

A tabela base é particionada por hash em order_id com 16 partições.

Etapa 2: Criar o GSI

CREATE GLOBAL INDEX `g_i_seller`
    ON t_order (`seller_id`)
    COVERING (`order_snapshot`)
    PARTITION BY HASH(`seller_id`) PARTITIONS 16;

Este comando cria uma tabela de índice g_i_seller com as seguintes características:

Particionada por seller_id (chave de partição do índice)
Armazena order_snapshot como coluna de cobertura; assim, consultas em seller_id que também precisam de order_snapshot dispensam consulta à tabela base
Inclui automaticamente id (chave primária) e order_id (chave de shard da tabela base) como colunas de cobertura padrão

Etapa 3: Verificar índices locais

Execute SHOW INDEX para visualize os índices locais na tabela base. Para mais informações, consulte SHOW INDEX.

SHOW INDEX FROM t_order;

Saída de exemplo:

+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table              | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_order_****_00000 |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_order_****_00000 |          1 | l_i_order |            1 | order_id    | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.02 sec)

A saída mostra a chave primária em id e o índice local l_i_order em order_id. Os GSIs não aparecem nesta visualização.

Etapa 4: Verificar o GSI

Execute SHOW GLOBAL INDEX para visualize os GSIs. Para mais informações, consulte SHOW GLOBAL INDEX.

SHOW GLOBAL INDEX FROM t_order;

Saída de exemplo:

+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| SCHEMA              | TABLE   | NON_UNIQUE | KEY_NAME   | INDEX_NAMES | COVERING_NAMES | INDEX_TYPE | DB_PARTITION_KEY | DB_PARTITION_POLICY | DB_PARTITION_COUNT | TB_PARTITION_KEY | TB_PARTITION_POLICY | TB_PARTITION_COUNT | STATUS |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+
| ZZY3_DRDS_LOCAL_APP | t_order | 1          | g_i_seller | seller_id   | id, order_id   | NULL       | seller_id        | HASH                | 4                  |                  | NULL                | NULL               | PUBLIC |
+---------------------+---------+------------+------------+-------------+----------------+------------+------------------+---------------------+--------------------+------------------+---------------------+--------------------+--------+

A coluna STATUS exibe PUBLIC, indicando que o GSI está ativo e visível para o otimizador. A coluna COVERING_NAMES confirma a inclusão automática de id e order_id.

Etapa 5: Inspecionar a estrutura da tabela de índice

SHOW CREATE TABLE g_i_seller;

Saída de exemplo:

+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                                                                                                                                                                                  |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| g_i_seller | CREATE TABLE `g_i_seller` (
  `id` bigint(11) NOT NULL,
  `order_id` varchar(20) DEFAULT NULL,
  `seller_id` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `auto_shard_key_seller_id` (`seller_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 partition by hash(`seller_id`) partitions 16 |
+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

A tabela de índice inclui:

id — chave primária da tabela base (sem AUTO_INCREMENT)
order_id — chave de shard da tabela base (coluna de cobertura padrão)
seller_id — chave de partição da tabela de índice

O índice local da tabela base l_i_order não é copiado para a tabela de índice.

Próximos passos

GSI — conheça a arquitetura do GSI e seu comportamento interno
Como usar índices secundários globais — lista completa de limites e diretrizes de uso de GSIs
CREATE TABLE (modo DRDS) — referência de sintaxe da cláusula GSI para CREATE TABLE