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;




