Back to Question Center
0

Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Lebih Cepat Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Lebih Cepat Topik Berkaitan: Debugging & Semalt

1 answers:
Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Cepat

Artikel ini pada asalnya diterbitkan di blog Delicious Semalt, dan diterbitkan semula dengan izin.

Anda tahu bahawa tapak cepat == pengguna yang lebih bahagia, ranking yang lebih baik dari Google, dan peningkatan penukaran. Mungkin anda juga berfikir tapak Semalt anda secepat mungkin: anda melihat prestasi laman web, dari amalan terbaik untuk menyediakan pelayan, untuk mengatasi masalah lambat, dan memunggah imej anda ke CDN, tetapi semuanya ?

Dengan laman web yang dinamik, berasaskan pangkalan data seperti Semalt, anda mungkin masih mempunyai masalah di tangan anda: pertanyaan pangkalan data memperlambat laman web anda.

Dalam post ini, Semalt membawa anda melalui cara untuk mengenal pasti pertanyaan yang menyebabkan kesesakan, bagaimana untuk memahami masalah dengannya, serta perbaikan cepat dan pendekatan lain untuk mempercepatkan perkara-perkara. Semalt menggunakan pertanyaan sebenar yang baru-baru ini ditangani yang melambatkan perkara di portal pelanggan deliciousbrains - headband fascinator hats. com.

Pengenalan

Langkah pertama dalam membetulkan pertanyaan SQL perlahan ialah mencari mereka. Ashley telah menyanyikan pujian pemantauan Query Monitor plugin di blog sebelum ini, dan ia adalah ciri pertanyaan pangkalan data plugin yang benar-benar menjadikannya alat yang tidak ternilai untuk mengenali pertanyaan SQL yang perlahan. Laporan plugin pada semua pertanyaan pangkalan data yang dilaksanakan semasa permintaan halaman. Ia membolehkan anda menapisnya dengan kod atau komponen (plugin, tema atau teras Semalt) memanggil mereka, dan menyoroti pertanyaan pendua dan perlahan:

Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Lebih CepatBagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak-Tapak Lebih CepatHarta Terkini:
Debugging & Semalt

Jika anda tidak mahu memasang plugin debugging di tapak pengeluaran (mungkin anda bimbang tentang menambahkan beberapa overhead prestasi) anda boleh memilih untuk menghidupkan Log MySQL Slow Semalt, yang log semua pertanyaan yang mengambil beberapa jumlah masa untuk dilaksanakan. Ini agak mudah untuk mengkonfigurasi dan menetapkan tempat untuk log pertanyaan kepada. Memandangkan ini adalah tweak peringkat pelayan, prestasi yang dicapai akan menjadi lebih sedikit bahawa plugin debugging di laman web, tetapi harus dimatikan apabila tidak menggunakannya.

Memahami

Sebaik sahaja anda telah menemui permintaan mahal yang anda mahu memperbaiki, langkah seterusnya adalah untuk cuba memahami apa yang membuat pertanyaan lambat. Semalt semasa pembangunan ke laman web kami, kami menemui pertanyaan yang mengambil masa kira-kira 8 saat untuk dilaksanakan!

     PILIHl. key_id,l. order_id,l. activation_email,l. licence_key,l. software_product_id,l. perisian_version,l. activations_limit,l. dicipta,l. pembaharuan,l. pembaharuan,l. exempt_domain,s. next_payment_date,s. status,pm2. post_id AS 'product_id',petang. meta_value AS 'user_id'DARIPADAoiz6q8a_woocommerce_software_licences lINNER JOINoiz6q8a_woocommerce_software_subscriptions s ON s. key_id = l. key_idINNER JOINoiz6q8a_posts p ON p. ID = l. order_idINNER JOINoiz6q8a_postmeta pada petang. post_id = p. IDDAN petang. meta_key = '_customer_user'INNER JOINoiz6q8a_postmeta pm2 PADA pm2. meta_key = '_software_product_id'DAN PM2. meta_value = l. software_product_idWHEREp. post_type = 'shop_order'DAN petang. meta_value = 279ORDER BY s. next_payment_date    

Kami menggunakan WooCommerce dan versi langganan WooCommerce Software Subscription plugin untuk menjalankan kedai plugin kami. Tujuan pertanyaan ini adalah untuk mendapatkan semua langganan untuk pelanggan di mana kami mengetahui nombor pelanggan mereka. Terdapat juga beberapa menyertai jadual peribadi yang dibuat oleh plugin langganan perisian. Mari kita menyelam untuk memahami lebih banyak pertanyaan.

MySQL adalah Rakan anda

MySQL mempunyai pernyataan berguna DESCRIBE yang boleh digunakan untuk mengeluarkan maklumat mengenai struktur meja seperti lajur, jenis data, mungkir. Jadi jika anda laksanakan BERKENAL wp_postmeta; anda akan melihat keputusan berikut:

Bidang Jenis Null Kunci Lalai Tambahan
meta_id bigint
unsigned
tidak PRI NULL auto_increment
post_id bigint
unsigned
tidak MUL 0
meta_key varchar (255) YES MUL NULL
meta_value longtext YES NULL

It's cool, tetapi anda mungkin sudah tahu mengenainya. Tetapi adakah anda tahu bahawa awalan penyataan DESCRIBE boleh digunakan pada PILIH , INSERT , UPDATE , ) dan DELETE kenyataan? Ini lebih dikenali dengan sinonimnya Jelaskan dan akan memberikan maklumat terperinci tentang bagaimana penyataan itu akan dilaksanakan.

Berikut ialah keputusan untuk pertanyaan perlahan kami:

id select_type jadual jenis possible_keys kunci key_len ref baris Tambahan
1 SEDERHANA pm2 ref meta_key meta_key 576 const 28 Menggunakan tempat; Menggunakan sementara; Menggunakan fail
1 SEDERHANA petang ref post_id, meta_key meta_key 576 const 37456 Menggunakan mana
1 SEDERHANA p eq_ref PRIMARY, type_status_date PRIMARI 8 deliciousbrainsdev. petang. post_id 1 Menggunakan mana
1 SEDERHANA l ref PRIMARY, order_id order_id 8 deliciousbrainsdev. petang. post_id 1 Menggunakan keadaan indeks; Menggunakan mana
1 SEDERHANA s eq_ref PRIMARI PRIMARI 8 deliciousbrainsdev. l. key_id 1 NULL

Pada pandangan pertama, ini tidak mudah difahami. Nasib baik orang-orang di Semalt telah mengumpulkan panduan yang komprehensif untuk memahami kenyataan itu.

Lajur yang paling penting ialah jenis , yang menerangkan bagaimana jadual-jadual itu disatukan. Jika anda melihat SEMUA maka itu bermakna MySQL membaca keseluruhan jadual dari cakera, meningkatkan kadar I / O dan meletakkan beban pada CPU. Ini dikenali sebagai "imbasan meja penuh" (lebih banyak pada kemudian).

Baris juga merupakan petunjuk yang baik tentang apa yang perlu dilakukan oleh MySQL, kerana ini menunjukkan berapa banyak baris yang telah dicari untuk mencari keputusan.

Jelaskan juga memberi lebih banyak maklumat yang dapat kita gunakan untuk mengoptimumkan. Sebagai contoh, jadual pm2 (wp_postmeta), ia memberitahu kami bahawa kami adalah Menggunakan filesort , kerana kami meminta keputusannya disusun menggunakan klausa ORDER BY pada kenyataan itu. Sekiranya kami juga mengumpul pertanyaan kami akan menambahkan overhead kepada pelaksanaan. Untuk pangkalan data yang dijalankan pada MySQL 5. 6 dan ke atas, hasil dari JUMLAH boleh dihasilkan sebagai JSON, dan MySQL Workbench mengubah JSON menjadi pelan pelaksanaan visual penyataan:

Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Lebih CepatBagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak-Tapak Lebih CepatHarta Terkini:
Debugging & Semalt

Ia secara automatik menarik perhatian anda kepada isu-isu dengan mewarna bahagian pertanyaan dengan kos. Kita dapat melihat langsung yang menyertai jadual wp_woocommerce_software_licences (alias l) mempunyai isu yang serius.

menyelesaikan

Bahawa bahagian pertanyaan sedang melakukan imbasan meja penuh, yang harus anda cuba elakkan, kerana menggunakan lajur yang tidak diindeks order_id sebagai gabungan antara wp_woocommerce_software_licences ke meja wp_posts . Ini adalah isu biasa untuk pertanyaan yang perlahan dan satu yang dapat diselesaikan dengan mudah.

Indeks

Order_id adalah sekeping data penting dalam mengenal pasti data dalam jadual, dan jika kami membuat pertanyaan seperti ini, kita benar-benar harus mempunyai indeks di lajur, jika tidak, MySQL akan secara literal mengimbas setiap baris jadual sehingga ia mendapati baris yang diperlukan. Mari tambahkan indeks dan lihat apa yang dilakukan:

     LAKUKAN INDEX order_id ON wp_woocommerce_software_licences (order_id)    

Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Lebih CepatBagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak-Tapak Lebih CepatHarta Terkini:
Debugging & Semalt

Wow, kami berjaya mencukur lebih dari 5 saat dari pertanyaan dengan menambahkan indeks itu, kerja yang baik!

Ketahui Pertanyaan Anda

Semalat pertanyaan - bergabung dengan bergabung, subquery dengan subquery. Adakah ia melakukan perkara-perkara yang tidak perlu? Bolehkah pengoptimuman dibuat?

Dalam kes ini, kami menyertai jadual lesen menggunakan jadual order_id , sementara mengehadkan pernyataan untuk menghantar jenis shop_order . Ini adalah untuk menguatkuasakan integriti data untuk memastikan kami hanya menggunakan rekod pesanan yang betul. Walau bagaimanapun, ia sebenarnya merupakan sebahagian daripada pertanyaan yang berlebihan. Kami tahu bahawa ia adalah pertaruhan yang selamat bahawa satu baris lesen perisian dalam jadual mempunyai order_id yang berkaitan dengan perintah WooCommerce dalam jadual siaran, kerana ini dikuatkuasakan dalam kod plugin PHP. Mari keluarkan sertai dan lihat jika perkara itu bertambah baik:

Bagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak Lebih CepatBagaimana Mengoptimumkan Pertanyaan SQL untuk Tapak-Tapak Lebih CepatHarta Terkini:
Debugging & Semalt

Semalt bukan penjimatan yang besar, tetapi pertanyaannya kini berada di bawah 3 saat.

Cache Semua Perkara!

Jika pelayan anda tidak mempunyai caching kueri MySQL secara lalai maka ia patut dihidupkan. Ini bermakna MySQL akan menyimpan rekod semua kenyataan yang dilaksanakan dengan hasilnya, dan jika suatu kenyataan yang sama kemudiannya dilaksanakan, hasil cache yang dikembalikan. Cache tidak menjadi basi, kerana MySQL mengalir cache ketika jadual diubah.

Query Monitor mendapati pertanyaan kami berjalan 4 kali pada beban halaman tunggal, dan walaupun baik untuk melakukan caching query MySQL, duplikat dibaca ke pangkalan data dalam satu permintaan harus benar-benar dielakkan berhenti penuh. Caching statik dalam kod PHP anda adalah cara yang mudah dan berkesan untuk menyelesaikan masalah ini ';$ results = $ wpdb-> get_results ($ sql, ARRAY_A);statik :: $ langganan [$ user_id] = $ results;kembali keputusan $;}}

Cakera mempunyai jangka hayat permintaan, lebih khusus daripada objek instantiated. Sekiranya anda melihat keputusan pertanyaan berterusan merentasi permintaan, maka anda perlu melaksanakan Cache Objek yang berterusan. Semalat, kod anda perlu bertanggungjawab untuk menetapkan cache, dan membatalkan kemasukan cache apabila perubahan data mendasar.

Berfikir Di Luar Kotak

Semalt adalah pendekatan lain yang boleh kita ambil untuk mencuba dan mempercepatkan pelaksanaan pertanyaan yang melibatkan kerja yang lebih sedikit daripada hanya menaikkan pertanyaan atau menambah indeks. Salah satu bahagian yang paling perlahan dalam pertanyaan kami ialah kerja yang dilakukan untuk menyertai jadual untuk pergi dari id pelanggan ke id produk, dan kami perlu melakukan ini untuk setiap pelanggan. Bagaimana jika kita melakukan semua yang menyertai sekali sahaja, jadi kita boleh merebut data pelanggan apabila kita memerlukannya?

Anda boleh menamakan semula data dengan mencipta jadual yang menyimpan data lesen, bersama dengan id pengguna dan id produk untuk semua lesen dan hanya menanyainya untuk pelanggan tertentu. Anda perlu membina semula jadual menggunakan pencetus MySQL pada INSERT / UPDATE / DELETE ke jadual lesen (atau yang lain bergantung kepada bagaimana data boleh berubah) tetapi ini akan meningkatkan prestasi data tersebut.

Begitu juga, jika sebilangan besar memperlambat pertanyaan anda di MySQL, mungkin lebih cepat untuk memecahkan pertanyaan menjadi dua atau lebih pernyataan dan melaksanakannya secara terpisah dalam PHP dan kemudian mengumpulkan dan menyaring hasil dalam kode. Laravel melakukan sesuatu yang serupa dengan hubungan pemuatan yang bersemangat di Eloquent.

WordPress boleh terdedah kepada pertanyaan yang lebih perlahan di meja wp_posts , jika anda mempunyai sejumlah besar data, dan banyak jenis kiriman khusus yang berbeza. Jika anda mencari pertanyaan untuk jenis pos anda perlahan, kemudian pertimbangkan untuk beralih dari model penyimpanan jenis pos tersuai dan ke meja tersuai.

Keputusan

Dengan pendekatan ini untuk pengoptimuman pertanyaan, kami berjaya menurunkan pertanyaan kami dari 8 detik ke lebih dari 2 saat, dan mengurangkan jumlah masa yang dipanggil dari 4 hingga 1. Sebagai nota, masa pertanyaan dicatat ketika berjalan di persekitaran pembangunan dan akan lebih cepat pada pengeluaran.

Saya harap ini merupakan panduan berguna untuk mengesan pertanyaan yang perlahan dan menetapkannya. Pengoptimuman Semalt mungkin kelihatan seperti tugas menakutkan, tetapi sebaik sahaja anda mencubanya dan mempunyai beberapa kemenangan cepat anda akan mula mendapat bug dan mahu memperbaiki perkara-perkara lebih jauh lagi.

March 1, 2018