Khóa học miễn phí MySQL – Vertical Partitioning nhận dự án làm có lương

MySQL – Vertical Partitioning

Table of content


The MySQL Partitioning is used to divide large tables into smaller partitions that are stored in different physical locations and are treated as separate tables. Even though the smaller partitions are managed individually, they are still part of the main table.

There are two forms of partitioning in MySQL: Horizontal Partitioning and Vertical Partitioning.

The MySQL Vertical Partitioning

The MySQL Vertical partitioning divides the table into multiple tables based on columns, rather than rows.

There are two main types of vertical partitioning in MySQL, each serving specific purposes −

  • RANGE Columns Partitioning
  • LIST Columns Partitioning

Both Range Columns Partitioning and List Columns Partitioning support various data types, including integer types (TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT), string types (CHAR, VARCHAR, BINARY, and VARBINARY), as well as DATE and DATETIME data types.

Range Columns Partitioning

The MySQL Range Columns partitioning uses one or more columns as partition keys to divide the data into partitions based on a defined range of column values.

The values in these columns are compared to predefined ranges, and each row is assigned to the partition that encompasses the range containing its column values.

Example

In the following query, we are creating a table named INVENTORY and dividing it into three partitions based on “product_quantity” and “product_price” columns. Rows with specific values in these columns are stored in their corresponding partitions −

CREATE TABLE INVENTORY (
   id INT,
   product_name VARCHAR(50),
   product_quantity INT,
   product_price int
)
   PARTITION BY RANGE COLUMNS(product_quantity, product_price) (
   PARTITION P_low_stock VALUES LESS THAN (10, 100),
   PARTITION P_medium_stock VALUES LESS THAN (50, 500),
   PARTITION P_high_stock VALUES LESS THAN (200, 1200)
);

Here, we are inserting rows into the above-created table −

INSERT INTO INVENTORY VALUES
(1, ''Headphones'', 5, 50),
(2, ''Mouse'', 15, 200),
(3, ''Monitor'', 30, 300),
(4, ''Keyboard'', 60, 600),
(5, ''CPU'', 100, 1000);

Following is the INVENTORY table obtained −

id product_name product_quantity product_price
1 Headphones 5 50
2 Mouse 15 200
3 Monitor 30 300
4 Keyboard 60 600
5 CPU 100 1000

Now that we have some data in the INVENTORY table, we can display the partition status to see how the data is distributed among the partitions using the following query −

SELECT PARTITION_NAME, TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME=''inventory

You will see in the output below that the respective columns are assigned to their respective partitions based on the defined range values −

PARTITION_NAME TABLE_ROWS
P_high_stock 2
P_low_stock 1
P_medium_stock 2

Displaying Partitions −

We can also display data from specific partitions using the PARTITION clause. For instance, to retrieve data from partition P_high_stock, we use the following query −

SELECT * FROM inventory PARTITION (P_high_stock);

It will display all the records in partition P_high_stock −

ID NAME AGE ADDRESS SALARY
4 Keyboard 60 600
5 CPU 100 1000

Similarly, we can display other partitions using the same syntax.

List Columns Partitioning

The MySQL List columns partitioning uses one or more columns as partition keys and assigns records to partitions based on specific values in those columns. This method is handy when you want to group data into partitions based on discrete values or categories.

Example

Let us create a table named “EMPLOYEES” and partition it using LIST COLUMNS partitioning based on the “department” column −

CREATE TABLE EMPLOYEES (
   id INT,
   first_name VARCHAR(50),
   last_name VARCHAR(50),
   hiring_date DATE,
   department VARCHAR(50)
)
   PARTITION BY LIST COLUMNS(department) (
   PARTITION p_sales VALUES IN (''Sales'', ''Marketing''),
   PARTITION p_engineering VALUES IN (''Engineering'', ''Research''),
   PARTITION p_operations VALUES IN (''Operations'')
);

Here, we are inserting records into above-created table −

INSERT INTO EMPLOYEES VALUES 
(1, ''John'', ''Doe'', ''2020-01-01'', ''Sales''),
(2, ''Jane'', ''Doe'', ''2020-02-01'', ''Marketing''),
(3, ''Bob'', ''Smith'', ''2020-03-01'', ''Engineering''),
(4, ''Alice'', ''Johnson'', ''2020-04-01'', ''Research''),
(5, ''Mike'', ''Brown'', ''2020-05-01'', ''Operations'');

Following is the EMPLOYEES table obtained −

id first_name last_name hiring_date department
1 John Doe 2020-01-01 Sales
2 Jane Doe 2020-02-01 Marketing
3 Bob Smith 2020-03-01 Engineering
4 Alice Johnson 2020-04-01 Research
5 Mike Brown 2020-05-01 Operations

We can display the partition status of the EMPLOYEES table to see how the data is distributed among partitions using the following query −

SELECT PARTITION_NAME, TABLE_ROWS 
FROM INFORMATION_SCHEMA.PARTITIONS 
WHERE TABLE_NAME=''EMPLOYEES

It will display the partitions and the number of rows in each partition based on the department values −

PARTITION_NAME TABLE_ROWS
p_engineering 2
p_operations 1
p_sales 2

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc