تحسينات SQL رائعة لا تعتمد على نموذج التكلفة. الجزء 1

خمسة تحسينات بسيطة يمكن تنفيذها بناءً فقط على البيانات الوصفية (أي القيود) والاستعلام نفسه. نحن نقدم لك تعديلًا لمقالة Lukas Eder، المصممة لأولئك الذين لديهم فهم عام لقواعد البيانات وSQL، بالإضافة إلى بعض الخبرة العملية في نظام إدارة قواعد البيانات (DBMS) . يعد تحسين التكلفة في الواقع طريقة قياسية لتحسين استعلامات SQL في قواعد البيانات الحديثة. ولهذا السبب، من الصعب جدًا كتابة خوارزمية معقدة يدويًا بلغة 3GL (لغات برمجة الجيل الثالث) والتي يتجاوز أدائها خطة التنفيذ المحسوبة ديناميكيًا والتي تم إنشاؤها بواسطة مُحسِّن حديث. لن نناقش اليوم تحسين التكلفة، أي التحسين بناءً على نموذج التكلفة لقاعدة البيانات. سننظر في تحسينات أبسط بكثير. تلك التي يمكن تنفيذها بناءً على البيانات الوصفية (أي القيود) والطلب نفسه فقط. عادة ما يكون تنفيذها لقاعدة البيانات ليس ذو حدين لنيوتن، لأنه في هذه الحالة، أي تحسين سيؤدي إلى خطة تنفيذ أفضل، بغض النظر عن وجود الفهارس وحجم البيانات وانحراف توزيع البيانات. "ليست ذات حدين لنيوتن" لا تعني مدى سهولة تنفيذ التحسين، ولكن ما إذا كان ينبغي القيام به. تعمل هذه التحسينات على التخلص من العمل الإضافي غير الضروري [لقاعدة البيانات] ( على عكس العمل غير الضروري والمطلوب، والذي كتبت عنه بالفعل ).

ما هي هذه التحسينات المستخدمة ل؟

يتم استخدام معظمها من أجل:

• إصلاحات الأخطاء في الاستعلامات؛
• السماح بإعادة استخدام طرق العرض دون أن تقوم قاعدة البيانات بتنفيذ منطق العرض فعليًا.

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

قواعد البيانات المستخدمة

في هذه المقالة سنقوم بمقارنة 10 تحسينات لـ SQL في أنظمة إدارة قواعد البيانات الخمسة الأكثر استخدامًا ( وفقًا لتصنيفات قاعدة البيانات ):

• أوراكل 12.2؛
• ماي إس كيو إل 8.0.2؛
• خادم SQL 2014؛
• بوستجري إس كيو إل 9.6؛
• DB2 لوو 10.5.

تصنيف آخر يردده تقريبًا. كالعادة، في هذه المقالة سأقوم بالاستعلام عن قاعدة بيانات الساقية . فيما يلي قائمة بهذه الأنواع العشرة من التحسينات:

1. إغلاق متعد؛
2. المسندات المستحيلة واستدعاءات الجدول غير الضرورية؛
3. إزالة JOIN؛
4. القضاء على المسندات "التي لا معنى لها"؛
5. التوقعات في الاستعلامات الفرعية الموجودة؛
6. دمج المسندات.
7. مجموعات فارغة يمكن إثباتها؛
8. التحقق من القيود؛
9. اتصالات انعكاسية غير ضرورية؛
10. المسندات الضغط لأسفل

اليوم سنناقش ص. 1-3، في الجزء الثاني - 4 و 5، وفي الجزء 3 - 6-10.

1. الإغلاق المتعدي

لنبدأ بشيء أبسط: الإغلاق المتعدي . وهذا مفهوم تافه ينطبق على العديد من العمليات الرياضية، مثل عامل المساواة. ويمكن صياغتها في هذه الحالة على النحو التالي: إذا كان A = B و B = C، فإن A = C.

ليس صعبا، أليس كذلك؟ ولكن هذا له بعض الآثار المثيرة للاهتمام بالنسبة لمحسنات SQL. لنلقي نظرة على مثال. لنستخرج جميع الأفلام ذات ACTOR_ID = 1:

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE a.actor_id = 1;

والنتيجة هي على النحو التالي:

FIRST_NAME      LAST_NAME  FILM_ID
PENELOPE        GUINESS    1
PENELOPE        GUINESS    23
PENELOPE        GUINESS    25
PENELOPE        GUINESS    106
PENELOPE        GUINESS    140
PENELOPE        GUINESS    166
...

دعونا الآن نلقي نظرة على خطة تنفيذ هذا الاستعلام في حالة Oracle DBMS:

--------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |       |
|   1 |  NESTED LOOPS                |               |    19 |
|   2 |   TABLE ACCESS BY INDEX ROWID| ACTOR         |     1 |
|*  3 |    INDEX UNIQUE SCAN         | PK_ACTOR      |     1 |
|*  4 |   INDEX RANGE SCAN           | PK_FILM_ACTOR |    19 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."ACTOR_ID"=1)
   4 - access("FA"."ACTOR_ID"=1)

القسم الخاص بالمسندات مثير للاهتمام بشكل خاص هنا. المسند ACTOR_ID = 1، بسبب الإغلاق المتعدي، ينطبق على كل من جدول ACTOR وجدول FILM_ACTOR. لو:

• A.ACTOR_ID = 1 (из предиката WHERE) и…
• A.ACTOR_ID = FA.ACTOR_ID (из предиката ON)
  То:
• FA.ACTOR_ID = 1

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

SELECT first_name, last_name, film_id
FROM actor a
JOIN film_actor fa ON a.actor_id = fa.actor_id
WHERE first_name = 'PENELOPE'
AND last_name = 'GUINESS'

خطته:

----------------------------------------------------------------------------
| Id  | Operation                            | Name                | Rows  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                     |       |
|   1 |  NESTED LOOPS                        |                     |     2 |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR               |     1 |
|*  3 |    INDEX RANGE SCAN                  | IDX_ACTOR_LAST_NAME |     3 |
|*  4 |   INDEX RANGE SCAN                   | PK_FILM_ACTOR       |    27 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"."FIRST_NAME"='PENELOPE')
   3 - access("A"."LAST_NAME"='GUINESS')
   4 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")

كما ترون، تم المبالغة في تقدير عدد الصفوف في جدول FILM_ACTOR، في حين تم التقليل من عدد الصفوف المتداخلة. فيما يلي بعض القيم المثيرة للاهتمام:

SELECT count(*) FROM film_actor WHERE actor_id = 1;
SELECT avg(c) FROM (
  SELECT count(*) c FROM film_actor GROUP BY actor_id
);

نتيجة:

19
27.315

ومن هنا تأتي التقديرات. إذا عرفت قاعدة البيانات أننا نتحدث عن ACTOR_ID = 1، فيمكنها جمع إحصائيات حول عدد الأفلام لهذا الممثل بالذات . إذا لم يحدث ذلك (نظرًا لأن آلية جمع الإحصائيات القياسية لا تربط FIRST_NAME/LAST_NAME بـ ACTOR_ID)، فسنحصل على متوسط ​​عدد الأفلام لجميع الممثلين . خطأ بسيط وغير مهم في هذه الحالة بالذات، ولكن في الاستعلام المعقد يمكن أن ينتشر بشكل أكبر ويتراكم ويؤدي إلى الاستعلام (في أعلى الخطة) إلى اختيار JOIN غير صحيح. لذلك، كلما استطعت، قم بتصميم الصلات والمسندات البسيطة للاستفادة من الإغلاق المتعدي. ما هي قواعد البيانات الأخرى التي تدعم هذه الميزة؟

DB2

نعم!

Explain Plan
-----------------------------------------------------------
ID | Operation              |                 Rows | Cost
 1 | RETURN                 |                      |   13
 2 |  NLJOIN                |              27 of 1 |   13
 3 |   FETCH ACTOR          |     1 of 1 (100.00%) |    6
 4 |    IXSCAN PK_ACTOR     |   1 of 200 (   .50%) |    0
 5 |   IXSCAN PK_FILM_ACTOR | 27 of 5462 (   .49%) |    6
Predicate Information
 4 - START (Q2.ACTOR_ID = 1)
      STOP (Q2.ACTOR_ID = 1)
 5 - START (1 = Q1.ACTOR_ID)
      STOP (1 = Q1.ACTOR_ID)

بالمناسبة، إذا كنت تحب خطط التنفيذ الرائعة مثل هذه، فاطلع على نص Markus Winand .

ماي إس كيو إل

لسوء الحظ، خطط تنفيذ MySQL ليست مناسبة تمامًا لهذا النوع من التحليل. المسند نفسه مفقود من معلومات الإخراج:

ID  SELECT TYPE  TABLE  TYPE   REF    ROWS
------------------------------------------
1   SIMPLE       a      const  const  1
1   SIMPLE       fa     ref    const  19

لكن حقيقة تحديد const مرتين في عمود REF توضح أن كلا الجدولين يبحثان عن قيمة ثابتة. وفي الوقت نفسه، تبدو خطة الاستعلام مع FIRST_NAME/LAST_NAME كما يلي:

ID  SELECT TYPE  TABLE  TYPE   REF         ROWS
-----------------------------------------------
1   SIMPLE       a      ref    const       3
1   SIMPLE       fa     ref    a.actor_id  27

وكما ترون، يشير REF الآن إلى العمود من دالة JOIN. درجة الأصل هي نفسها تقريبًا كما في Oracle. لذا نعم، يدعم MySQL عمليات الإغلاق المتعدية أيضًا.

PostgreSQL

نعم!

QUERY PLAN
------------------------------------------------------------------------------------
Nested Loop  (cost=4.49..40.24 rows=27 width=15)
  ->  Seq Scan on actor a  (cost=0.00..4.50 rows=1 width=17)
        Filter: (actor_id = 1)
  ->  Bitmap Heap Scan on film_actor fa  (cost=4.49..35.47 rows=27 width=4)
        Recheck Cond: (actor_id = 1)
        ->  Bitmap Index Scan on film_actor_pkey  (cost=0.00..4.48 rows=27 width=0)
              Index Cond: (actor_id = 1)

خادم قاعدة البيانات

نعم!

|--Nested Loops(Inner Join)
     |--Nested Loops(Inner Join)
     |    |--Index Seek (SEEK:([a].[actor_id]=(1)))
     |    |--RID Lookup
     |--Index Seek (SEEK:([fa].[actor_id]=(1)))

ملخص

جميع قواعد البيانات لدينا تدعم الإغلاق المتعدي. ومع ذلك، انتظر رقم 6 في الجزء التالي من المقال. هناك حالات معقدة من الإغلاق المتعدي لا تستطيع جميع قواعد البيانات التعامل معها.

2. المسندات المستحيلة واستدعاءات الجدول غير الضرورية

هذا هو التحسين غبي تماما، ولكن لماذا لا؟ إذا كتب المستخدمون مسندات مستحيلة، فلماذا تهتم بتنفيذها؟ وهنا بعض الأمثلة:

-- "Очевидный"
SELECT * FROM actor WHERE 1 = 0
-- "Хитрый"
SELECT * FROM actor WHERE NULL = NULL

من الواضح أن الاستعلام الأول لن يُرجع أية نتائج أبدًا، ولكن العبارة نفسها تنطبق على الاستعلام الثاني. بعد كل شيء، على الرغم من أن NULL IS NULL دائمًا ما يكون صحيحًا، فإن نتيجة الحساب NULL = NULL هي NULL، والتي، وفقًا للمنطق ثلاثي القيم ، تعادل FALSE. يعد هذا أمرًا لا يحتاج إلى شرح، لذا دعنا ننتقل مباشرة إلى معرفة قواعد البيانات التي تقوم بهذا التحسين.

DB2

نعم!

Explain Plan
-----------------------------------
ID | Operation      |   Rows | Cost
 1 | RETURN         |        |    0
 2 |  TBSCAN GENROW | 0 of 0 |    0

كما ترون، تم استبعاد الوصول إلى جدول ACTOR تمامًا من الخطة. فهو يحتوي فقط على عملية GENROW، التي تنشئ صفر صفوف. ممتاز.

ماي إس كيو إل

نعم!

ID  SELECT TYPE  TABLE   EXTRAS
-----------------------------------------
1   SIMPLE         Impossible WHERE

هذه المرة، كانت MySQL لطيفة بما يكفي لإبلاغنا عن عبارة WHERE المستحيلة. شكرًا لك! وهذا يجعل التحليل أسهل بكثير، خاصة بالمقارنة مع قواعد البيانات الأخرى.

وحي

نعم!

---------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |
---------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |      0 |
|*  1 |  FILTER            |       |      1 |        |      0 |
|   2 |   TABLE ACCESS FULL| ACTOR |      0 |    200 |      0 |
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

نرى أن الخطة لا تزال تشير إلى الوصول إلى جدول ACTOR، ولا يزال العدد المتوقع للصفوف 200، ولكن هناك أيضًا عملية تصفية (FILTER) ذات المعرف = 1، حيث لن يكون هناك TRUE أبدًا. نظرًا لعدم إعجاب Oracle بنوع بيانات SQL Boolean القياسي ، تعرض Oracle NULL IS NOT NULL في الخطة، بدلاً من FALSE فقط. أوه حسنًا... لكن على محمل الجد، انتبه لهذا المسند. لقد أتيحت لي الفرصة لتصحيح أخطاء خطط التنفيذ باستخدام أشجار فرعية مكونة من 1000 سطر وقيم تكلفة عالية للغاية، فقط لأكتشف بعد حقيقة أن الشجرة الفرعية بأكملها تم "قطعها" بواسطة مرشح NULL IS NOT NULL. أقول لك إنه محبط بعض الشيء.

PostgreSQL

نعم!

QUERY PLAN
-------------------------------------------
Result  (cost=0.00..0.00 rows=0 width=228)
  One-Time Filter: false

بالفعل أفضل. لا توجد مكالمات جدول ATOR مزعجة ومسند FALSE صغير أنيق.

خادم قاعدة البيانات؟

نعم!

|--Constant Scan

يطلق SQL Server على هذا اسم " الفحص المستمر "، وهو فحص لا يحدث فيه أي شيء - على غرار DB2. يمكن لجميع قواعد البيانات لدينا استبعاد المسندات المستحيلة:

3. القضاء على الصلات

في القسم السابق، لاحظنا عمليات وصول غير ضرورية إلى الجدول في استعلامات الجدول الفردي. ولكن ماذا يحدث إذا كانت JOIN لا تتطلب الوصول إلى أحد الجداول المتعددة؟ لقد كتبت بالفعل عن إزالة JOIN في مشاركة سابقة من مدونتي . محرك SQL قادر على تحديد، بناءً على نوع الاستعلام ووجود المفاتيح الأساسية والأجنبية، ما إذا كانت هناك حاجة بالفعل إلى JOIN معين في استعلام معين، أو ما إذا كان حذفها لن يؤثر على دلالات الاستعلام. في جميع الأمثلة الثلاثة التالية، ليست هناك حاجة إلى JOIN. يمكن التخلص من الصلة الداخلية... بواحد من خلال وجود مفتاح خارجي NOT NULL. بدلاً من ذلك:

SELECT first_name, last_name
FROM customer c
JOIN address a ON c.address_id = a.address_id

يمكن لقاعدة البيانات القيام بما يلي:

SELECT first_name, last_name
FROM customer c

يمكن استبدال INNER JOIN من النوع "...-to-one" إذا كان هناك مفتاح خارجي لاغٍ. يعمل الاستعلام أعلاه إذا كان المفتاح الخارجي خاضعًا لقيود NOT NULL. إذا لم يكن الأمر كذلك، على سبيل المثال، كما في هذا الطلب:

SELECT title
FROM film f
JOIN language l ON f.original_language_id = l.language_id

ثم لا يزال من الممكن حذف JOIN، ولكن سيتعين عليك إضافة المسند NOT NULL، مثل هذا:

SELECT title
FROM film
WHERE original_language_id IS NOT NULL

يمكن إزالة الصلة الخارجية من النوع "...-إلى-واحد" إذا كان هناك مفتاح فريد. بدلا من هذا:

SELECT first_name, last_name
FROM customer c
LEFT JOIN address a ON c.address_id = a.address_id

قاعدة البيانات، مرة أخرى، يمكنها القيام بما يلي:

SELECT first_name, last_name
FROM customer c

... حتى لو لم يكن هناك مفتاح خارجي لـ CUSTOMER.ADDRESS_ID. يمكن إزالة الاتصال الخارجي الفريد (DISTINCT OUTER JOIN) من النوع "...-إلى العديد". بدلا من هذا:

SELECT DISTINCT first_name, last_name
FROM actor a
LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id

يمكن لقاعدة البيانات القيام بما يلي:

SELECT DISTINCT first_name, last_name
FROM actor a

لقد تمت دراسة كل هذه الأمثلة بالتفصيل في المقالة السابقة، لذلك لن أكرر نفسي، ولكن سألخص فقط كل ما يمكن لقواعد البيانات المختلفة حذفه: لسوء الحظ، لا تستطيع جميع قواعد البيانات حل جميع أنواع الاتصالات. DB2 وSQL Server هما الرائدان بلا منازع هنا! يتبع