假设我有四个表:tbl1
。。。CCD_ 2。每个字段都有一个唯一的数字id
字段。tbl1
、tbl2
和tbl3
中的每一个都具有用于序列中的下一个表的外键字段。例如,tbl1
有一个tbl2_id
外键字段,依此类推。每个表也有一个字段order
(以及与问题无关的其他字段(。
连接所有四个表以返回tbl1的所有行以及其他三个字段中的相应字段是很简单的。通过order
字段的特定ORDER BY
组合也可以容易地对该结果集进行排序。也很容易仅返回与tbl1
中的某个特定id
(例如WHERE tbl1.id = 7777
(相对应的行。
问题:哪个查询最有效地返回(例如(100行,从对应于id=7777
的行开始,按order
字段特定组合确定的顺序?
使用ROW_NUMBER
或(在MySQL版本<8中对其进行模拟(来获取id=7777行的位置,然后在同一查询的新版本中使用该位置来设置LIMIT
子句中的偏移量将是一种方法。(中间有一个读锁。(但是可以在一个查询中完成吗?
# FIRST QUERY: get row number of result row where tbl1.id = 7777
SELECT x.row_number
FROM
(SELECT @row_number:=@row_number+1 AS row_number, tbl1.id AS id
FROM (SELECT @row_number:=0) AS t, tbl1
INNER JOIN tbl2 ON tbl2.id = tbl1.tbl2_id
INNER JOIN tbl3 ON tbl3.id = tbl2.tbl3_id
INNER JOIN tbl4 ON tbl4.id = tbl3.tbl4_id
WHERE <some conditions>
ORDER BY tbl4.order, tbl3.order, tbl2.order, tbl1.order
) AS x
WHERE id=7777;
存储上面查询中的行号,并使用它在下面的查询中绑定:offset
。
# SECOND QUERY : Get 100 rows starting from the one with id=7777
SELECT x.field1, x.field2, <etc.>
FROM
(SELECT @row_number:=@row_number+1 AS row_number, field1, field2
FROM (SELECT @row_number:=0) AS t, tbl1
INNER JOIN tbl2 ON tbl2.id = tbl1.tbl2_id
INNER JOIN tbl3 ON tbl3.id = tbl2.tbl3_id
INNER JOIN tbl4 ON tbl4.id = tbl3.tbl4_id
WHERE <same conditions as before>
ORDER BY tbl4.order, tbl3.order, tbl2.order, tbl1.order
) AS x
LIMIT :offset, 100;
澄清问题
在一般情况下,您不会要求tbl4
0。相反,您有一个(11,22,33,44)
元组,并且您希望"继续您停止的地方"。
两次讨论,
这很混乱,但并非不可能。请参见通过复合键循环。Ig给出了一个用2列来做这件事的例子;来自4个表的4列是这样的扩展。
变化
以下是另一个讨论:https://dba.stackexchange.com/questions/164428/should-i-store-data-pre-ordered-rather-than-ordering-on-the-fly/164755#164755
在实际实现中,我发现让"100"(LIMIT
(变得灵活更容易思考。其想法是:向前延伸100行(使用LIMIT 100,1
(。假设你得到(111222333444(。如果您当前处于(111,…(,则处理id2/3/4。如果是(113,…(,则执行WHERE id1 < 113
并省略id2/3/4的任何规范。这意味着获取不到100行,但它会让你差一点就开始id1=113。
也就是说,它涉及到构造一个具有1到4个条件的WHERE
子句。
在所有情况下,您的查询都显示ORDER BY id1, id2, id3, id4
。LIMIT
的唯一用途是在探测器中计算第100行(使用LIMIT 100,1
(的前方距离。
我想我可以为此挖掘一些旧的Perl代码。