MySQL 查询运行速度非常慢

Question

我有一个相当大的查询，运行速度非常慢。开始时大约需要15分钟。然后我添加了所有适当的索引并将其降低到 30 秒。这是昨天的事了。今天，我添加了 2 个新连接，实际上将一些 LEFT JOIN 更改为 INNER，现在运行需要 20 多分钟。

这是查询的EXPLAIN。我将 Excel 文件上传到 SkyDrive，其中包含结果:http://cid-a32b31fdac0efced.office.live.com/view.aspx/mysqlexplain.xlsx

您将在这里看到有一个表 mwd，它说它没有键。我已经检查过该表，它确实附加了适当的索引。但是，这不是我添加到连接的表，并且昨天运行良好。

我创建的新连接可以在第 28 和 29 行看到。

任何帮助将不胜感激。

编辑:编辑:我再次运行查询，但没有 mwd 表中的字段，即。 BIR3M、dealer_code、country_code、dsm_dealer_number、location_number、dms_type、sequence_number、version_no_edr、id_dms_type、version_no_dms、version_date_dms 和现在只需 4 秒即可运行! (它仍在加入此表)

我知道它无法找到 mwd 的键，即使正确字段上有索引，那么我该怎么办？

编辑:并不是真的想发布查询，工作内容。但这是连接的第一部分，导致了延迟。好长啊...

SELECT mwd.dealer_code AS "BIR3M",
    vm.chassis_number AS "VIN",
    vm.Vehicle_ID,
    vm.Registration,
    COALESCE(vm.After_sale_type, "") AS "After Sales Type",
    COALESCE(vm.Fabrication_No, "") AS "Fabrication No",
    I.created_date AS "InvoiceDate",
    I.department_id,
    COALESCE(C.claim_number, "") AS "Intervention ID",
    wb.booking_id AS "Booking ID",
    wb.booking_date_time AS "Booking Date",
    iba.account AS "Account Number",
    acc.account_name AS "Account Description",
    ibi.warranty_percentage AS "Payee Responsibility %",
    IF(I.iscredit = 1, I.invoice_number, COALESCE(I2.invoice_number, "")) AS "Invoice Number",
    IF(I.iscredit = 1, COALESCE(I2.invoice_number, "") , "") AS "Original Invoice Number",
    "" AS "Part Reference",
    "" AS "Part Description",
    ibi.Booking_Time AS "Quantity",
    0 AS "Cause Indicator",
    "" AS "Stock Status",
    ROUND(
            (
                CASE WHEN iBI.Claimed_Flag = 1 THEN (ibi.booking_time * ibi.warranty_labour_rate)
                    #(iBI.Claimed_Value * iBI.warranty_Labour_Rate) 
                WHEN iBi.Claimed_Flag = 0 THEN ibi.booking_time
                    #(iBI.Claimed_Value) 
                END  
            ) # End 
            * CASE WHEN I.iscredit = 1 THEN -1 ELSE 1 END
        ,2) AS "RetailPrice", 
    ibia.discount AS "Discount Rate",
    0.00 AS "Surcharge Rate",
    ROUND(
    ibi.Booking_Time * ibi.warranty_labour_rate * (1 - ibia.discount / 100) * 
        COALESCE(mwc.commercial_cont, 100) / 100 , 2)
    AS "Cost Before Tax",
        iba.account AS "Account",
    CASE
        WHEN ibi.warranty_percentage > 0 THEN "Warranty"
        WHEN act.generic_type = 0 THEN "External"
        WHEN act.generic_type = 1 THEN "Vehicle"
        WHEN act.generic_type = 2 THEN "Internal"
        WHEN act.generic_type = 3 THEN "Non Productive"
    END AS "AccountType",
    ibi.booking_time AS "Labour Time", 
    ibi.warranty_labour_rate AS "Hourly Rate",
    0.00 AS "VAT Rate",
    0.00 AS "VAT Total",
    IBI.id AS "Invoice Booking Item ID",
    IBI.operation_code AS "OPECOD",
    IBI.job_description AS "Description",
    CASE WHEN i.iscredit = 1 THEN 9 ELSE 0 END AS "Accounts Allocation",
    COALESCE(mwc.contract_card_no, "") AS "Contract Card No",
    COALESCE(mwc.vehicle_mileage, "") AS "Mileage",
    COALESCE(mwc.delivery_date, "") AS "Delivery Date",
    COALESCE(mwc.vo_number, "") AS "VO Number",
    COALESCE(mwc.expense_code, "") AS "Expense Code",
    COALESCE(mwc.catalog_function, "") AS "Catalog Function",
    COALESCE(mwc.customer_complaint, "") AS "Customer Complaint",
    COALESCE(mwc.commercial_cont, 100) AS "Commercial Contribution",
    COALESCE(mwc.ots_otc_no, "") AS "OTS/OTC No",
    COALESCE(mwc.supplier_code, "") AS "Supplier Code",
    COALESCE(mwc.paint_code, "") AS "Paint Code",
    COALESCE(mwc.off_road_breakdown, "") AS "Off Road Breakdown",
    COALESCE(mwc.approval_no, "") AS "Approval No",
    COALESCE(mwc.assistance_number, "") AS "Assistance Number",
    COALESCE(mwc.customer_complaint_comment, "") AS "Customer Complaint Comment",
    wb.booking_id AS "Repair Order Number",
    wb.booking_date_time AS "Repair Order Date",
    mwd.dealer_code,
    mwd.country_code,
    mwd.dsm_dealer_number,
    mwd.location_number,
    mwd.dms_type,
    mwd.sequence_number,
    mwd.version_no_edr,
    mwd.id_dms_type,
    mwd.version_no_dms,
    mwd.version_date_dms,
    COALESCE(mwc.customer_satisfied, "") AS "Customer Satisfied",
    COALESCE(mwc.dealer_satisfied, "") AS "Dealer Satisfied",
    COALESCE(mwc.parts_invoice_no, "") AS "Parts Invoice Number",
    "" AS "Parts Type",
    "" AS "Packaging Code",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN "P"
         WHEN br.Business_ID IS NOT NULL THEN "S"
         WHEN d.CODE IS NOT NULL THEN "S"
    END AS "Customer Type",
    "P" AS "Customer Vehicle Relation",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Surname
         WHEN br.Business_ID IS NOT NULL THEN br.Company_Name
         WHEN d.CODE IS NOT NULL THEN d.Description
    END AS "Surname/Corporate Name",
    "GB" AS "Language",
    COALESCE(cr.Surname, "") AS "Second Name",
    COALESCE(cr.Forename, "") AS "First Name",
    COALESCE(cr.Date_Of_Birth, "") AS "DOB",
    COALESCE(cr.Title, "") AS "Title",
    COALESCE(vm.Registration_Date, "") AS "Registration Date",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.Letter_Contact_Allowed = 1, "", "A")
         WHEN br.Business_ID IS NOT NULL THEN IF(br.Letter_Contact_Allowed = 1, "", "A")
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Mail Contact",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.EMail_Contact_Allowed = 1, "", "E")
         WHEN br.Business_ID IS NOT NULL THEN IF(br.EMail_Contact_Allowed = 1, "", "E")
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "EMail Contact",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.Fax_Contact_Allowed = 1, "", "F")
         WHEN br.Business_ID IS NOT NULL THEN IF(br.Fax_Contact_Allowed = 1, "", "F")
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Fax Contact",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN IF(cr.Telephone_Contact_Allowed = 1, "", "T")
         WHEN br.Business_ID IS NOT NULL THEN IF(br.Telephone_Contact_Allowed = 1, "", "T")
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Telephone Contact",
    COALESCE(CASE WHEN cr.Contact_ID IS NOT NULL THEN 
        CASE WHEN cr.Preferred_Contact_Method = 1 THEN "A"
             WHEN cr.Preferred_Contact_Method = 2 THEN "E"
             WHEN cr.Preferred_Contact_Method = 9 THEN "F"
             WHEN cr.Preferred_Contact_Method = 5 THEN "T"
        END
         WHEN br.Business_ID IS NOT NULL THEN
        CASE WHEN br.Preferred_Contact_Method = 1 THEN "A"
             WHEN br.Preferred_Contact_Method = 2 THEN "E"
             WHEN br.Preferred_Contact_Method = 9 THEN "F"
             WHEN br.Preferred_Contact_Method = 5 THEN "T"
        END
         WHEN d.CODE IS NOT NULL THEN "E"
    END, "") AS "Preferred Support Type",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_1
         WHEN br.Business_ID IS NOT NULL THEN br.Address_1
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Street Name",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Postcode
         WHEN br.Business_ID IS NOT NULL THEN br.Postcode
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Post Code",
    "GBR" AS Country,
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_2
         WHEN br.Business_ID IS NOT NULL THEN br.Address_2
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Address 1",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_3
         WHEN br.Business_ID IS NOT NULL THEN br.Address_3
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Address 2",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Address_4
         WHEN br.Business_ID IS NOT NULL THEN br.Address_4
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Address 3",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Email_Address
         WHEN br.Business_ID IS NOT NULL THEN br.Email_Address
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Email",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Tel_num_home
         WHEN br.Business_ID IS NOT NULL THEN br.Tel_num_primary
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Phone Number 1",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Tel_num_mobile
         WHEN br.Business_ID IS NOT NULL THEN br.Tel_num_secondary
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Phone Number 2",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Tel_num_business
         WHEN br.Business_ID IS NOT NULL THEN br.Tel_num_mobile
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Phone Number 3",
    CASE WHEN cr.Contact_ID IS NOT NULL THEN cr.Fax_num
         WHEN br.Business_ID IS NOT NULL THEN br.Fax_num
         WHEN d.CODE IS NOT NULL THEN ""
    END AS "Fax Number",
    wb.Owner_ID AS "Owner",
    1 AS "Labour Flag",
    "WARRANTY"
FROM
    I
    INNER JOIN iba ON I.monetary_transaction_id = iba.monetary_transaction_id
    INNER JOIN ibi ON ibi.booking_id = iba.booking_id AND ibi.job_group = iba.job_group AND ibi.sequence = iba.sequence
    INNER JOIN G ON G.Booking_item_id= Ibi.ID
        AND g.Type_ID = 1
    INNER JOIN C ON C.ID = G.Claim_Booking_claim_ID
    INNER JOIN WM ON WM.Warranty_Booking_Claim_ID = C.ID
    INNER JOIN mwc ON mwc.ID = WM.Manufacturer_Warranty_Claim_ID
    INNER JOIN ibia ON ibia.booking_item_id = ibi.id 
        AND ibia.sequence = ibi.sequence AND ibia.booking_account_id = iba.id AND ibia.sequence = iba.sequence
    INNER JOIN wb ON wb.booking_id = ibi.booking_id
    INNER JOIN a ON a.account_id = iba.account
    INNER JOIN act ON act.type = a.type
    LEFT JOIN ibev ON ibev.booking_item_id = ibi.id AND ibi.sequence = ibev.sequence
    INNER JOIN vm ON wb.vehicle_id = vm.vehicle_id
    INNER JOIN mwd ON mwd.department_id = I.department_id
    INNER JOIN mt ON I.monetary_transaction_id = mt.master_monetary_transaction_id
    INNER JOIN I2 ON mt.id = I2.monetary_transaction_id
    INNER JOIN acc ON iba.account = acc.account_id
    LEFT JOIN cr ON wb.owner_id = cr.contact_id
    LEFT JOIN br ON wb.owner_id = br.business_id
    LEFT JOIN d ON wb.owner_id = d.code

    WHERE I.department_id IN (120, 322, 362)
    AND I.created_date BETWEEN '2011-03-01 00:00:00' AND '2011-03-02 23:59:59'
    AND ibev.booking_item_id IS NULL AND NOT ibi.booking_item_type_id IN (10,14) AND ibi.warranty_percentage > 0
    AND wb.booking_id IN (454017, 454019, 454021, 454031)

Answer 1

每当您的查询涉及如此多的表时，通常都会出现设计问题。撇开这一点不谈，我注意到您混合了左(外)连接和内连接。

除非表之间存在特定的依赖关系，例如

A LEFT JOIN B on (a..b) INNER JOIN C on (c..b)

(C 必​​须在 b 之后)

您应该尽早将所有 INNER JOIN 组合在一起，例如而不是

A LEFT JOIN B on (a..b) INNER JOIN C on (c..a)

写成

A INNER JOIN C on (c..a) LEFT JOIN B on (a..b)

这将帮助优化器首先处理 INNER JOIN，这可能会减少行数。根据定义，LEFT JOIN 会保留前面表中的所有行，因此优化器通常会稍后处理这些后续表。