[JAVA] J'ai essayé d'assembler à partir de 0 tout en considérant le côté performance de la conception de la table de la fonction d'affichage jour / semaine / mois / classement général

1.Tout d'abord

Actuellement, nous développons quelque chose comme un service de publication d'articles en tant que création de portfolio, et comme l'une des fonctions, nous avons créé une fonction pour afficher des classements tels que jour / semaine / mois à l'écran (ci-après dénommée fonction d'affichage de classement). Il y a. Le contenu de la conception de table + α qui a été fait pour créer la fonction d'affichage de classement est décrit dans cet article.

J'ai écrit cet article dans le but de documenter le processus de réflexion, en pensant que ce sera peut-être pour moi à l'avenir qui aura l'occasion de développer des fonctions similaires à l'avenir, ou pour ceux qui créeront des fonctions similaires à l'avenir. ing.

• Bien que l'affiche se soit spécialisée dans le domaine de l'information depuis qu'il était étudiant et qu'il ait des connaissances, il s'agit d'une application Java qui n'utilise pas DB quand il était étudiant, et dans la pratique après l'emploi, il conçoit et développe également des fonctions qui gèrent DB. C'est en fait la première fois que je conçois correctement une table car je n'y suis jamais allée (j'ai porté une sorte d'obscurité sur mon dos). Nous vous serions reconnaissants de bien vouloir nous faire part de vos opinions et suggestions. Aussi, pardonnez-moi car c'est le premier message de Qiita.

2. Hypothèse: aperçu de la fonction d'affichage du classement

À titre de prémisse, la fonction d'affichage du classement doit afficher le classement de 5 éléments quotidiens / hebdomadaires / mensuels / généraux / tendance. Chaque classement est déterminé comme suit.

Puisqu'il est considéré qu'il n'est pas nécessaire de toujours afficher le dernier statut du classement, il peut être une politique d'effectuer une agrégation une fois dans une certaine période.

• Bien que ce soit un serpent, utilisez les langues suivantes. ・ Langage de programmation: Java ・ Cadre: Spring Framework ・ DB: Postgresql

3. Conception de la table

Dans ce chapitre, nous décrirons le processus de réflexion de la conception de table pour la fonction d'affichage de classement. Ci-après, le «nombre de likes» et le «nombre d'accès» sont collectivement appelés «données de comptage».

3.1. Tout d'abord, pensez à quelque chose de simple

Si vous vous souciez de la performance depuis le début, votre réflexion sera compliquée, alors n'y pensez pas. Si tout est simple, c'est mieux que ça, alors je me suis d'abord demandé si je pouvais simplifier le tableau.

Ce à quoi j'ai pensé, c'est que les candidats suivants doivent figurer dans le tableau de l'aperçu des fonctions, mais est-ce vraiment nécessaire pour chaque donnée de comptage pour 1 semaine / 1 mois / toute la période? ・ Données de comptage quotidien ・ Données de comptage hebdomadaire ・ Données de comptage mensuel ・ Compter les données pour toute la période

L'unité minimale de données requise pour agréger les données de comptage pour une semaine / un mois / une période entière est les données de comptage quotidiennes. Par conséquent, il est possible pour le programme d'obtenir les données de comptage pour chaque période tant que les données de comptage pour chaque jour sont enregistrées. Sur la base de cette idée, la fonction elle-même peut être réalisée avec le tableau suivant.

Figure 1. Tableau des articles

• Bien qu'il existe d'autres tableaux nécessaires pour le service de publication d'articles, cet article est limité à ceux liés à la fonction d'affichage du classement.

3.2. Tenir compte des performances

Les données du tableau de la figure 1 sont divisées en deux cas d'utilisation hautement indépendants suivants.

Dans le cas de la table de la figure 1, des données autres que les données de comptage ne peuvent pas être mises à jour ensemble lors de la mise à jour des données de comptage.Par conséquent, en divisant la table de la figure 1 comme suit, les deux peuvent être mises à jour en même temps.

Figure 2. Tableau après fractionnement des données de comptage

Après cela, nous procéderons à l'examen des performances sur la base du tableau de la figure 2. Le tableau de la figure 2 présente encore des problèmes de performances (sans parler). Considérant qu'il existe deux problèmes majeurs, nous avons envisagé une conception pour résoudre chaque problème.

** (Problème 1) Agrégation de classement à chaque fois que la page est affichée = Le temps de calcul de l'agrégation de classement est long ** ** (Problème 2) Stocker les données de comptage quotidien = dégradation des performances due à l'augmentation du nombre d'enregistrements **

3.2.1 Problème 1: Le calcul du classement prend beaucoup de temps.

Si vous disposez des données de comptage pour chaque jour, vous pouvez calculer le nombre pour une semaine / un mois / une période entière, mais si vous les retournez, les données de comptage pour chaque période seront agrégées chaque fois que l'utilisateur accède à la page où le classement est affiché. Doit.

Afin de résoudre ce problème, le résultat agrégé des données de comptage pour 1 jour / 1 semaine / 1 mois / toute la période doit être conservé dans un tableau séparé, et lors de l'affichage du classement, il suffit de se référer à ce tableau. J'ai pensé que c'était bon (l'agrégation est effectuée à intervalles réguliers par un traitement d'exécution périodique).

ER Figure 3_Improvement 1.png Figure 3. Après avoir ajouté le tableau d'agrégation des données de catégorie au tableau de la figure 2.

En faisant cela, il n'est pas nécessaire d'agréger à chaque fois, et la table de notation ajoute / met à jour les données de comptage quotidiennes, et la table aggreagate_points fait référence aux données de comptage (lors de l'affichage du classement), de sorte que la division des rôles et l'accès à la table soient distribués. Peut être fait.

3.2.2. Problème 2: dégradation des performances due à l'augmentation du nombre d'enregistrements

Dans le tableau de la figure 1, puisque les données de comptage pour chaque jour sont conservées pour chaque article, le nombre d'enregistrements augmente de façon exponentielle à mesure que le nombre d'articles postés et le nombre de jours ouvrables augmentent. En conséquence, la capacité de la base de données est réduite et la vitesse de recherche de données est réduite. Par conséquent, nous avons envisagé deux mesures pour réduire le nombre d'enregistrements.

** (Mesures 1) Jusqu'à 30 jours d'enregistrements par article ** Les données de comptage pour toute la période peuvent être maintenues à jour en continuant à ajouter les données de comptage quotidiennes, il n'y a donc pas de problème même si les données de comptage pour les 30 derniers jours sont réduites (les données de comptage pour toute la période sont comme le montre la figure 2). Tenez une autre table).

** (Mesures 2) Collectez les données des 30 derniers jours dans un seul enregistrement ** Même si les données contenues dans le tableau de la figure 1 sont réduites au cours des 30 derniers jours, on ne peut pas dire que le nombre d'enregistrements a diminué car le nombre d'enregistrements = le nombre total d'articles publiés x 30 (si le nombre d'articles publiés est de 10 000, le nombre d'enregistrements est Sera 300 000). Afin d'obtenir les données de comptage total pendant un mois, il est nécessaire d'extraire 30 données de l'article avec le SQL suivant à chaque fois, donc plus il y a de données, plus la recherche prend du temps.

SELECT * FROM rating WHERE articl_id=[Id de l'article];

Dans ce cas, je pensais que si les données de 30 jours à partir du début étaient transformées en un ensemble de données, la difficulté d'extraire 30 données pourrait être sauvegardée et le nombre d'enregistrements = le nombre total d'articles publiés pourrait être supprimé.

Les données de comptage du jour sont mises à jour fréquemment, mais les autres données de comptage passées ajoutent uniquement les dernières données / suppriment les données les plus anciennes. Par conséquent, si les 30 derniers jours sont rassemblés dans un format qui permet à ces opérations d'être facilement exécutées côté programme, il n'y a pas de problème même si elles sont sauvegardées comme un enregistrement de données.

Certaines bases de données peuvent stocker des données JSON ou XML (Postgresql utilisé cette fois-ci peut stocker les deux), et comme JSON ou XML est plus facile à gérer du côté programme, 30 jours de données sont JSON ou XML. Je vais le stocker dans un seul enregistrement au format. On craint que les performances chutent si le format de données est JSON ou XML, mais le moment de la modification des données est insignifiant par rapport aux performances globales car il est effectué à intervalles réguliers par un traitement d'exécution périodique.

Figure 4. Après avoir appliqué les mesures de réduction du nombre d'enregistrements au tableau de la figure 2

・ Exemple de conversion des données de comptage des 30 derniers jours au format JSON

{
  rating_info: [
    {
      "favorite_count":"12"
      "date":"2019-04-17"
    },
    {
      "favorite_count":"15"
      "date":"2019-04-16"
    },

　　　：(Abréviation)
}

-Exemple de conversion des données de comptage des 30 derniers jours au format XML

<rating_info>
  <day_rating>
    <favorite_count>12</favorite_count>
    <date>2019-04-17</date>
  </day_rating>
  <day_rating>
    <favorite_count>15</favorite_count>
    <date>2019-04-16</date>
  </day_rating>
</rating_info>

3.3. Formulaire de table finale

La combinaison des résultats considérés dans la section 3.2 donne le tableau suivant.

Figure 5. Table finale

Par conséquent, il était possible de séparer les tables auxquelles les opérations des utilisateurs accédaient comme suit, donc si vous créez un index pour chaque table en utilisant l'ID comme clé, vous pouvez maintenir les performances même s'il y a beaucoup de données stockées dans la table. ..

(* 1) Lors de l'exécution d'un traitement d'exécution périodique une fois toutes les 24 heures (* 2) L'ajout / la suppression d'enregistrements dépend de l'ajout / de la suppression d'articles

4. Traitement d'exécution périodique (agrégation des données de comptage)

Je suis désolé de mentionner les éléments de programmation. Cette fois, nous utiliserons Java + Spring Framework, mais puisque Spring Framework a une fonction qui peut exécuter des tâches périodiquement, nous l'utiliserons.

@Scheduled(cron = "0 0 0 * * *", zone = "Asia/Tokyo")
public void updateAggregateData() {
  //...
}

De gauche à droite, les options cron incluent les secondes (0-59), les minutes (0-59), les heures (0-23), les jours (1-31), les mois (1-12), les jours (0: jours, 1: Lun, 2: mar, 3: mer, 4: jeu, 5: ven, 6: sam, 7: dim), donc si vous utilisez la source ci-dessus, elle peut être exécutée régulièrement à 0:00:00 tous les jours. Sera.

5. Enfin

Épelez-le correctement avec le texte écrasé à la fin.

Au moment où j'écrivais, je pensais que les données de comptage telles que 1 semaine / 1 mois au début ne sont pas nécessaires car elles sont obtenues à partir des données de chaque jour. J'ai pensé que je l'avais enlevé, mais vu la performance, j'ai pensé que c'était nécessaire après tout, et j'ai retourné ma main et créé un élément de plongée. La leçon est que les données de comptage telles que 1 semaine / 1 mois doivent être mises dans le tableau pour le moment afin qu'il n'y ait pas de perte.

Le contenu que j'ai écrit cette fois a également été écrit dans le but personnel de documenter correctement le contenu que le contributeur extrêmement sensuel pensait dans son esprit avec sensation et image. J'ai vérifié si j'avais fait une terrible erreur, mais il m'a fallu moins de 24 heures pour écrire autant, alors je me suis reconfirmé que je n'étais pas douée pour rédiger des documents (chaque semaine pendant 3 ans lorsque j'étais étudiant). J'avais l'habitude de créer 6-8 pages de ce matériel de la taille d'un article dans Word, mais j'ai reconfirmé que cela faisait trop longtemps depuis l'apogée et que je n'ai pas le sentiment de cette époque). La sortie est un problème.

Il n'est pas exagéré de dire que vous êtes un débutant en conception de tables et en DB, donc je pense qu'il y a beaucoup de choses que vous pouvez apprendre en lisant la littérature comme des conseils approfondis sur la conception de bases de données et des anti-modèles SQL. Alors, acquérez des compétences pratiques grâce au développement du service de publication d'articles qui était à l'origine de cet article, et du prochain service planifié avec un grand nombre de tables / colonnes, et parfois de l'apprentissage de la sortie ici également. Je pense.

c'est tout

• Je fais Twitter pour le moment https://twitter.com/SYM_souten

Site de référence

(1) Fractionner une table avec un grand nombre de colonnes dans une relation 1: 1 à l'aide d'une clé primaire

(2) Comment exécuter périodiquement des tâches avec Spring Boot

(3) WWW SQL Designer * Utilisé pour créer des diagrammes ER