关于MySQL，SQLite和PostgreSQL在特殊应用中使用想法和总结
一不小心就写了老长，本文主要是关于MySQL，SQLite和PostgreSQL在我的特殊应用中使用想法和总结。MySQL部分是上个月的实践，PostgreSQL和非数据库解决方案是我这几天的心得。
`
本文努力地比较了MySQL内存数据库和SQLite数据库在特定应用下的优劣，MySQL一般数据库？它太笨了所以被我放弃了。随后兴头所至干脆加入了PostgreSQL和非数据库解决方案。
`
本文的结论是非数据库解决方案>PostgreSQL>SQLite~MySQL内存方案，P在个方面都要要好于S，但差距不大，而S和M则互有优劣，在此应用中我更偏向于S。
`
0.缘起
前一阵发过一文，愤恨地对MySQL的Select+Limit性能提出质疑。全文除了一些测试数据外，大部分抱怨其实只是阐述了如下一个事实：MySQL的order by语句不使用索引;limit分页性能很差;所以MySQL总体性能很差。
`
我个人因为这次数据测试而对SQLite极度推崇，然而上个月发生了几起事故，使得我对SQLite愈发不满起来。
`
事情是这样的，SimpleCD网站架设起来以后，有了一个意想不到的功效：VeryCD自从去年末的广电许可证风波后，日益和谐化，不停地删贴，电影，电视剧，删得非常之勤快；而SimpleCD则成了意外的抢险队员，相当大部分的资源索引被抢救了下来，如果说VeryCD变为“全年龄版”了的话（误），SimpleCD等若变相成为了“18X版”（大误）。举个例子好了，这两天网站近1/5的流量都是“2010春节联欢晚会”带来的，因为VC把春晚的资源都删了，只让发几个小品。其他的例子还有2012，avatar等等。
`
总之，自从有了SimpleCD以后，因为访问速度和搜索速度快很多，而且搜索结果直接包含VeryCD的结果，我找电驴的资源就直接上SimpleCD而不去VeryCD了。但是当时SimpleCD介绍没有图，资源没有评论，界面又很丑（现在也好不到哪里去就是了@.@），往往找到了还是得链到VeryCD去看看评论和介绍。所以自然而然地，我就打算要改进SimpleCD了。
`
—-话说，缘起好长，再话说，以上不是广告啊不是广告。再再话说，缘起部分还有一半—-
`
终于说到正题了，在改版中，遇到了一些问题，最严重的莫过于数据库损坏错误。那时SimpleCD的流量每天大概也有5000PV，加上有不少人用搜索功能，其实会把数据库锁住一阵，不让写；然后后台有爬虫进程在不停地更新数据，也会锁住数据库；最严重的是，因为需要加入新功能，所以要抓一些以前没有抓的数据，这个爬虫需要几乎时刻不停地写数据库，是导致悲剧发生的最重要原因。
`
以上这些进程全部都是多线程的，SQLite难免就会有长时间处于锁状态的时候，好死不死我又在调试SimpleCD发布资源的代码，需要解锁，我就很流氓地杀掉锁住的进程。几次之后，悲剧发生了，网页瘫痪，显示database is malformed。还好有备份，于是恢复，重抓，折腾一阵，搞定。过了一阵，又悲剧了，再来。。。
`
以上悲剧不断重演，导致我最后干脆写了个solve_mal.py的脚本，每次出问题就：啊拉，Tea Time了么，然后非常潇洒地输入python solve_mal.py，泡茶闲逛去了。
`
平心而论，SQLite的并发能力要比我想象的更为出色，要不是升级数据库，这种问题应该即使在50WPV的情况下也不会出现，远远好于我原本的预期。而这个问题应该更类似于一个处理机制上的bug。因为就算进程被终止，也不应该把数据库给写坏了啊，这不是并发问题了，而是严重地数据安全问题了，试想假如不是杀进程而是断电怎么办？数据库就活该损坏吗？显然SQLite的设计者无论如何也想不到会有每半小时把后台运行的数据库程序杀掉重开的变态存在，又或者没有考虑到断电的数据保护，所以疏忽了吧。
`
如此一来对SQLite愈发不满起来，再加上想试试WSGI（因为不知为何FCGI占了我好多内存，而且spawn-fcgi一天到晚内存泄漏，我每隔一个小时就要kill掉spawn-fcgi重新spawn一下，不然最夸张的时候不出4小时他们就会吃掉所有内存让网站瘫痪。），而不论是nginx的mod_wsgi还是apache的mod_啥来着，对wsgi+web.py+sqlite的组合都有古怪的bug，sqlite数据库只能读不能写，这都是啥破事啊，太ft了，我一度打算换django，因为那时候自己处理页面缓存的架构痛苦不堪，不过wsgi+django也有sqlite数据库只读的怪事，所以就暂时搁下了。
`
于是虽然很烦恼MySQL的低性能，但是那不是因为我数据结构不好么，再加上怀疑SQLite”作弊”地利用了内存才获得了高性能。反正mysql是后台程序，基本上几百年都不会关的，我让在初始化运行的时候生成一个搜索用的动态内存数据库不就解决性能问题了么？
`
以上，缘起结束。
`
1.MySQL的数据库结构优化
要使MySQL版本的SimpleCD跑起来，做大手术是必不可少的。首先要优化数据结构，其次要换用内存数据库，这时候我做了一个猜想。
`
猜想一：MySQL自动对text类型做了压缩处理，这才是搜索性能低下的原因。
`
这段时间断断续续看了一点MySQL的东东，首先抱怨一下他的文档，这是我见过的最混乱的文档，文档做得差到这个地步也不容易了。看文档无比地累，往往看完文档一头雾水，相关链接又链到完全不相关的地方，不得不google到别人的博客看别人的经验。我不要求做到像python的文档那么赞，你好歹也做到像你的同类型的sqlite的文档那样吧？真是的。
`
本来这种猜想只要到官网查查文档就能验证了，可是我楞是在文档区兜了半天圈子没有发现相关的内容，所以没办法了，还是继续猜想，说到压缩，因为MySQL的数据类型非常之多，什么CHAR，VARCHAR的；而sqlite就只有text了，以至于在sqlite中我根本就没有意识到不同的文本类型的处理方式可能会是不一样的。（请尽情吐槽我对数据库的无知好了，我本来还以为数据库数据类型就只有像sqlite那样的null,text,integer,real,blob这几种呢）。好的，那么改进一就是把标题等TEXT类型改成VARCHAR类型
`
猜想二：MySQL不用索引也是因为数据类型的原因，MySQL认为数据库的设计者不会愚蠢到用text类型来做order by，因为他们根本不会想到会有人把日期写成text保存起来，而不是专门的date类型或者int/real类型。
`
555，sqlite里面没有date类型啊，我当时也考虑过要不要转int，后来觉得转为int存还要去查mktime,strptime,gmtime等一堆乱七八糟的函数的用法，而且转来转去很麻烦。再加上觉得也就20W的数据量，二叉树的话也就是10多次的比较的事情，10多次integer比较能比10多次长度为20的字符串比较快多少秒？恐怕得用纳秒来记了吧。虽然我写程序时经常斤斤计较这个那个的，不过在明显IO-bound的这种应用里面，我还真是懒得去花时间优化这个。真不知道是我想太多了还是MySQL想太多了，反正结果就是，MySQL很可能默认了text做order by就是不用索引，MySQL就是逮到一切机会用filesort，效率怎么低怎么来。
`
猜想三：MySQL因为嫌弃我的数据表太臃肿，而没有全部载入内存进行比较。
`
因为我就一个表，而一般的设计都是大块头的东东另外放一个表，什么标题，类别等可以用char限定字符个数的东东放一个表，这样的话用作索引显示的表的大小就小了，而表小的话显然好处是很多的，比如可以允许更为频繁的读写等。（因为要写入的数据少了，那么锁住表的时间也会显著减少）惭愧，我一开始设计的时候尽想着偷懒和简单，没有想过规模变大的问题，所以才有这种问题。不过没事，现在反正重新搞数据库了，再加上要弄个内存表，到时候你MySQL就算真是filesort魔，你也是在内存里面filesort，逃不出我的五指心。
`
全部改动：`
分析了半天，痛定思痛，最终我敲定了改动方案，就是如上所述的，内存表+数据类型的改动。