MySQL中创建及优化索引组织结构的思路.docxVIP

【导读】 通过一个实际生产环境中的数据存取需求，分析如何设计此存储结构，如何操纵存储的数据， 以及如何使操作的成本或代价更低，系统开销最小。同时，让更多初学者明白数据存储的表 上索引是如何一个思路组织起来的，希望起到一个参考模板的价值作用。 n 测试用例描述 测试用例为 B2C 领域，一张用于存储用户选购物品而生成的产品订单信息表，不过去掉一些其他字段，以便用于测试，其表中的数据项也不特别描述，字段意思见表 USE `test`; DROP TABLE IF EXISTS `test`.`goods_order`; CREATE TABLE `goods_order`( `order_id` INT UNSIGNED NOT NULL COMMENT ‘订单单号’, `goods_id` INT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘商品款号’, `order_type` TINYINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘订单类型’, `order_status` TINYINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘订单状态’, `color_id` SMALLINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘颜色 id’, `size_id` SMALLINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘尺寸 id’, `goods_number` MEDIUMINT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘数量’, `depot_id` INT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘仓库 id’, `packet_id` INT UNSIGNED NOT NULL DEFAULT ’0′ COMMENT ‘储位 code’, `gmt_create` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘添加时间’, `gmt_modify` TIMESTAMP NOT NULL DEFAULT ’0000-00-00 00:00:00′ COMMENT ‘更新时间’, PRIMARY KEY(order_id,`goods_id`) )ENGINE=InnoDB AUTO_INCREMENT=1 CHARACTER SET‘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`为联合主键索引的尾部。 数据存储表设计的小结： 设计用于存储数据的表结构，首先要知道有哪些数据项，也即行内常说的数据流，以及各个数据项的属性，比如存储的数据类型、值域范围及长度、数据完整性等要求，从而确定数据项的属性定义。存储的数据项信息确定之后，至少进行如下三步分析： l 首先，确定哪些数据项或组合，可以作为记录的唯一性标志； l 其次，要确定对数据记录有哪些操作，每个操作的频率如何，对网站等类型应用，还需要区分前台操作和后台操作，也即分外部用户的操作，还是内部用户的操作； l 最后，对作为数据记录操作的条件部分的数据项，分析其数据项的筛选率如何，也即数据项不同值占总数据记录数的比例关心，比例越接近 1 则是筛选率越好，以及各个值得分布率； 综上所述，再让数据修改性操作优先级别高于只读性操作，就可以创建一个满足要求且性能较好的索引组织结构。 数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点，特别解释下，建议学到BCNF 范式为止，1NF、2NF、3NF 和 BCNF 之间的差别，各 自规避的问题、存在的缺陷都要一清二楚，但是在真实的工作环境中，不要任何存取设计都想向范式靠，用一句佛语准确点表达：空即是色，色即是空。 n 用于生成测试数据的存储过程代码 创建索引，就离不开表存