preface

Table structure changes are a common requirement in daily DBA operations and maintenance. Common new columns, a new index, change the type of column, such as operation, if the operation of the object is a hot table, table, hard to avoid in the mind a afraid, whether the DDL operations can be executed directly, which will affect online reading and writing, which will affect the master-slave, even cause the server pressure surge, this article made a comb, hope to be of help.

The body of the

Online DDL was only supported in MySQL 5.6. In MySQL 5.5 and earlier, using alter TABLE/CREATE index commands to modify the table structure would lock the table, which is obviously unacceptable in production environments.

In MySQL 5.7, Online DDL has been continuously optimized in terms of performance and stability, with significant performance advantages, small impact on business load, controllable write stop time, compared with PT-OSC/GH-OST, there is no need to install third-party dependency packages, and Online DDL with Inplace algorithm is supported. Less disk space is required because there is no copying of tables.

Let’s start with a common DDL statement:

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
Copy the code

LOCK describes the degree of concurrency during DDL, ALGORITHM describes the implementation of DDL

The LOCK parameters

  1. LOCK=NONE: Concurrent queries and DML operations are allowed
  2. LOCK=SHARED: Allows concurrent queries but blocks DML operations
  3. LOCK=DEFAULT: Allows as much concurrency (concurrent queries, DML, or both) as the system decides. If you omit the LOCK clause, you specify LOCK=DEFAULT
  4. LOCK=EXCLUSIVE: blocks concurrent queries and DML operations.

ALGORITHM parameters

  1. ALGORITHM=COPY: COPY table changes, similar to PT-OSC/GH-OST;
  2. ALGORITHM=INPLACE: only data changes at the engine layer are required, not at the Server layer;

COPY the TABLE process

  1. A temporary TABLE is first established. The structure of the TABLE is the modified structure of the ALTAR TABLE
  2. Import data from the original table into a temporary table (temporary table created on server layer, IBD file displayed)
  3. Delete the original table
  4. Rename the temporary table to the original table name

At the same time, in order to maintain data consistency during the process, the entire Copy Table lock Table is read-only. If there are write requests, services cannot be provided, resulting in a number of connections.

The IN – PLACE process

  1. Create a temporary file that scans all data pages for the primary key of the original table
  2. Innodb_temp_data_file_path temporary tablespace create temporary files
  3. During temporary file generation, all operations on the original table are recorded in a log file (Rowlog)
  4. After the temporary file is generated, the operations in the log file are applied to the temporary file to obtain a volume data that is identical with the original table
  5. Data files (log file recording and replay operations)
  6. Replace the original table data file with a temporary file

In this process, the ALTER statement obtains the MDL write lock at startup, but the write lock degrades to a read lock before the data is actually copied. This means that DML operations can be performed on the original table during the most time-consuming copy of the data to the temporary file, and the lock is only added during the final transition between the old and new tables. So rename’s going to be pretty quick.

DDL operations that allow concurrent DML

  • Create/add a secondary index
  • Renames a secondary index
  • Delete secondary index
  • Change the index type (USING {BTREE | HASH})
  • Add a primary key (Expensive Cost)
  • ALTER TABLE tbl_name DROP PRIMARY KEY, ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;)
  • New column (Expensive Cost)
  • Delete column (Expensive Cost)
  • Rename columns
  • Column reorder (Expensive cost)
  • Change the column defaults
  • Delete column defaults
  • Change column auto-increment
  • Set column attributes to NULL /not NULL (Expensive cost)
  • Modify the definition of enumeration or collection columns
  • Change ROW_FORMAT
  • Change key block size

An operation marked as Expensive Cost allows OnlineDDL, but it imposes a high burden on server IO,CPU, replication blocking, and another form of slave replication delay. Therefore, for large tables, it is recommended to perform it during off-peak hours

DDL operations for concurrent DML are not allowed

  • Adding a full-text index
  • Adding a spatial index
  • Remove the primary key
  • Change the column data type
  • Add a custom column (New column -> Become custom column)
  • Alter table character set
  • Modify the length of the data type

    • Special case: when the vARCHar character length changes from 10 to less than 255, the inplace mode will not lock the table. Changing from 255 to 10 locks the table

Refer to the file

  1. The official documentation

Dev.mysql.com/doc/refman/…

  1. MySQL 8.0 Online DDL, PT-OSC, GH-OST

zhuanlan.zhihu.com/p/115277009