Il s'agira d'optimiser la base de données MySQL.
Cela s'est produit lorsque nous avons créé un système de newsletters par courrier électronique. Notre système était censé envoyer des dizaines de millions de lettres par jour. L'envoi d'une lettre n'est pas une tâche facile, même si tout semble assez primitif:
- Collectez une lettre de création html, substituez des données personnalisées.
- Ajoutez un pixel d'affichage du message, remplacez tous les liens du message par le vôtre - pour suivre les clics.
- Vérifiez avant d'envoyer que l'e-mail ne figure pas dans la liste noire.
- Envoyez un e-mail à un pool spécifique.
Je vais vous en dire plus sur le deuxième paragraphe:
Le constructeur de courrier électronique Microservice prépare une lettre à envoyer:
- trouve tous les liens dans la lettre;
- un uuid unique de 32 caractères est généré pour chaque lien;
- remplace le lien d'origine par un nouveau et enregistre les données dans la base de données.
Ainsi, tous les liens source seront remplacés par uuid et le domaine sera changé pour le nôtre. Lorsque vous recevez une demande GET en utilisant ce lien, nous procurons l'image d'origine ou la redirige vers le lien d'origine. L'enregistrement a lieu dans la base de données MySQL, nous enregistrons l'uuid généré avec le lien d'origine et certaines méta-informations (email de l'utilisateur, identifiant de mailing et autres données). La dénormalisation nous aide en une seule demande à obtenir toutes les données nécessaires pour enregistrer les statistiques ou à démarrer une sorte de chaîne de déclenchement.
Problème numéro 1
La génération de uuid en nous dépendait de l'horodatage.
Étant donné que les envois postaux ont généralement lieu dans un certain laps de temps et que de nombreux cas de microservice pour assembler une lettre sont lancés, il s'est avéré que certains des uuids étaient très similaires.
Cela a donné une faible sélectivité. UPD: parce que les données étaient similaires, travailler avec le bi-arbre n'était pas très efficace.
Nous avons résolu ce problème en utilisant le module uuid en python, où il n'y a pas de dépendance temporelle.
Une telle chose implicite a réduit la vitesse des index.
Comment se passe le stockage?
La structure du tableau était la suivante:
CREATE TABLE IF NOT EXISTS `Messages` ( `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`UUID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Au moment de la création, tout semblait logique:
UUID est une clé primaire et est également un index cluster. Lorsque nous faisons une sélection sur ce champ, nous sélectionnons simplement l'enregistrement, car toutes les valeurs sont stockées juste là. C'était une décision délibérée.
En savoir plus sur l'index clusterisé.Tout était super jusqu'à ce que la table grossisse.
Problème numéro 2
Si vous en savoir plus sur l'index de cluster, vous pouvez découvrir cette nuance:
Lors de l'ajout d'une nouvelle ligne au tableau, elle est ajoutée non pas à la fin du fichier, pas à la fin de la liste plate, mais à la branche souhaitée de l'arborescence qui lui correspond par tri.
Ainsi, avec l'augmentation de la charge, le temps d'insertion a augmenté.
La solution était d'utiliser une structure de table différente.
CREATE TABLE IF NOT EXISTS `Messages` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `UUID` varchar(32) NOT NULL, `Message` json NOT NULL, `Inserted` DATE NOT NULL, PRIMARY KEY (`ID`), UNIQUE KEY `UUID` (`UUID`, `Inserted`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Étant donné que la clé primaire est désormais auto-incrémentée et que mysql stocke le cache du dernier emplacement d'insertion, maintenant l'insertion se produit toujours à la fin, c'est-à-dire que Innodb est optimisé pour écrire des valeurs augmentant séquentiellement.
J'ai trouvé les détails de cette optimisation dans
le code source de postgres. Mysql implémente une optimisation très similaire.
Bien sûr, j'ai dû ajouter une clé unique pour qu'il n'y ait pas de conflits, mais nous avons augmenté la vitesse d'insertion.
La base augmentant encore plus, nous avons pensé à supprimer les anciennes données. L'utilisation de DELETE sur le champ Inséré n'est absolument pas optimale - c'est un temps très long, et l'espace ne sera pas libéré tant que nous n'exécuterons pas la commande d'
optimisation de table . Soit dit en passant, cette opération bloque complètement la table - cela ne nous convenait pas du tout.
Par conséquent, nous avons décidé de diviser notre table en partitions.
1 jour - 1 partition, les anciennes tombent automatiquement le moment venu.
Problème numéro 3
Nous avons eu la possibilité de supprimer les anciennes données, mais nous n'avons pas eu la possibilité de choisir dans la partition souhaitée, car avec select`e, nous spécifions uniquement uuid, mysql ne sait pas dans quelle partition nous devons le rechercher et recherche en tout.
La solution est née du problème n ° 1 - ajoutez un horodatage à l'uuid généré. Seulement cette fois, nous avons fait un peu différemment: nous avons inséré un horodatage à un endroit aléatoire sur la ligne, pas au début ni à la fin; avant et après ils ont ajouté
un symbole de tiret afin qu'il puisse être obtenu avec une expression régulière.
Grâce à cette optimisation, nous avons pu obtenir la date de génération de l'uuid et déjà effectuer une sélection indiquant la valeur spécifique du champ inséré. Maintenant, nous lisons les données immédiatement à partir de la partition dont nous avons besoin.
De plus, grâce à des choses comme
ROW_FORMAT = COMPRESSED et en changeant l'encodage en
latin1 , nous avons économisé encore plus d'espace sur le disque dur.