十条更好的SQL模式规则

在创建新表和数据仓库时,要做很多决定。一些在当时似乎无关紧要的地方,却让你和用户在数据库的生命期内感到痛苦。

我们和成千上万的人们以及他们的数据库一道工作,经历了长期的读写查询,我们差不多看到了每种情况。下面是创建免去痛苦模式的 10 条规则。

1.只使用小写字母、数字和下划线

不要在数据库、模式、表或列名中使用点(dot)、空格、或连接号。点用于标示对象,通常以database.schema.table.column 的方式。

对象名称中包含点将引起混淆。类似地,在对象名字里使用空格将迫使你在查询语句中添加不必要的引号:

select "user name" from events
-- vs
select user_name from events

如果在表或列名里有大写字母,查询语句将难以书写。如果所有字母都是小写的,人们将不必记住 users 表是 Users 还是 users

当你最终修改数据库或把你的表复制到仓库时,你不需要记住哪个表是大小写敏感的。

2.使用简单的、自说明的列名

如果 users 表需要 packages 表的外键,就把键命名为 package_id。避免使用简短、晦涩的名字,比如 pkg_fk;其他人不知道它代表什么。自说明的名字让其他人更容易理解模式,随着团队规模的增加,这对于维护效率至关重要。

不要为多态的数据使用有歧义的名字。如果你发现自己创建了形如 item_typeitem_value 的列,那么你最好使用带有具体名字的、更多的列,比如photo_countview_couttransaction_price

这样,列的内容就可以常从模式中获悉,而不用依赖于当前行的其它值。

select sum(item_value) as photo_count
from items
where item_type = 'Photo Count'
-- vs
select sum(photo_count) from items

不要把包含表的名字做为列名的前缀。通常,让用户表包含形如user_birthdayuser_created_atuser_name 的列,没有多少帮助。

避免使用 columntaguser 之类的保留字做为列名。你将不得不在查询中使用额外的引号,不这么做将让你对错误信息感到困惑。如果保留字出现在列名应该出现的地方,数据库会极大地错误理解查询。

3.使用简单的、自说明的表名

如果表名由多个单词组成,就使用下划线隔开这些单词。package_deliveries 要比packagedeliveries 更容易阅读。

如有可能,使用一个单词而不是两个单词:deliveries 更易于读。

select * from packagedeliveries
-- vs
select * from deliveries

不要给表加前缀来暗示模式。如果你需要把表分组为范围,就把这些表放入一个模式。store_itemsstore_transactionsstore_coupons 之类的表名,和加了前缀的列名一样,通常不值得额外敲键盘。

我们推荐表名使用复数(例如 packages),连接表(join table)名字的两个单词都用复数(例如 packages_users)。单数的表名更有可能偶尔与保留字相撞,并且在查询语句中通常有着较低的可读性。

4.主键为整数

即使你在用 UUID,它也没有意义(比如对于连接表来说),添加标准的 id 列、自增整数序列。这种 key 使得某些查询更加容易,比如仅仅选取一组数据的第一行

如果导入的任务需要复制数据,这种 key 将成为救命稻草,因为你能够删除特定行:

delete from my_table
where id in (select ...) as duplicated_ids

避免多列主键。它们在尽量编写有效查询时难以推断,且难以修改。要使用整数主键、多列 unique 约束、一些单列索引代替。

5.与外键保持一致

有很多关于主键和外键的命名风格。我们推荐,最受欢迎的是,任何表 foo,都要拥有一个名叫id 的主键,所有的主键命名为 foo_id

另一种受欢迎的风格使用全局唯一键名,表 foo 有个名叫 foo_id 的主键,所有外键也叫foo_id。如果你使用缩写(users 表的主键用 uid),会引起混淆或命名冲突,故不要缩写。

无论你选择什么风格,就保持下去。不要在有的地方使用 uid,在另外地方使用user_idusers_fk

select *
from packages
join users on users.user_id = packages.uid

-- vs

select *
from packages
join users on users.id = packages.user_id

-- or

select *
from packages
join users using (user_id)

还要留意不能明显匹配表的外键。名叫 owner_id 的列名或许是 users 表的外键,或许不是。把列名取为 user_id,如有必要,取为 owner_user_id

6.把时间存储为 Datetime

不要把日期保持为 Unix 时间戳或字符串:而是把它们转化为 datetime。虽然 SQL 的 date 数学函数不是最好的,但是你自己处理时间戳甚至更难。使用 SQL date 函数要求每次查询都把时间戳转化为 datetime:

select date(from_unixtime(created_at))
from packages

-- vs

select date(created_at)
from packages

不要在单独的列里存储年、月、日。这使得每一条时间序列查询非常难以编写,将阻碍大多数刚入门的 SQL 用户使用表格中的日期信息。

select date(created_year || '-'
|| created_month || '-'
|| created_day)

-- vs

select date(created_at)

7.UTC,一直都是 UTC

使用某种时区而非 UTC 将引起永无止境的问题。优秀的工具(包括 Periscope)具备所有你需要的、将数据从 UTC 转换成当前时区的功能。在 Periscope 里,添加 :pst就轻松地将 UTC 转换成 Pacific Time:

select [created_at:pst], email_address
from users

数据库的时区应该是 UTC,所有的 datetime 列应该是去除了时区的类型(没有时区的时间戳)。

如果你的数据库的时区不是 UTC,或者你的数据库既有 UTC、又有非 UTC 的 datetime,那么时间序列的分析难度将大为增加。

8.单一的真实数据来源

对于一条数据,应该有且只有一个真实来源。视图和汇总应该打上标签。这样,数据的使用人员将明白,在他们使用的数据和真实数据之间存在差异。

select *
from daily_usage_rollup

留下废弃的 user_iduser_id_olduser_id_v2 之类的列,将变成混淆的、永无止境的源头。在日常维护中,要确信 drop 掉了已被抛弃的表、和弃用的列。

9.更喜欢没有 JSON 列的表

你肯定不想要非常宽的表。如果有很多列,且它们有的按顺序命名(比如answer1answer2answer3),今后你就会痛苦。

把这种表拆分成没有重复列的模式,这种模式的形态将特别容易查询。例如,获取survey 表的、完成的答案的数目:

select
sum(
(case when answer1 is not null
then 1 else 0 end) +
(case when answer2 is not null
then 1 else 0 end) +
(case when answer3 is not null
then 1 else 0 end)
) as num_answers
from surveys
where id = 123

-- vs

select count(response)
from answers
where survey_id = 123

对于分析查询,从 JSON 列提取数据,能够极大地降低查询效率。虽然在生产环境有很多理由使用 JSON 列,但那不是针对分析的。强势地把 JSON 列转换为更简单的数据类型,让分析更加容易、更加快捷。

10.不要过度规范化

日期、邮编和国家,不需要让它们自己的表带有主键查询。如果你带了,每次查询将包含有少量的相同连接。这会给数据库创建大量重复的 SQL,以及大量额外工作。

select
dates.d,
count(1)
from users
join dates on users.created_date_id = dates.id
group by 1

-- vs

select
date(created_at),
count(1)
from users
group by 1

表是有着它们大量自己的数据的第一类对象。其它数据都应该是更加重要的对象上的、另外的列。

打赏此文

如果您觉得本站的内容对您有所帮助,您可以扫描下面的二维码小额支付请我喝杯茶,感谢!打赏记录
支付宝
微信
承诺:凡打赏捐助的朋友,留言备注自己的邮箱,在打赏捐助时间点的6个月内,本站会每周邮件推送原创专业技术博文,供大家学习和参考!

留下评论

All fields marked (*) are required