استفاده بهينه از عملگرهاي منطقي

kosarsh

کاربر برنزی

تاریخ عضویت : بهمن 1389

تعداد پست ها : 1138

محل سکونت : خوزستان

استفاده بهينه از عملگرهاي منطقي
چهارشنبه 17 خرداد 1391 11:22 PM

استفاده صحيح و بهينه از عملگرهاي منطقي در بسياري موارد تاثيري چشمگير بر افزايش بازدهي دارد. در اين قسمت اين عملگرها را با هم بررسي ميکنيم. البته اين نکته را ذکر کنيم که ORACLE برعکس ديگر DBMS ها عبارتهايي که در آنها از عملگرهاي منطقي استفاده شده است را از راست به چپ بررسي ميکند.

براي بهينه سازي اين عملگر بهتر است ساده ترين و محتمل ترين شرط در سمت چپ عبارت قرار گيرد، چه آنکه اگر عبارت مذکور با مقدار true ارزيابي گردد ديگر نيازي به ارزيابي ديگر عبارتهاي سمت راست OR نيست.

مثال :

فرض کنيد در سازمان تازه تاسيسي به دنبال کارمنداني ميگرديم که يا ساعات کاري آنها بيش از 140 ساعت در ماه باشد يا سابقه کاريشان کمتر از 5 سال باشد.از آنجا که شرط اول محتمل تر است بهتر است اين جستجو به اين صورت درآيد :

… WHERE exp < 5 OR Hpm > 100

چون در اين حالت خاص تعداد کارکناني که ساعات کاريشان بيش از 140 ساعت در ماه باشد بسيار کمتر از کارمنداني است که کمتر از 5 سال سابقه دارند

AND

براي بهينه سازي اين عملگر بهتر است غير محتمل ترين شرط در سمت چپ عبارت قرار گيرد، چه آنکه اگر عبارت شرطي مذکور با مقدار false ارزيابي گردد ديگر نيازي به ارزيابي ديگر عبارتهاي سمت راست AND نيست.

در مثال قبلي فرض کنيد بدنبال کارمنداني ميگرديم که بيشتر از 5 سال سابقه دارند و در ماه حداکثر 130 ساعت کار ميکنند، بهترين حالت براي نوشتن آن جستجو اين است :

… WHERE exp > 5 AND Hpm < = 100

چون در اين حالت بسيار کم بر ميخوريم به کارمنداني که بالاي 5 سال سابقه کار دارند.

ترکيبي از AND و OR

به قانون دمورگان دقت کنيد

A AND (B OR C) = (A AND B) OR (A AND C)

در بسياري مواقع اين تبديل سرعت کار را بالا ميبرد.
جدول زير را در نظر بگيريد :

ROW#	Column1	Column2
1	3	A
2	2	B
3	1	C

فرض کنيد شما اين Query را ميخواهيد روي اين جدول اجرا کنيد :

SELECT * FROM Table1
WHERE (column1 = 1 AND column2 = 'A')
OR (column1 = 1 AND column2 = 'B')

اکثرا DBMS ها اگر از index lookup استفاده نمايند عملکردشان چيزي شبيه به اين خواهد بود :

Index lookup: column1=1. Result set = {row 3}
Index lookup: column2='A'. Result set = {row 1}
AND to merge the result sets. Result set = {}
Index lookup: column1=1. Result set = {row 3}
Index lookup: column2='A'. Result set = {row 1}
AND to merge the result sets. Result set = {}
OR to merge the result sets. Result set = {}

حال اگر اين جستجو را به اين صورت درآوريم

SELECT * FROM Table1
WHERE column1 = 1
AND (column2 = 'A' OR column2 = 'B')

عملکرد index lookup به اين شکل درمي آيد :

Index lookup: column2='A'. Result set = {row 1}
Index lookup: column2='B'. Result set = {row 2}
OR to merge the result sets. Result set = {row 1, 2}
Index lookup: column1=1. Result set = {row 3}
AND to merge the result sets. Result set = {}

که همانگونه که ميبينيد عمليات پردازش کمتر شده و سرعت کار بالاتر خواهد رفت.البته تعدادي از DBMS ها هستند که به طور خودکار اين عمل را انجام ميدهند.

نکته

در کامپيوترهاي 32 بيتي سريعترين حالت براي محاسبات رياضي زماني است که عملگرها integer باشند تا زماني که samllint و float و ... باشند پس بهتر است تا حد امکان از integer ها در عمليات رياضي استفاده کنيم.

NOT

حتي المقدور از عملگر NOT پرهيز کنيد !!! مثلا عبارتهاي

... WHERE NOT (column1 > 5)
... WHERE NOT (column1 > 5 OR column2 = 7)

را به

... WHERE column1 <= 5
... WHERE column1 <= 5 AND column2 <> 7

تبديل نماييد. از آنجايي که برخي DBMS ها از ايندکس ها براي عملگر نامساوي استفاده نمي کنند بهتر است عبارتي مثل :

... WHERE NOT (bloodtype = 'O')

به اين عبارت تبديل شود :

... WHERE bloodtype < 'O'
OR bloodtype > 'O'

نکته : در مثال قبل از آنجايي اين تبديل را انجام داديم که به عنوان مثال ميدانستيم در کشورهاي آمريکاي شمالي گروه خوني اکثر مردم O است.

بسياري فکر ميکنند که بين دو عبارت زير تفاوتي وجود ندارد :

... WHERE column1 = 5 OR column1 = 6

... WHERE column1 IN (5, 6)

با توجه به تجربيات ما در برخي از DBMS هاي بزرگ عملگر IN اندکي سريعتر از عملگر OR ميباشد.البته در مواردي که در يک محدوده متوالي جستجو ميکنيم بهتر است آنچه را که در اين محدوده نيست بيابيم مثلا به جاي استفاده از :

WHERE column1 IN (1, 3, 4, 5)

اينگونه بنويسيم :

WHERE column1 BETWEEN 1 AND 5
AND column1 <> 2

که بازدهي را به طور چشمگيري بالا ميبرد.

LIKE

اکثر DBMS ها از يک ايندکس براي الگوي LIKE استفاده ميکنند در صورتي که با يک کاراکتر واقعي آغاز گردد و نه با کاراکترهايي مثل % يا _ و تنها DBMS اي که هرگز از ايندکسها براي LIKE استفاده نميکند mSQL است.

بعنوان مثال اگر شرط جستجو مطابق زير باشد :

... WHERE column1 LIKE 'C_F%'

DBMS ها اين شرط را اينگونه تحليل ميکنند که ابتدا به دنبال کليه کليد ايندکسهايي ميگردند که با C شروع شود، سپس آنهايي که حاوي F در مکان سوم است را فيلتر ميکنند.به عبارت ديگر نيازي نيست که شما شرط جستجو مذکور را به اين شکل بهينه کنيد:

... WHERE column1 >= 'C'
AND column1 < 'D'
AND column1 LIKE 'C_F%'

که باعث ميشود بازدهي بسيار پايين بيايد.

چنانچه شما ميخواهيد عملگر LIKE اي که پارامتر دارد را بهينه کنيد در حالي که ميدانيد الگوي مورد نظرتان با يک کاراکتر بهتر است عبارتي شبيه به زير را

... WHERE column1 LIKE ?

به اين صورت بهينه کنيد :

... WHERE column1 > SUBSTRING(? FROM 1 FOR 1)
AND column1 LIKE ?

چنانچه ستوني تنها 2 يا 3 کارکتر داشته باشد بهتر است از SUBSTRING به جاي LIKE استفاده کنيد اما از آنجايي که اعمال توابع بر روي ستونها خوب نيست ، در مورد SUBSTRING هاي چند گانه بهتر است از همان عملگر LIKE استفاده شود مثالا عبارت زير را :

... WHERE SUBSTRING(column1 FROM 1 FOR 1) = 'F'
OR SUBSTRING(column1 FROM 2 FOR 1) = 'F'
OR SUBSTRING(column1 FROM 3 FOR 1) = 'F'

بهتر است به اين شکل در آوريم :

...WHERE column1 LIKE '%F%'

SIMILAR

اگر شما دو جدول را بر روي ستونهايي که از نوع داده اي char يا varchar هستند با استفاده از OR پيوند بزنيد بهتر است بدانيد که عملگر جديدي که در سال 1999 به SQL اضافه گرديده است ميتواند سريعتر عمل کند. اين عبارت SIMILAR است.

سينتاکس اين عبارت به اين شکل است :

... <string> SIMILAR TO <'pattern'>

که string معمولا نام ستون است. شما ميتوانيد اين کاراکترهاي ويژه را نيز درون الگوي مورد نظرتان قرار دهيد :

• % يا _ به معناي مشابه آنچه در LIKE استفاده ميشد.

• * يا + به معناي "عبارت قبلي به طور نامحدود تکرار ميگردد "

• [A-Z] به معناي کاراکترهاي بين A و Z

• [AEK] به معناي يکي از کاراکترهاي A ، E ، K

• [^AEK] به معناي چيزي غير از کاراکترهاي A ، E ، K

• [:ALPHA:] به معناي کاراکترهاي لاتين

• [:UPPER:] به معناي کاراکترهاي با حروف بزرگ

• [:LOWER:] به معناي کاراکترهاي با حروف کوچک

• [:DIGIT:] به معناي ارقام بين 0 تا 9

• [:ALNUM:] به معناي کاراکترهاي لاتين يا ارقام

• | يا | | به معناي OR منطقي بين دو عبارت و الحاق ترتيبي

بنابراين شرط زير :

... WHERE column1 SIMILAR TO '[A-F][AEK]_'

براي عبارتهاي DEN و FAB برابر true و براي عبارتهاي GIB و AKRON برابر false خواهد بود.

از آنجاييکه که عملگر SIMILAR از OR منطقي در الگوي مورد نظر استفاده ميکند شما بعضا نيازي به OR نداريد و ميتوانيد عبارتي مثل زير را :

... WHERE column1 = 'A'
OR column1 = 'B'
OR column1 = 'K'

به اين عبارت تبديل کنيد :

... WHERE column1 SIMILAR TO '[ABK]'

UNION

در SQL یک اجتماع از دو جدول مجموعه داده های متمایزي که در هر دو جدول وجود دارند. UNION هرگز سطرهاي تکراري برنمي گرداند که باعث ميشود بعنوان یک راه عالی برای ترکیب داده ها مورد استفاده قرار گیرد اما آیا این بهترین راه است ؟

برای پاسخ به سوال فوق ما دو جستجو را انجام داده ایم :

Query #1
SELECT * FROM Table1
WHERE column1 = 5
UNION
SELECT * FROM Table1
WHERE column2 = 5
Query #2
SELECT DISTINCT * FROM Table1
WHERE column1 = 5
OR column2 = 5

در تستي که کرده ایم ستونهای column1 , column2 هیچ یک ایندکس گذاری نشده اند و به این نتیجه رسیده ایم که جستجوی شماره 2 در کلیه DBMS های مشهور سریعتر اجرا میگردد و بنابراین توصیه میکنیم تا جستجوی اول را همواره به جستجوی دوم تبدیل کنید به جز در یک مورد که برای دانستن آن باید 2 قانون بهینه سازها (optimizer) را بدانیم :

قانون اول بسیاری از بهینه سازها صرفا درون یک WHERE درون یک عبارت SELECT را بهینه سازی میکنند.پس هر دو عبارت SELECT در جستجوی شماره 1 في الواقع بهینه میشوند. ابتدا بهینه ساز کلیه سطرهايي که شرط column1 = 5 آنها true باشد را پیدا کرده و سپس کلیه سطرهايي که شرط column2 = 5 آنها true باشد را جداگانه پیدا میکند پس با این حساب 2 مرتبه جدول را پیمایش میکند و بنابراین چنانچه ستونهای مورد نظر ایندکس گذاری نشده باشند، این عمل جستجو بسیار زمان میبرد.چنانچه ستون column1 ایندکس گذاری شده باشد، بازهم عمل جستجو دو بار صورت میگیرد اما طبق يک قانون دیگر که نامعمول و نانوشته است و در برخی از DBMS ها دیده میشود که طبق آن ، زمانی که بهینه سازها به جستویی شرطی که OR دارد برخورد میکنند آنها به کلی از ایندکس گذاری اجتناب میکنند! پس در این مورد و صرفا در همین مورد UNION از SELECT بازدهی بیشتری دارد ولی با این حال باز هم استفاده از SELECT را توصیه میکنیم.

نکته :

MySQL عملگر UNION را پوشش نمیدهد.

منبع : SQL Tuning
مترجم: علیرضا شیرازی- ar.shirazi@gmail.com