Présentation de la solution d'optimisation des grandes tables MySQL

Recommandations d'apprentissage gratuites : base de données mysql(vidéo )

Contexte

La table commerciale de la base de données Alibaba Cloud RDS FOR MySQL (MySQL version 5.7) contient plus de 10 millions de nouvelles données ajoutées chaque mois. À mesure que la quantité de données continue d'augmenter, des requêtes lentes sur de grandes tables se produisent dans notre entreprise. Pendant les périodes de pointe, les requêtes lentes sur la table commerciale principale prennent des dizaines de secondes, ce qui affecte sérieusement l'activité

Présentation. de la solution

1. Conception de la base de données et optimisation de l'index

La base de données MySQL elle-même est très flexible, ce qui entraîne performances insuffisantes et forte dépendance aux capacités de conception de table et d'indexation des développeurs, voici quelques suggestions d'optimisation

• Convertissez le type d'heure au format d'horodatage, stockez-le en type int, créez un index pour augmenter l'efficacité des requêtes
• Il est recommandé que la définition du champ ne soit pas nulle, les valeurs nulles sont difficiles à interroger et à optimiser et occupent un espace d'index supplémentaire
• Utilisez le type TINYINT au lieu de l'énumération ENUM
• Le stockage de nombres à virgule flottante précis doit utiliser DECIMAL au lieu de FLOAT et DOUBLE
• La longueur du champ est sérieuse. Selon les besoins de l'entreprise, ne la définissez pas trop grande
• Essayez de ne pas utiliser le type TEXT si vous le souhaitez. Si vous devez l'utiliser, il est recommandé de diviser les grands champs rarement utilisés en d'autres tables
• MySQL a des restrictions sur la longueur des champs d'index Oui, la longueur de chaque colonne d'index du moteur innodb est limitée à 767 octets. par défaut, et la somme des longueurs de toutes les colonnes d'index ne peut pas être supérieure à 3072 octets (un seul index mysql8.0 peut créer 1024 caractères)
• large Si la table a des exigences DDL, veuillez contacter le DBA

Règle de correspondance d'index la plus à gauche

Comme son nom l'indique, il s'agit de la priorité la plus à gauche lors de la création d'un index combiné, elle doit être utilisée dans la clause Where en fonction des besoins de l'entreprise. est placé à l'extrême gauche. Un problème très important dans un index composé est de savoir comment organiser l'ordre des colonnes. Par exemple, si les deux champs c1 et c2 sont utilisés après où, alors l'ordre de l'index est (c1, c2) ou (c2, c1). . La bonne approche est de répéter Plus la valeur est petite, plus elle est placée haut. Par exemple, si 95 % des valeurs d'une colonne ne sont pas répétées, alors cette colonne peut généralement être placée au premier plan

• Indice composé index(a,b,c)
• où a=3 seul a est utilisé
• où a=3 et b=5 est utilisé a, b
• où a=3 et b=5 et c=4 est utilisé a,b,c
• où b=3 ou où c=4 Aucun index n'est utilisé
• où a =3 et c=4 Seulement a
• où a= est utilisé 3 et b>10 et c=7 utilise a,b
• où a=3 et b comme 'xx%' et c=7 utilise a,b
• ce qui équivaut en fait à créer plusieurs index : key(a), key(a,b), key(a,b,c)

2. Basculez la base de données vers la séparation lecture-écriture PloarDB

PolarDB est une base de données cloud relationnelle de nouvelle génération auto-développée par Alibaba Cloud. Elle est 100 % compatible avec MySQL Le stockage. la capacité peut atteindre 100 To. Une seule base de données peut être étendue jusqu'à 16 nœuds. Elle convient à divers scénarios d'application de base de données des entreprises. PolarDB adopte une architecture qui sépare le stockage et l'informatique. Tous les nœuds informatiques partagent une copie des données et fournissent des mises à niveau et des rétrogradations de configuration infimes, une récupération après panne de deuxième niveau, une cohérence globale des données et des services gratuits de sauvegarde des données et de reprise après sinistre.

• Architecture de cluster, séparation du calcul et du stockage
  PolarDB adopte une architecture de cluster multi-nœuds. Il existe un nœud Writer (nœud maître) et plusieurs nœuds Reader (nœuds en lecture seule) dans le. cluster. Chaque nœud partage le stockage sous-jacent (PolarStore) via le système de fichiers distribué (PolarFileSystem)
• Séparation de la lecture et de l'écriture
  Lorsque l'application utilise l'adresse du cluster, PolarDB fournit des services externes via la couche proxy interne. (Proxy) et l'application Toutes les requêtes passent d'abord par le proxy puis accèdent au nœud de base de données. La couche proxy peut non seulement effectuer l'authentification et la protection de sécurité, mais également analyser SQL, envoyer des opérations d'écriture (telles que des transactions, UPDATE, INSERT, DELETE, DDL, etc.) au nœud maître et répartir uniformément les opérations de lecture (telles que SELECT ) vers plusieurs nœuds. Les nœuds de lecture réalisent une séparation automatique en lecture et en écriture. Pour les applications, c'est aussi simple que d'utiliser un seul point de base de données.

Dans des scénarios mixtes hors ligne : différents services utilisent différentes adresses de connexion et différents nœuds de données pour éviter une influence mutuelle

Rapport de test de résistance de performances Sysbench :

• PloarDB 4 cœurs 16G 2 unités

• PloarDB 8 cœurs 32G 2 unités

3. Migrer les données historiques des sous-tables vers le moteur de stockage MySQL8.0 X-Engine

La table commerciale divisée conserve 3 mois de données (cela est basé sur les besoins de l'entreprise) et les données historiques sont divisées en tables de moteur de stockage X-Engine de la base de données historique sur une base mensuelle. Pourquoi devrions-nous choisir les tables du moteur de stockage X-Engine. ? Quels sont ses avantages ?

1. En réduisant les coûts, le coût de stockage de X-Engine est environ la moitié de celui d'InnoDB
2. Le stockage hiérarchisé X-Engine améliore le QPS, en utilisant un stockage hiérarchique La structure permettant de combiner les données chaudes avec les données froides est stockée à différents niveaux, et le niveau où se trouvent les données froides est compressé par défaut

X-Engine est un traitement de transactions en ligne (OLTP) auto-développé par Alibaba Cloud Database Product Division Processing) moteur de stockage de base de données.
Le moteur de stockage X-Engine est non seulement parfaitement compatible avec MySQL (grâce à la fonctionnalité MySQL Pluginable Storage Engine), mais X-Engine utilise également une architecture de stockage en couches. Étant donné que l'objectif est de stocker des données massives à grande échelle, de fournir des capacités élevées de traitement des transactions simultanées et de réduire les coûts de stockage, dans la plupart des scénarios de volumes de données importants, les opportunités d'accès aux données sont inégales et les données chaudes fréquemment consultées représentent en réalité Très rarement, X-Engine divise les données en plusieurs niveaux en fonction de la fréquence d'accès aux données. Selon les caractéristiques d'accès de chaque niveau de données, il conçoit la structure de stockage correspondante et l'écrit sur le périphérique de stockage approprié

X-Engine utilise LSM-Tree comme base architecturale pour le stockage hiérarchique et a été repensé :
• La couche de données à chaud et les mises à jour des données utilisent le stockage en mémoire via la technologie de base de données en mémoire (index Lock-Free structure/ajout uniquement) améliore les performances de traitement des transactions.
• Le mécanisme de traitement des transactions du pipeline est parallèle à plusieurs étapes du traitement des transactions, améliorant considérablement le débit.
• Les données à faible fréquence d'accès sont progressivement éliminées ou fusionnées dans la couche de stockage persistante et combinées avec des périphériques de stockage multicouches (NVM/SSD/HDD) pour le stockage.
• De nombreuses optimisations ont été apportées au processus de compactage, ce qui a un impact important sur les performances :
• Divisez la granularité du stockage des données, utilisez les caractéristiques des points chauds de mise à jour des données relativement concentrés et réutilisez les données. autant que possible dans le processus de fusion.
• Contrôlez finement la forme du LSM, réduisez les coûts d'E/S et de calcul et atténuez efficacement l'augmentation de l'espace pendant le processus de fusion.
• Utilisez également des mécanismes de contrôle d'accès et de mise en cache plus précis pour optimiser les performances de lecture.

4. Requête parallèle d'Alibaba Cloud PloarDB MySQL8.0 version

Après avoir divisé les tables, notre volume de données est toujours très grand, cela ne résout pas complètement notre problème de requêtes lentes, mais réduit seulement la taille de nos tables métier. Pour ces requêtes lentes, nous devons utiliser l'optimisation des requêtes parallèles de PolarDB

PolarDB MySQL 8.0 lance le. cadre de requêtes parallèles, lorsque la quantité de données de votre requête atteint un certain seuil, le cadre de requêtes parallèles sera automatiquement démarré, réduisant ainsi de manière exponentielle le temps de requête

La couche de stockage fragmentera les données en différents threads et plusieurs threads fonctionneront calculs parallèles. Les résultats du pipeline sont résumés dans le thread principal, et enfin le thread principal effectue une simple fusion et la renvoie à l'utilisateur pour améliorer l'efficacité des requêtes.
Parallel Query utilise les capacités de traitement parallèle des processeurs multicœurs. En prenant comme exemple la configuration à 8 cœurs de 32 Go, le diagramme schématique est le suivant.

Les requêtes parallèles conviennent à la plupart des instructions SELECT, telles que les requêtes de grandes tables, les requêtes de jointure multi-tables et les requêtes avec des charges de calcul importantes. Pour les requêtes très courtes, l’effet est moins perceptible.

Utilisation des requêtes parallèles, utilisez la syntaxe Hint pour contrôler une seule instruction. Par exemple, lorsque le système désactive les requêtes parallèles par défaut, mais que vous devez accélérer une requête SQL lente à haute fréquence, vous pouvez utiliser Hint. vers SQL spécifique est accéléré.

SELECT /

+PARALLEL(x)/ … FROM …; – x >0

SELECT /*+ SET_VAR(max_parallel_degree=n) */ * FROM … // n > 0

Test de requête : configuration de la base de données 16 cœurs 32G, le volume de données d'une seule table dépasse 30 millions

C'était 4326 ms avant l'ajout de la requête parallèle, et 525 ms après l'ajout l'optimisation des requêtes parallèles 8,24X a amélioré l'efficacité, mais nous ne pouvons toujours pas répondre à certaines exigences spécifiques en matière de rapports en temps réel et de grands écrans en temps réel, et ne pouvons compter que sur le Big Data pour le traitement.

Nous recommandons ici l'analyse interactive Holologre d'Alibaba Cloud (

https://help.aliyun.com/product/113622.html)

6 . Postscript

L'optimisation de dizaines de millions de grandes tables est basée sur des scénarios commerciaux et au prix d'un coût. Il ne s'agit pas dès le départ de diviser et d'étendre horizontalement la base de données, ce qui entraînera des problèmes. pour l'exploitation, la maintenance et les affaires. Dans de nombreux cas, les résultats peuvent ne pas être bons. Que nos stratégies de conception de bases de données, d'optimisation d'index et de partitionnement de tables soient en place, nous devons choisir la technologie appropriée pour les mettre en œuvre en fonction de l'activité. besoins.

Plus de recommandations d'apprentissage gratuites associées : tutoriel MySQL(vidéo)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!