MySQL 单表十亿级别数据量设计和优化

需求分析

每天定时4000千万条数据入表（采用load data infile 加载文件入表），保存6个月数据，估算单表总数据量70亿左右。表中有18个字段。每天定时建立两个分区。查询需用到开始时间startTime和结束时间stopTime字段。

设计思路

1.数据库设计和表创建时就要考虑性能

• 选择合适的数据类型

1. 使用可存下数据的最小的数据类型，整型 < date,time < char,varchar < blob
2. 使用简单的数据类型，整型比字符处理开销更小，因为字符串的比较更复杂。如，int类型存储时间类型，bigint类型转ip函数
3. 使用合理的字段属性长度，固定长度的表会更快。使用enum、char而不是varchar
4. 尽可能使用not null定义字段
5. 尽量少用text，非用不可最好分表

• 选择合适的索引列

1. 查询频繁的列，在where，group by，order by，on从句中出现的列
2. where条件中<，<=，=，>，>=，between，in，以及like 字符串+通配符（%）出现的列
3. 长度小的列，索引字段越小越好，因为数据库的存储单位是页，一页中能存下的数据越多越好

• 分区

1. 优点：
   （1）可以让单表存储更多的数据
   （2）分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作
   （3）部分查询能够从查询条件确定只落在少数分区上，速度会很快
   （4） 可以备份和恢复单个分区
2. 缺点
   （1）一个表最多只能有1024个分区
   （2）如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
   （3）分区表无法使用外键约束
   （4）NULL值会使分区过滤无效

表结构设计

CREATE TABLE `cdnUserRateRecord` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `bossUserID` varchar(20) DEFAULT NULL COMMENT '主账号',
  `userID` varchar(48) DEFAULT NULL COMMENT '子账号',
  `citycode` varchar(9) DEFAULT NULL COMMENT '地市',
  `areacode` varchar(9) DEFAULT NULL COMMENT '区县',
  `startTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '开始时间',
  `stopTime` bigint(20) NOT NULL DEFAULT '0' COMMENT '结束时间',
  `businessType` varchar(2) DEFAULT NULL COMMENT '业务类型',
  `programID` varchar(128) DEFAULT NULL COMMENT '内容',     
  `programName` varchar(128) DEFAULT NULL COMMENT '内容名称',  
  `contentSources` varchar(2) DEFAULT NULL COMMENT '内容访问来源',   
  `sequenceType` varchar(1) DEFAULT NULL COMMENT '序列号类型',     
  `sequenceNumber` varchar(128) DEFAULT NULL COMMENT '序列号',    
  `terminalIP` varchar(128) DEFAULT NULL COMMENT '终端IP',
  `flow` int(10) DEFAULT '0' COMMENT '用户使用流量',
  `cpid` varchar(20) DEFAULT NULL COMMENT '发起播放的cp的代码',
  `productid` varchar(20) DEFAULT NULL COMMENT '内容归属产品代码',
  `serviceTime` varchar(20) DEFAULT NULL COMMENT '厂商服务时长/秒',
  `cdnmanufacturer` int(2) DEFAULT NULL COMMENT 'cdn 服务厂商',
  PRIMARY KEY (`id`,`startTime`,`stopTime`),   
  KEY `bossUserIDs` (`bossUserID`,`startTime`,`stopTime`),
  KEY `userIDs` (`userID`,`startTime`,`stopTime`),
  KEY `businessType` (`businessType`,`startTime`,`stopTime`),
  KEY `programNames` (`programName`,`startTime`,`stopTime`),
  KEY `cdnmanufacturer` (`cdnmanufacturer`,`startTime`,`stopTime`)
)DEFAULT CHARSET=utf8 COMMENT='cdn用户收视纪录表'
PARTITION BY RANGE (stopTime)
(PARTITION p2020032700 VALUES LESS THAN (1585242000) ENGINE = MyISAM,
 PARTITION p2020032701 VALUES LESS THAN (1585245600) ENGINE = MyISAM,
 PARTITION p2020032702 VALUES LESS THAN (1585249200) ENGINE = MyISAM,
 PARTITION p2020032703 VALUES LESS THAN (1585252800) ENGINE = MyISAM,
 PARTITION p2020032704 VALUES LESS THAN (1585256400) ENGINE = MyISAM,
 PARTITION p2020032705 VALUES LESS THAN (1585260000) ENGINE = MyISAM,
 PARTITION p2020032706 VALUES LESS THAN (1585263600) ENGINE = MyISAM,
 PARTITION p2020032707 VALUES LESS THAN (1585267200) ENGINE = MyISAM,
 PARTITION p2020032708 VALUES LESS THAN (1585270800) ENGINE = MyISAM,
 PARTITION p2020032709 VALUES LESS THAN (1585274400) ENGINE = MyISAM,
 PARTITION p2020032710 VALUES LESS THAN (1585278000) ENGINE = MyISAM,
 PARTITION p2020032711 VALUES LESS THAN (1585281600) ENGINE = MyISAM,
 PARTITION p2020032712 VALUES LESS THAN (1585285200) ENGINE = MyISAM,
 PARTITION p2020032713 VALUES LESS THAN (1585288800) ENGINE = MyISAM,
 PARTITION p2020032714 VALUES LESS THAN (1585292400) ENGINE = MyISAM,
 PARTITION p2020032715 VALUES LESS THAN (1585296000) ENGINE = MyISAM,
 PARTITION p2020032716 VALUES LESS THAN (1585299600) ENGINE = MyISAM,
 PARTITION p2020032717 VALUES LESS THAN (1585303200) ENGINE = MyISAM,
 PARTITION p2020032718 VALUES LESS THAN (1585306800) ENGINE = MyISAM,
 PARTITION p2020032719 VALUES LESS THAN (1585310400) ENGINE = MyISAM,
 PARTITION p2020032720 VALUES LESS THAN (1585314000) ENGINE = MyISAM,
 PARTITION p2020032721 VALUES LESS THAN (1585317600) ENGINE = MyISAM,
 PARTITION p2020032722 VALUES LESS THAN (1585321200) ENGINE = MyISAM,
 PARTITION p2020032723 VALUES LESS THAN (1585324800) ENGINE = MyISAM,
 PARTITION p2020032800 VALUES LESS THAN (1585328400) ENGINE = MyISAM,
 PARTITION p2020032801 VALUES LESS THAN (1585332000) ENGINE = MyISAM,
 PARTITION p2020032802 VALUES LESS THAN (1585335600) ENGINE = MyISAM,
 PARTITION p2020032803 VALUES LESS THAN (1585339200) ENGINE = MyISAM,
 PARTITION p2020032804 VALUES LESS THAN (1585342800) ENGINE = MyISAM,
 PARTITION p2020032805 VALUES LESS THAN (1585346400) ENGINE = MyISAM);

SQL优化

查看表中总数据量，大概19亿条

查看分区

1. 避免扫描全表，查询索引列，limit限制返回条数

用时0.6秒，