Rencana eksekusi kueri

Ringkasan

Halaman ini memberikan konsep tentang rencana eksekusi kueri dan cara penggunaannya oleh Spanner untuk menjalankan kueri di lingkungan terdistribusi. Untuk mempelajari cara mengambil rencana eksekusi untuk kueri tertentu menggunakan Google Cloud Console, lihat Memahami cara Spanner menjalankan kueri. Anda juga dapat melihat contoh paket kueri historis dan membandingkan performa kueri dari waktu ke waktu untuk kueri tertentu. Untuk mempelajari lebih lanjut, lihat Contoh paket kueri.

Spanner menggunakan pernyataan SQL deklaratif untuk membuat kueri database-nya. Pernyataan SQL menentukan apa yang diinginkan pengguna tanpa menentukan cara mendapatkan hasilnya. Rencana eksekusi kueri adalah serangkaian langkah untuk memperoleh hasil. Untuk pernyataan SQL tertentu, mungkin ada beberapa cara untuk mendapatkan hasilnya. Pengoptimal kueri Spanner mengevaluasi berbagai rencana eksekusi dan memilih rencana yang dianggap paling efisien. Spanner kemudian menggunakan rencana eksekusi untuk mengambil hasilnya.

Secara konseptual, rencana eksekusi adalah pohon operator relasional. Setiap operator membaca baris dari inputnya dan menghasilkan baris output. Hasil operator di root eksekusi ditampilkan sebagai hasil kueri SQL.

Sebagai contoh, kueri ini:

SELECT s.SongName FROM Songs AS s;

menghasilkan rencana eksekusi kueri yang dapat divisualisasikan sebagai:

Contoh rencana eksekusi kueri

Kueri dan rencana eksekusi di halaman ini didasarkan pada skema database berikut:

CREATE TABLE Singers (
  SingerId   INT64 NOT NULL,
  FirstName  STRING(1024),
  LastName   STRING(1024),
  SingerInfo BYTES(MAX),
  BirthDate  DATE
) PRIMARY KEY(SingerId);

CREATE INDEX SingersByFirstLastName ON Singers(FirstName, LastName);

CREATE TABLE Albums (
  SingerId        INT64 NOT NULL,
  AlbumId         INT64 NOT NULL,
  AlbumTitle      STRING(MAX),
  MarketingBudget INT64
) PRIMARY KEY(SingerId, AlbumId),
  INTERLEAVE IN PARENT Singers ON DELETE CASCADE;

CREATE INDEX AlbumsByAlbumTitle ON Albums(AlbumTitle);

CREATE INDEX AlbumsByAlbumTitle2 ON Albums(AlbumTitle) STORING (MarketingBudget);

CREATE TABLE Songs (
  SingerId  INT64 NOT NULL,
  AlbumId   INT64 NOT NULL,
  TrackId   INT64 NOT NULL,
  SongName  STRING(MAX),
  Duration  INT64,
  SongGenre STRING(25)
) PRIMARY KEY(SingerId, AlbumId, TrackId),
  INTERLEAVE IN PARENT Albums ON DELETE CASCADE;

CREATE INDEX SongsBySingerAlbumSongNameDesc ON Songs(SingerId, AlbumId, SongName DESC), INTERLEAVE IN Albums;

CREATE INDEX SongsBySongName ON Songs(SongName);

CREATE TABLE Concerts (
  VenueId      INT64 NOT NULL,
  SingerId     INT64 NOT NULL,
  ConcertDate  DATE NOT NULL,
  BeginTime    TIMESTAMP,
  EndTime      TIMESTAMP,
  TicketPrices ARRAY<INT64>
) PRIMARY KEY(VenueId, SingerId, ConcertDate);

Anda dapat menggunakan pernyataan Bahasa Manipulasi Data (DML) berikut untuk menambahkan data ke tabel ini:

INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate)
VALUES (1, "Marc", "Richards", "1970-09-03"),
       (2, "Catalina", "Smith", "1990-08-17"),
       (3, "Alice", "Trentor", "1991-10-02"),
       (4, "Lea", "Martin", "1991-11-09"),
       (5, "David", "Lomond", "1977-01-29");

INSERT INTO Albums (SingerId, AlbumId, AlbumTitle)
VALUES (1, 1, "Total Junk"),
       (1, 2, "Go, Go, Go"),
       (2, 1, "Green"),
       (2, 2, "Forever Hold Your Peace"),
       (2, 3, "Terrified"),
       (3, 1, "Nothing To Do With Me"),
       (4, 1, "Play");

INSERT INTO Songs (SingerId, AlbumId, TrackId, SongName, Duration, SongGenre)
VALUES (2, 1, 1, "Let's Get Back Together", 182, "COUNTRY"),
       (2, 1, 2, "Starting Again", 156, "ROCK"),
       (2, 1, 3, "I Knew You Were Magic", 294, "BLUES"),
       (2, 1, 4, "42", 185, "CLASSICAL"),
       (2, 1, 5, "Blue", 238, "BLUES"),
       (2, 1, 6, "Nothing Is The Same", 303, "BLUES"),
       (2, 1, 7, "The Second Time", 255, "ROCK"),
       (2, 3, 1, "Fight Story", 194, "ROCK"),
       (3, 1, 1, "Not About The Guitar", 278, "BLUES");

Mendapatkan rencana eksekusi yang efisien bukanlah hal yang mudah karena Spanner membagi data menjadi bagian. Bagian dapat berpindah secara independen satu sama lain dan ditetapkan ke server yang berbeda, yang mungkin berada di lokasi fisik yang berbeda. Untuk mengevaluasi rencana eksekusi pada data terdistribusi, Spanner menggunakan eksekusi berdasarkan:

  • eksekusi lokal subpaket di server yang berisi data tersebut
  • orkestrasi dan agregasi beberapa eksekusi jarak jauh dengan pemangkasan distribusi yang agresif

Spanner menggunakan operator primitif distributed union, beserta varian distributed cross apply dan distributed outer apply, untuk mengaktifkan model ini.

Contoh paket kueri

Dengan paket kueri Spanner, Anda dapat melihat contoh paket kueri historis dan membandingkan performa kueri dari waktu ke waktu. Tidak semua kueri memiliki contoh paket kueri. Hanya kueri yang menggunakan CPU lebih tinggi yang mungkin diambil sampelnya. Retensi data untuk contoh paket kueri Spanner adalah 30 hari. Anda dapat menemukan contoh paket kueri di halaman Query insights pada Konsol Google Cloud. Untuk mengetahui petunjuknya, buka Melihat contoh paket kueri.

Anatomi paket kueri sampel sama dengan rencana eksekusi kueri reguler. Untuk mengetahui informasi selengkapnya tentang cara memahami rencana visual dan menggunakannya untuk men-debug kueri, lihat Tur visualizer rencana kueri.

Kasus penggunaan umum untuk contoh paket kueri:

Beberapa kasus penggunaan umum untuk contoh paket kueri mencakup:

Jika performa kueri menunjukkan perbedaan yang signifikan dari waktu ke waktu atau jika Anda ingin meningkatkan performa kueri, lihat praktik terbaik SQL untuk membuat pernyataan kueri yang dioptimalkan agar membantu Spanner menemukan rencana eksekusi yang efisien.

Hidup sebuah kueri

Kueri SQL di Spanner pertama kali dikompilasi ke dalam rencana eksekusi, lalu dikirim ke server root awal untuk dieksekusi. Server root dipilih untuk meminimalkan jumlah hop yang dilakukan guna mencapai data yang sedang dikueri. Kemudian, server root:

  • memulai eksekusi subrencana dari jarak jauh (jika perlu)
  • menunggu hasil dari eksekusi jarak jauh
  • menangani langkah eksekusi lokal yang tersisa seperti menggabungkan hasil
  • menampilkan hasil kueri

Server jarak jauh yang menerima subpaket bertindak sebagai server "root" untuk subrencananya, mengikuti model yang sama dengan server root teratas. Hasilnya adalah pohon eksekusi jarak jauh. Secara konseptual, eksekusi kueri mengalir dari atas ke bawah, dan hasil kueri ditampilkan dari bawah ke atas.Diagram berikut menunjukkan pola ini:

Paket kueri konseptual

Contoh berikut menggambarkan pola ini secara lebih detail.

Kueri agregat

Kueri gabungan mengimplementasikan kueri GROUP BY.

Misalnya, dengan menggunakan kueri ini:

SELECT s.SingerId, COUNT(*) AS SongCount
FROM Songs AS s
WHERE s.SingerId < 100
GROUP BY s.SingerId;

Berikut adalah hasilnya:

+----------+-----------+
| SingerId | SongCount |
+----------+-----------+
|        3 |         1 |
|        2 |         8 |
+----------+-----------+

Secara konseptual, ini adalah rencana eksekusi:

Rencana eksekusi kueri gabungan

Spanner mengirimkan rencana eksekusi ke server root yang mengkoordinasikan eksekusi kueri dan melakukan distribusi subrencana dari jarak jauh.

Rencana eksekusi ini dimulai dengan gabungan terdistribusi, yang mendistribusikan subrencana ke server jarak jauh yang pemisahannya memenuhi SingerId < 100. Setelah pemindaian pada setiap bagian selesai, operator agregat aliran menggabungkan baris untuk mendapatkan jumlah setiap SingerId. Operator serialize result kemudian melakukan serialisasi hasilnya. Terakhir, distributed union menggabungkan semua hasil dan menampilkan hasil kueri.

Anda dapat mempelajari agregat lebih lanjut di operator agregat.

Kueri bergabung dengan lokasi bersama

Tabel yang disisipkan secara fisik disimpan dengan baris tabel terkait yang ditempatkan bersama. Gabungan lokasi yang sama adalah gabungan antara tabel yang disisipkan. Gabungan lokasi yang sama dapat menawarkan manfaat performa dibandingkan gabungan yang memerlukan indeks atau back join.

Misalnya, dengan menggunakan kueri ini:

SELECT al.AlbumTitle, so.SongName
FROM Albums AS al, Songs AS so
WHERE al.SingerId = so.SingerId AND al.AlbumId = so.AlbumId;

(Kueri ini mengasumsikan bahwa Songs disisipkan dalam Albums.)

Berikut adalah hasilnya:

+-----------------------+--------------------------+
| AlbumTitle            | SongName                 |
+-----------------------+--------------------------+
| Nothing To Do With Me | Not About The Guitar     |
| Green                 | The Second Time          |
| Green                 | Starting Again           |
| Green                 | Nothing Is The Same      |
| Green                 | Let's Get Back Together  |
| Green                 | I Knew You Were Magic    |
| Green                 | Blue                     |
| Green                 | 42                       |
| Terrified             | Fight Story              |
+-----------------------+--------------------------+

Berikut adalah rencana eksekusinya:

Rencana eksekusi kueri bergabung ditempatkan bersama

Rencana eksekusi ini dimulai dengan gabungan terdistribusi, yang mendistribusikan subpaket ke server jarak jauh yang memiliki bagian dari tabel Albums. Karena Songs adalah tabel Albums yang disisipkan, setiap server jarak jauh dapat menjalankan seluruh subpaket pada setiap server jarak jauh tanpa perlu bergabung ke server yang berbeda.

Subrencana berisi penerapan silang. Setiap penerapan silang menjalankan tabel pemindaian pada tabel Albums untuk mengambil SingerId, AlbumId, dan AlbumTitle. Penerapan silang kemudian memetakan output dari pemindaian tabel ke output dari pemindaian indeks pada indeks SongsBySingerAlbumSongNameDesc, dengan tunduk pada filter SingerId dalam indeks yang cocok dengan SingerId dari output pemindaian tabel. Setiap penerapan silang mengirimkan hasilnya ke operator serialisasi hasil yang melakukan serialisasi data AlbumTitle dan SongName serta menampilkan hasil ke gabungan terdistribusi lokal. Gabungan terdistribusi menggabungkan hasil dari union terdistribusi lokal dan menampilkannya sebagai hasil kueri.

Mengindeks dan kueri back join

Contoh di atas menggunakan gabungan pada dua tabel, satu disisipi di tabel lainnya. Rencana eksekusi akan lebih kompleks dan kurang efisien jika dua tabel, atau satu tabel dan indeks, tidak disisipkan.

Pertimbangkan indeks yang dibuat dengan perintah berikut:

CREATE INDEX SongsBySongName ON Songs(SongName)

Gunakan indeks ini dalam kueri ini:

SELECT s.SongName, s.Duration
FROM Songs@{force_index=SongsBySongName} AS s
WHERE STARTS_WITH(s.SongName, "B");

Berikut adalah hasilnya:

+----------+----------+
| SongName | Duration |
+----------+----------+
| Blue     |      238 |
+----------+----------+

Berikut adalah rencana eksekusinya:

Kembali ke rencana eksekusi kueri bergabung

Rencana eksekusi yang dihasilkan rumit karena indeks SongsBySongName tidak berisi kolom Duration. Untuk mendapatkan nilai Duration, Spanner harus menggabungkan kembali hasil yang diindeks ke tabel Songs. Ini adalah gabungan, tetapi tidak ditempatkan bersama karena tabel Songs dan indeks global SongsBySongName tidak disisipi. Rencana eksekusi yang dihasilkan lebih kompleks daripada contoh gabungan yang ditempatkan bersama karena Spanner melakukan pengoptimalan untuk mempercepat eksekusi jika data tidak ditempatkan bersama.

Operator teratas adalah penerapan silang terdistribusi. Sisi input operator ini adalah batch baris dari indeks SongsBySongName yang memenuhi STARTS_WITH(s.SongName, "B") predikat. Penerapan silang terdistribusi kemudian memetakan batch ini ke server jarak jauh yang pemisahannya berisi data Duration. Server jarak jauh menggunakan pemindaian tabel untuk mengambil kolom Duration. Pemindaian tabel menggunakan filter Condition:($Songs_key_TrackId' = $batched_Songs_key_TrackId), yang menggabungkan TrackId dari tabel Songs ke TrackId baris yang dikelompokkan dari indeks SongsBySongName.

Hasilnya digabungkan menjadi jawaban kueri akhir. Selanjutnya, sisi input dari penerapan silang terdistribusi berisi pasangan union terdistribusi/pasangan union terdistribusi lokal untuk mengevaluasi baris dari indeks yang memenuhi predikat STARTS_WITH.

Pertimbangkan kueri yang sedikit berbeda yang tidak memilih kolom s.Duration:

SELECT s.SongName
FROM Songs@{force_index=SongsBySongName} AS s
WHERE STARTS_WITH(s.SongName, "B");

Kueri ini dapat memanfaatkan indeks sepenuhnya seperti yang ditunjukkan dalam rencana eksekusi ini:

Rencana eksekusi kueri yang lebih sederhana

Rencana eksekusi tidak memerlukan back join karena semua kolom yang diminta oleh kueri ada di indeks.

Langkah selanjutnya