이 데모에 사용되는 초기 테이블을 설정하는 코드를 보여줍니다.

 / * 
 Setup code for the online DDL demonstration :
 - Set up some config variables.
 - Create 2 tables that are clones of one of the INFORMATION_SCHEMA tables
   that always has some data. The "small"table has a couple of thousand rows.
   For the "big"table, keep doubling the data until it reaches over a million rows.
 - Set up a primary key for the sample tables, since we are demonstrating InnoDB aspects.
 * / 

 set autocommit = 0;
 set foreign_key_checks = 1;
 set global innodb_file_per_table = 1;
 set old_alter_table = 0;
 prompt mysql : 

 use test;

 \! echo "Setting up 'small'table"
 drop table if exists small_table;
 create table small_table as select * from information_schema.columns;
 alter table small_table add id int unsigned not null primary key auto_increment;
 select count (id) from small_table;

 \! echo "Setting up 'big'table"
 drop table if exists big_table;
 create table big_table as select * from information_schema.columns;
 show create table big_table \ G

 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 insert into big_table select * from big_table;
 commit;

 alter table big_table add id int unsigned not null primary key auto_increment;
 select count (id) from big_table;

이 코드를 실행하면 단순화를 위해 압축 된 가장 중요한 점은 굵게 표시 한 다음 출력을 얻을 수 있습니다.

 Setting up 'small'table :
 Query OK, 0 rows affected (0.01 sec)

 Query OK 1678 rows affected (0.13 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 Query OK 1678 rows affected (0.07 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

+-----------+
| count(id) |
+-----------+
|      1678 |
+-----------+
1 row in set (0.00 sec)

 Setting up 'big'table :
 Query OK, 0 rows affected (0.16 sec)

 Query OK 1678 rows affected (0.17 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 *************************** 1. row ******************** *******
        Table : big_table
 Create Table : CREATE TABLE`big_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
   `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
   `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
   `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
   `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT ''
 ) ENGINE = InnoDB DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

 Query OK 1678 rows affected (0.09 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 Query OK, 3356 rows affected (0.07 sec)
 Records : 3356 Duplicates : 0 Warnings : 0

 Query OK, 6712 rows affected (0.17 sec)
 Records : 6712 Duplicates : 0 Warnings : 0

 Query OK, 13424 rows affected (0.44 sec)
 Records : 13424 Duplicates : 0 Warnings : 0

 Query OK, 26848 rows affected (0.63 sec)
 Records : 26848 Duplicates : 0 Warnings : 0

 Query OK, 53696 rows affected (1.72 sec)
 Records : 53696 Duplicates : 0 Warnings : 0

 Query OK, 107392 rows affected (3.02 sec)
 Records : 107392 Duplicates : 0 Warnings : 0

 Query OK, 214784 rows affected (6.28 sec)
 Records : 214784 Duplicates : 0 Warnings : 0

 Query OK, 429568 rows affected (13.25 sec)
 Records : 429568 Duplicates : 0 Warnings : 0

 Query OK, 859136 rows affected (28.16 sec)
 Records : 859136 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.03 sec)

 Query OK, 1718272 rows affected (1 min 9.22 sec)
 Records : 1718272 Duplicates : 0 Warnings : 0

+-----------+
| count(id) |
+-----------+
|   1718272 |
+-----------+
1 row in set (1.75 sec)

다음 명령문 시퀀스는 CREATE INDEX 및 DROP INDEX 문의 상대 속도를 보여줍니다. 작은 테이블의 경우 빠른 방법과 느린 방법 중 어느 것을 사용하여도 경과 시간은 1 초 미만이므로 "rows affected '의 출력을보고 어느 작업 테이블 재구성을 차단할 수 있는지를 확인합니다. 큰 테이블의 경우 테이블 재구성의 생략에 의해 상당한 시간이 절약되므로 효율의 차이는 분명하다.

 \! clear

 \! echo "=== Create and drop index (small table, new / fast technique) ==="
 \! echo
 \! echo "Data size (kilobytes) before index created"
 \! du -k data / test / small_table.ibd
 create index i_dtyp_small on small_table (data_type) algorithm = inplace;
 \! echo "Data size after index created"
 \! du -k data / test / small_table.ibd
 drop index i_dtyp_small on small_table, algorithm = inplace;

 - Compare against the older slower DDL.

 \! echo "=== Create and drop index (small table, old / slow technique) ==="
 \! echo
 \! echo "Data size (kilobytes) before index created"
 \! du -k data / test / small_table.ibd
 create index i_dtyp_small on small_table (data_type) algorithm = copy;
 \! echo "Data size after index created"
 \! du -k data / test / small_table.ibd
 drop index i_dtyp_small on small_table, algorithm = copy;

 - In the above example, we examined the "rows affected"number,
 - ideally looking for a zero figure. Let 's try again with a larger
 - sample size, where we 'll see that the actual time taken can
 - vary significantly.

 \! echo "=== Create and drop index (big table, new / fast technique) ==="
 \! echo
 \! echo "Data size (kilobytes) before index created"
 \! du -k data / test / big_table.ibd
 create index i_dtyp_big on big_table (data_type) algorithm = inplace;
 \! echo "Data size after index created"
 \! du -k data / test / big_table.ibd
 drop index i_dtyp_big on big_table, algorithm = inplace;

 \! echo "=== Create and drop index (big table, old / slow technique) ==="
 \! echo
 \! echo "Data size (kilobytes) before index created"
 \! du -k data / test / big_table.ibd
 create index i_dtyp_big on big_table (data_type) algorithm = copy;
 \! echo "Data size after index created"
 \! du -k data / test / big_table.ibd
 drop index i_dtyp_big on big_table, algorithm = copy;

이 코드를 실행하면 단순화를 위해 압축 된 가장 중요한 점은 굵게 표시 한 다음 출력을 얻을 수 있습니다.

 Query OK, 0 rows affected (0.00 sec)

 === Create and drop index (small table, new / fast technique) ===

 Data size (kilobytes) before index created : 
 384 data / test / small_table.ibd
 Query OK, 0 rows affected (0.04 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 Data size after index created : 
 432 data / test / small_table.ibd
 Query OK, 0 rows affected (0.02 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.00 sec)

 === Create and drop index (small table, old / slow technique) ===

 Data size (kilobytes) before index created : 
 432 data / test / small_table.ibd
 Query OK 1678 rows affected (0.12 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 Data size after index created : 
 448 data / test / small_table.ibd
 Query OK 1678 rows affected (0.10 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.00 sec)

 === Create and drop index (big table, new / fast technique) ===

 Data size (kilobytes) before index created : 
 315392 data / test / big_table.ibd
 Query OK, 0 rows affected (33.32 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 Data size after index created : 
 335872 data / test / big_table.ibd
 Query OK, 0 rows affected (0.02 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.00 sec)

 === Create and drop index (big table, old / slow technique) ===

 Data size (kilobytes) before index created : 
 335872 data / test / big_table.ibd
 Query OK, 1718272 rows affected (1 min 5.01 sec)
 Records : 1718272 Duplicates : 0 Warnings : 0

 Data size after index created : 
 348160 data / test / big_table.ibd
 Query OK, 1718272 rows affected (46.59 sec)
 Records : 1718272 Duplicates : 0 Warnings : 0

CREATE INDEX 및 DROP INDEX 동시에 실행되는 DML 문 (삽입, 업데이트 또는 삭제)을 나타 내기 위해 다음 동일한 데이터베이스에 연결된 개별 mysql 세션에서 수행 한 몇 가지 코드 조각을 보여줍니다 .

 / *
 CREATE INDEX statement to run against a table while 
 insert / update / delete statements are modifying the
 column being indexed.
 * /

 - We 'll run this script in one session, while simultaneously creating and dropping
 - an index on test / big_table.table_name in another session.

 use test;
 create index i_concurrent on big_table (table_name);

 / *
 DROP INDEX statement to run against a table while
 insert / update / delete statements are modifying the
 column being indexed.
 * /

 - We 'll run this script in one session, while simultaneously creating and dropping
 - an index on test / big_table.table_name in another session.

 use test;
 drop index i_concurrent on big_table;

 / *
 Some queries and insert / update / delete statements to run against a table
 while an index is being created or dropped. Previously, these operations
 would have stalled during the index create / drop period and possibly
 timed out or deadlocked.
 * /

 - We 'll run this script in one session, while simultaneously creating and dropping
 - an index on test / big_table.table_name in another session.

 - In our test instance, that column has about 1.7M rows, with 136 different values​​.
 - Sample values​​ : COLUMNS (20480) ENGINES (6144) EVENTS (24576) FILES (38912) TABLES (21504) VIEWS (10240)

 set autocommit = 0;
 use test;

 select distinct character_set_name from big_table where table_name = 'FILES';
 delete from big_table where table_name = 'FILES';
 select distinct character_set_name from big_table where table_name = 'FILES';

 - I 'll issue the final rollback interactively, not via script,
 - the better to control the timing.
 - rollback;

이 코드를 실행하면 단순화를 위해 압축 된 가장 중요한 점은 굵게 표시 한 다음 출력을 얻을 수 있습니다.

 mysql : source concurrent_ddl_create.sql
 Database changed
 Query OK, 0 rows affected (1 min 25.15 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 mysql : source concurrent_ddl_drop.sql
 Database changed
 Query OK, 0 rows affected (24.98 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 mysql : source concurrent_dml.sql
 Query OK, 0 rows affected (0.00 sec)

Database changed
+--------------------+
| character_set_name |
+--------------------+
| NULL               |
| utf8               |
+--------------------+
2 rows in set (0.32 sec)

 Query OK, 38912 rows affected (1.84 sec)

 Empty set (0.01 sec)

 mysql : rollback;
 Query OK, 0 rows affected (1.05 sec)

ALTER TABLE 을 사용하여 열 이름 변경의 데모를 보여줍니다. 새로운 고속 DDL 메커니즘을 사용하여 이름을 변경하고 기존 느린 DDL 메커니즘을 ( old_alter_table=1 과 함께) 사용하여 원래의 컬럼 이름을 복원합니다.

참고 :

 / *
 Run through a sequence of 'rename column'statements.
 Because this operation involves only metadata, not table data,
 it is fast for big and small tables, with new or old DDL mechanisms.
 * /

 \! clear

 \! echo "Rename column (fast technique, small table) :"
 alter table small_table change`IS_NULLABLE``NULLABLE` varchar (3) character set utf8 not null, algorithm = inplace;
 \! echo "Rename back to original name (slow technique) :"
 alter table small_table change`NULLABLE``IS_NULLABLE` varchar (3) character set utf8 not null, algorithm = copy;


 \! echo "Rename column (fast technique, big table) :"
 alter table big_table change`IS_NULLABLE``NULLABLE` varchar (3) character set utf8 not null, algorithm = inplace;
 \! echo "Rename back to original name (slow technique) :"
 alter table big_table change`NULLABLE``IS_NULLABLE` varchar (3) character set utf8 not null, algorithm = copy;

이 코드를 실행하면 단순화를 위해 압축 된 가장 중요한 점은 굵게 표시 한 다음 출력을 얻을 수 있습니다.

 Rename column (fast technique, small table) :
 Query OK, 0 rows affected (0.05 sec)

 Query OK, 0 rows affected (0.13 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 Rename back to original name (slow technique) :
 Query OK, 0 rows affected (0.00 sec)

 Query OK 1678 rows affected (0.35 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 Rename column (fast technique, big table) :
 Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected (0.11 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 Rename back to original name (slow technique) :
 Query OK, 0 rows affected (0.00 sec)

 Query OK, 1718272 rows affected (1 min 0.00 sec)
 Records : 1718272 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.00 sec)

외부 키 데모 (외래 키 제약 조건의 삭제 속도의 향상을 포함)를 보여줍니다.

 / *
 Demonstrate aspects of foreign keys that are or are not affected by the DDL improvements.
 - Create a new table with only a few values​​ to serve as the parent table.
 - Set up the 'small'and 'big'tables as child tables using a foreign key.
 - Verify that the ON DELETE CASCADE clause makes changes ripple from parent to child tables.
 - Drop the foreign key constraints, and optionally associated indexes (This is the operation that is sped up)
 * /

 \! clear

 - Make sure foreign keys are being enforced, and allow
 - rollback after doing some DELETEs that affect both
 - parent and child tables.
 set foreign_key_checks = 1;
 set autocommit = 0;

 - Create a parent table, containing values​​ that we know are already present
 - in the child tables.
 drop table if exists schema_names;
 create table schema_names (id int unsigned not null primary key auto_increment, schema_name varchar (64) character set utf8 not null, index i_schema (schema_name)) as select distinct table_schema schema_name from small_table;

 show create table schema_names \ G
 show create table small_table \ G
 show create table big_table \ G

 - Creating the foreign key constraint still involves a table rebuild when foreign_key_checks = 1,
 - as illustrated by the "rows affected"figure.
 alter table small_table add constraint small_fk foreign key i_table_schema (table_schema) references schema_names (schema_name) on delete cascade;
 alter table big_table add constraint big_fk foreign key i_table_schema (table_schema) references schema_names (schema_name) on delete cascade;

 show create table small_table \ G
 show create table big_table \ G

 select schema_name from schema_names order by schema_name;
 select count (table_schema) howmany, table_schema from small_table group by table_schema;
 select count (table_schema) howmany, table_schema from big_table group by table_schema;

 - big_table is the parent table.
 - schema_names is the parent table.
 - big_table is the child table.
 - (One row in the parent table can have many "children"in the child table)
 - Changes to the parent table can ripple through to the child table.
 - For example, removing the value 'test'from schema_names.schema_name will
 - result in the removal of 20K or so rows from big_table.

 delete from schema_names where schema_name = 'test';

 select schema_name from schema_names order by schema_name;
 select count (table_schema) howmany, table_schema from small_table group by table_schema;
 select count (table_schema) howmany, table_schema from big_table group by table_schema;

 - Because we 've turned off autocommit, we can still get back those deleted rows
 - if the DELETE was issued by mistake.
 rollback;

 select schema_name from schema_names order by schema_name;
 select count (table_schema) howmany, table_schema from small_table group by table_schema;
 select count (table_schema) howmany, table_schema from big_table group by table_schema;

 - All of the cross-checking between parent and child tables would be
 - deadly slow if there was not the requirement for the corresponding
 - columns to be indexed!

 - But we can get rid of the foreign key using a fast operation
 - that does not rebuild the table.
 - If we did not specify a constraint name when setting up the foreign key, we would
 - have to find the auto-generated name such as 'big_table_ibfk_1'in the
 - output from 'show create table'.

 - For the small table, we 'll drop the foreign key and the associated index.
 - Having an index on a small table is less critical.

 \! echo "DROP FOREIGN KEY and INDEX from small_table"
 alter table small_table drop foreign key small_fk, drop index small_fk;

 - For the big table, we 'll drop the foreign key and leave the associated index.
 - If we are still doing queries that reference the indexed column, the index is
 - very important to avoid a full table scan of the big table.
 \! echo "DROP FOREIGN KEY from big_table"
 alter table big_table drop foreign key big_fk;


 show create table small_table \ G
 show create table big_table \ G

이 코드를 실행하면 단순화를 위해 압축 된 가장 중요한 점은 굵게 표시 한 다음 출력을 얻을 수 있습니다.

 Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected (0.01 sec)

 Query OK, 4 rows affected (0.03 sec)
 Records : 4 Duplicates : 0 Warnings : 0

 *************************** 1. row ******************** *******
        Table : schema_names
 Create Table : CREATE TABLE`schema_names` (
   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
   `schema_name` varchar (64) CHARACTER SET utf8 NOT NULL,
   PRIMARY KEY (`id`)
   KEY`i_schema` (`schema_name`)
 ) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

 *************************** 1. row ******************** *******
        Table : small_table
 Create Table : CREATE TABLE`small_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
   `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
   `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL,
   `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
   `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
   `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
 ) ENGINE = InnoDB AUTO_INCREMENT = 1679 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

 *************************** 1. row ******************** *******
        Table : big_table
 Create Table : CREATE TABLE`big_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
   `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
   `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL,
   `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
   `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
   `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
   KEY`big_fk` (`TABLE_SCHEMA`) 
 ) ENGINE = InnoDB AUTO_INCREMENT = 1718273 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

 Query OK 1678 rows affected (0.10 sec)
 Records : 1678 Duplicates : 0 Warnings : 0

 Query OK, 1718272 rows affected (1 min 14.54 sec)
 Records : 1718272 Duplicates : 0 Warnings : 0

 *************************** 1. row ******************** *******
        Table : small_table
 Create Table : CREATE TABLE`small_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
   `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
   `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL,
   `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
   `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
   `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
   KEY`small_fk` (`TABLE_SCHEMA`) 
   CONSTRAINT`small_fk` FOREIGN KEY (`TABLE_SCHEMA`) REFERENCES`schema_names` (`schema_name`) ON DELETE CASCADE 
 ) ENGINE = InnoDB AUTO_INCREMENT = 1679 DEFAULT CHARSET = latin1
 1 row in set (0.12 sec)

 *************************** 1. row ******************** *******
        Table : big_table
 Create Table : CREATE TABLE`big_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
   `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
   `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL,
   `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
   `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
   `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
   KEY`big_fk` (`TABLE_SCHEMA`) 
   CONSTRAINT`big_fk` FOREIGN KEY (`TABLE_SCHEMA`) REFERENCES`schema_names` (`schema_name`) ON DELETE CASCADE 
 ) ENGINE = InnoDB AUTO_INCREMENT = 1718273 DEFAULT CHARSET = latin1
 1 row in set (0.01 sec)

+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

+---------+--------------------+
| howmany | table_schema       |
+---------+--------------------+
|     563 | information_schema |
|     286 | mysql              |
|     786 | performance_schema |
|      43 | test               |
+---------+--------------------+
4 rows in set (0.01 sec)

+---------+--------------------+
| howmany | table_schema       |
+---------+--------------------+
|  576512 | information_schema |
|  292864 | mysql              |
|  804864 | performance_schema |
|   44032 | test               |
+---------+--------------------+
4 rows in set (2.10 sec)

 Query OK, 1 row affected (1.52 sec)

+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
+---------+--------------------+
| howmany | table_schema       |
+---------+--------------------+
|     563 | information_schema |
|     286 | mysql              |
|     786 | performance_schema |
+---------+--------------------+
3 rows in set (0.00 sec)

+---------+--------------------+
| howmany | table_schema       |
+---------+--------------------+
|  576512 | information_schema |
|  292864 | mysql              |
|  804864 | performance_schema |
+---------+--------------------+
3 rows in set (1.74 sec)

 Query OK, 0 rows affected (0.60 sec)

+--------------------+
| schema_name        |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

+---------+--------------------+
| howmany | table_schema       |
+---------+--------------------+
|     563 | information_schema |
|     286 | mysql              |
|     786 | performance_schema |
|      43 | test               |
+---------+--------------------+
4 rows in set (0.01 sec)

+---------+--------------------+
| howmany | table_schema       |
+---------+--------------------+
|  576512 | information_schema |
|  292864 | mysql              |
|  804864 | performance_schema |
|   44032 | test               |
+---------+--------------------+
4 rows in set (1.59 sec)

 DROP FOREIGN KEY and INDEX from small_table :
 Query OK, 0 rows affected (0.02 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 DROP FOREIGN KEY from big_table :
 Query OK, 0 rows affected (0.02 sec)
 Records : 0 Duplicates : 0 Warnings : 0

 *************************** 1. row ******************** *******
        Table : small_table
 Create Table : CREATE TABLE`small_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
   `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
   `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL,
   `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
   `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
   `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
   `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
   `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (`id`)
 ) ENGINE = InnoDB AUTO_INCREMENT = 1679 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

 *************************** 1. row ******************** *******
        Table : big_table
 Create Table : CREATE TABLE`big_table` (
   `TABLE_CATALOG` varchar (512) CHARACTER SET utf8 NOT NULL DEFAULT '',
   `TABLE_SCHEMA` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `TABLE_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `COLUMN_NAME` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ORDINAL_POSITION` bigint (21) unsigned NOT NULL DEFAULT '0'
  `COLUMN_DEFAULT` longtext CHARACTER SET utf8,
  `IS_NULLABLE` varchar (3) CHARACTER SET utf8 NOT NULL,
  `DATA_TYPE` varchar (64) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `CHARACTER_MAXIMUM_LENGTH` bigint (21) unsigned DEFAULT NULL,
  `CHARACTER_OCTET_LENGTH` bigint (21) unsigned DEFAULT NULL,
  `NUMERIC_PRECISION` bigint (21) unsigned DEFAULT NULL,
  `NUMERIC_SCALE` bigint (21) unsigned DEFAULT NULL,
  `DATETIME_PRECISION` bigint (21) unsigned DEFAULT NULL,
  `CHARACTER_SET_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
  `COLLATION_NAME` varchar (32) CHARACTER SET utf8 DEFAULT NULL,
  `COLUMN_TYPE` longtext CHARACTER SET utf8 NOT NULL,
  `COLUMN_KEY` varchar (3) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `EXTRA` varchar (30) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `PRIVILEGES` varchar (80) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `COLUMN_COMMENT` v​​archar (1024) CHARACTER SET utf8 NOT NULL DEFAULT '',
  `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
  KEY`big_fk` (`TABLE_SCHEMA`)
) ENGINE = InnoDB AUTO_INCREMENT = 1718273 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

테이블 컬럼의 자동 증가 의 하한 값을 늘리 코드를 보여줍니다. 이것은 이렇게하면 테이블 재구성이 어떻게 피할 수 있는지, 또 InnoDB 의 자동 증가 컬럼에 관한 흥미로운 기타 몇 가지 사실을 보여줍니다.

 / *
If this script is run after foreign_key.sql, the schema_names table is
already set up. But to allow this script to run multiple times without
running into duplicate ID errors, we set up the schema_names table
all over again.
 * /

\! clear

\! echo "=== Adjusting the Auto-Increment Limit for a Table ==="
\! echo

drop table if exists schema_names;
create table schema_names (id int unsigned not null primary key auto_increment,
  schema_name varchar (64) character set utf8 not null, index i_schema (schema_name))
  as select distinct table_schema schema_name from small_table;

\! echo "Initial state of schema_names table. AUTO_INCREMENT is included in SHOW CREATE TABLE output"
\! echo "Note how MySQL reserved a block of IDs, but only needed 4 of them in this transaction, so the next inserted values​​ would get IDs 8 and 9"
show create table schema_names \ G
select * from schema_names order by id;

\! echo "Inserting even a tiny amount of data can produce gaps in the ID sequence."
insert into schema_names (schema_name) values​​ ( 'eight') ( 'nine');

\! echo "Bumping auto-increment lower limit to 20 (fast mechanism) :"
alter table schema_names auto_increment = 20 , algorithm = inplace ;

\! echo "Inserting 2 rows that should get IDs 20 and 21"
insert into schema_names (schema_name) values​​ ( 'foo') ( 'bar');
commit;

\! echo "Bumping auto-increment lower limit to 30 (slow mechanism) :"
alter table schema_names auto_increment = 30 , algorithm = copy ;

\! echo "Inserting 2 rows that should get IDs 30 and 31"
insert into schema_names (schema_name) values​​ ( 'bletch') ( 'baz');
commit;

select * from schema_names order by id;

\! echo "Final state of schema_names table. AUTO_INCREMENT value shows the next inserted row would get ID = 32"
show create table schema_names \ G

이 코드를 실행하면 단순화를 위해 압축 된 가장 중요한 점은 굵게 표시 한 다음 출력을 얻을 수 있습니다.

=== Adjusting the Auto-Increment Limit for a Table ===

 Query OK, 0 rows affected (0.01 sec)

Query OK, 4 rows affected (0.02 sec)
Records : 4 Duplicates : 0 Warnings : 0

Initial state of schema_names table. AUTO_INCREMENT is included in SHOW CREATE TABLE output.
Note how MySQL reserved a block of IDs, but only needed 4 of them in this transaction, so the next inserted values​​ would get IDs 8 and 9.
 *************************** 1. row ******************** *******
       Table : schema_names
Create Table : CREATE TABLE`schema_names` (
  `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
  `schema_name` varchar (64) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
  KEY`i_schema` (`schema_name`)
) ENGINE = InnoDB AUTO_INCREMENT = 8 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

+----+--------------------+
| id | schema_name        |
+----+--------------------+
|  1 | information_schema |
|  2 | mysql              |
|  3 | performance_schema |
|  4 | test               |
+----+--------------------+
4 rows in set (0.00 sec)

Inserting even a tiny amount of data can produce gaps in the ID sequence.
 Query OK, 2 rows affected (0.00 sec)
Records : 2 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.00 sec)

Bumping auto-increment lower limit to 20 (fast mechanism) :
Query OK, 0 rows affected (0.01 sec)
Records : 0 Duplicates : 0 Warnings : 0

Inserting 2 rows that should get IDs 20 and 21 :
 Query OK, 2 rows affected (0.00 sec)
Records : 2 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

Bumping auto-increment lower limit to 30 (slow mechanism) :
Query OK, 8 rows affected (0.02 sec)
 Records : 8 Duplicates : 0 Warnings : 0

Inserting 2 rows that should get IDs 30 and 31 :
 Query OK, 2 rows affected (0.00 sec)
Records : 2 Duplicates : 0 Warnings : 0

 Query OK, 0 rows affected (0.01 sec)

+----+--------------------+
| id | schema_name        |
+----+--------------------+
|  1 | information_schema |
|  2 | mysql              |
|  3 | performance_schema |
|  4 | test               |
|  8 | eight              |
|  9 | nine               |
| 20 | foo                |
| 21 | bar                |
| 30 | bletch             |
| 31 | baz                |
+----+--------------------+
10 rows in set (0.00 sec)

 Query OK, 0 rows affected (0.00 sec)

Final state of schema_names table. AUTO_INCREMENT value shows the next inserted row would get ID = 32.
 *************************** 1. row ******************** *******
       Table : schema_names
Create Table : CREATE TABLE`schema_names` (
  `id` int (10) unsigned NOT NULL AUTO_INCREMENT,
  `schema_name` varchar (64) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
  KEY`i_schema` (`schema_name`)
) ENGINE = InnoDB AUTO_INCREMENT = 32 DEFAULT CHARSET = latin1
 1 row in set (0.00 sec)

이 예는 ALTER TABLE 문 LOCK 절을 사용하여 온라인 DDL 작업이 진행되는 동안 테이블에 병렬 액세스를 허용하거나 거부하는 ​​방법을 보여줍니다. 이 절에는 쿼리와 DML 문을 허용할지 ( LOCK = NONE ) 쿼리 만 허용하거나 ( LOCK = SHARED ) 또는 병렬 액세스를 전혀 허용하지 않는 ( LOCK = EXCLUSIVE ) 설정이 있습니다.

여기에서는 하나의 세션 대기 중이거나 교착 상태에서 동작을 확인하기 위해 LOCK 절 다른 값을 사용하여 하나의 세션에서 연속 ALTER TABLE 문을 실행하여 인덱스를 생성 및 삭제 합니다. 앞의 예와 같은 BIG_TABLE 테이블을 사용하여 약 170 만 행에서 시작합니다. 설명을 위해, IS_NULLABLE 열에 대해 인덱싱 및 쿼리를 실행합니다. (그러나 실제로는 고유 한 값이 2 개 밖에없는 아주 작은 컬럼의 인덱스를 작성하는 것은있을 수 없습니다.)

mysql: desc big_table;
+--------------------------+---------------------+------+-----+---------+----------------+
| Field                    | Type                | Null | Key | Default | Extra          |
+--------------------------+---------------------+------+-----+---------+----------------+
| TABLE_CATALOG            | varchar(512)        | NO   |     |         |                |
| TABLE_SCHEMA             | varchar(64)         | NO   |     |         |                |
| TABLE_NAME               | varchar(64)         | NO   |     |         |                |
| COLUMN_NAME              | varchar(64)         | NO   |     |         |                |
| ORDINAL_POSITION         | bigint(21) unsigned | NO   |     | 0       |                |
| COLUMN_DEFAULT           | longtext            | YES  |     | NULL    |                |

| IS_NULLABLE              | varchar(3)          | NO   |     |         |                |
...
+--------------------------+---------------------+------+-----+---------+----------------+
21 rows in set (0.14 sec)

mysql : alter table big_table add index i1 (is_nullable);
Query OK, 0 rows affected (20.71 sec)

mysql : alter table big_table drop index i1;
Query OK, 0 rows affected (0.02 sec)

mysql : alter table big_table add index i1 (is_nullable) , lock = exclusive ;
Query OK, 0 rows affected (19.44 sec)

mysql : alter table big_table drop index i1;
 Query OK, 0 rows affected (0.03 sec)

mysql : alter table big_table add index i1 (is_nullable) , lock = shared ;
Query OK, 0 rows affected (16.71 sec)

mysql : alter table big_table drop index i1;
 Query OK, 0 rows affected (0.05 sec)

mysql : alter table big_table add index i1 (is_nullable) , lock = none ;
Query OK, 0 rows affected (12.26 sec)

mysql : alter table big_table drop index i1;
 Query OK, 0 rows affected (0.01 sec)

... repeat statements like the above while running queries ...
... and DML statements at the same time in another session ...

DDL 문을 실행하는 세션에서는 특별한 것은 아무것도 발생하지 않습니다. 경우에 따라서는 다른 트랜잭션이 DDL 중에 테이블을 변경하거나 DDL 전에 테이블을 쿼리 할 때 트랜잭션의 완료를 기다리는 위해 ALTER TABLE 에 매우 오랜 시간이 걸릴 수 있습니다.

mysql : alter table big_table add index i1 (is_nullable), lock = none;

Query OK, 0 rows affected (59.27 sec)

mysql : - The previous ALTER took so long because it was waiting for all the concurrent
mysql : - transactions to commit or roll back.

mysql : alter table big_table drop index i1;
Query OK, 0 rows affected (41.05 sec)

mysql : - Even doing a SELECT on the table in the other session first causes
mysql : - the ALTER TABLE above to stall until the transaction
mysql : - surrounding the SELECT is committed or rolled back.

동시에 실행되는 다른 세션의 로그를 보여줍니다. 여기에서는 앞의 목록에 표시된 DDL 작업 전, 도중 및 이후에 테이블에 대해 쿼리와 DML 문을 실행하고 있습니다. 첫 번째 목록은 쿼리 만 보여줍니다. LOCK = NONE 또는 LOCK = SHARED 를 사용하여 DDL 작업 중에 쿼리가 허용 될 수 및 ALTER TABLE 문에 LOCK = EXCLUSIVE 가 포함되어있는 경우 DDL이 완료 될 때까지 쿼리가 대기 할 것을 예측 있습니다.

mysql: show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.01 sec)

mysql : - A trial query before any ADD INDEX in the other session :
mysql : - Note : because autocommit is enabled, each
mysql : - transaction finishes immediately after the query.
mysql : select distinct is_nullable from big_table;
+-------------+
| is_nullable |
+-------------+
| NO          |
| YES         |
+-------------+
2 rows in set (4.49 sec)

mysql : - Index is being created with LOCK = EXCLUSIVE on the ALTER statement.
mysql : - The query waits until the DDL is finished before proceeding.
mysql : select distinct is_nullable from big_table;
+-------------+
| is_nullable |
+-------------+
| NO          |
| YES         |
+-------------+

2 rows in set (17.26 sec)

mysql : - Index is being created with LOCK = SHARED on the ALTER statement.
mysql : - The query returns its results while the DDL is in progress.
mysql : - The same thing happens with LOCK = NONE on the ALTER statement.
mysql : select distinct is_nullable from big_table;
+-------------+
| is_nullable |
+-------------+
| NO          |
| YES         |
+-------------+
2 rows in set (3.11 sec)

mysql : - Once the index is created, and with no DDL in progress,
mysql : - queries referencing the indexed column are very fast :
mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   411648 |
+----------+
1 row in set (0.20 sec)

mysql : select distinct is_nullable from big_table;
+-------------+
| is_nullable |
+-------------+
| NO          |
| YES         |
+-------------+
2 rows in set (0.00 sec)

그런 다음이 병렬 세션에서 DML 문 또는 DML 문 및 쿼리의 조합을 포함한 여러 트랜잭션을 실행합니다. 테이블에 예측 가능하고 검증 가능한 변화를 설명하기 위해 DELETE 문을 사용합니다. 이 부분에있는 트랜잭션은 여러 문을 분산시킬 수 있기 때문에 이러한 테스트는 autocommit 이 꺼져있는 상태에서 실행합니다.

mysql : set global autocommit = off;
 Query OK, 0 rows affected (0.00 sec)

mysql : - Count the rows that will be involved in our DELETE statements :
mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   411648 |
+----------+
1 row in set (0.95 sec)

mysql : - After this point, any DDL statements back in the other session 
mysql : - stall until we commit or roll back.

mysql : delete from big_table where is_nullable = 'YES'limit 11648;
Query OK, 11648 rows affected (0.14 sec)

mysql : select count (*) from big_table where is_nullable = 'YES';
+ ---------- +
 | count (*) |
+ ---------- +
| 400000 |
+ ---------- +
1 row in set (1.04 sec)

mysql : rollback;
Query OK, 0 rows affected (0.09 sec)

mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (1.04 sec)

mysql : - OK, now we 're going to try that during index creation with LOCK = NONE.
mysql : delete from big_table where is_nullable = 'YES'limit 11648;
Query OK, 11648 rows affected (0.21 sec)

mysql : - We expect that now there will be 400000 'YES'rows left :
mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   411648 |
+----------+
1 row in set (0.93 sec)

mysql : - In the other session, the ALTER TABLE is waiting before finishing,
mysql : - because _this_ transaction has not committed or rolled back yet.
mysql : rollback;
Query OK, 0 rows affected (0.11 sec)

mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   411648 |
+----------+
1 row in set (0.19 sec)

mysql : - The ROLLBACK left the table in the same state we originally found it.
mysql : - Now let 's make a permanent change while the index is being created,
mysql : - again with ALTER TABLE ... LOCK = NONE.
mysql : - First, commit so the DROP INDEX in the other shell can finish;
mysql : - the previous SELECT started a transaction that accessed the table.
mysql : commit;
 Query OK, 0 rows affected (0.00 sec)

mysql : - Now we add the index back in the other shell, then issue DML in this one
mysql : - while the DDL is running.
mysql : delete from big_table where is_nullable = 'YES'limit 11648;
Query OK, 11648 rows affected (0.23 sec)

mysql : commit;
 Query OK, 0 rows affected (0.01 sec)

mysql : - In the other shell, the ADD INDEX has finished.
mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (0.19 sec)

mysql : - At the point the new index is finished being created, it contains entries
mysql : - only for the 400000 'YES'rows left when all concurrent transactions are finished.
mysql : 
mysql : - Now we will run a similar test, while ALTER TABLE ... LOCK = SHARED is running.
mysql : - We expect a query to complete during the ALTER TABLE, but for the DELETE
mysql : - to run into some kind of issue.
mysql : commit;
 Query OK, 0 rows affected (0.00 sec)

mysql : - As expected, the query returns results while the LOCK = SHARED DDL is running :
mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (2.07 sec)

mysql : - The DDL in the other session is not going to finish until this transaction
mysql : - is committed or rolled back. If we tried a DELETE now and it waited because
mysql : - of LOCK = SHARED on the DDL, both transactions would wait forever (deadlock).
mysql : - MySQL detects this condition and cancels the attempted DML statement.
mysql : delete from big_table where is_nullable = 'YES'limit 100000;
ERROR 1213 (40001) : Deadlock found when trying to get lock; try restarting transaction
mysql : - The transaction here is still going, so in the other shell, the ADD INDEX operation
mysql : - is waiting for this transaction to commit or roll back.
mysql : rollback;
 Query OK, 0 rows affected (0.00 sec)

mysql : - Now let 's try issuing a query and some DML, on one line, while running
mysql : - ALTER TABLE ... LOCK = EXCLUSIVE in the other shell.
mysql : - Notice how even the query is held up until the DDL is finished.
mysql : - By the time the DELETE is issued, there is no conflicting access
mysql : - to the table and we avoid the deadlock error.
mysql : select count (*) from big_table where is_nullable = 'YES'; delete from big_table where is_nullable = 'YES'limit 100000;
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (15.98 sec)

Query OK, 100000 rows affected (2.81 sec)

mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.17 sec)

mysql : rollback;
Query OK, 0 rows affected (1.36 sec)

mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   400000 |
+----------+
1 row in set (0.19 sec)

mysql : commit;
 Query OK, 0 rows affected (0.00 sec)

mysql : - Next, we try ALTER TABLE ... LOCK = EXCLUSIVE in the other session
mysql : - and only issue DML, not any query, in the concurrent transaction here.
mysql : delete from big_table where is_nullable = 'YES'limit 100000;
Query OK, 100000 rows affected (16.37 sec)

mysql : - That was OK because the ALTER TABLE did not have to wait for the transaction
mysql : - here to complete. The DELETE in this session waited until the index was ready.
mysql : select count (*) from big_table where is_nullable = 'YES';
+----------+
| count(*) |
+----------+
|   300000 |
+----------+
1 row in set (0.16 sec)

mysql : commit;
 Query OK, 0 rows affected (0.00 sec)

이전 목록 예제에서는 다음을 알 수있었습니다.

하나의 ALTER TABLE 문에서 테이블에 여러 인덱스를 만들 수 있습니다. 테이블의 클러스터 된 인덱스는 한 번만 스캔 할 필요가 없다 (그러나 데이터는 새로운 인덱스에 대해 개별적으로 정렬됩니다) 때문에 이것은 매우 효율적입니다. 예 :

CREATE TABLE T1 (A INT PRIMARY KEY, B INT, C CHAR (1)) ENGINE = InnoDB;
INSERT INTO T1 VALUES (1, 'a'), (2, 'b'), (3,2, 'c'), (4,3 'd'), (5,2 ' e ');
 COMMIT;
ALTER TABLE T1 ADD INDEX (B), ADD UNIQUE INDEX (C);

컬럼 A 에 기본 키에 대한 문 CREATE TABLE T1 은 여러 행을 삽입 한 뒤, 컬럼 B 와 C 에 2 개의 새로운 인덱스를 구축합니다. ALTER TABLE 문 앞에 T1 에 많은 행이 삽입되어 있었다고하면이 방법은 데이터를로드하기 전에 모든 보조 인덱스를 만드는 것보다 훨씬 효율적입니다.

InnoDB 보조 인덱스의 삭제에 테이블 데이터 복사는 필요 없기 때문에 하나의 ALTER TABLE 문 이상의 DROP INDEX 문에서 여러 인덱스를 삭제하는 것은 동일 효율적입니다.

ALTER TABLE T1 DROP INDEX B, DROP INDEX C;

또는

DROP INDEX B ON T1;
DROP INDEX C ON T1;

InnoDB 테이블의 클러스터 된 인덱스 의 재구성은 항상 테이블 데이터의 복사본이 필요합니다. 따라서 테이블의 재구성을 방지하기 위해 나중에 ALTER TABLE ... ADD PRIMARY KEY 를 발행하는 것이 아니라 테이블을 만들 때 기본 키 를 정의하는 것이 좋습니다.

다음의 예와 같이 나중에 PRIMARY KEY 를 정의하면 데이터가 복사됩니다.

CREATE TABLE T2 (A INT, B INT);
INSERT INTO T2 VALUES (NULL, 1);
ALTER TABLE T2 ADD PRIMARY KEY (B);

UNIQUE 또는 PRIMARY KEY 인덱스를 만들 때, MySQL은 몇 가지 추가 작업을 수행해야합니다. UNIQUE 인덱스의 경우 MySQL은 테이블에 중복 키 값이 포함되어 있지 않은지 확인합니다. PRIMARY KEY 인덱스의 경우도, MySQL은 어떤 PRIMARY KEY 컬럼에 NULL 이 포함되어 있지 않은지 확인합니다.

ALGORITHM = COPY 절을 사용하여 기본 키를 추가 할 때 MySQL은 실제로 연결된 컬럼의 NULL 값을 기본값, 즉 숫자 0은 문자 기반의 컬럼이나 BLOB이면 빈 문자열 및 DATETIME 의 경우 0000-00-00 00:00:00로 변환합니다. 이것은 비표준 동작이기 때문에 이것에 의존하지 않도록하는 것이 좋습니다. ALGORITHM = INPLACE 를 사용하여 기본 키의 추가는 SQL_MODE 설정에 strict_trans_tables 또는 strict_all_tables 플래그가 포함되어있는 경우에만 허용됩니다. SQL_MODE 설정이 정확할 경우 ADD PRIMARY KEY ... ALGORITHM = INPLACE 이 허용되지만 요청 된 프라이 머리 키 컬럼에 NULL 값이 포함되어 있으면 문은 계속 실패합니다. ALGORITHM = INPLACE 의 동작은 더 표준을 준수하고 있습니다.

다음의 예는 ADD PRIMARY KEY 절 몇 가지 가능성을 보여줍니다. ALGORITHM = COPY 절을 사용하면 프라이 머리 키 컬럼에 NULL 값이 있어도 작업이 성공합니다. 데이터는 암묵적으로 변경되고 그로 인해 문제가 발생할 수 있습니다.

 mysql> CREATE TABLE add_pk_via_copy (c1 INT, c2 VARCHAR (10), c3 DATETIME);
 Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO add_pk_via_copy VALUES (1, 'a', '2014-11-03 11:01:37'), (NULL, NULL, NULL);
 Query OK, 2 rows affected (0.00 sec)
Records : 2 Duplicates : 0 Warnings : 0

mysql> SET sql_mode = '';
 Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_copy ADD PRIMARY KEY (c1, c2, c3) ALGORITHM = COPY;
Query OK, 2 rows affected 3 warnings (0.07 sec)
Records : 2 Duplicates : 0 Warnings : 3

mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1265 | Data truncated for column 'c1' at row 2 |
| Warning | 1265 | Data truncated for column 'c2' at row 2 |
| Warning | 1265 | Data truncated for column 'c3' at row 2 |
+---------+------+-----------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM add_pk_via_copy;
+----+----+---------------------+
| c1 | c2 | c3                  |
+----+----+---------------------+
|  0 |    | 0000-00-00 00:00:00 |
|  1 | a  | 2014-11-03 11:01:37 |
+----+----+---------------------+
2 rows in set (0.00 sec)
        
      

ALGORITHM = INPLACE 절을 사용하는 경우이 설정은 데이터의 무결성을 높은 우선 순위로 간주하고 있기 때문에 작업은 여러 가지 이유로 실패 할 수 있습니다. 이 문은 SQL_MODE 설정이 충분히 " 엄격 " 가 아닌 경우, 또는 프라이 머리 키 컬럼에 NULL 값이 포함되어있는 경우 오류를 출력합니다. 이 두 요건을 충족하면 ALTER TABLE 작업이 성공합니다.

 mysql> CREATE TABLE add_pk_via_inplace (c1 INT, c2 VARCHAR (10), c3 DATETIME);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO add_pk_via_inplace VALUES (1, 'a', '2014-11-03 11:01:37'), (NULL, NULL, NULL);
 Query OK, 2 rows affected (0.00 sec)
Records : 2 Duplicates : 0 Warnings : 0

mysql> SELECT * FROM add_pk_via_inplace;
+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|    1 | a    | 2014-11-03 11:01:37 |
| NULL | NULL | NULL                |
+------+------+---------------------+
2 rows in set (0.00 sec)

mysql> SET sql_mode = '';
 Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace AD​​D PRIMARY KEY (c1, c2, c3) ALGORITHM = INPLACE;
ERROR 1846 (0A000) : ALGORITHM = INPLACE is not supported. Reason : can not silently convert NULL values​​, 
as required in this SQL_MODE. Try ALGORITHM = COPY.

mysql> SET sql_mode = 'strict_trans_tables';
 Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace AD​​D PRIMARY KEY (c1, c2, c3) ALGORITHM = INPLACE;
ERROR 1138 (22004) : Invalid use of NULL value
mysql> DELETE FROM add_pk_via_inplace WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL;
 Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM add_pk_via_inplace;
+------+------+---------------------+
| c1   | c2   | c3                  |
+------+------+---------------------+
|    1 | a    | 2014-11-03 11:01:37 |
+------+------+---------------------+
1 row in set (0.00 sec)

mysql> ALTER TABLE add_pk_via_inplace AD​​D PRIMARY KEY (c1, c2, c3) ALGORITHM = INPLACE;
Query OK, 0 rows affected (0.09 sec)
Records : 0 Duplicates : 0 Warnings : 0

기본 키없이 테이블을 작성하면, InnoDB는 기본 키를 자동으로 선택합니다. 이것은 NOT NULL 컬럼에 정의 된 최초의 UNIQUE 키 또는 시스템에서 생성 된 키가 될 수 있습니다. 숨겨진 특별한 컬럼의 불확실성과 그에 대한 공간 요구 사항이 발생할 가능성을 배제하려면 CREATE TABLE 문의 일부로 PRIMARY KEY 절을 지정합니다.