服务器开发中的MYSQL.docVIP

服务器开发中的Mysql 数据库开发优化 作者：胡章优 日期：2007-9-4 MSN Mail:huzhangyou2002@ 0:引言 在CSDN也总看到很多朋友讨论数据库优化的问题，另外在官方的mysql讨论组也有类似的讨论，本篇文章以其说出自笔者之手，倒不如说是笔者经过阅读，整理而成的一篇集众家之长的小paper.大部分概念来自于一些优秀的DBA以及相应的参考书籍，后面引文中将详细列出，如果某部分内容属于您原创，同时笔者没有注明，或者侵犯了您的权利，请通过开头的联系方式联系笔者，将以最快的速度处理。另外本文在撰写过程中可能没有同服务器开发连贯起来，因为对于系统来说，他们同样是服务器。我们只在使用他作为存储系统服务器。需要指出的是MYSQL系统的编译选项优化本文不列入讨论，可以参考官方的一些文档。另外在写本篇内容的时候，对以下人员表示感谢： 老婆兵() 刘博 张博（帮我借书） 1:索引正确使用 索引的出现验证了一个叫做 空间换时间的概念，也就是占用多一些的时间，来达到提高搜索定位匹配效率的概念。从数据结构的角度分析，MYSQL的索引都是在B树[1]中存储的。 而同时索引带来的代价不仅仅限于上面的内容，在数据的插入，更新，删除的时候，操作的速度同样会有一些损耗，这个应该不难理解。就是比如，插入一行新的数据时，需要计算索引的位置，并更新索引数据，这部分的时间是应该考虑进去的。所以我们也不是随便对于一些需要搜索的内容就加入索引，或者说不是索引越多越好，有时候不适当的使用索引反而会更大的影响性能[2]。比如对于如下数据表或者字段，是可以考虑不加入索引的： 很小的数据表 频繁插入，更新，删除的表，而搜索比较少 另外在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询1000*1000*1000行数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄……）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号” 这个连接字段上建立索引。 还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取[3]。 上面的例子在理解的时候可能有点晦涩，但是给我们一个比较形象的数据概念，就是一个嵌套查询中，如果3层的话，不使用索引，每层1000行比较，整个比较就达到了10亿次之多，可想而知这是一个多么庞大的计算量。 一个列涉及到查找，分组，排序，索引将可以达到提高性能的效果。这些列包含联合查询的列，或者where,group by,order by 子句[4]. 例子说明一下： Select * from blog join user on blog.userid=user.id where title=’服务器开发’ order by id; 对于上面的查询，如果是一种经常性的动作，很明显按照上面的说法，我们应该在blog表的userid以及user表的id上面设置索引，同时由于title是where子句，所以我们也应该设置索引。而blog的id字段属于order by子句，所以同样也应该设置索引。 应该提到的另外一个功能就是如何监视索引效率情况。《MYSQL 完全手册》一书中第十六章第一节提到： 可以使用SHOW STATUS或者mysqladmin扩展命令显示。如果索引正在工作，Handler_read_key的值将会很高.下图为笔者某个服务器使用SHOW STATUS的查询结果报告: 上面提到的值中: Handler_read_key代表了一个行索引被值读的次数.有点不好理解,笔者试图使用搜索引擎获取关于这个值的详细信息.--------------------未完待续 Handler_read_rnd_next的高值意味这查询运行低效,且索引的建立被认为是一种补救. --------------------未完待续 另外如果已经在表中装载了数据,并且按照希望的方式建立了索引,可以经常使用 ANALYZE TABLE user,blog;以保证优化程序总可以使用最高效率的索引[5]. 对于索引的的建立,还应该遵循以下规则: 如何包好NULL值的列都不会出现在索引中,既如果某行存在NULL值,即使对他进行索引也不会有效率的提高, 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的[6]。 索引的创建，一般应该尽可量在比较小的字段列上面设置，这样将会使得索引占据较少的资源。所以如果我们将在某一列考虑设立索引的时候，应该尽可量的估计该字段的长度预设值，这样将会带来比较好的效果。 2