优化我的查询(索引，EXPLAIN)Mysql [英] Optimize my query (indexes, EXPLAIN) Mysql

问题描述

在 stackoverflow 上的开发人员的帮助下，我已经能够优化我的查询以使其性能比最初更好.执行时间下降到:1.2s

With the help of developers here on stackoverflow, I have been able to optimize my query to perform better than it was initially doing. Execution time dropped to: 1.2s

但是，在对查询执行 EXPLAIN 后，似乎没有使用 loan_applications_tbl ( date_updated, Loan_status, current_loan, ippis ) 索引，因为类型列对表 表示 ALL一个.请问这可能是什么原因?为什么该表的索引被忽略，我该如何解决?

However, after doing EXPLAIN on the query, it seemed like the loan_applications_tbl ( date_updated, loan_status, current_loan, ippis ) indexes were not being used, as the type column says ALL for table a. What might be the cause for this please? Why is the index for this table being ignored and how do I fix this?

在下面查看我的查询:

 EXPLAIN 
SELECT 
        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.top_up, 
        a.current_loan, 
        a.date_created, 
        a.date_updated, 
        c.loan_id, 
        c.user_unique_id tu_user_unique_id, 
        c.ippis tu_ippis, 
        c.top_up_approved,
        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 
    FROM 
        -- this creates inline mySQL variables I can use for the WHERE condition
        -- by doing comma after with no explicit join, it is a single row
        -- and thus no Cartesian result, just @variables available now
        ( select 
                -- first truncating any TIME portion by casting to DATE()
                @myToday := date(curdate()),
                -- now subtract day of month -1 to get first of THIS month
                @beginOfMonth := date_sub( @myToday, interval dayOfMonth( @myToday ) -1 day ),
                -- and now, add 1 month for beginning of next
                @beginNextMonth := date_add( @beginOfMonth, interval 1 month ) ) SqlVars,

        loan_applications_tbl a
    
            LEFT JOIN topup_or_reapplication_tbl c
                ON  a.ippis = c.ippis   
                AND c.current_loan='1'
                AND c.status IN ('pending', 'corrected', 'Rejected', 
                                'Processing', 'Captured', 'Reviewed', 'top up') 

                
                AND @beginOfMonth <= c.date_updated
                AND c.date_updated < @beginNextMonth
    WHERE
            -- forces only activity for the single month in question
            -- since the "a" table knows of any "updates" to the "C",
            -- its updated basis will keep overall restriction to any accounts
            -- updated within this month in question only
            @beginOfMonth <= a.date_updated 
        AND a.date_updated < @beginNextMonth
        
        -- and NOW we can easily apply the OR without requiring
        -- to run against the ENTIRE set of BOTH tables.
        AND (
                    c.ippis IS NOT NULL
                OR 
                    ( a.loan_status IN (  'pending', 'corrected', 'Rejected', 'Processing', 
                            'Captured', 'Reviewed', 'top up')
                    AND (   
                            a.current_loan = '1' 
                        OR  (   a.current_loan = '0' 
                            AND a.loan_status IN ('Approved', 'Closed')
                            )
                        )
                    )
            )

使用的索引:

loan_applications_tbl ( date_updated, loan_status, current_loan, ippis )
topup_or_reapplication_tbl ( ippis, status, current_loan, date_updated )

对查询的解释给出以下屏幕截图:

EXPLAIN on the query gives the screenshot below:

推荐答案

尝试 https://dev.mysql.com/doc/refman/8.0/en/index-hints.html#:~:text=%20USE%20INDEX%20(%20index_list%20)%20hint,some%20particular%20index%20or%20indexes.

例如

SELECT * FROM table1 USE INDEX (col1_index,col2_index) WHERE col1=1 AND col2=2 AND col3=3;

这篇关于优化我的查询(索引，EXPLAIN)Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！