更好的 SQL 模式的 10 条规则
来源:互联网 发布:政府网络不能玩皮皮 编辑:程序博客网 时间:2024/06/06 17:31
在创建新表和数据仓库时,要做很多决定。一些在当时似乎无关紧要的地方,却让你和用户在数据库的生命期内感到痛苦。
我们和成千上万的人们以及他们的数据库一道工作,经历了长期的读写查询,我们差不多看到了每种情况。下面是创建免去痛苦模式的 10 条规则。
1.只使用小写字母、数字和下划线不要在数据库、模式、表或列名中使用点(dot)、空格、或连接号【注1】。点用于标示对象,通常以database.schema.table.column
的方式。
对象名称中包含点将引起混淆。类似地,在对象名字里使用空格将迫使你在查询语句中添加不必要的引号:
select "user name" from events-- vsselect user_name from events
如果在表或列名里有大写字母,查询语句将难以书写。如果所有字母都是小写的,人们将不必记住users表是Users还是users。
当你最终修改数据库或把你的表复制到仓库时,你不需要记住哪个表是大小写敏感的。
2.使用简单的、自说明的列名如果users表需要packages表的外键,就把键命名为 package_id
.避免使用简短、晦涩的名字,比如pkg_fk
;其他人不知道它代表什么。自说明的名字让其他人更容易理解模式,随着团队规模的增加,这对于维护效率至关重要。
不要为多态的数据使用有歧义的名字。如果你发现自己创建了形如item_type
或item_value
的列,那么你最好使用带有具体名字的、更多的列,比如photo_count
、view_cout
、transaction_price
。
这样,列的内容就可以常从模式中获悉,而不用依赖于当前行的其它值。
select sum(item_value) as photo_countfrom itemswhere item_type = 'Photo Count'-- vsselect sum(photo_count) from items
不要把包含表的名字做为列名的前缀。通常,让用户表包含形如user_birthday
、user_created_at
、user_name
的列,没有多少帮助。
避免使用column
、tag
和user
之类的保留字做为列名。你将不得不在查询中使用额外的引号,不这么做将让你对错误信息感到困惑。如果保留字出现在列名应该出现的地方,数据库会极大地错误理解查询。
如果表名由多个单词组成,就使用下划线隔开这些单词。package_deliveries
要比packagedeliveries
更容易阅读。
如有可能,使用一个单词而不是两个单词:deliveries
更易于读。
select * from packagedeliveries-- vsselect * from deliveries
不要给表加前缀来暗示模式。如果你需要把表分组为范围,就把这些表放入一个模式。store_items
、store_transactions
、store_coupons
之类的表名,和加了前缀的列名一样,通常不值得额外敲键盘。
我们推荐表名使用复数(例如packages),连接表(join table)名字的两个单词都用复数(例如packages_users)。单数的表名更有可能偶尔与保留字相撞,并且在查询语句中通常有着较低的可读性。
4.主键为整数即使你在用 UUID,它也没有意义(比如对于连接表来说),添加标准的id列、自增整数序列。这种 key 使得某些查询更加容易,比如仅仅选取一组数据的第一行。
如果导入的任务需要复制数据,这种 key 将成为救命稻草,因为你能够删除特定行:
delete from my_tablewhere id in (select ...) as duplicated_ids
避免多列主键。它们在尽量编写有效查询时难以推断,且难以修改。要使用整数主键、多列 unique 约束、一些单列索引代替。
5.与外键保持一致有很多关于主键和外键的命名风格。我们推荐,最受欢迎的是,任何表foo,都要拥有一个名叫id的主键,所有的主键命名为foo_id
。
另一种受欢迎的风格使用全局唯一键名,表foo有个名叫foo_id
的主键,所有外键也叫foo_id
。如果你使用缩写(users表的主键用uid),会引起混淆或命名冲突,故不要缩写。
无论你选择什么风格,就保持下去。不要在有的地方使用uid,在另外地方使用user_id
或users_fk
。
还要留意不能明显匹配表的外键。名叫owner_id
的列名或许是users
表的外键,或许不是。把列名取为user_id
,如有必要,取为owner_user_id
。
不要把日期保持为 Unix 时间戳或字符串:而是把它们转化为 datetime
。虽然 SQL 的 date 数学函数不是最好的,但是你自己处理时间戳甚至更难。使用 SQL date
函数要求每次查询都把时间戳转化为 datetime
:
select date(from_unixtime(created_at))from packages-- vsselect date(created_at)from packages
不要在单独的列里存储年、月、日。这使得每一条时间序列【注2】查询非常难以编写,将阻碍大多数刚入门的 SQL 用户使用表格中的日期信息。
select date(created_year || '-' || created_month || '-' || created_day)-- vsselect date(created_at)
7.UTC,一直都是 UTC使用某种时区而非UTC将引起永无止境的问题。优秀的工具(包括Periscope)具备所有你需要的、将数据从 UTC 转换成当前时区的功能。在 Periscope 里,添加:pst就轻松地将 UTC 转换成 Pacific Time:
select [created_at:pst], email_addressfrom users
数据库的时区应该是 UTC,所有的 datetime 列应该是去除了时区的类型(没有时区的时间戳)。
如果你的数据库的时区不是 UTC,或者你的数据库既有 UTC、又有非 UTC 的 datetime,那么时间序列的分析难度将大为增加。
8.单一的真实数据来源对于一条数据,应该有且只有一个真实来源【注3】。视图和汇总应该打上标签。这样,数据的使用人员将明白,在他们使用的数据和真实数据之间存在差异。
select *from daily_usage_rollup
留下废弃的user_id、user_id_old、user_id_v2之类的列,将变成混淆的、永无止境的源头。在日常维护中,要确信 drop 掉了已被抛弃的表、和弃用的列。
9.更喜欢没有 JSON 列的表你肯定不想要非常宽的表。如果有很多列,且它们有的按顺序命名(比如answer1、answer2、answer3),今后你就会痛苦。
把这种表拆分成没有重复列的模式,这种模式的形态将特别容易查询。例如,获取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_answersfrom surveyswhere id = 123-- vsselect count(response)from answerswhere survey_id = 123
对于分析查询,从 JSON 列提取数据,能够极大地降低查询效率。虽然在生产环境有很多理由使用 JSON 列,但那不是针对分析的。强势地把 JSON 列转换为更简单的数据类型,让分析更加容易、更加快捷。
10.不要过度规范化日期、邮编和国家,不需要让它们自己的表带有主键查询。如果你带了,每次查询将包含有少量的相同连接。这会给数据库创建大量重复的 SQL,以及大量额外工作。
select dates.d, count(1)from users join dates on users.created_date_id = dates.idgroup by 1-- vsselect date(created_at), count(1)from usersgroup by 1
表是有着它们大量自己的数据的第一类对象。其它数据都应该是更加重要的对象上的、另外的列。
作者: IT程序狮
链接:http://www.imooc.com/article/1122
来源:慕课网
- 更好的sql模式的10条规则
- 更好的 SQL 模式的 10 条规则
- 更好的 SQL 模式的 10 条规则
- 更好的 SQL 模式的 10 条规则
- 更好的 SQL 模式的 10 条规则
- SQL 模式的 10 条规则
- 十条实用的SQL模式规则
- MVP模式的14条规则
- 项目经理的10条规则
- 项目经理的10条规则
- 电子邮件的10条规则
- 10 条建议让你创建更好的 jQuery 插件
- 10 条建议让你创建更好的 jQuery 插件
- 10 条建议让你创建更好的 jQuery 插件
- 10 条建议让你创建更好的 jQuery 插件
- 10条建议让你创建更好的jQuery插件
- 10条建议让你创建更好的jQuery插件
- 10 条建议让你创建更好的 jQuery 插件
- Android7.0上PopupWindow的showAsDropDown位置问题
- java读取文件统计一串英文出现频率最高的单词或字母
- placeholder 和 计算
- BufferedReader的读取行数问题
- 手把手git教程(02)--创建、提交、回退
- 更好的 SQL 模式的 10 条规则
- http和https有什么区别?
- 单例模式
- bzoj2957 楼房重建
- 半自动化生成README.md文件
- NYOJ InterestingPunch-Bowl
- 我的博客之路---起点
- Linux--01--WEB服务器安装
- 图像处理中的形态学(二)