In Aurora, when a user issues a DDL statement:
That’s it for the synchronous part of the operation. Done.
Then, on subsequent DML operations, we check to see if the affected data page has a pending schema operation. That’s easily done by comparing the log sequence number (LSN) timestamp for the page with the LSN timestamp of schema changes. If needed, we then update the page to the new schema before applying the DML statement. This operation follows the same upgrade process for redo-undo record pages as everything else. And any I/Os are piggybacked on top of user activity.
You have to be careful about only upgrading the page on DML operations, because upgrades can cause page splits. We need to deal with upgrades on our Aurora Replicas too, and they’re not allowed to change any data. For SELECT statements, we change the memory image of the buffer being passed back to MySQL. This way, it always sees the latest schema, even though the underlying storage is a mix of old and new schema formats.
Table 14.11 Online DDL Support for Index Operations
|Operation||In Place||Rebuilds Table||Permits Concurrent DML||Only Modifies Metadata|
|Creating or adding a secondary index||Yes||No||Yes||No|
|Dropping an index||Yes||No||Yes||Yes|
|Renaming an index||Yes||No||Yes||Yes|
|Adding a ||Yes*||No*||No||No|
|Adding a ||Yes||No||No||No|
|Changing the index type||Yes||No||Yes||Yes|
alter table tb_order_archive ADD security_type tinyint(4) DEFAULT 1 NOT NULL, ;
Altering Tables in Amazon Aurora Using Fast DDL
Amazon Aurora Under the Hood: Fast DDL
MySQL Online DDL Operations