测试用例为B2C领域,一张用于存储用户选购物品而生成的产品订单信息表,不过去掉一些其他字段,以便用于测试,其表中的数据项也不特别描述,字段意思见表
class="dp-sql">
- 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`为联合主键索引的尾部。
数据存储表设计的小结:
设计用于存储数据的表结构,首先要知道有哪些数据项,也即行内常说的数据流,以及各个数据项的属性,比如存储的数据类型、值域范围及长度、数据完整性等要求,从而确定数据项的属性定义。存储的数据项信息确定之后,至少进行如下三步分析:
综上所述,再让数据修改性操作优先级别高于只读性操作,就可以创建一个满足要求且性能较好的索引组织结构。
数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点,特别解释下,建议学到BCNF范式为止,1NF、2NF、3NF和BCNF之间的差别,各自规避的问题、存在的缺陷都要一清二楚,但是在真实的工作环境中,不要任何存取设计都想向范式靠,用一句佛语准确点表达:空即是色,色即是空。
创建索引,就离不开表存储的真实数据,为此编写一个存储过程近可能模拟真实生产环境中的数据,同时也方便大家使用此存储过程,在自己的测试环境中,真实感受验证,
存储过程代码:
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `usp_make_data` $$
- CREATE PROCEDURE `usp_make_data`()
- BEGIN
- DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;
- DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;
- DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;
- SET iv_goods_id=5000;
- SET iv_depot_id=10;
- SET iv_packet_id=20;
- WHILE iv_goods_id>0
- DO
- START TRANSACTION;
- WHILE iv_depot_id>0
- DO
- WHILE iv_packet_id>0
- DO
- INSERT INTO goods_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)
- );
- SET iv_packet_id=iv_packet_id-1;
- END WHILE;
- SET iv_packet_id=20;
- SET iv_depot_id=iv_depot_id-1;
- END WHILE ;
- COMMIT;
- SET iv_depot_id=10;
- SET iv_goods_id=iv_goods_id-1;
- END WHILE ;
- END $$
- DELIMITER ;
- EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986;
- SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040…) ORDER BY gmt_modify DESC;
- UPDATE goods_order SET gmt_modify=NOW(),…. WHERE `order_id`=40717328 AND goods_id=4248;
- SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
- SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
- SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1
- SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50;
- SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’;
我们再分析上述列出来的SQL ,分为2类,一类是读操作的SQL (备注:SELECT操作),另外一类为修改性操作(备注:UPDATE、DELETE操作),分别如下:
SELECT 的WHERE子句、GROUP BY子、ORDER BY 子句和HAVING 子句中,出现的字段:
(1). order_id
(2). order_id+gmt_modify
(3). depot_id+gmt_modify
(4). depot_id+packet_id+gmt_modify
(5). goods_id+order_status+order_type
(6). goods_id+order_status+order_type+gmt_modify
(7). gmt_modify
修改性操作的WHERE子句中出现的条件字段:
(8). order_id+ goods_id
我们已经存在主键索引:PRIMARY KEY(order_id,`goods_id`),另外考虑到此表数据的操作以SELECT和INSERT为主,UPDATE的SQL 量其次,再根据上述SQL 语句,为此我们可以初步确定需要创建的索引:
- ALTER TABLE goods_order
- ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify),
- ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify);
总结:
文章中也分析了为何联合主键索引的顺序为:order_id,`goods_id`,再补充下作为主键的联合索引的字段属性的其他特性:字段值写入之后不变化、字段值长度短且最好为数值类型;
对于编号SQL :(8),每天按更新日期读取一次数据的操作,以采用全表扫描的方式实现,牺牲其数据读取的性能,以减少更新字段修改日期的值而带来的索引维护开销;
对于编号SQL :(4)、(5),考虑到每次都是读取最新的50条记录,以及读取的数据基本上可肯定为热数据,为此不得不牺牲其中一条SQL 的数据读取性能,而少创建一个联合索引,从而减少维护索引字段的IO量;
对于编号SQL :(6)、(7),创建的联合索引,需要特别注意联合索引:idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify)中的字段顺序,其中:
最后,再梳理一下从需求到设计存储结构,再到编写SQL 和创建索引结构,我们应该做的步骤:
备注:
本想再用测试环境结合业务的方式,跑一套模拟测试脚本程序,让大家更加直观地看到不同索引组织情况下,相同的SQL 操作及频率,数据库服务器的处理能力和负载变化及对比信息,可惜唯一的服务器无法使用了,只好放弃。对于分析相同的SQL ,走不通索引,其需要的逻辑IO和物理IO量也是一个办法,此次就不分析了,有需要的朋友可以去玩玩,另外建议初学者一定要好好阅读下mysql 手册上的相关章节内容:7.2.6. Index Merge Optimization。
原文链接:http://arlxy.iteye.com/blog/1191029