数据库mysql优化小技巧

　　MySQL的数据库优化思想，源远流长，对产品系统来说，优化很重要，它涉及了如何高效的处理数据而不占据系统的内存，那么mysql数据库优化有哪些方法呢？

　　数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

　　本章主要讲解了几种优化MySQL的方法，并且给出了例子。记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力。

　　优化概述

　　让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。

　　最常见的系统瓶颈有以下几种：

　　磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个磁盘中。

　　磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。

　　CPU周期。数据存储在主内存中(或者它已经在主内存中了)，这就需要处理这些数据以得到想要的结果。存在多个?

　　内存带宽。当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。

　　1.1MySQL设计的局限性

　　当使用MyISAM存储引擎时，MySQL会使用一个快速数据表锁以允许同时多个读取和一个写入。这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。如果这种情况在某个表中存在，可以使用另一种表类型。详情请看"15MySQLStorageEnginesandTableTypes"。

　　MySQL可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表(发生错误时不能回滚)，有以下几条规则：

　　所有的字段都有默认值

　　如果字段中插入了一个"错误"的值，比如在数字类型字段中插入过大数值，那么MySQL会将该字段值置为"最可能的值"而不是给出一个错误。数字类型的值是0，最小或者最大的可能值。字符串类型，不是空字符串就是字段所能存储的最大长度。

　　所有的计算表达式都会返回一个值而报告条件错误，例如1/0返回NULL。

　　这些规则隐含的意思是，不能使用MySQL来检查字段内容。相反地，必须在存储到数据库前在应用程序中来检查。

　　1.2应用设计的可移植性

　　由于各种不同的数据库实现了各自的SQL标准，这就需要我们尽量使用可移植的SQL应用。查询和插入操作很容易就能做到可移植，不过由于更多的约束条件的要求就越发困难。想要让一个应用在各种数据库系统上快速运行，就变得更困难了。

　　为了能让一个复杂的应用做到可移植，就要先看这个应用运行于哪种数据库系统之上，然后看这些数据库系统都支持哪些特性。

　　每个数据库系统都有某些不足。也就是说，由于设计上的一些妥协，导致了性能上的差异。

　　可以用MySQL的crash-me程序来看选定的数据库服务器上可以使用的函数，类型，限制等。crash-me不会检查各种可能存在的特性，不过这仍然是合乎情理的理解，大约做了450次测试。

　　一个crash-me的信息类型的例子就是，它会告诉您如果想使用Informix或DB2的话，就不能使字段名长度超过18个字符。

　　crash-me程序和MySQL基准使每个准数据库都实现了的。可以通过阅读这些基准程序是怎么写的，自己就大概有怎样做才能让程序独立于各种数据库这方面的想法了。这些程序可以在MySQL源代码的`sql-bench'目录下找到。他们大部分都是用Perl写的，并且使用DBI接口。由于它提供了独立于数据库的各种访问方式，因此用DBI来解决各种移植性的问题。

　　如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法。例如，MySQL对于MyISAM类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。作为Oracle来说，它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)。事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。

　　为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于C++在很多系统上都可以使用，因此使用C++作为数据库的基类结果很合适。

　　如果使用了某些数据库独有的特定功能(比如REPLACE语句就只在MySQL中独有)，这就需要通过编写替代方法来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。

　　在MySQL中，可以在查询语句中使用/*!*/语法来增加MySQL特有的关键字。然而在很多其他数据库中，/**/却被当成了注释(并且被忽略)。

　　如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果，这可能会有更高的性能。通过让旧数据在一定时间后过期，来合理的更新缓存。这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常。

　　这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。

　　一个实现应用层缓存的可选方案是使用MySQL的查询缓存(querycache)。启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用。它大大简化了应用程序，详情请看"5.11TheMySQLQueryCache"。

　　.3我们都用MySQL来做什么

　　本章描述了一个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直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了。

　　这个系统工作的很好，并且在适度的SunUltraSPARC工作站(2x200MHz)上处理数据没有任何问题。最终这个系统移植到了Linux上。

　　.4MySQL基准套件

　　本章本来要包括MySQL基准套件(以及crash-me)的技术描述的，但是至今还未写。现在，您可以通过查看MySQL发布源代码`sql-bench'目录下的代码以及结果有一个更好的想法。

　　基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差。

　　请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。

　　想要使用基准套件，必备以下几个条件：

　　基准脚本是用Perl写的，它用Perl的DBI模块来连接数据库，因此必须安装DBI模块。并且还需要每个要做测试的服务器上都有特定的BDB驱动程序。例如，为了测试MySQL、PostgreSQL和DB2，就必须安装DBD::mysql,DBD::Pg及DBD::DB2模块。

　　取得MySQL的分发源代码后，就能在`sql-bench'目录下看到基准套件。想要运行这些基准测试，请先搭建好服务，然后进入`sql-bench'目录，执行run-all-tests脚本：

　　shell>cdsql-bench

　　shell>perlrun-all-tests--server=server_name

　　server_name可以是任何一个可用的服务。想要列出所有的可用选项和支持的服务，只要调用以下命令：

　　shell>perlrun-all-tests--help

　　crash-me脚本也是放在`sql-bench'目录下。crash-me通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制。例如，它可以判断：

　　·都支持什么字段类型

　　·支持多少索引

　　·支持什么样的函数

　　·能支持多大的查询

　　·VARCHAR字段类型能支持多大

　　可以从http://dev.mysql.com/tech-resources/crash-me.php上找到各种不同数据库crash-me的结果。

　　1.5使用您自己的基准

　　请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈(或者使用一个假的模块来代替)之后，就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。

　　从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看"7.1.4TheMySQLBenchmarkSuite"。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。

　　另一个基准套件是开放源码的数据库基准，可以在http://osdb.sourceforge.net上找到。

　　当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个(测试过的)生产系统也遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计(例如，在高负载下扫描数据表的表现不好)、操作系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。

　　为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试!可以使用SuperSmack，在http://jeremy.zawodny.com/mysql/super-smack可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试。

　　2、优化SELECT语句及其他查询

　　首先，影响所有语句的一个因素是：您的权限设置越复杂，那么开销就越大。

　　使用比较简单的GRANT语句能让MySQL减少在客户端执行语句时权限检查的开销。例如，如果没有设定任何表级或者字段级的权限，那么服务器就无需检查tables_priv和columns_priv表的记录了。同样地，如果没有对帐户设定任何资源限制的话，那么服务器也就无需做资源使用统计了。如果有大量查询的话，花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的。

　　如果问题处在一些MySQL特定的表达式或者函数上，则可以通过mysql客户端程序使用BENCHMARK()函数做一个定时测试。它的语法是：BENCHMARK(loop_count,expression)。例如：

　　mysql>SELECTBENCHMARK(1000000,1+1);

　　+------------------------+

　　|BENCHMARK(1000000,1+1)|

　　+------------------------+

　　|0|

　　+------------------------+

　　1rowinset(0.32sec)

　　上述结果是在PentiumII400MHz的系统上执行得到的。它告诉我们：MySQL在这个系统上可以在0.32秒内执行1,000,000次简单的加法运算。

　　所有的MySQL函数都应该被最优化，不过仍然有些函数例外。BENCHMARK()是一个用于检查查询语句中是否存在问题的非常好的工具。

　　1.EXPLAIN语法(得到SELECT的相关信息)

　　EXPLAINtbl_name

　　或者:

　　EXPLAINSELECTselect_options

　　tableIN语句可以被当作DESCRIBE的同义词来用，也可以用来获取一个MySQL要执行的SELECT语句的相关信息。EXPLAINtbl_name语法和DESCRIBEtbl_name或SHOWCOLUMNSFROMtbl_name一样。当在一个SELECT语句前使用关键字EXPLAIN时，MYSQL会解释了即将如何运行该SELECT语句，它显示了表如何连接、连接的顺序等信息。本章节主要讲述了第二种EXPLAIN用法。

　　在EXPLAIN的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让SELECT运行更快。

　　如果由于不恰当使用索引而引起一些问题的话，可以运行ANALYZETABLE来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择。

　　您还可以查看优化程序是否以最佳的顺序来连接数据表。为了让优化程序按照SELECT语句中的表名的顺序做连接，可以在查询的开始使用SELECTSTRAIGHT_JOIN而不只是SELECT。

　　EXPLAIN返回了一行记录，它包括了SELECT语句中用到的各个表的信息。这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来。MySQL用一次扫描多次连接(single-sweep,multi-join)的方法来解决连接。这意味着MySQL从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理。

　　在MySQLversion4.1中，EXPLAIN输出的结果格式改变了，使得它更适合例如UNION语句、子查询以及派生表的结构。更令人注意的是，它新增了2个字段：id和select_type。当你使用早于MySQL4.1的版本就看不到这些字段了。

　　EXPLAIN结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段：

　　EXPLAIN结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段：

　　id

　　本次SELECT的标识符。在查询中每个SELECT都有一个顺序的数值。

　　select_type

　　SELECT的类型，可能会有以下几种：

　　SIMPLE

　　简单的SELECT(没有使用UNION或子查询)

　　PRIMARY

　　最外层的SELECT。

　　UNION

　　第二层，在SELECT之后使用了UNION。

　　DEPENDENTUNION

　　UNION语句中的第二个SELECT，依赖于外部子查询

　　SUBQUERY

　　子查询中的第一个SELECT

　　DEPENDENTSUBQUERY

　　子查询中的第一个SUBQUERY依赖于外部的子查询

　　DERIVED

　　派生表SELECT(FROM子句中的子查询)

　　table

　　记录查询引用的表。

　　type

　　表连接类型。以下列出了各种不同类型的表连接，依次是从最好的到最差的：

　　system

　　表只有一行记录(等于系统表)。这是const表连接类型的一个特例。

　　const

　　表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。const表查询起来非常快，因为只要读取一次!const用于在和PRIMARYKEY或UNIQUE索引中有固定值比较的情形。下面的几个查询中，tbl_name就是const表了：

　　SELECT*FROMtbl_nameWHEREprimary_key=1;

　　SELECT*FROMtbl_name

　　WHEREprimary_key_part1=1ANDprimary_key_part2=2;

　　eq_ref

　　从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与const类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个PRIMARYKEY或UNIQUE类型。eq_ref可以用于在进行"="做比较时检索字段。比较的值可以是固定值或者是表达式，表达式中可以使用表里的字段，它们在读表之前已经准备好了。以下的几个例子中，MySQL使用了eq_ref连接来处理ref_table：

　　SELECT*FROMref_table,other_table

　　WHEREref_table.key_column=other_table.column;

　　SELECT*FROMref_table,other_table

　　WHEREref_table.key_column_part1=other_table.columnANDref_table.key_column_part2=1;

　　ref

　　该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref用于连接程序使用键的最左前缀或者是该键不是PRIMARYKEY或UNIQUE索引(换句话说，就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。ref还可以用于检索字段使用=操作符来比较的时候。以下的几个例子中，MySQL将使用ref来处理ref_table：

　　SELECT*FROMref_tableWHEREkey_column=expr;

　　SELECT*FROMref_table,other_table

　　WHEREref_table.key_column=other_table.column;

　　SELECT*FROMref_table,other_table

　　WHEREref_table.key_column_part1=other_table.column

　　ANDref_table.key_column_part2=1;

　　ref_or_null

　　这种连接类型类似ref，不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录。这种连接类型的优化是从MySQL4.1.1开始的，它经常用于子查询。在以下的例子中，MySQL使用ref_or_null类型来处理ref_table：

　　SELECT*FROMref_table

　　WHEREkey_column=exprORkey_columnISNULL;

　　index_merge

　　这种连接类型意味着使用了IndexMerge优化方法。这种情况下，key字段包括了所有使用的索引，key_len包括了使用的键的最长部分。详情请看"7.2.5HowMySQLOptimizesORClauses"。

　　unique_subquery

　　这种类型用例如一下形式的IN子查询来替换ref：

　　valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)

　　unique_subquery只是用来完全替换子查询的索引查找函数效率更高了。

　　index_subquery

　　这种连接类型类似unique_subquery。它用子查询来代替IN，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：

　　valueIN(SELECTkey_columnFROMsingle_tableWHEREsome_expr)

　　range

　　只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key字段表示使用了哪个索引。key_len字段包括了使用的键的最长部分。这种类型时ref字段值是NULL。range用于将某个字段和一个定植用以下任何操作符比较时=,<>,>,>=,<,<=,ISNULL,<=>,BETWEEN,或IN：

　　SELECT*FROMtbl_name

　　WHEREkey_column=10;

　　SELECT*FROMtbl_name

　　WHEREkey_columnBETWEEN10and20;

　　SELECT*FROMtbl_name

　　WHEREkey_columnIN(10,20,30);

　　SELECT*FROMtbl_name

　　WHEREkey_part1=10ANDkey_part2IN(10,20,30);

　　index

　　连接类型跟ALL一样，不同的是它只扫描索引树。它通常会比ALL快点，因为索引文件通常比数据文件小。MySQL在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。

　　ALL

　　将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为const的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免ALL。

　　possible_keys

　　possible_keys字段是指MySQL在搜索表记录时可能使用哪个索引。注意，这个字段完全独立于EXPLAIN显示的表顺序。这就意味着possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是NULL，就表示没有索引被用到。这种情况下，就可以检查WHERE子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样，创建一下索引，然后再用EXPLAIN检查一下。详细的查看章节"14.2.2ALTERTABLESyntax"。想看表都有什么索引，可以通过SHOWINDEXFROMtbl_name来看。

　　key

　　key字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是NULL。想要让MySQL强行使用或者忽略在possible_keys字段中的索引列表，可以在查询语句中使用关键字FORCEINDEX,USEINDEX,或IGNOREINDEX。如果是MyISAM和BDB类型表，可以使用ANALYZETABLE来帮助分析使用使用哪个索引更好。如果是MyISAM类型表，运行命令myisamchk--analyze也是一样的效果。详细的可以查看章节"14.5.2.1ANALYZETABLESyntax"和"5.7.2TableMaintenanceandCrashRecovery"。

　　key_len

　　key_len字段显示了MySQL使用索引的长度。当key字段的值为NULL时，索引的长度就是NULL。注意，key_len的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。

　　ref

　　ref字段显示了哪些字段或者常量被用来和key配合从表中查询记录出来。

　　rows

　　rows字段显示了MySQL认为在查询中应该检索的记录数。

　　Extra

　　本字段显示了查询中MySQL的附加信息。以下是这个字段的几个不同值的解释：

　　Distinct

　　MySQL当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。

　　Notexists

　　MySQL在查询时做一个LEFTJOIN优化时，当它在当前表中找到了和前一条记录符合LEFTJOIN条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：

　　SELECT*FROMt1LEFTJOINt2ONt1.id=t2.id

　　WHEREt2.idISNULL;

　　假使t2.id定义为NOTNULL。这种情况下，MySQL将会扫描表t1并且用t1.id的值在t2中查找记录。当在t2中找到一条匹配的记录时，这就意味着t2.id肯定不会都是NULL，就不会再在t2中查找相同id值的其他记录了。也可以这么说，对于t1中的每个记录，MySQL只需要在t2中做一次查找，而不管在t2中实际有多少匹配的记录。

　　rangecheckedforeachrecord(indexmap:#)

　　MySQL没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引(如果有的话)，并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。

　　Usingfilesort

　　MySQL需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合WHERE条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。详情请看"7.2.9HowMySQLOptimizesORDERBY"。

　　Usingindex

　　字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。

　　Usingtemporary

　　MySQL需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了GROUPBY和ORDERBY子句，它以不同的方式列出了各个字段。

　　Usingwhere

　　WHERE子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的Extra字段值不是Usingwhere并且表连接类型是ALL或index时可能表示有问题。

　　如果你想要让查询尽可能的快，那么就应该注意Extra字段的值为Usingfilesort和Usingtemporary的情况。

　　你可以通过EXPLAIN的结果中rows字段的值的乘积大概地知道本次连接表现如何。它可以粗略地告诉我们MySQL在查询过程中会查询多少条记录。如果是使用系统变量max_join_size来取得查询结果，这个乘积还可以用来确定会执行哪些多表SELECT语句。

　　下面的例子展示了如何通过EXPLAIN提供的信息来较大程度地优化多表联合查询的性能。

　　假设有下面的SELECT语句，正打算用EXPLAIN来检测：

　　EXPLAINSELECTtt.TicketNumber,tt.TimeIn,

　　tt.ProjectReference,tt.EstimatedShipDate,

　　tt.ActualShipDate,tt.ClientID,

　　tt.ServiceCodes,tt.RepetitiveID,

　　tt.CurrentProcess,tt.CurrentDPPerson,

　　tt.RecordVolume,tt.DPPrinted,et.COUNTRY,

　　et_1.COUNTRY,do.CUSTNAME

　　FROMtt,et,etASet_1,do

　　WHEREtt.SubmitTimeISNULL

　　ANDtt.ActualPC=et.EMPLOYID

　　ANDtt.AssignedPC=et_1.EMPLOYID

　　ANDtt.ClientID=do.CUSTNMBR;

　　在这个例子中，先做以下假设：

　　要比较的字段定义如下：

　　TableColumnColumnType

　　ttActualPCCHAR(10)

　　ttAssignedPCCHAR(10)

　　ttClientIDCHAR(10)

　　etEMPLOYIDCHAR(15)

　　doCUSTNMBRCHAR(15)

　　数据表的索引如下：

　　TableIndex

　　ttActualPC

　　ttAssignedPC

　　ttClientID

　　etEMPLOYID(primarykey)

　　doCUSTNMBR(primarykey)

　　tt.ActualPC的值是不均匀分布的。

　　在任何优化措施未采取之前，经过EXPLAIN分析的结果显示如下：

　　tabletypepossible_keyskeykey_lenrefrowsExtra

　　etALLPRIMARYNULLNULLNULL74

　　doALLPRIMARYNULLNULLNULL2135

　　et_1ALLPRIMARYNULLNULLNULL74

　　ttALLAssignedPC,NULLNULLNULL3872

　　ClientID,

　　ActualPC

　　rangecheckedforeachrecord(keymap:35)

　　由于字段type的对于每个表值都是ALL，这个结果意味着MySQL对所有的表做一个迪卡尔积;这就是说，每条记录的组合。这将需要花很长的时间，因为需要扫描每个表总记录数乘积的总和。在这情况下，它的积是74*2135*74*3872=45,268,558,720条记录。如果数据表更大的话，你可以想象一下需要多长的时间。

　　在这里有个问题是当字段定义一样的时候，MySQL就可以在这些字段上更快的是用索引(对ISAM类型的表来说，除非字段定义完全一样，否则不会使用索引)。在这个前提下，VARCHAR和CHAR是一样的除非它们定义的长度不一致。由于tt.ActualPC定义为CHAR(10)，et.EMPLOYID定义为CHAR(15)，二者长度不一致。

　　为了解决这个问题，需要用ALTERTABLE来加大ActualPC的长度从10到15个字符：

　　mysql>ALTERTABLEttMODIFYActualPCVARCHAR(15);

　　现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)了。再来执行一次EXPLAIN语句看看结果：

　　tabletypepossible_keyskeykey_lenrefrowsExtra

　　ttALLAssignedPC,NULLNULLNULL3872Using

　　ClientID,where

　　ActualPC

　　doALLPRIMARYNULLNULLNULL2135

　　rangecheckedforeachrecord(keymap:1)

　　et_1ALLPRIMARYNULLNULLNULL74

　　rangecheckedforeachrecord(keymap:1)

　　eteq_refPRIMARYPRIMARY15tt.ActualPC1

　　这还不够，它还可以做的更好：现在rows值乘积已经少了74倍。这次查询需要用2秒钟。

　　第二个改变是消除在比较tt.AssignedPC=et_1.EMPLOYID和tt.ClientID=do.CUSTNMBR中字段的长度不一致问题：

　　mysql>ALTERTABLEttMODIFYAssignedPCVARCHAR(15),

　　->MODIFYClientIDVARCHAR(15);

　　现在EXPLAIN的结果如下：

　　tabletypepossible_keyskeykey_lenrefrowsExtra

　　etALLPRIMARYNULLNULLNULL74

　　ttrefAssignedPC,ActualPC15et.EMPLOYID52Using

　　ClientID,where

　　ActualPC

　　et_1eq_refPRIMARYPRIMARY15tt.AssignedPC1

　　doeq_refPRIMARYPRIMARY15tt.ClientID1

　　这看起来已经是能做的最好的结果了。

　　遗留下来的问题是，MySQL默认地认为字段tt.ActualPC的值是均匀分布的，然而表tt并非如此。幸好，我们可以很方便的让MySQL分析索引的分布：

　　mysql>ANALYZETABLEtt;

　　到此为止，表连接已经优化的很完美了，EXPLAIN的结果如下：

　　tabletypepossible_keyskeykey_lenrefrowsExtra

　　ttALLAssignedPCNULLNULLNULL3872Using

　　ClientID,where

　　ActualPC

　　eteq_refPRIMARYPRIMARY15tt.ActualPC1

　　et_1eq_refPRIMARYPRIMARY15tt.AssignedPC1

　　doeq_refPRIMARYPRIMARY15tt.ClientID1

　　请注意，EXPLAIN结果中的rows字段的值也是MySQL的连接优化程序大致猜测的，请检查这个值跟真实值是否基本一致。如果不是，可以通过在SELECT语句中使用STRAIGHT_JOIN来取得更好的性能，同时可以试着在FROM

　　分句中用不同的次序列出各个表。

　　2.1.估算查询性能

　　在大多数情况下，可以通过统计磁盘搜索次数来估算查询的性能。对小表来说，通常情况下只需要搜索一次磁盘就能找到对应的记录(因为索引可能已经缓存起来了)。对大表来说，大致可以这么估算，它使用B树做索引，想要找到一条记录大概需要搜索的次数为：log(row_count)/log(index_block_length/3*2/(index_length+data_pointer_length))+1。

　　在MySQL中，一个索引块通常是1024bytes，数据指针通常是4bytes。对于一个有500,000条记录、索引长度为3bytes(mediuminteger)的表来说，根据上面的公式计算得到需要做log(500,000)/log(1024/3*2/(3+4))+1=4次搜索。

　　这个表的索引大概需要500,000*7*3/2=5.2MB的存储空间(假定典型的索引缓冲区的2/3)，因此应该会有更多的索引在内存中，并且可能只需要1到2次调用就能找到对应的记录。

　　对于写来说，大概需要4次(甚至更多)搜索才能找到新的索引位置，更新记录时通常需要2次搜索。

　　请注意，前面的讨论中并没有提到应用程序的性能会因为logN的值越大而下降。只要所有的东西都能由操作系统或者SQL服务器缓存起来，那么性能只会因为数据表越大而稍微下降。当数据越来越大之后，就不能全部放到缓存中去了，就会越来越慢了，除非应用程序是被磁盘搜索约束的(它跟随着的logN值增加而增加)。为了避免这种情况，可以在数据量增大以后也随着增大索引缓存容量。对MyISAM类型表来说，索引缓存容量是由系统变量key_buffer_size控制的。

　　2.2SELECT查询的速度

　　通常情况下，想要让一个比较慢的SELECT...WHERE查询变得更快的第一件事就是，先检查看看是否可以增加索引。所有对不同表的访问都通常使用索引。可以使用EXPLAIN语句来判断SELECT使用了哪些索引。详情请看"7.4.5HowMySQLUsesIndexes"和"7.2.1EXPLAINSyntax(GetInformationAboutaSELECT)"。

　　以下是几个常用的提高MyISAM表查询速度的忠告：

　　想要让MySQL将查询优化的速度更快些，可以在数据表已经加载完全部数据后执行行ANALYZETABLE或运行myisamchk--analyze命令。它更新了每个索引部分的值，这个值意味着相同记录的平均值(对于唯一索引来说，这个值则一直都是1)。MySQL就会在当你使用基于一个非恒量表达式的两表连接时，根据这个值来决定使用哪个索引。想要查看结果，可以在分析完数据表后运行SHOWINDEXFROMtbl_name查看Cardinality字段的值。myisamchk--description--verbose显示了索引的分布信息。

　　想要根据一个索引来排序数据，可以运行myisamchk--sort-index--sort-records=1(如果想要在索引1上做排序)。这对于有一个唯一索引并且想根据这个索引的顺序依次读取记录的话来说是一个提高查询速度的好办法。不过要注意的是，第一次在一个大表上做排序的话将会耗费很长时间。

　　2.3MySQL如何优化WHERE子句

　　这个章节讲述了优化程序如何处理WHERE子句。例子中使用了SELECT语句，但是在DELETE和UPDATE语句中对WHERE子句的优化是一样的。注意，关于MySQL优化的工作还在继续，因此本章节还没结束。MySQL做了很多优化工作，而不仅仅是文档中提到的这些。

　　MySQL的一些优化做法如下：

　　去除不必要的括号：

　　((aANDb)ANDcOR(((aANDb)AND(cANDd))))

　　->(aANDbANDc)OR(aANDbANDcANDd)

　　展开常量：

　　(a

　　->b>5ANDb=cANDa=5

　　去除常量条件(在展开常量时需要)：

　　(B>=5ANDB=5)OR(B=6AND5=5)OR(B=7AND5=6)

　　->B=5ORB=6

　　常量表达示在索引中只计算一次

　　在单独一个表上做COUNT(*)而不使用WHERE时，对于MyISAM和HEAP表就会直接从表信息中检索结果。在单独一个表上做任何表NOTNULL达式查询时也是这样做。

　　预先探测无效的常量表达式。MySQL会快速探测一些不可能的SELECT语句并且不返回任何记录。

　　当没用GROUPBY或分组函数时，HAVING和WHERE合并(COUNT(),MIN()等也是如此)。

　　为表连接中的每个表构造一个简洁的WHERE语句，以得到更快的WHERE计算值并且尽快跳过记录。

　　查询中所有的常量表都会比其他表更早读取。一个常量表符合以下几个条件：

　　空表或者只有一条记录。

　　与在一个UNIQUE索引、或一个PRIMARYKEY的WHERE子句一起使用的表，这里所有的索引部分和常数表达式做比较并且索引部分被定义为NOTNULL。

　　以下的几个表都会被当成常量表：

　　SELECT*FROMtWHEREprimary_key=1;

　　SELECT*FROMt1,t2

　　WHEREt1.primary_key=1ANDt2.primary_key=t1.id;

　　MySQL会进各种可能找到表连接最好的连接方法。如果在ORDERBY和GROUPBY子句中的所有字段都来自同一个表的话，那么在连接时这个表就会优先处理。

　　如果有ORDERBY子句和一个不同的GROUPBY子句，或者如果ORDERBY或GROUPBY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。

　　如果使用SQL_SMALL_RESULT，MySQL就会使用内存临时表了。

　　所有的表索引都会查询，最好的情况就是所有的索引都会被用到，除非优化程序认为全表扫描的效率更高。同时，数据表扫描是基于判断最好的索引范围超过数据表的30%。现在，优化程序复杂多了，它基于对一些附加因素的估计，例如表大小，记录总数，I/O块大小，因此就不能根据一个固定的百分比来决定是选择使用索引还是直接扫描数据表。

　　在某些情况下，MySQL可以直接从索引中取得记录而无需查询数据文件。如果所有在索引中使用的字段都是数字类型的话，只需要用索引树就能完成查询。

　　每条记录输出之前，那些没有匹配HAVING子句的就会被跳过。

　　以下几个查询速度非常快：

　　SELECTCOUNT(*)FROMtbl_name;

　　SELECTMIN(key_part1),MAX(key_part1)FROMtbl_name;

　　SELECTMAX(key_part2)FROMtbl_name

　　WHEREkey_part1=constant;

　　SELECT...FROMtbl_name

　　ORDERBYkey_part1,key_part2,...LIMIT10;

　　SELECT...FROMtbl_name

　　ORDERBYkey_part1DESC,key_part2DESC,...LIMIT10;

　　以下几个查询都是使用索引树，假使那些索引字段都是数字型：

　　SELECTkey_part1,key_part2FROMtbl_nameWHEREkey_part1=val;

　　SELECTCOUNT(*)FROMtbl_name

　　WHEREkey_part1=val1ANDkey_part2=val2;

　　SELECTkey_part2FROMtbl_nameGROUPBYkey_part1;

　　以下几个查询使用索引来取得经过顺序排序后的记录而无需经过独立的排序步骤：

　　SELECT...FROMtbl_name

　　ORDERBYkey_part1,key_part2,...;

　　SELECT...FROMtbl_name

　　ORDERBYkey_part1DESC,key_part2DESC,...;

　　2.4MySQL如何优化OR子句

　　IndexMerge方法用于使用ref,ref_or_null,或range扫描取得的记录合并起来放到一起作为结果。这种方法在表条件是或条件ref,ref_or_null,或range，并且这些条件可以用不同的键时采用。

　　"join"类型的优化是从MySQL5.0.0开始才有的，代表者在索引的性能上有着标志性的改进，因为使用老规则的话，数据库最多只能对每个引用表使用一个索引。

　　在EXPLAIN的结果中，这种方法在type字段中表现为index_merge。这种情况下，key字段包含了所有使用的索引列表，并且key_len字段包含了使用的索引的最长索引部分列表。

　　例如：

　　SELECT*FROMtbl_nameWHEREkey_part1=10ORkey_part2=20;

　　SELECT*FROMtbl_name

　　WHERE(key_part1=10ORkey_part2=20)ANDnon_key_part=30;

　　SELECT*FROMt1,t2

　　WHERE(t1.key1IN(1,2)ORt1.key2LIKE'value%')

　　ANDt2.key1=t1.some_col;

　　SELECT*FROMt1,t2

　　WHEREt1.key1=1

　　AND(t2.key1=t1.some_colORt2.key2=t1.some_col2);

　　2.5MySQL如何优化ISNULL

　　MySQL在col_nameISNULL时做和col_name=constant_value一样的优化。例如，MySQL使用索引或者范围来根据ISNULL搜索NULL。

　　SELECT*FROMtbl_nameWHEREkey_colISNULL;

　　SELECT*FROMtbl_nameWHEREkey_col<=>NULL;

　　SELECT*FROMtbl_name

　　WHEREkey_col=const1ORkey_col=const2ORkey_colISNULL;

　　如果一个WHERE子句包括了一个col_nameISNULL条件，并且这个字段声明为NOTNULL，那么这个表达式就会被优化。当字段可能无论如何都会产生NULL值时，就不会再做优化了;例如，当它来自一个LEFTJOIN中右边的一个表时。

　　MySQL4.1.1或更高会对连接col_name=exprANDcol_nameISNULL做额外的优化，常见的就是子查询。EXPLAIN当优化起作用时会显示ref_or_null。

　　优化程序会为任何索引部分处理ISNULL。

　　以下几个例子中都做优化了，假使字段a和表t2中b有索引了：

　　SELECT*FROMt1WHEREt1.a=exprORt1.aISNULL;

　　SELECT*FROMt1,t2WHEREt1.a=t2.aORt2.aISNULL;

　　SELECT*FROMt1,t2

　　WHERE(t1.a=t2.aORt2.aISNULL)ANDt2.b=t1.b;

　　SELECT*FROMt1,t2

　　WHEREt1.a=t2.aAND(t2.b=t1.bORt2.bISNULL);

　　SELECT*FROMt1,t2

　　WHERE(t1.a=t2.aANDt2.aISNULLAND...)

　　OR(t1.a=t2.aANDt2.aISNULLAND...);

　　ref_or_null首先读取引用键，然后独立扫描键值为NULL的记录。

　　请注意，优化程序只会处理一个ISNULL级别。下面的查询中，MySQL只会使用键来查询表达式(t1.a=t2.aANDt2.aISNULL)而无法使在b上使用索引部分：

　　SELECT*FROMt1,t2

　　WHERE(t1.a=t2.aANDt2.aISNULL)

　　OR(t1.b=t2.bANDt2.bISNULL);

　　2.6MySQL如何优化DISTINCT

　　在很多情况下，DISTINCT和ORDERBY一起使用时就会创建一个临时表。

　　注意，由于DISTINCT可能需要用到GROUPBY，就需要明白MySQL在ORDERBY或HAVING子句里的字段不在选中的字段列表中时是怎么处理的。详情请看"13.9.3GROUPBYwithHiddenFields"。

　　当LIMITrow_count和DISTINCT一起使用时，MySQL在找到row_count不同记录后就会立刻停止搜索了。

　　如果没有用到来自查询中任何表的字段时，MySQL在找到第一个匹配记录后就会停止搜索这些没没用到的表了。在下面的情况中，假使t1在t2前就使用了(可以通过EXPLAIN分析知道)，MySQL就会在从t2中找到第一条记录后就不再读t2了(为了能和中t1的任何特定记录匹配)：

　　SELECTDISTINCTt1.aFROMt1,t2wheret1.a=t2.a;

　　2.7MySQL如何优化LEFTJOIN和RIGHTJOIN

　　ALEFTJOINBjoin_condition在MySQL中实现如下：

　　表B依赖于表A以及其依赖的所有表。

　　表A依赖于在LEFTJOIN条件中的所有表(除了B)。

　　LEFTJOIN条件用于决定如何从表B中读取记录了(换句话说，WHERE子句中的任何条件都对此不起作用)。

　　所有标准的连接优化都会执行，例外的情况是有一个表总是在它依赖的所有表之后被读取。如果这是一个循环的依赖关系，那么MySQL会认为这是错误的。

　　所有的标准WHERE优化都会执行。

　　如果A中有一条记录匹配了WHERE子句，但是B中没有任何记录匹配ON条件，那么就会产生一条B记录，它的字段值全都被置为NULL。

　　如果使用LEFTJOIN来搜索在一些表中不存在的记录，并且WHERE部分中有检测条件：col_nameISNULL，col_name字段定义成NOTNULL的话，MySQL就会在找到一条匹配LEFTJOIN条件的记录(用于和特定的索引键做联合)后停止搜索了。

　　RIGHTJOIN的实现和LEFTJOIN类似，不过表的角色倒过来了。

　　连接优化程序计算了表连接的次序。表读取的顺序是由LEFTJOIN强行指定的，而且使用STRAIGHT_JOIN能帮助连接优化程序更快地执行，因为这就会有更少的表排队检查了。注意，这里是指如果你执行下面这种类型的查询后，MySQL就会对b做一次全表扫描，因为LEFTJOIN强制要求了必须在读d之前这么做：

　　SELECT*

　　FROMa,bLEFTJOINcON(c.key=a.key)LEFTJOINdON(d.key=a.key)

　　WHEREb.key=d.key;

　　解决这种情况的方法是按照如下方式重写查询：

　　SELECT*

　　FROMb,aLEFTJOINcON(c.key=a.key)LEFTJOINdON(d.key=a.key)

　　WHEREb.key=d.key;

　　从4.0.14开始，MySQL做如下LEFTJOIN优化：如果对产生的NULL记录WHERE条件总是假，那么LEFTJOIN就会变成一个普通的连接。

　　例如，下面的查询中如果t2.column1的值是NULL的话，WHERE子句的结果就是假了：

　　SELECT*FROMt1LEFTJOINt2ON(column1)WHEREt2.column2=5;

　　因此，这就可以安全的转换成一个普通的连接查询：

　　SELECT*FROMt1,t2WHEREt2.column2=5ANDt1.column1=t2.column1;

　　这查询起来就更快了，因为如果能有一个更好的查询计划的话，MySQL就会在t1之前就用到t2了。想要强行指定表顺序的话，可以使用STRAIGHT_JOIN。

　　2、7MySQL如何优化ORDERBY

　　在一些情况下，MySQL可以直接使用索引来满足一个ORDERBY或GROUPBY子句而无需做额外的排序。

　　尽管ORDERBY不是和索引的顺序准确匹配，索引还是可以被用到，只要不用的索引部分和所有的额外的ORDERBY字段在WHERE子句中都被包括了。下列的几个查询都会使用索引来解决ORDERBY或GROUPBY部分：

　　SELECT*FROMt1ORDERBYkey_part1,key_part2,...;

　　SELECT*FROMt1WHEREkey_part1=constantORDERBYkey_part2;

　　SELECT*FROMt1WHEREkey_part1=constantGROUPBYkey_part2;

　　SELECT*FROMt1ORDERBYkey_part1DESC,key_part2DESC;

　　SELECT*FROMt1

　　WHEREkey_part1=1ORDERBYkey_part1DESC,key_part2DESC;

　　在另一些情况下，MySQL无法使用索引来满足ORDERBY，尽管它会使用索引来找到记录来匹配WHERE子句。这些情况如下：

　　对不同的索引键做ORDERBY：

　　SELECT*FROMt1ORDERBYkey1,key2;

　　在非连续的索引键部分上做ORDERBY：

　　SELECT*FROMt1WHEREkey2=constantORDERBYkey_part2;

　　同时使用了ASC和DESC：

　　SELECT*FROMt1ORDERBYkey_part1DESC,key_part2ASC;

　　用于搜索记录的索引键和做ORDERBY的不是同一个：

　　SELECT*FROMt1WHEREkey2=constantORDERBYkey1;

　　有很多表一起做连接，而且读取的记录中在ORDERBY中的字段都不全是来自第一个非常数的表中(也就是说，在EXPLAIN分析的结果中的第一个表的连接类型不是const)。

　　使用了不同的ORDERBY和GROUPBY表达式。

　　表索引中的记录不是按序存储。例如，HASH和HEAP表就是这样。

　　通过执行EXPLAINSELECT...ORDERBY，就知道MySQL是否在查询中使用了索引。如果Extra字段的值是Usingfilesort，则说明MySQL无法使用索引。当必须对结果进行排序时，MySQL4.1以前它使用了以下filesort算法：

　　根据索引键读取记录，或者扫描数据表。那些无法匹配WHERE分句的记录都会被略过。

　　在缓冲中每条记录都用一个‘对’存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量sort_buffer_size的值而定。

　　当缓冲慢了时，就运行qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对’值都能保存在缓冲中，就无需创建临时文件了)。

　　执行上面的操作，直到所有的记录都读取出来了。

　　做一次多重合并，将多达MERGEBUFF(7)个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。

　　重复以上操作，直到剩余的块数量小于MERGEBUFF2(15)。

　　在最后一次多重合并时，只有记录的指针(排序索引键的最后部分)写到结果文件中去。

　　通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。缓冲的大小由系统变量read_rnd_buffer_size的值而定。这个步骤的代码在源文件`sql/records.cc'中。

　　这个逼近算法的一个问题是，数据库读取了2次记录：一次是估算WHERE分句时，第二次是排序时。尽管第一次都成功读取记录了(例如，做了一次全表扫描)，第二次是随机的读取(索引键已经排好序了，但是记录并没有)。

　　在MySQL4.1及更新版本中，filesort优化算法用于记录中不只包括索引键值和记录的位置，还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的filesort算法做法大致如下：

　　跟以前一样，读取匹配WHERE分句的记录。

　　相对于每个记录，都记录了一个对应的;‘元组’信息信息，包括索引键值、记录位置、以及查询中所需要的所有字段。

　　根据索引键对‘元组’信息进行排序。

　　按序读取记录，不过是从已经排序过的‘元组’列表中读取记录，而非从数据表中再读取一次。

　　使用改进后的filesort算法相比原来的，‘元组’比‘对’需要占用更长的空间，它们很少正好适合放在排序缓冲中(缓冲的大小是由sort_buffer_size的值决定的)。因此，这就可能需要有更多的I/O操作，导致改进的算法更慢。为了避免使之变慢，这种优化方法只用于排序‘元组’中额外的字段的大小总和超过系统变量max_length_for_sort_data的情况(这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载)。

　　想要提高ORDERBY的速度，首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引，可以试着遵循以下策略：

　　增加sort_buffer_size的值。

　　增加read_rnd_buffer_size的值。

　　修改tmpdir，让它指向一个有很多剩余空间的专用文件系统。如果使用MySQL4.1或更新，这个选项允许有多个路径用循环的格式。各个路径之间在Unix上用冒号(':')分隔开来，在Windows，NetWare以及OS/2上用分号(';')。可以利用这个特性将负载平均分摊给几个目录。注意：这些路径必须是分布在不同物理磁盘上的目录，而非在同一个物理磁盘上的不同目录。

　　默认情况下，MySQL也会对所有的GROUPBYcol1,col2,...查询做排序，跟ORDERBYcol1,col2,...查询一样。如果显式地包含一个有同样字段列表的ORDERBY分句，MySQL优化它的时候并不会损失速度，因为排序总是会发生。如果一个查询中包括GROUPBY，但是想要避免对结果排序的开销，可以通过使用ORDERBYNULL来取消排序。例如：

　　INSERTINTOfoo

　　SELECTa,COUNT(*)FROMbarGROUPBYaORDERBYNULL;

　　2、9MySQL如何优化LIMIT

　　在一些情况下，MySQL在碰到一个使用LIMITrow_count但没使用HAVING的查询时会做不同的处理：

　　如果只是用LIMIT来取得很少的一些记录，MySQL有时会使用索引，但是更通常的情况是做一个全表扫描。

　　如果LIMITrow_count和ORDERBY一起使用，则MySQL在找到row_count条记录后就会停止排序了，而非对整个表进行排序。

　　当LIMITrow_count和DISTINCT一起联合起来时，MySQL在找到row_count条唯一记录后就不再搜索了。

　　在某些情况下，GROUPBY可以通过按照顺序读取索引键来实现(或者在索引键上做排序)并且计算累计信息直到索引键改变了。在这种情况下，LIMITrow_count不会计算任何非必须的GROUPBY值。

　　一旦MySQL将请求的记录全数发送给客户端后，它就中止查询除非使用了SQL_CALC_FOUND_ROWS。

　　LIMIT0总是返回一个空的结果集。这对于检查查询或者取得结果字段的类型非常有用。

　　当服务器使用临时表来处理查询，则LIMITrow_count可以用来计算需要多少空间。INSERTINTOfoo

　　2、10如何避免全表扫描

　　如果MySQL需要做一次全表扫描来处理查询时，在EXPLAIN的结果中type字段的值是ALL。在以下几种条件下，MySQL就会做全表扫描：

　　数据表实在太小了，做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。

　　没有合适用于ON或WHERE分句的索引字段。

　　让索引字段和常量值比较，MySQL已经计算(基于索引树)到常量覆盖了数据表的很大部分，因此做全表扫描应该会来得更快。详情请看"7.2.4HowMySQLOptimizesWHEREClauses"。

　　通过其他字段使用了一个基数很小(很多记录匹配索引键值)的索引键。这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快。

　　对于小表来说，全表扫描通常更合适。但是对大表来说，尝试使用以下技术来避免让优化程序错误地选择全表扫描：

　　执行ANALYZETABLEtbl_name更新要扫描的表的索引键分布。

　　使用FORCEINDEX告诉MySQL，做全表扫描的话会比利用给定的索引更浪费资源。

　　SELECT*FROMt1,t2FORCEINDEX(index_for_column)

　　WHEREt1.col_name=t2.col_name;

　　启动mysqld时使用参数--max-seeks-for-key=1000或者执行SETmax_seeks_for_key=1000来告诉优化程序，所有的索引都不会导致超

　　2.11加速INSERT

　　插入一条记录花费的时间由以下几个因素决定，后面的数字大致表示影响的比例：

　　连接：(3)

　　发送查询给服务器：(2)

　　解析查询：(2)

　　插入记录：(1x记录大小)

　　插入索引：(1x索引数量)

　　关闭：(1)

　　这里并没有考虑初始化时打开数据表的开销，因为每次运行查询只会做这么一次。

　　如果是B-tree索引的话，随着索引数量的增加，插入记录的速度以logN的比例下降。

　　可以使用以下几种方法来提高插入速度：

　　如果要在同一个客户端在同一时间内插入很多记录，可以使用INSERT语句附带有多个VALUES值。这种做法比使用单一值的INSERT语句快多了(在一些情况下比较快)。如果是往一个非空的数据表里增加记录，可以调整变量bulk_insert_buffer_size的值使之更快。

　　如果要从不同的客户端中插入大量记录，使用INSERTDELAYED语句也可以提高速度。

　　对MyISAM而言，可以在SELECT语句正在运行时插入记录，只要这时候没有正在删除记录。

　　想要将一个文本文件加载到数据表中，可以使用LOADDATAINFILE。这通常是使用大量INSERT语句的20倍。

　　通过一些额外的工作，就可能让LOADDATAINFILE在数据表有大量索引的情况下运行的更快。步骤如下：

　　用CREATETABLE随便创建一个表。

　　执行FLUSHTABLES语句或mysqladminflush-tables命令。

　　执行myisamchk--keys-used=0-rq/path/to/db/tbl_name命令，删掉数据表的所有索引。

　　执行LOADDATAINFILE，数据插入到表中，由于无需更新表索引，因此这将非常快。

　　如果将来只是读取改表，运行myisampack让数据表变得更小点。详情查看"15.1.3.3CompressedTableCharacteristics"。

　　运行myisamchk-r-q/path/to/db/tbl_name重建索引。创建的索引树在写入磁盘前先保存在内存中，这省去了磁盘搜索，因此速度快多了。重建后的索引树分布非常均衡。

　　执行FLUSHTABLES语句或mysqladminflush-tables命令。

　　注意，LOADDATAINFILE将数据插入一个空表时，也会做前接优化;主要的不同在于：运行myisamchk会分配更多的临时内存用于创建索引，而执行LOADDATAINFILE命令则是让数据库服务器分配内存用于重建索引。从MySQL4.0起，可以运行ALTERTABLEtbl_nameDISABLEKEYS来代替myisamchk--keys-used=0-rq/path/to/db/tbl_name，运行ALTERTABLEtbl_nameENABLEKEYS代替myisamchk-r-q/path/to/db/tbl_name。这么做就可以省去FLUSHTABLES步骤。

　　可以在锁表后，一起执行几个语句来加速INSERT操作：

　　LOCKTABLESaWRITE;

　　INSERTINTOaVALUES(1,23),(2,34),(4,33);

　　INSERTINTOaVALUES(8,26),(6,29);

　　UNLOCKTABLES;

　　这对性能提高的好处在于：直到所有的INSERT语句都完成之后，索引缓存一次性刷新到磁盘中。通常情况是，多有少次INSERT语句就会有多数次索引缓存刷新到磁盘中的开销。如果能在一个语句中一次性插入多个值的话，显示的锁表操作也就没必要了。对事务表而言，用BEGIN/COMMIT代替LOCKTABLES来提高速度。锁表也回降低多次连接测试的总时间，尽管每个独立连接为了等待锁的最大等待时间也会增加。例如：

　　Connection1does1000inserts

　　Connections2,3,and4do1insert

　　Connection5does1000inserts

　　如果没有锁表，则连接2，3，4会在1，5之前就做完了。如果锁表了，则连接2，3，4可能在1，5之后才能完成，但是总时间可能只需要40%。MySQL的INSERT,UPDATE,DELETE操作都非常快，不过在一个语句中如果有超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多个插入，最好是在每个循环(大约1000次)的前后加上LOCKTABLES和UNLOCKTABLES，从而让其他进程也能访问数据表;这么做性能依然不错。INSERT总是比LOADDATAINFILE插入数据来得慢，因为二者的实现策略有着分明的不同。

　　想要让MyISAM表更快，在LOADDATAINFILE和INSERT时都可以增加系统变量key_buffer_size的值。

　　2.12加速UPDATE

　　UPDATE语句的优化和SELECT一样，只不过它多了额外的写入开销。写入的开销取决于要更新的记录数以及索引数。如果索引没有发生变化，则就无需更新。

　　另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做。如果锁表了，那么同时做很多次更新比分别做更新来得快多了。

　　注意，如果是在MyISAM表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分。如果经常做这个，那么偶尔做一次OPTIMIZETABLE就显得非常重要了。详情请看"14.5.2.5OPTIMIZETABLESyntax"。

　　2.13加速DELETE

　　删除单个记录的时间和它的索引个数几乎成正比。想更快地删除记录，可以增加索引键的缓存。详情请看"7.5.2TuningServerParameters"。

　　如果想要删除数据表的所有记录，请使用TRUNCATETABLEtbl_name而不是DELETEFROMtbl_name。

　　2.14其他优化点子

　　本章节列出了一些改善查询处理速度的其他点子：

　　使用永久连接到数据库，避免连接的开销。如果需要初始化很多连接，而又不能用永久连接，那么可以修改变量thread_cache_size的值，详情请看"7.5.2TuningServerParameters"。

　　总是检查查询是否利用了表中已有的索引。在MySQL中，可以用EXPLAIN语句来分析。

　　尽量不要在经常需要更新的MyISAM表上用太过复杂的SELECT语句，这是为了避免在读和写之间争夺锁。

　　在MyISAM表中，如果没有正在删除记录，则可以在其他查询正在读取数据的同时插入记录。如果这种情况十分重要，那么就要尽量在表没有删除记录时才使用表。另一个可能的办法就是在删除一大堆记录之后执行OPTIMIZETABLE语句。

　　如果总是需要按照expr1,expr2,...的顺序取得记录，那么请使用ALTERTABLE...ORDERBYexpr1,expr2,...修改表。通过这种方法扩充修改表之后，就可能获得更高的性能表现。

　　在一些情况下，让一个字段类型是``hashed``，它基于其他字段信息。如果这个字段比较短而且基本上都是唯一值的话，那么就可能会比在几个字段上使用一个大索引来得更快，很简单的就能使用这样的额外字段，如下：

　　SELECT*FROMtbl_nameWHEREhash_col=MD5(CONCAT(col1,col2))

　　ANDcol1='constant'ANDcol2='constant';

　　如果MyISAM表经常大量修改，那么要尽量避免修改所有的变长字段(VARCHAR,BLOB，TEXT)。尽管表中只有一个变长字段，它也会采用动态记录格式的。详情请看"15MySQLStorageEnginesandTableTypes"。

　　通常情况下，当数据表记录变``大``之后，将表拆分成几个不同的表并没有多大用处。访问一条记录是最大的性能点在于磁盘搜索时找到记录的第一个字节上。只要找到记录的位置后，现在的大部分磁盘对于大部分的应用程序来说都能很快的读取到记录。将MyISAM表拆分成多个唯一有关系的情况是，数据表中动态格式的字段(见上)就可以被修改成固定大小的记录，或者需要频繁的扫描表，但是却不需要读取出大部分的字段。详情请看"15MySQLStorageEnginesandTableTypes"。

　　如果需要频繁的对一个表做基于很多字段信息的统计信息的话，那么可能新建一个表来存储这些实时更新的统计结果会更好。类似下面的更新就会非常快了：

　　UPDATEtbl_nameSETcount_col=count_col+1WHEREkey_col=constant;

　　如果只需要表级锁(多个读/一个写)，那么采用MyISAM存储引擎就非常重要了，例如MyISAM和ISAM表。这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用。

　　如果需要从很大的日志表中搜集统计信息的话，可以用摘要表来代替扫描整个日志表。维护摘要表比保持``实时``的统计信息来得更快。当事情发生变化时(比如商业决策)，重新建里摘要表比修改运营中的应用程序快多了。

　　如果可能，最好是分类报告``实时``还是``统计``的，报告所需要的数据只需要来自摘要表，摘要表的信息则是周期的从实时数据中产生。

　　应该认识到一个优点就是字段有默认值。当要插入的值和默认值不一致时才需要明确指定。这就省去了MySQL需要来提高插入速度这步了。

　　在一些情况下，将数据组装存储在BLOB类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开BLOB字段中的值，不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从记录-和-字段格式的表结构是很实用。

　　通常地，应该保存所有的冗余数据(在数据库原理中叫做"第三范式")。然而，为了能取得更高的效率复制一些信息或者创建摘要表也是划算的。

　　存储过程或者UDFs(用户定义函数)的方式在执行一些任务时可能性能更高。尽管如此，当数据库不支持这些特性时，还是有其他的替代方法可以达到目的，即使它们有点慢。

　　可以从查询缓存或应答中取得结果，然后将很多次的插入及更新操作放在一起做。如果数据库支持表锁(如MySQL和ORACLE)，那么这就可以确保索引缓存在所有的更新操作之后只需要刷新一次。

　　当不需要直到数据什么时候写入表中时，可以用INSERTDELAYED。这就会提高速度，因为多条记录同时在一起做一次磁盘写入操作。

　　当想让SELECT语句的优先级比插入操作还高时，用INSERTLOW_PRIORITY。

　　用SELECTHIGH_PRIORITY来使检索记录跳过队列，也就是说即使有其他客户端正要写入数据，也会先让SELECT执行完。

　　在一条INSERT语句中采用多重记录插入格式(很多数据库都支持)。

　　用LOADDATAINFILE来导入大量数据，这比INSERT快。

　　用AUTO_INCREMENT字段来生成唯一值。

　　定期执行OPTIMIZETABLE防止使用动态记录格式的MyISAM表产生碎片。

　　采用HEAP表，它可能会提高速度。详情请看"15.1.3MyISAMTableStorageFormats"。

　　正常的WEB服务器配置中，图片文件最好以文件方式存储，只在数据库中保存文件的索引信息。这么做的原因是，通常情况下WEB服务器对于文件的缓存总是做的比数据库来得好，因此使用文件存储会让系统更容易变得更快。

　　对于频繁访问的不是很重要的数据，可以保存在内存表中，例如对那些web客户端不能保存cookies时用于保存最后一次显示的标题等信息。

　　在不同表中值相同的字段应该将它们声明为一样的类型。在MySQL3.23之前，不这么做的话在表连接时就会比较慢。让字段名尽可能简单，例如，在一个叫做customer的表中，用name来代替customer_name作为字段名。为了让字段名在其他数据库系统中也能移植，应该保持在18个字符长度以内。

　　如果需要真正的高速，建议看看各种数据库服务器支持的底层数据存储接口之间的区别。例如，通过直接访问MySQL的MyISAM存储引擎，会比通过其他的SQL接口快2-5倍。这要求数据必须和应用程序在同一个服务器上，并且它通常只被一个进程访问(因为外部文件锁确实慢)。只用一个进程就可以消除在MySQL服务器上引入底层的MyISAM指令引发的问题了(这容易获得更高性能，如果需要的话)。由于数据库接口设计的比较细心，就很容易支持这种优化方式了。

　　如果使用数字型数据的话，在很多情况下想要访问数据库(使用在线连接)的信息会比采用文本文件来得快。由于数字型信息相比文本文件在数据库中存储的更加紧凑，因此访问时只需要更少的磁盘搜索。而且在应用程序中也可以节省代码，因为无需解析文本文件以找到对应的行和字段。

　　数据库复制对一些操作会有性能上的益处。可以将客户端从多个复制服务器上取得数据，这就能将负载分摊了。为了避免备份数据时会让主服务器变慢，还可以将备份放在从服务器上。详情请看"6ReplicationinMySQL"。

　　定义MyISAM表时增加选项DELAY_KEY_WRITE=1，这样的话就会另索引更新更快，因为只有等到数据表关闭了才会刷新磁盘。不过缺点是可能会在数据表还打开时服务器被杀死，可以使用参数--myisam-recover来保证数据的安全，或者在数据库重启前运行myisamchk命令(尽管如此，在这种情况下，使用DELAY_KEY_WRITE的话也不会丢失任何东西，因为索引总是可以从数据中重新生成)。

　　3.1锁机制

　　当前MySQL已经支持ISAM,MyISAM,MEMORY(HEAP)类型表的表级锁了，BDB表支持页级锁，InnoDB表支持行级锁。很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。

　　想要决定是否需要采用一个支持行级锁的存储引擎，就要看看应用程序都要做什么，其中的查询、更新语句是怎么用的。例如，很多的web应用程序大量的做查询，很少删除，主要是基于索引的更新，只往特定的表中插入记录。采用基本的MySQLMyISAM表就很合适了。

　　MySQL中对表级锁的存储引擎来说是释放死锁的。避免死锁可以这样做到：在任何查询之前先请求锁，并且按照请求的顺序锁表。

　　MySQL中用于WRITE(写)的表锁的实现机制如下：

　　如果表没有加锁，那么就加一个写锁。

　　否则的话，将请求放到写锁队列中。

　　MySQL中用于READ(读)的表锁的实现机制如下：

　　如果表没有加写锁，那么就加一个读锁。

　　否则的话，将请求放到读锁队列中。

　　当锁释放后，写锁队列中的线程可以用这个锁资源，然后才轮到读锁队列中的线程。

　　这就是说，如果表里有很多更新操作的话，那么SELECT必须等到所有的更新都完成了之后才能开始。

　　从MySQL3.23.33开始，可以通过状态变量Table_locks_waited和Table_locks_immediate来分析系统中的锁表争夺情况：

　　mysql>SHOWSTATUSLIKE'Table%';

　　+-----------------------+---------+

　　|Variable_name|Value|

　　+-----------------------+---------+

　　|Table_locks_immediate|1151552|

　　|Table_locks_waited|15324|

　　+-----------------------+---------+

　　在MySQL3.23.7(在Windows上是3.23.25)以后，在MyISAM表中只要没有冲突的INSERT操作，就可以无需使用锁表自由地并行执行INSERT和SELECT语句。也就是说，可以在其它客户端正在读取MyISAM表记录的同时时插入新记录。如果数据文件的中间没有空余的磁盘块的话，就不会发生冲突了，因为这种情况下所有的新记录都会写在数据文件的末尾(当在表的中间做删除或者更新操作时，就可能导致空洞)。当空洞被新数据填充后，并行插入特性就会自动重新被启用了。

　　如果想要在一个表上做大量的INSERT和SELECT操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现：

　　mysql>LOCKTABLESreal_tableWRITE,insert_tableWRITE;

　　mysql>INSERTINTOreal_tableSELECT*FROMinsert_table;

　　mysql>TRUNCATETABLEinsert_table;

　　mysql>UNLOCKTABLES;

　　InnoDB使用行级锁，BDB使用页级锁。对于InnoDB和BDB存储引擎来说，是可能产生死锁的。这是因为InnoDB会自动捕获行锁，BDB会在执行SQL语句时捕获页锁的，而不是在事务的开始就这么做。

　　行级锁的优点有：

　　在很多线程请求不同记录时减少冲突锁。

　　事务回滚时减少改变数据。

　　使长时间对单独的一行记录加锁成为可能。

　　行级锁的缺点有：

　　比页级锁和表级锁消耗更多的内存。

　　当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。

　　当需要频繁对大部分数据做GROUPBY操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。

　　使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。

　　表级锁在下列几种情况下比页级锁和行级锁更优越：

　　很多操作都是读表。

　　在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：

　　UPDATEtbl_nameSETcolumn=valueWHEREunique_key_col=key_value;

　　DELETEFROMtbl_nameWHEREunique_key_col=key_value;

　　SELECT和INSERT语句并发的执行，但是只有很少的UPDATE和DELETE语句。

　　很多的扫描表和对全表的GROUPBY操作，但是没有任何写表。

　　当前MySQL已经支持ISAM,MyISAM,MEMORY(HEAP)类型表的表级锁了，BDB表支持页级锁，InnoDB表支持行级锁。很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。

　　表级锁和行级锁或页级锁之间的不同之处还在于：

　　将同时有一个写和多个读的地方做版本(例如在MySQL中的并发插入)。也就是说，数据库/表支持根据开始访问数据时间点的不同支持各种不同的试图。其它名有：时间行程，写复制，或者是按需复制。

　　按需复制在很多情况下比页级锁或行级锁好多了。尽管如此，最坏情况时还是比其它正常锁使用了更多的内存。

　　可以用应用程序级锁来代替行级锁，例如MySQL中的GET_LOCK()和RELEASE_LOCK()。但它们是劝告锁(原文：Theseareadvisorylocks)，因此只能用于安全可信的应用程序中。

　　3、2锁表

　　为了能有快速的锁，MySQL除了InnoDB和BDB这两种存储引擎外，所有的都是用表级锁(而非页、行、列级锁)。

　　对于InnoDB和BDB表，MySQL只有在指定用LOCKTABLES锁表时才使用表级锁。在这两种表中，建议最好不要使用LOCKTABLES，因为InnoDB自动采用行级锁，BDB用页级锁来保证事务的隔离。

　　如果数据表很大，那么在大多数应用中表级锁会比行级锁好多了，不过这有一些陷阱。

　　表级锁让很多线程可以同时从数据表中读取数据，但是如果另一个线程想要写数据的话，就必须要先取得排他访问。正在更新数据时，必须要等到更新完成了，其他线程才能访问这个表。

　　更新操作通常认为比读取更重要，因此它的优先级更高。不过最好要先确认，数据表是否有很高的SELECT操作，而更新操作并非很‘急需’。

　　表锁锁在一个线程在等待，因为磁盘空间满了，但是却需要有空余的磁盘空间，这个线程才能继续处理时就有问题了。这种情况下，所有要访问这个出问题的表的线程都会被置为等待状态，直到有剩余磁盘空间了。

　　表锁在以下设想情况中就不利了：

　　一个客户端提交了一个需要长时间运行的SELECT操作。

　　其他客户端对同一个表提交了UPDATE操作，这个客户端就要等到SELECT完成了才能开始执行。

　　其他客户端也对同一个表提交了SELECT请求。由于UPDATE的优先级高于SELECT，所以SELECT就会先等到UPDATE完成了之后才开始执行，它也在等待第一个SELECT操作。

　　下列所述可以减少表锁带来的资源争夺：

　　让SELECT速度尽量快，这可能需要创建一些摘要表。

　　启动mysqld时使用参数--low-priority-updates。这就会让更新操作的优先级低于SELECT。这种情况下，在上面的假设中，第二个SELECT就会在INSERT之前执行了，而且也无需等待第一个SELECT了。

　　可以执行SETLOW_PRIORITY_UPDATES=1命令，指定所有的更新操作都放到一个指定的链接中去完成。

　　用LOW_PRIORITY属性来降低INSERT，UPDATE，DELETE的优先级。

　　用HIGH_PRIORITY来提高SELECT语句的优先级。

　　从MySQL3.23.7开始，可以在启动mysqld时指定系统变量max_write_lock_count为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有SELECT操作的优先级。它允许在WRITE锁达到一定数量后有READ锁。

　　当INSERT和SELECT一起使用出现问题时，可以转而采用MyISAM表，它支持并发的SELECT和INSERT操作。

　　当在同一个表上同时有插入和删除操作时，INSERTDELAYED可能会很有用。

　　当SELECT和DELETE一起使用出现问题时，DELETE的LIMIT参数可能会很有用。

　　执行SELECT时使用SQL_BUFFER_RESULT有助于减短锁表的持续时间.

　　可以修改源代码`mysys/thr_lock.c'，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。

　　以下是MySQL锁的一些建议：

　　只要对同一个表没有大量的更新和查询操作混在一起，目前的用户并不是问题。

　　执行LOCKTABLES来提高速度(很多更新操作放在一个锁之中比没有锁的很多更新快多了)。将数据拆分开到多个表中可能也有帮助。

　　当MySQL碰到由于锁表引起的速度问题时，将表类型转换成InnoDB或BDB可能有助于提高性能。