5.5.5. Примеры Онлайнового DDL

Вот примеры кода, показывая некоторые операции, производительность которых, параллелизм, и масштабируемость улучшаются последними онлайновыми улучшениями DDL.

Пример 5.1, "Код Установки схемы для Онлайновых Экспериментов DDL" устанавливает названные таблицы BIG_TABLE и SMALL_TABLE используемый в последующих примерах.
Пример 5.2, "Скорость и Эффективность CREATE INDEX и DROP INDEX" иллюстрируют аспекты производительности создания и отбрасывания, индексирует.
Пример 5.3, "Параллельный DML Во время CREATE INDEX и DROP INDEX" показывает запросы и операторы DML, работающие во время a DROP INDEX работа.
Пример 5.4, "Переименовывая Столбец" демонстрирует улучшение скорости для того, чтобы переименовать столбец, и показывает, что забота должна была сохранить тип данных точно тем же самым, делая переименовать работу.
Пример 5.5, "Отбрасывая Внешние ключи" демонстрирует, как внешние ключи работают с онлайновым DDL. Поскольку две таблицы включаются в операции внешнего ключа, есть дополнительные соображения блокировки. Таким образом у таблиц с внешними ключами иногда есть ограничения для онлайновых операций DDL.
Пример 5.6, "Изменяя Автоинкрементное Значение" демонстрирует, как столбцы автоприращения работают с онлайновым DDL. У таблиц со столбцами автоприращения иногда есть ограничения для онлайновых операций DDL.
Пример 5.7, "Управляя Параллелизмом с LOCK Пункт" демонстрирует опции, чтобы разрешить или ограничить параллельные запросы и операции DML, в то время как онлайновая работа DDL происходит. Это показывает ситуации, когда оператор DDL мог бы ожидать, или параллельная транзакция могла бы ожидать, или параллельная транзакция могла бы отменить оператор DML из-за ошибки мертвой блокировки.
Пример 5.8, "Код Установки схемы для Онлайновых Экспериментов DDL" демонстрирует, как создавать и отбрасывать многократный, индексирует в единственном операторе, который может быть более эффективным, чем использование отдельного оператора для каждого индексирует работу.
Пример 5.9, "Создавая и Отбрасывая Первичный ключ" демонстрирует, как более эффективно определить первичный ключ, составляя таблицу, и относительно дорогой, чтобы добавить один позже.

Пример 5.1. Код Установки схемы для Онлайновых Экспериментов DDL

Вот код, который устанавливает начальные таблицы, используемые на этих демонстрациях:

/* 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\Ginsert 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: 0Query 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_tableCreate 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` varchar(1024) CHARACTER SET utf8 NOT NULL DEFAULT '') ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)Query OK, 1678 rows affected (0.09 sec)Records: 1678  Duplicates: 0  Warnings: 0Query OK, 3356 rows affected (0.07 sec)Records: 3356  Duplicates: 0  Warnings: 0Query OK, 6712 rows affected (0.17 sec)Records: 6712  Duplicates: 0  Warnings: 0Query OK, 13424 rows affected (0.44 sec)Records: 13424  Duplicates: 0  Warnings: 0Query OK, 26848 rows affected (0.63 sec)Records: 26848  Duplicates: 0  Warnings: 0Query OK, 53696 rows affected (1.72 sec)Records: 53696  Duplicates: 0  Warnings: 0Query OK, 107392 rows affected (3.02 sec)Records: 107392  Duplicates: 0  Warnings: 0Query OK, 214784 rows affected (6.28 sec)Records: 214784  Duplicates: 0  Warnings: 0Query OK, 429568 rows affected (13.25 sec)Records: 429568  Duplicates: 0  Warnings: 0Query OK, 859136 rows affected (28.16 sec)Records: 859136  Duplicates: 0  Warnings: 0Query 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)

Пример 5.2. Скорость и Эффективность CREATE INDEX и DROP INDEX

Вот последовательность операторов, демонстрирующих относительную скорость CREATE INDEX и DROP INDEX операторы. Для маленькой таблицы прошедшее время является меньше чем секунда, используем ли мы быстрый или медленный метод, таким образом, мы смотрим на "строки" вывод, на который влияют, чтобы проверить, какие операции могут избежать, чтобы таблица восстановила. Для большой таблицы различие в эффективности очевидно, потому что пропуск таблицы восстанавливает, экономит существенное время.

\! 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.ibdcreate index i_dtyp_small on small_table (data_type), algorithm=inplace;\! echo "Data size after index created: "\! du -k data/test/small_table.ibddrop 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.ibdcreate index i_dtyp_small on small_table (data_type), algorithm=copy;\! echo "Data size after index created: "\! du -k data/test/small_table.ibddrop 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.ibdcreate index i_dtyp_big on big_table (data_type), algorithm=inplace;\! echo "Data size after index created: "\! du -k data/test/big_table.ibddrop 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.ibdcreate index i_dtyp_big on big_table (data_type), algorithm=copy;\! echo "Data size after index created: "\! du -k data/test/big_table.ibddrop 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.ibdQuery OK, 0 rows affected (0.04 sec)Records: 0  Duplicates: 0  Warnings: 0Data size after index created: 432  data/test/small_table.ibdQuery OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0Query 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.ibdQuery OK, 1678 rows affected (0.12 sec)Records: 1678  Duplicates: 0  Warnings: 0Data size after index created: 448  data/test/small_table.ibdQuery OK, 1678 rows affected (0.10 sec)Records: 1678  Duplicates: 0  Warnings: 0Query 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.ibdQuery OK, 0 rows affected (33.32 sec)Records: 0  Duplicates: 0  Warnings: 0Data size after index created: 335872  data/test/big_table.ibdQuery OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0Query 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.ibdQuery OK, 1718272 rows affected (1 min 5.01 sec)Records: 1718272  Duplicates: 0  Warnings: 0Data size after index created: 348160  data/test/big_table.ibdQuery OK, 1718272 rows affected (46.59 sec)Records: 1718272  Duplicates: 0  Warnings: 0

Пример 5.3. Параллельный DML Во время CREATE INDEX и DROP INDEX

Вот некоторые отрывки кода, который я выполнял в отдельных mysql сеансах, соединенных с той же самой базой данных, чтобы иллюстрировать операторы DML (вставьте, обновите, или удалите), работающий одновременно как CREATE INDEX и DROP INDEX.

/*CREATE INDEX statement to run against a table while insert/update/delete statements are modifying thecolumn 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 whileinsert/update/delete statements are modifying thecolumn 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 tablewhile an index is being created or dropped. Previously, these operationswould have stalled during the index create/drop period and possiblytimed 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.sqlDatabase changedQuery OK, 0 rows affected (1 min 25.15 sec)Records: 0  Duplicates: 0  Warnings: 0mysql: source concurrent_ddl_drop.sqlDatabase changedQuery OK, 0 rows affected (24.98 sec)Records: 0  Duplicates: 0  Warnings: 0mysql: source concurrent_dml.sqlQuery 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)

Пример 5.4. Переименование Столбца

Вот демонстрация использования ALTER TABLE переименовать столбец. Мы используем новый, быстрый механизм DDL, чтобы изменить название, тогда старый, медленный механизм DDL (с old_alter_table=1) восстановить исходное имя столбца.

Примечания:

Поскольку синтаксис для того, чтобы переименовать столбец также включает переопределение типа данных, очень делать все возможное определить точно тот же самый тип данных, чтобы избежать, чтобы дорогостоящая таблица восстановила. В этом случае мы проверяли вывод show create table table\G и скопированный любые пункты такой как CHARACTER SET и NOT NULL из исходного определения столбца.
Снова, переименование столбца для маленькой таблицы достаточно быстро, что мы должны исследовать "строки" число, на которое влияют, чтобы проверить, что новый механизм DDL более эффективен чем старый. С большой таблицей различие в прошедшее время делает улучшение очевидным.

/*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: 0Rename 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: 0Rename 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: 0Rename 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: 0Query OK, 0 rows affected (0.00 sec)

Пример 5.5. Отбрасывание Внешних ключей

Вот демонстрация внешних ключей, включая улучшение скорости отбрасывания ограничения внешнего ключа.

/*Demonstrate aspects of foreign keys that are or aren't 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\Gshow create table small_table\Gshow 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\Gshow create table big_table\Gselect 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 wasn't the requirement for the corresponding-- columns to be indexed!-- But we can get rid of the foreign key using a fast operation-- that doesn't rebuild the table.-- If we didn't 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\Gshow 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_namesCreate 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=latin11 row in set (0.00 sec)*************************** 1. row ***************************       Table: small_tableCreate 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` varchar(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=latin11 row in set (0.00 sec)*************************** 1. row ***************************       Table: big_tableCreate 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` varchar(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=latin11 row in set (0.00 sec)Query OK, 1678 rows affected (0.10 sec)Records: 1678  Duplicates: 0  Warnings: 0Query OK, 1718272 rows affected (1 min 14.54 sec)Records: 1718272  Duplicates: 0  Warnings: 0*************************** 1. row ***************************       Table: small_tableCreate 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` varchar(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=latin11 row in set (0.12 sec)*************************** 1. row ***************************       Table: big_tableCreate 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` varchar(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=latin11 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: 0DROP FOREIGN KEY from big_table:Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0*************************** 1. row ***************************       Table: small_tableCreate 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` varchar(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=latin11 row in set (0.00 sec)*************************** 1. row ***************************       Table: big_tableCreate 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` varchar(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=latin11 row in set (0.00 sec)

Пример 5.6. Изменение Автоинкрементного Значения

Вот иллюстрация увеличения автоинкрементного более низкого предела для столбца таблицы, демонстрируя, как эта работа теперь избегает, чтобы таблица восстановила плюс некоторые другие забавные факты о InnoDB столбцы автоприращения.

/*If this script is run after foreign_key.sql, the schema_names table isalready set up. But to allow this script to run multiple times withoutrunning into duplicate ID errors, we set up the schema_names tableall over again.*/\! clear\! echo "=== Adjusting the Auto-Increment Limit for a Table ==="\! echodrop 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\Gselect * 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: 0Initial 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_namesCreate 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=latin11 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: 0Query 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: 0Inserting 2 rows that should get IDs 20 and 21:Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0Query 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: 0Inserting 2 rows that should get IDs 30 and 31:Query OK, 2 rows affected (0.00 sec)Records: 2  Duplicates: 0  Warnings: 0Query 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_namesCreate 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=latin11 row in set (0.00 sec)

Пример 5.7. Управление Параллелизмом с LOCK Пункт

Этот пример показывает, как использовать LOCK пункт ALTER TABLE оператор, чтобы позволить или лишить параллельного доступа к таблице, в то время как онлайновая работа DDL происходит. У пункта есть настройки, которые позволяют запросы и операторы DML (LOCK=NONE), только запрашивает (LOCK=SHARED), или никакой параллельный доступ вообще (LOCK=EXCLUSIVE).

В одном сеансе мы выполняем последовательность ALTER TABLE операторы, чтобы создать и отбросить индексирование, используя различные значения для LOCK пункт, чтобы видеть, что происходит с ожиданием или заведением в тупик в любом сеансе. Мы используем то же самое BIG_TABLE таблица как в предыдущих примерах, запускающихся приблизительно с 1.7 миллионов строк. В целях иллюстрации мы индексируем и запросим 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. Иногда, ALTER TABLE берет необычно долго, потому что это ожидает другой транзакции, чтобы закончиться, когда та транзакция, измененная таблица во время DDL или, запрашивала таблицу перед DDL:

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 concurrentmysql: -- 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 causesmysql: -- the ALTER TABLE above to stall until the transactionmysql: -- surrounding the SELECT is committed or rolled back.

Вот журнал от другого сеанса, работающего одновременно, где мы выпускаем запросы и операторы DML против таблицы прежде, во время, и после операций DDL, показанных в предыдущих списках. Это первое перечисление шоу запрашивает только. Мы ожидаем, что запросы будут позволены во время использования операций DDL LOCK=NONE или LOCK=SHARED, и для запроса, чтобы ожидать, пока DDL не заканчивается если ALTER TABLE оператор включает LOCK=EXCLUSIVE.

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, eachmysql: -- 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(*) |+----------+|   411648 |+----------+1 row in set (0.93 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(*) |+----------+|   400000 |+----------+1 row in set (1.25 sec)mysql: -- In the other session, the ALTER TABLE is waiting before finishing,mysql: -- because _this_ transaction hasn't 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 onemysql: -- 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 entriesmysql: -- 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 DELETEmysql: -- 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 transactionmysql: -- is committed or rolled back. If we tried a DELETE now and it waited becausemysql: -- 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 transactionmysql: -- The transaction here is still going, so in the other shell, the ADD INDEX operationmysql: -- 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 runningmysql: -- 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 accessmysql: -- 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 sessionmysql: -- 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 transactionmysql: -- 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)

В предыдущих списках в качестве примера мы узнали что:

LOCK пункт для ALTER TABLE выделяется от остальной части оператора запятой.
Онлайновые операции DDL могли бы ожидать перед запуском, пока любые предшествующие транзакции, которые получают доступ к таблице, не фиксируются или откатываются.
Онлайновые операции DDL могли бы ожидать перед завершением, пока любые параллельные транзакции, которые получают доступ к таблице, не фиксируются или откатываются.
В то время как онлайновая работа DDL работает, параллельные запросы являются относительно прямыми, пока ALTER TABLE использование оператора LOCK=NONE или LOCK=SHARED.
Обратите внимание на ли autocommit включается или выключается. Если это выключается, делайте все возможное закончить транзакции в других сеансах (даже только запросы) прежде, чем выполнить операции DDL на таблице.
С LOCK=SHARED, параллельные транзакции, которые смешивают запросы и DML, могли встретиться с ошибками мертвой блокировки и иметь, чтобы быть перезапущенными после того, как DDL заканчивается.
С LOCK=NONE, параллельные транзакции могут свободно смешать запросы и DML. Работа DDL ожидает, пока параллельные транзакции не фиксируются или откатываются.
С LOCK=NONE, параллельные транзакции могут свободно смешать запросы и DML, но те транзакции ожидают, пока работа DDL не заканчивается прежде, чем они смогут получить доступ к таблице.

Пример 5.8. Код Установки схемы для Онлайновых Экспериментов DDL

Можно создать многократный, индексирует на таблице с одним ALTER TABLE оператор. Это относительно эффективно, потому что кластерный индекс таблицы должен быть отсканирован только однажды (хотя данные сортируются отдельно для каждого нового, индексируют). Например:

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

Вышеупомянутые операторы составляют таблицу T1 с первичным ключом на столбце A, вставьте несколько строк, затем создайте два новый, индексирует на столбцах B и C. Если было много строк, вставленных в T1 перед ALTER TABLE оператор, этот подход намного более эффективен, чем создание всего вторичного устройства индексирует прежде, чем загрузить данные.

Поскольку отбрасывание вторичного InnoDB индексирует, также не требует никакого копирования табличных данных, одинаково эффективно отбросить многократный, индексирует с синглом ALTER TABLE оператор или многократный DROP INDEX операторы:

ALTER TABLE T1 DROP INDEX B, DROP INDEX C;

или:

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

Пример 5.9. Создание и Отбрасывание Первичного ключа

Реструктурирование кластерного индекса для InnoDB таблица всегда требует копирования табличных данных. Таким образом, лучше определять первичный ключ, когда Вы составляете таблицу вместо издания ALTER TABLE ... ADD PRIMARY KEY позже, чтобы избежать восстанавливать таблицу.

Определение a PRIMARY KEY более поздние причины данные, которые будут скопированы, как в следующем примере:

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

Когда Вы создаете a UNIQUE или PRIMARY KEY индексируйте, MySQL должен сделать некоторую дополнительную работу. Для UNIQUE индексирует, MySQL проверяет, что таблица не содержит двойных значений для ключа. Для a PRIMARY KEY индексируйте, MySQL также проверяет что ни один из PRIMARY KEY столбцы содержат a NULL.

Когда Вы добавляете первичный ключ, используя ALGORITHM=COPY пункт, MySQL фактически преобразовывает NULL значения в связанных столбцах к значениям по умолчанию: 0 для чисел, пустой строки для символьно-ориентированных столбцов и BLOB, и 1 января 1975 для дат. Это - нестандартное поведение, что Oracle рекомендует, чтобы Вы не положились. Добавление использования первичного ключа 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 значения в столбцах первичного ключа; данные тихо изменяются, который мог вызвать проблемы. С ALGORITHM=INPLACE пункт, работа могла перестать работать по различным причинам, потому что эта установка считает целостность данных высоким приоритетом: оператор дает ошибку если SQL_MODE установка не достаточно "строга", или если столбцы первичного ключа содержат кого-либо NULL значения. Как только мы адресуем оба из тех требований, ALTER TABLE работа успешно выполняется.

CREATE TABLE add_pk_via_copy (c1 INT, c2 VARCHAR(10), c3 DATETIME);INSERT INTO add_pk_via_copy VALUES (1,'a','...'),(NULL,NULL,NULL);ALTER TABLE add_pk_via_copy ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=COPY;SELECT * FROM add_pk_via_copy;CREATE TABLE add_pk_via_inplace (c1 INT, c2 VARCHAR(10), c3 DATETIME);INSERT INTO add_pk_via_inplace VALUES (1,'a','...'),(NULL,NULL,NULL);SET sql_mode = 'strict_trans_tables';ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=COPY;SET sql_mode = '';ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=COPY;DELETE FROM add_pk_via_inplace WHERE c1 IS NULL OR c2 IS NULL OR c3 IS NULL;ALTER TABLE add_pk_via_inplace ADD PRIMARY KEY (c1,c2,c3), ALGORITHM=COPY;SELECT * FROM add_pk_via_inplace;

Если Вы составляете таблицу без первичного ключа, InnoDB выбирает один для Вас, которые могут быть первыми UNIQUE ключ, определенный на NOT NULL столбцы, или сгенерированный системой ключ. Чтобы избежать любой неопределенности и потенциального требования пространства для дополнительного скрытого столбца, определите PRIMARY KEY пункт как часть CREATE TABLE оператор.