技术开发 频道

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

  ■ 业务逻辑描述

  ● 非注册用户,或网站的注册用户不登陆,都能可选购买物品,生成订单号对应的用户UID为系统默认的;

  ● 订单与用户UID关联、描述等信息,存储其它的表中,通过订单号的模式关联;

  ● 用户的订单信息,在未付款之前都可以再修改,付款之后则无法修改;

  ● 已经付费的订单信息,自动发送到物流部门,进行后续工序的操作。处理完毕之后,会更新订单中涉及物品的存储位置信息;

  ● 定期读取部分数据到数据仓库分析系统,用于统计分析;

  ● 个人订单查询,前后台都有;

  ● 购物记录查询显示;

  ■ 根据业务规则描述需要使用操纵数据的SQL语句

(1).    EXPLAIN SELECT * FROM goods_order WHERE `order_id`=40918986;
(2).    SELECT * FROM goods_order WHERE `order_id` IN (40918986,40717328,30923040...) ORDER BY gmt_modify DESC;
(3).    UPDATE goods_order SET gmt_modify=NOW(),.... WHERE  `order_id`=40717328 AND goods_id=4248;
(4).    SELECT COUNT(*) FROM goods_order WHERE depot_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
(5).    SELECT * FROM goods_order WHERE depot_id=6 AND packet_id=0 ORDER BY gmt_modify DESC LIMIT 0,50;
(6).    SELECT COUNT(*) FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1
(7).    SELECT * FROM goods_order WHERE goods_id=4248 AND order_status=0 AND order_type=1 ORDER BY gmt_modify DESC LIMIT 0,50;
(8).    SELECT * FROM goods_order WHERE gmt_modify>=’ 2011-04-06’;

   8条SQL语句按触发其执行的用户分类:

  ● 前台用户点击触发的操作而会执行的SQL语句为:(1)、(2)、(3);

  ● 后台内部用户点击触发的操作而会执行的SQL语句为:(1)、(2)、(3)、(4)、(5)、(6)、(7);

  ● 后台系统自动定期执行:(4)、(5)、(6)、(7),工作时间正常情况每隔15分钟执行一次,以检查是否有已付款而没有准备货物的订单、是否有收款而未发货的订单等;

  ● 统计分析系统定期导出数据而执行的SQL语句为:(8),频率为每24小时一次;

  我们再分析上述列出来的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)中的字段顺序,其中:

  ● goods_id字段的筛选率高于order_type,order_status,另外gmt_modify字段只出现在ORDER BY子句中,为此只有让goods_id字段作为联合索引的头部,以提高索引的筛选率,从而提高索引的效率,减少逻辑或物理的读。

  ● order_status字段只有0或1两种值,而order_type有多种,以及根据SQL语句,必须order_type出现在联合中的位置要比order_status靠近头部;

  ● gmt_modify字段出现在ORDER BY子句中,为此必须放到联合索引字段的最后;

  最后,再梳理一下从需求到设计存储结构,再到编写SQL和创建索引结构,我们应该做的步骤:

  ● 整理业务产生的数据流,读取数据的方式;

  ● 整理清楚数据流中的每个数据项属性信息;

  ● 分析业务指标,推测需要存储数据的规模(备注:一定要以多少GB作为容量单位);

  ● 选择可能用于支持业务的硬件设备和数据库架构;

  ● 把所有可能操纵数据的条件和操作类型,都整理清楚;

  ● 分析操纵数据条件字段各自的数据筛选率;

  ● 权衡各个SQL的性能和IO量,也即类似于哪个操作权重高一些,那些操作权重适当低一些;

  ● 创建索引组织结构;

  ● 收集测试和生产环境的反馈信息,优化索引组织结构;

  备注:

  本想再用测试环境结合业务的方式,跑一套模拟测试脚本程序,让大家更加直观地看到不同索引组织情况下,相同的SQL操作及频率,数据库服务器的处理能力和负载变化及对比信息,可惜唯一的服务器无法使用了,只好放弃。对于分析相同的SQL,走不通索引,其需要的逻辑IO和物理IO量也是一个办法,此次就不分析了,有需要的朋友可以去玩玩。

0
相关文章