现有一个库存表,约6亿数据
CREATE TABLE `stock` (
`id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT ,==主键自增列
`mid` int(11) UNSIGNED NOT NULL DEFAULT 0 ,==会员ID编号
`model` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==型号
`brand` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==品牌
`package` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==封装
`lot` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==批号
`amount` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==数量
`price` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==价格
`description` varchar(800) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==说明
`type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ,==类型>>普通=0|优势=1|现货=2
`top` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ==推荐>>已推荐=1|0=未推荐
`addtime` int(11) UNSIGNED NOT NULL DEFAULT 0 ,==更新时间
PRIMARY KEY (`id`),
INDEX `model` (`model`) USING BTREE ,
INDEX `mid` (`mid`) USING BTREE ,
INDEX `top,type,addtime` (`type`, `top`, `addtime`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=9677138
ROW_FORMAT=COMPACT
;
表格结构如上,现在规划每个会员20万条数据,3000个会员,总数据量约6个亿
条件查询字段:mid,model,brand,package,lot,type,top
排序字段:top desc,type desc,addtime desc
请教:该表应该如何合理分区及如何合理建立索引?
CREATE TABLE `stock` (
`id` bigint(11) UNSIGNED NOT NULL AUTO_INCREMENT ,==主键自增列
`mid` int(11) UNSIGNED NOT NULL DEFAULT 0 ,==会员ID编号
`model` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==型号
`brand` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==品牌
`package` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==封装
`lot` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==批号
`amount` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==数量
`price` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==价格
`description` varchar(800) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,==说明
`type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ,==类型>>普通=0|优势=1|现货=2
`top` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ==推荐>>已推荐=1|0=未推荐
`addtime` int(11) UNSIGNED NOT NULL DEFAULT 0 ,==更新时间
PRIMARY KEY (`id`),
INDEX `model` (`model`) USING BTREE ,
INDEX `mid` (`mid`) USING BTREE ,
INDEX `top,type,addtime` (`type`, `top`, `addtime`) USING BTREE
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=9677138
ROW_FORMAT=COMPACT
;
表格结构如上,现在规划每个会员20万条数据,3000个会员,总数据量约6个亿
条件查询字段:mid,model,brand,package,lot,type,top
排序字段:top desc,type desc,addtime desc
请教:该表应该如何合理分区及如何合理建立索引?
`top` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 ==推荐>>已推荐=1|0=未推荐,这两个字段的数据值很好分类,比较松散,所以适合建立索引。另外在些sql语句的时候,where语句顺序与索引顺序一致,order 语句顺序也要一致,并且索引排序跟order语句的排序一致。