Mysql table sharding principles

Mysql table sharding guidelines when using Mysql in large quantities and with high data access, to improve performance, table sharding is required. The following section describes the mysql table sharding standards and will continue to supplement the environment in the future: business type: OLTP hardware: cpu: 8 cpu 2.4 GHZmem: 48g Disk: RAID 5 6 × what table does sas need to split: determine whether to split a table based on its volume, number of rows, and access characteristics. split criteria: 1. the table is larger than 2 GB or the number of rows is greater than 1000 w. Data is accessed in simple forms such as a single table primary key. In this case, table sharding is required. the table is larger than 2 GB or the number of rows is greater than 500 W. Data is accessed in the form of two tables jion and small-scale queries (100 rows smaller than the result set). In this case, table sharding is required. the table is larger than 2 GB or the number of rows is greater than 200 w. Data is accessed in complex forms such as multi-table join, range query, order by, group by, and high frequency, especially DML, this requires table sharding. the table fields contain text and other large fields, varchar (500) and above, and rarely used plain words. Segment split into Parent and Child tables, which can be used together with the above 5. if the data has the time-out feature, you need to archive the data into sub-tables as long as it meets any of the above standards. table sharding method: 1. cold/hot data table sharding: applicable to small traffic volumes. Cold data rarely uses 1.1 million single table fields, remove frequently-used and non-frequently-used integer fields or large fields into two tables. The data in Table 1.2 is time-out, split expired data into history tables or split tables by time gradient. horizontal table sharding: Applicable to sharding tables with a large access volume of 2.1, such as hash, or other table sharding based on a certain number. This facilitates data distribution, the disadvantage is that it is no longer possible to expand the table sharding by primary key id. For example, one table sharding by 2.2 IDS is convenient to expand. The disadvantage is that the pressure is not balanced. 2.3 Table sharding by date, for example, a table Shard is created every day, every month, and every year. The advantage is convenient scalability, and the disadvantage is the description of unbalanced pressure. how can I estimate the volume of a TABLE? CREATE TABLE 'td _ skate' ('valid' BIGINT (20) NOT NULL AUTO_IN Crement comment 'value id', 'propertyid' BIGINT (20) null default null comment' property id', 'text' VARCHAR (400) null default null, 'text' VARCHAR (400) null default null, 'picurl' VARCHAR (200) null default null comment' attribute value indicates the image, save the relative address of the image ', 'isother 'bigint (20) null default null comment 'is the other value, 0 No 1 Yes ', 'createtime' datetime null default null comment 'creation time', 'createuser' BIGINT (20) NULL DEFAULT NULL COMM ENT 'create user', 'lastmodify' datetime null default null comment' last modified time', 'updatetimeuser' BIGINT (20) null default null comment' last modified ', 'deletetime' datetime null default null comment' deletion time', 'deleteuser' BIGINT (20) null default null comment' delete', 'description' VARCHAR (4000) null default null comment 'product description', 'isdelete' INT (11) null default '0', primary key ('valid'), INDEX 'fk _ td_prodline_attrval_td _ Prodline_attr '('propertyid'), CONSTRAINT 'fk _ td_prodline_attrval_td_prodline_attr 'eign KEY ('propertyid') REFERENCES 'td _ prodline_attr' ('propertyid ')) COLLATE = 'utf8 _ general_ci 'ENGINE = InnoDBAUTO_INCREMENT = 2491650; add the number of bytes occupied by all fields in the table, and multiply the estimated number of rows by the table size. For example, in the preceding table, it is estimated that there will be 1000 million, so his volume is (8 + 8 + 400 + 400 + 200 + 8 + 8 + 8 + 8 + 8 + 8 + 8 + 4000 + 8) * 10000000 = 50.8 GB. We can see that the table design is unreasonable and can be modified as follows: replace biginttimestamp with int instead of datetime status bit isdelete and replace tinyint with industry Depends on whether the varchar (4000) optimize the table size in a Word Table: (4 + 4 + 400 + 400 + 200 + 4 + 4 + 4 + 4 + 4 + 4 + 4 + 4 + 1) * 10000000 = 10.37 GB. To further improve performance, you need to delete Foreign keys and table shards to ensure that a single table is less than 2 GB. If you want to view the description information and view the sub-table through primary key Association, only valid sub-table information is scanned, and the performance will be greatly improved. 2. estimate the number of rows in a table is very simple. estimate the number of rows according to business characteristics and access volume ------- end ------