Indexar i claves furanas

[xinxan22]

Buenos días,
tengo una duda en tablas InnoDB. Tengo dos tablas relacionadas entre ellas por una clave furana. También me gustaría tener indexado el campo de la clave furana. Hace falta que lo indexe o haciendo clave furana ya se indexa?
(Quiero indexarlo para que las busquedas de ese "id" sean más rápidas, quiero relacionarlo con la otra tabla para que pueda mostrar campos de la otra tabla de manera mucho más rápida, entre otras cosas)
Saludos.

[gnzsoloyo]

Una clave foránea, por definición, genera un índice, pero ese índice tiene como único objetivo mantener la integridad referencial. No se usa como índice de búsquedas.
Por otro lado, no existe un impedimento para indexar en una tabla un campo que sea FK. En ese sentido, los índices son entidades flexibles que pueden crearse según la conveniencia del usuario.
Lo que no es buena idea es recargar una tabla de gran carga de inserciones/hora con demasiados índices, porque producirás una reducción de la performance de las mismas. Es preferible indexar tablas de información consolidada (las tablas de hechos de OLAP, por ejemplo).

[xinxan22]

Iniciado por gnzsoloyo

Lo que no es buena idea es recargar una tabla de gran carga de inserciones/hora con demasiados índices, porque producirás una reducción de la performance de las mismas. Es preferible indexar tablas de información consolidada (las tablas de hechos de OLAP, por ejemplo).

No entiendo esto. A que te refieres?

Saludos y gracias.

[gnzsoloyo]

Los índices se actualizan con cada insert. En un caso el índice establece el ordenamiento físico de los registros (PK), en otros establecen un orden definido alternativo, pero no físico sino lógico.
Como los ordenamientos deben estar disponibles tan rápido como el registro se terminó de insertar; si estás metiendo 600 registros por hora, los índices se están actualizando 1 vez cada 6 segundos. Esto significa uso de memoria adicional para mantener los índices no primarios, que luego se vuelcan al disco.
Ahora bien imagínate esto:
Si una clave abarca tres campos BIGINT o DOUBLE, estás usando 12 bytes + longitud usada por la página de índice. Supongamos que usas 32 bytes por entrada y estás actualizando una tabla con 1.000.000 registros. Son como mínimo 32 Mb usados por el sistema en mantener el índice que se está actualizando. Considerando que un bloque de índice usa 64 K, se requieren 500 bloques de datos para mantener el índice, a lo que le debes sumar las tablas de páginas, el overhead en el procesador, el swapping al disco y un montón de cosas más...
Y esto, por cada uno de los índices definidos, con el agregado que estoy poniendo un ejemplo bastante barato (una KEY basada en tres campos numéricos)
Resumiendo: Operar con demasiados índices en una tabla que se recibe muchas inserciones/hora, trae problemas de performance.

[xinxan22]

Creo que voy por mal camino entonces. Tengo una tabla que recibe entre uno y tres INSERT por segundo.

Esta tabla tiene unos 10 campos.
Un campo id_xxx que es clave forana de otra tabla.
Un campo "fecha" y "string" que están indexados para poder hacer las consultas más rápidas.

La memoria que utiliza es la Memoria RAM del servidor ?

Saludos.

[gnzsoloyo]

El problema te va a aparecer a medida que la tabla vaya creciendo. Después de una cantidad de registros.
Si tienes una tabla con demasiadas transacciones/hora (lo que planteas roza las 10.800, puede no ser demasiado, habría que ver la longitud del registro y la capacidad del hardware), puede ser conveniente independizar la tabla de consultas de la tabla de transacciones. E
Esto implicaría en alguna medida crear una tabla espejo que se actualice dos o tres veces al día, en la cual puedas definir los índices necesarios. Como los volcados de datos de tabla a tabla (las cargas masivas) son inmensamente más rápidas que las inserciones, eso podría resolver parte del problema, además de asegurar el nivel de aislamiento/lectura de los datos.
Puede incluso analizarse en ese contexto la posibilidad de en lugar de hacer una tabla espejo, crear tablas con información resumida tomada de la misma (ver OLAP) que de datos ya consolidados.