基于 3 个条件 Select 3 行的最佳 MySQL 索引和查询

发布于05月27日

我有一个MySQL表products，大约有10k行，如下所示:

+----+-----+--------+------+---------+
| id | cat | color  | size |  descr  |
+----+-----+--------+------+---------+
|  1 |   1 | red    |    1 | Lorem 1 |
|  2 |   1 | green  |    2 | Lorem 2 |
|  3 |   2 | orange |    1 | Lorem 3 |
|  4 |   2 | blue   |    3 | Lorem 4 |
+----+-----+--------+------+---------+

我需要使用一个MySQL查询，根据3个条件 Select 3个特定记录.始终会有正好3条记录满足所有3个条件.

以下是我提出的一个有效的问题:

SELECT 
  category, 
  color, 
  size, 
  descr 
FROM 
  products 
WHERE 
  (
    cat = 1 
    AND color = 'red' 
    AND size = 1
  ) 
  OR (
    cat = 2 
    and color = 'orange' 
    and size = 1
  ) 
  OR (
    cat = 2 
    AND color = 'blue' 
    AND size = 3
  )

问题1:这是基于3个条件检索3条记录的最佳查询吗？

问题2:此表的最佳索引 struct 是什么？

提前感谢！

推荐答案

我会在(cat, color, size)上创建一个索引.在这种情况下，索引中列的顺序并不重要，因为在所有情况下都使用=进行比较.

MySQL的IN() predicate支持行构造函数比较语法，MySQL的当前版本确实支持index optimization for row constructor expressions(MySQL的旧版本不支持，但这些版本现在都已经过期).

SELECT 
  category, 
  color, 
  size, 
  descr 
FROM 
  products 
WHERE (cat, color, size) IN (
    (1, 'red', 1),
    (2, 'orange', 1),
    (2, 'blue', 3)
)