Table options - Problems with slow ALTER TABLE in MySQL

Lecture

Это продолжение увлекательной статьи про alter table.

...

style="color:rgb(0, 119, 170); font-size:12.8304px">DROP PARTITION partition_names | DISCARD PARTITION {partition_names | ALL} TABLESPACE | IMPORT PARTITION {partition_names | ALL} TABLESPACE | TRUNCATE PARTITION {partition_names | ALL} | COALESCE PARTITION number | REORGANIZE PARTITION partition_names INTO (partition_definitions) | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH | WITHOUT} VALIDATION] | ANALYZE PARTITION {partition_names | ALL} | CHECK PARTITION {partition_names | ALL} | OPTIMIZE PARTITION {partition_names | ALL} | REBUILD PARTITION {partition_names | ALL} | REPAIR PARTITION {partition_names | ALL} | REMOVE PARTITIONING | UPGRADE PARTITIONING } key_part: col_name [(length)] [ASC | DESC] index_type: USING {BTREE | HASH} index_option: { KEY_BLOCK_SIZE [=] value | index_type | WITH PARSER parser_name | COMMENT 'string' } table_options: table_option [[,] table_option] ... table_option: { AUTO_INCREMENT [=] value | AVG_ROW_LENGTH [=] value | [DEFAULT] CHARACTER SET [=] charset_name | CHECKSUM [=] {0 | 1} | [DEFAULT] COLLATE [=] collation_name | COMMENT [=] 'string' | COMPRESSION [=] {'ZLIB' | 'LZ4' | 'NONE'} | CONNECTION [=] 'connect_string' | {DATA | INDEX} DIRECTORY [=] 'absolute path to directory' | DELAY_KEY_WRITE [=] {0 | 1} | ENCRYPTION [=] {'Y' | 'N'} | ENGINE [=] engine_name | INSERT_METHOD [=] { NO | FIRST | LAST } | KEY_BLOCK_SIZE [=] value | MAX_ROWS [=] value | MIN_ROWS [=] value | PACK_KEYS [=] {0 | 1 | DEFAULT} | PASSWORD [=] 'string' | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT} | STATS_AUTO_RECALC [=] {DEFAULT | 0 | 1} | STATS_PERSISTENT [=] {DEFAULT | 0 | 1} | STATS_SAMPLE_PAGES [=] value | TABLESPACE tablespace_name [STORAGE {DISK | MEMORY}] | UNION [=] (tbl_name[,tbl_name]...) } partition_options: (see CREATE TABLE options)

ALTER TABLE changes the structure of a table. For example, you can add or remove columns, create or drop indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment.

To use ALTER TABLE, you need the ALTER, CREATE and INSERT privileges for the table. Renaming a table requires ALTER and DROP on the old table plus ALTER, CREATE and INSERT on the new table.
Following the table name, specify the changes to be made. If nothing is given, ALTER TABLE does nothing.
The syntax of many of the permitted alterations is similar to clauses of the CREATE TABLE statement. The column_definition clause uses the same syntax for ADD and CHANGE as for CREATE TABLE. For more information, see Section 13.1.18, «CREATE TABLE Statement».
The word COLUMN is optional and can be omitted.
Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which allows only one instance of each clause per ALTER TABLE statement. For example, to drop multiple columns in a single statement, do the following:
```
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
```
If the storage engine does not support the attempted ALTER TABLE operation, a warning may appear. Such warnings can be displayed with SHOW WARNINGS. See Section 13.7.5.40, «SHOW WARNINGS». For information on troubleshooting ALTER TABLE, see Section B.3.6.1, «Problems with ALTER TABLE».
For information on generated columns, see Section 13.1.8.2, «ALTER TABLE and Generated Columns».
For usage examples, see Section 13.1.8.3, «ALTER TABLE Examples».
Using the mysql_info() C API function, you can find out how many rows were copied by ALTER TABLE. See mysql_info().

Several additional aspects of the ALTER TABLE statement are covered in the following sections of this section:

Table options
Performance and space requirements
Concurrency control
Adding and removing columns
Renaming, redefining, and reordering columns
Primary keys and indexes
Foreign keys and other constraints
Changing the character set
Discarding and importing InnoDB tablespaces
Row order for MyISAM tables
Partitioning options

Table options

table_options refers to the various table options that can be used in a CREATE TABLE statement, such as ENGINE, AUTO_INCREMENT, AVG_ROW_LENGTH, MAX_ROWS, ROW_FORMAT, or TABLESPACE.

For a description of all table options, see Section 13.1.18, «CREATE TABLE Statement». However, ALTER TABLE ignores DATA DIRECTORY and INDEX DIRECTORY if specified as table options. ALTER TABLE permits them only as partitioning options and, starting with MySQL 5.7.17, requires that you have the FILE privilege.

Using table options with ALTER TABLE provides a convenient way to change the characteristics of an individual table. For example:

If t1 is not currently an InnoDB table, this statement changes its storage engine to InnoDB:
```
ALTER TABLE t1 ENGINE = InnoDB;
```
- See Section 14.6.1.5, «Converting Tables from MyISAM to InnoDB» to learn how to switch tables to the InnoDB storage engine.
- When you specify an ENGINE clause, ALTER TABLE rebuilds the table. This is true even if the table already has the specified storage engine.
- Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a «null» operation that can be used to defragment the table, as described in Section 14.12.4, «Defragmenting a Table». Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.
- ALTER TABLE tbl_name ENGINE=INNODB and ALTER TABLE tbl_name FORCE use online DDL. For more information, see Section 14.13, «InnoDB and Online DDL».
- The result of attempting to change a table's storage engine depends on whether the desired storage engine is available and on the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.10, «Server SQL Modes».
- To prevent unintended data loss, ALTER TABLE cannot be used to change a table's storage engine to MERGE or BLACKHOLE.
To change an InnoDB table to use a compressed row storage format:
```
ALTER TABLE t1 ROW_FORMAT = COMPRESSED;
```
To enable or disable encryption for an InnoDB table in a "file-per-table" tablespace:
```
ALTER TABLE t1 ENCRYPTION='Y';
ALTER TABLE t1 ENCRYPTION='N';
```
To use this ENCRYPTION option, you must install and configure the keyring plugin. For more information, see Section 14.14, «InnoDB Data-at-Rest Encryption».
To reset the current auto-increment value:
```
ALTER TABLE t1 AUTO_INCREMENT = 13;
```
You cannot reset the counter to a value less than or equal to the current value. For both InnoDB and MyISAM, if the value is less than or equal to the current maximum value in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.
To change a table's default character set:
```
ALTER TABLE t1 CHARACTER SET = utf8;
```
See also Changing the Character Set.

To add (or change) a table comment:

ALTER TABLE t1 COMMENT = 'New table comment';

Use ALTER TABLE with the TABLESPACE option to move InnoDB tables between existing general tablespaces, file-per-table tablespaces, and the system tablespace. See «Moving Tables Between Tablespaces with ALTER TABLE».
- ALTER TABLE ... TABLESPACE operations always cause a full rebuild of the table, even if the TABLESPACE attribute has not changed from its previous value.
- ALTER TABLE ... TABLESPACE syntax does not support moving a table from a temporary tablespace to a permanent tablespace.
- The DATA DIRECTORY clause, which is supported by CREATE TABLE ... TABLESPACE, is not supported by ALTER TABLE ... TABLESPACE and is ignored if specified.
- For more information about the capabilities and limitations of this TABLESPACE option, see the CREATE TABLE section.
MySQL NDB Cluster 7.5.2 and later supports NDB_TABLE settings to control the balance of table partitions (fragment count type), the ability to read from any replica, full replication, or any combination of these as part of the table comment for the ALTER TABLE statement, in the same way as for CREATE TABLE, as shown in this example:
```
ALTER TABLE t1 COMMENT = "NDB_TABLE=READ_BACKUP=0,PARTITION_BALANCE=FOR_RA_BY_NODE";
```
Keep in mind that ALTER TABLE ... COMMENT ... discards any existing comments for the table. See the section on setting NDB_TABLE options for more information and examples.

To verify that the table options were changed as expected, use SHOW CREATE TABLE or query INFORMATION_SCHEMA.TABLES.

Performance and space requirements

ALTER TABLE operations are processed using one of the following algorithms:

COPY: Operations are performed on a copy of the original table, and table data is copied from the original table into the new table row by row. Concurrent DML use is not allowed.
INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during the preparation and execution phases of the operation. Concurrent DML is generally supported.

The ALGORITHM clause is optional. If the ALGORITHM clause is omitted, MySQL uses ALGORITHM=INPLACE for storage engines and ALTER TABLE clauses that support it. Otherwise, ALGORITHM=COPY is used.

Specifying the ALGORITHM clause requires that the operation use the specified algorithm for the clauses and storage engines that support it; otherwise it fails with an error. Specifying ALGORITHM=DEFAULT is equivalent to omitting the ALGORITHM clause.

ALTER TABLE operations that use the COPY algorithm wait for other operations modifying the table to complete. After changes have been made to the copy of the table, the data is copied over, the original table is deleted, and the table copy is renamed to the name of the original table. While the ALTER TABLE operation is executing, the original table is available for reading by other sessions (except as briefly noted). Updates and writes to the table started after the ALTER TABLE operation begins are stalled until the new table is ready, and are then automatically redirected to the new table. A temporary copy of the table is created in the database directory of the original table, unless it is a RENAME TO operation that moves the table to a database located in a different directory.

The exception mentioned earlier is that ALTER TABLE blocks reads (not just writes) at the moment it is ready to install the new version of the table's .frm file, drop the old file, and clear obsolete table structures from the table and table-definition caches. At this stage it must acquire an exclusive lock. To do this, it waits for current readers to finish and blocks new reads and writes.

An ALTER TABLE operation that uses the COPY algorithm prevents concurrent DML operations. Concurrent queries are still allowed. That is, a table-copying operation always involves at least the LOCK=SHARED concurrency restriction (allow queries but not DML). You can further restrict concurrency for operations that support the LOCK clause by specifying LOCK=EXCLUSIVE, which prevents both DML and queries. For more information, see Concurrency Control.

To force use of the COPY algorithm for an ALTER TABLE operation that would not otherwise use it, enable the old_alter_table system variable or specify ALGORITHM=COPY. If there is a conflict between the old_alter_table setting and an ALGORITHM clause with a value other than DEFAULT, the ALGORITHM clause takes precedence.

For InnoDB tables, an ALTER TABLE operation using the COPY algorithm on a table located in a shared tablespace may increase the amount of space used by the tablespace. Such operations require as much additional space as the data in the table plus indexes. For a table located in a shared tablespace, the extra space used during the operation is not returned to the operating system, as it is for a table located in a file-per-table tablespace.

For information about space requirements for online DDL operations, see Section 14.13.3, «Online DDL Space Requirements».

ALTER TABLE operations that use the INPLACE algorithm include:

ALTER TABLE operations supported by the InnoDB online DDL feature. See Section 14.13.1, «Online DDL Operations».
Renaming a table. MySQL renames the files corresponding to the table tbl_name without creating a copy. (You can also use the RENAME TABLE statement to rename tables. See Section 13.1.33, «RENAME TABLE Statement».) Privileges granted specifically for the renamed table are not transferred to the new name. They must be changed manually.
Operations that change only the table's metadata. These operations execute immediately because the server changes only the table's .frm file, without touching the table's contents. Metadata-only operations include:
- Renaming a column.
- Changing a column's default value (except for NDB tables).
- Changing an ENUM or SET column definition by adding new enumeration or set elements to the end of the list of permitted element values, provided that the storage size of the data type does not change. For example, adding an element to a SET column that has 8 elements changes the required storage per value from 1 byte to 2 bytes; this requires a copy of the table. Adding elements in the middle of the list causes renumbering of the existing elements, which also requires a copy of the table.
Renaming an index.
Adding or removing a secondary index, for InnoDB and NDB tables. See Section 14.13, «InnoDB and Online DDL».
For NDB tables — operations that add and remove indexes on variable-width columns. These operations execute online, without copying tables and without blocking concurrent DML actions for most of their duration. See Section 21.5.11, «Online Operations with ALTER TABLE in NDB Cluster».

ALTER TABLE Upgrading temporary columns from MySQL 5.5 format to 5.6 format via ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be performed using the INPLACE algorithm, because the table must be rebuilt, so specifying ALGORITHM=INPLACE in these cases results in an error. Specify ALGORITHM=COPY when needed.

If an ALTER TABLE operation on a multi-column KEY index used to partition the table changes the order of the columns, it can only be performed using ALGORITHM=COPY.

The WITHOUT VALIDATION and WITH VALIDATION clauses affect whether an ALTER TABLE operation is performed in place for modifications to virtual generated columns. See Section 13.1.8.2, «ALTER TABLE and Generated Columns».

NDB Cluster previously supported online ALTER TABLE operations using the ONLINE and OFFLINE keywords. These keywords are no longer supported; using them causes a syntax error. MySQL NDB Cluster 7.5 (and later) supports online operations using the same ALGORITHM=INPLACE syntax as the standard MySQL server. NDB does not support changing a tablespace online. See Section 21.5.11, «Online Operations with ALTER TABLE in NDB Cluster», for more information.

ALTER TABLE with DISCARD ... PARTITION ... TABLESPACE or IMPORT ... PARTITION ... TABLESPACE does not create any temporary tables or temporary partition files.

ALTER TABLE with ADD PARTITION, DROP PARTITION, COALESCE PARTITION, REBUILD PARTITION, or REORGANIZE PARTITION does not create temporary tables (except when used with NDB tables); however, these operations may create, and do create, temporary partition files.

ADD or DROP operations for RANGE or LIST partitions are immediate operations, or nearly so. ADD or COALESCE operations for HASH or KEY partitions copy data between all partitions unless LINEAR HASH and LINEAR KEY were used; this is effectively the same as creating a new table, although the ADD or COALESCE operation is performed partition by partition. REORGANIZE operations only copy the changed partitions and leave unchanged ones untouched.

For MyISAM tables you can speed up index re-creation (the slowest part of the change process) by setting the myisam_sort_buffer_size system variable to a high value.

Concurrency Control

For ALTER TABLE operations that support it, you can use the LOCK clause to control the level of concurrent reads and writes on the table while it is being altered. Specifying a value other than the default for this clause lets you require a certain amount of concurrent access or exclusive access during the alter operation, and stops the operation if the requested degree of locking is not available. The options for this LOCK clause are:

LOCK = DEFAULT

Maximum level of concurrency for the given ALGORITHM clause (if any) and ALTER TABLE operation: allow concurrent reads and writes if supported. If not, allow concurrent reads if supported. If not, use exclusive access.
LOCK = NONE

If supported, allow concurrent reads and writes. Otherwise, an error occurs.
LOCK = SHARED

If supported, allow concurrent reads, but block writes. Writes are blocked even if concurrent writes are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation. If concurrent reads are not supported, an error occurs.
LOCK = EXCLUSIVE

Enforce exclusive access. This is done even if concurrent read/write operations are supported by the storage engine for the given ALGORITHM clause (if any) and ALTER TABLE operation.

Adding and Dropping Columns

Use ADD to add new columns to a table and DROP to remove existing columns. DROP col_name is a MySQL extension to standard SQL.

To add a column at a specific position within the table row, use FIRST or AFTER col_name. By default the column is added last.

If a table contains only one column, that column cannot be dropped. If you want to drop the table, use a DROP TABLE statement instead.

If columns are dropped from a table, they are also removed from any index they are part of. If all columns making up an index are dropped, the index is dropped as well.

Renaming, Redefining, and Reordering Columns

The CHANGE, MODIFY, and ALTER clauses let you change the names and definitions of existing columns. They have the following comparative characteristics:

CHANGE:
- Can rename a column and change its definition, or both.
- Has more capabilities than MODIFY, but at the cost of convenience for some operations. CHANGE requires naming the column twice if you're not renaming it.
- With FIRST or AFTER you can reorder columns.
MODIFY:
- Can change a column's definition, but not its name.
- It is more convenient than CHANGE for changing a column's definition without renaming it.
- With FIRST or AFTER you can reorder columns.
ALTER: Used only to change a column's default value.

CHANGE is a MySQL extension to standard SQL. MODIFY is a MySQL extension for Oracle compatibility.

To change a column so as to change both its name and its definition, use CHANGE, specifying the old and new names and the new definition. For example, to rename an INT NOT NULL column from a to b and change its definition to use the BIGINT data type while retaining the NOT NULL attribute, do the following:

ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;

To change a column's definition but not its name, use CHANGE or MODIFY. With CHANGE syntax, two column names are required, so you have to specify the same name twice to leave the name unchanged. For example, to change the definition of column b, do the following:

ALTER TABLE t1 CHANGE b b INT NOT NULL;

MODIFY is more convenient for changing a definition without changing the name, because the column name is required only once:

ALTER TABLE t1 MODIFY b INT NOT NULL;

To change a column's name but not its definition, use CHANGE. The syntax requires a column definition, so to leave the definition unchanged you must re-specify the definition the column currently has. For example, to rename an INT NOT NULL column from b to a, do the following:

ALTER TABLE t1 CHANGE b a INT NOT NULL;

When changing a column's definition with CHANGE or MODIFY, the definition must include the data type and all attributes that should apply to the new column, other than index attributes such as PRIMARY KEY or UNIQUE. Attributes present in the original definition but not specified in the new definition are not carried over. Suppose column col1 is defined as INT UNSIGNED DEFAULT 1 COMMENT 'my column', and you alter the column as follows, intending to change only INT to BIGINT:

продолжение следует...