MySQL InnoDB Modify table columns online DDL

Overview

In general, once the database structure has been designed, it cannot be easily changed because changes DDL (Data Definition Language) are expensive to operate, so you need to be cautious about database structure design.

However, business development is unknown, especially those with great changes, so it is unavoidable to modify the database structure, this paper mainly discusses the modification of mysql5.6+ InnoDB storage engine field.

For different scenarios, the way you use them will vary greatly, especially when modifying millions, Tens table fields.

DDL operation type

The DDL operations of the database structure are generally as follows:

• Indexing Operations (Index Operations)
• Key operation (Primary key Operations)
• Column operations (columns Operations)
• Foreign key operation (Foreign key Operations)
• Table Operations (Table Operations)
• Partitioning operations (partitioning Operations)

This article mainly discusses the column Operations, other more detailed information refer to MySQL official English document

Online DDL Operations Brief

This article discusses the Online DDL operation, MySQL5.6 above support, compared to the general DDL , it is implemented to modify the table structure while still allowing DML operation (Select,insert,update,delete).

There are two main ways of Online DDL: IN PLACE and COPY .

• IN PLACE: Modify directly on the original table to COPY avoid the IO and CPU consumption resulting from rebuilding tables, better performance and support for concurrent DML operations
• COPY: Creates a modified temporary table, and then copies the data from the original table to the staging table, which does not allow concurrent writes during execution DML , or results in dirty data.

Before MySQL, we generally used COPY the way, with the help of temporary table, manually modified.

It is important to note that not all online DDL operations are supported in this IN PLACE way.

MySQL InnoDB Data storage method

In MySQL, the data of a table is divided into two kinds, one is structure data, the Recorder station table contains which fields, which data types, the other is record data, save the raw data recorded daily. They are stored in different files.

In the data_dir data storage directory specified by MySQL, you can see each table corresponding frm to a file, which is the structure of the data stored in the table.

INPLACEDetailed description of the method

For adding indexes, adding/removing columns, modifying column NULL/NOT NULL properties, and so on, you need to modify the data records inside MySQL, and Online DDL you need to rebuild the table (rebuild) when you operate on such operations.

Conversely, for deleting indexes, modifying column defaults, modifying column names, and so on, you do not need to modify the data records inside MySQL, just modify the structure data frm file without rebuilding the table (No-rebuild).

In addition, different Online DDL operations can choose different locking mechanisms during operation. The following locking mechanisms are mainly:

• LOCK=DEFAULT: default mode, MySQL decide which lock mode to use, try not to lock the table
• LOCK=NONE: No Lock: Allows Online DDL concurrent read and write operations during the period. If the Online DDL operation does not support continuing writes to the table, the DDL operation fails and the table modification is not valid
• LOCK=SHARED: Shared Lock: Online DDL blocked write during operation, does not affect read
• LOCK=EXCLUSIVE: Exclusive Lock: No Online DDL action is allowed on the lock table during operation

Regardless of the mode, the Online DDL operation begins with a short period of exclusive locks to prepare the environment for waiting for other operations on the table to complete, and the Online DDL operation prompts: Waiting meta data lock.

Also Online DDL waits for the transaction to complete during the operation until the end of the operation, and an Online DDL exclusive lock is also present.

Therefore, you need to ensure that Online DDL no large DML transactions occupy the table before and during execution, or there will be a long lock table or even a deadlock.

Online DDL various column operation conditions

As can be seen from the above introduction, the different DDL operations, the specific details of the execution is not the same, see the following table:

in place Only

Operation		Rebuilds Table	permits Concurrent DML	modifies Metadata
Adding a column	Yes	Yes	Yes*	No
Dropping a column	Yes	Yes	Yes	No
Renaming a column	Yes	No	Yes*	Yes
reordering columns	Yes	Yes	Yes	No
Setting a column default value	Yes	No	Yes	Yes
Changing the column data type	No	Yes	No	No
Extending VARCHAR Column Size	Yes	No	Yes	Yes
Dropping the column default value	Yes	No	Yes	Yes
Changing the Auto-increment value	Yes	No	Yes	no*
Making a column NULL	Yes	Yes*	Yes	No
Making a column not NULL	Yes*	Yes*	Yes	No
Modifying the definition of an ENUM or SET column	Yes	No	Yes	Yes

The indicators of each column are explained as follows:

• In Place: Whether In Place the support mode, yes for the preferred scheme
• Re Builds Table: If you need to rebuild the table, do not rebuild (NO) as the preferred scheme
• Permits Concurrent DML: Allow concurrent DML operation, allow (Yes) for preferred scheme
• Only Modifies Metadata: Whether the value modifies the table structure data, only the file is modified frm

Column operation mode

The following is a list of common ways to perform column operations and considerations.

Add columns (Adding a column)

Here's how to add a column to a table:

ALTER TABLE tbl_name ADD COLUMN column_name column_definition, ALGORITHM=INPLACE, LOCK=NONE;

If additional options are added when adding a column, auto increment concurrent DML operations are not allowed, and this operation rebuilds the table, which is costly. The optimization option is specified: ALGORITHM=INPLACE, LOCK=SHARED .

Delete columns (dropping a column)

ALTER TABLE tbl_name DROP COLUMN column_name, ALGORITHM=INPLACE, LOCK=NONE;

Rename column name (renaming a)

ALTER TABLE tbl CHANGE old_col_name new_col_name data_type, ALGORITHM=INPLACE, LOCK=NONE;

If your purpose is simply to modify the column name, be sure to ensure that the modified column has the same data type NULL/NOT NULL as the original column.

This action is recommended for the specified INPLACE method, so that only the frm file is updated, even if the modified column name is a foreign key.

Rearrange column order (reordering columns)

ALTER TABLE tbl_name MODIFY COLUMN col_name column_definition FIRST, ALGORITHM=INPLACE, LOCK=NONE;

This operation thankless, it is not recommended to operate on large tables that have more than millions of data, and it rebuilds the table.

Modify the column data type (changing the

ALTER TABLE tbl_name CHANGE c1 c1 BIGINT, ALGORITHM=COPY;

Modifying the data type only supports COPY methods.

Modify the default value of a column (Setting A, column, default value)

ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT, ALGORITHM=INPLACE, LOCK=NONE;

Modifying the self-familiarity of columns (changing the auto-increment value)

ALTER TABLE table AUTO_INCREMENT=next_value, ALGORITHM=INPLACE, LOCK=NONE;

This action modifies the self-increment of the next record, modifying only the values in memory without modifying the data file.

For distributed systems, it is often necessary to manually set the value of the start increment that you can use.

Modify Null/not Null property (Making a column null and Making a column not NULL)

-- Making a column NULLALTER TABLE tbl_name MODIFY COLUMN column_name data_type NULL, ALGORITHM=INPLACE, LOCK=NONE;-- Making a column NOT NULLALTER TABLE tbl_name MODIFY COLUMN column_name data_type NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;

Because NULL the column is added to the original data type space on the basis of a direct to store if it is set, the NULL table needs to be rebuilt.

When NULL the column is set to NOT NULL , if there is a record NULL , the operation will fail.

Modify the definition of the enum or set (modifying the definition of an ENUM or set column)

CREATE TABLE t1 (c1 ENUM('a', 'b', 'c'));ALTER TABLE t1 MODIFY COLUMN c1 ENUM('a', 'b', 'c', 'd'), ALGORITHM=INPLACE, LOCK=NONE;

The method is used to modify the value of an enumeration or collection, and in the case of adding an enumeration or collection value at the end, if the storage space does not change after the increment, the in place method can be used.

Conversely, if the storage space changes, such as from 2 bytes to three bytes, or in the middle of adding values, then you need COPY the way.

For scenarios where the number of values is indeterminate or the enumeration name changes, it is recommended to use tinyint instead ENUM or SET to store.

How to perform DDL modifications in practice

Combined with the above, three commonly used methods can be obtained.

Online DDL

Modify directly by executing the ALTER command. The applicable conditions are as follows:

• Small amount of data in table, less than millions
• Need mysql5.6+ above
• It takes less than an hour to endure a millions table that is not serviced for a long time.

Manually Modify frmFile

This method is suitable for unsupported Online DDL scenarios and can only perform Only Modifies Metadata partial DDL modifications. Here's how to modify it:

Find the MySQL data storage path First, which can be viewed from the process information:

# 查找mysql进程信息ps aux|grep mysql

Find the data store directory for the current database, and then CD to the frm table structure file directory you see to back up the files that need to be processed frm .

Create a similar data table in the database, modify the table, and then replace the table's frm file with the original table's frm file.

-- mysql中创建临时表create table tbl_temp like tbl; -- 修改临时表ALTER TABLE tblADD COLUMN `count`  bigint(20) NOT NULL DEFAULT 0 COMMENT '';-- 锁表flush tables with write lock; -- 备份源文件cp tbl.frm tbl.frm.bak# 替换数据结构文件frmcp tbl_temp.frm tbl.frm -- mysql移除读锁unlock tables; -- 测试修改是否成功select * from tbl limit 1; -- 如果出现错误，导致连接丢失等，可以回滚flush tables with write lock;cp tbl.frm.bak tbl.frmunlock tables;

Manually perform copy mode

Copy the temporary table, modify the temporary table, and then copy the data from the original table to the staging table, and switch between the temporary table and the original table.

When additional processing is required for the data in the original table, this mode can only be selected, which creates a large amount of disk IO and does not allow writing during execution.

For TENS tables, you can replicate in batches, using policies that allow for writes during migration.

Factors to consider when performing a modification

First you need to confirm the amount of table data executed, and if the amount of data exceeds millions or even tens, you need to check the following:

• Current system memory capacity is sufficient
• Current system memory usage is good
• Current system CPU Usage idle
• Whether to allow services to be stopped during modification
• Whether there are other databases associated with data consistency

If there are some missing points, you are welcome to add, so that you can also encounter similar problems in the future to provide reference.

The article has been synced to the personal blog: http://uusama.com/866.html, after two days of the other online DDL operation Test and Add. (a month did not write articles, already lazy like a lump of salted fish)