Comment optimiser les instructions SQL dans MySQL

1. Présentation

Pendant le processus de développement du système d'application, en raison de la petite quantité initiale de données, les développeurs accordent plus d'attention à la mise en œuvre fonctionnelle lors de l'écriture des instructions SQL. officiellement lancé, avec l'augmentation rapide de la quantité de données de production, de nombreuses instructions SQL ont progressivement commencé à présenter des problèmes de performances, et leur impact sur l'environnement de production est devenu de plus en plus grand. À cette époque, ces instructions SQL problématiques sont devenues un goulot d'étranglement. de l'ensemble des performances du système, nous devons donc nous en occuper. Ils sont optimisés.

2. Comprenez la fréquence d'exécution de divers SQL via la commande show status

Une fois le client MySQL connecté, les informations sur l'état du serveur peuvent être fournies via la session show |global]status , ces messages peuvent également être obtenus à l'aide de la commande mysqladmin extended-status sur le système d'exploitation. show [session|global] status peut ajouter le paramètre "session" ou "global" selon les besoins pour afficher les résultats statistiques au niveau de la session (connexion actuelle) et les résultats statistiques au niveau global (depuis le dernier démarrage de la base de données ). S'il n'est pas écrit, le paramètre par défaut est "session".

La commande suivante affiche les valeurs de tous les paramètres statistiques de la session en cours :

-- 查看会话所有统计的值
SHOW STATUS LIKE 'Com_%';
Or
SHOW SESSION STATUS LIKE 'Com_%';

La commande suivante affiche le global actuel Les valeurs de tous les paramètres statistiques dans :

--Afficher les valeurs de toutes les statistiques globales

SHOW GLOBAL STATUS LIKE 'Com_%';

# 🎜🎜#Com_xxx représente chaque instruction xxx Le nombre d'exécutions, nous nous soucions généralement des paramètres statistiques suivants :

• Com_select : Le nombre de fois que l'opération SELECT est exécutée, un seul est accumulé pour une requête.

• Com_insert : Le nombre de fois où effectuer des opérations INSERT pour les opérations INSERT d'insertion par lots, il n'est accumulé qu'une seule fois.

• Com_update : Le nombre d'opérations de MISE À JOUR effectuées.

• Com_delete : Le nombre d'opérations DELETE effectuées.

Les paramètres ci-dessus seront accumulés pour toutes les opérations de table du moteur de stockage. Ces paramètres s'appliquent uniquement au moteur de stockage InnoDB et son algorithme d'accumulation est légèrement différent.

• Innodb_rows_read : Le nombre de lignes renvoyées par la requête SELECT.

• Innodb_rows_inserted : Le nombre de lignes insérées par l'opération INSERT.

• Innodb_rows_updated : Le nombre de lignes mises à jour par l'opération UPDATE.

• Innodb_rows_deleted : Le nombre de lignes supprimées par l'opération DELETE.

Grâce aux paramètres ci-dessus, vous pouvez facilement comprendre si le système d'application de base de données actuel est principalement basé sur des opérations d'insertion et de mise à jour ou des opérations de requête, ainsi que divers types de quoi est le taux d'exécution approximatif de SQL ? Indépendamment de la validation ou de l'annulation, le nombre d'opérations de mise à jour sera accumulé et l'objet count est le nombre d'exécutions.

Pour les applications transactionnelles, Com_commit et Com_rollback peuvent être utilisés pour comprendre la soumission et la restauration des transactions. Pour les bases de données avec des opérations de restauration très fréquentes, cela peut signifier qu'il y a des problèmes dans l'écriture de l'application. De plus, les paramètres suivants aident les utilisateurs à comprendre la situation de base de la base de données.

• Connections : Le nombre de tentatives de connexion au serveur MySQL.

• Uptime : Temps de travail du serveur.

• Slow_queries : Le nombre de requêtes lentes.

3. Localisez les instructions SQL avec une faible efficacité d'exécution

Vous pouvez localiser les instructions SQL avec une faible efficacité d'exécution des deux manières suivantes.

• Localisez les instructions SQL avec une faible efficacité d'exécution via le journal des requêtes lentes. Lors du démarrage avec l'option --log-slow-queries[=file_name], mysqld écrit un. message contenant le fichier journal de toutes les instructions SQL dont l'exécution a pris plus de long_query_time secondes.

• Le journal des requêtes lentes est enregistré une fois la requête terminée, donc lorsque le système d'application reflète des problèmes d'efficacité d'exécution, l'interrogation du journal des requêtes lentes ne peut pas localiser le problème. utiliser show processlist La commande est utilisée pour afficher les threads MySQL actuels, y compris l'état du thread, s'il faut verrouiller la table, etc. Vous pouvez visualiser l'exécution de SQL en temps réel et optimiser certaines opérations de verrouillage de table.

4. Analysez le plan d'exécution de SQL inefficace via EXPLAIN

Après avoir localisé les instructions SQL avec une faible efficacité d'exécution, vous pouvez utiliser EXPLAIN ou The La commande DESC obtient des informations sur la façon dont MySQL exécute l'instruction SELECT, y compris la façon dont les tables sont connectées et l'ordre des connexions lors de l'exécution de l'instruction SELECT. Par exemple, si vous souhaitez compter le nombre de toutes les étapes d'inventaire, vous devez le faire. associez la table marchandises_stock et la table marchandises_stock_price, et effectuez une requête sur le champ marchandises_stock_price.Qty Et opération (somme), le plan d'exécution SQL correspondant est le suivant :

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

#🎜. 🎜#Comme le montre la figure ci-dessus, l'explication simple de chaque colonne est la suivante :

select_type : Indique le type de SELECT valeurs communes ​. ​sont :
SIMPLE (table simple, c'est-à-dire sans utiliser de jointures de table ni de sous-requêtes).
PRIMARY (requête principale, c'est-à-dire la requête externe), UNION (la deuxième instruction de requête ou suivante dans UNION), ◎SUBQUERY (première sous-requête SELECT) etc.

table : La table qui génère l'ensemble de résultats.

• type：表示表的连接类型，性能由好到差的连接类型为：

• system（表中仅有一行，即常量表）。

• const（单表中最多有一个匹配行，例如primary key或者unique index）。

• eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用primary key或者unique index）。

• ref（与eq_ref类似，区别在于不是使用primary key或者unique index，而是使用普通的索引）。

• ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）。

• index_merge（索引合并优化）。

• unique_subquery（in的后面是一个查询主键字段的子查询）。

• index_subquery（与unique_subquery类似，区别在于in的后面是查询非唯一索引字段的子查询）。

• range（单表中的范围查询）。

• index（对于前面的每一行，都通过查询索引来得到数据）。

• all（对于前面的每一行，都通过全表扫描来得到数据）。

• possible_keys：表示查询时，可能使用的索引。

• key：表示实际使用的索引。

• key_len：索引字段的长度。

• rows：扫描行的数量。

• filtered：返回结果的行占需要读到的行(rows列的值)的百分比。

• Extra：执行情况的说明和描述。

• Using index（此值表示mysql将使用覆盖索引，以避免访问表）。

• Using where（mysql 将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当(并且如果)它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。“Using where”有时提示了一种可能性：查询可以从不同的索引中受益。

• Using temporary（mysql 对查询结果排序时会使用临时表）。

• MySQL will apply an external index sorting on the results instead of reading rows from the table in index order.。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成）。

• Range checked for each record(index map: N) （没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的）。

5.确定问题并采取相应的优化措施

经过以上定位步骤，我们基本就可以分析到问题出现的原因。此时我们可以根据情况采取相应的改进措施，进行优化提高语句执行效率。
在上面的例子中，已经可以确认是goods_stock是走主键索引的，但是对goods_stock_price子表的进行了全表扫描导致效率的不理想，那么应该对goods_stock_price表的GoodsStockID字段创建索引，具体命令如下：

-- 创建索引
CREATE INDEX idx_stock_price_1 ON goods_stock_price (GoodsStockID);
-- 附加删除跟查询索引语句
ALTER TABLE goods_stock_price DROP INDEX idx_stock_price_1;
SHOW INDEX FROM goods_stock_price;

创建索引后，我们再看一下这条语句的执行计划，具体如下：

EXPLAIN SELECT SUM(sp.Qty)
FROM goods_stock AS s LEFT JOIN goods_stock_price AS sp
ON s.ID=sp.GoodsStockID;

可以发现建立索引后对goods_stock_price子表需要扫描的行数明显减少（从 3 行减少到1行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。

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!