具有多个联接(包括远程联接)的MariaDB/MySQL UPDATE语句 [英] MariaDB/MySQL UPDATE statement with multiple joins including a ranged join

问题描述

我有桌子

登录历史记录

create table login_history
(
    id         int auto_increment primary key,
    ip         int unsigned,
    created    datetime(6)  not null,
    uid    int unsigned not null,
);

IP到位置表

create table ip2location
(
    ip_from      int unsigned not null primary key,
    ip_to        int unsigned null,
    country_code char(2)      null,
)

帐户表

create table account
(
    uid               int unsigned not null primary key,
);

部分订单

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
)

所有表都有适合该问题的索引.

All tables have fitting indices for this problem.

我的目标是使用ip2location表中的国家/地区填写订单的国家/地区代码.我有一个登录历史记录，并且因为我想使问题不再变得不必要的复杂，所以我可以使用用户在给定时间范围内拥有的最新IP.我认为在一个时间范围内切换国家并购买商品是可以忽略的用例.另外，由于登录历史记录仅保留了几天，因此我想填写将country_code设置为null的旧订单，也可以为用户获取国家/地区.

My goal is to fill the country code of the orders with the country from the ip2location table. I have a login history and because I want to make the problem not more complicated as necessary, I am fine with using the most recent IP, a user had in the given time range. I assume that switching a country and buying something within the time range, is a negligible use case. Also, because the login history is only held for a couple of days, I want to fill old orders, which have set the country_code to null, also to get the country for the user.

我的方法如下.

我正在尝试将两个表与以下"on"连接起来.表达.

I am trying to join both tables with the following "on" expression.

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;

它可以工作，但是非常慢.可能还因为表格的大小:

It works but is very slow. Probably also because of the size of the tables:

• login_history>15宇达电通条目(where语句将其减少到50万个条目)
• 帐户>7宇达电通条目
• ip2location〜200K条目
• 订单>1 Mio.

也许这是MariaDB无法提供解决方案的用例.目标是在30秒内完成此查询.出于不长时间锁定表的原因，当然更快会更好.

Maybe that's a use case where MariaDB can't provide a solution. The target is, to finish this query in under 30 seconds. For reasons of not locking the table for too long, faster would be better of course.

我在以下陈述中看到了一些潜力.为了在ip2location表中找到正确的条目，我必须使用一个范围，并且还必须考虑存在一个条目，其中仅给出一个IP，并且ip_to字段为空.

I see some potential in the following statement. For finding the right entry in the ip2location table, I have to use a range and I have to also consider an entry exists, where only one IP is given, and the ip_to field is null.

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)

此外，以下选择看起来有些耗时:

Also, the following select looks somewhat time intense:

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

我考虑过先使用select然后再使用update语句来进行拆分，但是最后，由于脚本组织了此任务，因此这可能会花费更多时间，并且还会占用更多CPU时间.

I thought about splitting this up by first use a select and then an update statement, but in the end, this could cost more time and also would use more CPU time, because of the script, which organizes this task.

您能帮我找到一个更好的查询吗?或者您对如何有效解决此问题有一些好的建议?

Can you help me find a better query or do you have some good advice on how to tackle this problem efficiently?

提前感谢，祝您有愉快的一天！

Thanks in advance and have a nice day!

推荐答案

我认为以下基于相关子查询的方法可以满足您的要求:

I think the following approach, based on a correlated subquery, does what you ask for:

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

这会为日期等于或等于订购时间戳的同一用户搜索最新的登录历史记录，并恢复相应的国家/地区.

This searches for the latest login_history for the same user whose date prior or equal to the order timestamp, and recovers the corresponding country.

这篇关于具有多个联接(包括远程联接)的MariaDB/MySQL UPDATE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！