MySQL数据库优化资料

MySQL数据库优化（一）

作者：叶金荣，出处:IT专家网，责任箱辑：李书琴，

2008-06-0609:30

数据库优化是一项很更杂的工作，因为这最终需耍对系统优化的很好理解才行。尽管对系统或应用系统的了解不多

的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

国

数据库优化是•项很攵杂的工作，因为这最终需要对系统优化的很好理的才行。尽管对系统或应用系统的了解不多

的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。

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

需要更多的努力。

1优化概述

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

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

进盘搜索.它慢慢地在磁盘中搜索数据块.对现代磁盘来设，平时的搜索时间基本上小于10富秒，因此理论上每秒

钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜

索时间的方法是将数据分开存放到多个磁盘中。

磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐珏至少是10-20MB/杪。这比

磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。

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

嗣啾饶佳媛禁坷此蹈窍拗频囊蛋亍•2还孕"硅此担俣韧U2皇俏侍狡？

内存带宽.当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常

见的瓶颈，不过也是要注意的一个因素。

1.1MySQL设计的局限性

当使川MylSAM存储引擎时,MySQL会使川一个快速数据表锁以允许同时多个读取和一个写入。这种存储弓擎的

最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。如果这种情况在某个表中存在，可以使用另一种

表类型。详情请看”15MySQLStorageEnginesandTableTypes",

MySQL可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表（发生错误时不能回滚），有以

下几条规则：

所有的字段都有汰认但

如果字段中插入r一个“错误"的值，比如在数字类型字段中插入过大数值，那么MySQL会将该字段伯置为"最可能的他■•而不是给出

一个错误。数字类型的殖是0,最小或者最大的可能烧。字符串类型，不是空字符串就是字段所能存储的最大长度。

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

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

详情请看18.6HowMySQLDealswithConstraints和"1414INSERTSyntax".

1.2应用设计的可移植性

由于各种不同的数据库实现了各自的SQL标准，这就需要我们尽量使用可移植的SQL应用。查询和插入操作很容

易就能做到可移植，不过由r•更多的约束条件的要求就越发困难.想要让一个应用在各种数据库系统上快速运行，就变

得更困难了。

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

持哪些特性。

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

可以川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服务器的瓶颈都有一些很好的想法。例如，MySQLXf丁

MylSAM类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。作为Oracle

来说，它在访问刚刚被更新的记录时有很大的问题（直到结果被刷新到磁盘中）.事务数据库•般地在从口志表中生成摘

要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。

为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于C++在很多系统

上都可以使川，因此使用C++作为数据库的基类结果很合适。

如果使用了某些数据库独有的特定功能（比如REPLACE语句就只在MySQL中独有），这就需要通过编写替代方法

来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。

在MySQL中，可以在查询语句中使用/*!*/语法来增加MySQL特有的关键字。然而在很多其他数据库中，广/却

被当成了注释（并且被忽略）。

如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果,

这可能会有更高的性能“通过让旧数据在一定时间后过期，来合理的更新缓存。这是处理负载高峰期时的一种方法，这

种情况下，可以通过加大缓存容量和过期时间直到负我趋于正常。

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

一个实现应用层缓存的可选方案是使用MySQL的查询缓存（querycache）。启用查询缓存后，数据库就会根据一些

详情来决定哪些结果可以被重用。它大大简化了应用程序，详情请看”5.11TheMySQLQueryCache”。

1.3我们都用MySQL来做什么

本章描述了一个MySQL的早期应用。

在MySQL最开始的开发过程中，MySQL本来是要准备给大客户用的，他们是瑞典的2个最大的零售商，他们用于

货物存储数据管理。

我们每周从所有的商店中得到交易利涧累计结果，以此给商店的老板梃供有用的信息.，加助他们分析如果更好的打

广告以影响他们的客户。

数据量相当的大（每个月的交易累计结果大概有7百万），而且还需要显示4-10年间的数据。我们每周都得到客户的

需求，他们要求能‘瞬间’地得至!数据的最新报表.

我们把每个月的全部信息存储在一个压缩的‘交易’表中以解决这个问题。我们有一些简单的宏指令集，它们能根

据不同的标准从存储的‘交易'表中根据字段分组（产品组、客户id、商店等等）取得结果。我们用一个小Perl脚本动态

的生成Web页面形式的报表。这个脚本解析Web页面，执行SQL语句，并且插入结果。我们还可以用PHP或者

mod_perl来做这个工作，不过当时还没有这2个工具。

为了得到图形数据，我们还写了一个简单的C语言工具，用于执行SQL查询并且将结果做成GIF图片。这个工具

同样是Perl脚木解析Web页面后动态执行的。

很多情况下，只要拷贝现有他脚本简单的修改里面的SQL查询语句就能产生新的报表了.有时候，就需要在现存的

累计表中增加更多的字段或者新建一个。这个操作I•分简单，因为我们在磁盘上存储有所有的交易表（总共大概有50G

的交易表以及20G的其他客户资料）。

我们还允许客户通过ODBC直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了°

这个系统工作的很好，并且在适度的SunUltraSPARC工作站（2x200MHz）上处理数据没有任何问题。最终这个系

统移植到/Linux上。

1.4MySQL基准套件

本章本来要包括MySQL基准套件（以及crash-me）的技术描述的，但是至今还木写。现在，您可以通过直看MySQL

发布源代码'sql-bench'目录下的代码以及结果有一个更好的想法。

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

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

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

基准套件在MySQL的发布源代码中就有。可以去下载发布版或者使用现有开发代码树（详情请看23.3Installing

fromtheDevelopmentSourceTree"）.

基准脚本是用Perl写的，它用Perl的DBI模块来连接数据库，因此必须安装DBI模块.并且还需要每个要做测试

的服务器上都有特定的BDB.驱动程序。例如，为了测试MySQL、PostgeSQL和DB2,就必须安装DBD::mys。，

DBD::Pg及DBD::DB2模块。。情请看”2.7PerlInstallationNote%

取得MySQL的分发源代码后，就能在'sql-bench'目录下看到基准套件。想要运行这些基准测试，请先搭建好股

务，然后进入'sql-bench'目录,执行run-all-tests脚本：

shell>cdsql-bench

shell>perirun-all-tests-server=server_name

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

shell>perlrun-all-tests-help

crash-me脚本也是放在'sql-bench,目录下。crash-me通过执行真正的查询以试图判断数据阵都支持

什么特性、性能表现以及限制。例如，它可以判断：

都支持什么字段类型

支持多少索引

支持什么样的函数

能支持多大的查询

VARCHAR字段类型非支持多大

可以从上找到各种不同数据库crash-me的结果。更多的信息请访问。

1.5使用您自己的基准

请确定对您的数据库或者应用程序做基准测试，以发现它们的腰颈协任。解决这个肮颈(或者使用一个假的模决来代

替)之后，就能很容易地找到下一个瓶颈了.即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个粒颈

的计划，说不定某天您就希望应用程序能有更好的性能。

从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看”7.1.4TheMySQLBenchmark

Suite"。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办

法来解决问题，知道哪个程序才是最快的。

另一个基准套件是开放源码制数据库基准，可以在.net上找到。

力系统负载十分繁里的时候，通常就会发生问题。我们就有很多客户我系我们说他们有一个(测试过的)生产系统也

遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计(例如，在高负载下扫描数据表的表现不好)、噪作系

统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。

为了避免发生这样的问题，垠好让您的应用程序在可能的最差的负载卜做基准测试何以使用SuperSmack,

可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系统死抻，因此确认只在开发系统上做测试。

2优化SELECT语句及其他查询

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

使用比较简小的GRANT语句能让MySQL减少在客户端执行语句时权限检查的开销。例如，如果没有设定任何表

级或者字段级的权限，那么服务器就无需检查tables_priv和columns_priv表的记录了.同样地，如果没有对帐户设定

任何资源限制的话，那么股务器也就无需做资源使用统计了。如果有大量直询的话，花点时间来规划简单的授权机制以

减少服务器权限检查的开销是值得的.

如果问题处在一些MySQL特定的表达式或者函数上，则可以通过mysql客户端程序使用BENCHMARK()

函数做一个定时测试。它的语法是：BENCHMARK(loop_count,expression)o例如：

mysql>SELECTBENCHMARK(1OOOOOO,1+1);

++

IBENCHMARK(1000000,1+1)|

+-...............-...................+

I0|

+-...............-...................+

1rowinset（0.32sec）

上述结果是在PentiumII400MHz的系统上执行得到的。它告诉我们：MySQL在这个系统上可以在0.32秒内执行

1,000,000次简单的加法运算。

所有的MySQL函数都应该被最优化，不过仍然有些函数例外，BENCHMARKO是一个用「检查查询语句中是否存

在问题的非常好的工具。

MySQL数据库优化（二）

作者：叶金荣，出处:IT专家网，责任期辑：李书琴，

2008-06-11（19:55

EXPLAIN语句可以被当作DESCR旧E的同义词来用，也可以川来茯取一个MySQL要执行的SELECT语句的

相关信息八FXPIAINthLnamP语法和RFSCRIRFthLnamP或SHOV/COIIJMNSFROMthLnamP一样-

MySQL数据库优化（一）

1.EXPLAI.语法（得至IJSELEC.的相关信息）

EXPLAINtblname

或者：

EXPLAINSELECTselectoptions

tablelN语句可以被当作DESCR旧E的同义词来用，也可以用来获取一个MySQL要执行的SELECT语句的相

关信息。EXPLAINtbLname语法和DESCR旧Elbl_name或SHOWCOLUMNSFROMtbl.name一样。当在个

SELECT语句前使用关键字EXPLAIN时，MYSQL会解释了即将如何运行该SELECT语句，它显示了表如何连接、

连接的顺序等信息。本章节主要讲述了第二种EXPLAIN用法。

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

如果由于不恰当使用索引而引起一些问题的话，可以运行ANALYZETABLE来更新该表的统计信息，例如键的基

数，它能帮您在优化方面做出更好的选择。

您还可以杳看优化程序是否以最佳的顺序来连接数据表。为J'让优化程序按照SELECT语句中的表名的顺序做连

接，可以在查询的开始使用SELECTSTRAIGHT_JOIN而不只是SELECT.

EXPLAIN返回/一行记录，它包括「SELECT语句中川到的各个袤的信息。这些表在结果中按照MySQL即将执

行的查询中读取的眼序列出来。MySQL用一次扫描多次连接（single-sweep,multi-join）的方法来解决连接。这意味着

MySQL从第一个表中读取一条记录，然比在第二个表中查找到时应的记录，然后在第三个表中查找，依次类推。当所有

的表都扫描完了，它输出选择的字段并且回溯所有的表，宜到找不到为止，因为有的表中可能有多条匹配的记录下一条

记录将从该表读取，再从卜.一个表开始继续处理。

在MySQLversion4.1中，EXPLAIN输出的结果格式改变了，使得它更适合例如UNION语句、子查询以及派生表

的结构。更令人注意的是，它新增了2个字段：id和selectjype,当你使用早于MySQL4.1的版本就看不到这些字

段了。

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

id

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

select_type

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

SIMPLE

简单的SELECT（没有使用JNION或子查询）

PRIMARY

最外层的SELECT0

UNION

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

DEPENDENTUNION

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

SUBQUERY

子查闻中的第一个SELECT

DEPENDENTSUBQUERY

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

DERIVED

派生表SELECT（FROM子句中的子查询）

table

记录查询引用的表.

type

衣性按类型。以下列出「各种不同类型的表连按，依次是从最好的到最爰的:

system

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

const

表中最多只有一行匹配的记录，它在查i旬一开始的时候就会被读取出来。由于只有一行记录，在余卜的优亿程

序里该行记录的字段值可以被当作是一个恒定值。const表杳询起来非常快，因为只要读取一次!const用于在和

PRIMARYKEY或UNIQUE索引中有固定值比较的情形。下面的几个查询中,tbl_name就是const表了：

SELECT*FROMtblnameWHEREprimarykey=1;

SELECT*FROMtbl_name

WHEREprimary_key_part1=1ANDprimary_key_part2=2;

eq_ref

从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与const类型不同的是，这是

最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个PRIMARYKEY或UNIQUE类型。

eq_ref可以用于在进行三"做比较时检索字段。比较的值可以是固定值或者是表达式，表达式中可以使用表里的字

段，它们在读表之前已经准备好了.以下的几个例子中，MySQL使用了eq_ref连接来处理refjable:

SELECT,FROMrefjable.otierjable

WHEREref_table.key_column=other_table.column;

SELECT*FROMrefjable.olierjable

WHEREret_table.key_column_part1=other_table.column

ANDref_table.key_column_part2=1：

该表中所布■符合检索值的记录都会被取出来和从上•个表中取出来的记录作联合。ref用于连接程序使用犍的

最左前缀或者是该键不是PRIMARYKEY或UNIQUE索引（换句话说，就是连接程序无法根据键值只取得一条

记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是•个不错的连接类型。ref还可以用于检索字

段使川=操作符来比较的时候。以下的几个例子中，MySQL将使用ref来处理refjable:

SELECT*FROMrefjableWHEREkey_column=expr;

SELECT*FROMreftable.otiertable

WHEREref_table.key_column-other_table.column;

SELECT,FROMreftable.otierjable

WHERErefjable.key_column_part1=other_table.column

ANDref_table.key_column_part2=1;

refornull

这种连接类型类似ref,不同的是MySQL会在检索的时候额外的搜索包含NULL值的记录。这种连接类型

的优化是从MySQL4.1.1开始的，它经常用于子查询。在以下的例子中，MySQL使用ref_or_null类型来处理

ref_table:

SELECT*FROMrefjable

WHEREkey_column=exprORkey_columnISNULL：

in<Jex_merge

这种连接类型意味着使用了IndexMerge优化方法。这种情况下,key字段包括了所有使用的索引，

keyjen包括了使用的键的最长部分。详情请看“725HowMySQLOptimizesORClauses",

uniquesubquery

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

valueIN(SELECTprimary_keyFROMsingle_tableWHEREsome_expr)

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

index_subquery

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

以下形式：

valueIN(SELECTkey_columnFROMsinglejableWHEREsome_expr)

range

只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key字段表示使用了哪个索引。keyjen字

段包括了使用的键的最长部分,这种类型时ref字段值是NULL.,rarge用于将某个字段和一个定植用以卜任何

操作符比较时=,<>,>,>=,<,<=,ISNULL,<=>,BETWEEN,或IN:

SELECT*FROM

WHEREkey_column=10；

SELECT*FROMtbl_name

WHEREkey_columnBETWEEN10and20；

SELECT*FROMtbl_nam@

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里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是NU-L,就

表示没有索引被用到。这种情况卜；就可以检查WHERE了句中哪些字段那些字段适合增加索引以提高查询的性能.

就这样，创建一下索引，然后再用EXPLAIN检查一下。详细的查看章节,14.2.2ALTERTABLESyntax"。想看表都有

什么索引，可以通过SHOWINDEXFROMtbl_name来看。

key

key字段显示了MySQL实际上要用的索引。而没有任何索引被用到的时候，这个字段的值就是NULL.想要让

MySQL强行使用或者忽略在possible_keys字段中的索引列表，可以在咨询语句中使用关键字FORCEINDEX,USE

INDEX,或IGNOREINDEX,如果是MylSAM和BDB类型表，可以使用ANALYZETABLE来帮助分析使用费用哪

个索引更好.如果是MylSAM类型表，运行命令myisamchk-analyze也是•样的效果。详细的可以查看章节”1452.1

ANALYZETABLESyntax"和"5.7.2TabicMaintenanceandCrashRecovery"

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*FROM11LEFTJOINt2ONH.id=t2.id

WHEREt2.idISNULL;

假使t2.id定义为NOTNULLO这种情况下,MySQL将会扫描表t1并且川tl.id的值在t2中查找记录。当在t2

中找到一条匹配的记录时，这就意味着t2.id肯定不会都是NULL,就不会再在t2中查找相同id值的其他记录了。也

可以这么说，对于H中的每个记录,MySQL只需要在t2中做一次查找，而不管在t2中实际有多少匹配的记录。

rangecheckedforeachrecord(indexmap:#)

MySQL没找到合适的可用的索引。取代的办法是，时丁前一个表的每一个行连接，它会做一个检验以决定该更用哪

个索引(如果有的话)，并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得

快.

Using

MySQL需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合

WHERE条件的记录的要排序的键和指向记录的指针存储起来。这些键匕经排完序了，对应的记录也会按照排好的顺序

取出来。详情请看”7.2.9HowMySQLOptimizesORDERBY”。

Usingindex

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

一部分.

Usingtemporary

MySQL需要创建临时表存储结果以完成查询。这种情况通常发生在互询时包含了GROUPBY和ORDER3YT

句，它以不同的方式列出了各个字段。

Usingwhere

WHERE子句将用来限制哪些记录匹配了下一个表或者发送给客户端.除非你特别地想要取得或者检杳表种的所有

记录，否则的话当查询的Extra字段值不是Usingwhere并且表连接类型是ALL或index时可能表示有问题，

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

你可以通过EXPLAIN的结果中rows字段的值的乘枳大概地知道本次连接表现如何。它可以粗略地告诉我们

MySQL在查询过程中会置询多少条记录。如果是使用系统变量maxjoin_size来取得筐询结果，这个乘积还可以用来

确定会执行哪些多表SELECT语句。详情请看"7.5.2TuningServerParameters"。

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

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

EXPLAINSELECTtt.TicketNurrber,tt.Timeln,

tt.ProjectReference,tt.EstimatedShipDate,

tt.ActualShipDate,tt.ClientID,

tt.ServiceCodes,tt.RepetidvelD,

tt.CurrentProcess,tt.CurrentDPPerson,

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

et_l.COUNTRY,do.CUSTNAME

FROMtt,et,etASet_l,do

WHEREtt.SubmitTimeISNULL

ANDtt.ActualPC=etEMPLOYID

ANDtt.AssignedPC=et_l.EMPLOYID

ANDtt.ClientID=do.CUSTNMBR;

在这个例子中，先侬以下假设:

要比较的字段定义如F:

ColumnColumnType

Table

ttActualPCCHAR(10)

ttAssignedPCCHAR(10)

ttClientTDCHAR(10)

etEMPLOYIDCHAR(15)

doCUSTNWBRCHAR(15)

数据表的索引如下：

Index

Table

ttActualPC

ttAssignedPC

ttClientID

etEMPLOYID(primarykey)

doCUSTNMBR(primarykey)

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

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

tabletypepossible_keyskeykey_lenrefrowsExtra

etALLPRIMARYNULLNULLNULL74

doALLPRIMARYNULLNULLNULL2135

et1ALLPRIMARYNULLNULLNULL74

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都是VARCIIAR(15)

了。再来执行•次EXPLAIN语句看看结果：

tabletypepossible_keyskeykey_lenrefrowsExtra

ttALLAssignedPC,NULLNULLNULL3872Using

CllentID,where

ActualPC

doALLPRIMARYNULLNULLNULL2135

idiigeclieckedfuteacliiecuid(key111dp.1)

et_lALLPRIMARYNULLNULLNULL74

rangecheckedforeachrecord(keymap:1)

eteq_refPRIMARYPRIMARY15tt.ActualPC1

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

第二个改变是消除在比较tt.AssignedPC=et1.EMPLOYID和It.ClientlD=do.CUSTNMBR中字段的改

度不一致问题：

mysql>ALTERTABLEttMODIFYAssignedPCVARCHAR(15),

->MODIFYClientIDVARCHAR(15)；

现在EXPLAIN的结果如下：

tabletypepossible_keyskeykey_lenrefrowsExtra

etALLPRIMARYNULLNULLNULL74

ttrefAssignedPC,ActualPC15et.EMPLOYID52Using

ClientID,where

ActualPC

et_ieq_refPRIMARYPRIMARY15tt.AssignedPC1

doeq_refPRIMARYPRIMARY15tt.ClientID1

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

遗留下来的问题是，MySQL默认地认为字段tt.ActualPC的值是均匀分布的，然而表tt并非如此.幸好，我

们可以很方便的让MySQL分析索引的分布：

mysql>ANALYZETABLEtt；

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

tabletypepossible_keyskeykeylenrefrowsExtra

ttALLAssignedPCNUL_NULLNULL3872Using

ClientID,where

ActualPC

eteq_refPRIMARYPRIMARY15tt.ActualPC1

et_leq_refPRIMARYPRIMARY15tt.AssignedPC1

doeq_refPRIMARYPRIMARYlbtt.LhentlD1

请注意，EXPLAIN结果中的rows字段的值也是MySQL的连接优化程序大致猜测的，请检查这个值跟真实谊是否

基本一致。如果不是，可以通过在SELECT语句中使用STRAIGHT_JOIN来取得更好的性能，同时可以试着在

FROM

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

MySQL数据库优化(三)

作者：叶金荣，出处：n•专家网，责任簿我：李书琴，

2008-06-1110:05

这个章节讲述了优化程序如何处理WHERE子句。例子中使用了SELECT语句，但是在DELETE和UPDATE

语句中对WHERE了句的优化是•样的。注意，关于MySQL优化的工作还在维续，因此本章节还没结束。MySQL做

了很多优化工作，而不仅仅是文档中提到的这些。

1.估算查询性能

在大多数情况下，可以通过统计磁盘搜索次数来估算查询的性能.时小表来说，通常情况下只需要搜索一次磁盘就

能找到对应的记录(因为索引可能已经缓存起来J')。对大表来说，大致可以这么估算，它使用B树做索引，想要找到一条

记录大概需要搜索的次数为:log(row_count)/log(index_block_length/3,2/(indexjength+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服务器缓存起来，那么性能只会因为数据表越大而稍微下降。当数据越来越大之后，就不能全部放到缓存中去

了，就会越来越慢了，除非应用程序是被磁盘搜索约束的(它跟随着的log7值增加而增加)。为了避免这种情况，uj"以在

数据量增大以后也随着增大索引线存容量。对MylSAM类型表来说，索引缓存容量是由系统变量key_buffer_size控

制的。

2.SELEC.杳询的速度

通常情况下，想要让一个比较慢.SELEC....WHER.查询变得更快的第一件事就是，先检查看看是否可以烟加索引。

所有对不同表的访问都通常使用索用。可以使.EXPLAI.语句来判.SELEC.使用了哪些索引。洋情请看

"7.4..Ho.MySQ.Use.Indexes"-fli"7.2..EXPLAI.Synta.(Ge.Informatio.Abou.SELECT)”。

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

想要让MySQL将查询优化的速度更快些，可以在数据表已经加载完全部数据后执行行ANALYZETABLE或运行

myisamchk-analyze命令.它更新了每个索引部分的值，这个值意味着相同记录的平均值(对于唯一索引来说，这个值

则一直都是1)。MySQL就会在当你使用基于一个非恒量表达式的两表连接时，根据这个值来决定使用哪个索引。想要

查看结果，可以在分析完数据表后运行SHOWINDEXFROMtbl_name查看Cardinality字段的值。myisamch<

-description-verbose显示了索引的分布信息。

想要根据一个索引来排序数据，可以运行myisamchk-sort-index-sort-records=1(如果想要在索引1上做排

序)“这对于布••个唯•索引并且想根据这个索引的顺序依次读取记录的话来说是•个提高查询速度的好办法。不过要注

忌的是，第一次在一个大表上做排序的话将会耗菸很长时间。

3.MySQL如何优.WHER.了句

这个章节讲述了优化程序如何处理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

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

在单独一个表上做COUNTC)而不使用WHERE时，对于MylSAM和HEAP表就会直接从表信息中检索结

果。在单独一个表上做任何表NOTNULL达式置询时也是这样做。

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

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

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

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

空表或者只有一条记录。

与布•个UNIQUE索引、或•个PRIMARYKEY的WHERE子句•起使用的表，这里所TT的索引部分和常数表

达式做比较并且索引部分被定义为NOTNULLo

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

SELECT,FROMtWHEREprimary_key=1;

SELECT*FROM11,t2

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

MySQL会进各种可能找到表连接最好的连接方法。如果在ORDERBY和GROUPBY了句中的所有字段都来

自同一个表的话，那么在连接时这个表就会优先处理。

如果有ORDERB丫子句和一个不同的GROUPBY于句，或者如果ORDERBY或GROUPBY中的字段都来

白其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。

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

所行的表索引都会查询，最好的情况就是所TT的索引都会被用到，除非优化程序认为全表扫描的效率更高.同时，数

据表扫描是基于判断最好的索引:范围超过数据表的30%,现在，优化程序复杂多儿它基于对一些附加因素的估计，例

如表大小，记录总数，I/O块大小，因此就不能根据•个固定的百分比来决定是选择使用索引还是直接扫描数据表。

在某些情况下,MySQL可以直接从索引中取得记录而无需资询数据文件。如果所有在索引中使用的字段都是数字类

型的话，只而要用素引树就能完成r［向。

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

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

SELECTCOUNTC)FROMtb_name;

SELECTMIN(key_part1).MAX(key_part1)FROMtbl_name;

SELECTMAX(keypart2)FROMtblname

WHEREkey_part1=constant;

SELECT...FROMtbl_name

ORDERBYkey_part1,key_part2,...LIMIT10;

SELECT...FROMtbl_name

ORDERBYkey_pamDESC,key_part2DESC,...LIMIT10;

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

SELECTkey_part1,key_part2FROMtbl_nameWHEREkey_partl=val;

SELECTCOUNT(-)FROMtb_name

WHEREkey_partl=vallANDkey_part2=val2：

SELECTkey_part2FROMtbl_nameGROUPBYkey_part1;

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

SELECT...FROMtbLname

ORDERBYkey_part1,key_part2,...;

SELECT...FROMtblname

ORDERBYkey_part1DESC,key_part2DESC,...;

4.MySQ.如何优。子句

IndexMerge方法用,使用,ef,ref_or_null,或range扫描取得的记录合并起来放到一起作为结果。这种方法在表

条件是或条件ref,ref_or_null,或range,并且这些条件可以用不同的键时采用。

・join1•类型的优化是从MySQL5.0.0开始才有的，代表者在索引的性能上有若标志性的改进，因为使用老规则的话,

数据库最多只能对每个引用表使用一个索引。

在EXPLAIN的结果中，这种方法在type字段中表现为index_merge.这种情况卜，key字段包含了所有例用的

索引列表，并且key_len字段包含了使用的索引的最长索引部分列表。

例如：

SELECT*FROMtbl_nameWHEREkey_part1.10ORkey_parl2-20;

SELECT*FROMtbl_name

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

SELECT*FROMt1,t2

WHERE(H.keyiIN(1,2)0Rtl.key2LIKE'value%')

ANDt2.key1=t1.some_col;

SELECT*FROMt1,t2

WHEREI1.key1-1

AND(t2.key1=t1.some_colORt2.key2=t1.some_co!2);

5.MySQ.如何优.I.NULL

MySQL在col_nameISNULL时做和col_name=constant_value•样的优化"例如，MySQL使用索引或

者范围来根据ISNULL搜索NULL。

SELECT,FROMWHEREkey_colISNULL;

SELECT*FROMtblnameWHEREkey_col<=>NULL;

SELECT*FROMtbl_name

WHEREkeycol=const1ORkeycol=const2ORkeycolISNULL;

如果一个WHERE子句包括了一个col_nameISNULL条件，并且这个字段声明为NOTNULL,那么这个表达

式就会被优化。当字段可能无论如何都会产生NULL值时，就不会再做优化了例如，当它来自一个LEFTJOIN中右边

的一个表时。

MySQL4.1.1或更高会对连接col_name=exprANDcoLnameISNULL做额外的优化，常见的就是子查询。

EXPLAIN当优化起作用时会显示ref_or_nulL

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

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

SELECT*FROMt1WHEREt1.a=exprORt1.aISNULL;

SELECT,FROMI1,t2WHEFEt1.a-t2.aORt2.aISNULL;

SELECT*FROMt1,t2

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

SELECT*FROMt1,t2

WHEREt1.a=t2.aAND(t2.b=d.bORt2.bISNULL)；

SELECT*FROMH,tZ

WHERE(|1.a=t2.aANDt2.aISNULLAND...)

OR(t1.a-t2.aANDt2.aISNLLLAND

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

请注意，优化程序只会处理一个ISNULL级别。下面的行询中,MySQL只会使用健来查询表达式(t1.a=12.a

ANDt2.aISNULL)而无法使在b上使用索引部分:

SELECT*FROMt1,t2

WHERE(t1.a=t2.aANDt2.a