"keraksiz, ammo talab qilinadigan ish" dan kelib chiqadigan SQL ishlash muammolari

Maqolani tushunish uchun zarur bo'lgan bilim darajasi: ma'lumotlar bazalari va SQL haqida umumiy tushuncha, ma'lumotlar bazasi bo'yicha ba'zi amaliy tajriba. Samarali SQL so'rovlarini yozishni o'rganishingiz mumkin bo'lgan eng muhim narsa indekslashdir. Biroq, ikkinchi o'rinda, juda yaqin orqada, ko'plab SQL mijozlari ma'lumotlar bazasidan juda ko'p "keraksiz, ammo zarur ishlarni" bajarishni talab qiladigan bilimdir . Mendan keyin takrorlang: "Keraksiz, ammo majburiy ish" nima? Kapitan Obvious bizga aytganidek, u:

Keraksiz

Bizning mijoz ilovamizga quyidagi ma'lumotlar kerak bo'lsin: Hech qanday g'ayrioddiy narsa yo'q. Biz kino maʼlumotlar bazasi bilan ishlayapmiz (masalan, Sakila maʼlumotlar bazasi ) va barcha filmlar nomi va reytingini foydalanuvchilarga koʻrsatmoqchimiz. Quyidagi so'rov bizga kerakli natijani berishi mumkin:

SELECT title, rating
FROM film

Biroq, bizning ilovamiz (yoki ORM) o'rniga ushbu so'rovni bajaradi:

SELECT *
FROM film

Natijada nimaga erishamiz? Taxmin qiling. Biz juda ko'p keraksiz ma'lumotlarni olamiz: O'ng tomonda siz hatto ba'zi bir murakkab JSON yuklanayotganini ko'rishingiz mumkin:

• diskdan
• keshlash uchun
• sim orqali
• mijoz xotirasida
• va nihoyat tashlandi [keraksiz]

Ha, biz bu ma'lumotlarning ko'p qismini tashlaymiz. Ushbu ma'lumotni olish uchun qilingan barcha harakatlar mutlaqo foydasiz bo'lib chiqdi. Bu rostmi? Bu rostmi.

Majburiy

Va endi - eng yomoni. Optimizatorlar hozir juda ko'p ish qila olishiga qaramasdan, bu harakatlar ma'lumotlar bazasi uchun majburiydir. Ma'lumotlar bazasi mijoz dasturiga ushbu ma'lumotlarning 95% kerak emasligini bilishning hech qanday usuli yo'q. Va bu eng oddiy misol. Bir nechta jadvallarni ulashni tasavvur qiling ... Xo'sh, nima deysiz, lekin ma'lumotlar bazalari tez? Sizni xayolingizga ham keltirmagan narsalar haqida maʼlumot bersam. Albatta, individual so'rovni bajarish vaqti hech narsaga ta'sir qilmaydi. Yaxshi, u bir yarim baravar sekinroq ishladi, lekin biz buni engib o'tamiz, to'g'rimi? Qulaylik uchunmi? Ba'zan bu haqiqat. Ammo agar siz har doim qulaylik uchun ishlashni qurbon qilsangiz , bu kichik narsalar qo'shila boshlaydi. Biz endi ishlash (individual so'rovlarni bajarish tezligi) haqida emas, balki o'tkazish qobiliyati (tizimning javob vaqti) haqida gapiramiz, keyin esa hal qilish unchalik oson bo'lmagan jiddiy muammolar boshlanadi. O'sha paytda siz miqyoslilikni yo'qotasiz. Keling, ijro rejalarini ko'rib chiqaylik, bu holda Oracle DBMS:

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

bilan solishtirganda:

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

SELECT sarlavhasi o'rniga SELECT * so'rovini bajarish, reyting ma'lumotlar bazasida 8 marta ko'proq xotiradan foydalanadi. Hech narsa kutilmagan, to'g'rimi? Bu sodir bo'lishini bilardik. Ammo biz hali ham ko'plab so'rovlarimiz uchun bunga rozi bo'lamiz, ularda bizga bu ma'lumotlarning barchasi kerak emas. Biz ma'lumotlar bazasi uchun keraksiz, ammo majburiy ishlarni yaratamiz , ular to'planib qoladi. Biz kerak bo'lganidan 8 marta ko'proq xotira ishlatamiz (ko'paytiruvchi o'zgaradi, albatta). Shu bilan birga, boshqa barcha bosqichlarda (diskni kiritish-chiqarish, tarmoq orqali ma'lumotlarni uzatish, mijoz tomonidan xotira iste'moli) muammolar mutlaqo bir xil, ammo men ularni o'tkazib yuboraman va o'rniga ...

Indekslardan foydalanish

Ko'pgina ma'lumotlar bazalari bugungi kunda indekslarni qoplash tushunchasini allaqachon qadrlashgan . Qoplovchi indeks o'zi indeksning maxsus turi emas. Lekin u ma'lum bir so'rov uchun "tasodifan" yoki shunday bo'lishi uchun mo'ljallangan "maxsus indeks" bo'lib chiqishi mumkin. Quyidagi so'rovni ko'rib chiqing:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Uni amalga oshirish nuqtai nazaridan kutilmagan narsa yo'q. Bu oddiy so'rov. Indeks bo'yicha diapazonni ko'ring, jadvalga kiring - va siz bajardingiz:

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

Yaxshi reja, shunday emasmi? Xo'sh, agar bizga bu haqiqatan ham kerak bo'lsa, unda yo'q: Shubhasiz, biz xotirani behuda sarflaymiz va hokazo. Keling, ushbu so'rovni muqobil sifatida ko'rib chiqaylik:

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

Uning rejasi quyidagicha:

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

Bizning so'rovimizning barcha ehtiyojlarini qondiradigan indeks mavjudligi tufayli jadvalga kirishni butunlay yo'q qilishga muvaffaq bo'ldik ... qoplovchi indeks. Muhimmi? Va qanday! Ushbu yondashuv sizga ba'zi so'rovlarni kattalik tartibi bo'yicha tezlashtirishga imkon beradi (yoki indeks ba'zi o'zgarishlardan keyin qoplanmagan bo'lsa, ularni kattalik tartibida sekinlashtiring). Qoplama indekslaridan har doim ham foydalanish mumkin emas. Siz indekslar uchun to'lashingiz kerak va ulardan ko'pini qo'shmasligingiz kerak. Ammo bu holatda hamma narsa aniq. Keling, ishlashni baholaymiz:

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

E'tibor bering, aktyorlar jadvali faqat 4 ta ustunga ega, shuning uchun 1 va 2 bayonotlar orasidagi ishlash farqi unchalik katta emas, lekin u hali ham muhim. Shuni ham ta'kidlab o'tamanki, men optimallashtiruvchi so'rov uchun u yoki bu aniq indeksni tanlashi uchun Oracle optimallashtiruvchi maslahatlaridan foydalanganman. Operator 3 bizning poygamizning so'zsiz g'olibidir. Uning ishlashi ancha yaxshi va biz juda oddiy so'rov haqida gapiramiz. Shunga qaramay, biz SELECT * ni yozganimizda, biz ma'lumotlar bazasi uchun keraksiz, lekin u optimallashtira olmaydigan majburiy ishlarni yaratamiz. U qoplama indeksini tanlamaydi, chunki u tanlagan LAST_NAME indeksidan biroz yuqoriroq va boshqa narsalar qatorida, masalan, keraksiz LAST_UPDATE ustunini olish uchun jadvalga kirishi kerak. Ammo biz SELECT * ni qanchalik chuqur tahlil qilsak, shunchalik yomonroq bo'ladi. Keling, gaplashaylik ...

SQL konvertatsiyalari

Optimizatorlar juda yaxshi ishlaydi, chunki ular SQL so'rovlarini o'zgartiradilar ( Men bu qanday ishlashi haqida Tsyurixdagi Voxxed kunlaridagi so'nggi nutqimda gapirgan edim ). Misol uchun, juda kuchli "istisno JOIN" transformatsiyasi mavjud. Ushbu jadvallarni har safar qo'lda birlashtirmaslik uchun biz yaratishimiz kerak bo'lgan quyidagi yordamchi ko'rinishni ko'rib chiqing:

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 ko'rinish oddiygina MUZUCH mijozlar jadvali va ularning manzillari qismlari uchun turli jadvallar o'rtasidagi barcha "...-to-bir" ulanishlarni amalga oshiradi. Rahmat, normalizatsiya. Tasavvur qiling-a, bu ko'rinish bilan biroz ishlagandan so'ng, biz bunga ko'nikib qoldik va uning ostidagi jadvallarni unutdik. Va endi biz quyidagi so'rovni bajaramiz:

SELECT *
FROM v_customer

Natijada, biz juda ta'sirli rejaga ega bo'lamiz:

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

Xo'sh, albatta. Ma'lumotlar bazasi barcha bu qo'shilishlarni va jadvalni to'liq skanerlashni amalga oshirmoqda, chunki biz unga shuni aytdik - bu barcha ma'lumotlarni oling. Endi, yana bir bor tasavvur qiling-a, bizga haqiqatan ham bu kerak edi: Nima, jiddiy, to'g'rimi? Endi siz nima haqida gapirayotganimni tushunishni boshlaysiz. Ammo tasavvur qiling-a, biz o'tmishdagi xatolardan nimanidir o'rgandik va ushbu, eng maqbul so'rovni bajaring:

SELECT first_name, last_name
FROM v_customer

Endi nima bo'lganini tekshiramiz!

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

Amalga oshirish nuqtai nazaridan yaxshi tomonga keskin o'zgarishlar. Birlashmalar olib tashlandi, chunki optimallashtiruvchi endi ularning foydasiz ekanligini ko'ra oladi va agar u buni ko'rsa (va siz * ni tanlab, bu ishni majburiy qilib qo'ymagan bo'lsangiz), u shunchaki barcha ishlarni bajara olmaydi. Nima uchun bu holatda bu shunday? ADDRESS.ADDRESS_ID asosiy kalitiga CUSTOMER.ADDRESS_ID tashqi kaliti ikkinchisining aynan bitta qiymatini kafolatlaydi, ya'ni JOIN operatsiyasi qatorlar sonini oshirmaydigan yoki kamaytirmaydigan "...-birga" birikmasi bo'ladi. . Va biz hech qanday qatorlarni tanlamaganimiz yoki so'ramaganimiz sababli, ularni umuman yuklashning ma'nosi yo'q. JOINni olib tashlash so'rov natijasiga umuman ta'sir qilmasligi mumkin. Ma'lumotlar bazalari buni har doim bajaradi. Siz deyarli har qanday ma'lumotlar bazasida quyidagi so'rovni bajarishingiz mumkin:

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

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

Bunday holda, quyidagi so'rovni bajarishda bo'lgani kabi, arifmetik istisno paydo bo'lishini kutishingiz mumkin:

SELECT 1 / 0 FROM dual

Lekin bu sodir bo'lmaydi. Optimallashtiruvchi (yoki hatto tahlil qiluvchi ham) EXISTS predikatidagi (SELECT ..) tanlangan roʻyxat elementlari soʻrov natijasini oʻzgartirmasligiga ishonch hosil qilishi mumkin, shuning uchun uni bajarishga hojat yoʻq. Mana bunday!

Shu vaqtda...

ORM bilan bog'liq eng zerikarli muammolardan biri shundaki, ular SELECT * so'rovlarini yozish juda oson. Aslida, masalan, HQL / JPQL da ular odatda sukut bo'yicha ishlatiladi. Biz SELECT bandini butunlay o'tkazib yuborishimiz mumkin, chunki biz butun ob'ektni olamiz, to'g'rimi? Masalan:

FROM v_customer

Masalan, Hibernate bilan ishlash boʻyicha ekspert va advokat Vlad Mixalcea hisobdan chiqqandan keyin hech qanday oʻzgarishlarni saqlashni xohlamasligingizga ishonchingiz komil boʻlsa, deyarli har doim [malakali] soʻrovlardan foydalanishni tavsiya qiladi. ORMlar ob'ekt grafiklarining barqarorligi muammosini hal qilishni sezilarli darajada osonlashtiradi. Eslatma: qat'iylik. Ob'ekt grafiklarini haqiqatda o'zgartirish va o'zgarishlarni saqlash vazifalari bir-biri bilan uzviy bog'liqdir. Ammo agar siz buni qilmoqchi bo'lmasangiz, unda nima uchun mohiyatni ajratib olish kerak? Nega [takomillashtirilgan] so'rov yozmaysiz? Keling, aniq aytaylik: ishlash nuqtai nazaridan, sizning maxsus foydalanish holatlaringizga moslashtirilgan so'rovni yozish boshqa har qanday variantga qaraganda yaxshiroq. Sizni qiziqtirmasligingiz mumkin, chunki sizning ma'lumotlar to'plami kichik va bu muhim emas. Ajoyib. Ammo siz oxir-oqibat miqyoslilikka muhtoj bo'lganingizda, ob'ekt grafigini majburiy o'tish o'rniga so'rovlardan foydalanish uchun ilovalaringizni qayta loyihalash juda qiyin bo'ladi. Va usiz qiladigan narsangiz bo'ladi.

Biror narsa bor yoki yo'qligini aniqlash uchun qatorlarni sanash

Ma'lumotlar bazasida biror narsa bor yoki yo'qligini bilish uchun COUNT(*) so'rovlarni bajarish eng yomon resurslardan biridir. Misol uchun, ma'lum bir foydalanuvchida umuman buyurtma bor yoki yo'qligini aniqlashimiz kerak. Va biz so'rovni bajaramiz:

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

Boshlang'ich. Agar COUNT = 0 bo'lsa, unda buyurtmalar yo'q. Aks holda, ha. ORDERS.USER_ID ustunida indeks mavjud bo‘lgani uchun unchalik yomon bo‘lmaydi. Ammo yuqoridagi so'rovning ishlashi quyidagi variant bilan qanday taqqoslanadi deb o'ylaysiz:

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

Haqiqiy mavjudlik predikati birinchisini topishi bilanoq qo'shimcha satrlarni qidirishni to'xtatib qo'yishini aniqlash uchun raketachi olim kerak emas . Shunday qilib, agar natija "buyurtmasiz" bo'lib chiqsa, tezlikni solishtirish mumkin bo'ladi. Agar natijada "ha, buyurtmalar bor" bo'lsa, aniq miqdorni hisoblash kerak bo'lmagan taqdirda, javob tezroq qabul qilinadi . Axir bizni aniq raqam qiziqtirmaydi. Biroq, biz ma'lumotlar bazasiga uni hisoblashni aytdik ( keraksiz ish ) va ma'lumotlar bazasi biz 1 dan katta barcha natijalarni e'tiborsiz qoldirayotganimizni bilmaydi ( kerakli ish ). Albatta, xuddi shunday natijalarga erishish uchun JPA tomonidan qo'llab-quvvatlangan to'plamda list.size() ga qo'ng'iroq qilsak, bundan ham yomonroq bo'lar edi. Men bu haqda avvalroq blogimda yozgan edim va ikkala variantni ham qiyosiy sinovdan o'tkazdim ...

Xulosa

Ushbu maqolada aniq aytilgan. Ma'lumotlar bazasini keraksiz, ammo talab qilinadigan ishlarni qilishga majburlamang . Bu kerak emas , chunki talablarni inobatga olgan holda, ba'zi bir ishni bajarish shart emasligini bilasiz. Biroq, siz ma'lumotlar bazasiga buni qilishni aytasiz. Bu talab qilinadi, chunki ma'lumotlar bazasi uchun bu ish keraksizligini ta'minlash uchun hech qanday yo'l yo'q . Ushbu ma'lumot faqat mijoz uchun mavjud va serverda mavjud emas. Shunday qilib, ma'lumotlar bazasi uni bajarishi kerak. Maqola SELECT * ga e'tibor qaratdi, chunki u ko'rish uchun juda qulay ob'ekt. Ammo bu nafaqat ma'lumotlar bazalariga tegishli. Bu mijoz serverga keraksiz, lekin talab qilinadigan ishlarni bajarishni aytadigan barcha tarqatilgan algoritmlar uchun amal qiladi . O'rtacha AngularJS ilovangizda nechta N+1 vazifalari mavjud bo'lib, u B ga barcha qo'ng'iroqlarni bitta qo'ng'iroqqa to'plash o'rniga, B xizmatiga bir necha marta qo'ng'iroq qilib, A xizmatining natijasi bo'ylab aylanadi? Bu juda keng tarqalgan hodisa. Yechim har doim bir xil. Buyruqlaringizni bajaruvchi ob'ektga qanchalik ko'p ma'lumot bersangiz, u (nazariy jihatdan) bu buyruqlarni tezroq bajaradi. Optimal so'rovlarni yozing. Har doim. Buning uchun butun tizimingiz sizga rahmat aytadi. Asl maqola