Tutoriel sur l'optimisation des instructions SQL

Il existe de nombreux tutoriels sur l'optimisation SQL sur Internet, mais ils sont assez compliqués. Je les ai récemment triés quand j'en avais le temps, et je les ai écrits pour les partager avec vous. optimisation. Les amis qui en ont besoin peuvent Pour référence, apprenons avec l'éditeur ci-dessous.

Avant-propos

Cet article partage principalement avec vous les étapes générales de l'optimisation des instructions SQL. Il est partagé pour votre référence et votre étude. pas grand chose à dire ci-dessous. Cela dit, jetons un œil à l’introduction détaillée.

1. Utilisez la commande show status pour comprendre la fréquence d'exécution de divers SQL

Une fois la connexion du client mysql réussie, vous pouvez utiliser la commande show [session|global] status pour fournit des informations sur l'état du serveur, qui peuvent également être obtenues à l'aide de la commande mysqladmin extend-status sur le système d'exploitation.

show status L'option session (par défaut) ou global peut être ajoutée à la commande :

• session (connexion actuelle)

• global (depuis le dernier démarrage des données)

# Com_xxx 表示每个 xxx 语句执行的次数。
mysql> show status like 'Com_%';

Nous nous soucions généralement des paramètres statistiques suivants :

• Com_select : Le nombre de fois que l'opération de sélection est effectuée, une seule est accumulée pour une requête.

• Com_insert : Le nombre d'opérations d'insertion effectuées. Pour les opérations d'insertion par lots, une seule est accumulée.

• Com_update : Le nombre de fois où effectuer des opérations de mise à jour.

• Com_delete : le nombre de fois où l'opération de suppression est effectuée.

Les paramètres ci-dessus seront accumulés pour toutes les opérations de la table du moteur de stockage. Les paramètres suivants concernent uniquement innodb et l'algorithme d'accumulation est légèrement différent :

• Innodb_rows_read : le nombre de lignes renvoyées par la requête de sélection.

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

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

• Innodb_rows_deleted : Nombre de lignes supprimées par opération de suppression.

Grâce aux paramètres ci-dessus, vous pouvez facilement comprendre si l'application de base de données actuelle est principalement basée sur des opérations d'insertion et de mise à jour ou de requête, ainsi que le taux d'exécution approximatif des différents types de SQL Comment c'est beaucoup. Le nombre d'opérations de mise à jour correspond au nombre d'exécutions et sera accumulé indépendamment de la soumission ou de l'annulation.

Pour les applications transactionnelles, vous pouvez utiliser Com_commit et Com_rollback pour comprendre la situation de validation et d'annulation de transaction. Pour les bases de données avec des opérations d'annulation 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 :

• Connexions : le nombre de tentatives de connexion au serveur mysql.

• Disponibilité : temps de travail du serveur.

• Slow_queries : Le nombre de requêtes lentes.

2. Définir les instructions SQL avec une faible efficacité d'exécution

1. Localisez celles avec une faible efficacité d'exécution grâce à une requête lente. logs Instruction SQL inférieure, lorsqu'il est démarré avec l'option --log-slow-queries[=file_name], mysqld écrit un fichier journal contenant toutes les instructions SQL qui prennent plus de long_query_time secondes à s'exécuter.

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

3. Analysez le plan d'exécution de SQL inefficace via expliquer

Test de l'adresse de la base de données : https://downloads.mysql.com/docs /sakila-db.zip (téléchargement local)

Pour compter le montant total payé par un certain email pour la location d'une copie de film, vous devez associer la table client client et la table de paiement paiement, et faire le champ montant du paiement Opération Somme, le plan d'exécution correspondant est le suivant :

mysql> explain select sum(amount) from customer a , payment b where a.customer_id= b.customer_id and a.email='JANE.BENNETT@sakilacustomer.org'\G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

• select_type : représente le type de sélection, les valeurs communes ​​sont :
  ​​​simple : table simple, et n'utilise pas de jointures de table ni de sous-requêtes
  Primaire : La requête principale, la requête de la couche externe
  Union : la deuxième instruction de requête ou suivante dans union
  subquery : La première sélection dans la sous-requête

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

• type : Indique que MySQL trouve tous les résultats dans le tableau La méthode requise, ou type d'accès, les performances des types courants du pire au meilleur sont : all, index, range, ref, eq_ref, const, system, null :
  

1.type=ALL, analyse complète de la table, mysql parcourt toute la table pour trouver les lignes correspondantes :

mysql> explain select * from film where rating > 9 \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: ALL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 33.33
 Extra: Using where
1 row in set, 1 warning (0.01 sec)

2.type=index, 索引全扫描，mysql 遍历整个索引来查询匹配的行

mysql> explain select title form film\G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: film
 partitions: NULL
 type: index
possible_keys: NULL
  key: idx_title
 key_len: 767
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: Using index
1 row in set, 1 warning (0.00 sec)

3.type=range,索引范围扫描，常见于<、<=、>、>=、between等操作：

mysql> explain select * from payment where customer_id >= 300 and customer_id <= 350 \G 

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: range
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: NULL
 rows: 1350
 filtered: 100.00
 Extra: Using index condition
1 row in set, 1 warning (0.07 sec)

4.type=ref, 使用非唯一索引扫描或唯一索引的前缀扫描，返回匹配某个单独值的记录行，例如：

mysql> explain select * from payment where customer_id = 350 \G 
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: payment
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: const
 rows: 23
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warning (0.01 sec)

索引 idx_fk_customer_id 是非唯一索引，查询条件为等值查询条件 customer_id = 350, 所以扫描索引的类型为 ref。ref 还经常出现在 join 操作中：

mysql> explain select b.*, a.* from payment a,customer b where a.customer_id = b.customer_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 599
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.b.customer_id
 rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

5.type=eq_ref,类似 ref，区别就在使用的索引时唯一索引，对于每个索引的键值，表中只要一条记录匹配；简单的说，就是多表连接中使用 primary key 或者 unique index 作为关联条件。

mysql> explain select * from film a , film_text b where a.film_id = b.film_id \G

*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
 type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
 rows: 1000
 filtered: 100.00
 Extra: NULL
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
 type: eq_ref
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: sakila.b.film_id
 rows: 1
 filtered: 100.00
 Extra: Using where
2 rows in set, 1 warning (0.03 sec)

6.type=const/system,单表中最多有一个匹配行，查起来非常迅速，所以这个匹配行中的其他列的值可以被优化器在当前查询中当作常量来处理，例如，根据主键 primary key 或者唯一索引 unique index 进行查询。

mysql> create table test_const (
 ->  test_id int,
 ->  test_context varchar(10),
 ->  primary key (`test_id`),
 -> );
 
insert into test_const values(1,'hello');

explain select * from ( select * from test_const where test_id=1 ) a \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: test_const
 partitions: NULL
 type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 4
  ref: const
 rows: 1
 filtered: 100.00
 Extra: NULL
 1 row in set, 1 warning (0.00 sec)

7.type=null, mysql 不用访问表或者索引，直接就能够得到结果：

mysql> explain select 1 from dual where 1 \G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: NULL
 partitions: NULL
 type: NULL
possible_keys: NULL
  key: NULL
 key_len: NULL
  ref: NULL
 rows: NULL
 filtered: NULL
 Extra: No tables used
1 row in set, 1 warning (0.00 sec)

　　类型 type 还有其他值，如 ref_or_null (与 ref 类似，区别在于条件中包含对 null 的查询)、index_merge(索引合并优化)、unique_subquery (in 的后面是一个查询主键字段的子查询)、index_subquery(与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询)等。

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

• key ：表示实际使用索引

• key-len : 使用到索引字段的长度。

• rows ： 扫描行的数量

• extra：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

show warnings 命令

执行explain 后再执行 show warnings，可以看到sql 真正被执行之前优化器做了哪些 sql 改写：

MySQL [sakila]> explain select sum(amount) from customer a , payment b where 1=1 and a.customer_id = b.customer_id and email = 'JANE.BENNETT@sakilacustomer.org'\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: a
 partitions: NULL
  type: ALL
possible_keys: PRIMARY
  key: NULL
 key_len: NULL
  ref: NULL
  rows: 599
 filtered: 10.00
 Extra: Using where
*************************** 2. row ***************************
  id: 1
 select_type: SIMPLE
 table: b
 partitions: NULL
  type: ref
possible_keys: idx_fk_customer_id
  key: idx_fk_customer_id
 key_len: 2
  ref: sakila.a.customer_id
  rows: 26
 filtered: 100.00
 Extra: NULL
2 rows in set, 1 warning (0.00 sec)

MySQL [sakila]> show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                               |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select sum(`sakila`.`b`.`amount`) AS `sum(amount)` from `sakila`.`customer` `a` join `sakila`.`payment` `b` where ((`sakila`.`b`.`customer_id` = `sakila`.`a`.`customer_id`) and (`sakila`.`a`.`email` = 'JANE.BENNETT@sakilacustomer.org')) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

从 warning 的 message 字段中能够看到优化器自动去除了 1=1 恒成立的条件，也就是说优化器在改写 sql 时会自动去掉恒成立的条件。

explain 命令也有对分区的支持.

MySQL [sakila]> CREATE TABLE `customer_part` (
 -> `customer_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
 -> `store_id` tinyint(3) unsigned NOT NULL,
 -> `first_name` varchar(45) NOT NULL,
 -> `last_name` varchar(45) NOT NULL,
 -> `email` varchar(50) DEFAULT NULL,
 -> `address_id` smallint(5) unsigned NOT NULL,
 -> `active` tinyint(1) NOT NULL DEFAULT '1',
 -> `create_date` datetime NOT NULL,
 -> `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 -> PRIMARY KEY (`customer_id`)
 -> 
 -> ) partition by hash (customer_id) partitions 8;
Query OK, 0 rows affected (0.06 sec)

MySQL [sakila]> insert into customer_part select * from customer;
Query OK, 599 rows affected (0.06 sec)
Records: 599 Duplicates: 0 Warnings: 0

MySQL [sakila]> explain select * from customer_part where customer_id=130\G
*************************** 1. row ***************************
  id: 1
 select_type: SIMPLE
 table: customer_part
 partitions: p2
  type: const
possible_keys: PRIMARY
  key: PRIMARY
 key_len: 2
  ref: const
  rows: 1
 filtered: 100.00
 Extra: NULL
1 row in set, 1 warnings (0.00 sec)

可以看到 sql 访问的分区是 p2。

四、通过 performance_schema 分析 sql 性能

旧版本的 mysql 可以使用 profiles 分析 sql 性能，我用的是5.7.18的版本，已经不允许使用 profiles 了，推荐用
performance_schema 分析sql。

五、通过 trace 分析优化器如何选择执行计划。

mysql5.6 提供了对 sql 的跟踪 trace，可以进一步了解为什么优化器选择 A 执行计划而不是 B 执行计划，帮助我们更好的理解优化器的行为。

使用方式：首先打开 trace ，设置格式为 json，设置 trace 最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整显示。

MySQL [sakila]> set optimizer_trace="enabled=on",end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

MySQL [sakila]> set optimizer_trace_max_mem_size=1000000;
Query OK, 0 rows affected (0.00 sec)

接下来执行想做 trace 的 sql 语句，例如像了解租赁表 rental 中库存编号 inventory_id 为 4466 的电影拷贝在出租日期 rental_date 为 2005-05-25 4:00:00 ~ 5:00:00 之间出租的记录：

mysql> select rental_id from rental where 1=1 and rental_date >= &#39;2005-05-25 04:00:00&#39; and rental_date <= &#39;2005-05-25 05:00:00&#39; and inventory_id=4466;
+-----------+
| rental_id |
+-----------+
| 39 |
+-----------+
1 row in set (0.06 sec)

MySQL [sakila]> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
    QUERY: select * from infomation_schema.optimizer_trace
    TRACE: {
 "steps": [
 ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
  INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

六、 确定问题并采取相应的优化措施

经过以上步骤，基本就可以确认问题出现的原因。此时可以根据情况采取相应的措施，进行优化以提高执行的效率。

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!