分区本质 从磁盘物理层面优化查询 分区操作的锁表

小结：

分区本质  从磁盘物理层面优化查询

MySQL :: MySQL 8.0 Reference Manual :: 23.1 Overview of Partitioning in MySQL https://dev.mysql.com/doc/refman/8.0/en/partitioning-overview.html

23.1 Overview of Partitioning in MySQL

This section provides a conceptual overview of partitioning in MySQL 8.0.

For information on partitioning restrictions and feature limitations, see Section 23.6, “Restrictions and Limitations on Partitioning”.

The SQL standard does not provide much in the way of guidance regarding the physical aspects of data storage. The SQL language itself is intended to work independently of any data structures or media underlying the schemas, tables, rows, or columns with which it works. Nonetheless, most advanced database management systems have evolved some means of determining the physical location to be used for storing specific pieces of data in terms of the file system, hardware or even both. In MySQL, the InnoDB storage engine has long supported the notion of a tablespace (see Section 15.6.3, “Tablespaces”), and the MySQL Server, even prior to the introduction of partitioning, could be configured to employ different physical directories for storing different databases (see Section 8.12.2, “Using Symbolic Links”, for an explanation of how this is done).

Partitioning takes this notion a step further, by enabling you to distribute portions of individual tables across a file system according to rules which you can set largely as needed. In effect, different portions of a table are stored as separate tables in different locations. The user-selected rule by which the division of data is accomplished is known as a partitioning function, which in MySQL can be the modulus, simple matching against a set of ranges or value lists, an internal hashing function, or a linear hashing function. The function is selected according to the partitioning type specified by the user, and takes as its parameter the value of a user-supplied expression. This expression can be a column value, a function acting on one or more column values, or a set of one or more column values, depending on the type of partitioning that is used.

分区实践 A PRIMARY KEY must include all columns in the table's partitioning function - xl0808tx - 博客园 https://www.cnblogs.com/yuanjiangw/p/10107511.html

MySQL :: MySQL 8.0 Reference Manual :: 23.6 Restrictions and Limitations on Partitioning https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html

Performance considerations.  Some effects of partitioning operations on performance are given in the following list:

• File system operations.  Partitioning and repartitioning operations (such as ALTER TABLE with PARTITION BY ..., REORGANIZE PARTITION, or REMOVE PARTITIONING) depend on file system operations for their implementation. This means that the speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space, file handling efficiency of the operating system, and MySQL server options and variables that relate to file handling. In particular, you should make sure that large_files_support is enabled and that open_files_limit is set properly. Partitioning and repartitioning operations involving InnoDB tables may be made more efficient by enabling innodb_file_per_table.

  See also Maximum number of partitions.

• Table locks.  Generally, the process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed. For InnoDB-specific exceptions to this limitation, seePartitioning Operations.

• Indexes; partition pruning.  As with nonpartitioned tables, proper use of indexes can speed up queries on partitioned tables significantly. In addition, designing partitioned tables and queries on these tables to take advantage of partition pruning can improve performance dramatically. See Section 23.4, “Partition Pruning”, for more information.

  Index condition pushdown is supported for partitioned tables. See Section 8.2.1.5, “Index Condition Pushdown Optimization”.

• Performance with LOAD DATA.  In MySQL 8.0, LOAD DATA uses buffering to improve performance. You should be aware that the buffer uses 130 KB memory per partition to achieve this.