数据库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来说，它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)。事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。
#p#分页标题#e#
为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于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
#p#分页标题#e#
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从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理。
#p#分页标题#e#
在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
#p#分页标题#e#
只有在给定范围的记录才会被取出来，利用索引来取得一条记录。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
#p#分页标题#e#
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
#p#分页标题#e#
请注意，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;
#p#分页标题#e#
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一起使用时就会创建一个临时表。
#p#分页标题#e#
注意，由于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;
#p#分页标题#e#
有很多表一起做连接，而且读取的记录中在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条唯一记录后就不再搜索了。
#p#分页标题#e#
在某些情况下，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;
#p#分页标题#e#
这对性能提高的好处在于：直到所有的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;
#p#分页标题#e#
如果只需要表级锁(多个读/一个写)，那么采用MyISAM存储引擎就非常重要了，例如MyISAM和ISAM表。这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用。
如果需要从很大的日志表中搜集统计信息的话，可以用摘要表来代替扫描整个日志表。维护摘要表比保持“实时“的统计信息来得更快。当事情发生变化时(比如商业决策)，重新建里摘要表比修改运营中的应用程序快多了。
如果可能，最好是分类报告“实时“还是“统计“的，报告所需要的数据只需要来自摘要表，摘要表的信息则是周期的从实时数据中产生。
应该认识到一个优点就是字段有默认值。当要插入的值和默认值不一致时才需要明确指定。这就省去了MySQL需要来提高插入速度这步了。
在一些情况下，将数据组装存储在BLOB类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开BLOB字段中的值，不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从记录-和-字段格式的表结构是很实用。
通常地，应该保存所有的冗余数据(在数据库原理中叫做”第三范式”)。然而，为了能取得更高的效率复制一些信息或者创建摘要表也是划算的。
存储过程或者UDFs(用户定义函数)的方式在执行一些任务时可能性能更高。尽管如此，当数据库不支持这些特性时，还是有其他的替代方法可以达到目的，即使它们有点慢。
可以从查询缓存或应答中取得结果，然后将很多次的插入及更新操作放在一起做。如果
关键字：
