Use of MySQL table partitions

Partitions split rows in the data table into smaller Datasets Based on the specified rules and then distributed in multiple directories and disks. MySQL supports Partition from 5.1.3.

Partitions split rows in the data table into smaller Datasets Based on the specified rules and then distributed in multiple directories and disks. MySQL supports Partition from 5.1.3.

Concepts of partition and table sharding

Partitions split rows in the data table into smaller Datasets Based on the specified rules and then distributed in multiple directories and disks. MySQL supports Partition from 5.1.3.

Table shards are manually created to create three tables. The files in each table are relatively independent.

Comparison of partitions and sub-tables:

Table sharding

Multiple Data Tables one data table

The risk of duplicate data is not the risk of duplicate data

Write multiple tables into one table

There are no unified constraints.

II. Introduction to MySQL partition types

RANGE partition ):

Each partition contains rows that can correspond to a group of specific values (ranges). Applicable scenarios include conditions for common queries to directly partition tables, or you need to delete the table to quickly delete the old data.

List partitions:

The list partitions are similar to the range partitions. The main difference is that the list partition mode allows data to be divided based on the DBA pre-defined Value List (rather than a range composed of a series of continuous values.

Column partition:

Column partitioning supports using multiple columns in the partition key. These columns are used to determine which rows are to be cleansed in the partition and the partition pruning operation. Column partitioning is an extension of range partitions and list partitions.

Hash Partition ):

Hash partitions are used to ensure that data is evenly allocated to multiple pre-defined partitions. In range partitions and list partitions, you must specify the partition in which the specific column values are stored. In the hash partition, MySQL will automatically do this for you, you only need to specify the column value or expression based on the hash value and the number of partitions in the partition table.

Linear hash partitioning is a variant of list partitioning. It uses a more complex data partitioning algorithm, you can increase the speed of adding, deleting, merging, and splitting partitions when processing TB-level tables.

Key partition:

Key partitions are similar to hash partitions. MySQL uses a hash key generated by a system to ensure the average distribution of data, rather than using a user-defined expression for hash partitions. Key partitions support the use of various types of column data in partitions.

Subpartition:

Subpartitions support further partitioning of each partition in a partition table. They are used to process super large tables and distribute data and indexes on many servers and disks.

Partition pruning:

By using the "WHERE" or "ON" clause in a timely manner in a query, you can ensure that the MySQL optimizer only accesses qualified partitions. In this way, the query execution speed will be an order of magnitude higher than that of the unpartitioned table.

Iii. Use of common partition types

RANGE partition usage:

Create table 'virus _ daily '(
'Id' INT (11) unsigned not null AUTO_INCREMENT,
'Day' date not null default '2017-00-00 ',
'Event _ count' INT (11) null default 0 COMMENT 'event number ',
'IP _ count' INT (11) null default 0 COMMENT 'IP address number ',
Primary key ('id', 'day') -- Note that day must be defined as an index field.
) ENGINE = MyISAM default character set = utf8 COLLATE = utf8_general_ci partition by range (to_days ('day '))(
PARTITION p1 values less than (to_days ('2017-12-03 ')),
PARTITION p2 values less than (to_days ('2017-12-04 ')),
PARTITION p3 values less than (to_days ('2017-12-05 ')),
PARTITION p4 values less than (to_days ('2017-12-06 ')),
PARTITION p5 values less than (to_days ('2017-12-07 ')),
PARTITION p6 values less than (to_days ('2017-12-08 ')),
PARTITION p7 values less than (to_days ('2017-12-09 ')),
PARTITION p8 values less than (to_days ('2017-12-10 '))
);

Based on this partition scheme, the daily data is stored in a partition. For example, when the insertion time is, the data will be saved to the partition p1.