MariaDB / MySQL UPDATE स्टेटमेंट में एक राउंडेड जॉइन सहित कई जॉइन होते हैं

Question

मेरे पास टेबल के लिए है

एक लॉगिन इतिहास

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

एक आईपी स्थान तालिका के लिए

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 तालिका से देश के साथ आदेशों का देश कोड भरना है। मेरे पास एक लॉगिन इतिहास है और क्योंकि मैं समस्या को और अधिक जटिल नहीं बनाना चाहता हूं, मैं सबसे हाल के आईपी का उपयोग करने के साथ ठीक हूं, एक उपयोगकर्ता को दिए गए समय सीमा में था। मैं मानता हूं कि किसी देश को बदलना और समय सीमा के भीतर कुछ खरीदना, एक नगण्य उपयोग का मामला है। इसके अलावा, क्योंकि लॉगिन इतिहास केवल कुछ दिनों के लिए आयोजित किया जाता है, मैं पुराने आदेशों को भरना चाहता हूं, जिन्होंने उपयोगकर्ता के लिए देश प्राप्त करने के लिए देश_कोड को शून्य करने के लिए भी सेट किया है।

मेरा दृष्टिकोण निम्नलिखित है।

मैं निम्नलिखित "अभिव्यक्ति" पर दोनों तालिकाओं के साथ जुड़ने की कोशिश कर रहा हूं।

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> 15 Mio। प्रविष्टियाँ (जहाँ कथन इसे 500K प्रविष्टियों तक घटाता है)
• खाता> 7 Mio। प्रविष्टियों
• ip2location ~ 200K प्रविष्टियाँ
• आदेश> 1 Mio।

हो सकता है कि यह एक उपयोग का मामला है जहां मारियाडीबी एक समाधान प्रदान नहीं कर सकता है। लक्ष्य इस क्वेरी को 30 सेकंड से कम समय में पूरा करने का है। बहुत लंबे समय तक मेज पर ताला नहीं लगाने के कारणों के लिए, तेजी से बेहतर होगा।

मैं निम्नलिखित कथन में कुछ क्षमता देखता हूं। Ip2location तालिका में सही प्रविष्टि खोजने के लिए, मुझे एक सीमा का उपयोग करना होगा और मुझे एक प्रविष्टि मौजूद होने पर भी विचार करना होगा, जहाँ केवल एक IP दिया गया है, और ip_to फ़ील्ड शून्य है।

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

मैंने सोचा कि इस विभाजन को पहले एक चयन और फिर एक अपडेट स्टेटमेंट का उपयोग करें, लेकिन अंत में, यह अधिक समय खर्च कर सकता है और स्क्रिप्ट के कारण अधिक सीपीयू समय का भी उपयोग करेगा, जो इस कार्य को व्यवस्थित करता है।

क्या आप मुझे एक बेहतर क्वेरी खोजने में मदद कर सकते हैं या क्या आपको इस समस्या से कुशलतापूर्वक निपटने के लिए कुछ अच्छी सलाह है?

अग्रिम धन्यवाद और आपका दिन शुभ हो!

Answer 1

मुझे लगता है कि निम्नलिखित दृष्टिकोण, एक सहसंबद्ध उपश्रेणी पर आधारित है, जो आप के लिए पूछते हैं:

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 के लिए खोज करता है, जिसकी तिथि पूर्व या टाइमस्टैम्प के बराबर होती है , और संबंधित देश को पुनर्प्राप्त करती है।