sql - optimizar - No hay índices en tablas pequeñas?

optimizar consultas mysql explain (13)

"Deberíamos olvidarnos de pequeñas eficiencias, digamos el 97% de las veces: la optimización prematura es la raíz de todo mal". (Donald Knuth). Es poco probable que mis tablas SQL contengan más de unos miles de filas cada una (¡y esas son las más importantes!). SQL Server Database Engine Tuning Advisor descarta la cantidad de datos como irrelevantes. Así que ni siquiera debería pensar en poner índices explícitos en estas tablas. ¿Correcto?

Absolutamente incorrecto 100% incorrecto No coloque un millón de índices inútiles, pero sí quiere una clave principal (en la mayoría de los casos), y la quiere CLUSTERED correctamente.

Este es el por qué:

SELECT * FROM MySmallTable <-- No worries... Index won''t help SELECT * FROM MyBigTable INNER JOIN MySmallTable ON... <-- Ahh, now I''m glad I have my index.

Aquí hay una buena regla para seguir.

"Como tengo una TABLA, es probable que quiera consultarla en algún momento ... Si voy a consultarla, es probable que lo haga de forma consistente ..." <- - Así es como debes indexar la mesa.

EDITAR: Estoy agregando esta línea: si tiene un ejemplo concreto en mente, le mostraré cómo indexarlo, y qué tanto ahorro obtendrá al hacerlo. Proporcione una tabla y un ejemplo de cómo planea usar esa tabla.

Como regla general, es bueno evitar índices más pequeños, ya que normalmente no se usarán.

Pero a veces pueden proporcionar un gran impulso como lo describí here .

Debe comprender que, de acuerdo con la consulta, se pueden realizar dos búsquedas, una en el índice para obtener el puntero a la fila, la próxima a la fila misma. Si los datos que se están consultando están en las columnas de índice, ese paso adicional puede no ser necesario.

Es muy posible que la inmersión doble para los datos sea más lenta incluso si el optimizador sigue el índice. Si nos importa o no depende de los perfiles de la aplicación y los planes de explicación eventual.

Depende. ¿La mesa es una tabla de referencia?

Hay tablas de mil filas donde la ausencia de un índice y los escaneos de tabla resultantes pueden marcar la diferencia entre una operación bastante simple que retrasa al usuario en 5 minutos en lugar de 5 segundos. He visto exactamente este problema, usando un DBMS que no sea SQL Server.

En general, si la tabla es una tabla de referencia, las actualizaciones serán relativamente raras. Esto significa que el rendimiento alcanzado para actualizar el índice también será relativamente raro. Si el optimizador supera el índice, el rendimiento alcanzado en el optimizador será insignificante. El espacio necesario para almacenar el índice también será insignificante.

Si declara una clave principal, debe obtener un índice automático en esa clave. Ese índice automático casi siempre le hará suficiente para justificar su costo. Déjalo ahí. Si crea una tabla de referencia sin una clave principal, existen otros problemas en su metodología de diseño.

Si realiza búsquedas frecuentes o participaciones frecuentes en algún conjunto de columnas que no sea la clave principal, un índice adicional podría pagarse por sí mismo. No solucione ese problema a menos que sea un problema.

Aquí está la regla general: vaya con el comportamiento predeterminado del DBMS, a menos que encuentre una razón para no hacerlo. Cualquier otra cosa es una preocupación prematura por la optimización de su parte.

Incluso si tiene un índice, SQL Server ni siquiera podría usarlo, según las estadísticas de esa tabla. Y si planea incluir un índice para un informe que se ejecutará como mucho un par de veces al año, tenga en cuenta que las penalizaciones INSERT / UPDATE para agregar el índice estarán vigentes TODO EL TIEMPO. Antes de agregar un índice, pregúntese si vale la pena el rendimiento.

Las columnas de clave principal se indexarán para la restricción única. Todavía indexaría todas las columnas de clave externa. El optimizador puede elegir ignorar su índice si es irrelevante.

Si solo tiene un poco de datos, entonces el costo adicional por insertar / actualizar tampoco debería ser significativo.

Las palabras sabias de Knuth no son aplicables a la creación (o no) de índices, ya que al agregar índices no está optimizando nada directamente: está proporcionando un índice que el optimizador de DBMS puede usar para optimizar algunas consultas. De hecho, podría argumentar mejor que la decisión de no indexar una tabla pequeña es una optimización prematura, ya que al hacerlo restringe las opciones del optimizador de DBMS.

Los diferentes SGBD tendrán diferentes pautas para elegir si se deben indexar las columnas en función de varios factores, incluido el tamaño de la tabla, y deben tenerse en cuenta.

¿Qué es un ejemplo de optimización prematura en las bases de datos: "desnormalización para el rendimiento" antes de que cualquier evaluación comparativa haya indicado que la base de datos normalizada en realidad tiene algún problema de rendimiento.

Los índices a menudo se crean implícitamente cuando se usan restricciones ÚNICAS. ¡No trataría de evitar su uso en ese caso!

Pon los índices SOLO si tienes que :)
Hay momentos en que poner índices puede dañar el rendimiento, dependiendo de para qué se usa la tabla ...
Por lo tanto, en otras palabras, pensaría en poner índices en las tablas cuando sea necesario según lo determine el perfil de la aplicación.

Si las filas tienen un ancho estrecho y caben unas mil filas en las páginas de 10-20 8K, es poco probable que el optimizador de SQL elija usar un índice incluso si crea uno.

Sugiero que siga las reglas habituales sobre indexación, lo que significa, aproximadamente, "crear índices en esas columnas que usa en sus consultas".

Esto puede parecer innecesario con una base de datos tan pequeña. Como ya han dicho otros: siempre y cuando su base de datos se mantenga tan pequeña como ha descrito, las consultas serán lo suficientemente rápidas de todos modos, y los índices no son realmente necesarios. Incluso pueden ralentizar las inserciones y actualizaciones, pero a menos que tenga requisitos muy específicos allí, no importa con una base de datos tan pequeña.

Pero , si la base de datos crece (lo que a veces tienden a hacer las bases de datos), no tiene que acordarse de agregar índices a esa antigua base de datos que probablemente haya olvidado para entonces. Tal vez incluso se haya instalado en uno de sus clientes, ¡y no puede modificarlo!

Creo que lo que estoy diciendo es esto: los índices deberían ser una parte tan natural del diseño de su base de datos, que la falta de índices es la optimización, prematura o no.

Supongo que hay una indexación automática en la clave principal de la tabla que debería ser suficiente cuando se consulta en una tabla con menos datos.

Por lo tanto, sí se pueden evitar los índices explícitos en caso de que haya un pequeño conjunto de datos para trabajar.

El valor de los índices está en las lecturas de exceso de velocidad. Por ejemplo, si está haciendo muchos SELECT basados en un rango de fechas en una columna de fecha, tiene sentido poner un índice en esa columna. Y, por supuesto, generalmente agrega índices en cualquier columna en la que se unirá con una frecuencia significativa. La ganancia de eficiencia también se relaciona con la relación entre el tamaño de los conjuntos de registros típicos y el número de registros (es decir, si se toman 20/2000 registros se beneficia más de la indexación que de los registros de 90/100). Una búsqueda en una columna no indexada es esencialmente una búsqueda lineal.

El costo de los índices entra en escrituras, porque cada INSERT también requiere una inserción interna para cada índice de columna.

Entonces, la respuesta depende completamente de su aplicación: si es algo así como un sitio web dinámico donde el número de lecturas puede ser 100x o 1000x las escrituras, y está haciendo búsquedas frecuentes y dispares basadas en columnas de datos, la indexación puede ser beneficiosa. . Pero si las escrituras superan con creces las lecturas, entonces su sintonía debe centrarse en acelerar esas consultas.

Toma muy poco tiempo identificar y comparar algunas de las operaciones más frecuentes de su aplicación, con y sin índices en las columnas JOIN / WHERE, le sugiero que haga eso. También es inteligente supervisar su aplicación de producción e identificar las consultas más caras y más frecuentes, y enfocar sus esfuerzos de optimización en la intersección de esos dos conjuntos de consultas (lo que podría significar índices o algo totalmente diferente, como asignar más o menos memoria para consultar o unir cachés).