"Lazımsız, lakin tələb olunan iş" nəticəsində yaranan SQL performans problemləri

Məqaləni başa düşmək üçün tələb olunan bilik səviyyəsi: verilənlər bazası və SQL haqqında ümumi anlayış, DBMS ilə bəzi praktik təcrübə. Effektiv SQL sorğularını yazmağı öyrənə biləcəyiniz ən vacib şey, yəqin ki, indeksləşdirmədir. Bununla belə, ikinci yerdə, çox yaxından arxada, bir çox SQL müştərisinin verilənlər bazasından çoxlu "lazımsız, lakin zəruri işlər" görməsini tələb etdiyi bilikdir . Məndən sonra təkrar et: “Lazımsız, lakin məcburi iş” nədir? Kapitan Obvious bizə dediyi kimi, o:

Lazımsız

Müştəri tətbiqimizə aşağıdakı məlumatlar lazım olsun: Qeyri-adi heç nə yoxdur. Biz film bazası ilə işləyirik (məsələn, Sakila verilənlər bazası ) və istifadəçilərə bütün filmlərin başlığını və reytinqini göstərmək istəyirik. Aşağıdakı sorğu bizə lazım olan nəticəni verə bilər:

SELECT title, rating
FROM film

Bununla belə, tətbiqimiz (və ya ORM) əvəzinə bu sorğunu yerinə yetirir:

SELECT *
FROM film

Nəticədə nə əldə edirik? Təxmin et. Çoxlu faydasız məlumatlar alırıq: Sağda hətta bəzi mürəkkəb JSON-un yükləndiyini görə bilərsiniz:

• diskdən
• keş etmək
• tel ilə
• müştərinin xatirəsinə
• və nəhayət atıldı [lazımsız olaraq]

Bəli, biz bu məlumatların çoxunu atırıq. Bu məlumatı çıxarmaq üçün görülən bütün tədbirlər tamamilə faydasız oldu. Doğrudurmu? Doğrudurmu.

Məcburi

İndi - ən pis hissə. Optimizatorlar indi çox şey edə bilsələr də, bu hərəkətlər verilənlər bazası üçün məcburidir. Verilənlər bazasında müştəri tətbiqinin bu məlumatların 95%-nə ehtiyacı olmadığını bilmək imkanı yoxdur. Və bu ən sadə nümunədir. Təsəvvür edin ki, bir neçə cədvəli birləşdirəsiniz... Bəs nə deyirsiniz, amma verilənlər bazası sürətlidir? İcazə verin, yəqin ki, düşünmədiyiniz bəzi şeylər haqqında sizə məlumat verim. Təbii ki, fərdi sorğunun icra müddəti əslində heç nəyə təsir etmir. Yaxşı, bir yarım dəfə yavaş işləyirdi, amma biz bunun öhdəsindən gələcəyik, elə deyilmi? Rahatlıq üçün? Bəzən bu doğrudur. Ancaq hər zaman rahatlıq üçün performansı qurban versəniz , bu kiçik şeylər artmağa başlayacaq. Artıq performans (fərdi sorğuların icra sürəti) haqqında deyil, ötürmə qabiliyyəti (sistemin cavab müddəti) haqqında danışacağıq və sonra həlli o qədər də asan olmayan ciddi problemlər başlayacaq. Bu zaman siz miqyaslılığı itirirsiniz. Gəlin icra planlarına nəzər salaq, bu halda Oracle DBMS:

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

ilə müqayisədə:

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

SELECT başlığı əvəzinə SELECT * sorğusunu icra etməklə, reytinq verilənlər bazasında 8 dəfə daha çox yaddaş istifadə edir. Gözlənilməz heç nə yoxdur, elə deyilmi? Bunun baş verəcəyini bilirdik. Ancaq biz hələ də bütün bu məlumatlara ehtiyac duymadığımız bir çox sorğularımız üçün bununla razılaşırıq. Biz verilənlər bazası üçün lazımsız, lakin məcburi işlər yaradırıq ki, bu da yığılıb qalaqlanır. Lazım olduğundan 8 dəfə çox yaddaş istifadə edirik (təbii ki, çarpan dəyişəcək). Bu arada, bütün digər mərhələlərdə (disk daxil/çıxışı, şəbəkə üzərindən məlumat ötürülməsi, müştəri tərəfindən yaddaş istehlakı) problemlər tam olaraq eynidir, lakin mən onları atlayıb yerinə baxacağam...

İndekslərdən istifadə

Bu gün əksər verilənlər bazaları indeksləri əhatə etmək konsepsiyasını artıq qiymətləndirmişdir . Əhatə indeksi özü xüsusi bir indeks növü deyil. Lakin bu, ya "təsadüfən" və ya belə olması nəzərdə tutulduğu üçün müəyyən sorğu üçün "xüsusi indeks" ola bilər. Aşağıdakı sorğunu nəzərdən keçirin:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

Onun həyata keçirilməsi baxımından gözlənilməz heç nə yoxdur. Bu sadə bir xahişdir. İndeks üzrə diapazona baxın, cədvələ daxil olun - və bitirdiniz:

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

Yaxşı plan, elə deyilmi? Əgər buna həqiqətən ehtiyacımız varsa, yox: Aydındır ki, biz yaddaş itiririk və s. Bu sorğuya alternativ olaraq baxaq:

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

Onun planı belədir:

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

Sorğumuzun bütün ehtiyaclarını ödəyən bir indeksin olması sayəsində cədvələ girişi tamamilə aradan qaldıra bildik... əhatə edən indeks. Bu vacibdirmi? Və necə! Bu yanaşma sizə bəzi sorğuları böyüklük sırasına görə sürətləndirməyə imkan verir (yaxud bəzi dəyişikliklərdən sonra indeks artıq əhatə olunmayanda onları böyüklük sırası ilə yavaşlatın). Örtük indeksləri həmişə istifadə edilə bilməz. İndekslər üçün ödəniş etməlisiniz və onlardan çoxunu əlavə etməməlisiniz. Ancaq bu vəziyyətdə hər şey göz qabağındadır. Performansı qiymətləndirək:

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

Qeyd edək ki, aktyor cədvəlində yalnız 4 sütun var, ona görə də 1 və 2-ci ifadələr arasındakı performans fərqi o qədər də böyük deyil, lakin yenə də əhəmiyyətlidir. Onu da qeyd edəcəm ki, mən Oracle optimallaşdırıcı göstərişlərindən istifadə etmişəm ki, optimizator sorğu üçün bu və ya digər xüsusi indeks seçsin. Operator 3 yarışımızın şəksiz qalibidir. Onun performansı daha yaxşıdır və biz son dərəcə sadə sorğudan danışırıq. Yenə SELECT * yazanda verilənlər bazası üçün optimallaşdıra bilmədiyi lazımsız, lakin məcburi iş yaradırıq. O, əhatə indeksini seçməyəcək, çünki onun seçdiyi LAST_NAME indeksindən bir qədər yüksək yükə malikdir və digər şeylərlə yanaşı, məsələn, faydasız LAST_UPDATE sütununu əldə etmək üçün hələ də cədvələ daxil olmalıdır. Ancaq SELECT *-i nə qədər dərindən təhlil etsək, bir o qədər pis şeylər ortaya çıxır. Gəlin danışaq...

SQL çevrilmələri

Optimizatorlar SQL sorğularını çevirdikləri üçün çox yaxşı işləyirlər ( Sürixdə Voxxed Günlərindəki son çıxışımda bunun necə işlədiyini danışdım ). Məsələn, son dərəcə güclü "istisna QOŞULUN" transformasiyası var. Bütün bu cədvəllərə hər dəfə əl ilə qoşulmamaq üçün yaratmalı olduğumuz aşağıdakı köməkçi görünüşü nəzərdən keçirək:

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örünüş sadəcə olaraq MÜŞTƏRİ müştəri cədvəli ilə onların ünvanlarının hissələri üçün müxtəlif cədvəllər arasında bütün "...-to-bir" birləşmələrini edir. Sağ olun, normallaşma. Təsəvvür edin ki, bu mənzərə ilə bir az işlədikdən sonra biz buna öyrəşdik və əsas cədvəlləri unutduq. İndi biz aşağıdakı sorğunu yerinə yetiririk:

SELECT *
FROM v_customer

Nəticədə çox təsir edici bir plan əldə edirik:

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

Yaxşı, əlbəttə. Verilənlər bazası bütün bu birləşmələri və tam cədvəl skanlarını həyata keçirir, çünki biz ona bunu etməyi söylədik - bütün bu məlumatları gətirin. İndi bir daha təsəvvür edin ki, bizə həqiqətən lazım olan tək şey bu idi: Nə, ciddi, hə? İndi nə danışdığımı anlamağa başlayırsınız. Ancaq təsəvvür edin ki, keçmiş səhvlərdən bir şey öyrəndik və bu, daha optimal sorğunu yerinə yetirin:

SELECT first_name, last_name
FROM v_customer

İndi nə baş verdiyini yoxlayaq!

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

İcra baxımından yaxşılığa doğru köklü dəyişikliklər. Qoşulmalar aradan qaldırıldı, çünki optimallaşdırıcı indi onların yararsız olduğunu görə bilir və əgər bunu görə bilirsə (və siz * seçərək bu işi məcburi etməmisiniz), o, sadəcə olaraq bütün bu işləri görə bilməz. Bu vəziyyətdə niyə belədir? ADDRESS.ADDRESS_ID əsas açarının CUSTOMER.ADDRESS_ID xarici açarı sonuncunun dəqiq bir dəyərinə zəmanət verir, bu o deməkdir ki, JOIN əməliyyatı cərgələrin sayını artırmayan və ya azaltmayan "...-bir" birləşmə olacaq. . Biz heç bir sətir seçmədiyimizə və ya tələb etmədiyimizə görə, onları yükləməyin heç bir mənası yoxdur. JOIN-in silinməsi, yəqin ki, sorğunun nəticəsinə təsir etməyəcək. Verilənlər bazaları bunu hər zaman edir. Aşağıdakı sorğunu demək olar ki, istənilən verilənlər bazasında işlədə bilərsiniz:

-- 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 halda, aşağıdakı sorğunu yerinə yetirərkən olduğu kimi arifmetik istisnanın atılmasını gözləyə bilərsiniz:

SELECT 1 / 0 FROM dual

Amma bu baş vermir. Optimizator (və ya hətta təhlilçi) EXISTS predikatında (SEÇ ..) heç bir seçilmiş siyahı elementinin sorğunun nəticəsini dəyişdirməyəcəyinə əmin ola bilər, ona görə də onu yerinə yetirməyə ehtiyac yoxdur. Bunun kimi!

Bu arada...

ORM ilə bağlı ən zəhlətökən problemlərdən biri onların SELECT * sorğularını yazmağın çox asan olmasıdır. Əslində, məsələn, HQL / JPQL-də onlar ümumiyyətlə standart olaraq istifadə olunur. Biz SELECT müddəasını tamamilə buraxa bilərik, çünki biz bütün obyekti götürəcəyik, elə deyilmi? Misal üçün:

FROM v_customer

Məsələn, Hibernate ilə inkişaf üzrə ekspert və müdafiəçi Vlad Mihalcea , yoxlamadan sonra hər hansı dəyişikliyi saxlamaq istəmədiyinizə əmin olduğunuz zaman demək olar ki, həmişə [ixtisaslı] sorğulardan istifadə etməyi tövsiyə edir. ORM-lər obyekt qrafiklərinin davamlılığı probleminin həllini xeyli asanlaşdırır. Qeyd: Davamlılıq. Obyekt qrafiklərinin faktiki olaraq dəyişdirilməsi və dəyişikliklərin saxlanması vəzifələri ayrılmaz şəkildə bağlıdır. Amma bunu etməyəcəksənsə, onda mahiyyəti çıxarmaqla niyə narahat olursan? Niyə [zərif] sorğu yazmırsınız? Gəlin aydın olaq: ​​performans nöqteyi-nəzərindən, xüsusi istifadə vəziyyətinizə uyğunlaşdırılmış sorğu yazmaq hər hansı digər seçimdən daha yaxşıdır. Verilənlər dəstiniz kiçik olduğundan və bunun heç bir əhəmiyyəti olmadığından sizə əhəmiyyət verməyə bilər. Əla. Lakin nəhayət miqyaslılığa ehtiyacınız olduqda, obyekt qrafikinin imperativ keçidi əvəzinə sorğulardan istifadə etmək üçün tətbiqlərinizi yenidən dizayn etmək olduqca çətin olacaq. Və onsuz bir işiniz olacaq.

Bir şeyin olub olmadığını öyrənmək üçün sətirləri saymaq

Resursların ən pis tullantılarından biri verilənlər bazasında nəyinsə olub-olmadığını görmək üçün COUNT(*) sorğu işlətməkdir. Məsələn, müəyyən bir istifadəçinin ümumiyyətlə sifarişlərinin olub olmadığını öyrənməliyik. Və sorğunu yerinə yetiririk:

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

İbtidai. COUNT = 0 olarsa, heç bir sifariş yoxdur. Əks halda, bəli. Yəqin ki, ORDERS.USER_ID sütununda indeksimiz olduğu üçün performans o qədər də pis olmayacaq. Ancaq sizcə, yuxarıdakı sorğunun performansı aşağıdakı seçimlə müqayisə ediləcək:

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

Həqiqi mövcudluq predikatının birincini tapan kimi əlavə sətirləri axtarmağı dayandıracağını anlamaq üçün bir raket alimi lazım deyil . Beləliklə, nəticə "sifarişsiz" olarsa, sürət müqayisə edilə bilər. Bununla belə, nəticə "bəli, sifarişlər var" olarsa, dəqiq miqdarın hesablanmasına ehtiyac olmadığı halda, cavab daha sürətli alınacaq . Axı bizi dəqiq rəqəm maraqlandırmır. Bununla belə, verilənlər bazasına onu hesablamağı dedik ( gərəksiz iş ) və verilənlər bazası bilmir ki, biz 1-dən çox olan bütün nəticələri görməzlikdən gəlirik ( tələb olunan iş ). Eyni nəticələrə nail olmaq üçün JPA tərəfindən dəstəklənən kolleksiyada list.size() funksiyasını çağırsaq, əlbəttə ki, daha pis olardı. Bu barədə əvvəllər bloqumda yazmışdım və hər iki variantın müqayisəli testini aparmışdım...

Nəticə

Bu məqalə açıq-aşkar ifadə edir. Verilənlər bazasını lazımsız, lakin tələb olunan işləri görməyə məcbur etməyin . Bu , lazımsızdır , çünki tələbləri nəzərə alaraq, bilirsiniz ki, müəyyən bir işin görülməsinə ehtiyac yoxdur. Bununla belə, verilənlər bazasına bunu etməyi əmr edirsiniz. Bu tələb olunur, çünki verilənlər bazası üçün bu işin lazımsız olmasını təmin etmək üçün heç bir yol yoxdur . Bu məlumat yalnız müştəri üçün mövcuddur və server üçün mövcud deyil. Beləliklə, verilənlər bazası onu yerinə yetirməlidir. Məqalədə SELECT *-ə diqqət yetirildi, çünki bu, baxmaq üçün çox əlverişli bir obyektdir. Lakin bu, təkcə verilənlər bazasına aid deyil. Bu, müştərinin serverə lazımsız, lakin tələb olunan işi görməyi əmr etdiyi bütün paylanmış alqoritmlərə aiddir . Orta hesabla AngularJS tətbiqinizdə neçə N+1 tapşırığı var ki, burada UI B xidmətinə bir neçə dəfə zəng edərək A xidmətinin nəticəsi vasitəsilə dövr edir, B-yə bütün zəngləri bir zəngə yığmaq əvəzinə? Bu çox yaygın bir fenomendir. Həll həmişə eynidir. Əmrlərinizi yerinə yetirən quruma nə qədər çox məlumat versəniz, o (nəzəri cəhətdən) həmin əmrləri bir o qədər tez yerinə yetirir. Optimal sorğuları yazın. Həmişə. Bütün sisteminiz bunun üçün sizə təşəkkür edəcəkdir. Orijinal məqalə