[MySQL5.1 experience] MySQL Partition

Translated by: ye Jinrong (Email :), source: imysql.cn I. Summary I believe many people may frequently ask the same question: when the total number of MySQL records exceeds 1 million, will there be a significant reduction in performance? The answer is yes, but the performance drop rate varies, depending on the system architecture, applications, indexes,

Http://imysql.cn I. Summary I believe many people often ask the same question: when the total number of MySQL records exceeds 1 million, will there be a significant reduction in performance? The answer is yes, but the performance drop rate varies, depending on the system architecture, applications, including indexes,

Ye Jinrong (Email:), source: http://imysql.cn

I. Overview

I believe many people often ask the same question: when MySQL
When the total number of records exceeds 1 million, will the performance be greatly reduced? The answer is yes, but the performance drop-down rate is different. It depends on the system architecture, applications, and various factors, including index and server hardware. When a netizen asks me this question, my most common answer is: Table sharding, which can be divided into multiple values based on the id range or time sequence.
Table sharding rules. Table sharding is easy. However, the resulting changes to the application and even the architecture are not too small, but also include future scalability.

In the past, a solution was to use MERGE
Type, which is very convenient for cooking. There is basically no need to change the architecture and program. However, its disadvantages are obvious:

• Only MyISAM tables with the same structure can be used
• You cannot enjoy all the functions of MyISAM. For example, you cannot perform FULLTEXT search on the MERGE type.
• It requires more file descriptors
• Slow index reading

At this time, the advantages of the Partition Function added in MySQL 5.1 are obvious:

• You can store more data than a single disk or file system partition.
• It is easy to delete unnecessary or outdated data.
• Some queries can be greatly optimized.
• When Aggregate functions such as SUM ()/COUNT () are involved
• Higher IO Throughput

Partitions can be set to rules of any size, and multiple parts of a single table are allocated across file systems. In fact, different parts of a table are stored as separate tables in different locations.

Ii. partition type

• RANGE partition: multiple rows are allocated to the partition based on the column values in a given continuous interval. See section 18.2.1, RANGE partitioning
• LIST partition: similar to partitioning by RANGE, the difference is that LIST partition is selected based on the column value matching a value in a discrete value set. See section 18.2.2, LIST partitions.
• HASH partition: select a partition based on the return value of a user-defined expression. This expression uses the column values of the rows to be inserted into the table for calculation. This function can include any expressions that contain valid non-negative integer values in MySQL. See section 18.2.3, HASH Partition
• KEY
  Partitioning: similar to partitioning by HASH, the difference is that the KEY partition only supports computing one or more columns, and the MySQL server provides its own HASH function. One or more columns must contain> integer values.
  See section 18.2.4, KEY Partition

Iii. Partition example:

• RANGE type
  

  CREATE TABLE users (
      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL DEFAULT '',
      email VARCHAR(30) NOT NULL DEFAULT ''
  )
  PARTITION BY RANGE (uid) (
      PARTITION p0 VALUES LESS THAN (3000000)
      DATA DIRECTORY = '/data0/data'
      INDEX DIRECTORY = '/data1/idx',
  
      PARTITION p1 VALUES LESS THAN (6000000)
      DATA DIRECTORY = '/data2/data'
      INDEX DIRECTORY = '/data3/idx',
  
      PARTITION p2 VALUES LESS THAN (9000000)
      DATA DIRECTORY = '/data4/data'
      INDEX DIRECTORY = '/data5/idx',
  
      PARTITION p3 VALUES LESS THAN MAXVALUE    DATA DIRECTORY = '/data6/data' 
      INDEX DIRECTORY = '/data7/idx'
  );
  

  Here, the user table is divided into four partitions, with every 3 million records as the limit, each partition has its own independent data, index file storage directory, at the same time, directory>
  Physical Disk Partitions may also be completely independent, which increases the disk I/O throughput.

• LIST type
  

  CREATE TABLE category (
      cid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL DEFAULT ''
  )
  PARTITION BY LIST (cid) (
      PARTITION p0 VALUES IN (0,4,8,12)
      DATA DIRECTORY = '/data0/data' 
      INDEX DIRECTORY = '/data1/idx',
      
      PARTITION p1 VALUES IN (1,5,9,13)
      DATA DIRECTORY = '/data2/data'
      INDEX DIRECTORY = '/data3/idx',
      
      PARTITION p2 VALUES IN (2,6,10,14)
      DATA DIRECTORY = '/data4/data'
      INDEX DIRECTORY = '/data5/idx',
      
      PARTITION p3 VALUES IN (3,7,11,15)
      DATA DIRECTORY = '/data6/data'
      INDEX DIRECTORY = '/data7/idx'
  );  
  

  It is divided into four zones, where data files and index files are stored separately.

• HASH type
  

  CREATE TABLE users (
      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL DEFAULT '',
      email VARCHAR(30) NOT NULL DEFAULT ''
  )
  PARTITION BY HASH (uid) PARTITIONS 4 (
      PARTITION p0
      DATA DIRECTORY = '/data0/data'
      INDEX DIRECTORY = '/data1/idx',
  
      PARTITION p1
      DATA DIRECTORY = '/data2/data'
      INDEX DIRECTORY = '/data3/idx',
  
      PARTITION p2
      DATA DIRECTORY = '/data4/data'
      INDEX DIRECTORY = '/data5/idx',
  
      PARTITION p3
      DATA DIRECTORY = '/data6/data'
      INDEX DIRECTORY = '/data7/idx'
  );
  

  It is divided into four zones, where data files and index files are stored separately.

• KEY type
  

  REATE TABLE users (
      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL DEFAULT '',
      email VARCHAR(30) NOT NULL DEFAULT ''
  )
  PARTITION BY KEY (uid) PARTITIONS 4 (
      PARTITION p0
      DATA DIRECTORY = '/data0/data'
      INDEX DIRECTORY = '/data1/idx',
      
      PARTITION p1
      DATA DIRECTORY = '/data2/data' 
      INDEX DIRECTORY = '/data3/idx',
      
      PARTITION p2 
      DATA DIRECTORY = '/data4/data'
      INDEX DIRECTORY = '/data5/idx',
      
      PARTITION p3 
      DATA DIRECTORY = '/data6/data'
      INDEX DIRECTORY = '/data7/idx'
  );  
  

  It is divided into four zones, where data files and index files are stored separately.

• Subpartition
  Subpartitions are used to split each partition in a RANGE/LIST table. Re-division can be HASH/KEY type. For example:

  CREATE TABLE users (
      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL DEFAULT '',
      email VARCHAR(30) NOT NULL DEFAULT ''
  )
  PARTITION BY RANGE (uid) SUBPARTITION BY HASH (uid % 4) SUBPARTITIONS 2(
      PARTITION p0 VALUES LESS THAN (3000000)
      DATA DIRECTORY = '/data0/data'
      INDEX DIRECTORY = '/data1/idx',
  
      PARTITION p1 VALUES LESS THAN (6000000)
      DATA DIRECTORY = '/data2/data'
      INDEX DIRECTORY = '/data3/idx'
  );
  

  The RANGE partition is partitioned by subpartitions again. The subpartitions adopt the HASH type.

  Or

  CREATE TABLE users (
      uid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(30) NOT NULL DEFAULT '',
      email VARCHAR(30) NOT NULL DEFAULT ''
  )
  PARTITION BY RANGE (uid) SUBPARTITION BY KEY(uid) SUBPARTITIONS 2(
      PARTITION p0 VALUES LESS THAN (3000000)
      DATA DIRECTORY = '/data0/data'
      INDEX DIRECTORY = '/data1/idx',
  
      PARTITION p1 VALUES LESS THAN (6000000)
      DATA DIRECTORY = '/data2/data'
      INDEX DIRECTORY = '/data3/idx'
  );
  

  The RANGE partition is divided into sub-partitions again, and the sub-partitions adopt the KEY type.

Iv. Partition Management

• Delete Partition
  

  ALERT TABLE users DROP PARTITION p0;
  

  Delete partition p0.

• Re-create a partition
  • RANGE partition Reconstruction
    

    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES LESS THAN (6000000));
    

    Combine the original p0 and p1 partitions and place them in the new p0 partition.

  • LIST partition Reconstruction
    

    ALTER TABLE users REORGANIZE PARTITION p0,p1 INTO (PARTITION p0 VALUES IN(0,1,4,5,8,9,12,13));
    

    Combine the original p0 and p1 partitions and place them in the new p0 partition.

  • HASH/KEY partition Reconstruction
    

    ALTER TABLE users REORGANIZE PARTITION COALESCE PARTITION 2;
    

    The number of re-created partitions in the REORGANIZE mode is changed to 2. Here, the number can only be reduced and cannot be increased. To ADD a PARTITION, use the add partition method.

• Add Partition
  • Add RANGE Partition
    

    ALTER TABLE category ADD PARTITION (PARTITION p4 VALUES IN (16,17,18,19)
    DATA DIRECTORY = '/data8/data'
    INDEX DIRECTORY = '/data9/idx');
    

    Adds a RANGE partition.

  • Add HASH/KEY partitions
    

    ALTER TABLE users ADD PARTITION PARTITIONS 8;
    

    Extend the total number of partitions to 8.

Now, this is a first-served experience. For more details, see
Target = "_ blank"> MySQL Manual Chapter 18th.