profuturo - Compuesto principal rendimiento clave desventaja en MySQL

fondos de ahorro para el retiro

Question

Tenemos una tabla con una clave principal compuesta que consta de tres campos (y está en MySQL 5.1). Hay cerca de 200 inserciones y 200 selecciones por segundo en esta tabla, y el tamaño de la tabla es de alrededor de 1 millón de filas y está aumentando.

Mi pregunta es: ¿la "clave principal compuesta" disminuye el rendimiento de las inserciones y selecciones en esta tabla?

¿Debería estar usando un campo de ID INT en aumento automático en lugar de una clave primaria compuesta? (Creo que la respuesta está muy relacionada con la forma en que MySQL maneja los índices en varias columnas)

Answer 1

1. Tener esa clave primaria compuesta ralentiza SELECT un poquito, aunque el efecto es bastante insignificante y no merece la pena preocuparse.
2. Tener esas columnas indexadas en todo disminuye la velocidad de tus INSERT , y ciertamente estás haciendo suficientes INSERT para preocuparte por eso. Esto es mucho más preocupante si se trata de una tabla MyISAM, donde un INSERT bloquea la tabla, que si se trata de una tabla InnoDB. Si, al ir con la clave principal auto_increment, podría dejar esas columnas sin indexar, se beneficiaría del cambio. Sin embargo, si aún necesitaría mantener esas tres columnas indexadas (por ejemplo, si necesita imponer la singularidad en la combinación de ellas), no hará nada por usted en cuanto al rendimiento.

Answer 2

Si es InnoDB, la clave primaria compuesta se incluirá en cada entrada en cada uno de los índices secundarios.

Esto significa que

• Sus índices secundarios ocuparán tanto espacio como esas columnas + todas las columnas en la clave primaria
• Puede usar un índice secundario como índice de cobertura si todas las columnas requeridas están contenidas en el índice secundario + pk

Estos son, por supuesto, una desventaja y una ventaja respectivamente.

Las claves primarias compuestas no son necesariamente malas, a veces pueden ser realmente útiles porque InnoDB las agrupa, lo que significa que las exploraciones de rango (enlazadas a disco) sobre la PK se pueden realizar con muchas menos operaciones IO de las que se requerirían en un índice no agrupado .

Por supuesto, si tiene claves externas en otras tablas, son más anchas y necesitan incluir la clave completa de la tabla principal.

Pero diría en general, no, en general. Tener una clave primaria compuesta NO causa un problema por sí mismo. Sin embargo, es posible que tenga una clave principal "grande" (por ejemplo, grandes varchars), si eso supera las ventajas de agrupar en clústeres y ser capaz de utilizar índices de cobertura.

Answer 3

INSERT rendimiento de INSERT y UPDATE varía poco: será casi igual para las teclas (INT) y (INT, INT) .

SELECT rendimiento de SELECT de la PRIMARY KEY compuesta depende de muchos factores.

Si su tabla es InnoDB , entonces la tabla está agrupada implícitamente en el valor de PRIMARY KEY .

Eso significa que las búsquedas de ambos valores serán más rápidas si ambos valores comprenden la clave: no se requerirá una búsqueda de clave adicional.

Suponiendo que su consulta es algo como esto:

SELECT *
FROM mytable
WHERE col1 = @value1
AND col2 = @value2

y el diseño de la mesa es este:

CREATE TABLE mytable (
col1 INT NOT NULL,
col2 INT NOT NULL,
data VARCHAR(200) NOT NULL,
PRIMARY KEY pk_mytable (col1, col2)
) ENGINE=InnoDB

, el motor solo necesitará buscar el valor clave exacto en la tabla en sí.

Si usa un campo de autoincremento como una identificación falsa:

CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
col1 INT NOT NULL,
col2 INT NOT NULL,
data VARCHAR(200) NOT NULL,
UNIQUE KEY ix_mytable_col1_col2 (col1, col2)
) ENGINE=InnoDB

Luego, el motor necesitará, primero, buscar los valores de (col1, col2) en el índice ix_mytable_col1_col2 , recuperar el puntero de la fila del índice (el valor de id ) y hacer otra búsqueda por id en la tabla.

Sin embargo, para las tablas MyISAM , esto no hace ninguna diferencia, porque las tablas MyISAM están organizadas en montón y el puntero de la fila es solo el desplazamiento del archivo.

En ambos casos, se creará un mismo índice (para PRIMARY KEY o para UNIQUE KEY ) y se usará de la misma manera.