Warum MySQL langsamer wird, ohne einen Index anzugeben

Ich versuche, eine SQL-Abfrage zu optimieren, würde aber gerne wissen, wie man es richtig macht.

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';

Hier erzwinge ich die Verwendung eines Index (booking_id, from_spot_id, to_spot_id), der dazu führt, dass die Abfrage etwa 100 ms benötigt, um innerhalb von 25 Sekunden nach dem nächsten Datum ausgeführt zu werden!

booking 表大约有 200 万行，而 ride Die Tabelle hat etwa 5 Millionen Zeilen.

Ich kann mir jedoch vorstellen, dass mehr Zeilen mithilfe der erzwungenen Indizierung gescannt werden:

id	Typ auswählen	Tisch	Typ	Mögliche Schlüssel	Schlüssel	key_len	Referenz	OK	Gefiltert	Extra
1	Einfach	b1_	Umfang	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	Indexbedingungen verwenden; Standorte verwenden
1	Einfach	r0_	Referenz	ride_booking_id_IDX	ride_booking_id_IDX	109	ector.b1_.id	1	100,0
1	Einfach	s2_	eq_ref	Haupt, IDX_B9327A739F2C3FAB, spot_type_IDX	Haupt	4	ector.r0_.from_spot_id	1	72,52	Verwendungsort
1	Einfach	s3_	eq_ref	Haupt	Haupt	4	ector.r0_.to_spot_id	1	100,0	Verwendungsort

Im Vergleich zur gleichen Abfrage ohne Index:

id	Typ auswählen	Tisch	Typ	Mögliche Schlüssel	Schlüssel	key_len	Referenz	OK	Gefiltert	Extra
1	Einfach	s2_	Referenz	Haupt, IDX_B9327A739F2C3FAB, spot_type_IDX	spot_type_IDX	767	Konstant	161	100,0	Indexbedingungen verwenden
1	Einfach	r0_	Referenz	IDX_9B3D7CD0ABAF30D3, IDX_9B3D7CD03301C60, ride_booking_id_IDX, booking_from_spot_to_spot_IDX	IDX_9B3D7CD0ABAF30D3	5	ector.s2_.id	392	100,0
1	Einfach	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	Haupt	108	ector.r0_.booking_id	1	5.0	Verwendungsort
1	Einfach	s3_	eq_ref	Haupt	Haupt	4	ector.r0_.to_spot_id	1	100,0	Verwendungsort

Soweit ich das beurteilen kann, ist das Datum, mit dem ich vergleiche, der Grund dafür, dass die Abfrage spürbar schneller ist. start_at 和 end_at

Also versuche ich, die langsamsten Teile auf kleinere Abfragen zu isolieren:

从预订 b 中选择 *，其中 b.start_at < '2021-01-01' 和 b.end_at > '2021-01-01';

Bei der Tabellenbuchung habe ich zwei Indizes

Sie sorgen dafür, dass diese Abfrage schneller ausgeführt wird, je näher Sie dem Maximal- und Minimalwert kommen (da der Index die meisten Zeilen filtert, sind nur noch sehr wenige Zeilen übrig). (start_at) 和 (end_at)

Wenn ich jedoch einen Zufallswert weit genug in die Vergangenheit nehme, wird er viel langsamer. Die Ausführung der obigen Abfrage dauert 10 Sekunden, da sie wie erwartet nur einen der beiden Indizes verwendet. Ich weiß nicht, warum die Erklärung für merge_index bei einer so einfachen Abfrage nicht angezeigt wird:

idTyp auswählenTischPartitionTypMögliche SchlüsselSchlüsselkey_lenReferenzOKGefiltertExtra1EinfachbUmfangIDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107CIDX_E00CEDDEB75363F76114731950Indexbedingungen verwenden; Standorte verwenden

Da ich keinen Index haben kann, der beide Bereichsbedingungen erfüllt, habe ich versucht, die Abfrage in zwei Hälften zu teilen

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';

Diese Abfrage wird deutlich schneller ausgeführt und dauert etwa 600 Millisekunden. Aufgrund der Einfachheit der Abfrage und der Tatsache, dass sie etwa 7.000 Zeilen zurückgibt, würde ich jedoch höchstens zweistellige Werte erwarten.

Ich verstehe nicht, warum die Abfrage meinen Index nicht automatisch auswählt (id, start_at) 和 (id, end_at)? Was vermisse ich?

Ich weiß, dass ich die Tabelle partitionieren kann, um bessere Ergebnisse zu erzielen, aber ich habe Fremdschlüssel, die nicht gelöscht werden können, also ist das keine Lösung. Sollte ich ein anderes Schema in Betracht ziehen und eine Tabelle haben, die die Reservierungsdaten separat ohne Fremdschlüssel enthält, und die Reservierungstabelle darauf verweisen lassen, damit ich die Reservierungstabelle partitionieren kann? Ist es möglich, Fremdschlüssel zu verwenden, um zum Zeitpunkt des Abonnements auf eine partitionierte Abonnementtabelle zu verweisen?

Die MySQL-Engine läuft in AWS mit der folgenden Version: 8.0.mysql_aurora.3.02.2

Die Ausgabe von

SELECT @@optimizer_switch ist:

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

P粉787806024408 Tage vor541

Antworte allen(1)Ich werde antworten

P粉0186537512023-09-08 23:04:14

您的索引 (id, start_at) 未被选取，因为没有固定的 id 可供搜索。

根据您的使用案例，您可能需要在 start_at 上创建一个索引，在 end_at 上创建另一个索引。之后，一个简单的查询 SELECT * from booking b where b.start_at < '2021-01-01' and b.end_at > '2021-01-01'; 将立即生效；根据搜索条件，MySQL 可能会通过 MERGE INDEX 优化操作使用一个索引或同时使用这两个索引。

如果您希望使用单个索引，则需要仔细选择字段的顺序，因为索引的使用顺序与其定义的顺序相同。

编辑：OPs编辑后，这是我的想法。

这很好地解释了事情。假设你是SELECT *，MySQL将被迫读取整个表。尝试仅选择id，在这种情况下它很可能会使用索引；因为它将是一个索引覆盖的查询。

Antwort

StornierenAntwort