Cara mengoptimumkan pertanyaan SQL untuk laman web yang lebih cepat

Pengoptimuman Imej dan Pengoptimuman Pertanyaan Pangkalan Data: Panduan Praktikal untuk Meningkatkan Kelajuan Laman Web WordPress

Artikel ini pada asalnya diterbitkan di blog Brains Delicious dan diterbitkan semula di sini dengan kebenaran

Anda tahu bahawa laman web yang cepat bermakna pengguna yang lebih bahagia, kedudukan Google yang lebih baik dan kadar penukaran yang lebih tinggi. Anda mungkin berfikir laman web WordPress anda cukup cepat: anda telah memeriksa prestasi tapak, dari amalan persediaan pelayan terbaik untuk memperlahankan masalah penyelesaian masalah, dan mengimbangi imej ke CDN, tetapi adakah itu semua?

Untuk laman web yang dinamik, berasaskan pangkalan data seperti WordPress, anda masih boleh menghadapi masalah: Pertanyaan pangkalan data menyebabkan kelajuan laman web untuk melambatkan.

Dalam siaran ini, saya akan membimbing anda melalui cara mengenal pasti pertanyaan yang menyebabkan kesesakan, bagaimana memahami masalah dengan pertanyaan ini, dan cara lain untuk memperbaiki dan mempercepatkan dengan cepat. Saya akan menggunakan pertanyaan sebenar yang baru -baru ini kami menyelesaikan yang melambatkan portal pelanggan DeliciousBrains.com.

pertanyaan dan pengenalan

Langkah pertama dalam menetapkan pertanyaan SQL yang perlahan adalah untuk mencari mereka. Ashley sebelum ini memuji plugin debug monitor pertanyaan di blognya, dan keupayaan pertanyaan pangkalan data plugin menjadikannya alat yang berharga untuk mengenal pasti pertanyaan SQL yang perlahan. Plugin ini melaporkan semua pertanyaan pangkalan data yang dilakukan semasa permintaan halaman. Ia membolehkan anda menapisnya dengan kod atau komponen (plugin, tema, atau teras WordPress) yang memanggilnya dan menyerlahkan pertanyaan pendua dan perlahan:

jika anda tidak mahu memasang plugin debug di tapak pengeluaran anda (mungkin anda bimbang untuk menambah beberapa overhead prestasi), anda boleh memilih untuk membolehkan log pertanyaan perlahan MySQL, yang log semua pertanyaan yang mengambil sedikit masa untuk melaksanakan . Ini agak mudah untuk mengkonfigurasi dan menetapkan lokasi pembalakan untuk pertanyaan. Oleh kerana ini adalah penalaan peringkat pelayan, kesan prestasi akan kurang daripada plugin debug di laman web ini, tetapi ia harus dimatikan apabila tidak digunakan.

Memahami soalan pertanyaan

Selepas mencari pertanyaan mahal untuk memperbaiki, langkah seterusnya adalah untuk cuba memahami apa yang menyebabkan pertanyaan melambatkan. Baru -baru ini, apabila membangunkan laman web kami, kami mendapati bahawa pertanyaan mengambil masa kira -kira 8 saat untuk dilaksanakan!

Kami menggunakan perisian WooCommerce dan WooCommerce untuk melanggan versi tersuai plugin untuk menjalankan kedai plugin kami. Tujuan pertanyaan ini adalah untuk mendapatkan semua langganan kepada pelanggan yang kami tahu nombor pelanggan. WooCommerce mempunyai model data yang agak kompleks, walaupun pesanan disimpan sebagai jenis pos tersuai, ID pelanggan (kedai yang setiap pelanggan membuat pengguna WordPress) tidak disimpan sebagai post_author, tetapi sebagai sebahagian daripada metadata pos. Plugin langganan perisian juga mewujudkan beberapa sambungan jadual tersuai. Mari kita lihat lebih mendalam pada pertanyaan.

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

Gunakan alat MySQL

MySQL menyediakan pernyataan yang mudah DESCRIBE yang boleh digunakan untuk mengeluarkan maklumat mengenai struktur jadual, seperti lajur, jenis data, dan nilai lalai. Jadi jika anda melakukan DESCRIBE wp_postmeta;, anda akan melihat hasil berikut:

Field	Type	Null	Key	Default	Extra
meta_id	bigint(20) unsigned	NO	PRI	NULL	auto_increment
post_id	bigint(20) unsigned	NO	MUL	0
meta_key	varchar(255)	YES	MUL	NULL
meta_value	longtext	YES		NULL

Ini sejuk, tetapi anda mungkin sudah tahu. Tetapi adakah anda tahu bahawa awalan pernyataan DESCRIBE sebenarnya boleh digunakan untuk SELECT, INSERT, UPDATE, REPLACE, DELETE, dan EXPLAIN pernyataan? Ini lebih sering dirujuk sebagai sinonimnya

, yang akan memberi kita maklumat terperinci tentang bagaimana pernyataan dilaksanakan.

Berikut adalah hasil pertanyaan lambat kami:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	pm2	ref	meta_key	meta_key	576	const	28	Using where; Using temporary; Using filesort
1	SIMPLE	pm	ref	post_id,meta_key	meta_key	576	const	37456	Using where
1	SIMPLE	p	eq_ref	PRIMARY,type_status_date	PRIMARY	8	deliciousbrainsdev.pm.post_id	1	Using where
1	SIMPLE	l	ref	PRIMARY,order_id	order_id	8	deliciousbrainsdev.pm.post_id	1	Using index condition; Using where
1	SIMPLE	s	eq_ref	PRIMARY	PRIMARY	8	deliciousbrainsdev.l.key_id	1	NULL

Pada pandangan pertama, ini tidak mudah dijelaskan. Nasib baik, SitePoint Friends telah menulis panduan komprehensif mengenai pemahaman pernyataan.

Lajur yang paling penting ialah

, yang menggambarkan bagaimana jadual disatukan. Jika anda melihat type, ini bermakna MySQL membaca keseluruhan jadual dari cakera, meningkatkan kadar I/O dan meningkatkan beban CPU. Ini dipanggil "imbasan meja penuh" (lebih lanjut mengenai ini kemudian). ALL Lajur

juga merupakan petunjuk yang baik yang mesti dilakukan MySQL kerana ia menunjukkan bilangan baris yang dilihatnya untuk mencari hasilnya. rows

Maklumat lanjut yang tersedia untuk pengoptimuman juga disediakan. Sebagai contoh, jadual EXPLAIN (pm2), yang memberitahu kita bahawa kita menggunakan wp_postmeta kerana kita memerlukan keputusan untuk disusun menggunakan klausa filesort dalam pernyataan. Sekiranya kita mengumpulkan pertanyaan juga, kita akan meningkatkan overhead pelaksanaan. ORDER BY

Analisis visual

MySQL Workbench adalah alat lain yang mudah dan percuma yang boleh digunakan untuk siasatan sedemikian. Untuk pangkalan data yang dijalankan pada MySQL 5.6 dan kemudian, hasil

boleh dikeluarkan sebagai JSON, yang mana MySQL Workbench menukar kepada pelan pelaksanaan visual untuk pernyataan: EXPLAIN

Ia secara automatik menarik perhatian anda dengan mewarnai bahagian pertanyaan dengan kos. Kita dapat melihat dengan segera bahawa terdapat masalah yang serius dengan menyertai Jadual (alias l).

wp_woocommerce_software_licences Penyelesaian

Imbasan meja penuh sedang dilakukan di bahagian pertanyaan , dan anda harus cuba mengelakkan ini kerana ia menggunakan lajur bukan indeks sebagai sambungan antara jadual

dan jadual

. Ini adalah masalah biasa dengan pertanyaan perlahan dan mudah diselesaikan. order_id wp_woocommerce_software_licences wp_posts Tambah indeks

adalah sebahagian daripada data pengenalan yang sangat penting dalam jadual, dan jika kita bertanya seperti ini, kita semestinya menambah indeks pada lajur itu, jika tidak, MySQL akan mengimbas jadual secara progresif sehingga baris yang dikehendaki dijumpai. Mari tambahkan indeks dan lihat apa yang akan dilakukannya:

order_id

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

Wow, kami berjaya mengurangkan pertanyaan dengan menambahkan indeks ini selama lebih dari 5 saat, dilakukan dengan baik!

Ketahui pertanyaan anda

Semak pertanyaan-sambungan satu demi satu, sub-pertanyaan satu demi satu. Adakah ia melakukan operasi yang tidak diingini? Apa pengoptimuman yang boleh dibuat?

Dalam kes ini, kami menggunakan order_id untuk menyambungkan jadual lesen ke meja pos sambil menyekat pernyataan ke jenis pos shop_order. Ini adalah untuk memaksa integriti data untuk memastikan kami hanya menggunakan rekod pesanan yang betul. Walau bagaimanapun, ia sebenarnya adalah bahagian yang berlebihan dari pertanyaan. Kami tahu bahawa mempunyai baris lesen perisian di dalam jadual dengan order_id yang berkaitan dengan perintah wooCommerce dalam jadual pos adalah pertaruhan yang selamat kerana ini dikuatkuasakan dalam kod plugin PHP. Mari padamkan sambungan dan lihat apakah ini akan memperbaiki keadaan:

Ini bukan penjimatan besar, tetapi pertanyaan kini kurang dari 3 saat.

Cache

Jika pelayan anda tidak membolehkan caching pertanyaan MySQL secara lalai, ia bernilai membolehkan. Ini bermakna bahawa MySQL akan menyimpan rekod semua kenyataan yang dilaksanakan dan hasilnya, dan jika pernyataan yang sama kemudiannya dilaksanakan, hasil cache akan dikembalikan. Cache tidak akan tamat tempoh kerana MySQL menyegarkan cache apabila menukar jadual.

Monitor pertanyaan mendapati bahawa pertanyaan kami dijalankan 4 kali dalam beban halaman, dan sambil membolehkan caching pertanyaan MySQL adalah baik, bacaan berulang pangkalan data dalam satu permintaan harus benar -benar dielakkan sama sekali. Caching statik dalam kod PHP adalah cara yang mudah dan sangat berkesan untuk menyelesaikan masalah ini. Pada asasnya, anda mendapatkannya dari pangkalan data apabila kali pertama anda meminta hasil pertanyaan pangkalan data dan simpannya dalam sifat statik kelas, dan panggilan seterusnya akan mengembalikan hasil dari sifat statik:

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

jangka hayat cache adalah jangka hayat permintaan, dan lebih khusus, jangka hayat objek instantiated. Jika anda ingin meneruskan hasil pertanyaan antara permintaan, anda perlu melaksanakan caching objek yang berterusan. Walau bagaimanapun, kod anda perlu bertanggungjawab untuk menubuhkan cache dan membatalkan entri cache apabila data asas berubah.

Kaedah lain

kita boleh mengambil cara lain untuk cuba mempercepat pelaksanaan pertanyaan, yang memerlukan lebih banyak kerja daripada hanya menala pertanyaan atau menambah indeks. Salah satu bahagian paling perlahan dari pertanyaan kami ialah kerja sambungan jadual dari ID pelanggan ke ID produk, yang perlu kami lakukan untuk setiap pelanggan. Bagaimana jika kita hanya melakukan semua sambungan sekali, maka apa yang harus kita lakukan jika kita hanya perlu mendapatkan data pelanggan apabila kita memerlukannya?

Anda boleh menghilangkan data dengan membuat jadual yang menyimpan data lesen serta ID pengguna dan ID produk untuk semua lesen, hanya menanyakan jadual untuk pelanggan tertentu. Anda perlu membina semula jadual apabila menggunakan pencetus mysql

/INSERT/UPDATE/DELETE ke jadual lesen (atau jadual lain, bergantung kepada bagaimana perubahan data), tetapi ini akan meningkatkan prestasi prestasi dengan ketara menanyakan data tersebut.

Begitu juga, jika banyak sambungan melambatkan pertanyaan di MySQL, mungkin lebih cepat untuk memecahkan pertanyaan kepada dua atau lebih pernyataan dan melaksanakannya secara berasingan dalam PHP, dan kemudian mengumpul dan menapis hasil dalam kod anda. Laravel melakukan operasi yang sama dengan mengasyikkan hubungan dengan fasih.

Jika anda mempunyai sejumlah besar data dan mempunyai banyak jenis pos tersuai yang berbeza, WordPress mungkin terdedah kepada pertanyaan yang lebih perlahan pada jadual wp_posts. Jika anda merasa lambat untuk menanyakan jenis pos anda, pertimbangkan untuk meninggalkan model penyimpanan jenis post tersuai dan menggunakan jadual tersuai.

Hasil

Dengan kaedah pengoptimuman pertanyaan ini, kami berjaya mengurangkan masa pertanyaan dari 8 saat hingga lebih dari 2 saat dan mengurangkan jumlah panggilan dari 4 hingga 1. Perhatikan bahawa masa pertanyaan ini direkodkan dalam persekitaran pembangunan kami dan akan lebih cepat dalam persekitaran pengeluaran.

Saya harap panduan ini akan membantu anda untuk mengesan dan memperbaiki pertanyaan yang perlahan. Pengoptimuman pertanyaan mungkin kelihatan seperti tugas yang dahsyat, tetapi sebaik sahaja anda mencuba dan mendapatkan kejayaan yang cepat, anda akan mula terpesona olehnya dan berharap dapat memperbaiki lagi.

Atas ialah kandungan terperinci Cara mengoptimumkan pertanyaan SQL untuk laman web yang lebih cepat. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!