Detailed explanation of the use of MySQL Online DDL

text

Online DDL is only supported in MySQL 5.6. In versions 5.5 and earlier, using commands such as alter table/create index to modify the table structure will lock the table, which is obviously unacceptable in a production environment.

In MySQL 5.7, Online DDL has been continuously optimized in terms of performance and stability. It has significant performance advantages, has little impact on business load, and has controllable downtime. Compared with pt-osc/gh-ost, it does not require the installation of third-party dependency packages. It also supports Online DDL of the Inplace algorithm. Since there is no need to copy tables, the required disk space is also smaller.

Let's first look at a common DDL statement:

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

Among them, LOCK describes the degree of concurrency during the DDL operation, and ALGORITHM describes the implementation method of DDL.

LOCK parameter

1. LOCK=NONE: Allow concurrent queries and DML operations
2. LOCK=SHARED: allows concurrent queries but blocks DML operations
3. LOCK=DEFAULT: Allow as much concurrency as possible (concurrent queries, DML, or both), as determined by the system. If the LOCK clause is omitted, it is equivalent to specifying LOCK=DEFAULT
   
4. LOCK=EXCLUSIVE: Block concurrent queries and DML operations.
   

ALGORITHM parameters

1. ALGORITHM=COPY: Use the copy method to modify the table, similar to pt-osc/gh-ost;
2. ALGORITHM=INPLACE: Only the engine layer data needs to be changed, and the server layer is not involved;
   

COPY TABLE Process

1. First, create a temporary table with the structure changed by ALTAR TABLE.
2. Import the data in the original table into a temporary table (the server layer creates a temporary table, and there will be an 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 this process, the table is locked and read-only during the intermediate data copying (Copy Table). If a write request comes in, the service will not be able to be provided, which will cause the number of connections to explode.

IN-PLACE Process

1. Create a temporary file and scan all data pages of the original table primary key
2. Generate a B+ tree using the original table records in the data page and store it in a temporary file (create a temporary file in the innodb_temp_data_file_path temporary tablespace)
3. In the process of generating temporary files, all operations on the original table are recorded in a log file (rowlog)
4. After the temporary file is generated, apply the operations in the log file to the temporary file to obtain a table that is identical to the original table in terms of data.
5. Data files (log files record and replay operations)
6. Replace the original table data file with a temporary file
   

During this process, the alter statement acquires the MDL write lock when it is started, but this write lock degenerates into a read lock before the data is actually copied. That is to say, during the most time-consuming process of copying data to a temporary file, the original table can be operated with DML. It is only locked during the final switching phase between the old and new tables. This rename process is very fast.

Allow concurrent DML and DDL operations

• Create/add a secondary index
• Rename a secondary index
• Deleting a secondary index
• Changing the index type (USING {BTREE | HASH})
• Add a primary key (expensive cost)
• Drop the primary key and add another (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 reordering (expensive cost)
• Changing column default values
• Remove column default value
• Change the column auto-increment value
• Setting column attributes null/not null (expensive cost)
• Modify the definition of an enumeration or collection column
• Change ROW_FORMAT
• Change key block size
  

Although Online DDL is allowed for operations marked as expensive cost, they will impose a high burden on the server IO and CPU. They will also cause replication blockage, which will cause another form of slave replication delay. Therefore, if the table is large, it is recommended to execute it during off-peak hours.

DDL operations that do not allow concurrent DML

• Add full-text index
• Adding a spatial index
• Deleting a primary key
• Changing column data types
• Add an auto-increment column (add a new column->change it to an auto-increment column)
• Change table character set
• Modify the length of the data type
  • Special case: If the length of varchar character changes from 10 to less than 255, the table will not be locked if the inplace method is used; but if it changes from 255 to 10, the table will be locked;

The above is the detailed content of the detailed explanation of the use of MySQL Online DDL. For more information about the use of MySQL Online DDL, please pay attention to other related articles on 123WORDPRESS.COM!