Global Secondary Index - Hologres - Centro de documentación de Alibaba Cloud

Desde la versión V4.0, Hologres admite índices secundarios globales para realizar búsquedas eficientes de clave-valor en columnas que no son clave principal. A diferencia de un índice de clave principal, el índice secundario global no requiere valores únicos, pero mejora significativamente el rendimiento de las consultas en columnas específicas.

Requisitos previos

Su instancia de Hologres debe ser V4.0 o posterior. Si su instancia tiene una versión anterior, consulte Instance upgrade.

Limitaciones

Las tablas con un índice secundario global no admiten la escritura de datos mediante Fixed FE o Fixed Copy.
Las columnas del índice secundario global solo admiten los tipos de datos TEXT, INTEGER, BIGINT y VARCHAR.
No es posible modificar un índice secundario global.
Una columna no puede utilizarse simultáneamente como columna de índice y como columna incluida.
La tabla de origen debe tener una clave principal.
No se puede establecer el parámetro time_to_live_in_seconds en la tabla de origen.
El número total de columnas de índice y columnas incluidas en un índice secundario global no puede exceder 512.
Los índices secundarios globales solo se pueden crear en tablas internas. No se admiten tablas particionadas físicas ni tablas particionadas lógicas.
No se puede eliminar ni modificar una columna de la tabla de origen si forma parte de un índice secundario global.
No se puede modificar el Table Group ni ejecutar Resharding en una tabla de origen que tenga un índice secundario global.
De forma predeterminada, los índices secundarios globales utilizan únicamente almacenamiento estándar.
Un índice secundario global utiliza el mismo formato de almacenamiento que su tabla de origen: almacenamiento orientado a filas, almacenamiento orientado a columnas o almacenamiento híbrido de filas y columnas.
- Si la tabla de origen usa almacenamiento orientado a filas, su índice secundario global también usará este tipo de almacenamiento de forma predeterminada.
- Si la tabla de origen usa almacenamiento orientado a columnas, su índice secundario global también usará este tipo de almacenamiento de forma predeterminada.
- Si la tabla de origen usa almacenamiento híbrido de filas y columnas, su índice secundario global también usará este tipo de almacenamiento de forma predeterminada.

Crear un índice secundario global

Sintaxis

CREATE GLOBAL INDEX [ IF NOT EXISTS ] index_name
  ON [schema_name.]table_name (index_column_name [, ...])
  [ INCLUDE (include_column_name[, ...]) ]

Parámetros

Parámetro	Obligatorio	Descripción
index_name	Sí	Nombre del índice secundario global.
schema_name	No	Nombre del esquema de la tabla de origen. Si no especifica este parámetro, se utiliza el esquema predeterminado.
table_name	Sí	Nombre de la tabla de origen.
index_column_name	Sí	Columnas que se van a indexar. Para obtener mejores resultados, utilice las columnas por las que filtra en consultas puntuales sobre claves no principales.
include_column_name	No	Columnas que se incluirán en el índice secundario global.

Notas de uso
- Después de enviar la sentencia SQL, el sistema comienza a compilar el índice. La operación CREATE GLOBAL INDEX finaliza solo cuando el índice se ha compilado y es visible.
- La compilación de un índice escribe datos adicionales, lo cual afecta el rendimiento de escritura. Este impacto aumenta según la cantidad de datos en la tabla de origen y el número de columnas del índice.
- Un índice secundario global siempre se crea en el mismo esquema que su tabla de origen. No es posible especificar un esquema diferente para el índice.
- Una consulta solo puede utilizar un índice secundario global si todas las columnas referenciadas están cubiertas por el índice, ya sea como columnas de índice o como columnas incluidas.

Eliminar un índice secundario global

Sintaxis
```
DROP INDEX [schema_name.]index_name
```

Parámetros

Parámetro	Obligatorio	Descripción
schema_name	No	Nombre del esquema del índice secundario global. Si no especifica este parámetro, se utiliza el esquema predeterminado.
index_name	Sí	Nombre del índice secundario global.

Consultar índices secundarios globales

Consultar todos los índices secundarios globales

SELECT 
    n.nspname AS table_namespace,
    t.relname AS table_name,
    i.relname AS index_name
FROM 
    pg_class t
JOIN 
    pg_index ix ON t.oid = ix.indrelid
JOIN 
    pg_class i ON i.oid = ix.indexrelid
JOIN 
    pg_am am ON am.oid = i.relam
JOIN
    pg_namespace n ON n.oid = t.relnamespace 
WHERE 
    t.relkind = 'r'  -- Query only regular tables.
    AND am.amname = 'globalindex'

Consultar el tamaño de almacenamiento del índice secundario global

Donde global_index_name es el nombre del índice secundario global.
```
SELECT pg_relation_size('schema_name.global_index_name');
```

Consultar las columnas incluidas

SELECT pg_catalog.pg_get_indexdef('global_index_name'::regclass, 0, true);

Ejemplos

Suponga que su aplicación de pedidos consulta frecuentemente datos por prioridad de pedido. El siguiente ejemplo utiliza la tabla orders:

Columna	Tipo	Descripción
O_ORDERKEY	BIGINT	ID del pedido (clave principal).
O_CUSTKEY	INT	ID del cliente (clave externa que referencia la tabla CUSTOMER).
O_ORDERSTATUS	CHAR(1)	Estado del pedido ('F' = Terminado, 'O' = Abierto, 'P' = En proceso).
O_TOTALPRICE	DECIMAL(15,2)	Precio total del pedido.
O_ORDERDATE	DATE	Fecha de creación del pedido.
O_ORDERPRIORITY	TEXT	Prioridad del pedido ('1-URGENT', '2-HIGH', etc.).
O_CLERK	TEXT	ID del empleado que procesó el pedido.
O_SHIPPRIORITY	INT	Prioridad de envío. Un valor mayor indica una prioridad más alta.
O_COMMENT	TEXT	Comentarios sobre el pedido.

Sentencia SQL para crear la tabla de ejemplo orders:

CREATE TABLE ORDERS
(
    O_ORDERKEY      BIGINT      NOT NULL PRIMARY KEY,
    O_CUSTKEY       INT         NOT NULL,
    O_ORDERSTATUS   CHAR(1)         NOT NULL,
    O_TOTALPRICE    DECIMAL(15,2) NOT NULL,
    O_ORDERDATE     DATE NOT NULL,
    O_ORDERPRIORITY TEXT        NOT NULL,
    O_CLERK         TEXT        NOT NULL,
    O_SHIPPRIORITY  INT         NOT NULL,
    O_COMMENT       TEXT        NOT NULL
) WITH (
  orientation='row,column',
  segment_key='O_ORDERDATE',
  distribution_key='O_ORDERKEY',
  bitmap_columns='O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY',
  dictionary_encoding_columns='o_comment:off,o_orderpriority,o_clerk'
);
COMMENT ON TABLE ORDERS IS 'Main order table that stores basic order information and status.';
COMMENT ON COLUMN ORDERS.O_ORDERKEY IS 'The order ID (primary key).';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS 'The customer ID (a foreign key that references the CUSTOMER table).';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS 'The order status (''F'' = Finished, ''O'' = Open, ''P'' = Processing).';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS 'The total price of the order.';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS 'The date when the order was created.';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS 'The order priority (''1-URGENT'', ''2-HIGH'', etc.).';
COMMENT ON COLUMN ORDERS.O_CLERK IS 'The ID of the employee who processed the order.';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'The shipping priority. A larger value indicates a higher priority.';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Comments about the order.';

Si ejecuta frecuentemente una consulta como la siguiente para obtener pedidos de una prioridad específica:

SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

Puede usar EXPLAIN para comprobar el plan de ejecución de una sentencia SQL:

EXPLAIN
SELECT 
  O_ORDERKEY,
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_ORDERPRIORITY,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'

QUERY PLAN
Gather  (cost=0.00..1.00 rows=1 width=53)
  ->  Local Gather  (cost=0.00..1.00 rows=1 width=53)
        ->  Index Scan using Clustering_index on orders  (cost=0.00..1.00 rows=1 width=53)
              Bitmap Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0

Es posible realizar consultas más eficientes agregando un índice a la columna O_ORDERPRIORITY.

El plan devuelto muestra que la consulta utilizó un Bitmap Index, lo cual ofrece una mejora limitada de rendimiento. Para lograr un QPS mayor, agregue un índice secundario global en la columna O_ORDERPRIORITY:
```
CREATE GLOBAL INDEX idx_orders ON orders(O_ORDERPRIORITY)
INCLUDE (
  O_CUSTKEY,
  O_ORDERSTATUS,
  O_TOTALPRICE,
  O_ORDERDATE,
  O_CLERK,
  O_SHIPPRIORITY,
  O_COMMENT
  );
```
Después de agregar el índice, ejecute nuevamente la sentencia EXPLAIN para comprobar el plan de ejecución:
```
QUERY PLAN
Local Gather  (cost=0.00..1.76 rows=3035601 width=99)
  ->  Index Scan using Clustering_index on idx_orders  (cost=0.00..1.54 rows=3035601 width=99)
        Shard Prune: Eagerly
        Shards selected: 1 out of 20
        Cluster Filter: (o_orderpriority = '1-URGENT'::text)
Query Queue: init_warehouse.default_queue
Optimizer: HQO version 4.0.0
```
Ahora el plan muestra que Index Scan using Clustering_index on apunta al índice secundario global idx_orders, y se aplica la poda de shards. Esto mejora eficazmente el QPS.

Utilice un Fixed Plan para mejorar aún más el QPS.

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;

El plan de ejecución ahora muestra un FixedSelectNode en el índice, lo que confirma que la consulta está optimizada con un Fixed Plan para obtener el máximo rendimiento.

SET hg_experimental_enable_fixed_dispatcher_for_scan = true;
EXPLAIN
SELECT
    O_ORDERKEY,
    O_CUSTKEY,
    O_ORDERSTATUS,
    O_TOTALPRICE,
    O_ORDERDATE,
    O_ORDERPRIORITY,
    O_CLERK,
    O_SHIPPRIORITY,
    O_COMMENT
FROM ORDERS
WHERE O_ORDERPRIORITY='1-URGENT'
QUERY PLAN
FixedSelectNode on idx_orders  (cost=0.00..0.00 rows=0 width=0)
  Qual: o_orderpriority =>  ranges: {['1-URGENT'::text,'1-URGENT'::text]}
Optimizer: HQO version 4.0.0