• 搜狐网站诚聘Java、PHP和C++工程师
• JavaEye问答大赛开始了！ 从6月23日 至 7月6日，奖品丰厚 ！
• 快来参加7月17日在成都举行的SOA中国技术论坛
• 北京: 千橡集团暨校内网诚聘软件研发工程师

One of the items we're struggling with now on Stack Overflow is how to maintain near-instantaneous performance levels in a relational database as the amount of data increases. More specifically, how to scale our tagging system. Traditional database design principles tell you that well-designed databases are always normalized, but I'm not so sure.

Dare Obasanjo had an excellent post When Not to Normalize your SQL Database wherein he helpfully provides a sample database schema for a generic social networking site. Here's what it would look like if we designed it in the accepted normalized fashion:

Normalization certainly delivers in terms of limiting duplication. Every entity is represented once, and only once -- so there's almost no risk of inconsistencies in the data. But this design also requires a whopping six joins to retrieve a single user's information.

select * from Users u
inner join UserPhoneNumbers upn
on u.user_id = upn.user_id
inner join UserScreenNames usn
on u.user_id = usn.user_id
inner join UserAffiliations ua
on u.user_id = ua.user_id
inner join Affiliations a
on a.affiliation_id = ua.affiliation_id
inner join UserWorkHistory uwh
on u.user_id = uwh.user_id
inner join Affiliations wa
on uwh.affiliation_id = wa.affiliation_id

(Update: this isn't intended as a real query; it's only here to visually illustrate the fact that you need six joins -- or six individual queries, if that's your cup of tea -- to get all the information back about the user.)

Those six joins aren't doing anything to help your system's performance, either. Full-blown normalization isn't merely difficult to understand and hard to work with -- it can also be quite slow.

As Dare points out, the obvious solution is to denormalize -- to collapse a lot of the data into a single Users table.

This works -- queries are now blindingly simple (select * from users), and probably blindingly fast, as well. But you'll have a bunch of gaping blank holes in your data, along with a slew of awkwardly named field arrays. And all those pesky data integrity problems the database used to enforce for you? Those are all your job now. Congratulations on your demotion!

Both solutions have their pros and cons. So let me put the question to you: which is better -- a normalized database, or a denormalized database?

Trick question! The answer is that it doesn't matter! Until you have millions and millions of rows of data, that is. Everything is fast for small n. Even a modest PC by today's standards -- let's say a dual-core box with 4 gigabytes of memory -- will give you near-identical performance in either case for anything but the very largest of databases. Assuming your team can write reasonably well-tuned queries, of course.

There's no shortage of fascinating database war stories from companies that made it big. I do worry that these war stories carry an implied tone of "I lost 200 pounds and so could you!"; please assume the tiny-asterisk disclaimer results may not be typical is in full effect while reading them. Here's a series that Tim O'Reilly compiled:

• Second Life
• Blogline and Memeorandum
• Flickr
• NASA World Wind
• Craigslist
• O'Reilly Research
• Google File System and BigTable
• Findory and Amazon
• MySQL

There's also the High Scalability blog, which has its own set of database war stories:

• YouTube
• PlentyOfFish
• Google
• MySpace
• Amazon
• Twitter

First, a reality check. It's partially an act of hubris to imagine your app as the next Flickr, YouTube, or Twitter. As Ted Dziuba so aptly said, scalability is not your problem, getting people to give a shit is. So when it comes to database design, do measure performance, but try to err heavily on the side of sane, simple design. Pick whatever database schema you feel is easiest to understand and work with on a daily basis. It doesn't have to be all or nothing as I've pictured above; you can partially denormalize where it makes sense to do so, and stay fully normalized in other areas where it doesn't.

Despite copious evidence that normalization rarely scales, I find that many software engineers will zealously hold on to total database normalization on principle alone, long after it has ceased to make sense.

When growing Cofax at Knight Ridder, we hit a nasty bump in the road after adding our 17th newspaper to the system. Performance wasn't what it used to be and there were times when services were unresponsive.

A project was started to resolve the issue, to look for 'the smoking gun'. The thought being that the database, being as well designed as it was, could not be of issue, even with our classic symptom being rapidly growing numbers of db connections right before a crash. So we concentrated on optimizing the application stack.

I disagreed and waged a number of arguments that it was our database that needed attention. We first needed to tune queries and indexes, and be willing to, if required, pre-calculate data upon writes and avoid joins by developing a set of denormalized tables. It was a hard pill for me to swallow since I was the original database designer. Turned out it was harder for everyone else! Consultants were called in. They declared the db design to be just right - that the problem must have been the application.

After two months of the team pushing numerous releases thought to resolve the issue, to no avail, we came back to my original arguments.

Pat Helland notes that people normalize because their professors told them to. I'm a bit more pragmatic; I think you should normalize when the data tells you to:

1. Normalization makes sense to your team.
2. Normalization provides better performance. (You're automatically measuring all the queries that flow through your software, right?)
3. Normalization prevents an onerous amount of duplication or avoids risk of synchronization problems that your problem domain or users are particularly sensitive to.
4. Normalization allows you to write simpler queries and code.

Never, never should you normalize a database out of some vague sense of duty to the ghosts of Boyce-Codd. Normalization is not magical fairy dust you sprinkle over your database to cure all ills; it often creates as many problems as it solves. Fear not the specter of denormalization. Duplicated data and synchronization problems are often overstated and relatively easy to work around with cron jobs. Disks and memory are cheap and getting cheaper every nanosecond. Measure performance on your system and decide for yourself what works, free of predispositions and bias.

As the old adage goes, normalize until it hurts, denormalize until it works.

某些时候，如果从语句的本身上来说，已经很难优化了。如我们电子客票系统中有一个查询语句就是如此。我不便写出语句，但是我可以还原场景给大家看看。
我们的电子客票系统中，在首页有个特价机票展示区域，如下所示：


当我们点击“更多单程特价票”/“更多往返特价票”的时候，会转到如下的结果list分页页面：

这个里面就显示了所有的单程特价票或者是往返特价票，默认根据特价票最近被修改时间来排序。
我们现在来分析一下这个业务的合理性，或者说这种展示方式存在的必要性。
用户从首页的特价展示区中点击更多进入此list页面，看到的是所有的特价机票，通常来说，这对用户是没有必要的。因为，一般来说用户不会去关心整个中国的所有的特价机票，他们一般只会关心自己所在的城市的特价机票，或者是自己要去的某个城市的特价机票。比方说我人在杭州，要去北京，我想要看的肯定是从杭州出发的特价机票，最好是直接能看到从杭州出发到北京的特价机票，或者是想看从北京出发到杭州的特价机票，因为我要回来。我根本不会去关心从合肥到香港的特价机票，因为这对我来说没有意义。基于以上推理中的大部分用户行为，全部特价机票的首页信息几乎没用，更不会去点第二页，第三页，而是去重新从输入框中输入相关的起飞城市等信息后查询。
从以上的分析中，我与开发人员以及产品经理等讨论，最终说服他们。在用户点击“更多……”时的行为上，增加一个判断，根据用户的IP判断用户所在城市（或离其最近的有机场的城市），然后默认显示这个城市的特价机票，即把原来的“所有城市”变成“所在城市”，在美化上，开发做了些变更，最终特价机票显示页变成了如下这种形式：

这种从业务角度的优化，带来了如下的好处：
1、用户点击“更多……”时刻，所执行的sql语句得到了大大的优化，逻辑读从3w左右降至300以内甚至几十。
2、本来显示所有特价机票的无用信息，被更换成显示用户所在城市的特价机票信息，绝大多数情况下，恰好提供了用户所需的有用信息，提高了用户体验。
3、以前，用户进入所有特价机票首页后，一般情况下要自己重新输入条件搜索自己所在城市特价机票信息，浪费了大量的用户劳动力；优化后，这批劳动力亦节省下来。
另，欢迎大家来淘宝网预定机票和酒店