[vc_row][vc_column][vc_column_text]
مقدمه
اگر شما یک برنامه نویس حرفه ای هستید حتما می دانید که کوئری هایی که می نویسید باید بهینه باشند. این کار هم سرعت سیستم را افزایش می دهد و هم منابع کمتری از سرور را اشغال می کند. در این مقاله قصد داریم درباره بهینه سازی کوئری های MySQL آموزش دهیم.[/vc_column_text][ultimate_spacer height=”20″][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]
بهینه سازی کوئری های MySQL؛ استفاده از کوئری ها
از امکانات موجود در دیتابیس MySQL که سرعت دریافت اطلاعات را افزایش می دهد Query Cache است. کارکرد آن به این صورت است که نتیجه دستور Select در دیتابیس ذخیره می شود و در صورت درخواست مجدد، بدون اجرای مجدد دستور، نتیجه به سرعت ارسال می شود. مزیت آن این است که با عدم درگیری منابع و MySQL و عدم اجرای مجدد، سرعت بسیار افزایش می یابد.
همچنین به این دلیل که Query Cache از RAM به جای هارد استفاده می کند و سرعت خواندن اطلاعات در آن بسیار بالاتر از هارد است، سرویس MySQL سرعت بسیار بالایی خواهد داشت. حال می خواهیم مورد گفته شده را در یک مثال نشان بدهیم. مثلا یک سایت وردپرس را درنظر بگیرید که درخواست های زیادی برای تغییر در جداول دارد. می خواهیم در یک دیتابیس بررسی کنیم که فعال بودن و یا غیر فعال بودن Query Cache چه تاثیری روی سرعت انجام دستورات دارد و کارکرد آن را در عمل مشاهده کنیم. برای ادامه مثال به یک سرور مجازی که لینوکس روی آن نصب شده باشد و روی لینوکس، MySQL موجود باشد نیاز داریم. همچنین در نظر داشته باشید که دیتابیس رمز نداشته باشد.[/vc_column_text][ultimate_spacer height=”20″][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]
مراحل بررسی تاثیر Query Cache برای بهینه سازی کوئری های MySQL
در ادامه آموزش بهینه سازی کوئری های MySQL، مراحل مثالی که قبل تر گفته شد را با توضیحات مربوط به آن بیان می کنیم.
در مرحله اول باید از پشتیبانی دیتابیس MySQL از قابلیت Query Cache مطمئن شویم. برای این کار با استفاده از SSH به محیط Command Line در لینوکس وارد می شویم و دستورات زیر را اجرا می کنیم.
mysql -u root –p
پس از وارد کردن این دستور، وارد محیط MySQL خواهیم شد و برای بررسی وضعیت پشتیبانی از Query Cache، دستور زیر را تایپ می کنیم:
show variables like ‘have_query_cache’ ;
و در ادامه آن خروجی زیر را خواهیم داشت:
+——————+——-+ | Variable_name | Value | +——————+——-+ | have_query_cache | YES | +——————+——-+ row in set (0.01 sec) 1
در اینجا اگر متغیر have-query-cache، دارای مقدار YES باشد، دیتابیس ما از قابلیت Query Cache پشتیبانی می کند و امکان فعال سازی آن وجود دارد. حال که از وجود این قابلیت بر روی دیتابیس مطمئن شدیم، به ادامه آموزش می پردازیم.[/vc_column_text][ultimate_spacer height=”20″][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]مرحله دوم در این مرحله به بررسی متغیر های مربوط به Query Cache می پردازیم. اما قبل از آن، باید با متغیر های آن آشنا شوید. داشتن آشنایی لازم با این متغیرها سبب می شود که بتوانید بهترین تنظیمات را برای دیتابیس خود انجام دهید و بهینه سازی کوئری های MySQL را به بهترین نحو انجام دهید. برای دسترسی به لیست متغیر های Query Cache در محیطی که قبلا وارد شدیم دستور زیر را وارد کنید:
show variables like ‘query_cache_%’ ;
پس از وارد کردن این دستور، خروجی زیر را خواهیم داشت:
+——————————+———-+ | Variable_name | Value | +——————————+———-+ | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | OFF | | query_cache_wlock_invalidate | OFF | +——————————+———-+ 5 rows in set (0.00 sec)
حال به معرفی این متغیرها می‌پردازیم:
- query_cache_limit: این متغیر، مشخص می کند که فضای مورد نظر برای نتیجه یک کوئری چقدر است. میزان پیش فرض تعیین شده برای آن، یک مگابایت است.
- query_cache_min_res_unit: در دیتابیس MySQL اطلاعات به جای ذخیره شدن در یک فضای بزرگ، در بلاک های کوچک ذخیره می شوند. مقدار این بلاک ها با این متغیر مشخص می شود و مقدار پیش فرض آن 4 کیلوبایت است.
- query_cache_size: با استفاده از این متغیر قادر خواهید بود که کل فضای مربوط به Query Cache بر روی دیتابیس را کنترل کنید. اگر مقدار آن را روی صفر قرار دهید این قابلیت غیر فعال خواهد شد. مقداری که به صورت پیش فرض برای آن تعیین شده است 16 مگابایت است و حداقل فضایی که Query Cache برای فعالیت به آن نیاز دارد و در نتیجه حداقل مقدار این متغیر برای فعالیت Query Cache، 40 کیلو بایت است.
- query_cache_type: این متغیر نوع کوئری هایی که باید Cache شود را تشخیص می دهد. اگر مقدار آن روی 1 قرار داده شود، تمام کوئری ها به جز آن هایی که جز SELECT SQL NO CACHE باشند، Cache می شوند. اگر روی 2 قرار بگیرد فقط کوئری های این دسته Cache می‌شوند و در صورتی که مقدار آن صفر باشد، هیچ کوئری cache نمی‌شود.
- query_cache_wlock_invalidate: این متغیر که در حالت پیش فرض روی OFF قرار دارد، مشخص می کند که کوئری های قفل شده cache می شوند یا خیر.
[/vc_column_text][ultimate_spacer height=”20″][/vc_column][/vc_row][vc_row][vc_column][vc_column_text]مرحله سوم در راستای آموزش بهینه سازی کوئری های MySQL، تا به اینجا متغیر های مربوط به قابلیت Query Cache معرفی شد. اکنون قصد داریم که بازدهی و کارکرد MySQL را بدون استفاده از این قابلیت بررسی کنیم. برای این کار دیتابیسی به نام sample_db ایجاد می کنیم و درون آن جدولی با نام customers و دو فیلد customer_id و customer_name ایجاد خواهیم کرد.
در ابتدا به آموزش ایجاد دیتابیس می پردازیم. برای این کار دستور زیر را وارد کنید:
;Create database sample_db
پس از آن خروجی زیر را خواهیم داشت:
Query OK, 1 row affected (0.00 sec)
حال که دیتابیس ایجاد شد، باید آن را تغییر دهیم تا تغییرات مورد نظرمان روی آن اعمال شود. به این منظور دستور زیر را تایپ می کنیم:
;Use sample_db
و خروجی زیر را خواهیم داشت:
Database changed
حال برای ایجاد جدول و فیلدهای آن دستور زیر را اجرا می کنیم:
Create table customers (customer_id INT PRIMARY KEY, customer_name ;VARCHAR(50) NOT NULL) Engine = InnoDB
و این خروجی را خواهیم داشت:
Query OK, 0 rows affected (0.01 sec)
حال که جدول و فیلدهای آن هم ساخته شد، در ادامه آموزش بهینه سازی کوئری های MySQL، دستورات زیر را به ترتیب وارد کرده و ادامه مراحل را طی کنید.
Insert into customers(customer_id, customer_name) values (‘1’, ‘JANE DOE’); Insert into customers(customer_id, customer_name) values (‘2’, ‘JANIE DOE’); Insert into customers(customer_id, customer_name) values (‘3’, ‘JOHN ROE’); Insert into customers(customer_id, customer_name) values (‘4’, ‘MARY ROE’); Insert into customers(customer_id, customer_name) values (‘5’, ‘RICHARD ROE’); Insert into customers(customer_id, customer_name) values (‘6’, ‘JOHNNY DOE’); Insert into customers(customer_id, customer_name) values (‘7’, ‘JOHN SMITH’); Insert into customers(customer_id, customer_name) values (‘8’, ‘JOE BLOGGS’); Insert into customers(customer_id, customer_name) values (‘9’, ‘JANE POE’); Insert into customers(customer_id, customer_name) values (’10’, ‘MARK MOE’);
این دستورات، خروجی زیر را به ما می دهند:
Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.00 sec) …
ادامه مرحله سوم
حال باید امکان Profiler برای امکان ارزیابی کوئری ها فعال شود. برای این کار مطابق مراحل زیر مقدار آن را روی 1 قرار بدهید
SET profiling = 1;
و خروجی
Query OK, 0 rows affected, 1 warning (0.00 sec)
را خواهیم داشت.
حالا کوئری را وارد می کنیم تا اطلاعات جدول را دریافت کند.
Select * from customers
خروجی ==>
+————-+—————+ | customer_id | customer_name | +————-+—————+ | 1 | JANE DOE | | 2 | JANIE DOE | | 3 | JOHN ROE | | 4 | MARY ROE | | 5 | RICHARD ROE | | 6 | JOHNNY DOE | | 7 | JOHN SMITH | | 8 | JOE BLOGGS | | 9 | JANE POE | | 10 | MARK MOE | +————-+—————+ 10 rows in set (0.00 sec)
حالا با اجرای دستور زیر، بازدهی کوئری را مشاهده می کنیم:
SHOW PROFILES
و برای خروجی آن خواهیم داشت:
+———-+————+————————-+ | Query_ID | Duration | Query | +———-+————+————————-+ | 1 | 0.00044075 | Select * from customers | +———-+————+————————-+ 1 row in set, 1 warning (0.00 sec)
دقت کنید که زمان نشان داده شده در سیستم های مختلف متفاوت است. این زمان را به خاطر داشته باشید تا پس از فعال سازی Query Cache، به مقایسه آن بپردازید. حال با اجرای دستور quit از محیط Command Line خارج شوید تا به ادامه کار بپردازیم.
مرحله چهارم
همان طور که دیدید در مرحله قبلی عملکرد MySQL را بدون استفاده از قابلیت Query Cache بررسی کردیم. حال می خواهیم با ویرایش فایل ایجاد شده، تاثیر این قابلیت در بهینه سازی کوئری های MySQL را بررسی کنیم. در ابتدا در محیط Command Line دستور زیر را اجرا کنید:
vi /etc/mysql/my.cnf
حال دستورات زیر را در فایل باز شده وارد کنید:
[mysqld] query_cache_type=1 query_cache_size = 10M query_cache_limit=256K
در اینجا با تغییر مقدار متغیر query_cache_type به 1، قابلیت Query Cache فعال خواهد شد و پس از آن 10 مگابایت از حافظه رم را به آن اختصاص می دهیم. پس از انجام تغییرات مورد نظر فایل را ذخیره کرده و از محیط خارج می شویم. پس از آن MySQL را با دستور زیر مجددا راه اندازی می‌کنیم:
systemctl restart mysql
حال برای بررسی تفاوت ایجاد شده مانند حالت قبل Profiler را فعال می کنیم و بازدهی سیستم را در این حالت مشاهده می کنیم. با اجرای دستورات خروجی زیر به ما نشان داده می شود که همان طور که مشاهده می کنید در این مثال چقدر زمان انجام کار کاهش یافته است.
+———-+————+————————-+ | Query_ID | Duration | Query | +———-+————+————————-+ | 1 | 0.00049250 | Select * from customers | | 2 | 0.00026000 | Select * from customers | +———-+————+————————-+ 2 rows in set, 1 warning (0.00 sec)
[/vc_column_text][ultimate_spacer height=”20″][/vc_column][/vc_row]
