首页 >  技术 > Instant ADD COLUMN for InnoDB

Instant ADD COLUMN for InnoDB

原文 2017-10-12 04:54:38 0 评论

MariaDB Server 10.3.2 alpha was releasedthis week. For InnoDB, the new features coming in MariaDB Server 10.3 include CREATE SEQUENCEwhich is a logical continuation of the Persistent AUTO_INCREMENTthat appeared in MariaDB Server 10.2.

Perhaps one of the most important InnoDB changes coming in MariaDB Server 10.3 is Instant ADD COLUMN for InnoDB tables. The design was brainstormed in April by engineers from MariaDB Corporation, Alibaba and Tencent. A prototype was developed by Vin Chen (陈福荣) from the Tencent Game DBA Team.

What is so special about this? Normally, adding a column to a table requires the full table to be rebuilt. The complexity of the operation is proportional to the size of the table, or O( n · m ) where n is the number of rows in the table and m is the number of indexes. Sure, with my online ALTER TABLE for InnoDB tables in MySQL 5.6 and MariaDB Server 10.0, you would be able to modify the table while it is being rebuilt, but it would significantly increase the I/O and memory consumption and cause a replication lag. With instant ADD COLUMN , all that is needed is an O(log n ) operation to insert a special hidden record into the table, and an update of the data dictionary. For a large table, instead of taking several hours, the operation would be completed in the blink of an eye. The ADD COLUMN operation is only slightly more expensive than a regular INSERT , due to locking constraints.

In the past, some developers may have implemented a kind of instant add column in the application by encoding multiple columns in a single TEXT or BLOB column. MariaDB Dynamic Columnswas an early example of that. A more recent example is download MariaDB Server 10.3.2 here. Note that MariaDB Server 10.3.2 is an alpha release. Please do not use it in production, but feel free to test it.

For now, ALTER TABLE…DROP COLUMN will require the table to be rebuilt. We are proud of the exciting contributions from the robust MariaDB community. Stay tuned for new improvements coming to MariaDB!