Mysql5.6 Online DDL

InnoDB performance Improvement Aspects:

--users can add indexes and perform standard table alterations while the database remains available for application update S
Support Online operation (add index, ALTER TABLE):

Example One: Add an index online :
Open a session and create an index on the Data_userinfo table under the Italk library (this table is about 700,000 data):
Mysql>>create index idx_groupid on Data_userinfo (GroupID);
Query OK, 0 rows affected (9.26 sec)
records:0 duplicates:0 warnings:0
At the same time, in another session, perform an update operation on the table:
Mysql>>update Data_userinfo set Status=1 where id=22;
Query OK, 1 row affected (0.02 sec)
Rows matched:1 changed:1 warnings:0
The update operation returns the result immediately, and in previous versions, because the index was created and ALTER TABLE is a table-level lock, the update can only wait for the statement that added the index to complete before it can be executed, showing the show full processlist status as:
Waiting for table Metadata Lock | Update Data_userinfo set status=2 where id=22

Alter table to add an indexed form:
Mysql>>alter Table Data_userinfo Add index idx_groupid (groupid);
Query OK, 0 rows affected (12.77 sec)
records:0 duplicates:0 warnings:0
Mysql>>update Data_userinfo set status=2 where id=23;
Query OK, 1 row affected (0.05 sec)
Rows matched:1 changed:1 warnings:0

Example two: Adding a column field online :
Mysql>>alter table Data_userinfo add age int unsigned not NULL default 0;
Query OK, 0 rows affected (57.71 sec)
records:0 duplicates:0 warnings:0
Mysql>>update Data_userinfo set Status=1 where id=23;
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0

example Three: Changing the data type of a column field :
Changing the data type of a column (takes substantial time and does require rebuilding all the rows of the table):
Takes a lot of time and rebuilds all row data in the table
Query OK, 668200 rows affected (1 min 35.54 sec)

When you perform DDL operations on a large table, you can determine whether the operation is fast or slow to perform by following these steps:
1. Cloning the table structure of the original table
2. Populate the cloned table with a small portion of the original data
3. Perform DDL operations on the cloned table
4. Check if rows affected is 0 or not 0. If non-0 means that the DDL operation is going to rebuild all row data in the table, it means that you need to schedule a low peak time for a business or
The DDL operation on the slave

Performance Benchmark Test

You can test the performance of online DDL operations by performing the same alter operation on a larger InnoDB table in the latest version 5.6 and older versions
[Email protected]:italk 17:45:09>>set old_alter_table=1; -------------set to in Legacy mode
Query OK, 0 rows Affected (0.00 sec)

[Email protected]:italk 17:45:30>>alter Table Data_userinfo Add index idx_groupid (groupid);
Query OK, 668200 rows Affected (42.05 sec)-------------performs a table copy is completed with copy table data, the number of rows affected is all rows
records:668200 duplicates:0 warnings:0

[Email protected]:italk 17:46:28>>set old_alter_table=0; -------------set to in the latest mode
Query OK, 0 rows Affected (0.00 sec)

[Email protected]:italk 17:46:54>>alter Table Data_userinfo Add index idx_groupid (groupid);
Query OK, 0 rows affected (10.67 sec)-------------changes in-place in-place change without affecting row data
records:0 duplicates:0 warnings:0

We can see that the operation time is greatly reduced and the performance is significantly improved by time consuming.

The algorithm options and lock options are new in the ALTER TABLE syntax for 5.6:
Algorithm_option:
algorithm [=] {default| inplace| COPY}

Lock_option:
LOCK [=] {default| none| shared| EXCLUSIVE}
You can use these 2 options to control whether your DDL operation is in-place mode or the old one. Copy the table mode

Study Questions:

1. Why is deleting index so fast?

A: When you delete index, just make a usable flag in the space of the secondary index. and deletes the index definition for the table without creating a new table.

Where is the index stored in 2.innodb

Analysis InnoDB will find that the index exists in the new page and is not stored in the same pageh as the data. The secondary index page is stored with the value of the index row and the value of the primary key.

3. What does it take to ensure that both the index and update data are added online without being affected?

A: MySQL5.6 in the row_log to ensure the consistency of data, and both sides are unaffected.

Online ADD Index processing process

• Determine if DML can be performed with inplace (no new table)

• Get started with online operations pre-preparation

(1. Modifying the table's data dictionary information

(2. Wait for all threads in the background to stop the operation of this table

(In the 3.online process, the original table allows read and write, so the data for DML operations needs to be logged to Row_log, not updated on the index

• Start a real online add index operation

(1. Read the clustered index, sort, and insert into the new index, and change the record in row log to update to the new index

(2. After the new index is inserted, the record changes recorded in Row_log during this period are re-applied

(3. New Index Short lock

Mysql5.6 Online DDL