遠隔結合を含む複数の結合を含むMariaDB / MySQLUPDATEステートメント

Nov 27 2020

私はテーブルのために持っています

ログイン履歴

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
)

すべてのテーブルには、この問題に適合するインデックスがあります。

私の目標は、注文の国コードにip2locationテーブルの国を入力することです。ログイン履歴があり、必要に応じて問題を複雑にしないようにしたいので、ユーザーが指定した時間範囲内に持っていた最新のIPを使用しても問題ありません。国を切り替えて時間内に何かを購入することは、ごくわずかなユースケースだと思います。また、ログイン履歴は数日間しか保持されないため、country_codeをnullに設定した古い注文を処理して、ユーザーの国を取得したいと思います。

私のアプローチは次のとおりです。

次の「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;

それは動作しますが、非常に遅いです。おそらくテーブルのサイズも原因です。

login_history> 15Mio。エントリ（ステートメントはこれを500Kエントリに減らします）
アカウント> 7ミオ。エントリ
ip2location〜200Kエントリ
注文> 1ミオ。

たぶんそれはMariaDBが解決策を提供できないユースケースです。目標は、このクエリを30秒以内に完了することです。テーブルを長時間ロックしないという理由から、もちろん速い方が良いでしょう。

次のステートメントには、いくつかの可能性があります。ip2locationテーブルで正しいエントリを見つけるには、範囲を使用する必要があります。また、IPが1つだけ指定され、ip_toフィールドが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)

また、次の選択はやや時間がかかるように見えます。

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時間も多く使用する可能性があります。

より良いクエリを見つけるのを手伝ってもらえますか、またはこの問題に効率的に取り組む方法についていくつかの良いアドバイスがありますか？

よろしくお願いします。良い一日を！

回答

1 GMB Nov 27 2020 at 09:41

相関サブクエリに基づく次のアプローチは、あなたが求めていることを実行すると思います。

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

これにより、注文のタイムスタンプより前または同じ日付の同じユーザーの最新のlogin_historyが検索され、対応する国が復元されます。