Mga problema sa pagganap ng SQL na nagmumula sa "hindi kailangan ngunit kinakailangang trabaho"

Antas ng kaalaman na kinakailangan upang maunawaan ang artikulo: isang pangkalahatang pag-unawa sa mga database at SQL, ilang praktikal na karanasan sa DBMS. Marahil ang pinakamahalagang bagay na matututunan mong magsulat ng epektibong mga query sa SQL ay ang pag-index. Gayunpaman, sa pangalawang lugar, napakalapit sa likod, ay ang kaalaman na maraming mga kliyente ng SQL ang nangangailangan ng database na gumawa ng maraming "hindi kailangan ngunit kinakailangang gawain" . Ulitin pagkatapos ko: Ano ang "hindi kailangan ngunit obligadong gawain"? Tulad ng sinabi sa atin ni Captain Obvious, siya:

Hindi kailangan

Hayaang kailanganin ng aming client application ang sumusunod na data: Walang kakaiba. Nakikipagtulungan kami sa isang database ng pelikula (tulad ng database ng Sakila ) at gustong ipakita ang pamagat at rating ng lahat ng pelikula sa mga user. Ang sumusunod na query ay maaaring magbigay ng resulta na kailangan namin:

SELECT title, rating
FROM film

Gayunpaman, ang aming application (o ang aming ORM) sa halip ay nagpapatupad ng query na ito:

SELECT *
FROM film

Ano ang makukuha natin bilang resulta? Hulaan. Nakatanggap kami ng maraming walang kwentang impormasyon: Sa kanan maaari mo ring makita ang ilang kumplikadong JSON na nilo-load:

• mula sa disk
• sa cache
• sa pamamagitan ng wire
• sa memorya ng kliyente
• at sa wakas ay itinapon [bilang hindi kailangan]

Oo, itinatapon namin ang karamihan sa impormasyong ito. Ang lahat ng mga aksyon na ginawa upang kunin ang impormasyong ito ay naging ganap na walang silbi. Totoo ba? Totoo ba.

Sapilitan

At ngayon - ang pinakamasamang bahagi. Bagama't marami na ngayong magagawa ang mga optimizer, ang mga pagkilos na ito ay sapilitan para sa database. Ang database ay walang paraan upang malaman na ang client application ay hindi nangangailangan ng 95% ng data na ito. At ito lang ang pinakasimpleng halimbawa. Isipin ang pagkonekta ng ilang mga talahanayan... Kaya ano, sasabihin mo, ngunit ang mga database ay mabilis? Hayaan akong maliwanagan ka sa ilang mga bagay na malamang na hindi mo naisip. Siyempre, ang oras ng pagpapatupad ng isang indibidwal na kahilingan ay hindi talaga nakakaapekto sa anuman. Okay, tumakbo ito ng isa at kalahating beses na mas mabagal, ngunit malalampasan natin ito, tama ba? Para sa kaginhawahan? Minsan ito ay totoo. Ngunit kung palagi mong isinasakripisyo ang pagganap para sa kaginhawahan , magsisimulang dumami ang maliliit na bagay na ito. Hindi na natin pag-uusapan ang pagganap (ang bilis ng pagpapatupad ng mga indibidwal na kahilingan), ngunit tungkol sa throughput (oras ng pagtugon ng system), at pagkatapos ay magsisimula ang mga seryosong problema, na hindi gaanong madaling lutasin. Iyan ay kapag nawalan ka ng scalability. Tingnan natin ang mga plano sa pagpapatupad, sa kasong ito, ang Oracle DBMS:

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

kumpara sa:

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

Ang pagpapatakbo ng SELECT * query sa halip na SELECT title, ang rating ay gumagamit ng 8 beses na mas maraming memory sa database. Walang hindi inaasahan, tama ba? Alam namin na mangyayari ito. Ngunit sumasang-ayon pa rin kami dito para sa marami sa aming mga kahilingan kung saan hindi namin kailangan ang lahat ng data na ito. Gumagawa kami ng hindi kailangan ngunit ipinag-uutos na trabaho para sa database , na patuloy na natambak at nakatambak. Gumagamit kami ng 8 beses na mas memory kaysa sa kinakailangan (siyempre, magbabago ang multiplier). Samantala, sa lahat ng iba pang mga yugto (disk I/O, paglilipat ng data sa network, pagkonsumo ng memorya ng kliyente) ang mga problema ay eksaktong pareho, ngunit laktawan ko ang mga ito at sa halip ay titingnan ang...

Paggamit ng mga Index

Karamihan sa mga database ngayon ay pinahahalagahan na ang konsepto ng sumasaklaw sa mga index . Ang isang sumasaklaw na index ay hindi mismo isang espesyal na uri ng index. Ngunit ito ay maaaring lumabas na isang "espesyal na index" para sa isang partikular na query, alinman "sa pamamagitan ng aksidente" o dahil ito ay nilayon na maging gayon. Isaalang-alang ang sumusunod na query:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Walang hindi inaasahan sa mga tuntunin ng pagpapatupad nito. Ito ay isang simpleng kahilingan. Tingnan ang hanay ayon sa index, i-access ang talahanayan - at tapos ka na:

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

Magandang plano, di ba? Well, kung talagang kailangan natin ito, hindi: Malinaw, tayo ay nag-aaksaya ng memorya, atbp. Isaalang-alang natin ang query na ito bilang isang alternatibo:

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

Ang kanyang plano ay ito:

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

Nagawa naming ganap na alisin ang access sa talahanayan, salamat sa pagkakaroon ng isang index na nakakatugon sa lahat ng mga pangangailangan ng aming query... isang sumasaklaw na index. Mahalaga ba ito? At kung paano! Binibigyang-daan ka ng diskarteng ito na pabilisin ang ilang mga query sa pamamagitan ng isang pagkakasunud-sunod ng magnitude (o pabagalin ang mga ito sa pamamagitan ng isang order ng magnitude kapag ang index ay hindi na sumasaklaw pagkatapos ng ilang mga pagbabago). Hindi laging magagamit ang mga sumasaklaw na index. Kailangan mong magbayad para sa mga index at hindi ka dapat magdagdag ng masyadong marami sa mga ito. Ngunit sa kasong ito, ang lahat ay halata. Suriin natin ang pagganap:

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

Tandaan na ang talahanayan ng aktor ay mayroon lamang 4 na column, kaya ang pagkakaiba sa pagganap sa pagitan ng mga pahayag 1 at 2 ay hindi ganoon kalaki, ngunit ito ay makabuluhan pa rin. Mapapansin ko rin na gumamit ako ng mga pahiwatig ng Oracle optimizer para piliin ng optimizer ang isa o isa pang partikular na index para sa query. Ang Operator 3 ay ang hindi mapag-aalinlanganang nagwagi sa aming lahi. Ang pagganap nito ay mas mahusay, at pinag-uusapan natin ang tungkol sa isang napakasimpleng query. Muli, kapag isinulat namin ang SELECT *, gumagawa kami ng hindi kailangan ngunit ipinag-uutos na trabaho para sa database na hindi nito ma-optimize. Hindi niya pipiliin ang sumasaklaw na index dahil ito ay may bahagyang mas mataas na overhead kaysa sa LAST_NAME na index na pinili niya, at, bukod sa iba pang mga bagay, kailangan pa rin niyang i-access ang talahanayan upang makakuha ng walang silbi na LAST_UPDATE na column, halimbawa. Ngunit habang mas malalim ang pagsusuri natin sa SELECT *, mas malala ang mga nangyayari. Pag-usapan natin ang...

Mga conversion ng SQL

Napakahusay ng pagganap ng mga Optimizer dahil binabago nila ang mga query sa SQL ( Napag-usapan ko kung paano ito gumagana sa aking kamakailang pag-uusap sa Voxxed Days sa Zurich ). Halimbawa, mayroong isang napakalakas na pagbabagong "exception JOIN". Isaalang-alang ang sumusunod na view ng helper na kailangan naming gawin upang maiwasang manu-manong sumali sa lahat ng mga talahanayang ito sa bawat oras:

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)

Ginagawa lang ng view na ito ang lahat ng "...-to-one" na pinagsama sa pagitan ng CUSTOMER customer table at iba't ibang table para sa mga bahagi ng kanilang address. Salamat, normalisasyon. Isipin na, pagkatapos magtrabaho nang kaunti sa view na ito, nasanay kami at nakalimutan ang tungkol sa mga talahanayan na pinagbabatayan nito. At ngayon isinasagawa namin ang sumusunod na query:

SELECT *
FROM v_customer

Bilang resulta, nakakakuha kami ng isang napaka-kahanga-hangang plano:

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

Well, siyempre. Ginagawa ng database ang lahat ng pagsali at pag-scan ng buong talahanayan dahil iyon ang sinabi namin na gawin nito - kunin ang lahat ng data na ito. Ngayon, muli, isipin na ang talagang kailangan namin ay ito: Ano, seryoso, tama ba? Ngayon ay nagsisimula ka nang maunawaan kung ano ang sinasabi ko. Ngunit isipin na may natutunan kami mula sa mga nakaraang pagkakamali, at isagawa ito, mas pinakamainam na query:

SELECT first_name, last_name
FROM v_customer

Ngayon tingnan natin kung ano ang nangyari!

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

Mga matinding pagbabago para sa mas mahusay sa mga tuntunin ng pagpapatupad. Ang mga pagsali ay inalis dahil nakikita na ngayon ng optimizer na wala silang silbi , at kung nakikita nito iyon (at hindi mo ginawang mandatory ang gawaing iyon sa pamamagitan ng pagpili sa *), kung gayon hindi nito magagawa ang lahat ng gawaing iyon. Bakit ganito sa kasong ito? Ang foreign key na CUSTOMER.ADDRESS_ID sa pangunahing key na ADDRESS.ADDRESS_ID ay ginagarantiyahan ang eksaktong isang halaga ng huli, na nangangahulugang ang JOIN operation ay isang "...-to-one" na pagsali na hindi nagpapataas o nagpapababa ng bilang ng mga row . At dahil hindi kami pumipili o humihiling ng anumang mga hilera, walang saysay na i-load ang mga ito. Ang pag-alis sa JOIN ay malamang na hindi makakaapekto sa resulta ng query. Ginagawa ito ng mga database sa lahat ng oras. Maaari mong patakbuhin ang sumusunod na query sa halos anumang 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)

Sa kasong ito, maaari mong asahan ang isang pagbubukod sa aritmetika na itatapon, tulad ng kapag isinasagawa ang sumusunod na query:

SELECT 1 / 0 FROM dual

Ngunit hindi ito nangyayari. Ang optimizer (o kahit na ang parser) ay maaaring matiyak na walang mga piling elemento ng listahan sa EXISTS predicate (SELECT ..) na magbabago sa resulta ng query, kaya hindi na kailangang isagawa ito. Ganito!

Samantala...

Ang isa sa mga pinaka nakakainis na problema sa mga ORM ay napakadali nilang magsulat ng SELECT * query. Sa katunayan, halimbawa, sa HQL / JPQL karaniwang ginagamit ang mga ito bilang default. Maaari nating alisin ang SELECT clause nang buo, dahil kukunin natin ang buong entity, tama ba? Halimbawa:

FROM v_customer

Halimbawa, si Vlad Mihalcea, isang dalubhasa at tagapagtaguyod para sa pagbuo sa Hibernate , ay nagrerekomenda ng paggamit ng [kwalipikadong] mga query halos palagi kapag sigurado kang hindi mo gustong mag-save ng anumang mga pagbabago pagkatapos ng pag-checkout. Lubos na pinadali ng mga ORM ang solusyon sa problema ng pananatili ng mga object graph. Tandaan: Pagtitiyaga. Ang mga gawain ng aktwal na pagbabago ng mga object graph at pag-save ng mga pagbabago ay hindi mapaghihiwalay na nauugnay. Ngunit kung hindi mo gagawin iyon, kung gayon bakit mag-abala sa pagkuha ng kakanyahan? Bakit hindi sumulat ng [pinong] kahilingan? Maging malinaw tayo: mula sa isang pananaw sa pagganap, ang pagsulat ng isang query na partikular na iniakma sa iyong partikular na kaso ng paggamit ay malinaw na mas mahusay kaysa sa anumang iba pang opsyon. Maaaring wala kang pakialam dahil maliit ang iyong set ng data at hindi ito mahalaga. Malaki. Ngunit kapag kailangan mo ng scalability sa kalaunan, ang muling pagdidisenyo ng iyong mga application upang gumamit ng mga query sa halip na kinakailangang pag-traversal ng entity graph ay magiging mahirap. At magkakaroon ka ng isang bagay na gagawin nang wala ito.

Nagbibilang ng mga hilera para malaman kung may naroroon

Ang isa sa mga pinakamasamang pag-aaksaya ng mga mapagkukunan ay nagpapatakbo ng COUNT(*) na mga query para lang makita kung mayroong isang bagay sa database. Halimbawa, kailangan nating malaman kung may mga order ang isang partikular na user. At isinasagawa namin ang kahilingan:

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

elementarya. Kung COUNT = 0, walang mga order. Kung hindi, oo. Hindi magiging ganoon kalala ang performance dahil malamang na mayroon tayong index sa column na ORDERS.USER_ID. Ngunit ano sa palagay mo ang pagganap ng query sa itaas ay ihahambing sa sumusunod na opsyon:

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

Hindi kailangan ng isang rocket scientist upang malaman na ang isang tunay na predicate ng pag-iral ay titigil sa paghahanap ng mga karagdagang string sa sandaling mahanap nito ang una . Kaya kung ang resulta ay lumabas na "walang mga order", kung gayon ang bilis ay maihahambing. Kung, gayunpaman, ang resulta ay "oo, may mga order," kung gayon sa kaso kung saan ang eksaktong dami ay hindi kailangang bilangin, ang sagot ay matatanggap nang mas mabilis. Pagkatapos ng lahat, hindi kami interesado sa eksaktong numero. Gayunpaman, sinabi namin sa database na kalkulahin ito ( hindi kinakailangang trabaho ) at hindi alam ng database na binabalewala namin ang lahat ng mga resulta na higit sa 1 ( kinakailangang trabaho ). Siyempre, magiging mas masahol pa kung tatawagan namin ang list.size() sa isang koleksyon na sinusuportahan ng JPA upang makamit ang parehong mga resulta. Nagsulat na ako tungkol dito sa aking blog kanina, at nagsagawa ng comparative testing ng parehong mga opsyon sa...

Konklusyon

Ang artikulong ito ay nagsasaad ng halata. Huwag pilitin ang database na gumawa ng hindi kailangan ngunit kinakailangang gawain . Ito ay hindi kailangan dahil, dahil sa mga kinakailangan, alam mo na ang ilang partikular na piraso ng trabaho ay hindi kailangang gawin. Gayunpaman, sasabihin mo sa database na gawin ito. Ito ay kinakailangan dahil walang paraan para sa database upang matiyak na ang gawaing ito ay hindi kailangan . Ang impormasyong ito ay magagamit lamang sa kliyente at hindi magagamit sa server. Kaya ang database ay kailangang isagawa ito. Nakatuon ang artikulo sa SELECT *, pangunahin dahil ito ay isang maginhawang bagay upang tingnan. Ngunit nalalapat ito hindi lamang sa mga database. Nalalapat ito sa lahat ng ipinamahagi na algorithm kung saan sinasabi ng kliyente sa server na gumawa ng hindi kailangan ngunit kinakailangang gawain . Ilang N+1 na gawain ang mayroon sa iyong average na AngularJS application kung saan ang UI ay umiikot sa resulta ng serbisyo A, pagtawag sa serbisyo B nang maraming beses, sa halip na i-pack ang lahat ng tawag sa B sa isang tawag? Ito ay isang pangkaraniwang pangyayari. Ang solusyon ay palaging pareho. Kung mas maraming impormasyon ang ibibigay mo sa entity na nagpapatupad ng iyong mga utos, mas mabilis itong (theoretically) na nagpapatupad ng mga utos na iyon. Sumulat ng pinakamainam na mga query. Laging. Ang iyong buong sistema ay magpapasalamat sa iyo para dito. Orihinal na artikulo