mysql > kegagalan indeks

MySQL ialah sistem pengurusan pangkalan data hubungan yang digunakan secara meluas. Semasa pembangunan, indeks boleh meningkatkan kecekapan pertanyaan, tetapi kadangkala anda menghadapi kegagalan indeks, yang menyebabkan pertanyaan menjadi perlahan atau menjadikannya mustahil untuk menggunakan indeks.

Artikel ini akan memperkenalkan punca kegagalan indeks, cara mengesan kegagalan indeks, cara mengoptimumkan indeks dan beberapa kes praktikal.

1. Sebab kegagalan indeks

1. Fungsi ini digunakan pada lajur indeks

Contohnya: PILIH * DARI pengguna WHERE DATE_FORMAT(create_time, '%Y -%m-%d')='2021-01-01';

Penyelesaian: Simpan hasil operasi fungsi dalam jadual sementara atau jadual baharu, dan kemudian buat pertanyaan.

2. Tidak padan jenis lajur indeks

Contohnya: PILIH * DARI pengguna WHERE create_time='2021-01-01';

Jika medan create_time ialah jenis datetime, dan syarat pertanyaan ialah jenis aksara, ia akan menyebabkan indeks gagal.

Penyelesaian: Pastikan jenis keadaan pertanyaan konsisten dengan jenis lajur indeks.

3. Operator

digunakan pada lajur indeks Contohnya: PILIH * DARI pengguna DI MANA create_time + INTERVAL 1 HARI >

Kaedah Penyelesaian: Cuba elakkan menggunakan operator pada lajur indeks Anda boleh mengira hasil operasi dahulu dan kemudian bertanya.

4. IS NULL atau IS NOT NULL digunakan pada lajur indeks

Contohnya: SELECT * FROM user WHERE create_time IS NULL;

Penyelesaian: Gunakan indeks bersama atau ubah suai pernyataan pertanyaan.

5. Nilai lajur indeks diagihkan secara tidak sekata

Contohnya: untuk jadual yang mengandungi berjuta-juta data, 90% daripada nilai tertentu medan adalah sama. Sebarang pertanyaan menggunakan medan ini akan gagal.

Penyelesaian: Gunakan indeks bersama atau gunakan indeks penutup.

6. Jumlah data terlalu besar

Contohnya: PILIH * DARI pengguna DI MANA nama LIKE '%abc%';

Penyelesaian: Gunakan indeks teks penuh atau Ubah suai syarat pertanyaan.

2. Bagaimana untuk mengesan kegagalan indeks

Anda boleh melihat pelan pelaksanaan pernyataan pertanyaan melalui kata kunci EXPLAIN.

Contohnya: EXPLAIN SELECT * FROM user WHERE create_time='2021-01-01';

Jika Use where muncul dalam hasil pertanyaan, ini bermakna indeks tidak sah dan perlu dioptimumkan.

3. Cara mengoptimumkan indeks

1. Buat indeks bersama

Indeks bersama merujuk kepada indeks yang terdiri daripada berbilang lajur, yang boleh menambah baik kecekapan pertanyaan.

Contohnya: CREATE INDEX idx_user ON user(create_time, name);

Urutan pertanyaan mesti dipertimbangkan Contohnya, dalam pernyataan di atas, create_time ialah lajur dan nama pertama ialah lajur kedua Create_time mesti wujud dalam keadaan pertanyaan sebelum indeks ini boleh digunakan.

2. Buat indeks penutup

Indeks penutup bermakna hasil pertanyaan hanya perlu diperoleh daripada indeks, bukan daripada jadual data.

Contohnya: CREATE INDEX idx_user ON user(create_time) INCLUDE(name);

Anda mesti menyatakan dengan jelas lajur yang perlu disertakan semasa membuat pertanyaan, anda hanya perlu mendapatkan pertanyaan hasil daripada indeks Anda boleh Mengurangkan bilangan akses kepada jadual data dan meningkatkan kecekapan pertanyaan.

3. Menggunakan indeks teks penuh

Indeks teks penuh ialah indeks khas yang sesuai untuk senario carian teks.

Contohnya: CIPTA INDEKS PENUH idx_user ON user(name);

Perlu diingat bahawa MySQL hanya menyokong indeks teks penuh bahasa Inggeris dan Cina, bahasa lain memerlukan penggunaan ketiga -pemalam parti.

4. Kes sebenar

1. Kegagalan indeks menyebabkan pertanyaan menjadi perlahan

Sistem tertentu mempunyai pengguna jadual pengguna, yang mengandungi berjuta-juta data , yang mempunyai nama medan, digunakan untuk menanyakan pengguna.

Pada mulanya, medan tidak mempunyai sebarang indeks dan pertanyaannya sangat perlahan. Kemudian, indeks telah ditambahkan pada medan ini, dan kelajuan pertanyaan telah dipertingkatkan.

Walau bagaimanapun, selepas sistem berada dalam talian untuk tempoh masa tertentu, didapati kelajuan pertanyaan menurun semula dan banyak rekod yang serupa dengan yang berikut muncul dalam log akses:

PILIH * DARI pengguna WHERE nama LIKE '%abc% ';

Lihat pelan pelaksanaan melalui kata kunci EXPLAIN dan mendapati bahawa indeks itu tidak sah dan memerlukan pengoptimuman.

Penyelesaian terakhir ialah mencipta indeks teks penuh untuk medan dan mengubah suai pernyataan pertanyaan. Pernyataan pertanyaan yang diubah suai adalah seperti berikut:

PILIH * DARI pengguna WHERE MATCH(name) AGAINST('abc');

2. Kegagalan indeks menyebabkan pertanyaan berjalan terlalu lama

Sistem tertentu mempunyai susunan jadual pesanan, yang mengandungi berjuta-juta data Terdapat medan create_time yang digunakan untuk membuat pertanyaan.

Pada mulanya, medan diindeks dan kelajuan pertanyaan boleh diterima. Walau bagaimanapun, apabila bilangan pesanan meningkat, masa berjalan pertanyaan meningkat secara beransur-ansur, malah menyebabkan beberapa pertanyaan tamat masa.

Lihat pelan pelaksanaan melalui kata kunci EXPLAIN dan ketahui bahawa indeks itu tidak sah dan memerlukan pengoptimuman.

Penyelesaian terakhir ialah mencipta indeks bersama untuk medan dan mengubah suai pernyataan pertanyaan. Pernyataan pertanyaan yang diubah suai adalah seperti berikut:

SELECT * FROM order WHERE create_time='2021-01-01' AND status='SUCCESS';

Ringkasnya, indeks adalah untuk menambah baik pertanyaan kecekapan bermakna penting, tetapi dalam penggunaan sebenar, kegagalan indeks perlu dielakkan. Dengan mengesan dan mengoptimumkan indeks, kecekapan pertanyaan boleh dipertingkatkan dan prestasi pangkalan data yang optimum boleh dicapai.

Atas ialah kandungan terperinci mysql > kegagalan indeks. Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!