Shulou(Shulou.com)06/01 Report--

This article mainly tells you about the detailed steps of MySQL performance optimization and partition and table. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article on MySQL performance optimization and the detailed steps of partition and sub-table can bring you some practical help.

I. Sub-table

1. Brief introduction of sub-table

Sub-table is to decompose a large table into multiple physical tables with independent storage space according to certain rules.

If the table you are using needs to be partitioned, you need to modify the rules of app at the same time so that mysql can know where the data the user is querying is.

two。 Sub-table type

Divided into vertical segmentation and horizontal segmentation

Vertical split: divide some columns into another table

Split horizontally: split some rows into another table

3. The way of dividing the table

1) Mysql cluster

It is not a sub-table, but it plays the same role as a sub-table. The cluster can share the number of database operations and share the tasks to multiple databases. The cluster can separate reading and writing, reducing the pressure of reading and writing. Thus improve the performance of the database.

2) pre-estimate the tables with large amount of data and frequently accessed, and divide them into several tables

Allow users to access different tables according to certain algorithms (such as using hash, or using remainder (modulo))

3) using merge storage engine to realize sub-table.

If you use the merge storage engine to subtable, you no longer need to modify the app rules

Merge sub-table, divided into the main table and the child table, the main table is similar to a shell, logically encapsulates the child table, in fact, the data is stored in the child table.

4. Merge storage engine sub-table

Create a child table

Create a master table

Start sub-table

Filter out the data to be inserted into the child table by ID.

Verification

Subtable 2 has only odd data, while the main table will have

Each child table will have its own separate table file

II. Zoning

1. What is zoning?

Partitions are similar to subtables in that they are decomposed according to the rules. The difference is that the sub-table decomposes the large table into several independent physical tables, while the partition divides the data into segments and stores it in multiple locations. After the partition, the table is still a table, but the data is hashed to multiple locations.

two。 Zoning form

Horizontal partitioning (HorizontalPartitioning) is a form of partitioning that partitions the rows of a table, and all the columns defined in the table can be found in every dataset, so the characteristics of the table are still maintained.

Vertical partitioning (VerticalPartitioning) generally reduces the width of the target table through vertical partitioning of the table, so that some specific columns are divided into specific partitions, and each partition contains rows corresponding to the columns in it.

3. Check whether mysql supports partitioning technology

This example is 5.7

First create a table, partition it, and insert data for testing

The following figure shows the inserted data

To check the physical file

View partition information from the partitions table in the information_schema system library

Query data from a partition

Add a partition

Merge Partition

4. Compare the performance of creating a partition and not creating a partition

First create a table tab1 without partitioning

Create table tab2 split by year of date

Create a stored procedure that loops large enough data into the table for testing

Execute stored procedure

Insert tab1 data into tab2

Execute query statements to test performance. Performance with short execution time is good.

Analyze the execution through explain statement

Unpartitioned table

Partitioned table

Create an index for two tables

Test again

3. Partition type of Mysql

Use: partition by type (field) when creating the table to define the partition type and the fields it uses

1. Range

Based on the column value of a continuous interval, multiple rows are assigned to the partition, and the interval cannot overlap.

L define partitions using the values less than operator

L generally end up creating a maxvalue partition to store values that do not match the previous interval

2. List partition

Partition based on column values matching a value in a collection of discrete values (discontiguous values)

L use: Values in, operator to define partition

L List does not have a maxvalue-like definer in range, so values to match must be created manually

3. Hash partition

The hashkey of one or more columns of the table is calculated, and finally the data region corresponding to different values of the Hash code is automatically partitioned, but the number of partitions needs to be specified.

LList and range must specify in which partition a given column value or collection of column values should be saved, and in the HASH partition, MYSQL does this automatically.

The purpose of lHash partitions is to distribute data evenly among predefined partitions

4. Key partition

L is similar to hash, except that hash partition is partitioned by user-defined functions, while key partition is partitioned using functions provided by mysql database. Different storage engines may use different functions.

5. Columns partition

L support from mysql5.5 can be seen as the evolution of range and list partitions. Columns partitions can be partitioned using non-× × data directly.

LCOLUMNS partitions support the following data types:

All × ×, such as INTSMALLINT TINYINT BIGINT. FLOAT and DECIMAL do not support it.

Date types, such as DATE and DATETIME. The remaining date types are not supported.

String types, such as CHAR, VARCHAR, BINARY, and VARBINARY. BLOB and TEXT types are not supported.

COLUMNS can use multiple columns for partitioning.

6. The Mysql partition can specify the storage location. By viewing the help, we can get more parameters about creating the table.

Example: create a partition table and store the data file in another location. The innodb storage engine, the data file and the index file are together, so you only need to specify the location of the data file

7. In fact, hash and key also perform partition operations based on the remainder.

For example, it is divided into four divisions.

4. The difference between Mysql partition and sub-table.

1. Mode of realization

A sub-table really divides a table into multiple small tables

The partition only partitions the data, but the table after the partition is still a table, and the data processing is still done by yourself.

two。 Data processing.

After the sub-table, the data is stored in the sub-table, the total table is just a shell, and the access to data occurs in a sub-table.

Partition, there is no concept of sub-table, partition only divides the file storing data into many small blocks, the table after partition is still a table, the data processing is still done by yourself.

3. Improve performanc

After dividing the table, the concurrency ability of single table is improved, and so is the performance of disk I _ table O. The key point is how to improve the concurrency ability of mysql when accessing data.

The main purpose of partition is to break through the bottleneck of disk Igamot O and to improve the read and write ability of disk so as to increase mysql performance.

4. Difficult and easy to achieve

Except that the sub-table is transparent to the program code with the marge storage engine, most of the other tables are not

Partition implementation is relatively simple, the establishment of partition tables, the root of the normal table is no different, and is transparent to the open code side.

Fifth, the relationship between partitions and subtables

Both can improve the high performance of mysql and have a good performance in the state of high concurrency.

Sub-tables and partitions do not contradict each other, and can cooperate with each other. For those tables with large access volume and more table data, we can adopt the combination of sub-tables and partitions. For tables with small access volume but a lot of table data, we can adopt partitioning and so on.

The sub-table technology is troublesome, it needs to create the child table manually, and the app server needs to calculate the child table name when reading and writing. It is better to use merge, but you also need to create union relationships between child tables and configure child tables.

Compared with the sub-table, the table partition is easy to operate, and there is no need to create child tables.

MySQL performance optimization and partition, sub-table detailed steps to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.