मेरे पास एक सिस्टम में एक आवर्ती पैटर्न है जिस पर मैं वर्तमान में काम कर रहा हूं, उदाहरण के लिए, मुझे उन सभी उपयोगकर्ताओं का चयन करने की आवश्यकता है जिनके पास संभावित कंपनियों की सूची के तहत ऑर्डर हैं। या यदि इस उपयोगकर्ता को फ़्लैग किए जाने का रिकॉर्ड मौजूद है, तो उपयोगकर्ताओं को चुनने की आवश्यकता है।

मेरी users तालिका में 430,825 रिकॉर्ड हैं, इसलिए इससे निपटना वास्तव में इतना कठिन नहीं होना चाहिए। अभी मैं करीब हूं, मेरे पास एक प्रश्न है जो .047s निष्पादन समय प्राप्त करता है जिसे मैं ढूंढता हूं, लेकिन अगर मैं इसमें एक और टुकड़ा जोड़ता हूं, तो यह वास्तविक धीमा हो जाता है।

यहाँ मेरी वर्तमान क्वेरी है, सबसे तेज़:

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1 
        from`usercustomerflags`
        where`__Active`=1 
        and`users`.`UserID`=`UserID`))
and`Deleted`=0 
order by`DateTimeAdded`desc 
limit 50;

(अतिरिक्त select*from(...) इस वजह से है https://stackoverflow.com/a/1434712/728236)

बीच में, मैं उपयोगकर्ताओं को ईमेल पते से आगे खींच रहा हूं, जबकि ईमेल के लिए अन्य संबंधित तालिकाओं की जांच कर रहा हूं जो इस उपयोगकर्ता से संबंधित हो सकते हैं। जैसे, अगला भाग ग्राहकों को उद्धरण भेजे जाने पर उनके सीसी पते सहित उपयोगकर्ताओं की खोज करता है।

select`UserID`
from`users`
where(`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
    or`UserID`in(select*
        from(select`UserID`
            from`invoices`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`quoterequests`
            where`CompanyID`in('3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8')
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`userassociations`
            where`_Email`='brian@yeet.com'
            and`__Active`=1)`a`)
    or`UserID`in(select*
        from(select`UserID`
            from`userquotesemails`
            where`Email`='brian@yeet.com'
            and`__Active`=1)`a`))
and(`UserID`in(select*
        from(select`UserID`
            from`usercustomerflags`
            where`CustomerFlagID`in(10,27,17,1,2,3,4,5,6)
            and`__Active`=1)`a`)
    or not exists(select 1 
        from`usercustomerflags`
        where`__Active`=1 
        and`users`.`UserID`=`UserID`))
and`Deleted`=0 
order by`DateTimeAdded`desc 
limit 50;

मैंने ईमेल खोजने के लिए वैकल्पिक तालिका जोड़ी है, लेकिन अब क्वेरी में 3.016 सेकंड लगते हैं, जो कि रास्ता धीमा है। यह अजीब लगता है कि, जैसा कि मैं इस प्रश्न का निर्माण कर रहा था, वह अंतिम भाग यहाँ प्रदर्शन में महत्वपूर्ण बिंदु लग रहा था, इसका कारण क्या होगा?

पहला और दूसरा क्रमशः बताते हैं

+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+
|  1 | PRIMARY            | <subquery6>       |  | ALL            |                                                                                             |                              |      |                       |   | 0.00  | Using temporary; Using filesort |
|  1 | PRIMARY            | users             |  | eq_ref         | PRIMARY,UserID_UNIQUE,fk_users_1_idx,users_Customers                                        | PRIMARY                      |  144 | <subquery6>.UserID    | 1 | 50.00 | Using where                     |
|  6 | MATERIALIZED       | userassociations  |  | ref            | userassociations_UserID,userassociations__Email                                             | userassociations__Email      | 1026 | const                 | 3 | 10.00 | Using where                     |
| 10 | DEPENDENT SUBQUERY | usercustomerflags |  | ref            | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx |  144 | sterling.users.UserID | 1 | 10.00 | Using where                     |
|  8 | DEPENDENT SUBQUERY | usercustomerflags |  | index_subquery | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           | usercustomerflags_UserID_idx |  144 | func                  | 1 | 4.95  | Using where                     |
|  4 | DEPENDENT SUBQUERY | quoterequests     |  | index_subquery | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests__Latest        |  145 | func                  | 2 | 5.00  | Using where                     |
|  2 | DEPENDENT SUBQUERY | invoices          |  | index_subquery | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_UserID_idx           |  145 | func                  | 1 | 3.33  | Using where                     |
+----+--------------------+-------------------+--+----------------+---------------------------------------------------------------------------------------------+------------------------------+------+-----------------------+---+-------+---------------------------------+

+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+
|  1 | PRIMARY            | users             |  | ref | fk_users_1_idx,users_Customers                                                              | users_Customers                |    4 | const                 | 227515 | 100.00 | Using where |
| 12 | DEPENDENT SUBQUERY | usercustomerflags |  | ref | usercustomerflags_UserID_idx                                                                | usercustomerflags_UserID_idx   |  144 | sterling.users.UserID |      1 | 10.00  | Using where |
| 10 | SUBQUERY           | usercustomerflags |  | ALL | usercustomerflags_CustomerFlagID_idx,usercustomerflags_UserID_idx                           |                                |      |                       |   3509 | 4.94   | Using where |
|  8 | SUBQUERY           | userquotesemails  |  | ref | userquotesemails_Email__Active,userquotesemails_UserID                                      | userquotesemails_Email__Active | 1027 | const,const           |      1 | 100.00 |             |
|  6 | SUBQUERY           | userassociations  |  | ref | userassociations_UserID,userassociations__Email                                             | userassociations__Email        | 1026 | const                 |      3 | 10.00  | Using where |
|  4 | SUBQUERY           | quoterequests     |  | ref | quoterequests_CompanyID,quoterequests_UserID,quoterequests__Latest,quoterequests_UserQuotes | quoterequests_CompanyID        |  144 | const                 |  16702 | 10.00  | Using where |
|  2 | SUBQUERY           | invoices          |  | ref | Invoice_UserID_idx,Invoice_CompanyID_idx,invoices_SampleRequests,invoices_LateOrdersBubble  | Invoice_CompanyID_idx          |  144 | const                 |  17678 | 10.00  | Using where |
+----+--------------------+-------------------+--+-----+---------------------------------------------------------------------------------------------+--------------------------------+------+-----------------------+--------+--------+-------------+

साथ ही, मैंने जॉइन का उपयोग करने का प्रयास किया है, उदा। invoices तालिका, आदि में शामिल होना, लेकिन फिर मुझे प्रत्येक invoice या quoterequest के प्रति डुप्लिकेट उपयोगकर्ता पंक्तियाँ होने का मुद्दा मिलता है, जो प्राप्त होते हैं, और परिणामी डेटा को समूहबद्ध/विशिष्ट और क्रमित करना अत्यधिक हो जाता है धीमा, मिनटों में।

मैंने पहली क्वेरी के "मौजूद" संस्करण की भी कोशिश की है, जैसा कि डॉक्टर द्वारा सुझाव दिया गया है mysql performance percona mysql-5.7 percona-xtradb-cluster

0
Brian Leishman 8 पद 2017, 00:30

2 जवाब

सबसे बढ़िया उत्तर

उस छोटी समस्या के लिए:

( select u.`UserID`, u.`_Customer`, u.DateTimeAdded
    from  `users` AS u
    where  u.`Email` = 'brian@stumpyinc.com'
      and  u.`Deleted` = 0
      AND EXISTS ( SELECT * FROM `userassociations`
                       WHERE UserId = u.UserID
                         AND __Active = 1 )
    order by  u.`DateTimeAdded` desc
    limit  50
)
UNION DISTINCT
( select u.`UserID`, u.`_Customer`, u.DateTimeAdded
    from  `users` AS u
    JOIN  `userassociations` AS ua
         ON  ua.`UserID` = u.`UserID`
        and  ua.`__Active` = 1
    where  ua.`_Email` = 'brian@stumpyinc.com' 
      and  u.`Deleted`=0
    order by  u.`DateTimeAdded` desc
    limit  50
)
order by `DateTimeAdded` desc
limit  50

इनकी जरूरत होगी:

u:  INDEX(Email, Deleted, DateTimeAdded)  -- date last
ua: INDEX(UserId, __Active)   -- either order
ua: INDEX(_Email)
u:  INDEX(UserID, Deleted)

(यदि आपको सिंटैक्स त्रुटियां मिलती हैं तो मुझे बताएं। यदि यह बहुत धीमी है, तो कृपया EXPLAIN प्रदान करें।)

इंडेक्स प्रीफ़िक्सिंग (Email(191)) आमतौर पर बेकार है। छुटकारा हो तो। इससे बचने के 5 तरीके यहां दिए गए हैं: http://mysql.rjweb.org/doc। php/limits#767_limit_in_innodb_indexes

एक पीके एक अद्वितीय कुंजी है, इसलिए दूसरे से छुटकारा पाएं:

PRIMARY KEY (`UserID`),
UNIQUE KEY `UserID_UNIQUE` (`UserID`),

एक यूयूआईडी की तरह बदबू आ रही है; ascii (ascii_general_ci) का उपयोग करें, utf8mb4 का नहीं:

... char(36) COLLATE utf8mb4_unicode_ci

INT(1) 4 बाइट लेता है; झंडे के लिए TINYINT का प्रयोग करें।

1
Rick James 8 पद 2017, 07:52

निश्चित रूप से आप उन सभी उपश्रेणियों को WHERE खंड में INNER और LEFT JOIN के संयोजन से बदल सकते हैं। ये कोशिश करें:

SELECT u.UserID 
FROM users u
INNER JOIN userassociations ua ON ua.userid = u.userid
LEFT JOIN usercustomerflags uc ON uc.userid = u.userid AND uc.__Active =1
LEFT JOIN invoices i ON i.userid = u.userid AND i.__Active =1
LEFT JOIN quoterequests q ON q.userid = u.userid AND q.__Active =1
WHERE ua._Email ='brian@yeet.com'
AND ua.__Active =1
AND (uc.userid IS NULL 
        OR (uc.userid IS NOT NULL AND uc.CustomerFlagID IN (10,27,17,1,2,3,4,5,6))
    )
AND (u.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8'
        OR (i.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND i.userid IS NOT null)
        OR (q.CompanyID = '3e55d1bb-d8b6-11e4-b38f-b8ca3a83b4c8' AND q.userid IS NOT null)
    )
AND u.Deleted =0 
ORDER BY u.DateTimeAdded desc 
LIMIT 50;
0
digital.aaron 8 पद 2017, 00:51