Desventaja de rendimiento de clave primaria compuesta en MySQL

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 primaria compuesta” disminuye el rendimiento de los insertos y selecciona en esta tabla?

¿Debo usar un campo de identificación INT de Aumento automático simple en lugar de una clave primaria compuesta? (Creo que la respuesta está muy relacionada con la forma en que MySQL maneja los índices en múltiples columnas)

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

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

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

Esto significa que las búsquedas de ambos valores serán más rápidas si ambos valores incluyen la clave: no se requerirá 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 tabla 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 tendrá que buscar el valor clave exacto en la tabla en sí.

Si usa un campo de autoincrement 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 

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

Para las tablas MyISAM , sin embargo, esto no hace diferencia, porque las tablas MyISAM están organizadas en un montón y el puntero de fila es solo un desplazamiento de archivo.

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

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 principal
  • 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 (en disco) sobre la PK pueden satisfacerse utilizando muchas menos operaciones de IO que las requeridas en un índice no agrupado .

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

Pero yo diría que, en general, en general, no. Tener una clave primaria compuesta NO causa un problema en sí mismo. Sin embargo, tener una clave principal “grande” (por ejemplo, varchar grande) puede hacer, si eso supera las ventajas de agrupar y poder usar índices de cobertura.

  1. Tener esa clave primaria compuesta se ralentiza SELECT un poquito, aunque el efecto es bastante insignificante y no vale la pena preocuparse.
  2. Tener esas columnas indexadas ralentiza tus INSERT s, y ciertamente estás haciendo INSERT s lo suficiente como para preocuparte por ello. 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, yendo con la clave primaria auto_increment, pudieras dejar esas columnas sin indexar, te beneficiarías del cambio. Sin embargo, si aún necesita mantener esas tres columnas indexadas (por ejemplo, si necesita imponer la singularidad en la combinación de ellas), no le hará ningún cambio en el rendimiento.
    Intereting Posts