服务器开发中的Mysql 数据库开发优化

1、服务器开发中的Mysql 数据库开发优化作者：胡章优日期：2007-9-40:引言在CSDN也总看到很多朋友讨论数据库优化的问题，另外在官方的mysql讨论组也有类似的讨论，本篇文章以其说出自笔者之手，倒不如说是笔者经过阅读，整理而成的一篇集众家之长的小paper.大部分概念来自于一些优秀的DBA以及相应的参考书籍，后面引文中将详细列出，如果某部分内容属于您原创，同时笔者没有注明，或者侵犯了您的权利，请通过开头的联系方式联系笔者，将以最快的速度处理。另外本文在撰写过程中可能没有同服务器开发连贯起来，因为对于系统来说，他们同样是服务器。我们只在使用他作为存储系统服务器。需要指出的是MYSQL系统

2、的编译选项优化本文不列入讨论，可以参考官方的一些文档。另外在写本篇内容的时候，对以下人员表示感谢：老婆兵()刘博张博（帮我借书）1:索引正确使用索引的出现验证了一个叫做 空间换时间的概念，也就是占用多一些的时间，来达到提高搜索定位匹配效率的概念。从数据结构的角度分析，MYSQL的索引都是在B树1可以搜索 b-tree filetype:pdf可以得到很多相关的paper,笔者参考了 http:/www.cs.tau.ac.il/ohadrode/Ex/btree_env.pdf中存储的。而同时索引带来的代价不仅仅限于上面的内容，在数据的插入，更新，删除的时候，操作的速度同样会有一些损耗，这个应

3、该不难理解。就是比如，插入一行新的数据时，需要计算索引的位置，并更新索引数据，这部分的时间是应该考虑进去的。所以我们也不是随便对于一些需要搜索的内容就加入索引，或者说不是索引越多越好，有时候不适当的使用索引反而会更大的影响性能2比如频繁进行插入，更新的数据表，而搜索查询比较少时候，应该考虑避免索引的使用。比如对于如下数据表或者字段，是可以考虑不加入索引的：a) 很小的数据表b) 频繁插入，更新，删除的表，而搜索比较少另外在嵌套查询中，对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略，一个嵌套3层的查询，如果每层都查询1000行，那么这个查询就要查询1000*1000*1000行

4、数据。避免这种情况的主要方法就是对连接的列进行索引。例如，两个表：学生表（学号、姓名、年龄）和选课表（学号、课程号、成绩）。如果两个表要做连接，就要在“学号” 这个连接字段上建立索引。还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的where子句强迫优化器使用顺序存取3有点晦涩，原文参考。上面的例子在理解的时候可能有点晦涩，但是给我们一个比较形象的数据概念，就是一个嵌套查询中，如果3层的话，不使用索引，每层1000行比较，整个比较就达到了10亿次之多，可想而知这是一个多么庞大的计算量。一个列涉及到查找，分组，排序，索引将可以达到提高性能的效果。这些列包含联合查询的列，或

5、者where,group by,order by 子句4.见Mysql 完全手册 第 16章 第一节例子说明一下：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 完

6、全手册一书中第十六章第一节提到：可以使用SHOW STATUS或者mysqladmin扩展命令显示。如果索引正在工作，Handler_read_key的值将会很高.下图为笔者某个服务器使用SHOW STATUS的查询结果报告:上面提到的值中:Handler_read_key代表了一个行索引被值读的次数.有点不好理解,笔者试图使用搜索引擎获取关于这个值的详细信息.-未完待续Handler_read_rnd_next的高值意味这查询运行低效,且索引的建立被认为是一种补救. -未完待续另外如果已经在表中装载了数据,并且按照希望的方式建立了索引,可以经常使用 ANALYZE TABLE user,bl

7、og;以保证优化程序总可以使用最高效率的索引5引自MYSQL完全手册原文 第十六章 第一节.对于索引的的建立,还应该遵循以下规则:如何包好NULL值的列都不会出现在索引中,既如果某行存在NULL值,即使对他进行索引也不会有效率的提高, 任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的6本文已经丢失作者,而且很多转载的地方都极其不负责任,丢失了里面的SQL语句,还好在CSDN的一个blog中找到: 。索引的创建，一般应该尽可量在比较小的字段列上面设置，这样将会使得索引占据较少的资源。所以如果我们将在某一列考虑设立索引的时候，应该尽可量的估计该字段的长

8、度预设值，这样将会带来比较好的效果。2:数据库类型Innodb,BDB,IASM,MyIASM其他，各自的优势劣势 本小节提到的数据库类型,对于MYSQL来说,包含如下几种:ISAMISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MySQL能够支持这样的备份应

9、用程序。MyISAMMyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMChk工具和用来恢复浪费空间的MyISAMPack工具。MyISAM强调了快速读取操作，这可能就是为什么MySQL受到了Web开发如此青睐的主要原因：在Web开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和Internet平台

10、提供商（Internet Presence Provider，IPP）只允许使用MyISAM格式。HEAPHEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MyISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。让我再重复一遍：在你用完表格之后，不要忘记删除表格。InnoDB和Berkley DBInnoDB和Berkley DB（BDB）数据库引擎都是造就MySQ

11、L灵活性的技术的直接产品，这项技术就是MySQL+ API。在使用MySQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MyISAM引擎慢很多，但是InnoDB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。如果感觉自己的确技术高超，你还能够使用MySQL+来创建自己的数据库引擎。这个API为你提供了操作字段、记录、表格、数据库、连接、安全帐号的功能，以及建立诸如MySQL这样DBMS所需要的所有其他无数功能。

12、深入讲解API已经超出了本文的范围，但是你需要了解MySQL+的存在及其可交换引擎背后的技术，这一点是很重要的。估计这个插件式数据库引擎的模型甚至能够被用来为MySQL创建本地的XML提供器（XML provider）。（任何读到本文的MySQL+开发人员可以把这一点当作是个要求。）7同样该文失去作者，若原创作者看到请联系笔者。目前摘录地址一些笔者对各自格式的使用上的理解分析8这是对不同数据库类型的简介的介绍：a) MYISAM支持快速读取操作，并带有锁定表格方式，优化并发操作。b) InnoDB以及BDB支持事务以及外键，所以如果您的开发需要这些功能，请选择该类型数据库。3:正确选择字段大小

13、长度由于MYSQL提供了非常多的内定字段数据类型，所以也给开发带来了很多方便，然而方便的同时也应该考虑效率的问题，包括正确的选择字段类型，以及字段长度。比如要存储一个人的姓名，就不应该使用TEXT，而可以使用varchar(20)或者varchar(25)920或者25表示长度，如果再加长，考虑实际情况也没有必要。开发中应该用下面的技术使表的性能更好并且使存储空间最小10本部分内容来自： 1. 尽可能地使用最有效(最小)的类型。MySQL有很多节省磁盘空间和内存的专业化类型。 2. 如果可能使表更小，使用较小的整数类型。例如，MEDIUMINT经常比INT好一些。 3. 如果可能，声明列为NO

14、T NULL。它使任何事情更快而且你为每列节省一位。注意如果在你的应用程序中你确实需要NULL，你应该毫无疑问使用它，只是避免缺省地在所有列上有它。 4. 如果你没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。5. 每张表应该有尽可能短的主索引。这使一行的辨认容易而有效。 6. 对每个表，你必须决定使用哪种存储/索引方法。7. 只创建你确实需要的索引。索引对检索有好处但是当你需要快速存储东西时就变得糟糕。如果你主要通过搜索列的组合来存取一个表，以它们做一个索引。第一个索引部分应该是最常用的列。如果你总是使用许多列，你应该首先

15、以更多的副本使用列以获得更好的列索引压缩。 8. 如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持在一个字符列的一部分上的索引。更短的索引更快，不仅因为他们占较少的磁盘空间而且因为他们将在索引缓存中给你更多的命中率并且因此有更少磁盘寻道。9. 在一些情形下，分割一个经常被扫描进2个表的表是有益的。特别是如果它是一个动态格式的表并且它可能使一个能用来扫描后找出相关行的较小静态格式的表。4:正确使用Like在使用%通配符搜索的时候,对于%huzhangyou%的搜索是无法使用索引的,而可以采取huzhangyou%或者%huzhangyou的搜索方法,以达到使用索

16、引的目的.这点在搜索的时候一定要注意.比如:select * from employee where last_name like '%cliton%'和select * from employee where last_name like 'c%'或者select * from employee where last_name like '%c'5:插入数据优化11来自插入一个记录的时间由下列组成： 1. 连接：(3) 2. 发送查询给服务器：(2) 3. 分析查询：(2) 4. 插入记录：（1 x 记录大小） 5. 插入索引：（1 x 索引）

17、 6. 关闭：(1) 这里的数字有点与总体时间成正比。这不考虑打开表的初始开销(它为每个并发运行的查询做一次)。 表的大小以N log N (B 树)的速度减慢索引的插入。 加快插入的一些方法： i. 如果你同时从同一客户插入很多行，使用多个值表的INSERT语句。这比使用分开INSERT语句快(在一些情况中几倍)。 ii. 如果你从不同客户插入很多行，你能通过使用INSERT DELAYED语句得到更高的速度。iii. 注意，用MyISAM，如果在表中没有删除的行，能在SELECT:s正在运行的同时插入行。 iv. 当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比

18、使用很多INSERT语句快20倍。 当表有很多索引时，有可能多做些工作使得LOAD DATA INFILE更快些。使用下列过程： 1. 有选择地用CREATE TABLE创建表。例如使用mysql或Perl-DBI。 2. 执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。 3. 使用myisamchk -keys-used=0 -rq /path/to/db/tbl_name。这将从表中删除所有索引的使用。 4. 用LOAD DATA INFILE把数据插入到表中，这将不更新任何索引，因此很快。 5. 如果你有myisampack并且想要压缩表，在它上面

19、运行myisampack。 6. 用myisamchk -r -q /path/to/db/tbl_name再创建索引。这将在将它写入磁盘前在内存中创建索引树，并且它更快，因为避免大量磁盘寻道。结果索引树也被完美地平衡。 7. 执行FLUSH TABLES，或外壳命令mysqladmin flush-tables。 这个过程将被构造进在MySQL的某个未来版本的LOAD DATA INFILE。你可以锁定你的表以加速插入。mysql> LOCK TABLES a WRITE;mysql> INSERT INTO a VALUES (1,23),(2,34),(4,33);mysql

20、> INSERT INTO a VALUES (8,26),(6,29);mysql> UNLOCK TABLES;主要的速度差别是索引缓冲区仅被清洗到磁盘上一次，在所有INSERT语句完成后。一般有与有不同的INSERT语句那样夺的索引缓冲区清洗。如果你能用一个单个语句插入所有的行，锁定就不需要。锁定也将降低多连接测试的整体时间，但是对某些线程最大等待时间将上升(因为他们等待锁)。例如：thread 1 does 1000 insertsthread 2, 3, and 4 does 1 insertthread 5 does 1000 inserts如果你不使用锁定，2、3和4

21、将在1和5前完成。如果你使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。因为INSERT, UPDATE和DELETE操作在MySQL中是很快的，通过为多于大约5次连续不断地插入或更新一行的东西加锁，你将获得更好的整体性能。如果你做很多一行的插入，你可以做一个LOCK TABLES，偶尔随后做一个UNLOCK TABLES(大约每1000行)以允许另外的线程存取表。这仍然将导致获得好的性能。当然，LOAD DATA INFILE对装载数据仍然是更快的。 为了对LOAD DATA INFILE和INSERT得到一些更快的速度，扩大关键字缓冲区。6:搜索数据优化Betwe

22、en and 查询，in查询,exists查询的效率比较，实际的例子效率。1. Order by语句12ORDER BY语句决定了Oracle如何将返回的查询结果排序。Order by语句对要排序的列没有什么特别的限制，也可以将函数加入列中(象联接或者附加等)。任何在Order by语句的非索引项或者有计算表达式都将降低查询速度。仔细检查order by语句以找出非索引项或者表达式，它们会降低性能。解决这个问题的办法就是重写order by语句以使用索引，也可以为所使用的列建立另外一个索引，同时应绝对避免在order by子句中使用表达式。2. NOT我们在查询时经常在where子句使用一些逻

23、辑表达式，如大于、小于、等于以及不等于等等，也可以使用and(与)、or(或)以及not(非)。NOT可用来对任何逻辑运算符号取反。下面是一个NOT子句的例子：. where not (status ='VALID') 如果要使用NOT，则应在取反的短语前面加上括号，并在短语前面加上NOT运算符。NOT运算符包含在另外一个逻辑运算符中，这就是不等于(<>)运算符。换句话说，即使不在查询where子句中显式地加入NOT词，NOT仍在运算符中，见下例：. where status <>'INVALID' 再看下面这个例子：select * f

24、rom employee where salary<>3000; 对这个查询，可以改写为不使用NOT：select * from employee where salary<3000 or salary>3000; 虽然这两种查询的结果一样，但是第二种查询方案会比第一种查询方案更快些。第二种查询允许Oracle对salary列使用索引，而第一种查询则不能使用索引。3. IN和EXISTS有时候会将一列和一系列值相比较。最简单的办法就是在where子句中使用子查询。在where子句中可以使用两种格式的子查询。第一种格式是使用IN操作符：. where column in(

25、select * from . where .); 第二种格式是使用EXIST操作符：. where exists (select 'X' from .where .); 我相信绝大多数人会使用第一种格式，因为它比较容易编写，而实际上第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询。第二种格式中，子查询以select 'X'开始。运用EXISTS子句不管子查询从表中抽取什么数据它只查看where子句。这样优化器就不必遍历整个表而仅根据索引就可完成工作(这里假定在where语句中使用的列存在索引)。相对于IN子句来说，EXISTS使用相连子查询，构造起来要比IN子查询困难一些。通过使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以