database - source - oracle data modeler tutorial español
¿Cuántos índices de bases de datos son demasiados? (17)
¿Cuántas columnas hay? Siempre me han dicho que haga índices de una sola columna, no índices de varias columnas. Así que no hay más índices que la cantidad de columnas, en mi humilde opinión.
Estoy trabajando en un proyecto con una base de datos Oracle bastante grande (aunque mi pregunta se aplica igualmente a otras bases de datos). Tenemos una interfaz web que permite a los usuarios buscar casi cualquier combinación de campos.
Para que estas búsquedas sean más rápidas, agregamos índices a los campos y combinaciones de campos en los que creemos que los usuarios buscarán habitualmente. Sin embargo, dado que no sabemos realmente cómo utilizarán este software nuestros clientes, es difícil saber qué índices crear.
El espacio no es una preocupación; tenemos un disco RAID de 4 terabytes del cual estamos usando solo una pequeña fracción. Sin embargo, me preocupan las posibles penalizaciones de rendimiento por tener demasiados índices. Debido a que esos índices deben actualizarse cada vez que se agrega, elimina o modifica una fila, me imagino que sería una mala idea tener docenas de índices en una sola tabla.
Entonces, ¿cuántos índices se consideran demasiados? 10? 25? 50? ¿O debería cubrir los casos realmente comunes y obvios e ignorar todo lo demás?
Además de los puntos que todos los demás han planteado, el optimizador basado en el costo incurre en un costo al crear un plan para una declaración SQL si hay más índices porque hay más combinaciones que debe considerar. Puede reducir esto utilizando correctamente variables de vinculación para que las declaraciones SQL permanezcan en la memoria caché de SQL. Oracle puede hacer un análisis suave y volver a usar el plan que encontró la última vez.
Como siempre, nada es simple. Si hay columnas asimétricas e histogramas involucrados, puede ser una mala idea.
En nuestras aplicaciones web, tendemos a limitar las combinaciones de búsquedas que permitimos. De lo contrario, tendría que probar literalmente cada combinación de rendimiento para asegurarse de que no tenía un problema al acecho que alguien encontraría algún día. También hemos implementado límites de recursos para evitar que esto cause problemas en otras partes de la aplicación si algo sale mal.
Depende de las operaciones que ocurren en la mesa.
Si hay muchos SELECT y muy pocos cambios, indexe todo lo que quiera ... esto (potencialmente) acelerará las declaraciones SELECT.
Si la tabla recibe un gran impacto de UPDATE, INSERTs + DELETE ... estos serán muy lentos con muchos índices, ya que todos deben modificarse cada vez que se lleva a cabo una de estas operaciones.
Una vez dicho esto, puede agregar claramente una gran cantidad de índices sin sentido a una tabla que no hará nada. Agregar índices B-Tree a una columna con 2 valores distintos no tendrá sentido ya que no agrega nada en términos de buscar los datos. Cuanto más únicos sean los valores en una columna, más se beneficiará de un índice.
El servidor Sql le brinda algunas buenas herramientas que le permiten ver qué índices se están usando realmente. Este artículo, http://www.mssqltips.com/tip.asp?tip=1239 , le ofrece algunas consultas que le permiten obtener una mejor idea de cuánto se usa un índice, en comparación con la cantidad que se actualiza.
En última instancia, la cantidad de índices que necesita depende del comportamiento de sus aplicaciones que se encuentran en la parte superior de su servidor de base de datos.
En general, cuanto más inserciones hagas, más dolorosos serán tus índices. Cada vez que hace una inserción, todos los índices que la incluyen deben actualizarse.
Ahora, si su aplicación tiene una cantidad decente de lectura, o incluso más si es casi todo lectura, entonces los índices son el camino a seguir, ya que habrá mejoras de rendimiento importantes a muy bajo costo.
En el almacenamiento de datos, es muy común tener una gran cantidad de índices. He trabajado con tablas de hechos que tienen doscientas columnas y 190 de ellas indexadas.
Aunque hay una sobrecarga para esto, debe entenderse en el contexto que en un almacén de datos generalmente solo insertamos una fila una vez, nunca la actualizamos, pero luego puede participar en miles de consultas SELECT que podrían beneficiarse de la indexación en cualquiera de las columnas.
Para una flexibilidad máxima, un depósito de datos generalmente utiliza índices de mapa de bits de una sola columna, excepto en columnas de cardinalidad alta, donde se pueden usar índices btree (comprimidos).
La sobrecarga en el mantenimiento del índice se asocia principalmente con el gasto de escribir en un gran número de bloques y el bloque se divide a medida que se agregan nuevas filas con valores que están "en el medio" de los rangos de valores existentes para esa columna. Esto puede mitigarse mediante la partición y teniendo las nuevas cargas de datos alineadas con el esquema de particionamiento, y mediante el uso de insertos de ruta directos.
Para abordar su pregunta de manera más directa, creo que probablemente sea correcto indexar lo obvio al principio, pero no tema agregar más índices si las consultas en la tabla se beneficiarían.
En una paráfrasis de Einstein sobre simplicidad, agregue tantos índices como necesite y nada más.
En serio, sin embargo, cada índice que agregue requiere mantenimiento siempre que se agreguen datos a la tabla. En tablas que son principalmente de solo lectura, muchos de los índices son algo bueno. En las tablas que son altamente dinámicas, menos es mejor.
Mi consejo es cubrir los casos comunes y obvios y luego, a medida que encuentre problemas en los que necesite más velocidad para obtener datos de tablas específicas, evalúe y agregue índices en ese punto.
Además, es una buena idea volver a evaluar sus esquemas de indexación cada pocos meses, solo para ver si hay algo nuevo que necesite indexación o cualquier índice que haya creado que no se esté utilizando para nada y que deba eliminarse. .
Está totalmente basado en las columnas que se utilizan en Where Clause. Y como pulgar de regla, debemos tener índices en columnas de clave externa para evitar DEADLOCKS. El informe de AWR debe analizarse periódicamente para comprender la necesidad de los índices.
Esta es realmente una pregunta más teórica que práctica. Los índices impactan en su desempeño depende del hardware que tiene, la versión de Oracle, los tipos de índice, etc. Ayer escuché que Oracle anunció un almacenamiento dedicado, hecho por HP, que se supone que debe funcionar 10 veces más rápido con 11g de base de datos. En cuanto a su caso, puede haber varias soluciones: 1. Tener una gran cantidad de índices (> 20) y reconstruirlos diariamente (cada noche). Esto sería especialmente útil si la tabla recibe miles de actualizaciones / eliminaciones diariamente. 2. Divida su tabla (si eso aplica su modelo de datos). 3. Use una tabla separada para datos nuevos / actualizados y ejecute un proceso nocturno que combine los datos. Esto requeriría un cambio en la lógica de su aplicación. 4. Cambie a IOT (índice de la tabla organizada), si sus datos lo admiten.
Por supuesto, puede haber muchas más soluciones para ese caso. Mi primera sugerencia para usted sería clonar el DB en un entorno de desarrollo y ejecutar algunas pruebas de estrés en su contra.
Hice algunas pruebas simples en mi proyecto real y base de datos real MySql. Ya respondí en este tema: ¿Cuál es el costo de indexar múltiples columnas db?
Pero creo que será mejor si lo cito aquí:
Hice algunas pruebas simples usando mi proyecto real y una base de datos MySql real.
Mis resultados son: agregar un índice promedio (1-3 columnas en un índice) a una tabla, hace que las inserciones sean más lentas en un 2.1%. Entonces, si agrega 20 índices, sus insertos serán más lentos en un 40-50%. Pero tus selecciones serán 10-100 veces más rápidas.
Entonces, ¿está bien agregar muchos índices? - Depende :) Te di mis resultados - ¡Tú decides!
Lo que realmente se reduce a esto es no agregar un índice a menos que sepa (y esto a menudo significa recopilar estadísticas de uso) que se usará con mucha más frecuencia de lo que se actualiza.
Cualquier índice que no cumpla con ese criterio le costará más reconstruir que la penalización de rendimiento por no tenerlo en el extraño caso en que se usó.
No hay una respuesta estática en mi opinión, este tipo de cosas cae bajo ''ajuste de rendimiento''.
Podría ser que todo lo que hace su aplicación es buscado por una clave principal, o podría ser el opuesto en que las consultas se realizan sobre combinaciones de campos no restringidos y cualquiera en particular podría usarse en cualquier momento dado.
Más allá de la indexación, está regrabando su base de datos para incluir campos de búsqueda calculados, tablas de división, etc. Depende realmente de las formas de carga y los parámetros de consulta, cuánto / qué datos ''realmente'' necesitan ser recuperados por una consulta.
Si toda su base de datos está encabezada por fachadas de procedimientos almacenados, el cambio se vuelve un poco más fácil, ya que no tiene que preocuparse por cada consulta ad-hoc. O puede tener una comprensión profunda del tipo de consultas que afectarán a su base de datos, y puede limitar el ajuste a las mismas.
Para SQL Server he encontrado útil el asesor de ajuste del Motor de base de datos: configura cargas de trabajo ''típicas'' y puede hacer recomendaciones sobre cómo agregar / eliminar índices y estadísticas. Estoy seguro de que otros DB tienen herramientas similares, ya sean ''oficiales'' o de terceros.
Por lo general, procedo de esta manera.
- Obtenga un registro de las consultas reales ejecutadas en los datos en un día típico.
- Agregue índices para que las consultas más importantes lleguen a los índices en su plan de ejecución.
- Intenta evitar indexar los campos que tienen muchas actualizaciones o insertos
- Después de algunos índices, obtenga un nuevo registro y repita.
Como con cualquier optimización, detengo cuando se alcanza el rendimiento solicitado (esto obviamente implica que el punto 0. obtendría requisitos de rendimiento específicos).
Si lee en su mayoría (y algunas actualizaciones), entonces realmente no hay razón para no indexar todo lo que necesitará para indexar. Si actualiza con frecuencia, es posible que deba tener cuidado con la cantidad de índices que tiene. No hay un número difícil, pero notarás cuando las cosas empiecen a ralentizarse. Asegúrese de que su índice agrupado sea el que tenga más sentido según los datos.
Todos los demás te han estado dando buenos consejos. Tengo una sugerencia adicional para ti a medida que avanzas. En algún momento, debe tomar una decisión sobre su mejor estrategia de indexación. Sin embargo, al final, la mejor estrategia de indexación PLANIFICADA puede terminar creando índices que no terminan usándose. Una estrategia que le permite encontrar índices que no se utilizan es monitorear el uso del índice. Usted hace esto de la siguiente manera:
alter index my_index_name monitoring usage;
A continuación, puede controlar si el índice se utiliza o no a partir de ese punto en adelante al consultar v $ object_usage. La información sobre esto se puede encontrar en la Guía del administrador de la base de datos Oracle® .
Solo recuerde que si tiene una estrategia de almacenamiento de caída de índices antes de actualizar una tabla, luego recreándolos, tendrá que configurar el índice nuevamente para monitoreo, y perderá cualquier historial de monitoreo para ese índice.
Un índice impone un costo cuando la tabla subyacente se actualiza. Un índice proporciona un beneficio cuando se usa para generar una consulta. Para cada índice, debe equilibrar el costo con el beneficio. ¿Cuánto más lento se ejecuta la consulta sin el índice? ¿Cuánto de un beneficio se está ejecutando más rápido? ¿Pueden usted o sus usuarios tolerar la velocidad lenta cuando falta el índice?
¿Puedes tolerar el tiempo adicional que lleva completar una actualización?
Necesita comparar costos y beneficios. Eso es particular a tu situación. No hay un número mágico de índices que supere el umbral de "demasiados".
También está el costo del espacio necesario para almacenar el índice, pero has dicho que en tu situación eso no es un problema. Lo mismo es cierto en la mayoría de las situaciones, dado lo barato que se ha convertido el espacio en disco.
Una cosa que puede considerar es crear índices para enfocarse en una combinación estándar de búsquedas. Si se busca con frecuencia column1, y column2 a menudo se usa con él, y column3 a veces se usa con column2 y column1, entonces un índice en column1, column2 y column3 en ese orden se puede usar para cualquiera de esas tres circunstancias, aunque es solo un índice que debe mantenerse.