mysql performance primary-key innodb myisam

mysql - ¿Hay una diferencia REAL de rendimiento entre las teclas principales INT y VARCHAR?



performance primary-key (15)

¿Existe una diferencia de rendimiento mensurable entre el uso de INT frente a VARCHAR como clave principal en MySQL? Me gustaría usar VARCHAR como la clave principal para las listas de referencias (piense en los Estados Unidos, Códigos de país) y un compañero de trabajo no se moverá en INT AUTO_INCREMENT como clave principal para todas las tablas.

Mi argumento, como se detalla here , es que la diferencia de rendimiento entre INT y VARCHAR es insignificante, ya que cada referencia de clave externa INT requerirá un JOIN para dar sentido a la referencia, una clave VARCHAR presentará directamente la información.

Entonces, ¿alguien tiene experiencia con este caso de uso particular y las preocupaciones de rendimiento asociadas con él?

Absolutamente no.

He realizado varias ... varias ... verificaciones de rendimiento entre INT, VARCHAR y CHAR.

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

Dicho eso ... use lo que sea mejor para su aplicación. No te preocupes por el rendimiento.

Casos comunes en los que un sustituto AUTO_INCREMENT duele:

Un patrón de esquema común es un mapeo 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 usa 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 de InnoDB necesitan una búsqueda adicional; moviendo el par en PK, eso se evita para una dirección.
  • El índice secundario está "cubriendo", por lo que no necesita una 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 INT 4 bytes en lugar de utilizar una cadena de 2 bytes ''natural'' de 2 bytes, casi inmutable. Más rápido, más pequeño, menos JOINs, más legible.

Como de costumbre, no hay respuestas generales. ''¡Depende!'' y no estoy siendo gracioso. Mi comprensión de la pregunta original era para claves en tablas pequeñas, como Country (entero ID o código char / varchar) que es una clave externa para una mesa potencialmente enorme como dirección / tabla de contactos.

Aquí hay dos escenarios cuando quiere datos de la base de datos. En primer lugar, se trata de una consulta de tipo lista / búsqueda en la que desea enumerar todos los contactos con códigos o nombres de estado y país (los identificadores no son útiles y, por lo tanto, será necesario buscarlos). El otro es un escenario de obtención en la clave principal que muestra un registro de contacto único donde se debe mostrar el nombre del estado y el país.

Para los últimos, probablemente no importa en qué se basa el FK ya que estamos reuniendo tablas para un solo registro o algunos registros y lecturas de claves. El escenario anterior (búsqueda o lista) puede verse afectado por nuestra elección. Como es necesario mostrar el país (al menos un código reconocible y tal vez incluso la búsqueda incluye un código de país), no tener que unirse a otra mesa mediante una clave sustituta puede (estoy siendo cauteloso aquí porque en realidad no he probado esto, pero parece altamente 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 el país y el estado, puede estar bien utilizar las claves naturales como claves foráneas en este escenario.

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

Depende de la longitud .. Si el varchar será de 20 caracteres, y el int es 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 cantidad de 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 claves externas que hacen referencia a las filas en estas tablas ...

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

Para el uso específico del que está hablando (como las 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 los índices en tablas con menos de unos pocos miles de filas. ..

En HauteLook, hemos cambiado muchas de nuestras tablas para usar claves naturales. Experimentamos un aumento del rendimiento en el mundo real. Como mencionas, muchas de nuestras consultas ahora usan menos combinaciones, lo que hace que las consultas sean más efectivas. Incluso usaremos una clave primaria compuesta si tiene sentido. Una vez dicho esto, es más fácil trabajar con algunas tablas si tienen una clave sustituta.

Además, si le permite a las personas escribir interfaces en su base de datos, una clave sustituta puede ser útil. El tercero puede confiar en el hecho de que la clave sustituta cambiará solo en circunstancias muy excepcionales.

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

Para una referencia como clave foránea, usar un número entero que se incrementa automáticamente como un sustituto es una buena idea por dos razones principales.
- En primer lugar, generalmente se incurre en menos gastos generales en la unión.
- En segundo lugar, si necesita actualizar la tabla que contiene el varchar único, la actualización debe descender a todas las tablas secundarias y actualizar todas ellas, así como los índices, mientras que con el sustituto int, solo tiene que actualizar el mesa maestra y sus índices.

El inconveniente de usar el sustituto es que posiblemente podría permitir cambiar el 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 más significa.

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

Sin embargo, tenga en cuenta que no lo hago de forma regular, así que por favor revise mi configuración y los pasos para ver los factores que podrían haber influido en los resultados involuntariamente, y publique sus inquietudes en los comentarios.

La configuración fue la siguiente:

  • CPU Intel® Core ™ i7-7500U a 2.70GHz × 4
  • 15.6 GiB RAM, de los cuales aseguré que alrededor de 8 GB eran gratis durante la prueba.
  • Disco SSD de 148.6 GB, con mucho espacio libre.
  • Ubuntu 16.04 de 64 bits
  • MySQL Ver 14.14 Distrib 5.7.20, para Linux (x86_64)

Las mesas:

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;

Luego, llené 10 millones de filas en cada tabla con un script PHP cuya esencia es la siguiente:

$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 tablas int , el bit ($keys[rand(0, 9)]) fue reemplazado por just rand(0, 9) , y para tablas varchar , utilicé nombres de estado completos de EE. UU., 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 la tabla jan_int :
    • SELECT count(*) FROM jan_int WHERE myindex = 5;
    • SELECT BENCHMARK(1000000000, (SELECT count(*) FROM jan_int WHERE myindex = 5));
  • Para otras tablas, igual que arriba, con myindex = ''califo'' para tablas char y myindex = ''california'' para tablas varchar .

Tiempos de la consulta BENCHMARK en cada tabla:

  • jan_int: 21.30 seg
  • jan_int_index: 18.79 sec
  • jan_char: 21.70 sec
  • jan_char_index: 18.85 sec
  • jan_varchar: 21.76 sec
  • jan_varchar_index: 18.86 sec

En cuanto a los tamaños de tabla e índice, aquí está el resultado del show table status from janperformancetest; de show table status from janperformancetest; (con pocas 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.

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

El almacenamiento en la tabla no es el problema, pero actualizar y hacer referencia al índice es. Las consultas que implican buscar un registro basado en su clave principal son frecuentes; usted desea que ocurran lo más rápido posible porque ocurren con tanta frecuencia.

Se trata de una CPU que trata con enteros de 4 bytes y 8 bytes, naturalmente, en silicio . Es REALMENTE rápido para poder comparar dos enteros: ocurre en uno o dos ciclos de reloj.

Ahora mira una cadena: está formada por muchos personajes (más de un byte por personaje en estos días). La comparación de dos cadenas de precedencia no se puede hacer en uno o dos ciclos. En cambio, los caracteres de las cadenas deben repetirse 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 porque la CPU hace una comparación interna de forma natural y muy rápida en silicio.

Mi regla general: cada tecla principal debe ser un INT de autoincrementación, especialmente en aplicaciones OO que usan un ORM (Hibernate, Datanucleus, lo que sea) donde hay muchas relaciones entre objetos; por lo general, siempre se implementarán como un simple FK y la capacidad del DB para resolver esos rápidos es importante para la capacidad de respuesta de su aplicación.

Me enfrenté al mismo dilema. Hice un DW (esquema Constellation) con 3 tablas de hechos, accidentes de carretera, vehículos en accidentes y bajas en accidentes. Los datos incluyen todos los accidentes registrados en el Reino Unido desde 1979 hasta 2012, y 60 tablas de dimensiones. Todos juntos, alrededor de 20 millones de registros.

Relaciones de tablas de hechos:

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

RDMS: MySQL 5.6

Nativamente, el índice de Accidentes es varchar (números y letras), con 15 dígitos. Intenté no tener claves sustitutivas, una vez que los índices de accidentes nunca cambiarían. En una computadora i7 (8 núcleos), el DW se hizo demasiado lento para consultar después de 12 millones de registros de carga dependiendo de las dimensiones. Después de mucho re-trabajar y agregar llaves suplentes Bigint, obtuve un impulso de rendimiento de velocidad promedio del 20%. Sin embargo, para obtener un bajo rendimiento, pero prueba válida. Estoy trabajando en el ajuste y clustering de MySQL.

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

No se trata de rendimiento. Se trata de lo que hace una buena clave principal. Único e inmutable a lo largo del 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 principal. Pero la experiencia amarga es que rara vez es así.

INT AUTO_INCREMENT cumple con la condición "única e inmutable a lo largo del tiempo". De ahí la preferencia.

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

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 Usuario, 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 necesita unirse a esta mesa con esa clave, se vuelve mucho más costoso.

Permítanme decir que sí, definitivamente hay una diferencia, teniendo en cuenta el alcance del rendimiento (definición de fábrica):

1- El uso de sustituto int es más rápido en la aplicación porque no necesita usar ToUpper (), ToLower (), ToUpperInvarient () ni ToLowerInvarient () en su código o en su consulta, y estas 4 funciones tienen diferentes puntos de referencia de rendimiento. Ver las reglas de rendimiento de Microsoft sobre esto. (rendimiento de la aplicación)

2- El uso de garantías int subrogadas no cambia la clave a lo largo del tiempo. Incluso los códigos de país pueden cambiar, vea Wikipedia cómo los códigos ISO cambiaron con el tiempo. Eso tomaría mucho tiempo para cambiar la clave primaria para los subárboles. (rendimiento de mantenimiento de datos)

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

Señala que puedes evitar un número de consultas unidas al usar lo que se llama una clave natural en lugar de una clave sustituta . Solo usted puede evaluar si el beneficio de esto es significativo en su aplicación.

Es decir, puede medir las consultas en su aplicación que son las más importantes para ser rápido, ya que trabajan con grandes volúmenes de datos o se ejecutan con mucha frecuencia. Si estas consultas se benefician al eliminar una unión, y no sufren el uso de una clave primaria varchar, hágalo.

No use ninguna estrategia para todas las tablas en su base de datos. Es probable que en algunos casos, una clave natural sea mejor, pero en otros casos una clave sustituta es mejor.

Otras personas hacen una buena observación sobre el hecho de que es raro en la práctica que una llave natural nunca cambie o que tenga duplicados, por lo que las llaves sustitutas por lo general valen la pena.