MySql没有正确地优化查询

Question

我的桌子结构如下：

CREATE TABLE `sale_product_inventories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `size` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `tier_number` int(11) NOT NULL DEFAULT '1',
  `sale_product_pool_id` int(11) DEFAULT NULL,
  `inventory` int(11) NOT NULL,
  `in_cart_units` int(11) DEFAULT '0',
  `size_display_order` tinyint(4) NOT NULL DEFAULT '0',
  `last_updated_by` int(11) DEFAULT '0',
  `created_by` int(11) DEFAULT '0',
  `status` enum('active','inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQUE` (`sale_id`,`product_id`,`tier_number`,`size`,`sale_product_pool_id`)
) ENGINE=InnoDB AUTO_INCREMENT=92872 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

注:我有一个索引UNIQUE = sale_id，product_id，tier_number，size，sale_product_pool_id

当我运行这个查询时：

select * from sale_product_inventories 
where 
sale_id in (502,504)  and 
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql使用索引唯一，执行时间为0.7毫秒

但

当我运行这个查询时

select * from sale_product_inventories 
where 
(sale_id, product_id) in ((502,2),(502,1), (502,3),(502,4) ,(504,2) ,(504,3) )

MySql不使用唯一索引，执行时间为76毫秒。

Mysql : 5.5.27 InnoDB版本: 1.1.8

我的问题是，为什么mysql的行为是这样的。有人能帮我一下吗。

编辑：

我发现了这一点，所以我认为添加MySQL可能是有用的，通常不能在列上使用索引，除非在查询中隔离列。“隔离”列意味着它不应该是表达式的一部分，也不应该在查询中的函数中。

Answer 1

MySQL优化器不能以这种格式优化表达式：

WHERE (col_1,col_2) IN ((a,b),(c,d),(e,f))

这不是让索引正确的问题--它看起来只是没有实现。

优化器不明白这等同于.

WHERE (col_1,col_2) IN ((a,b)) 
   OR (col_1,col_2) IN ((c,d)) 
   OR (col_1,col_2) IN ((e,f)) 

..。或者..。

WHERE (col_1 = a AND col_2 = b)
   OR (col_1 = c AND col_2 = d)
   OR (col_1 = e AND col_2 = f)

Bug #35819是我最初在这篇文章中找到的，在这个职位上的评论中也提到了这一点。

不幸的是，直到我已经突破了新的MySQL 5.6中的优化跟踪并在其中运行了几个测试用例，我才找到它们。似乎可以肯定的是，如果5.6不能处理，那么以前的版本就无法处理它。

事实证明，MySQL 5.6确实无法处理它。"set in set of set“构造似乎根本不是优化器所能理解的。所以在这种情况下，这不是优化器选择一个完整的表扫描而不是其他计划的问题--优化器实际上得出的结论是，甚至没有任何其他可能的计划需要考虑。

这只适用于IN右侧的多个“行构造函数”。对于单个表达式，优化器执行它的操作，并实现这相当于col_1 = a AND col_2 = b：

WHERE (col_1,col_2) IN ((a,b))    # is optimized correctly
WHERE (col_1,col_2) IN (ROW(a,b)) # is an equivalent expression in MySQL

有趣的是，您最初的EXPLAIN表明，这个独特的索引的使用方式与您可能认为它被使用的方式不完全相同。它只用于查找具有所需sale_id的行.不是两者都有价值。

您将在原始EXPLAIN中注意到，key_len显示为4，这意味着只检查索引的最左边的4个字节-- sale_id，4字节的INT将是该索引中最左边的4个字节。Using where意味着优化器意识到，可能需要对范围扫描返回的行进行额外的筛选，以消除任何不满足WHERE子句其余部分的行--所有具有sale_id 502和504的行都将通过索引检索，而不管它们对于product_id的值如何，随后将对结果行进行筛选，以满足WHERE施加的附加约束。

最佳路径可能是在where子句中坚持使用(expr和expr)或(expr和expr)或(expr和expr)。它在逻辑上是等价的，优化器理解它。

关于你的一些评论。根据我前面讨论的内容，索引提示不会有帮助，因为优化器似乎没有意识到您所使用的表达式与它可以处理的其他表达式的等价性。但是作为参考，它在语法上无效的原因是您必须使用索引的名称，而不是索引中的列列表。您已经将您的唯一索引称为“唯一的”，因此将其用作索引提示的方式应该是以下格式：

USE INDEX(`UNIQUE`)

Answer 2

尝试将查询重构为一个全面连接。

SELECT B.* FROM
(
    SELECT 502 sale_id,2 product_id
    UNION SELECT 502,1
    UNION SELECT 502,3
    UNION SELECT 504,4
    UNION SELECT 504,2
    UNION SELECT 504,3
) A INNER JOIN sale_product_inventories B
USING (sale_id,product_id);

在这种情况下，必须使用索引。

为什么它没有在原始查询中使用索引？我将其归咎于WHERE子句，因为查询优化器首先看到了sale_id查找，然后可能决定，对于WHERE子句的其余部分，完整的表扫描是阻力最小的路径。

试试看!！