بهینه سازی کوئری های MySQL برای افزایش سرعت و کارایی

بهینه سازی کوئری

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

دیتابیس MYSQL

پیش نیازها

سرور پایگاه داده MySQL.

یک کاربر MySQL که قادر به اجرای دستورات root باشد.

 

نکته 1 : فهرست تمام ستونهای مورد استفاده توسط where ، order by و group by

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

ایندکس های MySQL ممکن است فضای بیشتری را به خود اختصاص داده و باعث کاهش کارایی در درج (inserts) ، حذف (deletes) و به روز رسانی (updates) شود. اما در صورتی که جدول شما بیش از 10 سطر داشته باشد، باعث می شود زمان اجرای درخواست پرس و جو به میزان قابل توجهی کاهش یابد.

همواره توصیه می شود نمایش داده های MySQL را با سناریو بدترین حالت(worst case scenario) مقدار نمونه از داده ها آزمایش کنید تا تصویری واضح تر از نحوه عملکرد پرس و جو در واقعیت را بدست آورید.

بعنوان نمونه در نظر بگیرید که در حال انجام query SQL زیر از یک پایگاه داده با 500 سطر و بدون ایندکس باشید:

mysql> select customer_id, customer_name from customers where customer_id=’140385′;

کوئری فوق سرور MySQL را مجبور به اسکن کامل جدول (start to finish) برای بدست آوردن نتیجه موردنظر می کند.

خوشبختانه ، MySQL دستور “EXPLAIN” ویژه ای دارد که می توانید در کنار دستورات انتخاب ، حذف ، درج ، جایگزینی و به روزرسانی برای تجزیه و تحلیل نمایش داده های خود استفاده کنید.

اگر SQL فوق را یک بار دیگر با دستور explain انجام دهیم ، تصویر کاملی از آنچه MySQL برای اجرای پرس و جو انجام خواهد داد ، دریافت خواهیم کرد:

همانطور که مشاهده می کنید ، بهینه ساز اطلاعات بسیار مهمی را نشان داده است که می تواند به ما در تنظیم دقیق جدول بانک اطلاعاتی ما کمک کند. اول اینکه روشن است که MySQL اسکن جدول کامل را انجام می دهد زیرا ستون کلیدی “NULL” است. دوم اینکه سرور MySQL به وضوح اعلام کرده است که قصد دارد اسکن کامل را روی 500 ردیف در پایگاه داده ما انجام دهد.

برای بهینه سازی کوئری فوق، ما می توانیم با افزودن index به فیلد customer_id بصورت زیر عمل کنیم:

mysql> Create index customer_id ON customers (customer_Id); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0

در این حالت اگر بار دیگر دستور explain را اجرا کنیم ، نتایج زیر را می گیریم:

از خروجی داده شده مشخص است که سرور MySQL از ایندکس ما (customer_Id) برای جستجوی جدول استفاده می کند. به وضوح می بینید که تعداد ردیف ها برای اسکن 1 میباشد. اگرچه این پرس و جو در یک جدول با 500 رکورد اجرا شده، اما قطعا در جستجوی یک مجموعه داده بزرگ (مانند جدول با 1 میلیون سطر) ایندکس ها بسیار مفید واقع می شوند.

برای اطلاع از کاربرد و عملکرد SQL Server بهتر است مقاله ای که در مرواهاست قرار داده‌ایم را مطالعه کنید.

نکته 2 : دستور های مشابه را با Union Clause بهینه کنید.

بعضی اوقات ، ممکن است بخواهید با استفاده از عملگر مقایسه (or) در فیلدها یا ستونهای مختلف در یک جدول خاص، استفاده نمایید. هنگامی که از or بیش از حد در عبارت where استفاده می شود ، ممکن است بهینه ساز MySQL به اشتباه یک اسکن کامل جدول را برای بازیابی یک رکورد انتخاب کند.

union clause می تواند باعث شود query سریعتر اجرا شود، به خصوص اگر شما یک ایندکس داشته باشید که یک طرف پرس و جو را بهینه کند و یک ایندکس دیگر برای بهینه سازی طرف دیگر داشته باشد.

به عنوان مثال ، مورد زیر را در نظر بگیرید که کوئری با ‘first_name’ و ‘last_name’ ایندکس شده است:

mysql> select * from students where first_name like ‘Ade%’ or last_name like ‘Ade%’ ;

جستجوی بالا در مقایسه با عبارت زیر که از یک اپراتور اتحادیه(union) استفاده می کند، بسیار کندتر می باشد. کوئری زیر نتایج 2 پرس و جو سریع جداگانه را که از ایندکس ها استفاده می کنند، را باهم ادغام کند.

mysql> select from students where first_name like ‘Ade%’ union all select from students where last_name like ‘Ade%’ ;

نکته 3: از دستورات Like جهت Leading Wildcards استفاده نکنید.

هنگامی که leading wildcard در یک پرس و جو وجود دارد، MySQL قادر به استفاده از ایندکس ها نیست. اگر کوئری فوق را در جدول دانش آموزان ببرید ، باعث می شود كه MySQL اسكن جدول را كامل انجام دهد، حتی اگر فیلد “first_name” را در جدول دانش آموزان ایندکس كنید.

mysql> select * from students where first_name like ‘%Ade’ ;

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

نکته 4 : از جستجوی Full-Text MySQL استفاده کنید.

اگر با شرایطی روبرو هستید که نیاز به جستجوی داده ها با استفاده از wildcards هستید و نمی خواهید پایگاه داده خود را تحت الشعاع قرار دهید ، می توانید از جستجوی متن کامل MySQL (FTS) استفاده کنید، زیرا آن‌ها خیلی سریعتر از پرس و جوها با استفاده از کاراکترهای wildcard می باشد.

علاوه بر این ، full-text search (FTS) هنگام جستجوی یک بانک اطلاعاتی عظیم می تواند نتایج بهتری و مرتبط را به همراه آورد.

برای اضافه کردن فهرست جستجوی متن کامل به جدول نمونه دانش آموزان ، می توان از دستور زیر MySQL استفاده کرد:

mysql>Alter table students ADD FULLTEXT (first_name, last_name); mysql>Select * from students where match(first_name, last_name) AGAINST (‘Ade’);

در مثال بالا، ستون هایی را که می خواهیم که در آن‌ها first_name و last_name با کلید واژه “Ade” مطابقت داشته باشد.

اگر از بهینه ساز(optimizer) برای اجرای درخواست پرس و جوی فوق استفاده کنیم ، نتایج زیر را کسب می کنیم:

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

نتیجه گیری

در این راهنما ، به شما نشان داده ایم كه چگونه سرور MySQL خود را از نظر سرعت و كارآیی بهینه كنید.

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

“در صورتی که مقاله فوق مورد توجه شما قرار گرفته پیشنهاد میکنیم مقالات “تفاوت هاست لینوکس و هاست ویندوز” و ”علت بلاک شدن IP در فایروال سرور” در بلاگ مرواهاست مطالعه فرمایید”

 

فیس بوک
توییتر
لینکدین
تامبلر
پین ترست
رددیت
ایمیل

11 مهر 1403

در 12:12

ارور 404 یکی از رایج‌ترین خطاهای اینترنتی است که کاربران در هنگام بازدید از صفحات وب با آن مواجه می‌شوند. این ارور نشان‌دهنده این است که صفحه‌ای که کاربر به ...

9 مهر 1403

در 22:09

ارور 400 چیست؟ در دنیای وب، ممکن است با خطاهای مختلفی مواجه شویم که یکی از رایج‌ترین آن‌ها خطای 400 است. این خطا زمانی نمایش داده می‌شود که سرور درخواست ارسا...

4 مهر 1403

در 21:30

ارورهای 408 Request Timeout یکی از خطاهای رایجی هستند که هنگام بازدید از وب‌سایت‌ها ممکن است با آن‌ها مواجه شوید. این ارور زمانی رخ می‌دهد که سرور در مدت زما...

2 مهر 1403

در 12:12

خطای 500 وردپرس یکی از رایج‌ترین مشکلاتی است که کاربران هنگام بازدید از وب‌سایت‌های وردپرسی ممکن است با آن مواجه شوند. این خطا معمولا نشان‌دهنده یک مشکل جدی ...

28 شهریور 1403

در 12:12

یکی از موضوعات مهم در طراحی سایت استفاده از هاست (Host) است. هاست فضایی است که برای ذخیره داده‌های سایت استفاده می‌شود. معمولا خرید این فضا هزینه‌بردار است. ...

26 شهریور 1403

در 21:51

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

دیدگاه‌ خود را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *

این سایت از اکیسمت برای کاهش هرزنامه استفاده می کند. بیاموزید که چگونه اطلاعات دیدگاه های شما پردازش می‌شوند.