mysql multi-field partitioning

Partitioning is based on certain rules. The database decomposes a table into multiple smaller, more manageable parts. As far as accessing database applications is concerned, logically there is only one table or one index, but in fact this table may be composed of N physical partition objects. Each partition is an independent object and can be processed independently and can be used as part of the table. for processing. Partitioning is completely transparent to the application and does not affect the application's business logic.

Recommended courses: MySQL Tutorial.

Partitioning is conducive to managing very large tables. It adopts the logic of divide and conquer. Partitioning introduces the concept of partition key. The partition key is used to calculate the partition value according to a certain interval value (or range). value), a specific value list or a hash function value to perform data aggregation, allowing the data to be distributed in different partitions according to rules, making one large object some small objects.

MySQL partitioning can partition data or indexes.

Note: No matter what kind of partitioning, either there is no primary key/unique key on your partitioned table, or the primary key/unique key of the partitioned table must contain the partition key, which means that you cannot use fields other than the primary key/unique key. other field partitions.

The limitations of MySQL partitions mainly include the following four aspects:

Compared with a single disk or file system partition, more data can be stored

Optimize queries. When the partition condition is included in the where clause, you can only scan the necessary one or more partitions to improve query efficiency; at the same time, when queries involving aggregate functions such as sum() and count(), you can easily scan each partition Parallel processing, and finally only need to summarize the results obtained from all partitions

For data that has expired or does not need to be saved, the data can be quickly deleted by deleting the partitions related to these data

Across multiple Disks are used to disperse data queries to obtain greater query throughput

Partitioning is similar to horizontal table partitioning. It divides the data of a large table into multiple small tables, because the query does not require a full table scan. , only certain partitions need to be scanned, so partitioning can improve query speed.

Horizontal sharding requires users to manually and explicitly create multiple sharding tables (such as tbl_user0, tbl_user1, tbl_user2) in advance, and physically create multiple tables through the client agent (Sharding-JDBC etc.) or middleware agent (Mycat, etc.) to implement table splitting logic.

Partition is a plug-in function of MySQL, which divides the data of a large table into multiple partition files at the bottom of the database (such as tbl_user#P#p0.ibd, tbl_user#P#p1.ibd, tbl_user#P #p2.ibd), unlike horizontal partitioning, partitioning does not require explicit creation of "partitioning tables". The database will automatically create partition files. What the user sees is just an ordinary table, which actually corresponds to multiple A partition is shielded and transparent to users. Its use is exactly the same as using a table. It does not require any functions to implement it. Partition is a logical horizontal table, but it is still a table at the physical level.

Before mysql5.5, range partitioning and list partitioning only supported integer partitioning, and an integer could be obtained through additional function operations or additional conversions. The columns partition is divided into range columns and list columns, and supports three major data types: integer (tinyint to bigint, decimal and float are not supported), date (date, datetime), and string (char, varchar, binary, varbinary).

Columns partitioning supports one or more fields as partitioning keys, but does not support expressions as partitioning keys. This is different from range and list partitioning. It should be noted that the comparison of range columns partition keys is based on tuple comparison, that is, based on field group comparison, which is different from range partitioning.

 create talbe rc3 (    
    a int,
    b int
)
partition by range columns(a, b) (
    partition p01 values less than (0, 10),
    partition p02 values less than (10, 10),
    partition p03 values less than (10, 20),
    partition p04 values less than (10, 35),
    partition p05 values less than (10, maxvalue),
    partition p06 values less than (maxvalue, maxvalue),
);

insert into rc3(a, b) values(1, 10);

select (1, 10) < (10, 10) from dual;

-- 根据结果存放到p02分区上了
select
    partition_name,
    partition_expression,
    partition_description,
    table_rows
from information_schema.partitions
where table_schema = schema() and table_name = 'rc3';

The comparison of range columns partition keys (comparison of tuples) is actually multi-column sorting, first sorting according to the a field and then sorting according to the b field, partitioning the data according to the sorting result, and range single field partitioning The rules for sorting are actually the same.

The above is the detailed content of mysql multi-field partitioning. For more information, please follow other related articles on the PHP Chinese website!