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

Large tables are often encountered in daily development or maintenance. the so-called large tables refer to tables that store millions or even tens of millions of records. Such tables are too large, resulting in database query and insert time is too long, poor performance, if federated queries are involved, the performance will be even worse. The purpose of table partition and table partition is to reduce the burden of the database and improve the efficiency of the database. Generally speaking, it is to improve the efficiency of table addition, deletion, modification and query.

I. what is a sub-table:

Sub-table is to decompose a large table into multiple physical tables with independent storage space according to certain rules, which can be called sub-tables. Each table corresponds to three files, MYD data file, .MYI index file, .frm table structure file. These subtables can be distributed on the same disk or on different machines.

1. At present, there are two ways to optimize massive data according to sub-table technology:

1. Vertical segmentation: divide a table with a large amount of data into multiple tables according to the attributes or frequent usage of a field, or divide the database of a business system into different instances.

2. Split horizontally: put data rows into multiple independent tables according to the values of one or more columns. Horizontal split table can be integrated by multiple low-configuration hosts to achieve high performance.

3. Their advantages and disadvantages:

Horizontal advantages: the splitting rules are abstract, the JION operation can basically be done in the database, there is no performance bottleneck of single table big data and high concurrency, less application modification, and improve the stability and load capacity of the system.

Disadvantages: sharding transaction consistency is difficult to solve, before MyCAT2.0 MySQL5.7, data is still weak XA. It is difficult to expand the data for many times, and the maintenance amount is large, and the performance of Kuaku JOIN is poor.

Vertical advantages: clear business after split, clear split rules, easy integration or expansion between systems, and simple database maintenance.

Disadvantages: part of the business can not use JOIN, can only be solved through the interface, providing the system can be complex, due to different limitations of each business, there are performance bottlenecks, it is not easy to expand data and improve performance.

Transaction processing is complex, and after vertical segmentation, tables are distributed to different databases according to the classification of business, which will lead to the large size of some business tables and the bottleneck of reading, writing and storage in a single database.

2. What is zoning

Partition is to divide the data of a table into more than N areas. after partitioning, it is still a table on the surface, but the data is hashed to multiple locations according to the amount of data, combined with the actual business.

1. Zoning methods are as follows:

A, range partition: mainly used for time column partition, value range, row data is put into the partition based on the column value of a given continuous partition. For example, the table of sales category can store sales records according to the years.

B, list partition: facing discrete values, the value to be specified in the partition, when inserting specified data into the specified partition table, such as specifying certain values in a specific partition.

C, key partitioning: similar to partitioning by HASH, except that KEY partitions only support calculating one or more columns, and the MySQL server provides its own hash function. One or more columns must contain integer values.

D, hash partition: a partition selected based on the return value of a user-defined expression that uses the column values of the rows to be inserted into the table. This function can contain any valid expression in MySQL that produces a non-negative integer value.

3. Zoning examples:

Create redundant format

If there is a primary key or unique index in the table, the partitioned column must be part of the unique index create table T11 (col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1,col2)) partition by hash (col1) partitions 4 Hash create table T121 (col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col1,col2)) partition by hash (year (col2)) partitions 4 Primary key create table T31 (col1 int not null, col2 date not null, col3 int not null, col4 int not null, primary key (col1,col2)) partition by hash (col1) partitions 8 Primary key and index exist simultaneously: create table T41 (col1 int not null, col2 date not null, col3 int not null, col4 int not null, unique key (col4), primary key (col1)) partition by hash (col1) partitions 5 A unique index can be null. A partitioned column does not need to be a partitioned column create table t223332 (col1 int null,col2 date null,col3 int null,col4 int null) partition by hash (col3) partitions 4 as long as it is a part of the unique index. Without a primary key or unique index, you can specify any column partition column create table t223332 (col1 int null,col2 date null,col3 int null,col4 int null,key (col4)) partition by hash (col3) partitions 4 Rang partition: mainly used for time column partitions, such as sales class tables. Sales records can be defined according to the partition over the years: row data is put into the partition based on the column values of a given consecutive partition, and id is the primary key create table T3 (id int) engine=innodb partition by range (id) (partition p0 values less than (10), partition p1 values less than (20)). View the data file t3.frm t.par insert into t select 9; insert into t select 10; insert into t select 15; check the partition status use information_schema select * from PARITIONS where table_schema=''test and table_name='t3'\ G; partition_method represents the partition type. Error table has no partition for value 40 alter table t add partition (partition p2 values less than maxalue) when the partition condition is not met. It is mainly used for time column partitions, such as sales tables, which can be used to store sales records (year (date)) create table sales (money int not null,date datetime) engine=innodb partition by range (year (date)) (partition p2008 values less than (2009), partition p2009 values less than (2010), partition p2010 values less than (2011)) according to the time of the year. Insert into sales select 100, 2008-02-01; insert into sales select 100, 2008-01-02; insert into sales select 100, 2009-03-01; insert into sales select 100, 2010-01-01 List partition: for discrete values, the value to be specified by the partition. When inserting specified data into the specified partition table, create table t_list (an int,b int) engine=innodb partition by list (b) (partition p0 values in, partition p1 values in); insert into T4 select 1 select 3; insert into T4 select 1, 5; insert into T4 select 1, 8) Insert into T4 select 1,6; table has no partition for values10 it is worth noting that the LIST partition does not have a definition like "VALUES LESS THAN MAXVALUE" that includes other values. Any values to be matched must be found in the values list. In addition to combining LIST partitions with RANGE partitions to form a composite subpartition, it is also possible to combine with HASH and KEY partitions to generate composite subpartitions. Note: innodb myisam distinguishes between the values defined by partitions encountered in the process of inserting multi-row data with insert. The processing of myisam and innodb storage engines is completely different. Myisam is not successful, and the previous success values will enter innodb into the table as long as one of them is not successful. Create table t (an int,b int) engine=myisam partition by list (b) (partition p0 values in, partition p1 values in) Insert into t values (1 from 2), (2) HY000), (6 from 19), (5 from 3); insert into t values (1), (2), (6), (5); ERROR 1526 (HY000): Table has no partition for value 19select * from t +-+-+ | a | b | +-+-+ | 1 | 2 | 2 | 4 | +-+-+ 2 rows in set (0.00 sec) create table tt (an int,b int) engine=innodb partition by list (b) (partition p0 values in, partition p1 values in) Insert into tt values (1 select 2), (2 HY000), (6 select 19), (5 from tt 3); insert into tt values (1 select 2), (2) select 3); Table has no partition for value 1526 (HY000): Table has no partition for value 19 select * from tt Empty set (0.00 sec) hash partition: partition according to the return value of the user's expression. The return value cannot be negative. Add a partition by hash (expr) sentence to the create table statement, where expr is an expression that returns an integer. It can simply add a partitions num clause after the column name of the number field type mysql integer. Num is a nonnegative number create table t_hash (an int,b date) engine=innodb partition by hash (YEAR (b)) partitions 4 Insert into t_hash select 1, create table tt_hash (an int,b date) engine=innodb partition by hash (a) partitions 4 # # columns partition is different from other partitions, and the partition condition must be integer If it is not an integer, you should also need to convert it to an integer columns, which is evolutionarily supported by the rang list partition. The other date types do not support the string type char varcha binary varbinary, and those of the blok and text types do not support create table tt_column_range (an int,b int) engine=innodb partition by range columns (partition b) (partition p0 values less than (010)). Partition p1 values less than (1010), partition p2 values less than (20), partition p3 values less than (30), partition p4 values less than (40) Subpartition: MYSQL database allows further hask or key subpartitions on rang and list partitions, create table ts (an int,b date) engine=innodb partition by range (year (b)) subpartition by hash (to_days (b)) subpartitions 3 (partition p0 values less than (2013), partition p0 values less than (2014), partition p1 values less than (2015) partition p2 values less than maxvalue) Create table ts (an int,b datepartition by range (year (b)) subpartition by hash (to_days (b)) (partition p0 values less than (2014) (subpartition s0 subpartition S1) partition p1 values less than (2015) (subpartition S2 subpartition 3) partition p2 values less than maxvalue (subpartition s4subpartition S5)) each subpartition must contain the name of the partition. The name of the subpartition is unique. The null value in the partition create table T3 (id int) engine=innodb partition by range (id) (partition p0 values less than (10), partition p1 values less than (20);); the null value is placed on the leftmost.

Summary: to understand the basic principles and methods of sub-table, it is also necessary to achieve the optimal business architecture according to the combination of business.

If there is something wrong, welcome to correct it!

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.