mysql - 如何从多列主键中选择一系列行？

Question

我正在尝试对 MySQL 5.5 中的行进行分 block ，为此我想在两个主键之间选择一个范围(我可以轻松获得)。当主键只有一列时，这是微不足道的。但是，我需要分 block 的一些表在主键中有多个列，我还没有想出如何在一个准备好的语句中完成这项工作。

这是一个包含一些数据的示例表:

CREATE TABLE test (
  a INT UNSIGNED NOT NULL,
  b INT UNSIGNED NOT NULL,
  c INT UNSIGNED NOT NULL,
  d VARCHAR(255) DEFAULT '', -- various data columns
  PRIMARY KEY (a, b, c)
) ENGINE=InnoDB;

INSERT INTO test VALUES 
(1, 1, 1),
(1, 1, 2),
(1, 1, 3),
(1, 2, 1),
(1, 2, 2),
(1, 2, 3),
(1, 3, 1),
(1, 3, 3),
(2, 1, 1),
(2, 1, 2),
(2, 2, 2),
(2, 3, 1),
(2, 3, 3),
(3, 1, 2),
(3, 1, 3),
(3, 2, 1),
(3, 2, 2),
(3, 2, 3),
(3, 3, 1),
(3, 3, 3);

如果我有两个主键，例如 (1, 1, 3) 和 (3, 2, 1)，则以下语句会起作用。 a1、b1 和 c1 是来自第一个主键的值，a2、b2 和 c2 是来自第二个主键的值:

SELECT * FROM test WHERE a = a1 AND b = b1 AND c >= c1
UNION
SELECT * FROM test WHERE a = a1 AND b > b1
UNION
SELECT * FROM test WHERE a > a1 AND a < a2
UNION
SELECT * FROM test WHERE a = a2 AND b < b2
UNION
SELECT * FROM test WHERE a = a2 AND b = b2 AND c <= c2

或者

SELECT * FROM test WHERE a = 1 AND b = 1 AND c >= 3
UNION
SELECT * FROM test WHERE a = 1 AND b > 1
UNION
SELECT * FROM test WHERE a > 1 AND a < 3
UNION
SELECT * FROM test WHERE a = 3 AND b < 2
UNION
SELECT * FROM test WHERE a = 3 AND b = 2 AND c <= 1

哪个给

(1, 1, 3),
(1, 2, 1),
(1, 2, 2),
(1, 2, 3),
(1, 3, 1),
(1, 3, 3),
(2, 1, 1),
(2, 1, 2),
(2, 2, 2),
(2, 3, 1),
(2, 3, 3),
(3, 1, 2),
(3, 1, 3),
(3, 2, 1)

但是当第一列相同时，上面的操作失败了，例如(1, 2, 2) 和 (1, 3, 1)。在这种情况下，第二个和第四个 SELECT 选择太多。

SELECT * FROM test WHERE a = 1 AND b = 2 AND c >= 2
UNION
SELECT * FROM test WHERE a = 1 AND b > 2
UNION
SELECT * FROM test WHERE a > 1 AND a < 1
UNION
SELECT * FROM test WHERE a = 1 AND b < 3
UNION
SELECT * FROM test WHERE a = 1 AND b = 3 AND c <= 1

哪个给

(1, 1, 1), -- erroneously selected from: SELECT * FROM test WHERE a = 1 AND b < 3
(1, 1, 2), -- erroneously selected from: SELECT * FROM test WHERE a = 1 AND b < 3
(1, 1, 3), -- erroneously selected from: SELECT * FROM test WHERE a = 1 AND b < 3
(1, 2, 1), -- erroneously selected from: SELECT * FROM test WHERE a = 1 AND b < 3
(1, 2, 2),
(1, 2, 3),
(1, 3, 1),
(1, 3, 3)  -- erroneously selected from: SELECT * FROM test WHERE a = 1 AND b > 2

期望的输出是

(1, 2, 2),
(1, 2, 3),
(1, 3, 1)

我想要一个适用于所有主键范围的语句，包括第一列和第二列的相同值。我还有主键包含 4 列的表，在这种情况下我将扩展模式。

我希望每个表只有一个语句，而不是即时创建查询，因为当我分 block 浏览表时，查询将执行多达一百万次。一些表的行数超过 1 亿。

我宁愿避免构造多个语句，因为我有数百个要按照这种模式编写，而且编写更多的语句会明显增加工作量。如果这是唯一的选择，我会这样做。

我目前使用参数化查询，并以编程方式从两个主键生成值，处理应用层中所需的重复值(上例中的 a1 x3、b1 x2、a2 x3、b2 x2)。因此，为参数传递重复值对我来说很简单。

此时我最好的猜测是复制 SELECT，并在 WHERE 子句中增加一个比较主键列值的部分。

Answer 1

我会使用这个查询来选择一个范围:

SELECT * 
FROM test
WHERE (a,b,c) >= (1, 1, 3) 
  and (a,b,c) <= (3, 2, 1)

演示:http://www.sqlfiddle.com/#!2/d6cf7b/4

---

不幸的是，MySql 无法对上述查询执行范围优化，请参阅此链接:http://dev.mysql.com/doc/refman/5.7/en/range-optimization.html#range-access-single-part
(章节:8.2.1.3.4.行构造函数表达式的范围优化)
他们说从 5.7 版本开始，MySql 只能优化一种形式的查询:

WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

基本上上面的查询等同于这个查询:

SELECT * 
FROM test
WHERE  
     a = 1 and b = 1 and c >= 3 -- lowest end
     or 
     a = 3 and b = 2 and c <= 1 -- highest end
     or 
     a = 1 and b > 1
     or
     a = 3 and b < 2
     or 
     a  > 1 and a < 3
;

MySql 可能会针对这种形式的查询使用范围访问方法优化，请参见下面的链接
(章节:8.2.1.3.2。多部分索引的范围访问方法):
http://dev.mysql.com/doc/refman/5.7/en/range-optimization.html