Mysql partition and instance demonstration, mysql partition instance demonstration

Source: Internet
Author: User

Mysql partition and instance demonstration, mysql partition instance demonstration


    I. Why partition?

    Requirement: big data. Solution: divide and conquer. Divides large tables and large indexes into a smaller operation unit.

    In mysql, partitions allow table, index, and index orchestration tables to be subdivided into smaller units. After a partition, each partition has its own name. For DBAs, these partitions can be managed in a unified manner or separately.

    However, for applications, the partitioned tables are the same as those without partitions. In other words, partitions are transparent to applications, but the database reorganizes the data.


    Mysql partition. This is mysql's official explanation of the partition.

     

    1. Partition Function

    A) Improve Performance

     

    It should be said that almost all partitions are used to improve performance. It's not who eats the Egg bread one morning. Let's have fun in different zones! No. So how does partitioning improve the performance of data retrieval? Before data is partitioned, data is always queried and retrieved for the entire database and table. After partitioning, mysql generates specific data files and index files for each partition. Only a specific part of data is retrieved each time, so it is better to execute and maintain the database. The reason is that after the partition, the table is assigned to different physical drives, so that the physical I/O contention can be reduced when multiple partitions are accessed at the same time.


    B) Ease of Management


    For some useless redundant historical data, you can directly Delete the corresponding partition based on the partition. It is easier to operate, because it is much more difficult and time-consuming to execute tens of thousands of data records than to delete files directly.


    C) Fault Tolerance


    After partitioning, a table is divided into three files from the previous file compared to before partitioning. After partitioning, even if a file is damaged, other data will not be affected.

    2 ..Common partitioning Methods

    1. RANGE partitioning
    2. LIST Partition
    3. HASH Partition
    4. KEY Partition

    Ii. How to partition?

    1.Check whether the database supports partitioning

    SHOW VARIABLES LIKE '%partition%';

     

     

     

    For mysql, currently versions 5.1 and support partition operations. If YES, the database supports partition operations.

     

    2. Four common partitions

     


    Range Partition

    Commonly known as range partition. Partitions are based on the value of a table field and a given continuous interval.

     

    Directly create table Time Zone

     

    create table teacher(id varchar(20) not null ,name varchar(20),age varchar(20),birthdate date not null,salary int)partition by range(year(birthdate))(partition p1 values less than (1970),partition p2 values less than (1990),partition p3 values less than maxvalue);


     

    Ps: Create a teacher table. When creating a teacher table, divide the table into three partitions: p1, p2, and p3 Based on the birthdate field.

     

     

    Partition after creating a table

     

    ALTER TABLE teacher partition by range(year(birthdate))(partition p1 values less than (1970),partition p2 values less than (1990),partition p3 values less than maxvalue);

    Ps: partitions of the created table, including p1, p2, and p3.

     

    LIST Partition

    Common Name: List partition. In fact, list partitions and range partitions are the same. The difference is that range partitions are based on a continuous interval; list partitions are based on a group of distributed hash values.


    create table student (id varchar(20) not null , studentno int(20) not null, name varchar(20), age varchar(20) ) partition by list(studentno) ( partition p1 values in (1,2,3,4), partition p2 values in  (5,6,7,8), partition p3 values in (9,10,11) );


    Ps: Create the student table and divide it into three partitions: p1, p2, and p3. Note that the partition field of a table is of the int or other numerical type.

     

    HASH Partition

     

    Nickname: Hash partition. Hash partitions are based on a table field and the number of specified partitions.


    create table user (  id int(20) not null,  role varchar(20) not null,  description varchar(50) )partition by hash(id) partitions 10;

     

    Ps: create a user table and divide the user table into ten partitions on average. The limit is that you need to know how much data the table has to distribute partitions evenly.

     

     

    Key Partition

    Similar to HASH partitioning, the difference is that KEY partitioning only supports computing one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain integer values.


     

    create table role( id int(20) not null,name varchar(20) not null)partition by linear key(id)partitions 10;

     

    3. Partition Table Management

    Add partitions to a specified table


    alter table user add partition(partition p4 values less than MAXVALUE);  


    Deletes a specified partition from a specified table.


    Alter table student drop partition p1;


    Create a subpartition


    create table role_subp(id int(20) not null,name int(20) not null)partition by list(id)subpartition by hash(name)subpartitions 3(  partition p1 values in(10),  partition p2 values in(20))

     

    Composite Partition

     

    alter table userreorganize partition p1,p3 into(partition p1 values less than (1000));


Related Article

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.