O que sempre que li alguns tutoriais falando sobre MySQL e também já ouvi de alguns colegas programadores é que a falta de índices ou chaves estrangeiras numa tabela pode deixar uma consulta lenta.

Por exemplo, tenho um sistema onde a tabela usuarios tenha um campo nivel_id para referenciar o id de niveis. Mas eu não coloquei nivel_id como foreign key de niveis.id, eu apenas define ela como INT UNSIGNED.

Sobre isso tenho algumas dúvidas:

1 – É verdade mesmo que isso pode fazer com que uma consulta se torne lenta?

2 – Se a resposta pra pergunta acima é “Sim”, gostaria de saber o porquê de deixar lenta.

3 – A falta de índices ou foreign keys podem deixar uma consulta mais lenta, num caso de um JOIN por exemplo?

Exemplo para a questão 3:

SELECT A.*, B.nome FROM usuarios
JOIN niveis AS B ON B.id = A.nivel_id

Se a consulta acima retornasse umas 10.000 linhas, por exemplo, ela seria mais rápida usando FOREIGN KEY ou INDEX?

Estou perguntando isso porque já tive que dar manutenção em alguns sistemas em que não colocaram FOREIGN KEYS em alguns registros que deveriam ser relacionados e, além de tornar alguns dados inconsistentes, queria saber se corro risco de ter uma lentidão apresentada por conta disso.

Primeiro vamos separar as coisas. Criar índice e colocar uma restrição de chave estrangeira são coisas diferentes e não relacionadas diretamente.

É verdade mesmo que isso pode fazer com que uma consulta se torne lenta?

Deixar de colocar índice é quase certo que sim, na maioria dos casos, embora o sistema pode ser inteligente em certas ocasiões e conseguir fazer de uma forma razoável, ou então pode ser que tudo dê na mesma, mas é raro. Veja mais em Quais as vantagens e desvantagens do uso de índices em base de dados?. O SGDB pode criar um índice temporário na falta de um. Se isto ocorrer sempre é porque está faltando índice.

Mas tem caso que índice demais pode tornar as escritas mais lentas.

Lento é um conceito muito amplo. Tem várias formas de lentidão, algumas mais importantes, outras menos.

A chave estrangeira não afeta nada até onde se sabe no geral, mas nada impede de algum banco de dados inventar alguma maluquice.

Se a resposta pra pergunta acima é “Sim”, gostaria de saber o porquê de deixar lenta.

Ter chave estrangeira de forma direta não causa ou deixa de causar problemas. Na verdade sua existência em alguns casos pode causar lentidão porque ela pode forçar uma verificação desnecessária em algum momento, mas nada crítico. O fato é que com ou sem ela é preciso ter cuidado e saber o que está fazendo, ver se o planejamento da consulta está adequada. Por ter algo extra a fazer a tendência é ela mais prejudicar do que ajudar na performance. E se tiver a chave estrangeira e não tiver índice aí é quase certo que será pior, mas é mais pelo índice.

Se a consulta acima retornasse umas 10.000 linhas, por exemplo, ela seria mais rápida usando FOREIGN KEY ou INDEX?

Com índice, certamente que sim, desde que criado adequadamente, claro. Com chave estrangeira não, ela é só uma restrição, não uma forma de otimização, a consulta se baseia nela mesma, não importa como a chave estrangeira foi definida, ela é uma ferramenta de integridade e não de acesso.

Quando pega tudo e não tem ordem específica a necessidade de índice é menor ou inexistente, pelo menos para a tabela usuarios aí.

Já niveis sem índice pode ser trágico. Mas pode nem ser tanto, pode ser que tenha poucos níveis para pegar, pode ser que entre tudo em cache e fique rápido. A pergunta não diz quantos níveis existem.

Uso do EXPLAIN

Pode ser que um dia seja lento, outro menos de acordo com o uso, pode ser que uma versão nova do MySQL melhore, ou seja, é detalhe. Cache conta muito nessas horas também. Por isso o EXPLAIN deve ser usado com cuidado, as pessoas acham que todos os dias ele dará o mesmo resultado, isto não é verdade, então é bobagem ver um exemplo dele para o entendimento geral. O que vem em um momento, em uma caso específico, na mesma tabela, na mesma query, pode ser diferente em outro caso de uso. O EXPLAIN sem saber da configuração geral, sem saber que dados tem ali, sem conhecer as estatísticas armazenadas só te engana, portanto tem nada de precisão nela. E se não souber interpretar o resultado pode causar mais estrago.

Consistência

Essa estória de não ter FOREIGN KEY e inconsistência andarem juntos só é verdade quando você deixa o banco de dados cuidar para você, se sua aplicação cuidar ela se torna desnecessário. Tem gente que não usa FOREIGN KEY nunca ou quase nunca, e é alguém que gosta de performance, como você deve saber desse seu amigo :). Sabendo fazer a performance melhor, uma pena que nem todos os saibam, por isso tem bastante software ruim por aí.

Performance

O segredo da performance é a modelagem básico correta pensada para performance e uso adequado de índice. Evite:

• ter que acessar mais de uma tabela na consulta
• acessar coisas demais e desnecessárias na maioria das consultas
• e acessar diretamente a tabela quando precisa de acesso diferente da estrutura básica dela.

Veja Como aplicar indexes para melhorar a performance das queries?. E também Subqueries podem diminuir a performance? Mito ou verdade? já que JOIN não deixa de ser uma subquery.

Em geral

1. talvez, depende
2. talvez, depende
3. talvez, depende

Um pouco menos geral

INDEXES melhoram buscas com valores específicos nas colunas indexada (incluindo joins) mas incorrem overhead em INSERT|UPDATE, vide Quais as vantagens e desvantagens do uso de índices em base de dados?.

FOREIGN KEYS vão afetar negativamente tempo de INSERT|UPDATE, o que é lógico já que ter que garantir integridade referencial envolve checagens extras (pense em como você implementaria esse mecanismo e vai ver que não tem como fugir de um custo extra). Elas também podem acelerar operaçõesSELECT, contudo como e porque vai depender do banco de dados sendo usado.

O impacto “negativo” de FKs e INDEXES em inserções/atualições é claramente documento em alguns bancos, ex: PostgreSQL recomenda remover indexes e foreign keys em bulk inserts, o mesmo para MySQL.

O impacto positivo de FKs em SELECTS é algo meio obscuro, sinceramente nunca havia ouvido falar disso e não encontrei documentação explicita sobre, mas existem relatos pela net e até um exemplo abaixo mostrando como de fato isso ocorre.

Sendo preciso

Use EXPLAIN: PostgreSQL, MySQL, SQLite, Oracle, SQL Server.

Vamos pegar seu exemplo e fazer uma análise rasa dele no MySQL:

CREATE TABLE niveis (
    id INT,
    nome VARCHAR(255)
);
CREATE TABLE usuarios (
    id INT,
    nivel_id INT
);

Sem indexes e fks com a query

EXPLAIN SELECT A.*, B.nome FROM usuarios AS A JOIN niveis AS B ON B.id = A.nivel_id G

temos:

*************************** 1. row ***************************
       id: 1
select_type: SIMPLE
    table: A
partitions: NULL
     type: ALL
possible_keys: NULL
      key: NULL
  key_len: NULL
      ref: NULL
     rows: 1
 filtered: 100.00
    Extra: NULL
*************************** 2. row ***************************
       id: 1
select_type: SIMPLE
    table: B
partitions: NULL
     type: ALL
possible_keys: NULL
      key: NULL
  key_len: NULL
      ref: NULL
     rows: 1
 filtered: 100.00
    Extra: Using where; Using join buffer (Block Nested Loop)

Vamos focar na coluna type com valores ALL e ALL. O que isso indica é que todas as linhas da tabela A vão ser escaneadas e para cada uma delas nós vamos escanear todas as linhas da tabela B, o equivalente ao seguinte pseudo-código:

para id in A:
    para id_2 in B:
        if id == id_2:
            print(id, id_2)

Uma operação O(n^2).

Agora usando INDEXES (PRIMARY KEY gera UNIQUE CLUSTERED INDEXES em MySQL):

ALTER TABLE niveis ADD PRIMARY KEY (id);
ALTER TABLE usuarios ADD PRIMARY KEY (id);

mysql> EXPLAIN SELECT A.*, B.nome FROM usuarios AS A JOIN niveis AS B ON B.id = A.nivel_id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: B
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.A.nivel_id
         rows: 1
     filtered: 100.00
        Extra: NULL

Novamente temos ALL para A, mas agora com eq_ref para B. ALL vai percorrer todas as linhas de A e eq_ref vai utilizar o index e pegar apenas as linhas necessárias, equivalente a:

para id in A:
    if B possui id:
        print(id, id)

O(n).

Por fim vamos análisar com a FOREIGN KEY:

ALTER TABLE usuarios ADD FOREIGN KEY (nivel_id) REFERENCES niveis(id);

mysql> EXPLAIN SELECT A.*, B.nome FROM usuarios AS A JOIN niveis AS B ON B.id = A.nivel_id G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: A
   partitions: NULL
         type: index
possible_keys: nivel_id
          key: nivel_id
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: B
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: test.A.nivel_id
         rows: 1
     filtered: 100.00
        Extra: NULL

index + eq_ref. Segundo a documentação index é “praticamente” ALL, porém geralmente mais rápido pois faz a busca completa na estrutura de indexão ao invés de diretamente na tabela. Ou seja, ainda O(n), mas mais veloz que ALL + eq_ref.

Por fim é importante deixar claro que resultados de EXPLAIN variam, e muito, conforme dados e quantidade deles, então é importante não ficar preso a primeira análise pois ela vai precisar ser repetidada conforme seu banco cresce e a estratégia de indexação pode mudar várias vezes (isso não quer dizer que o EXPLAIN é inútil em uma database vazia, o banco não vai fazer mágica para transformar suas queries ineficientes em algo decente, um ALL, ALL por exemplo é quase sempre um mal sinal).

Comentários

Primeiramente se você não tem certeza de que isso vai causar um impacto visívelmente negativo na sua aplicação (quase nunca vai), use foreign keys. Razões:

• O banco de dados é otimizado para lidar com isso, tentar replicar essas checagens na sua aplicação vai definitivamente ser mais lento.
• A grande força de SQL é ser uma linguagem declarativa, você não especifica “como” fazer, apenas “o quê”. Isso vale para a integridade referencial, você declara quais relações existem sem precisar implementar elas em código. Levando a ideia para seu código você teria que fazer as duas coisas, declarar as relações e implementar as checagens, não muito produtivo.
• Se o seu modelo é grande, se seu app é complicado e você não tem uma longa barba e se chama Ken Thompson (ou algum dos outros barbudos antediluvianos) você vai gerar incosistência no seus dados se tentar manter eles coerentes trantando tudo na aplicação. É um fato da vida, aceite.

Sobre os INDEXES não tem receita de bolo, você vai ter que avaliar o uso geral da sua aplicação e que tipos de queries são feitas para poder determinar quando utilizar eles. Sim, EXPLAIN é complicado e tedioso, mas você não precisa usar a todo momento, apenas quando estiver em dúvida sobre o comportamento do RDBMS em queries complexas, aí não tem como fugir.

Meu amigo, irei resumir o que em conhecimento sobre isso.

FK desnecessárias fazem com que o banco realize consultas desnecessárias aumentando o custo. Caso o seu modelo necessite de FK, implemente-as.

Sobre os índices: quase todos os SGBDs criam índices por baixo dos panos, mas coloca-los de forma explícita talvez seja o melhor caminho. Isso lhe faz ter aumento de performance e em algumas consultas pode reduzir o custo de forma grandiosa.

Varios fatores podem tornar a pesquisa lenta:

Select * from usarios

é mais lento que

Select id,usario,senha from usarios

porque ? no primeiro exemplo eu forcei o banco a pesquisar e ordenar todas as colunas da tabela usuarios. Claro que em tabelas pequenas o tempo é imperceptivel, mas imagine uma tabela que possua um numero relativamente alto de colunas, o desempenho da consulta seria afetado.

Indices são criados automaticamente nos SGBDs mais inteligentes porém nem todos são otimizados para este tipo de serviço. Criar um indice é uma forma de aliviar o stress do banco.

Por exemplo eu vou mostrar quando se cria um indice por nome.

Observe que na coluna Indice ID, é o comportamento normal do banco.

Quando eu criei um indice para o nome, ele ordenou dessa forma por baixo dos panos “Observe a indice por Nome“.

Se eu rodar uma query buscando todos os nomes que começa com L, em qual indice vai ser mais rapido? acertou se disse no indice de nome, pois o banco vai ir direto naquela parte onde todos nomes começam com L.

Sobre FKS, como foi dito acima se o banco não precisa não as implemente, FKS desnecessarias são equivalente a buscas desnecessarias.