New features of MySQL5.6: online DDL

New features of MySQL5.6: online DDL/changes. before MySQL 5.6, whether it is MyISAM or InnoDB engine, the database will always block the DML of the database when executing DDL operations, especially when the data volume in the table is large, the new features of MySQL5.6 are online DDL/change. before MySQL 5.6, whether it is MyISAM or InnoDB engine, when executing DDL operations, the database will always block the DML of the database. in particular, when the data volume in the table is large, an index is added and deleted for a field in the table, and the field type is modified, when a field is added, deleted, and other DDL operations, other users may be unable to execute the DML statement of the table. it may take hours or even days to execute the preceding DDL operations. You may have to rely on third-party online DDL modification tools to improve efficiency, for example, oak-online-alter-table tools that are currently well known in the industry. MySQL and later versions fully support online DDL processing for InnoDB. the DDL operation of other users is not affected at the same time.
The following example shows how to create a large data table in InnoDB and MyISAM engines, perform DDL operations (add indexes) on the large data table, and update the database table records at the same time:
1. create a table named test with the engine Innodb;
Create table test (id int not null AUTO_INCREMENT,
Deptno VARCHAR (100) not null, name_s VARCHAR (200) not null,
Content text not null, primary key (id) ENGINE = INNODB;
2. create a stored procedure and prepare for adding big data:
DELIMITER $
Drop procedure if exists test $
Create procedure test (num INT)
BEGIN
DECLARE name_s VARCHAR (200) DEFAULT '';
DECLARE I INT DEFAULT 1;
DECLARE content VARCHAR (100) DEFAULT '';
WHILE I <= num DO
SET name_s = CONCAT ('con, '-', I );
SET content = CONCAT (RAND (), '-', RAND ());
Insert into test (name_s, deptno, content) VALUES
(Name_s, CEIL (RAND () * 10), content );
SET I = I + 1;
End while;
END
3. CALL test (1000000) adds data records to the test table.
4. open two MySQL (session) command lines.
Session1 execute alter table test add index (deptno );
Session2 execute UPDATE test SET name_s = 'test' where id = 10000;
When Session1 executes the statement, session2 quickly completes the update operation without long blocking.
Session1:

Session2:

Update completed in 0.01 seconds


MyISAM engine and (before MySQL5.6) testing:
1. change the engine of the test data table to MyISAM and delete the index in the test table.
Alter table test ENGINE = MyISAM;
Alter table test drop index deptno;
2. open two MySQL (session) command lines.
Session1 execute alter table test add index (deptno );
Session2 execute UPDATE test SET name_s = 'test' where id = 10000;
When Session1 executes the statement, session2 does not quickly complete the update operation, thus forming a long blocking.
Session1:

Session2:

Update completed in 10.59 seconds