Un index de recherche permet d'appliquer des filtres multidimensionnels, des tris, des requêtes paginées, des recherches en texte intégral et des agrégations sur une table large LindormTable sans analyser la table entière. Ce guide présente la syntaxe SQL de chaque type de requête avec des exemples fonctionnels.
Prérequis
Avant de commencer, assurez-vous :
D'être connecté à LindormTable via Lindorm-cli. Consultez Connexion et utilisation de LindormTable avec Lindorm-cli
D'avoir préparé les données de test et créé un index de recherche. Consultez Gestion des index de recherche
Requêtes multidimensionnelles
Utilisez les opérateurs AND, OR et les opérateurs de comparaison pour filtrer simultanément sur plusieurs colonnes.
Exemple 1 : Renvoyez tous les utilisateurs masculins situés à Hangzhou ou à Pékin.
SELECT * FROM search_table WHERE gender='M' AND city='Hangzhou' OR city='Beijing';Résultat :
+---------+--------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+--------+-----+--------+-----------------------------+------------------+----------+Exemple 2 : Renvoyez les utilisateurs âgés de plus de 30 ans qui ne résident pas à Hangzhou.
SELECT * FROM search_table WHERE age > 30 AND city != 'Hangzhou';Résultat :
+---------+------+-----+--------+----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+----------------------------+------------------+----------+
| 28 | Chen | 36 | F | Nanshan District, Shenzhen | a***@example.net | Shenzhen |
+---------+------+-----+--------+----------------------------+------------------+----------+Tri et pagination
Un index de recherche prend en charge le tri sur n'importe quelle colonne. Combinez ORDER BY avec LIMIT et OFFSET pour obtenir des résultats paginés.
Tri : Renvoyez les utilisateurs masculins triés par âge décroissant.
SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC;Résultat :
+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+Pagination : Ignorez le premier résultat et renvoyez jusqu'à 10 enregistrements (syntaxe LIMIT offset, count).
SELECT * FROM search_table WHERE gender='M' ORDER BY age DESC LIMIT 1,10;Résultat :
+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+Requêtes floues
Utilisez l'opérateur LIKE avec les caractères génériques % pour faire correspondre des chaînes partielles.
Exemple : Renvoyez tous les utilisateurs dont l'e-mail contient example.
SELECT * FROM search_table WHERE email LIKE '%example%';Résultat :
+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+Les performances des requêtes floues se dégradent à mesure que la taille de la table augmente. Pour les colonnes tokenisées, privilégiez les requêtes de texte tokenisé.
Requêtes d'agrégation
Les index de recherche prennent en charge les fonctions COUNT, SUM, AVG, MIN, MAX, DISTINCT et la clause GROUP BY.
Comptage : Comptez les lignes où la valeur de city est Hangzhou.
SELECT COUNT(*) FROM search_table WHERE city='Hangzhou';Résultat :
+--------+
| EXPR$0 |
+--------+
| 2 |
+--------+Déduplication : Renvoyez les âges distincts des utilisateurs masculins.
SELECT distinct(age) FROM search_table WHERE gender='M';Résultat :
+---------------+
| DISTINCT(age) |
+---------------+
| 18 |
| 28 |
| 32 |
+---------------+Groupement : Comptez les utilisateurs masculins par ville, triés par nombre.
SELECT city, count(*) AS cnt FROM search_table WHERE gender='M' GROUP BY city ORDER BY cnt DESC;Résultat :
+----------+-----+
| city | cnt |
+----------+-----+
| Hangzhou | 2 |
| Beijing | 1 |
+----------+-----+Sans clause WHERE, une requête d'agrégation n'utilise pas l'index de recherche. Pour forcer l'utilisation de l'index, ajoutez le paramètre HINT _l_force_index_ avant le mot-clé WHERE. Consultez Paramètres de hintOptions et Exemple 4 : Spécifier _l_force_index_ pour forcer l'utilisation d'un index.
Requêtes de texte tokenisé
Pour les colonnes configurées avec un tokenizer, utilisez la syntaxe MATCH ... AGAINST pour effectuer des recherches au sein des limites des tokens. Cette méthode est plus rapide et plus précise que les requêtes floues sur les grands ensembles de données.
La fonction MATCH nécessite LindormTable version 2.7.2 ou ultérieure. Pour vérifier ou mettre à jour votre version, consultez Notes de version de LindormTable et Mise à jour mineure.
Tous les exemples utilisent la fonction MATCH sur la colonne address, qui est configurée comme une colonne tokenisée.
Correspondance de token (sans guillemets) : Renvoyez tout enregistrement dont la liste de tokens de la colonne address contient Hangzhou, avec un champ age compris dans l'intervalle ]10, 30]. La transmission du terme de recherche sans guillemets permet de faire correspondre des tokens individuels.
SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND MATCH (address) AGAINST ('Hangzhou');Résultat :
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Correspondance d'expression exacte (avec guillemets) : Renvoyez les enregistrements où la colonne address contient l'expression exacte Yuhang District, Hangzhou, avec un champ age compris dans l'intervalle ]30, 35]. L'encapsulation du terme de recherche entre guillemets doubles dans la clause AGAINST bascule de la correspondance de token à la correspondance d'expression exacte.
SELECT * FROM search_table WHERE age > 30 AND age <= 35 AND MATCH (address) AGAINST ('"Yuhang District, Hangzhou"');Résultat :
+---------+------+-----+--------+---------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+---------------------------+------------------+----------+
| 6 | Li | 32 | M | Yuhang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+---------------------------+------------------+----------+Requêtes tokenisées basées sur l'égalité (obsolètes)
Les requêtes de texte tokenisé basées sur l'égalité sont obsolètes. À partir de LindormTable 2.8.2.3, les nouveaux index de recherche ne les prennent plus en charge ; les index existants restent inchangés. Mettez à niveau vers LindormTable 2.7.2 ou une version ultérieure et utilisez la fonction MATCH à la place. Consultez Notes de version de LindormTable et Mise à jour mineure.
Dans les versions antérieures à la 2.7.2, utilisez l'opérateur d'égalité = pour interroger les colonnes tokenisées :
SELECT * FROM search_table WHERE age > 10 AND age <= 30 AND address = 'Hangzhou';Résultat :
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Sous-champ de texte tokenisé
Cette fonctionnalité nécessite LindormTable 2.8.0 ou une version ultérieure, ainsi que LindormSearch compatible avec Elasticsearch. Si votre version ne remplit pas ces conditions, mettez à jour la version mineure.
Contexte
Lorsqu'une colonne VARCHAR est définie sur le type tokenisé (type=text), l'analyseur divise les valeurs stockées en tokens au moment de l'indexation. Par exemple, avec l'analyseur IK, Binjiang District, Hangzhou est divisé en deux tokens : Hangzhou et Binjiang District.
Étant donné que la chaîne d'origine est remplacée par ses tokens, les requêtes floues LIKE sur la chaîne brute peuvent ne renvoyer aucun résultat :
-- Returns no results: the IK analyzer splits 'Binjiang District, Hangzhou' into
-- [Hangzhou] and [Binjiang District], so '%Binjiang District%' has no raw string to match.
SELECT * FROM search_table WHERE city LIKE '%Binjiang District%' LIMIT 10;La fonctionnalité de sous-champ de texte tokenisé résout ce problème en stockant à la fois la chaîne complète d'origine (pour la correspondance exacte et floue) et ses tokens (pour les requêtes MATCH) dans la même colonne d'index.
Activer la fonctionnalité
Définissez l'attribut de colonne sur type=string,textSubField=true,analyzer=ik lors de la création de l'index. Choisissez l'analyzer en fonction de votre cas d'utilisation. Consultez type d'analyseur.
CREATE INDEX idx USING SEARCH ON search_table(user_id, address(type=string, textSubField=true, analyzer=ik));Si un index de recherche existe déjà, utilisez Index de recherche multiples par table (aperçu public) pour créer un nouvel index avec le sous-champ activé, puis basculez les requêtes vers le nouvel index.
Méthodes de requête
Avec le sous-champ de texte tokenisé activé, la colonne address prend en charge simultanément trois types de requêtes.
Les requêtes d'équivalence utilisent la correspondance de chaîne complète sur la valeur stockée d'origine.
-- No results: no record has address exactly equal to 'Hangzhou'
SELECT * FROM search_table WHERE address='Hangzhou' LIMIT 10;-- Returns user_id 20: the full string 'Binjiang District, Hangzhou' matches exactly
SELECT * FROM search_table WHERE address='Binjiang District, Hangzhou' LIMIT 10;Résultat :
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Les requêtes floues utilisent LIKE sur la valeur stockée d'origine.
SELECT * FROM search_table WHERE address LIKE '%Binjiang%' LIMIT 10;Résultat :
+---------+------+-----+--------+-----------------------------+------------------+----------+
| user_id | name | age | gender | address | email | city |
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 20 | Wang | 28 | M | Binjiang District, Hangzhou | a***@example.net | Hangzhou |
+---------+------+-----+--------+-----------------------------+------------------+----------+Les requêtes de texte tokenisé utilisent la fonction MATCH sur l'index de tokens.
SELECT * FROM search_table WHERE MATCH (address) AGAINST ('Hangzhou') LIMIT 10;Résultat :
+---------+------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+------+-----+--------+-----------------------------+------------------+----------+La requête MATCH renvoie les deux lignes car les deux adresses contiennent le token Hangzhou, bien que leurs chaînes complètes diffèrent.