优化MySQL自连接查询

Question

我有包含重复行的c_regs表。我已经为form_number和property_name列创建了索引。不幸的是，这个查询仍然要花很长时间才能完成，特别是添加了t10和t11联接。有没有优化它的方法？谢谢。

select 
    ifnull(x.form_datetime,'') reg_date,
    ifnull(x.property_value,'') amg_id,
    x.form_number,
    x.form_name,
    x.form_version,
    ifnull(t1.property_value,'') first_name,
    ifnull(t2.property_value,'') last_name,
    ifnull(t3.property_value,'') address, 
    ifnull(t4.property_value,'') address_2,
    ifnull(t5.property_value,'') city,
    ifnull(t6.property_value,'') state_code,
    ifnull(t7.property_value,'') zip,
    ifnull(t8.property_value,'') phone,
    ifnull(t9.property_value,'') email,
    ifnull(t10.property_value,'') registrant_type,
    t11.property_value auth_type_code
from 
    (select distinct form_datetime, form_number, form_name, form_version, property_value  from c_regs where property_name = 'field.frm_personID') as x
    inner join (select distinct * from c_regs) as t1 on t1.form_number = x.form_number and t1.property_name = 'field.frm_firstName'
    inner join (select distinct * from c_regs) as t2 on t2.form_number = x.form_number and t2.property_name = 'field.frm_lastName'
    inner join (select distinct * from c_regs) as t3 on t3.form_number = x.form_number and t3.property_name = 'field.frm_address'
    left join (select distinct * from c_regs) as t4 on t4.form_number = x.form_number and t4.property_name = 'field.frm_address2'
    inner join (select distinct * from c_regs) as t5 on t5.form_number = x.form_number and t5.property_name = 'field.frm_city'
    inner join (select distinct * from c_regs) as t6 on t6.form_number = x.form_number and t6.property_name = 'field.frm_state'
    inner join (select distinct * from c_regs) as t7 on t7.form_number = x.form_number and t7.property_name = 'field.frm_zip'
    inner join (select distinct * from c_regs) as t8 on t8.form_number = x.form_number and t8.property_name = 'field.frm_phone'
    inner join (select distinct * from c_regs) as t9 on t9.form_number = x.form_number and t9.property_name = 'field.frm_emailAddress'
    left join (select distinct * from c_regs) as t10 on t10.form_number = x.form_number and t10.property_name = 'field.frm_youAre'
    inner join (select distinct * from c_regs) as t11 on t11.form_number = x.form_number and t11.property_name = 'field.frm_authType'
;

Answer 1

您不应该一直使用SELECT DISTINCT。请记住，如果选择列表中有任何唯一的约束，那么DISTINCT必然是不操作的，所以可能没有必要。如果存在重复，DISTINCT是昂贵的，因为它对表进行排序，以便将重复的数据排列在一起以解除欺骗。

对于这类数据，您也不应该做大量的自联接。自联接中的每个子查询都在读取整个表。

SELECT form_number,
  MAX(form_datetime) AS reg_date,
  MAX(form_name) AS form_name,
  MAX(form_version) AS form_version,
  MAX(CASE property_name WHEN 'field.frm_personID' THEN property_value END) AS amg_id,
  MAX(CASE property_name WHEN 'field.frm_firstName' THEN property_value END) AS first_name,
  MAX(CASE property_name WHEN 'field.frm_lastName' THEN property_value END) AS last_name,
  MAX(CASE property_name WHEN 'field.frm_address' THEN property_value END) AS address,
  MAX(CASE property_name WHEN 'field.frm_address2' THEN property_value END) AS address_2,
  MAX(CASE property_name WHEN 'field.frm_city' THEN property_value END) AS city,
  MAX(CASE property_name WHEN 'field.frm_state' THEN property_value END) AS state_code,
  MAX(CASE property_name WHEN 'field.frm_zip' THEN property_value END) AS zip,
  MAX(CASE property_name WHEN 'field.frm_phone' THEN property_value END) AS phone,
  MAX(CASE property_name WHEN 'field.frm_emailAddress' THEN property_value END) AS email,
  MAX(CASE property_name WHEN 'field.frm_youAre' THEN property_value END) AS registrant_type,
  MAX(CASE property_name WHEN 'field.frm_authType' THEN property_value END) AS auth_type_code
FROM c_regs
GROUP BY form_number;

说明：GROUP BY将使给定form_number的所有行被视为一个组，并且结果将每个组有一行。

GROUP BY中未命名的所有其他列都必须在分组函数中。我选择了MAX()。我假设每个组的表单日期时间、名称和版本应该只有一个不同的值。

对于这些属性，我们在MAX()函数中放置一个表达式，仅在属性具有特定值的行上返回该值。在其他行中，表达式为NULL，MAX()将忽略该表达式。

这样，您就可以得到您想要的结果，而不必执行任何自联接或不同的修饰符。查询只扫描一次表，它应该更快。

Answer 2

BK认为很多自我连接都是有害的，这是误导性的。

考虑一个EAV数据集，该数据集由10,000个实体组成，每个实体具有以下12个属性：

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(entity INT NOT NULL
,attribute INT NOT NULL
,value INT NOT NULL
,PRIMARY KEY(entity,attribute)
);

INSERT INTO my_table VALUES
(1,101,RAND()*100),
(1,102,RAND()*100),
(1,103,RAND()*100),
(1,104,RAND()*100),
(1,105,RAND()*100),
(1,106,RAND()*100),
(1,107,RAND()*100),
(1,108,RAND()*100),
(1,109,RAND()*100),
(1,110,RAND()*100),
(1,111,RAND()*100),
(1,112,RAND()*100);

使用这个初始种子，我可以使用一个整数表(0-9)快速填充表的其余部分.

INSERT IGNORE INTO my_table SELECT i4.i*1000+i3.i*100+i2.i*10+i1.i+1, attribute, RAND()*100 FROM my_table,ints i1, ints i2, ints i3, ints i4;

比尔的询问..。

SELECT SQL_NO_CACHE a.entity
     , MAX(CASE WHEN attribute = 101 THEN value END) x101
     , MAX(CASE WHEN attribute = 102 THEN value END) x102
     , MAX(CASE WHEN attribute = 103 THEN value END) x103
     , MAX(CASE WHEN attribute = 104 THEN value END) x104
     , MAX(CASE WHEN attribute = 105 THEN value END) x105
     , MAX(CASE WHEN attribute = 106 THEN value END) x106
     , MAX(CASE WHEN attribute = 107 THEN value END) x107
     , MAX(CASE WHEN attribute = 108 THEN value END) x108
     , MAX(CASE WHEN attribute = 109 THEN value END) x109
     , MAX(CASE WHEN attribute = 110 THEN value END) x110
     , MAX(CASE WHEN attribute = 111 THEN value END) x111
     , MAX(CASE WHEN attribute = 112 THEN value END) x112
  FROM my_table a
 GROUP 
    BY a.entity;

+--------+------+------+------+------+------+------+------+------+------+------+------+------+
| entity | x101 | x102 | x103 | x104 | x105 | x106 | x107 | x108 | x109 | x110 | x111 | x112 |
+--------+------+------+------+------+------+------+------+------+------+------+------+------+
|      1 |   78 |    8 |    4 |   95 |   66 |   43 |   16 |   51 |    9 |   89 |   20 |   33 |
...
|   9998 |   61 |   72 |   67 |   20 |   23 |   10 |   31 |   37 |   69 |   18 |   24 |   32 |
|   9999 |   67 |   91 |   32 |   58 |   77 |   81 |   61 |   22 |   75 |   65 |   91 |   42 |
|  10000 |   52 |   38 |   56 |   32 |   14 |   77 |   10 |   99 |   70 |   70 |   82 |   13 |
+--------+------+------+------+------+------+------+------+------+------+------+------+------+    
10000 rows in set (0.20 sec)

另一个选择..。

SELECT SQL_NO_CACHE a.entity
     , a.value x101
     , b.value x102
     , c.value x103
     , d.value x104
     , e.value x105
     , f.value x106
     , g.value x107
     , h.value x108
     , i.value x109
     , j.value x110
     , k.value x111
     , l.value x112
  FROM my_table a
  LEFT JOIN my_table b ON b.entity = a.entity  AND b.attribute = 102
  LEFT JOIN my_table c ON c.entity = a.entity  AND c.attribute = 103
  LEFT JOIN my_table d ON d.entity = a.entity  AND d.attribute = 104
  LEFT JOIN my_table e ON e.entity = a.entity  AND e.attribute = 105
  LEFT JOIN my_table f ON f.entity = a.entity  AND f.attribute = 106
  LEFT JOIN my_table g ON g.entity = a.entity  AND g.attribute = 107
  LEFT JOIN my_table h ON h.entity = a.entity  AND h.attribute = 108
  LEFT JOIN my_table i ON i.entity = a.entity  AND i.attribute = 109
  LEFT JOIN my_table j ON j.entity = a.entity  AND j.attribute = 110
  LEFT JOIN my_table k ON k.entity = a.entity  AND k.attribute = 111
  LEFT JOIN my_table l ON l.entity = a.entity  AND l.attribute = 112
  WHERE a.attribute = 101;

+--------+------+------+------+------+------+------+------+------+------+------+------+------+
| entity | x101 | x102 | x103 | x104 | x105 | x106 | x107 | x108 | x109 | x110 | x111 | x112 |
+--------+------+------+------+------+------+------+------+------+------+------+------+------+
|      1 |   78 |    8 |    4 |   95 |   66 |   43 |   16 |   51 |    9 |   89 |   20 |   33 |
...
|   9998 |   61 |   72 |   67 |   20 |   23 |   10 |   31 |   37 |   69 |   18 |   24 |   32 |
|   9999 |   67 |   91 |   32 |   58 |   77 |   81 |   61 |   22 |   75 |   65 |   91 |   42 |
|  10000 |   52 |   38 |   56 |   32 |   14 |   77 |   10 |   99 |   70 |   70 |   82 |   13 |
+--------+------+------+------+------+------+------+------+------+------+------+------+------+
10000 rows in set (0.23 sec)

所以，比尔的查询要快一些。但是，一旦您减少了所寻找的实体的数量(同时保持相同数量的属性-所以相同数量的联接)，替代查询就可以超过Bill的查询，使其接近相同的范围……

比尔对WHERE a.entity <= 5000的查询

  |   4998 |   59 |   55 |   93 |   48 |   72 |   32 |   38 |   36 |    6 |   82 |   23 |   62 |
  |   4999 |   23 |   10 |   11 |   29 |   69 |   67 |   92 |   72 |   25 |   49 |   79 |   48 |
  |   5000 |   39 |   86 |   77 |    0 |   30 |   38 |   48 |   54 |    9 |   97 |   25 |   54 |
  +--------+------+------+------+------+------+------+------+------+------+------+------+------+
 5000 rows in set (0.12 sec)

添加WHERE a.entity <= 5000的替代方案

  |   4998 |   59 |   55 |   93 |   48 |   72 |   32 |   38 |   36 |    6 |   82 |   23 |   62 |
  |   4999 |   23 |   10 |   11 |   29 |   69 |   67 |   92 |   72 |   25 |   49 |   79 |   48 |
  |   5000 |   39 |   86 |   77 |    0 |   30 |   38 |   48 |   54 |    9 |   97 |   25 |   54 |
  +--------+------+------+------+------+------+------+------+------+------+------+------+------+
 5000 rows in set (0.11 sec)

因此，实际上并不是连接的数量，而是索引的频繁使用，使得慢速查询和快速查询之间的区别。

Answer 3

你不需要所有这些加入。通过我的优化，数据将以行形式返回，而不是按列返回。

(我没有运行这个，所以先测试一下)

SELECT 
    ifnull(x.form_datetime,'') reg_date,
    ifnull(x.property_value,'') amg_id,
    x.form_number,
    x.form_name,
    x.form_version,
    x.property_name,
    x.property_value
FROM c_regs x
WHERE x.property_name IN (
    'field.frm_firstName',
    'field.frm_lastName',
    'field.frm_address',
    ...
)
AND x.form_number = 'the form id'
GROUP BY x.form_number, x.property_name
ORDER BY x.form_number ASC;

只有当您想要一个特定的表单，而不是所有的表单时，AND才是必需的。(我建议)

也要问你自己这个问题:你是否需要在这种情况下有字段名？您可以使用my query作为子查询，然后像以前一样将每个字段合并为列，而不需要其他联接。