Shulou(Shulou.com)06/01 Report--

MySQL since 5.6.17, support online modification table structure operation (online ddl), that is, in the process of changing the table structure, do not block dml and dql operations.

Online ddls fall into two broad categories, depending on whether a table copy is required during the operation:

1. ddl operations requiring table copy:

Add, delete, rearrange.

Add or delete primary keys.

Change the ROW_FORMAT or KEY_BLOCK_SIZE properties of a table.

The null state of the changed field.

Perform OPTIMIZE TABLE to optimize the table.

Rebuild the table using the FORCE option.

Use ALTER TABLE... ENGINE=INNODB statement.

Create a full-text index for the first time.

2. ddl operations that do not require table copy:

Create, add, and delete regular indexes.

Create second and subsequent full-text indexes.

Set default values for fields.

Change the auto-increment value.

Delete foreign key constraints.

Add foreign key constraints (only if foreign_key_checks=off)

Just change the name of the column

Set persistent statistics options for tables (STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES)

Special note: full-text indexing requires special attention, creating a full-text index of the table basically does not support online ddl operations.

ySQL 5.6 Online DDL extends this feature to adding columns, deleting columns, modifying column types, renaming columns, setting defaults, and so on, depending on the options and types of actions used.

1.1 Online DDL options

MySQL online DDL is divided into INPLACE and COPY, which are specified by the ALGORITM parameter in the ALTER statement.

ALGORIFITHM =INPLACE, which avoids IO and CPU consumption caused by rebuilding the table and ensures good performance and concurrency during ddl.

ALGORIFITH M =COPY, need to copy the original table, so concurrent DML write operations are not allowed, readable. This copy method is not as efficient as inplace, because the former needs to record undo and redo log, and because the temporary occupation of buffer pool causes short-term performance impact.

The above is just an internal implementation of Online DDL, in addition to the LOCK option to control whether to lock the table, depending on the type of DDL operation has different behavior: the default mysql does not try to unlock the table, but expensive operations such as modifying the primary key have to choose to lock the table.

LOCK=NONE, that is, concurrent reading and writing of tables involved during DDL is allowed. For example, in order to ensure that ALTER TABLE does not affect user registration or payment, it can be explicitly specified. The advantage is that if unfortunately the alter statement does not support continuing to write to the table, it will prompt failure, and will not be directly sent to the library for execution. ALGORITHM=COPY Default LOCK level

LOCK=SHARED, i.e. writes to the table are blocked during DDL, but reads are not affected.

LOCK=DEFAULT, let mysql judge the mode of lock by itself, the principle is that mysql does not lock the table as much as possible

LOCK=EXCLUSIVE, i.e. the table is unavailable during DDL, blocking any read and write requests. If you want the alter operation to be completed in the shortest time possible, or if the table is unavailable for a short time, you can specify it manually.

However, it should be noted that in any mode, a short exclusive time is needed to prepare the environment before the online ddl starts, so after the alter command is issued, it will wait for other operations on the table to complete first, and requests after the alter command will appear waiting for meta data lock. Also waiting for all transactions to complete during the alter period before ddl ends will block for a short time. So try to make sure there are no large transactions executing before ALTER TABLE, otherwise there will be a chain lock table.

1.2 Consider different DDL operation categories

As can be seen from the above introduction, not 5.6 support online ddl can be arbitrary alter table, lock lock table depends on the situation:

Tip: The following table collates and selects common operations according to the official Summary of Online Status for DDL Operations.

In-Place is Yes is preferred, indicating that the operation supports INPLACE

Copies Table No is preferred because Yes requires rebuilding the table. Most of the time, In-Place is the opposite.

Allows Concurrent DML? Yes is a preferred option, indicating that the table is still readable and writable during ddl. LOCK=NONE can be specified (mysql is NONE automatically if the operation is allowed)

Allows Concurrent Query? Query requests are allowed by default during all DDL operations, and are placed here for reference only

Notes will explain the restrictions with * in the first few columns Yes/No

OperationIn-Place? Copies Table? Allows Concurrent DML? Allows Concurrent Query? Notes Add index Yes*No*YesYes Some restrictions on full-text indexing Delete index YesNoYesYes Modify table metadata only OPTIMIZE TABLEYesYesYes Use ALGORITM =INPLACE since 5.6.17, of course if old_alter_table=1 or mysqld boot tape--skip-new is specified it will still be COPY mode. If there is a full-text index on the table, only COPY is supported. Set default value for one column. YesNoYesYes modifies metadata of the table only. Modify auto-increment value for one column. YesNoYesYes modifies metadata of the table only. Add foreign key constraintYes*No*YesYes To avoid copying tables, foreign_key_checks are disabled when constraints are created. Delete foreign key constraintYesNoYesforeign_key_checks does not affect changing column names. Yes*No*Yes*Yes To allow DML concurrency, if the same data type is maintained. Change column names only Add columns Yes*Yes Although ALGORITM =INPLACE is allowed, the data is heavily reorganized, so it is still an expensive operation. DML concurrent deletion of columns is not allowed when adding columns is auto-increment YesYes*YesYes Although ALGORITM =INPLACE is allowed, the data is heavily reorganized, so it is still an expensive operation Modifying column data type NoYes*NoYes Modifying type or adding length copies the table, and update operations are not allowed Changing column order YesYesYes Although ALGORITM =INPLACE is allowed, the data is heavily reorganized, so it is still an expensive operation Modifying ROW_FORMAT

and KEY_BLOCK_SIZEYesYesYes Although ALGORITM =INPLACE is allowed, the data is heavily reorganized, so it is still an expensive operation to set column properties NULL

Or NOT NULLYesYesYes Although ALGORITM =INPLACE is allowed, the data is heavily reorganized, so it is still an expensive operation Add primary key Yes*YesYesYes Although ALGORITM =INPLACE is allowed, the data is heavily reorganized, so it is still an expensive operation.

INPLACE is not allowed to delete and add a primary key if the column definition must be converted to NOT NULL YesYesYes is allowed to delete an existing primary key and add a new primary key in the same ALTER TABLE statement, so it remains an expensive operation. Delete primary key NoYesNoYes Concurrent DML is not allowed, copy table, and receive restricted change table character set if primary key is not added in same ATLER TABLE statement NoYesNoYes If new character set encoding is different, rebuild table

As can be seen from the table, In-Place is No, DML must be No, indicating that ALGORITM =COPY must copy the table, read-only. But ALGORITM =INPLACEE may also copy tables, but concurrent DML:

Add, delete, and change column order

Add or delete primary keys

Change row format ROW_FORMAT and compressed block size KEY_BLOCK_SIZE

Change column NULL or NOT NULL

OPTIMIZE TABLE

Force rebuild the table

Concurrent DML is not allowed in the following cases: modifying column data types, deleting primary keys, and changing table character sets, that is, ddl operations of these types cannot be online.

In addition, changing the primary key index and ordinary index processing is different, the primary key is clustered index, reflecting the arrangement of table data on the physical disk, including the data row itself, need to copy the table; and ordinary index through the primary key column to locate the data, so the creation of ordinary index only needs to scan the primary key once, and is to establish a secondary index on the existing data table, more compact, future query efficiency is higher.

Changing the primary key also means rebuilding all the normal indexes. It is easier to delete the secondary index, modify the InnoDB system table information and data dictionary, mark it does not exist, and mark the table space occupied by it can be reused by the new index or data row.

MySQL5.6 Several ways to build index comparison.

mysql> select version();

+-----------+

| version() |

+-----------+

| 5.6.17 |

+-----------+

mysql> CREATE TABLE test AS SELECT * FROM information_schema.columns;

mysql> INSERT INTO test SELECT * FROM test;

mysql> INSERT INTO test SELECT * FROM test;

mysql> SELECT COUNT(1) FROM test;

+----------+

| COUNT(1) |

+----------+

| 312928 |

+----------+

1 row in set (0.17 sec)

ALGORIFITH M = in place, where modification operations can be performed directly on the table if allowed.

mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=inplace;

Query OK, 0 rows affected (1.54 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE test DROP INDEX ind_t_column_name;

Query OK, 0 rows affected (0.00 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> CREATE INDEX ind_t_column_name ON test(column_name);

Query OK, 0 rows affected (1.70 sec)

Records: 0 Duplicates: 0 Warnings: 0

ALGORITM =copy, used to identify whether the entire table is needed for the change operation.

mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=copy;

Query OK, 312928 rows affected (5.93 sec)

Records: 312928 Duplicates: 0 Warnings: 0

Note:

SET old_alter_table=0; --ALGORITHM=inplace

SET old_alter_table=1; --ALGORITHM=copy

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.