一个应用系统中的数据库部分,DBA 往往充当了一个救火的角色和一个背锅的角色,并且DBA 尝尝被认为是一个运维的角色,在PG 的应用系统设计中,DB 到底在数据库部分充当什么角色,怎么能饰演好这个角色,其中今天说说设计模式这个DBA 应该扮演好的角色。
下面是一个应用系统中的关于账号和电话信息的设计
设计模式 1
CREATE TABLE public.account
( account_id serial primary key,
login text,
first_name text,
last_name text,
frequent_flyer_id integer,
update_ts timestamp without time zone
);
CREATE TABLE public.phone
( phone_id serial primary key,
account_id integer,
phone text,
phone_type text,
primary_phone boolean,
update_ts timestamp without time zone
);
设计模式2
CREATE TABLE public.account_phone
( account_id serial primary key,
login text,
first_name text,
last_name text,
frequent_flyer_id integer,
home_phone text,
work_phone text,
cell_phone text,
primary_phone text,
update_ts timestamp with time zone
);
两个模式有什么不一样,主要还是基于业务和访问的关系所决定,如果按照单表访问的方式来说,实际上比多表的设计,在访问上是要快的,数据页面装载到内存中,只需要一个IO,而如果使用第一个表的设计,必须将表进行联合查询才能得出相关的数据,而这样必须至少有两个页面进入到内存中,所以
如果从访问速度和物力内存消耗的关系上,第一个设计显然不如第二个设计。
但问题怕返回过来想,如果这个数据表中的电话会在后期进行添加,如home phone, cell phone ,前期数据是没有的,但后期添加了相关的数据,则页面有可能进行页分割,产生页面的碎片,导致访问效率逐渐变低,而此时如果仅仅访问账号的信息,这样的情况下,则性能必然不如第一种设计。
那么现在就有矛盾了,到底第一种设计好,还是第二种设计好,那种会提高查询和插入的性能,这其实是一个很难回答的问题,这里列出几个维度
1 查询频率与查询语句的维度
2 数据写入的维度与性能
3 后续扩展性问题
如果考虑这三个问题,则如果是高频插入数据,并且后续扩展性不进行考虑,或需求中扩展性不大的情况下,第二种方式是好的选择,而如果查询的频率高,后续的电话的添加的内容可能性大,数据的写入频率不高的情况下,则第一种设计的模式更好。
SELECT account_id FROM account WHERE home_phone=’8471234567’ OR work_phone=’8471234567’ OR cell_phone=’8471234567’;
SELECT DISTINCT account_id FROM phone WHERE phone=’8471234567’;
上面两个SQL 如果是经常进行的操作,那么上面的表那种设计更好
1 通过电话找人,这样的情况下,第1个表整体的设计更有利于这样的查询,原因在于如果想要命中所有的索引,需要针对三个条件建立三个索引,这样一个表的索引量的问题就凸显了,所以还是讲表的电话和账号数据分开更好,那么第二个语句如果经常进行,第二种设计更好,因为不在需要跨表的操作,才能获得数据。
所以根据业务和查询的频度,数据插入的频度,来构思一个表的设计是表设计的第一关。
除此之外一个表的设计还有你的系统是偏向OLTP还是OLAP 有关,如果是OLTP 系统,灵活性和访问的便捷性等都是考虑的方向,所以还是需要两个表的审核及模式。但如果是OLAP 的系统,提取数据中宽表的设计更得人心,一次I/O 就可以将数据提取。
那么除此以外还有别的设计的模式可以使用吗,当然目前除了在表设计中,存在的,实体--属性--值 ,还有一种将关键值与提取值分别存放的模式。
CREATE TABLE pass_json ( (pass_id INT primary key, passenger_info JSON);
passenger_info:
{"booking_ref" : "4H5B12", "passenger_no": "2", "first_name" : "MTRIAM", "last_name" : "SRREN", "update_ts" : "2021-04-12T19:45:55.022782-05:00", }
将主键进行设置,同时将值以KEY VALUE的方式,存储到JSON 的数据格式中,这样的方式好的地方是数据的输入更加灵活,乘客的信息可能在每个乘客收集的信息都不十分一致,在这样的情况下,二维表格的方式是对数据处理的一种束缚和空间的浪费,所以可以通过这样的方式来对数据进行处理。通过主键的方式进行数据的提取,并在程序的缓冲中对这些数据进行在处理和改写,在重新写入到数据库中,也是一种应对灵活环境和需求的做法。
显然这样的设计可能需要更多的内存,来将JSON 的数据统统装入到数据库的SHARE BUFFER 中。同时为在JSONB 中添加的索引也是内存消耗的一个大户。
这里小结一下
表的设计在PG中,影响了数据的存储的效率,数据提取的效率,以及运维数据库的效率,此时一个应用系统的表设计对于数据库本身来说是很重要的。
总结我们在一个数据库系统中,需要考虑
1 逻辑业务灵活扩展性,在数据库中的体现和支持
2 支持程序在数据正确性,业务逻辑正确性方的支持方式方法
3 程序设计使用数据库的方式方法,与应用后数据库运维的便捷性和可操作性
所以这里在设计一个应用系统的数据库中,要考虑的问题很多,所以大多数的时间内,设计的数据库表的性能问题被放到了最后,因为在此之前我们需要考虑表设计的灵活性,以及数据的有效性,和正确性等问题(数据去重)。
最后一个设计良好的应用系统是离不开表设计的,但很不幸的是大多数系统因为需求的问题,设计的不合理的情况,OLTP OLAP 不明确等问题,给数据库的表设计带来很各种各样的问题,如不断往一个表中增加字段,或者不断建立依附与主表的子表,如果要获取更多的信息则需要多个表进行复杂的JOIN 操作等。
本文分享自 AustinDatabases 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!