mysql - Sql 查询填充/tmp 并需要几分钟才能运行

Question

我有一个查询，它不会对大量数据进行操作(恕我直言)，但需要花费几分钟(5-10)来执行，并最终在执行时填充/tmp 空间(最多需要 20GB)。一旦完成，空间就会再次释放。

查询如下:

SELECT c.name, count(b.id), c.parent_accounting_reference, o.contract, a.contact_person, a.address_email, a.address_phone, a.address_fax, concat(ifnull(concat(a.description, ', '),''), ifnull(concat(a.apt_unit, ', '),''), ifnull(concat(a.preamble, ', '),''), ifnull(addr_entered,''))                FROM
                booking b
            join visit v on (b.visit_id = v.id)
            join super_booking s on (v.super_booking_id = s.id)
            join customer c on (s.customer_id = c.id)
            join address a on (a.customer_id = c.id)
            join customer_number cn on (cn.customer_numbers_id = c.id)
            join number n on (cn.number_id = n.id)
            join customer_email ce on (ce.customer_emails_id = c.id)
            join email e on (ce.email_id = e.id)
            left join organization o on (o.accounting_reference = c.parent_accounting_reference)
            left join address_type at on (a.type_id = at.id and at.name_key = 'billing')
            where s.company_id = 1
            and v.expected_start_date between '2015-01-01 00:00:00' and '2015-02-01 00:00:00'
            group by s.customer_id
            order by count(b.id) desc

其解释计划是:

+----+-------------+-------+--------+--------------------------------------------------------------+---------------------+---------+--------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                | key                 | key_len | ref                                  | rows  | Extra                                        |
+----+-------------+-------+--------+--------------------------------------------------------------+---------------------+---------+--------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | s     | ref    | PRIMARY,FKC4F8739580E01B03,FKC4F8739597AD73B1                | FKC4F8739580E01B03  | 9       | const                                | 74088 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ce    | ref    | FK864C4FFBAF6458E3,customer_emails_id,customer_emails_id_2   | customer_emails_id  | 9       | id_dev.s.customer_id                 |     1 | Using where                                  |
|  1 | SIMPLE      | cn    | ref    | FK530F62CA30E87991,customer_numbers_id,customer_numbers_id_2 | customer_numbers_id | 9       | id_dev.ce.customer_emails_id         |     1 | Using where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY                                                      | PRIMARY             | 8       | id_dev.s.customer_id                 |     1 |                                              |
|  1 | SIMPLE      | e     | eq_ref | PRIMARY                                                      | PRIMARY             | 8       | id_dev.ce.email_id                   |     1 | Using index                                  |
|  1 | SIMPLE      | n     | eq_ref | PRIMARY                                                      | PRIMARY             | 8       | id_dev.cn.number_id                  |     1 | Using index                                  |
|  1 | SIMPLE      | v     | ref    | PRIMARY,FK6B04D4BEF4FD9A                                     | FK6B04D4BEF4FD9A    | 8       | id_dev.s.id                          |     1 | Using where                                  |
|  1 | SIMPLE      | b     | ref    | FK3DB0859E1684683                                            | FK3DB0859E1684683   | 8       | id_dev.v.id                          |     1 | Using index                                  |
|  1 | SIMPLE      | o     | ref    | org_acct_reference                                           | org_acct_reference  | 767     | id_dev.c.parent_accounting_reference |     1 |                                              |
|  1 | SIMPLE      | a     | ref    | FKADDRCUST,customer_address_idx                              | FKADDRCUST          | 9       | id_dev.c.id                          |   256 | Using where                                  |
|  1 | SIMPLE      | at    | eq_ref | PRIMARY                                                      | PRIMARY             | 8       | id_dev.a.type_id                     |     1 |                                              |
+----+-------------+-------+--------+--------------------------------------------------------------+---------------------+---------+--------------------------------------+-------+----------------------------------------------+

它似乎使用了正确的索引等，因此我无法理解为什么大量使用/tmp 和很长的执行时间。

Answer 1

您的查询使用临时表，您可以通过 EXPLAIN 结果中的 Using tempor; 注释看到该表。您的 MySQL 设置可能配置为使用/tmp 来存储临时表。

如果您想进一步优化查询，您可能应该调查为什么需要临时表。最好的方法是逐渐简化查询，直到找出导致查询的原因。在这种情况下，可能只是需要处理的行数，因此如果您确实需要所有这些数据，您可能也需要临时表。但不要放弃优化我的帐户；)

顺便说一句，您可能需要研究 COALESCE 来处理 NULL 值。