Le partage le plus complet des spécifications d'utilisation de MySQL dans l'histoire

[Recommandations d'apprentissage associées : Tutoriel MySQL]

Il y a eu de nombreuses opérations liées aux bases de données récemment, et les spécifications existantes de l'entreprise sont pas trop complet. Selon les normes pertinentes de divers experts sur Internet, j'ai compilé quelques usages standards pour mon propre usage. J'espère que vous pourrez me corriger.

Environnement de base de données

dev : Environnement de développement

Le développement peut lire et écrire, et peut modifier la structure des tables. Les développeurs peuvent modifier la structure de la table et les données qu'elle contient à volonté, mais doivent s'assurer que cela n'affecte pas les autres collègues de développement.

test : L'environnement de test

est lisible et inscriptible par les développeurs, et les développeurs peuvent modifier la structure de la table via des outils.

en ligne : environnement en ligne

Les développeurs ne sont pas autorisés à effectuer directement des opérations de base de données dans l'environnement en ligne. Si des opérations sont nécessaires, ils doivent trouver l'administrateur de base de données pour opérer et effectuer. enregistrements correspondants. Ceci est interdit.

Le problème clé est que les autorisations utilisateur correspondant au serveur MySQL dans chaque environnement doivent être clairement divisées et identifiables, et peuvent distinguer spécifiquement les scénarios commerciaux, etc.

Conventions de dénomination

Règles de dénomination de base

• Utiliser un vocabulaire et des mots anglais significatifs sont séparés par des traits de soulignement. (N'utilisez pas de pinyin)
• Seules les lettres, chiffres et traits de soulignement anglais peuvent être utilisés et commencer par une lettre anglaise.
• Utilisez toutes les lettres minuscules pour les bibliothèques, les tables et les champs, et n'utilisez pas de dénomination en casse chameau.
• Évitez d'utiliser des mots réservés ORACLE et MySQL, tels que desc, et des mots-clés tels que index.
• Le nom ne peut pas dépasser 32 caractères. La signification du nom doit être claire. Il est recommandé d'utiliser des noms au lieu de verbes
• Pour les bases de données et les tableaux de données, tous les préfixes doivent être utilisés. tmp est le préfixe et la date est le suffixe
• Les bibliothèques et tables de sauvegarde doivent être bak, et la date est le suffixe

Pourquoi toutes les bibliothèques, tables, et les champs utilisent des minuscules ?

Dans MySQL, les bases de données et les tables correspondent aux répertoires et fichiers situés sous ces répertoires. Par conséquent, la sensibilité du système d’exploitation détermine la sensibilité à la casse des noms de base de données et de table.

Il n'est pas sensible à la casse sous Windows.

• Règles de casse sous Linux
• Les noms de bases de données et les noms de tables sont strictement sensibles à la casse ;
• Les alias de tables sont strictement sensibles à la casse ;
• Les noms de colonnes et ; les alias de colonnes ne sont pas sensibles à la casse dans tous les cas ;
• Les noms de variables sont également strictement sensibles à la casse
• Comment résoudre le problème si la dénomination camel a été définie ? Vous devez ajouter lower_case_table_names = 1 au fichier de configuration MySQL my.ini.
• 
  

Nom des tables

Les tables du même module doivent utiliser le même préfixe autant que possible, et les noms des tables doivent exprimer la signification comme autant que possible. Toutes les tables de journaux commencent par log_

Nom de champ

Un mot anglais ou une abréviation qui exprime sa signification réelle. Les champs ayant une signification booléenne sont préfixés par is_, suivi du participe passé du verbe.

• Les champs ayant la même signification entre les tables doivent avoir le même nom. Les champs ayant la même signification entre les tables sont nommés avec le nom de la table_nom du champ moins le préfixe du module.
• Les champs de clé étrangère utilisent le nom de la table nom_champ pour indiquer leur association.
• La clé primaire d'une table est généralement acceptée comme étant id, un type à incrémentation automatique, et les clés étrangères des autres tables sont exprimées sous la forme de xxx_id.

Nom de l'index

L'index non unique doit être nommé selon "idx_field name_field name[_field name]"

• L'index unique doit être nommé selon "uniq_field name_field name[_field name]"
• 
  

Nom de la contrainte

Contrainte de clé primaire : nom de la table_pk.

• Contrainte unique : uk_table name_field name. (L'application doit avoir une logique de vérification d'unicité en même temps.)
• 
  

Spécifications de conception de table

Le moteur de table dépend de l'application réelle scénario ; journaux et rapports Il est recommandé d'utiliser myisam pour les tables de classes et le moteur innodb pour les tables liées aux transactions, aux audits et aux montants. S'il n'y a pas d'explication, le moteur innodb sera utilisé lors de la création de la table

Le jeu de caractères utf8mb4 par défaut est utilisé et la règle de tri de la base de données est utf8mb4_general_ci (puisque la définition de la base de données utilise la valeur par défaut, la table de données. ne peut plus être défini, mais à des fins d'assurance, il est recommandé d'écrire

Pourquoi ne pas choisir utf8 pour le jeu de caractères et utf8_general_ci pour la règle de tri des octets d'expressions Emoji ? faire en sorte que le projet back-end prenne pleinement en charge les expressions Emoji saisies par le client, la mise à niveau de l'encodage vers utf8mb4 est la meilleure solution. Pour la chaîne de connexion JDBC, le caractère Encoding est défini sur utf8 ou la configuration ci-dessus ne peut toujours pas être insérée normalement dans le. Dans le cas des données emoji, vous devez spécifier le jeu de caractères de connexion comme utf8mb4 dans le code Toutes les tables et tous les champs doivent utiliser l'attribut de colonne de commentaire pour décrire la véritable signification de la table et du champ. valeur d'énumération, il est recommandé de définir tout le contenu utilisé dans ce champ. S'il n'est pas spécifié, le premier champ d'identification de la table doit être la clé primaire et croître automatiquement en tant que contexte et condition. une non-transaction est interdite. Il est interdit d'utiliser le type varchar comme conception d'instruction de clé primaire.

S'il n'y a pas de description, la table doit contenir les champs create_time et modifier_time, c'est-à-dire que la table doit contenir des champs qui enregistrent l'heure de création et l'heure de modification

S'il n'y a pas de description, la table doit contient is_del, qui est utilisé pour indiquer si les données ont été supprimées. En principe, la suppression physique des données de la base de données n'est pas autorisée.

• Utilisez le moins d'espace de stockage possible pour stocker des données dans un seul champ
• Si vous pouvez utiliser int, n'utilisez pas char ou varchar
• Si vous le pouvez utilisez tinyint, ne l'utilisez pas int
• utilise UNSIGNED pour stocker des valeurs non négatives.
• Il n'est pas recommandé d'utiliser les types ENUM et SET. Utilisez plutôt TINYINT.
• Utilisez des types de données courts. Par exemple, lorsque la plage de valeurs est comprise entre 0 et 80, utilisez TINYINT UNSIGNED
.
• pour stocker des nombres à virgule flottante précis, vous devez utiliser DECIMAL au lieu des champs de temps FLOAT et DOUBLE
• Sauf circonstances particulières, int est utilisé pour enregistrer unix_timestamp
• pour stocker l'année en utilisant l'ANNÉE. taper.
• Stockez les dates en utilisant le type DATE.
• Il est recommandé d'utiliser le type TIMESTAMP pour stocker l'heure (précise à la seconde près), car TIMESTAMP utilise 4 octets et DATETIME utilise 8 octets.
• Il est recommandé d'utiliser INT UNSIGNED pour stocker IPV4.
• N'utilisez pas autant que possible les types TEXT et BLOB
• Il est interdit d'utiliser VARBINARY et BLOB pour stocker des images, des fichiers, etc. dans la base de données. Il est recommandé d'utiliser d'autres méthodes de stockage (TFS/SFS). MySQL enregistre uniquement les informations du pointeur.
• La taille d'un seul enregistrement ne doit pas dépasser 8 ko (longueur de colonne (chinois)_3 (UTF8) + longueur de colonne (anglais)_1)

Quelle est la différence entre datetime et timestamp ?

Points similaires :

Le format d'affichage de la colonne TIMESTAMP est le même que la colonne DATETIME. La largeur d'affichage est fixée à 19 caractères et le format est AAAA-MM-JJ HH:MM:SS.

Différences :

TIMESTAMP

• Stockage 4 octets, plage horaire : 1970-01-01 08:00:01 ~ 2038- 01-19 11:14:07 La valeur est enregistrée au format UTC, ce qui implique une conversion de fuseau horaire. Le fuseau horaire actuel est converti lors du stockage, puis reconverti dans le fuseau horaire actuel lors de la récupération.
• Datetime est stocké sur 8 octets, plage horaire : 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Stockage au format réel, quelle que soit l'heure zone
  

Comment utiliser l'attribut d'affectation automatique de TIMESTAMP ?

Définissez l'heure actuelle comme valeur par défaut de ts : ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP. Lorsque la ligne est mise à jour, mettez à jour la valeur de ts : ts TIMESTAMP DEFAULT 0 ON UPDATE CURRENT_TIMESTAMP.

Vous pouvez combiner 1 et 2 : ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.

Comment utiliser INT UNSIGNED pour stocker l'IP ?

Utilisez INT UNSIGNED au lieu de char(15) pour stocker l'adresse ipv4 et utilisez les fonctions MySQL inet_ntoa et inet_aton pour la conversion. Les adresses IPv6 n'ont actuellement pas de fonction de conversion et doivent être stockées à l'aide de DECIMAL ou de deux bigINT.

• S'il n'y a pas de commentaire, tous les champs sont définis sur NOT NULL et les valeurs par défaut sont définies
• Il est interdit de stocker des mots de passe en texte brut dans la base de données
• S'il n'y a pas de commentaire, tous les champs booléens, tels que is_hot et is_deleted, doivent définir une valeur par défaut et la définir sur 0
• Si aucune remarque n'est faite, le champ de tri order_id est disposé dans ordre décroissant par défaut dans le programme ;
• Définition entière Aucune longueur n'est ajoutée, par exemple, INT est utilisé à la place de INT[4]
  

INT[M]. Que signifie la valeur M ?

Notez que le nombre après les parenthèses de type numérique indique uniquement la largeur et n'a rien à voir avec la plage de stockage. Beaucoup de gens pensent que les plages de valeurs de INT(4) et INT(10) sont respectivement (-9999 à 9999) et (-9999999999 à 9999999999). Cette compréhension est fausse. En fait, lorsque la valeur M dans l'entier est utilisée en combinaison avec l'attribut ZEROFILL, les valeurs des colonnes peuvent être de même largeur. Quelle que soit la valeur de M dans INT[M], sa plage de valeurs est toujours (-2147483648 à 2147483647 lorsqu'elle est signée), (0 à 4294967295 lorsqu'elle n'est pas signée).

La largeur d'affichage ne limite pas la plage de valeurs pouvant être enregistrées dans la colonne, ni l'affichage des valeurs qui dépassent la largeur spécifiée de la colonne. Lorsqu'ils sont utilisés conjointement avec l'attribut étendu facultatif ZEROFILL, les espaces supplémentaires par défaut sont remplacés par des zéros. Par exemple : Pour une colonne déclarée comme INT(5) ZEROFILL, la valeur 4 est récupérée sous la forme 00004. Veuillez noter que si vous stockez une valeur dans une colonne entière qui dépasse la largeur d'affichage, MySQL rencontrera des problèmes lors de la génération de tables temporaires pour des jointures complexes, car dans ces cas, MySQL estime que les données correspondent à la largeur de colonne d'origine si vous spécifiez ZEROFILL pour. une colonne numérique, MySQL ajoute automatiquement l'attribut UNSIGNED à la colonne.

Utilisez VARBINARY pour stocker des chaînes de longueur variable sensibles à la casse

Quand utiliser CHAR et quand utiliser VARCHAR ?

Les types CHAR et VARCHAR sont similaires, mais ils sont enregistrés et récupérés différemment. Ils diffèrent également en termes de longueur maximale et de préservation des espaces de fin. La longueur déclarée pour les types CHAR et VARCHAR représente le nombre maximum de caractères que vous souhaitez enregistrer. Par exemple, CHAR(30) peut occuper 30 caractères.

La longueur de la colonne CHAR est fixée à la longueur déclarée lors de la création du tableau. La longueur peut être n'importe quelle valeur comprise entre 0 et 255. Lors de l'enregistrement des valeurs CHAR, remplissez-les vers la droite avec des espaces jusqu'à la longueur spécifiée. Lorsqu'une valeur CHAR est récupérée, les espaces de fin sont supprimés. Aucune conversion de cas n'est effectuée pendant le stockage ou la récupération.

Les valeurs des colonnes VARCHAR sont des chaînes de longueur variable. La longueur peut être spécifiée comme une valeur comprise entre 0 et 65 535. (La longueur effective maximale de VARCHAR est déterminée par la taille maximale des lignes et le jeu de caractères utilisé. La longueur maximale globale est de 65 532 octets). Par rapport à CHAR, la valeur VARCHAR enregistre uniquement le nombre requis de caractères, plus un octet pour enregistrer la longueur (si la longueur déclarée de la colonne dépasse 255, deux octets sont utilisés). Les valeurs VARCHAR sont enregistrées sans remplissage. Les espaces de fin sont conservés lorsque la valeur est enregistrée et récupérée, conformément au SQL standard.

char convient au stockage de la valeur de hachage MD5 du mot de passe utilisateur, et sa longueur est toujours la même. char est également meilleur que varchar pour les valeurs qui changent fréquemment, car les lignes de longueur fixe sont moins sujettes à la fragmentation, et char est également plus efficace que varchar pour les colonnes très courtes. La chaîne char(1) n'occupera qu'un octet pour un jeu de caractères à un octet, mais la chaîne varchar(1) occupera 2 octets, car 1 octet est utilisé pour stocker les informations de longueur.

Spécifications de conception d'index

La vitesse des requêtes MySQL repose sur une bonne conception d'index, les index sont donc cruciaux pour des performances élevées. Les index raisonnables accéléreront les requêtes (y compris les vitesses UPDATE et DELETE. MySQL chargera la page contenant la ligne en mémoire puis effectuera les opérations UPDATE ou DELETE), tandis que les index déraisonnables ralentiront la vitesse. La recherche par index MySQL est similaire à la recherche en pinyin et en radical dans le dictionnaire Xinhua. Lorsque l'index en pinyin et en radical n'existe pas, il ne peut être recherché qu'en tournant page par page. Lorsque les requêtes MySQL ne peuvent pas utiliser d'index, MySQL effectuera une analyse complète de la table, ce qui consommera beaucoup d'E/S. Objectif de l'index : déduplication, accélération du positionnement, évitement du tri, écrasement de l'index.

Qu'est-ce qu'un index de couverture

Dans le moteur de stockage InnoDB, l'index secondaire (index de clé non primaire) ne stocke pas directement les adresses de lignes et stocke la clé primaire valeurs. Si l'utilisateur doit interroger une colonne de données qui n'est pas incluse dans l'index secondaire, il doit d'abord trouver la valeur de la clé primaire via l'index secondaire, puis interroger les autres colonnes de données via la clé primaire. être interrogé deux fois. Le concept de l'index de couverture est que la requête peut être complétée dans un index, et l'efficacité de l'index de couverture sera relativement élevée. La requête de clé primaire est un index de couverture naturel. La création raisonnable d'index et l'utilisation raisonnable d'instructions de requête peuvent améliorer les performances lors de l'utilisation d'index de couverture. Par exemple, SELECT email, uid FROM user_email WHERE uid=xx, si uid n'est pas la clé primaire, vous pouvez ajouter l'index en tant qu'index(uid, email) le cas échéant pour améliorer les performances.

Spécifications de base pour les index

• Contrôle du numéro d'index, le nombre d'index dans une seule table ne doit pas dépasser 5 et le nombre de champs dans une seule L'indice ne doit pas dépasser 5.
• Évaluation complète de la densité et de la distribution des données
• Considérez le taux de requêtes et de mise à jour
  

Pourquoi ne peut-il pas y avoir trop d'index dans un tableau?

L'index secondaire d'InnoDB utilise b+tree pour le stockage, donc b+tree doit être ajusté pendant UPDATE, DELETE et INSERT. Des index excessifs ralentiront la vitesse de mise à jour.

Utiliser l'index de préfixe pour les chaînes. La longueur de l'index de préfixe ne doit pas dépasser 8 caractères. Il est recommandé de donner la priorité à l'index de préfixe. Si nécessaire, vous pouvez ajouter des pseudo-colonnes et créer des index.

N'indexez pas les champs tels que les objets blob/texte ou les champs volumineux. Cela risquerait de faire occuper trop d'espace de stockage par l'index.

Qu'est-ce qu'un index de préfixe ?

Pour parler franchement, un index de préfixe indexe les premiers caractères du texte (plus précisément, les quelques caractères spécifiés lors de la création de l'index), afin que l'index créé soit plus petit, donc la requête est plus rapide. L'index de préfixe peut réduire efficacement la taille des fichiers d'index et améliorer la vitesse d'indexation. Mais les index de préfixe ont aussi leurs inconvénients : MySQL ne peut pas utiliser d'index de préfixe dans ORDER BY ou GROUP BY, ni comme index de couverture.

Syntaxe pour établir un index de préfixe : ALTER TABLE table_name ADD KEY(column_name(prefix_length));

Critères de clé primaire

• Le la table doit avoir une clé primaire
• Ne pas utiliser de colonnes fréquemment mises à jour
• Essayez de ne pas sélectionner de colonnes de chaîne
• Ne pas utiliser UUID MD5 HASH
• Utiliser des non- clés uniques vides par défaut
• Il est recommandé de choisir l'auto-incrémentation ou l'émetteur
  

Le SQL important doit être indexé, le noyau SQL donne la priorité à la couverture de l'index

• UPDATE, La colonne de condition WHERE de l'instruction DELETE
• Champs ORDER BY, GROUP BY, DISTINCT
• Champs JOIN multi-tables
  

avoir la plus grande différenciation Placez les champs au premier plan

• Choisissez les champs avec de meilleures propriétés de filtrage et placez-les au premier plan, comme le numéro de commande, l'identifiant d'utilisateur, etc. Il n'est généralement pas recommandé de placer les propriétés de filtrage telles que le type, le statut, etc. au premier plan
• L'index est basé sur le principe du préfixe gauche Lorsqu'un index conjoint (a,b,c) est établi, la condition de requête ne peut être utilisée que lorsqu'elle contient (a) ou (a,b) ou (a,b,c). Lors de l'utilisation de l'index, (a, c) ne peut utiliser l'index d'une colonne que lorsqu'il est utilisé comme). une condition, donc à ce stade, il faut s'assurer que les colonnes de retour de a ne doivent pas être trop nombreuses, sinon la conception de l'instruction sera déraisonnable et (b, c) ne pourra pas utiliser l'index
Créer raisonnablement un index conjoint (pour éviter la redondance), (a,b,c) est équivalent à (a), (a,b), (a,b,c)
• 
  

Index Tabou

• Ne pas indexer sur les colonnes à faible cardinalité, telles que "genre"
• Ne pas effectuer d'opérations mathématiques et fonctionnelles sur les colonnes indexées
• Ne pas indexer les petites tables couramment utilisées
• 🎜>
Essayez de ne pas utiliser de clés étrangères
• Les clés étrangères sont utilisées pour protéger l'intégrité référentielle et peuvent être implémentées du côté de l'entreprise
• Les opérations sur les tables parentes et les tables enfants s'affecteront mutuellement et réduire la disponibilité
• 
  INNODB lui-même a des limitations sur le DDL en ligne

Limitations sur les index dans MYSQL

La longueur totale du Les index du moteur de stockage MYISAM ne peuvent pas dépasser 1 000 octets
Seuls les index de préfixe peuvent être créés pour les colonnes de type BLOB et TEXT
MYSQL ne prend actuellement pas en charge les index de fonction
Lors de l'utilisation d'une valeur différente de (!= ou a8093152e673feb7aba1828c43532094) , MYSQL ne peut pas utiliser l'index.
Après avoir filtré les champs à l'aide d'opérations fonctionnelles (telles que abs (colonne)), MYSQL ne peut pas utiliser d'index.
MYSQL ne peut pas utiliser l'index lorsque les types de champs de condition de jointure dans l'instruction de jointure sont incohérents
Lors de l'utilisation de l'opération LIKE, si la condition commence par un caractère générique (tel que '%abc…'), MYSQL ne peut pas utiliser le indice.

Lors de l'utilisation de requêtes non équivalentes, MYSQL ne peut pas utiliser les index de hachage.

Spécifications de conception des instructions

Utiliser des instructions précompilées

• Ne transmettre que les paramètres, ce qui est plus efficace que de transmettre du SQL
• Analyser une fois, utiliser plusieurs fois
• 
  Réduire la probabilité d'injection SQL

Éviter la conversion implicite

Provoquera un échec de l'index

Utiliser pleinement l'index de préfixe

• doit être le préfixe le plus à gauche
• Il est impossible d'en utiliser deux conditions de plage en même temps
• 
  N'utilisez pas de requêtes % principales, telles que "%ab"

N'utilisez pas de requêtes négatives, telles que not in/like

• Impossible d'utiliser l'index, ce qui entraîne une analyse complète de la table
• 
  Analyse complète de la table entraînant une utilisation réduite du pool de tampons

Évitez d'utiliser des procédures stockées et des déclencheurs, des serveurs, des UDF, des événements, etc.

• Laissez la base de données faire ce qu'elle fait de mieux
• Réduisez le couplage d'affaires et quittez place pour le sacle et le sharding
• 
  Évitez les BUG

Évitez d'utiliser JOIN de grandes tables

Ce que MySQL fait le mieux, c'est la requête clé primaire/index secondaire d'une seule table

JOIN consomme plus de mémoire et génère des tables temporaires

Évitez les opérations mathématiques dans la base de données

• MySQL n'est pas bon en opérations mathématiques et en jugements logiques
• 
  Impossible d'utiliser les index

Réduire le nombre d'interactions avec la base de données

• INSÉRER… SUR LA MISE À JOUR DE LA CLÉ EN DOUBLE
• REMPLACER DANS, INSÉRER IGNORER, INSÉRER DANS LES VALEURS(),(),()
• MISE À JOUR… OÙ ID IN(10,20,50 ,…)

Utiliser la pagination de manière raisonnable

Limiter le nombre de pages affichées par pagination Vous ne pouvez cliquer sur la page précédente et la page suivante qu'en utilisant le différé. association

Comment utiliser correctement la pagination ?

Supposons qu'il existe une instruction de pagination similaire à la suivante : SELECT * FROM table ORDER BY id LIMIT 10000, 10 Puisque la façon dont LIMIT OFFSET est traitée dans MySQL consiste à supprimer toutes les données de OFFSET+ LIMIT, puis supprimez OFFSET et renvoyez LIMIT en bas. Par conséquent, lorsque la valeur OFFSET est grande, les performances des requêtes MySQL seront très faibles. Il peut être résolu en utilisant id > n :

Il existe des limites à l'utilisation de id > Pour le problème des identifiants discontinus, il peut être résolu en transmettant le dernier identifiant en même temps lors de la rotation. la page.

http://example.com/page.php?last=100 
select * from table where id<100 order by id desc limit 10 
//上一页 
 http://example.com/page.php?first=110 
select * from table where id>110 order by id desc limit 10

Le plus gros inconvénient de cette méthode est que s'il y a une opération d'insertion/suppression pendant la navigation, le changement de page ne sera pas mis à jour, et le nombre total de pages pourra toujours être calculé en fonction du nouveau décompte ( *), et enfin Certains enregistrements peuvent être inaccessibles. Afin de résoudre ce problème, vous pouvez continuer à introduire le numéro de page actuel et s'il existe des opérations d'insertion/suppression qui affectent le nombre total d'enregistrements depuis le dernier tour de page et les mettre en cache

select * from table where id >= (select id from table order by id limit #offset#, 1)

• Rejeter grand SQL et divisez-le en petit SQL
• Utilisez pleinement le QUERY CACHE
• Utilisez pleinement le processeur multicœur
• Utilisez in au lieu de ou, la valeur de in ne doit pas dépasser 1000
• Ne pas utiliser order by rand ()
• 
  Utiliser les diagnostics EXPLAIN pour éviter de générer des tables temporaires

L'instruction EXPLAIN (exécutée dans le client MySQL) peut obtenir des informations sur la manière dont MySQL exécute l'instruction SELECT. En exécutant EXPLAIN sur l'instruction SELECT, vous pouvez savoir si MySQL utilise des index, des analyses de tables complètes, des tables temporaires, un tri et d'autres informations lors de l'exécution de l'instruction SELECT. Essayez d'éviter que MySQL n'effectue des analyses de tables complètes, en utilisant des tables temporaires, en triant, etc. Consultez la documentation officielle pour plus de détails.

Utiliser union all au lieu de union

Quelle est la différence entre union all et union ?

Les mots-clés union et union all fusionnent tous deux deux ensembles de résultats en un seul, mais ils sont différents en termes d'utilisation et d'efficacité.

Union filtrera les enregistrements en double après la liaison des tables, donc après la liaison des tables, elle triera l'ensemble de résultats généré, supprimera les enregistrements en double, puis renverra les résultats. Par exemple :

select * from test_union1 
union select * from test_union2

Ce SQL extrait d'abord les résultats des deux tables pendant le fonctionnement, puis utilise l'espace de tri pour trier et supprimer les enregistrements en double, et renvoie enfin l'ensemble de résultats si les données de la table sont volumineuses. cela peut entraîner l'utilisation de disques.

Et union all combine simplement les deux résultats et les renvoie. De cette façon, s'il y a des données en double dans les deux jeux de résultats renvoyés, le jeu de résultats renvoyé contiendra des données en double.

从效率上说，union all要比union快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用union all，如下：

select * from test_union1 union all select * from test_union2

•  程序应有捕获SQL异常的处理机制
• 禁止单条SQL语句同时更新多个表
• 不使用select * ，SELECT语句只获取需要的字段
• 消耗CPU和IO、消耗网络带宽
• 无法使用覆盖索引
• 减少表结构变更带来的影响
• 因为大，select/join 可能生成临时表
• UPDATE、DELETE语句不使用LIMIT
• INSERT语句必须显式的指明字段名称，不使用INSERT INTO table()
• INSERT语句使用batch提交（INSERT INTO table VALUES(),(),()……），values的个数不超过500
• 统计表中记录数时使用COUNT(*)，而不是COUNT(primary_key)和COUNT(1) 备注：仅针对Myisam
• 数据更新建议使用二级索引先查询出主键，再根据主键进行数据更新
• 禁止使用跨库查询
• 禁止使用子查询，建议将子查询转换成关联查询
• 针对varchar类型字段的程序处理，请验证用户输入，不要超出其预设的长度；
  

分表规范

单表一到两年内数据量超过500w或数据容量超过10G考虑分表，需提前考虑历史数据迁移或应用自行删除历史数据，采用等量均衡分表或根据业务规则分表均可。要分表的数据表必须与DBA商量分表策略

• 用HASH进行散表，表名后缀使用十进制数，下标从0开始
• 按日期时间分表需符合YYYY[MM][dd][HH]格式
• 采用合适的分库分表策略。例如千库十表、十库百表等
• 禁止使用分区表，分区表对分区键有严格要，分区表在表变大后执行DDL、SHARDING、单表恢复等都变得更加困难。
• 拆分大字段和访问频率低的字段，分离冷热数据
  

行为规范

• 批量导入、导出数据必须提前通知DBA协助观察
• 禁止在线上从库执行后台管理和统计类查询
• 禁止有super权限的应用程序账号存在
• 产品出现非数据库导致的故障时及时通知DBA协助排查
• 推广活动或上线新功能必须提前通知DBA进行流量评估
• 数据库数据丢失，及时联系DBA进行恢复
• 对单表的多次alter操作必须合并为一次操作
• 不在MySQL数据库中存放业务逻辑
• 重大项目的数据库方案选型和设计必须提前通知DBA参与
• 对特别重要的库表，提前与DBA沟通确定维护和备份优先级
• 不在业务高峰期批量更新、查询数据库其他规范
• 提交线上建表改表需求，必须详细注明所有相关SQL语句
  

其他规范

日志类数据不建议存储在MySQL上，优先考虑Hbase或OceanBase，如需要存储请找DBA评估使用压缩表存储。

相关图文教程：mysql数据库图文教程

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!