关于ddl(新增字段)对数据库锁表|读写操作的影响_资料

1.对一个表执行ddl(新增字段)会不会阻塞表，影响读写？

在一次项目升级之前需要执行一个新增字段的脚本(alter table...)，表的数据量是260多万，执行时间是72秒，感觉略长，不知道会不会影响项目对数据库的操作，故连百度带问一番，得出一些结果。

结论是：执行alter table...会影响读写

2.MySQL官方文档alter table的解释

ALTER TABLE operations that use the COPY algorithm wait for other operations that are modifying the table to complete. After alterations are applied to the table copy, data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While the ALTER TABLE operation executes, the original table is readable by other sessions(读，不阻塞) (with the exception noted shortly). Updates and writes to the table started after the ALTER TABLE operation begins are stalled until the new table is ready(更新|写入阻塞), then are automatically redirected to the new table. The temporary copy of the table is created in the database directory of the original table unless it is a RENAME TO operation that moves the table to a database that resides in a different directory.

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes)(读写都会阻塞，当数据库操作.frm文件时) at the point where it is ready to install a new version of the table .frm file, discard the old file, and clear outdated table structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To do so, it waits for current readers to finish, and blocks new reads and writes.

3.总结

肯定会影响写入的，读取会有一段时间的影响，最后决定在升级项目，停服时执行alter table...，这样不会影响写入；

还是要看官方文档，别自己猜。

转载于:https://www.cnblogs.com/rocker-pg/p/10186634.html