mysql - 当前查询运行需要一分钟，何时创建单独的查询而不是仅创建一个查询？

Question

所以我正在处理一个具有相当数量的连接和很多主关系的查询。

唯一具有一对多关系的表是invoice、so 和xc_orders。

每个表也有数十万行 -

• 发票有 822,967 行
• invc_fee 有 208,021 行
• invc_tender 有 821,799 行
• 客户有 377,515 行
• cust_address 有 665,633
• invc_item 有 1,975,436 行
• invn_sbs 有 122,669 行
• 所以有 195,169 行
• xc_orders 有 267,165 行

如果我根据 WHERE 条件将下面的查询拆分为两个单独的查询，则会将运行查询的时间长度从第一个查询的 56.8 秒更改为 5.36 秒，将第二个查询的运行时间从 5.32 秒更改为 5.32 秒。我认为这是由于 OR 子句造成的？仅单独运行查询并查看运行这些查询而不缓存结果的时间是确定是否可以组合 WHERE 条件的最明显方法吗？我是否遗漏了一些东西，可以让我加快结果速度，同时仍然保留 OR 条件语句？感谢您的帮助。

对于它的值(value)来说，这是运行 MySQL 5.5 数据库。

 SELECT SQL_NO_CACHE i.invc_no, DATE_FORMAT(i.created_date, '%Y-%m-%d') AS invcdate, IF(i.so_no LIKE '%WEB%', substring(i.so_no,5,10),i.so_no) AS so, format(SUM(it.amt),2) AS invc_amt, i.invc_type, format(ii.qty,0) as qty, isb.description1, format(ii.price,2) As price, replace(isb.dcs_code,' ','') AS dcs, isb.siz, isb.attr, trim(i.note) AS invc_note, trim(so.note) AS so_note, trim(xo.notes) AS xcart_notes, trim(xo.customer_notes) AS xcart_cust_notes
            FROM      rp.invoice AS i
            LEFT JOIN rp.invc_fee AS ife ON i.invc_sid = ife.invc_sid
            LEFT JOIN rp.invc_tender AS it ON it.invc_sid = i.invc_sid
            LEFT JOIN rp.customer AS c ON i.cust_sid = c.cust_sid
            LEFT JOIN rp.cust_address AS ca ON c.cust_sid = ca.cust_sid /* NEW */
            LEFT JOIN rp.invc_item AS ii ON ii.invc_sid = i.invc_sid
            LEFT JOIN rp.invn_sbs AS isb ON isb.item_sid = ii.item_sid
            LEFT JOIN rp.so AS so ON so.so_sid = i.so_sid
            LEFT JOIN dev.xc_orders AS xo ON xo.orderid = REPLACE(so.so_no,'WEB0','')
            WHERE     i.invc_no != '0' AND (c.email_addr = 'email@gmail.com' OR (c.first_name = 'Eric' AND c.last_name = 'MXXXX' AND ca.address1 LIKE '1234%' AND ca.zip = '12345')) AND IFNULL(ife.fee_type, 0) >= 0
            GROUP BY  i.invc_no, i.created_date, i.so_no, i.invc_type, ii.qty, isb.description1, ii.price, isb.dcs_code, isb.siz, isb.attr, i.note, so.note, xo.notes, xo.customer_notes, ii.item_pos, ii.item_sid
            ORDER BY  i.created_date desc, i.invc_no, i.invc_type

这是解释结果

 id    select     table  type    possible_keys    key_len    ref             rows    filtered    Extra 
 1     SIMPLE     i      ALL     INVC_NO                                     822967  91.92       Using where; Using temporary; Using filesort
 1     SIMPLE     ife    ref     PRIMARY          8          rp.i.INVC_SID   2080    100.00      Using where; Using index
 1     SIMPLE     it     ref     PRIMARY          8          rp.i.INVC_SID   8217    100.00 
 1     SIMPLE     c      eq_ref  PRIMARY          8          rp.i.CUST_SID   1       100.00      Using where
 1     SIMPLE     ca     ref     PRIMARY          8          rp.c.CUST_SID   6656    100.00      Using where
 1     SIMPLE     ii     ref     PRIMARY          8          rp.i.INVC_SID   19754   100.00 
 1     SIMPLE     isb    ref     PRIMARY          8          rp.ii.ITEM_SID  1226    100.00      Using where
 1     SIMPLE     so     eq_ref  PRIMARY          8          rp.i.SO_SID     1       100.00      Using where
 1     SIMPLE     xo     eq_ref  PRIMARY          4          func            1       100.00      Using where

Answer 1

为了提高性能，我建议替换 左连接 rp.customer AS c ON i.cust_sid = c.cust_sid LEFT JOIN rp.cust_address AS ca ON c.cust_sid = ca.cust_sid/* 新 */.... 其中 i.invc_no != '0' AND (c.email_addr = 'email@gmail.com' OR (c.first_name = 'Eric' AND c.last_name = 'MXXXX' AND ca.address1 LIKE '1234%' AND ca .zip = '12345')) AND IFNULL(ife.fee_type, 0) >= 0

与

左连接( SELECT * FROM rp.customer WHERE c.email_addr = 'email@gmail.com' OR (c.first_name = 'Eric' AND c.last_name = 'MXXXX' ) AS c ON i.cust_sid = c.cust_sid LEFT JOIN (SELECT * FROM rp.cust_addr WHERE ca.address1 LIKE '1234%' AND ca.zip = '12345') AS ca ON c.cust_sid = ca.cust_sid/* 新 */....WHERE i.invc_no != '0' AND IFNULL(ife.fee_type, 0) >= 0