مشكلات أداء SQL الناشئة عن "العمل غير الضروري ولكنه مطلوب"

مستوى المعرفة المطلوب لفهم المقالة: فهم عام لقواعد البيانات وSQL، وبعض الخبرة العملية في نظام إدارة قواعد البيانات (DBMS). ربما يكون أهم شيء يمكنك تعلمه لكتابة استعلامات SQL فعالة هو الفهرسة. ومع ذلك، في المرتبة الثانية، وهي قريبة جدًا، هي معرفة أن العديد من عملاء SQL يطلبون من قاعدة البيانات القيام بالكثير من "الأعمال غير الضرورية ولكنها ضرورية" . كرر من بعدي: ما هو "العمل غير الضروري ولكن الإلزامي"؟ وكما يخبرنا الكابتن أوبفيوس، فهي:

غير ضروري

دع تطبيق عميلنا يحتاج إلى البيانات التالية: لا شيء غير طبيعي. نحن نعمل مع قاعدة بيانات الأفلام (مثل قاعدة بيانات الساقية ) ونريد عرض عنوان وتصنيف جميع الأفلام للمستخدمين. الاستعلام التالي يمكن أن يعطي النتيجة التي نحتاجها:

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 في زيوريخ ). على سبيل المثال، هناك تحويل "استثناء 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

لكن هذا لا يحدث. يمكن للمحسن (أو حتى المحلل اللغوي) التأكد من عدم قيام أي عناصر قائمة محددة في المسند الموجود (SELECT ..) بتغيير نتيجة الاستعلام، لذلك ليست هناك حاجة لتنفيذه. مثله!

في أثناء...

إحدى أكثر المشكلات المزعجة في ORMs هي سهولة كتابة استعلامات SELECT *. في الواقع، على سبيل المثال، في HQL/JPQL يتم استخدامها بشكل افتراضي بشكل عام. يمكننا حذف جملة SELECT تمامًا، لأننا سنقوم باسترداد الكيان بأكمله، أليس كذلك؟ على سبيل المثال:

FROM v_customer

على سبيل المثال، يوصي فلاد ميهالسيا، الخبير والمدافع عن التطوير باستخدام Hibernate ، باستخدام الاستعلامات [المؤهلة] دائمًا تقريبًا عندما تكون متأكدًا من أنك لا تريد حفظ أي تغييرات بعد الخروج. تسهل ORMs بشكل كبير حل مشكلة استمرار الرسوم البيانية للكائنات. ملحوظة: الثبات. ترتبط مهام التعديل الفعلي للرسوم البيانية للكائنات وحفظ التغييرات ارتباطًا وثيقًا. ولكن إذا كنت لن تفعل ذلك، فلماذا تهتم باستخراج الجوهر؟ لماذا لا تكتب طلبًا [مكررًا]؟ لنكن واضحين: من وجهة نظر الأداء، من الواضح أن كتابة استعلام مصمم خصيصًا لحالة الاستخدام المحددة الخاصة بك أفضل من أي خيار آخر. قد لا تهتم نظرًا لأن مجموعة البيانات الخاصة بك صغيرة ولا يهم. عظيم. ولكن عندما تحتاج في النهاية إلى قابلية التوسع، فإن إعادة تصميم تطبيقاتك لاستخدام الاستعلامات بدلاً من الاجتياز الحتمي للرسم البياني للكيان سيكون أمرًا صعبًا للغاية. وسيكون لديك ما تفعله بدونه.

خطوط العد لمعرفة ما إذا كان هناك شيء موجود

أحد أسوأ إهدار الموارد هو تشغيل استعلامات 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 في مكالمة واحدة؟ هذا أمر شائع جدًا. الحل هو نفسه دائما. كلما زادت المعلومات التي تقدمها للكيان الذي ينفذ أوامرك، كلما كان تنفيذ تلك الأوامر أسرع (نظريًا). كتابة الاستعلامات الأمثل. دائماً. سوف يشكرك نظامك بأكمله على هذا. المقالة الأصلية