InnoDB modifies the table object structure online

InnoDB modifies the table object structure online

Modify the table object structure online:

Before MySQL5.6

1. Create a temporary table with the same structure as the original table object (invisible to users by implicit operations) and change the table structure to the expected structure.

2. Lock the original table. Only queries are allowed. modification is not allowed.

3. Copy the original table data to the newly created temporary table, similar to insert into new_tb select * from old_tb;

4. Rename the original table, change the new temporary table name to the formal table name, release the lock, and delete the original table.

After MySQL5.6, online DDL provides limited support for modifying InnoDB tables.

In-Place In Place indicates that the modification operation can be executed directly on the table object.

Copy the table Copies Tables, indicating that the entire table needs to be copied before modification can be performed.

You can use the LOCK and ALGORITHM clauses in the alter table statement to clearly control the operation behavior of online DDL statements. The LOCK clause is effective for fine-tuning the table parallel read control, while the ALGORITHM clause has a great impact on the performance and operation policies during operations.

LOCK has four option values:

DEFAULT: the DEFAULT processing policy, equivalent to not specifying the LOCK clause

NONE: no locking policy is used. Other sessions can both read and write.

SHARED: adopts the sharing policy. Other sessions are readable but not writable.

EXCLUSIVE: Take EXCLUSIVE lock, other sessions can neither read nor write

ALGORITHM has three option values:

DEFAULT: The ALGORITHM clause is not specified.

INPLACE: if it is supported, it is directly modified. If it is not supported, an error is returned.

COPY: whether or not local modification is supported, you can COPY the data in the table object to make modifications.

If you want the highest concurrency granularity, you must specify LOCK = NONE (readable and writable). If you want the lowest operation cost, you 'd better specify ALGORITHM = INPLACE (directly operate on the object, the minimum data volume involved in read/write operations)

 

Online DDL testing:

Log on to mysql and run the object creation script.

Use hugcdb;

Set autocommit = 0;

Create table t_idb_big as select * from information_schema.columns;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Alter table t_idb_big add id int unsigned not null primary key auto_increment;

Select count (*) from t_idb_big;

1. Test adding/deleting Indexes

INPLACE method is very efficient

Du-k/data/mysqldata/3306/data/hugcdb/t_idb_big.ibd

Alter table t_idb_big add index ind_data_type (data_type), algorithm = inplace;

Du-k/data/mysqldata/3306/data/hugcdb/t_idb_big.ibd

Alter table t_idb_big drop index ind_data_type, algorithm = inplace;

Low efficiency in using COPY

Create index ind_data_type on t_idb_big (data_type) alogorithm = copy;

Du-k/data/mysqldata/3306/data/hugcdb/t_idb_big.ibd

Drop index ind_data_type on t_idb_big alogorithm = copy;

2. Test DML operations during index addition/Deletion

Add Table Data

Alter table t_idb_big drop id;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Insert into t_idb_big select * from t_idb_big;

Alter table t_idb_big add id int unsigned not null primary key auto_increment;

First, test the traditional method of modifying the table structure and execute DDL statements in the first session.

Set old_alter_table = 1;

Create index ind_tablename on t_idb_big (table_name );

Perform the following operations in another session

Set autocommit = 0;

Use hugcdb;

Select count (*) from t_idb_big where table_name = 'files ';

Delete from t_idb_big where table_name = 'files ';

Rollback;

The statement is blocked.

Introduce the online DDL mode and execute it in the first session.

Set old_alter_table = 0;

Create index ind_tablename on t_idb_big (table_name) algorithm = inplace;

Perform the following operations in another session

Select count (*) from t_idb_big where table_name = 'files ';

Delete from t_idb_big where table_name = 'files ';

Rollback;

3. Test column Modification

Modify columns using the COPY Mechanism

Alter table t_idb_big change nullable is_unllable varchar (3), algorithm = copy;

Modify columns in online DDL Mode

Alter table t_idb_big change nullable is_unllable varchar (3), algorithm = inplace;

4. Test the auto-increment column modification function.

Traditional Modification

Alter table t_idb_big auto_increment = 1000000, algorithm = copy; very slow

Connection DDL Mode Modification

Alter table t_idb_big auto_increment = 1000000, algorithm = inplace; very fast

Not only do you not need to recreate the object, but you only need to modify the tag in the. frm file and the auto-increment value in the memory.

5. Test the LOCK clause to control parallel DML

Show processlist;

The ID column is used to identify the session, the Command column is used to identify the Command type specified by the session (for example, query, idle, etc.), and the State column identifies the current State of the session, the Info column identifies the operation currently executed by the session. If it is NULL, it indicates that the session is currently idle, focusing on the State column and Info column.

MySQL InnoDB Storage engine lock mechanism Experiment

Startup, shutdown, and restoration of the InnoDB Storage Engine

MySQL InnoDB independent tablespace Configuration

Architecture of MySQL Server layer and InnoDB Engine Layer

InnoDB deadlock Case Analysis

MySQL Innodb independent tablespace Configuration

This article permanently updates the link address: