Início / dba / Perguntas / 43
Accepted
Maniero
Asked: 2011-01-04 14:18:52 +0800 CST

Como criar um índice condicional no MySQL?

  • 772

Como criar um índice para filtrar um intervalo específico ou subconjunto da tabela no MySQL? AFAIK é impossível criar diretamente, mas acho que é possível simular esse recurso.

Exemplo: quero criar um índice para NAMEcoluna apenas para linhas comSTATUS = 'ACTIVE'

Essa funcionalidade seria chamada de índice filtrado no SQL Server e índice parcial no Postgres.

mysql index

6 respostas

  1. Best Answer

    MySQL atualmente não suporta índices condicionais.

    Para conseguir o que você está pedindo (não que você deva fazer ;) ) você pode começar criando uma tabela auxiliar:

    CREATE TABLE  `my_schema`.`auxiliary_table` (
   `id` int unsigned NOT NULL,
   `name` varchar(250), /* specify the same way as in your main table */
   PRIMARY KEY (`id`),
   KEY `name` (`name`)
);

    Então você adiciona três gatilhos na tabela principal:

    delimiter //

CREATE TRIGGER example_insert AFTER INSERT ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   END IF;
END;//

CREATE TRIGGER example_update AFTER UPDATE ON main_table
FOR EACH ROW
BEGIN
   IF NEW.status = 'ACTIVE' THEN
      REPLACE auxiliary_table SET
         auxiliary_table.id = NEW.id,
         auxiliary_table.name = NEW.name;
   ELSE
      DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
   END IF;
END;//

CREATE TRIGGER example_delete AFTER DELETE ON main_table
FOR EACH ROW
BEGIN
   DELETE FROM auxiliary_table WHERE auxiliary_table.id = OLD.id;
END;//

delimiter ;

    Precisamos delimiter //porque queremos usar ;dentro das triggers.

    Dessa forma, a tabela auxiliar conterá exatamente os IDs correspondentes às linhas da tabela principal que contém a string "ACTIVE", sendo atualizados pelas triggers.

    Para usar isso em um select, você pode usar o usual join:

    SELECT main_table.* FROM auxiliary_table LEFT JOIN main_table
   ON auxiliary_table.id = main_table.id
   ORDER BY auxiliary_table.name;

    Se a tabela principal já contém dados, ou caso você faça alguma operação externa que altere os dados de forma não usual (EX: fora do MySQL), você pode corrigir a tabela auxiliar com isto:

    INSERT INTO auxiliary_table SET
   id = main_table.id,
   name = main_table.name,
   WHERE main_table.status="ACTIVE";

    Sobre o desempenho, provavelmente você terá inserções, atualizações e exclusões mais lentas. Isso pode fazer algum sentido apenas se você realmente lidar com alguns casos em que a condição desejada é positiva. Mesmo assim, provavelmente apenas testando você poderá ver se o espaço economizado realmente justifica essa abordagem (e se você realmente está economizando algum espaço).

    • 12

  2. Se entendi a pergunta corretamente, acho que o que você está tentando fazer é criar um índice em ambas as colunas, NAME e STATUS. Isso permitiria que você consultasse com eficiência onde NAME='SMITH' e STATUS='ACTIVE'

    • 7

  3. Você não pode fazer indexação condicional, mas, para seu exemplo, pode adicionar um índice de várias colunas em ( name, status).

    Mesmo que indexe todos os dados nessas colunas, ainda ajudará você a encontrar os nomes que procura com o status "ativo".

    • 5

  4. Você poderia fazer isso dividindo os dados entre duas tabelas, usando visualizações para unir as duas tabelas quando todos os dados forem necessários e indexando apenas uma das tabelas nessa coluna - mas acho que isso causaria problemas de desempenho para consultas que precisam percorrer toda a tabela, a menos que o planejador de consultas seja mais inteligente do que eu acredito. Essencialmente, você estaria particionando manualmente a tabela (e aplicando o índice a apenas uma das partições).

    Infelizmente, o recurso de particionamento de tabela integrado não o ajudará em sua busca, pois você não pode aplicar um índice a uma única partição.

    Você pode manter uma coluna extra com um índice e só ter um valor nessa coluna quando a condição na qual deseja que o índice seja baseado for verdadeira, mas isso provavelmente será trabalhoso e de valor limitado (ou negativo) em termos de eficiência da consulta e economia de espaço.

    • 4

  5. O MySQL agora possui colunas virtuais, que podem ser usadas para índices.

    • 0

  6. Eu sei que esta questão é bastante antiga, mas a melhor abordagem para isso seria apenas criar o índice com as duas colunas na ordem correta. Se você colocar primeiro o status no índice e depois o nome, estará criando um índice que "agrupará" todos os seus nomes 'ATIVOS'. Dessa forma, quando você consultar a tabela, o otimizador saberá exatamente onde no índice começar a procurar o nome que você está consultando.

    • 0

relate perguntas