Declaración UPDATE de MariaDB / MySQL con múltiples combinaciones, incluida una combinación a distancia
Tengo para mesas
Un historial de inicio de sesión
create table login_history
(
id int auto_increment primary key,
ip int unsigned,
created datetime(6) not null,
uid int unsigned not null,
);
Una tabla de IP a ubicación
create table ip2location
(
ip_from int unsigned not null primary key,
ip_to int unsigned null,
country_code char(2) null,
)
Una tabla de cuentas
create table account
(
uid int unsigned not null primary key,
);
Algunas órdenes
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 las tablas tienen índices adecuados para este problema.
Mi objetivo es completar el código de país de los pedidos con el país de la tabla ip2location. Tengo un historial de inicio de sesión y, como quiero que el problema no sea más complicado según sea necesario, estoy bien con el uso de la IP más reciente que tuvo un usuario en el rango de tiempo dado. Supongo que cambiar de país y comprar algo dentro del rango de tiempo es un caso de uso insignificante. Además, debido a que el historial de inicio de sesión solo se mantiene durante un par de días, quiero completar los pedidos anteriores, que han establecido el country_code en nulo, también para obtener el país del usuario.
Mi enfoque es el siguiente.
Estoy tratando de unir ambas tablas con la siguiente expresión "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 pero es muy lento. Probablemente también por el tamaño de las tablas:
- login_history> 15 Mio. entradas (donde la declaración reduce esto a 500K entradas)
- cuenta> 7 Mio. entradas
- ip2location ~ 200K entradas
- pedidos> 1 Mio.
Quizás ese sea un caso de uso en el que MariaDB no puede proporcionar una solución. El objetivo es finalizar esta consulta en menos de 30 segundos. Por razones de no bloquear la mesa por mucho tiempo, más rápido sería mejor, por supuesto.
Veo cierto potencial en la siguiente declaración. Para encontrar la entrada correcta en la tabla ip2location, tengo que usar un rango y también tengo que considerar que existe una entrada, donde solo se proporciona una IP y el campo ip_to es 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)
Además, la siguiente selección parece algo intensa en el tiempo:
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
Pensé en dividir esto usando primero una instrucción de selección y luego una de actualización, pero al final, esto podría costar más tiempo y también usaría más tiempo de CPU, debido al script, que organiza esta tarea.
¿Puede ayudarme a encontrar una consulta mejor o tiene algún buen consejo sobre cómo abordar este problema de manera eficiente?
Gracias de antemano y que tengas un buen día!
Respuestas
Creo que el siguiente enfoque, basado en una subconsulta correlacionada, hace lo que pide:
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
Esto busca el historial de inicio de sesión más reciente para el mismo usuario cuya fecha es anterior o igual a la marca de tiempo del pedido y recupera el país correspondiente.