隨著用戶活動的增加，查詢變慢

下面的查詢執行需要 0.6748 秒，但是當有許多用戶嘗試同時執行相同的活動時，行程變得非常慢，執行時間大約為 30 秒。

服務器帶寬：7TB

我已經多次嘗試優化查詢并提高了性能，但是隨著資料庫變大，性能再次下降，我沒有更多的選擇可以嘗試。

請協助進一步優化我的查詢

SELECT * FROM (SELECT * FROM
                    (SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated 
                    FROM
                        loan_applications_tbl a 
                    LEFT JOIN
                        topup_or_reapplication_tbl AS c 
                            ON a.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    (
                                        a.current_loan = '0' 
                                        AND a.loan_status IN (
                                            'Approved', 'Closed'
                                        )
                                    )
                                ) 
                                AND (
                                    ((a.loan_status='pending' 
                            AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                    'NIL', 'NIL', 'NIL' )  )) 
                                ) 
                                AND (
                                    (
                                        MONTH(a.date_updated) = MONTH(CURRENT_DATE) 
                                        AND YEAR(a.date_updated) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION ALL 
                    SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated 
                    FROM
                        loan_applications_tbl_dump b 
                    LEFT JOIN
                        topup_or_reapplication_tbl c 
                            ON b.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    (
                                        b.current_loan = '0' 
                                        AND b.loan_status IN (
                                            'Approved', 'Closed'
                                        )
                                    )
                                ) 
                                AND (
                                    ((b.loan_status='pending' 
                            AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL' ) )) 
                                ) 
                                AND (
                                    (
                                        MONTH(b.date_updated) = MONTH(CURRENT_DATE) 
                                        AND YEAR(b.date_updated) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION DISTINCT 
                    SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated 
                    FROM
                        loan_applications_tbl a 
                    INNER JOIN
                        topup_or_reapplication_tbl AS c 
                            ON a.ippis = c.ippis 
                    WHERE
                        (
                            (
                                c.current_loan = '1' 
                                AND (
                                    ((c.status='pending' 
                            AND c.top_up_approved < 500000)  OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL') )) 
                                ) 
                                AND (
                                    (
                                        MONTH(c.date_updated) = MONTH(CURRENT_DATE) 
                                        AND YEAR(c.date_updated) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated 
                    FROM
                        loan_applications_tbl_dump b 
                    INNER JOIN
                        topup_or_reapplication_tbl c 
                            ON b.ippis = c.ippis 
                    WHERE
                        (
                            (
                                c.current_loan = '1' 
                                AND (
                                    ((c.status='pending' 
                            AND c.top_up_approved < 500000)  OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL') )) 
                                ) 
                                AND (
                                    (
                                        MONTH(c.date_updated) = MONTH(CURRENT_DATE) 
                                        AND YEAR(c.date_updated) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated 
                    FROM
                        loan_applications_tbl a 
                    LEFT JOIN
                        topup_or_reapplication_tbl AS c 
                            ON a.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    a.current_loan = '1'
                                ) 
                                AND (
                                    ((a.loan_status='pending' 
                            AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                    'NIL', 'NIL', 'NIL' )  )) 
                                ) 
                                AND (
                                    (
                                        MONTH(a.date_updated) = MONTH(CURRENT_DATE) 
                                        AND YEAR(a.date_updated) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated 
                    FROM
                        loan_applications_tbl_dump b 
                    LEFT JOIN
                        topup_or_reapplication_tbl c 
                            ON b.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    b.current_loan = '1'
                                ) 
                                AND (
                                    ((b.loan_status='pending' 
                            AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL' ) )) 
                                ) 
                                AND (
                                    (
                                        MONTH(b.date_updated) = MONTH(CURRENT_DATE) 
                                        AND YEAR(b.date_updated) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated 
                    FROM
                        loan_applications_tbl a 
                    INNER JOIN
                        topup_or_reapplication_tbl AS c 
                            ON a.ippis = c.ippis 
                    WHERE
                        (
                            (
                                c.current_loan = '1' 
                                AND (
                                    ((c.status='pending' 
                            AND c.top_up_approved < 500000)  OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL') )) 
                                ) 
                                AND (
                                    (
                                        MONTH(c.date_created) = MONTH(CURRENT_DATE) 
                                        AND YEAR(c.date_created) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated 
                    FROM
                        loan_applications_tbl_dump b 
                    INNER JOIN
                        topup_or_reapplication_tbl c 
                            ON b.ippis = c.ippis 
                    WHERE
                        (
                            (
                                c.current_loan = '1' 
                                AND (
                                    ((c.status='pending' 
                            AND c.top_up_approved < 500000)  OR (c.status IN ('corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL') )) 
                                ) 
                                AND (
                                    (
                                        MONTH(c.date_created) = MONTH(CURRENT_DATE) 
                                        AND YEAR(c.date_created) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated 
                    FROM
                        loan_applications_tbl a 
                    LEFT JOIN
                        topup_or_reapplication_tbl AS c 
                            ON a.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    (
                                        a.current_loan = '0' 
                                        AND a.loan_status IN (
                                            'Approved', 'Closed'
                                        )
                                    )
                                ) 
                                AND (
                                    ((a.loan_status='pending' 
                            AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                    'NIL', 'NIL', 'NIL' )  )) 
                                ) 
                                AND (
                                    (
                                        MONTH(a.date_created) = MONTH(CURRENT_DATE) 
                                        AND YEAR(a.date_created) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated 
                    FROM
                        loan_applications_tbl_dump b 
                    LEFT JOIN
                        topup_or_reapplication_tbl c 
                            ON b.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    (
                                        b.current_loan = '0' 
                                        AND b.loan_status IN (
                                            'Approved', 'Closed'
                                        )
                                    )
                                ) 
                                AND (
                                    ((b.loan_status='pending' 
                            AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL' ) )) 
                                ) 
                                AND (
                                    (
                                        MONTH(b.date_created) = MONTH(CURRENT_DATE) 
                                        AND YEAR(b.date_created) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated , a.id ,a.user_unique_id ,a.loan_location ,a.ippis ,a.tel_no ,a.organisation ,a.branch ,a.loan_agree ,a.loan_type ,a.appr ,a.sold ,a.loan_status , a.channel, a.top_up ,a.current_loan, a.retrieved_customer_bal ,a.date_created ,a.date_updated 
                    FROM
                        loan_applications_tbl a 
                    LEFT JOIN
                        topup_or_reapplication_tbl AS c 
                            ON a.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    a.current_loan = '1'
                                ) 
                                AND (
                                    ((a.loan_status='pending' 
                            AND a.appr < 500000) OR ( a.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                    'NIL', 'NIL', 'NIL' )  )) 
                                ) 
                                AND (
                                    (
                                        MONTH(a.date_created) = MONTH(CURRENT_DATE) 
                                        AND YEAR(a.date_created) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        ) 
                    UNION
                    DISTINCT SELECT
                         c.loan_id ,c.channel tu_channel, c.user_unique_id tu_user_unique_id ,c.ippis tu_ippis ,c.top_up_approved, c.ret_customer_bal ,c.loan_type tu_loan_type ,c.dse ,c.status ,c.current_loan tu_current_loan ,c.record_category ,c.date_created tu_date_created ,c.date_updated tu_date_updated, b.loan_id ,b.user_unique_id ,b.loan_location ,b.ippis ,b.tel_no ,b.organisation ,b.branch ,b.loan_agree ,b.loan_type ,b.appr ,b.sold ,b.loan_status ,b.channel, b.top_up ,b.current_loan, b.retrieved_customer_bal ,b.date_created ,b.date_updated 
                    FROM
                        loan_applications_tbl_dump b 
                    LEFT JOIN
                        topup_or_reapplication_tbl c 
                            ON b.ippis = c.ippis 
                    WHERE
                        (
                            (
                                (
                                    b.current_loan = '1'
                                ) 
                                AND (
                                    ((b.loan_status='pending' 
                            AND b.appr < 500000) OR (b.loan_status IN ( 'corrected', 'Approved By Group Head', 'NIL', 
                        'NIL', 'NIL', 'NIL' ) )) 
                                ) 
                                AND (
                                    (
                                        MONTH(b.date_created) = MONTH(CURRENT_DATE) 
                                        AND YEAR(b.date_created) = YEAR(CURRENT_DATE)
                                    )
                                )
                            )
                        )
                    ) t3 ORDER BY t3.date_updated, t3.tu_date_updated DESC LIMIT 18446744073709551615) AS t4 GROUP BY t4.ippis

EXPLAIN 陳述句給了我下表：

uj5u.com熱心網友回復：

這是一個非常大的查詢。對于 Stack Overflow 問題來說，甚至可能太大了。

1. 我建議您嘗試一次優化一個子查詢（UNION 操作的一個分支）。這樣您就不必立即考慮整個混亂局面。

2. 如果可以，請使用UNION ALL而不是 UNION DISTINCT；UNION DISTINCT 對其結果集進行重復資料洗掉，這需要 CPU 時間和 RAM。

3. 你有多次重復出現的 WHERE 子句模式。

       MONTH(a.date_updated) = MONTH(CURRENT_DATE)
   AND YEAR(a.date_updated) = YEAR(CURRENT_DATE)
   

   這不是sargable。也就是說，它的撰寫是為了阻止在 上使用索引loan_applications_tbl.date_updated。請改用此等效項。如果你這樣做 MySQL 將能夠對索引進行范圍掃描。

       a.date_updated >= LAST_DAY(CURRENT_DATE)   1 DAY - 1 MONTH
   AND a.date_updated <  LAST_DAY(CURRENT_DATE)   1 DAY
   

   LAST_DAY(CURRENT_DATE)獲取該月最后一天的午夜，其余的日期算術獲取本月第一天的午夜和下個月第一天的午夜。

4. 您沒有告訴我們有關您的索引的任何資訊。您的查詢有 OR 使它們變慢，因此您必須對此進行試驗。如果您還沒有此索引，請嘗試創建它。

   ALTER TABLE loan_applications_tbl 
    ADD INDEX cur_stat_date 
              (current_loan, loan_status, date_updated, appr)
   

   它可能會有所幫助，因為它允許 MySQL 隨機訪問我建議的索引，然后按順序讀取它。

   你需要一個類似的索引loan_applications_tbl_dump。

5. 當您有很多用戶時，您可能會看到爭用。如果您的應用程式可以容忍檢索可能不完全適合當前正在插入和更新的行的資料，請在查詢之前立即發出此命令。

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   

   如果您的應用程式使用大量事務，這對于資料完整性來說可能有點風險。但它會減少爭用。

6. 你能重構查詢以減少重復嗎？

uj5u.com熱心網友回復：

這些可能有幫助：

c:  INDEX(current_loan, status, top_up_approved, ippis)
a, b:  INDEX(current_loan, loan_status, appr, ippis)

拆分日期通常效率低下：

AND  MONTH(b.date_created) = MONTH(CURRENT_DATE)
AND  YEAR(b.date_created) = YEAR(CURRENT_DATE)

-->

AND b.date_created >= LEFT(CURDATE(), 7)

這應該允許這些索引有用：

b, c:  INDEX(current_loan, date_created, ippis)

這可以通過從c開始加快速度

標籤：mysql 表现 优化 查询优化 用户活动

上一篇：一種使用Dijkstra計算的演算法

下一篇：高級JQ技術