É uma otimização prematura para adicionar índices de banco de dados?

Otimização prematura é "otimizar" algo por causa de um sentido vago e intuitivo de que, provavelmente, isso provavelmente será lento, especialmente em detrimento da capacidade de leitura e manutenção do código . Isso não significa intencionalmente não seguir boas práticas bem estabelecidas em relação ao desempenho.

Às vezes, essa é uma linha difícil de desenhar, mas eu definitivamente diria que não adicionar nenhum índice antes de você ir ao vivo é a otimização late-late ; isso punirá os primeiros usuários - seus usuários mais ansiosos e importantes - e dará a eles uma visão negativa de seu produto, que eles espalharão em avaliações, discussões, etc. Monitorar consultas para encontrar pontos problemáticos que precisem de indexação é um boa ideia, mas eu faria isso antes do beta.

monitor for slow queries once we go live

porque nada diz qualidade, como fazer com que seus usuários sofram por falta de design!

Você deve saber quais consultas precisam de índices ao projetar as tabelas, você sabe quais colunas estão sendo consultadas em onde cláusulas e junções. Estes devem ser indexados já porque o que pode não ser aparente em um ambiente ao vivo pode rapidamente se tornar aparente quando a carga ou os dados armazenados aumentam. O que você não quer fazer quando isso acontece é colocar os índices em todas as consultas "lentas", e você acabará com um índice sobre tudo.

"Otimização prematura", em seu sentido depreciativo, significa otimização dispendiosa que pode não ser necessária. Não significa toda a otimização implementada antes do último ponto possível para evitar a falência!

Em particular, é legítimo otimizar com base em testes de desempenho antes de entrar em produção, para garantir que você possa atender a alguns requisitos sensatos (embora aproximados) para que seu aplicativo não seja completamente ruim.

Com um mínimo absoluto você deve carregar seu banco de dados com uma quantidade plausível de dados de teste e verificar a capacidade de resposta do seu aplicativo. Isso não é prematuro, já que você sabe que vai acontecer e vai capturar qualquer pergunta que acione varreduras absurdamente lentas. Como A E diz em um comentário:

Use indexes to avoid a full table scan for any query which the end-user will commonly be doing in real-time

Pelo menos, para tabelas planejadas para uso.

Então, como um atalho para isso, se você tiver uma experiência significativa com o mecanismo de banco de dados e já tiver planejado os testes ao escrever o primeiro recorte do código, muitas vezes você saberá, mesmo sem executá-lo, que a consulta você está escrevendo será muito lento sem um índice. É claro que você está livre para fingir que não sabe, e assistir o teste falhar antes de adicionar o índice para fazê-lo passar, mas não há razão para o código defeituoso conhecido (porque não responde) ir ao vivo.

I feel this is premature optimisation because our application is not even released yet. I suggested to monitor for slow queries once we go live and then add indices accordingly.

Você não pode tratar seus usuários finais e o ambiente de produção como garantia de qualidade. Em outras palavras, você está dizendo que vai descobrir isso em produção. Eu não acho que é o caminho certo, e eu vejo essa abordagem dar errado todos os dias .

Você precisa manter uma coisa em mente, já que não pode pintar isso com um pincel largo.

Qual é a sua carga de trabalho comum ?

Isso pode parecer óbvio ou tedioso, mas é significativo na prática. Se você tiver 10 consultas que compõem 98% de sua carga de trabalho (bastante comum, acredite ou não), minha recomendação seria uma análise difícil antes da produção . Com dados realistas e representativos, certifique-se de que essas 10 consultas sejam as melhores possíveis (o perfeito é um desperdício de tempo valioso e quase não é possível).

Para as outras 200 consultas que compõem os 2% da carga de trabalho , são aquelas que provavelmente não valem muito esforço, e comporão a solução de problemas perf. esquisitices na produção. Isso também é uma realidade, e não uma coisa terrivelmente ruim. Mas isso não significa ignorar as melhores práticas de indexação ou fazer suposições estimadas sobre a recuperação de dados.

É comum e boa prática descobrir o desempenho do banco de dados antes da produção. Na verdade, há uma posição relativamente comum para esse tipo de coisa chamada DBA de desenvolvimento .

Mas ...

Alguns levam isso longe demais e ficam loucos adicionando índices "apenas no caso". Alguém recomenda que este é um índice ausente? Adicione e quatro outras variações. Também é uma má ideia. Você precisa não apenas pensar em sua recuperação de dados, mas e a modificação de dados? Quanto mais índices você tiver em uma tabela, em geral, mais sobrecarga você terá quando modificar os dados.

Como a maioria das coisas, há um equilíbrio saudável.

Como uma divertida nota secundária ... A pluralização do "Index"

"Índices" são para pessoas financeiras

"Índices" são para nós

Não, não é uma otimização prematura, mas deve ser feita corretamente, como qualquer otimização deve ser.

Aqui está o que eu faria:

1. Carregue o banco de dados com dados de teste suficientes para imitar uma carga de produção. Você não pode obter 100% de precisão, mas tudo bem: basta colocar dados suficientes. Uma tabela tem uma quantidade fixa de dados? Carregue-o. Você tem uma tabela que contém muitos dados, por exemplo: Qualquer tabela contém perguntas sobre este site? Carregue alguns milhões de registros, mesmo que apenas dados falsos.
2. Ative a criação de perfil em seu servidor de banco de dados.
3. Atraia o aplicativo usando uma combinação de scripts automatizados (fornece volume) e usuários reais (eles sabem como quebrar as coisas).
4. Revise os dados de criação de perfil. As consultas específicas são lentas? Verifique os planos de explicação e veja se o servidor de banco de dados está lhe dizendo que quer um índice, mas ele não existe.

Servidores de banco de dados são peças de software complexas e inteligentes. Eles podem te dizer como otimizá-los se você souber ouvir.

As chaves são: medir o desempenho antes e depois da otimização e permitir que o banco de dados informe o que precisa .

Seguir padrões comprovados para problemas conhecidos (como encontrar um registro por sua identificação) não é nada prematuro. É apenas sensato.

Dito isso, os índices nem sempre são um negócio direto. Muitas vezes, é difícil saber durante a fase de design quais índices dependerão do tráfego e quais serão os gargalos nas operações de gravação. Assim, eu argumentaria por alavancar algumas boas práticas de design de esquema "óbvias" (use PKs apropriados para os padrões de leitura / gravação projetados e FKs de índice); mas, não coloque um índice em qualquer outra coisa até que seu teste de estresse exija isso.

Quando seu aplicativo é lançado, é tarde demais.

Mas qualquer processo de desenvolvimento adequado deve incluir testes de desempenho.

Use os resultados de seus testes de desempenho para decidir quais índices adicionar e verifique sua eficácia repetindo os testes de desempenho.

Embora eu não ache que toda consulta deva ser otimizada, os índices fazem parte do RDBMS e precisam ser considerados antes de serem liberados. Quando você executa uma consulta, ao contrário de outras formas de programação, você não está dizendo ao sistema como executá-lo. Eles desenvolvem planos próprios e quase sempre se baseiam na disponibilidade de um índice. A composição e o volume de dados serão considerados também em momentos posteriores.

Aqui estão algumas coisas que eu consideraria:

1. Existem algumas perguntas que você deve identificar no seu desenvolvimento inicial que você sabe que serão usadas com frequência. Concentre-se neles.
2. Haverá consultas lentas. Ao indexá-los primeiro, você pode determinar se o desempenho ainda não é rápido o suficiente e, em seguida, considerar um novo design (a desnormalização pode ser prematura). Eu prefiro fazer isso antes de um lançamento. Ninguém quer um sistema em que leva 10 minutos para encontrar algo no inventário.
3. Índices podem melhorar o desempenho da consulta, mas não impedem a modificação de dados.
4. Muitos sistemas têm ferramentas para analisar suas consultas, portanto, não tenha medo de usá-los.

Após a sua análise inicial, você deve seguir com algumas considerações para quando você deve rever isso novamente e como você vai ser capaz de coletar as informações para fazer isso (monitorar o uso, obter cópias de dados do cliente, etc .).

Eu sei que você não quer otimizar prematuramente, mas é quase certo que você terá um desempenho ruim sem indexar seu banco de dados. Ao tirar isso do caminho, você pode determinar se há outras áreas que causam problemas de desempenho.

Também depende de quantos usuários você espera. Você definitivamente deve fazer alguns testes de carga e certificar-se de que seu banco de dados pode acompanhar de 10 a 100 a 1.000 solicitações simultâneas. Novamente, isso depende de quanto tráfego você espera e de quais áreas você espera que sejam usadas mais do que outras.

Em geral, eu ajustaria as áreas que espero que o usuário acerte mais primeiro. Então eu afinar tudo o que é lento do ponto de vista da experiência do usuário. Sempre que o usuário tiver que esperar por algo, ele terá uma experiência ruim e poderá ser recusado. Não é bom!

É uma boa prática identificar quais colunas definitivamente precisam de um índice por alguma análise inicial. Existe um risco real de degradação gradual ou inesperada do desempenho na produção, pois o tamanho do banco de dados aumenta se você não tiver absolutamente nenhum índice. A situação que você deseja evitar é onde uma consulta normalmente executada requer a verificação de um grande número de linhas da tabela. Não é uma otimização prematura adicionar índices a colunas críticas, pois você tem muitas das informações necessárias disponíveis e as diferenças potenciais de desempenho são significativas (ordens de grandeza). Há também situações em que o benefício dos índices é menos claro ou mais dependente dos dados - provavelmente você pode adiar a decisão por alguns desses casos.

Algumas perguntas que você precisa fazer são:

• Quais são os limites de design para o tamanho de cada tabela?

Se as tabelas sempre forem pequenas (digamos < 100 linhas), não será um desastre se o banco de dados precisar examinar a tabela inteira. Pode ser benéfico adicionar um índice, mas isso requer um pouco mais de conhecimento ou medição para determinar.

• Com que frequência cada consulta será executada e qual é o tempo de resposta necessário?

Se a consulta for executada com pouca frequência e não tiver requisitos de tempo de resposta estritos (por exemplo, geração de relatórios) e o número de linhas não for grande, provavelmente será bastante seguro adiar o acréscimo de índices. Mais uma vez, a perícia ou a medição podem ajudar a dizer se isso será benéfico.

• A consulta requer a consulta da tabela por algo além da chave primária? Por exemplo. filtrar por intervalo de datas, juntando-se a uma chave estrangeira?

Se essas consultas forem executadas com frequência e tocarem em tabelas com muitas linhas, você deverá considerar seriamente a inclusão de um índice de forma preemptiva. Se você não tiver certeza se esse é o caso de uma consulta, poderá preencher o banco de dados com uma quantidade realista de dados e, em seguida, verificar o plano de consulta.