M.A.H.S.A
07-03-2011, 09:41 AM
استفاده صحیح و بهینه از عملگرهای منطقی در بسیاری موارد تاثیری چشمگیر بر افزایش بازدهی دارد. در این قسمت این عملگرها را با هم بررسی میکنیم. البته این نکته را ذکر کنیم که ORACLE برعکس دیگر DBMS ها عبارتهایی که در آنها از عملگرهای منطقی استفاده شده است را از راست به چپ بررسی میکند.
OR
برای بهینه سازی این عملگر بهتر است ساده ترین و محتمل ترین شرط در سمت چپ عبارت قرار گیرد، چه آنکه اگر عبارت مذکور با مقدار true ارزیابی گردد دیگر نیازی به ارزیابی دیگر عبارتهای سمت راست OR نیست.
مثال :
فرض کنید در سازمان تازه تاسیسی به دنبال کارمندانی میگردیم که یا ساعات کاری آنها بیش از ۱۴۰ ساعت در ماه باشد یا سابقه کاریشان کمتر از ۵ سال باشد.از آنجا که شرط اول محتمل تر است بهتر است این جستجو به این صورت درآید :
… WHERE exp < 5 OR Hpm > 100
چون در این حالت خاص تعداد کارکنانی که ساعات کاریشان بیش از ۱۴۰ ساعت در ماه باشد بسیار کمتر از کارمندانی است که کمتر از ۵ سال سابقه دارند
AND
برای بهینه سازی این عملگر بهتر است غیر محتمل ترین شرط در سمت چپ عبارت قرار گیرد، چه آنکه اگر عبارت شرطی مذکور با مقدار false ارزیابی گردد دیگر نیازی به ارزیابی دیگر عبارتهای سمت راست AND نیست.
در مثال قبلی فرض کنید بدنبال کارمندانی میگردیم که بیشتر از ۵ سال سابقه دارند و در ماه حداکثر ۱۳۰ ساعت کار میکنند، بهترین حالت برای نوشتن آن جستجو این است :
… WHERE exp > 5 AND Hpm < = 100
چون در این حالت بسیار کم بر میخوریم به کارمندانی که بالای ۵ سال سابقه کار دارند.
ترکیبی از AND و OR
به قانون دمورگان دقت کنید
A AND (B OR C) = (A AND B) OR (A AND C)
در بسیاری مواقع این تبدیل سرعت کار را بالا میبرد.
جدول زیر را در نظر بگیرید :
http://sourceup.tk/uploads/1355151015.png
فرض کنید شما این 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 ها هستند که به طور خودکار این عمل را انجام میدهند.
نکته
در کامپیوترهای ۳۲ بیتی سریعترین حالت برای محاسبات ریاضی زمانی است که عملگرها 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 است.
IN
بسیاری فکر میکنند که بین دو عبارت زیر تفاوتی وجود ندارد :
… 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 ?
چنانچه ستونی تنها ۲ یا ۳ کارکتر داشته باشد بهتر است از 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 پیوند بزنید بهتر است بدانید که عملگر جدیدی که در سال ۱۹۹۹ به SQL اضافه گردیده است میتواند سریعتر عمل کند. این عبارت SIMILAR است.
سینتاکس این عبارت به این شکل است :
… <string> SIMILAR TO <’pattern’>
که string معمولا نام ستون است. شما میتوانید این کاراکترهای ویژه را نیز درون الگوی مورد نظرتان قرار دهید :
• % یا _ به معنای مشابه آنچه در LIKE استفاده میشد.
• * یا + به معنای “عبارت قبلی به طور نامحدود تکرار میگردد ”
• [A-Z] به معنای کاراکترهای بین A و Z
• [AEK] به معنای یکی از کاراکترهای A ، E ، K
• [^AEK] به معنای چیزی غیر از کاراکترهای A ، E ، K
• [:ALPHA:] به معنای کاراکترهای لاتین
• [:UPPER:] به معنای کاراکترهای با حروف بزرگ
• [:LOWER:] به معنای کاراکترهای با حروف کوچک
• [http://l.yimg.com/us.yimg.com/i/mesg/emoticons7/4.gifIGIT:] به معنای ارقام بین ۰ تا ۹
• [: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 هیچ یک ایندکس گذاری نشده اند و به این نتیجه رسیده ایم که جستجوی شماره ۲ در کلیه DBMS های مشهور سریعتر اجرا میگردد و بنابراین توصیه میکنیم تا جستجوی اول را همواره به جستجوی دوم تبدیل کنید به جز در یک مورد که برای دانستن آن باید ۲ قانون بهینه سازها (optimizer) را بدانیم :
قانون اول بسیاری از بهینه سازها صرفا درون یک WHERE درون یک عبارت SELECT را بهینه سازی میکنند.پس هر دو عبارت SELECT در جستجوی شماره ۱ فی الواقع بهینه میشوند. ابتدا بهینه ساز کلیه سطرهایی که شرط column1 = 5 آنها true باشد را پیدا کرده و سپس کلیه سطرهایی که شرط column2 = 5 آنها true باشد را جداگانه پیدا میکند پس با این حساب ۲ مرتبه جدول را پیمایش میکند و بنابراین چنانچه ستونهای مورد نظر ایندکس گذاری نشده باشند، این عمل جستجو بسیار زمان میبرد.چنانچه ستون column1 ایندکس گذاری شده باشد، بازهم عمل جستجو دو بار صورت میگیرد اما طبق یک قانون دیگر که نامعمول و نانوشته است و در برخی از DBMS ها دیده میشود که طبق آن ، زمانی که بهینه سازها به جستویی شرطی که OR دارد برخورد میکنند آنها به کلی از ایندکس گذاری اجتناب میکنند! پس در این مورد و صرفا در همین مورد UNION از SELECT بازدهی بیشتری دارد ولی با این حال باز هم استفاده از SELECT را توصیه میکنیم.
نکته :
MySQL عملگر UNION را پوشش نمیدهد.
OR
برای بهینه سازی این عملگر بهتر است ساده ترین و محتمل ترین شرط در سمت چپ عبارت قرار گیرد، چه آنکه اگر عبارت مذکور با مقدار true ارزیابی گردد دیگر نیازی به ارزیابی دیگر عبارتهای سمت راست OR نیست.
مثال :
فرض کنید در سازمان تازه تاسیسی به دنبال کارمندانی میگردیم که یا ساعات کاری آنها بیش از ۱۴۰ ساعت در ماه باشد یا سابقه کاریشان کمتر از ۵ سال باشد.از آنجا که شرط اول محتمل تر است بهتر است این جستجو به این صورت درآید :
… WHERE exp < 5 OR Hpm > 100
چون در این حالت خاص تعداد کارکنانی که ساعات کاریشان بیش از ۱۴۰ ساعت در ماه باشد بسیار کمتر از کارمندانی است که کمتر از ۵ سال سابقه دارند
AND
برای بهینه سازی این عملگر بهتر است غیر محتمل ترین شرط در سمت چپ عبارت قرار گیرد، چه آنکه اگر عبارت شرطی مذکور با مقدار false ارزیابی گردد دیگر نیازی به ارزیابی دیگر عبارتهای سمت راست AND نیست.
در مثال قبلی فرض کنید بدنبال کارمندانی میگردیم که بیشتر از ۵ سال سابقه دارند و در ماه حداکثر ۱۳۰ ساعت کار میکنند، بهترین حالت برای نوشتن آن جستجو این است :
… WHERE exp > 5 AND Hpm < = 100
چون در این حالت بسیار کم بر میخوریم به کارمندانی که بالای ۵ سال سابقه کار دارند.
ترکیبی از AND و OR
به قانون دمورگان دقت کنید
A AND (B OR C) = (A AND B) OR (A AND C)
در بسیاری مواقع این تبدیل سرعت کار را بالا میبرد.
جدول زیر را در نظر بگیرید :
http://sourceup.tk/uploads/1355151015.png
فرض کنید شما این 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 ها هستند که به طور خودکار این عمل را انجام میدهند.
نکته
در کامپیوترهای ۳۲ بیتی سریعترین حالت برای محاسبات ریاضی زمانی است که عملگرها 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 است.
IN
بسیاری فکر میکنند که بین دو عبارت زیر تفاوتی وجود ندارد :
… 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 ?
چنانچه ستونی تنها ۲ یا ۳ کارکتر داشته باشد بهتر است از 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 پیوند بزنید بهتر است بدانید که عملگر جدیدی که در سال ۱۹۹۹ به SQL اضافه گردیده است میتواند سریعتر عمل کند. این عبارت SIMILAR است.
سینتاکس این عبارت به این شکل است :
… <string> SIMILAR TO <’pattern’>
که string معمولا نام ستون است. شما میتوانید این کاراکترهای ویژه را نیز درون الگوی مورد نظرتان قرار دهید :
• % یا _ به معنای مشابه آنچه در LIKE استفاده میشد.
• * یا + به معنای “عبارت قبلی به طور نامحدود تکرار میگردد ”
• [A-Z] به معنای کاراکترهای بین A و Z
• [AEK] به معنای یکی از کاراکترهای A ، E ، K
• [^AEK] به معنای چیزی غیر از کاراکترهای A ، E ، K
• [:ALPHA:] به معنای کاراکترهای لاتین
• [:UPPER:] به معنای کاراکترهای با حروف بزرگ
• [:LOWER:] به معنای کاراکترهای با حروف کوچک
• [http://l.yimg.com/us.yimg.com/i/mesg/emoticons7/4.gifIGIT:] به معنای ارقام بین ۰ تا ۹
• [: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 هیچ یک ایندکس گذاری نشده اند و به این نتیجه رسیده ایم که جستجوی شماره ۲ در کلیه DBMS های مشهور سریعتر اجرا میگردد و بنابراین توصیه میکنیم تا جستجوی اول را همواره به جستجوی دوم تبدیل کنید به جز در یک مورد که برای دانستن آن باید ۲ قانون بهینه سازها (optimizer) را بدانیم :
قانون اول بسیاری از بهینه سازها صرفا درون یک WHERE درون یک عبارت SELECT را بهینه سازی میکنند.پس هر دو عبارت SELECT در جستجوی شماره ۱ فی الواقع بهینه میشوند. ابتدا بهینه ساز کلیه سطرهایی که شرط column1 = 5 آنها true باشد را پیدا کرده و سپس کلیه سطرهایی که شرط column2 = 5 آنها true باشد را جداگانه پیدا میکند پس با این حساب ۲ مرتبه جدول را پیمایش میکند و بنابراین چنانچه ستونهای مورد نظر ایندکس گذاری نشده باشند، این عمل جستجو بسیار زمان میبرد.چنانچه ستون column1 ایندکس گذاری شده باشد، بازهم عمل جستجو دو بار صورت میگیرد اما طبق یک قانون دیگر که نامعمول و نانوشته است و در برخی از DBMS ها دیده میشود که طبق آن ، زمانی که بهینه سازها به جستویی شرطی که OR دارد برخورد میکنند آنها به کلی از ایندکس گذاری اجتناب میکنند! پس در این مورد و صرفا در همین مورد UNION از SELECT بازدهی بیشتری دارد ولی با این حال باز هم استفاده از SELECT را توصیه میکنیم.
نکته :
MySQL عملگر UNION را پوشش نمیدهد.