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

总结：

文章中也分析了为何联合主键索引的顺序为：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量也是一个办法，此次就不分析了，有需要的朋友可以去玩玩。