reconstruir - tamaño de indices mysql
¿Hay alguna razón para preocuparse por el orden de las columnas en una tabla? (13)
Sé que puedes ALTERAR el orden de las columnas en MySQL con FIRST y DESPUÉS, pero ¿por qué querrías molestarte? Dado que las buenas consultas nombran explícitamente las columnas al insertar datos, ¿hay alguna razón para preocuparse por el orden en que se encuentran sus columnas en la tabla?
Algunas aplicaciones mal escritas pueden depender del orden / índice de la columna en lugar del nombre de la columna. No deberían serlo, pero sucede. Cambiar el orden de las columnas rompería tales aplicaciones.
Como se señaló, existen numerosos problemas potenciales de rendimiento. Una vez trabajé en una base de datos donde poner columnas muy grandes al final mejoraba el rendimiento si no hacía referencia a esas columnas en su consulta. Aparentemente, si un registro abarcaba varios bloques de disco, el motor de la base de datos podría dejar de leer bloques una vez que obtuviera todas las columnas que necesitaba.
Por supuesto, cualquier implicación en el rendimiento depende en gran medida no solo del fabricante que está utilizando, sino también de la versión. Hace unos meses noté que nuestro Postgres no podía usar un índice para una comparación "me gusta". Es decir, si escribió "somecolumn like ''M%''", no fue lo suficientemente inteligente como para omitir las M y salir cuando encontró la primera N. Estaba planeando cambiar un grupo de consultas para usar "between". Luego obtuvimos una nueva versión de Postgres y se manejó de manera inteligente. Me alegro de que nunca llegué a cambiar las consultas. Obviamente no es directamente relevante aquí, pero mi punto es que cualquier cosa que haga por consideraciones de eficiencia podría quedar obsoleta con la próxima versión.
El orden de las columnas es casi siempre muy relevante para mí porque de forma rutinaria escribo código genérico que lee el esquema de la base de datos para crear pantallas. Por ejemplo, mis pantallas de "editar un registro" casi siempre se crean leyendo el esquema para obtener la lista de campos y luego mostrarlos en orden. Si cambio el orden de las columnas, mi programa seguirá funcionando, pero la visualización puede ser extraña para el usuario. Al igual que, espera ver nombre / dirección / ciudad / estado / zip, no ciudad / dirección / zip / nombre / estado. Claro, podría poner el orden de visualización de las columnas en código o en un archivo de control o algo así, pero cada vez que agreguemos o eliminemos una columna tendremos que acordarnos de actualizar el archivo de control. Me gusta decir cosas una vez. Además, cuando la pantalla de edición se crea exclusivamente a partir del esquema, agregar una nueva tabla puede significar escribir cero líneas de código para crear una pantalla de edición para él, lo cual es genial. (Bueno, está bien, en la práctica, generalmente tengo que agregar una entrada al menú para llamar al programa de edición genérico, y en general, he abandonado el genérico "seleccionar un registro para actualizar" porque hay demasiadas excepciones para que sea práctico .)
Como suele ser el caso, el factor más importante es el siguiente tipo que tiene que trabajar en el sistema. Intento primero tener las columnas de clave principal, las columnas de clave externa en segundo lugar y luego el resto de las columnas en orden descendente de importancia / importancia para el sistema.
Durante la capacitación de Oracle en un trabajo anterior, nuestro DBA sugirió que poner todas las columnas que no admiten nulos antes de las que aceptan valores NULL era ventajoso ... aunque TBH no recuerdo los detalles de por qué. ¿O tal vez solo los que probablemente se actualizarán deberían ir al final? (Tal vez pospone tener que mover la fila si se expande)
En general, no debería hacer ninguna diferencia. Como dices, las consultas siempre deben especificar columnas en lugar de confiar en el orden de "seleccionar *". No sé de ningún DB que les permita ser cambiado ... bueno, no sabía que MySQL lo permitiera hasta que lo mencionó.
El orden de las columnas tuvo un gran impacto en el rendimiento en algunas de las bases de datos que he ajustado, que abarcan Sql Server, Oracle y MySQL. Esta publicación tiene buenas reglas generales :
- Primeras columnas de clave primero
- Columnas clave extranjeras siguiente.
- Las columnas más buscadas a continuación
- Columnas actualizadas con frecuencia más tarde
- Columnas anulables últimas.
- Las columnas anulables menos utilizadas después de las columnas con nulos más utilizadas
Un ejemplo de diferencia de rendimiento es una búsqueda de índice. El motor de base de datos encuentra una fila según algunas condiciones en el índice y recupera una dirección de fila. Ahora di que estás buscando SomeValue, y está en esta tabla:
SomeId int,
SomeString varchar(100),
SomeValue int
El motor tiene que adivinar dónde se inicia SomeValue, porque SomeString tiene una longitud desconocida. Sin embargo, si cambia el orden a:
SomeId int,
SomeValue int,
SomeString varchar(100)
Ahora el motor sabe que SomeValue se puede encontrar 4 bytes después del inicio de la fila. Entonces, el orden de las columnas puede tener un impacto considerable en el rendimiento.
EDITAR: Sql Server 2005 almacena campos de longitud fija al comienzo de la fila. Y cada fila tiene una referencia al comienzo de un varchar. Esto niega completamente el efecto que he enumerado arriba. Entonces, para las bases de datos recientes, el orden de las columnas ya no tiene ningún impacto.
En general, lo que sucede en SQL Server cuando se cambia el orden de las columnas a través de Management Studio, es que crea una tabla temporal con la nueva estructura, mueve los datos a esa estructura desde la anterior, descarta la anterior y cambia el nombre a la nueva. Como se puede imaginar, esta es una opción muy pobre para el rendimiento si tiene una mesa grande. No sé si mi SQL hace lo mismo, pero es una razón por la cual muchos de nosotros evitamos reordenar columnas. Como select * nunca debe usarse en un sistema de producción, agregar columnas al final no es un problema para un sistema bien diseñado. En general, el orden de las columnas en la tabla no debe ser alterado.
La única razón por la que puedo pensar es para depurar y combatir incendios. Tenemos una tabla cuya columna de "nombre" aparece alrededor del décimo en la lista. Es difícil cuando seleccionas rápidamente * de la tabla donde identificamos (1,2,3) y luego tienes que desplazarte para ver los nombres.
Pero eso es todo.
La única vez que tendrá que preocuparse por el orden de las columnas es si su software se basa específicamente en ese orden. Por lo general, esto se debe al hecho de que el desarrollador se volvió flojo e hizo un select * y luego se refirió a las columnas por índice en lugar de por nombre en su resultado.
Legibilidad de la salida cuando debe escribir:
select * from <table>
en su software de administración de base de datos?
Es una razón muy falsa, pero por el momento no puedo pensar en otra cosa.
Más allá del ajuste de rendimiento obvio, me encontré con un caso de esquina donde las columnas de reordenación hacían que fallara una secuencia de comandos sql (previamente funcional).
De la documentación "las columnas TIMESTAMP y DATETIME no tienen propiedades automáticas a menos que se especifiquen explícitamente, con esta excepción: de forma predeterminada, la primera columna TIMESTAMP tiene tanto DEFAULT CURRENT_TIMESTAMP como ON UPDATE CURRENT_TIMESTAMP si no se especifica explícitamente" https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html
Entonces, un comando ALTER TABLE table_name MODIFY field_name timestamp(6) NOT NULL; funcionará si ese campo es la primera marca de tiempo (o fecha y hora) en una tabla, pero no de otra manera.
Obviamente, puede corregir ese comando alter para incluir un valor predeterminado, pero el hecho de que una consulta que funcionó dejó de funcionar debido a un reordenamiento de columna hizo que me doliera la cabeza.
No, el orden de las columnas en una tabla de base de datos SQL es totalmente irrelevante, excepto para fines de visualización / impresión. No tiene sentido reordenar columnas: la mayoría de los sistemas ni siquiera proporcionan una forma de hacerlo (excepto descartar la tabla anterior y recrearla con el nuevo orden de las columnas).
Bagazo
EDITAR: de la entrada de Wikipedia en la base de datos relacional, aquí está la parte relevante que para mí muestra claramente que el orden de las columnas nunca debe ser motivo de preocupación:
Una relación se define como un conjunto de n-tuplas. Tanto en matemáticas como en el modelo de base de datos relacional, un conjunto es una colección desordenada de elementos, aunque algunos DBMS imponen un orden a sus datos. En matemáticas, una tupla tiene un orden y permite la duplicación. EF Codd originalmente tuplas definidas utilizando esta definición matemática. Más tarde, fue una de las grandes ideas de EF Codd que el uso de nombres de atributos en lugar de un orden sería mucho más conveniente (en general) en un lenguaje de computadora basado en las relaciones. Esta idea todavía se usa hoy.
Si vas a utilizar mucho UNION, facilita la combinación de columnas si tienes una convención sobre su orden.
Actualizar:
En MySQL , puede haber una razón para hacer esto.
Dado que los tipos de datos variables (como VARCHAR ) se almacenan con longitudes variables en InnoDB , el motor de la base de datos debe recorrer todas las columnas anteriores en cada fila para encontrar el desplazamiento del dado.
El impacto puede ser tan grande como 17% para 20 columnas.
Vea esta entrada en mi blog para más detalles:
En Oracle , las columnas NULL finales no consumen espacio, por eso siempre debe ponerlas al final de la tabla.
También en Oracle y en SQL Server , en el caso de una fila grande, puede producirse un ROW CHAINING .
ROW CHANING está dividiendo una fila que no cabe en un bloque y abarcando los múltiples bloques, conectados con una lista vinculada.
La lectura de columnas finales que no se ajustaron al primer bloque requerirá atravesar la lista vinculada, lo que dará como resultado una operación de I/O adicional.
Consulte esta página para ver la ilustración de ROW CHAINING en Oracle :
Es por eso que debe poner las columnas que usa con frecuencia al comienzo de la tabla, y las columnas que no usa con frecuencia, o las columnas que tienden a ser NULL , hasta el final de la tabla.
Nota IMPORTANTE:
Si te gusta esta respuesta y quieres votar por ella, vota también por la respuesta de @Andomar .
Él contestó lo mismo, pero parece estar degradado sin razón.