First, sub table

1. Vertical segmentation: that is to divide a table into fields, each table guarantees the same primary key. Generally speaking, the commonly used fields and large fields are divided into tables.

                      

Advantages: Compared with no sub-table, it improves the query speed and reduces the memory used for query results.

Disadvantage: It does not solve the problem of a large number of records. For a single table, performance declines rapidly with the increase of records.

2. Horizontal segmentation: Horizontal segmentation is the most commonly used in enterprises. Horizontal segmentation is that large tables are divided into many sub-tables according to records:

                     

Horizontal scoring rules are completely customized, with the following reference designs:

1. Hash, self-increasing id module: Hash a field to determine the creation of several tables, and according to the hash results into different tables;

2. According to the time: according to the business can be divided into days, months and years;

3. Fixed number of records per table: Generally, the table is disassembled according to the self-increasing ID. When the data row of a table reaches the specified number, it is automatically saved to the next table. For example, it stipulates that only 1-1000 records can be stored in a table.

4. Migrate old data to a history table: for example, log table, which only queries data within 3 months, and for records over 3 months, migrate them to a history sub-table.

2. Zoning:

Partitioning is to store the files and indexes of a data table separately in different physical files. The partition types supported by mysql include Range, List, Hash, Key, among which Range is commonly used:

1. RANGE partitioning: Allocate multiple rows to partitions based on column values belonging to a given continuous interval.

create table t_range( 
　　   id int(11), 
　　   money int(11) unsigned not null, 
　　   date datetime 
　　)partition by range(year(date))( 
　　partition p2007 values less than (2008), 
　　partition p2008 values less than (2009), 
　　partition p2009 values less than (2010) 
　　partition p2010 values less than maxvalue  #MAXVALUE represents the largest possible integer value
　　)；

RANGE Partitioning is particularly useful in the following situations:
1),When "old" data on a partition needs to be deleted,Just delete the partition. If you use the partitioning scheme given in the latest example above, you just need to use it simply. ALTER TABLE employees DROP PARTITION p0;"
　　//To delete all rows corresponding to all employees who stopped working before 1991. For tables with a large number of rows, this is better than running a table such as "DELETE FROM employees WHERE YEAR (separated)<== 1990;"
　　//Such a DELETE query is much more effective. 
2),You want to use a column that contains date or time values, or values that grow from some other series.
3),Frequently run queries that directly depend on the columns used to split tables.
　　//For example, when a query like "SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;" is executed,
　　MySQL It can be quickly determined that only partitions are available p2 Scanning is required because it is impossible for the remaining partitions to contain a partition that meets this requirement WHERE Any record of clauses

2. LIST partitioning: Similar to partitioning by RANGE, the difference is that LIST partitioning is based on column values matching a value in a set of discrete values to select.

create table t_list( 
　　a int(11), 
　　b int(11) 
　　)(partition by list (b) 
　　partition p0 values in (1,3,5,7,9), 
　　partition p1 values in (2,4,6,8,0) 
);
LIST There is no partition like this“ VALUES LESS THAN MAXVALUE"Such a definition includes other values. Any value to be matched must be found in the list of values.

3. 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 expression that is valid in MySQL that generates nonnegative integer values.

CREATE TABLE user (  
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     username VARCHAR(30) NOT NULL DEFAULT '',  
     email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY HASH (id) PARTITIONS 4 (  
     PARTITION p0 ,  
     PARTITION p1,  
     PARTITION p2,
     PARTITION p3  
);

4. KEY partition: Similar to partitioning by HASH, KEY partition only supports computing one or more columns, and MySQL server provides its own hash function. There must be one or more columns containing integer values.

CREATE TABLE user (  
     id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,  
     name VARCHAR(30) NOT NULL DEFAULT '',  
     email VARCHAR(30) NOT NULL DEFAULT ''  
)  
PARTITION BY KEY (id) PARTITIONS 4 (  
     PARTITION p0,  
     PARTITION p1,  
     PARTITION p2,  
     PARTITION p3
);   

5. Zoning restrictions:

1. Primary keys or unique indexes must contain partition fields, such as primary key (id,username), but innoDB's large build performance is poor.

2. In many cases, the use of partitions will not use the primary key, otherwise it may affect performance.

3. Partitions can only be partitioned by fields of type int or expressions returning type int, usually using functions such as year or to_days (mysql 5.6 begins to release restrictions).

4. Each table has a maximum of 1024 partitions, and multi-partitions consume a lot of memory.

5. Partitioned tables do not support foreign keys, and related logical constraints need to be implemented by programs.

6. After partitioning, index failure may occur, and partitioning feasibility needs to be verified.

Since then:

https://www.cnblogs.com/phpshen/p/6198375.html

https://www.cnblogs.com/myvic/p/7711498.html