数据库课设的技术细节

前端项目地址：https://github.com/ZWN2001/TrainClientFlutter

后端项目地址：https://github.com/ZWN2001/TrainServerSpringboot

（包括数据库数据构造器代码）

爬虫项目地址：https://github.com/ZWN2001/data_spider_12306

技术栈

后端：springboot+redis+mybatis+druid+rabbit MQ，CQRS

前端：flutter+Hive

总体架构选型

我觉得首先我们要对这个题目有一些基本的认识，这是一个查操作极其频繁、写逻辑极其繁琐的系统，我们的架构导向就是更加高效地完成查操作，同时实现高内聚低耦合的更高维护性的写逻辑。

 

数据源

数据源来自12306官网，通过爬虫爬取了12306官网中热门城市的排列组合之间的所有车次以及这些车次的原子区间，共4376车次，60615个原子区间，基于这些数据进行了座位管理表、原子票价表、原子票务票的数据构造，统一随机生成了10天的服务量，共生成了座位管理表1050204行数据，原子票价表181845行数据，原子票务票1818450行数据（构造时对源数据进行了清洗，所以并不是整倍数关系）。

 

数据库设计的细节

核心聚合根是什么？

网上看大家的思考，汤雪华老师说核心聚合根是车次，我起初不太理解，但越思考就越觉得他说的有道理。车次这个聚合体，大小适中，如果我们追求更大的聚合，比如一辆火车，就会带来混淆，因为一辆火车可能会跑两个线路、三个线路，甚至更多，这时候存储这些线路的经停车站就成了问题，这时候火车与经停情况就变成了间接一对多关系，而且必须由线路来进行标定（这与我的数据库设计有关，见下文第四段）。出票等等业务也会出现类似的问题。而且火车编号加线路号完全就等价于车次编号，而一个火车在不同线路运行时车次编号一定是不同的。

如果使用更小的聚合根，比如车票，那更直接是灾难，数据库随着系统运转将会飞速膨胀。

而使用车次，我们注意到，在我们的业务场景中，我们需要管理的有：车站信息，车次，车次经停，票务，座位管理等等等等一系列数据，而这些其实都可以由车次串联起来。比如车次的运转会经停有限个次序固定的车站，同时票务在处理时可以考虑一个车次的所有票，并且基于这个车次进行更细化的划分，比如车厢，比如车票的分类，对于座位管理，我们以车次为思考中心则更是可以进行数据量上的把控，使得数据的划分既不会因为划分地太大而导致一次查询会查出过多数据，也不会太小而导致需要多次查询，所以使得在查询时所查到的聚合体不会存在冗余数据，更贴合业务需求。

当然，这也分业务模块，对于火车核心系统的运转，核心聚合根当然是车次，试着用车次去串联所有数据，会感到一种整齐的感觉，在ER模型上的反映更是直观，对于其他业务（比如用户信息登录注册等）核心聚合根则是其他的聚合根。

 

查询车次的一个细节

在查询车次时会发现，车次并不都是都是从始发站开始发出的，这其实就牵扯到一个问题，比如我查询从济南到上海的车次，除了会查到所有从济南发车到达上海的车次，还会查到经停济南最后到达上海的所有车次。

首先要注明一点，一个车次是由始发站与终点站标定的（当然还可能有列车编号发车时间等等），但我想强调的是，某个车次的经停站到终点站，或者始发站到某个经停站，并不算一个新的车次，因为显然如果算新的车次的话那就会产生不必要的复杂度（因为完全是可以被优化掉的）。

如果不理解可以看看汤雪华老师的https://www.cnblogs.com/netfocus/p/5187241.html

在设计ER模型时我就在考虑这个问题，后来一想也很简单，我们之所以没有思路是因为我们并没有对车次的运行范围进行约束，也就是说，我们默认经过一个车站的所有车次编号总数是一个极大的数，那么这样的话我们确定经停的车次的时候就需要遍历所有的车次，查看其所有经停站点中有没有当前车站，这显然是无法接受的。

那么我们完全可以对车次的运行范围进行限制，也就是说，我们默认无论车次怎样安排，在某个车站经停以及始发的车次的车次编号数量永远是一定的，而且这个编号的集合的元素总数并不会很大（有点计算机局部性原理那种感觉了）。也就是说，我在车站的实体中添加一个车次编号的多值属性即可。

这样一来我们转化到数据库的关系的时候就会为车站和车次编号建立一张表（多值属性嘛），这样我们一方面解决了判断某车站会运营哪些火车，同时也解决了判断某车次会经停哪些站的问题，两个属性都是主键。（当然了还需要一个值来标定经停车站的顺序）

那么我们怎么判断一个车次在当前车站是始发还是经停呢？也很简单，我们拿到车次编号去查找该车次的始发站看看跟当前车站是否一直即可。

 

用户的一个小细节

很多时候我们上网购物，往往在购物时会将用户购买的商品与用户身份的唯一标识进行绑定以标识是哪个用户购买了哪个商品，但对于票务来说并非如此。

对于票务系统，我们会有更复杂的情况，比如一个用户为多个乘员购票，我们只需要保证每个购票人是唯一的（比如一个身份证号只能买一张票），但并不需要规定一个用户只能在一个车次买一张票。也就是说，用户身份并不参与购票，真正购票使用的应该是乘员的信息，而与用户无关。

也就是说，真正参与到订票系统模块中的用户系统模块元素是用户的乘员，虽然也储存用户id，但真正串联起业务的用户系统模块元素是用户的乘员（以及火车系统模块的车次）。

相应的，一些app购票时会显示曾经用过的乘员信息，对应的有两种方案，一种是直接在前端做缓存，优点是查找的相应速度快，缺点是无法多平台互通，另一种是在数据库存储，与第一种方案优缺点正好相反，速度慢但是可以平台互通。当然也存在第三种方案，即同时使用方案一和二，唯一需要保证的就是数据一致性问题。携程可能使用了最后一种方案，响应快，同时保证了平台统一。

关于票的生命周期

我这样设计一张票：首先，用户订票，相当于给一张票加了锁，此时相当于预定票，还未支付，放在预订票表里，表中的表项设置定时任务，超时则自动取消，计入订单表，状态是超时未支付，规定时间内支付则计入订单表，状态是已支付，此时再进行分配车厢、座号，这样可以尽可能高的提高相应速率，减少对数据库的操作，对于改签也是如此。

 

中转的逻辑设计(业务细粒度把控)

按照常理来讲，中转与直达在业务逻辑上其实是类似的，那么如何对中转的业务进行设计，使得中转与直达在数据库中不需要区分从而能存到一个表里呢？

当然，直接把两种业务的订单分开也不是不行，那基本就意味着两种业务的订单与车票都至少要有各自的一张表，个人认为这种方法虽然省事但是效率未免太过低下，那么就要思考如何控制业务细粒度以将两种类似的业务进行统一。

首先我们应该思考两种业务的相同之处与不同之处，相同之处在于，它们都包含有订单号、用户id，乘员id，发车日期，车次，始发站终点站等等信息，区别在哪里？区别在于中转的业务要有两个车次。那么在存储上讲，我们唯一要考虑的就是，订票时生成的订单号怎样才是最合理的、离我们的业务需求更近的。

我最终的选择是两个车次的订单采用同一个订单号，也就是说，中转尽管相当于买了两个车次的票，但我将其看做一笔订单。首先，这样做似乎更符合我们认知上的常理，虽然这两个车次的票之间没有任何关系而应该进行解耦，但对于订单来说，这是同一乘客的一笔交易。在查询时我们无须额外的存储就能判断出那些订单属于中转，那些订单属于直达，从而在前端的显示上进行区分，这对于查询来说是极大的便利，同时也避免了冗余（存储订单类型），而且这种方式也很好的兼容了两种订单的信息存储。当然这就意味着我们无法采用订单号作为主键，不过我的设计之中也没有采用订单号作为主键，我们应当注意，一个乘员在某一天不能买同一车次的多张票，如果使用订单号作为主键，我们必须进行额外的查询来保证，而其实我们可以使用 发车日期+乘员id+车次编号 作为主键，如果插入时出现多重主键的报错则就说明不符合条件。

 

座位分配表结构与算法分析

车次座位余量存储的表的字段结构是这样的

train_route_id varchar(8)   not null,
depart_date    date         not null,
carriage_id    int          not null,
locate         tinyint      not null,
seat_remain    text         not null,

我们不妨抽象出这样一个模型，对于所有车次，每个车厢都有许多排座位，每排座位有多个位置，这里我们统一默认是每排四个座位，因为这种信息其实可能通过加一张表来记录不同车型车厢中的排布情况，不多赘述。同时我们不妨默认每列有三十排，相当于我们抽象出了一个 $4\times30$ 的矩形，我们针对不同车次不同车厢在不同发车日期的不同位置，可以定位到30个位置（如果是座位全部为空），而且这30个位置在车次的所有原子区间内都是可用的。

顾客在订票时，会选择票的种类（决定了车厢号的取值集合），并且选定自己喜欢的位置，由系统分配车厢号跟座号，并且前端会返回到车次的订票区间，也就是说，我们要在这个订票区间内在其合适的车厢与位置中分配一个坐（当然如果分配不到合适的位置而需要调剂则另说）。

我们采用这样的算法和数据结构去记录与分配：

采用键值对Map进行存储，值就是有多少个座位是处于某个状态，键是对这个状态的标定，由两部分组成，第一部分是可用区间，指这个座位在哪一段区间内还有剩余，第二部分是已分配过的座号，如果是全程，则认为是没分配过，置0。

比如G1车次的1号车厢在2022-09-03这天在1号位置还没分配过，则seat_remain = {'1_12_0':30}，（我们假设其有12个车次原子区间），意味着从1号车站到12号车站的30个座位都没有分配，假如有人买了该位置上区间为(5,9)的一张车票，则给他分配座位，排号为1，此后更新数据库该行为seat_remain = {'1_5_1':1,'9_12_1':1,1_12_0':29}，我们能通过这种方式至多分配30排，同时，我们可以保证分配过的键值对会往前放，这样在遍历时就会在尽量小的区间内比对，即优先重用已经分配过的（而且这样也不需要再去分配排号）。退票时我们也遍历键值对进行比对，如果退票的区间刚好可以和一个或两个键相连通并且座号是一样的，那么就进行合并。

这样可以解决一系列问题，首先是优先使用更碎片化的车次区间，这个上面说过，其次是记住了排号，遍历一遍，选择空余中编号最小的排号即可作为分配的排号，对于已经分配过排号的情况更是可以直接确定排号，第三是不会造成误合并，即解决了这种情况：排号为2的人退了一张(5,9)的票，键值对中恰好有(1,5)的一张票，但排号是1，如果没有做排号比对就直接合并，会导致某个乘客恰好买了一张(1,9)的票，但是但是前半段旅程要坐在第一排，后半段要改到第二排上去坐，即中途还需要换位置，这是不合适的。

 

冗余与效率：范式与反范式

数据库设计的冗余主要有两方面，一方面是订单系统中对于预定票、改签票、完成票的信息冗余，预定票、改签票的冗余主要原因是表需要定期扫描以判定是否超时（比如预订票支付超时），同时相对于处于其他生命周期的票，这三种票被查询的次数要显著高，冗余使得在查询时也可以更快地找到订单信息。另一方面冗余是站序号的冗余，由于存在两个原子化表，一些业务就需要在同一车次的序号区间之间进行操作（比如购票），冗余能够避免表的join操作，因为序号区间不可避免地使用BETWEEN而使得索引失效，再join将会进一步降低效率，（后来觉得还是使用索引会更好一些，冗余未免有点多，而且冗余对于join也非常有效）。

性能优化思路

最左前缀规则

这个比较常见，即保证查询尽量走索引。

explain

查看sql执行计划情况的简单易用的方法

小表驱动大表

小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。

当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表

SELECT info FROM A WHERE id in (SELECT id FROM B)

当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表

SELECT info FROM A WHERE EXISTS (SELECT id FROM B WHERE B.id = A.id)

读写分离

本来最希望实践的是这个思路但是发现只给了一个数据库于是乎就没能做成。

其余优化方式还有很多，我的优化只能说是入门级实践。

 

性能监控

使用druid的一个原因就是简单易用的性能监控，在配置文件中完成配置并在spring security中放通界面即可使用web查看sql执行细节并以此作为性能优化的依据。

 

时间的细节

系统中会设计各种时间，虽然直接使用年月日时分秒会简单粗暴，但是有时候业务并不需要太精确，比如发车日期只需要精确到天，改签、订票的订单日期不需要年月日（12306晚上停服所以不会出现跨天的情况），所以在不同业务场景下使用不同的时间会免去不必要的麻烦（不过也会带来时间格式不一致的一些问题，个人还是偏向于选择合适的时间格式）

Date

名称	解释
显示格式	YYYY-MM-DD
显示范围	1601-01-01 到 9999-01-01
应用场景	当业务需求中只需要精确到天时，可以用这个时间格式
后台取值	@JSONField(format=”yyyy-MM-dd”)

 

DateTime

名称	解释
显示格式	YYYY-MM-DD HH:mm:ss
显示范围	1601-01-01 00:00:00 到 9999-12-31 23:59:59
应用场景	当业务需求中需要精确到秒时，可以用这个时间格式
后台取值	@JSONField(format=”yyyy-MM-dd HH:mm:ss”)

 

TimeStamp

名称	解释
显示格式	YYYY-MM-DD HH:mm:ss
显示范围	1601-01-01 00:00:00 到 9999-12-31 23:59:59
应用场景	当业务需求中需要精确到秒或者毫秒时，或者该系统用于不同时区，可以用这个时间格式
后台取值	@JSONField(format=”yyyy-MM-dd HH:mm:ss:SSS”)（这里只会精确到秒）

 

Time

名称	解释
显示格式	HH:mm:ss
显示范围	00:00:00 到 23:59:59
应用场景	当业务需求中只需要每天的时间，可以用这个时间格式
后台取值	@JSONField(format=”HH:mm”)（这里是只精确到分，数据库存的也就是只精确到分，比如 09:36:00，如果需要精确到秒。需要使用@JSONField(format=”HH:mm:ss”)）

DateTime和TimeStamp都是年月日时分秒，为什么还有这两个类型呢？

1、精度：DateTime和TimeStamp都可以精确到毫秒（我目前使用的5.5的mysql，所以只能精确到秒，据说高版本的可以用TIMESTAMP(3)与 DATETIME(3)精确到毫秒。），但是用的很少，一般精确到毫秒都是用Timestamp，另外关于Timestamp的10位和13位的区别参考另一篇文章java时间戳 10位和13位分别是怎么来的？

2、DateTime和TimeStamp最大的区别只是用的场景不同，如果你的应用是用于不同时区（就是国内和国外同时使用），这时候如果用dateTime就会出现各种各样的问题，但是如果使用TimeStamp就不会出现这种时差的问题。

误区:之前我也也为datetime和TimeStamp是一样的，因为他们在数据库存的时间都是YYYY-MM-DD HH:mm:ss格式的，但是TimeStamp类型存的是10位或者13位的时间戳，不要被表现骗了，使用

select UNIX_TIMESTAMP(TIME) date from table即可获取时间戳里面具体存的什么

 

功能需求

用户：

• 注册、登录
• 查询车次
• 查看车票详情
• 买票，直达、换乘
• 退票
• 改签
• 查看订单

管理员：

• 放票（添加可查的票）
• 调整票价
• 系统管理

 

后端

技术栈选用

springboot，最基本的后端框架，承担主要的服务层开发。

redis，高速键值对存储，一种非关系型数据库，负责订票业务的高响应比与高可用性

myBatis，轻量级持久化框架。

durid：阿里巴巴开源的Durid数据库连接池，主要负责稳定的数据库连接与重用以及性能监控（不是指Apache Druid）。

rabbit MQ：主要负责处理购票订单的高效消息队列，保证订票业务的高响应比。

秒杀抢购系统选型

回到我们最初提到的问题中来：火车票秒杀系统如何在高并发情况下提供正常、稳定的服务呢？

从上面的介绍我们知道用户秒杀流量通过层层的负载均衡，均匀到了不同的服务器上，即使如此，集群中的单机所承受的QPS也是非常高的。如何将单机性能优化到极致呢？要解决这个问题，我们就要想明白一件事： 通常订票系统要处理生成订单、减扣库存、用户支付这三个基本的阶段，我们系统要做的事情是要保证火车票订单不超卖、不少卖，每张售卖的车票都必须支付才有效，还要保证系统承受极高的并发。这三个阶段的先后顺序改怎么分配才更加合理呢?我们来分析一下：

 

下单减库存

当用户并发请求到达服务端时，首先创建订单，然后扣除库存，等待用户支付。这种顺序是我们一般人首先会想到的解决方案，这种情况下也能保证订单不会超卖，因为创建订单之后就会减库存，这是一个原子操作。但是这样也会产生一些问题，第一就是在极限并发情况下，任何一个内存操作的细节都至关影响性能，尤其像创建订单这种逻辑，一般都需要存储到磁盘数据库的，对数据库的压力是可想而知的；第二是如果用户存在恶意下单的情况，只下单不支付这样库存就会变少，会少卖很多订单，虽然服务端可以限制IP和用户的购买订单数量，这也不算是一个好方法。

 

支付减库存

如果等待用户支付了订单在减库存，第一感觉就是不会少卖。但是这是并发架构的大忌，因为在极限并发情况下，用户可能会创建很多订单，当库存减为零的时候很多用户发现抢到的订单支付不了了，这也就是所谓的“超卖”。也不能避免并发操作数据库磁盘IO

 

预扣库存

从上边两种方案的考虑，我们可以得出结论：只要创建订单，就要频繁操作数据库IO。那么有没有一种不需要直接操作数据库IO的方案呢，这就是预扣库存。先扣除了库存，保证不超卖，然后异步生成用户订单，这样响应给用户的速度就会快很多；那么怎么保证不少卖呢？用户拿到了订单，不支付怎么办？我们都知道现在订单都有有效期，比如说用户五分钟内不支付，订单就失效了，订单一旦失效，就会加入新的库存，这也是现在很多网上零售企业保证商品不少卖采用的方案。订单的生成是异步的,一般都会放到MQ、kafka这样的即时消费队列中处理,订单量比较少的情况下，生成订单非常快，用户几乎不用排队。

 

扣库存的艺术

从上面的分析可知，显然预扣库存的方案最合理。我们进一步分析扣库存的细节，这里还有很大的优化空间，库存存在哪里？怎样保证高并发下，正确的扣库存，还能快速的响应用户请求？

在单机低并发情况下，我们实现扣库存通常是这样的:

为了保证扣库存和生成订单的原子性，需要采用事务处理，然后取库存判断、减库存，最后提交事务，整个流程有很多IO，对数据库的操作又是阻塞的。这种方式根本不适合高并发的秒杀系统。

接下来我们对单机扣库存的方案做优化：本地扣库存。我们把一定的库存量分配到本地机器，直接在内存中减库存，然后按照之前的逻辑异步创建订单。改进过之后的单机系统是这样的:

这样就避免了对数据库频繁的IO操作，只在内存中做运算，极大的提高了单机抗并发的能力。但是百万的用户请求量单机是无论如何也抗不住的，虽然nginx处理网络请求使用epoll模型，c10k的问题在业界早已得到了解决。但是linux系统下，一切资源皆文件，网络请求也是这样，大量的文件描述符会使操作系统瞬间失去响应。上面我们提到了nginx的加权均衡策略，我们不妨假设将100W的用户请求量平均均衡到100台服务器上，这样单机所承受的并发量就小了很多。然后我们每台机器本地库存100张火车票，100台服务器上的总库存还是1万，这样保证了库存订单不超卖,下面是我们描述的集群架构:

问题接踵而至，在高并发情况下，现在我们还无法保证系统的高可用，假如这100台服务器上有两三台机器因为扛不住并发的流量或者其他的原因宕机了。那么这些服务器上的订单就卖不出去了，这就造成了订单的少卖。要解决这个问题，我们需要对总订单量做统一的管理，这就是接下来的容错方案。服务器不仅要在本地减库存，另外要远程统一减库存。有了远程统一减库存的操作，我们就可以根据机器负载情况，为每台机器分配一些多余的“buffer库存”用来防止机器中有机器宕机的情况。我们结合下面架构图具体分析一下:

我们采用Redis存储统一库存，因为Redis的性能非常高，号称单机QPS能抗10W的并发。在本地减库存以后，如果本地有订单，我们再去请求redis远程减库存，本地减库存和远程减库存都成功了，才返回给用户抢票成功的提示,这样也能有效的保证订单不会超卖。当机器中有机器宕机时，因为每个机器上有预留的buffer余票，所以宕机机器上的余票依然能够在其他机器上得到弥补，保证了不少卖。buffer余票设置多少合适呢，理论上buffer设置的越多，系统容忍宕机的机器数量就越多，但是buffer设置的太大也会对redis造成一定的影响。虽然redis内存数据库抗并发能力非常高，请求依然会走一次网络IO,其实抢票过程中对redis的请求次数是本地库存和buffer库存的总量，因为当本地库存不足时，系统直接返回用户“已售罄”的信息提示，就不会再走统一扣库存的逻辑，这在一定程度上也避免了巨大的网络请求量把redis压跨，所以buffer值设置多少，需要架构师对系统的负载能力做认真的考量。

链接：https://juejin.cn/post/6844903949632274445
来源：稀土掘金

 

预扣库存的一致性问题

订票流程

 

客户端

技术栈选用

flutter：基本的跨平台客户端开发框架，承担主要客户端开发业务

Hive：高速键值对存储，一种非关系型数据库，负责前端信息存储（高速缓存）

因为客户端跟数据库关系并不大，只是系统逻辑设计的一个呈现，所以不做赘述。

 

总结

优缺点

优点在于，比较完整地复刻了12306客户端的核心逻辑，自行实现了所有业务算法并实现了较为高效、维护性、扩展性较好的系统架构，在一些核心逻辑上进行了学习并有自己的思考，且将其应用于实践，在界面上也进行了较为完美的呈现。

缺点在于，一些功能还不够完善，比如管理端以及维护用户信息等功能，同时在订单与车票两个业务之间耦合度偏高，在后续系统扩展中可能出现问题，同时也没有完整地模拟12306的一些较为核心的业务比如列车调度的时间表以及列车正晚点状态等。

总体来讲课设对于选题有较好的呈现，也存在一些不足，还需再接再厉。

 

老师给的建议

建议是在系统中加入信息统计的模块，收集系统运行时产生的信息。

 

附：

座位分类

$seat\_type$ ( seat_type_id, seat_type_name )

create table seat_type
(
    seat_type_id   tinyint     not null,
    seat_type_name varchar(4)  not null,
    constraint seat_type_pk
        primary key (seat_type_id)
);

 

车站

$station$ ( station_id, station_name, city )

create table station
(
    station_id   char(3)    not null,
    station_name varchar(8) not null,
    city         varchar(8) not null,
    constraint station_pk
        primary key (station_id)
);

 

车次

$train\_route$ ( train_route_id, from_station_id, to_station_id, from_time, elapsed_time_minute, run_plan )

create table train_route
(
    train_route_id      varchar(8) not null,
    from_station_id     char(3)    not null,
    to_station_id       char(3)    not null,
    from_time           time       not null,
    elapsed_time_minute int        not null,
    run_plan            char(31)    not null,
    constraint train_route_pk
        primary key (train_route_id),
    constraint train_route_from_station_id_fk
        foreign key (from_station_id) references station (station_id),
    constraint train_route_to_station_id_fk
        foreign key (to_station_id) references station (station_id)
);

create index train_route_from_station_id_index
    on train_route (from_station_id);
create index train_route_to_station_id_index
    on train_route (to_station_id);

 

车次原子区间

$train\_route\_atom$ ( train_route_id, station_id, station_no, arrive_time, start_time, stopover_time)

create table train_route_atom
(
    train_route_id      varchar(8) not null,
    station_id          char(3)    not null,
    station_no          smallint   not null,
    arrive_time         time       not null,
    start_time          time       not null,
    stopover_time       int        not null,
    constraint train_route_atom_pk
        primary key (train_route_id,station_id),
    constraint train_route_atom_station_id_fk
        foreign key (station_id) references station (station_id)
);

create index train_route_atom_station_no_index
    on train_route_atom (station_no);

 

车厢

$carriage$ ( train_route_id, carriage_id, seat_type_id)

create table carriage
(
    train_route_id varchar(8)     not null,
    carriage_id    int         not null,
    seat_type_id   tinyint     not null,
    
    constraint carriage_pk
        primary key (train_route_id, carriage_id),
    constraint carriage_train_route_id_fk
        foreign key (train_route_id) references train_route (train_route_id),
    constraint carriage_seat_id_fk
        foreign key (seat_type_id) references seat_type (seat_type_id)
);

 

车次票价原子区间

$ticket\_price\_atom$ (train_route_id, station_id, station_no, seat_type_id, price)

create table ticket_price_atom
(
    train_route_id      varchar(8) not null,
    station_id          char(3)    not null,
    #默认是出发站到下一站
    station_no          smallint   not null,
    seat_type_id        tinyint   not null,
    price               smallint   not null,
    constraint ticket_price_atom_pk
        primary key (train_route_id,station_id,seat_type_id),
    constraint ticket_price_atom_station_id_fk
        foreign key (station_id) references station (station_id),
    constraint ticket_price_atom_train_route_id_fk
        foreign key (train_route_id) references train_route (train_route_id),
    constraint ticket_price_atom_seat_type_id_fk
        foreign key (seat_type_id) references seat_type (seat_type_id)
);

create index ticket_price_atom_station_no_index
    on ticket_price_atom (station_no);

 

原子票务

$ticket\_management\_atom$ ( train_route_id, seat_type_id, station_id, ticket_date, station_no remaining_ticket_num )

create table ticket_management_atom
(
    train_route_id        varchar(8) not null,
    seat_type_id          tinyint    not null,
    station_id            char(3)    not null,
    station_no            smallint   not null,
    ticket_date           date       not null,
    remaining_ticket_num  int        not null,
    constraint ticket_management_pk
        primary key (train_route_id, seat_type_id, station_id, ticket_date),
    constraint ticket_management_train_route_id_fk
        foreign key (train_route_id) references train_route (train_route_id),
    constraint ticket_management_seat_type_id_fk
        foreign key (seat_type_id) references seat_type (seat_type_id),
    constraint ticket_management_from_station_id_fk
        foreign key (station_id) references station (station_id)
);

create index ticket_management_atom_station_no_index
    on ticket_management_atom (station_no);

 

座位选座

$ticket\_seat\_management$ ( train_route_id, depart_date, carriage_id, locate, seat_remain)

create table ticket_seat_management
(
    train_route_id varchar(8)   not null,
    depart_date    date         not null,
    carriage_id    int          not null,
    locate         tinyint      not null,
    seat_remain    text         not null,
    constraint ticket_seat_management_pk
        primary key (train_route_id, depart_date, carriage_id, locate),
    constraint ticket_seat_management_carriage_fk
        foreign key (train_route_id,carriage_id) references carriage (train_route_id,carriage_id)
);

 

用户

男true女false

$users$ ( user_id, user_name, role, gender, login_key, email )

create table users
(
    user_id   bigint                               not null,
    user_name varchar(8)                           null,
    role      enum ('common','student', 'admin' )  not null,
    gender    bool                                 null,
    login_key varchar(128)                         not null,
    email     varchar(32)                          null,
    constraint users_pk
        primary key (user_id)
);

 

乘员

$passenger$ ( user_id, passenger_id, passenger_name, phone_num, prole)

create table passenger
(
    user_id        bigint                      not null,
    passenger_id   char(18)                    not null,
    passenger_name varchar(8)                  not null,
    phone_num      char(11)                    not null,
    prole          enum ('common','student')   not null,
    constraint passenger_pk
        primary key (user_id, passenger_id),
    constraint passenger_user_id_fk
        foreign key (user_id) references users (user_id)
);
create index passenger_passenger_id_index
    on passenger (passenger_id);

（原数据库仍然保留role而非prole

 

已结票

出票的订单

$ticket\_sold$ ( ticket_id, order_id, passenger_id, carriage_id, seat)

create table ticket_sold
(
    ticket_id      bigint     auto_increment,
    order_id       bigint     not null,
    passenger_id   char(18)   not null,
    carriage_id    int        not null,
    seat           int        not null,
    constraint ticket_sold_pk primary key (ticket_id)
);

create index ticket_sold_order_id_index
    on ticket_sold (order_id);
create index ticket_sold_passenger_id_index
    on ticket_sold (passenger_id);

 

订单

(trade_no，支付宝流水号)

改了主键，避免同一人在同一天买多张同一车次车票

$ticket\_order$ ( order_id, user_id, passenger_id, departure_date, train_route_id, from_station_id, to_station_id, seat_type_id, order_status, order_time, price, trade_no )

create table ticket_order
(
    order_id          bigint                                      not null,
    user_id           bigint                                      not null,
    passenger_id      char(18)                                    not null,
    departure_date    date                                        not null,
    train_route_id    varchar(8)                                  not null,
    from_station_id   char(3)                                     not null,
    to_station_id     char(3)                               	  not null,
    seat_type_id      tinyint                                     not null,
    order_status    enum ('未支付','已取消', '支付超时', '已支付','待改签', '已改签', '已退票') not null,
    order_time      datetime                                  not null,
    price           double                                    not null,
    trade_no        varchar(255)                              null,
    constraint ticket_order_pk primary key (passenger_id,departure_date,train_route_id),
    constraint ticket_order_user_id_fk
        foreign key (user_id) references users (user_id),
    constraint ticket_order_passenger_id_fk
        foreign key (passenger_id) references passenger (passenger_id),
    constraint ticket_order_seat_type_id_fk
        foreign key (seat_type_id) references seat_type (seat_type_id),
    constraint ticket_order_from_station_id_fk
        foreign key (from_station_id) references station (station_id),
    constraint ticket_order_to_station_id_fk
        foreign key (to_station_id) references station (station_id)
);

create index ticket_order_order_id_index
    on ticket_order (order_id);

 

改签

$ticket\_rebook$( order_id, user_id, passenger_id, train_route_id, departure_date, from_station_id, to_station_id, seat_type_id, seat_booking, original_price, price, create_time )

create table ticket_rebook
(
    order_id          bigint                  not null,
    user_id           bigint                                      not null,
    passenger_id      char(18)                not null,
    train_route_id    varchar(8)              not null,
    departure_date    date                                        not null,
    from_station_id   char(3)                                     not null,
    to_station_id     char(3)                               	  not null,
    seat_type_id      tinyint                                     not null,
    seat_booking      enum ('1','2','3','4')  not null,
    original_price    double                  not null,
    price             double                  not null,
    create_time       time                    not null,
    constraint ticket_order_pk primary key (order_id, passenger_id, train_route_id)
);

 

预定票（改过

$ticket\_booking$ ( order_id, passenger_id, train_route_id, seat_booking, user_id, create_time)

create table ticket_booking
(
    order_id        bigint         not null,
    passenger_id    char(18)     not null,
    train_route_id  varchar(8)   not null,
    seat_booking    enum ('1','2','3','4') not null,
    user_id         bigint         not null,
    create_time     time           not null,
        constraint ticket_order_pk primary key (order_id, passenger_id,train_route_id)
);

create index ticket_booking_order_id_index
    on ticket_booking (order_id);

 

数据库的迭代

• 1.0：初期设计，主要参考了个人对于12306系统的一些最基本构想与推断，结合课程内容对数据库模型及系统架构进行设计。（4d

• 2.0：初期推演，通过爬取12306数据、参考他人对12306系统的分析等对初期设计的模型进行迭代，优化一些不合理的设计。（6d

• 3.0：开发迭代，在系统开发架构过程中使用测试数据对数据库设计进行测试，同步优化开发模型与数据库模型。（8d

  • 3.1数据构造，在华为云数据库中添加全部数据源并构造数据（3d

• 4.0：定型，基于定型的数据库进行正式开发。（10d

参考教程

book:

《高性能mysql》

《myBatis从入门到精通》

blog:

https://zhuanlan.zhihu.com/p/109114670

https://www.cnblogs.com/netfocus/archive/2011/10/10/2204949.html

https://www.cnblogs.com/netfocus/p/3307971.html

https://www.cnblogs.com/netfocus/p/4055346.html

https://www.jdon.com/cqrs.html

https://www.jdon.com/54180

https://blog.csdn.net/luxianping/article/details/122857386?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_title~default-2-122857386-blog-79849285.pc_relevant_vip_default&spm=1001.2101.3001.4242.2&utm_relevant_index=5