php - 加快 MYSQL LARGE 查询速度

Question

我遇到一个非常大的 MYSQL 查询的问题，该查询处理显示记录的时间超过 10 分钟。

查询如下所示:

SELECT SQL_CALC_FOUND_ROWS * FROM (
        SELECT 't-0' as typo, sh_inquiry.id as ref, sh_inquiry.pid as pid, partner, sh_inquiry.order, if(p_status IS NULL OR p_status = 0,'not paid','paid') as payment, p_voucher, country, city, delivery_date, sh_inquiry.amount as c_amount, currency as c_currency, NULL as s_amount, NULL as s_currency, IF(confirmed=0,if(delivered=0,"not confirmed","delivered"),if(delivered=0,"confirmed","delivered")) as conf
        FROM   sh_inquiry
        LEFT JOIN sh_orders ON sh_inquiry.id = sh_orders.i_id
                LEFT JOIN sh_partners ON sh_partners.pid = sh_inquiry.pid
                LEFT JOIN sh_currency ON sh_currency.id = sh_inquiry.curr_id
                LEFT JOIN sh_country ON sh_country.id = sh_inquiry.cid
                LEFT JOIN sh_debts ON sh_inquiry.id = sh_debts.i_id
        WHERE  sh_inquiry.del = 0 AND sh_inquiry.type = 1 AND sh_orders.del = 0
        UNION
                SELECT 't-1' as typo, sh_orders.i_id as ref, sid as pid, partner, sh_inquiry.order, if(p_status IS NULL OR p_status = 0,'not paid','paid') as payment, p_voucher, country, city, delivery_date, NULL as c_amount, NULL as c_currency, sh_orders.amount as s_amount, currency as s_currency, IF(confirmed=0,if(delivered=0,'not confirmed','delivered'),if(delivered=0,'confirmed','delivered')) as conf FROM sh_orders
                LEFT JOIN sh_partners ON sh_partners.pid = sh_orders.sid
                LEFT JOIN sh_currency ON sh_currency.id = sh_orders.curr_id
                LEFT JOIN sh_inquiry ON sh_inquiry.id = sh_orders.i_id
                LEFT JOIN sh_country ON sh_country.id = sh_inquiry.cid
                LEFT JOIN sh_debts ON sh_orders.id = sh_debts.o_id
                WHERE sh_orders.del = 0 AND sh_inquiry.del = 0
        ) AS U

        ORDER BY typo ASC, delivery_date
                    asc
        LIMIT 0, 10

问题是我使用的 2 个表必须连接到一个表中(这 2 个表非常大)，这就是我使用 UNION 语句的原因。我正在 jquery 数据表服务器端页面中显示记录，因此限制显示的记录无济于事。

有人能想出一个关于加速这个查询的想法吗？如果您需要有关数据库结构的更多信息，请询问。

EXPLAIN 语句被触发:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra   
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    17630   Using filesort
2   DERIVED     sh_orders   ALL     i_id    NULL    NULL    NULL    8696    Using where
2   DERIVED     sh_inquiry  eq_ref  PRIMARY,id  PRIMARY     4   reur3918_sh.sh_orders.i_id  1   Using where
2   DERIVED     sh_partners     eq_ref  PRIMARY     PRIMARY     4   reur3918_sh.sh_inquiry.pid  1   
2   DERIVED     sh_currency     eq_ref  PRIMARY     PRIMARY     4   reur3918_sh.sh_inquiry.curr_id  1   
2   DERIVED     sh_country  eq_ref  PRIMARY     PRIMARY     4   reur3918_sh.sh_inquiry.cid  1   
2   DERIVED     sh_debts    ALL     NULL    NULL    NULL    NULL    18678   
3   UNION   sh_orders   ALL     i_id    NULL    NULL    NULL    8696    Using where
3   UNION   sh_partners     eq_ref  PRIMARY     PRIMARY     4   reur3918_sh.sh_orders.sid   1   
3   UNION   sh_currency     eq_ref  PRIMARY     PRIMARY     4   reur3918_sh.sh_orders.curr_id   1   
3   UNION   sh_inquiry  eq_ref  PRIMARY,id  PRIMARY     4   reur3918_sh.sh_orders.i_id  1   Using where
3   UNION   sh_country  eq_ref  PRIMARY     PRIMARY     4   reur3918_sh.sh_inquiry.cid  1   
3   UNION   sh_debts    ALL     NULL    NULL    NULL    NULL    18678   
NULL    UNION RESULT    <union2,3>  ALL     NULL    NULL    NULL    NULL    NULL    

对所有 ID 建立索引后，查询大约需要 133 秒..

Answer 1

谢谢大家的想法!实际上，运行 EXPLAIN 语句帮助我找到了没有定义 INDEX 的表。在查询中使用过的所有字段(主要是JOIN ON子句)添加INDEX后，运行时间减少到0.83秒