mysql索引组织结构_MySQL中创建及优化索引组织结构的思路-CSDN博客

本文链接：https://blog.csdn.net/weixin_34443284/article/details/114350963

通过一个实际生产环境中的数据存取需求，分析如何设计此存储结构，如何操纵存储的数据，以及如何使操作的成本或代价更低，系统开销最小。同时，让更多初学者明白数据存储的表上索引是如何一个思路组织起来的，希望起到一个参考模板的价值作用。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

数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点，特别解释下，建议学到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`()

BEGINDECLAREiv_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$$