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

1. ALter table (5.7)

In general, alter table will make a temporary copy of the original table, and then apply the table to the copy, and then delete the original table, rename copy. During this process, the original table is externally readable; however, the DML for that table is blocked until the alter is complete.

The exception referred to earlier is that ALTER TABLE blocks reads (not just writes) at the point where

It is ready to install a new version of the table .frm file, discard the old file, and clear outdated table

Structures from the table and table definition caches. At this point, it must acquire an exclusive lock. To

Do so, it waits for current readers to finish, and blocks new reads (and writes).

In the alter table read block, install the newly created. Frm. When discarding files and data, other locks will be added.

It is generally not necessary to create a temporary copy to do the following:

1) ALTER TABLE tbl_name RENAME TO new_tbl_name without any other options

2) when only the metadata of the table is changed without changing the data:

(1) renaming of columns

(2) change the default value of the column

(3) adding a list to the ENUM and SET columns at the end of the member list (but adding a member to the SET column with 8 members will change the required storage space from 1 byte to 2 bytes each; adding a member in the middle of the list will cause existing members to renumber, which require a copy of the table. )

3) discard and import tablespaces

4) rename index, add and delete index (for innodb and NDB)

2 Summary of Online Status for DDL Operations

(1) rename the column

Only change the column name without changing the column properties, you can operate online

The ALGORITHM=COPY option cannot be used for the column where the foreign key is modified.

(2) change the character length of the VARCHAR column

If the length defined by the original VARCHAR column is in the range of 0-255,you can use ALGORITHM=INPLACE if it is also in this range after modification.

If the length defined by the original VARCHAR column is more than 256, you can also use ALGORITHM=INPLACE if it is also in this range after modification.

However, if the range changes from less than 256 to greater than 256, or if the length of the varchar column changes from large to small, a copy of the table will be made.

(3) the following operations, although a copy of the table is made, DML is still supported

Adding, dropping, or reordering columns.

Adding or dropping a primary key.

Changing the ROW_FORMAT or KEY_BLOCK_SIZE properties for a table.

Changing the nullable status for a column.

OPTIMIZE TABLE

Rebuilding a table with the FORCE option

Rebuilding a table using a "null" ALTER TABLE... ENGINE=INNODB statement

Note: if you need to make a copy of the table, the copy of the table will be temporarily under the path specified by the parameter tmpdir. Any DDL statement waits for the end of the current transaction to start execution, because exclusive locks are briefly added at the beginning and the end of the DDL execution.

Although changes to the primary key require a copy of the table, the use of ALGORITHM=INPLACE is allowed and is more efficient than ALGORITHM=COPY. Because ALGORITHM=INPLACE does not need to record the corresponding undo and redo logs, the secondary index has been stored and can be sequentially load, and change buffer is not used because there is no random secondary index insertion.

(4) whether the DDL operation executes inplace or copy, the most intuitive representation is to view the "rows affected" after the completion of the operation, as follows:

(5) for the operation of a large table, you need to confirm the efficiency of the corresponding DDL:

1)。 Clone the table structure.

2)。 Populate the cloned table with a tiny amount of data.

3)。 Run the DDL operation on the cloned table.

4)。 Check whether the "rows affected" value is zero or not. A non-zero value means the operation willrequire rebuilding the entire table, which might require special planning. For example, you might do the DDL operation during a period of scheduled downtime, or on each replication slave server one at a time.

For a deeper understanding of the reduction in MySQL processing, examine the performance_schema and INFORMATION_SCHEMA tables related to InnoDB before and after DDL operations, to see the number of physical reads, writes, memory allocations, and so on.

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.