Movatterモバイル変換

For storage engines such asMyISAM that actually execute table-level locks when executing DML or DDL statements, such a statement in older versions of MySQL (5.6.5 and earlier) that affected a partitioned table imposed a lock on the table as a whole; that is, all partitions were locked until the statement was finished. In MySQL 5.7,partition lock pruning eliminates unneeded locks in many cases, and most statements reading from or updating a partitionedMyISAM table cause only the effected partitions to be locked. For example, aSELECT from a partitionedMyISAM table locks only those partitions actually containing rows that satisfy theSELECT statement'sWHERE condition are locked.

For statements affecting partitioned tables using storage engines such asInnoDB, that employ row-level locking and do not actually perform (or need to perform) the locks prior to partition pruning, this is not an issue.

The next few paragraphs discuss the effects of partition lock pruning for various MySQL statements on tables using storage engines that employ table-level locks.

Effects on DML statements

SELECT statements (including those containing unions or joins) lock only those partitions that actually need to be read. This also applies toSELECT ... PARTITION.

AnUPDATE prunes locks only for tables on which no partitioning columns are updated.

REPLACE andINSERT lock only those partitions having rows to be inserted or replaced. However, if anAUTO_INCREMENT value is generated for any partitioning column then all partitions are locked.

INSERT ... ON DUPLICATE KEY UPDATE is pruned as long as no partitioning column is updated.

INSERT ... SELECT locks only those partitions in the source table that need to be read, although all partitions in the target table are locked.

Locks imposed byLOAD DATA statements on partitioned tables cannot be pruned.

The presence ofBEFORE INSERT orBEFORE UPDATE triggers using any partitioning column of a partitioned table means that locks onINSERT andUPDATE statements updating this table cannot be pruned, since the trigger can alter its values: ABEFORE INSERT trigger on any of the table's partitioning columns means that locks set byINSERT orREPLACE cannot be pruned, since theBEFORE INSERT trigger may change a row's partitioning columns before the row is inserted, forcing the row into a different partition than it would be otherwise. ABEFORE UPDATE trigger on a partitioning column means that locks imposed byUPDATE orINSERT ... ON DUPLICATE KEY UPDATE cannot be pruned.

Affected DDL statements

CREATE VIEW does not cause any locks.

ALTER TABLE ... EXCHANGE PARTITION prunes locks; only the exchanged table and the exchanged partition are locked.

ALTER TABLE ... TRUNCATE PARTITION prunes locks; only the partitions to be emptied are locked.

In addition,ALTER TABLE statements take metadata locks on the table level.

Other statements

LOCK TABLES cannot prune partition locks.

CALL stored_procedure(expr) supports lock pruning, but evaluatingexpr does not.

DO andSET statements do not support partitioning lock pruning.