Comment améliorer la vitesse des requêtes de MySQL

Méthodes pour améliorer la vitesse des requêtes MySQL : 1. Sélectionnez les attributs de champ les plus applicables ; 2. Utilisez JOIN pour remplacer les sous-requêtes ; 3. Utilisez UNION pour remplacer les tables temporaires créées manuellement ; 5. Optimisez les requêtes et essayez d'éviter. analyses de table complètes ; 6. Essayez d'utiliser des variables de table au lieu de tables temporaires, etc.

L'environnement d'exploitation de ce tutoriel : système windows7, version mysql8, ordinateur Dell G3.

La raison de la lenteur des requêtes

Du point de vue d'un programmeur

• L'instruction de requête n'est pas bien écrite

• L'index n'est pas construit, l'index n'est pas construit raisonnablement ou l'index n'est pas valide

• La requête associée comporte trop de jointures

Du point de vue du serveur

• Espace disque du serveur insuffisant
• Paramètres de configuration de réglage du serveur déraisonnables

Huit façons d'optimiser Base de données MySQL

1. Sélectionnez les attributs de champ les plus applicables

MySQL peut bien prendre en charge l'accès à de grandes quantités de données, mais d'une manière générale, plus la table de la base de données est petite, plus les requêtes exécutées dessus sont rapides. sera . Ainsi, lors de la création d'un tableau, Afin d'obtenir de meilleures performances, nous pouvons définir la largeur des champs du tableau aussi petite que possible.

Par exemple, lors de la définition du champ du code postal, si vous le définissez sur CHAR(255), cela ajoutera évidemment de l'espace inutile à la base de données. Même l'utilisation de VARCHAR est redondante, car CHAR(6) peut facilement être bien fait. De même, si possible, nous devrions utiliser MEDIUMINT au lieu de BIGIN pour définir des champs entiers.

Une autre façon d'améliorer l'efficacité consiste à définir les champs sur NOTNULL autant que possible, afin que la base de données n'ait pas besoin de comparer les valeurs NULL lors de l'exécution de requêtes ultérieures.

Pour certains champs de texte, comme « province » ou « sexe », nous pouvons les définir comme de type ENUM.

Parce que dans MySQL, le type ENUM est traité comme des données numériques, et les données numériques sont traitées beaucoup plus rapidement que les types texte. De cette façon, nous pouvons améliorer les performances de la base de données.

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

MySQL prend en charge les sous-requêtes SQL à partir de la version 4.1. Cette technique vous permet d'utiliser une instruction SELECT pour créer une seule colonne de résultats de requête, puis d'utiliser ce résultat comme condition de filtre dans une autre requête. Par exemple, si nous souhaitons supprimer les clients qui n'ont aucune commande dans la table d'informations client de base, nous pouvons utiliser une sous-requête pour récupérer d'abord les identifiants de tous les clients qui ont émis des commandes à partir de la table d'informations sur les ventes, puis transmettre les résultats à la requête principale, comme indiqué ci-dessous :

DELETE FROM customerinfo WHERE CustomerID NOT IN (SELECT CustomerID FROM salesinfo)

L'utilisation de

subquery peut effectuer de nombreuses opérations SQL qui nécessitent logiquement plusieurs étapes à la fois. Elle peut également éviter les verrous de transaction ou de table, et elle est également facile à écrire. Cependant, dans certains cas, les sous-requêtes peuvent être remplacées par des jointures plus efficaces (JOIN). Par exemple, supposons que nous souhaitions récupérer tous les utilisateurs qui n'ont pas d'enregistrement de commande, nous pouvons utiliser la requête suivante pour la compléter :

SELECT * FROM customerinfo WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)

Si vous utilisez une connexion (JOIN)... pour compléter cette requête, la vitesse sera soyez beaucoup plus rapide. Surtout lorsqu'il y a un index sur CustomerID dans la table salesinfo, les performances seront meilleures. La requête est la suivante :

SELECT * FROM customerinfo LEFT JOIN salesinfo 
ON customerinfo.CustomerID=salesinfo.CustomerID 
WHERE salesinfo.CustomerID ISNULL

Join (JOIN).. La raison pour laquelle c'est plus efficace est que MySQL n'a pas besoin de créer un index. table temporaire en mémoire. La réalisation de cette requête logique nécessite deux étapes.

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

MySQL prend en charge les requêtes union à partir de la version 4.0, qui peuvent fusionner deux ou plusieurs requêtes de sélection nécessitant l'utilisation de tables temporaires. Une requête est en cours . À la fin de la session de requête du client, la table temporaire sera automatiquement supprimée pour garantir que la base de données est ordonnée et efficace. Lorsque vous utilisez union pour créer une requête, nous devons uniquement utiliser UNION comme mot-clé pour connecter plusieurs instructions select. Il convient de noter que le nombre de champs dans toutes les instructions select doit être le même. L'exemple suivant illustre une requête utilisant UNION.

SELECT Name,Phone FROM client UNION

SELECT Name,BirthDate FROM author UNION

SELECT Name,Supplier FROM product

4. Affaires

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

BEGIN; INSERT INTO salesinfo SET CustomerID=14; 
UPDATE inventory SET Quantity=11 WHERE item='book'; COMMIT;

事务的另一个重要作用是当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰。

5、锁定表

尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。

其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';

...

UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

这里，我们用一个select语句取出初始数据，通过一些计算，用update语句将新值更新到表中。包含有WRITE关键字的LOCKTABLE语句可以保证在UNLOCKTABLES命令被执行之前，不会有其它的访问来对inventory进行插入、更新或者删除的操作。

6、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。

CREATE  TABLE  customerinfo(CustomerIDINT  NOT    NULL,PRIMARYKEY(CustomerID))TYPE=INNODB;

CREATE  TABLE  salesinfo(SalesIDNT  NOT  NULL,CustomerIDINT  NOT  NULL,

PRIMARYKEY(CustomerID,SalesID),

FOREIGNKEY(CustomerID)  REFERENCES  customerinfo(CustomerID)  ON  DELETE    CASCADE)TYPE=INNODB;

注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。

7、使用索引

索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况

例如customerinfo中的“province”..字段，在这样的字段上建立索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使用ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，但仅能用于MyISAM类型的表。对于一个大的数据库，将数据装载到一个没有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX创建索引，将是非常快的。但如果将数据装载到一个已经有FULLTEXT索引的表中，执行过程将会非常慢。

8、优化的查询语句

绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

28种 SQL 查询语句的优化方法：

1、应尽量避免在 where 子句中使用 != 或者 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

2、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null;

可以在 num 上设置默认值 0 ，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num = 0;

3、查询语句的查询条件中只有OR关键字，并且OR前后的两个条件中的列都是索引时，查询中才使用索引。否则，查询将不使用索引。

4、对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

5、前导模糊查询不能利用索引(like '%XX'或者like '%XX%')，可以使用索引覆盖避免。

6、in 和 not in 也要慎用，否则会导致全表扫描。如:

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

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

select id from t where num=@num

可以改为强制查询使用索引：

select id from t with(index(索引名)) where num=@num

8、应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2 = 100;

应改为：

select id from t where num = 100 * 2;

9、应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name, 1, 3) = ’abc’–name;  //以abc开头的id
 
select id from t where datediff(day,createdate,’2005-11-30′) = 0–’2005-11-30′;  //生成的id

应改为:

select id from t where name like ‘abc%’
 
select id from t where createdate >= ’2005-11-30′ and createdate < ’2005-12-1′;

10、不要在 where 子句中的 “=” 左边进行函数，算术运算或者其他表达式运算，否则系统将可能无法正确使用索引。

11、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

12、很多时候用 exists 代替 in 是一个好的选择：

select num from a where num in(select num from b);

用下面的语句替换：

select num from a where exists(select 1 from b where num=a.num);

13、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

14、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

15、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

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

17、任何地方都不要使用 select * from t ，用具体的字段列表代替 *，不要返回用不到的任何字段。

18、尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。

19、避免频繁创建和删除临时表，以减少系统表资源的消耗。

20、临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

21、在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。

22、如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。

23、尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

24. Avant d'utiliser la méthode basée sur le curseur ou la méthode de table temporaire, vous devez d'abord rechercher une solution basée sur un ensemble pour résoudre le problème. La méthode basée sur un ensemble est généralement plus efficace.

25. Comme les tables temporaires, les curseurs ne sont pas inutilisables. L'utilisation de curseurs FAST_FORWARD avec de petits ensembles de données est souvent meilleure que d'autres méthodes de traitement ligne par ligne, en particulier lorsque plusieurs tables doivent être référencées pour obtenir les données requises. Les routines qui incluent des « totaux » dans un jeu de résultats sont généralement plus rapides que l'utilisation d'un curseur. Si le temps de développement le permet, vous pouvez essayer à la fois la méthode basée sur le curseur et la méthode basée sur les ensembles pour voir quelle méthode fonctionne le mieux.

26. Définissez SET NOCOUNT ON au début de toutes les procédures stockées et déclencheurs, et définissez SET NOCOUNT OFF à la fin. Il n'est pas nécessaire d'envoyer un message DONE_IN_PROC au client après chaque instruction de procédures stockées et de déclencheurs.

27. Essayez d'éviter de renvoyer de grandes quantités de données au client. Si la quantité de données est trop importante, vous devez vous demander si les exigences correspondantes sont raisonnables.

28. Essayez d'éviter les opérations de transactions importantes et d'améliorer la simultanéité du système.

【Recommandation associée : 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!