复杂查询的 MySQL 查询优化

Question

我正在处理一个现有网站，我遇到了以下需要优化的 MySQL 查询:

select
    mo.mmrrc_order_oid,
    mo.completed_by_email,
    mo.completed_by_name,
    mo.completed_by_title,
    mo.order_submission_oid,
    mo.order_dt,
    mo.center_id,
    mo.po_num_tx,
    mo.mod_dt,
    ste_s.state_cd,
    group_concat(distinct osr.status_cd order by osr.status_cd) as test,
    case group_concat(distinct osr.status_cd order by osr.status_cd)
        when 'Fulfilled' then 'Fulfilled'
        when 'Fulfilled,N/A' then 'Fulfilled'
        when 'N/A' then 'N/A'
        when 'Pending' then 'Pending'
        else 'In Process'
    end as restriction_status,
    max(osr.closed_dt) as restriction_update_dt,
    ot.milestone,
    ot.completed_dt as tracking_update_dt,
    dc.first_name,
    dc.last_name,
    inst.institution_name,
    order_search.products as products_ordered,
    mo.other_emails,
    mo.customer_label,
    mo.grant_numbers
from
    t_mmrrc_order mo
    join ste_state ste_s using(state_id)
    left join t_order_contact oc 
        on oc.mmrrc_order_oid=mo.mmrrc_order_oid and oc.role_cd='Recipient'
    left join t_distrib_cont_instn dci using(distrib_cont_instn_oid)
    left join t_institution inst using(institution_oid)
    left join t_distribution_contact dc using(distribution_contact_oid)
    left join t_order_tracking ot 
        on ot.mmrrc_order_oid=mo.mmrrc_order_oid 
            and ifnull(ot.order_tracking_oid, '0000-00-00')= ifnull(
                (
                select max(order_tracking_oid) 
                from t_order_tracking ot3 
                where 
                    ot3.mmrrc_order_oid=mo.mmrrc_order_oid 
                    and ot3.completed_dt= (
                            select max(completed_dt) 
                            from t_order_tracking ot2 
                            where ot2.mmrrc_order_oid=mo.mmrrc_order_oid
                        )
                ), '0000-00-00')
    left join t_order_strain_restriction osr 
        on osr.mmrrc_order_oid = mo.mmrrc_order_oid
    left join order_search on order_search.mmrrc_order_oid=mo.mmrrc_order_oid
group by
    mo.mmrrc_order_oid
LIMIT 0, 5

无论限制如何，此查询都需要 10 多秒才能运行。无限制运行时，共有5727个结果，运行时间为10.624秒。

使用“LIMIT 0, 5”需要 18.47 秒。

我知道有一堆连接和嵌套选择，这就是它这么慢的原因。关于如何在不更改数据库结构的情况下优化它的任何想法？

MySQL版本:5.0.95

大多数表都有超过 10,000 条记录。

这个更简单的查询大约需要 9 秒:

select
    mo.mmrrc_order_oid,
    mo.completed_by_email,
    mo.completed_by_name,
    mo.completed_by_title,
    mo.order_submission_oid,
    mo.order_dt,
    mo.center_id,
    mo.po_num_tx,
    mo.mod_dt,
    dc.first_name,
    dc.last_name,
    inst.institution_name,
    order_search.products as products_ordered,
    mo.other_emails,
    mo.customer_label,
    mo.grant_numbers
from
    t_mmrrc_order mo
    join ste_state ste_s using(state_id)
    left join t_order_contact oc 
        on oc.mmrrc_order_oid=mo.mmrrc_order_oid and oc.role_cd='Recipient'
    left join t_distrib_cont_instn dci using(distrib_cont_instn_oid)
    left join t_institution inst using(institution_oid)
    left join t_distribution_contact dc using(distribution_contact_oid)
    left join t_order_strain_restriction osr 
        on osr.mmrrc_order_oid = mo.mmrrc_order_oid
    left join order_search on order_search.mmrrc_order_oid=mo.mmrrc_order_oid
group by mo.mmrrc_order_oid
limit 0,5

我想分组最能减慢它的速度。在这种情况下，不分组只需要 0.17 秒。任何帮助，将不胜感激。谢谢。

其他详细信息 - 这是 EXPLAIN 为我提供的第一个查询的内容: View Image

我发现 order_search 是导致大部分速度下降的 View 。 View 的查询是:

SELECT 
    t_oi.mmrrc_order_oid AS mmrrc_order_oid,
    group_concat(t_im.icc_item_code separator ',') AS products
FROM 
    t_order_item t_oi
    JOIN t_item_master t_im on t_oi.item_master_oid = t_im.item_master_oid
    JOIN t_strain_archive on t_im.strain_archive_oid = t_strain_archive.strain_archive_oid
WHERE t_oi.item_status_cd IN (_utf8'Active',_utf8'Modified')
GROUP BY t_oi.mmrrc_order_oid
ORDER BY t_im.icc_item_code

Answer 1

只是假设你没有索引列，所以我为你的列创建了一些索引，这会对你有帮助，而且还有很多列需​​要索引，就像在你的连接条件中一样，你应该在那个列上应用这个操作，以便更好地执行

ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamemmrrc_order_oid` (`mmrrc_order_oid`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamecompleted_by_email` (`completed_by_email`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamecompleted_by_name` (`completed_by_name`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamecompleted_by_title` (`completed_by_title`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnameorder_submission_oid` (`order_submission_oid`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnameorder_dt` (`order_dt`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamecenter_id` (`center_id`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamepo_num_tx` (`po_num_tx`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamemod_dt` (`mod_dt`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnameother_emails` (`other_emails`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamecustomer_label` (`customer_label`);
ALTER TABLE `t_mmrrc_order` ADD INDEX `Indexnamegrant_numbers` (`grant_numbers`);
ALTER TABLE `t_distribution_contact ` ADD INDEX `Indexnamefirst_name` (`first_name`);
ALTER TABLE `t_distribution_contact ` ADD INDEX `Indexnamelast_name` (`last_name`);
ALTER TABLE `order_search` ADD INDEX `Indexnameproducts` (`products`);