MySQL数据库优化.doc

学习资料收集于网络，仅供参考MySQL数据库优化（一）作者: 叶金荣, 出处:IT专家网,责任编辑: 李书琴, 2008-06-06 09:30数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。本章主要讲解了几种优化MySQL的方法，并且给出了例子。记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力。1 优化概述让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。最常见的系统瓶颈有以下几种：磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个磁盘中。磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。CPU周期。数据存储在主内存中(或者它已经在主内存中了)，这就需要处理这些数据以得到想要的结果。存在多个?硐啾饶诖嫒萘坷此蹈窍拗频囊蛩亍2还孕砝此担俣韧2皇俏侍狻?内存带宽。当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。1.1 MySQL 设计的局限性当使用MyISAM存储引擎时，MySQL会使用一个快速数据表锁以允许同时多个读取和一个写入。这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。如果这种情况在某个表中存在，可以使用另一种表类型。详情请看15 MySQL Storage Engines and Table Types。MySQL可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表(发生错误时不能回滚)，有以下几条规则： 所有的字段都有默认值 如果字段中插入了一个错误的值，比如在数字类型字段中插入过大数值，那么MySQL会将该字段值置为最可能的值而不是给出一个错误。数字类型的值是0，最小或者最大的可能值。字符串类型，不是空字符串就是字段所能存储的最大长度。 所有的计算表达式都会返回一个值而报告条件错误，例如 1/0 返回 NULL。这些规则隐含的意思是，不能使用MySQL来检查字段内容。相反地，必须在存储到数据库前在应用程序中来检查。详情请看1.8.6 How MySQL Deals with Constraints 和 14.1.4 INSERT 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-me 的结果，可以访问：/tech-resources/crash-me.php. 访问 /tech-resources/benchmarks 可以看到基准的结果。如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法。例如，MySQL对于 MyISAM 类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。作为Oracle来说，它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)。事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于C+在很多系统上都可以使用，因此使用C+作为数据库的基类结果很合适。如果使用了某些数据库独有的特定功能(比如 REPLACE 语句就只在MySQL中独有)，这就需要通过编写替代方法来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。在MySQL中，可以在查询语句中使用 /*! */ 语法来增加MySQL特有的关键字。然而在很多其他数据库中，/*/ 却被当成了注释(并且被忽略)。如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果，这可能会有更高的性能。通过让旧数据在一定时间后过期，来合理的更新缓存。这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常。这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。一个实现应用层缓存的可选方案是使用MySQL的查询缓存(query cache)。启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用。它大大简化了应用程序，详情请看5.11 The MySQL Query Cache。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查询语句就能产生新的报表了。有时候，就需要在现存的累计表中增加更多的字段或者新建一个。这个操作十分简单，因为我们在磁盘上存储有所有的交易表(总共大概有50G的交易表以及20G的其他客户资料)。我们还允许客户通过ODBC直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了。这个系统工作的很好，并且在适度的Sun Ultra SPARC工作站(2x200MHz)上处理数据没有任何问题。最终这个系统移植到了Linux上。1.4 MySQL 基准套件本章本来要包括MySQL基准套件(以及 crash-me)的技术描述的，但是至今还未写。现在，您可以通过查看MySQL发布源代码 sql-bench 目录下的代码以及结果有一个更好的想法。基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差。请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。想要使用基准套件，必备以下几个条件：基准套件在MySQL的发布源代码中就有。可以去 /downloads/ 下载发布版或者使用现有开发代码树(详情请看2.3.3 Installing from the Development Source Tree)。基准脚本是用Perl写的，它用Perl的DBI模块来连接数据库，因此必须安装DBI模块。并且还需要每个要做测试的服务器上都有特定的BDB驱动程序。例如，为了测试MySQL、PostgreSQL和DB2，就必须安装 DBD:mysql, DBD:Pg 及 DBD:DB2 模块。详情请看2.7 Perl Installation Note。取得MySQL的分发源代码后，就能在 sql-bench 目录下看到基准套件。想要运行这些基准测试，请先搭建好服务，然后进入 sql-bench 目录，执行 run-all-tests 脚本：shell cd sql-benchshell perl run-all-tests -server=server_nameserver_name 可以是任何一个可用的服务。想要列出所有的可用选项和支持的服务，只要调用以下命令：shell perl run-all-tests -helpcrash-me 脚本也是放在 sql-bench 目录下。crash-me 通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制。例如，它可以判断： 都支持什么字段类型 支持多少索引 支持什么样的函数 能支持多大的查询 VARCHAR 字段类型能支持多大可以从 /tech-resources/crash-me.php 上找到各种不同数据库 crash-me 的结果。更多的信息请访问 /tech-resources/benchmarks。1.5 使用您自己的基准请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈(或者使用一个假的模块来代替)之后，就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看7.1.4 The MySQL Benchmark Suite。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。另一个基准套件是开放源码的数据库基准，可以在 上找到。当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个(测试过的)生产系统也遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计(例如，在高负载下扫描数据表的表现不好)、操作系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试!可以使用Super Smack，在 /mysql/super-smack 可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试。2 优化 SELECT 语句及其他查询首先，影响所有语句的一个因素是：您的权限设置越复杂，那么开销就越大。使用比较简单的 GRANT 语句能让MySQL减少在客户端执行语句时权限检查的开销。例如，如果没有设定任何表级或者字段级的权限，那么服务器就无需检查 tables_priv 和 columns_priv 表的记录了。同样地，如果没有对帐户设定任何资源限制的话，那么服务器也就无需做资源使用统计了。如果有大量查询的话，花点时间来规划简单的授权机制以减少服务器权限检查的开销是值得的。如果问题处在一些MySQL特定的表达式或者函数上，则可以通过 mysql 客户端程序使用 BENCHMARK() 函数做一个定时测试。它的语法是：BENCHMARK(loop_count,expression)。例如：mysql SELECT BENCHMARK(1000000,1+1);+-+| BENCHMARK(1000000,1+1) |+-+| 0 |+-+1 row in set (0.32 sec)上述结果是在Pentium II 400MHz的系统上执行得到的。它告诉我们：MySQL在这个系统上可以在0.32秒内执行 1,000,000 次简单的加法运算。所有的MySQL函数都应该被最优化，不过仍然有些函数例外。BENCHMARK() 是一个用于检查查询语句中是否存在问题的非常好的工具。MySQL数据库优化（二）作者: 叶金荣, 出处:IT专家网,责任编辑: 李书琴, 2008-06-11 09:55EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用，也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。MySQL数据库优化（一）1. EXPLAIN 语法(得到SELECT 的相关信息)EXPLAINtbl_name或者:EXPLAINSELECTselect_optionstableIN 语句可以被当作 DESCRIBE 的同义词来用，也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。当在一个 SELECT 语句前使用关键字 EXPLAIN 时，MYSQL会解释了即将如何运行该 SELECT 语句，它显示了表如何连接、连接的顺序等信息。本章节主要讲述了第二种 EXPLAIN 用法。在 EXPLAIN 的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让 SELECT 运行更快。如果由于不恰当使用索引而引起一些问题的话，可以运行 ANALYZE TABLE 来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择。您还可以查看优化程序是否以最佳的顺序来连接数据表。为了让优化程序按照 SELECT 语句中的表名的顺序做连接，可以在查询的开始使用 SELECT STRAIGHT_JOIN 而不只是 SELECT。EXPLAIN 返回了一行记录，它包括了 SELECT 语句中用到的各个表的信息。这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来。MySQL用一次扫描多次连接(single-sweep, multi-join) 的方法来解决连接。这意味着MySQL从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理。在MySQL version 4.1中，EXPLAIN 输出的结果格式改变了，使得它更适合例如 UNION 语句、子查询以及派生表的结构。更令人注意的是，它新增了2个字段： id 和 select_type。当你使用早于MySQL 4.1的版本就看不到这些字段了。EXPLAIN 结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段：id本次 SELECT 的标识符。在查询中每个 SELECT 都有一个顺序的数值。select_typeSELECT 的类型，可能会有以下几种： SIMPLE简单的 SELECT (没有使用 UNION 或子查询) PRIMARY最外层的 SELECT。 UNION第二层，在SELECT 之后使用了 UNION 。 DEPENDENT UNIONUNION 语句中的第二个 SELECT，依赖于外部子查询 SUBQUERY子查询中的第一个 SELECT DEPENDENT SUBQUERY子查询中的第一个 SUBQUERY 依赖于外部的子查询 DERIVED派生表 SELECT(FROM 子句中的子查询)table记录查询引用的表。type表连接类型。以下列出了各种不同类型的表连接，依次是从最好的到最差的： system表只有一行记录(等于系统表)。这是 const 表连接类型的一个特例。 const表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。const 表查询起来非常快，因为只要读取一次!const 用于在和 PRIMARY KEY 或 UNIQUE 索引中有固定值比较的情形。下面的几个查询中，tbl_name 就是 const 表了：SELECT*FROMtbl_nameWHEREprimary_key=1; SELECT*FROMtbl_name WHEREprimary_key_part1=1ANDprimary_key_part2=2; eq_ref从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与 const 类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个 PRIMARY KEY 或 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.column ANDref_table.key_column_part2=1; ref该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref 用于连接程序使用键的最左前缀或者是该键不是 PRIMARY KEY 或 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 值的记录。这种连接类型的优化是从MySQL 4.1.1开始的，它经常用于子查询。在以下的例子中，MySQL使用 ref_or_null 类型来处理 ref_table：SELECT*FROMref_table WHEREkey_column=exprORkey_columnISNULL; index_merge这种连接类型意味着使用了 Index Merge 优化方法。这种情况下，key字段包括了所有使用的索引，key_len 包括了使用的键的最长部分。详情请看7.2.5 How MySQL Optimizes OR Clauses。 unique_subquery这种类型用例如一下形式的 IN 子查询来替换 ref：value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery 只是用来完全替换子查询的索引查找函数效率更高了。 index_subquery这种连接类型类似 unique_subquery。它用子查询来代替 IN，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：value IN (SELECT key_column FROM single_table WHERE some_expr) range只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key 字段表示使用了哪个索引。key_len 字段包括了使用的键的最长部分。这种类型时 ref 字段值是 NULL。range 用于将某个字段和一个定植用以下任何操作符比较时 =, , , =, , =, IS NULL, , 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_keyspossible_keys 字段是指MySQL在搜索表记录时可能使用哪个索引。注意，这个字段完全独立于 EXPLAIN 显示的表顺序。这就意味着 possible_keys 里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是 NULL，就表示没有索引被用到。这种情况下，就可以检查 WHERE 子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样，创建一下索引，然后再用 EXPLAIN 检查一下。详细的查看章节14.2.2 ALTER TABLE Syntax。想看表都有什么索引，可以通过 SHOW INDEX FROM tbl_name 来看。keykey 字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是 NULL。想要让MySQL强行使用或者忽略在 possible_keys 字段中的索引列表，可以在查询语句中使用关键字FORCE INDEX, USE INDEX, 或 IGNORE INDEX。如果是 MyISAM 和 BDB 类型表，可以使用 ANALYZE TABLE 来帮助分析使用使用哪个索引更好。如果是 MyISAM 类型表，运行命令 myisamchk -analyze 也是一样的效果。详细的可以查看章节 ANALYZE TABLE Syntax和5.7.2 Table Maintenance and Crash Recovery。key_lenkey_len 字段显示了MySQL使用索引的长度。当 key 字段的值为 NULL 时，索引的长度就是 NULL。注意，key_len 的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。refref 字段显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来。rowsrows 字段显示了MySQL认为在查询中应该检索的记录数。Extra本字段显示了查询中MySQL的附加信息。以下是这个字段的几个不同值的解释：DistinctMySQL当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。Not existsMySQL在查询时做一个 LEFT JOIN 优化时，当它在当前表中找到了和前一条记录符合 LEFT JOIN 条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：SELECT*FROMt1LEFTJOINt2ONt1.id=t2.id WHEREt2.idISNULL;假使 t2.id 定义为 NOT NULL。这种情况下，MySQL将会扫描表 t1 并且用 t1.id 的值在 t2 中查找记录。当在 t2 中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是 NULL，就不会再在 t2 中查找相同 id 值的其他记录了。也可以这么说，对于 t1 中的每个记录，MySQL只需要在 t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。range checked for each record (index map: #)MySQL没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引(如果有的话)，并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。Using filesortMySQL需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 WHERE 条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。详情请看7.2.9 How MySQL Optimizes ORDER BY。Using index字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。Using temporaryMySQL需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了GROUP BY 和 ORDER BY 子句，它以不同的方式列出了各个字段。Using whereWHERE 子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的 Extra 字段值不是 Using where 并且表连接类型是 ALL 或 index 时可能表示有问题。如果你想要让查询尽可能的快，那么就应该注意 Extra 字段的值为Using filesort 和 Using temporary 的情况。你可以通过 EXPLAIN 的结果中 rows 字段的值的乘积大概地知道本次连接表现如何。它可以粗略地告诉我们MySQL在查询过程中会查询多少条记录。如果是使用系统变量 max_join_size 来取得查询结果，这个乘积还可以用来确定会执行哪些多表 SELECT 语句。详情请看7.5.2 Tuning Server Parameters。下面的例子展示了如何通过 EXPLAIN 提供的信息来较大程度地优化多表联合查询的性能。假设有下面的 SELECT 语句，正打算用 EXPLAIN 来检测：EXPLAIN SELECT tt.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 FROM tt, et, et AS et_1, do WHERE tt.SubmitTime IS NULL AND tt.ActualPC = et.EMPLOYID AND tt.AssignedPC = et_1.EMPLOYID AND tt.ClientID = do.CUSTNMBR;在这个例子中，先做以下假设： 要比较的字段定义如下：Table Column Column Type tt ActualPC CHAR(10) tt AssignedPC CHAR(10) tt ClientID CHAR(10) et EMPLOYID CHAR(15) do CUSTNMBR CHAR(15) 数据表的索引如下：Table Index tt ActualPC tt AssignedPC tt ClientID et EMPLOYID (primary key) do CUSTNMBR (primary key) tt.ActualPC 的值是不均匀分布的。在任何优化措施未采取之前，经过 EXPLAIN 分析的结果显示如下：table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL NULL 74do ALL PRIMARY NULL NULL NULL 2135et_1 ALL PRIMARY NULL NULL NULL 74tt ALL AssignedPC, NULL NULL NULL 3872 ClientID, ActualPC range checked for each record (key map: 35)由于字段 type 的对于每个表值都是 ALL，这个结果意味着MySQL对所有的表做一个迪卡尔积;这就是说，每条记录的组合。这将需要花很长的时间，因为需要扫描每个表总记录数乘积的总和。在这情况下，它的积是 74 * 2135 * 74 * 3872 = 45,268,558,720 条记录。如果数据表更大的话，你可以想象一下需要多长的时间。在这里有个问题是当字段定义一样的时候，MySQL就可以在这些字段上更快的是用索引(对 ISAM 类型的表来说，除非字段定义完全一样，否则不会使用索引)。在这个前提下，VARCHAR 和 CHAR是一样的除非它们定义的长度不一致。由于 tt.ActualPC 定义为 CHAR(10)，et.EMPLOYID 定义为 CHAR(15)，二者长度不一致。为了解决这个问题，需要用 ALTER TABLE 来加大 ActualPC 的长度从10到15个字符： mysqlALTERTABLEttMODIFYActualPCVARCHAR(15);现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)了。再来执行一次 EXPLAIN 语句看看结果：table type possible_keys key key_len ref rows Extratt ALL AssignedPC, NULL NULL NULL 3872 Using ClientID, where ActualPCdo ALL PRIMARY NULL NULL NULL 2135 range checked for each record (key map: 1)et_1 ALL PRIMARY NULL NULL NULL 74 range checked for each record (key map: 1)et eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1这还不够，它还可以做的更好：现在 rows 值乘积已经少了74倍。这次查询需要用2秒钟。第二个改变是消除在比较 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 中字段的长度不一致问题：mysql ALTER TABLE tt MODIFY AssignedPC VARCHAR(15), - MODIFY ClientID VARCHAR(15);现在 EXPLAIN 的结果如下：table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL NULL 74tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 Using ClientID, where ActualPCet_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1这看起来已经是能做的最好的结果了。遗留下来的问题是，MySQL默认地认为字段tt.ActualPC 的值是均匀分布的，然而表 tt 并非如此。幸好，我们可以很方便的让MySQL分析索引的分布：mysqlANALYZETABLEtt;到此为止，表连接已经优化的很完美了，EXPLAIN 的结果如下： table type possible_keys key key_len ref rows Extratt ALL AssignedPC NULL NULL NULL 3872 Using ClientID, where ActualPCet eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1请注意，EXPLAIN 结果中的 rows 字段的值也是MySQL的连接优化程序大致猜测的，请检查这个值跟真实值是否基本一致。如果不是，可以通过在 SELECT 语句中使用 STRAIGHT_JOIN 来取得更好的性能，同时可以试着在 FROM分句中用不同的次序列出各个表。MySQL数据库优化（三）作者: 叶金荣, 出处:IT专家网,责任编辑: 李书琴, 2008-06-11 10:05这个章节讲述了优化程序如何处理 WHERE 子句。例子中使用了 SELECT 语句，但是在 DELETE 和 UPDATE 语句中对 WHERE 子句的优化是一样的。注意，关于MySQL优化的工作还在继续，因此本章节还没结束。MySQL做了很多优化工作，而不仅仅是文档中提到的这些。1. 估算查询性能在大多数情况下，可以通过统计磁盘搜索次数来估算查询的性能。对小表来说，通常情况下只需要搜索一次磁盘就能找到对应的记录(因为索引可能已经缓存起来了)。对大表来说，大致可以这么估算，它使用B树做索引，想要找到一条记录大概需要搜索的次数为：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length) + 1。在MySQL中，一个索引块通常是1024bytes，数据指针通常是4bytes。对于一个有500,000条记录、索引长度为3bytes(medium integer)的表来说，根据上面的公式计算得到需要做 log(500,000)/log(1024/3*2/(3+4) + 1 = 4 次搜索。这个表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的存储空间(假定典型的索引缓冲区的2/3)，因此应该会有更多的索引在内存中，并且可能只需要1到2次调用就能找到对应的记录。对于写来说，大概需要4次(甚至更多)搜索才能找到新的索引位置，更新记录时通常需要2次搜索。请注意，前面的讨论中并没有提到应用程序的性能会因为log N的值越大而下降。只要所有的东西都能由操作系统或者SQL服务器缓存起来，那么性能只会因为数据表越大而稍微下降。当数据越来越大之后，就不能全部放到缓存中去了，就会越来越慢了，除非应用程序是被磁盘搜索约束的(它跟随着的log N值增加而增加)。为了避免这种情况，可以在数据量增大以后也随着增大索引缓存容量。对 MyISAM 类型表来说，索引缓存容量是由系统变量 key_buffer_size 控制的。2. SELECT 查询的速度通常情况下，想要让一个比较慢的 SELECT . WHERE 查询变得更快的第一件事就是，先检查看看是否可以增加索引。所有对不同表的访问都通常使用索引。可以使用 EXPLAIN 语句来判断 SELECT 使用了哪些索引。详情请看7.4.5 How MySQL Uses Indexes和7.2.1 EXPLAIN Syntax (Get Information About a SELECT)。以下是几个常用的提高 MyISAM 表查询速度的忠告：想要让MySQL将查询优化的速度更快些，可以在数据表已经加载完全部数据后执行行 ANALYZE TABLE 或运行 myisamchk -analyze 命令。它更新了每个索引部分的值，这个值意味着相同记录的平均值(对于唯一索引来说，这个值则一直都是 1)。MySQL就会在当你使用基于一个非恒量表达式的两表连接时，根据这个值来决定使用哪个索引。想要查看结果，可以在分析完数据表后运行 SHOW INDEX FROM tbl_name 查看 Cardinality 字