"Gereksiz, ýöne zerur iş" sebäpli ýüze çykýan SQL öndürijilik problemalary

Makala düşünmek üçin zerur bilim derejesi: maglumat bazalaryna we SQL-ä umumy düşünmek, DBMS bilen käbir amaly tejribe. Netijeli SQL talaplaryny ýazmagy öwrenip boljak iň möhüm zat indeksirlemekdir. Şeýle-de bolsa, ikinji ýerde, SQL müşderileriniň köpüsiniň "zerur däl, ýöne zerur iş" etmegi üçin maglumatlar bazasyndan talap edýändigi baradaky bilim . Menden soň gaýtala: “Gereksiz, ýöne hökmany iş” näme? Kapitan Obýusyň aýdyşy ýaly, ol:

Gerek däl

Müşderi programmamyza aşakdaky maglumatlar gerek bolsun: Üýtgeşik zat ýok. Kino maglumatlar bazasy ( Sakila maglumatlar bazasy ýaly) bilen işleýäris we ähli filmleriň adyny we reýtingini ulanyjylara görkezmek isleýäris. Aşakdaky talap bize zerur netijäni berip biler:

SELECT title, rating
FROM film

Şeýle-de bolsa, programmamyz (ýa-da ORM) bu soragy ýerine ýetirýär:

SELECT *
FROM film

Netijede näme alarys? Çaklaň. Köp peýdasyz maglumatlary alýarys: Sag tarapda hatda käbir çylşyrymly JSON-yň ýüklenýändigini görüp bilersiňiz:

• diskden
• keş
• sim bilen
• müşderiniň hatyrasyna
• we ahyrsoňy taşlandy [gereksiz]

Hawa, bu maglumatlaryň köpüsini taşlaýarys. Bu maglumatlary çykarmak üçin edilen ähli çäreler düýbünden peýdasyz boldy. Bu dogrumy? Bu dogrumy.

Hökmany

Indi bolsa iň erbet tarapy. Optimizatorlar indi köp zat edip bilseler-de, bu hereketler maglumatlar bazasy üçin hökmanydyr. Maglumat bazasy, müşderi programmasynyň bu maglumatlaryň 95% -ine mätäç däldigini bilip bilmeýär. Bu iň ýönekeý mysal. Birnäçe tablisany birleşdirmegi göz öňüne getiriň ... Onda näme diýýärsiňiz, ýöne maglumat bazalary çalt? Size belki pikir etmedik käbir zatlaryňyz barada maglumat bermäge rugsat ediň. Elbetde, aýratyn haýyşyň ýerine ýetiriliş wagty hiç zada täsir etmeýär. Bolýar, bir ýarym esse haýal işledi, ýöne geçeris, şeýlemi? Amatlylyk üçin? Käwagt bu dogry bolýar. Convenöne amatlylyk üçin elmydama öndürijiligi pida etseňiz , bu ownuk zatlar goşulyp başlar. Mundan beýläk öndürijilik (aýratyn haýyşlaryň ýerine ýetiriliş tizligi) hakda däl-de, giriş (ulgamyň jogap wagty) hakda gürleşeris, soň çözmek aňsat däl çynlakaý meseleler başlar. Göwrümliligini ýitiren wagtyňyz. Geliň, ýerine ýetiriş meýilnamalaryna göz aýlalyň, bu ýagdaýda Oracle DBMS:

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

bilen deňeşdirilende:

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

SELECT adynyň ýerine SELECT * talapyny işletmek, reýting maglumatlar bazasynda 8 esse köp ýat ulanýar. Garaşylmadyk zat ýok, şeýlemi? Munuň boljakdygyny bilýärdik. Emma, ​​bu maglumatlaryň hemmesine mätäç däldigimiziň köpüsi üçin henizem muňa razy. Maglumatlar bazasy üçin gereksiz, ýöne hökmany iş döredýäris , ol ýygnanýar we ýygnalýar. Zerurlykdan 8 esse köp ýat ulanýarys (köpeldiji elbetde üýtgär). Şol bir wagtyň özünde, beýleki ähli etaplarda (disk I / O, tor arkaly maglumat geçirmek, müşderiniň ýadyň sarp edilmegi) meseleler birmeňzeş, ýöne men olary taşlap, ýerine derek serederin ...

Indeksleri ulanmak

Häzirki wagtda maglumatlar bazalarynyň köpüsi indeksleri ýapmak düşünjesine eýýäm baha berdiler . Örtük indeksiniň özi aýratyn indeks görnüşi däl. Itöne belli bir talap üçin "tötänleýin" ýa-da şeýle edilmegi üçin "ýörite görkeziji" bolup biler. Aşakdaky soraga serediň:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Implementationerine ýetirilmegi taýdan garaşylmadyk zat ýok. Bu ýönekeý haýyş. Diapazony indeks boýunça görüň, tablisa giriň - we tamamladyňyz:

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

Gowy meýilnama, şeýlemi? Dogrusy, bu hakykatdanam zerur bolsa, ýok: Elbetde, ýadymyzy ýitirýäris we ş.m. Bu soraga alternatiwa hökmünde seredeliň:

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

Onuň meýilnamasy:

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

Gözlegimiziň ähli zerurlyklaryny kanagatlandyrýan indeksiň bolmagy sebäpli, tablisa girişi doly ýok edip bildik. Bu möhümmi? Nädip! Bu çemeleşme, käbir talaplary ululyk tertibi boýunça çaltlaşdyrmaga mümkinçilik berýär (ýa-da käbir üýtgeşmelerden soň indeks ýapylmasa ululyk tertibi bilen haýallaşdyryň). Örtük indeksleri hemişe ulanylyp bilinmez. Indeksler üçin pul tölemeli we olaryň köpüsini goşmaly däl. Emma bu ýagdaýda hemme zat äşgärdir. Netijä baha bereliň:

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

Aktýor tablisasynda diňe 4 sütün bar, şonuň üçin 1-nji we 2-nji jümleleriň arasyndaky ýerine ýetiriş tapawudy beýle uly däl, ýöne şonda-da möhümdir. Şeýle hem, optimizatoryň talap üçin bir ýa-da başga bir görkezijini saýlamagy üçin Oracle optimizer görkezmelerini ulanandygymy bellärin. Operator 3, ýaryşymyzyň jedelsiz ýeňijisidir. Onuň öndürijiligi has gowy we biz gaty ýönekeý bir sorag hakda gürleşýäris. Againene-de SELECT * ýazanymyzda, optimizirläp bilmeýän maglumatlar bazasy üçin zerur däl, hökmany iş döredýäris. Ol örtük indeksini saýlamaz, sebäbi saýlan LAST_NAME indeksinden birneme ýokarydyr we beýleki zatlar bilen birlikde, meselem, peýdasyz LAST_UPDATE sütünini almak üçin tablisa girmeli. Söne SELECT * -ni näçe çuňňur analiz etsek, şonça-da erbet zat bolýar. Geliň, gürleşeliň ...

SQL öwrülişikleri

Optimizatorlar SQL talaplaryny üýtgedýändikleri üçin gaty gowy ýerine ýetirýärler ( Sýurihdäki Voxxed günlerindäki soňky gepleşigimde munuň nähili işleýändigi hakda gürleşdim ). Mysal üçin, gaty güýçli "kadadan çykma JOIN" öwrülişigi bar. Her gezek bu tablisalara el bilen goşulmazlyk üçin döretmeli aşakdaky kömekçi pikirimize serediň:

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)

Bu görnüş, Müşderi müşderi stolunyň we salgysynyň bölekleri üçin dürli tablisalaryň arasynda birleşýän "...-to-one" -iň hemmesini ýerine ýetirýär. Sag bol, kadalaşdyrmak. Bu görnüş bilen azajyk işlänimizden soň, öwrenişip, aşagyndaky tablisalary ýatdan çykaranymyzy göz öňüne getiriň. Indi bolsa aşakdaky soragy ýerine ýetirýäris:

SELECT *
FROM v_customer

Netijede, gaty täsirli meýilnama alýarys:

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

Elbetde. Maglumat bazasy bu birleşmeleriň hemmesini we doly tablisa skanerlerini ýerine ýetirýär, sebäbi biz oňa etmeli diýdik - bu maglumatlary alyň. Indi bolsa, hakykatdanam bize zerur zadyň şudygyny göz öňüne getiriň: Näme çynlakaý? Indi meniň näme hakda gürleşýändigime düşünip başlaýarsyň. Pastöne geçmişdäki ýalňyşlyklardan bir zatlar öwrenendigimizi göz öňüne getiriň we has amatly soragy ýerine ýetiriň:

SELECT first_name, last_name
FROM v_customer

Indi näme bolandygyny barlap göreliň!

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

Executionerine ýetiriş nukdaýnazaryndan düýpli üýtgeşmeler. Goşulmalar ýok edildi, sebäbi optimizator indi olaryň peýdasyzdygyny görüp bilýär we eger muny görüp bilýän bolsa (we bu işi * saýlamak bilen hökmany etmediňiz), onda bu işleriň hemmesini edip bilmeýär. Näme üçin bu beýle? Daşary ýurt açary CUSTOMER.ADDRESS_ID esasy açary ADDRESS.ADDRESS_ID ikinjisiniň takyk bahasyny kepillendirýär, bu bolsa JOIN amalynyň hatar sanyny köpeltmeýän ýa-da azaltmaýan "... to-one" birleşjekdigini aňladýar. . Hiç hili hatar saýlamaýarys ýa-da asla islemeýäris, şonuň üçin olary ýüklemegiň manysy ýok. JOIN-i aýyrmak, soragyň netijesine asla täsir etmez. Maglumatlar bazalary muny elmydama edýär. Aşakdaky soragy islendik maglumat bazasynda işledip bilersiňiz:

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

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

Bu ýagdaýda, aşakdaky soragy ýerine ýetireniňiz ýaly, arifmetiki kadadan çykma garaşyp bilersiňiz:

SELECT 1 / 0 FROM dual

Emma beýle bolmaýar. Optimizator (ýa-da seljeriji), EXISTS predikatynda (SELECT ..) hiç bir saýlanan sanaw elementiniň talapyň netijesini üýtgetmejekdigini kepillendirip biler, şonuň üçin ony ýerine ýetirmegiň zerurlygy ýok. Bu ýaly!

Bu aralykda ...

ORM-leri iň gaharlandyrýan meseleleriň biri, SELECT * talaplaryny ýazmak gaty aňsat. Aslynda, mysal üçin, HQL / JPQL-da, adatça, adaty ýagdaýda ulanylýar. SELECT maddasyny düýbünden taşlap bileris, sebäbi ähli guramany alarys, şeýlemi? Mysal üçin:

FROM v_customer

Mysal üçin, “Hibernate” bilen işleşmegiň hünärmeni we tarapdary Wlad Mihalcea, tölegden soň hiç hili üýtgeşme saklamak islemeýändigiňize göz ýetireniňizde, hemişe diýen ýaly [ökde] talaplary ulanmagy maslahat berýär. ORM-ler obýekt grafikleriniň dowamlylygy meselesiniň çözülmegine ep-esli kömek edýär. Bellik: erjellik. Aslynda obýekt grafiklerini üýtgetmek we üýtgeşmeleri tygşytlamak meseleleri biri-biri bilen baglanyşyklydyr. Thatöne beýle etjek däl bolsaň, näme üçin manysyny çykarmakdan biynjalyk bolýarsyň? Näme üçin arassalanan haýyş ýazmaly däl? Aýdyň: ýerine ýetiriş nukdaýnazaryndan, aýratyn ulanylyş ýagdaýyna laýyk bir talap ýazmak, beýleki wariantlardan has gowudyr. Maglumat toplumyňyz az bolansoň, parhy ýok bolmagy mümkin. Gowy. Eventuallyöne ahyrsoňy ulalmak zerurlygy ýüze çykanda, programma grafigini hökmany geçmegiň ýerine talaplary ulanmak üçin programmalaryňyzy täzeden düzmek gaty kyn bolar. Onsuz etmeli bir zadyňyz bolar.

Bir zadyň bardygyny ýa-da ýokdugyny bilmek üçin hatarlary sanamak

Iň erbet çeşmeleriň biri, maglumatlar bazasynda bir zadyň bardygyny ýa-da ýokdugyny bilmek üçin COUNT (*) talaplaryny işletmekdir. Mysal üçin, berlen ulanyjynyň asla sargytlarynyň bardygyny ýa-da ýokdugyny anyklamaly. Biz haýyşy ýerine ýetirýäris:

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

Başlangyç. COUNT = 0 bolsa, onda sargyt ýok. Otherwiseogsam, hawa. Öndürijilik beýle erbet bolmaz, sebäbi ORDERS.USER_ID sütüninde indeksimiz bar. Aboveöne ýokardaky talapyň ýerine ýetirilişi aşakdaky wariant bilen deňeşdiriler öýdýärsiňizmi?

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

Hakyky barlygyň çaklamasy, birinji tapylan badyna goşmaça setirleri gözlemegi bes etjekdigine düşünmek üçin raketa alymy gerek däl . Şeýlelik bilen netije “sargyt ýok” bolup çyksa, tizlik deňeşdiriler. Şeýle-de bolsa, netije “hawa, sargytlar bar” bolsa, takyk mukdaryny sanamagyň zerurlygy ýok bolsa, jogap has çalt kabul ediler. Galyberse-de, takyk sany bilen gyzyklanamzok. Şeýle-de bolsa, maglumat bazasyna hasaplamagy tabşyrdyk ( gereksiz iş ) we maglumatlar bazasy 1-den uly ( zerur iş ) ähli netijeleri äsgermezlik edýändigimizi bilmeýär . Elbetde, şol bir netijeleri gazanmak üçin JPA goldaýan kolleksiýada list.size () çagyrsak has erbet bolardy. Bu barada öňem blogymda ýazypdym we iki warianty deňeşdirip synagdan geçirdim ...

Netije

Bu makalada aç-açan aýdylýar. Maglumatlar bazasyny gereksiz, ýöne zerur işleri etmäge mejbur etme . Gerek däl , sebäbi talaplary göz öňünde tutup, belli bir eseriň edilmeginiň zerur däldigini bilýärsiňiz. Şeýle-de bolsa, maglumat bazasyna muny etmegi aýdýarsyňyz. Talap edilýär, sebäbi bu işiň zerur däldigini üpjün etmek üçin maglumat bazasy üçin hiç hili ýol ýok . Bu maglumatlar diňe müşderi üçin elýeterlidir we serwer üçin elýeterli däldir. Şonuň üçin maglumat bazasy ony ýerine ýetirmeli. Makala, esasan, seretmek üçin amatly obýekt bolany üçin SELECT * -e gönükdirildi. Emma bu diňe maglumat bazalaryna degişli däl. Bu, müşderiniň serwere zerur däl, ýöne zerur işleri etmegi aýdýan ähli paýlanan algoritmlere degişlidir . Ortaça AngularJS programmaňyzda UI A hyzmatynyň netijesi bilen B-e birnäçe gezek jaň edip, B çagyryşyna bir gezek jaň etmegiň ýerine näçe gezek N + 1 mesele bar? Bu gaty ýygy duş gelýän hadysadyr. Çözüw hemişe birmeňzeş. Buýruklaryňyzy ýerine ýetirýän gurama näçe köp maglumat berseňiz, bu buýruklary çalt ýerine ýetirýär (teoretiki taýdan). Iň amatly talaplary ýazyň. Elmydama. Bütin ulgamyňyz munuň üçin sag bolsun aýdýar. Asyl makala