Masalah kinerja SQL amarga "kerja sing ora perlu nanging dibutuhake"

Tingkat kawruh sing dibutuhake kanggo mangerteni artikel kasebut: pangerten umum babagan database lan SQL, sawetara pengalaman praktis karo DBMS. Mbokmenawa sing paling penting sampeyan bisa sinau nulis pitakon SQL sing efektif yaiku indeksasi. Nanging, ing posisi kaping pindho, cedhak banget, yaiku kawruh yen akeh klien SQL mbutuhake database kanggo nindakake akeh "karya sing ora perlu nanging perlu" . Baleni sawise kula: Apa "karya sing ora perlu nanging wajib"? Minangka Kapten Obvious ngandhani, dheweke:

Ora perlu

Ayo aplikasi klien kita mbutuhake data ing ngisor iki: Ora ana sing ora biasa. Kita nggarap database film (kayata database Sakila ) lan pengin nampilake judhul lan rating kabeh film menyang pangguna. Pitakon ing ngisor iki bisa menehi asil sing dibutuhake:

SELECT title, rating
FROM film

Nanging, aplikasi kita (utawa ORM kita) tinimbang nglakokake pitakon iki:

SELECT *
FROM film

Apa sing kita entuk minangka asil? Tebak. Kita nampa akeh informasi sing ora ana gunane: Ing sisih tengen sampeyan bisa uga ndeleng sawetara JSON kompleks sing dimuat:

• saka disk
• kanggo cache
• dening kabel
• ing memori saka klien
• lan pungkasane dibuwang [kaya ora perlu]

Ya, kita mbuwang akeh informasi iki. Kabeh tumindak sing ditindakake kanggo ngekstrak informasi kasebut dadi ora ana gunane. Apa bener? Apa bener.

wajib

Lan saiki - sisih paling awon. Sanajan pangoptimal saiki bisa nindakake akeh, tumindak kasebut wajib kanggo basis data. Database ora duwe cara kanggo ngerti yen aplikasi klien ora mbutuhake 95% data iki. Lan iki mung conto sing paling gampang. Mbayangno nyambungake sawetara tabel ... Dadi apa, sampeyan ngomong, nanging database cepet? Ayo kula menehi pencerahan babagan sawetara perkara sing mbokmenawa durung sampeyan pikirake. Mesthine, wektu eksekusi panjaluk individu ora mengaruhi apa-apa. Oke, mlaku siji lan setengah luwih alon, nanging kita bakal ngatasi, ta? Kanggo penak? Kadhangkala iki bener. Nanging yen sampeyan tansah ngorbanake kinerja kanggo penak , prekara-prekara cilik iki bakal nambah. Kita ora bakal maneh ngomong babagan kinerja (kacepetan eksekusi panjaluk individu), nanging babagan throughput (wektu respon sistem), banjur masalah serius bakal diwiwiti, sing ora gampang diatasi. Nalika sampeyan kelangan skalabilitas. Ayo goleki rencana eksekusi, ing kasus iki, Oracle DBMS:

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

dibandhingake karo:

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

Nganggo pitakon SELECT * tinimbang judhul SELECT, rating nggunakake memori 8 kaping luwih akeh ing database. Ora ana sing ora dikarepake, ta? Kita ngerti iki bakal kelakon. Nanging kita isih setuju kanggo iki kanggo akeh panjalukan sing kita mung ora perlu kabeh data iki. Kita nggawe karya sing ora perlu nanging wajib kanggo basis data , sing terus-terusan lan numpuk. Kita nggunakake 8 kaping memori luwih saka needed (multiplier bakal ngganti, mesthi). Kangge, ing kabeh tahapan liyane (disk I / O, transfer data liwat jaringan, konsumsi memori dening klien) masalah sing persis padha, nanging aku bakal skip lan katon tinimbang ing ...

Nggunakake Indeks

Umume basis data saiki wis ngapresiasi konsep nutupi indeks . Indeks panutup dudu jinis indeks khusus. Nanging bisa uga dadi "indeks khusus" kanggo pitakon tartamtu, "kanthi ora sengaja" utawa amarga tujuane. Coba pitakon ing ngisor iki:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Ora ana sing ora dikarepke babagan implementasine. Iki minangka panyuwunan sing prasaja. Deleng kisaran miturut indeks, akses tabel - lan sampeyan wis rampung:

-------------------------------------------------------------------
| 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 apik, ta? Inggih, yen kita pancene butuh iki, mula ora: Temenan, kita mbuang memori, lsp. Coba pitakon iki minangka alternatif:

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

Rencanane yaiku:

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

Kita bisa ngilangi akses menyang meja kanthi lengkap, amarga ana indeks sing nyukupi kabeh kabutuhan pitakon kita ... indeks panutup. Apa iku penting? Lan carane! Pendekatan iki ngidini sampeyan nyepetake sawetara pitakon kanthi urutan gedhene (utawa alon-alon mudhun kanthi urutan gedhene nalika indeks ora nutupi maneh sawise sawetara owah-owahan). Panutup indeks ora bisa tansah digunakake. Sampeyan kudu mbayar indeks lan sampeyan ora kudu nambah akeh banget. Nanging ing kasus iki, kabeh wis jelas. Ayo ngevaluasi kinerja:

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

Elinga yen tabel aktor mung 4 kolom, supaya prabédan kinerja antarane statements 1 lan 2 ora sing amba, nanging isih wujud. Aku uga bakal nyathet yen aku nggunakake pitunjuk pangoptimal Oracle supaya pangoptimal milih siji utawa indeks khusus liyane kanggo pitakon. Operator 3 minangka pemenang sing ora bisa dibantah ing balapan kita. Kinerja kasebut luwih apik, lan kita ngomong babagan pitakon sing gampang banget. Maneh, nalika nulis SELECT *, kita nggawe karya sing ora perlu nanging wajib kanggo database sing ora bisa dioptimalake. Dheweke ora bakal milih indeks panutup amarga duwe overhead sing rada dhuwur tinimbang indeks LAST_NAME sing dipilih, lan, ing antarane, dheweke isih kudu ngakses tabel kanggo njupuk kolom LAST_UPDATE sing ora ana gunane, contone. Nanging luwih jero kita nganalisa SELECT *, kedadeyan sing luwih elek. Ayo ngomong babagan ...

konversi SQL

Optimizers nindakake kanthi apik amarga ngowahi pitakon SQL ( Aku ngomong babagan cara kerjane ing obrolan anyar ing Voxxed Days ing Zurich ). Contone, ana transformasi "pengecualian JOIN" sing kuat banget. Coba tampilan helper ing ngisor iki sing kudu digawe supaya ora gabung kabeh tabel iki kanthi manual saben wektu:

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 iki mung nindakake kabeh gabungan "...-kanggo-siji" antarane tabel pelanggan PELANGGAN lan macem-macem tabel kanggo bagean alamate. Matur nuwun, normalisasi. Mbayangno, sawise nggarap tampilan iki, kita wis biasa lan lali babagan tabel sing ana ing ngisor iki. Lan saiki kita nglakokake pitakon ing ngisor iki:

SELECT *
FROM v_customer

Akibaté, kita entuk rencana sing apik banget:

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

Inggih, mesthi. Basis data nindakake kabeh gabungan iki lan scan tabel lengkap amarga iku sing dakkandhakake - njupuk kabeh data iki. Saiki, maneh, bayangake yen kabeh sing dibutuhake yaiku: Apa, serius, bener? Saiki sampeyan wis mulai ngerti apa sing dakkandhakake. Nanging bayangake yen kita sinau saka kesalahan sing kepungkur, lan nglakokake pitakon iki sing luwih optimal:

SELECT first_name, last_name
FROM v_customer

Saiki ayo priksa apa sing kedadeyan!

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

Owah-owahan drastis kanggo luwih apik babagan eksekusi. Gabungan wis diilangi amarga pangoptimal saiki bisa ndeleng manawa ora ana gunane , lan yen bisa ndeleng (lan sampeyan ora nindakake tugas iki kanthi milih *), mula ora bisa nindakake kabeh karya kasebut. Yagene kaya ngono ing kasus iki? Kunci asing CUSTOMER.ADDRESS_ID menyang kunci utama ADDRESS.ADDRESS_ID njamin persis siji nilai sing terakhir, tegese operasi JOIN bakal dadi gabungan "...-kanggo-siji" sing ora nambah utawa nyuda jumlah baris. . Lan amarga kita ora milih utawa njaluk larik apa wae, mula ora ana gunane kanggo mbukak kabeh. Mbusak JOIN mbokmenawa ora bakal mengaruhi asil pitakon. Database nindakake iki kabeh wektu. Sampeyan bisa mbukak pitakon ing meh kabeh 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)

Ing kasus iki, sampeyan bisa nyana pangecualian aritmetika bakal dibuwang, kaya nalika nglakokake pitakon ing ngisor iki:

SELECT 1 / 0 FROM dual

Nanging iki ora kelakon. Pangoptimal (utawa malah parser) bisa mesthekake yen ora ana unsur dhaptar pilih ing predikat EXISTS (PILIH ..) bakal ngganti asil pitakonan, supaya ora perlu kanggo nglakokaké. Kaya iki!

Sauntara kuwi...

Salah sawijining masalah sing paling ngganggu karo ORM yaiku gampang banget kanggo nulis pitakon SELECT *. Nyatane, contone, ing HQL / JPQL umume digunakake kanthi standar. Kita bisa ngilangi klausa SELECT kabeh, amarga kita bakal njupuk kabeh entitas, ta? Tuladhane:

FROM v_customer

Contone, Vlad Mihalcea, pakar lan advokat kanggo ngembangake karo Hibernate , nyaranake nggunakake pitakon [qualified] meh tansah yen sampeyan yakin sampeyan ora pengin nyimpen owah-owahan sawise checkout. ORMs banget nggampangake solusi saka masalah terus-terusan grafik obyek. Cathetan: Ketekunan. Tugas kanggo ngowahi grafik obyek lan nyimpen owah-owahan ora bisa dipisahake. Nanging yen sampeyan ora bakal nindakake iku, banjur kok repot-repot ngekstrak inti? Apa ora nulis panjalukan [refined]? Ayo dadi cetha: saka sudut pandang kinerja, nulis pitakon sing disesuaikan karo kasus panggunaan khusus sampeyan jelas luwih apik tinimbang pilihan liyane. Sampeyan bisa uga ora peduli amarga set data sampeyan cilik lan ora masalah. Agung. Nanging yen pungkasane sampeyan butuh skalabilitas, ngrancang ulang aplikasi sampeyan kanggo nggunakake pitakon tinimbang traversal penting ing grafik entitas bakal cukup tantangan. Lan sampeyan bakal duwe soko apa tanpa iku.

Ngitung baris kanggo mangerteni yen ana sing ana

Salah sawijining sumber daya sing paling awon yaiku nglakokake pitakon COUNT (*) mung kanggo ndeleng apa ana ing database. Contone, kita kudu ngerteni manawa pangguna tartamtu duwe pesenan. Lan kita nindakake panjaluk kasebut:

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

SD. Yen COUNT = 0, banjur ora ana pesenan. Yen ora, ya. Kinerja ora bakal ala amarga kita bisa uga duwe indeks ing kolom ORDERS.USER_ID. Nanging apa sampeyan mikir kinerja pitakon ing ndhuwur bakal dibandhingake karo pilihan ing ngisor iki:

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

Ora butuh ilmuwan roket kanggo ngerteni manawa predikat eksistensi sing sejati bakal mandheg nggoleki senar tambahan sanalika nemokake sing pertama . Dadi yen asil dadi "ora ana pesenan", banjur kacepetan bakal bisa dibandhingake. Nanging, yen asile "ya, ana pesenan," banjur yen jumlah sing tepat ora perlu diitung, jawaban bakal ditampa luwih cepet. Sawise kabeh, kita ora kasengsem ing nomer pas. Nanging, kita ngandhani database kanggo ngetung ( kerja sing ora perlu ) lan database ora ngerti yen kita ora nggatekake kabeh asil luwih saka 1 ( kerja sing dibutuhake ). Mesthi, iku bakal akeh Samsaya Awon yen kita disebut list.size () ing koleksi JPA-digawe kanggo entuk asil padha. Aku wis nulis babagan iki ing blogku sadurunge, lan nganakake tes komparatif saka loro pilihan kasebut ing ...

Kesimpulan

Artikel iki nyatakake sing jelas. Aja meksa database nindakake karya sing ora perlu nanging dibutuhake . Ora perlu amarga, diwenehi syarat, sampeyan ngerti manawa sawetara karya tartamtu ora perlu ditindakake. Nanging, sampeyan ngandhani database kanggo nindakake. Iki dibutuhake amarga ora ana cara kanggo database kanggo mesthekake yen karya iki ora perlu . Informasi iki mung kasedhiya kanggo klien lan ora kasedhiya kanggo server. Dadi database kudu nglakokaké. Artikel kasebut fokus ing SELECT *, utamane amarga obyek kasebut trep kanggo dideleng. Nanging iki ditrapake ora mung kanggo database. Iki ditrapake kanggo kabeh algoritma sing disebarake ing ngendi klien ngandhani server supaya nindakake karya sing ora perlu nanging dibutuhake . Pira tugas N + 1 sing ana ing aplikasi AngularJS rata-rata ing ngendi UI puteran liwat asil layanan A, nelpon layanan B kaping pirang-pirang, tinimbang ngemas kabeh telpon menyang B dadi siji telpon? Iki kedadeyan sing umum banget. Solusi tansah padha. Informasi liyane sing diwenehake marang entitas sing nglakokake printah sampeyan, luwih cepet (secara teoritis) nglakokake perintah kasebut. Tulis pitakon sing optimal. tansah. Kabeh sistem sampeyan bakal matur nuwun kanggo iki. Artikel asli