Curso
Manipulação de dados em SQL
4 h
335K
Como cientista de dados, você vai lidar com grandes volumes de dados com frequência. Trabalhar com dados (em quantidades massivas) não é nada simples. Para fazer isso da forma mais eficiente possível, é essencial entender como os dados são organizados fisicamente, assim você consegue processá-los com técnicas certeiras.
SQL é uma habilidade indispensável para qualquer engenheiro de software moderno, porque a maioria dos softwares depende de algum tipo de dado e integra bem com um SGBD (Sistema de Gerenciamento de Banco de Dados Relacional). Seja um aplicativo web, uma API ou uma aplicação interna, o SGBD está sempre lá. E SQL é a linguagem para consultar um SGBD.
Como cientista de dados, é fundamental conhecer SQL e as técnicas relacionadas. Para conseguir consultar um SGBD e obter respostas para questões específicas sobre os dados com que você trabalha, SQL é o mínimo necessário.
No vídeo mais recente dele com a DataCamp, David Robinson (Chief Data Scientist @ DataCamp) mostrou como usa SQL em um problema de Data Science. Vale a pena conferir. O fluxo de trabalho dele é bem interessante.
Hoje você vai aprender uma técnica chamada indexação, que trata principalmente da organização dos dados dentro de um banco, e vai implementar algumas delas em SQL. Isso vai te dar uma visão geral de como a indexação pode ser usada para armazenar informações e como ela resulta em tempos de execução mais rápidos.
Obs.: Antes de começar este tutorial, é altamente recomendado aprender o básico de SQL se você ainda não domina. O curso Intro to SQL for Data Science da DataCamp é um excelente recurso para revisar seus fundamentos de SQL.
Uma breve nota sobre a organização de registros em um arquivo
Antes de estudar indexação, é imprescindível entender como os dados são organizados fisicamente dentro dos arquivos. Claro que não vamos entrar em todos os detalhes, mas ter uma boa visão geral da organização vai te ajudar a entender indexação com clareza. Vamos lá.
A organização de registros/dados em geral trata de como os registros são armazenados e, de forma ampla, pode ser dividida em dois tipos:
-
Organização ordenada: todos os registros em um arquivo são ordenados por algum valor de chave de busca. Em geral, utiliza-se a busca binária para procurar qualquer item em um arquivo ordenado. Isso torna a busca bem eficiente, pois a complexidade de tempo fica em tempo logarítmico. Por outro lado, inserir no arquivo se torna uma operação cara, porque pode ser necessário reorganizar o arquivo inteiro para acomodar o novo item.
-
Organização não ordenada: aqui, os registros são inseridos onde houver espaço disponível, geralmente no final do arquivo. Como a busca é linear, ela não é tão eficiente quanto na variante anterior, mas a inserção não é uma operação tão custosa.
Mesmo com uma organização ordenada, e se os tamanhos dos blocos dos arquivos e o tamanho de cada registro forem muito grandes? Vamos explorar isso na próxima seção.
Por que indexar
Você vai começar entendendo a motivação por trás do uso de indexação para armazenar arquivos/informações de forma eficiente e como isso melhora outras operações relacionadas a esses arquivos/informações.
Considere que você tem uma tabela (relação) com vários registros em um banco de dados. Os registros estão divididos em 1000 blocos. Visualmente, a organização fica assim:
A partir da organização acima, fica claro que:
- A ordenação é aplicada à primeira coluna dos registros (considere que este é um sistema relacional em que os dados são organizados em formato tabular)
- O número de blocos em que os dados totais estão divididos é de 1000 blocos.
Lembre-se: cada registro contém outras colunas, mas, para essa organização, a ordem é aplicada à primeira coluna e os blocos são divididos de acordo. Agora, se você fizer uma busca binária para procurar algo nessa organização de registros, o tempo total será $\log_{2}1000$, que dá 10 unidades de tempo. Dá para melhorar esse tempo de busca?
Claro que sim.
Pense em ler um livro sem página de índice. Você abre uma página aleatória (ou, no caso da busca binária, a página do meio) e vai folheando para a esquerda e para a direita até chegar onde quer. Isso leva tempo, não leva?
Mas se o livro tivesse uma página de índice, a busca seria bem mais eficiente. Você iria direto à página consultando o índice. Podemos aplicar indexação do mesmo jeito no caso acima.
Veja um resumo de como aplicar indexação aqui:
- Manter um ponteiro de bloco para cada bloco junto com os valores ordenados usados na organização anterior.
Isso vai resultar em um número menor de blocos usados para armazenar os arquivos de dados. Agora, para buscar um registro específico, você só precisa pesquisar nessa nova organização com menos blocos e, assim, chegar ao seu registro (se ele existir) em bem menos tempo. Vamos visualizar. Considerando que os registros indexados resultaram em 8 blocos, a organização fica assim:
O tempo de busca vai cair drasticamente por causa do menor número de blocos. Suponha que você queira buscar o registro 90. Nesse novo esquema indexado, primeiro você localiza o ponteiro de bloco correspondente, que neste caso é 3, e então, com essa informação, encontra os registros originais de uma vez só.
Então, o tempo de busca, neste caso, será $\log_{2}8 + 1$ = 4 unidades de tempo, o que é significativamente menor em comparação com o anterior.
O exemplo acima mostra por que precisamos de indexação. Aqui vão alguns pontos importantes sobre indexação:
- A ordenação nos registros de dados originais pode ser feita usando apenas um campo. Em seguida, as entradas desse campo podem ser indexadas. Só se você buscar por esse campo terá tempo de busca melhor. (Extremamente importante)
- Os índices também são ordenados.
- Um registro de índice contém dois campos (estrutura do arquivo de índice):
- A chave do arquivo original
- Ponteiro para o bloco onde a chave está disponível nos registros de dados originais
- Usa-se busca binária para pesquisar nos índices.
- Para acessar um registro usando as entradas indexadas, o número médio de acessos a blocos necessários é:
$\log_{2}B_i + 1$, em que $B_i$ é o número de blocos nos registros indexados - O índice pode ser criado em qualquer campo da relação (chave primária, chaves candidatas, não chaves).
Agora, dependendo da ordenação dos registros originais e de quantos registros você mantém no arquivo indexado, existem vários esquemas de indexação. Na próxima seção, você vai estudar os mais populares.
Estratégias de indexação
- Indexação densa: se for criada uma entrada de índice para cada valor de chave de busca, temos indexação densa. Veja o diagrama a seguir para entender visualmente.
- Indexação esparsa: se a entrada de índice é criada apenas para alguns registros, temos indexação esparsa. Aqui vai um diagrama ilustrando isso.
Os diagramas acima tornam os dois esquemas fáceis de entender. Um ponto crucial aqui é que o exemplo de indexação esparsa acima é uma combinação de indexação densa e esparsa. Isso porque para cada valor único de chave de busca (1, 2 e 3) existe um índice, mas não há índice para cada registro de dado.
Agora você vai estudar outros tipos de esquemas de indexação com base no nível dos registros. Na indexação de nível único, há apenas um arquivo de índice. Mas às vezes o arquivo de índice fica tão grande que ele próprio é indexado. Nesse caso, temos indexação multinível. Vamos avançar.
Aqui está uma visão geral das estratégias de indexação com base em níveis.
Indexação de nível único:
- Indexação primária
- Indexação por clusterização
- Indexação secundária
Indexação multinível:
- Árvore B
- Árvore B+
Você vai estudar todas as estratégias que pertencem à indexação de nível único. No final do tutorial, deixamos um link para você explorar a indexação multinível, se tiver interesse. Vamos analisar a indexação primária.
Indexação primária
Um índice primário é um arquivo ordenado cujos registros têm comprimento fixo com dois campos:
- O primeiro campo é o mesmo que a chave primária do arquivo de dados.
- O segundo campo é um ponteiro para o bloco de dados onde a chave primária está disponível. - Fonte
O índice criado para o primeiro registro de cada bloco é chamado de âncora de bloco. Na indexação primária, o número de entradas no índice = número de blocos de dados originais. O número médio de acessos a blocos usando índice primário é:
Consulte o diagrama a seguir para entender melhor: 
A figura da direita mostra os registros de dados originais divididos em vários blocos. Note que a coluna com os números 1, 2, 3, ... , 9 representa as chaves primárias. A figura da esquerda mostra as entradas indexadas, em que cada entrada consiste de:
- O primeiro registro de cada bloco de dados (âncora do bloco)
- O segundo campo indica o ponteiro do bloco.
Pense um pouco: que tipo de indexação é essa (esparsa ou densa)? Use a seção de comentários para postar sua resposta.
Agora você vai estudar a indexação por clusterização.
Indexação por clusterização
Um índice por clusterização é criado em um arquivo de dados cujos registros estão fisicamente ordenados por um campo que não é chave e que não tem valor distinto para cada registro. Esse campo é chamado de campo de clusterização e é com base nele que a indexação é feita. Daí o nome: índice por clusterização.
Diagramas sempre ajudam a entender. Como você pode ver, os dados originais estão ordenados por um atributo que não é chave e, para cada valor distinto desse atributo, é criada uma entrada de índice. O número médio de acessos a blocos necessário para localizar um registro usando esse esquema é $\geq$ $\log_{2}B_i + 1$, em que $B_i$ é o número de blocos nos registros indexados. Note o sinal $\geq$ aqui.
No índice por clusterização, depois de localizar o bloco onde a chave está presente, você pode precisar percorrer outros blocos também (o que fica evidente na figura acima).
Vale pensar: que tipo de indexação é essa (esparsa ou densa)? Use os comentários para postar sua resposta. Agora vamos ver a indexação secundária.
Indexação secundária
Suponha que você tenha uma tabela chamada Employee no seu banco. A tabela tem os seguintes atributos:
- employee_id
- employee_name
- employee_department
- employee_salary
employee_id é a chave primária. Você já criou a indexação primária nessa tabela com base em employee_id. Mas, ao desenvolver uma aplicação, percebeu que a maioria das consultas usa o atributo employee_name. Nesse caso, a indexação primária não vai ajudar muito, e é uma boa prática manter uma indexação separada para todos os valores de employee_name. De qualquer forma, os nomes dos funcionários não vão estar ordenados no banco. Então, indexá-los certamente vai acelerar as consultas envolvendo nomes.
Esse é um exemplo clássico de indexação secundária. Vamos construir uma imagem adequada para ela também:
Se tiver uma representação melhor, compartilhe na seção de comentários.
Agora você vai ver como criar índices no PostgreSQL. Faça o curso Joining Data in PostgreSQL da DataCamp se quiser revisar o básico.
Criando índices no PostgreSQL
Antes de criar índices em um banco PostgreSQL, você precisa ter dados em uma tabela. Vamos criar uma tabela simples chamada Student com os seguintes campos:
- student_id
- student_name
- student_year
Você vai definir student_id como chave primária e não permitir valores nulos em nomes e anos dos alunos.
A consulta será a seguinte:
CREATE TABLE STUDENT(
student_id TEXT PRIMARY KEY,
student_name TEXT NOT NULL,
student_year TEXT NOT NULL
);
Após criar a tabela, insira alguns dados nela. Para facilitar, você pode usar um .csv e importá-lo para Student. Você pode importar um arquivo .csv compatível para uma tabela PostgreSQL com a seguinte consulta:
COPY STUDENT FROM '/path/to/csv/Student.csv' WITH (FORMAT csv);
A consulta acima pressupõe que o nome do arquivo .csv de onde os dados estão sendo copiados é Student.
Vamos olhar os dados agora. Executar select * from STUDENT; retorna os seguintes registros: 
A consulta select deve retornar um total de 86 registros. Agora você já pode criar índices. É possível criar índices de coluna única usando a seguinte sintaxe:
CREATE INDEX index_name
ON table_name (column_name);
Vamos criar um índice no campo student_id (indexação primária).
CREATE INDEX id_index
ON STUDENT (student_id);
Também é possível criar índices multicoluna:
CREATE INDEX id_index
ON STUDENT (student_id,student_name);
Você pode remover um índice com a seguinte sintaxe:
DROP INDEX index_name;
É difícil perceber o impacto da indexação em uma tabela pequena como STUDENT. Mas se a tabela fosse grande (pense em uma universidade grande com registros semelhantes), a indexação certamente teria um papel vital.
Você conseguiu!
Parabéns por chegar até o fim. Neste tutorial, você aprendeu sobre indexação, por que ela é necessária e diferentes esquemas de indexação. Você também viu como fazer uma indexação simples no PostgreSQL.
Mas você não estudou indexação multinível aqui. A seguir, alguns ótimos recursos para explorar se tiver interesse:
Mas indexar ajuda sempre? Existem casos em que não é recomendável usar indexação.
Espero que o tutorial tenha ajudado você a clarear os fundamentos de indexação. Conte suas descobertas na seção de comentários.
Confira o Learn SQL Hub da DataCamp.
Tópicos
