Comment optimiser la base de données MySQL

Méthodes pour optimiser la base de données MySQL : établir un index d'index, utiliser moins d'instructions de sélection, activer la mise en cache des requêtes, choisir un moteur de stockage approprié, éviter d'utiliser ou dans la clause Where pour se connecter et éviter de renvoyer de grandes quantités de données, etc.

Pour une application centrée sur les données, la qualité de la base de données affecte directement les performances du programme, les performances de la base de données sont donc cruciales. Par conséquent, tout le monde doit comprendre les opérations d'optimisation de la base de données MySQL. Cet article résume principalement les opérations d'optimisation courantes dans la base de données MySQL. Je n'entrerai pas dans plus de détails ci-dessous, jetons un œil à l'introduction détaillée.

1. Index

Mettez l'index en premier. Inutile de dire que nous avons discrètement optimisé cette façon d'utiliser, c'est le cas. index de clé primaire. Parfois, nous ne nous en soucions pas. Si un index approprié est défini, les performances (vitesse) des requêtes de base de données seront améliorées plusieurs fois, voire des dizaines de fois.

2. Utilisez SELECT* avec parcimonie

Certaines personnes peuvent sélectionner ce qu'elles veulent interroger lors de l'interrogation de la base de données. Nous devrions obtenir les données que nous voulons utiliser, pas toutes, car lorsque nous les sélectionnons, cela augmentera la charge sur le serveur Web, augmentera la charge sur la transmission réseau et la vitesse des requêtes diminuera naturellement.

3. EXPLAIN SELECT

On estime que de nombreuses personnes n'ont jamais vu cette fonction, mais il est fortement recommandé de l'utiliser. expliquer montre comment MySQL utilise les index pour gérer les instructions de sélection et les tables de jointure. Peut aider à choisir de meilleurs index et à écrire des instructions de requête plus optimisées. L'utilisation principale est d'ajouter une explication avant de sélectionner.

EXPLAIN SELECT [查找字段名] FROM tab_name ...

4. Activer le cache de requêtes

La plupart des serveurs MySQL ont le cache de requêtes activé. C'est l'un des moyens les plus efficaces d'améliorer les performances, et il est géré par le moteur de base de données MySQL. Lorsque plusieurs des mêmes requêtes sont exécutées plusieurs fois, les résultats de la requête seront placés dans un cache, de sorte que les requêtes identiques suivantes n'auront pas besoin d'exploiter la table mais d'accéder directement aux résultats mis en cache.

La première étape consiste à définir query_cache_type sur ON, puis à vérifier si la variable système have_query_cache est disponible :

show variables like 'have_query_cache'

Après cela, allouez la taille de la mémoire au cache de requêtes et contrôlez la valeur maximale des résultats de requête mis en cache. Les opérations pertinentes sont modifiées dans le fichier de configuration.

5. Utilisez NOT NULL

De nombreuses tables contiennent des colonnes qui peuvent être NULL (valeur nulle), même si l'application n'a pas besoin d'enregistrer NULL, c'est parce que NULL est la propriété par défaut de la colonne. Il est généralement préférable de spécifier les colonnes comme NOT NULL, sauf si vous avez vraiment besoin de stocker des valeurs NULL.

Les requêtes contenant des colonnes NULLable sont plus difficiles à optimiser pour MySQL car les colonnes NULLable rendent les index, les statistiques d'index et les comparaisons de valeurs plus complexes. Les colonnes qui peuvent être NULL utilisent plus d'espace de stockage et nécessitent un traitement spécial dans MySQL. Lorsque les colonnes NULLable sont indexées, chaque enregistrement d'index nécessite un octet supplémentaire, ce qui, dans MyISAM, peut même faire qu'un index de taille fixe (comme un index avec une seule colonne entière) devienne un index de taille variable.

Habituellement, changer la colonne NULL en NOT NULL n'apporte que peu d'amélioration des performances, donc (lors du réglage), il n'est pas nécessaire de rechercher et de modifier d'abord cette situation dans le schéma existant, sauf si vous êtes sûr que cela entraînera. un problème. Cependant, si vous envisagez de créer un index sur une colonne, vous devez éviter de concevoir la colonne comme étant NULL. Bien sûr, il existe des exceptions. Par exemple, il convient de mentionner qu'InnoDB utilise un bit séparé pour stocker les valeurs NULL, ce qui lui confère une bonne efficacité spatiale pour les données clairsemées. Mais cela ne s'applique pas à MyISAM.

6. Sélection du moteur de stockage

Concernant la façon de choisir MyISAM et InnoDB, si vous avez besoin d'un traitement de transactions ou de clés étrangères, alors InnoDB peut être un meilleur moyen. Si vous avez besoin d'une indexation en texte intégral, MyISAM est généralement un bon choix car il est intégré au système. Cependant, nous ne testons pas très souvent 2 millions de lignes d'enregistrements. Ainsi, même si c'est un peu plus lent, nous pouvons obtenir un index en texte intégral depuis InnoDB en utilisant Sphinx.

La taille des données est un facteur important qui affecte le moteur de stockage que vous choisissez. Les ensembles de données de grande taille ont tendance à choisir InnoDB car il prend en charge le traitement des transactions et la récupération après échec. La taille de la base de données détermine la durée du temps de récupération après panne. InnoDB peut utiliser les journaux de transactions pour la récupération des données, ce qui sera plus rapide. Alors que MyISAM peut prendre

heures, voire jours, pour faire ces choses, InnoDB ne prend que quelques minutes.

Votre habitude d'utiliser les tables de base de données peut également être un facteur qui affecte grandement les performances. Par exemple : COUNT() sera très rapide dans la table MyISAM, mais peut être pénible dans la table InnoDB. La requête de clé primaire sera assez rapide sous InnoDB, mais nous devons faire attention au fait que si notre clé primaire est trop longue, cela entraînera également des problèmes de performances. Un grand nombre d'instructions d'insertion seront plus rapides sous MyISAM, mais les mises à jour seront plus rapides sous InnoDB - en particulier lorsque le degré de concurrence est important.

所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM也许会更适合。当然，在大型的环境下使用MyISAM也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用InnoDB方式。但需要记住InnoDB的表需要更多的内存和存储，转换100GB的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

七、避免在 where 子句中使用 or 来连接

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：  

select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'

八、多使用varchar/nvarchar

使用varchar/nvarchar代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

九、避免大数据量返回

这里要考虑使用limit，来限制返回的数据量，如果每次返回大量自己不需要的数据，也会降低查询速度。

十、where子句优化

where 子句中使用参数，会导致全表扫描,因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然 而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。

应尽量避免在 where 子句中对字段进行表达式操作，避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描。不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

相关学习推荐：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!