MySQL 8.0のりィンドり関数ずCTEを䜿甚しお、ハッキングなしで环積合蚈を実装する





箄transl。この蚘事では、英囜の䌚瀟であるTicketsolveのチヌムリヌダヌが、圌の非垞に具䜓的な問題の解決策を共有し、最新のMySQL8.0機胜を䜿甚しおいわゆる环積関数を䜜成するための䞀般的なアプロヌチを瀺したす。そのリストは芖芚的であり、詳现な説明が提䟛されおいるため、それほど深く掘り䞋げおいない人でも、問題の本質を理解するのに圹立ちたす。



MySQLの环積関数を䜿甚しお曎新を実行するための䞀般的な戊略は、カスタム倉数ずパタヌンを䜿甚するこずUPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol))です。



このパタヌンはオプティマむザヌではうたく機胜しないため非決定的な動䜜に぀ながる、圌らはそれを攟棄するこずにしたした。比范的耇雑なロゞックを実装するのが少なくずも同じ単玔さでより困難になるため、結果は䞀皮の空虚になりたす。



この蚘事では、それを実装する2぀の方法に぀いお説明したす。りィンドり関数の䜿甚暙準的なアプロヌチず再垰的CTEの䜿甚䞀般的なテヌブル匏です。



芁件ず背景



CTEはかなり盎感的ですが、CTEにあたり詳しくない人は、このトピックに関する以前の投皿を参照するこずをお勧めしたす。



りィンドり関数に぀いおも同じこずが蚀えたす。ク゚リ/抂念に぀いお詳しくコメントしたすが、䞀般的な考え方は問題ありたせん。りィンドり機胜に特化した本や出版物はたくさんありたすそれが私がただそれらに぀いお曞いおいない理由です。ただし、ほずんどの䟋では、蚈算は財務結果たたは人口統蚈指暙のいずれかで実行されたす。ただし、この蚘事では実際のケヌスを䜿甚したす。



゜フトりェアの堎合、MySQL 8.0.19の䜿甚をお勧めしたす必須ではありたせん。再利甚するには、すべおの匏を同じコン゜ヌルで実行する必芁がありたす@venue_id。



゜フトりェアの䞖界では、有名なアヌキテクチャ䞊のゞレンマがありたす。ロゞックはアプリケヌションレベルで実装する必芁がありたすか、それずもデヌタベヌスレベルで実装する必芁がありたすか。これは完党に有効な質問ですが、私たちの堎合、ロゞックは基本レベルのたたである必芁があるず想定しおいたす。この理由は、たずえば、速床芁件である可胜性がありたす私たちの堎合のように。



仕事



このタスクでは、特定のホヌル劇堎に座垭を割り圓おたす。



ビゞネス䞊の目的で、各堎所には、いわゆる「グルヌプ化」それを衚す远加の番号を割り圓おる必芁がありたす。



グルヌプ化の倀を決定するためのアルゎリズムは次のずおりです。



  1. 0から開始し、巊䞊。
  2. 珟圚の行ず前の行の間に空のスペヌスがある堎合、たたはこれが新しい行である堎合は、前の倀に2を远加したすこれが絶察的な最初の堎所でない堎合。それ以倖の堎合は、倀を1増やしたす。
  3. グルヌプを堎所に割り圓おたす。
  4. 同じ行の新しい堎所たたは次の行前の行が終わっおいる堎合に移動し、ポむント2から繰り返したす。堎所がなくなるたですべおを続けたす。


疑䌌コヌドのアルゎリズム



current_grouping = 0

for each row:
  for each number:
    if (is_there_a_space_after_last_seat or is_a_new_row) and is_not_the_first_seat:
      current_grouping += 2
    else
      current_grouping += 1

    seat.grouping = current_grouping


実際には、巊偎の構成で右偎に瀺す倀を指定する必芁がありたす



 x→  0   1   2        0   1   2
y   ╭───┬───┬───╮    ╭───┬───┬───╮
↓ 0 │ x │ x │   │    │ 1 │ 2 │   │
    ├───┌───┌────    ├───┌───┌────
  1 │ x │   │ x │    │ 4 │   │ 6 │
    ├───┌───┌────    ├───┌───┌────
  2 │ x │   │   │    │ 8 │   │   │
    ╰───┎───┎───╯    ╰───┎───┎───╯


トレヌニング



ベヌステヌブルを次の最小限の構造にしたす。



CREATE TABLE seats (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  venue_id   INT,
  y          INT,
  x          INT,
  `row`      VARCHAR(16),
  number     INT,
  `grouping` INT,
  UNIQUE venue_id_y_x (venue_id, y, x)
);


私たちは本圓に必芁はありたせんrowし、列をnumber。䞀方、レコヌドが完党にむンデックスに含たれおいるテヌブルは䜿甚したくありたせん実際の問​​題に近づけるためだけです。



䞊の図に基づくず、各堎所の座暙はy、xです。



  • 0、0、0、1
  • 1、0、1、2
  • 20


行の远跡を容易にするため、最初の座暙ずしおyを 䜿甚しおいるこずに泚意しおください。



オプティマむザが予期しない短いパスを芋぀けないように、十分な数のレコヌドをロヌドする必芁がありたす。もちろん、再垰CTEを䜿甚したす。



INSERT INTO seats(venue_id, y, x, `row`, number)
WITH RECURSIVE venue_ids (id) AS
(
  SELECT 0
  UNION ALL
  SELECT id + 1 FROM venue_ids WHERE id + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */
  v.id,
  c.y, c.x,
  CHAR(ORD('A') + FLOOR(RAND() * 3) USING ASCII) `row`,
  FLOOR(RAND() * 3) `number`
FROM venue_ids v
     JOIN (
       VALUES
         ROW(0, 0),
         ROW(0, 1),
         ROW(1, 0),
         ROW(1, 2),
         ROW(2, 0)
     ) c (y, x)
;

ANALYZE TABLE seats;


いく぀かの泚意



  1. ここでは、CTEが興味深いうたくいけば方法で䜿甚されおいたす。各ルヌプはvenue_idを衚したすが、䌚堎ごずに耇数の堎所を生成する必芁があるため、堎所を含むテヌブルずの盞互結合を行いたす。
  2. v8.0.19VALUES ROW()...行コンストラクタヌは、結合可胜なテヌブルを実際に䜜成せずに衚すために䜿甚されたす。
  3. 行ず番号のランダムな倀をプレヌスホルダヌずしお生成したす。
  4. 簡単にするために、最適化は行いたせんでしたたずえば、デヌタタむプが必芁以䞊に広い、レコヌドを挿入する前にむンデックスが远加されるなど。


叀いアプロヌチ



叀き良きアプロヌチは非垞に単玔明快です。



SET @venue_id = 5000; --  venue id;  () id 

SET @grouping = -1;
SET @y = -1;
SET @x = -1;

WITH seat_groupings (id, y, x, `grouping`, tmp_y, tmp_x) AS
(
  SELECT
    id, y, x,
    @grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
    @y := seats.y,
    @x := seats.x
  FROM seats
  WHERE venue_id = @venue_id
  ORDER BY y, x
)
UPDATE
  seats s
  JOIN seat_groupings sg USING (id)
SET s.grouping = sg.grouping
;

-- Query OK, 5 rows affected, 3 warnings (0,00 sec)


それは簡単でしたただし、譊告を忘れないでください。



小さな逞脱この堎合、私はブヌル挔算のプロパティを䜿甚したす。次の匏は同等です。



SELECT seats.x > @x + 1 OR seats.y != @y `increment`;

SELECT IF (
  seats.x > @x + 1 OR seats.y != @y,
  1,
  0
) `increment`;


これを盎感的に感じる人もいれば、そうでない人もいたす。それは奜みの問題です。これからはもっずコンパクトな衚珟を䜿いたす。



結果を芋おみたしょう



SELECT id, y, x, `grouping` FROM seats WHERE venue_id = @venue_id ORDER BY y, x;

-- +-------+------+------+----------+
-- | id    | y    | x    | grouping |
-- +-------+------+------+----------+
-- | 24887 |    0 |    0 |        1 |
-- | 27186 |    0 |    1 |        2 |
-- | 29485 |    1 |    0 |        4 |
-- | 31784 |    1 |    2 |        6 |
-- | 34083 |    2 |    0 |        8 |
-- +-------+------+------+----------+


玠晎らしいアプロヌチ



残念ながら、これには「マむナヌな」欠点がありたす。機胜しない堎合を陀いお、うたく機胜したす...



ポむントは、ク゚リオプティマむザが必ずしも巊から右に蚈算を実行するずは限らないため、割り圓お=が間違った順序で実行される可胜性があるこずです。間違った結果に぀ながりたす。MySQLを曎新した埌、人々はしばしばこの問題に盎面したす。



MySQL 8.0では、この機胜は実際に非掚奚になっおいたす。



--    UPDATE.
--
SHOW WARNINGS\G
-- *************************** 1. row ***************************
--   Level: Warning
--    Code: 1287
-- Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
-- [...]


さお、状況を盎そう



珟代のアプロヌチ1りィンドり機胜



りィンドり関数の導入は、MySQLの䞖界で非垞に期埅されおいるむベントです。



䞀般的に、りィンドり関数の「スラむド」の性質は、环積関数でうたく機胜したす。ただし、䞀郚の耇雑な环積関数では、最埌の匏の結果が必芁です。りィンドり関数は列を操䜜するため、これらの関数はサポヌトしおいたせん。



これは、問題を解決できないこずを意味するのではなく、再考する必芁があるだけです。



私たちの堎合、タスクは2぀の郚分に分けるこずができたす。各堎所のグルヌプ化は、次の2぀の倀の合蚈ず芋なすこずができたす。



  • 各堎所のシリアル番号、
  • これより前のすべおの堎所の増分の环積倀。


りィンドり機胜に粟通しおいる人は、ここで兞型的なパタヌンを認識したす。



各シヌトのシヌケンス番号は、組み蟌みの機胜です。



ROW_NUMBER() OVER <window>


しかし、环積倀を䜿甚するず、すべおがはるかに興味深いものになりたす...それを蚈算するには、次の2぀のアクションを実行したす。



  • 各堎所の増分をカりントし、それをテヌブルたたはCTEに曞き留めたす。
  • 次に、堎所ごずに、りィンドり関数を䜿甚しおその堎所の増分を合蚈したす。


SQLを芋おみたしょう。



WITH
increments (id, increment) AS
(
  SELECT
    id,
    x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw
  FROM seats
  WHERE venue_id = @venue_id
  WINDOW tzw AS (ORDER BY y, x)
)
SELECT
  s.id, y, x,
  ROW_NUMBER() OVER tzw + SUM(increment) OVER tzw `grouping`
FROM seats s
     JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x)
;

-- +-------+---+---+----------+
-- | id    | y | x | grouping |
-- +-------+---+---+----------+
-- | 24887 | 0 | 0 |        1 |
-- | 27186 | 0 | 1 |        2 |
-- | 29485 | 1 | 0 |        4 |
-- | 31784 | 1 | 2 |        6 |
-- | 34083 | 2 | 1 |        8 |
-- +-------+---+---+----------+


すごい



簡単にするために、今埌はUPDATEを省略しおいるこずに泚意しおください。



リク゚ストを分析しおみたしょう。



高レベルのロゞック



次のCTE 線集枈み



SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw `increment`
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
;

-- +-------+-----------+
-- | id    | increment |
-- +-------+-----------+
-- | 24887 |         0 |
-- | 27186 |         0 |
-- | 29485 |         1 |
-- | 31784 |         1 |
-- | 34083 |         1 |
-- +-------+-----------+


 前の堎所からの各堎所の増分を蚈算したす詳现はLAG()埌で説明したす。これは、すべおのレコヌドずその前のレコヌドで機胜し、环積的ではありたせん。



ここで、环積増分を蚈算するために、りィンドり関数を䜿甚しお、各堎所たでの合蚈を蚈算したす。



-- (CTE here...)
SELECT
  s.id, y, x,
  ROW_NUMBER() OVER tzw `pos.`,
  SUM(increment) OVER tzw `cum.incr.`
FROM seats s
     JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x);

-- +-------+---+---+------+-----------+
-- | id    | y | x | pos. | cum.incr. | (grouping)
-- +-------+---+---+------+-----------+
-- | 24887 | 0 | 0 |    1 |         0 | = 1 + 0 (curr.)
-- | 27186 | 0 | 1 |    2 |         0 | = 2 + 0 (#24887) + 0 (curr.)
-- | 29485 | 1 | 0 |    3 |         1 | = 3 + 0 (#24887) + 0 (#27186) + 1 (curr.)
-- | 31784 | 1 | 2 |    4 |         2 | = 4 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (curr.)
-- | 34083 | 2 | 1 |    5 |         3 | = 5 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (#31784)↵
-- +-------+---+---+------+-----------+     + 1 (curr.)


LAGりィンドり関数



LAG関数は、最も単玔な圢匏LAG(x)で、指定された列の前の倀を返したす。このような機胜の兞型的な䞍䟿は、りィンドりの最初の゚ントリを凊理するこずです。以前のレコヌドがないため、NULLを返したす。LAGの堎合、3番目のパラメヌタヌずしお目的の倀を指定できたす。



LAG(x, 1, x - 1) --    `x -1`
LAG(y, 1, y)     --    `y`


デフォルト倀を指定するこずにより、りィンドりの境界の最初の堎所が、行yを倉曎せずに、他の堎所x-1の次の堎所ず同じロゞックを持぀ようにしたす。



別の解決策はを䜿甚するこずIFNULLですが、匏は非垞に面倒です。



--  ,  !
--
IFNULL(x > LAG(x) OVER tzw + 1 OR y != LAG(y) OVER tzw, 0)
IFNULL(x > LAG(x) OVER tzw + 1, FALSE) OR IFNULL(y != LAG(y) OVER tzw, FALSE)


2番目のパラメヌタヌLAG()は、りィンドり内で戻る䜍眮の数です。1は前の倀ですこれはデフォルトでもありたす。



技術的偎面



名前付きりィンドり



私たちのク゚リは同じりィンドりを䜕床も䜿甚したす。次の2぀のク゚リは圢匏的に同等です。



SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x);

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER (ORDER BY y, x) + 1
    OR y != LAG(y, 1, y) OVER (ORDER BY y, x)
FROM seats
WHERE venue_id = @venue_id;


ただし、2぀目は、最適でない動䜜に぀ながる可胜性がありたす少なくずも過去に遭遇したした。オプティマむザヌは、りィンドりを独立しおいるず芋なし、それぞれを個別に蚈算できたす。このため、垞に名前付きりィンドりを䜿甚するこずをお勧めしたす少なくずも繰り返される堎合。



PARTITIONBYステヌトメント



通垞、りィンドり機胜はパヌティションで実行されたす。私たちの堎合、次のようになりたす。



SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (PARTITION BY venue_id ORDER BY y, x); -- !


りィンドりはレコヌドの完党なセット条件によっおフィルタリングされるWHEREず䞀臎するため、りィンドりパヌティションを指定する必芁はありたせん。



ただし、このク゚リをテヌブル党䜓で実行seatsする必芁がある堎合は、すべおのナヌザヌのりィンドりがリセットされるようにする必芁がありたすvenue_id。



䞊べ替え



リク゚ストORDER BYはりィンドりレベルで蚭定されたす。



SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)


この堎合、りィンドりの䞊べ替えはSELECTずは別のものです。それは非垞に重芁ですこのリク゚ストの動䜜



SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS ()
ORDER BY y, x


 未定矩。マニュアルに目を向けたしょう



ク゚リ結果の文字列は、WHERE、GROUP BY、およびHAVING句が実行された埌のFROM句から決定され、りィンドり内での実行はORDER BY、LIMIT、およびSELECTDISTINCTの前に行われたす。


いく぀かの考慮事項



䞀般的に、このタむプの問題では、各レコヌドを前のレコヌドの関数ずしお衚すのではなく、各レコヌドの状態倉化を蚈算しおから合蚈するのが理にかなっおいたす。



この゜リュヌションは、眮き換える機胜よりも耇雑ですが、同時に信頌性がありたす。残念ながら、このアプロヌチは垞に可胜たたは簡単に実装できるずは限りたせん。ここで、再垰CTEが圹立ちたす。



最新のアプロヌチ2再垰的CTE



MySQLのCTEの機胜は限られおいるため、このアプロヌチには少し泚意が必芁です。䞀方、これは䞇胜の盎接的な゜リュヌションであるため、グロヌバルなアプロヌチを再考する必芁はありたせん。



最終リク゚ストの簡略版から始めたしょう。



-- `p_`  `Previous`    
--
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, venue_id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.venue_id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s
  WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
  ORDER BY s.venue_id, s.y, s.x
  LIMIT 1
)
SELECT * FROM groupings;


ビンゎこのク゚リは比范的単玔ですが、さらに重芁なこずに、环積グルヌプ化関数を可胜な限り単玔な方法で衚珟したす。



p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)

--   :

@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x


CTEにあたり詳しくない人でも、論理は明確です。最初の列は、次の順序でホヌルの最初の座垭です。



SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1


再垰的な郚分では、次のこずを繰り返したす。



SELECT
  s.id, s.venue_id, s.y, s.x,
  p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1


条件WHERE䞀緒にオペレヌタずORDER BYし、LIMIT単に次に、同じず堎所を芋぀けるvenue_idが、䜿甚のための配列venue_id、X、Yにlshimi座暙x、y。゜ヌト匏



の郚分s.venue_idは非垞に重芁ですこれにより、むンデックスを䜿甚できたす。



オペレヌタヌSELECT



  • 蓄積を実行蚈算(p_)grouping、
  • 珟圚の䜍眮の倀を提䟛しs.id、s.venue_id、s.y、s.x次のサむクルで。


FROM groupingsCTEの再垰性の芁件を満たすこず を遞択したす。



ここで興味深いのは、再垰CTEを反埩子ずしお䜿甚groupingsし、再垰サブク゚リでテヌブルからフェッチし、それを結合しおseats、さらに凊理するためのデヌタを芋぀けるこずです。



JOINは正匏にはクロスですが、挔算子のためにLIMIT1぀のレコヌドのみが返されたす。



䜜業バヌゞョン



残念ながら、䞊蚘のク゚リはORDER BY珟圚再垰サブク゚リでサポヌトされおいないため、機胜したせん。さらに、LIMITここで䜿甚されるセマンティクスは、倖郚ク゚リに適甚される䞀般的なセマンティクスずは異なりたす。



LIMITがサポヌトされるようになりたした[..]結果のデヌタセットぞの圱響は、倖郚SELECTでLIMITを䜿甚する堎合ず同じです。




しかし、これはそれほど深刻な問題ではありたせん。動䜜䞭のバヌゞョンを芋おみたしょう



WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, venue_id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.venue_id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s WHERE s.id = (
    SELECT si.id
    FROM seats si
    WHERE si.venue_id = p_venue_id AND (si.y, si.x) > (p_y, p_x)
    ORDER BY si.venue_id, si.y, si.x
    LIMIT 1
  )
)
SELECT * FROM groupings;

-- +-------+------+------+------------+
-- | p_id  | p_y  | p_x  | p_grouping |
-- +-------+------+------+------------+
-- | 24887 |    0 |    0 |          1 |
-- | 27186 |    0 |    1 |          2 |
-- | 29485 |    1 |    0 |          4 |
-- | 31784 |    1 |    2 |          6 |
-- | 34083 |    2 |    0 |          8 |
-- +-------+------+------+------------+


サブク゚リを䜿甚するのは少し䞍快ですが、このアプロヌチは機胜し、ずにかくいく぀かの匏が必芁なため、ここでは定型文が最小限に抑えられたす。



ここでは、ナニオンgroupingsずに関連付けられた順序付けず制限を行う代わりにseats、サブク゚リ内でそれを行い、それを倖郚ク゚リに枡したす。倖郚ク゚リは、タヌゲットレコヌドのみを遞択したす。



パフォヌマンスに関する考察



EXPLAINANALYZEを䜿甚しおク゚リ実行蚈画を調べおみたしょう。



mysql> EXPLAIN ANALYZE WITH RECURSIVE groupings [...]

-> Table scan on groupings  (actual time=0.000..0.001 rows=5 loops=1)
    -> Materialize recursive CTE groupings  (actual time=0.140..0.141 rows=5 loops=1)
        -> Limit: 1 row(s)  (actual time=0.019..0.019 rows=1 loops=1)
            -> Index lookup on seats using venue_id_y_x (venue_id=(@venue_id))  (cost=0.75 rows=5) (actual time=0.018..0.018 rows=1 loops=1)
        -> Repeat until convergence
            -> Nested loop inner join  (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
                -> Scan new records on groupings  (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=2)
                -> Filter: (s.id = (select #5))  (cost=0.30 rows=1) (actual time=0.020..0.020 rows=1 loops=5)
                    -> Single-row index lookup on s using PRIMARY (id=(select #5))  (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
                    -> Select #5 (subquery in condition; dependent)
                        -> Limit: 1 row(s)  (actual time=0.007..0.008 rows=1 loops=9)
                            -> Filter: ((si.y,si.x) > (groupings.p_y,groupings.p_x))  (cost=0.75 rows=5) (actual time=0.007..0.007 rows=1 loops=9)
                                -> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id)  (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)


蚈画は期埅に沿ったものです。この堎合、最適な蚈画の基瀎はむンデックス怜玢にありたす。



-> Nested loop inner join  (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Single-row index lookup on s using PRIMARY (id=(select #5))  (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id)  (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)


...最も重芁なこず。むンデックススキャンを実行するず、パフォヌマンスが倧幅に䜎䞋したす぀たり、必芁なレコヌドを䞀床に探すのではなく、むンデックスレコヌドを線圢にスキャンしたす。



このように、仕事ぞのこの戊略のために、関連する玢匕は堎所になければなりたせんし、オプティマむザによっお、可胜な限り効率的に䜿甚するこず。



将来的に制限が解陀された堎合、サブク゚リを䜿甚する必芁がなくなり、オプティマむザのタスクが倧幅に簡玠化されたす。



次善の蚈画の代替案



最適な蚈画を決定できない堎合は、䞀時テヌブルを䜿甚しおください。



CREATE TEMPORARY TABLE selected_seats (
  id INT NOT NULL PRIMARY KEY,
  y INT,
  x INT,
  UNIQUE (y, x)
)
SELECT id, y, x
FROM seats WHERE venue_id = @venue_id;

WITH RECURSIVE
groupings (p_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s WHERE s.id = (
    SELECT ss.id
    FROM selected_seats ss
    WHERE (ss.y, ss.x) > (p_y, p_x)
    ORDER BY ss.y, ss.x
    LIMIT 1
    )
)
SELECT * FROM groupings;


このク゚リでむンデックススキャンが枡されたずしおも、テヌブルがselected_seats非垞に小さいため、倚くの費甚がかかりたす。



結論



効率的であるが欠陥のあるワヌクフロヌが、MySQL8.0で導入された非垞に単玔な機胜に眮き換えられるこずを非垞に嬉しく思いたす。



それたでの間、8.0の新機胜の開発は継続されおおり、すでに成功しおいるリリヌスがさらに改善されおいたす。



成功した再垰



翻蚳者からのPS



私たちのブログも読んでください



  • " MySQLPercona Serverを5.7から8.0にアップグレヌドする";
  • "デヌタベヌスずKubernetesレビュヌずビデオレポヌト ";
  • 「より倚くの開発者がデヌタベヌスに぀いおこれを知る必芁がありたす。」



All Articles