Instruction MariaDB / MySQL UPDATE avec plusieurs jointures, y compris une jointure à distance
J'ai pour les tables
Un historique de connexion
create table login_history
(
id int auto_increment primary key,
ip int unsigned,
created datetime(6) not null,
uid int unsigned not null,
);
Une table de localisation IP
create table ip2location
(
ip_from int unsigned not null primary key,
ip_to int unsigned null,
country_code char(2) null,
)
Une table de compte
create table account
(
uid int unsigned not null primary key,
);
Des commandes
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
)
Toutes les tables ont des indices d'ajustement pour ce problème.
Mon objectif est de renseigner le code pays des commandes avec le pays de la table ip2location. J'ai un historique de connexion et parce que je veux que le problème ne soit pas plus compliqué que nécessaire, je suis d'accord pour utiliser l'adresse IP la plus récente d'un utilisateur dans la plage de temps donnée. Je suppose que changer de pays et acheter quelque chose dans le laps de temps est un cas d'utilisation négligeable. De plus, comme l'historique de connexion n'est conservé que pendant quelques jours, je souhaite traiter les anciennes commandes, qui ont défini le country_code sur null, également pour obtenir le pays de l'utilisateur.
Mon approche est la suivante.
J'essaye de joindre les deux tables avec l'expression "on" suivante.
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;
Cela fonctionne mais est très lent. Probablement aussi à cause de la taille des tables:
- login_history> 15 Mio. entrées (où l'instruction réduit cela à 500K entrées)
- compte> 7 Mio. entrées
- ip2location ~ 200K entrées
- commandes> 1 Mio.
C'est peut-être un cas d'utilisation où MariaDB ne peut pas fournir de solution. L'objectif est de terminer cette requête en moins de 30 secondes. Pour des raisons de ne pas verrouiller la table trop longtemps, plus vite serait bien sûr mieux.
Je vois un certain potentiel dans la déclaration suivante. Pour trouver la bonne entrée dans la table ip2location, je dois utiliser une plage et je dois également considérer qu'une entrée existe, où une seule adresse IP est donnée et le champ ip_to est nul.
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)
En outre, la sélection suivante semble quelque peu intense dans le temps:
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
J'ai pensé à diviser cela en utilisant d'abord une instruction select puis une instruction de mise à jour, mais à la fin, cela pourrait coûter plus de temps et utiliser plus de temps CPU, à cause du script, qui organise cette tâche.
Pouvez-vous m'aider à trouver une meilleure requête ou avez-vous de bons conseils pour résoudre ce problème efficacement?
Merci d'avance et bonne journée!
Réponses
Je pense que l'approche suivante, basée sur une sous-requête corrélée, fait ce que vous demandez:
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
Cela recherche le dernier login_history pour le même utilisateur dont la date est antérieure ou égale à l'horodatage de la commande et récupère le pays correspondant.