3 Methods for MYSQL table partitioning

Source: Internet
Author: User

Background knowledge:

Table partitioning is to store logically the same range of data in the same file, just like the supermarket, put the same kind of goods in the same area, the different goods in different places. The difference is in the supermarket

is classified according to the purpose, the table partition is divided according to its range of values.

What's the use of partitioning?

1. After partitioning, you can use multiple files to save the data in the table, and these files can be located on different hard disks so that you can take advantage of the IO capability of multiple hard disks simultaneously.

2, the partition after the lock will be more flexible, if you want to lock a large number of data rows at a time, and the table is not partitioned, MYSQL may be to reduce the cost of the lock, will lock up the row

Level is a table lock, if the table is partitioned, the data rows to be accessed are only in the one or two partitions of the table, so that only the two partitions can be locked. Instead of locking the entire table.

3, because the current MySQL database is saved in a folder, as if the folder can not cross the hard disk, so the 1th said the benefits of no, but in other

This benefit exists in databases such as SQL Server.

Method 1,

Partition by range (COLUMN_NAME) partition pt ... Values less than method

CREATE TABLE T1 (X int, Y int)
Partition by Range (X) (
Partition P0 values less than (10),
Partition P3 values less than (100),
Partition P5 values less than maxvalue);

So I gave T1 three zones based on the value of X.

Method 2

Partiton by list (column) partition Pt.. Values in (Value1,value2,...);

CREATE TABLE T2 (X int, Y int)
PARTITION by LIST (X) (
Partition P1 values in (a),
Partition P2 values in (4,5,6),
Partition P3 values in (7,8,9))
;

Here is the file created by Method 1, Method 2.

3 Methods for MYSQL table partitioning

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.