Instrução MariaDB / MySQL UPDATE com várias junções, incluindo uma junção variada
Tenho para mesas
Um histórico de login
create table login_history
(
id int auto_increment primary key,
ip int unsigned,
created datetime(6) not null,
uid int unsigned not null,
);
Uma tabela de IP para localização
create table ip2location
(
ip_from int unsigned not null primary key,
ip_to int unsigned null,
country_code char(2) null,
)
Uma tabela de contas
create table account
(
uid int unsigned not null primary key,
);
Alguns pedidos
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
)
Todas as tabelas possuem índices adequados para este problema.
Meu objetivo é preencher o código do país dos pedidos com o país da tabela ip2location. Eu tenho um histórico de login e porque não quero tornar o problema mais complicado conforme necessário, estou bem em usar o IP mais recente que um usuário tinha no intervalo de tempo determinado. Presumo que trocar de país e comprar algo dentro do intervalo de tempo seja um caso de uso insignificante. Além disso, como o histórico de login é retido por apenas alguns dias, quero atender aos pedidos antigos, que definiram country_code como null, também para obter o país do usuário.
Minha abordagem é a seguinte.
Estou tentando juntar as duas tabelas com a seguinte expressão "on".
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;
Funciona, mas é muito lento. Provavelmente também pelo tamanho das mesas:
- login_history> 15 Mio. entradas (onde a declaração reduz para 500 mil entradas)
- conta> 7 milhões. entradas
- ip2location ~ 200K entradas
- pedidos> 1 Mio.
Talvez seja um caso de uso em que MariaDB não pode fornecer uma solução. O objetivo é terminar esta consulta em menos de 30 segundos. Por razões de não travar a mesa por muito tempo, mais rápido seria melhor, é claro.
Eu vejo algum potencial na declaração a seguir. Para encontrar a entrada certa na tabela ip2location, preciso usar um intervalo e também considerar que existe uma entrada, onde apenas um IP é fornecido e o campo ip_to é nulo.
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)
Além disso, a seleção a seguir parece um tanto demorada:
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
Pensei em dividir isso usando primeiro um select e depois uma instrução update, mas no final, isso poderia custar mais tempo e também consumiria mais tempo de CPU, por causa do script, que organiza essa tarefa.
Você pode me ajudar a encontrar uma consulta melhor ou tem alguns bons conselhos sobre como lidar com esse problema com eficiência?
Agradecemos antecipadamente e tenha um bom dia!
Respostas
Acho que a seguinte abordagem, com base em uma subconsulta correlacionada, faz o que você pede:
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
Isso procura o último login_history para o mesmo usuário cuja data é anterior ou igual ao carimbo de data / hora do pedido e recupera o país correspondente.