Clever Geek Handbook

استخدام وظائف النوافذ و CTEs في MySQL 8.0 لتنفيذ إجمالي تراكمي دون اختراق





تقريبا. ترجمة. : في هذه المقالة ، يشارك قائد فريق شركة Ticketsolve في المملكة المتحدة حلاً لمشكلته المحددة للغاية ، مع توضيح الأساليب العامة لإنشاء ما يسمى بالوظائف المتراكمة باستخدام ميزات MySQL 8.0 الحديثة. قوائمه واضحة ومزودة بتفسيرات مفصلة ، مما يساعد على فهم جوهر المشكلة ، حتى بالنسبة لأولئك الذين لم يتعمقوا فيها.



تتمثل الإستراتيجية الشائعة لإجراء التحديثات باستخدام الوظائف التراكمية في MySQL في استخدام المتغيرات والنمط المخصصUPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)).



هذا النمط لا يعمل بشكل جيد مع المُحسِّن (مما يؤدي إلى سلوك غير حتمي) ، لذلك قرروا التخلي عنه. والنتيجة هي نوع من الفراغ ، لأن المنطق المعقد (نسبيًا) أصبح الآن أكثر صعوبة في التنفيذ (على الأقل بنفس البساطة).



ستناقش المقالة طريقتين لتنفيذه: استخدام وظائف النافذة (النهج المتعارف عليه) واستخدام CTE العودية (تعبيرات الجدول العام).



المتطلبات والخلفية



على الرغم من أن CTEs بديهية إلى حد ما ، بالنسبة لأولئك الذين ليسوا على دراية بها ، أوصي بالإشارة إلى مشاركتي السابقة حول هذا الموضوع .



وينطبق الشيء نفسه على وظائف النافذة: سأعلق على الاستفسارات / المفاهيم بالتفصيل ، لكن الفكرة العامة لا تزال غير مؤذية. يتم تخصيص عدد كبير من الكتب والمنشورات لوظائف النوافذ (وهذا هو السبب في أنني لم أكتب عنها بعد) ؛ ومع ذلك ، في معظم الأمثلة ، يتم إجراء الحسابات إما على النتائج المالية أو على المؤشرات الديموغرافية. ومع ذلك ، في هذه المقالة سأستخدم حالة حقيقية.



من ناحية البرنامج ، أوصي باستخدام 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. من ناحية أخرى ، لا نريد استخدام جدول يحتوي على سجلاته بالكامل في الفهرس (فقط لنكون أقرب إلى المشاكل الحقيقية).



بناءً على الرسم البياني أعلاه ، فإن إحداثيات كل موقع هي (ص ، س):



  • (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 بطريقة مثيرة للاهتمام (نأمل!): تمثل كل حلقة معرفًا للمكان ، ولكن نظرًا لأننا نريد إنشاء مواقع متعددة لكل مكان ، فإننا نقوم بربط مشترك مع الجدول الذي يحتوي على المواقع.
  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.



بشكل عام ، تعمل طبيعة "الانزلاق" لوظائف النافذة بشكل جيد مع الدوال التراكمية. ومع ذلك ، تتطلب بعض الوظائف التراكمية المعقدة نتائج التعبير الأخير - الوظيفة التي لا تدعمها وظائف النافذة لأنها تعمل على أعمدة.



هذا لا يعني أن المشكلة لا يمكن حلها ؛ إنها تحتاج فقط إلى إعادة التفكير.



في حالتنا ، يمكن تقسيم المهمة إلى جزأين. يمكن اعتبار التجميع لكل موقع مجموع قيمتين:



  • الرقم التسلسلي لكل مكان ،
  • القيمة التراكمية للزيادات في كل الأماكن السابقة لهذا المكان.


سيتعرف أولئك الذين يعرفون وظائف النوافذ على الأنماط النموذجية هنا.



الرقم التسلسلي لكل مقعد هو وظيفة مضمنة: 



ROW_NUMBER() OVER <window>


لكن مع القيمة التراكمية ، كل شيء أكثر إثارة للاهتمام ... لحسابه ، نقوم بعملين:



  • احسب الزيادة لكل مكان واكتبها على الجدول (أو 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 |
-- +-------+---+---+----------+


عظيم!



(لاحظ أنني أغفلت التحديث من الآن فصاعدًا من أجل البساطة.)



دعنا نحلل الطلب.



منطق رفيع المستوى



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 ، يمكنك تحديد القيمة المرغوبة كمعامل ثالث: 



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


من خلال تحديد القيم الافتراضية ، نتأكد من أن المكان الأول في حدود النافذة سيكون له نفس منطق المكان التالي (x-1) وبدون تغيير الصف (y).



الحل البديل هو استخدام 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)


المعلمة الثانية LAG()هي عدد المواضع المراد الرجوع إليها داخل النافذة ؛ 1 هي القيمة السابقة (وهي أيضًا القيمة الافتراضية).



الجوانب الفنية



النوافذ المسماة



يستخدم استعلامنا نفس النافذة عدة مرات. الاستعلامات التالية متكافئة رسميًا: 



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;


ومع ذلك ، قد ينتج عن الثاني سلوك دون المستوى الأمثل (الذي صادفته - على الأقل في الماضي): يمكن للمحسن اعتبار النوافذ مستقلة وحساب كل منها على حدة. لهذا السبب ، أنصحك دائمًا باستخدام النوافذ المسماة (على الأقل عند تكرارها).



التقسيم حسب البيان



عادة ما يتم تنفيذ وظائف النوافذ على القسم. في حالتنا ، سيبدو كما يلي: 



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


… غير معرف. دعنا ننتقل إلى الدليل :



يتم تحديد سلاسل نتيجة الاستعلام من جملة FROM بعد تنفيذ الجمل WHERE و GROUP BY و HAVING ، ويتم التنفيذ داخل النافذة قبل ORDER BY و LIMIT و SELECT DISTINCT.


بعض الاعتبارات



بشكل عام ، بالنسبة لهذا النوع من المشاكل ، من المنطقي حساب تغير الحالة لكل سجل ثم جمعها - بدلاً من تمثيل كل سجل كدالة للسجل السابق.



هذا الحل أكثر تعقيدًا من الوظيفة التي يستبدلها ، ولكنه في نفس الوقت موثوق به. للأسف ، هذا النهج ليس دائمًا ممكنًا أو سهل التنفيذ. هذا هو المكان الذي يلعب فيه الاعتلال الدماغي المزمن العودي.



الأسلوب الحديث رقم 2: الاعتلال الدماغي الرهيب التكراري



يتطلب هذا النهج القليل من الخداع بسبب القدرات المحدودة لـ CTE في MySQL. من ناحية أخرى ، فهو حل مباشر يناسب الجميع ، لذلك لا يتطلب أي إعادة تفكير في نهج عالمي.



لنبدأ بنسخة مبسطة من الطلب النهائي: 



-- `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


المنطق واضح حتى بالنسبة لأولئك الذين ليسوا على دراية بالاعتلال الدماغي الرضحي المزمن. الصف الأول هو المقعد الأول في القاعة ، بالترتيب: 



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، ولكنها تستخدم ل إحداثيات lshimi (س، ص) في تسلسل (venue_id، س، ص).



الجزء s.venue_idفي تعبير الفرز مهم جدا! يسمح لنا باستخدام فهرس.



المشغل SELECT:



  • يؤدي التراكم (يحسب (p_)grouping) ،
  • يوفر قيمًا للوضع الحالي ( s.id، s.venue_id، s.y، s.x) في الدورة التالية.


نختار FROM groupingsتلبية متطلبات التكرار CTE.



الأمر المثير للاهتمام هنا هو أننا نستخدم CTE العودي كمكرر ، حيث نجلب من جدول groupingsفي استعلام فرعي متكرر وربطه seatsللعثور على البيانات لمزيد من المعالجة.



JOINيتم عرضه رسميًا ، ولكن LIMITيتم إرجاع سجل واحد فقط بسبب عامل التشغيل .



نسخة العمل



لسوء الحظ ، لا يعمل الاستعلام أعلاه لأنه ORDER BYغير مدعوم حاليًا في الاستعلامات الفرعية العودية. بالإضافة إلى ذلك ، LIMITتختلف الدلالات المستخدمة هنا عن الدلالات النموذجية التي تنطبق على استعلام خارجي :



يتم دعم LIMIT الآن [..] التأثير على مجموعة البيانات الناتجة مماثل لاستخدام LIMIT مع SELECT خارجي




ومع ذلك ، هذه ليست مشكلة خطيرة. دعنا نلقي نظرة على نسخة العمل: 



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، ونحن نفعل ذلك ضمن فرعي وتمريرها إلى الاستعلام الخارجي، أي بعد ذلك يختار فقط سجل الهدف.



تأملات في الأداء



دعنا نفحص خطة تنفيذ الاستعلام باستخدام شرح تحليل: 



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الكثير ، لأن الجدول صغير جدًا.



خاتمة



يسعدني جدًا أنه يمكن الآن استبدال سير العمل الفعال ولكن المعيب بوظيفة بسيطة إلى حد ما تم تقديمها في MySQL 8.0.



في غضون ذلك ، يستمر تطوير الميزات الجديدة لـ 8.0 ، مما يجعل الإصدار الناجح بالفعل أفضل.



العودية الناجحة!



PS من المترجم



اقرأ أيضًا على مدونتنا:





More articles:


All Articles