Résumer les opérations les plus élémentaires de l'optimisation MySQL

                                                                                                           

Idées d'optimisation

Les étapes détaillées d'optimisation de MySQL sont les suivantes :

• Vérifiez la structure de la table de données et améliorez la conception imparfaite
• Exécutez l'activité principale et collectez les requêtes de base de données SQL couramment utilisées
• Analysez la requête SQL et divisez-le de manière appropriée. Ajoutez des index et d'autres requêtes optimisées
• Tout en optimisant SQL, optimisez la logique du code
• Ajoutez un cache local et un cache Redis

Essayez de ne pas utiliser de valeurs NULL

Parce que lors de la création d'une table, si vous ne définissez pas de valeur par défaut pour la valeur créée, MySQL définira la valeur par défaut sur NULL. Alors pourquoi n'est-il pas bon d'utiliser NULL ? NULL。那么为啥用NULL不好呢？

• NULL使得索引维护更加复杂，强烈建议对索引列设置NOT NULL
• NOT IN、!=等负向条件查询在有NULL值的情况下返回永远为空结果，查询容易出错
• NULL列需要一个额外字节作为判断是否为NULL的标志位
• 使用NULL时和该列其他的值可能不是同种类型，导致问题。（在不同的语言中表现不一样）
• MySQL难以优化对可为NULL的列的查询

所以对于那些以前偷懒的字段，手动设置一个默认值吧，空字符串呀，0呀补上。

虽然这种方法对于MySQL的性能来说没有提升多少，但是这是一个好习惯，而且以小见大，不要忽略这些细节。

添加索引

对于经常查询的字段，请加上索引，有索引和没有索引的查询速度相差十倍甚至更多。

• 一般来说，每张表都需要有一个主键id字段
• 常用于查询的字段应该设置索引
• varchar类型的字段，在建立索引的时候，最好指定长度
• 查询有多个条件时，优先使用具有索引的条件
• 像LIKE条件这样的模糊搜索对于字段索引是无效的，需要另外建立关键词索引来解决
• 请尽量不要在数据库层面约束表和表之间的关系，这些表之间的依赖应该在代码层面去解决

当表和表之间有约束时，虽然增删查的SQL语句变简单了，但是带来的负面效果是插入等操作数据库都会去检查约束（虽然可以手动设置忽略约束），这样相当于把一些业务逻辑写到了数据库层，不便于维护。

优化表字段结构

数据库中那些可以用整形表示的数据就不要使用字符串类型，到底是用varchar还是char要看字段的可能值。

这种优化往往在数据库中有大量数据以后是不可行的，最好在数据库设计之前就设计好。

• 对于那些可能值很有限的列，使用tinyint代替VARCHAR，
  • 比如记录移动设备平台，只有两个值：android，ios，那么就可以使用0表示android，1表示ios，这种列一定要写好注释
  • 为什么不用ENUM呢？ENUM扩展困难，比如后来移动平台又增加了一个ipad，那岂不是懵逼了，而tinyint加个2就行，而且ENUM在代码里面处理起来特别奇怪，是当成整形呢还是字符串，各个语言不一样。
  • 这种方式，一定要在数据库注释或者代码里面写明各个值的含义
• 对于那些定长字符串，可以使用char，比如邮编，总是5位
• 对于那些长度未知的字符串，使用varchar
• 不要滥用bigint，比如记录文章数目的表id字段，用int就行了，21亿篇文章上限够了
• 适当打破数据库范式添加冗余字段，避免查询时的表连接

查询的时候，肯定int类型比varchar

NULL rend la maintenance de l'index plus compliquée. Il est fortement recommandé de définir NOT NULL pour les colonnes d'index

🎜NOT IN, . != et les autres requêtes conditionnelles négatives renverront toujours des résultats vides lorsqu'il y a une valeur <code>NULL. La requête est sujette aux erreurs🎜🎜La colonne NULL nécessite une valeur. octet supplémentaire pour déterminer si lors de l'utilisation de NULL pour le bit d'indicateur 🎜🎜 de NULL, il peut ne pas être du même type que les autres valeurs de la colonne, ce qui pose des problèmes . (Il se comporte différemment selon les langues) 🎜🎜MySQL a du mal à optimiser les requêtes pour les colonnes qui peuvent être NULL 🎜🎜🎜Donc, pour les champs qui étaient paresseux auparavant, définissez manuellement une valeur par défaut, la chaîne de caractères nulle , ajoutez 0. 🎜🎜Bien que cette méthode n'améliore pas beaucoup les performances de MySQL, c'est une bonne habitude, et les petites choses font de grandes choses, alors n'ignorez pas ces détails. 🎜🎜🎜🎜Ajouter un index🎜🎜Pour les champs fréquemment interrogés, veuillez ajouter des index. La vitesse de requête avec et sans index diffère de dix fois ou plus. 🎜🎜🎜De manière générale, chaque table doit avoir un champ de clé primaire id 🎜🎜Les champs couramment utilisés pour les requêtes doivent être indexés 🎜🎜Champs de type varchar, lors de la création Lors de l'indexation , il est préférable de spécifier la longueur🎜🎜Lorsque la requête comporte plusieurs conditions, les conditions avec index sont préférées🎜🎜Les recherches floues telles que les conditions LIKE ne sont pas valides pour les index de champ et des clés supplémentaires doivent être établies Index de mots à résoudre🎜🎜Veuillez essayer de ne pas contraindre la relation entre les tables au niveau de la base de données. Les dépendances entre ces tables doivent être résolues au niveau du code🎜🎜🎜Lorsqu'il y a un problème entre les tables Quand. contraignant, bien que l'instruction SQL d'ajout, de suppression et de requête devienne plus simple, l'effet négatif est que la base de données vérifiera les contraintes pour des opérations telles que l'insertion (bien que les contraintes puissent être définies manuellement pour les ignorer), ce qui équivaut à écrire quelques logique métier à la couche de base de données. Facile à maintenir. 🎜🎜🎜🎜Optimiser la structure des champs de la table🎜🎜N'utilisez pas de types de chaîne pour les données de la base de données qui peuvent être représentées par des entiers. L'utilisation de varchar ou de char dépend de. le champ. 🎜🎜Ce type d'optimisation n'est souvent pas réalisable lorsqu'il y a une grande quantité de données dans la base de données. Il est préférable de la concevoir avant la conception de la base de données. 🎜🎜🎜Pour les colonnes avec des valeurs possibles très limitées, utilisez tinyint au lieu de VARCHAR, 🎜🎜Par exemple, pour enregistrer la plate-forme d'appareil mobile, il n'y a que deux valeurs : Android , ios, alors Vous pouvez utiliser 0 pour représenter android et 1 pour représenter ios. Ce type de colonne doit être bien commenté🎜🎜Pourquoi ne pas utiliser ENUM ? Il est difficile de développer ENUM. Par exemple, lorsque la plate-forme mobile a ensuite ajouté un ipad, cela ne serait-il pas déroutant ? Mais ajoutez simplement un 2 à tinyint. et ENUM sont très étranges à gérer dans le code. Qu'il soit traité comme un entier ou une chaîne varie d'une langue à l'autre. 🎜🎜Dans cette méthode, la signification de chaque valeur doit être écrite dans le commentaire ou le code de la base de données🎜🎜🎜🎜Pour ces chaînes de longueur fixe, vous pouvez utiliser char, tel que le code postal, qui est toujours 5 chiffres🎜 🎜Pour les chaînes de longueur inconnue, utilisez varchar🎜🎜N'abusez pas de bigint, comme le champ id de la table qui enregistre le nombre d'articles, utilisez int fera l'affaire, la limite supérieure de 2,1 milliards d'articles est suffisante🎜🎜Briser correctement le paradigme de la base de données et ajouter des champs redondants pour éviter les connexions de table lors des requêtes🎜🎜🎜Lors de l'interrogation, assurez-vous d'utiliser le type int. Il est plus rapide que varchar, car la comparaison d'entiers peut être réalisée en appelant directement l'opérateur sous-jacent, tandis que la comparaison de chaînes nécessite des caractères- comparaison par caractère. 🎜🎜La requête de données de longueur fixe est plus rapide que la requête de données de longueur variable, car le décalage entre les données de longueur fixe et les données est fixe et il est facile de calculer le décalage des données suivantes. Pour les données de longueur variable, une étape supplémentaire est nécessaire pour interroger le décalage des données suivantes. mais. Les données de longueur fixe peuvent gaspiller davantage d'espace de stockage. 🎜

Répartition des grandes tables

Pour les tables dont le volume de données peut dépasser 5 millions dans un avenir proche ou croître rapidement, assurez-vous de diviser les tables verticalement ou horizontalement à l'avance. Lorsque le volume de données dépasse un million, la vitesse de requête sera. déclin évident.

Essayez de finaliser le plan de la sous-base de données et de la sous-table dès le début de la conception de la base de données, sinon cela augmentera considérablement la complexité du code et sera difficile à modifier plus tard.

Le partitionnement vertical de la table est basé sur des variables externes telles que les dates, et le partitionnement horizontal de la table est basé sur certaines relations de champs dans la table, en utilisant le mappage de hachage pour diviser la table en parties égales.

La condition préalable pour la sous-base de données et la sous-base de données de table est qu'avant d'exécuter l'instruction de requête, vous sachiez déjà dans quelle sous-base de données et dans quelle sous-table les données à interroger peuvent appartenir.

Optimiser les instructions de requête

C'est l'initiateur de goulots d'étranglement dans les bases de données dans de nombreux systèmes.

• Veuillez essayer d'utiliser des requêtes simples et évitez d'utiliser des liens de table
• Veuillez essayer d'éviter les analyses de table complètes. Les instructions qui entraîneront des analyses de table complètes incluent, sans s'y limiter :
  • La condition de la clause Where est toujours vraie ou vide
  .
  • Utilisez LIKELIKE
  • 使用不等操作符（<>、!=）
  • 查询含有is null的列
  • 在非索引列上使用or
  Utilisez les opérateurs d'inégalité (<>, !=)
Requête des colonnes contenant est nul
• Utilisez ou sur des colonnes non indexées
• • Lors d'une requête avec plusieurs conditions, veuillez mettre des conditions de requête simples ou une requête de colonne d'index devant
  • Veuillez essayer de spécifier les colonnes que vous devez interroger, ne soyez pas paresseux pour utiliser select *
Si vous ne le précisez pas, il renverra d'une part les données redondantes, la bande passante occupée, etc.
• D'autre part, lorsque MySQL exécute une requête, s'il n'y a pas de champs, il interrogera d'abord les champs de la table structure
• Les mots-clés de requête en majuscules sont un peu plus rapides que les minuscules
• L'utilisation de sous-requêtes La création d'une table temporaire sera légèrement plus lente que JOIN et UNION
• Essayez de ne pas utiliser les fonctions de base de données lors des requêtes sur les champs d'index, car cela n'est pas pratique pour mettre en cache les résultats de la requête

Lorsque vous n'avez besoin que d'une seule ligne de données, veuillez utiliser LIMIT 1. Si les données sont trop nombreuses, veuillez définir LIMIT de manière appropriée, requête de paginationNe commandez jamais ORDER BY RAND(), les performances sont extrêmement faibles

• Ajouter du cache
• Utilisez Redis et d'autres caches, ainsi que le cache de fichiers local, etc., ce qui peut réduire considérablement le nombre de requêtes de base de données. En matière de mise en cache, vous devez analyser les caractéristiques des données de votre propre système et faire les choix appropriés.
• Certaines données couramment utilisées, telles que les informations de configuration, etc., peuvent être placées dans le cache
• La structure des tables de la base de données peut être mise en cache localement

Les données mises en cache doivent être mises à jour dans le temps et la validité. la période doit être définie. Il est nécessaire d'augmenter le cache. Pour augmenter la complexité du système, assurez-vous de faire attention aux compromis

Vérifiez la structure de la table de données🎜🎜Apprentissage recommandé : "🎜Tutoriel vidéo 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!