Constantemente vejo as pessoas dizerem que os índices ficam mais lentos updatee delete. insertIsso é usado como uma declaração geral, como se fosse um absoluto.
Ao ajustar meu banco de dados para melhorar o desempenho, continuo me deparando com essa situação que parece contradizer essa regra logicamente para mim, e em nenhum lugar posso encontrar alguém que diga ou explique de outra forma.
No SQL Server, e acredito/presumo que a maioria dos outros DBMS, seus índices são criados com base em colunas específicas que você especifica. Inserções e exclusões sempre afetarão uma linha inteira, portanto, não há como não afetar o índice, mas as atualizações parecem um pouco mais exclusivas, elas podem afetar especificamente apenas determinadas colunas.
Se eu tenho colunas que não estão incluídas em nenhum índice e as atualizo, elas ficam lentas apenas porque tenho um índice em outras colunas dessa tabela?
Como exemplo, digamos que na minha Usertabela eu tenha um ou dois índices, a chave primária que é uma coluna de Incremento de Identidade/Auto e possivelmente outra em alguma coluna de chave estrangeira.
Se eu atualizar uma coluna sem o índice diretamente nela, como, digamos, seu número de telefone ou endereço, essa atualização fica mais lenta porque tenho índices nesta tabela em outras colunas em qualquer situação? As colunas que estou atualizando não estão em índices, então, logicamente, os índices não deveriam ser atualizados, não deveriam? Se alguma coisa, eu acho que eles são acelerados se eu usar os índices na cláusula WHERE.
Para um sistema moderno relativamente rápido, a adição de um único índice a uma tabela OLTP provavelmente será praticamente indetectável do ponto de vista de desempenho para a grande maioria dos sistemas . Dito isso, você não deve criar índices desnecessários e provavelmente não deve criar índices de coluna única para cada coluna em uma tabela.
Você está correto na suposição de que, para muitas consultas, a presença de índices úteis resultará em uma melhoria de velocidade muito perceptível.
Embora sua pergunta pareça estar relacionada ao desempenho, existem vários outros problemas em potencial na adição de índices, incluindo, entre outros:
O tempo necessário para criar o índice pode resultar em bloqueio enquanto o índice é adicionado à tabela. O bloqueio dura muito pouco e provavelmente não criará um grande problema.
As alterações de índice resultam na invalidação dos planos de execução para todos os planos que fazem referência à tabela subjacente. Quando esses planos de execução são recompilados, o desempenho pode mudar negativamente para algumas consultas.
As modificações de índice podem resultar em consultas retornando erros onde nenhum foi retornado anteriormente. Considere o caso de um índice filtrado que foi usado para retornar datas contidas em um campo varchar; se o filtro eliminou todas as linhas que não eram datas e esse filtro for alterado posteriormente, as consultas que dependiam desse índice agora podem falhar ao tentar converter dados sem data.
Um novo índice pode fazer com que a ordem de execução seja alterada, resultando em possíveis deadlocks ocorrendo onde não ocorreram antes.
O Guia de Design de Índice da Microsoft é um bom recurso em relação aos pontos acima.
Você está correto ao dizer que atualizar uma coluna não indexada não causará alterações nos índices. Em um caso simples, também não haveria impacto geral na tabela.
Se uma consulta puder usar o Índice para pesquisar dados, ela poderá acelerar a pesquisa, mas o comportamento exato (dependendo da marca SQL) pode ser diferente de outras marcas de SQL. (Eu uso o Microsoft SQL Server principalmente.)
É claro que atualizar uma coluna com um volume de dados significativamente maior pode fazer com que algumas linhas sejam movidas para páginas diferentes, etc.
Atualizações em colunas não indexadas não modificam o índice, mesmo que a linha de dados seja movida como consequência da atualização, pois um registro de encaminhamento é deixado no lugar.
Portanto, não há penalidade para a atualização.
Se a operação de atualização visa uma coluna não indexada de tamanho fixo (como um número inteiro), ela não deve ser lenta em geral, mas em comparação com uma instrução select, a atualização também deve ser gravada no disco lento.