Daha Hızlı WordPress Siteleri için SQL Sorgu Optimizasyonu

Biliyorsunuz ki hızlı bir site == daha mutlu kullanıcılar, Google sıralamasında iyileşme ve dönüşümlerde artış. Belki de WordPress sitenizin olabildiğince hızlı olduğunu düşünüyorsunuz – en iyi sunucu kurma uygulamalarından, yavaş kod sorunlarını gidermeye ve resimlerinizi bir CDN'ye boşaltmaya kadar site performansına baktınız, ancak hepsi bu kadar mı? ?

WordPress gibi dinamik veritabanı güdümlü web sitelerinde hala tek bir sorununuz olabilir: sitenizi yavaşlatan veritabanı sorguları.

Bu yazıda, darboğazlara neden olan sorguları nasıl tanımlayacağınızı, bunlarla ilgili sorunları nasıl anlayacağınızı, hızlı düzeltmeleri ve sql sorgusu yürütme süresini hızlandırmak için diğer yaklaşımları anlatacağım. Yakın zamanda ele aldığımız ve lezzetlibrains.com'un müşteri portalında işleri yavaşlatan gerçek bir sorguyu kullanacağım.

Yavaş SQL Sorgularının Tanımlanması

Yavaş SQL sorgularını düzeltmenin ilk adımı onları bulmaktır. Ashley, daha önce blogda hata ayıklama eklentisi Query Monitor'ün övgülerini dile getirmişti ve eklentiyi gerçekten yavaş SQL sorgularını belirlemek ve veritabanı performansını iyileştirmek için paha biçilmez bir araç yapan şey, eklentinin veritabanları sorguları özelliğidir. Eklenti, sayfa isteği sırasında yürütülen tüm veritabanı sorgularını raporlar. Bunları çağıran kod veya bileşene (eklenti, tema veya WordPress çekirdeği) göre filtrelemenize ve yinelenen ve yavaş sorguları vurgulamanıza olanak tanır:

Bir üretim sitesine hata ayıklama eklentisi yüklemek istemiyorsanız (belki biraz performans ek yükü eklemekten endişe duyuyorsunuz), belirli bir süre alan tüm sorguları günlüğe kaydeden MySQL Yavaş Sorgu Günlüğünü açmayı seçebilirsiniz. yürütmek için. Bu, sorguların nereye kaydedileceğini yapılandırmak ve ayarlamak için nispeten basittir. Bu, sunucu düzeyinde bir ayar olduğundan, performans artışı sitedeki bir hata ayıklama eklentisinden daha az olacaktır, ancak kullanılmadığında kapatılmalıdır.

Yavaş SQL Sorgularını Anlama

Geliştirmek istediğiniz pahalı bir sorgu bulduğunuzda, sonraki adım, sorguyu neyin yavaşlattığını anlamaya çalışmaktır. Son zamanlarda sitemizi geliştirirken, yürütülmesi yaklaşık 8 saniye süren bir sorgu bulduk!

 SELECT l.key_id, l.order_id, l.activation_email, l.licence_key, l.software_product_id, l.software_version, l.activations_limit, l.created, l.renewal_type, l.renewal_id, l.exempt_domain, s.next_payment_date, s.status, pm2.post_id AS 'product_id', pm.meta_value AS 'user_id' FROM oiz6q8a_woocommerce_software_licences l INNER JOIN oiz6q8a_woocommerce_software_subscriptions s ON s.key_id = l.key_id INNER JOIN oiz6q8a_posts p ON p.ID = l.order_id INNER JOIN oiz6q8a_postmeta pm ON pm.post_id = p.ID AND pm.meta_key = '_customer_user' INNER JOIN oiz6q8a_postmeta pm2 ON pm2.meta_key = '_software_product_id' AND pm2.meta_value = l.software_product_id WHERE p.post_type = 'shop_order' AND pm.meta_value = 279 ORDER BY s.next_payment_date

Eklenti mağazamızı çalıştırmak için WooCommerce ve WooCommerce Yazılım Abonelikleri eklentisinin özelleştirilmiş bir sürümünü kullanıyoruz. Bu sorgunun amacı, müşteri numarasını bildiğimiz bir müşterinin tüm aboneliklerini almaktır. WooCommerce biraz karmaşık bir veri modeline sahiptir, bir sipariş özel bir gönderi türü olarak saklansa bile, müşterinin kimliği (her müşterinin kendileri için bir WordPress kullanıcısı oluşturduğu mağazalar için) post_author olarak depolanmaz, bunun yerine post meta verilerinin bir parçası olarak.

Ayrıca, bu SQL ifadesini daha karmaşık ve anlaşılmasını zorlaştıran yazılım abonelikleri eklentisi tarafından oluşturulan özel tablolara birkaç iç birleşim vardır. Sorguyu daha fazla anlamak için konuya girelim.

MySQL, SQL Sorgu Optimizasyonu için Arkadaşınızdır

MySQL, bir tablonun sütunları, veri türleri, varsayılanlar gibi yapısı hakkında bilgi vermek için kullanılabilecek kullanışlı bir DESCRIBE deyimine sahiptir. Yani DESCRIBE wp_postmeta; aşağıdaki sonuçları göreceksiniz:

Alan	Tip	Boş	Anahtar	Varsayılan	Ekstra
meta_id	bigint(20) imzasız	HAYIR	PRI	BOŞ	otomatik artış
post_id	bigint(20) imzasız	HAYIR	MUL	0
Meta Anahtarı	varchar(255)	EVET	MUL	BOŞ
meta_değer	uzun metin	EVET		BOŞ

Bu harika, ama bunu zaten biliyor olabilirsiniz. Ancak DESCRIBE deyimi önekinin aslında SELECT , INSERT , UPDATE , REPLACE ve DELETE deyimlerinde kullanılabileceğini biliyor muydunuz? Bu daha yaygın olarak EXPLAIN eşanlamlısı ile bilinir ve bize ifadenin nasıl yürütüleceği hakkında ayrıntılı bilgi verecektir.

Yavaş sorgumuzun sonuçları:

İD	select_type	tablo	tip	olası_anahtarlar	anahtar	key_len	referans	satırlar	Ekstra
1	BASİT	pm2	referans	Meta Anahtarı	Meta Anahtarı	576	const	28	nerede kullanarak; Geçici kullanma; Dosya sıralamasını kullanma
1	BASİT	öğleden sonra	referans	post_id,meta_key	Meta Anahtarı	576	const	37456	nerede kullanılıyor
1	BASİT	P	eq_ref	BİRİNCİL, tür_durum_tarihi	ÖNCELİK	8	lezzetlibrainsdev.pm.post_id	1	nerede kullanılıyor
1	BASİT	ben	referans	BİRİNCİL, order_id	Sipariş Kimliği	8	lezzetlibrainsdev.pm.post_id	1	İndeks koşulunu kullanma; nerede kullanılıyor
1	BASİT	s	eq_ref	ÖNCELİK	ÖNCELİK	8	lezzetlibrainsdev.l.key_id	1	BOŞ

İlk bakışta, bunu yorumlamak çok kolay değil. Neyse ki SitePoint'tekiler, ifadeyi anlamak için kapsamlı bir rehber hazırladılar.

En önemli sütun, tabloların nasıl birleştirildiğini açıklayan type sütunudur. ALL görüyorsanız, bu MySQL'in tüm tabloyu diskten okuduğu, G/Ç oranlarını artırdığı ve CPU'ya yük bindirdiği anlamına gelir. Bu, "tam tablo taraması" olarak bilinir – daha sonra anlatacağız.

rows sütunu ayrıca MySQL'in ne yapmak zorunda olduğunun iyi bir göstergesidir, çünkü bu, bir sonuç bulmak için kaç satıra baktığını gösterir.

Explain ayrıca bize optimize etmek için kullanabileceğimiz daha fazla bilgi verir. Örneğin, pm2 tablosu (wp_postmeta), bize Using filesort söylüyor, çünkü sonuçların ifadede bir ORDER BY yan tümcesi kullanılarak sıralanmasını istiyoruz. Sorguyu da grupluyor olsaydık, yürütmeye ek yük eklerdik.

Yürütme Planı ile Görsel İnceleme

MySQL Workbench, bu tür araştırmalar için başka bir kullanışlı, ücretsiz araçtır. MySQL 5.6 ve üzeri sürümlerde çalışan veritabanları için, EXPLAIN sonuçları JSON olarak çıktılanabilir ve MySQL Workbench, bu JSON'u ifadenin görsel bir yürütme planına dönüştürür:

Sorgunun bölümlerini maliyete göre renklendirerek otomatik olarak konulara dikkatinizi çeker. wp_woocommerce_software_licences (takma ad l) tablosuna katılmanın ciddi bir sorunu olduğunu hemen görebiliriz. Hadi biraz sorgu ayarlaması yapalım!

Yavaş SQL Sorgularını Sorgu Optimizasyonu ile Çözme

Sorgunun bu kısmı, wp_woocommerce_software_licences tablosu ile wp_posts tablosu arasındaki birleşim olarak dizine eklenmemiş bir order_id sütunu kullandığından kaçınmanız gereken tam bir tablo taraması gerçekleştiriyor. Bu, yavaş sorgular için yaygın bir sorundur ve sorgu performansını artırmak için kolayca çözülebilen bir sorundur.

dizinler

order_id , tablodaki verileri tanımlamanın oldukça önemli bir parçasıdır ve eğer böyle sorgulama yapıyorsak, gerçekten sütunda bir indeksimiz olmalıdır, aksi takdirde MySQL, gereken satırları bulana kadar tablonun her satırını tam anlamıyla tarar. Bir dizin ekleyelim ve bunun ne işe yaradığını görelim:

 CREATE INDEX order_id ON wp_woocommerce_software_licences(order_id) 

Vay canına, bu dizini ekleyerek sorguyu 5 saniyeden fazla kısaltmayı başardık, aferin! Ama neden burada duralım, yürütme süresini azaltmak ve veritabanı sunucusu darboğazlarını azaltmak için kullanabileceğimiz birkaç optimizasyon tekniği daha var.

Sorgunuzu bilin

Sorguyu inceleyin – birleştirme ile birleştirme, alt sorgu ile alt sorgu. Gerekmeyen şeyleri yapıyor mu? Optimizasyon yapılabilir mi?

Bu durumda, ifadeyi shop_order gönderi türleriyle sınırlandırırken, order_id'yi kullanarak lisanslar tablosunu gönderiler tablosuna bağlarız. Bu, yalnızca doğru sipariş kayıtlarını kullandığımızdan emin olmak için veri bütünlüğünü zorlamak içindir, ancak aslında bu, sorgunun gereksiz bir parçasıdır. Tablodaki bir yazılım lisans satırının, PHP eklenti kodunda zorunlu kılındığı için, gönderiler tablosundaki WooCommerce siparişiyle ilgili order_id sahip olmasının güvenli bir bahis olduğunu biliyoruz. Birleştirmeyi kaldıralım ve bunun bir şeyleri iyileştirip iyileştirmediğini görelim:

Bu çok büyük bir tasarruf değil ama sorgu artık 3 saniyenin altında.

Her Şeyi Önbelleğe Alın!

Sunucunuzda varsayılan olarak önbelleğe alma MySQL sorgusu yoksa, açmaya değer. Bu, MySQL'in sonuçla birlikte yürütülen tüm ifadelerin kaydını tutacağı ve daha sonra aynı bir ifade çalıştırılırsa önbelleğe alınan sonuçların döndürüleceği anlamına gelir. Tablolar değiştirildiğinde MySQL önbelleği temizlediğinden önbellek eskimez.

Query Monitor, sorgumuzun tek bir sayfa yüklemesinde 4 kez çalıştığını buldu ve MySQL sorgusunun önbelleğe alınması iyi olsa da, bir istekte veritabanına yapılan yinelenen okumalardan gerçekten kaçınılmalıdır. PHP kodunuzdaki statik önbelleğe alma, bu sorunu çözmenin basit ve çok etkili bir yoludur. Temel olarak, ilk kez istendiğinde veritabanından bir sorgunun sonuçlarını alıyor ve bunları bir sınıfın statik özelliğinde saklıyorsunuz ve ardından sonraki çağrılar statik özellikten sonuçları döndürecek:

 class WC_Software_Subscription { protected static $subscriptions = array(); public static function get_user_subscriptions( $user_id ) { if ( isset( static::$subscriptions[ $user_id ] ) ) { return static::$subscriptions[ $user_id ]; } global $wpdb; $sql = '...'; $results = $wpdb->get_results( $sql, ARRAY_A ); static::$subscriptions[ $user_id ] = $results; return $results; } }

Önbelleğin, isteğin, daha özel olarak somutlaştırılan nesnenin ömrü vardır. İstekler arasında kalıcı sorgu sonuçlarına bakıyorsanız, kalıcı bir Nesne Önbelleği uygulamanız gerekir. Ancak, önbelleği ayarlamaktan ve temel alınan veriler değiştiğinde önbellek girişini geçersiz kılmaktan kodunuzun sorumlu olması gerekir.

Kutunun Dışında Düşünmek

Sorgu yürütmeyi denemek ve hızlandırmak için, sorguda ince ayar yapmaktan veya bir dizin eklemekten biraz daha fazla iş gerektiren başka yaklaşımlar da vardır. Sorgumuzun en yavaş bölümlerinden biri, müşteri kimliğinden ürün kimliğine gitmek için tabloları birleştirmek için yapılan çalışmadır ve bunu her müşteri için yapmak zorundayız. Peki ya tüm bu birleştirmeleri bir kez yapsaydık, böylece ihtiyaç duyduğumuzda müşterinin verilerini alabilseydik?

Tüm lisanslar için kullanıcı kimliği ve ürün kimliğiyle birlikte lisans verilerini depolayan bir tablo oluşturarak ve yalnızca belirli bir müşteri için buna karşı sorgulayarak verileri normalden arındırabilirsiniz. Tabloyu, INSERT/UPDATE/DELETE üzerindeki MySQL tetikleyicilerini kullanarak lisanslar tablosuna (veya verilerin nasıl değişebileceğine bağlı olarak diğerlerine) yeniden oluşturmanız gerekir, ancak bu, bu verileri sorgulama performansını önemli ölçüde artırır.

Benzer şekilde, bir dizi birleştirme MySQL'de sorgunuzu yavaşlatırsa, sorguyu iki veya daha fazla ifadeye bölmek ve bunları PHP'de ayrı ayrı yürütmek ve ardından sonuçları kodda toplamak ve filtrelemek daha hızlı olabilir. Laravel, Eloquent'te istekli yükleme ilişkileriyle benzer bir şey yapar.

Çok miktarda veriye ve birçok farklı özel gönderi türüne sahipseniz, WordPress wp_posts tablosunda daha yavaş sorgulara eğilimli olabilir. Gönderi türünüz için sorgulamayı yavaş buluyorsanız, özel gönderi türü depolama modelinden ve özel bir tabloya geçmeyi düşünün.

Son zamanlarda, postmeta tablomuzun sürekli artan boyutu (şu anda 2,5 milyon satırda) nedeniyle sorgunun giderek yavaşladığını gördük. Sorgu, bir kez müşteri kimliği için ve bir kez ürün kimliği için olmak üzere bu tabloya iki kez katıldığından, birleşimlerden birini kaldırmaya karar verdik.

'software_product_id'yi zaten, lisansın ait olduğu ürünün bir dize temsili olan lisanslar tablosunda saklıyorduk, örn. WP Migrate DB Pro geliştirici lisansı için WPMDB-DEV. Ancak, gerçek WooCommerce ürün kimliğinden yapılan bu soyutlama, sitemiz için gerçek bir amaca hizmet etmedi ve bu nedenle, 'product_id' sütunu için 'software_product_id' sütununu değiştirdik, verileri taşıdık ve WooCommerce yazılımı sürümümüzdeki tüm kodu güncelledik gerçek ürün kimliğini kullanmak için abonelikler eklentisi.

Bu, 4 saniyeye kadar süren bazı sorguları yalnızca 223 milisaniyeye indirdi! 💪

SQL Sorgu Optimizasyon Sonuçları

SQL sorgu optimizasyonuna yönelik bu yaklaşımlarla sorgumuzu 8 saniyeden 2 saniyenin biraz üzerine indirmeyi başardık ve çağrılma sayısını 4'ten 1'e indirdik. Not olarak, bu sorgu süreleri çalışırken kaydedildi. geliştirme ortamımız ve üretimde daha hızlı olurdu. SQL sorgu optimizasyonu, SQL Server performans ayarlama gibi daha karmaşık bir işlemin parçasıdır, bu kılavuzda bunun hakkında daha fazla bilgi edinin.

Umarım bu, yavaş sorguları izlemek ve bunları düzeltmek için yararlı bir rehber olmuştur. Sorgu optimizasyonu ürkütücü bir görev gibi görünebilir, ancak bunu denediğiniz ve hızlı kazançlar elde ettiğiniz anda hatayı almaya başlayacak ve işleri daha da geliştirmek isteyeceksiniz.

SQL sorgu optimizasyonu veya kullanmayı sevdiğiniz araçlar için herhangi bir ipucunuz var mı? Yorumlarda bize bildirin.