子查询单独运行非常快，但加入时非常慢

超立方体解决了这个问题。子查询是完全没有必要的，整个事情都可以通过简单的连接来工作。不过，MySQL 的优化器无法使用我原来的查询仍然很奇怪。有关问题和许多详细信息，请参见下文。加上我问题底部的完整解决方案。它基于 ypercube 的回答。

每个子查询都非常快，不到 1 秒。加入了 5-6 个子查询（一些LEFT，一些INNER），时间迅速增加到 400 秒。

我用于测试的整体查询仅返回 441 行。

我尝试将每个子查询放在“CREATE TABLE”查询中。每一个都在不到 1 秒的时间内完成。然后我使用那些新创建的表重新执行了外部查询，它也运行在不到 1 秒的时间内。所以连接没有实际问题。id我为我创建的表添加了索引。所有表都在匹配id=上连接id。

如何让 MySQL 高效地执行查询？我必须使用临时表吗？我已经编写了一堆 PHP 代码来将多个子查询连接放在一起，所以如果可能的话，我宁愿弄清楚如何使它工作。

我尝试使用“STRAIGHT_JOIN”关键字并删除外部ORDER BY. 这将查询时间减少到 90 秒。但我最多应该得到 1 秒。

我试过STRAIGHT_JOIN了ORDER BY，花了 235 秒。所以看起来外部ORDER BY是一个主要的性能问题。

编辑：

使用临时表进行测试。查询运行非常快。但是必须有一种方法可以让 mysql 通过 JOINS 快速完成。

此外，慢查询日志显示：

Rows_examined: 484006914

4.84 亿行看起来像一个笛卡尔积。为什么要检查这么多行？

查询具有以下结构：

SELECT t0.`id`, t1.`length`, t2.`height`, t3.`family`
FROM
`products` t0
INNER JOIN
(
SELECT t1.`id`, t2.`value` AS `length`
FROM `products` t1
INNER JOIN `product_eav_decimal` t2
ON t1.`id` = t2.`product_id`
WHERE t2.`attribute_id` = 91
AND t2.`value` BETWEEN 15 AND 35
) t1

ON t0.`id` = t1.`id`

LEFT JOIN
(
SELECT t1.`id`, t2.`value` AS `height`
FROM `products` t1
INNER JOIN `product_eav_decimal` t2
ON t1.`id` = t2.`product_id`
WHERE t2.`attribute_id` = 80
# no other conditions
) t2
ON t0.`id` = t2.`id`

INNER JOIN
(
.
.
.
) t6
ON t0.`id` = t6.`id`
ORDER BY t0.`id` ASC

...等 LEFT JOINS 用于子查询中除 attribute_id 之外的其他条件。当有其他条件时使用 INNER JOIN。这将创建一个有效的搜索结果。查询有效，只需要 400 秒而不是 0.04 秒。

如果没有人知道如何使 JOIN 语法起作用，那么我将使用临时表，因为这似乎有效。

表格：

1.) 产品

CREATE TABLE `products` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `sku` varchar(127) NOT NULL COMMENT '3char vencode + model',
 `model` varchar(127) NOT NULL,
 `vendor_id` int(11) DEFAULT NULL,
 `updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `sku` (`sku`),
 KEY `model` (`model`),
 KEY `vendor_id` (`vendor_id`),
 CONSTRAINT `FK1` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=153282 DEFAULT CHARSET=utf8

2.) 小数

CREATE TABLE `product_eav_decimal` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) NOT NULL,
 `attribute_id` int(11) DEFAULT NULL,
 `value` decimal(11,3) DEFAULT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `natural_key` (`product_id`,`attribute_id`,`value`),
 UNIQUE KEY `product_id_2` (`product_id`,`attribute_id`),
 KEY `last_update` (`last_update`),
 KEY `product_id` (`product_id`),
 KEY `attribute_id` (`attribute_id`),
 KEY `value` (`value`),
 CONSTRAINT `FK1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK2` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=370772 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

3.) varchar（引用另一个表，values_varchar实际 varchar 值的表）

CREATE TABLE `product_eav_varchar` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `product_id` int(11) DEFAULT NULL,
 `attribute_id` int(11) DEFAULT NULL,
 `value_id` int(11) DEFAULT NULL,
 `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `natural_key` (`product_id`,`attribute_id`,`value_id`),
 KEY `last_update` (`last_update`),
 KEY `product_id` (`product_id`),
 KEY `value_id` (`value_id`),
 KEY `attribute_id` (`attribute_id`),
 CONSTRAINT `FK1` FOREIGN KEY (`value_id`) REFERENCES `values_varchar` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `FK3` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=86049 DEFAULT CHARSET=utf8 COLLATE=utf8_bin

改编自 ypercube 的回答：

SELECT t0.id, 
       t1.`value` AS length, 
       t2.`value` AS height, 
       t3.`value` AS family,
       t5.`value` AS type
FROM
  products t0

INNER JOIN # INNER used when search criteria
# length (only searched values)
  product_eav_decimal t1
    ON  t1.product_id = t0.id  
    AND t1.attribute_id = 91
    AND t1.`value` BETWEEN 15 AND 35 # search criteria

LEFT JOIN # LEFT used when no search criteria
# height (all, including blank/null)
  product_eav_decimal t2
    ON  t2.product_id = t0.id  
    AND t2.attribute_id = 80  

LEFT JOIN  # LEFT - no search critera
# family - varchar type requires extra join to values table
  product_eav_varchar t3
    ON  t3.product_id = t0.id  
    AND t3.attribute_id = 77
LEFT JOIN # LEFT join to values table matches eav table join
values_varchar t4
    ON t3.value_id = t4.id
# search criteria would be here. see next

INNER JOIN # INNER - search criteria below
# type - varchar requires extra join, see below
  product_eav_varchar t5
    ON t5.product_id = t0.id
    AND t5.attribute_id = 76
INNER JOIN # INNER join to values table matches eav table join
values_varchar t6
    ON t5.value_id = t6.id
    # search criteria
    AND (t6.value LIKE "%sofa%" COLLATE utf8_general_ci OR t6.value LIKE "%chair%" COLLATE utf8_general_ci)

ORDER BY t0.id ASC;

查询有效。它在几毫秒内运行。如果给出了搜索词或范围限制，它只返回匹配的结果，使用 INNER JOIN。在没有条件的情况下，它使用 LEFT JOIN 来返回任何值（包括 NULL/空白）。

2014 年 8 月更新 - 现在表中有 400-500,000 行，products上面使用的查询样式仍然运行得很快。似乎连接比 MySQL 中的子查询快得多。