(Resolvido) Consulta/gravação de grande volume de registros em indices

[Spyder.RV]

Tenho uma tabela relativamente grande onde gravo informações que pego de diversos feeds RSS... atualmente essa tabela possui um pouco mais de 2.000.000 de registros.

Já achei muitos casos de feeds é usam campos diferenciados como registro único (um link, um código, um texto)... então eu tenho 2 campos na tabela para refletir essa realidade(feed_unico_campo e feed_unico_valor)... assim faço a checagem dos feeds novos que chegam para verificar se já existe ocorrência no banco de dados... até aí tranquilo. O problema é que isso gera um certo gargalo nas consultas ao banco de dados fazer consultas do tipo LIKE "String de texto longo%" sacrifica e muito a performance... ainda mais com zilhões de novas ocorrências chegando a toda hora.

Mudei então para indices FULLTEXT adicionando um indice para o campo feed_unico_valor. E parece ser uma boa saída, mas estou com uma dúvida cruel: Melhor seria fazer UMA CONSULTA ÚNICA, passando todos os campos únicos como parâmetro para a pesquisa FULLTEXT; ou percorrer os itens UM A UM e consultando se existem no banco para só então inserir o item que não for encontrado?

Detalhe: Uso PHP/PDO para a manipulação desses feeds.

Editado Dezembro 11, 2012 por Denis Courcy

[Kakao]

Qual é o tamanho da string de texto? Uma opção é salvar o hash das strings concatenadas em uma coluna BINARY:

create table t (
    feed_unico_campo text,
    feed_unico_valor text,
    md5_hash binary(16)
);

insert into t (feed_unico_campo, feed_unico_valor, md5_hash)
values
('ckslsdkdi', 'kdksoskd', unhex(md5(concat('ckslsdkdi', 'kdksoskd'))))
;

select *
from t
where md5_hash = unhex(md5(concat('ckslsdkdi', 'kdksoskd')))
;

A chance de colisão de um hash md5 (128 bits) é insignificante:

http://en.wikipedia.org/wiki/Birthday_para...obability_table

Editado Dezembro 4, 2012 por Kakao

[Spyder.RV]

Cara... Como fico preso na estrutura de cada fonte RSS, tive que definir um varchar(255) pra comportar tanto campos numéricas quanto links ou texto puro, nunca passou de 255.

Sua idéia é interessante... Essa questão de colisão não é tão importante, pois se ocorrer a colisão a consequência é um ou outro item não ser gravado e isso não é tão importante!

Só não entendi bem a aplicação de fazer essa hash... é pra melhorar a performance na hora de fazer um SELECT nele?

[Kakao]

Só não entendi bem a aplicação de fazer essa hash... é pra melhorar a performance na hora de fazer um SELECT nele?

Exato. Uma busca em um campo binário indexado vai ser muito mais rápida do que um full text search. Se a consequência da colisão não é importante então você pode diminuir o tamanho do binary para 8 por exemplo para ficar mais eficiente:

create table t (
    feed_unico_campo text,
    feed_unico_valor text,
    md5_hash binary(8)
);

insert into t (feed_unico_campo, feed_unico_valor, md5_hash)
values
('ckslsdkdi', 'kdksoskd', unhex(left(md5(concat('ckslsdkdi', 'kdksoskd')), 16)))
;

select *
from t
where md5_hash = unhex(left(md5(concat('ckslsdkdi', 'kdksoskd')), 16))
;

[Spyder.RV]

Entendi... no caso devo criar a coluna pro hash em binário ou da forma que você orientou?

create table t (
    feed_unico_campo text,
    feed_unico_valor text,
    md5_hash binary(8)
    md5_hash binary_2(8) CHARACTER SET binary, # OU assim
    md5_hash binary_3(8) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, # OU assim ?
);

Outra dúvida é quanto ao tipo de indice para essa coluna... crio um INDEX normal ou tem mais alguma opção a ser cnofigurada?

Editado Dezembro 4, 2012 por Spyder.RV

[Kakao]

É só "md5_hash binary(8)". Índice normal btree.

[Spyder.RV]

É só "md5_hash binary(8)". Índice normal btree.

Saquei... e quanto a usar completo (16) ou (8) vai interferir tanto na performance? Ou seria só forçar o aumento desnecessário de espaço em disco (campo + índice)?

fiz uns select benchmark no mysql agora com um hash completo e com a metade e o resultado foi praticamente o mesmo:

SELECT BENCHMARK(100000000, 'lorem ipsum dolor sit amet' = BINARY 'd9c5690031866d8b5b7171ed390cadfd'); - 2.07s

SELECT BENCHMARK(100000000, 'lorem ipsum dolor sit amet' = BINARY 'd9c5690031866d8b'); - 2.06s

Fiz outras comparações também e o resultado foi interessante...

SELECT BENCHMARK(100000000, 'lorem ipsum dolor sit amet' LIKE '%LOREM IPSUM DOLOR SIT AMET%'); - 7.57s

SELECT BENCHMARK(100000000, 'lorem ipsum dolor sit amet' LIKE 'LOREM IPSUM DOLOR SIT AMET%'); - 3.77s

SELECT BENCHMARK(100000000, 'lorem ipsum dolor sit amet' = 'LOREM IPSUM DOLOR SIT AMET'); - 3.06s

Editado Dezembro 4, 2012 por Spyder.RV

[Kakao]

Com tamanho 16 o índice vai ter o dobro do tamanho e portanto vai levar mais tempo para ser percorrido. A diferença real só poderá ser testada com uma tabela grande. Talvez seja pouco ou talvez seja mais. Só testando.

[Spyder.RV]

Quanto aos testes vou fazer um tabelão com uns 20 milhões de registros e fazer alguns testes e quando tiver pronto eu compartilho o resultado aqui!

Editado Dezembro 4, 2012 por Spyder.RV

[Spyder.RV]

Olha só... criei um campo binário da forma que foi comentado aqui usando todo o campo:

ALTER TABLE  `fee_itens` ADD  `ite_md5` BINARY( 16 ) NULL;

E em seguida atribui os valores a ele, usando como base o campo que estava sendo usado como campo único:

update fee_itens set ite_md5 = unhex( md5(ite_unico) );

Finalmente rodei uma query para checar se os valores batem:

SELECT ite_unico, ite_md5, UNHEX( MD5( ite_unico ) ) AS conf_md5 FROM fee_itens
ORDER BY ite_codigo DESC 
LIMIT 5

Bateu certinho e já notei que é rapidinho...

Editado Dezembro 4, 2012 por Spyder.RV

[Kakao]

Você setou o campo ite_md5 com o hash do campo ite_unico:

update fee_itens set ite_md5 = unhex( md5(ite_unico) );

Depois comparou com o hash de outro campo, o ite_codigo:

SELECT ite_unico, ite_md5, UNHEX( MD5( ite_codigo ) ) AS conf_md5 FROM fee_itens

Experimente comparar com o hash de ite_unico:

SELECT ite_unico, ite_md5, UNHEX( MD5( ite_unico ) ) AS conf_md5 FROM fee_itens

[Spyder.RV]

Realmente... eu havia feito essa burrada... eu percebi e até corrigi o post anterior, acho que no momento que eu tava corrigindo você tava respondendo aqui.. hehehehe!

Agora resumindo a coisa... eu fiz uma consulta simples... select * from fee_itens order by ite_codigo DESC limit 50000 e depois SELECT * from fee_itens WHERE ite_md5 = UNHEX( MD5( ite_unico ) ) order by ite_codigo DESC limit 50000... e o mais incrível é que com a execução das funções MD5() e UNHEX() na cláusula WHERE a segunda consulta foi alguns milésimos de segundos mais rápida que a primeira que não tinha nada no WHERE... Eu achei que levaria um tempo razoável!

Show de bola!!!!

Editado Dezembro 4, 2012 por Spyder.RV

[Spyder.RV]

Continuo apanhando... vejam o código abaixo:

SELECT ite_codigo, ite_unico
FROM fee_itens
WHERE ite_md5 = UNHEX( LEFT( MD5( ite_unico ) , 16 ) ) 
ORDER BY ite_codigo DESC 
LIMIT 5

Me retorna normalmente alguns registros conforme a imagem abaixo: Porém se eu tentar uma consulta comparando um resultado único não retorna zerado... detalhe... esse valor existe pois eu peguei do primeiro registro da primeira consuta.

SELECT ite_codigo, ite_unico
FROM fee_itens
WHERE ite_md5 = UNHEX( LEFT( MD5( 'Veneno de aranha é usado em pesquisa contra disfunção erétil' ) , 16 ) );

[Kakao]

Pra mim funcionou. Eu testei assim:

create table fee_itens (
    ite_unico varchar(255),
    ite_codigo integer,
    ite_md5 binary(8)
);

insert into fee_itens (ite_unico, ite_codigo, ite_md5)
values(
    'Veneno de aranha é usado em pesquisa contra disfunção erétil',
    7395927,
    unhex(left(md5('Veneno de aranha é usado em pesquisa contra disfunção erétil'), 16))
);

select
    ite_codigo,
    ite_unico,
    ite_md5,
    unhex(left(md5( 'Veneno de aranha é usado em pesquisa contra disfunção erétil'), 16))
FROM fee_itens
where ite_md5 = unhex(left(md5( 'Veneno de aranha é usado em pesquisa contra disfunção erétil'), 16))
;
+------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------+
| ite_codigo | ite_unico                                                        | ite_md5  | unhex(left(md5( 'Veneno de aranha é usado em pesquisa contra disfunção erétil'), 16))     |
+------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------+
|    7395927 | Veneno de aranha é usado em pesquisa contra disfunção erétil     | *A(%1  | *A(%1                                                                                   |
+------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------+

Teste a consulta a seguir e poste o resultado:

select
    ite_codigo,
    ite_unico,
    ite_md5,
    unhex(left(md5( 'Veneno de aranha é usado em pesquisa contra disfunção erétil'), 16))
FROM fee_itens
where ite_codigo = 7395927
;
+------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------------+
| ite_codigo | ite_unico                                                        | ite_md5  | UNHEX( LEFT( MD5( 'Veneno de aranha é usado em pesquisa contra disfunção erétil' ) , 16 ) )     |
+------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------------+
|    7395927 | Veneno de aranha é usado em pesquisa contra disfunção erétil     | *A(%1  | *A(%1                                                                                         |
+------------+------------------------------------------------------------------+----------+-------------------------------------------------------------------------------------------------+

Mostre o create da tabela fee_itens e também o insert da linha acima.

[Spyder.RV]

Achei o ponto... no phpmyadmin tava aparecendo certo, porém no mysql olha só a diferença... tá na imagem:

Questões de codificação de caracteres... Observem que os acentos que foram retornados estão

diferentes (no PHPmyAdmin ele já ajustou, mas no console do mysql direto é que o problema ficou visível).

O problema aqui agora mudou de status pois como as fontes RSS vêm de diferentes locais e possuem diferentes características, inclusive diferentes codificações, inclusive tem coisa que vem codificado como UTF-8 mas no cabeçalho do xml vem como ISO-8859-1... a solução aqui pelo que vejo é retirar toda a acentuação ou caracteres especiais pra evitar dor de cabeça... quase que um SLUG.

Pesquisei um pouco sobre expressões regulares no mysql e é muito complexo... alguma dica de como implementar direto no mysql?

Preciso pegar algo como: Veneno de aranha é usado em pesquisa contra disfunção erétil

E transformar em : veneno-de-aranha--usado-em-pesquisa-contra-disfuno-ertil

Eu já vou jogar tudo pronto a partir do PHP, então por lá é tranquilo... mas preciso montar uma query pra atualizar o que já tem no banco de dados e por isso preciso fazer a conversão também no banco e é mais rápido fazer isso numa query de update só do que fazer um loop no PHP com mais de 10.000.000 de registros

Editado Dezembro 5, 2012 por Spyder.RV

[Kakao]

O que aconteceu com o seu teste é que você digitou a string e ela foi com a codificação do seu terminal e aí ficou diferente do que está na base.

Veja que quando você for comparar uma string vinda de um feed com a que está na base as duas vão ter a mesma codificação. A não ser que o desenvolvedor do feed fique alterando. Então me parece que trocar os caracteres especiais é uma complicação desnecessária. Se for realmente necessário trocar os caracteres então eu acho que vale a pena pensar um pouco mais se a abordagem do hash é a mais indicada.

[Spyder.RV]

O que aconteceu com o seu teste é que você digitou a string e ela foi com a codificação do seu terminal e aí ficou diferente do que está na base.

Veja que quando você for comparar uma string vinda de um feed com a que está na base as duas vão ter a mesma codificação. A não ser que o desenvolvedor do feed fique alterando. Então me parece que trocar os caracteres especiais é uma complicação desnecessária. Se for realmente necessário trocar os caracteres então eu acho que vale a pena pensar um pouco mais se a abordagem do hash é a mais indicada.

Hum... realmente... testei com os caracteres e realmente funcionou bem!

Isso nos testes aqui me rendeu 18% de tempo de execução!

Editado Dezembro 6, 2012 por Spyder.RV

[Spyder.RV]

Só finalizando esse tópico...

A sugestão do colega Kakao foi sensacional, inclusive fiz uns testes aqui simulando situações hipotéticas que dificilmente vão ocorrer... por exemplo com 10.0000.000 de registros e cada registro com um campo único realmente diferente...

Para isso, após "inflar" a tabela com os 10.000.000 de registros, eu atualizei o valor do campo md5_unico, que é o nome que dei para o campo binário na tabela final. Gerei assim:

update fee_itens set md5_unico=UNHEX( left(MD5( RAND( ) ), 16 ) );

Em seguida rodei o meu código PHP para acessar 15 feeds diferentes, totalizando 464 itens a serem localizados e inseridos caso não existissem... o resultado foi: 100% dos registros inseridos em 13,902 segundos... Logo em seguida rodei o novamente e como já estava tudo cadastrado, dei menos de 5 segundos; visto que todos os itens já estavam inseridos e só SELECT's pois já estavam todos cadastrados... Repeti várias vezes e inclusive rodando simultaneamente de vários locais diferentes acessando o mesmo script e o pior score foi cerca de 17 segundos.... Vale ressaltar que o cenário dos 10.000.000 de registros é hipotético e muito acima do que realmente chegarei a ter na tabela e levo aí no mínimo 10 anos pra chegar nisso, e até lá já estarei num ORACLE da vida, hehehehe...Já o cenário de 15 fontes RSS diferentes com 464 registros é exatamente o que tenho atualmente... Outro detalhe é que como vão ter muitas consultas em sequencia ao mysql (um select e um insert), o MD5 já é enviado na consulta pronto, para evitar forçar o mysql, dividindo um pouco da tarefa com o php...

$query = "SELECT ID FROM fee_itens WHERE md5_unico = UNHEX(" . substr(md5($rss->item), 0, 16) . ");";

O resultado final foi muito acima das expectativas... confesso que estava esperando que esse tempo fosse beeeem mais alto!!!! Valeu Kakao.

Valeu Kakao pelo auxílio... ficou fora do comum

Editado Dezembro 10, 2012 por Spyder.RV