PolarDB:Exécuter des instructions DDL pour créer et supprimer dynamiquement un IMCI

Prérequis

Avant de commencer, assurez-vous de disposer des éléments suivants :

• Nœuds de stockage en colonnes en lecture seule ajoutés à votre cluster. Consultez Ajouter un nœud de stockage en colonnes en lecture seule.

• Endpoint de cluster configuré pour votre stratégie de distribution des requêtes. Consultez Présentation de la distribution des requêtes.

• Connexion au cluster établie via l'endpoint du cluster. Consultez Se connecter à un cluster.

Types de données et versions pris en charge

Les types de données suivants sont pris en charge selon votre version de PolarDB for MySQL :

Si une table ou une colonne possède déjà un commentaire, ajoutez COLUMNAR=1 (ou COLUMNAR=0) au début du contenu existant. Par exemple, COMMENT 'abc' devient COMMENT 'COLUMNAR=1abc'.

Créer un IMCI

Ajoutez COMMENT 'COLUMNAR=1' à une instruction ALTER TABLE pour créer un IMCI. La portée dépend de l'emplacement du commentaire :

• IMCI au niveau de la table : ajoutez le commentaire à ALTER TABLE. L'IMCI couvre alors toutes les colonnes dont les types de données sont pris en charge.

• IMCI au niveau de la colonne : ajoutez le commentaire à ALTER TABLE ... MODIFY COLUMN .... L'IMCI ne couvre que les colonnes spécifiées.

Si votre cluster est connecté via Data Management (DMS), n'utilisez pas le processus de modification de schéma sans verrouillage pour modifier le champ COMMENT.

Créer un IMCI au niveau de la table :

CREATE TABLE t5(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Create an IMCI for the entire table
ALTER TABLE t5 COMMENT 'COLUMNAR=1';

Créer un IMCI au niveau de la colonne :

-- Create an IMCI for specified columns only
ALTER TABLE t5 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=1',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=1';

Supprimer un IMCI

Définissez COMMENT 'COLUMNAR=0' pour supprimer un IMCI, en utilisant la même syntaxe que pour la création :

• Au niveau de la table : ajoutez le commentaire à ALTER TABLE.

• Au niveau de la colonne : ajoutez le commentaire à ALTER TABLE ... MODIFY COLUMN ....

Supprimer un IMCI au niveau de la colonne :

CREATE TABLE t6(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Remove the IMCI from specific columns
ALTER TABLE t6 MODIFY COLUMN col1 INT COMMENT 'COLUMNAR=0',
               MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Supprimer un IMCI au niveau de la table :

CREATE TABLE t7(
  col1 INT,
  col2 DATETIME,
  col3 VARCHAR(200)
) ENGINE InnoDB COMMENT 'COLUMNAR=1';

-- Remove the table-level IMCI
ALTER TABLE t7 COMMENT 'COLUMNAR=0';

Modifier la définition d'un IMCI

Ajoutez ou supprimez des colonnes individuelles d'un IMCI existant à l'aide de ALTER TABLE ... MODIFY COLUMN ... :

• COMMENT 'COLUMNAR=1' ajoute la colonne à l'IMCI.

• COMMENT 'COLUMNAR=0' retire la colonne de l'IMCI.

CREATE TABLE t8(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Add col3 to the IMCI
ALTER TABLE t8 MODIFY COLUMN col3 VARCHAR(200) COMMENT 'COLUMNAR=1';

-- Remove col2 from the IMCI
ALTER TABLE t8 MODIFY COLUMN col2 DATETIME COMMENT 'COLUMNAR=0';

Créer un IMCI pour la majorité des colonnes d'une table

Pour les charges de travail OLAP, les tables comportent souvent de nombreuses colonnes à couvrir par un IMCI. Plutôt que de lister chaque colonne individuellement, définissez COMMENT 'COLUMNAR=1' au niveau de la table (cela couvre par défaut toutes les colonnes aux types de données pris en charge) et utilisez COMMENT 'COLUMNAR=0' pour exclure des colonnes spécifiques.

Exemple : Création d'un IMCI pour toutes les colonnes sauf col7.

CREATE TABLE t9(
  col1 INT, col2 INT, col3 INT,
  col4 DATETIME, col5 TIMESTAMP,
  col6 CHAR(100), col7 VARCHAR(200),
  col8 TEXT, col9 BLOB
) ENGINE InnoDB;

Exécutez deux instructions ALTER TABLE distinctes pour obtenir de meilleures performances :

-- Step 1: Exclude the column that should not be in the IMCI
ALTER TABLE t9 MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0';

-- Step 2: Create the table-level IMCI
ALTER TABLE t9 COMMENT 'COLUMNAR=1';

Combiner les deux opérations dans une seule instruction, par exemple ALTER TABLE t9 COMMENT 'COLUMNAR=1', MODIFY COLUMN col7 VARCHAR(200) COMMENT 'COLUMNAR=0', déclenche le mode de reconstruction en ligne d'InnoDB Online DDL, ce qui réduit considérablement les performances. Utilisez plutôt deux instructions distinctes.

Après exécution des instructions, vérifiez le résultat avec SHOW CREATE TABLE t9 FULL\G. La sortie confirme les colonnes incluses dans l'IMCI :

SHOW CREATE TABLE t9 FULL\G
*************************** 1. row ***************************
      Table: t9
Create Table: CREATE TABLE `t9` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL,
  `col3` int(11) DEFAULT NULL,
  `col4` datetime DEFAULT NULL,
  `col5` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `col6` char(100) DEFAULT NULL,
  `col7` varchar(200) DEFAULT NULL COMMENT 'COLUMNAR=0',
  `col8` text,
  `col9` blob,
  COLUMNAR INDEX  (`col1`,`col2`,`col3`,`col4`,`col5`,`col6`,`col8`,`col9`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'COLUMNAR=1'

La colonne col7 est exclue de l'IMCI, comme l'indiquent son attribut COMMENT 'COLUMNAR=0' et son absence dans la définition de COLUMNAR INDEX.

Créer un IMCI lors de l'ajout de colonnes

Lorsque vous ajoutez de nouvelles colonnes avec ALTER TABLE ADD COLUMN, incluez COMMENT 'COLUMNAR=1' pour les ajouter automatiquement à l'IMCI.

CREATE TABLE t10(
  col1 INT COMMENT 'COLUMNAR=1',
  col2 DATETIME COMMENT 'COLUMNAR=1',
  col3 VARCHAR(200)
) ENGINE InnoDB;

-- Add col4 and include it in the IMCI
ALTER TABLE t10 ADD col4 DATETIME DEFAULT NOW() COMMENT 'COLUMNAR=1';

Utiliser INSTANT DDL avec les tables IMCI

Le comportement d'INSTANT DDL lors de l'ajout ou de la suppression de colonnes sur une table dotée d'un IMCI dépend de votre version de PolarDB for MySQL.

Pour les versions antérieures à 8.0.1.1.42 et 8.0.2.2.23 :

INSTANT DDL est désactivé par défaut pour les tables IMCI. Sans INSTANT DDL, l'ajout ou la suppression d'une colonne nécessite la reconstruction de l'IMCI. Ce dernier reste disponible pendant la reconstruction.

Pour activer INSTANT DDL, utilisez l'une des méthodes suivantes :

• Exécutez l'instruction suivante dans votre base de données :

  SET imci_enable_add_column_instant_ddl = ON

• Dans la console PolarDB, accédez à la page Parameters et définissez loose_imci_enable_add_column_instant_ddl sur ON.

Une fois INSTANT DDL activé, l'IMCI est reconstruit de manière asynchrone en arrière-plan lors de l'ajout ou de la suppression de colonnes. L'IMCI au niveau de la table est temporairement indisponible jusqu'à la fin de la reconstruction. Les performances des nœuds de stockage en lignes en lecture seule ne sont pas affectées.

Pour les versions 8.0.1.1.42 ou ultérieures et 8.0.2.2.23 ou ultérieures :

INSTANT DDL est activé par défaut pour les tables IMCI. Ce mode n'est pas compatible avec le mode de reconstruction d'origine. Pour utiliser le mode de reconstruction, définissez imci_enable_add_column_instant_ddl sur OFF et assurez-vous que la table possède une clé primaire.

Vérifier la progression de la construction de l'IMCI

La création d'un IMCI s'exécute comme une opération DDL asynchrone. Une fois l'instruction DDL terminée sur le nœud principal, les modifications des métadonnées sont répliquées vers le nœud de stockage en colonnes en lecture seule via les journaux Redo, et des threads d'arrière-plan commencent à construire l'IMCI de manière concurrente.

Pendant cette période :

• Les requêtes OLAP s'exécutent sur le nœud de stockage en lignes en lecture seule (l'IMCI n'est pas encore disponible).

• Une fois l'IMCI entièrement construit, les requêtes OLAP basculent vers le nœud de stockage en colonnes en lecture seule.

Pour vérifier si l'IMCI est prêt, interrogez INFORMATION_SCHEMA.IMCI_INDEXES sur le nœud de stockage en colonnes en lecture seule.

Exemple :

CREATE TABLE t11(
  col1 INT, col2 DATETIME, col3 VARCHAR(200)
) ENGINE InnoDB;

ALTER TABLE t11 COMMENT 'COLUMNAR=1';

-- Check IMCI build state
SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME = 't11';

Pour les tables partitionnées, utilisez un modèle LIKE :

SELECT * FROM INFORMATION_SCHEMA.IMCI_INDEXES WHERE TABLE_NAME LIKE '%t1%';

Exemple de sortie :

+--------+-----------+----------+--------+---------+------+----------+--------+
|TABLE_ID|SCHEMA_NAME|TABLE_NAME|NUM_COLS|PACK_SIZE|ROW_ID|STATE     |MEM_SIZE|
+--------+-----------+----------+--------+---------+------+----------+--------+
|    xxxx| test      | t11      |       3|    65536|     0|RECOVERING|    0   |
+--------+-----------+----------+--------+---------+------+----------+--------+

Pour obtenir des détails sur la progression de la construction, consultez Afficher la vitesse d'exécution DDL et la progression de la construction des IMCI.

Étapes suivantes

• Afficher la vitesse d'exécution DDL et la progression de la construction des IMCI

• Présentation de la distribution des requêtes