Desenvolvimento de software Ruby on Rails. Índices v2

Ao trabalhar com o Rubi sobre Carris normalmente lidamos com bases de dados relacionais, tais como MySQL ou PostgreSQL. Ao definir migrações utilizando as Migrações de registos activos, deparamo-nos com os chamados índices, mas os principiantes muitas vezes não compreendem bem os índices e as vantagens que trazem.

Neste post, gostaria de explicar o que são os índices, para que servem e apresentar algumas boas práticas sobre como utilizá-los.

Base de dados

Existem muitos motores de bases de dados, e um dos mais populares é o já mencionado MySQL, PostgreSQL, Oracle ou Microsoft SQL Server. Todos eles são bases de dados relacionais, o que significa que todos os dados estão relacionados entre si e armazenados em tabelas. Cada linha da tabela é designada por registo e cada uma tem o seu próprio identificador único (id). Pode consultar a classificação dos motores de bases de dados mais populares em https://db-engines.com/en/ranking. Também encontrará aí algumas bases de dados não relacionais, como o MongoDB.

Criar um índice

As tabelas das nossas bases de dados podem ter apenas algumas a várias dezenas - em casos extremos, até várias centenas - de colunas. Não se esqueça de que cada tabela pode ter um número ilimitado de linhas. Este número não resulta diretamente da estrutura da base de dados e devemos sempre assumir que o número de registos irá aumentar sucessivamente e, consequentemente, a nossa base de dados irá crescer. Os pressupostos iniciais e as consultas escritas nas aplicações existentes podem ser óptimos para um número pequeno ou médio de registos, mas com o tempo, quando chegam mais dados, a comunicação da aplicação com a base de dados deixa de ser eficiente.

O papel do programador é escrever consultas para obter alguns dados da tabela ou tabelas, mas a forma óptima de processar a consulta depende do motor da base de dados. Lembre-se de que os motores de bases de dados carregam os dados do disco para a memória e, em seguida, analisam-nos. Isto significa que, se muitos utilizadores realizarem operações complexas ao mesmo tempo, vários deles terão de esperar pela sua vez devido à falta de recursos para implementar as suas pesquisas. É por isso que os índices relevantes são tão importantes.

Wiki: Índice - uma estrutura de dados que aumenta a velocidade de execução de operações de pesquisa numa tabela.

Para cada índice, é necessário definir chaves (para uma ou várias colunas) que serão utilizadas para procurar registos na tabela. Os dados no índice serão ordenados com a chave que foi previamente definida, o que irá acelerar significativamente a pesquisa de dados na tabela. O exemplo mais simples da vida quotidiana é uma lista telefónica em que as pessoas são ordenadas por nome e apelido. Pode dizer-se que, neste caso, o nosso índice será o primeiro e o último nome.

Como é que se escolhe a melhor chave de índice? Não é difícil - basta lembrar-se de algumas regras. Crie um índice baseado em colunas que:

- será frequentemente utilizado nos nossos inquéritos (ONDE),

- em combinação uns com os outros dão um valor único (ou seja, um valor que indicará exatamente uma linha),

- serão utilizadas como colunas de ligação (JOIN),

- fornecem as chaves mais selectivas, ou seja, as que devolvem o menor número de linhas ao escrever uma consulta.

Se já soubermos quais as chaves ideais para a nossa tabela, podemos também perguntar-nos quantos índices precisamos. Neste caso, é melhor conhecer as consultas que farão referência à nossa tabela já na fase de conceção.

Vamos criar índices para consultas específicas que irão aparecer, mas não os escrevemos para cada coluna. Os índices, tal como as tabelas, têm de ser armazenados algures, por isso, quando criamos tabelas com um índice para cada coluna, temos de ter em conta que a quantidade de espaço utilizado pode aumentar significativamente.

Criar um índice único

Outra questão em que temos de pensar é a singularidade. Vale a pena gastar cinco minutos extra a pensar se o nosso índice é realmente único. Desta forma, dizemos ao optimizador de consultas que não tem de esperar duplicados na consulta. Por exemplo, endereços de correio eletrónico:

frozenstringliteral: true

class CreateUsers < ActiveRecord::Migration[6.0]
def change
createtable :users do |t|
t.string :email, null: false
end
addindex :users, :email, unique: true
end
fim

No exemplo do motor PostgreSQL, mostrarei a diferença na velocidade de consulta da coluna de correio eletrónico com um índice único e sem um índice.

1. Pode utilizar a amostra código na sua própria base de dados para poder testar o exemplo abaixo. Primeiro, vamos criar uma tabela vazia com uma coluna:

 CREATE TABLE users (
   email varchar
 );

2. Vamos gerar 10.000 registos para o teste:

 DO $
   BEGIN FOR i IN 1..10000 LOOP
     INSERT INTO users values((select 'user' || i || '@example.com'));
   END LOOP; END;
 $;

Utilizaremos o EXPLAIN ANALYZE para verificar a rapidez com que a nossa consulta será processada quando quisermos encontrar um utilizador específico na base de dados.

EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

A nossa consulta forçou a iteração em toda a tabela em busca do registo que interessa nós.

Este processo é designado por scanning sequencial. Neste caso, a leitura de toda a tabela e a filtragem de linhas específicas é a melhor forma de efetuar o trabalho.

O PostgreSQL filtrará as linhas desnecessárias e retornará apenas aquelas que nos interessam. Esta é realmente a melhor coisa a fazer neste caso. A varredura seqüencial nem sempre é ruim, há casos em que a varredura seqüencial é ideal.

4. Agora é o momento de verificar a consulta já efectuada sobre a tabela que tem o INDEX UNIQUE. Vamos definir o índice e executar a consulta.

 EATE UNIQUE INDEX index_email on users(email);
 EXPLAIN ANALYZE SELECT email FROM users WHERE email = 'user890example.com';

Desta vez, o PostgreSQL aproveitou a varredura do índice porque todas as colunas necessárias já estão no índice.

A seleção de apenas algumas linhas será muito eficiente quando se utiliza o índice. No entanto, se forem selecionados mais dados, a pesquisa do índice e da tabela será demasiado demorada.

Resumo

Como pode ver, o tempo de execução de uma consulta numa coluna com um índice é muito mais curto (no exemplo apresentado, é uma diminuição de 1,267 ms para 0,111 ms, ou seja, 91,24%!) A diferença mais importante é a forma como o PostgreSQL procura o registo que nos interessa. No primeiro caso, o motor de base de dados tinha de procurar em toda a tabela o registo de que precisávamos. No segundo, no entanto, a estrutura do índice é ordenada e única, pelo que o motor sabia onde se encontrava o registo, o que acelerou significativamente o tempo de processamento da consulta.

No caso de grandes bases de dados e de consultas muito complexas, os índices corretamente definidos podem acelerar significativamente o trabalho da sua aplicação sem que seja necessário aumentar a velocidade da máquina em que pesquisa a base de dados.

Vale a pena lembrar que a criação de índices em cada coluna não é uma boa prática. Os índices estabelecidos aceleram o trabalho do optimizador na procura de dados de interesse, mas, ao mesmo tempo, tornam mais lenta a inserção de novos dados e a atualização dos existentes.

Ler mais:

– Precisa de utilizar as estruturas JS comuns na sua aplicação Rails? O Stimulus.js pode ser uma alternativa

– Chegou a altura de uma nova realidade. A era do trabalho remoto começou há um mês

– Desenvolvimento de aplicações Web: Porque é que vale a pena escolher a tecnologia Ruby on Rails?