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

This article mainly introduces the detailed examples of MySQL & MariaDB Online DDL, which is very detailed and has a certain reference value. Friends who are interested must read it!

MySQL tutorials introduce and guide MySQL & MariaDB Online DDL.

Overview

In the early version of MySQL, DDL operations (such as creating indexes, etc.) usually need to lock the data table, and DML operations will be blocked during the operation, affecting the normal business. MySQL 5.6 and MariaDB 10.0 start to support Online DDL, which allows you to perform DDL operations without affecting the normal execution of DML, while directly performing DDL operations online is almost unaware of users (some operations affect performance).

There are some differences in the support of different versions of databases for various DDL statements. This article will make a summary of the support of MySQL and MariaDB for Online DDL. When you need to perform DDL operations, you can refer to the Online DDL support section of this article.

This article will continue to be revised and updated, the latest content please refer to my GITHUB programmer growth plan project, welcome Star, more wonderful content please follow me.

In ALTER TABLE statements, Online DDL is supported through ALGORITHM and LOCK statements:

ALGORITHM-controls how DDL operations are performed and which algorithm LOCK is used-controls the levels ALTER TABLE tab ADD COLUMN c varchar (50), ALGORITHM=INPLACE, LOCK=NONE that are allowed to lock tables when DDL is executed Copy code ALGORITHM support algorithm ALGORITHM description DEFAULT default algorithm, automatically use the most efficient algorithm available COPY the most primitive way, all storage engines support, do not use Online DDL, operation will create temporary tables, perform full table copy and reconstruction, the process will be written to Redo Log and a large number of Undo Log, need to add read locks, very inefficient INPLACE as far as possible to avoid table copy and reconstruction, the more accurate name should be ENGINE algorithm It is up to the storage engine to decide how to implement it. Some operations can take effect immediately (such as renaming columns, changing the default values of columns, etc.), but some operations still require copying and rebuilding of all or part of the table (such as adding deleted columns, adding primary keys, changing columns, etc.) NOCOPY. This algorithm is a subset of the INPLACE algorithm and is used to avoid full table reconstruction caused by the reconstruction of clustered indexes (primary key indexes). That is to say, using this algorithm will prohibit any operation that causes clustered index reconstruction INSTANT to avoid the extremely inefficient problem of INPLACE algorithm when the data file needs to be modified, and all operations involving table copy and reconstruction will be prohibited.

NOCOPY algorithm is supported: MariaDB 10.3.2 does not support this algorithm.

INSTANT algorithm supports: MariaDB 10.3.2 MySQL 8.0.12 +.

The rules used by the algorithm:

If the algorithm specified by the user is COPY, InnoDB uses the COPY algorithm. If the user specifies an algorithm other than COPY, InnoDB will choose the most efficient algorithm according to the algorithm efficiency, and use the user-specified algorithm in the worst case. For example, if the user specifies ALOGRITHM = NOCOPY, InnoDB will choose the most efficient algorithm supported from (NOCOPY, INSTANT).

MySQL service is mainly composed of Server layer and storage engine layer. Server layer contains most of the core functions of MySQL, all built-in functions, cross-storage engine functions such as stored procedures, triggers, views and so on. The storage engine layer is responsible for data storage and reading, and adopts the plug-in architecture mode.

The COPY algorithm works in the Server layer, and its execution process is in the Server layer, so all storage engines support the use of this algorithm, as shown in the figure below.

The INPLACE algorithm acts on the storage engine layer and is a unique DDL algorithm of the InnoDB storage engine. The execution process is shown in the following figure

LOCK strategy

By default, MySQL/MariaDB uses as few locks as possible during DDL, and if necessary, you can control the level at which locks on the table are allowed when DDL is executed through the LOCK clause. If the specified operation requires a restriction level that does not meet (EXCLUSIVE > SHARED > NONE), the statement execution fails with an error.

The policy states that DEFAULT uses the lock policy with the smallest granularity supported by the current operation, NONE does not acquire any table locks, allows all DML operations SHARED to add shared locks (read locks) to tables, only allows read-only DML operations EXCLUSIVE to add exclusive locks (write locks) to tables, and does not allow any DML operations

In order to avoid unavailable production service due to locking table when executing DDL, you can add LOCK=NONE clause when executing table structure change statement. If the statement needs to acquire shared lock or exclusive lock, it will directly report an error. In this way, accidental locking of the table can be avoided, resulting in unavailable online services.

Online DDL execution process

The Online DDL operation is mainly divided into three phases:

Phase 1: initialize

During the initialization phase, the server determines how much concurrency is allowed based on the capabilities of the storage engine, the statements to operate, and the ALGORITHM and LOCK options specified by the user. At this stage, a scalable metadata sharing lock (SU) is created to protect the table definition.

Phase 2: execution

This phase prepares and executes the DDL statement to decide whether to upgrade the metadata lock to an exclusive lock (X) based on the results of the phase 1 evaluation, and if it needs to be upgraded to an exclusive lock, add exclusive locks only briefly in the preparation phase of the DDL.

Phase 3: submit table definition

During the commit phase of the table definition, the metadata lock is upgraded to an exclusive lock to update the table definition. The duration of exclusive exclusive locks is very short.

Metadata lock (MDL,Metadata Lock) is mainly used for concurrent access control between DDL and DML operations to protect the consistency of table structure (table definition) and ensure the correctness of reading and writing. MDL does not need to be used explicitly and is automatically added when accessing the table.

Due to the monopoly of the metadata lock in the above three phases, the Online DDL process must wait for a concurrent transaction that already holds the metadata lock to commit or roll back before it can continue.

Note: when an Online DDL operation is waiting for a metadata lock, the metadata lock will be suspended and all subsequent transactions will be blocked. After MariaDB 10.3, the timeout for waiting can be controlled by adding NO WAIT or WAIT n, and the timeout fails immediately.

ALTER TABLE tbl_name [WAIT n | NOWAIT]... CREATE... INDEX ON tbl_name (index_col_name,...) [WAIT n | NOWAIT]... DROP INDEX... [WAIT n | NOWAIT] DROP TABLE tbl_name [WAIT n | NOWAIT]... LOCK TABLE... [WAIT n | NOWAIT] OPTIMIZE TABLE tbl_name [WAIT n | NOWAIT] RENAME TABLE tbl_name [WAIT n | NOWAIT]... SELECT. FOR UPDATE [WAIT n | NOWAIT] SELECT... LOCK IN SHARE MODE [WAIT n | NOWAIT] TRUNCATE TABLE tbl_name [WAIT n | NOWAIT] copy code to evaluate the performance of Online DDL operations

The performance of the Online DDL operation depends on whether the table is rebuilt or not. Before performing DDL operations on large tables, in order to avoid affecting normal business operations, it is best to evaluate the performance of DDL statements before choosing how to operate.

Copy the table structure, create a new table, insert a small amount of data into the newly created table, perform a DDL operation on the new table to check whether the rows affected returned after the operation is 0. If the value is not 0, it means that table data needs to be copied. At this time, the launch of DDL needs to be carefully considered and carefully planned.

such as

Modify the default value of a column (fast and do not affect the table data)

Query OK, 0 rows affected (0.07 sec) copy code

Add an index (it will take some time, but 0 rows affected means no table copy has occurred)

Query OK, 0 rows affected (21.42 sec) copy code

Change the data type of the column (it takes a long time and rebuild the table)

Query OK, 1671168 rows affected (1 min 35.54 sec) copy code

Because changes to concurrent DML operations need to be recorded during Online DDL execution, and then applied after the DDL operation is performed, it takes a little longer to perform with Online DDL operations than without Online mode.

Online DDL support

INSTANT algorithm supports: MariaDB 10.3.2 MySQL 8.0.12 +. NOCOPY only supports MariaDB 10.3.2 and above, but does not support MySQL, so it will be ignored here.

Focus on rebuilding tables and supporting concurrent DML: there is no need to rebuild tables, it is best to support concurrent DML.

Secondary index operation INSTANTINPLACE rebuild table concurrent DML only modify metadata creation or add secondary index ❌✅❌✅❌ delete index ❌✅❌✅✅ rename index (⚠️ MySQL 5.7 ❌✅ MariaDB 10.5.2+) ❌✅❌✅✅ add FULLTEXT index ❌✅ ①❌ ①❌❌ add SPATIAL index (⚠️ MySQL 5.7) MariaDB 10.2.2+ ❌✅❌❌❌ modify index type ✅✅❌✅✅

Description:

When ① adds a full-text index field for the first time, it needs to rebuild the table. After that, there is no need for primary key operation INSTANTINPLACE rebuild table concurrent DML only modify metadata add primary key ❌✅ ②✅ ②✅❌ delete primary key ❌❌✅❌❌ delete a primary key and add a new ❌✅✅✅❌

Description:

Rebuilding a clustered index always requires copying table data (InnoDB is "index organization table"), so it is best to define the primary key when creating the table. If the table is created without a primary key, InnoDB will choose the UNIQUE index of the first NOT NULL as the primary key, or use the system-generated KEY ②. For clustered indexes, using INPLACE mode is more efficient than COPY mode: no undo log and redo log are generated, and secondary indexes are ordered. So it can be loaded sequentially. Do not need to use change buffer normal column operation INSTANTINPLACE rebuild table concurrent DML only modify metadata column add ✅ ③✅❌ ③✅ ③❌ column delete ❌ ④✅✅✅❌ column rename ❌✅❌✅ ⑤✅ change column order ❌ ⑫✅✅✅❌ setting default ✅✅❌✅✅ modify data type ❌❌✅❌❌ extended VARCHAR length (⚠️ MySQL 5.7 + MariaDB 10.2.2+) ❌ ⑬✅❌ ⑥✅✅ delete column default ✅✅❌✅✅ change since value-added ❌✅❌✅❌ ⑦ setting column NULL ❌✅✅ ⑧✅❌ setting column NOT NULL ❌✅ ⑨✅ ⑨✅❌ modify definition of ENUM and SET column ✅✅❌ ⑩✅✅

Description:

③ concurrent DML: when inserting a self-incrementing column, concurrent DML operations are not supported. When adding a self-increment column, a large amount of data will be reorganized, which is costly.

③ rebuild table: when adding columns, MySQL 5.7 and earlier versions need to rebuild tables, MySQL 8.0 need to rebuild tables when ALGORITHM=INPLACE, but do not need to rebuild when ALGORITHM=INSTANT

③ INSTANT algorithm: when adding columns, using the INSTANT algorithm has the following limitations

Add column operation cannot be combined with other operations that do not support INSTANT algorithm into one ALTER TABLE statement. New columns can only be added to the end of the table, not in front of other columns. After MariaDB 10.4, columns cannot be added anywhere in a table that cannot be added to ROW_FORMAT=COMPRESSED. Columns cannot be added to tables that contain FULLTEXT. Columns cannot be added to temporary tables. Temporary tables only support ALGORITHM=COPY. Columns cannot be added to tables that reside in the data dictionary table space. Row size limits are not calculated when columns are added, and are checked only when DML operations are performed to insert or update the table.

When ④ deletes columns, a large amount of data needs to be reorganized, which is costly. After MariaDB 10.4, deleting columns supports the INSTANT algorithm.

When ⑤ renames a column, make sure that only the column name is changed, not the data type, so as to support concurrent DML operations.

When ⑥ extends the length of VARCHAR, INPLACE is conditional, and the length bytes used to identify the length of the string must be kept unchanged. (all the bytes mentioned here are bytes, not the character length of VARCHAR. The byte occupation is related to the character set used. Under the utf8 character set, one character accounts for 3 bytes, while utf8mb4 accounts for 4 bytes).

The length identifier occupies one byte when the length of the VARCHAR column is between 0 and 255bytes. When the length of the VARCHAR column is greater than 255bytes, the length identifier takes up two bytes.

Therefore, INPLACE only supports changes between 0 and 255 bytes or between 256 bytes and a larger length. INPLACE is not supported for VARCHAR column length reduction.

⑦ self-increment value change is a modified in-memory value, not a data file

When ⑧ ⑨ is listed as [NOT] NULL, a large amount of data is reorganized, which is costly

When ⑩ modifies column definitions of ENUM and SET types, whether a table copy is required depends on the number of existing elements and the location of the inserted members.

⑫ supports INSTANT algorithm for column sorting after MariaDB 10.4.

⑬ after MariaDB 10.4.3, InnoDB supports the use of INSTANT algorithm to increase the length of columns, but there are some limitations. For more information, please see Changing the Data Type of a Column.

Generate column operation INSTANTINPLACE rebuild table concurrent DML only modify metadata add STORED column ❌❌✅❌❌ modify sorting of STORED column ❌❌✅❌❌ delete STORED column ❌✅✅✅❌ add VIRTUAL column ✅✅❌✅✅ modify sorting of VIRTUAL column ✅❌✅❌❌ delete VIRTUAL column ✅✅❌✅✅ foreign key operation INSTANTINPLACE rebuild table concurrent DML only modify metadata add foreign key constraint ❌✅ ⑭❌✅✅ delete foreign key constraint ❌✅❌✅✅

Description:

When ⑭ adds a foreign key Only when the foreign_key_checks option is disabled is supported INPLACE algorithm table operation INSTANTINPLACE rebuild table concurrent DML only modify metadata modify ROW_FORMAT ❌✅✅✅❌ modify KEY_BLOCK_SIZE ❌✅✅✅❌ settings persistence table statistics ❌✅❌✅✅ specified character set ❌✅✅ ⑮❌❌ conversion character set ❌❌✅ ⑯❌❌ optimization table ❌✅ ⑰✅✅❌ use the FORCE option to rebuild the table ❌✅ ⑱ ✅✅❌ performs an empty rebuild ❌✅ ⑲✅✅❌ rename table ✅✅❌✅✅

Description:

⑮⑯ when the character set is different, you need to rebuild the table ⑰⑱⑲ if the table contains fields of FULLTEXT Do not support INPLACE tablespace operations INSTANTINPLACE rebuild table concurrent DML only modify metadata rename regular tablespace ❌✅❌✅✅ enable or disable regular tablespace encryption ❌✅❌✅❌ enable or disable file-per-table tablespace encryption ❌❌✅❌❌ limit table copy occurs when indexes are created on temporary table TEMPORARY TABLE if there are ON...CASCADE or ON...SET NULL constraints on the table Then ALERT TABLE does not support the phrase LOCK=NONE before the Onlne DDL operation is completed, it must wait for the transaction of the related table that already holds the metadata lock to be committed or rolled back, in the process, the new transaction of the related table will be blocked and cannot be executed. When DDL involving table reconstruction is executed on a large table, there are the following restrictions. There is no mechanism to pause the Online DDL operation or limit the Online DDL operation Icano or CPU utilization if the operation fails. It is very expensive to roll back the Online DDL operation. Long-running Online DDL can cause replication delays. The Online DDL operation must be completed on the Master before it can be executed on the Slave. In this process, the concurrently processed DML on the Slave must wait for the DDL operation to complete before it is executed. The above is all the contents of the detailed explanation example of MySQL & MariaDB Online DDL, thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.