此文在2016年6月份发布为《数据库设计,选择自增id还是指定主键id?》

在设计表时,最好为表加上一个主键id(尽量使用数字,主键索引数据最小),一种常见的做法是所有的表都有个自增主键id,其它表对该表有关联时,都关联上主键id。在实际开发过程中,这种方式是主要大多数的情况,但在一些场景下,有其麻烦的地方,需要更深入思考其本质。

我先假设一个场景,假设有一张学生表,学生有一个唯一的学号,还有姓名等其他信息。我们有两种方式来设计表:

方式一,表字段:

字段 说明
id 自增id
student_no 学生学号,也是一个数字或字符串,也是唯一的,有业务含义的编号
name 学生姓名

方式二,表字段:

字段 说明
id 学生学号,主键,一个数字或字符串,也是唯一的,有业务含义的编号
name 学生姓名

方式一:自增id的情况

我先直观说下自增id在哪些场景下会有麻烦的事情要处理:

  1. 自增id在开发、测试、线上每个环境是不同的,如果代码中需要写死引用哪个id的话,由于每个环境id不同,就需要为此配置不同的id值,增加了配置项和维护工作。当然,此时配置的情况下,就应该配置为学号student_no,然后代码中再翻查出学生id。

  2. 主键id对分表不友好,像上面的学生表,如果分表的话肯定是按学生学号来分表的,而数据库外表引用又是用主键id来引用。当分完好多表了,这些表自增id都是从1开始的,所以这些分表的id就大量相同,外表引用学生表主键id就很不好处理。

  3. 自增id一般是系统内部的id,对外部用户是不可见的也没有业务含义。如果关联了另外一个表的id,那么展示给前端学生编号时,就需要每次靠这个内部自增id去反查学号student_no。自增id并无显著编码规则,容易引用错导致数据污染。

  4. 自增id相当于是由数据库控制的主键,应用无法自行指定其id。当发生删除数据,再添加数据时,所有原来的表外键引用都会失效,重新关联也很麻烦。例如学生都有个学校id字段,如果学校id引用的是t_school表的自增id,当该学校记录被删除,那么应用将没法(没那么容易)重新插入数据获得原来的学校id。而如果是使用编号作为主键,那么表之间的关联稳定性就会强多了。当然,这个缺点也有优点的时候,自增的id可以轻易保证不会被其它表引用,而人工指定的id则不排除该id已经被其它表引用了,导致可能的脏关联。

方式二:业务id的情况

阅读全文

2014年,我第一次换工作,面试了很多家公司。其中有一家公司做了一款主打产品“第一次”,它是类似朋友圈样式的微博,用户以发图片加感想文字为主,记录自己第一次做某件事情的感受。例如,第一次上大学、第一次去某个城市、第一次谈恋爱、第一次有小孩等等,每一件事情都是那么的美好,“人生若只如初见”。当时我就觉得,回忆就像一杯美酒,越久越醇香,当你再回头品味时,还是忘不了当初那份懵懂。所以,这个产品就是一个酝酿美酒的酒窖。

刚上线时,该软件的用户交流还算活跃,第一次的事情总是能让人感到好奇,用户留下很多评论这问问那了解了解,甚至成为好友结伴而行。但是,这样就是一个好产品吗?我思考了好久发现这款软件有一个缺陷,它没有解决用户多大的痛点。用户因为好奇安装这个软件,看了好奇的图片和故事,心里感到很向往。这对于用户而言是闲底下打发时间的做法,无论给用户带来知识价值或者解决问题的方案都是有限的,毕竟都是新手的感言。所以,当用户有什么问题寻求解决时,几乎不会想到这个软件。也就是说,没有太多场景触发用户想起来要打开使用这款软件。

1. trigger

所以我觉得,“触发用户打开使用”是一个好产品的必要条件,我叫这个条件为trigger。基于这样的定义,我分析了一些trigger很强的产品:

  1. 当你想知道现在自己在哪里,想坐车去某个地方时,你会打开地图类软件。

  2. 当你想了解今天有什么事情发生时,你会打开新闻类软件。基本上大多数的资讯类产品都属于这个,当然每个人感兴趣的资讯不一样,所以这一类有很多垂直化产品。

  3. 如果你习惯在网上充值话费,当电话费快用完了就会想起微信支付。同样,当需要取现金时,会想起把钱从微众银行转回银行卡。

  4. 如果你刚好在等公交车,等了好久了车都没来,我想你一定非常希望有一款软件可以查下车到哪了。

  5. 如果你经常从团购中享受周边美食的介绍和优惠,那么当你来到一个陌生的地方,你会不会打开软件看看有啥好吃有啥优惠?

  6. 当你想和某人联系,想拍个视频或图片给他看时,微信是不是唯一选择?

可以看出,trigger就是当你很想解决一个问题时所推动你去寻求解决的动力。trigger需要培养的用户习惯,当用户遇到某类问题时,脑中第一个闪过的念头就是用什么软件解决。这个效果和广告让人记住品牌有相似之处。

2. linker

微信、QQ是非常成功的产品。它们满足trigger的同时,产品中用户产生的内容和信息交流都非常丰富活跃。当大部分人都使用微信时,其它类微信类的软件第一个大难题就是用户壁垒。我把这项条件称为linker。我这样定义它:用户在一款产品中的连结和互动。

这类linker很强的软件,用户粘性很高。用户是这类软件的核心资产,当然用户产生的内容也是核心资产。linker本身会带动出一种强trigger,还是以QQ为例,当你和网友聊得很熟之后,你要找他,你自然会想到QQ,他要找你,他会发QQ过来,QQ消息过来本身就是一种trigger,让你去打开软件去看消息。

再举一个例子,淘宝的trigger很强,当人需要在买点什么时,可能会想起网上买,而一旦想要在网上买,基本都会去淘宝搜。那它的linker在哪里呢?客户之间看起来没有太强烈的互动,实际上,淘宝上基于商品的大量成交记录和评论,就是强linker的体现。

所以,如果一款产品有好的trigger和linker,那它至少满足一个好产品需要的两个前提。但是好多情况下两者很难都有,两个中有一个特别突出也会是一款不错的产品。

按trigger和linker的思路看看一些产品,例如网易云音乐:

  • trigger: 想听歌,网易云音乐歌曲很多很全很专业、高品质和高体验
  • linker: 电台节目、歌单、歌曲评论、歌曲排名、个性化推荐歌曲(大数据)

3. 用户主动周期性的trigger很困难

有类产品,帮助用户记账,一般是线下购买的东西花费的钱。只有用户将所有帐都记上,软件每个周期做的支出分析才有参考意义。问题在于,让用户养成每花一笔钱都要记一下的习惯并不容易,不排除有非常乐于记录的用户,多半是由于对支出比较在意。

更困难的情况是,要让用户周期性在某个时间点自发地做一些动作,而每次做完之后又没有太大的激励。

当出现这种trigger时,可以考虑降低周期,降低频次,降低用户忘记的代价,提高激励。

备注

  1. 截止2015-05-06,“第一次”在华为市场下载15万次,3颗星;在腾讯市场下载6万次,4.5颗星。

注释和共享

2016-我的这半年

发布在 流水

今年5月份,我离开了Ali,在Ali做的项目是海关出口B类业务,我感觉B类业务的开发过于商业化和流程框架笨重,整个团队的氛围过于看中商业结果,不够关注产品体验,看事情不够长远。我还是喜欢小而美的互联网产品,加上毕业4年来都是在大公司,所以我想去小公司积累些经历。于是,就有了这半年来太多的感想,感觉比过去的几年,要丰富得多了。

柚A米公司

感悟到的一些想法

  1. 中小型公司招聘技术岗位的公司类型及个人选择:

    • 重构类或有历史包袱的公司,应聘职位必须是技术负责人身份或领导给予足够的权力和时间(后者实际上较难)。

    • 全新搭建系统,则看重业务和团队,只要在团队中有显眼的位置即可。

  2. 敏捷开发谁都喜欢,但并没有多少团队可以做到。靠流程的方式来规避技术缺陷是不太可能的。只有技术本身的提升可以解决技术缺陷。

    例如,规定每天的发布次数或晚上发布,并没有办法有效避免发布失败的问题。正确的方式是,预发布系统、回滚管理。

关于缓存的争论

我一直对缓存的使用有着非常慎重的态度,我认为使用缓存解决性能瓶颈时要非常小心,不然很容易出现分布式系统一致性问题。我在阿里时的团队,由于全局性缓存的使用,导致了几次系统大规模崩溃,最后团队才下决定不使用全局缓存。coolshell有篇文章缓存更新的套路非常系统和全面讲述了怎样使用缓存。

本质上,关系型数据库的事务和外部系统(如redis或消息队列等)的事务是没有简单办法保证强一致性。因此,两者的读写顺序和事务回滚策略需要精密设计。

刚到柚A米公司时,技术部负责人阳工,设计了一个全局的缓存方案,该缓存全局切面在web层,对所有URL的返回结果进行缓存,同时有对应的更新策略。我列举了很多弊端说明全局式切面式缓存容易失控、不好运维的情况:

  1. 强一致性问题,怎样快速发现快速修复?下单后快速看不到订单的问题如何解决?MYSQL的事务怎样和redis,mq等其它方式结合?

  2. 缓存数据由业务代码+数据库数据加工而成。当逻辑代码出行bug导致缓存数据错误时,怎样大规模更新缓存?当数据库发生数据订正时,怎样更新缓存数据?

  3. redis的运维稳定性和经验远弱于mysql。一个配置的redis ip被多个应用引用,一个数据存储被多个应用依赖,这也是不够内聚的。是否应该规范应用间调用仅存在SOA方式,方便治理,缓存只由一个应用管理。异步则仅用消息队列和定时任务方式。

  4. 这份缓存数据对预发环境可能不够友好,没有mysql对向下兼容的运维稳定性强。

  5. 对于前端的高并发接口,可以设置0.1秒的高速缓存来解决问题。不需要为冷门接口都设计缓存。

技术执念?iframe坚决不用

阅读全文

跨域的安全性设计

发布在 tech

开发网站时,最好的设计是前端和后台在同一个域名下,这样在调用时不用涉及跨域问题,在开发、测试、预发、线上环境中切换也方便。

按照这种设计,一般会这样划分域名和URL:

1
2
3
m.abc.com 线上域名、预发环境域名
dev.m.abc.com 开发环境域名
test.m.abc.com 测试环境域名
1
2
/_    所有下划线开头的,转发给后台
/ 其它所有链接,转发给前端静态文件目录

但是实际开发中,还是难免会出现跨域的问题。例如集团下两家独立子公司合作,共享登录态。于是就会涉及到跨域的设计。

跨域的URL、cookie、ajax调用相关设计

  1. 首先跨域调用,服务器端必须设置Access-Control-Allow-Origin头部。一般偷懒的做法会设置上Access-Control-Allow-Origin: *,这样是不安全的,现代浏览器已经为这种不安全做了一定的防范:当服务器端设置的Access-Control-Allow-Origin*时,跨域的cookie不会发送给服务器。也即,这种方式只适合于不需要登录态的跨域调用。因此,现代浏览器的这种做法可以阻挡大部分的钓鱼冒牌网站。

    正常的跨域调用,显然是需要带上跨域站点的cookie的。此时就必须让服务器设置上Access-Control-Allow-Origin值为明确某个域名,这个可以交给浏览器根据不同的跨域请求设置不同的域名。同时javascript XHR也需要设置允许带上跨域cookie。

  2. 为了使域名cookie在开发测试线上环境中同时可用(主要是登录态,例如微信登录就只能指定一个域名),一般我们会把cookie打在顶级域名下。但要注意外部存储平台如七牛,当七牛绑定到我们域名的某个二级域名时,请求图片就会带上顶级域名下的cookie,导致理论上七牛可以获得我们用户的登录态。解决方式是,为七牛绑定上另外一个顶级域名,例如google的所有静态资源就放在gstatic.com下,就可以从运维角度物理上完全杜绝google.com的cookie泄漏给CDN服务器的问题。

注释和共享

1. MySQL limit的性能实践

mysql的分页功能,在数据量大的情况下,即便有索引,也还是很慢。

做个实验:新建一个最简单的表:

1
2
3
4
CREATE TABLE `t_test` (
`id` BIGINT(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
)

然后写个程序,往表里面插入2000万条数据,然后做下面实验:

SQL 先执行10次后,再执行的耗时ms
SELECT id FROM t_test LIMIT 10000,10 3
SELECT id FROM t_test LIMIT 100000,10 28
SELECT id FROM t_test LIMIT 1000000,10 280
SELECT id FROM t_test LIMIT 10000000,10 4500
SELECT id FROM t_test LIMIT 20000000,10 6900

可见,即便是在只搜索索引(index索引覆盖)的情况下,只要有大的limit offset,那么查询时间和offset是O(n)线性增长。

如果没有limit大offset,例如第20000000个id是20022780起,那么这样查询速度很快:

SQL 先执行10次后,再执行的耗时ms
SELECT * FROM t_test WHERE id>=20022780 ORDER BY id LIMIT 10 1
SELECT id FROM t_test ORDER BY id LIMIT 10 1

出现上面的情况,可以感性的理解为,limit大offset对索引并没有效果,为什么没有效果?因为:

mysql的b树索引记录了索引字段(可以为组合索引)的大小顺序,存放的格式为(索引字段,key),但是索引没有记录下当前值之前的记录数量,这个记录在实际场景中意义也不大,因为where条件中刚好全是索引字段的,很少。

所以,limit大offset,免不了要全量地扫描数数。

讨论该问题的两篇博客:1 2

解决这个问题的思路:

  1. select的字段尽量用索引的字段,例如id,拿到id之后,再用in去select出全部需要的字段。这种方式用到索引覆盖。
  2. 从业务含义出发,限制limit的offset的最大值,这么多翻页实际上没有太多业务价值,搜索引擎最多也就100页,每页50个。

但是本质上,limit大offset的问题并没有解决,无论加什么索引,不可能把offset是几十万几百万的sql优化到几十毫秒的级别。而根据id来遍历全部,应该使用where id>xx limit 10的方式,id由应用自己记录,即类似于应用代码自行实现游标。

2. 关联查询的3种写法

阅读全文
作者的图片

Pugwoo Chia

关于一切的美好


Programmer


Shenzhen,Canton