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

只要您的查询涉及如此多的表格 - 它通常会带来设计问题。除此之外，我注意到你有一个LEFT（OUTER）和INNER JOIN的混合。

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

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

（C必须B之后来临）

你应该组在一起的所有内尽可能早地，例如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) 

这将有助于优化过程中的内连接第一，这潜在地降低了行计数。按照定义，LEFT JOIN保留前表的所有行，所以优化器通常会在稍后执行后续表。

Answer 2

到现在为止，我可以看到优化查询的末尾：

例如，这可以被改写

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) 

到

 JOIN (SELECT 120 AS d 
      UNION ALL 
      SELECT 322 AS d 
      UNION ALL 
      SELECT 362) d1 
     ON d1.d = i.department_id 
     JOIN (SELECT 454017 AS b 
      UNION ALL 
      SELECT 454019 AS b 
      UNION ALL 
      SELECT 454021 AS b 
      UNION ALL 
      SELECT 454031 AS b) d2 
     ON d2.b = wb.booking_id 
     LEFT JOIN (SELECT 10 AS bt 
        UNION ALL 
        SELECT 14 AS bt) d3 
     ON d3.bt <> ibi.booking_item_type_id 
WHERE 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 ibi.warranty_percentage > 0 

其中我们采用IN选项并放入派生的静态表中，以便在它们的匹配上--ON子句 - 索引sh可以使用。 MySQL在连接上使用索引，但不在IN子句中使用。

您需要太多以下列添加索引

• I.department_id
• wb.booking_id
• ibi.booking_item_type_id

Answer 3

OK，我rejigged的JOIN的秩序“ s并移动：

INNER JOIN mwd ON mwd.department_id = I.department_id 

成为第一个JOIN。现在查询需要8秒！所以这就是固定的。但任何人都可以解释为什么会发生？