¿Hay una diferencia real de rendimiento entre las claves primarias INT y VARCHAR?

No se trata de rendimiento. Se trata de lo que hace una buena clave primaria. Único e inmutable en el tiempo. Puede pensar que una entidad como un código de país nunca cambia con el tiempo y sería un buen candidato para una clave primaria. Pero la amarga experiencia es que rara vez es así.

INT AUTO_INCREMENT cumple con la condición "único e inmutable en el tiempo". De ahí la preferencia.

Depende de la longitud.. Si el varchar será de 20 caracteres, y el int es de 4, entonces si usa un int, su índice tendrá CINCO veces más nodos por página de espacio de índice en el disco... Eso significa que atravesar el índice requerirá una quinta parte de las lecturas físicas y / o lógicas..

Por lo tanto, si el rendimiento es un problema, dada la oportunidad, siempre use una clave integral no significativa (llamada sustituta) para sus tablas y para las claves foráneas que hacen referencia a las filas en estas tabla...

Al mismo tiempo, para garantizar la coherencia de los datos, cada tabla donde importa debe también tenga una clave alternativa no numérica significativa (o un índice único) para garantizar que no se puedan insertar filas duplicadas (duplicar en función de atributos de tabla significativos).

Para el uso específico del que estás hablando (como búsquedas de estado ) realmente no importa porque el tamaño de la tabla es muy pequeño.. En general, no hay impacto en el rendimiento de índices en tablas con menos de unos pocos miles de filas...

Absolutamente no.

He hecho varios... varios... comprobaciones de rendimiento entre INT, VARCHAR y CHAR.

La tabla de 10 millones de registros con una CLAVE PRIMARIA (única y agrupada) tenía exactamente la misma velocidad y rendimiento (y el costo del subárbol) sin importar cuál de los tres usé.

Dicho esto... utilice lo que sea mejor para su aplicación. No te preocupes por la actuación.

Para códigos cortos, probablemente no haya diferencia. Esto es especialmente cierto ya que la tabla que contiene estos códigos es probable que sea muy pequeña (un par de miles de filas como máximo) y no cambie a menudo (cuando es la última vez que agregamos un nuevo estado de EE.

Para tablas más grandes con una variación más amplia entre la clave, esto puede ser peligroso. Piense en usar la dirección de correo electrónico/nombre de usuario de una tabla de usuarios, por ejemplo. Qué sucede cuando tienes unos pocos millones de usuarios y algunos de esos usuarios tienen nombres largos o direcciones de correo electrónico. Ahora, cada vez que necesite unirse a esta mesa usando esa clave, se vuelve mucho más caro.

Estaba un poco molesto por la falta de puntos de referencia para este en línea, así que hice una prueba yo mismo.

Tenga en cuenta, sin embargo, que no lo hago en un básico regular, así que por favor revise mi configuración y pasos para cualquier factor que podría haber influido en los resultados involuntariamente, y publicar sus preocupaciones en los comentarios.

La configuración fue la siguiente:

• CPU Intel® CoreTM i7-7500U a 2,70 GHz × 4
• 15.6 GiB RAM, de los cuales me aseguré de que alrededor de 8 GB estaba libre durante el prueba.
• Unidad SSD de 148.6 GB, con mucho espacio libre.
• Ubuntu 16.04 64-bit
• MySQL Ver 14.14 Distrib 5.7.20, para Linux (x86_64)

Las tablas:

create table jan_int (data1 varchar(255), data2 int(10), myindex tinyint(4)) ENGINE=InnoDB;
create table jan_int_index (data1 varchar(255), data2 int(10), myindex tinyint(4), INDEX (myindex)) ENGINE=InnoDB;
create table jan_char (data1 varchar(255), data2 int(10), myindex char(6)) ENGINE=InnoDB;
create table jan_char_index (data1 varchar(255), data2 int(10), myindex char(6), INDEX (myindex)) ENGINE=InnoDB;
create table jan_varchar (data1 varchar(255), data2 int(10), myindex varchar(63)) ENGINE=InnoDB;
create table jan_varchar_index (data1 varchar(255), data2 int(10), myindex varchar(63), INDEX (myindex)) ENGINE=InnoDB;

Entonces, llené 10 millones de filas en cada tabla con un script PHP cuya esencia es así: {[19]]}

$pdo = get_pdo();

$keys = [ 'alabam', 'massac', 'newyor', 'newham', 'delawa', 'califo', 'nevada', 'texas_', 'florid', 'ohio__' ];

for ($k = 0; $k < 10; $k++) {
    for ($j = 0; $j < 1000; $j++) {
        $val = '';
        for ($i = 0; $i < 1000; $i++) {
            $val .= '("' . generate_random_string() . '", ' . rand (0, 10000) . ', "' . ($keys[rand(0, 9)]) . '"),';
        }
        $val = rtrim($val, ',');
        $pdo->query('INSERT INTO jan_char VALUES ' . $val);
    }
    echo "\n" . ($k + 1) . ' millon(s) rows inserted.';
}

Para las tablas int, el bit ($keys[rand(0, 9)]) fue reemplazado por solo rand(0, 9), y para las tablas varchar, utilicé nombres de estado completos de los Estados Unidos, sin cortarlos ni extenderlos a 6 caracteres. generate_random_string() genera una cadena aleatoria de 10 caracteres.

Luego corrí en MySQL:

• SET SESSION query_cache_type=0;
• Para jan_int cuadro:
  • SELECT count(*) FROM jan_int WHERE myindex = 5;
  • SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
• Para otros cuadros, iguales a los anteriores, con myindex = 'califo' para char cuadros y myindex = 'california' para varchar cuadros.

Tiempos de la consulta BENCHMARK en cada tabla:

• enero: 21.30 seg{[28]]}
• jan_int_index: 18.79 sec
• jan_char: 21.70 seg{[28]]}
• jan_char_index: 18,85 sec
• jan_varchar: 21.76 sec{[28]]}
• jan_varchar_index: 18.86 sec

Con respecto a los tamaños de tabla e índice, aquí está la salida de show table status from janperformancetest; (sin algunas columnas no mostradas):

|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| Name              | Engine | Version | Row_format | Rows    | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Collation              |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| jan_int           | InnoDB |      10 | Dynamic    | 9739094 |             43 |   422510592 |               0 |            0 |   4194304 |           NULL | utf8mb4_unicode_520_ci |  
| jan_int_index     | InnoDB |      10 | Dynamic    | 9740329 |             43 |   420413440 |               0 |    132857856 |   7340032 |           NULL | utf8mb4_unicode_520_ci |   
| jan_char          | InnoDB |      10 | Dynamic    | 9726613 |             51 |   500170752 |               0 |            0 |   5242880 |           NULL | utf8mb4_unicode_520_ci |  
| jan_char_index    | InnoDB |      10 | Dynamic    | 9719059 |             52 |   513802240 |               0 |    202342400 |   5242880 |           NULL | utf8mb4_unicode_520_ci |  
| jan_varchar       | InnoDB |      10 | Dynamic    | 9722049 |             53 |   521142272 |               0 |            0 |   7340032 |           NULL | utf8mb4_unicode_520_ci |   
| jan_varchar_index | InnoDB |      10 | Dynamic    | 9738381 |             49 |   486539264 |               0 |    202375168 |   7340032 |           NULL | utf8mb4_unicode_520_ci | 
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|

Mi conclusión es que no hay diferencia de rendimiento para este caso de uso en particular.

En cuanto a la Clave Primaria, lo que físicamente hace que una fila sea única debe determinarse como la clave primaria.

Para una referencia como clave foránea, usar un entero de incremento automático como sustituto es una buena idea por dos razones principales.
- Primero, hay menos gastos generales incurridos en la unión por lo general.
- En segundo lugar, si necesita actualizar la tabla que contiene el varchar único, entonces la actualización tiene que bajar en cascada a todas las tablas secundarias y actualizar todas ellas, así como los índices, mientras que con el sustituto int, solo tiene que actualizar la tabla maestra y sus índices.

El inconveniente de usar el sustituto es que posiblemente podría permitir el cambio del significado del sustituto:

ex.
id value
1 A
2 B
3 C

Update 3 to D
id value
1 A
2 B
3 D

Update 2 to C
id value
1 A
2 C
3 D

Update 3 to B
id value
1 A
2 C
3 B

Todo depende de lo que realmente necesita preocuparse en su estructura y lo que significa más.

En HauteLook, cambiamos muchas de nuestras tablas para usar teclas naturales. Experimentamos un aumento en el rendimiento en el mundo real. Como mencionaste, muchas de nuestras consultas ahora usan menos combinaciones, lo que hace que las consultas sean más eficientes. Incluso usaremos una clave primaria compuesta si tiene sentido. Dicho esto, algunas tablas son más fáciles de trabajar si tienen una llave sustituta.

Además, si está dejando que la gente escriba interfaces en su base de datos, una clave sustituta puede ser útil. La tercera parte puede confiar en el hecho de que la llave sustituta cambiará solo en circunstancias muy raras.

Casos comunes en los que una madre sustituta AUTO_INCREMENT duele:

Un patrón de esquema común es una asignación de muchos a muchos :

CREATE TABLE map (
    id ... AUTO_INCREMENT,
    foo_id ...,
    bar_id ...,
    PRIMARY KEY(id),
    UNIQUE(foo_id, bar_id),
    INDEX(bar_id) );

El rendimiento de este patrón es mucho mejor, especialmente cuando se utiliza InnoDB:

CREATE TABLE map (
    # No surrogate
    foo_id ...,
    bar_id ...,
    PRIMARY KEY(foo_id, bar_id),
    INDEX      (bar_id, foo_id) );

¿Por qué?

• Las claves secundarias InnoDB necesitan una búsqueda adicional; moviendo el par en el PK, eso se evita para una dirección.
• El índice secundario es "covering", por lo que no necesita la búsqueda adicional.
• Esta tabla es más pequeña debido a deshacerse de id y un índice.

Otro caso ( país):

country_id INT ...
-- versus
country_code CHAR(2) CHARACTER SET ascii

Con demasiada frecuencia el novato normaliza country_code en una cadena de 4 bytes INT en lugar de usar una cadena de 2 bytes 'natural', casi inmutable. Más rápido, más pequeño, menos uniones, más legible.

Me enfrenté al mismo dilema. Hice un DW (Esquema de Constelación) con 3 tablas de datos, Accidentes de Tráfico, Vehículos en Accidentes y Víctimas en Accidentes. Los datos incluyen todos los accidentes registrados en el Reino Unido entre 1979 y 2012, y 60 tablas de dimensiones. Todos juntos, unos 20 millones de discos.

Tablas de hechos relaciones:

+----------+          +---------+
| Accident |>--------<| Vehicle |
+-----v----+ 1      * +----v----+
     1|                    |1
      |    +----------+    |
      +---<| Casualty |>---+
         * +----------+ *

RDMS: MySQL 5.6

De forma nativa el índice de Accidentes es un varchar(números y letras), con 15 dígitos. Traté de no tener llaves sustitutas, una vez que el accidente los índices nunca cambiarían. En una computadora i7 (8 núcleos), el DW se volvió demasiado lento para consultar después de 12 millones de registros de carga dependiendo de las dimensiones. Después de un montón de re-trabajo y la adición de bigint subrogate keys obtuve un promedio de 20% de aumento de rendimiento de velocidad. Sin embargo, a la baja ganancia de rendimiento, pero intento válido. Estoy trabajando en MySQL tuning y clustering.

No estoy seguro de las implicaciones de rendimiento, pero parece que un posible compromiso, al menos durante el desarrollo, sería incluir tanto la clave "sustitutiva" de enteros auto-incrementados, como la clave "natural" deseada, única. Esto le daría la oportunidad de evaluar el rendimiento, así como otros posibles problemas, incluida la capacidad de cambio de las claves naturales.

La pregunta es sobre MySQL, así que digo que hay una diferencia significativa. Si se trataba de Oracle (que almacena los números como cadena - sí, no podía creerlo al principio) entonces no hay mucha diferencia.

El almacenamiento en la tabla no es el problema, pero la actualización y la referencia al índice sí lo es. Las consultas que implican buscar un registro en función de su clave principal son frecuentes: desea que ocurran lo más rápido posible porque ocurren con tanta frecuencia.

La cosa es que una CPU trata con 4 bytes y 8 enteros de bytes naturalmente, en silicio . Es muy rápido para comparar dos enteros-que sucede en uno o dos ciclos de reloj.

Ahora mira una cadena - está compuesta de muchos caracteres (más de un byte por carácter en estos días). La comparación de dos cadenas para la precedencia no se puede hacer en uno o dos ciclos. En su lugar, los caracteres de las cadenas deben iterarse hasta que se encuentre una diferencia. Estoy seguro de que hay trucos para hacerlo más rápido en algunas bases de datos, pero eso es irrelevante aquí debido a que una comparación int se realiza de forma natural y rápida en silicio por la CPU.

Mi regla general-cada clave primaria debe ser una INT autoincrementing especialmente en aplicaciones OO usando un OR (Hibernate, Datanucleus, lo que sea) donde hay muchas relaciones entre objetos - generalmente siempre se implementarán como un FK simple y la capacidad de la base de datos para resolverlas rápidamente es importante para su aplicación' s capacidad de respuesta.

Como de costumbre, no hay respuestas generales. 'Depende! y no estoy siendo chistoso. Mi comprensión de la pregunta original era para las claves en tablas pequeñas-como País (id entero o código char / varchar) siendo una clave foránea para una tabla potencialmente enorme como la tabla de direcciones / contactos.

Hay dos escenarios aquí cuando desea recuperar datos de la base de datos. Primero es un tipo de consulta de lista/búsqueda donde desea enumerar todos los contactos con códigos de estado y país o nombres (ids no ayudará y por lo tanto necesitará una búsqueda). El otro es un escenario get en clave primaria que muestra un solo registro de contacto donde se debe mostrar el nombre del estado, país.

Para este último get, probablemente no importa en qué se basa el FK, ya que estamos reuniendo tablas para un solo registro o algunos registros y en lecturas de teclas. El primer escenario (búsqueda o lista) puede verse afectado por nuestra elección. Dado que se requiere para mostrar el país (al menos un código reconocible y tal vez incluso el la búsqueda en sí incluye un código de país), no tener que unirse a otra tabla a través de una clave sustituta puede potencialmente (solo estoy siendo cauteloso aquí porque no he probado esto, pero parece muy probable) mejorar el rendimiento; a pesar del hecho de que ciertamente ayuda con la búsqueda.

Como los códigos son pequeños en tamaño - no más de 3 caracteres generalmente para país y estado, puede estar bien usar las claves naturales como claves foráneas en este escenario.

El otro escenario donde las claves dependen de valores varchar más largos y tal vez en tablas más grandes; la clave sustituta probablemente tiene la ventaja.

Permítanme decir que sí, definitivamente hay una diferencia, teniendo en cuenta el alcance del rendimiento (Definición fuera de la caja):

1 - Usar surrogate int es más rápido en la aplicación porque no necesita usar ToUpper(), ToLower(), ToUpperInvarient () o ToLowerInvarient() en su código o en su consulta y estas 4 funciones tienen diferentes parámetros de rendimiento. Consulte reglas de rendimiento de Microsoft al respecto. (ejecución de la aplicación)

2-Uso de sustituto int garantiza no cambiar la clave con el tiempo. Incluso los códigos de país pueden cambiar, vea Wikipedia cómo los códigos ISO cambiaron con el tiempo. Eso llevaría mucho tiempo cambiar la clave principal para los subárboles. (funcionamiento del mantenimiento de datos)

3 - Parece que hay problemas con las soluciones OR, como NHibernate cuando PK/FK no es int. (rendimiento del desarrollador)