Masalah prestasi SQL yang timbul daripada "kerja yang tidak perlu tetapi diperlukan"

Tahap pengetahuan yang diperlukan untuk memahami artikel: pemahaman umum pangkalan data dan SQL, beberapa pengalaman praktikal dengan DBMS. Mungkin perkara paling penting yang boleh anda pelajari untuk menulis pertanyaan SQL yang berkesan ialah pengindeksan. Walau bagaimanapun, di tempat kedua, sangat dekat di belakang, adalah pengetahuan bahawa banyak pelanggan SQL memerlukan pangkalan data untuk melakukan banyak "kerja yang tidak perlu tetapi perlu" . Ulangi selepas saya: Apakah "kerja yang tidak perlu tetapi wajib"? Seperti yang Kapten Obvious memberitahu kami, dia:

Tidak perlu

Biarkan aplikasi pelanggan kami memerlukan data berikut: Tiada yang luar biasa. Kami sedang bekerja dengan pangkalan data filem (seperti pangkalan data Sakila ) dan ingin memaparkan tajuk dan rating semua filem kepada pengguna. Pertanyaan berikut boleh memberikan hasil yang kami perlukan:

SELECT title, rating
FROM film

Walau bagaimanapun, aplikasi kami (atau ORM kami) sebaliknya melaksanakan pertanyaan ini:

SELECT *
FROM film

Apa yang kita dapat sebagai hasilnya? teka. Kami menerima banyak maklumat yang tidak berguna: Di sebelah kanan anda juga boleh melihat beberapa JSON kompleks sedang dimuatkan:

• daripada cakera
• ke cache
• melalui wayar
• dalam ingatan klien
• dan akhirnya dibuang [sebagai tidak perlu]

Ya, kami membuang kebanyakan maklumat ini. Semua tindakan yang diambil untuk mengekstrak maklumat ini ternyata tidak berguna sama sekali. Adakah benar? Adakah benar.

Wajib

Dan sekarang - bahagian yang paling teruk. Walaupun pengoptimum kini boleh melakukan banyak perkara, tindakan ini adalah wajib untuk pangkalan data. Pangkalan data tidak mempunyai cara untuk mengetahui bahawa aplikasi klien tidak memerlukan 95% daripada data ini. Dan ini hanyalah contoh paling mudah. Bayangkan menyambung beberapa jadual... Jadi apa, anda katakan, tetapi pangkalan data adalah pantas? Izinkan saya memberi pencerahan kepada anda tentang beberapa perkara yang mungkin anda tidak fikirkan. Sudah tentu, masa pelaksanaan permintaan individu tidak benar-benar mempengaruhi apa-apa. Okay, ia berjalan satu setengah kali lebih perlahan, tetapi kita akan melaluinya, bukan? Untuk kemudahan? Kadang-kadang ini benar. Tetapi jika anda sentiasa mengorbankan prestasi untuk kemudahan , perkara kecil ini akan mula bertambah. Kami tidak lagi akan bercakap tentang prestasi (kelajuan pelaksanaan permintaan individu), tetapi mengenai throughput (masa tindak balas sistem), dan kemudian masalah serius akan bermula, yang tidak begitu mudah untuk diselesaikan. Itulah apabila anda kehilangan kebolehskalaan. Mari kita lihat rancangan pelaksanaan, dalam kes ini, Oracle DBMS:

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

berbanding dengan:

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

Menjalankan pertanyaan SELECT * dan bukannya tajuk SELECT, rating menggunakan 8 kali lebih banyak memori dalam pangkalan data. Tidak ada yang tidak dijangka, bukan? Kami tahu ini akan berlaku. Tetapi kami masih bersetuju dengan ini untuk kebanyakan permintaan kami yang mana kami tidak memerlukan semua data ini. Kami mencipta kerja yang tidak perlu tetapi wajib untuk pangkalan data , yang terus bertimbun dan bertimbun. Kami menggunakan 8 kali lebih banyak memori daripada yang diperlukan (pengganda akan berubah, sudah tentu). Sementara itu, pada semua peringkat lain (cakera I/O, pemindahan data melalui rangkaian, penggunaan memori oleh pelanggan) masalahnya adalah sama, tetapi saya akan melangkaunya dan sebaliknya melihat...

Menggunakan Indeks

Kebanyakan pangkalan data hari ini telah pun menghargai konsep meliputi indeks . Indeks penutup itu sendiri bukanlah jenis indeks khas. Tetapi ia mungkin bertukar menjadi "indeks khas" untuk pertanyaan tertentu, sama ada "secara tidak sengaja" atau kerana ia bertujuan sedemikian. Pertimbangkan pertanyaan berikut:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Tiada apa yang tidak dijangka dari segi pelaksanaannya. Ini adalah permintaan yang mudah. Lihat julat mengikut indeks, akses jadual - dan anda 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 |
-------------------------------------------------------------------

Rancangan yang bagus, bukan? Nah, jika kita benar-benar memerlukan ini, maka tidak: Jelas sekali, kita membazirkan ingatan, dsb. Mari kita pertimbangkan pertanyaan ini sebagai alternatif:

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

Perancangannya begini:

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

Kami dapat menghapuskan sepenuhnya akses kepada jadual, terima kasih kepada kehadiran indeks yang memenuhi semua keperluan pertanyaan kami... indeks penutup. Adakah ia penting? Dan bagaimana! Pendekatan ini membolehkan anda mempercepatkan beberapa pertanyaan mengikut susunan magnitud (atau memperlahankannya mengikut susunan magnitud apabila indeks tidak lagi meliputi selepas beberapa perubahan). Meliputi indeks tidak boleh selalu digunakan. Anda perlu membayar untuk indeks dan anda tidak perlu menambah terlalu banyak daripadanya. Tetapi dalam kes ini, semuanya jelas. Mari kita nilai prestasi:

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;
/

Ambil perhatian bahawa jadual aktor hanya mempunyai 4 lajur, jadi perbezaan prestasi antara pernyataan 1 dan 2 tidaklah begitu besar, tetapi ia masih ketara. Saya juga akan ambil perhatian bahawa saya menggunakan petunjuk pengoptimum Oracle untuk meminta pengoptimum memilih satu atau indeks khusus lain untuk pertanyaan. Pengendali 3 adalah pemenang yang tidak dapat dipertikaikan dalam perlumbaan kami. Prestasinya jauh lebih baik, dan kita bercakap tentang pertanyaan yang sangat mudah. Sekali lagi, apabila kami menulis SELECT *, kami mencipta kerja yang tidak perlu tetapi wajib untuk pangkalan data yang tidak dapat dioptimumkan. Dia tidak akan memilih indeks penutup kerana indeks itu mempunyai overhed yang lebih tinggi sedikit daripada indeks LAST_NAME yang dia pilih, dan, antara lain, dia masih perlu mengakses jadual untuk mendapatkan semula lajur LAST_UPDATE yang tidak berguna, sebagai contoh. Tetapi semakin mendalam kita menganalisis SELECT *, semakin buruk keadaannya. Mari kita bercakap tentang...

Penukaran SQL

Pengoptimum berprestasi sangat baik kerana mereka mengubah pertanyaan SQL ( saya bercakap tentang cara ini berfungsi dalam ceramah saya baru-baru ini di Voxxed Days di Zurich ). Sebagai contoh, terdapat transformasi "pengecualian JOIN" yang sangat berkuasa. Pertimbangkan pandangan pembantu berikut yang perlu kami buat untuk mengelak daripada menyertai semua jadual ini secara manual setiap kali:

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)

Pandangan ini hanya melakukan semua gabungan "...-ke-satu" antara jadual pelanggan PELANGGAN dan pelbagai jadual untuk bahagian alamat mereka. Terima kasih, normalisasi. Bayangkan, selepas bekerja sedikit dengan pandangan ini, kami terbiasa dengannya dan terlupa tentang jadual yang mendasarinya. Dan sekarang kami melaksanakan pertanyaan berikut:

SELECT *
FROM v_customer

Hasilnya, kami mendapat rancangan yang sangat mengagumkan:

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

Sudah tentu. Pangkalan data sedang melakukan semua gabungan ini dan imbasan jadual penuh kerana itulah yang kami suruh ia lakukan - ambil semua data ini. Sekarang, sekali lagi, bayangkan bahawa semua yang kita perlukan adalah ini: Apa, serius, kan? Sekarang anda mula memahami apa yang saya katakan. Tetapi bayangkan bahawa kami belajar sesuatu daripada kesilapan lalu, dan laksanakan pertanyaan yang lebih optimum ini:

SELECT first_name, last_name
FROM v_customer

Sekarang mari kita semak apa yang berlaku!

------------------------------------------------------------------
| 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 drastik ke arah yang lebih baik dari segi pelaksanaan. Cantuman telah dihapuskan kerana pengoptimum kini boleh melihat bahawa ia tidak berguna , dan jika ia dapat melihatnya (dan anda tidak mewajibkan kerja itu dengan memilih *), maka ia tidak boleh melakukan semua kerja itu. Mengapa begitu dalam kes ini? Kunci asing CUSTOMER.ADDRESS_ID kepada kunci utama ADDRESS.ADDRESS_ID menjamin tepat satu nilai yang terakhir, yang bermaksud bahawa operasi JOIN akan menjadi gabungan "...-ke-satu" yang tidak menambah atau mengurangkan bilangan baris . Dan kerana kami tidak memilih atau meminta sebarang baris sama sekali, maka tiada gunanya memuatkannya sama sekali. Mengalih keluar JOIN mungkin tidak akan menjejaskan hasil pertanyaan sama sekali. Pangkalan data melakukan ini sepanjang masa. Anda boleh menjalankan pertanyaan berikut pada hampir mana-mana pangkalan data:

-- 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 kes ini, anda mungkin menjangkakan pengecualian aritmetik akan dilemparkan, seperti semasa melaksanakan pertanyaan berikut:

SELECT 1 / 0 FROM dual

Tetapi ini tidak berlaku. Pengoptimum (atau pun penghurai) boleh memastikan bahawa tiada unsur senarai pilih dalam predikat EXISTS (SELECT ..) akan mengubah hasil pertanyaan, jadi tidak perlu melaksanakannya. Macam ni!

Sementara itu...

Salah satu masalah yang paling menjengkelkan dengan ORM ialah mereka sangat mudah untuk menulis pertanyaan SELECT *. Malah, sebagai contoh, dalam HQL / JPQL ia biasanya digunakan secara lalai. Kita boleh meninggalkan klausa SELECT sama sekali, kerana kita akan mendapatkan semula keseluruhan entiti, bukan? Sebagai contoh:

FROM v_customer

Contohnya, Vlad Mihalcea, pakar dan peguam bela untuk membangunkan dengan Hibernate , mengesyorkan menggunakan pertanyaan [layak] hampir selalu apabila anda pasti anda tidak mahu menyimpan sebarang perubahan selepas pembayaran. ORM sangat memudahkan penyelesaian masalah kegigihan graf objek. Nota: Kegigihan. Tugas sebenarnya mengubah suai graf objek dan menyimpan perubahan adalah berkait rapat. Tetapi jika anda tidak akan melakukannya, mengapa perlu bersusah payah mengeluarkan intipatinya? Mengapa tidak menulis permintaan [diperhalusi]? Mari kita jelaskan: dari sudut prestasi, menulis pertanyaan yang disesuaikan secara khusus dengan kes penggunaan khusus anda jelas lebih baik daripada pilihan lain. Anda mungkin tidak peduli kerana set data anda kecil dan tidak mengapa. Hebat. Tetapi apabila anda akhirnya memerlukan kebolehskalaan, mereka bentuk semula aplikasi anda untuk menggunakan pertanyaan dan bukannya traversal penting graf entiti akan menjadi agak mencabar. Dan anda akan mempunyai sesuatu untuk dilakukan tanpanya.

Mengira baris untuk mengetahui sama ada ada sesuatu

Salah satu pembaziran sumber yang paling teruk ialah menjalankan COUNT(*) pertanyaan hanya untuk melihat sama ada sesuatu ada dalam pangkalan data. Sebagai contoh, kita perlu mengetahui sama ada pengguna tertentu mempunyai pesanan sama sekali. Dan kami melaksanakan permintaan:

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

peringkat rendah. Jika COUNT = 0, maka tiada pesanan. Jika tidak, ya. Prestasinya tidaklah begitu teruk kerana kami mungkin mempunyai indeks pada lajur ORDERS.USER_ID. Tetapi apakah pada pendapat anda prestasi pertanyaan di atas akan dibandingkan dengan pilihan 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
)

Ia tidak memerlukan saintis roket untuk mengetahui bahawa predikat kewujudan sebenar akan berhenti mencari rentetan tambahan sebaik sahaja ia menemui yang pertama . Jadi jika hasilnya ternyata "tiada pesanan", maka kelajuannya akan setanding. Walau bagaimanapun, jika hasilnya adalah "ya, ada pesanan," maka dalam kes di mana kuantiti yang tepat tidak perlu dikira, jawapan akan diterima dengan lebih cepat. Lagipun, kami tidak berminat dengan jumlah yang tepat. Walau bagaimanapun, kami memberitahu pangkalan data untuk mengiranya ( kerja yang tidak perlu ) dan pangkalan data tidak tahu bahawa kami mengabaikan semua hasil yang lebih besar daripada 1 ( kerja yang diperlukan ). Sudah tentu, ia akan menjadi lebih teruk jika kita memanggil list.size() pada koleksi yang disokong JPA untuk mencapai hasil yang sama. Saya sudah menulis tentang ini di blog saya sebelum ini, dan menjalankan ujian perbandingan kedua-dua pilihan pada...

Kesimpulan

Artikel ini menyatakan yang jelas. Jangan paksa pangkalan data untuk melakukan kerja yang tidak perlu tetapi diperlukan . Ia tidak perlu kerana, berdasarkan keperluan, anda tahu bahawa beberapa kerja tertentu tidak perlu dilakukan. Walau bagaimanapun, anda memberitahu pangkalan data untuk melakukannya. Ia diperlukan kerana tiada cara untuk pangkalan data memastikan kerja ini tidak diperlukan . Maklumat ini hanya tersedia kepada pelanggan dan tidak tersedia kepada pelayan. Jadi pangkalan data perlu melaksanakannya. Artikel ini memfokuskan pada SELECT *, terutamanya kerana ia adalah objek yang mudah untuk dilihat. Tetapi ini terpakai bukan sahaja untuk pangkalan data. Ini terpakai kepada semua algoritma yang diedarkan di mana pelanggan memberitahu pelayan untuk melakukan kerja yang tidak perlu tetapi diperlukan . Berapa banyak tugasan N+1 yang terdapat dalam aplikasi AngularJS purata anda di mana UI bergelung melalui hasil perkhidmatan A, memanggil perkhidmatan B beberapa kali, dan bukannya membungkus semua panggilan ke B ke dalam satu panggilan? Ini adalah fenomena yang sangat biasa. Penyelesaiannya sentiasa sama. Lebih banyak maklumat yang anda berikan kepada entiti yang melaksanakan arahan anda, lebih cepat ia (secara teorinya) melaksanakan arahan tersebut. Tulis pertanyaan yang optimum. Sentiasa. Seluruh sistem anda akan berterima kasih untuk ini. Artikel asal