Curso de MariaDB / Criar índices simples e compostos e escolher a ordem das colunas

Um índice permite localizar e ordenar linhas por uma estrutura de chaves, e a ordem das colunas de um índice composto influencia quais consultas ele pode ajudar a resolver. Examine filtros, ordenação, colunas solicitadas e índices existentes e compare o benefício de leitura com o custo de escrita e armazenamento.

Considerar a manutenção do índice

Uma estrutura de chaves ordenadas pode delimitar uma busca em vez de examinar todas as linhas. Porém, inserções, atualizações e exclusões também precisam manter seus índices. Acrescentar mais nem sempre é melhor: estude consultas frequentes, proporção de linhas selecionadas e necessidades de união e ordenação.

Consulta ou regra Ordem candidata Verificação
Pedidos recentes de um cliente customer_id, ordered_at, order_id Igualdade, intervalo temporal e desempate
Pedidos de todos os clientes por data Pode exigir início pela data É um requisito diferente do filtro por cliente
Código de produto único UNIQUE(sku) Também aplica uma regra de integridade
Suporte a uma chave estrangeira existente Examinar SHOW INDEX Evitar duplicações acidentais

A regra UNIQUE do código é aplicada na prática de ALTER TABLE. Um índice pode atender a consultas e a uma condição de integridade; examine ambos os usos antes de removê-lo com base em um SELECT isolado.

Inspecionar os índices existentes

Conecte ao servidor de prática como course_user e selecione shopcourse. O guia do cliente e de arquivos SQL UTF-8 explica como salvar os blocos e executar SOURCE C:/mariadb-lab/lesson.sql;, substituindo o caminho pelo real. Execute SQL dentro do cliente MariaDB.

Confira SELECT VERSION(), DATABASE(); e SHOW TABLES; antes de criar c39_customers, c39_products, c39_orders e c39_items. Os nomes pertencem exclusivamente ao exercício. Se já existirem, examine definições e registros antes de decidir se são prescindíveis; preserve tabelas de aplicações.

USE shopcourse;
CREATE TABLE c39_customers (
 customer_id INT NOT NULL PRIMARY KEY,
 customer_name VARCHAR(50) NOT NULL,
 city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_products (
 product_id INT NOT NULL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 category VARCHAR(30) NOT NULL,
 price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
 stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_orders (
 order_id INT NOT NULL PRIMARY KEY,
 customer_id INT NOT NULL,
 ordered_at DATETIME NOT NULL,
 status VARCHAR(20) NOT NULL,
 shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
 CONSTRAINT fk_c39_order_customer FOREIGN KEY (customer_id)
  REFERENCES c39_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c39_items (
 order_id INT NOT NULL,
 product_id INT NOT NULL,
 quantity INT NOT NULL CHECK (quantity > 0),
 unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
 PRIMARY KEY (order_id, product_id),
 CONSTRAINT fk_c39_item_order FOREIGN KEY (order_id)
  REFERENCES c39_orders(order_id) ON DELETE RESTRICT,
 CONSTRAINT fk_c39_item_product FOREIGN KEY (product_id)
  REFERENCES c39_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c39_customers VALUES
 (1,'김민수','서울'),(2,'이서연','부산'),
 (3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c39_products VALUES
 (1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
 (3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c39_orders
 (order_id,customer_id,ordered_at,status,shipping_fee) VALUES
 (101,1,'2026-01-10 10:00:00','paid',1000),
 (102,1,'2026-01-20 11:00:00','paid',1000),
 (103,2,'2026-02-05 12:00:00','paid',1000),
 (104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c39_items VALUES
 (101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
 (103,1,1,3500),(103,3,2,8000),(104,2,1,1500);

O SQL mantém nomes e cidades originais em coreano. Os produtos 1–4 são caderno, caneta, caneca e luminária. Os clientes 1 e 3 vivem em Seul; o 2, em Busan; e o 4, em Daejeon. Conserve os literais UTF-8 ao reproduzir os resultados.

SHOW INDEX FROM c39_orders;

Confira Key_name, Column_name e Seq_in_index: o último indica a posição da coluna dentro de cada chave. Examine também Non_unique e Sub_part ao comparar unicidade e prefixos. Nomes distintos não comprovam índices diferentes.

A chave primária e as chaves estrangeiras podem ter índices de suporte existentes. Examine também SHOW CREATE TABLE para identificar as restrições dependentes. Antes de criar outro índice, confira o papel daquele gerado pelo servidor.

Criar um índice por cliente, data e identificador

CREATE INDEX idx_c39_customer_date ON c39_orders(customer_id,ordered_at,order_id);
EXPLAIN SELECT order_id,ordered_at,status FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SELECT order_id FROM c39_orders
WHERE customer_id=1 AND ordered_at>='2026-01-01' AND ordered_at<'2026-02-01'
ORDER BY ordered_at,order_id;
SHOW INDEX FROM c39_orders;

A consulta retorna os pedidos 101 e 102. customer_id limita a população a um cliente; ordered_at expressa o intervalo de janeiro com limite superior exclusivo; order_id define o desempate entre pedidos da mesma data e hora.

Leia o EXPLAIN como seu servidor o retorna. possible_keys indica candidatos e key o índice escolhido; criar o índice não obriga o otimizador a usá-lo. Com quatro pedidos, examinar toda a tabela pode ser uma escolha razoável. Esta prática não demonstra ganho de velocidade.

Compreender colunas iniciais, intervalos e cobertura

Um índice composto organiza chaves pela ordem das colunas. Evite presumir que uma consulta somente por data terá o mesmo acesso por intervalo com uma chave que começa por cliente. Isso também difere de afirmar que nenhuma leitura do índice é possível sem uma igualdade na primeira coluna.

Intervalos, direções de ordenação, prefixos de texto e colunas retornadas influenciam o acesso possível. Um índice de cobertura contém as informações necessárias à consulta e pode evitar parte das leituras de linhas da tabela. Acrescentar indiscriminadamente todas as colunas aumenta o tamanho das chaves e sua manutenção.

Compare o índice selecionado e o campo Extra do EXPLAIN, distinguindo índice candidato, índice utilizado e consulta coberta. O guia oficial de projeto de índices conforme um SELECT apresenta critérios e exceções a conferir nas consultas e na versão reais.

Examinar sobreposições e dependências de restrições

Dois índices podem ter nomes distintos e sequências de colunas iguais ou parcialmente sobrepostas. Compare também unicidade e comprimento dos prefixos antes de decidir se são redundantes. Execute SHOW INDEX novamente depois de criar a chave composta para observar o suporte da chave estrangeira.

Remover o único índice de suporte a uma chave estrangeira pode ser rejeitado. Antes de remover um, identifique a restrição e outras consultas dependentes e prepare um substituto quando necessário. A prática de chaves e relações ajuda a revisar essas dependências. PRIMARY KEY e UNIQUE também impõem regras de dados e não se tornam dispensáveis apenas porque um SELECT não as utiliza.

Preservar os resultados ao comparar custos de acesso

Acrescentar um índice precisa preservar as linhas solicitadas e o significado da ordem. Compare resultados antes e depois, além de planos representativos, leituras reais, latência e custo de escrita com distribuição de dados adequada. Registre ambiente e método: um cache mais aquecido na segunda execução não comprova que toda a diferença veio do índice.

Antes de repetir a preparação, examine banco selecionado, definições e registros e copie o que precisar conservar. Se as quatro tabelas seguintes forem objetos prescindíveis somente do exercício, remova nesta ordem, primeiro as filhas e depois as referenciadas. DROP TABLE remove estrutura e dados e não é desfeito por um ROLLBACK comum; mantenha a limpeza fora dos testes transacionais.

DROP TABLE shopcourse.c39_items;
DROP TABLE shopcourse.c39_orders;
DROP TABLE shopcourse.c39_products;
DROP TABLE shopcourse.c39_customers;