一次查询优化过程
问题描述
在正在维护的系统中有一个数据分析的模块，用于来分析一些用户访问的数据。其中有一个操作是查找某URL地址对应的ID。在这里ID与URL是存储MySQL数据的一个表中，此表就两个字段，id,url.表存储引擎类型MyISAM，当然URL字段是加了索引的。现在每天按小时定时分析数据，开始程序没有问题，当几年的数据积累下来，特别是最近业务量增加时，明显感觉到程序的执行过程变得很漫长，通过xdebug分析一次执行过程的所有执行时间，发现瓶颈在于前面所提到的通过URL地址对应的ID。下面就开始了漫长的优化之路。
第一次优化：优化细节，针对特殊地址的优化
由于应急，先考虑一些细节的调整：
首先，我们针对一些特殊的地址进行了处理，直接返回ID，比如没有URL的情况，比如Google的首页地址；
然后，我们针对一些常见的地址作为Hash存储，以及一些热数据进行内存缓存。
结果，提高一些性能，能满足当时的业务需求，特别是当特殊的地址较多的时候，其情况还是非常乐观的。
过了一段时间，到了另一个高峰点，发现此分析模块仍然会时不时的出些问题，导致数据不准确，各种情况，各种应付…不得已，开始思考另一个方案。
第一个方案： key-value数据库
依据前面的对于热点数据，将其存储到内存缓存的思路，我们考虑是否将所有数据都作为热点数据，于是先做前期预研，将部分数据导到redis中，发现速度有了极大的提升，大概在500倍的一个量级，那叫一个激动，于是决定将所有数据都迁移到redis中，想法是美好的，结果是不言而喻的。失败了。为什么呢？很简单，没资源，我们没有那么大的内存存储N个上千万的表。
第二个方案：基于单词查找树的文件结构
从前面的key-value数据库方案看，存储url到id的映射，在实现思路上还是可行的，于是开动了小心思，既然内存不够用，那么用硬盘呢？
这个既便宜又实惠，是居家旅行，杀人越货的必备良品。于是计划以URL的主域名为目录，以每个地址的md5值为文件名，每个文件存储这个地址对应的ID。考虑到域名可能很多，于是计划使用变形的单词查找树来设置多级目录，但是当域名地址太长时，文件系统在生成目录的时候会出错，只得去掉单词查找树，使用某种简单的按主域名转化后的字符串转建立目录。在预研时，先生成了5万数据的文件结构，发现生成的速率很慢，并且由于大量小文件的生成，对于整个目录的查询会很慢，但对于单个文件的读取还是很快的，然而将随着数据生成越来越多，当达到40万时，整个目录占用了大概4G+的硬盘空间。
假想一下，当1千万的数据以这种方式存储到硬盘上，会是多少？如果数据持续增加呢？到达一个亿呢？感觉到不靠谱了。于是继续想下一种方案，这个方案继续执行。
第三个方案：信赖于数据库，增加md5值存储的字段
在第二种方案中，我们是使用md5值作为文件名，在想在数据库对于md5后的文件名的查找会更快一些呢？在ID和URL映射表中，增加一个字段url_md5，存储url使用md5后的值，在这里我们直接使用MySQL的md5函数更新表数据，测试后，发现其速度有了显著提升，单个查找操作大概能提升50倍+。整体性能提升5倍的样子。于是，果断采用此方案，结果是该分析模块一直没啥事发生。(^_^)
总结
- 不要过早优化
- 优化要先找瓶颈
- 数据结构和对工具的使用很重要
- 优化时需要理清问题的根本原因是什么，最好能到理论层面或实现原理层面。
4 条留言我要留言
1 - dvaknheo | 2012-01-16 at 11:46 am
第一个方案： key-value数据库
这个方案说明这个数据表是独立的，并非联合查询
对于这样的，我第一想法就是不改 数据库，在数据库前用 memcache 做一层缓存
第二个方案走了弯路
实际上， MD5 hash 做索引应该是很快就能想到的方法。
2 - 胖胖 | 2012-01-16 at 12:32 pm
加了缓存，不过效果只能保一时。
实际上第三个方案是在第二个方案测试过程中产生的。
3 - linvo | 2012-01-16 at 7:49 pm
方案二为何不用TT之类的持久化键值存储呢？
如果担心TT在海量数据下的性能骤减问题，redis应该也有持久化的解决方案吧。
4 - 胖胖 | 2012-01-17 at 9:25 am
没有接触过TT，而且当时的想法是在不增加其它第三方的工具的前提下(redis已经在应用)，所以在想解决方案时并没有调研其它key-value的存储方案。
在应用场景中，冷热数据的区分在冷数据达到一定的量时对整体的性能优化于事无补。