Pourquoi MySQL ralentit sans spécifier d'index
J'essaie d'optimiser une requête SQL, mais j'aimerais savoir comment le faire correctement.
SELECT
r0_.*
FROM ride r0_ use index (ride_booking_id_IDX)
LEFT JOIN booking b1_ ON r0_.booking_id = b1_.id
LEFT JOIN spot s2_ ON r0_.from_spot_id = s2_.id
LEFT JOIN spot s3_ ON r0_.to_spot_id = s3_.id
WHERE b1_.start_at <= '2023-04-21'
AND b1_.end_at >= '2023-04-20'
AND b1_.paid_at IS NOT NULL
AND b1_.cancelled_at IS NULL
AND ((s2_.zone_id = 1 OR s3_.zone_id = 1))
AND s2_.type = 'parking';
Ici, je force l'utilisation d'un index (booking_id, from_spot_id, to_spot_id) ce qui fait que la requête met environ 100 ms à s'exécuter dans les 25 secondes suivant la date la plus proche !
booking 表大约有 200 万行，而 ride Le tableau compte environ 5 millions de lignes.
Cependant, je peux le voir analyser plus de lignes en utilisant l'indexation forcée :
| id | Sélectionnez le type | Table | Partition | Type | Clés possibles | Clé | key_len | Référence | OK | Filtré | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Simple | b1_ | Portée | Main, booking_id_end_IDX, booking_id_IDX, booking_id_start_IDX, IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C, IDX_E00CEDDEDEA4208C, booking_paid_at_IDX, booking_cancelled_at_IDX | IDX_E00CEDDE37D3107C | 6 | 111456 | 6.6 | Utiliser les conditions d'indexation ; utiliser les emplacements | ||
| 1 | Simple | r0_ | Référence | ride_booking_id_IDX | ride_booking_id_IDX | 109 | ector.b1_.id | 1 | 100,0 | ||
| 1 | Simple | s2_ | eq_ref | Principal, IDX_B9327A739F2C3FAB, spot_type_IDX | Principal | 4 | ector.r0_.from_spot_id | 1 | 72.52 | Lieu d'utilisation | |
| 1 | Simple | s3_ | eq_ref | Principal | Principal | 4 | ector.r0_.to_spot_id | 1 | 100,0 | Lieu d'utilisation |
Par rapport à la même requête sans index :
| id | Sélectionnez le type | Table | Partition | Type | Clés possibles | Clé | key_len | Référence | OK | Filtré | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Simple | s2_ | Référence | Principal, IDX_B9327A739F2C3FAB, spot_type_IDX | spot_type_IDX | 767 | Constante | 161 | 100,0 | Utiliser les conditions d'indexation | |
| 1 | Simple | r0_ | Référence | IDX_9B3D7CD0ABAF30D3, IDX_9B3D7CD03301C60, ride_booking_id_IDX, booking_from_spot_to_spot_IDX | IDX_9B3D7CD0ABAF30D3 | 5 | ector.s2_.id | 392 | 100,0 | ||
| 1 | Simple | b1_ | eq_ref | Main, booking_id_end_IDX, booking_id_IDX, booking_id_start_IDX, IDX_E00CEDDEB75363F7, IDX_E00CEDDE37D3107C, IDX_E00CEDDEDEA4208C, booking_paid_at_IDX, booking_cancelled_at_IDX | Principal | 108 | ector.r0_.booking_id | 1 | 5.0 | Lieu d'utilisation | |
| 1 | Simple | s3_ | eq_ref | Principal | Principal | 4 | ector.r0_.to_spot_id | 1 | 100,0 | Lieu d'utilisation |
Pour autant que je sache, la date que j'utilise pour comparer avec start_at 和 end_at est la raison pour laquelle la requête est sensiblement plus rapide.
J'essaie donc d'isoler les parties les plus lentes vers des requêtes plus petites :
从预订 b 中选择 *，其中 b.start_at < '2021-01-01' 和 b.end_at > '2021-01-01';
Sur la réservation de table, j'ai deux index(start_at) 和 (end_at) ils aident cette requête à s'exécuter plus rapidement à mesure que vous vous rapprochez du maximum et du minimum (puisque l'index filtrera la plupart des lignes, il restera très peu de lignes à analyser).
Cependant, lorsque je prends une valeur aléatoire suffisamment loin dans le passé, cela devient beaucoup plus lent. La requête ci-dessus prend 10 secondes à s'exécuter car elle n'utilise qu'un des deux index comme prévu, je ne sais pas pourquoi l'explication n'apparaît pas pour merge_index sur une requête aussi simple :
| id | Sélectionnez le type | Table | Partition | Type | Clés possibles | Clé | key_len | Référence | OK | Filtré | Extra |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Simple | b | Portée | IDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107C | IDX_E00CEDDEB75363F7 | 6 | 1147319 | 50 | Utiliser les conditions d'indexation ; utiliser les emplacements |
Comme je ne peux pas avoir d'index qui satisfasse aux deux conditions de plage, j'ai essayé de diviser la requête en deux
SELECT * from booking b INNER JOIN booking b2 use index(booking_id_start_IDX) ON b.id = b2.id and b2.start_at < '2021-01-01' INNER JOIN booking b3 use index(booking_id_end_IDX) ON b.id = b3.id and b3.end_at > '2021-01-01';
Cette requête s'exécute beaucoup plus rapidement, prenant environ 600 millisecondes. Cependant, en raison de la simplicité de la requête et du fait qu'elle renvoie environ 7 000 lignes, je m'attendrais à ce qu'elle soit au maximum à deux chiffres.
Je ne comprends pas pourquoi la requête ne sélectionne pas automatiquement mon index (id, start_at) 和 (id, end_at) ? Qu'est-ce que je rate?
Je sais que je peux partitionner la table pour obtenir de meilleurs résultats, mais j'ai des clés étrangères qui ne peuvent pas être supprimées, ce n'est donc pas une solution. Dois-je envisager un autre schéma et disposer d'une table contenant les dates de réservation séparément sans aucune clé étrangère et faire référence à la table de réservation afin que je puisse partitionner la table de réservation ? Est-il possible d'utiliser des clés étrangères pour référencer une table d'abonnement partitionnée au moment de l'abonnement ?
Le moteur MySQL fonctionne sur AWS avec la version suivante : 8.0.mysql_aurora.3.02.2
Le résultat deSELECT @@optimizer_switch est :
index_merge=on、index_merge_union=on、index_merge_sort_union=on、index_merge_intersection=on、engine_condition_pushdown=on、index_condition_pushdown=on、mrr=on、mrr_cost_based=on、block_nested_loop=on、batched_key_access=off、物化=on，半连接=on、loosescan=on、firstmatch=on、duplicateweedout=on、subquery_materialization_cost_based=on、use_index_extensions=on、condition_fanout_filter=on、provided_merge=on、use_invisible_indexes=off、skip_scan=on、hash_join=on、subquery_to_衍生=off、prefer_ordering_index =开，hypergraph_optimizer=关，衍生_条件_pushdown=开
