Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of "what are the four partitioning ways of mysql table". The editor shows you the operation process through an actual case. The operation method is simple and fast, and it is practical. I hope this article "what are the four partitioning ways of mysql table" can help you solve the problem.

1. What is table partitioning?

The data in the mysql database is stored on disk as files, and is placed under / mysql/data by default (you can view it through datadir in my.cnf). A table mainly corresponds to three files, one for frm storage table structure, one for myd storage table data, and one for myi storage table index. If the amount of data in a table is too large, then myd,myi will become very large, and finding data will become very slow. At this time, we can use the partition function of mysql to physically divide the three files corresponding to this table into many small chunks. In this way, when we look for a piece of data, we do not have to find all of it, as long as we know which piece of data this data is in, and then find it in that piece. If the data in the table is too large, one disk may not fit. At this time, we can allocate the data to different disks.

Table partitioning refers to the decomposition of a table in a database into smaller, manageable parts according to certain rules. Logically, there is only one table, but the underlying layer consists of multiple physical partitions.

2. The difference between table partition and sub-table.

Sub-table: refers to the decomposition of a table into several different tables by certain rules. For example, record the user's order into multiple tables according to time. The difference between a partition and a partition is that a partition logically has only one table, while a sub-table breaks down a table into multiple tables.

3. What are the benefits of table partitioning?

(1) more data can be stored than a single disk or file system partition.

(2) for those data that have lost the meaning of preservation, it is usually easy to delete those data by deleting the partitions associated with those data. On the contrary, in some cases, the process of adding new data can be easily achieved by adding a new partition to those new data.

(3) some queries can be greatly optimized, mainly because the data satisfying a given WHERE statement can only be saved in one or more partitions, so that there is no need to find other remaining partitions when searching. Because partitions can be modified after the partition table is created, you can reorganize the data to improve the efficiency of commonly used queries when you first configure the partitioning scheme.

Queries involving aggregate functions such as SUM () and COUNT () can be easily processed in parallel. A simple example of such a query is "SELECT salesperson_id, COUNT (orders) as order_total FROM sales GROUP BY salesperson_id;". Through "parallel", this means that the query can be performed on each partition at the same time, and the final result only needs to be obtained by the total results of all partitions.

(5) to achieve greater query throughput by distributing data queries across multiple disks.

4. Limiting factors of partitioned tables

(1) A table can only have at most 1024 partitions.

(2) in MySQL5.1, the partition expression must be an integer, or an expression that returns an integer. Support for non-integer expression partitioning is provided in MySQL5.5.

(3) if there is a primary key or unique index column in the partition field, then many primary key columns and unique index columns must be included. That is, the partition field contains either no primary key or index column, or all primary key and index columns.

(4) Foreign key constraints cannot be used in partition tables.

(5) the partition of MySQL applies to all the data and indexes of a table, not only to the data of the table but not to the index, nor to the index but not to the table, nor to part of the data of the table.

5. How to determine whether the current MySQL supports partitioning? Mysql > show variables like'% partition%';+-+-+ | Variable_name | Value | +-+-+ | have_partitioning | YES | +-+-+ 1 row in set (0.00 sec)

The value of have_partintioning is YES, which means that partitions are supported.

6. What types of partitions are supported by MySQL?

(1) RANGE partition: allocates multiple rows to a partition based on column values belonging to a given contiguous interval.

(2), LIST partition: similar to partition by RANGE, except that LIST partition is selected based on column values matching a value in a set of discrete values.

(3) HASH partition: a partition selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value.

(4), KEY partition: similar to partition by HASH, except that KEY partition only supports calculating one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.

Note: in the MySQL5.1 version, RANGE,LIST,HASH partitioning requires that the partitioning key must be of type INT or return the type INT through an expression. However, when you partition an KEY, you can use other types of columns (except the BLOB,TEXT type) as the partitioning key.

6.1. RANGE Partition

According to the range partition, the ranges should be contiguous but not overlapping, using the PARTITION BY RANGE, VALUES LESS THAN keywords. When the COLUMNS keyword is not used, the RANGE must be an integer field name or a function that returns an integer in parentheses.

6.1.1, according to the numerical range drop table if exists employees Create table employees (id int not null, fname varchar (30), lname varchar (30), hired date not null default '1970-01-01, separated date not null default' 9999-12-31, job_code int not null default 0, store_id int not null default 0) engine=myisam default charset=utf8partition by range (store_id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16) Partition p3 values less than (21)) Insert into employees (id,fname,lname,hired,store_id) values (1 insert into employees, Zhang, 2015-05-04); insert into employees (id,fname,lname,hired,store_id) values (2, Li Si, Li, 2016-10-01); insert into employees (id,fname,lname,hired,store_id) values (3, Wang Wu, Wang, 2016-11-14) Insert into employees (id,fname,lname,hired,store_id) values (4) Zhao Liu, Zhao, '2017-08-24); insert into employees (id,fname,lname,hired,store_id) values (5)' Tian Qi', 'Tian', '2018-05-20)

According to this partitioning scheme, all rows corresponding to employees working in stores 1 to 5 are saved in partition P0, employees in stores 6 to 10 are saved in P1, and so on. Note that each partition is defined sequentially, from the lowest to the highest. This is a requirement of PARTITION BY RANGE syntax.

For a new row that contains data (6 Kang Ba, Kang, 2018-06-24 Kang 13), it can be easily determined that it will be inserted into the p2 partition.

Insert into employees (id,fname,lname,hired,store_id) values (6) Kangba, Kang, 2018-06-24)

But what happens if you add a store numbered 21 (7 recordings, 9 days, 7 weeks, 2018-07-24 minutes 21)? In this scenario, since there are no rules to include stores with store_id greater than 20, the server will not know where to save the row, which will result in an error.

Insert into employees (id,fname,lname,hired,store_id) values (7 values' values', 'Zhou', '2018-07-24); ERROR 1526 (HY000): Table has no partition for value 21

To avoid this error, you can use a "catchall" VALUES LESS THAN clause in the CREATE TABLE statement, which provides all values that are greater than the highest value explicitly specified:

Create table employees (id int not null, fname varchar (30), lname varchar (30), hired date not null default '1970-01-01, separated date not null default' 9999-12-31, job_code int not null default 0, store_id int not null default 0) engine=myisam default charset=utf8partition by range (store_id) (partition p0 values less than (6), partition p1 values less than (11), partition p2 values less than (16) Partition p3 values less than (21), partition p4 values less than MAXVALUE) 6.1.2, according to TIMESTAMP range drop table if exists quarterly_report_status Create table quarterly_report_status (report_id int not null, report_status varchar (20) not null, report_updated timestamp not null default current_timestamp on update current_timestamp) partition by range (unix_timestamp (report_updated)) (partition p0 values less than (unix_timestamp ('2008-01-01 00 not null 0000')), partition p1 values less than (unix_timestamp ('2008-04-01 00 not null 0000')) Partition p2 values less than (unix_timestamp ('2008-07-01 00 unix_timestamp), partition p3 values less than (unix_timestamp (' 2008-10-01 00 unix_timestamp)), partition p4 values less than (unix_timestamp ('2009-01-01 00 unix_timestamp')), partition p5 values less than (unix_timestamp ('2009-04-01 0000 purl 00')), partition p6 values less than (unix_timestamp (' 2009-07-01 0000 unix_timestamp)) Partition p7 values less than (unix_timestamp ('2009-10-01 00 unix_timestamp), partition p8 values less than (unix_timestamp (' 2010-01-01 00 unix_timestamp)), partition p9 values less than maxvalue) 6.1.3, according to DATE, DATETIME range

Add the COLUMNS keyword to define non-integer range and multi-column range. However, it is important to note that only column names can be found in COLUMNS parentheses, and functions are not supported. For multi-column ranges, the multi-column range must show an increasing trend:

Drop table if exists member Create table member (firstname varchar (25) not null, lastname varchar (25) not null, username varchar (16) not null, email varchar (35), joined date not null) partition by range columns (joined) (partition p0 values less than ('1960-01-01'), partition p1 values less than ('1970-01-01'), partition p2 values less than ('1980-01-01'), partition p3 values less than ('1990-01-01') Partition p4 values less than maxvalue) 6.1.4, based on multi-column range drop table if exists rc3 Create table rc3 (an int, b int) partition by range columns (agin b) (partition p0 values less than (0L10), partition p1 values less than (10LJ 20), partition p2 values less than (20LJ 30), partition p3 values less than (30LJ 40), partition p4 values less than (40LING 50), partition p5 values less than (maxvalue,maxvalue) 6.1.5, RANGE partition is particularly useful drop table if exists staff in the following situations Create table staff (id int not null, fname varchar (30), lname varchar (30), hired date not null default '1970-01-01, separated date not null default' 9999-12-31, job_code int not null default 0, store_id int not null default 0) engine=myisam default charset=utf8partition by range (year (separated)) (partition p0 values less than (1991), partition p1 values less than (1996), partition p2 values less than (2001), partition p4 values less than MAXVALUE)

(1) when you need to delete "old" data on a partition, just delete the partition. If you use the partitioning scheme given in the most recent example above, you simply use "alter table staff drop partition p0;" to delete all rows corresponding to all employees who stopped working before 1991. For tables with a large number of rows, this is better than running a table such as "delete from staff WHERE year (separated)"

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.