Optimisation efficace des instructions SQL MySQL - index

1 Combinaison d'union de deux index

ALTER TABLE album ADD INDEX name_release (name,first_released);
EXPLAIN SELECT a.name, ar.name,
a.first_released
  FROM album a
 INNER JOIN artist ar USING (artist_id)
 WHERE a.name = 'Greatest Hits'
 ORDER BY a.first_released;
mysql> EXPLAIN SELECT a.name, ar.name,
    -> a.first_released
    ->   FROM album a
    ->  INNER JOIN artist ar USING (artist_id)
    ->  WHERE a.name = 'Greatest Hits'
    ->  ORDER BY a.first_released;
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
| id | select_type | table | type   | possible_keys                  | key          | key_len | ref               | rows | Extra       |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
|  1 | SIMPLE      | a     | ref    | name_release,name_2,name_part2 | name_release | 257     | const             |  659 | Using where |
|  1 | SIMPLE      | ar    | eq_ref | PRIMARY                        | PRIMARY      | 4       | union.a.artist_id |    1 |             |
+----+-------------+-------+--------+--------------------------------+--------------+---------+-------------------+------+-------------+
2 rows in set (0.00 sec)

ALTER TABLE album ADD INDEX name_release (name,first_released);

MySQL peut utiliser des index dans les colonnes WHERE, ORDER BY et GROUP BY Cependant, en général ; MySQL ne sélectionne qu'un seul index sur une table.
À partir de MySQL 5.0, l'optimiseur peut utiliser plus d'un index dans des exceptions individuelles, mais cela dans les versions antérieures entraînerait l'exécution des requêtes plus lentement.

2 Union de deux index
Premier type : L'opération de fusion d'index la plus courante est l'union de deux index lorsque l'utilisateur ce type de fusion d'index. L'opération se produit lorsque deux index avec une cardinalité très
élevée effectuent une opération OU. Veuillez
regarder l'exemple suivant :

 SET @@session.optimizer_switch='index_merge_intersection=on';
 
 EXPLAIN SELECT artist_id, name
 FROM artist
 WHERE name = 'Queen'
 OR founded = 1942\G
 
mysql>  EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->  WHERE name = 'Queen'
    ->  OR founded = 1942;
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.01 sec)

Extra : Utilisation de union(name,founded); prenez la collection.

Remarque
La variable système optimiseur_switch a été introduite pour la première fois dans MySQL 5.1. Vous pouvez
contrôler ces options supplémentaires en activant ou en désactivant cette variable. Pour plus d'informations,
veuillez vous référer au lien suivant : http://www.php.cn/.

2 Le deuxième type de fusion d'index consiste à croiser deux index avec un petit nombre de valeurs uniques, comme indiqué ci-dessous :

SET @@session.optimizer_switch='index_merge_intersection=on';
EXPLAIN SELECT artist_id, name
 FROM artist
  WHERE type = 'Band'
 AND founded = 1942;
 
 mysql> SET @@session.optimizer_switch='index_merge_intersection=on';
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> 
mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->   WHERE type = 'Band'
    ->  AND founded = 1942;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
|  1 | SIMPLE      | artist | ref  | founded       | founded | 2       | const |  498 | Using where |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------------+
1 row in set (0.00 sec)

Extra : Utilisation de intersect(founded,type); Utilisation de Where Puisqu'il s'agit de AND, il vous suffit de prendre l'index le plus efficace parmi les deux index pour parcourir la valeur.

3 Le troisième type d'opération de fusion d'index est similaire à l'union de deux index, mais il doit d'abord être trié :

EXPLAIN SELECT artist_id, name
 FROM artist
 WHERE name = 'Queen'
  OR (founded BETWEEN 1942 AND 1950);
  mysql> EXPLAIN SELECT artist_id, name
    ->  FROM artist
    ->  WHERE name = 'Queen'
    ->   OR (founded BETWEEN 1942 AND 1950);
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                       |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL | 5900 | Using sort_union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+---------------------------------------------+
1 row in set (0.00 sec)

Vous pouvez en savoir plus sur la fusion d'index via le lien suivant : http://www.php.cn/.

4 Cas de fusion de plusieurs index
Au cours du processus de création de ces exemples, j'ai également découvert une méthode qui n'était apparue dans aucune requête client auparavant. situation nouvelle. Voici un exemple de fusion de trois index :

mysql> EXPLAIN SELECT artist_id, name
  FROM artist
  WHERE name = 'Queen'
 OR (type = 'Band' AND founded = '1942');
 .....
mysql> EXPLAIN SELECT artist_id, name
    ->   FROM artist
    ->   WHERE name = 'Queen'
    ->  OR (type = 'Band' AND founded = '1942');
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
| id | select_type | table  | type        | possible_keys | key          | key_len | ref  | rows | Extra                                  |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
|  1 | SIMPLE      | artist | index_merge | name,founded  | name,founded | 257,2   | NULL |  499 | Using union(name,founded); Using where |
+----+-------------+--------+-------------+---------------+--------------+---------+------+------+----------------------------------------+
1 row in set (0.00 sec)

Conseils
Les index multi-colonnes doivent toujours être évalués pour voir s'ils sont meilleurs qu'optimisés, le processeur fusionne les index plus efficacement. Lequel présente le plus d’avantages : plusieurs index à une seule colonne ou plusieurs index à plusieurs colonnes ? Cette question
ne peut recevoir de réponse qu'en conjonction avec le type de requête et la capacité de requête de l'application spécifique. Dans diverses conditions de requête, la
fusion d'index de ces index à colonne unique sur certaines colonnes à cardinalité élevée peut apporter une grande flexibilité. Les facteurs de référence de performance des opérations d'écriture de base de données affecteront également le chemin d'accès optimal aux données pour obtenir des données.

5 Créez de meilleurs index MySQL
Utilisez principalement 2 index spéciaux

En utilisant des index, interrogez Le temps d'exécution peut être réduit du de l'ordre de la seconde à l'ordre de la milliseconde. De telles améliorations des performances peuvent faire bondir les performances de votre application.
Régler correctement vos index est très important pour l'optimisation, en particulier pour les applications à haut débit. Même si l'amélioration du temps d'exécution n'est que de quelques millisecondes, il s'agit d'une amélioration des performances très significative pour
une requête exécutée 1000 fois par seconde. Par exemple, réduire le temps d'exécution
de 4 millisecondes pour une requête qui prend initialement 20 millisecondes pour s'exécuter 1 000 fois par seconde est crucial pour optimiser les instructions SQL. Nous utiliserons les méthodes présentées au chapitre 4 pour créer des index multi-colonnes et nous baserons sur cette base pour créer des index mieux couvrants.

Quantity Créer un index de couverture
ALTER TABLE artiste DROP INDEX fondé,
ADD INDEX found_name (founded,name);
Dans InnoDB, le code principal La valeur de sera ajoutée à chaque enregistrement correspondant dans l'index de clé non primaire, il n'est donc pas nécessaire de spécifier la clé primaire dans l'index de clé non primaire.
Cette fonctionnalité importante signifie que tous les index de clé non primaire du moteur InnoDB impliquent des colonnes de clé primaire. Et pour les tables converties à partir du moteur de stockage MyISAM, la clé primaire est généralement ajoutée comme dernier élément dans leurs index de table InnoDB. Lorsque QEP affiche Using index dans la colonne Extra, cela ne signifie pas que l'index est utilisé lors de l'accès aux données de la table sous-jacente. Cela signifie que seul cet index répond à toutes les exigences de la requête. Ce type d'index peut apporter des améliorations significatives des performances aux requêtes volumineuses ou aux requêtes fréquemment exécutées. C'est ce qu'on appelle un index de couverture. Un index couvrant tire son nom du fait qu'il couvre toutes les colonnes utilisées dans une table donnée dans une requête. Pour
créer un index de couverture, cet index doit contenir toutes les colonnes de la table spécifiée, y compris l'instruction WHERE, l'instruction ORDER BY, l'instruction GROUP BY (le cas échéant) et l'instruction
SELECT.

[Commentaire] : À mesure que la capacité des données augmente, en particulier lorsqu'elle dépasse la capacité maximale de la mémoire et du disque, la création d'un index pour une grande colonne peut
avoir un impact sur les performances globales du système. Les index de couverture constituent une optimisation idéale pour les grands schémas normalisés qui utilisent de nombreuses contraintes de clé primaire et de clé étrangère de petite longueur.

● Créer un index pour une colonne locale

ALTER TABLE artist
 DROP INDEX name,
  ADD INDEX name_part(name(20));

这里主要考虑的是如何减小索引占用的空间。一个更小的索引意味着更少的磁盘I/O 开销，而这又意味着能更快地访问到需
要访问的行，尤其是当磁盘上的索引和数据列远大于可用的系统内存时。这样获得的性能改进将会超过一个非唯一的并且拥有低
基数的索引带来的影响。局部索引是否适用取决于数据是如何访问的。之前介绍覆盖索引时，你可以看到记录一个短小版本的name 列不会对执行过
的SQL 语句有任何好处。最大的益处只有当你在被索引的列上添加限制条件时才能体现出来。

EXPLAIN SELECT artist_id,name,founded
 FROM artist
 WHERE name LIKE 'Queen%';
 mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name          | name | 257     | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

在这个示例中，Extra后面没有出现Using Index，所以在索引中记录全名并没有带来额外的益处。
而所提供的局部列索引满足了WHERE 条件。如何选择合适的长度取决于数据的分布以及访问路径。目前没有准确的方法计算索
引的恰当长度。因此对给定范围的列长度内的唯一值数目的比较
是必不可少的。

count了下SELECT count(*) FROM artist WHERE name LIKE 'Queen%'; 才93条记录，而SELECT count(*) FROM artist;有577983条记录，按照普遍的情况，可以走索引，难道是name(20)的20定义的太长了？

ALTER TABLE artist
 DROP INDEX name_part,
  ADD INDEX name_part2(name(10));

  mysql> ALTER TABLE artist
    ->  DROP INDEX name_part,
    ->   ADD INDEX name_part2(name(10));
Query OK, 0 rows affected (3.41 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name_part2    | name_part2 | 12      | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看结果，再用name(5) 试试看。
mysql> ALTER TABLE artist
    ->  DROP INDEX name_part2,
    ->   ADD INDEX name_part3(name(5));
Query OK, 0 rows affected (3.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT artist_id,name,founded
    ->  FROM artist
    ->  WHERE name LIKE 'Queen%';
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows | Extra       |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | artist | range | name_part3    | name_part3 | 7       | NULL |   93 | Using where |
+----+-------------+--------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)

看来局部索引对like的效果不是很明显的，可能跟数据分布范围有关，也许这93条数据全部打散在各个数据库块中，
所以导致解析器认为不能简单地通过数次index就能遍历出数据，故而Extra栏里面就没有出现Using Index的提示。

 
总结：在索引中正确的定义列(包括定义列的顺序和位置)能够改变索引的实际使用效果。好的索引能够为一个执行缓慢的查询带来
巨大的性能提升。索引也可能使原来执行很快的查询的执行时间减少若干毫秒。在高并发系统中，将1 000 000 条查询减少几毫秒
将会显著改善性能，并且获得更大的容量和扩展性。为SQL 查询创建最优索引可以认为是一项艺术。

 

 以上就是Effective MySQL之SQL语句最优化--索引 的内容，更多相关内容请关注PHP中文网（www.php.cn）！