PolarDB:Automatic IMCI-based query acceleration (AutoIndex)

Présentation

La technologie d'index en colonnes (IMCI) de PolarDB for MySQL est conçue pour les requêtes complexes portant sur de grands volumes de données dans des scénarios OLAP. Grâce aux index en colonnes, PolarDB for MySQL intègre le traitement transactionnel en temps réel et l'analyse de données en temps réel au sein d'une solution de base de données HTAP (Hybrid Transactional and Analytical Processing) tout-en-un.

Traditionnellement, pour accélérer les tables impliquées dans des requêtes lentes, vous devez utiliser manuellement CREATE TABLE ou ALTER TABLE afin d'ajouter COLUMNAR=1 au champ COMMENT de la table pour créer un index en colonnes. Cette approche manuelle s'avère inefficace lorsque de nombreux modèles SQL présentent une logique complexe.

L'accélération automatique des requêtes basée sur IMCI (AutoIndex) ajoute automatiquement des index en colonnes aux tables cibles en fonction des requêtes lentes, ce qui permet d'accélérer les requêtes sans intervention manuelle.

Contexte technique

AutoIndex est une technique qui optimise les performances SQL en ajoutant automatiquement des index. Les index row store ont fait l'objet de recherches approfondies et sont implémentés commercialement, par exemple dans Oracle et SQL Server. Ces systèmes analysent les index secondaires susceptibles d'être ajoutés en fonction des prédicats, des clauses ORDER BY ou des jointures. Ils recalculent le coût de la requête en supposant qu'un tel index est créé, puis créent les index qui réduisent significativement le coût de la requête.

Dans le domaine des index en colonnes, AutoIndex a été adopté par des systèmes tels que Redshift, Snowflake, Databricks et Heatwave. Les méthodes d'automatisation typiques incluent :

• Chargement/déchargement automatique : Crée automatiquement des index en colonnes et accélère les requêtes lentes en fonction de l'exécution des requêtes. Supprime automatiquement les index en colonnes rarement utilisés ou consommant trop de ressources afin de réduire l'utilisation du stockage.

• Encodage automatique : Sélectionne automatiquement l'algorithme d'encodage optimal en fonction de la distribution des données de chaque colonne pour compresser davantage le stockage.

• Sélection automatique de la clé de distribution : Équilibre la distribution des données dans les scénarios MPP, réduit le skew des données et optimise le brassage des données pour les opérations GROUP BY et JOIN distribuées.

• Sélection automatique de la clé de tri : Utilise les prédicats fréquemment employés, les colonnes de jointure ou les colonnes de tri pour accélérer le filtrage ou le tri des requêtes.

Dans IMCI, la création automatique d'un index en colonnes coûte moins cher et est plus tolérante aux pannes que la création d'un index row store, pour les raisons suivantes :

• Impact réduit sur les écritures : Un nombre excessif d'index row store affecte considérablement les performances d'écriture sur le nœud principal, tandis qu'IMCI ne matérialise pas les données en colonnes sur le nœud principal. Après l'ajout d'un index en colonnes, les opérations d'écriture doivent uniquement être synchronisées avec le nœud d'index en colonnes en lecture seule via Redo, sans impact notable sur les performances d'écriture.

• Taux de compression élevé : Les index en colonnes atteignent généralement un taux de compression de 3 à 5 fois, de sorte que la surcharge de stockage liée aux nouveaux index est relativement faible.

• Impact limité sur les opérations métier : Des structures de données efficaces et sans verrou collectent les informations SQL Trace, et le DDL non bloquant (Nonblock DDL) réduit les perturbations des opérations métier.

Fonctionnement

SELECT * FROM information_schema.imci_autoindex

SELECT *
FROM information_schema.imci_autoindex autoindex, information_schema.sql_sharing share
WHERE autoindex.sql_id = share.sql_id
  AND share.type = 'SQL'

Résultats des tests

L'exemple suivant est basé sur l'ensemble de test TPC-H de 100 Go.

• Configuration de l'environnement : Exécutez les 22 requêtes de test TPC-H une fois par minute. Activez HTAP Optimization (Transactional/Analytical Processing Splitting) pour le point de terminaison du cluster et définissez le degré de parallélisme du row store sur 8, principalement pour réduire la durée du test.

• Résultats des tests : Le tableau ci-dessous montre que les performances des requêtes s'améliorent considérablement lors des cycles suivants. Durant la seconde partie du premier cycle, à partir de la 12e requête environ, AutoIndex avait créé des index en colonnes pour la plupart des tables, et l'optimiseur acheminait automatiquement ces requêtes vers le nœud d'index en colonnes en lecture seule.

Avantages

Les index en colonnes offrent une série d'avantages, avec des améliorations significatives en matière de compatibilité, de performances et de coûts :

• Compatibilité MySQL à 100 % : Prend en charge tous les types de données MySQL et est entièrement compatible avec le protocole MySQL.

• Excellentes performances HTAP : Améliore généralement les performances dans les scénarios analytiques d'un à deux ordres de grandeur.

• Stockage hybride row et column store, coût réduit : Garantit la cohérence transactionnelle grâce au stockage hybride row et column store, tandis que les index en colonnes offrent de meilleures performances à moindre coût dans certains scénarios de requête.

• Accélération automatique simplifiée : Permet une accélération automatique des performances en un clic, sans configuration ni réglage complexes.

• Réduction de la charge opérationnelle : Crée automatiquement des index en colonnes pour les requêtes lentes, ce qui réduit le travail manuel d'optimisation des requêtes lentes.

• Réduction des coûts informatiques : Optimise uniquement les requêtes lentes au lieu de créer des index en colonnes pour toutes les bases de données et tables, ce qui économise des ressources mémoire et de stockage.

Versions prises en charge

• Les clusters **Enterprise Edition** doivent satisfaire aux conditions suivantes :

  • Series : Cluster Edition.

  • Version du moteur de base de données :

    • MySQL 8.0.1, et la version mineure du moteur doit être 8.0.1.1.45.2 ou ultérieure.

    • MySQL 8.0.2, et la version mineure du moteur doit être 8.0.2.2.27 ou ultérieure.

• Les clusters **Standard Edition** doivent satisfaire aux conditions suivantes :

  • CPU architecture : x86.

  • Version du moteur de base de données : MySQL 8.0.1, et la version mineure du moteur doit être 8.0.1.1.45.2 ou ultérieure.

Remarques d'utilisation

• La Multi-master Cluster (Limitless) EditionSeries et les clusters Serverless ne prennent pas en charge l'accélération automatique des requêtes basée sur IMCI.

• Après avoir activé l'accélération automatique des requêtes basée sur IMCI :

  • Les frais liés aux nœuds d'index en colonnes en lecture seule ajoutés suivent les règles de facturation des nœuds de calcul standards.

  • Le système crée automatiquement des index en colonnes en fonction de vos instructions SQL lentes (les données d'index en colonnes occupent de l'espace de stockage).

  • Le système utilise la fonctionnalité SQL Trace pour enregistrer l'historique d'exécution des instructions SQL lentes. Dans la plupart des cas, cela ne consomme pas plus de 3 % des performances du nœud de base de données.

• L'accélération automatique des requêtes basée sur IMCI utilise en interne le DDL non bloquant pour ajouter des index en colonnes. Le DDL non bloquant permet aux nouvelles transactions d'accéder à la table cible même lorsqu'un verrou MDL-X ne peut pas être obtenu, ce qui maintient la stabilité de l'ensemble du système métier. Pour plus d'informations, consultez Instructions DDL non bloquantes.

Activer l'accélération automatique des requêtes basée sur IMCI

1. Connectez-vous à la console PolarDB. Dans le volet de navigation de gauche, cliquez sur Clusters. Sélectionnez la Region où le cluster est déployé, puis cliquez sur l'ID du cluster pour accéder à la page des détails du cluster.

2. Sur la page Basic Information, repérez le champ Automatic IMCI-based Query Acceleration et cliquez sur Enable.

3. Les étapes suivantes diffèrent selon que le cluster actuel dispose ou non d'un nœud d'index en colonnes en lecture seule :

   • Si le cluster actuel possède déjà un nœud d'index en colonnes en lecture seule, dans la boîte de dialogue Enable Automatic IMCI-based Query Acceleration, cliquez sur OK pour activer l'accélération automatique des requêtes basée sur IMCI.

   • Si le cluster actuel ne possède pas de nœud d'index en colonnes en lecture seule, dans la boîte de dialogue Enable Automatic IMCI-based Query Acceleration, cliquez sur OK. Vous êtes redirigé vers la page d'ajout d'un nœud d'index en colonnes en lecture seule.

     Remarque

     • Après avoir cliqué sur OK, vous pouvez immédiatement Ajouter un nœud IMCI en lecture seule ou Ajouter un nœud IMCI en lecture seule manuellement ultérieurement.

     • Après avoir activé l'accélération automatique des requêtes basée sur IMCI, le cluster actuel doit contenir au moins un nœud d'index en colonnes en lecture seule. Sinon, même si Automatic IMCI-based Query Acceleration est défini sur Enable, le service d'accélération n'est pas fourni.

     • Après avoir activé l'accélération automatique des requêtes basée sur IMCI, si vous n'avez pas ajouté de nœud d'index en colonnes en lecture seule, le système utilise la fonctionnalité SQL Trace pour enregistrer l'historique d'exécution des instructions SQL lentes, mais ne crée pas d'index en colonnes. Par conséquent, aucun service d'accélération n'est fourni.

Désactiver l'accélération automatique des requêtes basée sur IMCI

1. Connectez-vous à la console PolarDB. Dans le volet de navigation de gauche, cliquez sur Clusters. Sélectionnez la Region où le cluster est déployé, puis cliquez sur l'ID du cluster pour accéder à la page des détails du cluster.

2. Sur la page Basic Information, repérez le champ Automatic IMCI-based Query Acceleration et cliquez sur Close.

3. Dans la boîte de dialogue Disable Automatic IMCI-based Query Acceleration, cliquez sur OK pour désactiver l'accélération automatique des requêtes basée sur IMCI.