MySQL: ¿VARCHAR grande frente a TEXTO?

Tengo una tabla de mensajes en MySQL que registra los mensajes entre los usuarios. Además de los típicos ids y tipos de mensajes (todos los tipos enteros), debo guardar el texto del mensaje real como VARCHAR o TEXTO. Estoy configurando un límite de entrada de 3000 caracteres, lo que significa que los mensajes nunca se insertarán en la base de datos más tiempo que este.

¿Hay alguna razón para ir con VARCHAR (3000) o TEXT? Hay algo acerca de simplemente escribir VARCHAR (3000) que se siente algo contrario a la intuición. He pasado por otras publicaciones similares en Stack Overflow, pero sería bueno obtener vistas específicas para este tipo de almacenamiento de mensajes comunes.

  • TEXT y BLOB se almacenan fuera de la tabla con la tabla solo con un puntero a la ubicación del almacenamiento real.

  • VARCHAR se almacena en línea con la tabla. VARCHAR es más rápido cuando el tamaño es razonable, la compensación de la cual sería más rápida depende de sus datos y su hardware, desea comparar un escenario real con sus datos.

Actualizar si VARCHAR o TEXT se almacena en línea, o no registrado, depende del tamaño de los datos, el tamaño de las columnas, el formato de fila y la versión de MySQL. No depende de “texto” frente a “varchar”.

¿Puedes predecir cuánto tiempo será la entrada del usuario?

VARCHAR (X)

Caso: nombre de usuario, correo electrónico, país, asunto, contraseña


TEXTO

Caso: mensajes, correos electrónicos, comentarios, texto formateado, html, código, imágenes, enlaces


MEDIUMTEXT

Caso: cuerpos json grandes, libros cortos y medianos, cadenas de csv


TEXTO LARGO

Caso: libros de texto, progtwigs, años de archivos de registro, harry potter y el cáliz de fuego, registro de investigación científica

Solo para aclarar la mejor práctica:

  1. Los mensajes de formato de texto casi siempre deben almacenarse como TEXTO (terminan siendo arbitrariamente largos)

  2. Los atributos de cadena deben almacenarse como VARCHAR (el nombre de usuario de destino, el sujeto, etc.).

Entiendo que tienes un límite de entrada, lo cual es genial hasta que no lo sea. * sonrisa * El truco es pensar en el DB como algo separado de las aplicaciones que se conectan a él. El hecho de que una aplicación ponga un límite a los datos no significa que los datos sean intrínsecamente limitados.

¿Qué tienen los mensajes en sí mismos que los fuerza a no tener más de 3000 caracteres? Si solo se trata de una restricción de aplicación arbitraria (por ejemplo, para un cuadro de texto o algo así), use un campo TEXT en la capa de datos.

Descargo de responsabilidad: no soy un experto en MySQL … pero esta es mi comprensión de los problemas.

Creo que TEXT se almacena fuera de la fila mysql, mientras que creo que VARCHAR se almacena como parte de la fila. Hay una longitud de fila máxima para las filas de MySQL … por lo que puede limitar la cantidad de datos que puede almacenar en una fila utilizando VARCHAR.

También debido a que VARCHAR forma parte de la fila, sospecho que las consultas que miran ese campo serán ligeramente más rápidas que las que usan un fragmento de TEXTO.

Respuesta corta: sin diferencia práctica, de rendimiento o de almacenamiento.

Respuesta larga:

Básicamente, no hay diferencia (en MySQL) entre VARCHAR(3000) (o cualquier otro límite grande) y TEXT . El primero se truncará a 3000 caracteres ; el último truncará a 65535 bytes . (Hago una distinción entre bytes y caracteres porque un personaje puede tomar múltiples bytes).

Para límites más pequeños en VARCHAR , hay algunas ventajas sobre TEXT .

  • “más pequeño” significa 191, 255, 512, 767 o 3072, etc., según la versión, el contexto y el CHARACTER SET .
  • INDEXes están limitados en qué tan grande se puede indexar una columna. (767 o 3072 bytes ; esta es la versión y la configuración depende)
  • Las tablas intermedias creadas por SELECTs complejos se manejan de dos maneras diferentes: MEMORIA (más rápida) o MyISAM (más lenta). Cuando se trata de columnas ‘grandes’, la técnica más lenta se selecciona automáticamente. (Cambios significativos en la versión 8.0, por lo que este elemento de la viñeta está sujeto a cambios).
  • En relación con el elemento anterior, todos los tipos de datos TEXT (a diferencia de VARCHAR ) saltan directamente a MyISAM. Es decir, TINYTEXT es automáticamente peor para las tablas temporales generadas que el VARCHAR equivalente. (¡Pero esto lleva la discusión en una tercera dirección!)
  • VARBINARY es como VARCHAR ; BLOB es como TEXT .

Refutación a otras respuestas

La pregunta original preguntaba una cosa (qué tipo de datos usar); la respuesta aceptada respondió algo más (almacenamiento fuera de registro). Esa respuesta ahora está desactualizada.

Cuando este subproceso se inició y respondió, solo había dos “formatos de fila” en InnoDB. Poco después, se introdujeron dos formatos más ( DYNAMIC y COMPRESSES ).

La ubicación de almacenamiento para TEXT y VARCHAR() se basa en el tamaño , no en el nombre del tipo de datos . Para obtener una discusión actualizada sobre el almacenamiento on / off-record de columnas grandes de texto / blob, consulte esto .

Las respuestas anteriores no insisten lo suficiente en el problema principal: incluso en consultas muy simples como

 (SELECT t2.* FROM t1, t2 WHERE t2.id = t1.id ORDER BY t1.id) 

se puede requerir una tabla temporal, y si se trata de un campo VARCHAR , se convierte en un campo CHAR en la tabla temporal. Entonces, si tiene en su tabla 500,000 líneas con un campo VARCHAR(65000) , esta columna solo usará 6.5 * 5 * 10 ^ 9 byte. Tales tablas temporales no se pueden manejar en la memoria y se escriben en el disco. Se puede esperar que el impacto sea catastrófico.

Fuente (con métricas): https://nicj.net/mysql-text-vs-varchar-performance/ (Esto se refiere al manejo de TEXT vs VARCHAR en el motor de almacenamiento “estándar” MyISAM. Puede ser diferente en otros, por ejemplo, InnoDB).