还剩48页未读，继续阅读
本资源只提供10页预览，全部文档请下载后查看！喜欢就下载吧，查找使用更方便
文本内容:
数据库优化
（一）MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-0609:30数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行本章主要讲解了几种优化MySQL的方法，并且给出了例子记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力优化概述1让系统运行得快得最重要因素是数据库基本的设计并且还必须清楚您的系统要用来做什么，以及存在的瓶颈最常见的系统瓶颈有以下儿种磁盘搜索它慢慢地在磁盘中搜索数据块对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此加快搜索时间的方法是将数据分开存放到多个磁盘中磁盘读/写当磁盘在正确的位置上时，就需要读取数据对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据CPU周期数据存储在主内存中（或者它已经在主内存中了），这就需要处理这些数据以得到想要的结果存在多个？胴啾饶佳媛蔡坷此蹈窍拗频囊蛰亍2还孕“祛此担俣韧42皇俏侍獴？内存带宽当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素设计的局限性
1.1MySQL当使用MylSAM存储引擎时，MySQL会使用一个快速数据表锁以允许同时多个读取和一个写入这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询如果这种情况在某个表中存在，可以使用另一种表类型详情请看15MySQL StorageEngines andTable Types%MySQL可以同时在事务及非事务表下工作为了能够平滑的使用非事务表（发生错误时不能回滚），有以下几条规则所有的字段都有默认值如果字段中插入了一个“错误”的值，比如在数字类型字段中插入过大数值，那么MySQL会将该字段值置为最可能的值”而不是给出•个错误数字类型的值是0,最小或者最大的可能值字符串类型，不是空字符串就是字段所能存储的最大长度所有的计算表达式都会返回一个值而报告条件错误，例如1/0返回NULL假使t
2.id定义为NOTNULLo这种情况下，MySQL将会扫描表t1并且用tl.id的值在t2中查找记录当在t2中找到一条匹配的记录时，这就意味着t
2.id肯定不会都是NULL,就不会再在t2中查找相同id值的其他记录了也可以这么说，对于t1中的每个记录，MySQL只需要在t2中做一次查找，而不管在t2中实际有多少匹配的记录range checkedfor eachrecord indexmap:#MySQL没找到合适的可用的索引取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引如果有的话，并且使用这个索引来从表里取得记录这个过程不会很快，但总比没有任何索引时做表连接来得快UsingMySQL需耍额外的做一遍从而以排好的顺序取得记录排序程序根据连接的类型遍历所有的记录，并且将所有符合WHERE条件的记录的要排序的键和指向记录的指针存储起来这些键已经排完序了，对应的记录也会按照排好的顺序取出来详情请看”
7.
2.9How MySQLOptimizes ORDER BY”Using index字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录这种策略用于查询时的字段是一个独立索引的一部分Using temporaryMySQL需要创建临时表存储结果以完成查询这种情况通常发生在查询时包含了GROUP BY和ORDER BY子句，它以不同的方式列出了各个字段Using whereWHERE子句将用来限制哪些记录匹配了下一个表或者发送给客户端除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的Extra字段值不是Using where并且表连接类型是ALL或index时可能表示有问题如果你想要让查询尽可能的快，那么就应该注意Extra字段的值为Using和Using temporary的情况你可以通过EXPLAIN的结果中rows字段的值的乘积大概地知道本次连接表现如何它可以粗略地告诉我们MySQL在查询过程中会查询多少条记录如果是使用系统变量maxjoin_size来取得查询结果，这个乘积还可以用来确定会执行哪些多表SELECT语句详情请看”
7.
5.2Tuning ServerParameters”下面的例子展示了如何通过EXPLAIN提供的信息来较大程度地优化多表联合查询的性能假设有下面的SELECT语句，正打算用EXPLAIN来检测EXPLAIN SELECTtt.TicketNumber,tt.Timeln,tt.ProjectReference,tt.EstimatedShipDate,tt.ActualShipDate,tt.ClientID,tt.ServiceCodes,tt.RepetitivelD,tt.CurrentProcess,tt.CurrentDPPerson,tt.RecordVolume,tt.DPPrinted,et.COUNTRY,et_l.COUNTRY,do.CUSTNAMEFROM tt,et,et ASet_l dozWHERE tt.SubmitTime IS NULLAND tt.ActualPC=et.EMPLOYIDAND tt.AssignedPC=et_l.EMPLOYIDAND tt.ClientID=do.CUSTNMBR;在这个例子中，先做以下假设:要比较的字段定义如下Column ColumnTypeTablett ActualPCCHAR10tt AssignedPCCHAR10tt ClientIDCHAR10et EMPLOYIDCHAR15do CUSTNMBRCHAR15数据表的索引如下IndexTablett ActualPCttAssignedPCtt ClientIDetEMPLOYIDprimary keydoCUSTNMBRprimary keytt.ActualPC的值是不均匀分布的在任何优化措施未采取之前，经过EXPLAIN分析的结果显示如下:et ALL PRIMARY NULL NULL NULL74table typepossible_keys keykey_len refrows ExtradoALL PRIMARY NULL NULL NULL2135et_l ALL PRIMARY NULL NULL NULL74tt ALL AssignedPC,NULL NULL NULL3872ClientID,ActualPCrange checkedfor eachrecord keymap:35由于字段type的对于每个表值都是ALL,这个结果意味着MySQL对所有的表做一个迪卡尔积;这就是说，每条记录的组合这将需要花很长的时间，因为需要扫描每个表总记录数乘积的总和在这情况下，它的积是74*2135*74*3872=45,268,558,720条记录如果数据表更大的话，你可以想象一下需要多长的时间在这里有个问题是当字段定义一样的时候，MySQL就可以在这些字段上更快的是用索引对ISAM类型的表来说，除非字段定义完全一样，否则不会使用索引在这个前提下，VARCHAR和CHAR是一样的除非它们定义的长度不一致由于tt.ActualPC定义为CHAR10,et.EMPLOYID定义为CHAR05,二者长度不一致为了解决这个问题，需要用ALTER TABLE来加大Actual PC的长度从10到15个字符:mysql ALTER TABLE ttMODIFY Actual PC VARCHAR15;了再来执行一次EXPLAIN语句看看结果:table typepossible_keys keykey_len refrows ExtrattALL AssignedPC,NULL NULLNULL3872Using现在tt.ActualPC和et.EMPLOYID都是VARCHAR15ClientID,whereActualPCdo ALLPRIMARYNULLNULLNULL2135et1ALLPRIMARYNULLNULLNULL74range checkedfor eachrecord keymap:1range checkedfor eachrecord keymap:1et eq_ref PRIMARYPRIMARY15tt.ActualPC1这还不够，它还可以做的更好现在rows值乘积已经少了74倍这次查询需要用2秒钟第二个改变是消除在比较tt.AssignedPC=et_
1.EMPLOYID和tt.ClientID=do.CUSTNMBR中字段的长度不一致问题:MODIFY ClientIDVARCHAR15;mysql ALTER TABLE ttMODIFY AssignedPCVARCHAR15,table typepossible_keys keykeyjen refrows ExtraetALLPRIMARYNULLNULLNULL74tt refAssignedPC,ActualPC15et.EMPLOYID52Using现在EXPLAIN的结果如下:ClientID,whereActualPCet1eq_ref PRIMARYPRIMARY15tt.AssignedPC1do eq_ref PRIMARYPRIMARY15tt.ClientID1这看起来己经是能做的最好的结果了遗留下来的问题是，MySQL默认地认为字段tt.ActualPC的值是均匀分布的，然而表tt并非如此幸好，我们可以很方便的让MySQL分析索引的分布:mysql ANALYZE TABLE tt;到此为止，表连接已经优化的很完美了，EXPLAIN的结果如下:rows ExtrattALLAssignedPCNULLNULLNULL3872Usingtable typepossible_keys keykeyjen refClientID,whereActual PCeteq_ref PRIMARYPRIMARY15tt.ActualPC1et1eq_ref PRIMARYPRIMARY15tt.AssignedPC1do eq_ref PRIMARYPRIMARY15tt.ClientID1请注意，EXPLAIN结果中的rows字段的值也是MySQL的连接优化程序大致猜测的，请检查这个值跟真实值是否基本一致如果不是，可以通过在SELECT语句中使用STRAIGHT.JOIN来取得更好的性能，同时可以试着在FROM分句中用不同的次序列出各个表数据库优化三MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1110:05这个章节讲述了优化程序如何处理WHERE子句例子中使用了SELECT语句，但是在DELETE和UPDATE语句中对WHERE子句的优化是一样的注意，关于MySQL优化的工作还在继续，因此本章节还没结束MySQL做了很多优化工作，而不仅仅是文档中提到的这些
1.估算查询性能在大多数情况下，可以通过统计磁盘搜索次数来估算查询的性能对小表来说，通常情况下只需要搜索一次磁盘就能找到对应的记录因为索引可能已经缓存起来了对大表来说，大致可以这么估算，它使用B树做索引，想要找到一条记录大概需要搜索的次数为logrow_count/logindex_block_length/3*2/indexjength+data_pointer_length+1o在MySQL中，一个索引块通常是1024bytes,数据指针通常是4bytes对于一个有500,000条记录、索引长度为3bytesmediuminteger的表来说，根据上面的公式计算得到需要做log500,000/log1024/3*2/3+4+1=4次搜索这个表的索引大概需要500,000*7*3/2=
5.2MB的存储空间假定典型的索引缓冲区的2/3,因此应该会有更多的索引在内存中，并且可能只需要1到2次调用就能找到对应的记录对于写来说，大概需要4次甚至更多搜索才能找到新的索引位置，更新记录时通常需要2次搜索请注意，前面的讨论中并没有提到应用程序的性能会因为log N的值越大而下降只要所有的东西都能由操作系统或者SQL服务器缓存起来，那么性能只会因为数据表越大而稍微下降当数据越来越大之后，就不能全部放到缓存中去了，就会越来越慢了，除非应用程序是被磁盘搜索约束的它跟随着的log N值增加而增加为了避免这种情况，可以在数据量增大以后也随着增大索引缓存容量对MylSAM类型表来说，索引缓存容量是由系统变量key_buffer_size控制的
2.SELEC.查询的速度通常情况下，想要让一个比较慢.SELEC....WHER.查询变得更快的第一件事就是，先检查看看是否可以增加索引所有对不同表的访问都通常使用索引可以使.EXPLAL语句来判.SELEC.使用了哪些索引详情请看”
7.
4..Ho.MySQ.Use.lndexes”和“
7.
2..EXPLAI.Synta.Ge.lnformatio.Abou..SELECT”以下是几个常用的提高MylSAM表查询速度的忠告想要让MySQL将查询优化的速度更快些，可以在数据表已经加载完全部数据后执行行ANALYZE TABLE或运行myisamchk-analyze命令它更新了每个索引部分的值，这个值意味着相同记录的平均值对于唯一索引来说，这个值则一直都是1MySQL就会在当你使用基于一个非恒量表达式的两表连接时，根据这个值来决定使用哪个索引想要查看结果，可以在分析完数据表后运行SHOW INDEXFROM tbl_name查看Cardinality字段的值myisamchk-description-verbose显示了索引的分布信息想要根据一个索引来排序数据，可以运行myisamchk-sort-index-sort-records=1如果想要在索引1上做排序这对于有一个唯一索引并且想根据这个索引的顺序依次读取记录的话来说是一个提高查询速度的好办法不过要注意的是，第一次在一个大表上做排序的话将会耗费很长时间
3.MySQL如何优.WHER.子句这个章节讲述了优化程序如何处理WHERE子句例子中使用了SELECT语句，但是在DELETE和UPDATE语句中对WHERE子句的优化是一样的注意，关于MySQL优化的工作还在继续，因此本章节还没结束MySQL做了很多优化工作，而不仅仅是文档中提到的这些MySQL的一些优化做法如下去除不必要的括号a AND b ANDc ORa ANDb ANDc ANDd-a ANDb ANDc ORa ANDb ANDc ANDd展开常量a-b5ANDb=c ANDa=5去除常量条件在展开常量时需要B=5AND B=5OR B=6AND5=5OR B=7AND5=6-B=5OR B=6常量表达示在索引中只计算一次在单独一个表上做COUNT*而不使用WHERE时，对于MylSAM和HEAP表就会直接从表信息中检索结果在单独一个表上做任何表NOT NULL达式查询时也是这样做预先探测无效的常量表达式MySQL会快速探测一些不可能的SELECT语句并且不返回任何记录当没用GROUP BY或分组函数时，HAVING和WHERE合并COUNT,MIN等也是如此为表连接中的每个表构造一个简洁的WHERE语句，以得到更快的WHERE计算值并且尽快跳过记录查询中所有的常量表都会比其他表更早读取一个常量表符合以下几个条件空表或者只有一条记录与在一个UNIQUE索引、或一个PRIMARY KEY的WHERE子句一起使用的表，这里所有的索引部分和常数表达式做比较并且索引部分被定义为NOT NULLo以下的几个表都会被当成常量表SELECT*FROM tWHERE primary_key=1;SELECT*FROM t1,t2WHERE t
1.primary_key=1AND t
2.primary_key=t
1.id;MySQL会进各种可能找到表连接最好的连接方法如果在ORDER BY和GROUP BY子句中的所有字段都来自同一个表的话，那么在连接时这个表就会优先处理如果有ORDER BY子句和一个不同的GROUP BY子句，或者如果ORDER BY或GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了如果使用SQL_SMALL_RESULT,MySQL就会使用内存临时表了所有的表索引都会查询，最好的情况就是所有的索引都会被用到，除非优化程序认为全表扫描的效率更高同时:数据表扫描是基于判断最好的索引范围超过数据表的30%现在，优化程序复杂多了，它基于对一些附加因素的估计，例如表大小，记录总数，I/O块大小，因此就不能根据一个固定的百分比来决定是选择使用索引还是直接扫描数据表在某些情况下，MySQL可以直接从索引中取得记录而无需查询数据文件如果所有在索引中使用的字段都是数字类型的话，只需要用索引树就能完成查询每条记录输出之前，那些没有匹配HAVING子句的就会被跳过以下几个查询速度非常快SELECT COUNT*FROM tbl_name;SELECT MINkey_part1,MAXkey_part1FROM tbl_name;SELECT MAXkeyjDart2FROM tbl_nameWHERE keyjDartl=constant;SELECT...FROM tbl_nameORDER BY key_part1,key_part2,...LIMIT10;SELECT...FROM tbl_nameORDER BYkey_part1DESC,key_part2DESC,...LIMIT10;以下几个查询都是使用索引树，假使那些索引字段都是数字型SELECT key_part1,key_part2FROM tbl_name WHERE key_part1=val;SELECT COUNT*FROM tbl_nameWHERE key_part1=val1AND key_part2=val2;SELECT key_part2FROM tbl_name GROUP BYkey_part1;以下几个查询使用索引来取得经过顺序排序后的记录而无需经过独立的排序步骤SELECT...FROM tbl_nameORDER BYkey_part1,key_part2,...;SELECT...FROM tbl_name；ORDER BYkey_part1DESC,key_part2DESC,…
4.MySQ.如何优Q.子句Index Merge方法用于使用ref,ref_or_null,或range扫描取得的记录合并起来放到一起作为结果这种方法在表条件是或条件ref,ref_or_null,或range,并且这些条件可以用不同的键时采用“join”类型的优化是从MySQL
5.
0.0开始才有的，代表者在索引的性能上有着标志性的改进，因为使用老规则的话,数据库最多只能对每个引用表使用一个索引在EXPLAIN的结果中，这种方法在type字段中表现为index.mergeo这种情况下，key字段包含了所有使用的索引列表，并且keyjen字段包含了使用的索引的最长索引部分列表例如SELECT*FROM tbl_name WHERE key_part1=10OR key_part2=20;SELECT*FROM tbl_nameWHERE key_part1=10OR key_part2=20AND non_key_part=30;SELECT*FROM t1,t2WHERE tl.keyl IN1,2OR t
1.key2LIKE value%*AND t
2.key1=t
1.some_col;SELECT*FROM t1,t2WHERE t
1.key1=1AND t
2.key1=t
1.some_col OR t
2.key2=t
1.some_col2;
5.MySQ.如何优.I.NULLMySQL在col_name IS NULL时做和col_name=constant_value一样的优化例如，MySQL使用索引或者范围来根据IS NULL搜索NULLoSELECT*FROM tbl_name WHERE key„col ISNULL;SELECT*FROM tbl_name WHEREkey_col=NULL;SELECT*FROM tbl_nameWHERE key_col=const1OR key_col=const2OR key_col ISNULL;如果一个WHERE子句包括了一个col_name ISNULL条件，并且这个字段声明为NOT NULL,那么这个表达式就会被优化当字段可能无论如何都会产生NULL值时，就不会再做优化了;例如，当它来自一个LEFT JOIN中右边的一个表时MySQL
4.
1.1或更高会对连接col_name=expr AND col_name ISNULL做额外的优化，常见的就是子查询EXPLAIN当优化起作用时会显示ref_or_nullo优化程序会为任何索引部分处理IS NULLo以下几个例子中都做优化了，假使字段a和表t2中b有索引了:SELECT*FROM t1WHERE t
1.a=expr ORt
1.a ISNULL;SELECT*FROM t1,t2WHERE t
1.a=t
2.a ORt
2.a ISNULL;SELECT*FROM t1,t2WHERE t
1.a=t
2.a ORt
2.a ISNULL AND t
2.b=t
1.b;SELECT*FROM t1,t2WHERE t
1.a=t
2.a ANDt
2.b=t
1.b ORt
2.b ISNULL;SELECT*FROM t1,t2WHERE t
1.a=t
2.a ANDt
2.a ISNULL AND...；ORt
1.a=t
2.a ANDt
2.a ISNULL AND...ref_or_null首先读取引用键，然后独立扫描键值为NULL的记录请注意，优化程序只会处理一个ISNULL级别下面的查询中，MySQL只会使用键来查询表达式t
1.a=t
2.aANDt
2.a ISNULL而无法使在b上使用索引部分SELECT*FROM t1,t2WHERE t1,a=t
2.a ANDt
2.a ISNULLOR t
1.b=t
2.b ANDt
2.b ISNULL;
6.MySQ.如何优.DISTINCT在很多情况下，DISTINCT和ORDER BY一起使用时就会创建一个临时表注意，由于DISTINCT可能需要用到GROUP BY,就需要明白MySQL在ORDER BY或HAVING子句里的字段不在选中的字段列表中时是怎么处理的详情请看”
13.
9.3GROUP BYwith HiddenFields0当LIMIT row_count和DISTINCT一起使用时，MySQL在找到row_count不同记录后就会立刻停止搜索了如果没有用到来自查询中任何表的字段时，MySQL在找到第一个匹配记录后就会停止搜索这些没没用到的表了在下面的情况中，假使t1在t2前就使用了可以通过EXPLAIN分析知道，MySQL就会在从t2中找到第一条记录后就不再读t2了为了能和中t1的任何特定记录匹配SELECT DISTINCTt
1.a FROM t1,t2where t
1.a=t
2.a;
7.MySQ如何优・LEF.JOl.RIGH.JOINA LEFT JOIN Bjoin_condition在MySQL中实现如下表B依赖于表A以及其依赖的所有表表A依赖于在LEFT JOIN条件中的所有表除了BoLEFT JOIN条件用于决定如何从表B中读取记录了换句话说,WHERE子句中的任何条件都对此不起作用所有标准的连接优化都会执行，例外的情况是有一个表总是在它依赖的所有表之后被读取如果这是一个循环的依赖关系，那么MySQL会认为这是错误的所有的标准WHERE优化都会执行如果A中有一条记录匹配了WHERE子句，但是B中没有任何记录匹配ON条件，那么就会产生一条B记录,它的字段值全都被置为NULLO如果使用LEFT JOIN来搜索在一些表中不存在的记录，并且WHERE部分中有检测条件col_name ISNULL,col_name字段定义成NOT NULL的话,MySQL就会在找到一条匹配LEFT JOIN条件的记录用于和特定的索引键做联合后停止搜索了RIGHT JOIN的实现和LEFT JOIN类似，不过表的角色倒过来了连接优化程序计算了表连接的次序表读取的顺序是由LEFT JOIN强行指定的，而且使用STRAIGHT_JOIN能帮助连接优化程序更快地执行，因为这就会有更少的表排队检查了注意，这里是指如果你执行下面这种类型的查询后，MySQL就会对b做一次全表扫描，因为LEFT JOIN强制要求了必须在读d之前这么做SELECT*FROM a,b LEFT JOIN cON c.key=a.key LEFT JOIN dON d.key=a.keyWHERE b.key=d.key;解决这种情况的方法是按照如下方式重写查询SELECT*FROM b,a LEFT JOIN cON c.key=a.key LEFT JOIN dON d.key=a.keyWHERE b.key=d.key;从
4.
0.14开始，MySQL做如下LEFT JOIN优化如果对产生的NULL记录WHERE条件总是假，那么LEFTJOIN就会变成一个普通的连接例如，下面的查询中如果t
2.column1的值是NULL的话,WHERE子句的结果就是假了SELECT*FROM t1LEFTJOINt2ON columnlWHERE t
2.column2=5;因此，这就可以安全的转换成一个普通的连接查询SELECT*FROM t1,t2WHERE t
2.column2=5ANDt
1.columnl=t
2.columnl;这查询起来就更快了，因为如果能有一个更好的查询计划的话，MySQL就会在t1之前就用到t2了想要强行指定表顺序的话，可以使用STRAIGHT_JOINoXX Ultra320SCSI通道（向下兼容Ultra160）
1.根据索引键读取记录，或者扫描数据表那些无法匹配WHERE分句的记录都会被略过
2.在缓冲中每条记录都用一个对存储了2个值索引键及记录指针缓冲的大小依据系统变量sort_buffer_size的值而定
3.当缓冲慢了时，就运行qsort快速排序并将结果存储在临时文件中将存储的块指针保存起来如果所有的‘对值都能
4.执行上面的操作，直到所有的记录都读取出来了保存在缓冲中，就无需创建临时文件了
5.做一次多重合并，将多达MERGEBUFF
（7）个区域的块保存在另一个临时文件中重复这个操作，直到所有在第一个文件的块都放到第二个文件了
6.重复以上操作，直到剩余的块数量小于MERGEBUFF2
（15）o在最后一次多重合并时，只有记录的指针（排序索引键的最后部分）写到结果文件中去
1.通过读取结果文件中的记录指针来按序读取记录想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中缓冲的大小由系统变量read_rnd_buffer_size的值而定这个步骤的代码在源文件sql/records.cc中
2.这个逼近算法的一个问题是，数据库读取了2次记录一次是估算WHERE分句时，第二次是排序时尽管第一次都成功读取记录了（例如，做了一次全表扫描），第二次是随机的读取（索引键已经排好序了，但是记录并没有）
3.在MySQL
4.1及更新版本中，优化算法用于记录中不只包括索引键值和记录的位置，还包括查询中要求的字段这么做避免了需要2次读取记录改进的算法做法大致如下
4.跟以前一样，读取匹配WHERE分句的记录
5.相对于每个记录，都记录了一个对应的；元组信息信息，包括索引键值、记录位置、以及查询中所需要的所有字段
6.根据索引键对‘元组信息进行排序按序读取记录，不过是从已经排序过的‘元组列表中读取记录，而非从数据表中再读取一次使用改进后的算法相比原来的，‘元组‘比对需要占用更长的空间，它们很少正好适合放在排序缓冲中（缓冲的大小是由sort_buffer_size的值决定的）因此，这就可能需要有更多的I/O操作，导致改进的算法更慢为了避免使之变慢，这种优化方法只用于排序元组中额外的字段的大小总和超过系统变量max_length_for_sort_data的情况（这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载）•想要提高ORDER BY的速度，首先要看MySQL能否使用索引而非额外的排序过程如果不能使用索引,可以试着遵循以下策略•增力口sort_buffer_size的值•增力n read_rnd_buffer_size的值修改tmpdir,让它指向一个有很多剩余空间的专用文件系统如果使用MySQL
4.1或更新，这个选项允许有多个路径用循环的格式各个路径之间在Unix上用冒号分隔开来，在Windows,NetWare以及OS/2上用分号（；）可以利用这个特性将负载平均分摊给几个目录注意这些路径必须是分布在不同物理磁盘上的目录，而非在同一个物理磁盘上的不同目录默认情况下，MySQL也会对所有.GROUE.col
1.col
2.…查询做排序，QRDEE.col
1.col
2.…查询一样如果显式地包含一个有同样字段列表.ORDE.B.分句，MySQL优化它的时候并不会损失速度，因为排序总是会发生如果一个查询中包.GROU.BY,但是想要避免对结果排序的开销，可以通过使QRDE.B.NUL.来取消排序例如INSERT INTOtooSELECT a,COUNT（*）FROM barGROUP BYa ORDER BY NULL;如何优化2MySQL LIMIT这些规则隐含的意思是，不能使用MySQL来检查字段内容相反地，必须在存储到数据库前在应用程序中来检查详情请看”
1.
8.6How MySQLDeals withConstraints和
14.
1.4INSERT Syntax%应用设计的可移植性
1.2由于各种不同的数据库实现了各自的SQL标准，这就需要我们尽量使用可移植的SQL应用查询和插入操作很容易就能做到可移植，不过由于更多的约束条件的要求就越发困难想要让一个应用在各种数据库系统上快速运行，就变得更困难了为了能让一个夏杂的应用做到可移植，就要先看这个应用运行于哪种数据库系统之上，然后看这些数据库系统都支持哪些特性每个数据库系统都有某些不足也就是说，由于设计上的一些妥协，导致了性能上的差异可以用MySQL的crash-me程序来看选定的数据库服务器上可以使用的函数，类型，限制等crash-me不会检查各种可能存在的特性，不过这仍然是合乎情理的理解，大约做了450次测试一个crash-me的信息类型的例子就是，它会告诉您如果想使用Informix或DB2的话，就不能使字段名长度超过18个字符crash-me程序和MySQL基准使每个准数据库都实现了的可以通过阅读这些基准程序是怎么写的，自己就大概有怎样做才能让程序独立于各种数据库这方面的想法了这些程序可以在MySQL源代码的sql-bench目录下找到他们大部分都是用Perl写的，并且使用DBI接口由于它提供了独立于数据库的各种访问方式，因此用DBI来解决各种移植性的问题想要看.crash-m.的结果，可以访问.com/tech-resources/crash-me.php.访.可以看到基准的结果如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法例如，MySQL对于MylSAM类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题作为Oracle来说，它在访问刚刚被更新的记录时有很大的问题（直到结果被刷新到磁盘中）事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展由于C++在很多系统上都可以使用，因此使用C++作为数据库的基类结果很合适如果使用了某些数据库独有的特定功能（比如REPLACE语句就只在MySQL中独有），这就需要通过编写替代方法来在其他数据库中实现这个功能尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能在MySQL中，可以在查询语句中使用/*!*/语法来增加MySQL特有的关键字然而在很多其他数据库中，/**/却被当成了注释（并且被忽略）如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果,这可能会有更高的性能通过让旧数据在一定时间后过期，来合理的更新缓存这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率一个实现应用层缓存的可选方案是使用MySQL的查询缓存（query cache）启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用它大大简化了应用程序，详情请看”
5.11The MySQLQuery Cached我们都用来做什么
1.3MySQL在一些情况下，MySQL在碰到一个使用LIMIT row_count但没使用HAVING的查询时会做不同的处理:如果只是用LIMIT来取得很少的一些记录，MySQL有时会使用索引，但是更通常的情况是做一个全表扫描如果LIMIT row_count和ORDER BY一起使用，则MySQL在找到row_count条记录后就会停止排序了，而非对整个表进行排序当LIMIT row_count和DISTINCT一起联合起来时，MySQL在找到row_count条唯一记录后就不再搜索了在某些情况下，GROUP BY可以通过按照顺序读取索引键来实现（或者在索引键上做排序）并且计算累计信息直到索引键改变了在这种情况下，LIMIT row_count不会计算任何非必须的GROUP BY值一旦MySQL将请求的记录全数发送给客户端后，它就中止查询除非使用了SQL_CALC_FOUND_ROWScLIMIT0总是返回一个空的结果集这对于检查查询或者取得结果字段的类型非常有用当服务器使用临时表来处理查询，贝U LIMITrow_count可以用来计算需要多少空间如何避免全表扫描3如果MySQL需要做一次全表扫描来处理查询时，在EXPLAIN的结果中type字段的值是ALL在以下几种条件下，MySQL就会做全表扫描数据表实在太小了，做一次全表扫描比做索引键的查找来得快多了当表的记录总数小于10旦记录长度比较短时通常这么做没有合适用于ON或WHERE分句的索引字段让索引字段和常量值比较,MySQL已经计算（基于索弓I树）到常量覆盖了数据表的很大部分，因此做全表扫描应该会来得更快详情请看“724How MySQLOptimizes WHEREClauseso通过其他字段使用了一个基数很小（很多记录匹配索引键值）的索引键这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快对于小表来说，全表扫描通常更合适但是对大表来说，尝试使用以下技术来避免让优化程序错误地选择全表扫描执行ANALYZE TABLEtbl_name更新要扫描的表的索引键分布使用FORCE INDEX告诉MySQL,做全表扫描的话会比利用给定的索引更浪费资源SELECT*FROM t1,t2FORCE INDEX（index_for_column）WHEREt
1.coLname=t
2.coLname;启动mysqld时使用参数一max-seeks-for-key=1000或者执行SET max_seeks_for_key=1000来告诉优化程序，所有的索引都不会导致超过1000次的索引搜索请查看章节”
5.
2.3Server SystemVariableso加速4INSERT插入一条记录花费的时间由以下几个因素决定，后面的数字大致表示影响的比例连接
（3）发送查询给服务器
（2）解析查询
（2）插入记录（1x记录大小）插入索引（1x索引数量）关闭⑴这里并没有考虑初始化时打开数据表的开销，因为每次运行查询只会做这么一次如果是B-tree索引的话，随着索引数量的增加，插入记录的速度以log N的比例下降可以使用以下几种方法来提高插入速度如果要在同一个客户端在同一时间内插入很多记录，可以使用INSERT语句附带有多个VALUES值这种做法比使用单一值的INSERT语句快多了（在一些情况下比较快）如果是往一个非空的数据表里增加记录，可以调整变量bulk_insert_buffer_size的值使之更快如果要从不同的客户端中插入大量记录，使用INSERT DELAYED语句也可以提高速度对MylSAM而言，可以在SELECT语句正在运行时插入记录，只要这时候没有正在删除记录想要籽一个文本文件加载到数据表中，可以使用LOAD DATAINFILEo这通常是使用大量INSERT语句的20倍O通过一些额外的工作，就可能让LOAD DATAINFILE在数据表有大量索引的情况下运行的更快步骤如下O用CREATE TABLE随便创建一个表O执行FLUSH TABLES语句或mysqladmin flush-tables命令O执行myisamchk-keys-used=0-rq/path/to/db/tbl_name命令，删掉数据表的所有索引O执行LOAD DATAINFILE,数据插入到表中，由于无需更新表索引，因此这将非常快O如果将来只是读取改表，运行myisampack让数据表变得更小点详情查看”
15.
1.
3.3Compressed TableCharacteristicsoO运行myisamchk-r-q/path/to/db/tbl_name重建索引创建的索引树在写入磁盘前先保存在内存中，这省去了磁盘搜索，因此速度快多了重建后的索引树分布非常均衡O执行FLUSH TABLES语句或mysqladmin flush-tables命令注意，LOAD DATAINFILE将数据插入一个空表时，也会做前接优化;主耍的不同在于运行myisamchk会分配更多的临时内存用于创建索引，而执行LOAD DATAINFILE命令则是让数据库服务器分配内存用于重建索弓I从MySQL
4.0起，可以运行ALTER TABLEtbl_name DISABLEKEYS来代替myisamchk-keys-used=O-rq/path/to/db/tbl_name,运行ALTER TABLEtbl_name ENABLEKEYS代替myisamchk-r-q/path/to/db/tbl_nameo这么做就可以省去FLUSH TABLES步骤可以在锁表后，一起执行几个语句来加速INSERT操作:LOCK TABLESa WRITE;INSERT INTOa VALUES1,23,2,34,4,33;INSERT INTOa VALUES8,26,6,29;UNLOCK TABLES;这对性能提高的好处在于直到所有的INSERT语句都完成之后，索引缓存一次性刷新到磁盘中通常情况是，多有少次INSERT语句就会有多数次索引缓存刷新到磁盘中的开销如果能在一个语句中一次性插入多个值的话，显示的锁表操作也就没必要了对事务表而言，用BEGIN/COMMIT代替LOCK TABLES来提高速度锁表也回降低多次连接测试的总时间，尽管每个独立连接为了等待锁的最大等待时间也会增加例如Connection1does1000insertsConnections2,3,and4do1insertConnection5does1000inserts如果没有锁表，则连接2,3,4会在1,5之前就做完了如果锁表了，则连接2,3,4可能在1,5之后才能完成，但是总时间可能只需要40%MySQL的INSERT,UPDATE,DELETE操作都非常快，不过在一个语句中如果有超过5个插入或者更新时最好加锁以得到更好的性能如果要一次性做很多个插入，最好是在每个循环大约1000次的前后加上LOCKTABLES和UNLOCK TABLES,从而让其他进程也能访问数据表;这么做性能依然不错INSERT总是比LOAD DATAINFILE插入数据来得慢，因为二者的实现策略有着分明的不同想要让MylSAM表更快，在LOAD DATAINFILE和INSERT时都可以增加系统变量key_buffer_size的值力口速5UPDATEUPDATE语句的优化和SELECT一样，只不过它多了额外的写入开销写入的开销取决于要更新的记录数以及索引数如果索引没有发生变化，则就无需更新另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做如果锁表了，那么同时做很多次更新比分别做更新来得快多了注意，如果是在MylSAM表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分如果经常做这个，那么偶尔做一次OPTIMIZE TABLE就显得非常重要了详情请看”
14.525OPTIMIZE TABLESyntax”加速6DELETE删除单个记录的时间和它的索引个数几乎成正比想更快地删除记录，可以增加索引键的缓存详情请看”
7.
5.2Tuning ServerParameters%如果想要删除数据表的所有记录，请使用TRUNCATE TABLEtbl_name而不是DELETE FROM tbl_name07其他优化点子本章节列出了一些改善查询处理速度的其他点子使用永久连接到数据库，避免连接的开销如果需要初始化很多连接，而又不能用永久连接，那么可以修改变量thread_cache_size的值，详情请看752Tuning ServerParameters总是检查杳询是否利用了表中已有的索引在MySQL中，可以用EXPLAIN语句来分析详情请看”
7.
2.1EXPLAIN SyntaxGetInformation Abouta SELECT尽量不要在经常需要更新的MylSAM表上用太过复杂的SELECT语句，这是为了避免在读和写之间争夺锁在MylSAM表中，如果没有正在删除记录，则可以在其他查询正在读取数据的同时插入记录如果这种情况十分重要，那么就要尽量在表没有删除记录时才使用表另一个可能的办法就是在删除一大堆记录之后执行OPTIMIZE TABLE语句如果总是需要按.exprl.expr
2....的顺序取得记录，那么请使.ALTE.TABL….ORDEBexph.expr
2.…修改表通过这种方法扩充修改表之后，就可能获得更高的性能表现.在一些情况下，让一个字段类型是hashed,它基于其他字段信息如果这个字段比较短而且基本上都是唯一值的话，那么就可能会比在几个字段上使用一个大索引来得更快，很简单的就能使用这样的额外字段，如下SELECT*FROM tbl.name WHEREhash_col=MD5CONCATcol1,col2AND colInconstant AND col2=constant,;如果MylSAM表经常大量修改，那么要尽量避免修改所有的变长字段VARCHAR,BLOB,TEXT尽管表中只有一个变长字段，它也会采用动态记录格式的详情请看15MySQL StorageEngines andTable Typeso通常情况下，当数据表记录变大、之后，将表拆分成儿个不同的表并没有多大用处访问一条记录是最大的性能点在于磁盘搜索时找到记录的第一个字节上只要找到记录的位置后，现在的大部分磁盘对于大部分的应用程序来说都能很快的读取到记录将MylSAM表拆分成多个唯一有关系的情况是，数据表中动态格式的字段见上就可以被修改成固定大小的记录，或者需要频繁的扫描表，但是却不需要读取出大部分的字段详情请看15MySQL StorageEngines andTable Typeso如果需要频繁的对一个表做基于很多字段信息的统计信息的话，那么可能新建一个表来存储这些实时更新的统计结果会更好类似下面的更新就会非常快了UPDATE tbl_name SETcount_col=count_col+1WHEREkey_col=constant;如果只需要表级锁多个读/一个写，那么采用MylSAM存储引擎就非常重要了，例如MylSAM和ISAM表这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用如果需要从很大的日志表中搜集统计信息的话，可以用摘要表来代替扫描整个日志表维护摘要表比保持实时的统计信息来得更快当事情发生变化时比如商业决策，重新建里摘要表比修改运营中的应用程序快多了如果可能，最好是分类报告实时还是统计的，报告所需要的数据只需要来自摘耍表，摘要表的信息则是周期的从实时数据中产生应该认识到一个优点就是字段有默认值当要插入的值和默认值不一致时才需要明确指定这就省去了MySQL需要来提高插入速度这步了在一些情况下，将数据组装存储在BLOB类型字段中更方便那么在应用程序中就需要增加额外的命令来组装和拆开BLOB字段中的值，不过这么做在一些时候就可以节省很多存储开销这在数据无需遵从记录-和-字段格式的表结构是很实用通常地，应该保存所有的冗余数据在数据库原理中叫做“第三范式”然而，为了能取得更高的效率复制一些信息或者创建摘要表也是划算的存储过程或者UDFs（用户定义函数）的方式在执行一些任务时可能性能更高尽管如此，当数据库不支持这些特性时:还是有其他的替代方法可以达到R的，即使它们有点慢可以从查询缓存或应答中取得结果，然后将很多次的插入及更新操作放在一起做如果数据库支持表锁（如MySQL和ORACLE）,那么这就可以确保索引缓存在所有的更新操作之后只需要刷新一次当不需要直到数据什么时候写入表中时，可以用INSERT DELAYED这就会提高速度，因为多条记录同时在一起做一次磁盘写入操作当想让SELECT语句的优先级比插入操作还高时，用INSERT LOW_PRIORITYo用SELECT HIGH_PRIORITY来使检索记录跳过队列，也就是说即使有其他客户端正要写入数据，也会先让SELECT执行完在一条INSERT语句中采用多重记录插入格式（很多数据库都支持）用LOAD DATAINFILE来导入大量数据，这比INSERT快用AUTO」NCREMENT字段来生成唯一值定期执行OPTIMIZE TABLE防止使用动态记录格式的MylSAM表产生碎片采用HEAP表，它可能会提高速度详情请看”
15.
1.3MylSAM TableStorage Formats”正常的WEB服务器配置中，图片文件最好以文件方式存储，只在数据库中保存文件的索引信息这么做的原因是，通常情况下WEB服务器对于文件的缓存总是做的比数据库来得好，因此使用文件存储会让系统更容易变得更快对于频繁访问的不是很重耍的数据，可以保存在内存表中，例如对那些web客户端不能保存cookies时用于保存最后一次显示的标题等信息在不同表中值相同的字段应该将它们声明为一样的类型在MySQL
3.23之前，不这么做的话在我连接时就会比较慢让字段名尽可能简单，例如，在一个叫做customer的表中，用name来代替customer_name作为字段名为了让字段名在其他数据库系统中也能移植，应该保持在18个字符长度以内如果需要真正的高速，建议看看各种数据库服务器支持的底层数据存储接口之间的区别例如，通过直接访问MySQL的MylSAM存储引擎，会比通过其他的SQL接口快2-5倍这要求数据必须和应用程序在同一个服务器上，并且它通常只被一个进程访问（因为外部文件锁确实慢）只用一个进程就可以消除在MySQL服务器上引入底层的MylSAM指令引发的问题了（这容易获得更高性能，如果需要的话）由于数据库接口设计的比较细心，就很容易支持这种优化方式了如果使用数字型数据的话，在很多情况下想要访问数据库（使用在线连接）的信息会比采用文本文件来得快由于数字型信息相比文木文件在数据库中存储的更加紧凑，因此访问时只需要更少的磁盘搜索而且在应用程序中也可以节省代码，因为无需解析文本文件以找到对应的行和字段数据库复制对一些操作会有性能上的益处可以将客户端从多个复制服务器上取得数据，这就能将负载分摊了为了避免备份数据时会让主服务器变慢，还可以将备份放在从服务器上详情请看“6Replication inMySQL”定义MylSAM表时增加选项DELAY_KEY_WRITE=1,这样的话就会另索引更新更快，因为只有等到数据表关闭了才会刷新磁盘不过缺点是可能会在数据表还打开时服务器被杀死，可以使用参数-myisam-recover来保证数据的安全，或者在数据库重启前运行myisamchk命令（尽管如此，在这种情况下，使用DELAY_KEY_WRITE的话也不会丢失任何东西，因为索引总是可以从数据中重新生成）数据库优化
（五）锁MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1214:57当前MySQL已经支持ISAM,MylSAM,MEMORY（HEAP）类型表的表级锁了，BDB表支持页级锁，InnoDB表支持行级锁很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁锁机制1当前MySQL已经支持ISAM,MylSAM,MEMORY（HEAP）类型表的表级锁了，BDB表支持页级锁，InnoDB表支持行级锁很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁想要决定是否需要采用一个支持行级锁的存储引擎，就要看看应用程序都要做什么，其中的查询、更新语句是怎么用的例如，很多的web应用程序大量的做查询，很少删除，主要是基于索引的更新，只往特定的表中插入记录采用基本的MySQL MylSAM表就很合适了MySQL中对表级锁的存储引擎来说是释放死锁的避免死锁可以这样做到在任何查询之前先请求锁，并且按照请求的顺序锁表MySQL中用于WRITE（写）的表锁的实现机制如下如果表没有加锁，那么就加一个写锁否则的话，将请求放到写锁队列中MySQL中用于READ（读）的表锁的实现机制如下如果表没有加写锁，那么就加一个读锁否则的话，将请求放到读锁队列中当锁释放后，写锁队列中的线程可以用这个锁资源，然后才轮到读锁队列中的线程这就是说，如果表里有很多更新操作的话，那么SELECT必须等到所有的更新都完成了之后才能开始从MySQL
3.
323.7（在Windows上是
3.
23.25）以后，在MylSAM表中只要没有冲突的INSERT操作，就可以无需使用锁表自由地并行执行INSERT和SELECT语句也就是说，可以在其它客户端正在读取MylSAM表记录的同时时插入新记录如果数据文件的中间没有空余的磁盘块的话，就不会发生冲突了，因为这种情况下所有的新记录都会写在数据文件的末尾（当在表的中间做删除或者更新操作时，就可能导致空洞）当空洞被新数据填充后，并行插入特性就会自动重新被启用了如果想要在一个表上做大量的INSERT和SELECT操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里可以用以下命令实现mysql LOCK TABLES realjableWRITE,insertjable WRITE;mysql INSERTINTO realjableSELECT*FROM insertjable;mysql TRUNCATETABLE insertjable;mysql UNLOCK TABLES;InnoDB使用行级锁，BDB使用页级锁对于InnoDB和BDB存储引擎来说，是可能产生死锁的这是因为InnoDB会自动捕获行锁，BDB会在执行SQL语句时捕获页锁的，而不是在事务的开始就这么做行级锁的优点有在很多线程请求不同记录时减少冲突锁事务回滚时减少改变数据使长时间对单独的一行记录加锁成为可能行级锁的缺点有比页级锁和表级锁消耗更多的内存当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源当需要频繁对大部分数据做GROUP BY操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了表级锁在下列几种情况下比页级锁和行级锁更优越很多操作都是读表在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时UPDATE tbl_name SETcolumn=value WHEREunique_key_col=key_value;DELETE FROM tbl_name WHEREunique_key_col=key_value;SELECT和INSERT语句并发的执行，但是只有很少的UPDATE和DELETE语句很多的扫描表和对全表的GROUP BY操作，但是没有任何写表表级锁和行级锁或页级锁之间的不同之处还在于将同时有一个写和多个读的地方做版本（例如在MySQL中的并发插入）也就是说，数据库/表支持根据开始访问数据时间点的不同支持各种不同的试图其它名有时间行程，写复制，或者是按需复制按需复制在很多情况下比页级锁或行级锁好多了尽管如此，最坏情况时还是比其它正常锁使用了更多的内存可以用应用程序级锁来代替行级锁，例如MySQL中的GET_LOCK和RELEASE_LOCK但它们是劝告锁原文Theseare advisorylocks,因此只能用于安全可信的应用程序中锁表2为了能有快速的锁,MySQL除了InnoDB和BDB这两种存储引擎外，所有的都是用表级锁而非页、行、列级锁对于InnoDB和BDB表,MySQL只有在指定用LOCK TABLES锁表时才使用表级锁在这两种表中，建议最好不要使用LOCKTABLES,因为InnoDB自动采用行级锁，BDB用页级锁来保证事务的隔离如果数据表很大，那么在大多数应用中表级锁会比行级锁好多了，不过这有一些陷阱表级锁让很多线程可以同时从数据表中读取数据，但是如果另一个线程想要写数据的话，就必须要先取得排他访问正在更新数据时，必须要等到更新完成了，其他线程才能访问这个表更新操作通常认为比读取更重要，因此它的优先级更高不过最好要先确认，数据表是否有很高的SELECT操作,而更新操作并非很‘急需表锁锁在一个线程在等待，因为磁盘空间满了，但是却需要有空余的磁盘空间，这个线程才能继续处理时就有问题了这种情况下，所有要访问这个出问题的表的线程都会被置为等待状态，直到有剩余磁盘空间了表锁在以下设想情况中就不利了一个客户端提交了一个需要长时间运行的SELECT操作其他客户端对同一个表提交了UPDATE操作，这个客户端就要等到SELECT完成了才能开始执行其他客户端也对同一个表提交了SELECT请求由于UPDATE的优先级高于SELECT,所以SELECT就会先等到UPDATE完成了之后才开始执行，它也在等待第一个SELECT操作下列所述可以减少表锁带来的资源争夺让SELECT速度尽量快，这可能需要创建一些摘要表启动mysqld时使用参数-low-priority-updates这就会让更新操作的优先级低于SELECTo这种情况下，在上面的假设中，第o二个SELECT就会在INSERT之前执行了，而且也无需等待第一个SELECT了可以执行SET LOW_PRIORITY_UPDATES=1命令，指定所有的更新操作都放到一个指定的链接中去完成用LOW_PRIORITY属性来降低INSERT,UPDATE,DELETE的优先级用HIGH_PRIORITY来提高SELECT语句的优先级从MySQL
3.
23.7开始，可以在启动mysqld时指定系统变量max_write_lock_count为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有SELECT操作的优先级它允许在WRITE锁达到一定数量后有READ锁当INSERT和SELECT一起使用出现问题时，可以转而采用MylSAM表，它支持并发的SELECT和INSERT操作当在同一个表上同时有插入和删除操作时NSERT DELAYED可能会很有用当SELECT和DELETE一起使用出现问题时，DELETE的LIMIT参数可能会很有用执行SELECT时使用SQL_BUFFER_RESULT有助于减短锁表的持续时间.可以修改源代码mysys/thr_lock.c,只用一个所队列这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助以下是MySQL锁的一些建议只要对同一个表没有大量的更新和查询操作混在一起，目前的用户并不是问题执行LOCKTABLES来提高速度（很多更新操作放在一个锁之中比没有锁的很多更新快多了）将数据拆分开到多个表中可能也有帮助当MySQL碰到由于锁表引起的速度问题时，将表类型转换成InnoDB或BDB可能有助于提高性能数据库优化
（六）优化数据库结构MySQL作者叶金荣，出处:n•专家网，责任编辑李书琴，2008-06-1310:49MySQL将记录数据和索引数据分别存放在不同的文件里其他很多（儿乎所有）数据库都将这记录和索引数据存在同一个文件中我们相信MySQL的选择对于现在更大范围的系统更合适设计选择1MySQL将记录数据和索引数据分别存放在不同的文件里其他很多（几乎所有）数据库都将这记录和索引数据存在同一个文件中我们相信MySQL的选择对于现在更大范围的系统更合适另一个存储记录数据的方法是将每个字段的信息保存在独立的区域中（例如SDBM和Focus）这当每个查询都要访问不只一个字段的时候会打击性能由于当访问越多的字段后，性能下降的越厉害，因此我们认为这种模式不适合正常目的的数据库更多的情况是把索引和数据保存在一起（例如Oracle/Sysbase等）这样的话，就能在索引的叶子页面找到记录的信息这种布局的有利之处在于，很多时候由于索引被缓存的比较好，因此就能节省磁盘读取，不过也有如下缺点由于需要通过读取索引才能得到数据，因此扫描表就更慢了杳询时只能根据索引来取得数据需要更多的磁盘空间，因为必须从节点中复制索引（不能将记录保存在节点中）删除会使表变得更慢（因为删除时并没有更新节点中的索引）很难只缓存索引数据让数据变得更小巧灵活2优化的最基本原则之一就是尽可能把数据表设计的占用更少磁盘空间这能得到巨大的性能改善，因为磁盘读取比较快，并且越小的表在处理查询内容时只需更少的主内存在小点的字段上做索引也只需更少的资源负载MySQL支持很多种不同的表类型以及记录格式可以决定每个表要采用那种存储引擎/索引方式根据不同的应用程序选择适当的表格式能大大提高性能用以下方法可以提高表性能同时节省存储空间尽可能使用最有效最小的数据类型MySQL有好几种特定的类型能节省磁盘和内存尽可能使用更小的整数类型例如，MEDIUMINT通常比更合适INTo尽可能定义字段类型为NOTNULLo这会运行的更快，而且每个字段都会节省1个bit如果在应用程序中确实需要用到NULL,那么就明确的指定它不过要避免所有的字段默认值是NULLo在MylSAM表中，如果没有用至IJ任何变长字段VARCHAR,TEXT,或BLOB字段的话，那么就采用固定大小的记录格式这样速度更快，不过可能会浪费点空间表的主索引应尽可能短这样的话会每条记录都有名字标识且更高效只创建确实需要的索引索引有利于检索记录，但是不利于快速保存记录如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩一个字段很有可能在最开始的一些数量字符是各不相同的，因此在这些字符上做索引更合适MySQL支持在一个字段的最左部分字符做索引索引越短，速度越快，不仅是因为它占用更少的磁盘空间，也因为这提高了索引缓存的命中率，由此减少了磁盘搜索详情请看“
7.
5.2Tuning ServerParameters”在某些情况下，把一个频繁扫描的表分割成两个更有利在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此字段索引3所有的MySQL字段类型都能被索引在相关字段上做索引对提高SELECT语句的性能最有效每个表的最大索引长度以及最多索引数量是由各自的存储引擎定义好了的所有的存储引擎对每个表都至少可以支持16个索引，索引长度最小是256字节大部分存储引擎的限制更高索引格式中使用col_namelength语法，就能只对CHAR或VARCHAR字段最前面的length个字符做索引象类似这样只对字段的前缀部分做索引能让索引文件更小MylSAM和InnoDB从MySQL
4.
0.14开始存储引擎还支持在BLOB和TEXT字段上做索引，但是必须指定索引的前缀长度，例如CREATE TABLEtest blob_col BLOB,INDEXblob_col10;前缀的长度可以多达255字节从MySQL
4.
1.2开始，MylSAM和InnoDB表支持1000字节注意，前缀长度限制是以字节数衡量的，然而CREATE TABLE语句中的前缀长度理解成为字符个数因此在指定字段索引前缀长度时要考虑到使用多字节字符集字段的情况了本章描述了一个MySQL的早期应用在MySQL最开始的开发过程中，MySQL本来是要准备给大客户用的，他们是瑞典的2个最大的零售商，他们用于货物存储数据管理我们每周从所有的商店中得到交易利润累计结果，以此给商店的老板提供有用的信息，帮助他们分析如果更好的打广告以影响他们的客户数据量相当的大（每个月的交易累计结果大概有7百万），而且还需耍显示4-10年间的数据我们每周都得到客户的需求，他们要求能‘瞬间’地得到数据的最新报表我们把每个月的全部信息存储在一个压缩的‘交易表中以解决这个问题我们有一些简单的宏指令集，它们能根据不同的标准从存储的‘交易表中根据字段分组（产品组、客户id、商店等等）取得结果我们用一个小Perl脚本动态的生成Web页面形式的报表这个脚本解析Web页面，执行SQL语句，并且插入结果我们还可以用PHP或者mod_perl来做这个工作，不过当时还没有这2个工具为了得到图形数据，我们还写了一个简单的C语言工具，用于执行SQL查询并且将结果做成GIF图片这个工具同样是Perl脚本解析Web页面后动态执行的很多情况下，只要拷贝现有的脚本简单的修改里面的SQL查询语句就能产生新的报表了有时候，就需要在现存的累计表中增加更多的字段或者新建一个这个操作十分简单，因为我们在磁盘上存储有所有的交易表（总共大概有50G的交易表以及20G的其他客户资料）我们还允许客户通过ODBC直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了这个系统工作的很好，并且在适度的Sun UltraSPARC工作站（2x200MHz）上处理数据没有任何问题最终这个系统移植到了Linux±o基准套件
1.4MySQL本章本来要包括MySQL基准套件（以及crash-me）的技术描述的，但是至今还未写现在，您可以通过查看MySQL发布源代码sql-bench,目录下的代码以及结果有一个更好的想法基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差请注意，这个基准是单线程的，因此它度量了操作执行的最少时间我们未来打算增加多线程测试的基准套件想要使用基准套件，必备以下几个条件基准套件在MySQL的发布源代码中就有可以去下载发布版或者使用现有开发代码树（详情请看”
2.
3.3Installing fromtheDevelopment SourceTree”）基准脚本是用Perl写的，它用Perl的DBI模块来连接数据库，因此必须安装DBI模块并且还需耍每个要做测试的服务器上都有特定的BDB驱动程序例如，为了测试MySQL、PostgreSQL和DB2,就必须安装DBD::mysql,DBD::Pg及DBD::DB2模块详情请看”
2.7Perl InstallationNote”取得MySQL的分发源代码后，就能在sql-bench Fl录下看到基准套件想要运行这些基准测试，请先搭建好服务,然后进入sql-bench目录,执行run-all-tests脚本shell cdsql-benchshell perlrun-all-tests—server=server_name从MySQL
3.
23.23开始，就可以创建FULLTEXT索引了，它们使用全文搜索只有MylSAM表支持对CHARARCHAR和TEXT字段做FULLTEXT索引只对整个字段检索有效，不支持部分前缀检索从MySQL
4.
1.0开始，还可以空间类型字段上做索引目前，只有MylSAM存储引擎支持空间类型空间索引使用R树索引MEMORY HEAP存储引擎支持哈希索引，从MySQL
4.
1.0开始，它也支持B树索引多字段索引4MySQL可以在多个字段上创建索引，可以由多达15个字段组成对特定的字段类型，还可以使用前缀索引多字段索引可以认为是由索引字段的值连接在一起而成，且经过排序之后的数组MySQL以如下方法使用多字段索引在WHERE子句中指定了已知数量的索引的第一个字段，查询就很快了，甚至无需指定其他字段的值假定一个表结构如下CREATE TABLEtest id INT NOT NULL,last_name CHAR30NOT NULL,first_name CHAR30NOTNULL,PRIMARY KEYid,INDEX namelast_name,first_name;索引name覆盖了last_name和first_name字段这个索引在字段last_name上或last_name和first_name一起的指定范围内查询时能起到作用因此这个索引在以下几个查询中都会被用到SELECT*FROM testWHERE lasLname^Widenius,;SELECT*FROM test；WHERE last_name=Widenius ANDfirst_name=MichaerSELECT*FROM testWHERElast_name=WideniusAND first_name=Michael ORfirst_name=Monty;SELECT*FROM testWHERElast_name=WideniusAND first_name=M ANDfirst_nameN*;不过，索引name在以下几个查询中不会被用到SELECT*FROM testWHERE first_name=Michael;SELECT*FROM testWHERElast_name=Widenius ORfirst_name=Michael;关于MySQL如何使用索引来改善查询性能的方式在下个篇文章中具体讨论数据库优化七如何使用索引MySQL MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1310:58引用于快速找到特定一些值的记录如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录表越大，资源消耗越大如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据㈤索引用于快速找到特定一些值的记录如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录表越大，资源消耗越大如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少大部分MySQL索引PRIMARY KEY,UNIQUEJNDEX和FULLTEXT都是以B树方式存储只有空间类型的字段使用R树存储，MEMORY HEAP表支持哈希索引字符串默认都是自动压缩前缀和后缀中的空格通常，如下所述几种情况下可以使用索引哈希索引用于MEMORY表的独特之处在后面会讨论到想要尽快找到匹配WHERE子句的记录根据条件排除记录如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引做表连接查询时从其他表中检索记录想要在指定的索引字段key_col上找到它的MIN或MAX值优化程序会在检查索引的key_col字段前就先检查其他索引部分是否使用了WHEREkey_part_#=constant子句这样的话，MySQL会为MIN或MAX表达式分别单独做一次索引查找，并且将它替换成常数当所有的表达式都被替换成常数后，查询就立刻返回如下SELECT MINkey_part2,MAXkey_part2FROM tbl_name WHEREkey_part1=10;对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时如ORDERBY keyjDart1,key_part2如果所有的索引部分都按照DESC排序，索引就按倒序排序o有些时候，查询可以优化使得无需计算数据就能直接取得结果当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果SELECT key_part3FROM tbl_name WHEREkey_part1=1假设有如下SELECT语句mysql SELECT*FROM tbl_name WHEREcol1=val1ANDcol2=val2;如果在coll和col2上有一个多字段索引的话，就能直接取得对应的记录了如果在coll和col2分别有独立的索引，那么优化程序会先找到限制最多的那个索引，然后根据哪个索引能找到更少的记录就决定使用哪个索引如果表里有一个多字段索引的话，那么该索引的任何最左前缀部分都可以被优化程序用来检索记录例如，在C0I1,col2,col3上有一个索引，那么按字段组合col1,col1,col2,和coll,col2,col3搜索的时候都会用到索引MySQL无法使用非最左前缀索引中的部分索引假如有以下SELECT语句SELECT*FROM tbl_name WHEREcol1=val1;SELECT*FROM tbl_name WHEREcol2=val2;SELECT*FROM tbl_name WHEREcol2=val2ANDcol3=val3;如果在COl1,COl2,C0I3上有一个索引，只有第一个查询用到索引了第二和第三个尽管包括了索引字段，但是col2和col2,col3并非索引col1,col2,c⑶的最左前缀部分当对字段做=,,=,v,v=,或BETWEEN比较操作时，也会用到索引MySQL在做LIKE比较时也可能用到索引，如果LIKE的参数是非通配字符开始的固定字符串的话以下的SELECT语句就用到了索引SELECT*FROM tbl_name WHEREkey_col LIKE•Patrick%;SELECT*FROM tbl_name WHEREkey_col LIKE,Pat%_ck%;第一个查询中，只有的Patrick v=key_colv Patricl记录才会被检索到第二个查询中，只检索Pat1=key_colPau的记录以下SELECT语句不使用索引SELECT*FROM tbl_name WHEREkey_col LIKE%Patrick%;SELECT*FROM tbl_name WHEREkey_col LIKEother_col;第一个语句中，LIKE的参数是以通配符开始的第二个语句中，LIKE的参数不是一个常值MySQ.
4.0及更高会做一个额外.LIK.优化如果使….LIK.%string%.并.strin.超过3个字符，MySQL就会.Turb.Boyer-Moor.算法来初始化模式，并且利用这个模式来加快搜索用col_name ISNULL搜索时也会使用索引，如果字段col_name上有索引的话任何在WHERE子句中没有跨越全部AND级分句的索引都不会用来优化查询换言之，想要启用一个索引，那么在任何AND分句中都必须使用索引的前缀字段以下WHERE子句使用索引...WHERE indexjDart1=1AND index_part2=2AND other_column=3/*index=1OR index=2*/...WHERE index=1OR A=10AND index=2/*优化了like index_part1=hellom*/...WHERE indexj3art1=hello AND index_part3=5/*使用索引indexl,但没有用到index2或index3*/...WHERE indexl=1AND index2=2OR indexl=3AND index3=3;...WHERE index_part1=1AND index_part2=2AND other_column=3/*index=1OR index=2*/...WHERE index=1OR A=10AND index=2/*优化了like index_part1=hello*/...WHERE index_part1=hello AND index_part3=5/*使用索引index!,但没有用到index2或index3*/...WHERE indexl=1AND index2=2OR indexl=3ANDindex3=3;以下WHERE子句不使用索引/*没用到index_part1*/...WHERE index_part2=1ANDindex_part3=2/*所有的AND部分没用到索引*/...WHERE index=1OR A=10/*索引没有跨越全部字段
7...WHERE indexjDart1=1OR index_part2=10有些时候尽管有可用的索引，MySQL也不会用到它们一种情况是优化程序认为如果使用索引会需要检索更大部分的表记录（这时候，扫描表可能更快，因为这支需要更少的搜索）尽管如此，如果有一个查询用LIMIT限制只检索部分记录，MySQL就一定会使用索引，因为这样能更快检索到更少记录来返回给结果以下是哈希索引的一些不同的特性它们只用于=或=比较（但并不很快）优化程序无法使用哈希索引来加速ORDERBY操作（这种索引不能用于按顺序搜索下一个记录）MySQL大致无法判断出介于两个值之间有多少记录（这由范围优化程序来决定使用哪个索引）这在把MylSAM表类型改为采用哈希索引的MEMORY类型后可能会影响一些查询只有全部索引键才能用于检索记录（如果是B树索引，任何前缀部分索引也能用于检索记录）数据库优化
（九）MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1311:25MySQL是多线程的，因此可能会有多个客户端同时发起查询某个表的请求为了最小化多个客户端线程在同一个表上的不同状态，针对每个并发的线程单独打开数据表这会占用一些内存，但是通常会提高性能如何统计打开的表数量MySQL执行命令mysqladmin status时，可以看到类似如下结果:Uptime:426Running threads:1Questions:11082Reloads:1Open tables:12如果只有6个数据表时，Open tables的值确实12,这可能会有些疑惑MySQL是多线程的，因此可能会有多个客户端同时发起查询某个表的请求为了最小化多个客户端线程在同一个表上的不同状态，针对每个并发的线程单独打开数据表这会占用一些内存，但是通常会提高性能如果是MylSAM表，针对每个打开数据表的客户端都要有一个额外的文件描述符打开数据文件的情况（不过索引文件的描述符则可以在所有的线程间共享）ISAM存储引擎则共享这些操作如何打开和关闭数据表MySQL系统变量table_cache,max_connections和max_tmp_tables影响着服务器保持打开的文件最大数量提高一个或多个这些变量，就可以提高操作系统在每次处理时能打开的文件描述符限制很多操作系统都可以增加文件打开的数量限制，不过每个系统的方法都各不一样查阅一下操作系统文档来判断是否可以提高限制以及怎么去做table_cache和max_connections相关例如,有200个并发的连接，那么则必须至少有200*N大小的表缓存,N是在一个表连接中最大的表数量同时也需要为临时表和临时文件保留一些额外的文件描述符请确认操作系统可以通过设定table_cache来处理对应数量的打开文件如果table_cache设得太高了，MySQL可能会用完全部的文件描述符而拒绝新连接，就无法执行查询，变得很不可靠因此必须要考虑到MylSAM存储引擎要为每个独立打开的表使用两个文件描述符可以在启动mysqld_safe的时候增加・・open・参数来提高MySQL打开文件描述符的数量详情请看“A217Found.缓存中会保存table_cache个打开的表会它的默认值是64;它可以在启动mysqld的时候通过修改-table_cache参数来改变注意，MySQL可能会临时打开比这个数更多的表来执行查询在以下情况中，一个不用的表会被关闭并且从缓存中被删除如果缓存满了，并且一个线程试图打开一个不在缓存中的表如果缓存中已经包含了不止table_cache个表目，并且线程无需再使用该表当发生刷新表操作当提交一个FLUSH TABLES语句或者执行mysqladmin flush-tables ormysqladmin refresh命令时就会这样当表缓存满了，服务器遵循以下步骤来分配一个新的缓存表目当前没使用的表都释放，依照最近最少使用的顺序如果有一个新的表要被打开，但是缓存满了且没有表被释放，缓存就临时根据需要扩充一下当缓存处于临时扩充状态，且有表处于从使用变成不使用状态时，就关闭这个表并且从缓存中释放每个并行访问都打开一个表这意味着当有两个线程同时访问一个表，或者一个线程在同一个查询中访问两次这个表（例如，在表连接中连接自己），那么这个表就需要被打开两次每次并发的打开都在表缓存中请求一个表目每个表第一次打开时都需要两个文件描述符一个给数据文件，一个给索引文件其他新增的对该表的打开则只需要一个文件，给数据文件索引文件的描述符在所有的线程间是共享的如果使用HANDLER tbl_name OPEN语句打开表，则有一个专用的表对象给该线程这个表对象不和其他线程共享，并且除非调用HANDLER tbl_name CLOSE语句或者线程结束，否则它不会关闭;这样的话，它就重新放回表索引中（如果索引还未满）详情请看”
14.
（八）索引缓存MySQL:MylSAM作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1311:06本章首先描述了MylSAM索引缓存的基本操作然后讨论在MySQL
4.1中所做的改进，它提高了索引缓存性能,同时能更好地控制缓存操作为了能最小化磁盘I/O,MylSAM存储引擎采用了很多数据库系统使用的一种策略它采用一种机制将最经常访问的表保存在内存区块中对索引区块来说，它维护着一个叫索引缓存（索引缓冲）的结构体这个结构体中放着许多那些最常使用的索引区块的缓冲区块对数据区块来说，MySQL没有使用特定的缓存它依靠操作系统的本地文件系统缓存本章首先描述了MylSAM索引缓存的基本操作然后讨论在MySQL
4.1中所做的改进，它提高了索引缓存性能，同时能更好地控制缓存操作线程之间不再是串行地访问索引缓存多个线程可以并行地访问索引缓存可以设置多个索引缓存，同时也能指定数据表索引到特定的缓存中索引缓存机制对ISAM表同样适用不过，这种有效性正在减弱自从MySQL
3.23开始MylSAM表类型引进之后，ISAM就不再建议使用了MySQL
4.1更是延续了这个趋势SAM类型默认被禁用了可以通过系统变量key_buffer_size来控制索引缓存区块的大小如果这个值大小为0,那么就不使用缓存当这个值小得于不足以分配区块缓冲的最小数量
（8）时，也不会使用缓存当索引缓存无法操作时，索引文件就只通过操作系统提供的本地文件系统缓冲来访问（换言之，表索引区块采用的访问策略和数据区块的一致）一个索引区块在MylSAM索引文件中是一个连续访问的单元通常这个索引区块的大小和B树索引节点大小一样（索引在磁盘中是以B树结构来表示的这个树的底部时叶子节点，叶子节点之上则是非叶子节点）在索引缓存结构中所有的区块大小都是一样的这个值可能等于，大于，或小于表的索引区块大小通常这两个值是不一样的当必须访问来自任何表的索引区块时，服务器首先检查在索引缓存中是否有可用的缓冲区块如果有，服务器就访问缓存中的数据，而非磁盘就是说，它直接存取缓存，而不是存取磁盘否则，服务器选择一个（多个）包含其它不同表索引区块的缓存缓冲区块，将它的内容替换成请求表的索引区块的拷贝一旦新的索引区块在缓存中了，索引数据就可以存取了当发生被选中要替换的区块内容修改了的情况时，这个区块就被认为脏了那么，在替换之前，它的内容就必须先刷新到它指向的标索引通常服务器遵循LRU（最近最少使用）策略当要选择替换的区块时，它选择最近最少使用的索引区块为了想要让选择变得更容易，索引缓存模块会维护一个包含所有使用区块特别的队列（LRU链）当一个区块被访问了，就把它放到队列的最后位置当区块要被替换时，在队列开始位置的区块就是最近最少使用的，它就是第一候选删除对象共享访问索引缓存在MySQL
4.1以前，访问索引缓存是串行的两个线程不能并行地访问索引缓存缓冲服务器处理一个访问索引区块的请求只能等它之前的请求处理完结果，新的请求所需的索引区块就不在任何索引缓存环冲区块中，因为其他线程把包含这个索引区块的缓冲给更新了从MySQL
4.
1.0开始，服务器支持共享方式访问索引缓存没有正在被更新的缓冲可以被多个线程访问缓冲正被更新时，需要使用这个缓冲的线程只能等到更新完成之后多个线程可以初始化需要替换缓存区块的请求，只要它们不干扰别的线程（也就是，它们请求不同的索引区块，因此不同的缓存区块被替换）共享方式访问索引缓存令服务器明显改善了吞吐量多重索引缓存共享访问索引缓存改善了性能，却不能完全消除线程间的冲突它们仍然争抢控制管理存取索引缓存缓冲的结构为了更进一步减少索引缓存存取冲突，MySQL4,
1.1提供了多重索引缓存特性这能将不同的表索引指定到不同的索引缓存当有多个索引缓存，服务器在处理指定的MylSAM表查询时必须知道该使用哪个默认地，所有的MylSAM表索引都缓存在默认的索引缓存中想要指定到特定的缓存中，可以使用CACHE INDEX语句如下语句所示，指定表的索t1,t2和t3引缓存到名为hot_cache的缓存中mysql CACHE INDEX t1,t2,t3IN hot_cache;+--------+---------------------+----------+----------+|Table|Op|Msg_type|Msg_text|+--------+---------------------+----------+----------+|test.tl|assign_to_keycache|status|OK||test.t2|assign_to_keycache|status|OK|I test.t3|assign_to_keycache|status|OK|+--------+---------------------+----------+----------+注意，如果服务器编译支持存ISAM储引擎了，那么ISAM表也使用索引缓存机制不过，ISAM表索引只能使用默认的索引缓存而不能自定义CACHE INDEX语句中用到的索引缓存是根据用SET GLOBAL语句的参数设定的值或者服务器启动参数指定的值创建的，如下mysql SETGLOBAL keycachel.key_buffer_size=128*1024;想要删除索引缓存，只需设置它的大小为0mysql SETGLOBAL keycachel.key_buffer_size=O;索引缓存变量是一个结构体变量，由名字和组件构成例如keycachel.key_buffer_size,keycachel就是缓存名,key_buffer_size是缓存组件默认地，表索引在服务器启动时指定到主（默认的）索引缓存中当一个索引缓存被删掉后，指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去对一个繁忙的系统来说，我们建议以下三条策略来使用索引缓存热缓存占用20%的总缓存空间用于繁重搜索但很少更新的表冷缓存占用20%的总缓存空间用于中等强度更新的表，如临时表冷缓存占用60%的总缓存空间作为默认的缓存，用于所有其他表使用三个缓存的一个原因是好处在于，存取一个缓存结构时不会阻止对其他缓存的访问访问一个表索引的查询不会跟指定到其他缓存的查询竞争性能提高还表现在以下几点原因热缓存只用于检索记录，因此它的内容总是不需要变化所以，无论什么时候一个索引区块需要从磁盘中引入，被选中要替换的缓存区块的内容总是要先被刷新索引被指向热缓存中后，如果没有需要扫描全部索引的查询，那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中在临时表里必须频繁执行一个更新操作是相当快的，如果要被更新的节点已经在缓存中了，它无需先从磁盘中读取出来当临时表的索引大小和冷缓存大小一样时，那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的中点插入策略默认地，MySQL
4.1的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块，不过它也支持更完善的方法，叫做”中点插入策略”使用中点插入策略时，LRU链就被分割成两半一个热子链，一个温子链两半分割的点不是固定的，不过缓存管理系统会注意不让温子链部分“太短，总是至少包括全部缓存区块的key_cache_division_limit比率key_cache_division_limit是缓存结构体变量的组件部分，因此它是每个缓存都可以设置这个参数值当一个索引区块从表中读入缓存时，它首先放在温子链的末尾当达到一定的点击率（访问这个区块）后，它就提升到热子链中去目前，要提升一个区块的点击率
4.1引进了对每个索引缓存的新变量key_cache_block_size这个变量可以指定每个索引缓存的区块大小用它就可0以来调整索引文件I/O操作的性能当读缓冲的大小和本地操作系统的I/O缓冲大小一样时.，就达到了I/O操作的最高性能了但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能读比较大的叶子节点时，服务器会读进来很多不必要的数据，这大大阻碍了读其他叶子节点目前，还不能控制数据表的索引区块大小这个大小在服务器创建索引文件.MYI时已经设定好了，它根据数据表的索引大小的定义而定在很多时候，它设置成和I/O缓冲大小一样在将来，可以改变它的值，并且会全面采用变量key_cache_block_size0重建索引缓存索引缓存可以通过修改其参数值在任何时候重建它，例如mysql SETGLOBAL cold_cache.key_buffer_size=4*1024*1024;如果设定索引缓存的结构体变量组件变量key_buffer_size或key_cache_block_size任何一个的值和它当前的值不一样，服务器就会清空原来的缓存，在新的变量值基础上重建缓存如果缓存中有任何的‘脏嗦引块，服务器会先把它们保存起来然后才重建缓存重新设定其他的索引缓存变量并不会重建缓存重建缓存时，服务器会把所有的脏缓冲的内容先刷新到磁盘中去之后，缓存的内容就无效了不过，重建的时候并不阻止那些需要使用指向到缓存中的索引的查询相反地，服务器使用本地文件系统缓存直接访问数据表索引文件系统缓存不如索引缓存来的高效，因此，可以预见这时的查询会比较慢一旦缓存重建完了，指向它的索引又可以使用了，同时也就不再使用文件系统缓存来访问索引了数据库优化
（十）优化服务器MySQL MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1909:46我们从系统级别的因素开始说起，因为有些方面的因素必须尽早决定才能取得较大性能改进其他情况下，只需要快速看一下本章节即可不过，在这个级别看看能做什么以取得更高性能更合适
1.调整系统因素及启动参数server_name可以是任何一个可用的服务想要列出所有的可用选项和支持的服务，只要调用以下命令shell perlrun-all-tests-helpcrash-me脚本也是放在sql-bench目录下crash-me通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制例如，它可以判断都支持什么字段类型支持多少索引支持什么样的函数能支持多大的查询VARCHAR字段类型能支持多大可以从上找到各种不同数据库crash-me的结果更多的信息请访问使用您自己的基准
1.5请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在解决这个瓶颈（或者使用一个假的模块来代替）之后，就能很容易地找到下一个瓶颈了即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了详情请看”
7.
1.4The MySQLBenchmark Suite%您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的另一个基准套件是开放源码的数据库基准，可以在.net上找到当系统负载十分繁重的时候，通常就会发生问题我们就有很多客户联系我们说他们有一个（测试过的）生产系统也遭遇了负载问题在很多情况下，性能问题归结于数据库的基本设计（例如，在高负载下扫描数据表的表现不好）、操作系统、或者程序库等因素很多时候，这些问题在还没有正式用于生产前相对更容易解决为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试!可以使用Super Smack,在可以找到从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试优化语句及其他查询2SELECT首先，影响所有语句的一个因素是您的权限设置越复杂，那么开销就越大使用比较简单的GRANT语句能让MySQL减少在客户端执行语句时权限检查的开销例如，如果没有设定任何表级或者字段级的权限，那么服务器就无需检查tables_priv和columns_priv表的记录了同样地，如果没有对帐户设定任何资源限制的话，那么服务器也就无需做资源使用统计了如果有大量查询的话，花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的如果问题处在一些MySQL特定的表达式或者函数上，则可以通过mysql客户端程序使用BENCHMARKQ函数做一个定时测试它的语法是BENCHMARK（loop_count,expression）例如0mysql SELECTBENCHMARK（1OOOOOO1+1）；Z我们从系统级别的因素开始说起，因为有些方面的因素必须尽早决定才能取得较大性能改进其他情况下，只需要快速看一下本章节即可不过，在这个级别看看能做什么以取得更高性能更合适使用默认的操作系统这很重要想要最有效地使用多CPU机器，就使用Solaris（因为它的线程实现确实很好）或Linux（因为
2.2的内核对SMP有良好的支持）请注意，老版本的Linux内核默认会有2GB文件大小限制如果使用这样的内核而文件又确实需要大于2GB,那么就必须对ext2文件系统打大文件支持（LFS）补丁其他文件系统诸如ReiserFS和XFS则没有这个限制在MySQL投入生产之前，我们建议你在欲使用的平台上先做一下测试其他tips:如果有足够的RAM（随机存储器），则应该去掉所有的交换设备有些操作系统在一些情景中尽管有剩余内存也会使用交换设备使用MySQL选项--skip-external-locking来避免外部锁从MySQL
4.0开始，这个选项默认是打开的在这之前,只有编译支持MIT-pthreads才能默认打开，因为在所有平台上的MIT-pthreads不能全部都支持flock这在Linux上也是默认打开的，因为Linux的文件锁还不安全注意，-skip-extemal-locking选项在服务器运行时并不会影响其功能性只要记住在运行myisamchk前要关闭服务器（或者锁定并且刷新相关数据表）在一些操作系统上这个选项是强制的，因为外部锁在任何情况下都无法使用不能使用--skip-external-locking选项的唯一情况是在同一个数据上运行多个MySQL服务器（不是客户端），或者运行myisamchk检查（不是修复）数据表前没有先告诉服务器要刷新并且锁定该表使用-skip-external-locking选项后依旧可以使用LOCKTABLES和UNLOCK TABLES语句
2.调整服务器参数可以使用以下mysqld命令（在MySQL
4.1以前，忽略-verbose）来确定默认的缓冲大小shell mysqld-verbose-help这个命令产生了所有的mysqld选项以及可以配置的系统变量列表结果中包括默认值，看起来像是如下backjog currentvalue:5bdb_cache_size currentvalue:1048540binlog_cache_size currentvalue:32768connect_timeout currentvalue:5delayedjnsertjimit currentvalue:100delayed_insert_timeout currentvalue:300delayed_queue_size currentvalue:1000flush_time currentvalue:0interactive_timeout currentvalue:28800join_buffer_size currentvalue:131072key_buffer_size currentvalue:1048540long_query_time currentvalue:10lower_case_table_names currentvalue:0max_allowed_packet currentvalue:1048576max_binlog_cache_size currentvalue:4294967295max_connect_errors currentvalue:10max_connections currentvalue:100max_delayed_threads currentvalue:20max_heap_table_size currentvalue:16777216maxjoin_size currentvalue:4294967295max_sort_length currentvalue:1024max_tmp_tables currentvalue:32max_write_lock_count currentvalue:4294967295myisam_sort_buffer_size currentvalue:8388608net_bufferjength currentvalue:16384net_read_timeout currentvalue:30net_retry_count currentvalue:10net_write_timeout currentvalue:60read_buffer_size currentvalue:131072read_rnd_buffer_size currentvalue:262144slow_launch_time currentvalue:2sort_buffer currentvalue:2097116table_cache currentvalue:64thread_concurrency currentvalue:10thread_stack currentvalue:131072tmp_table_size currentvalue:1048576waitjimeout currentvalue:28800如果当前有mysqld服务器在运行，可以连接上去用以下命令来查看实际使用的系统变量mysql SHOWVARIABLES;也可以用以下语句来查看运行中的系统的统计结果及状态报告mysql SHOWSTATUS;系统变量以及状态信息也可以通过mysqladmin来得到shell mysqladminvariablesshell mysqladminextended-statusMySQL使用的算法有高伸缩性，因此它通常可以只使用很少内存就能运行不过，给MySQL更多的内存通常能取得更好的性能调整MySQL服务器时，两个最重要的变量就是key_buffer_size和table_cache在试图修改其他变量前应该首先确认已经合o理设定这两个变量了以下例子展示了在不同的运行时配置一些典型的变量值这些例子使用mysqld_safe脚本和-var_name=value语法来设定变量var_name的值为value这个语法在MySQL
4.0以后就可以用了，在旧版本的MySQL中，考虑到如下一些不同之处使用safe_mysqld脚本而非mysqld_safeo使用--set-variable=var_name=value或-0var_name=value语法来设.置变量如果变量名以_size结尾，就必须去掉_size例如，一个旧变量名为sort_buffer_size就是sort_buffer,旧变量名read_buffer_size就是record_buffer用mysqld-help来要看那些变量是当前服务器版本可以识别的0如果至少有256MB内存，且有大量的数据表，还想要在有中等数量的客户端连接时能有最大性能，可以这么设定shell mysqld_safe--key_buffer_size=64M--table_cache=256\-sort_buffer_size=4M-read_buffer_size=1M如果只有128MB内存，且只有少量表，但是需要做大量的排序，可以这么设定shell mysqld_safe-key_buffer_size=16M-sort_buffer_size=1M如果有大量的并发连接，除非mysqld已经设置成对每次连接只是用很少的内存，否则可能发生交换问题mysqld在对每次连接都有足够内存时性能更好如果只有很少内存且有大量连接，可以这么设定shell mysqld_safe-key_buffer_size=512K-sort_buffer_size=100K\-read_buffer_size=100K甚至这样shell mysqld_safe--key_buffer_size=512K--sort_buffer_size=16K\-table_cache=32-read_buffer_size=8K\-neLbuffer length=1K如果在一个比可用内存大很多的标上做GROUPBY或ORDERBY操作时，那么最好加大read_rnd_buffer_size的值以加速排序操作后的读数据安装MySQL后，在support-files目录下会有一些不同的my.cnf样例文件my-huge.cnf,my-large.cnf,my-medium.cnf和my-small.cnf可以把它们作为优化系统的蓝本注意，如果是通过命令行给mysqld或mysqld_safe指定参数，那么它只在那次启动服务器时有效想要让这些选项在服务器启动时都有效，可以把它们放到配置文件中想要看参数改变后的效果，可以用以下方法（在MySQL
4.1以前，忽略-verbose）这个变量就会在结果的靠近末尾列出来确认--verbose和-help选项是放在最后面，否则，在命令行上列出来的结果中在它们之后的其他选项效果就不会被反映出来了关于调整InnoDB存储引擎的详细信息请参考T
6.12InnoDB PerformanceTuning Tips%
3.控制查询优化性能查询优化程序的任务就是找到最佳的执行SQL查询的方法因为”好“和“坏”方法之间的性能差异可能有数量级上的区别（也就是说，秒相对小时，甚至是天），MySQL中的大部分查询优化程序或多或少会穷举搜索可能的优化方法，从中找到最佳的方法来执行拿连接查询来说，MySQL优化程序搜索的可能方法会随着查询中引用表数量的增加而指数增加如果表数量较少（通常少于7-10个），那么这基本上不是问题不过，当提交一个很大的查询时，服务器的性能主要瓶颈很容易就花费在优化查询上MySQL
5.
0.1引进了一个更灵活的方法，它允许用户控制在查询优化程序穷举搜索最佳优化方法的数量一般的考虑是，优化程序搜索的方法越少，那么在编译查询时耗费的时间就越少另一个方面，由于优化程序可能会忽略一些方法,因此可能错过找到最佳优化方法关于控制优化程序评估优化方法的数量可以通过以下两个系统变量变量0Ptimizer_prune_level告诉优化程序在估算要访问的每个表的记录数基础上忽略一定数量的方法我们的经验表明，这种“学习猜测”方法很少会错过最佳方法，因为它可能戏剧性地减少编译时间这就是为什么这个选项默认是打开的（optimizer_prune」evel=1）不过，如果确信优化程序会错过更好的方法，这个选项可以关上（optimizecpruneJevetO）,不过要注意编译查询的时间可能会更长了要注意尽管是用了这种试探方法，优化程序仍会调查指数级的方法变量optimizer_search_depth告诉优化程序”将来”的每次顺序调查不完全的方法是否需要扩充的更远的深度0Ptimizer_search_depth的值越小，可能会导致查询编译时间的越少例如，有一个12-13或更多表的查询很容易就需要几小时甚至几天的时间来编译，如果optimizer_search_depth的值和表数量相近的话同样，如果optimizer_search_depth的值等于3或4,则编译器可能至需要花不到几分钟的时间就完成编译了如果不能确定0Ptimizer_search_depth的值多少才合适，就把它设置为0,让优化程序来自动决定数据库优化
（十一）MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1909:56以下的大部分测试都是在Linux上用MySQL的基准套件来做的，不过它们对其他操作系统以及不同的工作量也是有一定启示的里
1.编译和连接如何影响MySQL的速度以下的大部分测试都是在Linux上用MySQL的基准套件来做的，不过它们对其他操作系统以及不同的工作量也是有一定启示的用-static连接的话，MySQL执行速度的速度是最快的在Linux上，用pgcc和-03编译的代码最快大概需要200MB的内存加上这些选项来编译sql_yacc.cc,因为gcc/pgcc需要大量内存来编译所有的内部函数想要配置MySQL以避免包含libstdc++库就要加上CXX=gcc参数，不过这不是必须的注意，使用某些版本的pgcc编译的结果代码只能在奔腾处理器上运行，尽管已经给编译器增加选项想让结果代码可以在所有的X586类型（如AMD）处理器上运行如果使用比较好的编译器以及编译选项，应用程序大概能提高10-30%的速度这在自己编译MySQL服务器的时候尤为重要我们已经测试Cygnus CodeFusion和Fujitsu编译器了，不过测试它们时，无论哪个都没有完全地解决bug以允许最佳方式编译MySQLo标准的MySQL二进制发布包已经编译支持所有的字符集了如果想要自己编译MySQL,只需编译支持想要使用的字符集可以在configure时增加--with-charset选项来控制以下是我们已经做过的一些测量如果使用pgcc编译任何东西时用・06选项，则mysqld服务器会比用gcc
2.
95.2编译快1%如果使用动态连接（没用-static）,则在Linux上结果会慢13%注意，这仍然可以让你的应用程序客户端动态连接MySQL的库这对提高服务器性能相当关键如果做strip mysqld了，则结果会快4%在同一个主机上连接到服务器，如果使用TCP/IP而不是Unix套接字文件，那么会慢
7.5%（在Unix上，如果使用主机名localhost连接，MySQL默认使用套接字文件）用TCP/IP连接时，直接连接本机服务器比连接不同主机上的服务器会慢8-11%,尽管是通过100Mb/s的以太网运行基准测试时如果采用安全连接（所有的数据都用内部支持的SSL加密）则会比用不加密链接慢55%o编译时增力卜wTh-debug=full选项，则大部分查询都会慢20%一些查询可能会变得很慢;例如，MySQL的基准套件可能会慢35%如果只增加选项--w让h-debug（没有=full）,则只会慢15%一个使用--with-debug=full选项编译后的mysqld可以在启动时增加--skip-safemalloc选项来禁用运行时内存检查则执行速度就会接近于增加--with-debug参数时的情况在Sun UltraSPARC-lle上，用Forte
5.0编译的服务器会比用gcc
3.2编译的快4%在Sun UltraSPARC-lle上,用Forte
5.0编译的服务器在32位平台上比在64位平台上快4%在UltraSPARC上用gcc
2.
95.2编译，增加-mcpu=v8-Wa,-xarch=v8plusa选项的话会快4%在Solaris
2.
5.1±,MIT-pthreads比Solaris原始的单处理器线程慢8-12%在不同的负载或者CPU下，这个差异会更大在Linux x86上用gcc编译，不增加帧指针（-fomit-frame-pointer或-fom计-frame-pointer-什ixed-ebp）选项的话会慢1-4%MySQL AB提供的Linux上的MySQL二进制发布包通常用pgcc编译不过我们又重新使用gcc来编译了，因为用pgcc编译的代码不能在AMD上运行我们会一直使用gcc来编译直到pgcc的这个bug解决了与此同时，如果你有非AMD机器，就可以用pgcc来编译以使MySQL更快标准的MySQL Linux二进制包是静态连接的，这使得它更快且移植性更好如何使用内存
2.MySQL以下展示了mysqld服务器使用内存的几种方法可适用的是，和使用内存相关的系统变量名已经给出T索引缓冲（key_buffer_size变量）在所有的线程间是共享的;服务器所需的其他缓冲已经分配好了•每个连接使用的一些线程特有的空间•一个堆栈（默认是64KB,thread_stack变量）•一个连接缓冲（net_buffer_length变量）•一个结果缓冲（net_buffer_length变量）连接缓冲和结果缓冲可根据需要动态扩大到max_alIowed_packet一个查询正在执行时，当前查询语句的一份拷0贝也需要为之分配内存所有的线程共享一样的基本内存只有压缩后的ISAM和MylSAM表才是内存映射的这是因为32位的内存只有4GB的内存空间，对大部分大表都不够当64位寻址的操作系统更普遍时，我们会普遍支持内存映射的每个需要顺序扫描数据表的请求都需要分配一个读缓冲（read_buffer_size变量）如果以“随机顺序读取记录（例如，排序之后），一个随机一读缓冲就必须分配以避免磁盘搜索（read」nd_buffer_size变量）所有的表连接都在一个步骤中完成，甚至大部分连接可以不使用临时表就能完成大部分临时表都是基于内存（HEAP表）的记录长度很长（所有的字段长度之和）或者包含有BLOB字段的临时表都会存储在磁盘中在MySQL
3.
23.2之前有一个问题是，当一个内部的内存表大小超过tmp_table_size后，就会产生一个The tabletbl_name isfull错误在MySQL
3.
23.2后，这会自动处理，在必须的时候把内存表转换成基于磁盘存储的MylSAM表想要在旧版本的MySQL也能正常运行，可以给mysqld设置tmp_table_size选项以增加临时表大小,或者在客户端程序中设置SQL_BIG_TABLES选项在MySQL
3.20中，临时表的大小最多为record_buffer*16;如果你使用这个版本，就必须加大record_buffer的值可以在使用--big-tables选项启动mysqld来允许将临时表保存在磁盘中不过这会影响很多查询编译大部分需要做排序的请求都会分配排序缓冲，且视结果集大小分配0到2个临时文件几乎所有的解析和计算在本地内存存储中已经做完了无需为小的条目耗费内存，因此通常能避免比较慢的内存分配以及释放内存只分配给不可预料的大字符串;这由malloc（）和free（）来完成每个打开着的MylSAM和ISAM表，索引文件只打开一次，而数据文件则是每次为同时运行的线程打开一次在每个当前的线程中，表结构，每个字段的结构，需要分配大小3*N的缓冲（N是记录的最大长度，不计算BLOB字段）BLOB字段需要比BLOB数据长度多出来5到8字节MylSAM和ISAM存储引擎维护一个额外的缓冲字段供内部使用对于每个有BLOB字段的表，专门为其扩展一个动态缓冲来读取大BLOB数据读表的时候，就会分配一个以最大长的BLOB数据长度的缓冲所有的使用中的表句柄都缓存起来并且被当作FIFO来管理默认地，这个缓存有64个表目如果有一个表同时被两个运行着的线程使用了，那么在缓存中包含了该表的2个表目FLUSH TABLES语句或mysqladmin flush-tables命令会关闭所有非使用中的表，并且也会在线程结束后关闭那些使用中的表这对释放使用中的内存很有效PS和其他系统程序都可以报告mysqld使用了大量内存这个可能是因为在不同内存地址上的线程堆栈导致例如，Solaris下的ps会把堆栈中未使用的内存也认为是已使用了可以通过s命令来检查可用的交换内存我们已经用好几个内存泄漏监测器来测试了mysqld（包括商业的和开源的），应该不会存在内存泄漏
3、MySQL如何使用DNS当一个新的客户端连接到mysqld时，它就会产生一个新的线程来处理这个线程首先检查客户主机名是否在主机缓存中如果没有，它就尝试解析这个主机名如果操作系统支持线程安全的gethostbyaddr_r（）和gethostbyname」调用，就用他们来解析如果操作系统不支持上述两个的线程安全调用，那么线程就县锁定一个mutex,然后再调用它们这时，其他线程便无法解析不在主机缓存中主机名，直到第一个线程释放了mutex锁在mysqld启动时使用-skip-name-resolve选项就能禁用DNS解析不过，这样的话就不能在MySQL的授权表中使用主机名了而只能用ip格式如果DNS解析很慢且有很多主机，那么可以用-skip-name-resolve选项禁用DNS解析或者重新编译mysqld时增加HOST_CACHE_SIZE的定义值（默认是128）来提高性能启动服务器时使用--skip-name-resolve选项就能禁用主机缓存想要清除主机缓存，可以提交FLUSH HOSTS语句或者运行mysqladmin flush-hosts命令想要彻底禁用TCP/IP连接，在启动服务器时使用--skip-networking选项即可CONTENT=编译和连接如何影响MySQL的速度,mysql如何使用内存,mysql如何使用dnsCONTENT”编译和连接如何影响MySQL的速度,mysql如何使用内存,mysql如何使用dns数据库优化
（十二）磁盘MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1910:26磁盘搜索是性能的很大瓶颈这个问题在数据大量增长以至于无法使用有效的缓存时尤为明显或多或少随即访问大数据库时，就必然会有至少一次磁盘搜索来读数据，两次磁盘搜索来写数据最小化这个问题的办法就是降低磁盘搜索次数磁盘搜索是性能的很大瓶颈这个问题在数据大量增长以至于无法使用有效的缓存时尤为明显或多或少随即访问大数据库时，就必然会有至少一次磁盘搜索来读数据，两次磁盘搜索来写数据最小化这个问题的办法就是降低磁盘搜索次数增加有效磁盘马达数量（这能减少搜索时的开销）或者将不同的文件链接到不同的磁盘上又或者分盘使用符号链接意思是，把MylSAM表的索引文件和/或数据文件从数据目录下通常的地方链接到其他磁盘上（这也是分盘）如果这个磁盘没有其他用途的话，这对读写次数都比较好详情请看761Using SymbolicLinks%分盘如果有好几个磁盘，就把第一个区块放在第一个磁盘，把第二个区块放在第二个磁盘，以此类推这意味着正常的数据大小比分盘后的数据小（或者完全一样），这能获得更好性能分盘完全依赖于操作系统以及分盘的大小，因此要用不同的分盘大小基准测试应用程序详情请看”
7.
1.5Using YourOwn Benchmarks基准测试的速度的不同完全依赖于分盘大小依赖分o盘设置参数以及磁盘数量，会得到大量不同的测量结果必须随机或者顺序选择优化方法可能会为了可靠性采用RAID0+1,这时:就必须用2*N个驱动去来保存N个驱动器上的数据如果有足够的内存这可能是最好的方法不过，这也需要使用卷管理软件来有效地管理数据另一个好办法是RAID的级别根据数据的重要性而定例如，把能重新生成的有点重要的数据保存在RAID0磁盘上,把很重要的数据如主机信息日志等保存在RAID0+1或者RAID N磁盘上RAID N在有很多个写入时可能会有问题，因为会同时请求更新同一个字节位在Linux上，可以用hdparm来配置磁盘接口以获得更好的性能（在负载下高达100%也不是不可能的）以下hdparm配置选项对MySQL就很合适，对其他应用程序可能也不错hdparm-m16-d1注意，当使用这个命令之后性能和可靠性会依赖硬件，因此我们强烈建议在使用hdparm后一定要做测试请查阅hdparm的手册如果没有正确使用hdparm,则可能导致文件系统冲突，所以在试验之前备份一下还可以在数据库使用的文件系统上设置其参数如果无需知道文件的最后访问时间（这对数据库系统没用），则在挂载文件系统时使用-o noatime选项这就会略过更新文件系统节点的最后访问时间，也就减少了磁盘搜索在很多操作系统上，可以在挂载文件系统是使用-o async选项以异步更新它如果你的机器相当的稳定，这会带来性能提升但可靠性并没牺牲多少（默认只能在Linux上这样用）使用符号链接
1.可以把数据表或者数据库移动到别的FI录下，然后用符号链接到新的位置来代替你可能想这么做，例如，想要把表分布到不同的磁盘上以提高系统速度，就把它们移动到有更多剩余空间的磁盘上建议只是把数据库链接到其他磁盘I-,数据表的链接是最后的选择°在上符号链接数据库
1.1Unix在Unix上，给数据库做符号链接的方法是先在其他磁盘上创建一个目录，然后再把它链接到MySQL数据文件目录下shell mkdir/dr1/databases/testshell In-s/dr1/databases/test/path/to/datadirMySQL不支持把一个目录链接成多个数据库只要没有在数据库间做符号链接，那么它就没问题假使在MySQL数据文件目录下已经有一个数据库db1了，然后把db1链接到db2:shell cd/path/to/datadirshell In-s db1db2现在，在db1中的表tbl_a,也会在db2中出现如果有一个客户端要更新db
1.tbl_a而另一个要更新db
1.tbl_a,这时就会出问题了如果确实需要这么做，那么就修改一个源文件要修改的文件根据MySQL版本不同而不同MySQL
4.0或更新，在mysys/my_symlink.c,文件中找到以下语句if!MyFlagsMY_RESOLVE_LINK||!lstatS_ISLNKstat_buff.st_modeMySQL
4.0以前，在mysys/mf_format.c文件中找到如下语句if flag32||!lstatto,stat_buffS_ISLNKstat_buff.st_mode然后把这个语句改成⑴if在Windows上，在编译MySQL时使用选项・DUSE_SYMDIR就能内置支持目录符号链接这可以让你把不同的数据库放到不同的磁盘上在上符号链接数据表
1.2Unix在MySQL
4.0以前，除非特别小心否则不要链接数据表有一个问题是，当在一个符号链接表上执行ALTER TABLE,REPAIR TABLE,或OPTIMIZE TABLE时，符号链接就会被删除然后替换成原来的文件这是因为执行这些语句时，需要在数据库目录下创建临时文件，然后在操作完成后把临时文件替换到原来的文件中去最好不要在不能很好支持realpath调用的操作系统上链接数据表不过至少Linux和Solaris支持realpath执行SHOWVARIABLES LIKE，have_symlink语句来检查你的系统是否支持符号链接在MySQL4Q MylSAM表完全支持符号链接而其他表类型如果也做符号链接的话，则很可能在执行语句前会碰到一些奇怪的问题MySQL
4.0中的MylSAM表符号链接以如下方式工作在数据目录下，总是有表定义文件，以及数据文件，以及索引文件数据和索引文件可以被移动到任何处然后用符号链接代替，但是表定义文件不可以可以分别把数据和索引文件链接到不同目录下在mysqld没有运行时可以用命令行In-s手工完成符号链接如果用SQL,可以在CREATE TABLE时使用选项DATADIRECTORY和INDEX DIRECTORY告诉服务器使用符号链接详情请看”
14.
2.6CREATE TABLESyntax%myisamchk不会替换符号链接的数据或索引文件它直接在符号链接指向的文件上操作任何临时文件都创建在数据或索引文件所在的目录下当删除一个符号链接的表后，链接表及其指向的表都会被删除这就是为什么不能以root身份运行mysqld的原因，同样地，不要允许用户有权写MySQL数据库目录如果.ALTE.TABL.…RENAM.语句重命名一个表且没有把它移动到其他数据库下，那么在数据库目录下的文件就被改名了，相应地，它指向的数据或索引文件也改名了如果.ALTE.TABL....RENAM.语句把表移动到其他数据库下，则这个表就移动到其他数据库目录下旧的链接及其所指向的文件都被删掉换言之，新的表就不再被符号链接了如果没有使用符号链接，那么就给mysqld增加选项--skip-symbolic-links确保无人能删除或重命名数据文件目录以外的文件在MySQL
4.
0.15以前，SHOW CREATE TABLE语句不会报告一个表是否有符号链接mysqldump也一样，它是用SHOWCREATE TABLE来产生CREATETABLE语句的表符号链接操作还不支持ALTERTABLE操作会忽略DATA DIRECTORY和INDEX DIRECTORY表选项BACKUP TABLE和RESTORE TABLE也没考虑符号链接.frm文件肯定不能被符号链接（在前面提到，索引及数据文件可以被符号链接）企图这么做（比如用同义）的话就会导致一些错误假设有在数据库目录下有一个数据库db1,库里有一个表tbl1,在db1目录下把tbl2符号链接到tbl1:shell cd/path/to/datadir/db1shell In-s tbH.frm tbl
2.frmshell In-s tbh.MYD tbl
2.MYDshell In-s tbll.MYl tbl
2.MYI现在如果有一个线程读取dbl.tbll而另一个线程更新db
1.tbl2时就有问题了查询缓存就会被愚弄了（它认为tbl1没有被更新，因此返回out-of-data结果）在tbl2上执行ALTER语句也会失败在上符号链接数据库
1.3Windows从MySQL
3.
0.32sec上述结果是在Pentium II400MHz的系统上执行得到的它告诉我们MySQL在这个系统上可以在
0.32秒内执行1,000,000次简单的加法运算所有的MySQL函数都应该被最优化，不过仍然有些函数例外BENCHMARK是一个用于检查查询语句中是否存在问题的非常好的工具数据库优化二MySQL作者叶金荣，出处:IT专家网，责任编辑李书琴，2008-06-1109:55EXPLAIN语句可以被当作DESCRIBE的同义词来用，也可以用来获取一个MySQL要执行的SELECT语句的相关信息EXPLAIN tbl_name语法和DESCRIBE tbl_name或SHOW COLUMNSFROM tbl_name一样wMySQL数据库优化一
1.EXPLAL语法得至ij SELEC.的相关信息或者EXPLAIN tbl_nameEXPLAIN SELECTselect,optionstablelN语句可以被当作DESCR旧E的同义词来用，也可以用来获取一个MySQL要执行的SELECT语句的相关信息EXPLAIN tbl_name语法和DESCRIBE tbl_name或SHOW COLUMNSFROM tbl_name一样当在一个SELECT语句前使用关键字EXPLAIN时，MYSQL会解释了即将如何运行该SELECT语句，它显示了表如何连接、连接的顺序等信息本章节主要讲述了第二种EXPLAIN用法在EXPLAIN的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让SELECT运行更快如果由于不恰当使用索引而引起一些问题的话，可以运行ANALYZE TABLE来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择您还可以查看优化程序是否以最佳的顺序来连接数据表为了让优化程序按照SELECT语句中的表名的顺序做连接，可以在查询的开始使用SELECT STRAIGHT_JOIN而不只是SELECToEXPLAIN返回了一行记录，它包括了SELECT语句中用到的各个表的信息这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来MySQL用一次扫描多次连接single-sweep,multi-join的方法来解决连接这意味着MySQL从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推当所有从MySQL
4.0开始，默认支持符号链接如果不需要，用skip-symbolic-links选项关闭它[mysqld]skip-symbolic-links在MySQL
4.0以前，默认不支持符号链接想要支持它，就要在my.cnf或my.ini，文件中增加如下内容[mysqld]symbolic-links在Windows上，在MySQL数据文件目录下创建一个包含目标目录路径的文件来做符号链接这个文件的名字叫db_name.sym,db_name是数据库的名字假设MySQL数据文件目录是C:\mysql\data；现在想要把数据库foo放在D:\data\foo目录下按以下方法设置确认D:\data\f目录存在，如果有必要就创建它如果在数据文件目录下已经存在一个数据库目录名为f,那么就把它移动到下D:\data否则，符号链接就不生效移动数据库的时候最好不要运行服务器，以避免可能出现的问题创建一个文件C:\mysql\data\foo.sym,它的内容是路径D:\data\foo\o之后，数据库foo下的所有表都会创建到D:\data\f下注意，如果在MySQL数据文件目录下已经存在该数据库目录，那么就不会使用符号链接了的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理在MySQL version
4.1中，EXPLAIN输出的结果格式改变了，使得它更适合例如UNION语句、子查询以及派生表的结构更令人注意的是，它新增了2个字段id和select_type当你使用早于MySQL
4.1的版本就看不到这些字段了oEXPLAIN结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段id本次SELECT的标识符在查询中每个SELECT都有一个顺序的数值select_typeSELECT的类型，可能会有以下几种SIMPLE简单的SELECT（没有使用UNION或子查询）PRIMARY最外层的SELECToUNION第二层，在SELECT之后使用了UNIONDEPENDENT UNIONUNION语句中的第二个SELECT,依赖于外部子查询SUBQUERY子查询中的第一个SELECTDEPENDENT SUBQUERY子查询中的第一个SUBQUERY依赖于外部的子查询DERIVED派生表SELECT（FROM子句中的子查询）table记录查询引用的表type表连接类型以下列出了各种不同类型的表连接，依次是从最好的到最差的:system表只有一行记录（等于系统表）这是const表连接类型的一个特例const表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个恒定值const表查询起来非常快，因为只要读取一次!const用于在和PRIMARY KEY或UNIQUE索引中有固定值比较的情形下面的几个查询中，tbl_name就是const表了SELECT*FROM tbl_name WHEREprimary_key=1;SELECT*FROM tbl_nameWHERE primary_key_part1=1AND primary_key_part2=2;eq_ref从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合与const类型不同的是，这是最好的连接类型它用在索引所有部分都用于做连接并且这个索引是一个PRIMARY KEY或UNIQUE类型eq_ref可以用于在进行做比较时检索字段比较的值可以是固定值或者是表达式，表达式中可以使用表里的字段，它们在读表之前已经准备好了以下的几个例子中，MySQL使用了eq_ref连接来处理rentable:SELECT*FROM ref_table,other_tableWHERE refJable.key column=other_table.column;SELECT*FROM refjable,other_tableWHERE ref_table.key_columnjDart1=other_table.columnAND ref_table.key_column_part2=1;ref该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合ref用于连接程序使用键的最左前缀或者是该键不是PRIMARY KEY或UNIQUE索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型ref还可以用于检索字段使用=操作符来比较的时候以下的几个例子中，MySQL将使用ref来处理refjable:SELECT*FROM refjableWHEREkey_column=expr;SELECT*FROM ref_table,other_tableWHERE ref_table.key_column=other_table.column;SELECT*FROM refjable,other_tableWHERE refjable.key_columnjDart1=other_table.columnAND ref_table.key_column_part2=1;ref ornull这种连接类型类似ref,不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录这种连接类型的优化是从MySQL
4.
1.1开始的，它经常用于子查询在以下的例子中，MySQL使用ref_or_null类型来处理ref_table:SELECT*FROM refjableWHEREkey_column=expr ORkey_column ISNULL;index_merge这种连接类型意味着使用了Index Merge优化方法这种情况下，key字段包括了所有使用的索引，keyjen包括了使用的键的最长部分详情请看”
7.
2.5How MySQLOptimizes ORClauses10unique_subquery这种类型用例如一下形式的IN子查询来替换ref:value INSELECT primarykey FROMsingle_table WHEREsome_exprunique_subquery只是用来完全替换子查询的索引查找函数效率更高了index_subquery这种连接类型类似unique_subquery它用子查询来代替IN,不过它用于在子查询中没有唯一索引的情况下，例如以下形式value INSELECT key_column FROMsingle_table WHEREsome_exprrange只有在给定范围的记录才会被取出来，利用索引来取得一条记录key字段表示使用了哪个索引keyjen字段包括了使用的键的最长部分这种类型时ref字段值是NULLo range用于将某个字段和一个定植用以下任何操作符比较时=,,,=,,=,ISNULL,=,BETWEEN,或IN:SELECT*FROM tbLnameWHERE key_column=10;SELECT*FROM tbl_nameWHERE key_column BETWEEN10and20;SELECT*FROMtbl_nameWHEREkey_column IN10,20,30;SELECT*FROMtbl_nameWHERE keyjDart1=10AND key_part2IN10,20,30;index连接类型跟ALL一样，不同的是它只扫描索引树它通常会比ALL快点，因为索引文件通常比数据文件小MySQL在查询的字段知识单独的索引的一部分的情况下使用这种连接类型ALL将对该表做全部扫描以和从前一个表中取得的记录作联合这时候如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的正常地，可以通过增加索引使得能从表中更快的取得记录以避免ALLopossible_keyspossible_keys字段是指MySQL在搜索表记录时可能使用哪个索引注意，这个字段完全独立于EXPLAIN显示的表顺序这就意味着possible_keys里面所包含的索引可能在实际的使用中没用到如果这个字段的值是NULL,就表示没有索引被用到这种情况下，就可以检查WHERE子句中哪些字段那些字段适合增加索引以提高查询的性能就这样，创建一下索引，然后再用EXPLAIN检查一下详细的查看章节T422ALTERTABLESyntax”想看表都有什么索引，可以通过SHOW INDEXFROMtbl_name来看工keykey字段显示了MySQL实际上要用的索引当没有任何索引被用到的时候，这个字段的值就是NULL想要让MySQL强行使用或者忽略在possible_keys字段中的索引列表，可以在查询语句中使用关键字FORCE INDEX,USE INDEX,或IGNORE INDEX如果是MylSAM和BDB类型表，可以使用ANALYZE TABLE来帮助分析使用使用哪个索引更好如果是MylSAM类型表，运行命令myisamchk-analyze也是一样的效果详细的可以查看章节T
452.1ANALYZETABLESyntax”和
5.
7.2Table Maintenanceand CrashRecovery%key_lenkeyjen字段显示了MySQL使用索引的长度当key字段的值为NULL时，索引的长度就是NULL注意，keyjen的值可以告诉你在联合索引中MySQL会真正使用了哪些索引refref字段显示了哪些字段或者常量被用来和key配合从表中查询记录出来rowsrows字段显示了MySQL认为在查询中应该检索的记录数Extra本字段显示了查询中MySQL的附加信息以下是这个字段的几个不同值的解释DistinctMySQL当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了Not existsMySQL在查询时做一个LEFTJOIN优化时，当它在当前表中找到了和前一条记录符合LEFTJOIN条件后，就不再搜索更多的记录了下面是一个这种类型的查询例子SELECT*FROMt1LEFTJOINt2ON t
1.id=t
2.idWHERE t
2.idI8NULL;。
个人认证
优秀文档
获得点赞 0
