sql - 新手的 MySQL 查询优化和 EXPLAIN

Question

我已经使用数据库很长时间了，但我对查询优化还很陌生。我有以下查询(其中一些是代码生成的):

SELECT DISTINCT COALESCE(gi.start_time, '') start_time,
COALESCE(b.name, '') bank,
COALESCE(a.id, '') account_id,
COALESCE(a.account_number, '') account_number,
COALESCE(at.code, '') account_type,
COALESCE(a.open_date, '') open_date,
COALESCE(a.interest_rate, '') interest_rate,
COALESCE(a.maturity_date, '') maturity_date,
COALESCE(a.opening_balance, '') opening_balance,
COALESCE(a.has_e_statement, '') has_e_statement,
COALESCE(a.has_bill_pay, '') has_bill_pay,
COALESCE(a.has_overdraft_protection, '') has_overdraft_protection,
COALESCE(a.balance, '') balance,
COALESCE(a.business_or_personal, '') business_or_personal,
COALESCE(a.cumulative_balance, '') cumulative_balance,
COALESCE(c.customer_number, '') customer_number,
COALESCE(c.social_security_number, '') social_security_number,
COALESCE(c.name, '') customer_name,
COALESCE(c.phone, '') phone,
COALESCE(c.deceased, '') deceased,
COALESCE(c.do_not_mail, '') do_not_mail,
COALESCE(cdob.date_of_birth, '') date_of_birth,
COALESCE(ad.line1, '') line1,
COALESCE(ad.line2, '') line2,
COALESCE(ad.city, '') city,
COALESCE(s.name, '') state,
COALESCE(ad.zip, '') zip,
COALESCE(o.officer_number, '') officer_number,
COALESCE(o.name, '') officer_name,
COALESCE(po.line1, '') po_box,
COALESCE(po.city, '') po_city,
COALESCE(po_state.name, '') po_state,
COALESCE(po.zip, '') zip,
COALESCE(br.number, '') branch_number,
COALESCE(cd_type.code, '') cd_type,
COALESCE(mp.product_number, '') macatawa_product_number,
COALESCE(mp.product_name, '') macatawa_product_name,
COALESCE(pt.name, '') macatawa_product_type,
COALESCE(hhsc.name, '') harte_hanks_service_category,
COALESCE(mp.hoh_hierarchy, '') hoh_hierarchy,
COALESCE(cft.name, '') core_file_type,
COALESCE(oa.line1, '') original_address_line1,
COALESCE(oa.line2, '') original_address_line2,
COALESCE(uc.code, '') use_class
            FROM account a
            JOIN customer c ON a.customer_id = c.id
            JOIN officer o ON a.officer_id = o.id
            JOIN account_address aa ON aa.account_id = a.id
       LEFT JOIN account_po_box apb ON apb.account_id = a.id                
            JOIN address ad ON aa.address_id = ad.id
            JOIN original_address oa ON oa.address_id = ad.id
       LEFT JOIN address po ON apb.address_id = po.id
            JOIN state s ON s.id = ad.state_id
       LEFT JOIN state po_state ON po_state.id = po.state_id
       LEFT JOIN branch br ON a.branch_id = br.id
            JOIN account_import ai ON a.account_import_id = ai.id
            JOIN generic_import gi ON gi.id = ai.generic_import_id
            JOIN import_bundle ib ON gi.import_bundle_id = ib.id
            JOIN bank b ON b.id = ib.bank_id
       LEFT JOIN customer_date_of_birth cdob ON cdob.customer_id = c.id
       LEFT JOIN cd_type ON a.cd_type_id = cd_type.id
       LEFT JOIN account_macatawa_product amp ON amp.account_id = a.id
       LEFT JOIN macatawa_product mp ON mp.id = amp.macatawa_product_id
       LEFT JOIN product_type pt ON pt.id = mp.product_type_id
       LEFT JOIN harte_hanks_service_category hhsc
            ON hhsc.id = mp.harte_hanks_service_category_id
       LEFT JOIN core_file_type cft ON cft.id = mp.core_file_type_id
       LEFT JOIN use_class uc ON a.use_class_id = uc.id
       LEFT JOIN account_type at ON a.account_type_id = at.id

         WHERE 1
           AND gi.active = 1
           AND b.id = 8 AND ib.is_finished = 1

        ORDER BY a.id
           LIMIT 10

而且速度很慢。在我的开发服务器上运行大约需要一分钟，而在我的生产服务器上，那里有更多数据，我什至无法完成它。下面是一个 EXPLAIN 的样子:

http://i.stack.imgur.com/eR6lq.png

我知道 EXPLAIN 的基础知识。我知道对于 key 下的所有内容，我拥有 NULL 以外的东西是很好的。但我不知道，总的来说，我的查询有多少改进空间。我知道 Using temporary;在 Extra 下使用 filesort 是不好的，但我不知道该怎么做。

Answer 1

看起来您的大部分 JOIN 字段都没有索引。确保用作 JOIN 键的每个 字段在两个表上都有一个索引。

有 23 个连接，看起来只有 2 个相关索引，性能不佳是可以预料的。

没有索引可以引用，查询引擎会检查两个表中的每一行来比较它们，这显然是非常低效的。

编辑:

例如，在您的查询中有

在 a.customer_id = c.id 上加入客户 c

确保您在 a.customer_id AND customer.id 上有一个索引。在两个表(在 JOINed 字段上)都有一个索引将以指数方式加快查询速度。