PolarDB:Créer et utiliser un CCI

CREATE
    CLUSTERED COLUMNAR INDEX index_name
    ON tbl_name (index_sort_key_name,...)
    [partition_options]

-- Define a partitioning policy
partition_options:
    PARTITION BY
          HASH({column_name | partition_func(column_name)})
        | KEY(column_list)
        | RANGE({column_name | partition_func(column_name)})
        | RANGE COLUMNS(column_list)
        | LIST({column_name | partition_func(column_name)})
        | LIST COLUMNS(column_list)
    partition_list_spec

-- Define a partitioning function
partition_func:
    YEAR
  | TO_DAYS
  | TO_SECOND
  | UNIX_TIMESTAMP
  | MONTH

-- Define a partition list
partition_list_spec:
    hash_partition_list
  | range_partition_list
  | list_partition_list

-- For HASH/KEY partitioned tables
hash_partition_list:
    PARTITIONS partition_count

-- For RANGE/RANGE COLUMNS partitioned tables
range_partition_list:
    range_partition [, range_partition ...]

range_partition:
    PARTITION partition_name VALUES LESS THAN {(expr | value_list)} [partition_spec_options]

-- For LIST/LIST COLUMNS partitioned tables
list_partition_list:
    list_partition [, list_partition ...]

list_partition:
    PARTITION partition_name VALUES IN (value_list) [partition_spec_options]

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;

CREATE CLUSTERED COLUMNAR INDEX `cc_i_seller` ON t_order (`seller_id`) partition by hash(`order_id`) partitions 16;

Vérifier l'état de création du CCI

Après avoir exécuté l'instruction CREATE CLUSTERED COLUMNAR INDEX, vérifiez l'état de construction :

SHOW COLUMNAR INDEX FROM t_order;

Pour surveiller la progression de la tâche DDL :

SHOW DDL;

Le CCI est prêt à l'emploi lorsque son statut indique qu'il est terminé. Pour obtenir une description des champs de sortie, consultez SHOW COLUMNAR INDEX et SHOW DDL.

Sélection automatique

Pour les requêtes portant sur une table principale contenant un CCI, PolarDB-X sélectionne automatiquement la table d'index dont le coût estimé par l'optimiseur est le plus faible. Actuellement, seules les instances columnstore en lecture seule prennent en charge les requêtes basées sur un CCI. Aucune configuration supplémentaire n'est nécessaire.

USE INDEX

Recommandez à l'optimiseur d'utiliser l'index spécifié :

USE INDEX({index_name},...)

Exemple — privilégier cc_i_seller :

SELECT t_order.id, t_order.order_snapshot
FROM t_order USE INDEX(cc_i_seller)
WHERE t_order.seller_id = 's1';

IGNORE INDEX

Indiquez à l'optimiseur d'ignorer l'index spécifié :

IGNORE INDEX({index_name},...)

Exemple — exclure cc_i_seller de la sélection :

SELECT t_order.id, t_order.order_snapshot
FROM t_order IGNORE INDEX(cc_i_seller)
WHERE t_order.seller_id = 's1';

FORCE INDEX

Outrepassez l'optimiseur pour imposer l'utilisation de l'index spécifié :

FORCE INDEX({index_name})

Exemple — forcer cc_i_seller dans une jointure :

SELECT a.*, b.order_id
FROM t_seller a
  JOIN t_order b FORCE INDEX(cc_i_seller) ON a.seller_id = b.seller_id
WHERE a.seller_nick = 'abc';

Types de données pris en charge

Le tableau suivant présente les types de données pris en charge pour la clé primaire de la table principale, ainsi que pour les clés de tri et de partitionnement du CCI :

Les algorithmes de partitionnement prennent en charge différents types de données. Pour plus d'informations, consultez Types de données .

Limites des instructions DDL

Pour contrôler l'exécution des instructions DDL sur une table possédant un CCI, définissez le paramètre suivant (true autorise les DDL ; false les bloque) :

SET [GLOBAL] forbid_ddl_with_cci = [true | false];

Opérations DDL prises en charge sur les tables principales dotées d'un CCI :

Contraintes de modification des colonnes avec ALTER TABLE :

À partir de la version 5.4.20-20250714, MODIFY COLUMN et les modifications de valeurs par défaut des colonnes sont prises en charge pour certaines clés primaires, clés de partitionnement CCI et clés de tri CCI.

La modification d'une colonne critique (clé primaire, clé de partitionnement CCI ou clé de tri CCI) peut déclencher une reconstruction complète du CCI, opération potentiellement longue sur les tables volumineuses. Cette fonctionnalité est désactivée par défaut. Nous vous recommandons d'utiliser l'exécution asynchrone. Pour l'activer, définissez les paramètres suivants avant d'exécuter l'instruction ALTER :

-- Allow modifying critical columns of a CCI
SET ENABLE_MODIFY_CCI_CRITICAL_COLUMN = TRUE;
-- Rebuild strategy: 0 lets the system select automatically
SET REBUILD_CCI_STRATEGY = 0;
-- Maximum number of CCIs per table; increase if the table already has multiple CCIs
SET MAX_CCI_COUNT = 2;

Types de données pris en charge pour MODIFY COLUMN / CHANGE COLUMN :

Si vous devez convertir une colonne vers le type POINT ou GEOMETRY, supprimez le CCI à l'aide de DROP INDEX, modifiez le type de données, puis recréez le CCI.

Modifications d'index prises en charge avec ALTER TABLE :