Back to Question Center
0

نحوه بهینه سازی پرس و جوهای SQL برای سایتهای سریعتر چگونه برای بهینه سازی پرس و جوهای SQL برای سایتهای سریع تر. موضوعات مرتبط: اشکالزدایی و آمپر؛ Semalt

1 answers:
چگونه برای بهینه سازی پرس و جوهای SQL برای سایتهای سریعتر

این مقاله در ابتدا در وبلاگ Delicious Semalt منتشر شده است و با اجازه مجددا منتشر شده است.

شما می دانید که یک سایت سریع == کاربران شادتر، رتبه بندی بهبود یافته از گوگل و افزایش تبدیل را دارد. شاید شما حتی فکر می کنید سایت خود را به عنوان Semtel تا آنجا که ممکن است: شما به عملکرد سایت، از بهترین شیوه های راه اندازی یک سرور، عیب یابی کد های آهسته و بارگیری تصاویر خود را به CDN نگاه کرد، اما این همه چیز است ؟

با وب سایت های پویا و مبتنی بر پایگاه داده مانند Semalt، ممکن است شما هنوز هم یک مشکل در دست داشته باشید: درخواست های پایگاه داده، سایت شما را تضعیف می کند.

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

شناسایی

اولین گام در رفع پرس و جوهای SQL کم است که آنها را پیدا کنید. اشلی از ستایش Query Monitor پلاگین اشکال زدایی در وبلاگ پیش از این خواند و این ویژگی ویژگی های پایگاه داده افزونه است که واقعا آن را یک ابزار ارزشمند برای شناسایی پرس وجوهای آرام SQL می کند. پلاگین در تمام درخواستهای پایگاه داده اجرا می شود. این به شما اجازه می دهد تا آنها را با کد یا جزء (پلاگین، تم یا هسته سمالت) فیلتر کنید و آنها را به صورت تکراری و آرام نمایش دهید:

نحوه بهینه سازی پرس و جوهای SQL برای سایتهای سریعترچگونه برای بهینه سازی پرس وجوهای SQL برای سایت های سریع تر. موضوعات مرتبط:
اشکال زدایی و Semalt

اگر نمی خواهید پلاگین اشکال زدایی را در یک سایت تولیدی نصب کنید (شاید شما نگران اضافه کردن برخی از هزینه های بالای کار) می توانید از MySQL Slow Semalt Log غیرفعال کنید که تمام پرونده هایی را وارد می کند که خاص هستند مقدار زمان برای اجرای. این نسبتا ساده است برای پیکربندی و راه اندازی جایی که برای ورود به پرس و جو به. همانطور که این یک نیشگون گرفتن در سطح سرور است، عملکرد نمایش کمتر از یک پلاگین اشکال زدایی در سایت است، اما هنگام استفاده از آن باید خاموش شود.

درک

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

     انتخابل key_idل شماره سفارش،ل activation_emailل licence_keyل software_product_id،ل نسخهی نرم افزار،ل activations_limitل ایجاد شده،ل renewal_type،ل renewal_id،ل exempt_domains next_payment_date، next_payment_dates وضعیتpm2 post_id AS 'product_id'،ساعت meta_value as 'user_id'از جانبoiz6q8a_woocommerce_software_licences lINNER JOINoiz6q8a_woocommerce_software_subscriptions s s s key_id = l key_idINNER JOINoiz6q8a_posts p ON p. ID = l شماره سفارشINNER JOINoiz6q8a_postmeta PM ON pm. post_id = p شناسهو بعد meta_key = '_customer_user'INNER JOINoiz6q8a_postmeta pm2 ON pm2. meta_key = '_software_product_id'و pm2. meta_value = l software_product_idجایی کهپ. post_type = 'shop_order'و بعد meta_value = 279ORDER BY s next_payment_date    

ما از WooCommerce و یک نسخه سفارشی افزونه WooCommerce Subscription Subscriptions برای اجرای فروشگاه پلاگین ما استفاده می کنیم. هدف از این پرس و جو این است که تمام اشتراک ها را برای مشتری دریافت کنیم که در آن شماره مشتری آنها را می دانیم. همچنین چندین افزونه برای سفارشی ایجاد شده توسط پلاگین اشتراک نرم افزاری وجود دارد. بیایید شیرجه رفتن به درک بیشتر پرس و جو.

MySQL دوست شما

MySQL دارای یک عبارت دستی DESCRIBE است که می تواند برای خروج اطلاعات در مورد ساختار جدول مانند ستون های آن، انواع داده ها، پیش فرض ها استفاده شود. بنابراین اگر شما اجرا DESCRIBE wp_postmeta؛ نتایج زیر را خواهید دید:

فیلد نوع Null کلید پیش فرض غیره
meta_id bigint
بدون علامت
NO PRI NULL auto_increment
post_id bigint
بدون علامت
NO MUL 0
meta_key وارچار (255) بله MUL NULL
meta_value longtext بله NULL

این جالب است، اما شما ممکن است در مورد آن بدانید. اما شما می دانید که پیشوند بیانیه DESCRIBE در واقع SELECT ، INSERT ، UPDATE ، REPLACE و DELETE اظهارات؟ این معمولا توسط مترادف آن EXPLAIN شناخته شده است و به ما اطلاعات مفصلی در مورد چگونگی اظهارنظر اعدام ارائه می دهد.

برای نتایج پرس و جو ما در اینجا آمده است:

id select_type جدول نوع possible_keys کلید key_len ref ردیف غیره
1 ساده pm2 ref meta_key meta_key 576 const 28 با استفاده از کجا؛ استفاده موقت با استفاده از fileort
1 ساده بعد از ظهر ref post_id، meta_key meta_key 576 const 37456 استفاده از جایی که
1 ساده p eq_ref PRIMARY، type_status_date اولیه 8 deliciousbrainsdev. ساعت post_id 1 استفاده از جایی که
1 ساده l ref PRIMARY، order_id order_id 8 deliciousbrainsdev. ساعت post_id 1 با استفاده از وضعیت شاخص؛ استفاده از کجا
1 ساده s eq_ref اولیه اولیه 8 deliciousbrainsdev. ل key_id 1 NULL

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

مهمترین ستون نوع است، که توضیح میدهد که چگونه جدولها پیوست میشوند. اگر شما ALL را ببینید، این بدان معنی است که MySQL تمام جدول را از روی دیسک می خواند، نرخ های I / O را افزایش می دهد و بار را روی CPU قرار می دهد. این به عنوان "اسکن کامل جدول" شناخته می شود (بیشتر در مورد آن بعدا).

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

توضیح همچنین به ما اطلاعات بیشتری می دهد که می توانیم برای بهینه سازی آن استفاده کنیم. به عنوان مثال جدول pm2 (wp_postmeta)، به ما می گوید که ما با استفاده از fileort ، چون ما درخواست نتایج را با استفاده از بند ORDER BY در بیانیه مرتب می کنیم. اگر ما همچنین پرس و جو را دسته بندی کردیم، می توانیم سربار را به اعدام اضافه کنیم. برای پایگاه داده هایی که در MySQL 5.6 و بالاتر اجرا می شوند، نتایج EXPLAIN می تواند به عنوان JSON نمایش داده شود و MySQL Workbench تبدیل JSON به یک طرح اظطراری بصری از عبارت:

نحوه بهینه سازی پرس و جوهای SQL برای سایتهای سریعترچگونه برای بهینه سازی پرس وجوهای SQL برای سایت های سریع تر. موضوعات مرتبط:
اشکال زدایی و Semalt

به طور خودکار توجه خود را به مسائل می رساند با رنگ آمیزی بخش های پرس و جو با هزینه. ما می توانیم بلافاصله ببینیم که پیوستن به جدول wp_woocommerce_software_licences (alias l) یک مسئله جدی است.

حل

بخشي از پرس و جو، انجام يک اسکن کامل جدول است که بايد سعي کنيد از آن اجتناب کنيد، زيرا از ستون غير شاخصه order_id به عنوان پيوستن جدول 41 (wp_woocommerce_software_licences به جدول wp_posts جدول. این یک مسئله رایج برای پرس و جو های آهسته است و یکی از آن ها می تواند به راحتی حل شود.

شاخص ها

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

     CREATE INDEX order_id ON wp_woocommerce_software_licences (order_id)    

نحوه بهینه سازی پرس و جوهای SQL برای سایتهای سریعترچگونه برای بهینه سازی پرس وجوهای SQL برای سایت های سریع تر. موضوعات مرتبط:
اشکال زدایی و Semalt

وای، ما توانستیم بیش از 5 ثانیه از پرس و جو با اضافه کردن این شاخص، شغل خوب را بشکنیم!

پرسیدن خود را می دانید

سمولت پرس و جو - پیوستن با پیوستن، درخواست زیر زیر درخواست. آیا کارهایی را انجام نمی دهد که لازم باشد؟ آیا بهینه سازی می تواند انجام شود؟

در این مورد ما با استفاده از order_id در جدول مجوز به جدول پستها پیوسته و در عین حال محدود کردن بیانیه برای ارسال انواع shop_order . این برای اجرای یکپارچگی داده است تا اطمینان حاصل شود که ما فقط از سوابق سفارش درست استفاده می کنیم. با این حال، این در واقع یک قسمت افزوده شده از پرس و جو است. ما می دانیم که شرط امن است که یک سطر مجوز نرم افزار در جدول دارای order_id مربوط به سفارش WooCommerce در جدول پست ها است، همانطور که در کد پلاگین PHP اجرا می شود. بیایید پیوند را حذف کنیم و اگر چیزها را بهبود ببخشیم:

نحوه بهینه سازی پرس و جوهای SQL برای سایتهای سریعترچگونه برای بهینه سازی پرس وجوهای SQL برای سایت های سریع تر. موضوعات مرتبط:
اشکال زدایی و Semalt

Semalt صرفه جویی بزرگ نیست، اما پرس و جو در حال حاضر کمتر از 3 ثانیه است.

کشیدن همه چیزها!

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

پرسشنامه مانیتور پرس و جو ما را برای 4 بار در یک بار صفحه اجرا کرد، و اگر چه خواندن MySQL query caching خوب است، تکراری که به یک پایگاه داده می خواند، در یک درخواست واقعا باید از توقف کامل اجتناب کرد. ذخیره سازی استاتیک در کد PHP شما یک راه ساده و بسیار موثر برای حل این مسئله است '؛$ results = $ wpdb-> get_results ($ sql، ARRAY_A)؛static :: $ subscriptions [$ user_id] = $ results؛بازگشت نتایج $؛}}

حافظه پنهان عمر درخواست را دارد، به طور خاص از شیء نمونه. اگر به دنبال نتایج پرسوجوانه در هر درخواست هستید، پس شما نیاز به پیاده سازی یک حافظه مداوم را دارید. Semalt، کد شما باید برای تنظیم کش وظیفه کند و در صورت تغییر داده های اساسی، ورودی حافظه پنهان نامعتبر باشد.

تفکر خارج از جعبه

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

شما می توانید داده ها را با ایجاد یک جدول که داده های مجوز را ذخیره می کند، همراه با شناسه کاربری و شناسه محصول برای تمام مجوز ها و فقط پرس و جو در مورد آن برای یک مشتری خاص. شما نیاز به بازسازی جدول با استفاده از محرک های MySQL در INSERT / UPDATE / DELETE به جدول مجوز (و یا دیگران بسته به نحوه تغییر داده ها)، اما این به طور قابل توجهی بهبود عملکرد پرس و جو که داده ها.

به طور مشابه، اگر تعدادی از پیوندها پرس و جو خود را در MySQL کم کند، ممکن است سریعتر به شکستن پرس و جو به دو یا چند اظهارنظر بپردازند و آنها را به طور جداگانه در PHP اجرا کنند و سپس نتایج را در کد جمع آوری و فیلتر کنند. لاراول با روابط بارگیری مشتاقانه در Eloquent کار مشابهی انجام می دهد.

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

نتایج

با این رویکردها برای بهینه سازی پرس و جو، ما توانستیم پرس و جو خود را از 8 ثانیه به کمتر از 2 ثانیه برداریم و تعداد دفعاتی را که از 4 به 1 نامیده می شد کاهش دادیم. به عنوان یک یادداشت، در حال اجرا در محیط توسعه ما است و در تولید سریع تر خواهد شد.

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

March 1, 2018