Est-il judicieux de créer des tableaux différents pour les mêmes données pour chaque date ?

Question

J'ai une table MYSQL InnoDBtable avec les colonnes suivantes (les noms des tables et des colonnes ont été modifiés) :

• Date (PK, date)
• var_a (PK, FK, INT)
• var_b (PK, FK, INT)
• rel_ab(décimal)

où rel_ab 是描述给定日期 2 个变量 var_a 和 var_b 之间关系的列。 （var_a 和 var_b fait référence à différents tableaux)

Les données sont téléchargées par lots chaque jour, totalisant environ 7 millions de lignes par jour. Le problème était qu’après seulement quelques semaines, le téléchargement de chaque nouveau lot quotidien commençait à prendre des heures. De toute évidence, nous devons améliorer la conception de notre table. Voici quelques détails supplémentaires sur notre formulaire.

• Nous utilisons COMPRESSION="zlib".
• En plus de la clé primaire composite, nous créons également l'index requis pour la clé étrangère sur la colonne var_a 和 var_b.
• Lorsque nous extrayons les données de ce tableau, utilisez toujours pour une date donnée 的查询 SELECT * FROM table WHERE date = . La sélection ne prend que quelques minutes.
• Nous n'aurons (presque certainement) jamais de raison de supprimer une entrée de la table référencée par var_a 和 var_b .
• Les données sont téléchargées par la fonction pandas df.to_sql('temp', con, if_exists='replace', index=False, method='multi'), où nous insérons ignorer temp< /code> à df.to_sql('temp', con, if_exists='replace', index=False, method='multi') 上传，我们在其中插入忽略 < code>temp 到 table，然后删除 temp, puis supprimez temp.

Je prévois donc de faire au moins une des opérations suivantes :

• Supprimez la contrainte de clé étrangère sur la colonne var_a 和 var_b et comptez sur le processus de téléchargement des données pour tout faire correctement. En effet, aucun des deux index n'améliore réellement la vitesse des requêtes dans notre cas d'utilisation.
• Divisez le tableau en différents tableaux pour chaque date. Par exemple, j'ai une colonne appelée table_230501 的表，其中包含 var_a、var_b、rel_ab. En effet, nous ne sélectionnons qu'une seule date à la fois.

Je sais que la première solution peut menacer l'intégrité des données et que la seconde solution va gâcher notre architecture. D'après mon expérience limitée, je n'ai jamais entendu parler de la deuxième option non plus et je ne trouve aucun exemple de cette conception en ligne. L’une de ces options constitue-t-elle une solution raisonnable ? Les deux augmenteront les vitesses de téléchargement et réduiront l’utilisation du disque, mais les deux ont leurs inconvénients. Sinon, quels autres moyens existe-t-il pour augmenter la vitesse de téléchargement ?

EDIT : Le mien SHOW CREATE TABLE devrait ressembler à

CREATE TABLE table (
  date date NOT NULL,
  var_a int NOT NULL,
  var_b int NOT NULL,
  rel_ab decimal(19,16) NOT NULL,
  PRIMARY KEY (date,`var_a`,`var_b`),
  KEY a_idx (var_a),
  KEY b_idx (var_b),
  CONSTRAINT a FOREIGN KEY (var_a) REFERENCES other_table_a (var_a) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT b FOREIGN KEY (var_b) REFERENCES other_table_b (var_b) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMPRESSION="zlib"

Answer 1

Il existe quelques solutions potentielles qui peuvent vous aider à améliorer la vitesse de téléchargement de vos tables MySQL :

Supprimez les index sur var_a et var_b : puisque vous n'utilisez pas ces index pour accélérer les requêtes, leur suppression peut aider à accélérer le processus de téléchargement. Cependant, si vous utilisez des contraintes de clé étrangère, il est généralement recommandé de conserver les index sur les colonnes appartenant à la clé étrangère.

Partitionnez la table par date : le partitionnement contribue à améliorer les performances des requêtes car il permet à la base de données d'analyser uniquement les partitions pertinentes pour une requête donnée. Cependant, cela rend également la maintenance et les sauvegardes plus complexes, ce qui peut ne pas être nécessaire si vos requêtes fonctionnent déjà bien.

Utilisez la méthode d'insertion en masse : au lieu d'insérer des lignes individuelles à l'aide de df.to_sql, vous pouvez essayer d'utiliser une méthode d'insertion en masse telle que LOAD DATA INFILE ou l'API d'insertion en masse MySQL. C'est plus rapide que l'insertion individuelle, surtout si vous pouvez télécharger les données par lots plutôt qu'une ligne à la fois.

Utilisez un algorithme de compression différent : vous utilisez actuellement la compression zlib, mais il existe d'autres algorithmes de compression qui peuvent être plus rapides ou plus efficaces pour vos données. Vous pouvez essayer différentes options de compression pour voir si elles améliorent les vitesses de téléchargement.

Augmentez les ressources du serveur : si vous disposez du budget et des ressources, la mise à niveau du matériel du serveur ou l'augmentation du nombre de serveurs peut aider à augmenter les vitesses de téléchargement. Ce n'est peut-être pas une option viable pour tout le monde, mais cela vaut la peine d'y réfléchir si vous avez épuisé vos autres options.

En ce qui concerne l'option suggérée, la suppression des contraintes de clé étrangère peut entraîner des problèmes d'intégrité des données, je ne recommande donc pas cette approche. Si votre requête rencontre déjà des problèmes de performances, le partitionnement par date peut être une bonne solution, mais si votre requête s'exécute déjà rapidement, cela n'est peut-être pas nécessaire.

Answer 2

Pour accélérer les téléchargements, supprimez-les. Sérieusement, si la seule chose que vous faites est d'obtenir exactement le contenu d'un fichier à une certaine date, pourquoi mettre les données dans un tableau ? (Votre commentaire souligne qu'un seul fichier est en fait constitué de plusieurs fichiers. Cela pourrait être une bonne idée de les combiner d'abord.)

Si vous avez vraiment besoin des données du tableau, discutons-en...

• Avant de déterminer l'index, nous devrons examiner toutes les requêtes majeures.
• L'ordre des colonnes dans PK est important à la fois pour le chargement et l'interrogation.
• Le partitionnement peut faciliter le chargement, mais il est peu probable qu'il facilite les requêtes. Exceptions : supprimez-vous les "anciennes" données ?
• Veuillez fournir显示创建表 ; il se peut que certaines subtilités manquent dans ce que vous fournissez.
• Comment se déroule le chargement ? Un énorme 加载数据 ? J'espère ne pas insérer une ligne à la fois. Je ne sais pas comment fonctionnent les pandas. (Vous ne savez pas non plus comment fonctionnent les 99 autres packages qui « simplifient » l'accès à MySQL.) Veuillez comprendre ce qu'il fait dans les coulisses. Vous devrez peut-être contourner Pandas pour obtenir de meilleures performances. Le chargement en vrac est au moins 10 fois plus rapide que le chargement ligne par ligne.
• Je n'ai pas vu la nécessité d'une table temporaire lors du chargement. Peut être. Si vous supprimez le FK (comme vous l'avez suggéré), vous pouvez exécuter des requêtes pour vérifier l'existence de var_a et var_b dans d'autres tables. C'est "FK analogique".
• Si possible, triez les données entrantes en fonction de PK. (Cela pourrait être la source d'une croissance économique lente.)
• Y a-t-il des touches auxiliaires ? Ils affectent la vitesse de chargement.
• Je pense que votre FK fait allusion aux index sur d'autres tables.
• Ajoutez-vous de nouvelles lignes à d’autres tableaux ?
• "rel_ab (DECIMAL)" - combien de décimales ? Quelle est la déclaration exacte ? S'il s'agit d'une sorte de mesure, avez-vous pensé à FLOAT ?
• Il y a maintenant de nombreuses lignes dans d'autres tableaux. Autrement dit, avez-vous vraiment besoin d'un INT de 4 octets pour les référencer ? Passez à 3 octets MEDIUMINT [UNSIGNED] Économisez au moins 7 Mo par jour.
• Comment gérez-vous les 7 millions de lignes de ce SELECT ?
• Pas de compression. InnoDB est très inefficace. Une seule des 4 colonnes peut être compressible. La compression nécessite un buffer_pool_space supplémentaire. La compression utilise beaucoup de CPU. Pour InnoDB, un retrait 2x est typique.

Plusieurs tables « identiques » sont toujours imprudentes. Une table, c'est toujours mieux. Cependant, comme suggéré ci-dessus, une table nulle est toujours préférable.