database - soporta - ¿Cómo sabes cuál es un buen índice?
optimizar consultas mysql explain (6)
Cuando trabajas con tablas en Oracle, ¿cómo sabes cuándo estás configurando un buen índice en lugar de un índice malo?
Aquí hay un gran artículo de SQL Server: http://www.sql-server-performance.com/tips/optimizing_indexes_general_p1.aspx
Aunque las mecánicas no funcionarán en Oracle, las sugerencias son muy oportunas (sin tener en cuenta los índices agrupados, que no funcionan del mismo modo en Oracle).
Esto depende de lo que quiere decir con "bueno" y "malo". Básicamente debe darse cuenta de que cada índice que agregue aumentará el rendimiento en cualquier búsqueda por esa columna (por lo que agregar un índice a la columna ''apellido'' de una tabla persona aumentará el rendimiento en las consultas que tienen "donde apellido =") pero disminuir el rendimiento de escritura en toda la tabla.
La razón para esto es cuando agrega o actualiza una fila, debe agregar o actualizar tanto la tabla como todos los índices de los que es miembro la fila. Entonces, si tiene cinco índices en una tabla, cada adición debe escribir en seis lugares, cinco índices y la tabla, y una actualización puede estar tocando hasta seis lugares en el peor de los casos.
La creación del índice es un acto de equilibrio, luego entre la velocidad de consulta y la velocidad de escritura. En algunos casos, como una datamart que solo está cargada con datos una vez a la semana en un trabajo nocturno pero que se consulta miles de veces al día, tiene mucho sentido sobrecargar los índices y acelerar las consultas tanto como sea posible. Sin embargo, en el caso de los sistemas de procesamiento de transacciones en línea, debe intentar encontrar un equilibrio entre ellos.
En resumen, agregue índices a las columnas que se usan mucho en las consultas de selección, pero trate de evitar agregar demasiadas y, por lo tanto, agregue primero las columnas más utilizadas.
Después de eso, es una cuestión de pruebas de carga para ver cómo reacciona el rendimiento en condiciones de producción, y un montón de ajustes para encontrar un equilibrio aceptable.
Los campos que son diversos, altamente específicos o únicos hacen buenos índices. Tales como fechas y marcas de tiempo, números incrementales únicos (comúnmente utilizados como claves principales), nombres de personas, números de matrícula, etc.
Un contraejemplo sería el género: solo hay dos valores comunes, por lo que el índice en realidad no ayuda a reducir el número de filas que deben escanearse.
Las cadenas de forma libre descriptivas de longitud completa generan índices pobres, ya que quien realiza la consulta rara vez conoce el valor exacto de la cadena.
Los datos ordenados linealmente (como marcas de tiempo o fechas) se usan comúnmente como índice agrupado, lo que obliga a las filas a almacenarse en orden de índice y permite el acceso ordenado, acelerando en gran medida las consultas de rango (p. Ej., "Darme todas las órdenes de venta" entre octubre y diciembre ''). En tal caso, el motor DB puede simplemente buscar el primer registro especificado por el rango y comenzar a leer secuencialmente hasta que llegue al último.
Un buen índice es algo en lo que puede confiar para ser único para una fila de tabla específica.
Un esquema de índice comúnmente utilizado es el uso de números que se incrementan en 1 por cada fila en la tabla. Cada fila terminará teniendo un índice numérico diferente.
Algunas reglas generales si intenta mejorar una consulta en particular.
Para una tabla particular (donde crees que Oracle debería comenzar) intente indexar cada una de las columnas utilizadas en la cláusula WHERE. Primero coloque columnas con igualdad, seguidas de columnas con un rango o similar.
Por ejemplo:
WHERE CompanyCode = ? AND Amount BETWEEN 100 AND 200
Si las columnas son de gran tamaño (por ejemplo, está almacenando algo de XML o algo así), es mejor que las deje fuera del índice. Esto hará que el índice sea más pequeño para escanear, suponiendo que debe ir a la fila de la tabla para cumplir con la lista de selección de todos modos.
Alternativamente, si todos los valores en las cláusulas SELECT y WHERE están en el índice, Oracle no necesitará acceder a la fila de la tabla. Por lo tanto, a veces es una buena idea poner los valores seleccionados al final en el índice y evitar que todos tengan acceso a la tabla.
Podría escribir un libro sobre las mejores formas de indexar: busque al autor Jonathan Lewis.
@Infamous Cow: debes pensar en las claves primarias, no en los índices.
@Xenph Yan - Algo que otros no han tocado es elegir qué tipo de índice crear. Algunas bases de datos en realidad no le dan muchas opciones, pero algunas tienen una gran variedad de índices posibles. Los árboles B son el valor predeterminado pero no siempre el mejor tipo de índice. Elegir la estructura correcta depende del tipo de uso que esperas tener. ¿Qué tipo de consultas necesita para apoyar más? ¿Estás en un entorno de lectura mayormente o mayor escritura? ¿Sus escrituras están dominadas por actualizaciones o anexos? Etcétera etcétera.
Una descripción de los diferentes tipos de índices y sus pros y contras está disponible aquí: http://20bits.com/2008/05/13/interview-questions-database-indexes/ .