Structured Query Language (SQL) یک زبان برنامه نویسی برای اهداف خاص است که برای ذخیره ، دستکاری و بازیابی داده ها از پایگاه داده استفاده می شود.
SQL در بسیاری از سیستم های پایگاه داده رابطه ای از جمله MySQL ، Postgres ، Oracle ، SQL Server و غیره مورد استفاده قرار گرفته است.
با استفاده از عبارات SQL ، توسعه دهندگان به راحتی عملیات مختلف پایگاه داده عملکردی مانند ایجاد ، به روز رسانی و حذف داده ها را انجام می دهند.
هر چقدر حجم داده ها رشد می کنند و فناوری به طور فزاینده ای پیچیده می شود ، بهینه سازی مناسب پایگاه داده های MySQL برای ارائه تجربه کاربر نهایی و کاهش هزینه های زیرساخت از اهمیت بیشتری برخوردار است.
ابزار تنظیم عملکرد MySQL می تواند به متخصصان پایگاه داده کمک کند تا به سرعت گلوگاه ها (Bottleneck) را شناسایی کرده ، از طریق مرور درخواست های کوئری ، عملیات ناکافی را هدف قرار دهند و هرگونه حدس زدن را از بین ببرند.
مزایای تنظیم عملکرد MySQL
مزیت عمده شناسایی عامل موثر برا عملکرد پایگاه داده به شما این امکان را می دهد که با اندازه مناسب سرورهای خود از تهیه بیش از حد و کاهش هزینه جلوگیری کنید.
همچنین به شما این اجازه را می دهد که آیا انتقال ذخیره داده یا اضافه کردن ظرفیت سرور باعث بهبود عملکرد می شود یا خیر و اگر چنین است ، چقدر خواهد بود.
بهینه سازی عملکرد پایگاه داده MySQL و درخواست های مرتبط با چالش های کمی همراه نیست.
با این حال ، هنگامی که پایگاه داده به درستی تنظیم شود ، نتایج عملکرد ارزشمندی با عملکرد عالی ارائه می دهد.
این نه تنها بار کار ناخواسته را کاهش می دهد بلکه پایگاه داده MySQL را برای بازیابی سریعتر داده ها بهینه می کند.
و اما چه نکاتی را باید برای بهینه سازی رعایت کنیم؟
ما را دنبال کنید با شرح نکات در ادامه :
با روش های توصیه شده بهینه کوئری MySQL،کوئری ها را بهینه کنید
برای تنظیم عملکرد MySQL و بهینه سازی سرعت پایگاه داده ، بهترین روش ها را دنبال کنید.
اول از همه ، از ایندکس شدن همه ستون های استفاده شده در WHERE ، JOIN ، ORDER BY و GROUP BY اطمینان حاصل کنید.
WebSphere Commerce بر تقویت استفاده از ایندکس ها برای افزایش عملکرد SQL تأکید می کند.
زیرا ایندکس سازی نامناسب کوئری های SQL می تواند باعث اسکن جدول شود که در نهایت منجر به مشکلات قفل شدن و سایر موارد می شود.
بنابراین ، به شدت توصیه می شود که تمام ستون های اصلی را ایندکس گذاری کنید تا پایگاه داده بتواند بهینه سازی پرس و جو MySQL را تجربه کند.
از استفاده توابع داخل کوئری جلوگیری کنید
بانک اطلاعاتی یا دیتابیس،در صورت استفاده از تابع در کوئری،خاصیت ایندکس را از ستون حذف میکند و تاثیری در عملکرد بهتر نخواهید داشت.
برای نمونه :
SELECT * FROM TABLE1 WHERE UPPER(COL1)='ABC'
به دلیل استفاده از UPPER ، پایگاه داده از ایندکس در COL1 استفاده نمی کند.
اگر راهی برای جلوگیری از این عملکرد در SQL وجود ندارد ، باید یک ایندکس مبتنی بر عملکرد جدید ایجاد کنید یا برای بهبود عملکرد باید ستون های سفارشی را در پایگاه داده ایجاد کنید.
استفاده از wildcard (٪) در ابتدای شرط ها خودداری کنید
کوئری شبیه به 'abc%' کل جدول را اسکن خواهید کرد
برای نمونه :
SELECT * FROM TABLE1 WHERE COL1 LIKE '%ABC
در بیشتر موارد استفاده از این قابلیت،محدودیت کارایی به دنبال خواهد داشت.
از ستون های غیر قابل استفاده در کوئری SELECT استفاده نکنید
به جای استفاده از "* SELECT " ، همیشه ستون ها را در قسمت SELECT برای بهبود عملکرد MySQL مشخص کنید.
از آنجا که ستون های غیرضروری باعث ایجاد بار اضافی بر روی پایگاه داده می شوند ، عملکرد آن و همچنین کل روند سیستماتیک را کند می کنند.
بجای outer join از inner join استفاده کنید
فقط در صورت لزوم از outer join استفاده کنید.
استفاده از آن نه تنها عملکرد پایگاه داده را محدود می کند بلکه گزینه های بهینه سازی کوئری MySQL را محدود می کند ، در نتیجه اجرای عبارات SQL کندتر می شود.
از DISTINCT و UNION فقط زمان مورد نیاز استفاده کنید
استفاده از عملگرهای UNION و DISTINCT بدون هیچ هدف عمده ای باعث مرتب سازی ناخواسته و کندی اجرای SQL می شود.
به جای UNION ، استفاده از UNION ALL کارایی بیشتری را در روند کار ایجاد می کند و دقیق تر عملکرد MySQL را بهبود می بخشد.
اگر انتظار دارید نتیجه مرتب شده ای را بدست آورید ، دستور ORDER BY در SQL اجباری است
کلمه کلیدی ORDER BY مجموعه نتایج را در ستون های از پیش تعریف شده مرتب می کند.
اگرچه این بیانیه برای سرپرست پایگاه داده برای بدست آوردن داده های مرتب شده مزیتی به همراه دارد ، اما در اجرای SQL تأثیر کمی نیز بر عملکرد دارد.
از آنجا که کوئری ابتدا نیاز به مرتب سازی داده ها برای تولید مجموعه نتیجه نهایی دارد،باعث اجرای یک عملیات پیچیده در اجرای SQL می شود.
از MySQL به عنوان Queue استفاده نکنید
صف ها می توانند عملکرد پایگاه داده شما را از هسته اصلی تحت تأثیر قرار دهند و می توانند بدون اطلاع شما در پایگاه داده های برنامه شما وارد شوند.
به عنوان مثال ، اگر در حال تنظیم وضعیتی برای مورد خاصی هستید تا "فرآیند مربوطه" بتواند به آن دسترسی پیدا کند ، ناخواسته یک صف ایجاد می کنید. آنچه انجام می دهد این است که باعث افزایش زمان بار اضافی برای دسترسی به منبع می شود بدون هیچ دلیل عمده ای.
به دو دلیل عمده صف ایجاد مشکل می کند.
آنها حجم کار شما را به صورت Serial تنظیم می کنند ، مانع از تکمیل وظایف به طور Parallel می شوند و اغلب منجر به جدولی می شوند که حاوی کار در حال انجام و همچنین داده های تاریخی مشاغل قبلاً انجام شده است.
این نه تنها تأخیر را به برنامه می افزاید بلکه مانعی در تنظیم عملکرد MySQL ایجاد می کند.
چهار منبع اساسی را بفهمید
برای ایجاد توابع پایگاه داده به چهار منبع اساسی نیاز دارید.
پردازنده ، دیسک ، حافظه و شبکه.
اگر هرکدام از اینها به درستی کار نکند ، در نهایت بر روی سرور پایگاه داده تأثیر می گذارد و عملکرد خوبی ندارد.
برای درک صحیح منابع اساسی ، باید بر روی دو زمینه خاص یعنی انتخاب سخت افزار مناسب و عیب یابی با آن تمرکز کنید.
همیشه هنگام انتخاب سخت افزار برای پایگاه داده MySQL از استفاده از اجزای عملکرد همه جانبه اطمینان حاصل کنید.
نه تنها بهترین ها را در میان Stacks انتخاب کنید بلکه اطمینان حاصل کنید که تعادل مناسبی بین آنها وجود داشته باشد.
ما اغلب دیده ایم که سازمان ها تمایل به انتخاب سرورهایی با پردازنده سریع و دیسک های بزرگ دارند ، اما با حافظه گرسنه یا starved memory اشتباه می کنند که در نهایت باعث از بین رفتن عملکرد می شود.
در برخی از سناریوها ، اضافه کردن حافظه برای بهبود عملکرد هنگام اندازه گیری بسیار مهم می شود.
به نظر می رسد مقداری متناقض به نظر می رسد ، اما در بیشتر موارد ، استفاده بیش از حد از دیسک ها مستقیماً بر عملکرد پایگاه داده تأثیر می گذارد.
در مورد عیب یابی ، همیشه عملکرد هر چهار منبع اساسی را بررسی کنید.
اعتبار سنجی عملکرد آنها را مطابق نیازهای بهبود تأیید کنید.
با در نظر گرفتن منظم این ممیزی ، مشکلات اساسی عمده به سرعت حل می شود.
کوئری های صفحه بندی شده
برنامه هایی که به صورت صفحه ای ایجاد می شوند سرور را پایین می آورند.
برای نشان دادن نتایج یک صفحه،با پیوند به صفحه بعد،معمولا از گروه بندی و مرتب سازی و LIMIT استفاده میشود که فشار زیادی به سرور میاورد پس صفحه بندی را کنار بگذارید.
می توانید بهینه سازی ها را در رابط کاربری خود پیدا کنید.
به جای نشان دادن تعداد دقیق صفحات در نتایج و پیوندها به یک صفحه ، می توانید پیوندی به صفحه بعدی نشان دهید. همچنین می توانید از رفتن افراد به صفحات بی ربط جلوگیری کنید.
در قسمت query ، به جای استفاده از LIMIT با offset، می توانید یک ردیف بیشتر از مقدار مورد نیاز خود انتخاب کنید ، و هنگامی که کاربر روی پیوند "صفحه بعدی" کلیک کرد ، می توانید آن ردیف نهایی را به عنوان نقطه شروع مجموعه نتایج بعدی تعیین کنید .
به عنوان مثال ، اگر کاربر صفحه ای با سطرهای 101 تا 120 را مشاهده کرده باشد ، باید سطر 121 را نیز انتخاب کنید. برای ارائه صفحه بعدی ، می خواهید از سرور ردیف های بزرگتر یا مساوی 121 ، حد 21 را جستجو کنید.
بهینه سازی SubQuery ها در MySQL
مهمترین توصیه ای که شده است در مورد subquery ها این است که شما حداقل در نسخه های فعلی MySQL ، join را در صورت امکان را ترجیح دهید.
subquery ها موضوع حاشیه سازی برای بهینه ساز ها بوده و نسخه های آینده MySQL ممکن است دارای بهینه سازی های subquery بیشتری باشند.
بررسی کنید که کدام یک از بهینه سازی ها در نسخه منتشر شده وجود دارد و اینکه چه مقدار تفاوت ایجاد می کنند.
حرف اصلی در اینجا این است که استفاده از join ها در mysql ناخوشایند هم نیست.
حافظه پنهان Mysql
یکی از مهمترین جنبه های اندازه گیری عملکرد ، ذخیره مطالب است.
MySQL امکان کش کوئری های پایگاه داده را فراهم می کند که میتوانید دستور SELECT و نتیجه بازیابی شده را ذخیره می کند.
از این رو ، هر زمان که یک پایگاه داده تکراری ایجاد می کنید ، با MySQL cache query cache ارتباط میگیرید و به شما پاسخ می دهد و نتیجه را از cache نشان می دهد و هیچ ارتباط اضافی در کار نیست.
به این ترتیب می توانید فرآیند بهینه سازی حافظه پنهان MySQL را به حداکثر برسانید.
برای راه اندازی حافظه پنهان MySQL ، باید چند تنظیم به MySQL اضافه کنید. اول از همه ، شما باید با دستور زیر بررسی کنید که query cache موجود است یا نه:
mysql> SHOW VARIABLES LIKE 'have_query_cache';
این نتیجه را نشان می دهد ، بله. این بدان معنی است که حافظه پنهان MySQL به خوبی کار می کند.
- +------------------+-------+
- | Variable_name | Value |
- +------------------+-------+
- | have_query_cache | YES |
- +------------------+-------+
اکنون می توانید اندازه و نوع query cache را تنظیم کنید.
به یاد داشته باشید حداقل اندازه پیش فرض 40KB است.
حداکثر اندازه می تواند 32 مگابایت باشد.
با استفاده از دستور زیر می توانید MySQL query_cache_size را تنظیم کنید:
mysql> SET GLOBAL query_cache_size = 40000;
نوع query cache می تواند رفتار همه conenctions را تعیین کند.
همچنین می توانید query cache را برای کوئری ها با کد زیر غیر فعال کنید:
mysql> SET SESSION query_cache_type = OFF;
همچنین می توانید مقادیری مانند 0،1 و 2 را برای تنظیم وضعیت اتصال تنظیم کنید.
از Memcached برای MySQL Caching استفاده کنید
Memcached یک سیستم ذخیره سازی حافظه توزیع شده است.
با ذخیره کردن شی پایگاه داده در حافظه پویا ، سرعت وب سایت های دارای پایگاه داده پویای بزرگ را افزایش می دهد تا فشار هر سرور را کاهش دهد ، هر زمان که یک منبع داده خارجی درخواست خواندن کند.
یک لایه Memcached تعداد دفعات درخواست پایگاه داده را کاهش می دهد.
Memcached مقادیر (v) را با کلید (k) ذخیره می کند و مقادیر (v) را با کلید (k) بازیابی می کند حتی بدون پردازش query های پایگاه داده و شما را از چالش های مزاحم دور میکند.
