2023年7月6日发(作者：)

12306的数据库设计原⽂地址：/hnkontecna/article/details/61672983

标签PostgreSQL , 12306 , 春节 , ⼀票难求 , 门禁⼴告 , 数组 , 范围类型 , 抢购 , 排他约束 , ⼤盘分析 , ⼴告查询 , ⽕车票背景马上春节了，⼜到了⽕车票的销售旺季，⼀票难求的问题依旧存在吗？还记得10年前春节前买⽕车票得在放票前1天搬个⼩板凳去排队，对于热门路线，排⼀个晚上都有可能买不到票。随着互联⽹的发展，⼏年前建设了12306⽹上购票系统，可以从电脑上买票，但是不要以为在电脑上就能买到票。我记得12306刚推出时，经常发⽣12306⽹站打不开，⽆法付款的问题。为什么呢？原因很简单，春节期间⽹上购票的⼈可能达到⼏亿的级别，⽽且放票⽇期是同⼀天同⼀个时间点，也就是说同⼀时刻12306要接受⼏亿⽤户的访问。处理能⼒和实际的访问需求更不上，带来的结果就是⽹站打不开，系统不稳定的现象。后来12306想了分线路分时段开启的办法，想办法把不同线路的⽤户错开时间来访问12306的⽹站，但是这个⽅法起初的效果不明显，并不是所有⽤户都知道的（就好像你临时通知今天不上班，但还是有⽤户会来单位的），所以⼤多数⽤户还是集中在⼀个点去访问12306的⽹站。随着硬件的发展，技术的演进，12306的系统越来越趋于成熟，稳定性和响应速度也越来越好。据说现在很多商家还开通了云抢票业务，本质上是让你不要冲击12306系统了，把需求提前收集，在放票时，这些系统会进⾏排队与合并购买，这种⼿段可以减少12306的访问并发。抢⽕车票是很有意思的⼀个课题，对IT⼈的智商以及IT系统的健壮性，尤其是数据库的功能和性能都是⼀种挑战。接下来我们⼀起来缕⼀缕有哪些难点，⼜有怎样的解决⼿段。⼀、铁路售票系统 - 西天取经之路开始啦铁路售票系统最基本的功能包括查询余票、余票统计、购票、车次变化、退票、改签、中转乘车规划 等。

每个需求都有各⾃的特点，例如1. 查询余票，⽤户在购票前通常会查⼀下到达⽬的地有哪些余票，它属于⼀个⾼并发的操作，同时需要统计余票张数，需要很强的CPU来⽀撑实时的查询。2. 购票，购票和查询不⼀样，购票是会改变库存的，所以对数据库来说是更新的操作。⽽且购票很可能发⽣冲突，例如很多⼈要买同⼀趟车的票，那就出现冲突了，到底卖给谁呢？需要考虑锁冲突，尽量的让不同的⼈购买时可并⾏，或者可以合并多⼈的购票请求，来减少数据库的更新操作。3. 中转乘车，当⽤户需要购买的起点和到达站⽆票时，需要计算中转的搭乘⽅案。⽐如从北京到上海，如果没有直达车，是不是该转车呢？转哪趟，在哪⾥转就成了问题，简单⼀点就是买票的⼈⾃⼰想。⾼级⼀点的话，可以让12306给你推荐路线，这个涉及的是数据库的路径规划功能。我们来逐⼀分析⼀下这些需求的特点。1 查询余票1. 普通的余票查询需求你如果要买从北京到上海的⽕车票，通常会查⼀下哪些车次还有余票。查询的过滤条件可能很多，⽐如1.1. 上车站、下车站、中转站1.2. 车次类型（⾼铁、动车、直达、快速、普客、...）1.3. 出发⽇期、时段1.4. 到达⽇期、时段1.5. 席别（硬座、硬卧、...站票）1.6. 过滤掉没有余票的车次展⽰给⽤时还要考虑到怎么排序（是按始发时间排呢，还是按票价，或者按余票数量排？），怎么分页。眼见不⼀定为实查询余票通常不是实时的、或者说不⼀定是准确的，有可能是后台异步统计的结果。即使是实时统计的结果，在⾼并发的抢票期间，你看到的信息对你来说也许很快就会失效。⽐如你看到某趟车还有100张票，很可能等你付款的时候，已经卖光了。所以在⾼峰期，余票信息的参考价值并不⼤，不要被迷惑了。2. 查询余票的另⼀个更⾼级的需求是路径规划, ⾃动适配(根据⽤户输⼊的中转站点s)这个功能以前可能没有，但是总有⼀天会暴露出来，特别是车票很紧张的情况下。就⽐如从北京到上海，直达的没有了，系统可以帮你看看转⼀趟车的，转2趟车的，转N趟车的。（当然，转的越多越复杂）。从中转这个⾓度来讲，实际上已经扯上路径规划的技术了。怎么中转是时间最短的、价格最低的、中转次数最少的等等。（⾥⾯还涉及转车的输⼊要求（⽐如⽤户要求在⼀线城市转车，或者必须要转⾼铁））。关于路径规划，可以参考⼀下PostgreSQL pgrouting，已⽀持多种路径规划算法，⽀持算法的⾃定义扩展。简直是居家旅⾏，杀⼈灭⼝的必备良药。师⽗⼩⼼，有妖怪。。。1. ⼤多数⽤户是有选择综合症的，通常来说，⽤户可能会查询很多次，才选到合适⽇期的合适车次的票。查询量⽐较⼤，春节期间更甚。2. 为了展⽰余票数量，需要统计，会耗费较多的CPU, IO资源。3. 路径规划，帮⽤户选择最佳的转车路线，很考验数据库的功能，⼤多数数据库没有这个功能。2 余票统计对于售票系统来说，查询余票实际上是⼀个统计操作。统计操作相⽐简单查询，不但消耗更多的IO还消耗更多的CPU资源。想像⼀下⼏亿⼈（其实不⽤这么多，可能⼏⼗万就够了）来查询余票，即使机器没挂掉，也会把所有机器的资源跑满，CPU产⽣的热量，可能⼏分钟就能把鸡蛋煮熟咯。为了减少实时查询余票的开销，通常会分时进⾏统计，更新最新的统计信息。⽤户查询余票信息时，查到的是统计后的结果，前⾯我已经分析过了，余票是不可信的，所以存在⼀定的延迟其实也是允许的。这下不能煮鸡蛋了，因为把⼏亿个统计请求，变成了1个统计请求，是不是⼀下⼦世界就冷静了呢？我们可以看到12306主页的余票⼤盘数据师⽗⼩⼼，有妖怪。。。1. 余票信息需要统计，查询会耗费较多的CPU,IO。由于余票是不可信的，所以存在⼀定的延迟其实也是允许的，优化⼿段是异步统计，⽤户查询统计后的结果。3 购票购票相对于查询余票来说，从请求量来分析，⽐查询请求更少，因为通常来说，⽤户可能会查询很多次，才选到合适⽇期的合适车次的票。但是由于购票是⼀次交易，每次交易都会产⽣写操作，⽽且这种交易并不是⽆限库存的交易，因为库存是有限的，所以设计的关键是降低粒度，减少锁冲突，减少数据扫描量。另外还需要考虑的因素包括1. 同⼀趟车次的同⼀个座位，在不同的维度可能会被多次售卖1.1 时间维度，如发车⽇期1.2 空间维度，不同的起始站点2. 票价票价⼀般和席别绑定，按区间计费。另⼀个需求是尽量的将票卖出去，减少空洞座位。打个⽐⽅，从北京到上海的车，中间经过（天津、徐州、南京、⽆锡、苏州），如果天津到南京段有⼈买了，剩下的没有被购买的段应该还可以继续被购买。如果⼀趟从北京到上海的车，所有的票都被苏州到上海的⽤户买了，其他的位置没有卖出，铁⼤哥是不是要哭晕在厕所。⼜或者某趟车⼤量的座位被中途上车的⽤户买了，是不是可以买到全程的票数就少了。以前就存在这种情况，对铁⼤哥的成本是个不⼩的考验。师⽗⼩⼼，有妖怪。。。1. 为了减少购票系统的写锁冲突，例如同⼀个座位，尽量不出现因为⼀个会话在更新它，其他会话需要等待的情况。（⽐如A⽤户买了北京到天津的，B⽤户买了天津到上海的同⼀趟车的同⼀个座位，那么应该设计合理的合并操作（如数据库内核改进）或者从设计上避免锁等待）其实就是把座位的空间维度（从哪⾥到哪⾥）、本⾝的属性（座位号）、时间维度（发车⽇期）进⾏解耦，放到多条记录中，从⽽在购买时，可以同时进⾏。因为数据库中最⼩的锁⽬前是⾏锁（单⾏记录同⼀时刻只允许⼀个会话进⾏更新，其他的被堵塞，等待释放锁），也许随着技术的发展，会演变成列锁，或者列⾥⾯的元素锁（⽐如数组，JSON）。4 车次新增、删除、变更春节来临时、通常需要对某些热门线路增加车次。及车次的新增、删除和变更需求。在设计数据库时，应该考虑到这⼀点。师⽗⼩⼼，有妖怪。。。车次的变更简直是牵⼀发⽽动全⾝，⽐如余票统计会跟着变化，查询系统也要跟着变化。还有初始化信息的准备，例如为了加快购票的速度，可能会将车次的数据提前准备好（也许是每个座位⼀条记录），参考第3个需求的解说。5 对账需求票可能是经过很多渠道卖出去的，例如⽀付宝、去哪⼉、携程、铁⽼⼤的售票窗⼝、银⾏的代理窗⼝、客运机构 等等。涉及到实际的销售信息与资⾦往来的对账需求。通常这个操作是隔天延迟对账的。6 退票、改签需求退票和改签也是⽐较常见的需求，特别是现在APP流⾏起来，退改签都很⽅便。这就导致了⽤户可能会先买好⼀些，特别是春节期间，⽤户⽆法预先知道什么时候请假回家，所以先买⼏张不同⽇期的，到时候提前退票或者改签。改签和退票就涉及到位置回收（对数据库来说也许是更新数据），改签还涉及购票同样的流程。7 取票这个就很简单了，就是按照⽤户ID，查询已购买，未打印的车票。8 其他需求票的种类学⽣票、团体票、卧铺、站票这⾥特别是站票，站票是有上限的，需要控制⼀趟车的站票⼈数站票同样有起点和终点，但是有些⽤户可能买不到终点的票，会先买⼀段的，然后补票或者就⼀直在车上不下车，下车后再补票。先上车后补票这个⼿段极其恶劣，不过很多⼈都是这么⼲的，未婚先孕，现在的年轻⼈啊。。。。通常会考虑容积率，避免站票太多。如果⽆节制的销售站票，可能坐不下的。猴哥，师⽗被妖怪抓⾛啦1. ⼤多数⽤户是有选择综合症的，通常来说，⽤户可能会查询很多次，才选到合适⽇期的合适车次的票。查询量⽐较⼤，春节期间更甚。2. 为了展⽰余票数量，需要统计，会耗费较多的CPU, IO资源。3. 路径规划的需求，帮⽤户找出（时间最短、⾏程最短、指定中转站、最廉价、或者站票最少）等条件的中转搭乘路线。妈妈再也不⽤担⼼买不到票啦。4. 余票信息需要统计，查询会耗费较多的CPU,IO。由于余票是不可信的，所以存在⼀定的延迟其实也是允许的，优化⼿段是异步统计，⽤户查询统计后的结果。5. 为了减少购票系统的写锁冲突，例如同⼀个座位，尽量不出现因为⼀个会话在更新它，其他会话需要等待的情况。（⽐如A⽤户买了北京到天津的，B⽤户买了天津到上海的同⼀趟车的同⼀个座位，那么应该设计合理的合并操作（如数据库内核改进）或者从设计上避免锁等待）其实就是把座位的空间维度（从哪⾥到哪⾥）、本⾝的属性（座位号）、时间维度（发车⽇期）进⾏解耦，放到多条记录中，从⽽在购买时，可以同时进⾏。因为数据库中最⼩的锁⽬前是⾏锁（单⾏记录同⼀时刻只允许⼀个会话进⾏更新，其他的被堵塞，等待释放锁），也许随着技术的发展，会演变成列锁，或者列⾥⾯的元素锁（⽐如数组，JSON）。6. 车次的变更简直是牵⼀发⽽动全⾝，⽐如余票统计会跟着变化，查询系统也要跟着变化。还有初始化信息的准备，例如为了加快购票的速度，可能会将车次的数据提前准备好（也许是每个座位⼀条记录），参考第3个需求的解说。综合以上痛点和需求分析，我们在设计时应尽量避免锁等待，避免实时余票查询，同时还要避免席位空洞。⼆、谁是猴⼦请来的救兵?经过前⾯的分析，已经把铁路售票系统最关键的⼏个业务场景进⾏了描述，并且阐述了其中的设计痛点，那么我们如何设计合理的系统来满⾜⼏亿⼈民抢票的需求呢？西游记⾥每⼀集孙悟空师⽗被妖怪抓⾛，总能找到救兵来解救。我们也需要救兵，救兵快来啊。。。。PostgreSQL是全世界最⾼级的开源数据库，⼏乎适⽤于任何场景。有很多特性是可以⽤来加快开发效率，满⾜架构需求的。针对铁路售票系统，可以⽤到哪些救命法宝呢？1. 看招，法宝1，varbit类型使⽤varbit存储每趟车的每个座位途径站点是否已销售。例如 G1921车次，从北京到上海，途径天津、徐州、南京、苏州。包括起始站，总共6个站点。 那么使⽤6个⽐特位来表⽰。'000000'

如果我要买从天津到徐州的，这个值变更为(下车站的BIT不需要设置)'010000'

这个位置还可以卖从北京到天津，从徐州到终点的任意站点。余票统计也很⽅便，对整个车次根据BIT做聚合计算即可。统计任意组合站点的余票（ 北京-天津, 北京-徐州, 北京-南京, 北京-苏州, 北京-上海, 天津-徐州, 天津-南京, ......, 苏州-上海 ）udf_count(varbit) returns record

统计指定起始站点的余票（start: 北京, end: 南京； 则返回的是 北京-南京 的余票）udf_count(varbit,

start, end) returns record

以上两个需求，开发对应的聚合函数即可，其实就是⼀些指定范围的bitand的count操作。通过法宝1，解决了统计余票的需求、售票⽆空洞的需求。2. 看招，法宝2，数组类型使⽤数组存储每趟车的起始站点，途经站点。使⽤数组来存储，好处是可以使⽤到数组的GIN索引，快速的检索哪些车次是可以搭乘的。例如查询从北京到南京的车次。select 车次

from 全国列车时刻表 where column_arr @> array['北京','南京'];

这条SQL是可以⾛索引的，效率⾮常⾼，每秒请求⼏⼗万不是问题。法宝2解决了⾼并发请求查询符合条件的列车信息的需求。3. 看招，法宝3，skip locked这个特性是跳过已被锁定的⾏，⽐如⽤户在购买某⼀趟从北京到南京的车票时，其实是⼀次UPDATE ... SET BIT的操作。但是很可能其他⽤户也在购买，可能就会出现锁冲突，为了避免这个情况发⽣，可以skip locked，跳过锁冲突，直接找另⼀个座位。select *

from table

where column1='车次号' -- 指定车次

and column2='车次⽇期' -- 指定发车⽇期

-- and mod(pg_backend_pid(),100) = mod(pk,100) -- 提⾼并发，如果有多个连接并发的在更新，可以直接分开落到不同的⾏，但是可能某些pID卖完了，可能会找不到票，建议不要开启这个条件

and column4='席别' -- 指定席别

and getbit(column3, 开始站点位置, 结束站点位置-1) = '0...0' -- 获取起始位置的BIT位，要求全部为0

order by column3 desc -- 这个⽬的是先把已经卖了散票的的座位拿来卖，也符合铁⼤哥的思想，尽量把起点和重点的票卖出去，减少空洞

for update

skip locked -- 跳过被锁的⾏，⽼⽜逼了，不需要锁等待

limit ?; -- 要买⼏张票

法宝3解决了⼀伙⼈来抢票时，在同⼀趟车的座位发⽣冲突的问题。4. 看招，法宝4，cursor如果要查询⼤量记录，可以使⽤cursor，减少重复扫描。5. 看招，法宝5，路径规划如果⽤户选择直达车已经⽆票了，可以⾃动计算如何转乘，根据⽤户的乘车站点和⽬的地选择最佳搭乘路线。参考⼀下pgrouting，与物流的动态路径规划需求⼀致。6. 看招，法宝6，多核并⾏计算开源也⽀持多核并⾏计算的，在⽣成余票统计时，为了提⾼⽣成速度，可以将更多的CPU加⼊进来并⾏计算，快速得到余票统计。就⽐如你策划了⼀本书，已经列好了⼤纲，同时你找了100个作者，这100个作者可以根据你分配的⼯作，同时开始写作，很快就能把⼀本书写完。⽽传统的情况，⼀本书，只能⼀个作者帮你写，即使你找了100个作者，另外的99位也只能空闲，或者他们只能写其他的99本书。7. 看招，法宝7，资源隔离PostgreSQL为进程模型，所以可以控制每个进程的资源开销，包括(CPU,IOPS,MEMORY,network)，在铁路售票系统中，查询和售票是最关键的需求，使⽤这种⽅法，可以在关键时刻保证关键业务有⾜够的资源，流畅运⾏。这个思想和双⼗⼀护航也是⼀样的，在双⼗⼀期间，会关掉⼀些不必要的业务，保证主要业务的资源，以及它们的流畅运⾏。8. 看招，法宝8，分库分表铁路数据达到了海量数据的级别，很显然⼀台机器⽆法存下所有的铁路数据。那么怎么办呢？ 可以将铁路的数据进⾏分区存储，存到不同的主机。PostgreSQL的分库分表⽅案很多，例如plproxy, pgpool-II, pg-xl, pg-xc, citus等等.9. 看招，法宝9，递归查询铁路有⾮常典型的上下⽂相关特性，例如⼀趟车途径N个站点，全国铁路组成了⼀个很⼤的铁路⽹。递归查询可以根据某⼀个节点，向上或者向下递归搜索相关的站点。⽐如在有哪些车可以直达北京，有哪些车可以转车到达北京，⼜或者查询从北京到拉萨，有哪些线路以及途经线路可以⾛。10. 看招，法宝10，MPP，打完收⼯为了持续的提⾼12306的体验，铁⼤哥还有数据挖掘的需求，⽐如今年春节应该对哪些线路增加车次，每天的车次增加的规划，哪些线路可以减少车次也能在春节前将⽤户送回家。这些问题可以基于以往的运输数据进⾏挖掘计算，进⾏回答。基于PostgreSQL的MPP产品很多，例如Postgres-XL, Greenplum, Hawq, REDSHIFT, paraccl, 等等。使⽤PG可以和这些产品很好的融合，保持语法⼀致。降低数据分析的开发成本。10道法宝⼀出，师⽗⼜回来啦。猴⼦请来的救兵厉害吧，别急，还有更厉害的，阿⾥云在PostgreSQL基础上做了很多的改进，⽐如对12306的系统，就有特别的定制特性。三、阿⾥云PostgreSQL varbit, array增强介绍在铁路购票系统中，有⼏个需求需要⽤到bit和array的特殊功能，这些特殊的功能⽬前社区版本没有，阿⾥云RDS PostgreSQL对此做了增强，如下。1. 余票统计统计指定bit范围=全0的计数不指定范围，查询任意组合的bit范围全=0的计数2. 购票指定bit位置过滤、取出、设置对应的bit值根据数组值取其位置下标。回顾⼀下我之前写的两篇⽂章，也是使⽤varbit的应⽤场景，有异曲同⼯之妙PostgreSQL的bit, array功能已经很强⼤，阿⾥云RDS PostgreSQL的bitpack也是⽤户实际应⽤中的需求提炼的新功能，⼤伙⼀起来给阿⾥云提需求。打造属于国⼈的PostgreSQL。⼩结本⽂从铁路购票系统的需求出发，分析了购票系统的部分痛点，以及数据库设计时需要注意的事项。PostgreSQL的10个特性，以及阿⾥云对PostgreSQL的改进，可以很好的满⾜铁路购票系统的需求。1. 使⽤varbit存储每趟车的每个座位途径站点是否已销售。解决了统计余票的需求、售票⽆空洞的需求。2. 使⽤数组存储每趟车的起始站点，途经站点。数组类型⽀持索引，解决了⾼并发请求查询符合条件的列车信息的需求。3. 使⽤skip locked特性，解决了⼀伙⼈来抢票时，在同⼀趟车的座位发⽣冲突的问题。4. 使⽤pgrouting路径规划特性，解决了智能推荐乘车的需求。同时还可以⽤在很多场景，⽐如⾦融风险控制，刑侦，社会关系分析，⼈脉分析等。如果你感兴趣，⽹上有很多分析PostgreSQL, pgrouting, Neo4j的⽂章，PostgreSQL甚⾄⽐Neo4j更适合graph场景.5. 多核并⾏计算，让更多的CPU同时帮你⼲活，例如快速的异步余票统计。6. 减少坐席空洞的产⽣，保证更多的⼈可以购买到全程票。（购票时，如果是中途票，尽量选择已售的中途票）7. 根据每个进程进⾏资源隔离，可以提⾼稳定性。8. 对接HybridDB (基于GPHAWQ) MPP系统，语法⼀致，可以⽀持铁路系统的数据挖掘需求，节约了开发成本。阿⾥云长期提供PostgreSQL, HybridDB ( 基于Greenplum, HAWQ ) 服务和⽀持。