Primary Keys and Indexes - Problems with slow ALTER TABLE

Lecture

Это окончание невероятной информации про alter table.

...

style="box-sizing: inherit; margin: 0px; padding: 0px; border: 0px; outline: 0px; font-size: 14.256px; vertical-align: baseline; background-image: initial; background-position: initial; background-size: initial; background-repeat: initial; background-attachment: initial; background-origin: initial; background-clip: initial; color: rgb(85, 85, 85); font-family: ">

ALTER TABLE t1 MODIFY col1 BIGINT;

This statement changes the data type from INT to BIGINT, but also drops the UNSIGNED, DEFAULT, and COMMENT attributes. To keep them, the statement must include them explicitly:

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

When changing a data type with CHANGE or MODIFY, MySQL tries to convert existing column values to the new type as best it can.

Warning

This conversion may result in altered data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if converting to the new data type would result in data loss, enable strict SQL mode before using ALTER TABLE (see Section 5.1.10, «Server SQL Modes»).

If you use CHANGE or MODIFY to shorten a column for which an index exists on the column, and the resulting column length is shorter than the index length, MySQL automatically shortens the index.

For columns renamed with CHANGE, MySQL automatically renames these references to the renamed column:

Indexes referring to the old column, including indexes and disabled MyISAM indexes.
Foreign keys referring to the old column.

For columns renamed with CHANGE, MySQL does not automatically rename these references to the renamed column:

Generated column and partition expressions that refer to the renamed column. You must use CHANGE to redefine such expressions in the same ALTER TABLE statement as the one that renames the column.
Views and stored programs that reference the renamed column. You must manually alter the definitions of these objects so they reference the new column name.

To change the order of columns in a table, use the FIRST and AFTER clauses with CHANGE or MODIFY.

ALTER ... SET DEFAULT or ALTER ... DROP DEFAULT specify a new default value for a column, or remove the old default value, respectively. If the old default value is removed and the column can be NULL, the new default value is NULL. If the column cannot be NULL, MySQL assigns a default value as described in Section 11.6, «Data Type Default Values».

Primary Keys and Indexes

DROP PRIMARY KEY drops the primary key. If there is no primary key, an error occurs. For information about the performance characteristics of primary keys, especially for InnoDB tables, see Section 8.3.2, «Primary Key Optimization».

If you add a UNIQUE INDEX or PRIMARY KEY to a table, MySQL stores it before any non-unique index, to allow detection of duplicate keys as early as possible.

DROP INDEX removes an index. This is a MySQL extension to standard SQL. See Section 13.1.25, «DROP INDEX Statement». To determine index names, use SHOW INDEX FROM tbl_name.

Some storage engines allow you to specify an index type when creating an index. The syntax for the index_type specifier is USING type_name. For more information, see Section 13.1.14, «CREATE INDEX Statement». The preferred position is after the column list. Support for using this option before the column list is expected to be removed in a future MySQL release.

index_option values define additional options for the index. For more on the permitted index_option values, see Section 13.1.14, «CREATE INDEX Statement».

RENAME INDEX old_index_name TO new_index_name renames an index. This is a MySQL extension to standard SQL. The contents of the table remain unchanged. old_index_name must be the name of an existing index in the table that is not dropped by the same ALTER TABLE statement. new_index_name is the new index name, which cannot duplicate the name of an index in the resulting table after the changes are applied. Neither index name can be PRIMARY.

If you use ALTER TABLE on a MyISAM table, all non-unique indexes are created in a separate batch (as with REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating non-unique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create the missing indexes. MyISAM does this using a special algorithm that is much faster than inserting keys one at a time, so disabling keys before performing bulk insert operations should give a significant speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned previously.

While non-unique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that would otherwise use them.

After an ALTER TABLE statement, you may need to run ANALYZE TABLE to refresh index cardinality information. See Section 13.7.5.22, «SHOW INDEX Statement».

Foreign Keys and Other Constraints

The FOREIGN KEY and REFERENCES clauses are supported by the InnoDB and NDB storage engines, which implement ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (...) REFERENCES ... (...). See Section 1.7.3.2, «FOREIGN KEY Constraints». For other storage engines, the clauses are parsed but ignored.

The CHECK constraint clause is parsed but ignored by all storage engines. See Section 13.1.18, «CREATE TABLE Statement». The reason for accepting but ignoring the syntax clauses is compatibility, to simplify porting code from other SQL servers, and to run applications that create tables with references. See Section 1.7.2, «MySQL Differences from Standard SQL».

For ALTER TABLE, unlike CREATE TABLE, ADD FOREIGN KEY ignores index_name if given, and uses an automatically generated foreign key name instead. As a workaround, include a CONSTRAINT clause to specify the foreign key name:

ADD CONSTRAINT name FOREIGN KEY (....) ...

Important

MySQL silently ignores inline REFERENCES specifications, where references are defined as part of a column specification. MySQL only accepts REFERENCES clauses defined as part of a separate FOREIGN KEY specification.

Note

Partitioned InnoDB tables do not support foreign keys. This restriction does not apply to NDB tables, including those explicitly partitioned using [LINEAR] KEY. For more information, see Section 22.6.2, «Partitioning Limitations Relating to Storage Engines».

The MySQL server and NDB Cluster support using ALTER TABLE to drop foreign keys:

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;

Adding and dropping a foreign key in the same ALTER TABLE statement is supported with ALTER TABLE ... ALGORITHM=INPLACE but not with ALTER TABLE ... ALGORITHM=COPY.

The server forbids changes to foreign key columns that could lead to loss of referential integrity. The workaround is to use ALTER TABLE ... DROP FOREIGN KEY before changing the column definition and ALTER TABLE ... ADD FOREIGN KEY afterward. Examples of forbidden changes:

Data type changes to foreign key columns that could be unsafe. For example, changing VARCHAR(20) to VARCHAR(30) is allowed, but changing it to VARCHAR(1024) is forbidden because it changes the number of length bytes needed to store individual values.
Changing a NULL column to NOT NULL in non-strict mode is forbidden, to prevent converting NULL values to non-standard default values instead of NULL, for which there are no corresponding values in the referenced table. The operation is allowed in strict mode, but if such a conversion is required, an error is returned.

ALTER TABLE tbl_name RENAME new_tbl_name changes internally generated foreign key constraint names, as well as user-defined foreign key constraint names that begin with the string «tbl_name_ibfk_», to reflect the new table name. InnoDB interprets foreign key constraint names that begin with the string «tbl_name_ibfk_» as internally generated names.

Changing the Character Set

To change the default character set of a table and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

This statement also changes the collation of all character columns. If you don't specify a COLLATE clause to indicate which collation to use, the statement uses the default collation for the character set. If this collation is not suitable for the table's intended use (for example, if it changes from a case-sensitive collation to a case-insensitive one), specify the collation explicitly.

For a column with the VARCHAR data type, or one of the TEXT types, CONVERT TO CHARACTER SET changes the data type if needed, to ensure the new column is long enough to store the same number of characters as the original column. For example, a TEXT column has a two-byte length prefix that stores the length of values in the column, up to a maximum of 65,535 bytes. For a latin1 TEXT column, each character requires one byte, so the column can store up to 65,535 characters. If the column is converted to utf8, each character may require up to three bytes, for a maximum possible length of 3 × 65,535 = 196,605 bytes. This length doesn't fit in the TEXT column's length-prefix bytes, so MySQL converts the data type to MEDIUMTEXT, which is the smallest string type whose length-prefix bytes can record a value of 196,605. Similarly, a VARCHAR column can be converted to MEDIUMTEXT.

To avoid data type changes of the kind just described, don't use CONVERT TO CHARACTER SET. Instead, use MODIFY to change individual columns. For example:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR(M) CHARACTER SET utf8;

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to the corresponding binary string types (BINARY, VARBINARY, BLOB). This means the columns no longer have a character set, and a subsequent CONVERT TO operation does not apply to them.

If charset_name is DEFAULT in a CONVERT TO CHARACTER SET operation, the character set specified by the character_set_database system variable is used.

Warning

The CONVERT TO operation converts column values between the original and named character sets. This is not what you want if you have a column with one character set (for example, latin1), but the stored values actually use some other, incompatible character set (for example utf8). In this case, for each such column you need to do the following:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that conversion does not occur when converting to or from BLOB columns.

To change only the default character set for a table, use this statement:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

The word DEFAULT is not required. The default character set is the character set used if you don't specify a character set for columns you add to the table later (for example, with ALTER TABLE ... ADD column).

When the foreign_key_checks system variable is enabled, which is the default setting, character set conversion is not permitted for tables that include a character string column used in a foreign key constraint. The workaround is to disable foreign_key_checks before performing the character set conversion. You must perform the conversion for both tables participating in the foreign key constraint before re-enabling foreign_key_checks. If you re-enable foreign_key_checks after converting only one of the tables, an ON DELETE CASCADE or ON UPDATE CASCADE operation may corrupt data in the referencing table due to the implicit conversion that occurs during these operations (Bug #45290, Bug #74816).

Discarding and Importing InnoDB Tablespaces

An InnoDB table created in its own file-per-table tablespace can be imported from a backup or from another MySQL server instance using the DISCARD TABLESPACE and IMPORT TABLESPACE clauses. See Section 14.6.1.3, «Importing InnoDB Tables».

Row Order for MyISAM Tables

ORDER BY lets you create a new table with rows in a specific order. This option is useful primarily when you know you'll mostly be querying rows in a specific order. Using this option after major changes to a table can let you improve performance. In some cases, it may make sorting easier for MySQL if the table is ordered by the column you want to sort by later.

Note

The table doesn't remain in the specified order after inserts and deletes.

The ORDER BY syntax lets you specify one or more column names to sort by, each of which may be followed by ASC or DESC to indicate ascending or descending sort order, respectively. Ascending order is used by default. Only column names can be used as sort criteria; arbitrary expressions are not permitted. This clause should be placed last, after any other clauses.

ORDER BY makes no sense for InnoDB tables, because InnoDB always orders table rows according to the clustered index.

When used on a partitioned table, ALTER TABLE ... ORDER BY orders rows only within each partition.

Partitioning Options

partition_options denotes options that can be used with partitioned tables to repartition, add, drop, remove, import, merge, and split partitions, as well as to perform partitioning maintenance.

It is possible for an ALTER TABLE statement to contain a PARTITION BY or REMOVE PARTITIONING clause in addition to other alter specifications, but the PARTITION BY and REMOVE PARTITIONING clause must be specified last, after all other specifications. The ADD PARTITION, DROP PARTITION, DISCARD PARTITION, IMPORT PARTITION, COALESCE PARTITION, REORGANIZE PARTITION, EXCHANGE PARTITION, ANALYZE PARTITION, CHECK PARTITION, and REPAIR PARTITION options cannot be combined with other alter specifications in a single ALTER TABLE, since only the listed options act on individual partitions.

For more information about partitioning options, see Section 13.1.18, «CREATE TABLE Statement» and Section 13.1.8.1, «ALTER TABLE Partitioning Operations». For information and examples of ALTER TABLE ... EXCHANGE PARTITION statements, see Section 22.3.3, «Exchanging Partitions and Subpartitions with Tables».

Before MySQL 5.7.6, partitioned InnoDB tables used the generic ha_partition partitioning handler used by MyISAM and other storage engines that don't provide their own partitioning handlers; in MySQL 5.7.6 and later, such tables are created using InnoDB's own (or «native») storage engine partitioning handler. Starting with MySQL 5.7.9, you can upgrade an InnoDB table created in MySQL 5.7.6 or earlier (that is, created using ha_partition) to InnoDB's native partitioning handler using ALTER TABLE ... UPGRADE PARTITIONING. (Bug #76734, Bug #20727344) This ALTER TABLE syntax does not allow any other options and can only be used for one table at a time. You can also use mysql_upgrade in MySQL 5.7.9 or newer to upgrade older partitioned InnoDB tables to the native partitioning handler.