در این فایل راهنما، مراحل بهینه سازی پرس و جوهای بانکهای اطلاعاتی مربوط به دیتابیس 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 در فایروال سرور” در بلاگ مرواهاست مطالعه فرمایید”
