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.
INPLACE
Detailed 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:
Operation |
| in place
Rebuilds Table |
permits Concurrent DML | Only
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
frm
File
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)