M.A.H.S.A
07-03-2011, 10:42 AM
در این مقاله سعی بر آن گردیده است تا برخی اصول ابتدایی بهینه سازی کد SQL را بدون در نظر گرفتن اینکه از چه DBMS ای استفاده میکنید بررسی شود.ابتدا به بررسی سینتاکس پرداخته و روشی نوین جهت امتیاز دهی به دستورات و عبارات SQL بیان میگردد و سپس به بررسی برخی نکات ریز اما کلیدی می پردازیم و در پایان نقش عملگرها را بررسی میکنیم.
بهبود بازدهی از طریق بهبود syntax
یک syntax مجموعه ای است از عبارتها و نحوه قرارگیری آنها در یک عبارت بزرگتر و معمولا نمی توان صرفا Syntax را زیاد تغییر داد چراکه زبان SQL تعداد محدودی دستور دارد که در آنها گزینه های اختیاری زیادی وجود دارد.اما مهمترین Syntax ای که قابلیت بهینه سازی زیادی دارد عبارتهای شرطی در جستجوهاست.
در یک عبارت میتوانیم بگوییم بهترین جستجوهای شرطی آنهایی هستند که روی تعداد کمی از سطرها اعمال میشوند.
امتیازدهی به عبارت SQL
جدول زیر این شروط را از بهترین به بدترین مرتب میکند
http://sourceup.tk/uploads/1358526461.png
به این مثال دقت کنید :
…. WHERE smallint_column = 12345
این شرط جستجو ۲۷ امتیاز خواهد داشت :
• ۵ امتیاز برای ستون smallint_column که بتنهایی در سمت چپ قرار دارد
• ۲ امتیاز برای نوع داده ای ستون smallint_column که عددی است
• ۱۰ امتیاز برای عملگر مساوی
• ۱۰ امتیاز برای عبارت عددی ۱۲۳۴۵ که به تنهایی در سمت راست قرار دارد
حال به این مثال دقت کنید :
… WHERE char_column > = varchar_column | | ‘x’
این مثال تنها ۱۳ امتیاز خواهد داشت :
• ۵ امتیاز برای ستون char_column که بتنهایی در سمت چپ قرار دارد
• ۵ امتیاز برای عملگر > =
• ۳ امتیاز برای عبارت ترکیبی varchar_column | | ‘x’
اما چنانچه آن را به صورت زیر در آوریم :
GET var AS varchar_column | | ‘x’.
…. WHERE char_column > = var
آنگاه ۱۵ امتیاز خواهد گرفت که کاملا منطقی است. چرا که عمل or بین varchar_column و ‘x’ فقط یکبار انجام میشود.
قرار دادن ستونها در ترتیب صحیح خود
یکی از راههای بهبود بازدهی در زمانی که در یک عبارت جستجو میخواهیم چند ستون را شرکت دهیم ، رعایت ترتیب ستونهاست مثل :
SELECT * FROM Table1
WHERE column1 = 5
AND column2 = 77.3
AND column3 = ‘Smith’
AND column4 < 117
AND column4 > column5
نکته : اکثر DBMS های معروف به طور خودکار این عمل را انجام میدهند.
اصل تعدی
بر اساس اصل تعدی داریم :
IF
(A <comparison operator> B) IS TRUE AND (B <comparison operator> C) IS TRUE
THEN
(A <comparison operator> C) IS TRUE AND NOT (A <comparison operator> C) IS FALSE
با استفاده از این اصل در برخی موارد میتوان جای عملوندها را بدون تغییر مفهوم کلی عبارت تغییر دهیم به گونه ای که عبارت شرطی ما امتیاز بیشتری بدست آورد مثل :
Expression #1
… WHERE column1 < column2
AND column2 = column3
AND column1 = 5
Expression #2
… WHERE 5 < column2
AND column2 = column3
AND column1 = 5
که عبارت دوم بهینه تر از عبارت نخست است.
در مثال زیر ، با تغییر عبارت SQL و تبدیل آن به حالت دوم ، سرعت اجرا نیم برابر بیشتر می شود.
Expression #1
SELECT * FROM Table1
WHERE column1 = 5 AND
NOT (column3 = 7 OR column1 = column2)
Expression #2
SELECT * FROM Table1
WHERE column1 = 5
AND column3 <> 7
AND column2 <> 5
نکته : تنها نگرانی در استفاده از اصل تعدی ، برخورد با مقادیر NULL است که خوشبختانه DMBS های مطرح مثل SQL Server هنگامی که طرف راست یک عملگر مثل > = مقدار NULL باشد FALSE بر میگردانند.
مثالی از راهنمای online پایگاه داده MySQL
… WHERE a < b AND b = c AND a = 5
transforms to:
… WHERE b > 5 AND b = c AND a = 5
تبدیل ثوابت به مقدار عددی آنها
بعضا در بسیاری از موارد با ثوابت زیادی در جستجوها روبرو میشویم. این ثوابت هرچند باعث خوانایی کد و سادگی برنامه نویسی میشنود اما تاثیر منفی بر بازدهی دارند.مثلا کدهای زیر را در نظر بگیرید
Query #1:
SELECT * FROM Table1
WHERE date_column = CURRENT_DATE
AND amount * 5 > 100.00
Query #2:
SELECT * FROM Table1
WHERE date_column = DATE ’2002-01-01′
AND amount * 5 > 100.00
کد جستجوی دوم سریعتر از کد اول اجرا خواهد گردید اما ذکر این نکته ضروری است که در جهان واقع چنانچه بخواهید اینگونه عمل کنید باید خودتان به طور دستی هر روز این کد را تغییر داده و تاریخ آن روز را وارد نمایید که البته میتوانید این کار را با استفاده از یک query در ابتدای هر روز انجام دهید و یا اینکه به محض تغییر تاریخ این عمل را انجام دهید.
دقت در حذف کدهای به ظاهر غیر مؤثر
در برخی مواقع به کدهایی برمیخوریم که بدیهی به نظر میرسند و به ظاهر زائد میباشند. مثلا قطعه کدهای زیر را ببینید :
… WHERE column1 + 0
… WHERE 5 + 0.0
… WHERE column1 IN (1, 3, 3)
… CAST(1 AS INTEGER)
… WHERE ‘a’ || ‘b’
یا مثلا فرض کنید ستونی به نام col_indx داریم که مقدار آن همیشه بزرگتر از صفر باشد ، شاید جستجویی مثل عبارت زیر غیر منطقی به نظر برسد :
Select * from myTable
Where col_indx > 0
و بهتر باشد که حذف گردد اما در اغلب این مواقع چنانچه دلیل اصلی وجود چنین عبارتهایی را نمی دانیم بهتر است که آنها را به حال خود رها کنیم و یا چنانچه قصد بهینه سازی آنها را داریم، بسیار محتاط عمل کنیم چرا که در اکثر قریب به اتفاق مواردی که به چنین کدهایی بر میخوریم ، برنامه نویس ایجاد کننده آن کدها دلیل خاصی برای این کار خود داشته است.
در مثال بالا ممکن است به دلیل وجود یک ایندکس روی ستون col_indx ، شرط جستجو به این شکل در آمده باشد و چنانچه این کد را حذف کنیم نه تنها بازدهی بالا نخواهد رفت بلکه ممکن است شدیدا افت کند.
گاهی این کدها به دلایل دیگری مثل در نظر نگرفتن ایندکس ها، تغییر نوه دادهای حاصل از محاسبه، تفاوت قائل شدن بین انواع داده ای مشابه (مثل samllint, integer) و … ایجاد شده باشند. پس توصیه ما این است که چنین عباراتی را تا زمانی که دلیل واقعی آنها را نمی دانید تغییر ندهید.
دقت در جستجوهای حساس به حروف
از انجایی که یک قاعده کلی در مورد حساسیت به حروف در کلیه DBMS ها وجود ندارد – مثلا Microsoft Access رشته “SMITH” را با “Smith” یکسان در نطر میگیرد اما SQL Server به گونه ای است که در آن میتوان انتخاب کرد که حساس به حروف باشد یا نه- برخی برنامه نویسان در صدد برمی آیند تا با افزودن کدهایی عدم حساسیت به حروف را مثلا اینگونه نشان دهند :
… WHERE UPPER(column1) = ‘SMITH’
این کار هرچند با ظاهر در موقعی که با کاراکترهای لاتین کار میکنیم ممکن است مشکلی ایجاد نکند اما در هنگام کار با دیگر زبان ها به خصوص آلمانی و فرانسوی میتواند مشکل ساز باشد. به مثال زیر دقت کنید :
عبارت ‘résumé’ در زبان آلمانی به معنای curriculum vitae است که اگر تابع زیر روی آن اعمال گردد :
… UPPER(‘résumé’)
حاصل RESUME خواهد بود بمعنای begin again !!!
چرا که در هنگام تبدیل حروف کوچک به بزرگ علایم نحوی آن حذف گردیده اند و معنای لغت بکلی دگرگون شده است.
پرهیز از اعمال توابع بر روی ستونها
Microsoft و Oracle به شدت توصیه دارند که تا حد ممکن از اعمال توابع بر روی ستونها خودداری نمایید مثلا به جای عبارت زیر :
… WHERE column1 = ‘SMITH’
OR column1 = ‘Smith’
از این عبارت استفاده نمایید :
… WHERE column1 = ‘SMITH’
OR (‘SMITH’ <> ‘Smith’ AND column1 = ‘Smith’)
که باعث می شود ارجاع به ستون column1 و بازیابی مقدار آن حداقل شود که همین کار باعث بالا رفتن سرعت پردازش میگردد
بهبود بازدهی از طریق بهبود syntax
یک syntax مجموعه ای است از عبارتها و نحوه قرارگیری آنها در یک عبارت بزرگتر و معمولا نمی توان صرفا Syntax را زیاد تغییر داد چراکه زبان SQL تعداد محدودی دستور دارد که در آنها گزینه های اختیاری زیادی وجود دارد.اما مهمترین Syntax ای که قابلیت بهینه سازی زیادی دارد عبارتهای شرطی در جستجوهاست.
در یک عبارت میتوانیم بگوییم بهترین جستجوهای شرطی آنهایی هستند که روی تعداد کمی از سطرها اعمال میشوند.
امتیازدهی به عبارت SQL
جدول زیر این شروط را از بهترین به بدترین مرتب میکند
http://sourceup.tk/uploads/1358526461.png
به این مثال دقت کنید :
…. WHERE smallint_column = 12345
این شرط جستجو ۲۷ امتیاز خواهد داشت :
• ۵ امتیاز برای ستون smallint_column که بتنهایی در سمت چپ قرار دارد
• ۲ امتیاز برای نوع داده ای ستون smallint_column که عددی است
• ۱۰ امتیاز برای عملگر مساوی
• ۱۰ امتیاز برای عبارت عددی ۱۲۳۴۵ که به تنهایی در سمت راست قرار دارد
حال به این مثال دقت کنید :
… WHERE char_column > = varchar_column | | ‘x’
این مثال تنها ۱۳ امتیاز خواهد داشت :
• ۵ امتیاز برای ستون char_column که بتنهایی در سمت چپ قرار دارد
• ۵ امتیاز برای عملگر > =
• ۳ امتیاز برای عبارت ترکیبی varchar_column | | ‘x’
اما چنانچه آن را به صورت زیر در آوریم :
GET var AS varchar_column | | ‘x’.
…. WHERE char_column > = var
آنگاه ۱۵ امتیاز خواهد گرفت که کاملا منطقی است. چرا که عمل or بین varchar_column و ‘x’ فقط یکبار انجام میشود.
قرار دادن ستونها در ترتیب صحیح خود
یکی از راههای بهبود بازدهی در زمانی که در یک عبارت جستجو میخواهیم چند ستون را شرکت دهیم ، رعایت ترتیب ستونهاست مثل :
SELECT * FROM Table1
WHERE column1 = 5
AND column2 = 77.3
AND column3 = ‘Smith’
AND column4 < 117
AND column4 > column5
نکته : اکثر DBMS های معروف به طور خودکار این عمل را انجام میدهند.
اصل تعدی
بر اساس اصل تعدی داریم :
IF
(A <comparison operator> B) IS TRUE AND (B <comparison operator> C) IS TRUE
THEN
(A <comparison operator> C) IS TRUE AND NOT (A <comparison operator> C) IS FALSE
با استفاده از این اصل در برخی موارد میتوان جای عملوندها را بدون تغییر مفهوم کلی عبارت تغییر دهیم به گونه ای که عبارت شرطی ما امتیاز بیشتری بدست آورد مثل :
Expression #1
… WHERE column1 < column2
AND column2 = column3
AND column1 = 5
Expression #2
… WHERE 5 < column2
AND column2 = column3
AND column1 = 5
که عبارت دوم بهینه تر از عبارت نخست است.
در مثال زیر ، با تغییر عبارت SQL و تبدیل آن به حالت دوم ، سرعت اجرا نیم برابر بیشتر می شود.
Expression #1
SELECT * FROM Table1
WHERE column1 = 5 AND
NOT (column3 = 7 OR column1 = column2)
Expression #2
SELECT * FROM Table1
WHERE column1 = 5
AND column3 <> 7
AND column2 <> 5
نکته : تنها نگرانی در استفاده از اصل تعدی ، برخورد با مقادیر NULL است که خوشبختانه DMBS های مطرح مثل SQL Server هنگامی که طرف راست یک عملگر مثل > = مقدار NULL باشد FALSE بر میگردانند.
مثالی از راهنمای online پایگاه داده MySQL
… WHERE a < b AND b = c AND a = 5
transforms to:
… WHERE b > 5 AND b = c AND a = 5
تبدیل ثوابت به مقدار عددی آنها
بعضا در بسیاری از موارد با ثوابت زیادی در جستجوها روبرو میشویم. این ثوابت هرچند باعث خوانایی کد و سادگی برنامه نویسی میشنود اما تاثیر منفی بر بازدهی دارند.مثلا کدهای زیر را در نظر بگیرید
Query #1:
SELECT * FROM Table1
WHERE date_column = CURRENT_DATE
AND amount * 5 > 100.00
Query #2:
SELECT * FROM Table1
WHERE date_column = DATE ’2002-01-01′
AND amount * 5 > 100.00
کد جستجوی دوم سریعتر از کد اول اجرا خواهد گردید اما ذکر این نکته ضروری است که در جهان واقع چنانچه بخواهید اینگونه عمل کنید باید خودتان به طور دستی هر روز این کد را تغییر داده و تاریخ آن روز را وارد نمایید که البته میتوانید این کار را با استفاده از یک query در ابتدای هر روز انجام دهید و یا اینکه به محض تغییر تاریخ این عمل را انجام دهید.
دقت در حذف کدهای به ظاهر غیر مؤثر
در برخی مواقع به کدهایی برمیخوریم که بدیهی به نظر میرسند و به ظاهر زائد میباشند. مثلا قطعه کدهای زیر را ببینید :
… WHERE column1 + 0
… WHERE 5 + 0.0
… WHERE column1 IN (1, 3, 3)
… CAST(1 AS INTEGER)
… WHERE ‘a’ || ‘b’
یا مثلا فرض کنید ستونی به نام col_indx داریم که مقدار آن همیشه بزرگتر از صفر باشد ، شاید جستجویی مثل عبارت زیر غیر منطقی به نظر برسد :
Select * from myTable
Where col_indx > 0
و بهتر باشد که حذف گردد اما در اغلب این مواقع چنانچه دلیل اصلی وجود چنین عبارتهایی را نمی دانیم بهتر است که آنها را به حال خود رها کنیم و یا چنانچه قصد بهینه سازی آنها را داریم، بسیار محتاط عمل کنیم چرا که در اکثر قریب به اتفاق مواردی که به چنین کدهایی بر میخوریم ، برنامه نویس ایجاد کننده آن کدها دلیل خاصی برای این کار خود داشته است.
در مثال بالا ممکن است به دلیل وجود یک ایندکس روی ستون col_indx ، شرط جستجو به این شکل در آمده باشد و چنانچه این کد را حذف کنیم نه تنها بازدهی بالا نخواهد رفت بلکه ممکن است شدیدا افت کند.
گاهی این کدها به دلایل دیگری مثل در نظر نگرفتن ایندکس ها، تغییر نوه دادهای حاصل از محاسبه، تفاوت قائل شدن بین انواع داده ای مشابه (مثل samllint, integer) و … ایجاد شده باشند. پس توصیه ما این است که چنین عباراتی را تا زمانی که دلیل واقعی آنها را نمی دانید تغییر ندهید.
دقت در جستجوهای حساس به حروف
از انجایی که یک قاعده کلی در مورد حساسیت به حروف در کلیه DBMS ها وجود ندارد – مثلا Microsoft Access رشته “SMITH” را با “Smith” یکسان در نطر میگیرد اما SQL Server به گونه ای است که در آن میتوان انتخاب کرد که حساس به حروف باشد یا نه- برخی برنامه نویسان در صدد برمی آیند تا با افزودن کدهایی عدم حساسیت به حروف را مثلا اینگونه نشان دهند :
… WHERE UPPER(column1) = ‘SMITH’
این کار هرچند با ظاهر در موقعی که با کاراکترهای لاتین کار میکنیم ممکن است مشکلی ایجاد نکند اما در هنگام کار با دیگر زبان ها به خصوص آلمانی و فرانسوی میتواند مشکل ساز باشد. به مثال زیر دقت کنید :
عبارت ‘résumé’ در زبان آلمانی به معنای curriculum vitae است که اگر تابع زیر روی آن اعمال گردد :
… UPPER(‘résumé’)
حاصل RESUME خواهد بود بمعنای begin again !!!
چرا که در هنگام تبدیل حروف کوچک به بزرگ علایم نحوی آن حذف گردیده اند و معنای لغت بکلی دگرگون شده است.
پرهیز از اعمال توابع بر روی ستونها
Microsoft و Oracle به شدت توصیه دارند که تا حد ممکن از اعمال توابع بر روی ستونها خودداری نمایید مثلا به جای عبارت زیر :
… WHERE column1 = ‘SMITH’
OR column1 = ‘Smith’
از این عبارت استفاده نمایید :
… WHERE column1 = ‘SMITH’
OR (‘SMITH’ <> ‘Smith’ AND column1 = ‘Smith’)
که باعث می شود ارجاع به ستون column1 و بازیابی مقدار آن حداقل شود که همین کار باعث بالا رفتن سرعت پردازش میگردد