MySQL Partitioning – Generally idle

Tram Ho

Mysql Partitioning as its name implies is the division of a table into small chunks according to a certain logic, separated by key, this key is usually the name of the column in the table. As we know mysql and many other database management systems, it stores data in a table form of rows and columns. Each time the DB engine query has to go through the entire table to get data, this creates a performance problem when the record in the table is too large, this problem will be solved quite simply by partition, thanks to this technique. We will only get data in certain areas instead of the entire table as before. Let’s see an example to better understand the partition operations. For example we have table persons that have never been partitioned, and have specific fields as follows:

This table, when created by default, will be stored as a chunk in the file system

When using the partition, the table will be divided into multiple chunks with the key we defined. For example, here I use the age field as the key.

We can test the partition’s effectiveness with a simple query like this:

In the absence of a partition, the query execution time is 0.00064 sec.

With the partition, the results differ markedly, with the execution time being as little as 0.0016 seconds.

Because our table has little data, the efficiency is hard to feel, but for databases with millions of records, this is really a great solution. So through the above example, you have partly understood what Partition is and its effects, now let’s go into more detail.

1. How to create a partition

Before you create the partition, make sure that which column you use is used frequently in queries, it makes sense to create a new partition.

You can create a partition using CREATE TABLE or ALTER TABLE

For example :

2. Main partition types

  • Range partitioning
  • List partitioning
  • Columns partitioning
  • Hash partitioning
  • Key partitioning
  • Subpartitioning In the scope of the article and understanding I will focus on clarifying two types of partitions: Range partitioning, List partitioning . The rest of the categories will meet you in another article.

2.1. Range partitioning

Range partitioning simply means partitioning according to the range you want to use, that is, dividing the table into many ranges of values, these ranges of values ​​must be consecutive and do not overlap, for example, in 1 year you have 12 month, we can divide it into 12 consecutive intervals like

p1: 01-01-2020 to 31-01-2020 p2: 01-02-2020 to 29-02-2020 p3: 01-03-2020 to 30-03-2020 …. Purpose of zoning by will help insert and search much faster, when insert if there is a value in any range, it will be inserted in the specified range, and also when searching. Creating range partitioning requires the VALUE LESS THAN keyword to specify the range to use. Let’s see the following specific example:

Here we have created a sales table with 4 partitions specified with the scope of use, p0 is going to store records with amount <100. p2 will have 100 <= amount <300 Similar to the remaining partitions. again. Now let’s insert into this table

And when we select the results we see the results

Note before creating the range partioning, we need to specify the necessary ranges for the needs, if now we insert a row with amount> 1000, how will it be? You try to test and answer yourself

2.2. List partitioning

This type differs slightly from the range type as it does not divide by intervals but it picks up the specified elements to form a list, this type we will use the keyword VALUES IN (list_value) to create the partition . Also take the example above but a little more in the sales table we have an extra column of sales employee code, saler_id to know which employees have sold, and for example in a company we have 10 salespeople, along with 3 sale group, Each group works on a part of the road as required by their superiors.

For example:

  • Group A works in Pham Van Dong
  • Group B works in Tran Duy Hung
  • Group C works in Lang

Now let’s create the sales table as follows

And so later, when you want to search for group statistics, it will also be faster, statistic about which employees sell how many rows will also be faster than browsing the entire table.

3. Delete partition

If you don’t need a certain amount of data during system transport, you can delete it by deleting the partition itself.

4. Conclusion

So in this article I have introduced to you the concept, the purpose of using Mysql partition , and two main types of partitions commonly used are Range partition và List partition . Hope this will be useful knowledge to help you optimize queries during project work with large databases. The article may be flawed, as well as the misuse of partition and partitioning wording I hope you contribute and see you in the earliest article on the remaining four mysql partitioning .

Share the news now

Source : Viblo