Índices SQL explicados, pt. 1

Usado corretamente, um índice de banco de dados SQL pode ser tão eficaz que pode parecer mágica. Mas a série de exercícios a seguir mostrará que, por baixo, a lógica da maioria dos índices SQL – e manejá-los corretamente – é bastante direta.

Nesta série, SQL Indexes Explained , veremos as motivações para usar índices para acessar dados e para projetar índices da maneira como é feito por todos os RDBMSs modernos. Em seguida, examinaremos os algoritmos usados ​​para retornar dados para padrões de consulta específicos.

Você não precisa saber muito sobre índices para poder seguir o SQL Indexes Explained . Existem apenas duas pré-condições:

• Conhecimento básico de SQL
• Conhecimento básico de qualquer linguagem de programação

Os principais tópicos em que os índices SQL explicados entrarão são:

• Por que precisamos de índices de banco de dados SQL; visualizar planos de execução usando índices
• Design de índice: quais índices tornam uma consulta rápida e eficiente
• Como podemos escrever uma consulta para usar índices efetivamente
• O impacto do uso de índices em SQL na eficiência de leitura/gravação
• Índices de cobertura
• Particionamento, seu impacto na leitura e escrita e quando usá-lo

Este não é apenas um tutorial de índice SQL — é um mergulho profundo na compreensão da mecânica subjacente dos índices.

Vamos descobrir como um RDBMS usa índices fazendo exercícios e analisando nossos métodos de resolução de problemas. Nosso material de exercícios consiste em Planilhas Google somente leitura. Para fazer um exercício, você pode copiar a Planilha Google ( Arquivo → Fazer uma cópia ) ou copiar seu conteúdo em sua própria Planilha Google.

Em cada exercício, mostraremos uma consulta SQL que usa a sintaxe Oracle. Para datas, usaremos o formato ISO 8601, YYYY-MM-DD .

Exercício 1: Todas as Reservas de um Cliente

A primeira tarefa – não faça isso ainda – é encontrar todas as linhas da planilha de Reservas para um cliente específico de um sistema de reservas de hotéis e copiá-las em sua própria planilha, simulando a execução da seguinte consulta:

 SELECT * FROM Reservations WHERE ClientID = 12;

Mas queremos seguir um método particular.

Abordagem 1: sem classificação, sem filtragem

Na primeira tentativa, não use nenhum recurso de classificação ou filtragem. Por favor, registre o tempo gasto. A planilha resultante deve conter 73 linhas.

Este pseudocódigo ilustra o algoritmo para realizar a tarefa sem ordenar:

 For each row from Reservations If Reservations.ClientID = 12 then fetch Reservations.*

Nesse caso, tivemos que verificar todas as 841 linhas para retornar e copiar 73 linhas que satisfizessem a condição.

Abordagem 2: apenas classificação

Para a segunda tentativa, classifique a planilha de acordo com o valor da coluna ClientID . Não use filtros. Registre o tempo e compare-o com o tempo que levou para concluir a tarefa sem classificar os dados.

Após a classificação, a abordagem fica assim:

 For each row from Reservations If ClientID = 12 then fetch Reservations.* Else if ClientID > 12 exit

Desta vez, tivemos que verificar “apenas” 780 linhas. Se pudéssemos pular para a primeira fila, levaria ainda menos tempo.

Mas se tivéssemos que desenvolver um programa para a tarefa, essa solução seria ainda mais lenta que a primeira. Isso porque teríamos que ordenar todos os dados primeiro, o que significa que cada linha teria que ser acessada pelo menos uma vez. Essa abordagem é boa apenas se a folha já estiver classificada na ordem desejada.

Exercício 2: O número de reservas a partir de uma determinada data

Agora a tarefa é contar o número de check-ins no dia 16 de agosto de 2020:

 SELECT COUNT (*) FROM Reservations WHERE DateFrom = TO_DATE('2020-08-16', 'YYYY-MM-DD')

Use a planilha do Exercício 1. Meça e compare o tempo gasto para concluir a tarefa com e sem classificação. A contagem correta é 91.

Para a abordagem sem ordenação, o algoritmo é basicamente o mesmo do Exercício 1.

A abordagem de classificação também é semelhante à do exercício anterior. Vamos apenas dividir o loop em duas partes:

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 16th of August 2020. Repeat Read next row Until DateFrom = '2020-08-16' -- Calculate the count While DateFrom = '2020-08-16' Increase the count Read the next row

Exercício 3: Investigação Criminal

O inspetor de polícia pede para ver uma lista de hóspedes que chegaram ao hotel nos dias 13 e 14 de agosto de 2020.

 SELECT ClientID FROM Reservations WHERE DateFrom BETWEEN ( TO_DATE('2020-08-13', 'YYYY-MM-DD') AND TO_DATE('2020-08-14', 'YYYY-MM-DD') ) AND HotelID = 3;

Abordagem 1: classificado apenas por data

O inspetor quer a lista rapidamente. Já sabemos que é melhor ordenar a tabela/planilha de acordo com a data de chegada. Se acabamos de terminar o Exercício 2, temos sorte de a tabela já estar ordenada. Então, aplicamos a abordagem semelhante à do Exercício 2.

Por favor, tente registrar a hora, o número de linhas que você teve que ler e o número de itens na lista.

 -- Assumption: Table reservation is sorted by DateFrom -- Find the first reservation from the 13th of August 2020. Repeat Read next row Until DateFrom >= '2020-08-13' -- Prepare the list While DateFrom < '2020-08-15' If HotelID = 3 then write down the ClientID Read the next row

Usando essa abordagem, tivemos que ler 511 linhas para compilar uma lista de 46 convidados. Se pudéssemos deslizar para baixo com precisão, não precisaríamos realizar 324 leituras do ciclo de repetição apenas para localizar a primeira chegada no dia 13 de agosto. No entanto, ainda tivemos que ler mais de 100 linhas para verificar se o hóspede chegou ao hotel com HotelID de 3 .

O inspetor esperou todo esse tempo, mas não ficou feliz: em vez de nomes de convidados e outros dados relevantes, entregamos apenas uma lista de identidades sem sentido.

Voltaremos a esse aspecto mais tarde na série. Vamos primeiro encontrar uma maneira de preparar a lista mais rapidamente.

Abordagem 2: classificado por hotel, depois por data

Para classificar as linhas de acordo com HotelID e DateFrom , podemos selecionar todas as colunas e usar a opção de menu do Google Sheets Data → Sort range .

 -- Assumption: Sorted according to HotelID and DateFrom -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Find the first arrival at the hotel on 13th of August While HotelID = 3 and DateFrom < '2020-08-13' Read the next row -- Prepare the list While HotelID = 3 and DateFrom < '2020-08-15' Write down the ClientID Read the next row

Tivemos que pular as primeiras 338 chegadas antes de localizar a primeira em nosso hotel. Depois disso, analisamos 103 chegadas anteriores para localizar a primeira no dia 13 de agosto. Por fim, copiamos 46 valores consecutivos de ClientID . Ajudou-nos que, na terceira etapa, pudéssemos copiar um bloco de IDs consecutivos. Pena que não pudemos pular para a primeira linha desse bloco.

Abordagem 3: Classificada apenas por hotel

Agora tente o mesmo exercício usando apenas a planilha ordenada por HotelID .

O algoritmo aplicado à tabela ordenada apenas por HotelID é menos eficiente do que quando ordenamos por HotelID e DateFrom (nessa ordem):

 -- Assumption: Sorted according to HotelID -- Find the first reservation for the HotelID = 3. Repeat Read next row Until HotelID >= 3 -- Prepare the list While HotelID = 3 If DateFrom between '2020-08-13' and '2020-08-14' Write down the ClientID Read the next row

Neste caso, temos que ler todas as 166 chegadas ao hotel com um HotelID de 3 e, para cada uma, verificar se o DateFrom pertence ao intervalo solicitado.

Abordagem 4: classificado por data e depois por hotel

Realmente importa se classificamos primeiro por HotelID e depois por DateFrom ou vice-versa? Vamos descobrir: tente classificar primeiro por DateFrom , depois por HotelID .

 -- Assumption: Sorted according to DateFrom and HotelID -- Find the first arrival on 13th of August While DateFrom < '2020-08-13' Read the next row --Find the first arrival at the Hotel While HotelID < 3 and DateFrom < '2020-08-15' Read the next row Repeat If HotelID = 3 Write down the ClientID Read the next row Until DateFrom > '2020-08-14' or (DateFrom = '2020-08-14' and HotelID > 3)

Localizamos a primeira linha com a data relevante, depois lemos mais até localizarmos a primeira chegada ao hotel. Depois disso, para várias linhas, ambas as condições foram cumpridas, a data correta e o hotel certo. No entanto, após as chegadas ao Hotel 3, tivemos chegadas aos hotéis 4, 5 e assim sucessivamente, para a mesma data. Depois deles, tivemos que ler novamente as linhas do dia seguinte para os hotéis 1 e 2, até que pudéssemos ler as chegadas consecutivas ao nosso hotel de interesse.

Como podemos ver, todas as abordagens têm um único bloco consecutivo de dados no meio do conjunto completo de linhas, representando dados parcialmente correspondentes. As abordagens 2 e 4 são as únicas em que a lógica nos permite parar o algoritmo completamente antes de chegarmos ao final das correspondências parciais.

A Abordagem 4 tem dados totalmente correspondentes em dois blocos, mas a Abordagem 2 é a única em que os dados de destino estão todos em um bloco consecutivo.

Pelos números, fica claro que a Abordagem 2 tem mais vantagens neste caso.

Índices SQL explicados: conclusões e o que vem a seguir

A execução desses exercícios deve deixar claros os seguintes pontos:

1. A leitura de uma tabela classificada corretamente é mais rápida.
2. Se uma tabela ainda não estiver classificada, a classificação levará mais tempo do que a leitura de uma tabela não classificada.
3. Encontrar uma maneira de pular para a primeira linha que corresponda a uma condição de pesquisa na tabela classificada economizaria muitas leituras.
4. Seria útil ter uma tabela classificada com antecedência.
5. Manter as cópias classificadas da tabela para as consultas mais frequentes seria útil.

Agora, uma cópia ordenada de uma tabela soa quase como um índice de banco de dados. O próximo artigo em SQL Indexes Explained aborda uma implementação de índice rudimentar. Obrigado por ler!