Query data in a wide table by using a search index - Lindorm: Central de Documentação do Alibaba Cloud

Um índice de busca permite executar filtros multidimensionais, ordenações, consultas paginadas, buscas de texto completo e agregações em uma tabela wide do LindormTable sem varreduras completas na tabela. Este guia apresenta a sintaxe SQL para cada tipo de consulta com exemplos funcionais.

Pré-requisitos

Antes de começar, verifique se você:

Conectou-se ao LindormTable usando o Lindorm-cli. Consulte Conectar e usar o LindormTable com o Lindorm-cli
Preparou os dados de teste e crie um índice de busca. Consulte Gerencie índices de busca

Consultas multidimensionais

Use AND, OR e operadores de comparação para filtrar várias colunas simultaneamente.

Exemplo 1: Retornar todos os usuários do sexo masculino em Hangzhou ou Pequim.

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

Resultado:

+---------+--------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+--------+-----+--------+-----------------------------+------------------+----------+

Exemplo 2: Retornar usuários com mais de 30 anos que não estejam em Hangzhou.

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

Resultado:

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

Ordenação e paginação

O índice de busca suporta ordenação em qualquer coluna. Combine ORDER BY com LIMIT e OFFSET para obter resultados paginados.

Ordenar: Retornar usuários do sexo masculino ordenados por idade em ordem decrescente.

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

Resultado:

+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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  |
+---------+-------+-----+--------+-----------------------------+------------------+----------+

Paginar: Ignorar o primeiro resultado e retornar até 10 registros (LIMIT offset, count).

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

Resultado:

+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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  |
+---------+-------+-----+--------+-----------------------------+------------------+----------+

Consultas difusas

Use o operador LIKE com curingas % para corresponder a strings parciais.

Exemplo: Retornar todos os usuários cujo e-mail contenha example.

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

Resultado:

+---------+-------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+-------+-----+--------+-----------------------------+------------------+----------+

null

O desempenho de consultas difusas diminui conforme o tamanho da tabela aumenta. Para colunas tokenizadas, prefira consultas de texto tokenizado.

Consultas de agregação

Os índices de busca suportam COUNT, SUM, AVG, MIN, MAX, DISTINCT e GROUP BY.

Contar: Contar linhas onde city é Hangzhou.

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

Resultado:

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

Desduplicar: Retornar idades distintas para usuários do sexo masculino.

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

Resultado:

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

Agrupar: Contar usuários do sexo masculino por cidade, ordenados pela contagem.

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

Resultado:

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

null

Sem uma cláusula WHERE, a consulta de agregação não utiliza o índice de busca. Para forçar o uso do índice, adicione o parâmetro HINT _l_force_index_ antes da palavra-chave WHERE. Consulte Parâmetros de hintOptions e Exemplo 4: Especifique _l_force_index_ para forçar o uso de um índice.

Consultas de texto tokenizado

Para colunas configuradas com um tokenizador, use a sintaxe MATCH ... AGAINST para buscar dentro dos limites dos tokens. Essa abordagem é mais rápida e precisa do que consultas difusas em grandes conjuntos de dados.

Importante

A função MATCH requer o LindormTable 2.7.2 ou posterior. Para verificar ou atualizar sua versão, consulte Notas de versão do LindormTable e Atualização de versão secundária.

Todos os exemplos usam a função MATCH na coluna address, configurada como coluna tokenizada.

Correspondência de token (sem aspas): Corresponder a qualquer registro em que a lista de tokens de address contenha Hangzhou, com age no intervalo (10, 30]. Passar o termo de busca sem aspas corresponde a tokens individuais.

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

Resultado:

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

Correspondência de frase (com aspas): Corresponder a registros em que address contém a frase exata Yuhang District, Hangzhou, com age no intervalo (30, 35]. Envolver o termo de busca em aspas duplas dentro da cláusula AGAINST alterna da correspondência de tokens para a correspondência exata de frases.

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

Resultado:

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

Consultas tokenizadas baseadas em igualdade (obsoleto)

Importante

As consultas de texto tokenizado baseadas em igualdade estão obsoletas. A partir do LindormTable 2.8.2.3, novos índices de busca não as suportam mais, mas os índices existentes permanecem inalterados. Atualize para o LindormTable 2.7.2 ou posterior e use a função MATCH. Consulte Notas de versão do LindormTable e Atualização de versão secundária.

Em versões anteriores à 2.7.2, use o operador de igualdade = para consultar colunas tokenizadas:

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

Resultado:

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

Subcampo de texto tokenizado

Importante

Este recurso requer o LindormTable 2.8.0 ou posterior e o LindormSearch compatível com Elasticsearch. Se sua versão não atender a esses requisitos, atualize a versão secundária.

Contexto

Quando uma coluna VARCHAR é definida como tipo tokenizado (type=text), o analisador divide os valores armazenados em tokens durante a indexação. Por exemplo, com o analisador IK, Binjiang District, Hangzhou é dividido em dois tokens: Hangzhou e Binjiang District.

Como a string original é substituída por seus tokens, consultas difusas com LIKE na string bruta podem não retornar resultados:

-- 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;

O recurso de subcampo de texto tokenizado resolve isso armazenando tanto a string original completa (para correspondência exata e difusa) quanto seus tokens (para consultas MATCH) na mesma coluna do índice.

Ative o recurso

Defina o atributo da coluna como type=string,textSubField=true,analyzer=ik ao criar o índice. Escolha o analyzer com base no seu caso de uso. Consulte tipo de analisador.

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

Nota

Se já existir um índice de busca, use Vários índices de busca em tabela única (visualização pública) para criar um novo índice com o subcampo ativado e direcione as consultas para esse novo índice.

Métodos de consulta

Com o subcampo de texto tokenizado ativado, a coluna address suporta três tipos de consulta simultaneamente.

Consultas equivalentes usam correspondência de string completa no valor original armazenado.

-- 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;

Resultado:

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

Consultas difusas usam LIKE no valor original armazenado.

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

Resultado:

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

Consultas de texto tokenizado usam a função MATCH no índice de tokens.

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

Resultado:

+---------+------+-----+--------+-----------------------------+------------------+----------+
| 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 |
+---------+------+-----+--------+-----------------------------+------------------+----------+

A consulta MATCH retorna ambas as linhas porque ambos os endereços contêm o token Hangzhou, mesmo que suas strings completas sejam diferentes.