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

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

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

1.测试用例描述

测试用例为B2C领域,一张用于存储用户选购物品而生成的产品订单信息表,不过去掉一些其他字段,以便用于测试,其表中的数据项也不特别描述,字段意思见表:

  1. USE `test`;  
  2. DROP TABLE IF EXISTS `test`.`goods_order`;  
  3. CREATE TABLE `goods_order`(  
  4. `order_id`        INT UNSIGNED      NOT NULL             COMMENT '订单单号',  
  5. `goods_id`        INT UNSIGNED      NOT NULL DEFAULT '0' COMMENT '商品款号',  
  6. `order_type`      TINYINT UNSIGNED  NOT NULL DEFAULT '0' COMMENT '订单类型',  
  7. `order_status`    TINYINT UNSIGNED  NOT NULL DEFAULT '0' COMMENT '订单状态',  
  8. `color_id`        SMALLINT  UNSIGNED NOT NULL DEFAULT '0' COMMENT '颜色id',  
  9. `size_id`         SMALLINT  UNSIGNED NOT NULL DEFAULT '0' COMMENT '尺寸id',  
  10. `goods_number`    MEDIUMINT  UNSIGNED NOT NULL DEFAULT '0' COMMENT '数量',  
  11. `depot_id`        INT UNSIGNED  NOT NULL DEFAULT '0' COMMENT '仓库id',  
  12. `packet_id`       INT UNSIGNED  NOT NULL DEFAULT '0' COMMENT '储位code',  
  13. `gmt_create`      TIMESTAMP     NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '添加时间',  
  14. `gmt_modify`      TIMESTAMP     NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间',  
  15. PRIMARY KEY(order_id,`goods_id`)  
  16. )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`为联合主键索引的尾部。

数据存储表设计的小结:

设计用于存储数据的表结构,首先要知道有哪些数据项,也即行内常说的数据流,以及各个数据项的属性,比如存储的数据类型、值域范围及长度、数据完整性等要求,从而确定数据项的属性定义。存储的数据项信息确定之后,至少进行如下三步分析:

● 首先,确定哪些数据项或组合,可以作为记录的唯一性标志;

● 其次,要确定对数据记录有哪些操作,每个操作的频率如何,对网站等类型应用,还需要区分前台操作和后台操作,也即分外部用户的操作,还是内部用户的操作;

● 最后,对作为数据记录操作的条件部分的数据项,分析其数据项的筛选率如何,也即数据项不同值占总数据记录数的比例关心,比例越接近1则是筛选率越好,以及各个值得分布率;

综上所述,再让数据修改性操作优先级别高于只读性操作,就可以创建一个满足要求且性能较好的索引组织结构。

数据的存取设计,就涉及一块非常重要的知识: 关系数据库的基础知识和关系数据理论的范式。对于范式的知识点,特别解释下,建议学到BCNF范式为止,1NF、2NF、3NF和BCNF之间的差别,各自规避的问题、存在的缺陷都要一清二楚,但是在真实的工作环境中,不要任何存取设计都想向范式靠,用一句佛语准确点表达:空即是色,色即是空。

#p#

2.用于生成测试数据的存储过程代码

创建索引,就离不开表存储的真实数据,为此编写一个存储过程近可能模拟真实生产环境中的数据,同时也方便大家使用此存储过程,在自己的测试环境中,真实感受验证,

存储过程代码:

  1. DELIMITER $$  
  2. DROP PROCEDURE IF EXISTS `usp_make_data` $$  
  3. CREATE PROCEDURE `usp_make_data`()  
  4. BEGIN 
  5.     DECLARE iv_goods_id INT UNSIGNED DEFAULT 0;  
  6.     DECLARE iv_depot_id INT UNSIGNED DEFAULT 0;  
  7.     DECLARE iv_packet_id INT UNSIGNED DEFAULT 0;  
  8.       
  9.     SET iv_goods_id=5000;  
  10.     SET iv_depot_id=10;  
  11.     SET iv_packet_id=20;  
  12.       
  13.     WHILE iv_goods_id>0   
  14.     DO  
  15.      START  TRANSACTION;  
  16.       WHILE iv_depot_id>0   
  17.       DO  
  18.         WHILE iv_packet_id>0   
  19.         DO  
  20.           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)  
  21.           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),  
  22.                  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)  
  23.                 );  
  24.           SET iv_packet_id=iv_packet_id-1;    
  25.         END WHILE;          
  26.         SET iv_packet_id=20;  
  27.         SET iv_depot_id=iv_depot_id-1;    
  28.       END WHILE ;   
  29.        
  30.       COMMIT;  
  31.       SET iv_depot_id=10;  
  32.       SET iv_goods_id=iv_goods_id-1;  
  33.     END WHILE ;      
  34. END $$  
  35. DELIMITER ; 

3.业务逻辑描述

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

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

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

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

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

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

● 购物记录查询显示;

#p#

4.根据业务规则描述需要使用操纵数据的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语句,为此我们可以初步确定需要创建的索引:

  1. ALTER TABLE goods_order   
  2. ADD INDEX idx_goodsID_orderType_orderStatus_gmtmodify(goods_id,order_type,order_status,gmt_modify),  
  3. ADD INDEX idx_depotID_packetID_gmtmodify(depot_id,packet_id,gmt_modify); 

#p#

总结:

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

【编辑推荐】

  1. 教你如何利用MySQL学习MongoDB之SQL语法
  2. 教你如何利用MySQL学习MongoDB之数据存储结构
  3. 如何解决PHP+MySQL出现乱码的现象
  4. 教你如何利用MySQL学习MongoDB之安装篇
  5. MySQL配置时提示无法连接到MySQL本地服务器
责任编辑:艾婧 来源: ITPUB
相关推荐

2011-10-13 09:44:49

MySQL

2021-11-09 07:59:50

开发

2020-03-27 15:40:10

MySQL索引数据库

2011-07-11 15:28:19

MySQL索引优化

2011-07-11 15:03:36

MySQL索引数据结构

2023-02-24 16:37:04

MySQL数据查询数据库

2011-07-11 16:05:42

MySQL索引

2010-06-04 11:28:05

MySQL数据库

2010-10-13 10:27:35

MySQL修改表结构

2011-06-14 10:43:44

索引

2023-06-23 15:22:28

JettyJava

2020-10-19 19:45:58

MySQL数据库优化

2017-07-25 12:07:14

MySQL索引SQL

2010-05-21 10:01:11

MySQL数据库

2011-07-11 13:11:54

MySQL索引数据结构

2023-03-27 09:57:00

PostgreSQL并发索引

2018-06-07 08:54:01

MySQL性能优化索引

2023-12-08 09:15:53

Java单表树形结构Tree

2018-08-07 16:17:35

JavaMySQL数据库

2010-05-12 11:14:25

MySQL SQL优化
点赞
收藏

51CTO技术栈公众号