Home PC Games Linux Windows Database Network Programming Server Mobile  
  Home \ Database \ MySQL partition table Comments     - RedHat Performance Tuning (Server)

- To obtain installation package (RPM) under RHEL6 (Linux)

- Diagnose and resolve the SSH connection slow (Linux)

- Linux / Centos anti CC attack script (Linux)

- Struts2 Result Types (Programming)

- Linux IPTables anti-DDOS attack Shell Scripting (Linux)

- linux remote control software (Linux)

- Oracle 11g maintenance partitions - Adding Partitions (Database)

- There are more than one server, there is reason to try cloud operating system (Server)

- Linux Network Programming --TCP and UDP datagram type Explanation (Programming)

- IBM Data Studio to create objects using ---- double quotes / sensitive issues and the table / column renaming (Database)

- Ubuntu How to install and upgrade Linux Kernel 3.15 (Linux)

- Linux cron job (Linux)

- MySQL management partition table (Database)

- Joseph Central Java implementation (Programming)

- TCP network communication Java Programming (Programming)

- What Java 8 fastest garbage collector (Programming)

- Setting CentOS firewall open port (Linux)

- Java MD5 encryption implementation (Programming)

- Linux find and xargs (Linux)

  MySQL partition table Comments
  Add Date : 2016-07-04      
  Today, statistical data, it was found a table using the partition table, take the opportunity to record it.

1. What is table partitioning?

Partition table, is defined according to certain rules, a table in the database into multiple smaller, more manageable components. From a logical point of view, only a table, but the bottom is composed of a plurality of physical partitions.

2. The difference between the partition table and the points table

Score sheet: refers by certain rules, a table will be broken down into multiple different tables. For example, a user record based on the time line into multiple tables. The difference between points and partition table are: partition Logically speaking only a table, a table and sub-tables sucked down into multiple tables.

Partition Table 3. What are the benefits?

1) partition table data may be distributed over different physical devices to efficiently use multiple hardware devices. 2) and compared to a single disk or file system that can store more data 3) optimize queries. When the partition containing the condition where the statement can only scan one or more of the partition table to improve query efficiency; sum and count when it comes to statements, can also be processed in parallel on multiple partitions, the final summary results. 4) the partition table is easier to maintain. For example: to mass delete large amounts of data can clear the entire partition. 5) You can use the partition table to avoid certain specific bottlenecks, such as exclusive access to a single index of the InnoDB, ext3 ask price inode lock contention on your system and the like.

4. The constraints of the partition table

1) A table can have a maximum of 1024 partitions 2) MySQL5.1, the partition expression must be an integer, or returns an integer expression. Providing support for non-integer expressions partitions in MySQL5.5 in. 3) If the partition field primary key or unique index column, so many primary key columns and unique index columns must be included. Namely: either partition field does not contain a primary key or index columns or primary keys and indexes include all columns. 4) the partition table can not use foreign key constraints 5) MySQL partition apply to all data and indexes of a table, not only for table data partition without the index partition, not just the index partition without partitioning a table, not only part of the data table partitions.

5. How to determine whether the current MySQL support partitioning?

Command: show variables like '% partition%' operating results:

mysql> show variables like '% partition%';
+ ------------------- + ------- +
| Variable_name | Value |
+ ------------------- + ------- +
| Have_partitioning | YES |
+ ------------------- + ------- +
1 row in set (0.00 sec)
have_partintioning value YES, expressed support for the partition.

6. MySQL supported partition types are there?

1) RANGE partitioning: range interval according to the data partition 2) LIST partitions: List according to the values ​​of the partition, the difference between RANGE is the range of values ​​range partition range is continuous. 3) HASH partition 4) KEY partitions described in MySQL5.1 version, RANGE, LIST, HASH partitioning requires partitioning key must be INT type, or expression returns INT type. But KEY partition, you can use other types of columns (BLOB, TEXT except type) as the partitioning key.

7. Range Partition

Use the range partition range to be continuous and should not overlap each other. grammar:

partition by range (exp) (// exp may be a column name or expression, such as to_date (created_date)
    partition p0 values ​​less than (num)

mysql> create table emp (
    -> Id INTNOT null,
    -> Store_id int not null
    -> Partition by range (store_id) (
    -> Partition p0 values ​​less than (10),
    -> Partition p1 values ​​less than (20)
The above statement creates the emp table and partition according to store_id field, less than 10 in the presence of the partition p0, greater than or equal to 10, less than 20 in the presence of partition p1. Note that each partition is defined in order from lowest to highest. The above statement, if less than (10) and less than (20) in the order reversed, then the error, as follows:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition
RANGE Partitioning Problems

range coverage problem: when the range of values ​​into the record corresponding partitioning key is not defined in the partition when the insert statement will fail. The above example, if I insert a record store_id = 30 happen? Our top partition, the maximum is 20, if you insert a record of more than 20, will be given:
mysql> insert intoemp (id, store_id) values ​​(2,30);
ERROR 1526 (HY000): Table has no partition for value 30
Tip 30 this value does not correspond to the partition. Solution A. estimates partition key and timely new partition. B. Set partition, use values ​​less than maxvalue clause, MAXVALUE represents the maximum possible integer value. C. Try to select fields to be fully covered as the partitioning key, such as a year of twelve months and so on.
Range partition, if the partition key value is NULL, will be used as a minimum to deal with.
8. LIST partitions

List partition is to establish a list of discrete values ​​tell the specific value belongs to which database partition. grammar:

    partition by list (exp) (// exp a column name or expression
        partition p0 values ​​in (3,5) // value of 3 and 5 in partition p0
The difference is that with the Range, list partitions do not have any particular order of life. E.g:

mysql> create table emp1 (
    -> Id int not null,
    -> Store_id int not null
    -> Partition by list (store_id) (
    -> Partition p0 values ​​in (3,5),
    -> Partition p1 values ​​in (2,6,7,9)
Note If the value inserted record corresponding partition key values ​​of the partition is not specified in the list, it will insert failed. And, list did not provide maxvalue as range partitioning.

9. Columns partition

Partition type MySQL5.5 introduced to address the range and list partitioning partition before version 5.5 only supports integer partition problem. Columns range columns partition can be subdivided into partitions and partition list columns, they support integer, date, time, three string data type. (Does not support text and blob type as the partitioning key) columns partition also supports multi-column partition (not detailed here to expand).

10. Hash partition

Hash partition is mainly used to disperse the hot read, to ensure that data in a predetermined number of partitions distributed as evenly as possible. MySQL supports two partitions Hash: General Linear Hash Hash partitions and partition. A. Conventional Hash partition: Using modulo arithmetic syntax:

partition by hash (store_id) partitions 4;
The above statement, according to store_id for modulo 4, decided to record the storage location. For example store_id = 234 records, MOD (234,4) = 2, it will be stored in the second partition.

Advantages and disadvantages of advantages over conventional Hash partition: the ability to make the data evenly distributed as possible. Disadvantages: not suitable for constantly changing demand partition. If I want to add two new partitions, there are now six partitions, MOD (234,6) the results of the previous MOD (234,4) results will be inconsistent, so most of the data will need to be recalculated partition. To solve this problem, MySQL provides a linear Hash partition.

B. Linear Hash partition: partition function is a linear power of 2 algorithm. grammar:

partition by LINER hash (store_id) partitions 4;
Unlike conventional Hash that, "Liner" keyword. Algorithm description: Suppose you want to keep a record of the partition number N, num is a non-negative integer representing the number is divided into partitions, then N can be obtained by following these steps:
Step 1. Find a num greater than or equal the power of 2, the value of V, V can be obtained by the following formula:
V = Power (2, Ceiling (Log (2, num)))
For example: just set up four partitions, num = 4, Log (2,4) = 2, Ceiling (2) = 2, power (2,2) = 4, that is, V = 4
Step 2. Setting N = F (column_list) & (V-1)
For example: I just V = 4, store_id = 234 corresponding to the value of N, N = 234 & (4-1) = 2
Step 3. When N> = num, set V = Ceiling (V / 2), N = N & (V-1)
For example: store_id = 234, N = 2 <4, so N on the value of 2, you can.
Suppose calculated above N = 5, then V = Ceiling (2.5) = 3, N = 234 & (3-1) = 1, that is, in the first partition.

Advantages and disadvantages of the advantages of linear Hash: In the partition maintenance (add, delete, merge, split partitions) when, MySQL can be handled more quickly. Cons: Compared with conventional Hash partitioning, data Linear Hash partitions between the distribution is not balanced.

11. Key Partition

Similar Hash partitioning, Hash partitioning allows the use of user-defined expression, but Key partition is not allowed user-defined expressions. Hash only supports integer partitions, except Blob Key partitioning support and other types of text columns as the partitioning key. grammar:

partition by key (exp) partitions 4; // exp is a list of zero or more field names
key when the partition, exp can be empty, if empty, the default primary key as the partitioning key, no primary key, will select a non-empty unique key as the partitioning key.

12. The sub-district

The partition table is divided again for each partition, has become a composite partition.

13. Partition for handling NULL values

MySQ allow partition key value is NULL, a partition key might be a user-defined field or expression. Under normal circumstances, MySQL partition when put NULL value as zero value or a minimum of processing.
Range partition: NULL values ​​are treated as a minimum value
List partition: NULL values ​​must appear in the list, will not be accepted
Hash / Key partition: NULL value is treated as zero values

14. Partition Manager

Partition Manager includes an increase for the partition, delete, and query.

Increased partition:
For Range partitions and partition LIst for:

alter table table_name add partition (partition p0 values ​​... (exp))

Depending on the type of content values ​​behind the partition varies.
Key for Hash partition and the partition is:

altertable table_name addpartition partitions 8;

The above statement, referring to the new eight partitions.

2. Delete Partition
For Range and List partitioning partitions:
altertable table_name droppartition p0; // p0 for the name of the partition to be deleted
Deleted partitions, but also will remove the partition all the data. Also, if you delete the partition led to the partition can not cover all the value, then insert the data when the error.
For Hash and Key Subdivision:
altertable table_name coalescepartition2; // partition will be reduced to two
coalesce [ˌkəʊəles] vi. joint, merger
Partition Query 1) a query tables the total number of partitions

mysql> select
 -> Partition_name,
 -> Partition_expression,
 -> Partition_description,
 -> Table_rows
 -> From
 -> Where
 -> Table_schema = 'test' -> and table_name = 'emp';
---------------------- + --------- + ---------------- + -------------- + ------------ +
| Partition_name | partition_expression | partition_description | table_rows |
---------------------- + --------- + ---------------- + -------------- + ------------ +
| P0 | store_id | 10 | 0 |
| P1 | store_id | 20 | 1 |
---------------------- + --------- + ---------------- + -------------- + ------------ +

That is, you can query from information_schema.partitions table.
2) See the execution plan, it is determined whether or not a partition query data filter
mysql> explain partitions select * from emp where store_id = 10 \ G;
*************************** 1. row ******************** *******
        id: 1
select_type: SIMPLE
     table: emp
partitions: p1
      type: system
possible_keys: NULL
       key: NULL
   key_len: NULL
       ref: NULL
      rows: 1
1 row in set (0.00 sec)
The above results: partitions: p1 data representing p1 partition retrieval.
- zBackup: A versatile tool to remove duplicate backup (Linux)
- Red Hat Enterprise Linux 6.4 Configuring VNC Remote Desktop login access (Linux)
- Introduction and use of the Raspberry Pi (Linux)
- Oracle to read and modify the data block process (Database)
- Based Corosync + Pacemaker + DRBD + LNMP Web server to achieve high availability cluster (Server)
- Use Ansible installation NGINX and NGINX Plus (Server)
- git checkout generated in the use of temporary br (Linux)
- Vi / Vim prompt solutions do not have permission to save time (Linux)
- Performance comparison Fibonacci recursive and non-recursive (Programming)
- Ubuntu 14.04 LTS compiler installation R Source Code (Linux)
- Not safe procedure under Linux (Linux)
- C ++ input and output summary _ Input (Programming)
- Java heap (Heap) and stack difference (Programming)
- Iptables Instructions (Linux)
- Linux ldconfig command (Linux)
- Getting Started with Linux system to learn: how to use tcpdump to capture TCP SYN, ACK and FIN packets (Linux)
- MySQL thread cache thread_cache_size parameter optimization (Database)
- How to use the command line ftp upload and download files (Linux)
- DM9000 bare Driver Design (Programming)
- How to use the beta / unstable version of the software in Debian library (Linux)
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.