¿Es una optimización prematura agregar índices de base de datos?

La optimización prematura es "optimizar" algo debido a una vaga e intuitiva sensación de que, probablemente, esto será lento, especialmente en detrimento de la legibilidad y el mantenimiento del código . No significa voluntariamente no seguir buenas prácticas bien establecidas con respecto al rendimiento.

A veces, es una línea difícil de dibujar, pero definitivamente diría que no agregar ningún índice antes de que empiece a funcionar es una optimización demasiado tardía ; esto castigará a los adoptadores tempranos, sus usuarios más entusiastas e importantes, y les dará una visión negativa de su producto, que luego se difundirá en revisiones, discusiones, etc. buena idea, pero me aseguraría de hacerlo a más tardar en la versión beta.

  

monitorear las consultas lentas una vez que estemos en funcionamiento

¡porque nada dice calidad como hacer sufrir a tus usuarios por falta de diseño!

Debería saber qué consultas necesitan índices al diseñar las tablas, sabe qué columnas se están consultando en las cláusulas de dónde y las uniones. Estos ya deberían estar indexados porque lo que podría no ser evidente en un entorno en vivo puede hacerse evidente rápidamente cuando aumenta la carga o los datos almacenados. Lo que no quiere hacer cuando esto sucede es abofetear índices en cada consulta 'lenta', y terminará con un índice en todo.

"Optimización prematura", en su sentido despectivo, significa optimización costosa que podría no ser necesaria. ¡ no significa toda la optimización implementada antes del último punto posible para prevenir la quiebra!

En particular, es legítimo optimizar en función de las pruebas de rendimiento antes de su puesta en marcha, para garantizar que pueda cumplir con algunos requisitos razonables (aunque aproximados) para que su aplicación no apague por completo.

Como mínimo absoluto , debe cargar su base de datos con una cantidad plausible de datos de prueba y verificar la capacidad de respuesta de su aplicación. Esto no es prematuro, ya que sabes que va a suceder, y detectará cualquier consulta que genere escaneos absurdamente lentos. Como dice A E en un comentario:

  

Utilice índices para evitar un análisis completo de la tabla para cualquier consulta que   El usuario final lo hará comúnmente en tiempo real

Al menos, para las tablas que están planeadas para crecer en uso.

Luego, como un atajo a eso, si tiene una experiencia significativa con el motor de base de datos y ya planificó las pruebas cuando escribe el primer corte del código, a menudo sabrá, sin siquiera ejecutarlo, que la consulta Estás escribiendo será muy lento sin un índice. Por supuesto, es libre de fingir que no sabe, y ver cómo falla la prueba antes de agregar el índice para hacerlo pasar, pero no hay ninguna razón para que el código defectuoso conocido (porque no responde) se active.

  

Siento que esta es una optimización prematura porque nuestra aplicación aún no está lanzada. Sugerí monitorear las consultas lentas una vez que estemos en funcionamiento y luego agregar índices en consecuencia.

No puede tratar a sus usuarios finales y al entorno de producción como garantía de calidad. En otras palabras, estás diciendo que lo resolverás en producción. No creo que esa sea la forma correcta, y veo que el enfoque va terriblemente mal todos los días .

Debes tener en cuenta una cosa, ya que no puedes pintar esto con un pincel ancho.

¿Cuál es su carga de trabajo común ?

Eso puede sonar obvio o aburrido, pero es significativo en la práctica. Si tiene 10 consultas que conforman el 98% de su carga de trabajo (es bastante común, lo creas o no), mi recomendación sería un análisis exhaustivo antes de la producción . Con datos realistas y representativos, asegúrese de que esas 10 consultas sean lo mejor que puedan ser ( perfecto es una pérdida de tiempo valioso y casi no se puede lograr).

Para las otras 200 consultas que conforman el 2% de la carga de trabajo , esas son las que probablemente no valen la pena, y formarán parte de la solución de problemas de perfección. Las rarezas en la producción. Eso también es una realidad, y no es una cosa terriblemente mala. Pero eso no significa ignorar las mejores prácticas de indexación o hacer suposiciones estimadas sobre la recuperación de datos.

Es una práctica común y buena averiguar el rendimiento de la base de datos antes de la producción. De hecho, hay una posición relativamente común para este tipo de cosa llamada a desarrollo DBA .

But...

Algunos lo llevan demasiado lejos y se vuelven locos agregando índices "por si acaso". ¿Alguien recomienda que este sea un índice faltante? Agrégalo, y otras cuatro variaciones. También es una mala idea. No solo debe pensar en su recuperación de datos, sino también en la modificación de los datos. Cuantos más índices tenga en una tabla, en general, mayor será la sobrecarga que tendrá cuando modifique los datos.

Como la mayoría de las cosas, hay un equilibrio saludable.

Como una pequeña nota divertida ... La pluralización de "Index"

"Los índices" son para personas financieras

"Los índices" son para nosotros

No, no es una optimización prematura, pero debe hacerse correctamente como debería ser cualquier optimización.

Esto es lo que haría:

1. Cargue la base de datos con suficientes datos de prueba para imitar una carga de producción. No puede obtener este 100% de precisión, pero está bien: solo ingrese suficientes datos. ¿Una tabla tiene una cantidad fija de datos? Cárgalo. ¿Tiene una tabla que contiene una gran cantidad de datos, por ejemplo, ¿Qué tabla tiene preguntas en este sitio? Cargue unos pocos millones de registros, incluso si solo se trata de datos ficticios.
2. Active la generación de perfiles en su servidor de base de datos.
3. Golpee la aplicación con una combinación de scripts automatizados (proporciona volumen) y usuarios reales (saben cómo romper las cosas).
4. Revise los datos del perfil. ¿Las consultas específicas son lentas? Verifique los planes de explicación y vea si el servidor de la base de datos le está diciendo que quiere un índice pero no existe.

Los servidores de bases de datos son piezas de software complejas e inteligentes. Pueden indicarle cómo optimizarlos si sabe cómo escuchar.

Las claves son medir el rendimiento antes y después de la optimización y dejar que la base de datos le diga lo que necesita .

Seguir patrones probados para problemas conocidos (como encontrar un registro por su ID) no es nada prematuro. Es sensato.

Dicho esto, los índices no siempre son un negocio sencillo. A menudo es difícil saber durante la fase de diseño de qué índices dependerá el tráfico y cuáles serán las operaciones de escritura de cuellos de botella. Por lo tanto, abogaría por aprovechar algunas de las mejores prácticas de diseño de esquemas "obvias" (use los PK adecuados para los patrones de lectura / escritura diseñados e índices de FK); pero, no ponga un índice en nada más hasta que sus pruebas de estrés lo exijan.

Cuando se lanza su aplicación, es demasiado tarde.

Pero cualquier proceso de desarrollo adecuado debe incluir pruebas de rendimiento.

Use los resultados de sus pruebas de rendimiento para decidir qué índices agregar y verifique su efectividad repitiendo las pruebas de rendimiento.

Aunque no creo que todas las consultas deban optimizarse, los índices son una parte tan importante de RDBMS que deben tenerse en cuenta antes de lanzarlos. Cuando ejecuta una consulta, a diferencia de otras formas de programación, no le está diciendo al sistema cómo ejecutarlo. Desarrollan planes propios y casi siempre se basan en la disponibilidad de un índice. La composición y el volumen de los datos también se considerarán más adelante.

Aquí hay algunas cosas que consideraría:

1. Hay algunas consultas que debe identificar en su desarrollo temprano que sabe que se usarán con frecuencia. Enfócate en ellos.
2. Habrá consultas lentas. Al indexarlos primero, luego puede determinar si el rendimiento aún no es lo suficientemente rápido y luego considerar un rediseño (la desnormalización puede ser prematura). Prefiero hacer esto antes de un lanzamiento. Nadie quiere un sistema en el que se necesiten 10 minutos para encontrar algo en el inventario.
3. Los índices pueden mejorar el rendimiento de las consultas, pero no obstaculizan la modificación de los datos.
4. Muchos sistemas tienen herramientas para analizar sus consultas, así que no tenga miedo de usarlas.

Después de su revisión inicial, debe continuar con algunas consideraciones sobre cuándo debe revisar esto nuevamente y cómo podrá recopilar la información para hacerlo (controlar el uso, obtener copias de los datos del cliente, etc.) .).

Me doy cuenta de que no desea optimizar de manera prematura, pero es casi seguro que tendrá un bajo rendimiento sin indexar su base de datos. Al eliminar esto, puede determinar si hay otras áreas que causan problemas de rendimiento.

También depende de cuántos usuarios esperas. Definitivamente, debería realizar algunas pruebas de carga y asegurarse de que su base de datos pueda mantenerse al día con 10 a 100 a 1000 solicitudes simultáneas. Una vez más, depende de la cantidad de tráfico que espera y de las áreas que espera que se usen más que otras.

En general, me gustaría afinar las áreas en las que espero que el usuario golpee más primero. Luego ajustaría cualquier cosa que fuera lenta desde el punto de vista de la experiencia del usuario. Cuando el usuario tiene que esperar por algo, obtiene una mala experiencia y puede ser rechazado. ¡No es bueno!

Es una buena práctica identificar qué columnas definitivamente necesitan un índice por algún análisis inicial. Existe un riesgo real de degradación gradual o inesperada del rendimiento en la producción a medida que aumenta el tamaño de la base de datos si no tiene ningún índice. La situación que desea evitar es cuando una consulta comúnmente ejecutada requiere escanear un gran número de filas de tablas. No es una optimización prematura agregar índices a las columnas críticas, ya que tiene mucha de la información necesaria disponible y las posibles diferencias de rendimiento son significativas (órdenes de magnitud). También hay situaciones en las que el beneficio de los índices es menos claro o más dependiente de los datos; es probable que pueda postergar la decisión en algunos de estos casos.

Algunas preguntas que debes hacer son:

• ¿Cuáles serán los límites de diseño para el tamaño de cada tabla?

Si las tablas siempre van a ser pequeñas (por ejemplo, < 100 filas), no es un desastre si la base de datos tiene que escanear toda la tabla. Puede ser beneficioso agregar un índice, pero esto requiere un poco más de experiencia o medición para determinar.

• ¿Con qué frecuencia se ejecutará cada consulta y cuál es el tiempo de respuesta requerido?

Si la consulta se ejecuta con poca frecuencia y no tiene requisitos estrictos de tiempo de respuesta (por ejemplo, generación de informes) y el número de filas no es muy grande, es probable que sea bastante seguro diferir los índices de adición. Nuevamente, la experiencia o la medición pueden ayudar a determinar si será beneficioso.

• ¿La consulta requiere buscar en la tabla algo aparte de la clave principal? P.ej. filtrando por intervalo de fechas, uniendo una clave externa?

Si estas consultas se ejecutan con frecuencia y tocan tablas con muchas filas, debería considerar seriamente agregar un índice de manera preventiva. Si no está seguro de que este sea el caso de una consulta, puede llenar la base de datos con una cantidad realista de datos y luego mirar el plan de consulta.