Masalah kinerja SQL karena "pekerjaan yang tidak perlu namun wajib"

Tingkat pengetahuan yang diperlukan untuk memahami artikel: pemahaman umum tentang database dan SQL, beberapa pengalaman praktis dengan DBMS. Mungkin hal terpenting yang dapat Anda pelajari untuk menulis kueri SQL yang efektif adalah pengindeksan. Namun, yang kedua, yang sangat tertinggal, adalah pengetahuan bahwa banyak klien SQL memerlukan database untuk melakukan banyak "pekerjaan yang tidak perlu tetapi perlu" . Ulangi setelah saya: Apa yang dimaksud dengan “pekerjaan yang tidak perlu tetapi wajib”? Seperti yang dikatakan Kapten Obvious kepada kita, dia:

Tidak perlu

Biarkan aplikasi klien kami membutuhkan data berikut: Tidak ada yang aneh. Kami bekerja dengan database film (seperti database Sakila ) dan ingin menampilkan judul dan rating semua film kepada pengguna. Kueri berikut dapat memberikan hasil yang kita perlukan:

SELECT title, rating
FROM film

Namun, aplikasi kita (atau ORM kita) malah mengeksekusi query ini:

SELECT *
FROM film

Apa yang kita dapatkan sebagai hasilnya? Tebakan. Kami menerima banyak informasi yang tidak berguna: Di sebelah kanan Anda bahkan dapat melihat beberapa JSON kompleks sedang dimuat:

• dari disk
• untuk menyimpan cache
• dengan kawat
• dalam ingatan klien
• dan akhirnya dibuang [karena tidak perlu]

Ya, kami membuang sebagian besar informasi ini. Semua tindakan yang diambil untuk mengekstrak informasi ini ternyata sama sekali tidak berguna. Apakah itu benar? Apakah itu benar?

Wajib

Dan sekarang - bagian terburuknya. Meskipun pengoptimal sekarang dapat melakukan banyak hal, tindakan ini wajib untuk database. Basis data tidak memiliki cara untuk mengetahui bahwa aplikasi klien tidak memerlukan 95% data ini. Dan ini hanyalah contoh paling sederhana. Bayangkan menghubungkan beberapa tabel... Jadi apa, kata Anda, tapi database itu cepat? Izinkan saya mencerahkan Anda tentang beberapa hal yang mungkin belum terpikirkan. Tentu saja, waktu pelaksanaan permintaan individu tidak terlalu mempengaruhi apa pun. Oke, ini berjalan satu setengah kali lebih lambat, tapi kita akan melewatinya, bukan? Untuk kenyamanan? Terkadang ini benar. Namun jika Anda selalu mengorbankan performa demi kenyamanan , hal-hal kecil ini akan mulai bertambah. Kita tidak lagi berbicara tentang kinerja (kecepatan eksekusi permintaan individu), tetapi tentang throughput (waktu respons sistem), dan kemudian masalah serius akan dimulai, yang tidak mudah untuk diselesaikan. Saat itulah Anda kehilangan skalabilitas. Mari kita lihat rencana eksekusi, dalam hal ini Oracle DBMS:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 |   166K|
|   1 |  TABLE ACCESS FULL| FILM |  1000 |   166K|
--------------------------------------------------

dibandingkan dengan:

--------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes |
--------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1000 | 20000 |
|   1 |  TABLE ACCESS FULL| FILM |  1000 | 20000 |
--------------------------------------------------

Menjalankan kueri SELECT * alih-alih judul SELECT, peringkat menggunakan memori 8 kali lebih banyak dalam database. Tidak ada yang tidak terduga, bukan? Kami tahu ini akan terjadi. Namun kami tetap menyetujui hal ini untuk banyak permintaan kami yang tidak memerlukan semua data ini. Kami membuat pekerjaan yang tidak diperlukan namun wajib untuk database , yang terus menumpuk dan menumpuk. Kami menggunakan memori 8 kali lebih banyak dari yang dibutuhkan (tentu saja penggandanya akan berubah). Sementara itu, pada semua tahapan lainnya (I/O disk, transfer data melalui jaringan, konsumsi memori oleh klien) masalahnya persis sama, namun saya akan melewatkannya dan melihat...

Menggunakan Indeks

Kebanyakan database saat ini sudah mengapresiasi konsep cakupan indeks . Indeks penutup itu sendiri bukanlah jenis indeks khusus. Namun ini mungkin berubah menjadi "indeks khusus" untuk kueri tertentu, baik "secara tidak sengaja" atau karena memang dimaksudkan demikian. Pertimbangkan pertanyaan berikut:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Tidak ada yang tidak terduga dalam penerapannya. Ini adalah permintaan sederhana. Lihat rentang berdasarkan indeks, akses tabel - dan selesai:

-------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     8 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ACTOR               |     8 |
|*  2 |   INDEX RANGE SCAN          | IDX_ACTOR_LAST_NAME |     8 |
-------------------------------------------------------------------

Rencana yang bagus, bukan? Nah, jika kita benar-benar membutuhkan ini, maka tidak: Jelas sekali, kita membuang-buang memori, dll. Mari pertimbangkan kueri ini sebagai alternatif:

SELECT first_name, last_name
FROM actor
WHERE last_name LIKE 'A%'

Rencananya adalah ini:

----------------------------------------------------
| Id  | Operation        | Name            | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT |                 |     8 |
|*  1 |  INDEX RANGE SCAN| IDX_ACTOR_NAMES |     8 |
----------------------------------------------------

Kami dapat sepenuhnya menghilangkan akses ke tabel, berkat kehadiran indeks yang memenuhi semua kebutuhan kueri kami... indeks penutup. Apakah itu penting? Dan bagaimana! Pendekatan ini memungkinkan Anda untuk mempercepat beberapa kueri berdasarkan urutan besarnya (atau memperlambatnya berdasarkan urutan besarnya ketika indeks tidak lagi mencakup setelah beberapa perubahan). Indeks penutup tidak selalu dapat digunakan. Anda harus membayar untuk indeks dan Anda tidak boleh menambahkan terlalu banyak indeks. Namun dalam kasus ini, semuanya sudah jelas. Mari kita evaluasi kinerjanya:

SET SERVEROUTPUT ON
DECLARE
  v_ts TIMESTAMP;
  v_repeat CONSTANT NUMBER := 100000;
BEGIN
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Наихудший вариант requestа: перерасход памяти ПЛЮС доступ к таблице
      SELECT *
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 1 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Улучшенный request, но все равно с доступом к таблице
      SELECT /*+INDEX(actor(last_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 2 : ' || (SYSTIMESTAMP - v_ts));
  v_ts := SYSTIMESTAMP;

  FOR i IN 1..v_repeat LOOP
    FOR rec IN (
      -- Оптимальный request: покрывающий индекс
      SELECT /*+INDEX(actor(last_name, first_name))*/
        first_name, last_name
      FROM actor
      WHERE last_name LIKE 'A%'
    ) LOOP
      NULL;
    END LOOP;
  END LOOP;

  dbms_output.put_line('Оператор 3 : ' || (SYSTIMESTAMP - v_ts));
END;
/

Perhatikan bahwa tabel aktor hanya memiliki 4 kolom, sehingga perbedaan kinerja antara pernyataan 1 dan 2 tidak terlalu besar, namun tetap signifikan. Saya juga akan mencatat bahwa saya menggunakan petunjuk pengoptimal Oracle agar pengoptimal memilih satu atau beberapa indeks spesifik untuk kueri. Operator 3 adalah pemenang lomba kami yang tak terbantahkan. Kinerjanya jauh lebih baik, dan kita berbicara tentang kueri yang sangat sederhana. Sekali lagi, ketika kita menulis SELECT *, kita membuat pekerjaan yang tidak perlu tetapi wajib untuk database yang tidak dapat dioptimalkan. Dia tidak akan memilih indeks penutup karena memiliki overhead yang sedikit lebih tinggi daripada indeks LAST_NAME yang dia pilih, dan, antara lain, dia masih harus mengakses tabel untuk mengambil kolom LAST_UPDATE yang tidak berguna, misalnya. Namun semakin dalam kita menganalisis SELECT *, ternyata keadaannya semakin buruk. Mari Bicara tentang...

Konversi SQL

Pengoptimal berkinerja sangat baik karena mereka mengubah kueri SQL ( Saya berbicara tentang cara kerjanya dalam pembicaraan saya baru-baru ini di Voxxed Days di Zurich ). Misalnya, ada transformasi "pengecualian GABUNG" yang sangat kuat. Pertimbangkan tampilan pembantu berikut yang harus kita buat untuk menghindari penggabungan semua tabel ini secara manual setiap saat:

CREATE VIEW v_customer AS
SELECT
  c.first_name, c.last_name,
  a.address, ci.city, co.country
FROM customer c
JOIN address a USING (address_id)
JOIN city ci USING (city_id)
JOIN country co USING (country_id)

Tampilan ini hanya melakukan semua gabungan "...-ke-satu" antara tabel pelanggan PELANGGAN dan berbagai tabel untuk bagian alamatnya. Terima kasih, normalisasi. Bayangkan, setelah bekerja sedikit dengan tampilan ini, kita menjadi terbiasa dan melupakan tabel yang mendasarinya. Dan sekarang kami menjalankan kueri berikut:

SELECT *
FROM v_customer

Hasilnya, kami mendapatkan rencana yang sangat mengesankan:

----------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost |
----------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   599 | 47920 |   14 |
|*  1 |  HASH JOIN           |          |   599 | 47920 |   14 |
|   2 |   TABLE ACCESS FULL  | COUNTRY  |   109 |  1526 |    2 |
|*  3 |   HASH JOIN          |          |   599 | 39534 |   11 |
|   4 |    TABLE ACCESS FULL | CITY     |   600 | 10800 |    3 |
|*  5 |    HASH JOIN         |          |   599 | 28752 |    8 |
|   6 |     TABLE ACCESS FULL| CUSTOMER |   599 | 11381 |    4 |
|   7 |     TABLE ACCESS FULL| ADDRESS  |   603 | 17487 |    3 |
----------------------------------------------------------------

Tentu saja. Basis data melakukan semua penggabungan dan pemindaian tabel lengkap karena itulah yang kami perintahkan - ambil semua data ini. Sekarang, sekali lagi, bayangkan yang kita perlukan hanyalah ini: Apa yang serius, kan? Sekarang Anda mulai memahami apa yang saya bicarakan. Tapi bayangkan kita belajar sesuatu dari kesalahan masa lalu, dan jalankan query ini dengan lebih optimal:

SELECT first_name, last_name
FROM v_customer

Sekarang mari kita periksa apa yang terjadi!

------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |   599 | 16173 |     4 |
|   1 |  NESTED LOOPS      |             |   599 | 16173 |     4 |
|   2 |   TABLE ACCESS FULL| CUSTOMER    |   599 | 11381 |     4 |
|*  3 |   INDEX UNIQUE SCAN| SYS_C007120 |     1 |     8 |     0 |
------------------------------------------------------------------

Perubahan drastis menjadi lebih baik dalam hal eksekusi. Gabungan telah dihilangkan karena pengoptimal sekarang dapat melihat bahwa mereka tidak berguna , dan jika pengoptimal dapat melihatnya (dan Anda belum menjadikan pekerjaan ini wajib dengan memilih *), maka pengoptimal tidak dapat melakukan semua pekerjaan itu. Mengapa demikian dalam kasus ini? Kunci asing CUSTOMER.ADDRESS_ID ke kunci utama ADDRESS.ADDRESS_ID menjamin tepat satu nilai dari kunci utama, yang berarti bahwa operasi GABUNG akan menjadi gabungan "...-ke-satu" yang tidak menambah atau mengurangi jumlah baris . Dan karena kami tidak memilih atau meminta baris apa pun sama sekali, maka tidak ada gunanya memuatnya sama sekali. Menghapus JOIN mungkin tidak akan mempengaruhi hasil query sama sekali. Basis data melakukan hal ini sepanjang waktu. Anda dapat menjalankan kueri berikut di hampir semua database:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (SELECT 1 / 0)

Dalam kasus ini, Anda mungkin mengharapkan pengecualian aritmatika dilempar, seperti saat menjalankan kueri berikut:

SELECT 1 / 0 FROM dual

Tapi ini tidak terjadi. Pengoptimal (atau bahkan parser) dapat memastikan bahwa tidak ada elemen daftar pilih dalam predikat EXISTS (SELECT ..) yang akan mengubah hasil kueri, sehingga tidak perlu menjalankannya. Seperti ini!

Sementara itu...

Salah satu masalah yang paling menjengkelkan dengan ORM adalah sangat mudahnya menulis kueri SELECT *. Faktanya, misalnya, di HQL / JPQL umumnya digunakan secara default. Kita bisa menghilangkan klausa SELECT sama sekali, karena kita akan mengambil seluruh entitas, bukan? Misalnya:

FROM v_customer

Misalnya, Vlad Mihalcea, pakar dan penganjur pengembangan dengan Hibernate , merekomendasikan penggunaan kueri [memenuhi syarat] hampir selalu ketika Anda yakin tidak ingin menyimpan perubahan apa pun setelah checkout. ORM sangat memudahkan penyelesaian masalah persistensi grafik objek. Catatan: Kegigihan. Tugas untuk benar-benar memodifikasi grafik objek dan menyimpan perubahan saling terkait erat. Tetapi jika Anda tidak mau melakukan itu, mengapa repot-repot mengekstraksi esensinya? Mengapa tidak menulis permintaan [yang disempurnakan]? Mari kita perjelas: dari sudut pandang kinerja, menulis kueri yang secara khusus disesuaikan dengan kasus penggunaan spesifik Anda jelas lebih baik daripada opsi lainnya. Anda mungkin tidak peduli karena kumpulan data Anda kecil dan itu tidak masalah. Besar. Namun ketika Anda pada akhirnya membutuhkan skalabilitas, mendesain ulang aplikasi Anda untuk menggunakan kueri alih-alih traversal penting pada grafik entitas akan menjadi tantangan yang cukup besar. Dan Anda akan melakukan sesuatu tanpanya.

Menghitung garis untuk mengetahui apakah ada sesuatu

Salah satu pemborosan sumber daya yang paling buruk adalah menjalankan kueri COUNT(*) hanya untuk melihat apakah ada sesuatu di database. Misalnya, kita perlu mencari tahu apakah pengguna tertentu mempunyai pesanan. Dan kami menjalankan permintaan:

SELECT count(*)
FROM orders
WHERE user_id = :user_id

Dasar. Jika COUNT = 0, maka tidak ada pesanan. Jika tidak, ya. Kinerjanya tidak akan terlalu buruk karena kita mungkin memiliki indeks pada kolom ORDERS.USER_ID. Namun menurut Anda bagaimana kinerja kueri di atas akan dibandingkan dengan opsi berikut:

-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
) THEN 1 ELSE 0 END
FROM dual

-- Более адекватные диалекты SQL, например, PostgreSQL
SELECT EXISTS (
  SELECT *
  FROM orders
  WHERE user_id = :user_id
)

Tidak perlu seorang ilmuwan roket untuk mengetahui bahwa predikat keberadaan sejati akan berhenti mencari string tambahan segera setelah ia menemukan string pertama . Jadi jika hasilnya “tidak ada pesanan”, maka kecepatannya akan sebanding. Namun jika hasilnya “ya, ada pesanan”, maka dalam hal jumlah pastinya tidak perlu dihitung, jawabannya akan diterima lebih cepat. Lagi pula, kami tidak tertarik dengan angka pastinya. Namun, kami meminta database untuk menghitungnya ( pekerjaan yang tidak perlu ) dan database tidak mengetahui bahwa kami mengabaikan semua hasil yang lebih besar dari 1 ( pekerjaan yang diperlukan ). Tentu saja, akan lebih buruk jika kita memanggil list.size() koleksi yang didukung JPA untuk mencapai hasil yang sama. Saya sudah menulis tentang ini di blog saya sebelumnya, dan melakukan pengujian komparatif terhadap kedua opsi di...

Kesimpulan

Artikel ini menyatakan hal yang sudah jelas. Jangan memaksa database untuk melakukan pekerjaan yang tidak diperlukan namun wajib . Hal ini tidak perlu karena, mengingat persyaratannya, Anda tahu bahwa ada pekerjaan tertentu yang tidak perlu dilakukan. Namun, Anda memberitahu database untuk melakukannya. Hal ini diperlukan karena tidak ada cara bagi database untuk memastikan bahwa pekerjaan ini tidak diperlukan . Informasi ini hanya tersedia untuk klien dan tidak tersedia untuk server. Jadi database harus menjalankannya. Artikel ini berfokus pada SELECT *, terutama karena ini adalah objek yang nyaman untuk dilihat. Tapi ini tidak hanya berlaku untuk database. Hal ini berlaku untuk semua algoritma terdistribusi di mana klien memberitahu server untuk melakukan pekerjaan yang tidak perlu tetapi diperlukan . Berapa banyak tugas N+1 yang ada di rata-rata aplikasi AngularJS Anda di mana UI mengulang hasil layanan A, memanggil layanan B beberapa kali, daripada mengemas semua panggilan ke B menjadi satu panggilan? Ini adalah kejadian yang sangat umum. Solusinya selalu sama. Semakin banyak informasi yang Anda berikan kepada entitas yang menjalankan perintah Anda, semakin cepat entitas tersebut (secara teoritis) menjalankan perintah tersebut. Tulis kueri yang optimal. Selalu. Seluruh sistem Anda akan berterima kasih untuk ini. Artikel asli