MySQL – Where e Indices

Quando se desenvolve para web, a utilização de bases de dados é grande parte do dia a dia. Especialmente quando se tem o prazer de trabalhar em projectos diferentes regularmente.

E quando se trabalha com bases de dados cujos tempos de resposta são críticos, a relação entre os índices existentes na base de dados e os campos utilizados para filtrar os registos é critica.

Dependendo do motor de base de dados utilizado, o tipo de indices e condições de filtragem (clausula WHERE do SQL) podem variar. No entanto (quase) todos têm em comum três tipos de índices: Primary KeyUniqueIndex (ou Key).

No caso particular do MySQL existe ainda um quarto tipo de índice que é o Fulltext, que tem equivalente no Context do Oracle, e um equivalente no MS SQL Server.

É importante conhecer estes quatro tipos de índices para sabermos o que esperar e quando utilizar cada um deles.

Tipos de Índices

Primary Key

Um índice do tipo Primary Key é a chave primária de um registo, o identificador único do registo. Um campo (ou conjunto de campos) sobre o qual seja criado um índice deste tipo não pode ter qualquer valor repetido, e não pode ter qualquer valor NULL (campo vazio).

Utilizar um valor presente neste tipo de índice é normalmente a forma mais rápida de encontrar um registo.

Dependendo do motor de base de dados, e da definição da base de dados, muitas vezes a primary key de uma tabela é um campo cujo valor é auto-incremental, isto é, o valor do campo para o próximo registo é igual ao valor do campo no ultimo registo inserido mais um valor de incremento (normalmente 1, mas pode variar). No caso de motores de bases de dados que não suportam campo auto-incrementais (como o Oracle), existem normalmente funcionalidades que permitem encontrar um valor para atribuir a estes campos (como as SEQUENCEs, ainda no Oracle).

A questão é que para estes campos, o valor nunca se repete, e nunca pode ser NULL (vazio), pelo que o valor do campo pode ser sempre utilizado para aceder ao registo.

Chaves Únicas (UNIQUE)

O segundo tipo de índices são as Chaves Únicas (UNIQUE). Estas chaves, tal como asPrimary Key nunca podem ter valores repetidos. Mas ao contrário destas podem ter registos com valores NULL (vazios).

Com isto conseguimos ter uma forma de aceder sempre a um determinado registo da base de dados, mas sem sermos obrigados a atribuir sempre um valor a este campo.

Índices normais (KEY ou INDEX)

O terceiro tipo de índices permitem valores repetidos e valores NULL. Ao contrário das chaves únicas, estes índices destinam-se a encontrar numa tabela vários registo que partilham um mesmo valor (ou conjunto de valores), e não um único registo, ou para criar listagens ordenadas.

Não existem muitas regras que não se possam violar quando se criam este tipo de índices, mas não é normalmente considerado muito vantajoso criar índices que não permitam dividir os registos na base de dados em vários grupos.

Por exemplo, criar um índice num campo de tipo lógico cujos valores possíveis sãoverdadeiro ou falso, e em que o número de registo são mais ou menos semelhantes para um e outro valor, ou em que o valor porque normalmente se efectuam a maioria das listagens é o que tem mais registos não é muito relevante em termos de performance (na maioria dos caso).

Ao contrário, se a maioria dos valores são únicos, e especialmente se queremos ordenar as listagens de acordo com os valores desse campo, a criação de um indice é uma mais valia indesprezável.

Indexar todos os campos, também não é, por norma, uma tarefa muito proveitosa. O tamanho ocupado pelos índices duma tabela deve ser o menor possível, assim como o seu número, por forma a reduzir as possibilidades de índices utilizáveis, e com isso melhor o tempo de resposta do motor de base de dados.

Criar índices com vários campos também pode ser uma forma de melhor os tempos de resposta dos queries que fazemos à base de dados, especialmente se sempre que fazemos uma listagem de dados um conjunto de campos é sempre utilizado para filtrar os registos, ou para os ordenar.

Fulltext Search

Os índices do tipo FullText Search são uma funcionalidade interessante de vários motores de base de dados. Estes índices permitem-nos obter rapidamente registos que contenham uma determinada palavra num campo ou conjunto de campos.

São indicados para implementar sistemas de pesquisa, pois permitem muito rapidamente obter os registos que contém uma ou várias palavras passadas, e podem ser mesmo utilizados para ordenar esses registos de acordo com a relevâncias da pesquisa efectuada.

O problema de implementar pesquisas utilizando este tipo de índice são:

  • Palavras que existam em muito registos são ignoradas (por serem demasiado comuns e consequentemente irrelevantes)
  • Palavras menores que um determinado tamanho (3 letras normalmente) são igualmente ignoradas.
  • Pesquisas mais complexas do que uma listagem de artigos com uma ou mais palavras de um conjunto passado são possíveis, mas a sintaxe não é óbvia, especialmente para não programadores

Isto, obviamente, são problemas menores, especialmente porque a maioria dos utilizadores faz pesquisas utilizando apenas listas de palavras, e raramente se procura algo pelas palavras mais comuns de uma amostra. Não se procura palha num palheiro com esperança de encontrar uma qualquer palha especial.

Condições WHERE

A clausula WHERE do SQL, utilizada em quase todos os motores de base de dados hoje em dia, serve para filtrar os registos que se irão obter como resultado da listagem (alteração ou remoção também são instruções que podem ser filtradas com o WHERE. Concentremo-nos nas listagens porque são a tarefa mais comum, especialmente quando se programa para ambientes abertos como a Web, e aquela em que a performance é mais relevante – por ser a mais comum).

Mas, então, que atenções devemos ter na clausula WHERE quando escrevemos uma query SQL?

A primeira coisa que devemos ter em atenção é verificar que estamos a colocar as condições de filtragem da condição mais limitante para a menos limitante, isto é, que a primeira condição no WHERE é aquela que, se utilizada sozinha, devolve menos registos, e que a ultima é a que devolve menos.

Quando criamos índices que tenham os mesmo campos que os utilizados na condição de filtragem (ou alguns dos utilizados), devemos verificar que os campos se encontram no índice pela mesma ordem que se encontram na condição de filtragem.

Os campos para que não foram criados índices devem ser deixados para o fim. Isto, claro, se os índices tiverem sido bem criados, isto é, nos campos que permitem fazer maiores filtragens na base de dados.

Por exemplo, se precisa de listar artigos por dia e por categoria, que estejam activos, e tem (em média) 10 artigos por dia, 50 por categoria e 300 activos e outros tantos activos, criar um índice com a data e a categoria do artigo será provavelmente uma boa ideia, e é igualmente boa ideia colocar as condições na condição WHERE nessa mesma ordem.

Na clausula ORDER BY e ON (dos JOINs) aplicam-se as mesmas regras.

Conclusão

Na criação dos índices numa base de dados é importante saber que tipo e quantidade de dados lá serão colocados, e é preciso, acima de tudo, ter consciência da forma como esses dados serão apresentados.

Crie sempre uma primary key, ou poderá nunca mais conseguir alterar os registos ou aceder-lhes directamente.

Sempre que a primary key não seja utilizável para tudo, crie uma chave única para aceder directamente aos registos. Por exemplo, parte dos URLs utilizados no Web a Sério são uma chave única da tabelas de posts.

E vocês, há alguma consideração não referida que utilizem na criação dos vossos queries? Este artigo apresenta algo que nunca tinham considerado?

Que questões da Programação para Web gostariam de ver aqui discutidas?

Leave a Reply

Your email address will not be published. Required fields are marked *