Introdução

O MySQL é um bom banco de dados completo, mas, para aplicativos com uso pesado em pesquisa, é possível obter um melhor resultado procurando em utilitários de procura específica. Este artigo considera o Sphinx, um pacote de procura de texto completo bem conhecido, uma vez que a substituição do MySQL para procuras, aumentando a velocidade mesmo para procuras que não de texto completo. O artigo estuda os prós e contras e advertências envolvidos nessa mudança, demonstra alguns testes específicos e considera o que é necessário para usar o Sphinx de maneira geral.

MySQL ou Sphinx?

Por que você consideraria substituir o Sphinx pelo MySQL? Considere, por exemplo, um aplicativo de procura de um vendedor de livros. Um usuário poderia procurar um livro especificando o título, o autor, o status (novo ou usado), edição (primeira ou posterior), capa (dura ou brochura), editora, ano de publicação, se é autografado pelo autor, preço, entre outros. O MySQL tipicamente não usa mais de um índice (uma exceção é otimização de mesclagem de índice; consulte Recursos), de modo que a única maneira de otimizar todas as procuras possíveis é ter um número proibitivo de índices de diversas colunas, o que não é bom.

Entretanto, o Sphinx é um mecanismo de procura que pode integrar-se bem com o MySQL e funciona por si próprio de maneira independente. Ele fornece alto desempenho de procura e indexação e permite consultas por meio de SphinxQL, uma linguagem com base em Structured Query Language (SQL). Por fim, o Sphinx escala bem, sendo capaz de funcionar com bilhões de documentos abrangendo terabytes de dados com capacidades de procura distribuídas.

O Sphinx funciona com documentos (que podem ser simplesmente registros em uma visualização ou tabela de banco de dados), campos de texto (que indexa, fornecendo procuras de texto completo) e classificação de atributos (valores não de texto que podem ser usados para filtragem, classificação e agrupamento de resultados). Os atributos são mantidos em uma memória de acesso aleatório (RAM) para eficiência; consulte a documentação do Sphinx nos Recursos para a fórmula de cálculo de tamanho real.

Para processar consultas, o Sphinx depende de arquivos de índice especiais. É preciso definir as origens de dados para o procedimento de indexação e então executar o programa indexer . Outra possibilidade é usar arquivos de índice em tempo real, que podem ser atualizados rapidamente ao custo de uma eficiência um pouco reduzida. Isso é abordado em mais detalhes mais adiante no artigo.

Um problema de amostra

Para testar as velocidades de procura do Sphinx e MySQL, foram buscados alguns conjuntos de dados interessantes (em tamanho), e foi encontrado um banco de dados de teste com cerca de 3 milhões de registros de salário, o que pareceu bom o bastante para os objetivos deste artigo. (Consulte Recursos para obter esses dados.) O esquema é simples: você tem funcionários com nomes de cargos que trabalham em departamentos e ganham salários anuais. Essa amostra funciona com as duas maiores tabelas: employees e salaries.

Após instalar os dados, descobri a necessidade de corrigir alguns detalhes para a tabela salaries . Primeiro, o campo to_date às vezes continha 9999-01-01 como marcador especial; substituí por 2038-01-01 porque os registros de tempo do UNIX® não pode ir além de 2038. Além disso, o Sphinx requer que todo registro tenha uma chave de ID de campo única, então precisei adicionar um campo de incremento automático salaries_id à tabela salaries . Consulte a Listagem 1.

                
ALTER TABLE salaries
  DROP PRIMARY KEY,
  ADD UNIQUE emp_from (emp_no, from_date) ;
ALTER TABLE salaries
  ADD salaries_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ;
UPDATE salaries
  SET to_date="2038-01-01" WHERE to_date="9999-01-01";
            

Agora considere as consultas mostradas na Listagem 2.

                
SELECT *
  FROM employees.employees
  WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30'
    AND hire_date >= '1998-01-01';
38 rows in set (0.19 sec)

SELECT *
  FROM employees.employees eee JOIN employees.salaries sss
  ON sss.emp_no=eee.emp_no
  WHERE eee.first_name='Yucel'
    AND sss.salary>120000
    AND sss.from_date >= '2000-01-01';
5 rows in set (0.15 sec)
            

A primeira procura envolve apenas employees e procura pessoas nascidas em setembro de 1960 e contratadas em 1998 ou depois. O MySQL encontra 38 registros em aproximadamente 0,19 segundo. (É claro, seus resultados podem variar.) A segunda procura une employees e salaries para encontrar trabalhadores masculinos chamados Yucel que ganharam mais de 120 mil em 2000 ou depois. O MySQL encontra cinco registros em cerca de 0,15 segundo.

Para executar essas pesquisas com Sphinx, é preciso configurá-lo, indexar os dados e executar um daemon. A próxima seção aborda essas tarefas.

Configurando Sphinx

Origens de dados Sphinx de acordo com as definições no arquivo sphinx.conf , mostrado na Listagem 3. Observe as conversões de tipo para os campos de data. Este artigo mostra os principais parâmetros requeridos, mas é preciso estar ciente de que há muitas outras definições de configuração com as quais é possível trabalhar.

                
source employeesSource
{
  type = mysql
  sql_host = localhost
  sql_user = mysqluser
  sql_pass = mysqlpass
  sql_db = employees
  sql_query= SELECT \
    emp_no as id, \
    TO_DAYS(birth_date) AS birth_date_td, \
    first_name, \
    last_name, \
    gender, \
    TO_DAYS(hire_date) AS hire_date_td \
    FROM employees
  sql_attr_uint = birth_date_td
  sql_field_string = first_name
  sql_field_string = last_name
  sql_field_string = gender
  sql_attr_uint = hire_date_td
}

source employeesSalariesSource
{
  type = mysql
  sql_host = localhost
  sql_user = mysqluser
  sql_pass = mysqlpass
  sql_db = employees
  sql_query= SELECT \
    ss.salaries_id AS id, \
    ee.emp_no AS emp_no, \
    TO_DAYS(ee.birth_date) AS birth_date_td, \
    ee.first_name AS first_name, \
    ee.last_name AS last_name, \
    ee.gender AS gender, \
    TO_DAYS(ee.hire_date) AS hire_date_td, \
    ss.salary AS salary, \
    UNIX_TIMESTAMP(ss.from_date) AS from_date_ts, \
    UNIX_TIMESTAMP(ss.to_date) AS to_date_ts \
    FROM employees ee JOIN salaries ss \
    ON ss.emp_no=ee.emp_no
  sql_attr_uint = emp_no
  sql_attr_timestamp = birth_date_ts
  sql_field_string = first_name
  sql_field_string = last_name
  sql_field_string = gender
  sql_attr_uint = hire_date_td
  sql_attr_uint = salary
  sql_attr_timestamp = from_date_ts
  sql_attr_timestamp = to_date_ts
}
            

O arquivo de configuração é dividido em sub-rotinas. A parte de origem define as origens de dados. O Sphinx pode funcionar com muitos tipos de arquivos, incluindo texto, Linguagem de Marcação de Hipertexto (HTML) e Linguagem de Marcação Extensível (XML). Entretanto, este exemplo usa apenas MySQL, e, assim, type=mysql. Os parâmetros sql_host, sql_user, sql_pass e sql_db definem como acessar o banco de dados e qual esquema usar. O parâmetro sql_query fornece a sentença SQL que recupera os dados a serem indexados. Essencialmente, a única restrição aqui é que o primeiro campo deve ser um número de ID inteiro positivo único não assinado. É por isso que foi preciso adicionar um campo salaries_id à tabela salaries anteriormente. É possível especificar até 32 campos de texto e um número arbitrário de atributos. O texto completo Sphinx indexa todas as colunas, exceto ID (o primeiro campo) e atributos.

Como é possível ver na Tabela 1, o Sphinx tem suporte a vários tipos de atributos, mas não todos os tipos de dados MySQL possíveis. Há também alguns tipos de atributos específicos do Sphinx, porém, eles não são aplicáveis se você estiver apenas buscando substituição MySQL, então eles não foram incluídos aqui.

A parte de índice dos arquivos de configuração descreve os atributos das origens (Listagem 4).

                
index employeesIndex
{
  type = plain
  source = employeesSource
  path = /home/fkereki/bin/sphinx/var/data/sphinxFilesEI
  charset_type = utf-8
  preopen = 1
}

index employeesSalariesIndex
{
  type = plain
  source = employeesSalariesSource
  path = /home/fkereki/bin/sphinx/var/data/sphinxFilesESI
  charset_type = utf-8
  preopen = 1
}
            

O Sphinx usa arquivos de índice que são separados daqueles usados pelo MySQL. A linha type=plain significa que você está usando os arquivos de índice Sphinx padrão. Outras possibilidades são distributed (quando os arquivos de índice estão distribuídos por vários nós na rede) e rt (que significa tempo real). É possível atualizar esses índices rapidamente. A linha source= relaciona uma origem de dados e um índice. É possível de fato mesclar várias origens de dados, mas este exemplo não faz isso. A linha path= define o nome do arquivo de índice e onde ele está armazenado. A linha charset_type= especifica se você trabalhará com conjunto de caracteres de byte único (sbcs) ou com o formato de transformação conjunto de caracteres universais (UCS) - 8 bits (utf-8). Por fim, preopen=1 diz ao daemon de procura para abrir todos os arquivos de índice ao carregar, em vez de esperar as primeiras consultas chegarem.

As últimas sub-rotinas no arquivo de configuração estão relacionadas com os aplicativos indexer e searchd (Listagem 5).

                
indexer
{
  mem_limit = 1024M
}

searchd
{
  listen = 127.0.0.1:9306:mysql41
  log = /home/fkereki/bin/sphinx/var/log/searchd.log
  query_log = /home/fkereki/bin/sphinx/var/log/query.log
  pid_file = /home/fkereki/bin/sphinx/var/log/searchd.pid
}
            

É preciso definir um tamanho de RAM mem_limit para dar ao indexer memória suficiente para trabalhar. As definições searchd são bastante autoexplicativas, exceto por listen=. Esse parâmetro é usado para especificar em qual endereço IP e porta o protocolo de rede binário SphinxQL está disponível. Se você desejar interagir com o protocolo diretamente, é possível usar o cliente MySQL padrão inserindo mysql -P 9306 e executar consultas sem sequer executar o programa MySQL mysqld na máquina.

Agora que tudo está configurado, é possível simplesmente indexar os dados e começar a executar o daemon de procura (Listagem 6). O parâmetro ―all significa que todos os arquivos de índice serão gerados.

                
~/bin/sphinx/etc> ../bin/indexer ―all
Sphinx 2.0.1-beta (r2792)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file './sphinx.conf'...
indexing index 'employeesSalariesIndex'...
collected 2844047 docs, 40.9 MB
sorted 8.5 Mhits, 100.0% done
total 2844047 docs, 40877736 bytes
total 24.842 sec, 1645464 bytes/sec, 114482.32 docs/sec
indexing index 'employeesIndex'...
collected 300024 docs, 4.3 MB
sorted 0.9 Mhits, 100.0% done
total 300024 docs, 4311224 bytes
total 0.914 sec, 4714945 bytes/sec, 328119.56 docs/sec
total 7 reads, 0.274 sec, 19695.2 kb/call avg, 39.2 msec/call avg
total 110 writes, 0.528 sec, 2351.0 kb/call avg, 4.8 msec/call avg

~/bin/sphinx/etc> ../bin/searchd
Sphinx 2.0.1-beta (r2792)
Copyright (c) 2001-2011, Andrew Aksyonoff
Copyright (c) 2008-2011, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file './sphinx.conf'...
listening on 127.0.0.1:9306
precaching index 'employeesSalariesIndex'
precaching index 'employeesIndex'
precached 2 indexes in 0.124 sec
            

Agora que seus dados estão indexados e que o daemon requerido está em execução, você está pronto para executar algumas procuras.

Executando procuras

O método preferido para consultar o Sphinx é usar SphinxQL. Qualquer linguagem que funcione com MySQL também funcionará com SphinxQL. Este exemplo usa PHP, e o código é mostrado na Listagem 7.

                
$bd0 = mysql_connect('localhost:3306', 'mysqluser','mysqlpass');
$bd1 = mysql_connect('localhost:9306');

echo "FIRST TEST ... SINGLE TABLE\n\n";

$td1 = to_days("1960-09-01");
$td2 = to_days("1960-09-30");
$td3 = to_days("1998-01-01");

do_time("test 1 - MySQL ", "SELECT * FROM employees.employees ".
  "WHERE birth_date BETWEEN '1960-09-01' AND '1960-09-30' ".
  "AND hire_date >= '1998-01-01'", $bd0);

do_time("test 1 - Sphinx", "SELECT * FROM employeesIndex ".
  "WHERE birth_date_td BETWEEN {$td1} AND {$td2} ".
  "AND hire_date_td >= {$td3} LIMIT 0,10000 ", $bd1);

echo "\nSECOND TEST ... JOIN\n\n";

$ts1 = mktime(0,0,0,1,1,2000);

do_time("test 2 - MySQL ", "SELECT * ".
  "FROM employees.employees eee JOIN employees.salaries sss ".
  "ON sss.emp_no=eee.emp_no ".
  "WHERE eee.first_name='Yucel' ".
  "AND sss.salary>120000 ".
  "AND sss.from_date >= '2000-01-01'", $bd0);

do_time("test 2 - Sphinx", "SELECT * FROM employeesSalariesIndex ".
  "WHERE MATCH('@first_name Yucel') ".
  "AND salary>120000 ".
  "AND from_date_ts>={$ts1} limit 0,10000 ", $bd1);


function to_days($date) {
  return 719528 + floor(strtotime($date)/(60*60*24));
}


function do_time($description, $sentence, $bd) {
  $m0 = microtime(true);
  $res= @mysql_query($sentence, $bd);
  $m1 = microtime(true);
  $nr = mysql_num_rows($res);
  echo $description." ".$nr." rows in ".
    sprintf("%6.4f", $m1-$m0)." secs\n";
}
            

De acordo com a especificação na Listagem 5, as consultas SphinxQL devem ser enviadas para a porta 9306. Porque não há PHP equivalente para a função MySQL TO_DAYS() , escrevi a minha própria. Observe que mktime funciona para conversões UNIX_TIMESTAMP. A função do_time executa e sincroniza uma dada consulta em um servidor especificado.

As consultas do SphinxQL diferem das consultas MySQL das seguintes maneiras:

• Você usa MATCH para consultar campos de texto.
• É preciso converter todas as datas para registros de hora ou para números inteiros. Este exemplo usa ambos os métodos apenas para variedade.
• O Sphinx pode retornar registros inteiros ou apenas campos do ID, o que for mais eficiente. É claro, se você usar a última opção, deve usar MySQL para recuperar o restante da informação.
• Os operadores AND e OR têm a mesma prioridade, então tenha cuidado e use parênteses quando adequado.
• Nem todas as funções numéricas, cadeias de caracteres e outras funções MySQL são fornecidas pelo Sphinx.

Executar apenas alguns testes não é uma prova de conceito suficientemente boa, mas os resultados mostrados na Listagem 8 sugerem que a mudança de MySQL para Sphinx pode se mostrar útil.

                
~/bin/sphinx/etc> php test.php
FIRST TEST ... SINGLE TABLE

test 1 - MySQL  38 rows in 0.1912 secs
test 1 - Sphinx 38 rows in 0.0157 secs

SECOND TEST ... JOIN

test 2 - MySQL  5 rows in 0.1532 secs
test 2 - Sphinx 5 rows in 0.0020 secs
            

Esses resultados são bons, mas os exemplos até agora consideram apenas procuras estáticas, que assumem tabelas constantes. Você ainda precisa olhar para o problema de atualizar arquivos de índice.

Atualização seus arquivos de índice

O que acontece se os dados originais foram atualizados? Você deve atualizar seus arquivos de índice ou as procuras começarão a produzir os resultados errados. É possível reindexar tudo após cada atualização, mas isso provavelmente seria dispendioso demais! O Sphinx fornece duas soluções: os arquivos de índice delta e atualizações de índice de produção.

Frequentemente, você tem um grande conjunto de dados com um pequeno número de registros novos adicionados de vez em quando. Para este exemplo, presuma que os registros antigos não são modificados depois de terem sido gravados pela primeira vez. Você pode ter atualizações de índice quase em tempo real implementando o esquema principal+delta. A ideia é ter um índice para os dados antigos fixos e outro para os dados novos, que pode ser criado rapidamente em função do seu tamanho menor. Então, tudo o que se precisa fazer é consultar ambos os arquivos de índice e usar a união de ambos os resultados. Considere usar mesclagem de índice mesclando um índice anterior e um índice delta (consulte a documentação do Sphinx nos Recursos para ver os detalhes).

O que acontece se os dados antigos puderem ser modificados ou se você verdadeiramente precisar de atualizações em tempo real? Os arquivos de índice em tempo real do Sphinx são a solução, pois permitem comandos INSERT , REPLACE e DELETE para afetar os arquivos de índice em tempo real. Sempre que você atualizar as tabelas principais, deve ter cuidado para também executar as atualizações de índice correspondentes para garantir que não haja diferenças entre os dados MySQL e Sphinx. Por segurança, verifique as advertências e restrições na Seção 4.2 da documentação do Sphinx (consulte os Recursos).

Avaliação final

Que conclusões podem ser tiradas? Primeiro, usar o Sphinx em vez de MySQL pode fornecer vantagens de desempenho significativas. O Sphinx é muito bom para procurar tabelas estáticas. Entretanto, para tabelas frequentemente atualizadas, é possível usar os arquivos de índice plain . Em vez disso, é preciso implementar arquivos delta ou alterar para indexação em tempo real, e ambas as soluções têm um custo de desempenho extra. Por fim, usar o Sphinx com eficiência requer algum planejamento, pois é preciso predefinir todas as origens e arquivos de índice requeridos — é claro, essa não é uma desvantagem, apenas bom senso.

Substituir o Sphinx por MySQL não é tão trivial, mas também não é tão complicado quanto eliminar a opção. Se velocidade de procura rápida for uma exigência, vale a pena considerar uma mudança de MySQL para Sphinx, mesmo que você não realize procuras de texto completo extensas.

Recursos

Aprender

• Documentação do Sphinx: conheça mais seus recursos.
  
  
• Build a custom search engine with PHP (developerWorks, julho de 2007): explore mais sobre pesquisas em texto completo.
  
  
• MySQL: Otimização de mesclagem de índice: descubra mais sobre esse método.
  
  
• developerWorks no Twitter: siga-nos para acompanhar as últimas notícias.
  
  
• Zona de software livre do developerWorks: encontre informações práticas, ferramentas e atualizações de projeto amplas para ajudá-lo a desenvolver com tecnologias de software livre e utilizá-las com produtos IBM.
  
  
• Eventos interessantes: confira futuras conferências, exposições e webcasts interessantes para desenvolvedores de software livre IBM.
  
  
• Podcasts do developerWorks: escute entrevistas e explicações interessantes para desenvolvedores de software
  
  
• demos gratuitas on demand do developerWorks: Acompanhe nossas demos gratuitas e saiba mais sobre as tecnologias IBM e de software livre e funções dos produtos.
  
  

Obter produtos e tecnologias

• Sphinx: obtenha a última versão e teste o código neste artigo.
  
  
• O banco de dados de amostra usado neste artigo: faça o download no website The Data Charmer.
  
  
• Avalie produtos de software IBM: a partir de downloads de teste para produtos hospedados na nuvem, é possível inovar no seu próximo projeto de desenvolvimento de software livre usando software especialmente para desenvolvedores.
  
  

Discutir

• comunidade do developerWorks: Conecte-se a outros usuários do developerWorks enquanto explora os blogs, fóruns, grupos e wikis voltados para desenvolvedores. Ajude a desenvolver o software livre do mundo real na comunidade do developerWorks.
  
  

Sobre o autor

Federico Kereki é engenheiro de sistemas uruguaio com mais de 20 anos de experiência desenvolvendo sistemas, fazendo trabalho de consultoria e lecionando em universidades. Ele trabalha com software livre há mais de 10 anos e aprecia particularmente a maior segurança do Linux. Ele recentemente escreveu Essential GWT, um livro sobre essa ferramenta de software livre.

Classificar este artigo

Comentários

Voltar para parte superior

Índice

• Introdução
• MySQL ou Sphinx?
• Um problema de amostra
• Configurando Sphinx
• Executando procuras
• Atualização seus arquivos de índice
• Avaliação final
• Recursos
• Sobre o autor
• Comentários

Conheça a IBM da sua cidade

A IBM está mais perto do que você imagina!

---

Saiba mais