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

Blogger QQ:819594300

Blog address: http://zpf666.blog.51cto.com/

Friends who have any questions can contact the blogger, the blogger will help you answer, thank you for your support! I. Sub-table

Why do you need separate meters?

Our database data is getting larger and larger, followed by too much data in a single table. As a result, the query book reading becomes slow, and the application operation is seriously affected by the locking mechanism of the table, which leads to the bottleneck of database performance.

What is a sub-table?

Sub-table is to decompose a standard into several physical tables with independent storage space according to certain rules, each table corresponds to three files, .MYD data file, .MYI index file, .frm table structure file. These tables can be distributed on the same disk or on different disks on different hosts.

When App reads and writes, it gets the corresponding table name according to the pre-defined rules, and then manipulates it.

Split a single database table into multiple data tables, and then allow users to access different tables according to a certain algorithm (such as using hash, or using remainder (modular)), so that the data is distributed into multiple data tables, reducing the access pressure of a single data table. The performance of database access is improved. The purpose of sub-table is to reduce the burden of the database and shorten the query time.

The Mysql subtable is divided into vertical segmentation and horizontal segmentation.

Vertical segmentation refers to the splitting of data table columns, dividing a table with more columns into multiple tables. We often put several commonly used columns in one table and less commonly used columns in another table.

Horizontal split refers to the split of data table rows, splitting the data of a table into multiple tables to store. In general, we use hash algorithm and modularization to split the table. For example: for example, a user table users with 400W, in order to improve its query efficiency, we divide it into four tables users1,users2,users3,users4, by using ID modeling method to distribute the data into four tables Id%4=, and then query, update, delete is also through the modular method to query.

How many ways to divide 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 read and write, reduce the read and write pressure, and improve the performance of the database.

2) pre-estimate the tables that will appear a lot of data and access frequently, and divide them into several tables.

According to a certain algorithm (such as using hash, you can also use the method of remainder (modular)) to let users access different tables.

For example, the list of posts posted in the forum must be very big over a long period of time, hundreds of thousands or millions. Chat room inside the information table, dozens of people chat together for a night, for a long time, the data on this table must be very large. There are a lot of situations like this. Therefore, for this predictable big data scale, we will divide it into N tables in advance, and what this N is depends on the actual situation. Take chat information table as an example: we built 100 such tables in advance, message_00,message_01,message_02.message_98,message_99. Then according to the user's ID to determine which table the user's chat information is put in, it can be obtained either by hash or by remainder. There are many ways. Or you can design that each table contains N pieces of data, so how can you tell whether the data capacity of a table is full? In the program section, you can do the operation of the number of records in the statistical table before inserting the show variables like'% partition%' for the table to be added.

+-+ +

| | Variable_name | Value |

+-+ +

| | have_partition_engine | YES |

+-+ +

Description: if yes indicates that your current configuration supports partitions

After ② is 5.6and adopted, it will be viewed as follows

Note: in the display result, you can see that partition is ACTIVE, indicating that partition is supported.

Let's first demonstrate a table partition by range.

1) create range partition table

2) insert some data (to explain: the forehead data above the decomposition point will be assigned to the next partition, for example, data 3 will not put the p0 table, but the p1 partition)

3) take a look at the place where the database table files are stored

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

5) query data from a partition

6) New Partition

Mysql > alter table library name. Table name add partition (new partition name values less than (n) for partition)

Note: n is conditional on a specific number or maxvalues

7) Delete the partition

Note: when a partition is deleted, all data in that partition is also deleted.

8) merging of divisions

Performance testing of unpartitioned tables and partitioned tables

1) create an unpartitioned table

2) create a partition table and split it by the year of the date

3) insert 1 million pieces of test data through stored procedures

Create a stored procedure:

Note: the RAND () function produces a random number between 0 and 1, and if an integer parameter N is specified, it is used as a seed value. Each seed produces a different sequence of random numbers.

Execute the stored procedure load_part_tab to insert data into the bdqn.tab1 table:

Step out of mysql's interactive mode, and then after entering interactive mode, execute the following command

4) insert data into the test2.tab2 table

5) Test the performance of SQL

Summary: the results show that the execution time of partitioned tables is much less than that of unpartitioned tables.

6) analyze the execution through the explain statement

Summary: the explain statement shows the number of records to be processed by the SQL query, and you can see that the partitioned table has significantly fewer records scanned than the unpartitioned table.

7) Test the situation after creating the index

Summary: after creating the index, the partitioned table is not much different from the unpartitioned table (but the larger the amount of data is, the more obvious the difference is).

Type of Mysql partition

1. Range partition (range partition)

Function: assign multiple rows in a continuous column value to the partition, the column interval is continuous and does not overlap.

Example:

Summary: P0 to p3 partitions are defined in order, from the lowest to the highest, and from the highest to the lowest, the values at the demarcation point are automatically put into the next partition. For example, if store_id is 16, it is automatically put into the p3 partition, but if you insert data with store_id of 21, an error will be reported, because there is no partition containing 21. In order to avoid this error, we usually set the last partition of the range partition to maxvalue partition. Assign this value to the maxvalue partition that is not included in all previous partitions to avoid reporting errors.

If no maxvalue partition was created when the table was created, you can add it with the following command:

2. List partition (list partition)

Function: make a selection based on column values matching a value in a set of discrete values

Example:

Summary: list divides the data easily by region. For example, if the company plans to sell all its stores in the west, it only needs to delete pwest, which is very convenient. Note that if the value of the store_id column inserted by the view does not belong to any partition, the mysql will report an error and the insert will fail. The list partition does not have a maxvalue (maximum) partition of the range partition type, and the column values to match must be values that several partitions already had when the table was created.

Use the following statement to delete the pwest partition, which is the same as the DELETE (delete) query

"deletefrom employees2 where store_id in (7, 8, 15, 16);" is much more effective than that.

Delete pnorch partitions with another deletion method

3. Hash partition

Function: calculate the hash key of one or more columns of the table, and finally partition the data area corresponding to different values of the hash code.

Example:

Summary: hash partitions do not need to specify a collection of partitions, mysql will automatically complete the partition work, users only need to specify a column value or expression, as well as the number of partitions, the default number of partitions is 1 (that is, no partitions). + +

The example in the figure above shows that the value of column b takes the number of years / partitions, and then goes to the remainder. For example, if the number of partitions is 4, then the remainder can only be 0, 1, 2, 3, automatically put the remainder of 0 on the p0 partition, the remainder of 1 on the p1 partition and so on. Mysql automatically creates p0, p1, p2, and p3 partitions, and their names are p0, p1, p2, and p3.

Look up all the data in a specified partition in a table:

Query the details of each partition of the bdqn.employess3 table from the partitions table in the information_schema library:

4. Key partition

Function: very similar to hash partition, except that hash partition is partitioned by user-defined functions. Key uses functions provided by the mysql database for partitioning. NDB cluster uses the md5 function for partitioning and the internal hash function for other storage engines mysql.

Example:

Summary: in the four partitions of range, list, hash, and key, the condition of the partition must be an integer. If it is not an integer, it needs to be converted to an integer by a function.

5. Columns partition

Note: the mysql5.5 version begins to support columns partitions, which can be regarded as the evolution of range and list partitions. Columns partitions can be partitioned using non-integer data directly.

Columns partitions support the following data types:

All × ×, such as INT SMALLINTTINYINT 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.

When partitioning, put different partitions into different storage locations:

Before ① creates the table, create the storage directory in advance and authorize it to mysql:

② create table

Note: when using inodb, mysql's default storage engine, you only need to specify data directory, because the data and indexes of inodb are in one file. However, when you specify engine=myisam when you create a table, you need to specify both datadirectory and index directory to modify the storage location of the partition.

Summary: dividing all the data of a table into different directories (directories on different disks) can improve the performance of mysql O, improve the magnetic read and write ability, and make several disks work at the same time.

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.