مشکلات عملکرد SQL ناشی از "کار غیر ضروری اما ضروری"

سطح دانش مورد نیاز برای درک مقاله: درک کلی از پایگاه های داده و SQL، تجربه عملی با DBMS. احتمالاً مهمترین چیزی که می توانید برای نوشتن پرس و جوهای SQL موثر یاد بگیرید، نمایه سازی است. با این حال، در وهله دوم، بسیار نزدیک به عقب، این دانش است که بسیاری از مشتریان SQL به پایگاه داده نیاز دارند تا "کارهای غیر ضروری اما ضروری" زیادی را انجام دهد . بعد از من تکرار کن: «کار غیر ضروری اما واجب» چیست؟ همانطور که کاپیتان آشکار به ما می گوید، او:

غیر ضروری

اجازه دهید برنامه مشتری ما به داده های زیر نیاز داشته باشد: هیچ چیز غیرعادی نیست ما با یک پایگاه داده فیلم (مانند پایگاه داده Sakila ) کار می کنیم و می خواهیم عنوان و رتبه بندی همه فیلم ها را به کاربران نمایش دهیم. پرس و جو زیر می تواند نتیجه مورد نیاز ما را به دست دهد:

SELECT title, rating
FROM film

با این حال، برنامه ما (یا ORM ما) در عوض این کوئری را اجرا می کند:

SELECT *
FROM film

در نتیجه چه چیزی بدست می آوریم؟ حدس بزن. ما اطلاعات بی فایده زیادی دریافت می کنیم: در سمت راست حتی می‌توانید برخی از JSON پیچیده در حال بارگذاری را مشاهده کنید:

• از دیسک
• به کش
• توسط سیم
• به یاد مشتری
• و در نهایت دور انداخته می شود [به عنوان غیر ضروری]

بله، ما بیشتر این اطلاعات را دور می اندازیم. تمام اقدامات انجام شده برای استخراج این اطلاعات کاملاً بی فایده بود. آیا حقیقت دارد؟ آیا حقیقت دارد.

اجباری

و اکنون - بدترین بخش. اگرچه بهینه سازها اکنون می توانند کارهای زیادی انجام دهند، اما این اقدامات برای پایگاه داده اجباری است. پایگاه داده راهی ندارد که بداند برنامه مشتری به 95٪ از این داده ها نیاز ندارد. و این فقط ساده ترین مثال است. تصور کنید چندین جدول را به هم وصل کرده اید... خب، شما می گویید چه، اما پایگاه داده ها سریع هستند؟ اجازه دهید شما را در مورد چیزهایی که احتمالاً به آنها فکر نکرده اید روشن کنم. البته زمان اجرای یک درخواست فردی واقعاً روی چیزی تأثیر نمی گذارد. خوب، یک و نیم برابر کندتر بود، اما ما از آن عبور خواهیم کرد، درست است؟ برای آسودگی؟ گاهی اوقات این درست است. اما اگر همیشه عملکرد را فدای راحتی کنید ، این چیزهای کوچک شروع به جمع شدن خواهند کرد. ما دیگر در مورد عملکرد (سرعت اجرای درخواست های فردی) صحبت نخواهیم کرد، بلکه در مورد توان عملیاتی (زمان پاسخگویی سیستم) صحبت خواهیم کرد و سپس مشکلات جدی شروع می شود که حل آنها چندان آسان نیست. آن وقت است که مقیاس پذیری را از دست می دهید. بیایید نگاهی به برنامه های اجرایی، در این مورد، Oracle DBMS بیندازیم:

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

در مقایسه با:

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

با اجرای یک کوئری SELECT * به جای عنوان SELECT، رتبه بندی 8 برابر حافظه بیشتری در پایگاه داده استفاده می کند. هیچ چیز غیر منتظره ای نیست، درست است؟ می دانستیم که این اتفاق خواهد افتاد. اما هنوز برای بسیاری از درخواست‌هایمان که به سادگی به همه این داده‌ها نیازی نداریم، با این موافق هستیم. ما کارهای غیر ضروری اما اجباری را برای پایگاه داده ایجاد می کنیم که مدام انباشته و انباشته می شود. ما 8 برابر بیشتر از مقدار مورد نیاز حافظه استفاده می کنیم (البته ضریب تغییر خواهد کرد). در ضمن، در تمام مراحل دیگر (ورودی/خروجی دیسک، انتقال داده از طریق شبکه، مصرف حافظه توسط کلاینت) مشکلات دقیقاً یکسان است، اما من از آنها می گذرم و به جای آن به ...

استفاده از شاخص ها

امروزه اکثر پایگاه‌های اطلاعاتی مفهوم پوشش شاخص‌ها را درک کرده‌اند . شاخص پوششی خود نوع خاصی از شاخص نیست. اما ممکن است یک "شاخص ویژه" برای یک پرس و جو خاص باشد، یا "به طور تصادفی" یا به این دلیل که چنین در نظر گرفته شده است. پرس و جو زیر را در نظر بگیرید:

SELECT *
FROM actor
WHERE last_name LIKE 'A%'

هیچ چیز غیر منتظره ای در اجرای آن وجود ندارد. این یک درخواست ساده است. محدوده را بر اساس نمایه مشاهده کنید، به جدول دسترسی پیدا کنید - و کارتان تمام شد:

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

طرح خوبی است، اینطور نیست؟ خوب، اگر واقعاً به این نیاز داشتیم، پس نه: بدیهی است که ما حافظه را تلف می کنیم و غیره. بیایید این پرس و جو را به عنوان جایگزین در نظر بگیریم:

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

نقشه او این است:

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

به لطف وجود نمایه ای که تمام نیازهای پرس و جوی ما را برآورده می کند، توانستیم به طور کامل دسترسی به جدول را حذف کنیم... یک نمایه پوششی. آیا مهم است؟ و چطور! این رویکرد به شما اجازه می‌دهد تا سرعت برخی از پرس‌و‌جوها را با یک مرتبه بزرگی افزایش دهید (یا زمانی که شاخص پس از برخی تغییرات دیگر پوشش داده نمی‌شود، سرعت آن‌ها را با یک مرتبه قدر کاهش دهید). همیشه نمی توان از شاخص های پوششی استفاده کرد. شما باید برای ایندکس ها هزینه کنید و نباید تعداد زیادی از آنها را اضافه کنید. اما در این مورد همه چیز واضح است. بیایید عملکرد را ارزیابی کنیم:

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

توجه داشته باشید که جدول بازیگر تنها دارای 4 ستون است، بنابراین تفاوت عملکرد بین عبارت 1 و 2 آنقدر بزرگ نیست، اما همچنان قابل توجه است. همچنین اشاره خواهم کرد که از نکات بهینه ساز Oracle استفاده کردم تا بهینه ساز یک یا آن شاخص خاص را برای پرس و جو انتخاب کند. اپراتور 3 برنده بلامنازع مسابقه ما است. عملکرد آن بسیار بهتر است و ما در مورد یک پرس و جو بسیار ساده صحبت می کنیم. باز هم، وقتی SELECT * را می نویسیم، کارهای غیر ضروری اما اجباری برای پایگاه داده ایجاد می کنیم که نمی تواند بهینه سازی کند. او شاخص پوشش را انتخاب نخواهد کرد زیرا سربار آن کمی بالاتر از شاخص LAST_NAME است که انتخاب کرده است، و، از جمله، برای مثال، برای بازیابی یک ستون بی فایده LAST_UPDATE همچنان باید به جدول دسترسی داشته باشد. اما هرچه SELECT * را عمیق‌تر تجزیه و تحلیل کنیم، اوضاع بدتر می‌شود. اجازه دهید در موردش صحبت کنیم...

تبدیل های SQL

بهینه سازها بسیار خوب عمل می کنند زیرا پرس و جوهای SQL را تغییر می دهند ( من در سخنرانی اخیرم در Voxxed Days در زوریخ در مورد نحوه کارکرد آن صحبت کردم ). به عنوان مثال، یک تبدیل بسیار قدرتمند "Exception JOIN" وجود دارد. نمای کمکی زیر را در نظر بگیرید که باید ایجاد می‌کردیم تا از پیوستن دستی همه این جداول هر بار جلوگیری کنیم:

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)

این نمای به سادگی تمام اتصالات "...-به-یک" را بین جدول مشتری CUSTOMER و جداول مختلف برای بخش هایی از آدرس آنها انجام می دهد. با تشکر از شما، عادی سازی. تصور کنید که پس از کمی کار با این نما، به آن عادت کرده و جداول زیر آن را فراموش کرده ایم. و اکنون کوئری زیر را اجرا می کنیم:

SELECT *
FROM v_customer

در نتیجه، ما یک طرح بسیار چشمگیر دریافت می کنیم:

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

خوب البته. پایگاه داده همه این اتصالات و اسکن کامل جدول را انجام می دهد زیرا این همان کاری است که ما به آن گفته ایم - واکشی همه این داده ها. حالا، دوباره، تصور کنید که تنها چیزی که واقعاً نیاز داشتیم این بود: چی، جدی، درسته؟ حالا شما دارید متوجه می شوید که من در مورد چه چیزی صحبت می کنم. اما تصور کنید که ما از اشتباهات گذشته چیزی یاد گرفتیم و این کوئری بهینه تر را اجرا کنید:

SELECT first_name, last_name
FROM v_customer

حالا بیایید بررسی کنیم چه اتفاقی افتاده است!

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

تغییرات شدید برای بهتر شدن از نظر اجرا. اتصال‌ها حذف شده‌اند زیرا بهینه‌ساز اکنون می‌تواند بی‌فایده بودن آنها را ببیند ، و اگر بتواند آن را ببیند (و شما آن کار را با انتخاب * اجباری نکرده‌اید)، به سادگی نمی‌تواند همه آن کارها را انجام دهد. چرا در این مورد چنین است؟ کلید خارجی CUSTOMER.ADDRESS_ID به کلید اصلی ADDRESS.ADDRESS_ID دقیقاً یک مقدار از دومی را تضمین می کند، به این معنی که عملیات JOIN یک پیوستن "...-به-یک" خواهد بود که تعداد ردیف ها را افزایش یا کاهش نمی دهد. . و از آنجایی که ما اصلاً هیچ ردیفی را انتخاب یا درخواست نمی کنیم، بارگیری آنها اصلاً فایده ای ندارد. حذف JOIN احتمالاً هیچ تأثیری بر نتیجه پرس و جو نخواهد داشت. پایگاه های داده همیشه این کار را انجام می دهند. تقریباً در هر پایگاه داده ای می توانید پرس و جو زیر را اجرا کنید:

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

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

در این مورد، ممکن است انتظار داشته باشید که یک استثنای حسابی مانند هنگام اجرای پرس و جو زیر ایجاد شود:

SELECT 1 / 0 FROM dual

اما این اتفاق نمی افتد. بهینه ساز (یا حتی تجزیه کننده) می تواند اطمینان حاصل کند که هیچ عنصر لیست انتخابی در گزاره EXISTS (SELECT ..) نتیجه پرس و جو را تغییر نمی دهد، بنابراین نیازی به اجرای آن نیست. مثل این!

در همین حال...

یکی از آزاردهنده ترین مشکلات ORM ها این است که نوشتن پرس و جوهای SELECT * بسیار آسان است. در واقع، به عنوان مثال، در HQL / JPQL آنها به طور کلی به طور پیش فرض استفاده می شوند. ما می توانیم بند SELECT را به طور کلی حذف کنیم، زیرا می خواهیم کل موجودیت را بازیابی کنیم، درست است؟ مثلا:

FROM v_customer

به عنوان مثال، Vlad Mihalcea، متخصص و مدافع توسعه با Hibernate ، توصیه می‌کند که تقریباً همیشه وقتی مطمئن هستید که نمی‌خواهید هیچ تغییری را پس از تسویه‌حساب ذخیره کنید، از جستارهای [واجد شرایط] استفاده کنید. ORM ها تا حد زیادی حل مشکل تداوم گراف های شی را تسهیل می کنند. توجه: پایداری وظایف اصلاح واقعی نمودارهای شی و ذخیره تغییرات به طور جدایی ناپذیری به هم مرتبط هستند. اما اگر قرار نیست این کار را انجام دهید، پس چرا زحمت استخراج ذات را بدهید؟ چرا یک درخواست [تصفیه شده] نمی نویسید؟ بیایید واضح بگوییم: از نقطه نظر عملکرد، نوشتن یک پرس و جو که به طور خاص برای مورد استفاده خاص شما طراحی شده باشد، بدیهی است که بهتر از هر گزینه دیگری است. ممکن است اهمیتی ندهید زیرا مجموعه داده های شما کوچک است و مهم نیست. عالی. اما زمانی که در نهایت به مقیاس‌پذیری نیاز دارید، طراحی مجدد برنامه‌هایتان برای استفاده از پرس‌و‌جوها به جای پیمایش ضروری نمودار موجودیت، بسیار چالش برانگیز خواهد بود. و بدون آن کاری برای انجام دادن خواهید داشت.

برای اینکه بفهمید چیزی وجود دارد یا خیر، خطوط را می‌شمارید

یکی از بدترین اتلاف منابع، اجرای COUNT(*) پرس و جو فقط برای دیدن اینکه آیا چیزی در پایگاه داده وجود دارد یا خیر. به عنوان مثال، ما باید دریابیم که آیا یک کاربر خاص اصلاً سفارش دارد یا خیر. و ما درخواست را اجرا می کنیم:

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

ابتدایی. اگر COUNT = 0 باشد، هیچ سفارشی وجود ندارد. در غیر این صورت، بله. عملکرد آنچنان بد نخواهد بود زیرا احتمالاً یک نمایه در ستون ORDERS.USER_ID داریم. اما به نظر شما عملکرد کوئری فوق با گزینه زیر چگونه خواهد بود:

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

نیازی به یک دانشمند موشکی نیست تا بفهمد که یک محمول وجود واقعی به محض یافتن رشته اول، جستجوی رشته های اضافی را متوقف می کند . بنابراین اگر نتیجه "بدون سفارش" باشد، سرعت قابل مقایسه خواهد بود. با این حال، اگر نتیجه "بله، سفارشات وجود دارد" باشد، در صورتی که نیازی به شمارش مقدار دقیق نباشد، پاسخ بسیار سریعتر دریافت می شود. از این گذشته ، ما به تعداد دقیق علاقه نداریم. با این حال، ما به پایگاه داده گفتیم که آن را محاسبه کند ( کار غیر ضروری ) و پایگاه داده نمی داند که ما همه نتایج بزرگتر از 1 ( کار مورد نیاز ) را نادیده می گیریم. البته، بسیار بدتر خواهد بود اگر ما ()list.size را در مجموعه ای با پشتیبانی JPA فراخوانی کنیم تا به نتایج مشابهی برسیم. قبلاً در مورد این موضوع در وبلاگم نوشتم و آزمایش مقایسه ای هر دو گزینه را در ...

نتیجه

این مقاله موارد بدیهی را بیان می کند. پایگاه داده را مجبور به انجام کارهای غیر ضروری اما ضروری نکنید . این غیرضروری است زیرا با توجه به الزامات، می دانید که برخی از کارها نیازی به انجام دادن ندارند. با این حال، شما به پایگاه داده می گویید که این کار را انجام دهد. لازم است زیرا هیچ راهی برای پایگاه داده برای اطمینان از غیر ضروری بودن این کار وجود ندارد . این اطلاعات فقط در اختیار مشتری است و برای سرور در دسترس نیست. بنابراین پایگاه داده باید آن را اجرا کند. این مقاله روی SELECT * تمرکز داشت، عمدتاً به این دلیل که یک شی بسیار راحت برای نگاه کردن است. اما این نه تنها در مورد پایگاه داده ها صدق می کند. این برای همه الگوریتم‌های توزیع‌شده که در آنها مشتری به سرور می‌گوید کارهای غیرضروری اما ضروری را انجام دهد، اعمال می‌شود . چند کار N+1 در برنامه متوسط ​​AngularJS شما وجود دارد که در آن رابط کاربری از طریق نتیجه سرویس A حلقه می زند و چندین بار سرویس B را فراخوانی می کند، نه اینکه تمام تماس های B را در یک تماس جمع کند؟ این یک پدیده بسیار رایج است. راه حل همیشه یکسان است. هرچه اطلاعات بیشتری به نهادی که دستورات خود را اجرا می کند ارائه دهید، سریعتر (از لحاظ نظری) آن دستورات را اجرا می کند. پرس و جوهای بهینه را بنویسید همیشه. کل سیستم شما از این بابت از شما تشکر خواهد کرد. مقاله اصلی