Shulou(Shulou.com)06/01 Report--

This article mainly introduces the knowledge of Mysql zoning, the contents of the articles are carefully selected and edited by the author, with a certain pertinence, for your reference significance is still relatively great, the following with the author to understand the Mysql zoning knowledge.

I. Partition type

1. RANGE type (range partition)

Partition by scope, giving a certain range for each partition. The range must be continuous and cannot be repeated. Use the VALUES LESS THAN operator.

What do you mean? That is, the range type is a range, for example, partitioning from 1-10, 11-20, 21-30, 1-10 is in one partition, 11-20 is another partition, but it looks like they still have the same table.

Let's look at an example of creation.

CREATE TABLE `t1` (`id` int (11) NOT NULL, `uid` int 'user id', `score` int (3) NOT NULL DEFAULT' 0' COMMENT 'score', PRIMARY KEY (`id`, `score`) ENGINE=InnoDB DEFAULT CHARSET=utf8PARTITION BY RANGE (score) (PARTITION p0 VALUES LESS THAN (10) ENGINE=InnoDB, PARTITION p1 VALUES LESS THAN (20) ENGINE=InnoDB, PARTITION p2 VALUES LESS THAN (30) ENGINE=InnoDB, PARTITION p3 VALUES LESS THAN (40) ENGINE=InnoDB)

A T1 table is created, and T1 has four partitions. The range of the first partition, p0, is less than 10, and the second is less than 20. This is an example of a range partition.

Then why is a primary key defined as a double primary key? Because the partition key (score) must also be part of the primary or unique key.

For more information on Range partition, please see Mysql Partition introduction (2)-- RANGE Partition

2. LIST partition

LIST is different from RANGE partitions in that each partition must be explicitly defined based on the elements of the members of the column values in a set of lists

This is a bit of a mess, let's just look at an example of creation.

Create table T2 (id int not null, uid int not null comment 'user id', score int (3) not null default 0 comment' score', primary key (id, uid)) partition by list (uid) (partition p0 values in, partition p1 values in

The partition key of T2 is uid, and there are two partitions (this is obvious). If uid in (1Magne3, 5, 5, 7, 9), then this data will be saved in p0, if it is 2, 4, 6, 6, 8, 10, it will be in p1, this is LIST partition.

For more information on Range partition, please see Mysql Partition introduction (3)-- LIST Partition

3. COLUMNS partition

COLUMNS is very different from the above two, and you can use multiple partitioning keys to determine partitions. There are two ways, RANGE COLUMNS and LIST COLUMNS

1. RANGE COLUMNS partition

Similar to RANGE partitions, but can be defined using one or more field values

It's not easy to understand. Let's look at the example.

Create table T3 (an int, b int, c char (3), d int) PARTITION BY RANGE COLUMNS (an int dpencil c) (PARTITION p0 VALUES LESS THAN (5 p0 VALUES LESS THAN), PARTITION p1 VALUES LESS THAN (10 VALUES LESS THAN), PARTITION p2 VALUES LESS THAN (15p30 VALUES LESS THAN)

There are multiple partitioning keys, and they are all scoped, that is, RANGE COLUMNS partitions

For more information on RANGE COLUMNS partition, please see Mysql Partition introduction (4)-- RANGE COLUMNS Partition

2. LIST COLUMNS partition

Mysql 5.6starts to support LIST COLUMNS partitioning, you can start using multiple columns as partitioning keys, and column data types can be used as partitioned columns in addition to numeric types; you can also use string types, DATE and DATETIME

Let's look at the example.

CREATE TABLE customers_1 (first_name VARCHAR (25), last_name VARCHAR (25), street_1 VARCHAR (30), street_2 VARCHAR (30), city VARCHAR (15), renewal DATE) PARTITION BY LIST COLUMNS (city) (PARTITION pRegion_1 VALUES IN ('Oskarshamn',' H ö gsby','M ö nster å s'), PARTITION pRegion_2 VALUES IN ('Vimmerby',' Hultsfred','V ä stervik'), PARTITION pRegion_3 VALUES IN ('N ä ssj ö') 'Eksj ö', 'Vetlanda'), PARTITION pRegion_4 VALUES IN (' Uppvidinge', 'Alvesta',' V ä xjo'))

For more information on LIST COLUMNS partition, please see Mysql Partition introduction (5)-- LIST COLUMNS Partition

3. HASH partition

Use partition keys to ensure that data is evenly distributed over a predetermined partition. In hash partitions, there is no need to explicitly specify partitions

CREATE TABLE employees (id INT NOT NULL, fname VARCHAR (30), lname VARCHAR (30), hired DATE NOT NULL DEFAULT '1970-01-01-01, separated DATE NOT NULL DEFAULT' 9999-12-31, job_code INT, store_id INT) PARTITION BY HASH (store_id) PARTITIONS 4

For more information on HASH partition, please see Mysql Partition introduction (6)-- HASH Partition

4. KEY partition

Key partition is similar to hash partition and accepts 0 or more column names. The hash function of key partition is provided by MySQL CVM. The NDB cluster uses md5 (); using tables from other storage engines, the cloud server uses its own internal hash function based on the same algorithm password ().

CREATE TABLE K1 (id INT NOT NULL PRIMARY KEY, name VARCHAR (20)) PARTITION BY KEY () PARTITIONS 2

For more information about KEY partition, please see the introduction to Mysql partition (7)-- KEY partition.

5. Subpartition

Subpartition is also called compound partition, which is the way of further partition on the basis of partition.

CREATE TABLE ts (id INT, purchased DATE) PARTITION BY RANGE (YEAR (purchased)) SUBPARTITION BY HASH (TO_DAYS (purchased)) SUBPARTITIONS 2 (PARTITION p0 VALUES LESS THAN (1990), PARTITION p1 VALUES LESS THAN (2000), PARTITION p2 VALUES LESS THAN MAXVALUE)

After reading the above knowledge about Mysql zoning, many readers must have some understanding. If you need more industry knowledge and information, you can continue to follow our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.