Approx. terjemahan. : Dalam artikel ini, pemimpin tim perusahaan Inggris Ticketsolve berbagi solusi untuk masalah yang sangat spesifik, sambil mendemonstrasikan pendekatan umum untuk membuat apa yang disebut fungsi akumulasi menggunakan kapabilitas modern MySQL 8.0. Daftarnya jelas dan dilengkapi dengan penjelasan terperinci, yang membantu untuk memahami esensi masalah, bahkan bagi mereka yang tidak menyelami terlalu dalam.
Strategi umum untuk melakukan pembaruan menggunakan fungsi kumulatif di MySQL menggunakan variabel dan pola khusus
UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol)).
Pola ini tidak bekerja dengan baik dengan pengoptimal (mengarah ke perilaku non-deterministik), jadi mereka memutuskan untuk meninggalkannya. Hasilnya adalah semacam kekosongan karena logika (yang relatif) kompleks sekarang lebih sulit untuk diterapkan (setidaknya dengan kesederhanaan yang sama).
Artikel ini akan membahas dua cara untuk mengimplementasikannya: menggunakan fungsi jendela (pendekatan kanonik) dan menggunakan CTE rekursif (ekspresi tabel umum).
Persyaratan dan latar belakang
Meskipun CTE cukup intuitif, bagi mereka yang tidak terlalu mengenalnya, saya sarankan untuk merujuk ke posting saya sebelumnya tentang topik ini .
Hal yang sama berlaku untuk fungsi jendela: Saya akan mengomentari pertanyaan / konsep secara rinci, tetapi gambaran umum tetap tidak ada salahnya. Ada banyak sekali buku dan publikasi yang dikhususkan untuk fungsi jendela (itulah sebabnya saya masih belum menulis tentang mereka); namun, pada kebanyakan contoh, penghitungan dilakukan baik pada hasil keuangan atau indikator demografis. Namun, pada artikel ini saya akan menggunakan kasus nyata.
Untuk perangkat lunak, saya merekomendasikan menggunakan MySQL 8.0.19 (tetapi tidak wajib). Semua ekspresi harus dijalankan di konsol yang sama untuk digunakan kembali
@venue_id.
Dalam dunia perangkat lunak, terdapat dilema arsitektur yang terkenal: haruskah logika diterapkan pada level aplikasi atau pada level database? Meskipun ini adalah pertanyaan yang benar-benar valid, dalam kasus kami, saya mengasumsikan bahwa logika harus tetap pada tingkat dasar; alasan untuk ini mungkin, misalnya, persyaratan kecepatan (seperti yang terjadi dalam kasus kami).
Tugas
Dalam tugas ini, kami mengalokasikan kursi di aula (teater) tertentu.
Untuk tujuan bisnis, setiap lokasi perlu diberi apa yang disebut "pengelompokan" - nomor tambahan yang mewakilinya.
Berikut algoritma untuk menentukan nilai pengelompokan:
- mulai dari 0 dan kiri atas;
- jika ada ruang kosong antara arus dan yang sebelumnya, atau ini adalah baris baru, maka kami menambahkan 2 ke nilai sebelumnya (jika ini bukan tempat pertama absolut), jika tidak, kami menambah nilai sebesar 1;
- menetapkan pengelompokan ke suatu tempat;
- pergi ke tempat baru di baris yang sama atau ke baris berikutnya (jika yang sebelumnya sudah selesai) dan ulangi dari poin 2; kami melanjutkan semuanya sampai tempat habis.
Algoritma dalam pseudocode:
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
Dalam kehidupan nyata, kami ingin konfigurasi di sebelah kiri memberikan nilai yang ditunjukkan di sebelah kanan:
x→ 0 1 2 0 1 2
y ╭───┬───┬───╮ ╭───┬───┬───╮
↓ 0 │ x │ x │ │ │ 1 │ 2 │ │
├───┼───┼───┤ ├───┼───┼───┤
1 │ x │ │ x │ │ 4 │ │ 6 │
├───┼───┼───┤ ├───┼───┼───┤
2 │ x │ │ │ │ 8 │ │ │
╰───┴───┴───╯ ╰───┴───┴───╯
Latihan
Biarkan tabel dasar memiliki struktur minimalis berikut:
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)
);
Kami tidak terlalu membutuhkan kolom
rowdan number. Di sisi lain, kami tidak ingin menggunakan tabel yang catatannya sepenuhnya terdapat dalam indeks (hanya untuk lebih mendekati masalah nyata).
Berdasarkan diagram di atas, koordinat setiap lokasi adalah (y, x):
- (0, 0), (0, 1)
- (1, 0), (1, 2)
- (20)
Perhatikan bahwa kami menggunakan y sebagai koordinat pertama karena memudahkan untuk melacak baris.
Anda harus memuat record dalam jumlah yang cukup besar untuk mencegah pengoptimal menemukan jalur pendek yang tidak diharapkan. Tentu saja, kami menggunakan CTE rekursif:
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;
Beberapa catatan:
- Di sini, CTE digunakan dengan cara yang menarik (semoga!): Setiap loop mewakili venue_id, tetapi karena kami ingin beberapa lokasi dibuat untuk setiap tempat, kami melakukan penggabungan silang dengan tabel yang berisi lokasi.
- Konstruktor baris v8.0.19 (
VALUES ROW()...) digunakan untuk merepresentasikan tabel (dapat digabungkan ) tanpa benar-benar membuatnya. - Menghasilkan nilai acak untuk baris dan nomor sebagai tempat penampung.
- Demi kesederhanaan, kami tidak melakukan pengoptimalan apa pun (misalnya, tipe data lebih luas dari yang diperlukan; indeks ditambahkan sebelum memasukkan record, dll.).
Pendekatan lama
Pendekatan lama yang baik cukup mudah dan langsung:
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)
Itu mudah (tapi jangan lupakan peringatannya)!
Penyimpangan kecil: dalam hal ini, saya menggunakan properti aritmatika Boolean. Ekspresi berikut ini setara:
SELECT seats.x > @x + 1 OR seats.y != @y `increment`;
SELECT IF (
seats.x > @x + 1 OR seats.y != @y,
1,
0
) `increment`;
Beberapa menemukan ini intuitif, yang lain tidak; ini masalah selera. Mulai sekarang saya akan menggunakan ekspresi yang lebih ringkas.
Mari kita lihat hasilnya:
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 |
-- +-------+------+------+----------+
Pendekatan yang bagus!
Sayangnya, ini memiliki kelemahan "kecil": berfungsi dengan baik kecuali jika tidak berfungsi ...
Intinya adalah bahwa pengoptimal kueri tidak perlu melakukan kalkulasi dari kiri ke kanan, jadi penetapan (: =) dapat dilakukan dalam urutan yang salah mengarah ke hasil yang salah. Orang sering menghadapi masalah ini setelah memperbarui MySQL.
Di MySQL 8.0, fungsi ini memang sudah usang:
-- 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)'.
-- [...]
Baiklah, mari kita perbaiki situasinya!
Pendekatan Modern # 1: Fungsi Jendela
Pengenalan fungsi jendela telah menjadi peristiwa yang sangat dinantikan di dunia MySQL.
Secara umum, sifat "geser" dari fungsi jendela berfungsi baik dengan fungsi kumulatif. Namun, beberapa fungsi kumulatif kompleks memerlukan hasil dari ekspresi terakhir - fungsionalitas yang tidak didukung oleh fungsi jendela karena beroperasi pada kolom.
Ini tidak berarti bahwa masalah tidak dapat diselesaikan; ini hanya perlu dipikirkan kembali.
Dalam kasus kami, tugas dapat dibagi menjadi dua bagian. Pengelompokan untuk setiap lokasi dapat dianggap sebagai jumlah dari dua nilai:
- nomor seri setiap tempat,
- nilai kumulatif dari kenaikan semua tempat sebelum tempat ini.
Mereka yang akrab dengan fungsi windowing akan mengenali pola tipikal di sini.
Nomor urut setiap kursi adalah fungsi bawaan:
ROW_NUMBER() OVER <window>
Tetapi dengan nilai kumulatif, semuanya jauh lebih menarik ... Untuk menghitungnya, kami melakukan dua tindakan:
- hitung kenaikan untuk setiap tempat dan tuliskan ke tabel (atau CTE),
- kemudian, untuk setiap lokasi, kami menjumlahkan kenaikan untuk lokasi tersebut menggunakan fungsi jendela.
Mari kita lihat 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 |
-- +-------+---+---+----------+
Bagus!
(Perhatikan bahwa saya menghilangkan UPDATE mulai sekarang demi kesederhanaan.)
Mari kita analisis permintaannya.
Logika tingkat tinggi
CTE berikut (diedit) :
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 |
-- +-------+-----------+
… Menghitung kenaikan untuk setiap lokasi dari lokasi sebelumnya (lebih lanjut
LAG()nanti). Ini berfungsi pada setiap rekaman dan yang mendahuluinya serta tidak kumulatif.
Sekarang, untuk menghitung kenaikan kumulatif, kita cukup menggunakan fungsi jendela untuk menghitung jumlah dan termasuk setiap lokasi:
-- (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.)
Fungsi jendela LAG ()
Fungsi LAG, dalam bentuknya yang paling sederhana (
LAG(x)), mengembalikan nilai sebelumnya dari kolom tertentu. Ketidaknyamanan klasik dengan fungsi tersebut adalah menangani entri pertama di jendela. Karena tidak ada record sebelumnya, mereka mengembalikan NULL. Dalam kasus LAG, Anda dapat menentukan nilai yang diinginkan sebagai parameter ketiga:
LAG(x, 1, x - 1) -- `x -1`
LAG(y, 1, y) -- `y`
Dengan menentukan nilai default, kami memastikan bahwa tempat pertama di batas jendela akan memiliki logika yang sama dengan tempat setelah yang lain (x-1) dan tanpa mengubah baris (y).
Solusi alternatif adalah dengan menggunakan
IFNULL, bagaimanapun, ekspresi sangat rumit:
-- , !
--
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)
Parameter kedua
LAG()adalah jumlah posisi untuk mundur di dalam jendela; 1 adalah nilai sebelumnya (ini juga merupakan nilai default).
Aspek teknik
Jendela bernama
Kueri kami menggunakan jendela yang sama berkali-kali. Dua kueri berikut secara resmi setara:
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;
Namun, yang kedua dapat menyebabkan perilaku sub-optimal (yang telah saya temui - setidaknya di masa lalu): pengoptimal dapat mempertimbangkan jendela independen dan menghitung masing-masing secara terpisah. Untuk alasan ini, saya menyarankan Anda untuk selalu menggunakan jendela bernama (setidaknya saat jendela berulang).
Pernyataan PARTITION BY
Biasanya fungsi windowing dilakukan pada sebuah partisi. Dalam kasus kami, ini akan terlihat seperti ini:
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); -- !
Karena jendela cocok dengan kumpulan rekaman lengkap (yang difilter oleh kondisi
WHERE), kami tidak perlu menentukannya (partisi).
Tetapi jika kueri ini harus dijalankan di seluruh tabel
seats, maka itu harus dilakukan agar jendela disetel ulang untuk semua orang venue_id.
Penyortiran
Permintaan
ORDER BYditetapkan di tingkat jendela:
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)
Dalam kasus ini, pengurutan jendela terpisah dari SELECT. Ini sangat penting! Perilaku permintaan ini:
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
… tidak terdefinisi. Mari beralih ke manual :
String hasil kueri ditentukan dari klausa FROM setelah klausa WHERE, GROUP BY, dan HAVING dijalankan, dan eksekusi jendela terjadi sebelum ORDER BY, LIMIT, dan SELECT DISTINCT.
Beberapa pertimbangan
Secara umum, untuk jenis masalah ini, masuk akal untuk menghitung perubahan status untuk setiap record dan kemudian menjumlahkannya - alih-alih merepresentasikan setiap record sebagai fungsi dari record sebelumnya.
Solusi ini lebih kompleks daripada fungsionalitas yang digantikannya, tetapi pada saat yang sama dapat diandalkan. Sayangnya, pendekatan ini tidak selalu memungkinkan atau mudah diterapkan. Di sinilah CTE rekursif berperan.
Pendekatan Modern # 2: CTE Rekursif
Pendekatan ini membutuhkan sedikit tipu daya karena keterbatasan kemampuan CTE di MySQL. Di sisi lain, ini adalah solusi langsung satu ukuran untuk semua, sehingga tidak memerlukan pemikiran ulang tentang pendekatan global.
Mari kita mulai dengan versi sederhana dari permintaan akhir:
-- `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;
Bingo! Kueri ini (relatif) sederhana, tetapi yang lebih penting, ini mengekspresikan fungsi pengelompokan kumulatif dengan cara yang paling sederhana:
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
Logikanya jelas bahkan bagi mereka yang tidak terlalu mengenal CTE. Baris pertama adalah kursi pertama di aula, dengan urutan:
SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1
Di bagian rekursif, kami mengulangi:
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
Kondisi
WHEREbersama-sama dengan operator ORDER BYdan LIMIThanya menemukan tempat berikutnya, tempat dengan yang sama venue_id, tetapi digunakan untuk koordinat lshimi (x, y) dalam urutan (venue_id, x, y).
Bagian
s.venue_iddalam ekspresi pengurutan sangat penting! Ini memungkinkan kita menggunakan indeks.
Operator
SELECT:
- melakukan akumulasi (menghitung
(p_)grouping), - menyediakan nilai untuk posisi saat ini (
s.id,s.venue_id,s.y,s.x) pada siklus berikutnya.
Kami memilih
FROM groupingsuntuk memenuhi persyaratan rekursif CTE.
Yang menarik di sini adalah kami menggunakan CTE rekursif sebagai iterator, mengambil dari tabel
groupingsdi subkueri rekursif dan menggabungkannya dengan seatsuntuk menemukan data untuk diproses lebih lanjut.
JOINsecara resmi disilangkan, tetapi LIMIThanya satu record yang dikembalikan karena operator .
Versi kerja
Sayangnya, kueri di atas tidak berfungsi karena saat
ORDER BYini tidak didukung di subkueri rekursif. Selain itu, semantik yang LIMITdigunakan di sini berbeda dari semantik umum yang berlaku untuk kueri eksternal :
LIMIT sekarang didukung [..] Efek pada kumpulan data yang dihasilkan sama seperti menggunakan LIMIT dengan SELECT eksternal
Namun, ini bukanlah masalah yang serius. Mari kita lihat versi yang berfungsi:
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 |
-- +-------+------+------+------------+
Agak tidak nyaman menggunakan subquery, tetapi pendekatan ini berfungsi dan boilerplate minimal di sini, karena beberapa ekspresi diperlukan juga.
Di sini, alih-alih melakukan pengurutan dan pembatasan yang terkait dengan gabungan
groupingsdan seats, kami melakukannya di dalam subkueri dan meneruskannya ke kueri luar, yang kemudian hanya memilih rekaman target.
Refleksi atas kinerja
Mari kita periksa rencana eksekusi kueri menggunakan JELASKAN ANALISIS:
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)
Rencana tersebut sesuai dengan ekspektasi. Dalam hal ini, dasar dari rencana optimal terletak pada pencarian indeks:
-> 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)
... sangat penting. Kinerja akan turun secara signifikan jika Anda melakukan pemindaian indeks (yaitu, memindai catatan indeks secara linier alih-alih mencari yang diperlukan sekaligus).
Dengan demikian, agar strategi ini bekerja, indeks yang ditautkan harus ada dan digunakan seefisien mungkin oleh pengoptimal.
Jika batasan tersebut dicabut di masa mendatang, maka kebutuhan untuk menggunakan subquery akan hilang, yang akan sangat menyederhanakan tugas pengoptimal.
Alternatif untuk rencana suboptimal
Jika rencana optimal tidak dapat ditentukan, gunakan tabel sementara:
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;
Meskipun pemindaian indeks diteruskan dalam kueri ini, biayanya
selected_seatssangat mahal , karena tabelnya sangat kecil.
Kesimpulan
Saya sangat senang bahwa alur kerja yang efisien namun cacat sekarang dapat diganti dengan fungsionalitas yang cukup sederhana yang diperkenalkan di MySQL 8.0.
Sementara itu, pengembangan fitur baru untuk 8.0 terus berlanjut, yang membuat rilis yang sudah sukses menjadi lebih baik.
Rekursi berhasil!
PS dari penerjemah
Baca juga di blog kami: