Como criar índices parciais - PolarDB: Central de Documentação do Alibaba Cloud

Quando usar um índice parcial

Use um índice parcial quando a carga de trabalho de consultas se concentrar em um subconjunto de partições:

Separação de dados quentes/frios: Partições recentes recebem alto tráfego de consultas, enquanto partições mais antigas raramente são consultadas. Indexe apenas as partições recentes para reduzir o tamanho do índice e a sobrecarga de gravação nos dados frios.
Consultas específicas por tenant: Em tabelas multitenant, as consultas limitam-se a grupos específicos de tenants. Indexe apenas as partições relevantes para evitar a manutenção de um índice de tabela completa.
Consultas de intervalo seletivas: Relatórios que sempre filtram por um intervalo de partição conhecido. Um índice parcial nessas partições é menor e mais rápido de percorrer do que um índice global.

Se as consultas abrangerem regularmente todas as partições ou se a tabela for pequena o suficiente para permitir o gerenciamento de um índice completo, um índice global pode ser mais adequado.

Criar um índice parcial ao criar uma tabela particionada

Adicione a cláusula partial_partition_option em CREATE TABLE para definir índices parciais durante a criação da tabela.

Sintaxe

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
    [(create_definition,...)]
    [table_options]
    partition_options
     ...

create_definition é:

{
    col_name column_definition
  | {INDEX | KEY} [index_name] [index_type] (key_part,...)
      [index_option] [partial_partition_option]
      ...
}

partial_partition_option é:

([PARTITION partition_name0[(SUBPARTITION subpartition_name0[, ...])]][,...]
 )

Para todos os outros parâmetros, consulte CREATE TABLE.

Parâmetros

Parâmetro	Descrição
`table_name`	Nome da tabela.
`col_name`	Nome da coluna.
`partition_name0`	Nome da partição. Separe vários nomes de partição com vírgulas (`,`).
`subpartition_name0`	Nome da subpartição. Separe vários nomes de subpartição com vírgulas (`,`).

Exemplos

Indexar apenas a partição do mês atual

O exemplo a seguir cria uma tabela orders particionada por mês (RANGE(month(date))) e define dois índices parciais:

o_ind_dp(dept_no, part_no) — abrange apenas a partição de dezembro (orders_202212), que recebe gravações do mês atual e consultas direcionadas.
o_ind_amout(amount, order_id) — cobre as 11 partições históricas (de orders_202201 até orders_202211), oferecendo suporte a relatórios baseados em valor sobre dados passados.

CREATE TABLE orders
(
  order_id    INT,
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  Primary Key(order_id),
  KEY o_ind_dp(dept_no, part_no) (partition orders_202212),
  KEY o_ind_amout(amount, order_id)
   (partition orders_202201,
    partition orders_202202,
    partition orders_202203,
    partition orders_202204,
    partition orders_202205,
    partition orders_202206,
    partition orders_202207,
    partition orders_202208,
    partition orders_202209,
    partition orders_202210,
    partition orders_202211
   )
)
PARTITION BY RANGE(month(date))
(
  PARTITION orders_202201 VALUES LESS THAN(2),
  PARTITION orders_202202 VALUES LESS THAN(3),
  PARTITION orders_202203 VALUES LESS THAN(4),
  PARTITION orders_202204 VALUES LESS THAN(5),
  PARTITION orders_202205 VALUES LESS THAN(6),
  PARTITION orders_202206 VALUES LESS THAN(7),
  PARTITION orders_202207 VALUES LESS THAN(8),
  PARTITION orders_202208 VALUES LESS THAN(9),
  PARTITION orders_202209 VALUES LESS THAN(10),
  PARTITION orders_202210 VALUES LESS THAN(11),
  PARTITION orders_202211 VALUES LESS THAN(12),
  PARTITION orders_202212 VALUES LESS THAN(13)
);

Indexar subpartições selecionadas em uma tabela multitenant

O exemplo a seguir cria uma tabela tenants com subparticionamento LIST-RANGE e define dois índices parciais:

ind_id(id) — abrange todas as subpartições de p0 e as subpartições p1_1, p1_2 e p1_3 de p1.
ind_date(date) — cobre as subpartições p0_1 e p0_2 de p0, além das subpartições p1_1 e p1_2 de p1.

CREATE TABLE tenants (
  id INT,
  date DATE,
  count INT,
  KEY ind_id (id)  (
    partition p0,
    partition p1 (subpartition p1_1, subpartition p1_2, subpartition p1_3)
  ),
  KEY ind_date (date) (
    partition p0 (subpartition p0_1,  subpartition p0_2),
    partition p1 (subpartition p1_1, subpartition p1_2)
  )
) PARTITION BY LIST COLUMNS(id)
  SUBPARTITION BY RANGE (month(date))(
  PARTITION p0 VALUES IN (1, 2, 3, 4, 5) (
    SUBPARTITION p0_1 VALUES LESS THAN(4),
    SUBPARTITION p0_2 VALUES LESS THAN(7),
    SUBPARTITION p0_3 VALUES LESS THAN(10),
    SUBPARTITION p0_4 VALUES LESS THAN(13)
  ),
  PARTITION p1 VALUES IN (11, 12, 13, 14, 15) (
    SUBPARTITION p1_1 VALUES LESS THAN(4),
    SUBPARTITION p1_2 VALUES LESS THAN(7),
    SUBPARTITION p1_3 VALUES LESS THAN(10),
    SUBPARTITION p1_4 VALUES LESS THAN(13)
  )
);

Criar um índice parcial em uma tabela particionada existente

Use CREATE INDEX ou ALTER TABLE ADD KEY para adicionar um índice parcial a uma tabela existente.

Sintaxe

CREATE [UNIQUE] INDEX index_name
    [index_type]
    ON table_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option | partial_partition_option] ...

partial_partition_option é:

([PARTITION partition_name0[(SUBPARTITION subpartition_name0[, ...])]][,...]
  )

Para todos os outros parâmetros, consulte CREATE INDEX.

Parâmetros

Parâmetro	Descrição
`index_name`	Nome do índice.
`table_name`	Nome da tabela.
`partition_name0`	Nome da partição. Separe vários nomes de partição com vírgulas (`,`).
`subpartition_name0`	Nome da subpartição. Separe vários nomes de subpartição com vírgulas (`,`).

Exemplos

Adicionar um índice parcial a uma única partição

O exemplo a seguir adiciona um índice parcial o_part_id à partição orders_202201 da tabela orders existente:

CREATE INDEX o_part_id ON orders(part_no, order_id) (partition orders_202201);

Alternativamente, use ALTER TABLE ADD KEY:

ALTER TABLE orders ADD KEY o_part_id(part_no, order_id) (partition orders_202201);

Adicionar um índice parcial entre partições e subpartições

O exemplo a seguir adiciona um índice parcial ind_count a todas as subpartições de p0 e à subpartição p1_1 de p1 na tabela tenants existente:

CREATE INDEX ind_count ON tenants(count) (partition p0, partition p1 (subpartition p1_1));

Como alternativa, use ALTER TABLE ADD KEY:

ALTER TABLE tenants ADD KEY ind_count(count) (partition p0, partition p1 (subpartition p1_1));