MySQL索引在兑换码管理系统实际场景的应用

顽皮猫m
619 阅读4分钟

本文已参与「新人创作礼」活动，一起开启掘金创作之路。

Mysql是作为关系型数据库很常用的工具，关于讲解MySQL的书籍，我个人比较推荐小孩子的《MySQL是怎样运行的》，这本书讲解的挺详细。这篇文章不是专门讲原理的，而是在实际场景中的运营案例。

这里用一个公司管理京东卡、兑换券这样的一个管理系统来举例。

首先假设有这样一张简单的表，是专门用来存储京东卡的

CREATE TABLE `virtual_code_table` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '虚拟码,自增主键',
  `code` varchar(32) NOT NULL DEFAULT '' COMMENT '卡号',
  `code_key` varchar(32) NOT NULL DEFAULT '' COMMENT '卡密',
  `state` int(11) NOT NULL DEFAULT '0' COMMENT '0-待分配;1-分配中;2-可发放;3-已发放;4-已导出',
  `version` int(11) NOT NULL DEFAULT '0' COMMENT '该虚拟码导出的次数',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_code_version` (`code_key`,`code`,`version`),
  KEY `idx_state` (`state`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='卡号/卡密表';

这里有几种场景约定:

  • 1.京东卡在系统中不能有重复的记录。 (可能后台操作人员失误，导致重复导入多条记录)
  • 2.京东卡通过Excel文件刚导入系统内属于初始化(待分配)状态。 (state=0)
  • 3.京东卡如果需要使用，需要后台经过申请审批(state=1)，审批通过后才能使用(state=2)。如果审批驳回，则变成初始化状态(state=0)。
  • 4.审批通过的京东卡(state=2)，才能够发放给用户使用。发放给用户后，状态变为已发放(state=3)。
  • 5.如果京东卡申请多了，某次活动过后还剩下多余的京东卡，则管理员可以通过“导出释放”，将这些可发放的京东卡状态转化为导出状态(state=4)，且生成一个Excel文件。
  • 6.导出后的京东卡可以再次导入到管理系统中去，并且之前导出的记录也要保留。(后台要保留state=4的那条记录)

表的变化过程如下图:

image.png

首先为了满足第1点 库中的京东卡不能有重复，首先想到的就是利用一个唯一索引的限制来达到目的。所以表中有一个uniq前缀的索引，索引的前面2个字段是京东卡的卡号和卡密。

但是有一个问题来了，由于系统中多余的京东卡是可以导出的(eg：活动结束后财务要核算开销、对账，或者公司后续安排不确定需要先将这批审批通过待发放的京东卡收回),并且要能够看到哪些是导出了的(state=4)。当管理员把导出的京东卡回收掉，等过个几个月又有新的活动要使用虚拟码的时候，再次将这些京东卡导入系统，此时如果唯一索引没有version字段,那么将会导致这样的京东卡无法成功上传到系统。

所以这里的唯一索引加入了version字段，version是怎么来的呢？可以这样设置version字段的逻辑，第一步：先找到表中state=4的京东卡中verson最大的那个值,取名叫做lastMaxVerson；第二步:将本次要导出的一批京东卡（where state=2）兑换码的state设置为4的同时，version设置为lastMaxVerson+1(这里version的含义就表示整个后台导出了N次)。

条件2、3、4、5都是关于state状态的转化 由于表的体量还是较大的，所以为了保证系统的稳定运行，提高数据库执行update的效率，这里要添加一个idx_state索引。因为执行update的时候，首先代码中肯定不会准确到"where id = xxx"这样去写，Innodb无法使用“唯一性搜索”，转而采用“匹配模式”去确定扫描区间。（如何知道innodb的扫描区间是采用唯一性搜索还是匹配模式呢？可以利用explain语句，其中返回的type列就是表示查询的方式）。如果不加这个索引，mysql默认的隔离级别是Repeatable Read,这种隔离级别下，对于锁定读语句来说，先会扫描区间内的所有行记录都会加上读锁(并且是next-key 锁)，这样会把表中大量的行进行锁定，此时如果有一些京东卡正在给用户发放或者正在进行审批的操作，那么这个导出动作就会对系统运营造成比较大的影响。(这里具体原理可以看我上面推荐的“小孩子4919”的《MySql是怎么样运行的》)

第六点由于version的存在，所以也就得到的满足。这就是mysql索引在实际场景中的一些使用案例。

avatar
文章
阅读
粉丝