Kueri data di tabel lebar menggunakan indeks pencarian - Lindorm

Topik ini menjelaskan cara menggunakan indeks pencarian untuk mengkueri data di tabel lebar Lindorm melalui kueri multidimensi, kueri berhalaman dan terurut, kueri teks bertokenisasi, kueri fuzzy, serta kueri agregat.

Prasyarat

Anda telah terhubung ke LindormTable menggunakan Lindorm-cli. Untuk informasi selengkapnya, lihat Hubungkan dan gunakan LindormTable dengan Lindorm-cli.
Anda telah menyiapkan data uji dan membuat indeks pencarian. Untuk informasi selengkapnya, lihat Kelola indeks pencarian.

Jalankan kueri multidimensi

Jalankan pernyataan berikut untuk melakukan kueri multidimensi.

Contoh 1

SELECT * FROM search_table WHERE gender='M' AND city='Hangzhou' OR city='Beijing';

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id | name   | age | gender | address      | email            | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+

Contoh 2

SELECT * FROM search_table WHERE age > 30 AND city != 'Hangzhou';

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 28      | Chen   | 36  | F      | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
+---------+--------+-----+--------+--------------+------------------+------+

Pengurutan dan penomoran halaman

Indeks pencarian mendukung pengurutan pada kolom apa pun. Jalankan pernyataan berikut untuk mengurutkan hasil kueri.

SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC;

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
+---------+--------+-----+--------+--------------+------------------+------+

Indeks pencarian mendukung penomoran halaman menggunakan limit atau offset. Jalankan pernyataan berikut untuk melakukan kueri berhalaman.

SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC LIMIT 1,10;

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
+---------+--------+-----+--------+--------------+------------------+------+

Jalankan kueri fuzzy

Jalankan pernyataan berikut untuk mengkueri data yang memuat `example` pada kolom email.

SELECT * FROM search_table WHERE email LIKE '%example%';

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 1       | Zhang  | 18  | M      | Chaoyang District, Beijing | a***@example.net | Beijing |
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
| 28      | Chen   | 36  | F      | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
+---------+--------+-----+--------+--------------+------------------+------+

Catatan

Kinerja kueri fuzzy bergantung pada jumlah data dalam tabel. Kami menyarankan Anda menggunakan kueri teks bertokenisasi sebagai gantinya.

Agregasi dan analisis

Indeks pencarian mendukung fungsi agregat umum seperti COUNT, SUM, AVG, MIN, dan MAX. Indeks pencarian juga mendukung DISTINCT dan GROUP BY.

Jalankan pernyataan berikut untuk menghitung jumlah baris di mana kolom city bernilai Hangzhou.

SELECT COUNT(*) FROM search_table WHERE city='Hangzhou';

Hasil berikut dikembalikan:

+----------+
| EXPR$0   |
+----------+
| 2        |
+----------+

Jalankan pernyataan berikut untuk menghapus nilai duplikat dari hasil kueri.

SELECT distinct(age) FROM search_table WHERE gender='M';

Hasil berikut dikembalikan:

+---------------+
| DISTINCT(age) |
+---------------+
| 18            |
| 28            |
| 32            |
+---------------+

Jalankan pernyataan berikut untuk mengelompokkan hasil kueri.

SELECT city,count(*) AS cnt FROM search_table WHERE gender='M' GROUP BY city ORDER BY cnt DESC;

Hasil berikut dikembalikan:

+------+-----+
| city | cnt |
+------+-----+
| Hangzhou | 2 |
| Beijing | 1 |
+------+-----+

Catatan

Jika Anda tidak menentukan kondisi WHERE dalam kueri agregat, kueri tersebut tidak akan menggunakan indeks pencarian. Dalam kasus ini, Anda dapat menambahkan parameter HINT _l_force_index_ sebelum kata kunci WHERE untuk memaksa kueri menggunakan indeks pencarian. Untuk informasi selengkapnya, lihat Parameter hintOptions dan Contoh 4: Tentukan _l_force_index_ untuk memaksa penggunaan indeks dalam kueri.

Jalankan kueri teks bertokenisasi

Untuk kolom bertokenisasi, Anda dapat menggunakan fungsi MATCH untuk melakukan kueri teks bertokenisasi.

Penting

Fungsi MATCH didukung di LindormTable versi 2.7.2 dan yang lebih baru. Untuk melihat atau melakukan upgrade versi LindormTable Anda, lihat Catatan rilis LindormTable dan Peningkatan versi minor.

Contoh berikut mengkueri baris di mana nilai age berada dalam rentang (10,30] dan nilai address sesuai dengan Hangzhou. Kolom address bertokenisasi.

SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND MATCH (address) AGAINST ('Hangzhou');

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+

Contoh berikut mengkueri baris di mana age berada dalam rentang (30,35] dan address sesuai dengan frasa persis Yuhang District, Hangzhou, dan kolom address bertokenisasi.

SELECT * FROM search_table WHERE age > 30 AND age <= 35 AND MATCH (address) AGAINST ('"Yuhang District, Hangzhou"');

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id | name   | age | gender | address      | email            | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+

Pada versi LindormTable sebelum 2.7.2, Anda dapat menggunakan operator kesamaan = untuk melakukan kueri teks bertokenisasi.
Penting
- Kueri teks bertokenisasi berbasis kesamaan telah ditinggalkan. Lakukan upgrade ke versi yang lebih baru dan gunakan fungsi MATCH sebagai gantinya. Untuk melihat atau melakukan upgrade versi LindormTable Anda, lihat Catatan rilis LindormTable dan Peningkatan versi minor.
- Mulai dari LindormTable 2.8.2.3, indeks pencarian baru tidak lagi mendukung kueri teks bertokenisasi berbasis kesamaan. Indeks pencarian yang sudah ada masih mendukungnya.
Contoh berikut mengkueri baris di mana nilai age berada dalam rentang (10, 30] dan nilai address sesuai dengan Hangzhou, dengan kolom address bertokenisasi.
```
SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND address = 'Hangzhou';
```
Hasil berikut dikembalikan:
```
+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+
```

Jalankan kueri teks bertokenisasi dengan subfield

Penting

Fitur subfield teks bertokenisasi memerlukan LindormTable 2.8.0 atau yang lebih baru dan LindormSearch yang kompatibel dengan Elasticsearch. Jika versi Anda tidak memenuhi persyaratan ini, lakukan pembaruan versi minor.

Latar Belakang

Saat Anda membuat indeks pencarian, jika Anda mengatur kolom VARCHAR ke tipe bertokenisasi (type=text), sistem menggunakan tokenisasi untuk pencocokan dalam kueri kesamaan dan fuzzy. Hal ini dapat menghasilkan hasil yang tidak terduga.

Sebagai contoh, jika Anda mengonfigurasi kolom address(type=text,analyzer=ik), sistem membagi teks menjadi token berdasarkan kebijakan pemisah kata saat membuat indeks. Misalnya, Binjiang District, Hangzhou menjadi dua token: Hangzhou dan Binjiang District. Akibatnya, kueri fuzzy yang menggunakan LIKE mungkin gagal karena istilah target terpisah di beberapa token.

Pada contoh berikut, District dan Binjiang dipisahkan menjadi token terpisah, sehingga tidak terjadi kecocokan. Kueri tidak mengembalikan hasil apa pun.

SELECT * FROM search_table WHERE city LIKE '%Binjiang District%' LIMIT 10;

Metode Pengaktifan

Untuk memungkinkan kolom VARCHAR mendukung kueri teks bertokenisasi, kueri fuzzy, dan kueri kesamaan secara bersamaan, Anda dapat mengaktifkan fitur subfield teks bertokenisasi.

Untuk mengaktifkan fitur ini, atur atribut kolom menjadi type=string,textSubField=true,analyzer=ik saat membuat indeks. Pilih analyzer berdasarkan kasus penggunaan Anda. Untuk informasi selengkapnya, lihat tipe alat analisis. Berikut adalah contohnya:

CREATE INDEX idx USING SEARCH ON search_table(user_id,address(type=string, textSubField=true, analyzer=ik));

Catatan

Jika indeks pencarian sudah ada dan Anda ingin mengaktifkan fitur subfield teks bertokenisasi, Anda dapat menggunakan Multiple indeks pencarian per tabel tunggal (pratinjau publik) untuk membuat indeks pencarian baru dengan subfield teks bertokenisasi, lalu alihkan kueri ke indeks baru tersebut.

Metode kueri

Secara default, kueri kesamaan dan fuzzy menggunakan pencocokan string penuh. Kueri teks bertokenisasi menggunakan fungsi MATCH. Contoh berikut menunjukkan tiga metode kueri umum beserta hasilnya.

Equivalent Queries

Contoh 1:

SELECT * FROM search_table WHERE address='Hangzhou' LIMIT 10;

Kueri tidak mengembalikan hasil karena tidak ada catatan di kolom address yang persis cocok dengan string Hangzhou.

Contoh 2:

SELECT * FROM search_table WHERE address='Binjiang District, Hangzhou' LIMIT 10;

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+

Kueri fuzzy

Contoh:

SELECT * FROM search_table WHERE address like '%Binjiang%' LIMIT 10;

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+

Kueri teks bertokenisasi

Contoh:

SELECT * FROM search_table WHERE MATCH (address) AGAINST ('Hangzhou') LIMIT 10;

Hasil berikut dikembalikan:

+---------+--------+-----+--------+--------------+------------------+------+
| user_id |  name  | age | gender |   address    |      email       | city |
+---------+--------+-----+--------+--------------+------------------+------+
| 6       | Li     | 32  | M      | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
| 20      | Wang   | 28  | M      | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+--------+-----+--------+--------------+------------------+------+