Início / dba / Perguntas / 52156
Accepted
Mike Purcell
Asked: 2013-10-25 13:08:53 +0800 CST

MySQL - Adicionar índice à coluna de texto de baixa cardinalidade?

  • 772

Estou trabalhando em um projeto em que uma coluna enum está sendo convertida em uma coluna de texto (não posso alterar isso). A cardinalidade da coluna é baixa (7 valores exclusivos). Eu obteria um aumento de desempenho adicionando um índice de 10 a 15 caracteres ou a cardinalidade é baixa o suficiente para que o índice resulte em retornos decrescentes?

mysql performance

2 respostas

  1. Best Answer

    O tamanho ou tipo de dados da coluna é irrelevante. São os valores únicos que importam. Se você tiver apenas 7 valores exclusivos, isso significa que 14,286% das linhas devem ser consideradas.

    Em vez de dar ao MySQL Query Optimizer o estresse de descobrir isso, você deve particionar a tabela por hash:

    CREATE TABLE mytable
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    ...
    myenum INT NOT NULL,
    PRIMARY KEY (id)
)
PARTITION BY HASH( myenum )
PARTITIONS 7;

    Não há necessidade de ter o myenum em nenhum índice. Deixe o MySQL Query Optimizer pesquisar a partição correta caso qualquer consulta SELECT tenha uma WHEREcláusula que inclua AND myenum = ....

    Se você precisar aumentar o número de valores exclusivos, terá que aumentar o número de partições.

    De uma chance !!!

    ATUALIZAÇÃO 2013-10-24 17:57

    Como falei nos comentários, você deve particionar pelo enum com maior cardinalidade.

    E os outros enums? NÃO INDEXE O ENUM POR SI MESMO!!!

    Se suas consultas SELECT incluem WHERE enum2...AND enum3=...`` AND enum4=...`, você deve pensar em fazer índices compostos de enums.

    Por exemplo, se você tiver enum2, enum3 e enum4, poderá criar índices compostos como estes:

    ALTER TABLE mytable ADD INDEX (enum2,enum3,enum4);
ALTER TABLE mytable ADD INDEX (enum3,enum4);

    Qual ordem você deve escolher?

    • CARDINALIDADE(enum2) > CARDINALIDADE(enum3)
    • CARDINALIDADE(enum3) > CARDINALIDADE(enum4)

    CAVEAT : Mais uma vez, gosto de enfatizar, se você particionar por enum1, não há necessidade de indexar em enum1.

    • 4

  2. Eu dificilmente poderia discordar mais do que já discordo da resposta aceita, por dois motivos.

    Primeiro, toda a conversa sobre o otimizador não usar índices de cardinalidade baixa é exagerada. É verdade que o otimizador pode não preferir, e é verdade que o otimizador às vezes pode optar por desconsiderá-lo, mas tenho visto postagens sugerindo que se mais de "x" por cento das linhas corresponderem a um índice, ele não será usado. E isso não é verdade.

    Estou sentado em frente a uma mesa com mais de um milhão de linhas. Ele tem uma coluna de enumeração indexada, juntamente com vários outros índices, mas estou mostrando esse índice abaixo. Observe que a cardinalidade é 2.

    mysql> show indexes in xxxxxxx;
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| xxxxxxx |          1 | target_xxx          |            1 | target_xxx          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

    Então, o otimizador usa esse índice?

    mysql> explain select * from xxxxxxx where target_xxx = 'default';
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows    | Extra                 |
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
|  1 | SIMPLE      | xxxxxxx | ref  | target_xxx    | target_xxx | 1       | const | 1269015 | Using index condition |
+----+-------------+---------+------+---------------+------------+---------+-------+---------+-----------------------+
1 row in set (0.23 sec)

    Sim. Ele usa o índice para linhas que correspondem e também usa o índice para me dizer quase imediatamente que nenhuma linha corresponde se eu usar um valor na cláusula where que não é encontrado em nenhum lugar da tabela nessa coluna.

    O mito de que índices de baixa cardinalidade não são úteis ou usados... precisa seriamente ser descartado.

    Dê opções ao otimizador. Isso não é algo que você queira evitar.

    Em segundo lugar, se você particionar a tabela conforme discutido, todas as consultas que não fazem referência a essa coluna em sua cláusula where agora têm todas as 7 partições para lidar (e 7 conjuntos de índices). A menos que haja algo realmente significativo e significativo sobre esta coluna que signifique que você a interrogará na maioria de suas cláusulas where, particionar nela não parece ser um plano particularmente bom.

    O particionamento não é uma bala mágica.

    É, no entanto, uma bala de um tipo diferente - e tende a apontar para o seu pé, a menos que seja usada apropriadamente.

    • 4

relate perguntas