MySQL 永无止境 'Copying to tmp table' 与 db View 和 LEFT JOIN

Question

我在 MySQL 上遇到性能问题。数据库包含大约40万条记录，站点在Drupal上运行，所有列都有索引。不幸的是，有些广告没有所有数据，所以我需要使用 LEFT JOIN 而不是 INNER JOIN。

查看:

CREATE VIEW cases_view AS SELECT 
    n.nid AS nid,
        n.created AS created,
        n.type AS type,
        n.status AS status,
        i.field_adpersonid_value AS field_adpersonid_value,
        f.field_adpersonname_value AS field_adpersonname_value,
        l.field_adpersonfamilyname_value AS field_adpersonfamilyname_value,
        b.field_adpersondateofborn_value AS field_adpersondateofborn_value,
        d.field_adpersondateofdied_value AS field_adpersondateofdied_value,
        p.field_adnewspapergroupname_value AS field_adnewspapergroupname,
        c.field_sbfcompanyname_value AS field_sbfcompanyname_value
FROM
    node n
LEFT JOIN field_data_field_adpersonname f ON (f.entity_id = n.nid)
JOIN field_data_field_adpersonid i ON (i.entity_id = n.nid)
LEFT JOIN field_data_field_adpersonfamilyname l ON (l.entity_id = n.nid)
LEFT JOIN field_data_field_adpersondateofborn b ON (b.entity_id = n.nid)
LEFT JOIN field_data_field_adpersondateofdied d ON (d.entity_id = n.nid)
LEFT JOIN field_data_field_adnewspapergroupname p ON (p.entity_id = n.nid)
LEFT JOIN field_data_field_sbfcompanyname c ON (c.entity_id = n.nid)
WHERE
   n.type = 'advertisement' AND n.status = 1 AND  `p`.`field_adnewspapergroupname_value` <> 'Times Newspaper'

然后我使用以下查询在此 View (php) 中搜索数据:

SELECT DISTINCT v.field_adpersonname_value AS field_adpersonname_value, v.field_adpersonid_value AS field_adpersonid_value, v.field_adpersonfamilyname_value AS field_adpersonfamilyname_value, v.field_adpersondateofborn_value AS field_adpersondateofborn_value, v.field_adpersondateofdied_value AS field_adpersondateofdied_value, v.field_sbfcompanyname_value AS field_sbfcompanyname_value
FROM 
cases_view v
WHERE  (v.field_adpersonname_value LIKE 'Sample name%' ESCAPE '\\') AND (v.field_adpersonfamilyname_value LIKE '%' ESCAPE '\\') AND (v.field_adpersondateofborn_value LIKE '%' ESCAPE '\\') AND (v.field_adpersondateofdied_value LIKE '%' ESCAPE '\\') 
ORDER BY v.field_adpersonfamilyname_value ASC, v.field_adpersondateofdied_value DESC
LIMIT 20 OFFSET 0

我的数据库配置:

max_connections = 300
connect_timeout = 100
max_user_connections = 300
key_buffer = 256M
query_cache_size = 96MB
query_cache_limit = 4MB
table_cache = 1800
table_definition_cache = 1800
sort_buffer_size = 32M
myisam_sort_buffer_size = 32M
tmp_table_size = 64MB
join_buffer_size = 4M
wait_timeout = 60
innodb_buffer_pool_size = 2G
innodb_log_file_size = 1G
max_heap_table_size = 128M
tmp_table_size = 128M
max_allowed_packet = 32M
thread_cache_size = 128

在我启动 mysql 服务器站点后，搜索速度很快，但几个小时后，“复制到临时表”开始复制越来越多的数据，服务器变得越来越慢，最终崩溃。系统拥有 8GB RAM 和双核。

请帮忙!

Answer 1

我认为在这种特殊情况下，首先查找要选择的行的 ID，然后添加其他表可能会更快。玩一下。

select <fields>
from node n
<joins>
where nid in 
  ( select nid 
    from node 
    where n.status = 1 
      and n.type = 'advertisement
  )

另一种解决方案，特别是当您只需要另一个表中的一个字段时，是使用子选择，这听起来很慢，但有时比联接更快:

select n.fields,
  (select field_adpersonid_value from field_data_field_adpersonid i ON i.entity_id = n.nid) as field_adpersonid_value
from node n
where .. etc

你也可以尝试Explain来看看哪里慢了。