Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the MySQL Online DDL knowledge points". In the daily operation, I believe many people have doubts about the MySQL Online DDL knowledge points. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "what are the MySQL Online DDL knowledge points?" Next, please follow the editor to study!

First, the complex process of the initial alter.

Before MySQL 5.5, except for the innodb plugin of MySQL 5.1, the operation procedure of MySQL database is as follows for DDL operations such as adding or deleting indexes:

(1) first create a new temporary table. The table structure is defined by the command ALTAR TABLE.

(2) then import the data from the original table into the temporary table (unable to read and write)

(3) Delete the original table

(4) finally rename the temporary table to the original table name

In the above process, it is not difficult to find that it takes a long time to add or delete an index to a large table. the fatal thing is that if there are a large number of access requests, it means that services cannot be provided. 5.5 is out of date. Let's pay more attention to 5.6 and 5.7.

Second, fast index creation:

It is stated in the official document

In MySQL 5.5 and higher, or in MySQL 5.1 with the InnoDB Plugin, creating and dropping secondary indexes does not copy the contents of the entire table, making this operation much more efficient than with prior releases.

In mysql 5.5 or later, or InnoDB Plugin in mysql 5.1, creating and deleting secondary indexes no longer need to copy the data of the entire table to create temporary tables, and such operations become more efficient than previous versions

The innodb storage engine has supported Fast index Creation (fast index creation) since version 1.0.x. FIC for short. For secondary index creation, an S lock is added to the table that created the index. During the creation process, there is no need to rebuild the table, so the speed is significantly improved. To delete the secondary index, the innodb storage engine only needs to update the internal view, mark the space of the secondary index as available, and delete the index definition on the table on the internal view of the MySQL database. Because the S lock is added when the secondary index is created, the table can only be read during this process, and if there is a transaction that needs to write to the table, the database service is also unavailable. It should be noted that the FIC method is limited to secondary indexes, and a table needs to be rebuilt for the creation and deletion of primary keys; there is no difference between fast index creation statements and normal alter statements

Three: online ddl (note that it is for the innodb engine)

In mysql 5.6 and later, for most of our daily DDL, it is possible to do online DDL.

In general, you can use the default syntax for online DDL, but you can also change the behavior of DDL through options. There are two options

LOCK=

ALGORITHM= [input | COPY]

An introduction to these two parameters:

ALGORITHM=INPLACE, which can avoid the IO and CPU consumption caused by rebuilding the table, and ensure good performance and concurrency during ddl.

ALGORITHM=COPY, you need to copy the original table, so concurrent DML writes are not allowed and can be read. This copy approach is not as efficient as inplace because the former needs to record undo and redo log, and performance is affected in a short period of time because of the temporary occupation of buffer pool.

The LOCK option controls whether to lock the table, which varies according to different types of DDL operations: the default mysql does not lock the table as much as possible, but expensive operations such as modifying the primary key have to choose to lock the table. Here is the meaning of the specific values:

(1) NONE, when performing index creation or deletion operations, no locks are added to the target table, that is, transactions can still read and write without blocking, and this mode can achieve maximum concurrency.

(2) SHARE, similar to Fast index Creation, adds an S lock to the target table when an index creation or deletion operation is performed. For concurrent read transactions, it can still be executed. However, when a write transaction is encountered, a wait operation will occur, and an error message will be returned if the storage engine does not support SHARE mode.

(3) EXCLUSIVE, which adds an X lock to the target table when index creation or deletion is performed. Neither read nor write transaction can be carried out. Will block all threads. This is similar to the COPY approach, but there is no need to create a temporary table like the COPY approach.

(4) DEFAULT, this mode first determines whether the current operation can use NONE mode, if not, determines whether SHARE mode can be used, and finally determines whether EXCLUSIVE mode can be used. That is, DEFAULT determines the mode in which DDL is executed by determining the maximum concurrency of the transaction.

The statement of online ddl:

Alter table | ALGORITHM [=] {DEFAULT | INPLACE | COPY}, | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT} | CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST | AFTER col_name] | LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE} | MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name]

So how do I turn on online ddl?

Controlled by parameter old_alter_table

Old_alter_table=0, do not enable the old copy the table mode for ddl operation

The default old_alter_table=0 of mysql 5.6enables online ddl, and you can use the default syntax to perform online DDL.

(digression: about the difference between set old_alter_table=0; and set global old_alter_table=0;. The former only affects the current session, while the latter, as a global modification mode, will only affect the session; opened after the modification. Note that the latter cannot change the current session;)

Experiment 1:

1.1

Session 1

Mysql > set old_alter_table=1

Query OK, 0 rows affected (0.45 sec)

Mysql > show variables like 'old_alter_table'

+-+ +

| | Variable_name | Value |

+-+ +

| | old_alter_table | ON |

+-+ +

1 row in set (0.00 sec)

Mysql > alter table v_member_info add index inde_register (register_ip)

Session 2 performs a dml operation and is blocked.

Mysql > update v_member_info set phone='1771002222' where id=1

Looking at the process, it is found that the old copy the table mode is used for the ddl operation, and then the update operation cannot be performed, waiting for a metadata lock

Mysql > show processlist

+-+-- + -- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- + -- +

| | 5 | root | localhost | liuwenhe | Query | 107 | copy to tmp table | alter table v_member_info modify register_ip varchar (50) |

| | 6 | root | localhost | liuwenhe | Query | 17 | Waiting for table metadata lock | update v_member_info set phone='1771002222' where id=1 | |

| | 8 | root | localhost | NULL | Query | 0 | init | show processlist |

1.2

Session 1

Mysql > set old_alter_table=0

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'old_alter_table'

+-+ +

| | Variable_name | Value |

+-+ +

| | old_alter_table | OFF |

+-+ +

1 row in set (0.15 sec)

Mysql > alter table v_member_info add index inde_register (register_ip)

Query OK, 0 rows affected, 2 warnings (13.42 sec)

Records: 0 Duplicates: 0 Warnings: 2

Session 2 performs the dml operation and is not blocked

Mysql > update v_member_info set phone='1771002222' where id=1

Query OK, 0 rows affected (0.02 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Mysql > show processlist

+- -+

| | Id | User | Host | db | Command | Time | State | Info |

+- -+

| | 8 | root | localhost | NULL | Query | 0 | init | show processlist | |

| | 14 | root | localhost | liuwenhe | Query | 9 | altering table | alter table v_member_info add index inde_register (register_ip) |

| | 18 | root | localhost | liuwenhe | Sleep | 6 | | NULL |

+- -+

3 rows in set (0.07 sec)

The first experiment shows that when old_alter_table=0, it means that the old copy the table mode is not used for ddl operation, that is, online ddl is turned on. And when online ddl is enabled, the normal alter command to add an index will not block the dml operation. Because there is no need to create temporary tables, online ddl is very efficient

Experiment 2: when old_alter_table=1, is ALGORITHM=INPLACE still valid?

Mysql > set old_alter_table=1

Query OK, 0 rows affected (0.00 sec)

Mysql > show variables like 'old_alter_table'

+-+ +

| | Variable_name | Value |

+-+ +

| | old_alter_table | ON |

+-+ +

1 row in set (0.00 sec)

Session 1: notice that the parameter ALGORITHM = INPLACE is followed by a comma.

Mysql > alter table v_member_info ALGORITHM = INPLACE,add index inde_register (register_ip)

Session 2 does not block dml operations

Mysql > update v_member_info set phone='1771002222' where id=1

Query OK, 0 rows affected (0.40 sec)

Rows matched: 1 Changed: 0 Warnings: 0

Look at the process and find that ddl is not executed in the same way as copy temp table

Mysql > show processlist

+- -- +

| | Id | User | Host | db | Command | Time | State | Info |

+- -- +

| | 20 | root | localhost | NULL | Sleep | 5053 | | NULL |

| | 21 | root | localhost | liuwenhe | Query | 2 | altering table | alter table v_member_info ALGORITHM = INPLACE, add index inde_register (register_ip) |

| | 23 | root | localhost | NULL | Query | 0 | init | show processlist | |

+- -- +

3 rows in set (0.00 sec)

Experiment 2: the results show that in mysql5.6, when old_alter_table=1, ALGORITHM=INPLACE can be used to influence the execution mode of ddl, that is to say, the parameter of ALGORITHM=INPLACE has high priority, and the index is still created in the way of online ddl, and no temporary table is created (although old_alter_table=1). The lock parameter must be the same, and Lab 2 itself doesn't make much sense, because no one will set old_alter_table to 1 instead of the new online ddl feature.

Experiment 3:

Session 1 opens online ddl, and then executes the operation of adding indexes in the way of copy

Mysql > set old_alter_table=0

Query OK, 0 rows affected (0.04 sec)

Mysql > show variables like 'old_alter_table'

+-+ +

| | Variable_name | Value |

+-+ +

| | old_alter_table | OFF |

+-+ +

1 row in set (0.03 sec)

Mysql > alter table v_member_info ALGORITHM = copy, add index inde_register (register_date)

Session 2 blocking dml operation

Mysql > update v_member_info set phone='1771002222' where id=1

Mysql > show processlist; check the process and find that it is indeed waiting for Waiting for table metadata lock

+-+-- + -- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- + -- +

| | 28 | root | localhost | liuwenhe | Query | 60 | copy to tmp table | alter table v_member_info ALGORITHM = copy, add index inde_register (register_date) |

| | 29 | root | localhost | liuwenhe | Query | 20 | Waiting for table metadata lock | update v_member_info set phone='1771002222' where id=1 |

| | 30 | root | localhost | NULL | Query | 0 | init | show processlist | |

+-+-- + -- +

3 rows in set (0.06 sec)

Experiment 3 proves that after opening online ddl, you can also use copy to tmp table to create indexes, which will still block other dml operations. But there shouldn't be such a boring dba.

Experiment 4: verify that the myisam engine can online ddl

Session 1

Mysql > alter table v_member_info engine=myisam

Query OK, 1804082 rows affected (1 min 50.33 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

Mysql > set old_alter_table=0

Query OK, 0 rows affected (0.03 sec)

Mysql > alter table v_member_info add index inde_register (register_ip)

Query OK, 1804082 rows affected (1 min 57.77 sec)

Records: 1804082 Duplicates: 0 Warnings: 0

Session 2 is blocked

Mysql > update v_member_info set phone='1771002222' where id=1

Session 3 looks at the process status and sees the process of adding an index, just starting with copy to tmp table (creating temporary tables)

Mysql > show processlist

+-+-- + -- +

| | Id | User | Host | db | Command | Time | State | Info |

+-+-- + -- +

| | 36 | root | localhost | liuwenhe | Query | 7 | copy to tmp table | alter table v_member_info add index inde_register (register_ip) |

| | 37 | root | localhost | liuwenhe | Query | 5 | Waiting for table metadata lock | update v_member_info set phone='1771002222' where id=1 | |

| | 38 | root | localhost | NULL | Query | 0 | init | show processlist | |

+-+-- + -- +

3 rows in set (0.00 sec)

Experiment 4 proves that the myisam engine in mysql 5.6does not support online ddl, and adding indexes will still create temporary tables and block dml operations of other session.

On the principle level of online ddl:

The principle of innodb storage engine to implement Online DDL is to write DML operation logs such as INSERT,UPDATE,DELETE to a cache while performing creation or deletion operations, and then apply redo to the table after index creation, so as to achieve data consistency. The size of this cache is controlled by the parameter innodb_online_alter_log_max_size, and the default size is 128MB.

It should be noted that if the table to be updated is large and there are a large number of write transactions during the creation process, an error will be thrown if there is no space for logs in innodb_online_alter_log_max_size. If we encounter this error, we can increase this parameter to get more log cache space, or we can set the lock mode of ALTER TABLE to SHARE. In this way, no write transactions occur during execution. Therefore, there is no need to record the DML log.

However, it is important to note that in any mode, an exclusive lock (exclusive) is required to prepare the environment before online ddl starts, so after the alter command is issued, it will first wait for other operations on the table to complete, and requests after the alter command will wait for waiting meta data lock. Similarly, before the end of the ddl, you have to wait for all transactions to be completed during the alter, which will be blocked for a short period of time. So try to make sure there are no big transactions before ALTER TABLE, or there will be chained tables as well. You can determine whether it is IN-PLACE or COPY data by observing the output after DDL: XX rows affected. If it is 0, it is inplace.

The specific process of copy and inplace (adding index as an example)

Copy mode

(1)。 Create a new temporary table with index

(2)。 Lock the original table, disable DML, allow query

(3)。 Copy the original table data to a temporary table (no sort, one-line copy)

(4)。 Perform rename, upgrade dictionary locks, and prohibit reading and writing

(5)。 Complete the index creation operation

Inplace mode (the whole process is relatively fast)

(1)。 Create a data dictionary for the index

(2)。 Lock tables, disable DML, allow queries (this process is very short)

(3)。 Read clustered indexes, construct new index entries, sort and insert new indexes

(4)。 Waiting for all read-only transactions that open the current table to commit

(5)。 End of index creation

Online ddl implementation

In essence, online mode also includes copy and inplace methods. Ddl operations that do not support online use copy mode, such as modifying column types, deleting primary keys, modifying character sets, and so on. These operations will lead to changes in record format, and online cannot be implemented in a simple full + incremental way. For inplace, mysql is also divided into two categories based on "whether to modify the record format", one needs to rebuild the table (reorganize records), such as optimize table, add index, add / delete columns, modify column NULL/NOT NULL attributes, etc.; the other is only need to modify the metadata of the table, such as delete index, modify column name, modify column default values, modify column self-increment, and so on. Mysql calls these two kinds of methods rebuild mode and no-rebuild mode respectively.

two。 Implementation process (note that rebuild here refers to the reorganization of records, as opposed to modifying only the metadata of the table)

Online ddl mainly includes three stages: prepare stage, ddl execution stage, commit stage, rebuild (reorganize records) mode has essentially more ddl execution phase than no-rebuild (only need to modify table metadata), and prepare phase is similar to commit phase. The following will focus on the process of the three stages of ddl execution.

Prepare phase:

1) create a new temporary frm file (independent of InnoDB)

2) hold the EXCLUSIVE-MDL lock and prohibit reading and writing

3) determine the execution mode (copy,online-rebuild,online-norebuild) according to the type of alter

If it is Add Index, then choose online-norebuild or INPLACE mode.

1) Update the memory object of the data dictionary

2) assign row_log object record increments (only required by rebuild type)

3) generate a new temporary ibd file (required only for rebuild type)

Ddl execution phase:

1) downgrade the EXCLUSIVE-MDL lock to allow reading and writing

2) scan each record rec of old_table 's clustered index

3) traverse the clustered index and secondary index of the new table and process them one by one

4) construct the corresponding index entries according to rec

5) insert the construction index entry into the sort_buffer block sort

6) Update the sort_buffer block to the new index

7) record the increments generated during ddl execution (only required by the rebuild type)

8) replay the operations in row_log to the new index (no-rebuild data is updated on the original table)

9) generate dml operation append to the last Block of row_log between playback row_log

Commit phase:

1) when the current Block is the last row_log, disable reading and writing, and upgrade to EXCLUSIVE-MDL lock

2) redo the last part of the increment in row_log

3) update the data dictionary table of innodb

4) commit the transaction (swipe the redo log of the transaction)

5) modify statistics

6) rename temporary idb file, frm file

7) change completed

From the official table, there are still many operations that do not support complete online DDL, including adding a full-text index, changing the data type of the column, deleting a primary key, modifying the character set of the table, and so on.

OperationIn-Place? Do you want to rebuild the table to allow concurrent DML to modify only metadata? NotesCREATE INDEX, ADD INDEXYes*No*YesNoRestrictions apply for FULLTEXT indexes; see next row.ADD FULLTEXT INDEXYes*No*NoNoAdding the first FULLTEXT index rebuilds the table if there is no user-defined FTS_DOC_ID column. Subsequent FULLTEXT indexes may be added on the same table without rebuilding the table.DROP INDEXYesNoYesYesOnly modifies table metadata.OPTIMIZE TABLEYes*YesYesNoPerformed in-place as of MySQL 5.6.17. In-place operation is not supported for tables with FULLTEXTindexes.Set column default valueYesNoYesYesOnly modifies table metadata.Change auto-increment valueYesNoYesNo*Modifies a value stored in memory, not the data file.Add foreign key constraintYes*NoYesYesThe INPLACE algorithm is supported when foreign_key_checks is disabled. Otherwise, only the COPY algorithm is supported.Drop foreign key constraintYesNoYesYesforeign_key_checks can be enabled or disabled.Rename columnYesNoYes*YesTo permit concurrent DML, keep the same data type and only change the column name.Add columnYesYesYes*NoConcurrent DML is not permitted when adding an auto-increment column. Data is reorganized substantially, making it an expensive operation.Drop columnYesYesYesNoData is reorganized substantially, making it an expensive operation.Reorder columnsYesYesYesNoData is reorganized substantially, making it an expensive operation.Change ROW_FORMATpropertyYesYesYesNoData is reorganized substantially, making it an expensive operation.Change KEY_BLOCK_SIZEpropertyYesYesYesNoData is reorganized substantially, making it an expensive operation.Make column NULLYesYes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation.Make column NOT NULLYes*Yes*YesNoRebuilds the table in place. STRICT_ALL_TABLES or STRICT_TRANS_TABLES SQL_MODE is required for the operation to succeed. The operation fails if the column contains NULL values. As of 5.6.7, the server prohibits changes to foreign key columns that have the potential to cause loss of referential integrity. See Section 13.1.7, "ALTER TABLE Syntax". Data is reorganized substantially, making it an expensive operation.Change column data typeNoYesNoNoOnly supports ALGORITHM=COPYAdd primary keyYes*Yes*YesNoRebuilds the table in place. Data is reorganized substantially, making it an expensive operation.ALGORITHM=INPLACE is not permitted under certain conditions if columns have to be converted to NOT NULL.Drop primary key and add anotherYesYesYesNoData is reorganized substantially, making it an expensive operation.Drop primary keyNoYesNoNoOnly ALGORITHM=COPY supports dropping a primary key without adding a new one in the same ALTER TABLE statement.Convert character setNoYes*NoNoRebuilds the table if the new character encoding is different.Specify character setNoYes*NoNoRebuilds the table if the new character encoding is different.Rebuild with FORCE optionYes*YesYesNoUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes. "null" rebuild using ALTER TABLE. ENGINE=INNODBYes*YesYesNoUses ALGORITHM=INPLACE as of MySQL 5.6.17. ALGORITHM=INPLACE is not supported for tables with FULLTEXT indexes.Set STATS_PERSISTENT,STATS_AUTO_RECALC,STATS_SAMPLE_PAGESpersistent statistics optionsYesNoYesYesOnly modifies table metadata.

It can be seen from the table that In-Place must be No,DML and No, which means that a copy of the table must occur in ALGORITHM=COPY and is read-only.

It is also possible for ALGORITHM=INPLACEE to copy tables, but it can be concurrent with DML:

Add and delete columns, change the order of columns

Add or remove primary key

Change row format ROW_FORMAT and compression block size KEY_BLOCK_SIZE

Change the column NULL or NOT NULL

Optimized table OPTIMIZE TABLE

Force rebuild the table

Concurrency DML is not allowed in situations where column data types are modified, primary keys are deleted, and table character sets are changed, that is, these types of operations on ddl cannot be online.

In addition, changing the primary key index is different from the ordinary index. The primary key is the clustered index, which reflects the arrangement of the table data on the physical disk, including the data row itself, and the table needs to be copied. The ordinary index locates the data by including the primary key column, so the creation of the ordinary index only needs to scan the primary key once, and the secondary index is established on the table of the existing data, which is more compact and more efficient in the future.

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

At this point, the study of "what are the knowledge points of MySQL Online DDL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.