MySQL查询优化和EXPLAIN为noob [英] MySQL query optimization and EXPLAIN for a noob

问题描述

我一直在使用数据库很长时间，但我是新的查询优化。我有以下查询（一些代码生成）：

  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，''）open_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 ，''）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，''）城市，
 COALESCE（s.name，''）州，
 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帐户a 
 JOIN客户c ON a.customer_id = c.id 
 JOIN主管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状态s ON s.id = ad.state_id 
 LEFT JOIN状态po_state ON po_state.id = po.state_id 
 LEFT JOIN分支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 ampAaccount_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 on 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 
  

慢。在我的dev服务器上运行一分钟，在我的生产服务器上，有更多的数据，我不能得到它甚至完成。以下是 EXPLAIN 的外观：

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

我知道基本知识 EXPLAIN 。我知道对于 key 下的所有内容，我都有其他 NULL 的东西。但我不知道，总体来说，我的查询有多大的改进空间。我知道使用临时;使用 下的filesort 是不好的，但我不知道该怎么办。

解决方案

看起来你的大多数 JOIN 字段没有索引。请确保您用作 JOIN 键的每个字段在两个表上都有索引。

有23个连接，看起来只有2个相关索引，可以预期性能会下降。

索引到引用，查询引擎检查两个表中的每一行以进行比较，这显然是非常低效的。

edit： p>

例如，在您的查询中有

JOIN customer c ON a.customer_id = c.id

确保您的索引位于 a.customer_id AND customer.id 。在两个表上的索引（在 JOIN ed字段）将以指数加速查询。

I've been working with databases for a long time but I'm new to query optimization. I have the following query (some of it code-generated):

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

And it's pretty slow. On my dev server it takes about a minute to run and on my production server, where there's more data, I can't get it to even finish. Here's what an EXPLAIN looks like:

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

I know the basics of EXPLAIN. I know that it's good that I have something other than NULL for everything under key. But I don't know, overall, how much room for improvement my query has. I do know that Using temporary; Using filesort under Extra is bad, but I have no idea what to do about it.

解决方案

It looks like you don't have indexes on most of your JOIN fields. Make sure every field that you use as a JOIN key has an index on both tables.

With 23 joins and what looks like only 2 relevant indexes, poor performance can be expected.

With no index to reference, the query engine is checking every row in both tables to compare them, which is obviously very inefficient.

edit:

For example, in your query you have

JOIN customer c ON a.customer_id = c.id

Make sure you have an index on a.customer_id AND customer.id. Having an index on both tables (on the JOINed fields) will exponentially speed up the query.

这篇关于MySQL查询优化和EXPLAIN为noob的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！