MariaDB / MySQL UPDATE-Anweisung mit mehreren Joins, einschließlich eines Fernkampf-Joins
Ich habe für Tische
Ein Login-Verlauf
create table login_history
(
id int auto_increment primary key,
ip int unsigned,
created datetime(6) not null,
uid int unsigned not null,
);
Eine IP-zu-Standort-Tabelle
create table ip2location
(
ip_from int unsigned not null primary key,
ip_to int unsigned null,
country_code char(2) null,
)
Eine Kontotabelle
create table account
(
uid int unsigned not null primary key,
);
Einige Bestellungen
create table order
(
id int auto_increment primary key,
item_id varchar(20) not null,
price int not null,
timestamp datetime(6) not null,
country_code char(2) null,
uid int unsigned null
)
Alle Tabellen haben passende Indizes für dieses Problem.
Mein Ziel ist es, den Ländercode der Bestellungen mit dem Land aus der ip2location-Tabelle zu füllen. Ich habe einen Anmeldeverlauf und da ich das Problem nicht nach Bedarf komplizieren möchte, kann ich die neueste IP verwenden, die ein Benutzer im angegebenen Zeitraum hatte. Ich gehe davon aus, dass ein Landwechsel und der Kauf eines Produkts innerhalb des Zeitbereichs ein vernachlässigbarer Anwendungsfall ist. Da der Anmeldeverlauf nur einige Tage aufbewahrt wird, möchte ich alte Bestellungen ausführen, bei denen der country_code auf null gesetzt wurde, um auch das Land für den Benutzer abzurufen.
Mein Ansatz ist der folgende.
Ich versuche, beide Tabellen mit dem folgenden "on" -Ausdruck zu verbinden.
update order
left join account using(uid)
left join (
select uid,
LAST_VALUE(ip) over (PARTITION BY uid) as `ip_int`
from login_history
where created >= '{{ current_date }}'
and created < '{{ next_date }}'
group by user_id
) as lh
on account.uid = lh.uid
left join ip2location as ip on
(ip.ip_from < login_history.ip_int and ip.ip_to >= login_history.ip_int)
or
(ip.ip_from = lh.ip_int)
set
order.country_id = ip.country_id
where order.country_id is null;
Es funktioniert, ist aber sehr langsam. Wahrscheinlich auch wegen der Größe der Tabellen:
- login_history> 15 Mio. Einträge (wobei die Anweisung dies auf 500.000 Einträge reduziert)
- Konto> 7 Mio. Einträge
- ip2location ~ 200K Einträge
- Bestellungen> 1 Mio.
Vielleicht ist dies ein Anwendungsfall, in dem MariaDB keine Lösung anbieten kann. Ziel ist es, diese Abfrage in weniger als 30 Sekunden abzuschließen. Aus Gründen, den Tisch nicht zu lange zu sperren, wäre schneller natürlich besser.
Ich sehe ein gewisses Potenzial in der folgenden Aussage. Um den richtigen Eintrag in der Tabelle ip2location zu finden, muss ich einen Bereich verwenden und auch berücksichtigen, dass ein Eintrag vorhanden ist, in dem nur eine IP angegeben ist und das Feld ip_to null ist.
left join ip2location as ip on
(ip.ip_from <= login_history.ip_int and ip.ip_to >= login_history.ip_int)
or
(ip.ip_from = lh.ip_int)
Außerdem sieht die folgende Auswahl etwas zeitintensiv aus:
select uid,
LAST_VALUE(ip) over (PARTITION BY uid) as `ip_int`
from login_history
where created >= '{{ current_date }}'
and created < '{{ next_date }}'
group by user_id
Ich dachte darüber nach, dies aufzuteilen, indem ich zuerst eine select- und dann eine update-Anweisung verwende, aber am Ende könnte dies mehr Zeit kosten und aufgrund des Skripts, das diese Aufgabe organisiert, auch mehr CPU-Zeit verbrauchen.
Können Sie mir helfen, eine bessere Frage zu finden, oder haben Sie gute Ratschläge, wie Sie dieses Problem effizient angehen können?
Vielen Dank im Voraus und einen schönen Tag!
Antworten
Ich denke, der folgende Ansatz, der auf einer korrelierten Unterabfrage basiert, macht das, wonach Sie fragen:
update orders o
set country = (
select il.country_code
from login_history lh
inner join ip2location il on lh.ip >= il.ip_from and lh.ip_to < il.ip_to
where lh.created <= o.timestamp and lh.uid = o.uid
order by lh.created desc limit 1
)
where o.country_id is null
Dadurch wird nach der neuesten Anmeldegeschichte für denselben Benutzer gesucht, dessen Datum vor oder gleich dem Bestellzeitstempel liegt, und das entsprechende Land wird wiederhergestellt .