With the exception of
partitioning clauses, online DDL operations for partitioned
InnoDB tables follow the same rules that apply
InnoDB tables. Online DDL rules are
outlined in Table 15.10, “Online Status for DDL Operations”.
The following table shows
LOCK clause support for each
TABLE partitioning statement.
Table 15.11 ALGORITHM, LOCK Clause Support for ALTER TABLE Partitioning Clauses
|Partitioning Clause||Supported ALGORITHM, LOCK Options|
ALTER TABLE partitioning options
ALGORITHM=COPY or only permit
ALGORITHM=DEFAULT, LOCK=DEFAULT repartition the
table using the
COPY algorithm. In other words,
a new partitioned table is created with the new partitioning
scheme. The newly created table includes any changes applied by
ALTER TABLE statement and the
table data is copied into the new table structure.
If you do not modify table partitioning using
ALTER TABLE partitioning clauses
that use the
COPY algorithm or perform any
other partition management in your
TABLE performs supported
operations on each table partition. Be aware, however, that when
TABLE operations are performed on each partition, there
is increased demand on system resources due to operations being
performed on multiple partitions.
Even though most
partitioning clauses do not go through the same internal online
DDL API as regular non-partitioned
tables, MySQL still attempts to minimize data copying and locking
DROP PARTITIONfor tables partitioned by
LISTdo not copy any existing data.
TRUNCATE PARTITIONdoes not copy any existing data, for all types of partitioned tables.
Concurrent queries are permitted during
COALESCE PARTITIONfor tables partitioned by
LIST. MySQL copies the data while holding a shared lock.
REBUILD PARTITION, or
COALESCE PARTITIONfor a table partitioned by
LIST, concurrent queries are permitted. Data from the affected partitions is copied while holding a shared metadata (read) lock at the table level.