mysql - 优化对另一个表中可选字段的查询

Question

我有一个名为 items 的 innodb 表，它为一个电子商务网站提供支持。搜索系统允许您搜索可选/附加字段，以便您可以，例如仅搜索维修过的计算机或仅超过 2000 年的汽车。

这是通过名为 items_fields 的附加表完成的。它的设计非常简单:

+------------+------------------------+------+-----+---------+----------------+
| id         | int(11)                | NO   | PRI | NULL    | auto_increment |
| field_id   | int(11)                | NO   | MUL | NULL    |                |
| item_id    | int(11)                | NO   | MUL | NULL    |                |
| valueText  | varchar(500)           | YES  |     | NULL    |                |
| valueInt   | decimal(10,1) unsigned | YES  |     | NULL    |                |
+------------+------------------------+------+-----+---------+----------------+

还有一个名为fields 的表，它只包含字段名称和类型。

返回搜索结果的主要查询如下:

SELECT items...   
FROM items   
WHERE items... AND (  
      SELECT count(id)  
      FROM items_fields    
      WHERE items_fields.field_id = "59" AND items_fields.item_id = items.id AND
      items_fields.valueText = "Damaged")>0  
ORDER by ordering desc LIMIT 35;

在大规模(每天只有 400 万多个搜索查询)上，我需要进一步优化这些高级搜索。目前，平均高级搜索查询大约需要 100 毫秒。

我怎样才能加快这个查询？您还有其他优化建议吗？两个表都是 innodb，服务器堆栈绝对很棒，但是我仍然需要解决这个查询:)

Answer 1

为 (item_id, field_id, valueText) 添加索引，因为这是您的搜索。

去掉内部选择!!!高达 5.5 的 MySQL 无法优化带有内部选择的查询。据我所知，MariaDB 5.5 是目前唯一支持内部选择优化的 MySQL 替代品。

 SELECT i.*, f2.* as damageCounter FROM items i  
    JOIN items_fields f ON f.field_id = 59
                       AND f.item_id = i.id
                       AND f.valueText = "Damaged"
   JOIN item_fields f2 ON f2.item_id = i.id
   ORDER by i.ordering desc 

   LIMIT 35;

第一个连接将限制返回的集合。第二个连接将获取满足第一个连接的项目的所有 item_fields。在第一个和最后一个连接之间，您可以添加更多连接条件，这些条件将根据附加点过滤掉结果。例如:

   SELECT i.*, f3.* as damageCounter FROM items i  
    JOIN items_fields f ON f.field_id = 59
                       AND f.item_id = i.id
                       AND f.valueText = "Damaged"
   JOIN items_fields f2 ON f2.field_id = 22
                       AND f2.item_id = i.id
                       AND f.valueText = "Green"
   JOIN item_fields f3 ON f3.item_id = i.id
   ORDER by i.ordering desc 

   LIMIT 35;

这将返回一个结果集，其中包含字段 59 的值为“已损坏”和字段 22 的值为“绿色”的所有项目及其所有项目字段。