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 14.8, “Online Status for DDL Operations”.
ALTER TABLE partitioning clauses do
not go through the same internal online DDL API as regular
InnoDB tables, and most do not
If you use an
partitioning clause in an
TABLE statement, the partitioned table is repartitioned
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 the
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 partitioning clauses of the
TABLE statement do not go through the same internal
online DDL API as regular non-partitioned
InnoDB tables, MySQL still attempts to minimize
data copying and locking where possible:
DROP PARTITIONfor tables partitioned by
LISTdo not copy any existing data.
TRUNCATE PARTITIONdoes not copy existing data.
Concurrent queries are allowed 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 allowed. Data from the affected partitions is copied while holding a shared metadata (read) lock at the table level.
REMOVE PARTITIONINGsupport the
ALGORITHMclause with the
COPYoption. Both operations permit concurrent queries.
For additional information about
TABLE partitioning clauses, see
Partitioning Options, and
Section 220.127.116.11, “ALTER TABLE Partition Operations”. For
information about partitioning in general, see
Chapter 19, Partitioning.