主页 / dba / 问题 / 42918
Accepted
Egalitarian
Asked: 2013-05-24 07:54:21 +0800 CST

MySql 没有正确优化查询

  • 772

我有一个表结构如下：

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 使用索引 Unique 执行时间为 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 没有使用 UNIQUE 索引，执行时间为 76 毫秒。

Mysql：5.5.27 InnoDB 版本：1.1.8

我的问题是为什么 mysql 以这种方式运行。有人可以帮我解决这个问题吗？

编辑：
我遇到了这个所以认为添加 MySQL 通常不能在列上使用索引可能是有用的，除非列在查询中被隔离。“隔离”该列意味着它不应该是表达式的一部分或在查询的函数内。

mysql

2 个回答

  1. Best Answer

    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 中新的 Optimizer Trace并通过它运行一些测试用例，我才找到这些。这似乎是一个安全的赌注，如果 5.6 无法处理它，那么以前的版本也无法处理它。

    事实证明，MySQL 5.6 确实处理不了。“set in set of sets”构造似乎根本不是优化器所捕捉到的东西。所以在这种情况下，优化器选择全表扫描而不是其他计划并不是问题——优化器实际上得出结论，甚至没有任何其他可能的计划可供考虑。

    这仅适用于右侧的多个“行构造函数” 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 and expr) 或 (expr and expr) 或 (expr and expr)。它在逻辑上是等价的并且优化器理解它。

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

    USE INDEX(`UNIQUE`)
    • 6

  2. 尝试将查询重构为完全 JOIN

    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 子句的其余部分全表扫描是阻力最小的路径。

    试试看 ！！！

    • 2

相关问题