通过一个实际生产环境中的数据存取需求,分析如何设计此存储结构,如何操纵存储的数据,以及如何使操作的成本或代价更低,系统开销最小。同时,让更多初学者明白数据存储的表上索引是如何一个思路组织起来的,希望起到一个参考模板的价值作用。51Testing软件测试网GE};X
])adD0TY(^*_b^0
测试用例为B2C领域,一张用于存储用户选购物品而生成的产品订单信息表,不过去掉一些其他字段,以便用于测试,其表中的数据项也不特别描述,字段意思见表:.X.kl,UIB0
USE`test`;
DROPTABLEIFEXISTS`test`.`goods_order`;
CREATETABLE`goods_order`(
`order_id`INTUNSIGNEDNOTNULLCOMMENT'订单单号',
`goods_id`INTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'商品款号',
`order_type`TINYINTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'订单类型',
`order_status`TINYINTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'订单状态',
`color_id`SMALLINTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'颜色id',
`size_id`SMALLINTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'尺寸id',
`goods_number` MEDIUMINT UNSIGNEDNOTNULLDEFAULT'0'COMMENT'数量',
`depot_id`INTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'仓库id',
`packet_id`INTUNSIGNEDNOTNULLDEFAULT'0'COMMENT'储位code',
`gmt_create`TIMESTAMPNOTNULLDEFAULT'0000-00-00 00:00:00'COMMENT'添加时间',
`gmt_modify`TIMESTAMPNOTNULLDEFAULT'0000-00-00 00:00:00'COMMENT'更新时间',
PRIMARYKEY(order_id,`goods_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1CHARACTERSET'utf8'COLLATE'utf8_general_ci';
其中,主键信息:PRIMARY KEY(order_id,`goods_id`),为何主键索引索引字段的顺序为:order_id,`goods_id`,而不是: `goods_id`, order_id呢?原因很简单,goods_id在订单信息表中的重复率会比order_id高,也即order_id的筛选率更高,可以减少扫描索引记录个数,从而达到更高的效率,同时,下面即将会列出的SQL也告诉我们,有部分SQL语句的WHERE字句中只出现order_id字段,为此更加坚定我们必须把字段:order_id作为联合主键索引的头部,`goods_id`为联合主键索引的尾部。+lq.Z aJ}"E!U;v7i0
数据存储表设计的小结:'O9h dV hd[0
设计用于存储数据的表结构,首先要知道有哪些数据项,也即行内常说的数据流,以及各个数据项的属性,比如存储的数据类型、值域范围及长度、数据完整性等要求,从而确定数据项的属性定义。存储的数据项信息确定之后,至少进行如下三步分析:51Testing软件测试网XIah*Xr`OQ
● 首先,确定哪些数据项或组合,可以作为记录的唯一性标志;51Testing软件测试网5[9D_\z'RBW l
● 其次,要确定对数据记录有哪些操作,每个操作的频率如何,对网站等类型应用,还需要区分前台操作和后台操作,也即分外部用户的操作,还是内部用户的操作;2?JMSc%~fE F0
● 最后,对作为数据记录操作的条件部分的数据项,分析其数据项的筛选率如何,也即数据项不同值占总数据记录数的比例关心,比例越接近1则是筛选率越好,以及各个值得分布率;^d&T
c-oy&FQ0
综上所述,再让数据修改性操作优先级别高于只读性操作,就可以创建一个满足要求且性能较好的索引组织结构。51Testing软件测试网c+iv
F\
数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点,特别解释下,建议学到BCNF范式为止,1NF、2NF、3NF和BCNF之间的差别,各自规避的问题、存在的缺陷都要一清二楚,但是在真实的工作环境中,不要任何存取设计都想向范式靠,用一句佛语准确点表达:空即是色,色即是空。qWA.Gm`7b a0
■ 用于生成测试数据的存储过程代码51Testing软件测试网KL6]\.q x
创建索引,就离不开表存储的真实数据,为此编写一个存储过程近可能模拟真实生产环境中的数据,同时也方便大家使用此存储过程,在自己的测试环境中,真实感受验证,(Bb5i"p9yo4op@'A0
存储过程代码:51Testing软件测试网]4Rnp_y&W4R[
DELIMITER $$
DROPPROCEDUREIFEXISTS`usp_make_data` $$
CREATEPROCEDURE`usp_make_data`()
BEGIN
DECLAREiv_goods_idINTUNSIGNEDDEFAULT0;
DECLAREiv_depot_idINTUNSIGNEDDEFAULT0;
DECLAREiv_packet_idINTUNSIGNEDDEFAULT0;
SETiv_goods_id=5000;
SETiv_depot_id=10;
SETiv_packet_id=20;
WHILEiv_goods_id>0
DO
STARTTRANSACTION;
WHILEiv_depot_id>0
DO
WHILEiv_packet_id>0
DO
INSERTINTOgoods_order(order_id,goods_id,order_type,order_status,color_id,size_id,goods_number,depot_id,packet_id,gmt_create,gmt_modify)
VALUES(SUBSTRING(RAND(),3,8),iv_goods_id,SUBSTRING(RAND(),3,1),SUBSTRING(RAND(),5,1)%2,SUBSTRING(RAND(),3,3),SUBSTRING(RAND(),4,3),SUBSTRING(RAND(),5,2),
iv_depot_id,SUBSTRING(RAND(),4,2)*iv_packet_id,DATE_ADD(NOW(),INTERVAL-SUBSTRING(RAND(),2,3)DAY),DATE_ADD(NOW(),INTERVAL-SUBSTRING(RAND(),3,2)DAY)
);
SETiv_packet_id=iv_packet_id-1;
ENDWHILE;
SETiv_packet_id=20;
SETiv_depot_id=iv_depot_id-1;
ENDWHILE;
COMMIT;
SETiv_depot_id=10;
SETiv_goods_id=iv_goods_id-1;
ENDWHILE;
END$$
DELIMITER ;
■ 业务逻辑描述51Testing软件测试网.us*WWv'OY4M)w
● 非注册用户,或网站的注册用户不登陆,都能可选购买物品,生成订单号对应的用户UID为系统默认的;5p9}7z"^"^4N0
● 订单与用户UID关联、描述等信息,存储其它的表中,通过订单号的模式关联;N+j6n|i:n;iMV1Y0
● 用户的订单信息,在未付款之前都可以再修改,付款之后则无法修改;&n(z"SL$J}0
● 已经付费的订单信息,自动发送到物流部门,进行后续工序的操作。处理完毕之后,会更新订单中涉及物品的存储位置信息;$ah$T4KBMtz0
● 定期读取部分数据到数据仓库分析系统,用于统计分析;\0q)c0lJ5qf/i+e;_Y0
● 个人订单查询,前后台都有;QM1L#URO1c&th0p[0
● 购物记录查询显示;Y8{$YX'|^'\"hv0
■ 根据业务规则描述需要使用操纵数据的SQL语句]Ur,N/Qk/kApO:A0P0
我来说两句
显示全部
内容
昵称
验证
提交评论