Les dix principales méthodes d'optimisation pour MySQL

1. Sélectionnez l'attribut de champ le plus applicable

Définissez la largeur du champ dans le tableau aussi petite que possible : char La limite supérieure de varchar est de 255 octets (espace fixe occupé), la limite supérieure de varchar est de 65 535 octets (espace réellement occupé) et la limite supérieure du texte est de 65 535. char est plus efficace que varchar.

Essayez de définir les champs sur NOT NULL Lors de l'exécution de requêtes, la base de données n'a pas besoin de comparer les valeurs NULL.

2. Utilisez des jointures (JOIN) au lieu de sous-requêtes (Sous-requêtes)

La raison pour laquelle les jointures (JOIN) sont plus efficaces est que MySQL ne le fait pas. besoin Créez une table temporaire en mémoire pour terminer cette requête logique qui nécessite deux étapes (les conditions de requête conjointes et les index sont plus rapides).

3. Utilisez UNION pour remplacer les tables temporaires créées manuellement.

Une requête qui combine deux ou plusieurs requêtes SELECT nécessitant l'utilisation de tables temporaires.

SELECT Nom, Téléphone FROM client UNION SELECT Nom, Date de naissance FROM auteur UNION SELECT Nom, Fournisseur FROM produit ;

Transactions

Bien que nous. Vous pouvez utiliser des sous-requêtes (Sub-Queries), des connexions (JOIN) et des unions (UNION) pour créer diverses requêtes, mais toutes les opérations de base de données ne peuvent pas être effectuées avec une ou quelques instructions SQL. Le plus souvent, une série d’énoncés est nécessaire pour accomplir un certain type de travail.

L'effet est le suivant : soit chaque instruction du bloc d'instructions fonctionne avec succès, soit toutes échouent. En d’autres termes, la cohérence et l’intégrité des données de la base de données peuvent être maintenues. Les choses commencent par le mot-clé BEGIN et se terminent par le mot-clé COMMIT. Si une opération SQL échoue pendant cette période, la commande ROLLBACK peut restaurer la base de données à l'état avant le démarrage de BEGIN.

5. Verrouillage des tables

Bien que les transactions soient un très bon moyen de maintenir l'intégrité de la base de données, elles affectent parfois les performances de la base de données en raison de leur exclusivité, en particulier dans les grands systèmes d'applications. . Étant donné que la base de données sera verrouillée lors de l'exécution de la transaction, les autres demandes des utilisateurs ne pourront qu'attendre la fin de la transaction.

LOCK TABLE inventory WRITE 
SELECT Quantity FROM inventory 
WHEREItem='book'; 
... 
UPDATE inventory SET Quantity=11 
WHEREItem='book'; 
UNLOCK TABLES

Ici, nous utilisons une instruction SELECT pour obtenir les données initiales, via quelques calculs, et utilisons une instruction UPDATE pour mettre à jour les nouvelles valeurs dans le tableau. L'instruction LOCK TABLE contenant le mot clé WRITE garantit qu'il n'y aura aucun autre accès à l'inventaire à insérer, mettre à jour ou supprimer avant l'exécution de la commande UNLOCK TABLES.

6. L'utilisation de clés étrangères

pour verrouiller la table peut maintenir l'intégrité des données, mais elle ne peut pas garantir la pertinence des données. A ce stade, nous pouvons utiliser des clés étrangères. Par exemple, une clé étrangère peut garantir que chaque enregistrement de vente pointe vers un client existant. Ici, la clé étrangère peut mapper le CustomerID de la table customerinfo au CustomerID de la table salesinfo. Tout enregistrement sans CustomerID valide ne sera pas mis à jour ou inséré dans salesinfo.

CREATE TABLE customerinfo 
( 
CustomerID INT NOT NULL , 
PRIMARY KEY ( CustomerID ) 
) TYPE = INNODB; 
CREATE TABLE salesinfo 
( 
SalesID INT NOT NULL, 
CustomerID INT NOT NULL, 
PRIMARY KEY(CustomerID, SalesID), 
FOREIGN KEY (CustomerID) REFERENCES customerinfo 
(CustomerID) ON DELETECASCADE 
) TYPE = INNODB;

Notez le paramètre « ON DELETE CASCADE » dans l'exemple. Ce paramètre garantit que lorsqu'un enregistrement client dans la table customerinfo est supprimé, tous les enregistrements liés au client dans la table salesinfo seront également automatiquement supprimés. Si vous souhaitez utiliser des clés étrangères dans MySQL, n'oubliez pas de définir le type de table comme un type InnoDB sécurisé pour les transactions lors de la création de la table. Ce type n'est pas le type par défaut pour les tables MySQL. Il est défini en ajoutant TYPE=INNODB à l'instruction CREATE TABLE.

7. Lors de l'utilisation de l'index

lorsque l'instruction de requête contient des commandes telles que MAX (), MIN () et ORDERBY, l'amélioration des performances est plus évidente.

Les indices doivent être construits sur les champs qui seront utilisés pour le tri JOIN, WHERE et ORDER BY. Essayez de ne pas indexer un champ de la base de données contenant un grand nombre de valeurs en double. Pour un champ de type ENUM, il est très possible d'avoir un grand nombre de valeurs en double, comme par exemple le champ "province".. dans customerinfo ne servira à rien, bien au contraire. Réduisez les performances de la base de données.

La seule tâche d'un index normal (un index défini par les mots-clés KEY ou INDEX) est d'accélérer l'accès aux données. Par conséquent, les index ne doivent être créés que pour les colonnes de données qui apparaissent le plus fréquemment dans les conditions de requête (WHEREcolumn=) ou les conditions de tri (ORDERBYcolumn).

Les avantages de l'index unique : Premièrement, MySQL simplifie la gestion de cet index, et l'index devient plus efficace ; deuxièmement, MySQL vérifiera automatiquement les nouveaux enregistrements lorsque de nouveaux enregistrements sont insérés dans la table de données. la valeur de ce champ de l'enregistrement est déjà apparue dans ce champ d'un enregistrement ; si c'est le cas, MySQL refusera d'insérer le nouvel enregistrement ; En d’autres termes, un index unique peut garantir l’unicité des enregistrements de données. Dans de nombreux cas, le but de la création d’un index unique n’est pas d’améliorer la vitesse d’accès, mais simplement d’éviter la duplication des données.

8. Instructions de requête optimisées

SELECT FROM order WHERE YEAR(OrderDate)<2001; 
SELECT FROM order WHERE OrderDate<"2001-01-01";
SELECT FROM inventory WHERE Amount/7<24; 
SELECT FROM inventory WHERE Amount<24*7;

Évitez de laisser MySQL effectuer une conversion de type automatique dans la requête, car le processus de conversion rendra également l'index inefficace.

9. Échec de l'index

like 以 % 开头，索引无效；当 like 前缀没有 %，后缀有 % 时，索引有效。

or 语句前后没有同时使用索引。当 or 左右查询字段只有一个是索引，该索引失效，只有当 or 左右查询字段均为索引时，才会生效。

组合索引，不是使用第一列索引，索引失效。

数据类型出现隐式转化。如 varchar 不加单引号的话可能会自动转换为 int 型，使索引无效，产生全表扫描。

在索引字段上使用 not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。

当全表扫描速度比索引速度快时，mysql 会使用全表扫描，此时索引失效。

应尽量避免在 where 子句中使用 or,and,in,not in 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，合理使用 union all（允许重复的值，请使用 UNION ALL）。

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10    
union all    
select id from t where num=20

10. 引擎的选取

MyISAM 索引文件在数据库中存放的对应表的磁盘文件有.frm,.MYD,*.MYI 结尾的三个文件：

frm 文件是存放的表结构，表的定义信息；

MYD 文件是存放着表中的数据；

MYI 文件存放着表的索引信息；

InnoDB 存储引擎在磁盘中存放的对应的表的磁盘文件有.frm,.ibd 这两个文件；

frm 文件是存放表结构，表的定义信息；

ibd 文件是存放 表中的数据、索引信息；

详细出处参考：https://blog.csdn.net/jinxingfeng_cn/article/details/16878355

性能方面的优化：

explain 执行计划 ==>https://blog.csdn.net/yhl_jxy/article/details/88570154

一、分表的分类（单表记录条数达到百万到千万级别时就要使用分表）

1. 纵向分表

文章标题，作者，分类，创建时间等，是变化频率慢，查询次数多，而且最好有很好的实时性的数据，我们把它叫做冷数据。

浏览量，回复数等，类似的统计信息，或者别的变化频率比较高的数据，我们把它叫做活跃数据。

首先存储引擎的使用不同，冷数据使用 MyIsam 可以有更好的查询数据。活跃数据，可以使用 Innodb , 可以有更好的更新速度。

就是把原来一张表里的字段，冷数据的字段和活跃数据的字段分别建立 2 张表来管理。

2. 横向分表

把大的表结构，横向切割为同样结构的不同表，如，用户信息表，user_1,user_2 等，表结构是完全一样。

二、慢查询

show variables like 'slow%';
show global status like 'slow%';

使用 mysqlreport；

正确使用索引：explain 分析查询语句，组合索引，索引副作用（占空间、update）

开启慢查询日志、使用慢查询分析工具 mysqlsla；

索引缓存、索引代价（插入更新索引）；

表锁，行锁，行锁副作用（update 多时候变慢），在 select 和 update 混合的情况下，行锁巧妙解决了读写互斥的问题；

开启使用查询缓存；

修改临时表内存空间；

开启线程池；

MySQL Query 语句优化的基本思路和原则

1、优化需要优化的 Query；

2、定位优化对象的性能瓶颈；

3、明确优化目标；

4、从 Explaing 入手；

5、多使用 Profile；

6、永远用小结果集推动大的结果集；

7、尽可能在索引中完成排序；

8、只取自己需要的 Columns；

9、仅仅使用最有效的过滤条件；

10、尽可能避免复杂的 Join 和子查询。

推荐教程：《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!