Résumé de la conception de la base de données MySQL

Règle 1 : En général, vous pouvez choisir le moteur de stockage MyISAM. Si vous avez besoin de la prise en charge des transactions, vous devez utiliser le moteur de stockage InnoDB.

Remarque : L'index B-tree du moteur de stockage MyISAM a une grande limitation : la somme des longueurs de tous les champs participant à un index ne peut pas dépasser 1000 octets. De plus, les données et les index MyISAM sont séparés, tandis que le stockage des données d'InnoDB est classé par index de cluster et que la clé primaire est l'index de cluster par défaut. Par conséquent, bien que MyISAM ait généralement des performances de requête plus élevées qu'InnoDB, les performances de requête d'InnoDB sont basées sur la clé primaire. la clé est très haute.

Règle 2 : Règles de dénomination.

1. Les noms de la base de données et des tables doivent être aussi cohérents que possible avec le nom du module métier servi

2. La table de première classe servant le même sous-module doit être aussi cohérent que possible. Utilisez le nom du sous-module (ou une partie du mot) comme préfixe ou suffixe

3. Le nom de la table doit essayer d'inclure le mot correspondant à le champ des données stockées

4. Le nom doit également essayer de le garder correspondant aux données réelles

5. Le nom de l'index commun doit essayer d'inclure tous les noms ou abréviations des champs de la clé d'index, et l'ordre de chaque nom de champ dans le nom de l'index doit être cohérent avec la clé d'index. L'ordre de l'index dans l'index est cohérent et essayez d'inclure un préfixe ou un suffixe similaire à idx pour indiquer que l'objet le type est un index.

6. Les autres objets tels que les contraintes doivent également contenir autant que possible les noms des tables ou autres objets auxquels ils appartiennent pour indiquer leurs relations respectives

Règles 3 : Définition du type de champ de base de données

1. Pour les champs qui nécessitent souvent des calculs et des tris qui consomment du CPU, vous devriez essayer de choisir des champs plus rapides, comme en utilisant TIMESTAMP(section de 4 caractères, valeur minimale 1970-01-01 00:00:00) au lieu de Datetime (8 octets, valeur minimale 1001-01-01 00:00:00), remplacez les types de virgule flottante et de caractères avec des entiers

2. Utilisez varchar pour les champs de longueur variable, n'utilisez pas char

3. Pour les données multimédia binaires, les données de pipeline (telles que comme journaux), extrêmement volumineux Ne placez pas de données texte dans les champs de la base de données

Règle 4 : La table qui doit être lue par le processus d'exécution de la logique métier doit avoir un valeur initiale. Évitez la lecture commerciale de valeurs négatives ou infinies provoquant l'échec du programme

Règle 5 : Il n'est pas nécessaire d'adhérer à la théorie du paradigme, redondance modérée, pour que Query puisse minimiser Join

Règle 6 : Divisez les grands champs à faible fréquence d'accès en tableaux de données. Certains grands champs occupent beaucoup d'espace et sont consultés beaucoup moins fréquemment que d'autres champs. Dans ce cas, en divisant les champs, il n'est pas nécessaire de lire les grands champs lors de requêtes fréquentes, ce qui entraîne un gaspillage de ressources d'E/S.

Règle 7 : Le fractionnement horizontal peut être envisagé pour les grandes tables. Les tables volumineuses affectent l'efficacité des requêtes. Il existe de nombreuses méthodes de fractionnement basées sur les caractéristiques métier. Par exemple, les données qui augmentent en fonction du temps peuvent être divisées en fonction du temps. Les données divisées par identifiant peuvent être divisées en fonction du nombre id% de bases de données.

Règle 8 : Les index pertinents requis par l'entreprise sont déterminés en fonction de la condition Where de l'instruction SQL construite selon la conception réelle. Ne créez pas d'index qui ne sont pas nécessaires à l'entreprise. Les index conjoints ne sont pas autorisés (ou la clé primaire) contient plus d'un champ. En particulier, le champ n'apparaîtra pas du tout dans l'instruction conditionnelle.

Règle 9 : Une clé primaire ou un index unique doit être établi pour déterminer de manière unique un ou plusieurs champs d'un enregistrement. Si un enregistrement ne peut pas être déterminé de manière unique, un index commun est construit pour. améliorer l'efficacité des requêtes

Règle 10 : Certaines tables utilisées par les entreprises ont très peu d'enregistrements, voire un seul enregistrement. Afin de répondre aux besoins de contraintes, des index ou des clés primaires doivent être établis. .

Règle 11 : Pour les champs dont les valeurs ne peuvent pas être répétées et sont souvent utilisées comme conditions de requête, un index unique doit être construit (la clé primaire par défaut est un index unique), et les conditions de ce champ dans les conditions de requête doivent être placées en première position. Il n'est pas nécessaire de créer un index commun lié à ce champ.

Règle 12 : Pour les champs fréquemment interrogés dont les valeurs ne sont pas uniques, vous devez également envisager d'établir un index normal. Placez la condition du champ en première position dans l'instruction et le processus de requête. l'index commun. La même méthode.

Règle 13 : Lorsqu'une entreprise accède à des données via un index non unique, elle doit prendre en compte la densité des enregistrements renvoyés via la valeur de l'index. En principe, la densité maximale possible ne peut pas être atteinte. supérieur à 0,2. S'il est dense. Si le degré est trop grand, il ne convient pas de créer un indice.

Lorsque la quantité de données récupérées via cet index représente plus de 20 % de toutes les données de la table, vous devez en même temps prendre en compte le coût d'établissement de l'index, car l'analyse de l'index génère des I aléatoires. /O, ce qui en résulte Son efficacité est bien inférieure aux E/S séquentielles d'une analyse séquentielle de table complète. Le système de base de données ne peut pas utiliser cet index lors de l'optimisation de la requête.

Règle 14 : Les bases de données qui nécessitent des index conjoints (ou des clés primaires conjointes) doivent faire attention à l'ordre des index. Les conditions de correspondance dans l'instruction SQL doivent également être cohérentes avec l'ordre de l'index.

Remarque : une indexation incorrecte peut également entraîner de graves conséquences.

Règle 15 : Plusieurs champs de la table sont utilisés comme conditions de requête, ne contiennent pas d'autres index et les valeurs conjointes des champs ne sont pas répétées. Un index conjoint unique peut être construit. ces multiples champs. Supposons que l'index Si le champ est (a1, a2,...an), alors la condition de requête (a1 op val1,a2 op val2,...am op valm)m<=n peut utiliser l'index. La position du champ dans la condition de requête est cohérente avec la position de. le champ dans l'index.

Règle 16 : Principes d'établissement des index conjoints (ce qui suit suppose qu'un index conjoint (a, b, c) est établi sur les champs a, b, c de la table de la base de données)

1. Les champs de l'index conjoint doivent essayer de satisfaire l'ordre des données filtrées du plus au moins, c'est-à-dire que le champ avec la plus grande différence doit être le premier champ

2. La création de l'index doit être autant que possible cohérente avec la séquence de conditions de l'instruction SQL, de sorte que l'instruction SQL soit basée autant que possible sur l'intégralité de l'index, et essayez d'éviter d'utiliser une partie de l'index (surtout lorsque la première condition est incohérente avec le premier champ de l'index) comme condition de requête

3. Where a=1,where a>=12 and a<15,where a=1 and b<5 ,where a=1 and b=7 and c>=40为条件可以用到此联合索引；而这些语句where b=10,where c=221,where b>=12 and c=2则无法用到这个联合索引。

4. Lorsque tous les champs de la base de données qui doivent être interrogés sont reflétés dans l'index, la base de données peut interroger directement l'index pour obtenir les informations de requête sans analyser la table entière (c'est ce qu'on appelle la clé uniquement), ce qui peut. améliorer considérablement l'efficacité des requêtes.
   L'index peut être utilisé lors de l'interrogation de a, ab, abc en association avec d'autres champs de table

5. Lorsque a, ab, abc sont dans l'ordre au lieu de b, c, bc, ac. Les index peuvent être utilisés lors de l'exécution d'un ordre par ou par groupe

6. Dans les situations suivantes, l'analyse et le tri des tables peuvent être plus efficaces que l'utilisation d'index conjoints
   a. organisé selon l'index
   b. Une grande proportion de toutes les données de la station de données interrogée.

Règle 17 : Lorsqu'une entreprise importante accède aux tableaux de données. Cependant, lorsque les données ne sont pas accessibles via les index, vous devez vous assurer que le nombre d'enregistrements accédés séquentiellement est limité et ne doit en principe pas dépasser 10.

Règle 18 : Construire raisonnablement Instruction de requête

1. Dans l'instruction Insert, selon le test, l'efficacité est la plus élevée lors de l'insertion de 1 000 éléments dans un lot à la fois. Lorsqu'il y a plus de 1 000 éléments, il est nécessaire de le faire. être divisé. Si la même insertion est effectuée plusieurs fois, elle doit être fusionnée et regroupée. Notez que la longueur de l'instruction de requête doit être inférieure au paramètre mysqld max_allowed_packet

2. L'ordre d'exécution des différents opérateurs logiques dans les conditions de requête est et, ou, dans, vous devriez donc essayer pour éviter de les utiliser dans les conditions de requête. Utilisez in

3. dans un grand ensemble. Utilisez toujours un petit ensemble de résultats pour piloter un grand ensemble d'enregistrements, car dans MySQL, il n'y a qu'une seule jointure. méthode, Nested Join, ce qui signifie que la jointure de mysql se fait via une boucle imbriquée. Le principe des petits ensembles de résultats pilotant de grands ensembles d'enregistrements est utilisé pour réduire le nombre de boucles dans les boucles imbriquées afin de réduire la quantité totale d'E/S et le nombre d'opérations CPU

4. Optimiser la boucle interne de Nested Join autant que possible.

5. Prenez uniquement les colonnes requises, essayez de ne pas utiliser select *

6. Utilisez uniquement les champs de filtre les plus efficaces, il y a peu de conditions de filtre dans la clause Where Pour le meilleur

7. Essayez d'éviter les jointures et les sous-requêtes complexes

   Mysql n'est pas très bon en termes de concurrence Lorsque le niveau de concurrence est trop élevé, l'ensemble. les performances diminueront.La forte baisse est principalement liée au contrôle des verrous de contention des ressources internes de Mysql qui utilise des verrous de table et InnoDB utilise des verrous de ligne.

Règle 19 : Optimisation du système d'application

1. Utiliser le cache de manière raisonnable, pour les pièces qui changent moins Active les données sont mises en cache dans la mémoire via le cache de la couche application, ce qui améliore les performances de plusieurs ordres de grandeur.

2. Fusionnez la même requête à plusieurs reprises pour réduire le nombre d'IO.

c. Principe de pertinence minimale des transactions

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!