type name modify ejemplo constraint column sql mysql ddl alter-table

sql - name - ¿ALTER TABLE sin bloquear la mesa?



alter table oracle modify column type (19)

Como SeanDowney ha mencionado, pt-online-schema-change es una de las mejores herramientas para hacer lo que ha descrito en la pregunta aquí. Recientemente hice muchos cambios de esquema en una base de datos en vivo y funcionó bastante bien. Puede leer más sobre esto en mi blog aquí: http://mrafayaleem.com/2016/02/08/live-mysql-schema-changes-with-percona/ .

Cuando se hace una instrucción ALTER TABLE en MySQL, toda la tabla se lee durante toda la instrucción. Si se trata de una gran tabla, eso significa que las instrucciones de inserción o actualización podrían estar bloqueadas durante mucho tiempo. ¿Hay alguna manera de hacer una "modificación caliente", como agregar una columna de tal manera que la tabla sea actualizable durante todo el proceso?

En general, estoy interesado en una solución para MySQL, pero estaría interesado en otros RDBMS si MySQL no puede hacerlo.

Para aclarar, mi propósito es simplemente evitar el tiempo de inactividad cuando una nueva característica que requiere una columna adicional de la tabla se empuja a la producción. Cualquier esquema de base de datos cambiará con el tiempo, eso es solo un hecho de la vida. No veo por qué deberíamos aceptar que estos cambios inevitablemente provoquen tiempo de inactividad; eso es solo débil

Como ha preguntado sobre otras bases de datos, aquí hay información sobre Oracle.

Agregar una columna NULL a una tabla de Oracle es una operación muy rápida ya que solo actualiza el diccionario de datos. Esto tiene un bloqueo exclusivo en la mesa durante un período de tiempo muy corto. Sin embargo, invalidará cualquier procedimiento dependiente almacenado, vistas, disparadores, etc. Estos se volverán a compilar automáticamente.

Desde allí, si es necesario, puede crear un índice utilizando la cláusula ONLINE. De nuevo, solo bloqueos de diccionario de datos muy cortos. Leerá toda la tabla buscando elementos para indexar, pero no bloquea a nadie mientras hace esto.

Si necesita agregar una clave externa, puede hacer esto y lograr que Oracle confíe en que los datos son correctos. De lo contrario, debe leer toda la tabla y validar todos los valores que pueden ser lentos (primero crea tu índice).

Si necesita colocar un valor predeterminado o calculado en cada fila de la nueva columna, deberá ejecutar una actualización masiva o quizás un pequeño programa de utilidad que rellene los nuevos datos. Esto puede ser lento, especialmente si las filas son mucho más grandes y ya no caben en sus bloques. El bloqueo se puede gestionar durante este proceso. Como el versino anterior de su aplicación, que aún se está ejecutando, no conoce esta columna, es posible que necesite un disparador engañoso o que especifique un valor predeterminado.

A partir de ahí, puede realizar una conmutación en los servidores de su aplicación a la nueva versión del código y continuará ejecutándose. Suelta tu disparador disparador.

Alternativamente, puede usar DBMS_REDEFINITION que es una caja negra diseñada para hacer este tipo de cosas.

Todo esto es tan molesto de probar, etc. que simplemente tenemos un corte temprano en la mañana del domingo cada vez que lanzamos una versión principal.

Con el complemento Innodb, las instrucciones ALTER TABLE que solo agregan o eliminan índices secundarios se pueden realizar "rápidamente", es decir, sin reconstruir la tabla.

Sin embargo, en general, en MySQL, cualquier ALTER TABLE implica reconstruir toda la tabla, lo que puede llevar mucho tiempo (es decir, si la tabla tiene una cantidad útil de datos).

Realmente necesita diseñar su aplicación para que las instrucciones de ALTER TABLE no necesiten hacerse regularmente; Ciertamente no quiere que se realice ninguna ALTER TABLE durante el funcionamiento normal de la aplicación a menos que esté preparado para esperar o esté alterando las tablas pequeñas.

En general, la respuesta será "No". Está cambiando la estructura de la tabla que potencialmente requerirá muchas actualizaciones "y definitivamente estoy de acuerdo con eso. Si espera hacerlo a menudo, ofreceré una alternativa a las columnas" ficticias "- use VIEW s en lugar de tablas para SELECT datos. IIRC, cambiar la definición de una vista es relativamente ligero y la indirección a través de una vista se realiza cuando se compila el plan de consulta. El costo es que tendría que agregar la columna a una nueva tabla y hacer la vista JOIN en la columna.

Por supuesto, esto solo funciona si puede usar claves externas para realizar una cascada de eliminaciones y otras cosas. La otra ventaja es que puede crear una nueva tabla que contenga una combinación de los datos y apuntar la vista sin alterar el uso del cliente.

Solo un pensamiento.

Esta pregunta de 2009. Ahora MySQL ofrece una solución:

DDL en línea

Una característica que mejora el rendimiento, la concurrencia y la disponibilidad de las tablas InnoDB durante las operaciones DDL (principalmente ALTER TABLE). Consulte la Sección 14.11, "InnoDB y DDL en línea" para más detalles.

Los detalles varían según el tipo de operación. En algunos casos, la tabla se puede modificar al mismo tiempo mientras ALTER TABLE está en progreso. La operación podría realizarse sin hacer una copia de tabla, o usar un tipo de copia de tabla especialmente optimizado. El uso del espacio está controlado por la opción de configuración innodb_online_alter_log_max_size.

Le permite ajustar el equilibrio entre el rendimiento y la concurrencia durante la operación DDL, eligiendo si bloquear el acceso a la tabla por completo (cláusula LOCK = EXCLUSIVE), permitir consultas pero no DML (cláusula LOCK = SHARED) o permitir la consulta completa y el DML acceso a la tabla (cláusula LOCK = NONE). Cuando omite la cláusula LOCK o especifica LOCK = DEFAULT, MySQL permite tanta concurrencia como sea posible dependiendo del tipo de operación.

Al realizar cambios en el lugar donde sea posible, en lugar de crear una nueva copia de la tabla, se evitan aumentos temporales en el uso de espacio en disco y sobrecarga de E / S asociados con la copia de la tabla y la reconstrucción de índices secundarios.

ver MySQL 5.6 Manual de referencia -> InnoDB y DDL en línea para más información.

Parece que DDL en línea también está disponible en MariaDB

Alternativamente, puede usar ALTERAR LA TABLA EN LÍNEA para asegurarse de que su ALTER TABLE no bloquee las operaciones simultáneas (no requiere bloqueos). Es equivalente a LOCK = NONE.

MariaDB KB sobre ALTER TABLE

La única otra opción es hacer manualmente lo que muchos sistemas RDBMS hacen de todos modos ...
- Crea una nueva mesa

A continuación, puede copiar el contenido de la tabla anterior en un trozo a la vez. Siempre teniendo cuidado con cualquier INSERT / UPDATE / DELETE en la tabla fuente. (Podría ser administrado por un disparador. Aunque esto causaría una desaceleración, no es un bloqueo ...)

Una vez terminado, cambie el nombre de la tabla de origen y luego cambie el nombre de la nueva tabla. Preferiblemente en una transacción.

Una vez terminado, recompile cualquier procedimiento almacenado, etc. que use esa tabla. Los planes de ejecución probablemente ya no serán válidos.

EDITAR:

Se han hecho algunos comentarios sobre que esta limitación es un poco pobre. Así que pensé en ponerle una nueva perspectiva para mostrar por qué es así ...

  • Agregar un nuevo campo es como cambiar un campo en cada fila.
  • Field Locks sería mucho más difícil que los bloqueos de Row, sin importar los bloqueos de mesa.

  • En realidad estás cambiando la estructura física en el disco, cada registro se mueve.
  • Esto realmente es como una ACTUALIZACIÓN en la tabla completa, pero con más impacto ...

La diferencia entre Postgres y MySQL a este respecto es que en Postgres no vuelve a crear una tabla, sino que modifica el diccionario de datos que es similar a Oracle. Por lo tanto, la operación es rápida, mientras que todavía es necesario asignar un bloqueo exclusivo de tabla DDL durante muy poco tiempo, como se indicó anteriormente por otros.

En MySQL, la operación copiará los datos en una nueva tabla mientras bloquea las transacciones, lo que ha sido una molestia principal para los DBA MySQL anteriores a la v. 5.6.

La buena noticia es que desde la liberación de MySQL 5.6 la restricción se ha eliminado en su mayoría y ahora puede disfrutar del verdadero poder de la base de datos MYSQL.

Las columnas ficticias son una buena idea si puedes predecir su tipo (y hacer que se puedan agregar nulos). Compruebe cómo su motor de almacenamiento maneja los valores nulos.

MyISAM bloqueará todo si incluso menciona el nombre de una tabla de paso, en el teléfono, en el aeropuerto. Simplemente hace eso ...

Dicho esto, las cerraduras no son realmente un gran problema; siempre y cuando no intente agregar un valor predeterminado para la nueva columna en cada fila, pero déjelo como nulo, y su motor de almacenamiento es lo suficientemente inteligente como para no ir a escribirlo, debería estar bien con un bloqueo que es solo celebrado el tiempo suficiente para actualizar los metadatos. Si tratas de escribir un nuevo valor, bueno, eres un brindis.

Nop. Si está utilizando tablas MyISAM, a mi entender, solo hacen bloqueos de tabla, no hay bloqueos de registros, solo intentan mantener todo lo más rápido posible a través de la simplicidad. (Otras tablas de MySQL funcionan de manera diferente.) En cualquier caso, puede copiar la tabla en otra tabla, modificarla y luego cambiarla, actualizando por diferencias.

Esta es una alteración tan masiva que dudo que cualquier DBMS la respalde. En primer lugar, se considera un beneficio poder hacerlo con los datos en la tabla.

Percona crea una herramienta llamada pt-online-schema-change que permite hacer esto.

Básicamente hace una copia de la tabla y modifica la nueva tabla. Para mantener la nueva tabla sincronizada con el original, usa desencadenadores para actualizar. Esto permite acceder a la tabla original mientras se prepara la nueva tabla en segundo plano.

Esto es similar al método sugerido por Dems anteriormente, pero esto lo hace de manera automatizada.

Algunas de sus herramientas tienen una curva de aprendizaje, es decir, conectarse a la base de datos, pero una vez que la tienen, son excelentes herramientas para tener.

Ex:

pt-online-schema-change --alter "ADD COLUMN c1 INT" D=db,t=numbers_are_friends

Realmente no.

ESTÁS alterando la estructura subyacente de la tabla, después de todo, y eso es un poco de información que es bastante importante para el sistema subyacente. También (probablemente) mueve gran parte de los datos en el disco.

Si planeas hacer esto mucho, es mejor simplemente rellenar la mesa con columnas "ficticias" que estén disponibles para su uso futuro.

Recomiendo Postgres si esa es una opción. Con Postgres esencialmente no hay tiempo de inactividad con los siguientes procedimientos:

Otra gran característica es que la mayoría de las sentencias DDL son transaccionales, por lo que podría hacer una migración completa dentro de una transacción SQL, y si algo sale mal, todo se retrotrae.

Escribí this hace un momento, quizás puede arrojar algo más de información sobre los otros méritos.

Si alguien todavía está leyendo esto o pasa por aquí, este es el gran beneficio de usar un sistema de base de datos NoSQL como mongodb. Tuve el mismo problema con la modificación de la tabla para agregar columnas para funciones o índices adicionales en una tabla grande con millones de filas y escrituras altas. Terminaría bloqueado durante mucho tiempo, por lo que hacer esto en la base de datos LIVE frustraría a nuestros usuarios. En mesas pequeñas puedes salirte con la tuya.

Odio el hecho de que tenemos que "diseñar nuestras tablas para evitar alterarlas". Simplemente no creo que eso funcione en el mundo actual de los sitios web. No puede predecir cómo las personas usarán su software, por eso cambia rápidamente las cosas en función de los comentarios de los usuarios. Con mongodb, puede agregar "columnas" a voluntad sin tiempo de inactividad. Ni siquiera los agregas, solo insertas datos con nuevas columnas y lo hace automáticamente.

Vale la pena echarle un vistazo: www.mongodb.com

Si no puede permitirse el tiempo de inactividad de su base de datos cuando realiza actualizaciones de aplicaciones, debería considerar mantener un clúster de dos nodos para una alta disponibilidad. Con una configuración de replicación simple, puede hacer cambios estructurales casi en línea como el que sugiere:

  • esperar a que todos los cambios se repliquen en un esclavo pasivo
  • cambiar el esclavo pasivo para ser el maestro activo
  • hacer los cambios estructurales al viejo maestro
  • replicar los cambios desde el nuevo maestro al antiguo maestro
  • hacer el intercambio maestro de nuevo y el nuevo despliegue de la aplicación simultáneamente

¡No siempre es fácil pero funciona, generalmente con 0 tiempo de inactividad! El segundo nodo no tiene que ser solo pasivo, se puede usar para probar, hacer estadísticas o como un nodo de respaldo. Si no tiene infraestructura, la replicación se puede configurar en una sola máquina (con dos instancias de MySQL).

Solución temporal...

Otra solución podría ser agregar una tabla con la clave principal de la tabla original, junto con su nueva columna.

Complete su clave principal en la nueva tabla y complete los valores para la nueva columna en su nueva tabla, y modifique su consulta para unirse a esta tabla para seleccionar operaciones y también necesita insertar, actualizar por separado para este valor de columna.

Cuando puede obtener tiempo de inactividad, puede modificar la tabla original, modificar sus consultas DML y soltar su nueva tabla creada anteriormente

De lo contrario, puede optar por el método de agrupamiento, la replicación y la herramienta pt-online-schema de percona.

TokuDB puede agregar / soltar columnas y agregar índices "activos", la tabla está completamente disponible durante todo el proceso. Está disponible a través de www.tokutek.com

Yo recomendaría uno de dos enfoques:

  1. Diseñe las tablas de su base de datos con los posibles cambios en mente. Por ejemplo, he trabajado con Content Management Systems, que cambian los campos de datos en contenido regularmente. En lugar de construir la estructura física de la base de datos para que coincida con los requisitos iniciales del campo CMS, es mucho mejor construir en una estructura flexible. En este caso, usando un campo de texto blob (varchar (max) por ejemplo) para contener datos XML flexibles. Esto hace que los cambios estructurales sean menos frecuentes. Los cambios estructurales pueden ser costosos, por lo que aquí también hay un beneficio en el costo.

  2. Tener tiempo de mantenimiento del sistema. O el sistema se desconecta durante los cambios (mensualmente, etc.) y los cambios se programan durante la hora del día con mayor tráfico (por ejemplo, 3-5am). Los cambios se realizan antes del lanzamiento de la producción, por lo que tendrá una buena estimación de tiempo de inactividad de la ventana fija.

2a. Tener servidores redundantes, de modo que cuando el sistema tenga tiempo de inactividad, el sitio completo no se apague. Esto le permitiría "rodar" sus actualizaciones de forma escalonada, sin quitar todo el sitio.

Las opciones 2 y 2a pueden no ser factibles; tienden a ser solo para sitios / operaciones más grandes. Son opciones válidas, sin embargo, y yo personalmente he utilizado todas las opciones presentadas aquí.