mysql - 无法用此表实现覆盖索引(2 等式和 1 个选择)？

Question

CREATE TABLE `discount_base` (
  `id` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `amount` decimal(13,4) NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `family` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `customer_id` varchar(8) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_CUSTOMER` (`customer_id`),
  KEY `IDX_FAMILY_CUSTOMER_AMOUNT` (`family`,`customer_id`,`amount`),
  CONSTRAINT `FK_CUSTOMER` FOREIGN KEY (`customer_id`) 
      REFERENCES `customer` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

我在family、customer_id 和amount 上添加了封面索引IDX_FAMILY_CUSTOMER_AMOUNT 因为大部分时间我使用以下查询:

SELECT amount FROM discount_base WHERE family = :family AND customer_id = :customer_id

但是使用 EXPLAIN 和记录弹跳(~ 250000)它说:

'1', 'SIMPLE', 'discount_base', 'ref', 'IDX_CUSTOMER,IDX_FAMILY_CUSTOMER_AMOUNT', 'IDX_FAMILY_CUSTOMER_AMOUNT', '40', 'const,const', '1', 'Using where; Using index'

为什么我要使用where;使用索引 而不仅仅是使用索引？

编辑:Fiddle少量数据(Using where; Using index):

EXPLAIN SELECT amount
FROM discount_base
WHERE family = '0603' and customer_id = '20000275';

另一个fiddle其中 id 是 family + customer_id (const):

EXPLAIN SELECT amount
FROM discount_base
WHERE `id` = '060320000275';

Answer 1

有趣的问题。 IDX_FAMILY_CUSTOMER_AMOUNT 索引将用于此查询似乎“显而易见”:

SELECT amount
FROM discount_base
WHERE family = :family AND customer_id = :customer_id;

对我们来说“显而易见”，但对优化器来说显然不是。发生了什么事？

索引使用的这方面记录很少。我(明智地)推测当使用不区分大小写的排序规则(可能还有其他排序规则)对字符串进行比较时，= 操作实际上更像是一个 in。从概念上讲，有点像这样:

WHERE family in (lower(:family, upper(:family), . . .) and . . . 

这是概念上的。但这意味着 = 需要索引扫描而不是索引查找。打印上的细微变化。语义上非常重要。它阻止使用第二个 键。是的，这是不平等的不幸后果，即使它们看起来像 =。

因此，优化器比较了两个可能的索引，并确定 customer_id 比 family 更具选择性，并选择了前者。

唉，您的两个键都是不区分大小写的字符串。我的建议是用自动递增的整数 ID 替换至少其中一个。事实上，我的建议是基本上所有 表都有一个自动递增的整数 id，然后用于所有外键引用。

另一种解决方案是使用触发器创建单个列 CustomerFamily，并将值连接在一起。然后这个索引:

KEY IDX_CUSTOMERFAMILY_AMOUNT (CustomerFamily, amount)

应该做你想做的。区分大小写的编码也有可能解决该问题。