مشاهده خبر بازگشت به لیست اخبار

21 راه برای جست و جوی سریعتر در SQL

نوشته شده توسط: مهرنوش عبدعلی
در تاریخ:

21 راه برای جست و جوی سریعتر در SQL

همه خواستار پرس و جوی سریعتر در پایگاه داده هستند، توسعه دهندگان SQL و مدیران پایگاه داده می توانند به روش های آزمایش شده ی بسیاری، به این هدف برسند. متأسفانه، هیچ یک از روش ها کاملا بدون خطا نیستند. اما حتی اگر پاسخ درستی برای تعمیم به همه ی پرس و جوها وجود نداشته باشد، برای تسهیل در این امر باید و نبایدهای اثبات شده ی بسیاری  وجود دارند. در حالی که برخی از آنها مختص RDBMS هستند، اکثر این نکات به هر پایگاه داده ی رابطه ای اعمال می شود.

شما در هر یک از برنامه های SQL Server، Oracle، DB2، Sybase، MySQL یا دیگر پلاتفرم های ارتباطی که برنامه نویسی کنید، هدف یکسانی دارید:

شما می خواهید پایگاه داده، تعداد زیادی از کاربران را به طور همزمان ساپورت کند در حالی که با حداکثر سرعت ممکن در حال انجام جست و جوها و پاسخ به درخواست ها است.  این یعنی باید تداخل ورودی و خروجی ها و ترافیک شبکه را به حداقل برسانید، در حالی که بهینه سازی فضا و مدیریت منابع را کنترل می کنید.

تنظیم یک پایگاه داده، به دانش و هنر نیاز دارد. در اینجا 21 قاعدۀ آزمایش شده و کاربردی برای ساخت پایگاه داده ای سریع تر و کارآمدتر ذکر شده.

1. زمانی که می توانید از اشاره گر ها اجتناب کنید

این را فراموش نکنید. اشاره گرها ها نه تنها دارای مشکل سرعت هستند، بلکه می توانند موجب شوند یک عملیات باعث توقف عملیات دیگر برای مدت زمان طولانی تر از حد نیاز شود. این به طور قابل توجهی باعث کاهش همروندی در سیستم می شود.

2. وقتی نمی توانید از اشاره گر ها اجتناب کنید، از جداول موقت استفاده کنید

ممکن است نیاز به استفاده از اشاره گر داشته باشید. در این موارد، بهتر است عملیات اشاره گر را روی یک جدول موقت به جای یک جدول دائم انجام دهید. یک پیغام تکمیلی در برابر جدول دائم خواهید داشت که بسیار کوچکتر است که فقط برای مدت کوتاهی قفل نگه داشته می شود و همروندی را به طور قابل توجهی افزایش می دهد.

3. از جداول موقت هوشمندانه استفاده کنید.

می توانید جداول موقت را در برخی  شرایط دیگر نیز استفاده کنید. به عنوان مثال، اگر می خواهید یک جدول کوچک را به یک جدول بزرگ پیوند بزنید و در آن جدول بزرگ شرطی وجود دارد، می توانید عملکرد را به این صورت بهبود بخشید که اطلاعاتی که از جدول بزرگ نیاز دارید بیرون آورید و در یک جدول موقت قرار دهید و جدول کوچک را به آن جدول موقت پیوند بزنید. این کار به میزان قابل توجهی قدرت پردازش مورد نیاز را کاهش می دهد و می تواند مفید باشد اگر شما چندین پرس و جو را در این فرایند داشته باشید که باید به جدول مشابه اضافه شود.

4. اطلاعات خود را از قبل پایه ریزی کنید

این یک تکنیک قدیمی است که اغلب نادیده گرفته می شود. اگر گزارش یا رویه هایی دارید که باید مشابها به جداول بزرگ اضافه شوند، با اتصال جداول در طول زمان و ذخیره ی آنها در یک جدول، داده ها را از قبل پایه ریزی کنید ، حال حاضر گزارش ها می توانند مبنی بر جدول از پیش تهیه شده اجرا شوند و از اتصالات بزرگ جلوگیری می شود.

ممکن است همیشه قادر به استفاده از این تکنیک نباشید، اما در اکثر محیط ها، جداول عمومی وجود دارند که همیشه در حال پیوند داده شدن هستند. دلیلی وجود ندارد که آنها نتوانند از قبل تنظیم شوند، و این یک راه عالی برای ذخیره منابع سرور است.

 

5- viewهای تودرتو را کاهش دهید

Viewها برای محو کردن جست و جوهای بزرگ کاربران بسیار عالی هستند، اما زمانی که یک view  را  در داخل view دیگر که در داخل یکی دیگر از viewها قرار دارد ایجاد می کنید (و ساخت viewهای تودرتو را ادامه می دهید)، می توانید منجر به تاخیر عملکرد شدید شوید. Viewهای بیش از حد می توانند مقدار زیادی از داده ها را برای هر پرس و جو بازگردانند، یا حتی بدتر از آن ، بهینه ساز از جست و جو دست می کشد و پاسخی نمی دهد.

باز کردن viewهای تودرتو، می تواند زمان پاسخ هر جست و جو را از دقیقه به ثانیه کاهش دهد.

6- استفاده از CASE به جای Update

این سناریو را در نظر بگیرید : شما اطلاعات را در یک جدول موقت وارد می کنید و می خواهید اگر مقدار دیگری وجود دارد، مقدارخاصی نمایش داده شود. شاید شما از جدول مشتری ها واکشی می کنید و می خواهید تمام کسانی که خرید بالای 100هزار دلار داشته اند با عنوان "در اولویت" برچسب گذاری شوند. بنابراین داده ها را در جدول وارد می کنید و دستور update را اجرا میکنید تا ستون "رتبه مشتریان" برای گزینه ی "در اولویت" برای مشتریان مورد نظر تنظیم شود.

به نظر منطقی می آید، درسته؟ اما مشکل این است که دستور update وارد شده، که به این معنی است که برای هر ارسالی به جدول، دستور باید دو بار نوشته شود. البته راه حل استفاده از دستور case خطی در جست و جوی SQL است. این دستور هر سطر را برای شرایط مقدار سفارش مشتری و برچسب "در اولویت" قبل از اینکه در جدول نوشته شود، بررسی می کند. افزایش کارایی غافلگیر کننده است.

 

7- به جای توابع scalar ، از توابع table-valued  استفاده کنید

یک نکته ی حرفه ای : هنگام استفاده از یک عملکرد اسکالر در لیست SELECT یک پرس و جو می توانید عملکرد را با تبدیل آن به یک تابع table-valued  و استفاده از CROSS APPLY  در پرس و جو سریعتر کنید،  که می تواند تقاضاهای شما را به نصف برساند.

 

8. استفاده از پارتیشن ها در SQL Server

کاربران SQL Server Enterprise می توانند از ویژگی های پارتیشن بندی خودکار موتور داده برای افزایش سرعت استفاده کنند. در SQL Server، حتی جداول ساده به عنوان پارتیشن های مجزا ایجاد می شوند، که بعدا می توانید آنها را به اندازه ی نیاز تقسیم کنید. بنابراین هنگامی که نیاز دارید مقدار زیادی داده را بین جداول جا به جا کنید، می توانید از دستور SWITCH به جای INSERT و DELETE استفاده کنید. از آنجا که به جای حذف و وارد کردن مقادیر زیادی داده بین جداول، فقط ابردادها را برای یک جدول واحد تغییر  داده اید، ، عملیات زمان کمتری خواهد داشت.

 

9. حذف و به روز رسانی در دسته  -batch-

حذف یا به روز رسانی مقادیر زیادی از داده ها از جداول بزرگ می تواند یک کابوس باشد. مشکل این است که هر دو این رویه ها به عنوان تراکنش واحد اجرا می شوند و اگر نیاز به توقف آنها داشته باشید یا اگر عملیات دیگری در حین انجام آنها صورت گیرد، سیستم باید کل تراکنش را به اول بازگرداند. این بسیار زمانگیر است همچنین رویه های دیگر را در مدت زمان کار خود متوقف می کند، که بخش زیادی از ظرفیت سیستم را اشغال می کند.

راه حل این است که حذف یا به روز رسانی در دسته های کوچکتر صورت گیرد. اگر تراکنش متوقف شود، فقط تعداد کمی از ردیف هایی که طی شده را برمیگردد، بنابراین پایگاه داده سریعتر راه اندازی می شود. در این روش  همروندی  افزایش می یابد.

10. وقت صرف کنید

برخی از توسعه دهندگان فکر می کنند که این عملیات حذف و به روز رسانی همه باید در یک روز تکمیل شود. این همیشه درست نیست، به ویژه اگر شما در حال بایگانی کردن باشید. شما می توانید این عملیات را تا زمانی که نیاز دارید گسترش دهید و داشتن دسته های کوچکتر به شما کمک کنند. اگر می توانید این عملیات متمرکز را در زمان طولانی تر انجام دهید، زمان اضافی را صرف کنید و سیستم خود را از کار نیندازید.

 

11. ORM  ها را کنار کنید

ORMها (Object-relational mappers)  برخی از بدترین کد های موجود را تولید می کند و مسئول بسیاری از مشکلات عملکردی هستند. اما اگر نمیتوانید از آنها دوری کنید، با نوشتن رویه های ذخیره شده خودتان این مشکلات را کاهش دهید تا ORM به جای اینکه خود پرس و جوها را بسازد ، فقط رویه های نوشته شده  را فراخوانی کند.

 

12.  هر زمان که می توانید از رویه های ذخیره شده استفاده کنید

در راه رسیدن به کدهای مناسبتر، رویه های ذخیره شده نیز دارای مزایایی است. آنها تا حد زیادی ترافیک را کاهش می دهند، زیرا فراخوانی ها کوتاه تر می شوند. آنها با استفاده از ابزارهایی نظیر Profiler ساده تر می شوند، که به شما امکان می دهد تا عملکرد و مشکلات بالقوه را سریعتر شناسایی کنید. شما می توانید آنها را در یک راه سازگار تر توصیف کنید، به این معنی است که شما احتمال بیشتری برای استفاده مجدد از برنامه های اجرایی خود دارید و حسابرسی و بررسی نها آسانتر از  درخواست های adhoc است.

درست است که بسیاری از برنامه نویسان .NET معتقدند منطق کسب و کار متعلق به قسمت کاربران برنامه است نه پایگاه داده، اما آنها اشتباه می کنند.

13- اجتناب از دو برابر شدن

گاهی اوقات با استفاده از رویه های ذخیره شده می تواند منجر به دو باره کاری  شود - اجرای درخواست های جداگانه در جداول بزرگ، قرار دادن آنها در جداول موقت  و سپس پیوند دادن جداول با هم. این می تواند سربار زیادی بر عملکرد داشته باشد. بهتر است در زمانی که ممکن باشد، جست وجوی جداول بزرگ فقط یک بار صورت گیرد.

سناریو متفاوت زمانی است که یک زیر مجموعه از یک جدول بزرگ، مورد نیاز چندین مرحله از یک فرایند است، که باعث می شود جدول بزرگ هر بار مورد پرسش و جست و جو قرار گیرد. از این روند سخت، با پرس و جوی هر زیر مجموعه جلوگیری کنید.  سپس مراحل بعدی را به مجموعه داده های کوچکتر خود هدایت کنید.

 

14- تقسیم کارهای بزرگ به کارهای کوچکتر

مدیریت جداول چندگانه در یک تراکنش می تواند همه آنها را تا زمانیکه تراکنش به پایان برسد، متوقف می کند. راه حل این است که این روندها را به چندین روال تقسیم کنیم که هر کدام در یک زمان در یک جدول واحد عمل می کنند. این باعث کاهش میزان مسدود شدن و آزاد کردن جداول دیگر می شود و دیگر عملیات می توانند به کار ادامه دهند.

15. Triggerها را تحمل نکنید

استفاده ازtriggersها می تواند به یک مشکل مشابه منجر شود؛ در این صورت هر چیزی که می خواهید آنها انجام دهند، در همان رویه ی عملیات اصلی انجام می شود. این به این معنی است که تا زمانی که trigger به پایان برسد، می توانید قفل شدن جداول متعدد را متوقف کنید. تقسیم آنها به رویه های جداگانه منابع کمتری را قفل می کند و در بازگشت به عق در صورت لزوم را آسانتر می کند. تا جایی که مقدور است، از triggerها دوری کنید.

 

16- اجتناب از خوشه بندی در GUID

از شناسه های منحصر به فرد جهانی  (Globally Unique Identifiers – GUID)برای سفارش داده های جدول استفاده نکنید. این اعداد 16 بیتی که به صورت تصادفی تولید می شوند می توانند جدول شما را بسیار سریع از بین ببرند. بهتر است اطلاعات خود را با استفاده از مقداری که به طور مستمر افزایش می یابد مانند DATE یا IDENTITY سفارش دهید.

 

17- همه چیز را در جدول شمارش نکنید

 

بگذارید بگوییم شما وجود داده در جدول یا برای یک مشتری را نیاز دارید و بر اساس این یافته می خواهید عملیاتی انجام دهید.

نمیتوانم بگویم که چه تعداد افرادی را دیدم که دستور

SELECT COUNT(*) FROM dbo.T1

را برای بررسی وجود اطلاعات اجرا می کنند :

SET @CT = (SELECT COUNT(*) FROM dbo.T1); 
If @CT > 0 
BEGIN <Do something> 
END

 

این کاملا غیر ضروری است.  اگر می خواهید وجود داده ای را چک کنید ، این کار را انجام دهید:

If EXISTS (SELECT 1 FROM dbo.T1)

BEGIN

<Do something>

END

 

به عبارت دیگر، همه چیز را در جدول نشمارید. فقط ردیف اول را پیدا کنید، SQL Server به اندازه کافی هوشمند است تا از EXISTS به درستی استفاده کند و قسمت دوم کد بسیار سریع باز می شود. هرچه جدول بزرگترباشد، تفاوت استفاده از این روش آشکارتر است.

 

18- برای محاسبه ردیف ها از جدول سیستم استفاده کنید

اگر واقعا نیاز به شمارش ردیف ها ی یک جدول بزرگ دارید، می توانید آن را از جدول سیستم بیرون بکشید. با SELECT  ردیف ها از شاخص های Sys (Sysindexes) تعداد ردیف برای تمام شاخص ها را دریافت خواهید کرد.

و چون شاخص خوشه ای، خود نشان دهنده ی داده ها است، می توانید ردیف های جدول را با اضافه کردن WHERE indid = 1 دریافت کنید.

سپس نام جدول را به سادگی به دست می آورید و تمام. بنابراین پرس و جو نهایی چنین است:

SELECT rows FROM sysindexes WHERE object_name(id) = T1 AND indexid = 1

 

19- فقط تعداد ستون هایی که نیاز دارید بررسی کنید

کدگذاری درخواست ها با SELECT * به جای لیست کردن جداگانه بسیار آسانتر است. مشکل دیگر این است که داده های بیشتری از نیاز شما را ارائه می دهد. یک توسعه دهنده یک درخواست SELECT* در جدولی با 120 ستون و میلیون ها ردیف انجام می دهد، اما با استفاده از تنها سه تا پنج تا از آنها، آنها را بازخوانی میکند. در آن لحظه، شما نه تنها پردازش اطلاعات بیشتر از حد نیازتان دارید، بلکه منابع را از دسترس فرآیندهای دیگر خارج می کنید.

20- بازنویسی پرس و جوها برای جلوگیری از جستجوهای منفی

وقتی به مقایسه ی داده ردیف به ردیف نیاز دارید با درخواستی که نمی تواند از یک index استفاده کند – مانند SELECT* FROM Customers WHERE RegionID <> 3 -  بهتر است query بنویسید که بتواند از index استفاده کند. مانند :

SELECT * FROM Customers WHERE RegionID < 3 UNION ALL SELECT * FROM Customers WHERE RegionID 

 

اگر مجموعه داده های شما بزرگ است، استفاده از index می توانید نسخه اسکن جدول را به مراتب انجام دهید. البته، می تواند بدتر انجام گیرد ، بنابراین قبل از پیاده سازی کنید، آن را آزمایش کنید.

من متوجه شدم که این پرس و جو نکته شماره 13 ("بدون دو برابر شدن")، اما نشان می دهد که قوانین سخت و سریع وجود ندارد. اگر چه ما دوبار در اینجا غوطه ور می کنیم، ما این کار را برای جلوگیری از اسکن هزینه های جدول انجام می دهیم.

 

21- کورکورانه از کدها استفاده مجدد نکنید

کپی کدهای دیگران بسیار آسان است زیرا می دانید که داده هایی را که نیاز دارید را فراخوانی میکند. مشکل این است که در اغلب موارد اطلاعاتی بسیار بیشتر از آنچه شما نیاز دارید را ارائه می دهد و توسعه دهندگان به ندرت آن را از بین می برند، پس در آخر کار آنها با حجم زیادی از داده ها پایان می یابد. شما می توانید کدها را جوری تغییر دهید که فقط به اندازه ی مورد نیازتان داده ارائه دهد.

 

فقط به یاد داشته باشید که تمام این تکنیک ها برای تمام وضعیت ها کارایی نخواهند داشت. شما باید آزمایش کنید تا دریابید کدام روش بهتر کار می کند. به طور کلی، استفاده دقیق از این راهنمایی های SQL باعث افزایش همروندی و سازگاری، عملکرد سریع و سهولت بیشتر - برای DBA ها تا کاربران نهایی - می شود.


هیچ دیدگاهی تاکنون برای این خبر ثبت نشده است.

اولین نفر باشید!
دیدگاه خود را ثبت کنید: