Pourquoi MySQL ralentit sans spécifier d'index

Question

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 de

SELECT @@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=开

Answer 1

Votre index (id, start_at) 未被选取，因为没有固定的 id est consultable.

En fonction de votre cas d'utilisation, vous devrez peut-être start_at 上创建一个索引，在 end_at 上创建另一个索引。之后，一个简单的查询 SELECT * from booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01'; prendre effet immédiatement en fonction des critères de recherche, MySQL peut utiliser un index ou les deux via l'opération d'optimisation MERGE INDEX.

Si vous souhaitez utiliser un seul index, vous devez choisir soigneusement l'ordre des champs, car les index sont utilisés dans le même ordre dans lequel ils sont définis.

EDIT : Après la modification des OP, voici mes réflexions.

Cela explique très bien les choses. En supposant que vous l'êtes SELECT *，MySQL将被迫读取整个表。尝试仅选择id, auquel cas il utilisera très probablement un index puisqu'il s'agira d'une requête couverte par un index ;