Início / dba / Perguntas / 21504
Accepted
32bitfloat
Asked: 2012-07-26 11:03:43 +0800 CST

por que/como o número de colunas correspondentes influencia a forma de executar uma consulta

  • 772

Imagine a seguinte situação:

A Tabela A usa MyISAM e contém 4 campos (texto) com um índice FULLTEXT combinado.

FULLTEXT fulltext1 | fulltext2 | fulltext3 | fulltext4

A Tabela B usa InnoDB e contém alguns outros campos, onde 5 deles são indexados como 'ORD'.

ORD order1 | order2 | order3 | order4 | order5


Eu gosto de fazer uma pesquisa de texto completo na tabela A juntando a tabela B em seu ID estrangeiro e ordenando os resultados pelas colunas indexadas da tabela B.

Query1 - corresponde a todas as 4 colunas de texto completo:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id` 

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`, `tableA`.`fulltext4`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
) 
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

leva 1,6565 segundos.

EXPLICAR Consulta1:

select_type  table   type     possible_keys     key      key_len  ref   rows            Extra
SIMPLE       tableA  fulltext PRIMARY,FULLTEXT  FULLTEXT    0     1                     Using where; Using temporary; Using filesort
SIMPLE       tableB  eq_ref   PRIMARY           PRIMARY     4     db.tableA.tableB_id   1

nenhum índice é usado, a tabela temporária era necessária. Não sei o que significa "Extra 1" na segunda linha.

Query2 - corresponde apenas a 3 colunas:

SELECT `tableB`.`id`
FROM `tableA`
INNER JOIN `tableB` ON `tableA`.`tableB_id` = `tableB`.`id` 

WHERE MATCH (
`tableA`.`fulltext1`, `tableA`.`fulltext2`, `tableA`.`fulltext3`
)
AGAINST (
'+search*'
IN BOOLEAN
MODE
) 
ORDER BY
`tableB`.`order1` DESC,
`tableB`.`order2` DESC,
`tableB`.`order3` DESC,
`tableB`.`order4` DESC,
`tableB`.`order5` DESC
LIMIT 0,15

demora 0,0114 segundos.

EXPLICAR Consulta2:

select_type table   type    possible_keys  key     key_len  ref           rows  Extra
SIMPLE      tableB  index   PRIMARY        ORD     783      NULL          15    Using index
SIMPLE      tableA  eq_ref  PRIMARY        PRIMARY 4        db.tableB.id  1     Using where

A ordem das tabelas listadas foi alterada. Para tableB, o índice foi usado, para tableA nenhuma tabela temporária foi necessária.

Não importa qual coluna de texto completo eu excluo da consulta - desde que eu corresponda a menos de 4 delas, obtenho a duração e a explicação da Consulta2 exibida.
Talvez interessante: a quantidade de linhas é igual nas duas tabelas, cerca de 180k.

Gostaria de saber o motivo desse comportamento. Quero dizer, parece que todo o modo de execução dependeria do número de colunas correspondentes.

edit: agora estou completamente confuso. Eu apaguei o arquivo fulltext-index. Eu correspondi a todas as 4 (ex-) colunas de texto completo (Consulta1). Agora leva 0,1205 segundos. EXPLAIN mostra que nenhuma tabela temporária é necessária; mas eu me pergunto como posso combinar mais rápido no modo booleano sem ter um índice de texto completo.

mysql performance

1 respostas

  1. Best Answer

    Posso fornecer uma explicação geral, mas pode não se aplicar especificamente ao seu caso particular:

    A maneira como a tomada de decisão funciona é avaliando o custo do plano de execução e, em seguida, escolhendo o plano mais barato. Isso você já sabe.

    Quando se trata de indexação, porém, as coisas estão ficando interessantes. A maneira de avaliar a utilidade ou viabilidade de um índice é estimar a seletividade dado algum valor.

    Por enquanto, esqueça seu índice FULLTEXT e vamos assumir um índice simples em alguma coluna col1e outro índice em alguma coluna col2. Dadas as duas consultas a seguir:

    SELECT * FROM t WHERE col1 < 10 and col2 = 4;
SELECT * FROM t WHERE col1 BETWEEN 100 AND 110 and col2 = 4;

    Pode acontecer que a consulta seja avaliada de forma diferente nesses dois casos. Por quê? Porque pode acontecer que col2 = 4retorne mais linhas que col1 < 10, nesse caso preferimos usar index on col1. Mas então, pode retornar menos linhas que col1 BETWEEN 100 AND 110, caso em que preferimos o índice em col2.

    O seu caso não é muito diferente. O MySQL estima o número de linhas retornadas por alguma consulta de índice. Quando você usa mais colunas, o MySQL tem a impressão de que seu índice provavelmente resultará com poucas linhas. Então, ele escolhe começar com TableA, então junta o que deveria ser poucas linhas com TableB.

    Mas se o MySQL acredita que o índice retorna muitas linhas, pode preferir começar com TableB. Por que é que? Porque você está classificando em colunas indexadas de arquivos TableB. A classificação também dá muito trabalho. Portanto, o MySQL pode escolher primeiro classificar as linhas, depois juntar TableAe filtrar por índice de texto completo. Pode não ser uma má ideia se a pesquisa de texto completo resultar em muitas linhas de qualquer maneira.

    • 3

relate perguntas