Modify the partition field online in MySQL partition table partition, and learn more about partition later (1)-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Modify the partition field online in MySQL partition table partition, and learn more about partition later (1)

黄舟

Feb 17, 2017 pm 01:10 PM

The company is using partition online, and the partition field of one table is wrong and needs to be rebuilt. It turns out that there is no way to do it directly with a SQL like modifying the primary key field or modifying the index field. Instead, we need to build a temporary table and have down time, so I read the documentation carefully and studied the details of the partition.

When my company took it online, during the low business peak period at 1 am, I executed:

Create a temporary table

CREATE TABLE tbname_TMP (    
SHARD_ID INT NOT NULL,    
...
    xxx_DATE DATETIME NOT NULL,    
    PRIMARY KEY (xxx_DATE,shard_id)) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_binPARTITION BY LIST(MONTH(xxx_DATE)) (    
    PARTITION m1 VALUES IN (1),    
    PARTITION m2 VALUES IN (2),    
    PARTITION m3 VALUES IN (3),    
    PARTITION m4 VALUES IN (4),    
    PARTITION m5 VALUES IN (5),    
    PARTITION m6 VALUES IN (6),    
    PARTITION m7 VALUES IN (7),    
    PARTITION m8 VALUES IN (8),    
    PARTITION m9 VALUES IN (9),    
    PARTITION m10 VALUES IN (10),    
    PARTITION m11 VALUES IN (11),    
    PARTITION m12 VALUES IN (12)
    );

Copy after login

Switch the table name and modify the table structure

RENAME TABLE xxx TO xxx_DELETED, xxx_TMP TO xxx;

Copy after login

Import the original data

insert into xxx select * from xxx_DELETEDxxx_DELETED;

Copy after login

OK, everything is done, the whole process takes 50 minutes, the outline operation table structure changes and data import after MMM failover switching, The actual downtime does not include the time to modify the table structure partition field, only the failover switching time is 30 seconds

MySQL Partition, the official English information I read, the translation level is limited, some are not translated into Chinese, and are posted directly in English.
1 list partition table

mysql> CREATE TABLE `eh` (
    ->   `id` int(11) NOT NULL,
    ->   `ENTITLEMENT_HIST_ID` bigint(20) NOT NULL,
    ->   `ENTITLEMENT_ID` bigint(20) NOT NULL,
    ->   `USER_ID` bigint(20) NOT NULL,
    ->   `DATE_CREATED` datetime NOT NULL,
    ->   `STATUS` smallint(6) NOT NULL,
    ->   `CREATED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `MODIFIED_BY` varchar(32) COLLATE utf8_bin DEFAULT NULL,
    ->   `DATE_MODIFIED` datetime NOT NULL,
    ->   PRIMARY KEY (`DATE_MODIFIED`,`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    -> /*!50100 PARTITION BY LIST (MONTH(DATE_MODIFIED))
    -> (PARTITION m1 VALUES IN (1) ENGINE = InnoDB,
    ->  PARTITION m2 VALUES IN (2) ENGINE = InnoDB,
    ->  PARTITION m3 VALUES IN (3) ENGINE = InnoDB,
    ->  PARTITION m4 VALUES IN (4) ENGINE = InnoDB,
    ->  PARTITION m5 VALUES IN (5) ENGINE = InnoDB,
    ->  PARTITION m6 VALUES IN (6) ENGINE = InnoDB,
    ->  PARTITION m7 VALUES IN (7) ENGINE = InnoDB,
    ->  PARTITION m8 VALUES IN (8) ENGINE = InnoDB,
    ->  PARTITION m9 VALUES IN (9) ENGINE = InnoDB,
    ->  PARTITION m10 VALUES IN (10) ENGINE = InnoDB,
    ->  PARTITION m11 VALUES IN (11) ENGINE = InnoDB,
    ->  PARTITION m12 VALUES IN (12) ENGINE = InnoDB) */;
Query OK, 0 rows affected (0.10 sec)

Copy after login

2 rang partition table

mysql> CREATE TABLE rcx (
    ->     a INT,
    ->     b INT,
    ->     c CHAR(3),
    ->     d INT
    -> )
    -> PARTITION BY RANGE COLUMNS(a,d,c) (
    ->     PARTITION p0 VALUES LESS THAN (5,10,&#39;ggg&#39;),
    ->     PARTITION p1 VALUES LESS THAN (10,20,&#39;mmmm&#39;),
    ->     PARTITION p2 VALUES LESS THAN (15,30,&#39;sss&#39;),
    ->     PARTITION p3 VALUES LESS THAN (MAXVALUE,MAXVALUE,MAXVALUE)
    -> );
Query OK, 0 rows affected (0.15 sec)

Copy after login

3 create range use less character

CREATE TABLE employees_by_lname (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT NOT NULL,
    store_id INT NOT NULL
)

Copy after login

PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN (&#39;g&#39;),
    PARTITION p1 VALUES LESS THAN (&#39;m&#39;),
    PARTITION p2 VALUES LESS THAN (&#39;t&#39;),
    PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Copy after login

alter table structure, add a new partition block

ALTER TABLE employees_by_lname PARTITION BY RANGE COLUMNS (lname)  (
    PARTITION p0 VALUES LESS THAN (&#39;g&#39;),
    PARTITION p1 VALUES LESS THAN (&#39;m&#39;),
    PARTITION p2 VALUES LESS THAN (&#39;t&#39;),
 PARTITION p3 VALUES LESS THAN (&#39;u&#39;),
    PARTITION p4 VALUES LESS THAN (MAXVALUE)
);

Copy after login

4 List columns partitioning

character column
CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

Copy after login

PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN(&#39;Oskarshamn&#39;, &#39;H?gsby&#39;, &#39;M?nster?s&#39;),
    PARTITION pRegion_2 VALUES IN(&#39;Vimmerby&#39;, &#39;Hultsfred&#39;, &#39;V?stervik&#39;),
    PARTITION pRegion_3 VALUES IN(&#39;N?ssj?&#39;, &#39;Eksj?&#39;, &#39;Vetlanda&#39;),
    PARTITION pRegion_4 VALUES IN(&#39;Uppvidinge&#39;, &#39;Alvesta&#39;, &#39;V?xjo&#39;)
);

Copy after login

date column

CREATE TABLE customers_2 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)

Copy after login

PARTITION BY LIST COLUMNS(renewal) (
    PARTITION pWeek_1 VALUES IN(&#39;2010-02-01&#39;, &#39;2010-02-02&#39;, &#39;2010-02-03&#39;,
        &#39;2010-02-04&#39;, &#39;2010-02-05&#39;, &#39;2010-02-06&#39;, &#39;2010-02-07&#39;),
    PARTITION pWeek_2 VALUES IN(&#39;2010-02-08&#39;, &#39;2010-02-09&#39;, &#39;2010-02-10&#39;,
        &#39;2010-02-11&#39;, &#39;2010-02-12&#39;, &#39;2010-02-13&#39;, &#39;2010-02-14&#39;),
    PARTITION pWeek_3 VALUES IN(&#39;2010-02-15&#39;, &#39;2010-02-16&#39;, &#39;2010-02-17&#39;,
        &#39;2010-02-18&#39;, &#39;2010-02-19&#39;, &#39;2010-02-20&#39;, &#39;2010-02-21&#39;),
    PARTITION pWeek_4 VALUES IN(&#39;2010-02-22&#39;, &#39;2010-02-23&#39;, &#39;2010-02-24&#39;,
        &#39;2010-02-25&#39;, &#39;2010-02-26&#39;, &#39;2010-02-27&#39;, &#39;2010-02-28&#39;)
);

Copy after login

5 HASH Partitioning

int column,it can use digital function
CREATE TABLE employeesint (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)
PARTITION BY HASH(MOD(store_id,4))
PARTITIONS 4;

Copy after login

If you do not include a PARTITIONS clause, the number of partitions defaults to 1. as below :

CREATE TABLE employeestest (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id);

Copy after login

date colum

CREATE TABLE employees2 (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)
PARTITION BY HASH( YEAR(hired) )
PARTITIONS 4;

Copy after login

truncate all data rows: alter table rcx truncate PARTITION;

6 LINE AR HASH Partitioning

CREATE TABLE employees_linear (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT &#39;1970-01-01&#39;,
    separated DATE NOT NULL DEFAULT &#39;9999-12-31&#39;,
    job_code INT,
    store_id INT
)

Copy after login

PARTITION BY LINEAR HASH( YEAR(hired) )
PARTITIONS 4;

Given an expression expr, the partition in which the record is stored when linear hashing is used is partition number N from among num partitions, where N is derived according to the following algorithm:
(1) Find the next power of 2 greater than num. We call this value V; it can be calculated as:
V = POWER(2, CEILING(LOG(2, num)))
(Suppose that num is 13. Then LOG(2,13) is 3.7004397181411. CEILING(3.7004397181411) is 4, and V = POWER(2,4), which is 16.)
(2) Set N = F(column_list) & (V - 1).
(3) While N >= num:
Set V = CEIL(V / 2)
Set N = N & (V - 1)

[Note] The calculation principle of & in SQL is: For example
Convert decimal to binary and you get http://www.php.cn/

First align to the right, for example, become 0011 and 1000. Judge according to the number of each digit. If both are 1, the corresponding position of the result is 1, otherwise It is 0
If it is 1011 and 1000, the result is 1000
If it is 0110 and 1010, the result is 0010
But 3 is 0011 and 8 is 1000, so the result of 3&8 is 0

CEILING(X) CEIL(X): Return The smallest integer value that is not less than X.
LOG(X) LOG(B,X): If called with one parameter, this function will return the natural logarithm of X.
POWER(X,Y): Returns the result value of X raised to the power of Y.

Calculation method of which slice the data is distributed in:
Suppose that the table t1, using linear hash partitioning and having 6 partitions, is created using this statement:

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
    PARTITION BY LINEAR HASH( YEAR(col3) )
    PARTITIONS 6;

Copy after login

Now assume that you want to insert two records into t1 having the col3 column values '2003-04-14' and '1998-10-19'. The partition number for the first of these is determined as follows:

 V = POWER(2, CEILING( LOG(2,6) )) = 8
 N = YEAR(&#39;2003-04-14&#39;) & (8 - 1)
    = 2003 & 7
    = 3
 (3 >= 6 is FALSE: record stored in partition #3)

Copy after login

The number of the partition where the second record is stored is calculated as shown here:

 V = 8 N = YEAR(&#39;1998-10-19&#39;) & (8-1)   = 1998 & 7   = 6
 (6 >= 6 is TRUE: additional step required)
 N = 6 & CEILING(8 / 2)   = 6 & 3   = 2
 (2 >= 6 is FALSE: record stored in partition #2)

Copy after login

The advantage in partitioning by linear hash is that the adding, dropping, merging, and splitting of partitions is made much faster, which can be beneficial when dealing with tables containing extremely large amounts ( terabytes) of data. The disadvantage is that data is less likely to be evenly distributed between partitions as compared with the distribution obtained using regular hash partitioning.

One of the questions: How does MySQL use a SQL to delete partition fields without using a temporary table? Convert a partitioned table into a normal table?

The above is the MySQL partition table partition online modification of the partition field. Later, we will further learn the content of partition (1). For more related content, please pay attention to the PHP Chinese website (www.php.cn)!

Statement of this Website

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress images for free

Clothoff.io

AI clothes remover

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

How to fix KB5055523 fails to install in Windows 11?

3 weeks ago By DDD

How to fix KB5055518 fails to install in Windows 10?

3 weeks ago By DDD

Roblox: Dead Rails - How To Tame Wolves

4 weeks ago By DDD

Roblox: Grow A Garden - Complete Mutation Guide

2 weeks ago By DDD

Strength Levels for Every Enemy & Monster in R.E.P.O.

4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial

1657

CakePHP Tutorial

1415

Laravel Tutorial

1309

PHP Tutorial

1257

C# Tutorial

1230

Related knowledge

MySQL's Role: Databases in Web Applications Apr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

How to start mysql by docker Apr 15, 2025 pm 12:09 PM

The process of starting MySQL in Docker consists of the following steps: Pull the MySQL image to create and start the container, set the root user password, and map the port verification connection Create the database and the user grants all permissions to the database

Laravel Introduction Example Apr 18, 2025 pm 12:45 PM

Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.

Solve database connection problem: a practical case of using minii/db library Apr 18, 2025 am 07:09 AM

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

Laravel framework installation method Apr 18, 2025 pm 12:54 PM

Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.

MySQL and phpMyAdmin: Core Features and Functions Apr 22, 2025 am 12:12 AM

MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.

MySQL vs. Other Programming Languages: A Comparison Apr 19, 2025 am 12:22 AM

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL vs. Other Databases: Comparing the Options Apr 15, 2025 am 12:08 AM

MySQL is suitable for web applications and content management systems and is popular for its open source, high performance and ease of use. 1) Compared with PostgreSQL, MySQL performs better in simple queries and high concurrent read operations. 2) Compared with Oracle, MySQL is more popular among small and medium-sized enterprises because of its open source and low cost. 3) Compared with Microsoft SQL Server, MySQL is more suitable for cross-platform applications. 4) Unlike MongoDB, MySQL is more suitable for structured data and transaction processing.

See all articles