Como otimizar SELECT muito lento com LEFT JOINs em tabelas grandes

Escolha alguns atributos para incluir no person. Indexe-os em algumas combinações -- use índices compostos, não índices de coluna única.

Essa é essencialmente a única saída do EAV-suck-at-performance, que é onde você está.

Aqui está mais discussão: http://mysql.rjweb.org/doc.php/eav incluindo uma sugestão de usar JSON em vez da tabela de valores-chave.

Espero ter encontrado uma solução suficiente. É inspirado por este artigo .

Resposta curta:

1. Criei 1 tabela com todos os atributos. Uma coluna para um atributo. Mais coluna de chave primária.
2. Os valores de atributo são armazenados em células de texto (para pesquisa de texto completo) em formato semelhante ao CSV.
3. Índices de texto completo criados. Antes disso é importante definir ft_min_word_len=1(para MyISAM) na [mysqld]seção e innodb_ft_min_token_size=1(para InnoDb) no my.cnfarquivo, reinicie o serviço mysql.
4. Exemplo de pesquisa: SELECT * FROM person_index WHERE MATCH(attribute_1) AGAINST("123 456 789" IN BOOLEAN MODE) LIMIT 1000onde 123, 456a 789são IDs que as pessoas deveriam ter associado em attribute_1. Essa consulta levou menos de 1 segundo.

Resposta detalhada:

Etapa 1. Criando tabela com índices de texto completo. O InnoDb suporta índices fulltext do MySQL 5.7, então se você usa 5.5 ou 5.6, você deve usar MyISAM. Às vezes, é ainda mais rápido para pesquisa FT do que InnoDb.

CREATE TABLE `person_attribute_ft` (
  `person_id` int(11) NOT NULL,
  `attr_1` text,
  `attr_2` text,
  `attr_3` text,
  `attr_4` text,
  PRIMARY KEY (`person_id`),
  FULLTEXT KEY `attr_1` (`attr_1`),
  FULLTEXT KEY `attr_2` (`attr_2`),
  FULLTEXT KEY `attr_3` (`attr_3`),
  FULLTEXT KEY `attr_4` (`attr_4`),
  FULLTEXT KEY `attr_12` (`attr_1`,`attr_2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Etapa 2. Insira os dados da tabela EAV (entity-attribute-value). Por exemplo, declarado em questão, pode ser feito com 1 SQL simples:

INSERT IGNORE INTO `person_attribute_ft`
SELECT
    p.person_id,
    (SELECT GROUP_CONCAT(a.attribute_value SEPARATOR ' ') FROM attribute a WHERE a.attribute_type_id = 1 AND a.person_id = p.person_id LIMIT 10) attr_1,
    (SELECT GROUP_CONCAT(a.attribute_value SEPARATOR ' ') FROM attribute a WHERE a.attribute_type_id = 2 AND a.person_id = p.person_id LIMIT 10) attr_2,
    (SELECT GROUP_CONCAT(a.attribute_value SEPARATOR ' ') FROM attribute a WHERE a.attribute_type_id = 3 AND a.person_id = p.person_id LIMIT 10) attr_3,
    (SELECT GROUP_CONCAT(a.attribute_value SEPARATOR ' ') FROM attribute a WHERE a.attribute_type_id = 4 AND a.person_id = p.person_id LIMIT 10) attr_4
FROM person p

Resultado deve ser algo assim:

mysql> select * from person_attribute_ft limit 10;
+-----------+--------+--------+--------+--------+
| person_id | attr_1 | attr_2 | attr_3 | attr_4 |
+-----------+--------+--------+--------+--------+
|         1 | 541    | 2      | 1927   | 3      |
|         2 | 2862   | 2      | 1939   | 4      |
|         3 | 6573   | 2      | 1904   | 2      |
|         4 | 2432   | 1      | 2005   | 2      |
|         5 | 2208   | 1      | 1995   | 4      |
|         6 | 8388   | 2      | 1973   | 1      |
|         7 | 107    | 2      | 1909   | 4      |
|         8 | 5161   | 1      | 2005   | 1      |
|         9 | 8022   | 2      | 1953   | 4      |
|        10 | 4801   | 2      | 1900   | 3      |
+-----------+--------+--------+--------+--------+
10 rows in set (0.00 sec)

Etapa 3. Selecione na tabela com consulta como esta:

mysql> SELECT SQL_NO_CACHE *
    -> FROM `person_attribute_ft`
    -> WHERE 1 AND MATCH(attr_1) AGAINST ("3000 3001 3002 3003 3004 3005 3006 3007" IN BOOLEAN MODE)
    -> AND MATCH(attr_2) AGAINST ("1" IN BOOLEAN MODE)
    -> AND MATCH(attr_3) AGAINST ("1980 1981 1982 1983 1984" IN BOOLEAN MODE)
    -> AND MATCH(attr_4) AGAINST ("2,3" IN BOOLEAN MODE)
    -> LIMIT 10000;
+-----------+--------+--------+--------+--------+
| person_id | attr_1 | attr_2 | attr_3 | attr_4 |
+-----------+--------+--------+--------+--------+
|     12131 | 3002   | 1      | 1982   | 2      |
|     51315 | 3007   | 1      | 1984   | 2      |
|    147283 | 3001   | 1      | 1984   | 2      |
|    350086 | 3005   | 1      | 1982   | 3      |
|    423907 | 3004   | 1      | 1982   | 3      |
... many rows ...
|   9423907 | 3004   | 1      | 1982   | 3      |
|   9461892 | 3007   | 1      | 1982   | 2      |
|   9516361 | 3006   | 1      | 1980   | 2      |
|   9813933 | 3005   | 1      | 1982   | 2      |
|   9986892 | 3003   | 1      | 1981   | 2      |
+-----------+--------+--------+--------+--------+
90 rows in set (0.17 sec)

A consulta seleciona todas as linhas:

• correspondendo a pelo menos um desses IDs em attr_1:3000, 3001, 3002, 3003, 3004, 3005, 3006 or 3007
• AND at the same time matching 1 in attr_2 (this column represents gender so if this solution were customized, it should be smallint(1) with simple index, etc...)
• AND at the same time matching at least one of 1980, 1981, 1982, 1983 or 1984 in attr_3
• AND at the same time matching 2 or 3 in attr_4

Conclusion:

I know this solution is not perfect and ideal for many situations but can be used as good alternative for EAV table design.

I hope it'll help someone.

Adicione índices a attributefor:

• (person_id, attribute_type_id, attribute_value)e
• (attribute_type_id, attribute_value, person_id)

Explicação

Com seu design atual, EXPLAINespera-se que sua consulta examine 1,265,229 * 4 * 4 * 4 = 80,974,656linhas no attribute. Você pode reduzir esse número adicionando um índice composto para . Usando esse índice, sua consulta examinará apenas 1 em vez de 4 linhas para cada , e .attribute(person_id, attribute_type_id)locationeyecolorgender

Você pode estender esse índice para incluir attribute_type_valuetambém: (person_id, attribute_type_id, attribute_value). Isso transformaria esse índice em um índice de cobertura para essa consulta, o que também melhoraria o desempenho.

Além disso, adicionar um índice em (attribute_type_id, attribute_value, person_id)(novamente um índice de cobertura incluindo person_id) deve melhorar o desempenho sobre apenas usar um índice attribute_valueonde mais linhas teriam que ser examinadas. Nesse caso, o primeiro passo em sua explicação será fixado: selecionar um intervalo de bornyear.

O uso desses dois índices reduziu o tempo de execução da sua consulta no meu sistema de ~ 2,0 s para ~ 0,2 s com a saída explicada assim:

+----+-------------+----------+--------+-------------------------------------+-------------------+---------+--------------------------------+---------+----------+--------------------------+
| id | select_type | table    | type   | possible_keys                       | key               | key_len | ref                            |    rows | filtered | Extra                    |
+----+-------------+----------+--------+-------------------------------------+-------------------+---------+--------------------------------+---------+----------+--------------------------+
|  1 | SIMPLE      | bornyear | range  | person_type_value,type_value_person | type_value_person |       9 |                                | 1861881 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | location | ref    | person_type_value,type_value_person | person_type_value |       8 | bornyear.person_id,const       |       1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | eyecolor | ref    | person_type_value,type_value_person | person_type_value |       8 | bornyear.person_id,const       |       1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | gender   | ref    | person_type_value,type_value_person | person_type_value |      13 | bornyear.person_id,const,const |       1 |   100.00 | Using index              |
|  1 | SIMPLE      | person   | eq_ref | PRIMARY                             | PRIMARY           |       4 | bornyear.person_id             |       1 |   100.00 | Using index              |
+----+-------------+----------+--------+-------------------------------------+-------------------+---------+--------------------------------+---------+----------+--------------------------+

Suponho que pode haver problema com o design do banco de dados.

Você está usando o chamado design Entity-Attribute-Value, que geralmente tem um desempenho ruim, bem, por design.

Você tem alguma sugestão para projetar melhor esta situação, por favor?

A maneira relacional clássica de projetar isso seria criar uma tabela separada para cada atributo. Em geral, você pode ter essas tabelas separadas: location, gender, bornyear, eyecolor.

O seguinte depende se determinados atributos são sempre definidos para uma pessoa ou não. E se uma pessoa pode ter apenas um valor de um atributo. Por exemplo, geralmente a pessoa tem apenas um gênero. Em seu design atual, nada impede que você adicione três linhas para a mesma pessoa com valores diferentes de gênero nelas. Você também pode definir um valor de gênero não como 1 ou 2, mas para algum número que não faz sentido, como 987 e não há restrição no banco de dados que impeça isso. Mas essa é outra questão separada de manter a integridade dos dados com o design EAV.

Se você sempre sabe o sexo da pessoa, não faz sentido colocá-lo em uma tabela separada e é muito melhor ter uma coluna não nula GenderIDna persontabela, que seria uma chave estrangeira para a tabela de pesquisa com a lista de todos os gêneros possíveis e seus nomes. Se você souber o sexo da pessoa na maioria das vezes, mas nem sempre, poderá tornar essa coluna anulável e defini-la para NULLquando as informações não estiverem disponíveis. Se na maioria das vezes o sexo da pessoa não for conhecido, talvez seja melhor ter uma tabela separada gendervinculada a person1:1 e que tenha linhas apenas para as pessoas que têm um sexo conhecido.

Considerações semelhantes se aplicam a eyecolore bornyear- é improvável que a pessoa tenha dois valores para um eyecolorou bornyear.

Se for possível que uma pessoa tenha vários valores para um atributo, você definitivamente o colocaria em uma tabela separada. Por exemplo, não é incomum que uma pessoa tenha vários endereços (casa, trabalho, correio, feriado etc.), então você listaria todos eles em uma tabela location. Tabelas persone locationseriam ligados 1:M.

Ou apenas para ajustar o select acima?

Se estiver usando o design EAV, eu faria pelo menos o seguinte.

• Defina colunas attribute_type_id, attribute_value, person_idpara NOT NULL.
• Configure a chave estrangeira vinculada attribute.person_idao person.person_id.
• Crie um índice em três colunas (attribute_type_id, attribute_value, person_id). A ordem das colunas é importante aqui.
• Até onde eu sei, MyISAM não aceita chaves estrangeiras, então não use, use InnoDB.

Eu escreveria a consulta assim. Use INNERem vez de LEFTjunções e escreva explicitamente a subconsulta para cada atributo para dar ao otimizador todas as chances de usar o índice.

SELECT person.person_id
FROM
    person
    INNER JOIN
    (
        SELECT attribute.person_id
        FROM attribute
        WHERE attribute_type_id = 1
            AND location.attribute_value BETWEEN 3000 AND 7000
    ) AS location ON location.person_id = person.person_id
    INNER JOIN
    (
        SELECT attribute.person_id
        FROM attribute
        WHERE attribute_type_id = 2
            AND location.attribute_value = 1
    ) AS gender ON gender.person_id = person.person_id
    INNER JOIN
    (
        SELECT attribute.person_id
        FROM attribute
        WHERE attribute_type_id = 3
            AND location.attribute_value BETWEEN 1980 AND 2000
    ) AS bornyear ON bornyear.person_id = person.person_id
    INNER JOIN
    (
        SELECT attribute.person_id
        FROM attribute
        WHERE attribute_type_id = 4
            AND location.attribute_value IN (2, 3)
    ) AS eyecolor ON eyecolor.person_id = person.person_id
LIMIT 100000;

Além disso, pode valer a pena particionar a attributetabela por attribute_type_id.

Tente usar dicas de índice de consulta que parecem apropriadas

Dicas de índice do MySQL