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

The concept of partition and table in MySql

Sub-table: decompose a large table 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 machines. When app reads and writes, it gets the corresponding table name according to the pre-defined rules, and then manipulates it.

Commonly used algorithms: hash or remainder (modular), etc.

The benefits of sub-table: reduce the burden of the database and shorten the query time

Type of split table: ① vertical split: refers to the split of data table columns, splitting a table with more columns into multiple tables

② horizontal split refers to the split of data table rows, splitting the data of one table into multiple tables for storage.

Sub-table method: ① mysql cluster

② anticipates tables with large amounts of data and frequently accessed, and divides them into several tables

③ uses merge storage engine to realize sub-table

Cases of sub-tables:

1. Create libraries, tables, data

Use: select * from member; query table

two。 Divide the member into two tables, tb_member1,tb_member2.

Tb_member1

Tb_member2

Create a master table

Use descending order to view the structure of the b _ membertable

3. Divide the data into two tables

Mysql > insert into tb_member1 (id,name,sex) select id,name,sex from member where id%2=0

Mysql > insert into tb_member2 (id,name,sex) select id,name,sex from member where id%2=1

Check the main table: mysql > select * from tb_member;. At this time, the summary table is just a shell, the number of access.

According to the occurrence in a child table.

Mysql > select * from tb_member where id=3; query conditional statements based on id is faster

4. View the file table: each child table has its own independent related table file, while the main table is only a shell and there is no complete related table file.

The concept of zoning

Partitioning: partitions and subtables are similar 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. App still operates on table names when reading and writing, and db automatically organizes the partitioned data.

The main form of partitioning: ① horizontal partitioning (Horizontal Partitioning): this form of partitioning partitions the rows of the 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 (Vertical Partitioning) 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 the corresponding rows of the columns.

Technical support for partitioning: before ① 5.6, use this parameter to check whether partitions are supported when the configuration is in progress.

Mysql > SHOW VARIABLES LIKE'% partition%'

After ② has been adopted, it will be checked in the following manner

Mysql > show plugins

Zoning case

1. Create libraries, tables, range partition tables

Insert 15 pieces of data

View the files that store database tables

Calculate how many rows are based on id values

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

Query data from a partition

When a partition is deleted, all data in that partition is also deleted.

New partition

Partition merging

Check the storage database table file again

View merged partitions

Case study: performance testing of unpartitioned tables and partitioned tables

1. Create a database and an unpartitioned table

two。 Create a partition table and split it by the year of the date

3. Insert 100000 test statements

4. Insert data into a table

The query shows that the execution time of the partitioned table is much less than that of the unpartitioned table.

Analyze the execution through the explain statement

The explain statement shows the number of records to be processed by the SQL query, which shows that the partitioned table has significantly fewer records scanned than the unpartitioned table.

There is no difference between creating an index and not creating an index. 1 restart the mysql service after creating an index without creating an index.

Type of partition table

1.RANGE partition: assigns multiple rows to a partition based on column values that belong to a given contiguous interval. These intervals should be contiguous and cannot overlap each other and are defined using the VALUES LESS THAN operator.

Case study:

Create a maxvalue partition, and all records that are not within the specified range will be stored in the same partition as maxvalue.

2.LIST partitions: similar to partitioning by RANGE, except that LIST partitions are selected based on column values matching a value in a set of discrete values.

LIST partitions are implemented by using "PARTITION BY LIST (expr)", where "expr" is an expression based on a column value and returns an integer value, and then defines each partition as "VALUES IN (value_list)", where "value_list" is a comma-separated list of integers.

Case study:

Query statement

3.HASH partitioning: this mode allows DBA to compute the Hash Key of one or more columns of the table and finally partition the data regions corresponding to different values of the Hash code.

The purpose of hash partition is to distribute the data evenly among the predefined partitions to ensure that the amount of data in each partition is roughly the same. In RANGE and LIST partitions, you must specify which partition a given column value or set of column values should be stored in; in HASH partitions, MYSQL does this automatically, specifying a column value or expression that the user wants, and specifying the number of partitions that the partitioned table will be divided into.

The partition function page of hash needs to return an integer value. The value in the partitions clause is a non-negative integer. Without the partitions clause, the default is 1.

The record is placed in partition p2. Because insert 2010-04-01 into table t_hash, then

MOD (YEAR ('2010-04-01'), 4) = 2

4.key partition: key partition is similar to hash partition, except that hash partition is partitioned by user-defined functions, key partition uses functions provided by mysql database, NDB cluster uses MD5 function to partition, and internal hash function is used for other storage engines mysql.

In the above four partitions of RANGE, LIST, HASH and KEY, the condition of the partition must be × × ×. If it is not × × ×, it needs to be converted to × × × by function.

5.columns partitions: mysql-5.5 began to support COLUMNS partitions, which can be regarded as the evolution of RANGE and LIST partitions. COLUMNS partitions can be partitioned directly using non-× × data. COLUMNS partitions support the following data types:

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

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.