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

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

4. القضاء على المسندات "التي لا معنى لها".

لا معنى لها على حد سواء المسندات التي (تقريبا) صحيحة دائما. كما يمكنك أن تتخيل، إذا كنت تسأل:

SELECT * FROM actor WHERE 1 = 1;

... فإن قواعد البيانات لن تنفذها فعليًا، ولكنها ستتجاهلها ببساطة. لقد أجبت ذات مرة على سؤال حول هذا الموضوع في Stack Overflow ولهذا السبب قررت كتابة هذا المقال. سأترك اختبار هذا كتمرين للقارئ، ولكن ماذا يحدث إذا كان المسند أقل "بلا معنى"؟ على سبيل المثال:

SELECT * FROM film WHERE release_year = release_year;

هل تحتاج حقًا إلى مقارنة القيمة بنفسها لكل صف؟ لا، لا توجد قيمة يكون فيها هذا المسند FALSE ، أليس كذلك؟ ولكن لا يزال يتعين علينا التحقق من ذلك. على الرغم من أن المسند لا يمكن أن يكون مساويًا لـ FALSE ، إلا أنه قد يكون مساويًا لـ NULL في كل مكان ، وذلك مرة أخرى بسبب المنطق ثلاثي القيم. يكون العمود RELEASE_YEAR خاليًا، وإذا كان أي من الصفوف يحتوي على RELEASE_YEAR IS NULL ، فإن NULL = NULL ينتج عنه NULL ويجب حذف الصف. فيصبح الطلب كالتالي:

SELECT * FROM film WHERE release_year IS NOT NULL;

ما هي قواعد البيانات التي تفعل هذا؟

DB2

نعم!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49
Predicate Information
 2 - SARG Q1.RELEASE_YEAR IS NOT NULL

ماي إس كيو إل

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

CREATE INDEX i_release_year ON film (release_year);

واحصل على خطط للطلبات التالية في المقابل:

SELECT * FROM film WHERE release_year = release_year;
SELECT * FROM film WHERE release_year IS NOT NULL;

إذا نجح التحسين، فيجب أن تكون خطط كلا الاستعلامين متماثلة تقريبًا. ولكن في هذه الحالة ليس هذا هو الحال:

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film             1000  10.00           Using where

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film   i_release_year  1000  100.00    Using where

كما ترى، يختلف الاستعلامان بشكل كبير في قيم العمودين POSSIBLE_KEYS و FILTERED . لذا فإنني أجازف بتخمين معقول أن MySQL لا تقوم بتحسين هذا.

وحي

نعم!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|*  1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RELEASE_YEAR" IS NOT NULL)

PostgreSQL

للاسف لا!

QUERY PLAN
--------------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: ((release_year)::integer = (release_year)::integer)

تختلف الخطط والتكاليف. أي: انظر إلى تقدير الأصل، وهو غير جيد مطلقاً، بينما هذا المسند:

SELECT * FROM film WHERE release_year IS NOT NULL;

يعطي نتائج أفضل بكثير:

QUERY PLAN
---------------------------------------------------------
Seq Scan on film  (cost=0.00..65.00 rows=1000 width=386)
  Filter: (release_year IS NOT NULL)

الخسارة!

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

ومن الغريب أن SQL Server لا يبدو أنه يقوم بذلك أيضًا:

|--Table Scan(OBJECT:([film]), WHERE:([release_year]=[release_year]))

ومع ذلك، استنادًا إلى مظهر الخطة، يكون تقييم الأصل صحيحًا، كما هو الحال بالنسبة للتكلفة. ولكن في تجربتي مع SQL Server، أود أن أقول أنه في هذه الحالة، لا يحدث أي تحسين، لأن SQL Server سيعرض المسند الذي تم تنفيذه بالفعل في الخطة (لمعرفة السبب، ألق نظرة على أمثلة قيد التحقق أدناه). ماذا عن المسندات "التي لا معنى لها" في الأعمدة NOT NULL ؟ كان التحويل أعلاه ضروريًا فقط لأنه يمكن أن يكون RELEASE_YEAR غير محدد. ماذا يحدث إذا قمت بتشغيل نفس الاستعلام الذي لا معنى له، على سبيل المثال، العمود FILM_ID ؟

SELECT * FROM film WHERE film_id = film_id

هل يتوافق الآن مع أي مسند على الإطلاق؟ أو على الأقل هذا ما ينبغي أن يكون. ولكن هل هو كذلك؟

DB2

نعم!

Explain Plan
-------------------------------------------------
ID | Operation    |                   Rows | Cost
 1 | RETURN       |                        |   49
 2 |  TBSCAN FILM | 1000 of 1000 (100.00%) |   49

لا يتم تطبيق أي مسندات على الإطلاق ونختار جميع الأفلام.

ماي إس كيو إل

نعم! (مرة أخرى، تخمين المتعلمين)

ID  TABLE  POSSIBLE_KEYS   ROWS  FILTERED  EXTRA
------------------------------------------------------
1   film                   1000  100.00

لاحظ أن العمود EXTRA أصبح فارغًا الآن، كما لو لم يكن لدينا جملة WHERE على الإطلاق!

وحي

نعم!

----------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |
----------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |
|   1 |  TABLE ACCESS FULL| FILM |      1 |   1000 |
----------------------------------------------------

مرة أخرى، لا تنطبق أي المسندات.

PostgreSQL

واو، لا مرة أخرى!

QUERY PLAN
------------------------------------------------------
Seq Scan on film  (cost=0.00..67.50 rows=5 width=386)
  Filter: (film_id = film_id)

تم تطبيق الفلتر وتبقى درجة العلاقة الأساسية 5. المشكلة!

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

وهنا مرة أخرى لا!

|--Table Scan(OBJECT:([film]), WHERE:([film_id]=[film_id]))

ملخص

يبدو وكأنه تحسين بسيط، لكنه لا يستخدم في جميع أنظمة إدارة قواعد البيانات، وعلى وجه الخصوص، من الغريب أنه لا يستخدم في SQL Server!

5. التوقعات في الاستعلامات الفرعية الموجودة

ومن المثير للاهتمام، أنني أُسأل عنها طوال الوقت في صفي الرئيسي، حيث أدافع عن وجهة النظر القائلة بأن SELECT * عادة لا يؤدي إلى أي خير. السؤال هو: هل من الممكن استخدام SELECT * في استعلام فرعي موجود ؟ على سبيل المثال، إذا أردنا العثور على ممثلين لعبوا في الأفلام...

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT * -- Is this OK?
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

والجواب هو نعم. يستطيع. النجمة لا تؤثر على الطلب. كيف يمكنك التأكد من هذا؟ خذ بعين الاعتبار الاستعلام التالي:

-- DB2
SELECT 1 / 0 FROM sysibm.dual
-- Oracle
SELECT 1 / 0 FROM dual
-- PostgreSQL, SQL Server
SELECT 1 / 0
-- MySQL
SELECT pow(-1, 0.5);

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

-- DB2
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM sysibm.dual
) THEN 1 ELSE 0 END
FROM sysibm.dual
-- Oracle
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0 FROM dual
) THEN 1 ELSE 0 END
FROM dual
-- PostgreSQL
SELECT EXISTS (SELECT 1 / 0)
-- SQL Server
SELECT CASE WHEN EXISTS (
  SELECT 1 / 0
) THEN 1 ELSE 0 END
-- MySQL
SELECT EXISTS (SELECT pow(-1, 0.5));

الآن لم تُرجع أي من قواعد البيانات خطأً. جميعهم يعودون بـ TRUE أو 1 . هذا يعني أن أيًا من قواعد البيانات الخاصة بنا لا تقوم فعليًا بتقييم الإسقاط (أي عبارة SELECT ) للاستعلام الفرعي EXISTS . يعرض SQL Server، على سبيل المثال، الخطة التالية:

|--Constant Scan(VALUES:((CASE WHEN (1) THEN (1) ELSE (0) END)))

كما ترون، تم تحويل تعبير CASE إلى ثابت وتم حذف الاستعلام الفرعي. تقوم قواعد البيانات الأخرى بتخزين الاستعلام الفرعي في الخطة ولا تذكر أي شيء عن التوقع، لذلك دعونا نلقي نظرة أخرى على خطة الاستعلام الأصلية في Oracle:

SELECT first_name, last_name
FROM actor a
WHERE EXISTS (
  SELECT *
  FROM film_actor fa
  WHERE a.actor_id = fa.actor_id
)

تبدو خطة الاستعلام أعلاه كما يلي:

------------------------------------------------------------------
| Id  | Operation             | Name                    | E-Rows |
------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                         |        |
|*  1 |  HASH JOIN SEMI       |                         |    200 |
|   2 |   TABLE ACCESS FULL   | ACTOR                   |    200 |
|   3 |   INDEX FAST FULL SCAN| IDX_FK_FILM_ACTOR_ACTOR |   5462 |
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("A"."ACTOR_ID"="FA"."ACTOR_ID")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=1) LAST_NAME, FIRST_NAME
   2 - (rowset=256) A.ACTOR_ID, FIRST_NAME, LAST_NAME
   3 - FA.ACTOR_ID

نلاحظ معلومات حول الإسقاط عند Id=3 . في الواقع، نحن لا نصل حتى إلى جدول FILM_ACTOR لأننا لا نحتاج لذلك. يمكن تنفيذ المسند EXISTS باستخدام فهرس المفاتيح الخارجية في عمود ACTOR_ID واحد - كل ما هو مطلوب لهذا الاستعلام - على الرغم من أننا كتبنا SELECT * .

ملخص

لحسن الحظ، تقوم جميع قواعد البيانات لدينا بإزالة الإسقاط من الاستعلامات الفرعية EXISTS : ترقبوا الجزء 3 ، حيث سنناقش تحسينات SQL الرائعة الأخرى.