Prestasi mysql meningkatkan dengan indeks dan terangkan-tutorial php-php.cn

MySQL Performance Boosting with Indexes and Explain

mata utama

untuk menganalisis dan mengoptimumkan pelan pelaksanaan pertanyaan, memastikan operasi pangkalan data yang lebih efisien dengan mendedahkan maklumat utama seperti jenis sambungan dan penggunaan indeks. EXPLAIN
, yang memberi tumpuan kepada lajur yang digunakan dalam klausa EXPLAIN untuk mempercepat pengambilan data dan meningkatkan prestasi pertanyaan. WHERE
dalam pertanyaan. LIKE
dalam kombinasi dengan ORDER BY, kerana ia boleh mengimbangi kelebihan prestasi yang mengehadkan hasil, terutamanya jika indeks tidak digunakan dengan berkesan. LIMIT

Pengoptimuman pangkalan data biasanya merupakan tumpuan utama dalam meningkatkan prestasi aplikasi dan kesesakan yang paling biasa. Bagaimana untuk mengukur dan memahami apa yang memerlukan peningkatan?

Alat mudah dan berkesan ialah analisis pertanyaan. Analisis yang membolehkan membolehkan anggaran yang lebih tepat mengenai masa larian pertanyaan. Ini adalah proses dua langkah: pertama, membolehkan analisis;

show profiles Katakan operasi penyisipan berikut wujud dalam pangkalan data (dan mengandaikan bahawa Pengguna 1 dan Galeri 1 telah dibuat):

Sejumlah kecil data tidak akan menyebabkan masalah, tetapi kita boleh menggunakannya untuk analisis mudah. Pertimbangkan pertanyaan berikut:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Salin selepas log masuk

Jika terdapat banyak entri foto, pertanyaan ini mungkin menjadi masalah pada masa akan datang.

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Salin selepas log masuk

Untuk mendapatkan masa berjalan tepat pertanyaan ini, anda boleh menggunakan SQL berikut:

Hasilnya adalah seperti berikut:

set profiling = 1;
SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';
show profiles;

Salin selepas log masuk

Perintah

show profiles; bukan sahaja memaparkan masa pertanyaan asal, tetapi juga masa semua pertanyaan lain, supaya pertanyaan dapat dianalisis dengan tepat.

bagaimana untuk meningkatkan pertanyaan?

Anda boleh bergantung pada pengetahuan SQL untuk memperbaiki, atau bergantung pada perintah MySQL EXPLAIN dan meningkatkan prestasi pertanyaan berdasarkan maklumat sebenar.

EXPLAIN digunakan untuk mendapatkan pelan pelaksanaan pertanyaan, iaitu, bagaimana MySQL melaksanakan pertanyaan. Ia sesuai untuk SELECT, DELETE, INSERT, REPLACE, dan UPDATE pernyataan dan memaparkan maklumat mengenai pelan pelaksanaan pernyataan oleh pengoptimum. Dokumentasi rasmi menerangkan dengan baik bagaimana EXPLAIN bagaimana

dapat membantu kami:

EXPLAIN dengan EXPLAIN, anda dapat melihat jadual mana yang harus anda tambahkan indeks supaya pernyataan dapat dilaksanakan dengan lebih cepat dengan menggunakan indeks untuk mencari baris. Anda juga boleh menggunakan
untuk memeriksa sama ada pengoptimal menyertai jadual dalam urutan terbaik.

EXPLAIN Untuk memberi contoh untuk menggambarkan penggunaan UserManager.php, kami akan menggunakan pertanyaan untuk mencari e -mel pengguna dalam

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Salin selepas log masuk

EXPLAIN untuk menggunakan perintah SELECT, tambahkannya sebelum pertanyaan jenis

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Salin selepas log masuk

Hasilnya adalah seperti berikut (tatal ke kanan untuk melihat segala -galanya):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	const	UNIQ_1483A5E9E7927C74	UNIQ_1483A5E9E7927C74	182	const	1	100.00	NULL

<🎜>

Keputusan ini tidak mudah difahami pada mulanya, mari kita lihat dengan lebih dekat setiap satu:

id: Ini adalah pengecam berurutan untuk setiap pertanyaan dalam SELECT.
select_type: SELECT Jenis pertanyaan. Bidang ini boleh mengambil pelbagai nilai yang berbeza, jadi kami akan memberi tumpuan kepada yang paling penting:
- SIMPLE: pertanyaan mudah tanpa subqueries atau kesatuan
- PRIMARY: select terletak di pertanyaan paling luar dalam sambungan
- DERIVED: select adalah sebahagian daripada pertanyaan neutron from
- : Yang pertama di subquery SUBQUERY select
- : UNION adalah pernyataan kesatuan kedua atau berikutnya. Senarai lengkap nilai select medan boleh didapati di sini. select_type
: Jadual yang dirujuk oleh baris. table
: Bidang ini mewakili jenis jadual yang digunakan oleh sambungan MySQL. Ini mungkin bidang yang paling penting dalam output type. Ia boleh menunjukkan indeks yang hilang, atau ia dapat menunjukkan bagaimana pertanyaan ditindih. Nilai yang mungkin untuk medan ini adalah seperti berikut (disusun dari yang terbaik ke jenis terburuk): EXPLAIN
- : Jadual mempunyai baris sifar atau satu baris. system
- : Hanya ada satu baris dalam jadual yang sepadan dengan baris, dan baris telah diindeks. Ini adalah jenis sambungan terpantas. const
- : Semua bahagian indeks digunakan oleh gabungan, dan indeksnya eq_ref atau PRIMARY_KEY. UNIQUE NOT NULL
- : Untuk setiap kombinasi baris dari jadual sebelumnya, semua baris yang sepadan dengan lajur indeks dibaca. Jenis gabungan ini biasanya berlaku dalam lajur yang diindeks berbanding dengan ref atau pengendali. =
- : Sertai indeks teks penuh jadual. fulltext
- : Sama seperti ref_or_null, tetapi juga mengandungi baris dari nilai ref lajur. NULL
- : Sambungan Gunakan senarai indeks untuk menjana set keputusan. Lajur index_merge EXPLAIN akan mengandungi kekunci yang digunakan. KEY
- : unique_subquery subquery mengembalikan hanya satu hasil dari meja dan menggunakan kunci utama. IN
- : Gunakan indeks untuk mencari baris yang sepadan dalam julat tertentu. range
- : Imbas seluruh pokok indeks untuk mencari baris yang sepadan. index
- : Imbas seluruh jadual untuk mencari baris yang sepadan untuk menyertai. Ini adalah jenis gabungan yang paling teruk, biasanya menunjukkan indeks yang sesuai di atas meja. ALL
: Menunjukkan kunci yang boleh digunakan oleh MySQL untuk mencari baris dari meja. Kekunci ini mungkin atau tidak boleh digunakan dalam amalan. possible_keys
: Menunjukkan indeks yang sebenarnya digunakan oleh MySQL. MySQL sentiasa mencari kunci terbaik yang boleh digunakan untuk pertanyaan. Apabila menyertai pelbagai jadual, ia mungkin mendapati beberapa kunci yang tidak disenaraikan dalam keys tetapi lebih baik. possible_keys
: Menunjukkan panjang indeks yang akan digunakan oleh pengoptimasi pertanyaan. key_len
: Memaparkan lajur atau pemalar yang dibandingkan dengan indeks yang dinamakan dalam lajur ref.
rows: Menyenaraikan bilangan rekod yang diperiksa untuk menjana output. Ini adalah penunjuk yang sangat penting;
Extra: Mengandungi maklumat lain. Bersamaan Using filesort atau Using temporary dalam lajur ini boleh menunjukkan pertanyaan yang dipersoalkan.

EXPLAIN Dokumentasi lengkap format output boleh didapati di halaman MySQL rasmi.

Kembali ke pertanyaan mudah kami: ia adalah SIMPLE taip select dengan sambungan const jenis. Ini adalah kes pertanyaan terbaik yang mungkin kita ada. Tetapi apa yang berlaku apabila kita memerlukan pertanyaan yang lebih besar dan lebih kompleks?

Kembali ke mod aplikasi kami, kami mungkin ingin mendapatkan semua imej galeri. Kami juga mungkin mahu memasukkan hanya foto dengan perkataan "kucing" dalam keterangan. Ini pastinya situasi yang dapat kita temukan dalam keperluan projek. Mari lihat pertanyaan:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Salin selepas log masuk

Dalam keadaan yang lebih kompleks ini, kita harus mendapatkan lebih banyak maklumat dalam EXPLAIN untuk menganalisis:

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Salin selepas log masuk

ini akan memberikan hasil berikut (tatal ke kanan untuk melihat semua sel):

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	NULL	index	PRIMARY,UNIQ_1483A5E9BF396750	UNIQ_1483A5E9BF396750	108	NULL	1	100.00	Using index
1	SIMPLE	gal	NULL	ref	PRIMARY,UNIQ_F70E6EB7BF396750,IDX_F70E6EB7A76ED395	UNIQ_1483A5E9BF396750	108	homestead.users.id	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

mari kita lihat lebih dekat dan lihat apa yang dapat kita tingkatkan dalam pertanyaan.

Seperti yang dinyatakan sebelum ini, lajur utama yang harus dilihat terlebih dahulu ialah lajur type dan rows lajur. Matlamatnya adalah untuk mendapatkan nilai yang lebih baik dalam lajur type dan meminimumkan nilai lajur rows.

Hasil pertanyaan pertama ialah index, yang bukan hasil yang baik sama sekali. Ini bermakna kita mungkin dapat memperbaikinya.

Lihat pertanyaan kami, terdapat dua cara untuk menyelesaikannya. Pertama, jadual Users tidak digunakan. Kami sama ada memperluaskan pertanyaan untuk memastikan kami mensasarkan pengguna atau kami harus memadamkan bahagian pengguna pertanyaan sepenuhnya. Ia hanya meningkatkan kerumitan dan masa prestasi keseluruhan kami.

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Salin selepas log masuk

Jadi sekarang kita mendapat hasil yang sama. Mari kita lihat EXPLAIN:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	PRIMARY,UNIQ_1483A5E9BF396750	NULL	NULL	NULL	1	100.00	NULL
1	SIMPLE	img	NULL	ref	IDX_E01FBE6A4E7AF8F	IDX_E01FBE6A4E7AF8F	109	homestead.gal.id	1	25.00	Using where

Apa yang kita tinggalkan ialah jenis ALL. Walaupun ALL mungkin jenis sambungan yang paling teruk, terdapat beberapa kes di mana ia adalah satu -satunya pilihan. Mengikut keperluan kami, kami mahu semua imej galeri, jadi kami perlu mencari jadual keseluruhan galleries. Apabila kita memerlukan semua maklumat di dalam jadual, indeks sangat bagus apabila cuba mencari maklumat khusus di dalam jadual, tetapi mereka tidak membantu kami. Apabila kita menghadapi keadaan ini, kita perlu menggunakan kaedah lain, seperti caching.

Oleh kerana kita sedang mengusahakan LIKE, peningkatan terakhir yang boleh kita buat ialah menambah indeks teks penuh ke medan description kami. Dengan cara ini, kita boleh menukar LIKE ke match() dan meningkatkan prestasi. Maklumat lanjut mengenai pengindeksan teks penuh boleh didapati di sini.

kita juga perlu menyemak dua situasi yang sangat menarik: ciri -ciri terkini dan berkaitan dalam aplikasi. Ini berlaku untuk galeri dan melibatkan beberapa situasi yang melampau yang harus kita perhatikan:

INSERT INTO `homestead`.`images` (`id`, `gallery_id`, `original_filename`, `filename`, `description`) VALUES
(1, 1, 'me.jpg', 'me.jpg', 'A photo of me walking down the street'),
(2, 1, 'dog.jpg', 'dog.jpg', 'A photo of my dog on the street'),
(3, 1, 'cat.jpg', 'cat.jpg', 'A photo of my cat walking down the street'),
(4, 1, 'purr.jpg', 'purr.jpg', 'A photo of my cat purring');

Salin selepas log masuk

di atas adalah galeri yang berkaitan.

SELECT * FROM `homestead`.`images` AS i
WHERE i.description LIKE '%street%';

Salin selepas log masuk

di atas adalah galeri terkini.

Pada pandangan pertama, pertanyaan ini harus sangat cepat kerana mereka menggunakan LIMIT. Ini berlaku dalam kebanyakan pertanyaan yang menggunakan LIMIT. Malangnya bagi kami dan aplikasi kami, pertanyaan ini juga menggunakan ORDER BY. Kerana kita perlu menyusun semua keputusan sebelum mengehadkan pertanyaan, kita kehilangan kelebihan menggunakan LIMIT.

Oleh kerana kita tahu ORDER BY boleh menjadi rumit, mari kita gunakan yang boleh dipercayai EXPLAIN.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	IDX_F70E6EB7A76ED395	NULL	NULL	NULL	1	100.00	Using where; Using filesort
1	SIMPLE	u	NULL	eq_ref	PRIMARY,UNIQ_1483A5E9BF396750	PRIMARY	108	homestead.gal.id	1	100.00	NULL

dan,

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	gal	NULL	ALL	NULL	NULL	NULL	NULL	1	100.00	Using filesort

kita dapat melihat bahawa untuk kedua -dua pertanyaan kami, kami mempunyai jenis sambungan terburuk: ALL.

Secara sejarah, pelaksanaan MySQL, terutamanya apabila digunakan dengan ORDER BY, sering menjadi sumber isu prestasi MySQL. Gabungan ini juga digunakan dalam kebanyakan aplikasi interaktif dengan set data yang besar. Ciri -ciri seperti pengguna berdaftar baru dan tag popular sering menggunakan gabungan ini. LIMIT

Kerana ini adalah masalah biasa, kita juga harus menggunakan beberapa penyelesaian biasa untuk menyelesaikan masalah prestasi.

adalah calon yang baik kerana ia adalah bidang yang kami sorting. Dengan cara ini kita boleh lakukan created_at dan ORDER BY tanpa mengimbas dan menyusun set hasil lengkap. LIMIT
disusun mengikut medan yang bukan jadual pertama dalam urutan gabungan, indeks tidak boleh digunakan. ORDER BY
menggunakan indeks. ORDER BY
. Nilai -nilai LIMIT yang besar akan memaksa LIMIT untuk menyusun lebih banyak baris. Ini akan menjejaskan prestasi. ORDER BY

Apabila kita menggunakan kedua -dua

dan LIMIT, ini adalah beberapa langkah yang perlu kita ambil untuk meminimumkan isu -isu prestasi. ORDER BY

Kesimpulan

seperti yang telah kita lihat,

sangat berguna untuk mengenal pasti masalah dalam pertanyaan seawal mungkin. Terdapat banyak masalah yang hanya diperhatikan apabila aplikasi kami sedang dalam pengeluaran dan terdapat banyak data atau banyak pelawat untuk mengakses pangkalan data. Jika anda boleh menggunakan EXPLAIN untuk mengesan masalah ini seawal mungkin, maka kemungkinan masalah prestasi di masa depan adalah lebih kecil. EXPLAIN

Aplikasi kami mempunyai semua indeks yang diperlukan dan cepat, tetapi sekarang kita tahu bahawa setiap kali kita perlu menyemak keuntungan prestasi, kita sentiasa boleh beralih kepada

dan indeks. EXPLAIN

~~Soalan Lazim Mengenai Pengindeksan Prestasi MySQL (Soalan Lazim)~~

Apakah kepentingan pengindeksan prestasi MySQL?

Pengindeksan prestasi MySQL adalah penting untuk mengoptimumkan prestasi pangkalan data. Mereka dengan ketara mempercepat operasi pengambilan data dengan cepat mengakses baris dalam jadual data berdasarkan nilai -nilai dalam lajur yang diindeks. Tanpa indeks, MySQL perlu melintasi setiap baris dalam jadual untuk mencari baris yang relevan, yang boleh memakan masa yang sangat lama, terutama untuk pangkalan data yang besar.

Bagaimanakah perintah menjelaskan membantu meningkatkan prestasi MySQL?

Perintah

di MySQL adalah alat yang berkuasa yang memberikan maklumat tentang bagaimana MySQL melakukan pertanyaan. Ia menunjukkan susunan jadual baca, jenis operasi baca yang dilakukan, indeks yang boleh dipilih, dan anggaran bilangan baris yang akan diperiksa. Maklumat ini dapat membantu pemaju mengoptimumkan pertanyaan dan meningkatkan prestasi pangkalan data.

Mengapa MySQL tidak menggunakan kekunci yang mungkin?

MySQL tidak menggunakan kekunci yang mungkin untuk beberapa sebab. Salah satu sebabnya ialah pengoptimuman menganggarkan bahawa menggunakan indeks memerlukan pengimbasan kebanyakan jadual dan memutuskan bahawa pengimbasan jadual akan lebih cepat. Sebab lain ialah lajur dalam klausa WHERE tidak sepadan dengan lajur dalam indeks.

Bagaimana untuk mengoptimumkan pertanyaan MySQL saya?

Terdapat beberapa cara untuk mengoptimumkan pertanyaan MySQL. Salah satu cara ialah menggunakan indeks dengan berkesan. Indeks boleh mempercepatkan pengambilan data dengan ketara. Walau bagaimanapun, mereka melambatkan operasi pengubahsuaian data seperti INSERT, UPDATE dan DELETE. Oleh itu, sangat penting untuk mencari titik keseimbangan. Cara lain ialah menggunakan perintah EXPLAIN untuk memahami bagaimana MySQL melaksanakan pertanyaan dan mendapati kemunculan yang berpotensi.

Apakah perbezaan antara kunci utama dan indeks dalam MySQL?

Kunci utama dalam MySQL adalah indeks. Kunci utama adalah pengecam unik untuk baris dalam jadual. Ia menguatkuasakan keunikan gabungan lajur atau lajur dan memastikan bahawa gabungan lajur atau lajur tidak mengandungi nilai NULL. Sebaliknya, indeks adalah struktur data yang dapat meningkatkan kelajuan operasi pengambilan data. Ia boleh digunakan untuk mana -mana lajur atau gabungan lajur.

Bagaimana untuk membuat indeks dalam MySQL?

Anda boleh menggunakan pernyataan CREATE INDEX untuk membuat indeks dalam MySQL. Sintaks adalah seperti berikut: CREATE INDEX index_name ON table_name (column1, column2, …);. Ini mewujudkan indeks pada lajur yang ditentukan jadual yang ditentukan.

Apakah indeks komposit dalam MySQL?

Indeks komposit, juga dikenali sebagai indeks multi-lajur, adalah indeks yang mengandungi pelbagai lajur. Di MySQL, indeks komposit boleh mengandungi sehingga 16 lajur, tetapi jumlah saiz lajur yang diindeks tidak boleh melebihi 767 bait.

Bagaimana untuk memadam indeks dalam mysql?

Anda boleh menggunakan pernyataan DROP INDEX untuk memadam indeks dalam MySQL. Sintaks adalah seperti berikut: DROP INDEX index_name ON table_name;. Ini akan memadam indeks yang ditentukan dari jadual yang ditentukan.

Apakah perbezaan antara indeks clustered dan indeks yang tidak terkawal dalam MySQL?

Indeks kluster menentukan susunan data fizikal dalam jadual. Setiap jadual hanya boleh mempunyai satu indeks kluster. Sebaliknya, indeks yang tidak terkawal tidak mengubah urutan fizikal data dalam jadual. Sebaliknya, ia mengekalkan struktur data berasingan (indeks) yang menunjuk kepada baris data, yang membolehkan pengambilan data yang lebih cepat.

Bagaimana untuk memilih indeks untuk digunakan di MySQL?

MySQL menggunakan pengoptimum berasaskan kos untuk memilih indeks untuk digunakan. Pengoptimuman menganggarkan kos pelaksanaan pelan untuk pertanyaan yang berbeza dan memilih pelan kos terendah. Kos dianggarkan berdasarkan faktor -faktor seperti bilangan baris yang akan dibaca, bilangan cakera cakera, kos CPU, dan penggunaan memori.

Atas ialah kandungan terperinci Prestasi mysql meningkatkan dengan indeks dan terangkan. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!