Disons que j'ai trois tableaux: expéditions , clients et magasins . La table expéditions comporte deux index: customer_id de type INT (fait référence à la table clients) et date de type datetime. La table customers a un index: store_id de type INT (fait référence à la table store).

Si je filtre les envois par date, je vois l’index date utilisé:

EXPLAIN extended SELECT * FROM shipments
WHERE date >= '2020-04-01' AND date <= '2020-05-01';

+----+-------------+-----------+-------+---------------+------+---------+-------+--------+----------+-------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+-----------+-------+---------------+------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | shipments | range | date          | date | 9       | NULL  | 250796 |   100.00 | Using where |
+----+-------------+-----------+-------+---------------+------+---------+-------+--------+------------------------+

Cependant, la sortie de ces deux prochaines requêtes me confond, car c'est à peu près la même chose:

EXPLAIN extended SELECT shipments.* FROM shipments
LEFT JOIN customers ON shipments.customer_id = customers.id
WHERE customers.store_id = 100 AND 
shipments.date >= '2020-04-01 00:0:00.0' AND shipments.date <= '2020-05-01 00:0:00.0';

+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys     | key         | key_len | ref           | rows   | filtered | Extra                    |
+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+----------+--------------------------+
|  1 | SIMPLE      | customers | ref   | PRIMARY, store_id | store_id    | 5       | const         | 38     |   100.00 | Using where; Using index |
+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+----------+--------------------------+
|  1 | SIMPLE      | shipments | ref   | customer_id, date | customer_id | 5       | customers.id  | 663    |   100.00 | Using where              |
+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+-------------------------------------+

EXPLAIN extended SELECT shipments.* FROM shipments
LEFT JOIN customers ON shipments.customer_id = customers.id
WHERE customers.store_id = 100;

+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+----------+--------------------------+
| id | select_type | table     | type  | possible_keys     | key         | key_len | ref           | rows   | filtered | Extra                    |
+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+----------+--------------------------+
|  1 | SIMPLE      | customers | ref   | PRIMARY, store_id | store_id    | 5       | const         | 38     |   100.00 | Using where; Using index |
+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+----------+--------------------------+
|  1 | SIMPLE      | shipments | ref   | customer_id       | customer_id | 5       | customers.id  | 663    |   100.00 | Using where              |
+----+-------------+-----------+-------+-------------------+-------------+---------+---------------+--------+-------------------------------------+

Question nº1 : ce résultat signifie-t-il que la première de ces deux requêtes n'utilise pas du tout l'index date ? J'ai lu que MySQL n'utilisera pas plus d'un index par table, mon index date fait-il donc une différence en termes de performances? (dans mon programme, toutes les requêtes qui filtrent par plage de dates ressemblent à peu près à celle-ci) En supposant qu'il y ait des tonnes de clients et des tonnes d'expéditions et des tonnes de requêtes comme celle-ci lancées en même temps, comment dois-je procéder pour améliorer les performances ?

Question nº2 : Pourquoi la valeur des "lignes" dans la sortie est-elle la même pour ces deux requêtes, si la première implique plus de filtrage que la première? Cela ne devrait-il pas être différent? De toute évidence, je ne comprends pas bien cela, alors quelqu'un peut-il me l'expliquer?

Merci d'avance!

Remarque: il s'agit de mysql 5.5.56 et les tables sont InnoDB.

0
javier 27 avril 2020 à 02:14

2 réponses

Meilleure réponse

Ce n'est pas vraiment une jointure LEFT puisque vous avez besoin de store_id = 100. Cela ne changera pas la performance; l'Optimizer l'a déjà compris. (Cela aide les lecteurs à essayer de comprendre l'intention de la requête.)

Vous dites SELECT *. Si vous n'avez pas besoin de toutes les colonnes, ne les demandez pas toutes. Si l'une d'entre elles est une grosse colonne TEXT, le texte se trouve dans un bloc "hors enregistrement", ce qui demande des efforts pour être récupéré.

INDEX(customer_id), INDEX(date) n'est pas aussi bon que le "composite" INDEX(customer_id, date) Avec cela, il peut se concentrer sur les entrées de ce client et parcourir les dates souhaitées. Cela améliorera probablement la vitesse. Remarque: l'ordre des colonnes dans cet index est important - placez la (les) colonne (s) = (customer_id) en premier, la plage (date >=...) en dernier.

(Q1) MySQL n'utilise pas (à de rares exceptions près) plus d'un index à la fois. Vous filtrez shipments sur deux choses: customer_id et date, pas seulement date. Cette requête, en revanche, utiliserait INDEX(date), et n'utiliserait pas l'index composite ci-dessus: SELECT * FROM shipments where date >= CURDATE(); (qui obtient toutes les informations sur toutes les expéditions jusqu'à présent aujourd'hui pour tous les clients.

Remarque: vous incluez minuit aux deux extrémités. Modifiez la dernière comparaison de <= à <.

(Q2) Les nombres dans EXPLAIN sont des estimations. Ils reposent sur des «statistiques» et des «sondes» qui ne sont pas forcément très précises. En outre, certains conseils sont ignorés dans certains cas. Une omission flagrante est LIMIT.

Méfiez-vous en utilisant USE INDEX et FORCE INDEX. Si vous en ressentez le besoin, vous manquez peut-être quelque chose d'important. Si vous l'utilisez, «cela peut aider aujourd'hui, mais aggraver les choses demain lorsque la distribution des données changera.

Un conseil: pour les comparaisons avec un DATE / DATETIME / DATETIME (1) / TIMESTAMP, une heure de minuit peut laisser la partie "heure" désactivée: '2020-05-01' fonctionne de la même manière que '2020-05-01 00:0:00.0'

Version 5.5? C'est assez vieux. 5.6 ajoute EXPLAIN FORMAT=JSON, qui fournirait plus d'informations - des détails sur l'utilisation de l'index, le tri, le coût de la requête, etc.

"Ce truc d'optimisation est encore assez obscur pour moi." -- Oui. Et MySQL possède l'un des optimiseurs les plus simples.

1
Rick James 28 avril 2020 à 16:14

1) Oui, il filtre par customers.store_id, puis effectue une jointure en arrière à la table des expéditions en fonction de customer_id.

Vous pourrez peut-être améliorer cela en remplaçant l'index sur les expéditions (customer_id) par les expéditions (customer_id, date), à ​​moins que cet index couvre déjà les deux champs.

2) Parce qu'il s'agit d'une estimation basée sur les statistiques de l'indice, principalement la cardinalité de chaque indice.

2
Gordan Bobic 26 avril 2020 à 23:49