Indeks teks penuh komposit dalam MySQL

Question

Saya mahu sistem membenarkan carian mesej pengguna oleh pengguna tertentu. Katakan kita mempunyai jadual berikut

create table messages(
  user_id int,
  message nvarchar(500));

Jika saya ingin mencari semua mesej daripada pengguna 1 yang mengandungi perkataan "foo", indeks apakah yang harus saya gunakan di sini.

1. Mudah, indeks bukan unik user_id

   
   

   Ia akan menapis hanya mesej pengguna tertentu dan kemudian mengimbas sepenuhnya untuk perkataan tertentu.

2. Teks penuhindeks mesej

   
   

   Ini mencari semua mesej untuk semua pengguna dan kemudian menapis mengikut ID, yang kelihatan tidak cekap apabila volum pengguna besar.

3. Kompositindeks daripada

   user_id
   dan

   mesej

   Oleh itu, pepohon indeks teks penuh dicipta secara individu untuk setiap pengguna dan oleh itu boleh dicari secara individu. Semasa pertanyaan, sistem menapis mesej mengikut ID dan kemudian melakukan carian teks pada baris yang tinggal dalam indeks.

Setahu saya. Perkara terakhir adalah mustahil. Jadi saya menganggap saya harus menggunakan pilihan pertama, adakah ia akan berfungsi lebih baik jika terdapat beberapa ribu pengguna?

Bukankah lelaran penuh memerlukan terlalu banyak sumber jika terdapat kira-kira 100 mesej setiap satu?

Mungkin saya boleh memasukkan nama pengguna dalam mesej dan menggunakan mod carian teks penuh BOOLEAN, tetapi saya fikir ini akan menjadi lebih perlahan daripada menggunakan indeks user_id.

Answer 1

Anda harus menambah indeks biasa pada message 上添加全文索引，在 user_id dan gunakan pertanyaan berikut:

SELECT *
FROM messages
WHERE MATCH(message) AGAINST(@search_query)
AND user_id = @user_id;

Anda betul, anda tidak boleh melakukan pilihan 3. Tetapi daripada cuba memilih antara 1 dan 2, biarkan MySQL melakukan kerja untuk anda. MySQL hanya akan menggunakan satu daripada dua indeks dan melakukan imbasan linear untuk melengkapkan penapisan kedua, tetapi ia akan menganggarkan keberkesanan setiap indeks dan memilih yang terbaik.

NOTA: Hanya lakukan ini jika anda mampu membayar overhed dua indeks (sisipan/kemas kini/pemadaman yang lebih perlahan). Selain itu, jika anda tahu hanya terdapat beberapa mesej bagi setiap pengguna, mungkin masuk akal untuk menggunakan indeks mudah dan melakukan regex atau sesuatu yang serupa dalam lapisan aplikasi.

Answer 2

@Jawapan Alden Quimby adalah betul dengan sendirinya, tetapi masih banyak lagi ceritanya, kerana MySQL hanya akan cuba untuk memilih indeks yang terbaik, dan keupayaannya untuk membuat keputusan ini terhad kerana indeks teks penuh Cara berinteraksi dengan pengoptimum.

Apa yang berlaku sebenarnya adalah ini:

Jika user_id yang ditentukan wujud dalam 0 atau 1 baris yang sepadan dalam jadual, pengoptimum akan menyedarinya dan memilih user_id sebagai indeks untuk pertanyaan ini. Laksanakan dengan cepat.

Jika tidak, pengoptimum akan memilih indeks teks penuh, menapis setiap baris yang sepadan dengan indeks teks penuh untuk menghapuskan baris yang tidak mengandungi user_id yang sepadan dengan klausa WHERE. Tidak begitu pantas.

Jadi ini bukanlah jalan yang "terbaik". Ia lebih seperti teks penuh, dengan pengoptimuman yang bagus yang mengelak daripada melakukan carian teks penuh apabila kita tahu hampir tiada apa-apa yang menarik dalam jadual.

Sebab ini berlaku ialah indeks teks penuh tidak memberikan sebarang statistik yang bermakna kepada pengoptimum. Ia hanya mengatakan "ya, saya rasa pertanyaan mungkin hanya memerlukan saya menyemak 1 baris"... Sudah tentu, ini sangat memuaskan pengoptimum, jadi indeks teks penuh memenangi bida dengan kos terendah, melainkan indeks mempunyai integer nilai terlalu Agak rendah atau lebih rendah.

Itu tidak bermakna saya tidak akan mencuba ini dahulu.

Terdapat pilihan lain, paling sesuai untuk pertanyaan teks penuh IN BOOLEAN MODE, dan itu adalah untuk mencipta lajur lain yang boleh anda isi dengan CONCAT('user_id_',user_id) atau serupa, dan kemudian mengisytiharkan indeks teks penuh 2 lajur.

filter_string VARCHAR(48) # populated with CONCAT('user_id_',user_id);
....
FULLTEXT KEY (message,filter_string)

Kemudian nyatakan semua dalam pertanyaan.

SELECT ...
 WHERE user_id = 500 AND
 MATCH (message,filter_string) AGAINST ('+kittens +puppies +user_id_500' IN BOOLEAN MODE);

Kini indeks teks penuh akan bertanggungjawab untuk memadankan hanya baris yang anak kucing, anak anjing dan "user_id_500" muncul dalam gabungan indeks teks penuh kedua-dua lajur, tetapi anda masih mahu mempunyai penapis integer di sana juga untuk memastikan keputusan akhir adalah terhad walaupun kejadian rawak dalam mesej "user_id_500".