MySQL数据库优化总结
2013-07-23 16:35 dhtj 阅读(151) 评论(0) 编辑 收藏 举报适当建立索引
说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍,这可真有诱惑力。可是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。由于索引的存储结构不同于表的存储,一个表的索引所占空间比数据所占空间还大的情况经常发生。这意味着我们在写数据库的时候做了很多额外的工作,而这个工作只是为了提高读的效率。因此,我们建立一个索引,必须保证这个索引不会“亏本”。一般需要遵守这样的规则:
索引的字段必须是经常作为查询条件的字段;
如果索引多个字段,第一个字段要是经常作为查询条件的。如果只有第二个字段作为查询条件,这个索引不会起到作用;
索引的字段必须有足够的区分度;
Mysql 对于长字段支持前缀索引;
对表进行水平划分
如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了。如果我拆成100个表,那么每个表只有10万条记录。当然这 需要数据在逻辑上可以划分。一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能,那么把表按月 拆分成12个,每个查询只查询一个表就够了。如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了。所以一个好的拆分依据是 最重要的。
这里有个比较好的实例
![\](http://www.php100.com/uploadfile/2013/0723/20130723093418975.jpg)
每个学生做过的题都记录在这个表里,包括对题和错题。每个题会对应一个或多个知识点,我们需要根据错题来分析学生在哪个知识点上掌握的不足。这个表很容 易达到千万级,迫切需要拆分,那么根据什么来拆呢?从需求上看,无论是老师还是学生,最终会把焦点落在一个学生的身上。学生会关心自己,老师会关心自己班 的学生。而且每个学科的知识点是不同的。所以我们很容易想到,联合学科和知识点两个字段来拆分这个表。这样拆下来,每个表大概2万条数据,检索效率非常 高。
对表进行垂直划分
有些表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。
![\](http://www.php100.com/uploadfile/2013/0723/20130723093419231.jpg)
【试题内容】、【答案信息】两个表,最初是作为几个字段添加到【试题信息】里的,可以看到试题内容和答案这两个字段很长,在表里有3万记录时,表已经占 了1G的空间,在列试题列表时非常慢。经过分析,发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件,分页显示试题详细内容。而每 次检索都是这几个表做join,每次要扫描一遍1G的表,很郁闷啊。我们完全可以把内容和答案拆分成另一个表,只有显示详细内容的时候才读这个大表,由此 就产生了【试题内容】、【答案信息】两个表。转载请注明诚信在线下载http://www.ttt8888.com