How to create partial indexes - PolarDB - Centro de documentación de Alibaba Cloud

Un índice parcial abarca únicamente las particiones que usted especifique, en lugar de toda la tabla particionada. Esto mantiene el índice compacto y resulta útil cuando las consultas se dirigen a particiones específicas (por ejemplo, los meses recientes o un grupo particular de inquilinos), mientras que el resto de los datos se consulta con poca frecuencia o no se consulta en absoluto.

Cuándo usar un índice parcial

Utilice un índice parcial cuando la carga de trabajo de sus consultas se concentre en un subconjunto de particiones:

Separación de datos activos e inactivos: Las particiones recientes reciben mucho tráfico de consultas, pero las más antiguas casi no se consultan. Indexe solo las particiones recientes para reducir el tamaño del índice y la sobrecarga de escritura en los datos inactivos.
Consultas específicas por inquilino: En tablas multiinquilino, las consultas se limitan a grupos específicos de inquilinos. Indexe únicamente las particiones relevantes para evitar mantener un índice de toda la tabla.
Consultas de rango selectivas: Reportes que siempre filtran sobre un rango de particiones conocido. Un índice parcial en esas particiones es más pequeño y rápido de explorar que un índice global.

Si las consultas abarcan todas las particiones de forma regular, o si la tabla es lo bastante pequeña como para gestionar un índice completo, un índice global podría ser más adecuado.

Crear un índice parcial al crear una tabla particionada

Agregue la cláusula partial_partition_option dentro de CREATE TABLE para definir índices parciales durante la creación de la tabla.

Sintaxis

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

La definición create_definition es:

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

La opción partial_partition_option es:

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

Para conocer el resto de los parámetros, consulte CREATE TABLE.

Parámetros

Parámetro	Descripción
`table_name`	El nombre de la tabla.
`col_name`	El nombre de la columna.
`partition_name0`	El nombre de la partición. Separe varios nombres de partición con comas (`,`).
`subpartition_name0`	El nombre de la subpartición. Separe varios nombres de subpartición con comas (`,`).

Ejemplos

Indexar solo la partición del mes actual

El siguiente ejemplo crea una tabla orders particionada por mes (RANGE(month(date))) y define dos índices parciales:

o_ind_dp(dept_no, part_no): abarca únicamente la partición de diciembre (orders_202212), que recibe las escrituras del mes actual y las consultas dirigidas.
o_ind_amout(amount, order_id): abarca las 11 particiones históricas (desde orders_202201 hasta orders_202211) y admite la generación de reportes por monto sobre datos anteriores.

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 subparticiones seleccionadas en una tabla multiinquilino

El siguiente ejemplo crea una tabla LIST-RANGE tenants con subparticiones y define dos índices parciales:

ind_id(id): abarca todas las subparticiones de p0 y las subparticiones p1_1, p1_2 y p1_3 de p1.
ind_date(date): abarca las subparticiones p0_1 y p0_2 de p0, así como las subparticiones p1_1 y 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)
  )
);

Crear un índice parcial en una tabla particionada existente

Use CREATE INDEX o ALTER TABLE ADD KEY para agregar un índice parcial a una tabla que ya existe.

Sintaxis

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

La opción partial_partition_option es:

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

Para conocer el resto de los parámetros, consulte CREATE INDEX.

Parámetros

Parámetro	Descripción
`index_name`	El nombre del índice.
`table_name`	El nombre de la tabla.
`partition_name0`	El nombre de la partición. Separe varios nombres de partición con comas (`,`).
`subpartition_name0`	El nombre de la subpartición. Separe varios nombres de subpartición con comas (`,`).

Ejemplos

Agregar un índice parcial a una sola partición

El siguiente ejemplo agrega el índice parcial o_part_id a la partición orders_202201 de la tabla orders existente:

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

También puede usar ALTER TABLE ADD KEY:

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

Agregar un índice parcial en varias particiones y subparticiones

El siguiente ejemplo agrega el índice parcial ind_count a todas las subparticiones de p0 y a la subpartición p1_1 de p1 en la tabla tenants existente:

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

También puede usar ALTER TABLE ADD KEY:

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