Mon avis sur les spécifications de développement MySQL

La plupart des spécifications MySQL peuvent également être trouvées sur Internet. Ce que je souhaite partager ici, ce sont certaines choses que Lao Ye considère personnellement comme plus importantes, ou qui sont facilement négligées et facilement confondues.

1. Utiliser le moteur InnoDB par défaut

[Le point de vue de Old Ye] Je l'ai demandé à plusieurs reprises fois, et InnoDB convient à près de 99 % des scénarios d'application MySQL, et les tables système de MySQL 5.7 ont été modifiées en InnoDB, il n'y a aucune raison de s'en tenir à MyISAM.

De plus, les tables InnoDB qui sont fréquemment lues et écrites doivent utiliser des entiers avec des caractéristiques d'auto-incrémentation/séquentielle comme clés primaires explicites.

　[Référence] : Série [MySQL FAQ] - Pourquoi est-il recommandé d'utiliser des colonnes à incrémentation automatique comme clés primaires pour les tables InnoDB.

　2. Sélectionnez utf-8 comme jeu de caractères

[Point de vue de Old Ye] Si vous souhaitez économiser de l'espace disque, il est recommandé de choisir latin1. Il est généralement recommandé de choisir UTF-8 pour ce qu'on appelle "l'universalité", mais en fait, les données UTF-8 soumises par l'utilisateur peuvent également être stockées dans le jeu de caractères latin1.

Le problème que vous pouvez rencontrer lorsque vous utilisez latin1 pour stocker des données utf-8 est que s'il existe une récupération basée sur le chinois, elle peut ne pas être précise à 100 % (Lao Ye a personnellement simplement testé la récupération complète chinoise habituelle et ce n'était pas du tout un problème, c'est-à-dire que la comparaison générale avec le chinois ne pose aucun problème).

La méthode d'utilisation du jeu de caractères latin1 pour stocker les données utf-8 est la suivante : le jeu de caractères côté Web (côté utilisateur) est utf-8, et le programme back-end utilise également utf-8 pour le traitement. , mais Character_set_client, Character_set_connection, Character_set_results, Character_set_database et Character_set_server sont tous latin1, et les jeux de caractères des tables et des champs de données sont également latin1. Ou la table de données utilise latin1, exécutez simplement SET NAMES LATIN1 après chaque connexion.

　[Référence] : Une brève présentation du jeu de caractères MySQL.

　3. La longueur physique des enregistrements de lignes de la table InnoDB ne dépasse pas 8 Ko

[Point de vue de Old Ye] La page de données d'InnoDB est par défaut de 16 Ko. En fonction des caractéristiques. de B Tree, une donnée Au moins 2 enregistrements doivent être stockés dans la page. Par conséquent, lorsque la longueur de stockage réelle dépasse 8 Ko (en particulier les colonnes TEXT/BLOB), les grandes colonnes (grandes colonnes) provoqueront un « stockage par débordement de page », similaire à la « migration de lignes » dans ORACLE.

Par conséquent, si vous devez utiliser de grandes colonnes (en particulier les types TEXT/BLOB) et lire et écrire fréquemment, il est préférable de diviser ces colonnes en sous-tables et de ne pas les stocker avec la table principale. Si ce n'est pas trop fréquent, pensez à le conserver dans la table principale.

Bien entendu, si l'option innodb_page_size est modifiée à 8 Ko, il est alors recommandé que la longueur physique de l'enregistrement de ligne ne dépasse pas 4 Ko.

　[Référence] : [MySQL Optimization Case] ​​​​​​Série - Optimisation de l'efficacité du stockage des colonnes BLOB dans les tables InnoDB.

　4. Utiliser ou non des tables de partition

[Point de vue de Old Ye] Dans certains scénarios où l'utilisation de tables de partition peut évidemment améliorer les performances ou la commodité d'exploitation et de maintenance , il est toujours recommandé d'utiliser des tables partitionnées.

Par exemple, Lao Ye utilise le moteur TokuDB dans la base de données de zabbix et utilise des tables de partition basées sur la dimension temporelle. L'avantage de ceci est de garantir que les applications quotidiennes de Zabbix ne sont pas affectées, et il est pratique pour les administrateurs de supprimer régulièrement les données passées. Ils n'ont qu'à supprimer la partition correspondante, et il n'est pas nécessaire d'exécuter une suppression très lente qui affecte. la performance globale.

　[Référence] : Migrez la base de données Zabbix vers TokuDB.

　5. Utiliser ou non des procédures stockées et des déclencheurs

　[Point de vue de Lao Ye] Dans certains scénarios appropriés, l'utilisation de procédures stockées et de déclencheurs ne pose aucun problème.

Nous avions l'habitude d'utiliser le stockage pour terminer le traitement de la logique métier du jeu. Les performances ne sont pas un problème, et une fois que les exigences changent, il suffit de modifier la procédure stockée, et le coût de modification est très faible. Nous utilisons également des déclencheurs pour maintenir une table fréquemment mise à jour. Toutes les modifications apportées à cette table mettront à jour de manière synchrone certains champs vers une autre table (similaire à l'implémentation déguisée de vues matérialisées), et il n'y a aucun problème de performances.

Ne considérez pas les procédures stockées et les déclencheurs de MySQL comme des fléaux. Si vous les utilisez bien, il n'y aura aucun problème. Si vous rencontrez des problèmes, il n'est pas trop tard pour les optimiser. De plus, MySQL ne dispose pas de vues matérialisées, utilisez donc le moins possible les vues.

　6. Choisissez le bon type

【Point de vue de Lao Ye】En plus des suggestions courantes, il y a plusieurs autres points :

　6.1 . Utilisez INT UNSIGNED pour stocker l'adresse IPV4 et utilisez INET_ATON() et INET_NTOA() pour la conversion. Il n'est fondamentalement pas nécessaire d'utiliser CHAR(15) pour le stockage.

6.2. Les types d'énumération peuvent utiliser ENUM. Le mécanisme de stockage interne d'ENUM utilise TINYINT ou SMALLINT (pas CHAR/VARCHAR). N'oubliez pas de ne pas utiliser CHAR/VARCHAR pour stocker les énumérations. Donnez des données.

6.3. En référence à la « désinformation de bon sens » qui s'est répandue plus tôt, il est recommandé de remplacer DATETIME par TIMESTAMP. En fait, à partir de la version 5.6, il est recommandé de donner la priorité à DATETIME pour stocker la date et l'heure, car sa plage disponible est plus grande que TIMESTAMP et le stockage physique n'est que de 1 octet de plus que TIMESTAMP, donc la perte de performances globale n'est pas importante. .

6.4. Dans toutes les définitions de champs, des contraintes NOT NULL sont ajoutées par défaut, à moins qu'elles ne doivent être NULL (mais je ne vois aucun scénario où les valeurs NULL doivent être stockées dans la base de données, qui peuvent être représentées par 0). Lors de l'exécution de statistiques COUNT() sur ce champ, les résultats statistiques seront plus précis (ceux avec des valeurs NULL ne seront pas comptés par COUNT), ou lors de l'exécution d'une récupération WHERE column IS NULL, les résultats peuvent également être renvoyés rapidement.

6.5. Essayez de ne pas directement SELECT * pour lire tous les champs, surtout lorsqu'il y a de grandes colonnes TEXT/BLOB dans la table. Il n'est peut-être pas nécessaire de lire ces colonnes, mais parce que j'étais paresseux et que j'ai écrit SELECT *, le pool de mémoire tampon a été effacé par ces données "indésirables" et les données chaudes qui devaient vraiment être mises en mémoire tampon ont été effacées.

　8. À propos de l'index

[Le point de vue de Old Ye] En plus des suggestions communes, il y a plusieurs points clés :

8.1, plus de 20 Pour les colonnes de chaîne de longueur, il est préférable de créer un index de préfixe plutôt qu'un index de colonne complet (par exemple : ALTER TABLE t1 ADD INDEX(user(20))), ce qui peut améliorer efficacement l'utilisation de l'index. Cependant, son inconvénient. est qu'il n'est pas nécessaire de l'utiliser lors du tri de cette colonne selon l'index du préfixe. La longueur de l'index de préfixe peut être déterminée sur la base des statistiques du champ et est généralement légèrement supérieure à la longueur moyenne.

8.2. Utilisez régulièrement l'outil pt-duplicate-key-checker pour vérifier et supprimer les index en double. Par exemple, si l'index idx1(a, b) couvre déjà l'index idx2(a), l'index idx2 peut être supprimé.

8.3. Lorsqu'il existe un index conjoint multi-champs, l'ordre des champs des conditions de filtre dans WHERE n'a pas besoin d'être cohérent avec l'index, mais il doit être cohérent s'il y a un tri et un regroupement.

Par exemple, s'il existe un index conjoint idx1 (a, b, c), alors le SQL suivant peut utiliser pleinement l'index  :

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到，WHERE中字段顺序并没有和索引字段顺序一致
SELECT ... WHERE b = ? AND a = ? AND c = ?;
SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;
SELECT ... WHERE a = ? AND b = ? ORDER BY c;
SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;
SELECT ... WHERE a = ? ORDER BY b, c;
SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

Et le SQL suivant Alors ne peut utiliser qu'une partie de l'index :

SELECT ... WHERE b = ? AND a = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a IN (?, ?) AND b = ?;   -- 只能用到 (a, b) 部分
SELECT ... WHERE a = ? AND c = ?;   -- 只能用到 (a) 部分
SELECT ... WHERE a = ? AND b IN (?, ?);    -- 只能用到 (a, b) 部分
SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?;   -- 只能用到 (a) 部分，注意BETWEEN和IN的区别
SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?;    -- 只能用到 (a, b) 部分

Le SQL suivant n'utilise pas du tout cet index :

SELECT ... WHERE b = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... WHERE b = ? AND c = ?;
SELECT ... ORDER BY b;
SELECT ... ORDER BY b, a;

Comme le montrent les exemples ci-dessus, le "trompeur du bon sens" qui soulignait dans le passé que l'ordre des champs de condition WHERE doit être cohérent avec l'ordre de l'index avant d'utiliser l'index ne il n'est pas nécessaire de les suivre strictement.

De plus, il arrive parfois que l'index ou le plan d'exécution spécifié par l'optimiseur de requête ne soit pas optimal. Vous pouvez spécifier manuellement l'index optimal ou modifier l'option optimiseur_switch au niveau de la session pour désactiver certains facteurs qui pourraient aggraver la situation. résultats. Fonctionnalités (par exemple, la fusion d'index est généralement une bonne chose, mais il a également été constaté que c'est pire après l'utilisation de la fusion d'index. Dans ce cas, l'un des index doit être forcé à être spécifié, ou la fonction de fusion d'index. peut être temporairement désactivé).

　9. Autres

　9.1 Même s'il s'agit d'un filtrage conditionnel basé sur l'index, si l'optimiseur se rend compte que la quantité totale de données à analyser dépasse 30. % (il semble que dans ORACLE soit 20 %, MySQL est actuellement 30 %, peut-être qu'il sera ajusté à l'avenir), cela changera directement le plan d'exécution en une analyse complète de la table et n'utilisera plus l'index.

9.2. Lors de la jointure de plusieurs tables, la table avec la plus grande filtrabilité (pas nécessairement la plus petite quantité de données, mais celle avec la plus grande filtrabilité après seulement l'ajout de la condition WHERE) doit être sélectionnée comme table de pilotage. De plus, s'il y a un tri après JOIN, le champ de tri doit appartenir à la table des pilotes, afin que l'index de la table des pilotes puisse être utilisé pour terminer le tri.

9.3. Dans la plupart des cas, l'index de tri est généralement plus élevé, donc si vous voyez Utilisation du tri de fichiers dans le plan d'exécution, créez d'abord un index de tri.

9.4. Utilisez pt-query-digest pour analyser régulièrement le journal des requêtes lentes et combinez-le avec Box Anemometer pour créer un système d'analyse et d'optimisation des journaux de requêtes lentes.

　[Référence] : Série [MySQL FAQ] - Quelles informations contenues dans les résultats EXPLAIN devraient susciter des inquiétudes.

Remarque : Sauf indication contraire, les recommandations de spécification ci-dessus s'appliquent à MySQL 5.6 et aux versions antérieures. Il peut y avoir quelques changements dans les versions 5.7 et ultérieures, et les recommandations de spécifications individuelles doivent être ajustées en conséquence.

Ce qui précède est mon opinion sur les spécifications de développement MySQL. Pour plus de contenu connexe, veuillez faire attention au site Web PHP chinois (www.php.cn) !