Mysql分表准则

今天药忘吃喽~ 2022-02-18 23:25 232阅读 0赞

author：skate  
time：2013/05/14

**Mysql分表准则**

在大量使用mysql时，数据量大、高访问时，为了提高性能需要分表处理，简介下mysql分表的标准，后续会继续补充

环境：  
业务类型：OLTP  
硬件：  
cpu：8cpu 2.4GHZ  
mem：48G  
磁盘：raid5 6×sas

什么样的表需要拆分：根据表的体积、表的行数、访问特点来衡量表是否需要拆分

一.拆分标准是：  
  1.表的体积大于2G或行数大于1000w,以单表主键等简单形式访问数据，这个时候需要分表  
  2.表的体积大于2G或行数大于500W,以两表jion，小范围查询（结果集小100行）等形式访问数据，这个时候需要分表  
  3.表的体积大于2G或行数大于200w,以多表join，范围查询，order by，group by，高频率等复杂形式访问数据，尤其DML，这个时候需要分表  
  4.表的字段中含有text等大字段的、varchar(500)以上的、很少使用的字符型字段拆分成父子表,这种分表可以和以上联合使用  
  5.数据有时间过期特性的，需要做数据分表归档处理

**只要达到上面任何一个标准，都需要做分表处理**

二.分表方法：  
  1.冷热数据分表：适用小访问量，冷数据很少使用  
     1.1 单表字段很多，把频繁使用整型字段的和非频繁使用的字符型字段或大字段拆到两个表中  
     1.2 表数据具有时间过期性，把过期数据拆分到历史表里或者按时间梯度分表  
  2.横向分表：适用大访问量  
     2.1 如哈希等分切表或其他基于对某数字取余的切表，优点是方便数据分布，缺点是无法再扩展  
     2.2 按主键id递增分表，比如每100w个id一个分表，优点是方便扩展，缺点是压力不均  
     2.3 按日期分表，比如每天、每月、每年一个分表，优点是方便扩展，缺点是压力不均  
       
说明  
1.表的体积如何预估  
CREATE TABLE \`td\_skate\` (  
      \`valid\` BIGINT(20) NOT NULL AUTO\_INCREMENT COMMENT '值id',  
      \`propertyid\` BIGINT(20) NULL DEFAULT NULL COMMENT '属性id',  
      \`text\` VARCHAR(400) NULL DEFAULT NULL,  
      \`entext\` VARCHAR(400) NULL DEFAULT NULL,  
      \`picurl\` VARCHAR(200) NULL DEFAULT NULL COMMENT '属性值说明图片，保存图片相对地址',  
      \`isother\` BIGINT(20) NULL DEFAULT NULL COMMENT '是否是other值， 0  否  1  是',  
      \`createtime\` DATETIME NULL DEFAULT NULL COMMENT '创建时间',  
      \`createuser\` BIGINT(20) NULL DEFAULT NULL COMMENT '创建用户',  
      \`lastmodify\` DATETIME NULL DEFAULT NULL COMMENT '最后修改时间',  
      \`updatetimeuser\` BIGINT(20) NULL DEFAULT NULL COMMENT '最后修改人',  
      \`deletetime\` DATETIME NULL DEFAULT NULL COMMENT '删除时间',  
      \`deleteuser\` BIGINT(20) NULL DEFAULT NULL COMMENT '删除人',  
      \`description\` VARCHAR(4000) NULL DEFAULT NULL COMMENT '产品描述',  
      \`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\` FOREIGN KEY (\`propertyid\`) REFERENCES \`td\_prodline\_attr\` (\`propertyid\`)  
)  
COLLATE='utf8\_general\_ci'  
ENGINE=InnoDB  
AUTO\_INCREMENT=2491650;

把表的所有字段占用字节数相加，再乘以预估行数就是表的体积，比如上面的表，预估有1000W，那他的体积是  
（8+8+400+400+200+8+8+8+8+8+8+8+4000+8）×10000000=50.8G，可以看到这个表设计非常不合理，可以修改如下：

int替代bigint  
timestamp替代datetime  
状态位isdelete用tinyint替代  
根据业务特点看能否把varchar(4000)放到一个字表中

优化后表大小：（4+4+400+400+200+4+4+4+4+4+4+4+1）×10000000=10.37G，如果要进一步提升性能，需要删除外键，分表，保证单表在2G以下。  
如果需要查看description信息，通过主键关联查看子表，只会扫描有效的子表信息， 性能将会提升非常大。

2.表的行数预估就很简单，根据业务特点，访问量等预估

\-------end------