Warum MySQL langsamer wird, ohne einen Index anzugeben
P粉7878060242023-09-08 00:38:24
0
1
562
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:
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:
id
Typ auswählen
Tisch
Partition
Typ
Mögliche Schlüssel
Schlüssel
key_len
Referenz
OK
Gefiltert
Extra
1
Einfach
b
Umfang
IDX_E00CEDDEB75363F7,IDX_E00CEDDE37D3107C
IDX_E00CEDDEB75363F7
6
1147319
50
Indexbedingungen 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
根据您的使用案例，您可能需要在 start_at 上创建一个索引，在 end_at 上创建另一个索引。之后，一个简单的查询 SELECT * from booking b where b.start_at '2021-01-01'; 将立即生效；根据搜索条件，MySQL 可能会通过 MERGE INDEX 优化操作使用一个索引或同时使用这两个索引。
您的索引
(id, start_at)未被选取，因为没有固定的id可供搜索。根据您的使用案例，您可能需要在
start_at上创建一个索引，在end_at上创建另一个索引。之后，一个简单的查询SELECT * from booking b where b.start_at '2021-01-01';将立即生效；根据搜索条件，MySQL 可能会通过 MERGE INDEX 优化操作使用一个索引或同时使用这两个索引。如果您希望使用单个索引，则需要仔细选择字段的顺序，因为索引的使用顺序与其定义的顺序相同。
编辑：OPs编辑后，这是我的想法。
这很好地解释了事情。假设你是
SELECT *，MySQL将被迫读取整个表。尝试仅选择id，在这种情况下它很可能会使用索引；因为它将是一个索引覆盖的查询。