Início / dba / Perguntas / 17140
Accepted
Bruce Dou
Asked: 2012-04-27 23:55:09 +0800 CST

É possível adicionar índice ao mysql com várias colunas para otimizar a ordem por desempenho?

  • 772
  # I have a simple table
  create table t1 (c1 int, c2 int);
  # I want to do the following sorting
  select * from t1 order by c2 desc, c1 desc;
  select * from t1 order by c2 asc, c1 desc;
  # add indexs
  CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);  
  CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);  
  # then explain
  explain select * from t1 order by c2 desc, c1 desc;
  # the result is
  mysql> explain select * from t1 order by c2 desc, c1 asc;
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra                       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | t1    | index | NULL          | index_2 | 10      | NULL |    6 | Using index; Using filesort |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

minhas perguntas são

  1. Os índices são otimizados o desempenho?
  2. Por que usar o índice; Usando filesort show ao mesmo tempo?
mysql index

1 respostas

  1. Best Answer

    Você precisa de um índice (c2, c1)para esta consulta, não um índice para (c1, c2).

    Claro que seria melhor se houvesse um índice (c2 DESC, c1 DESC)para a primeira consulta e um índice (c2 DESC, c1 ASC)para a segunda consulta. Infelizmente o MySQL não pode criar tais índices. Os ASCe DESCsão ignorados.

    O que significa seus dois índices:

    CREATE INDEX index_1 ON t1 (c1 DESC, c2 DESC);  
CREATE INDEX index_2 ON t1 (c1 ASC, c2 DESC);

    são realmente idênticos e iguais como se você os tivesse definido como:

     CREATE INDEX c1_c2_index ON t1 (c1 ASC, c2 ASC);

    Se o desempenho - com o (c2, c1)índice - não for bom o suficiente, você tem algumas opções:

    • Como as colunas são do tipo inteiro, adicione mais duas colunas e armazene os valores negativos. Você precisará de gatilhos para polular essas colunas.

    . 

    CREATE TABLE 
( c1 INT
, c2 INT
, c1_neg INT
, c2_neg INT
);

    Então você pode indexar (c2_neg, c1)e(c2_neg, c1_neg)

    • Se você pode mudar para o MariaDB, pode usar VIRTUAL PERSISENTcolunas e índices apropriados para uma solução semelhante (um pouco melhor, pois você não precisará adicionar gatilhos, as colunas serão preenchidas automaticamente).
    • 2

relate perguntas