MySQL Sub-table rules

Author:skate
Time:2013/05/14

MySQL Sub-table guidelines

In the massive use of MySQL, data volume, high access, in order to improve performance needs table processing, introduction of MySQL sub-table standards, follow-up will continue to supplement

Environment:
Business Type: OLTP
Hardware:
Cpu:8cpu 2.4GHZ
mem:48g
Disk: RAID5 6xsas

What tables need to be split: measure whether a table needs to be split based on the size of the table, the number of rows in the table, and the access characteristics

A. The split criteria are:
1. The size of the table is greater than 2G or the number of rows is greater than 1000w, to single table primary key and other simple forms of access to data, this time need to table
2. The size of the table is greater than 2G or the number of rows is greater than 500W, in two tables jion, small range query (result set small 100 rows) and other forms of access to data, this time need to table
3. The size of the table is greater than 2G or the number of rows is greater than 200w, with multiple table joins, range queries, order By,group by, high frequency and other complex forms of access to data, especially DML, this time requires a table
4. The table field contains text, such as large fields, varchar (500) or more, rarely used character fields are split into parent-child tables, such a table can be used in conjunction with the above
5. Data has time-out characteristics, need to do data table file processing

As long as you reach any of the above standards, you need to do a table processing

Two. Method of table:
1. Hot and cold data sub-table: For small traffic, cold data rarely used
1.1 Single-table fields are many, and the frequently used and non-frequent use of the integer field or large number of fields are split into two tables
1.2 Table data has time expiration, split outdated data into the history table or by Time gradient
2. Horizontal sub-table: Applicable to large traffic
2.1 Such as hash cut table or other based on a number of the cut table, the advantage is convenient data distribution, the disadvantage is no longer expandable
2.2 By the primary key ID increment sub-table, such as each 100w ID a sub-table, the advantage is easy to expand, the disadvantage is the pressure is uneven
2.3 By date table, such as daily, monthly, yearly a sub-table, the advantage is easy to expand, the disadvantage is the pressure is uneven

Description
1. How the volume of the table is estimated
CREATE TABLE ' Td_skate ' (
' Valid ' BIGINT (a) not NULL auto_increment COMMENT ' value ID ',
' PropertyId ' BIGINT () null DEFAULT null COMMENT ' property ID ',
' Text ' VARCHAR (+) null DEFAULT null,
' Entext ' VARCHAR (+) null DEFAULT null,
' Picurl ' VARCHAR ($) NULL default NULL COMMENT ' property value description picture, save picture relative address ',
' Isother ' BIGINT () null default NULL COMMENT ' is the other value, 0 No 1 is ',
' Createtime ' DATETIME null DEFAULT null COMMENT ' creation time ',
' CreateUser ' BIGINT () null DEFAULT null COMMENT ' Create user ',
' lastmodify ' DATETIME null DEFAULT null COMMENT ' Last modified time ',
' Updatetimeuser ' BIGINT (+) null DEFAULT null COMMENT ' Last modified person ',
' Deletetime ' DATETIME null DEFAULT null COMMENT ' Delete Time ',
' DeleteUser ' BIGINT () null DEFAULT null COMMENT ' delete person ',
' Description ' VARCHAR (4000) null DEFAULT NULL COMMENT ' product description ',
' Isdelete ' INT (one) NULL DEFAULT ' 0 ',
PRIMARY KEY (' valid '),
INDEX ' fk_td_prodline_attrval_td_prodline_attr ' (' PropertyId '),
CONSTRAINT ' fk_td_prodline_attrval_td_prodline_attr ' FOREIGN KEY (' PropertyId ') REFERENCES ' td_prodline_attr ' (' PropertyId ')
)
Collate= ' Utf8_general_ci '
Engine=innodb
auto_increment=2491650;

Add all the fields in the table by the number of bytes, multiply the estimated number of rows is the size of the table, such as the table above, the estimated 1000W, then his volume is
(8+8+400+400+200+8+8+8+8+8+8+8+4000+8) x10000000=50.8g, you can see this table design is very unreasonable, can be modified as follows:

int replaces bigint
Timestamp alternate datetime
Status bit isdelete replaced with tinyint
According to the business characteristics, can we put varchar (4000) in a Word table

Optimized table size: (4+4+400+400+200+4+4+4+4+4+4+4+1) x10000000=10.37g, if you want to further improve performance, you need to remove the foreign key, sub-table, to ensure that the single table under 2G.
If you need to view the description information, the primary key association to view the child table, only the effective child table information will be scanned, the performance will be greatly improved.

2. The estimated number of rows in the table is simple, depending on the business characteristics, the amount of visits and other estimates

There are several types of the MySQL integer type:
Type      occupies bytes
tinyint        1
smallint     2
Mediumint 3
int              4
bigint         8
This is how many bytes the storage needs to occupy
tinyint (m), m default is 4;
SMALLINT (m), m default is 6;
Mediumint (m), m default is 9;
INT (m), m default is 11;
BIGINT (m), m defaults to 20.

Reprint Address: http://blog.csdn.net/wyzxg/article/details/8930658

MySQL Sub-table rule (GO)