mysql怎么优化语句提高速度

mysql如何优化语句提高速度
本问题地址：http://www.educity.cn/wenda/592484.html
　　
7.1. 优化概述
　　
7.1.1. MySQL设计局限与折衷
7.1.2. 为可移植性设计应用程序
7.1.3. 我们已将MySQL用在何处？
7.1.4. MySQL基准套件
7.1.5. 使用自己的基准
　　使一个系统更快的最重要因素当然是基本设计。此外，还需要知道系统正做什么样的事情，以及瓶颈是什么。
　　最常见的系统瓶颈是：
磁盘搜索。需要花时间从磁盘上找到一个数据，用在现代磁盘的平均时间通常小于10ms，因此理论上我们能够每秒大约搜索1000次。这个时间在新磁盘上提高不大并且很难为一个表进行优化。优化它的方法是将数据分布在多个磁盘上。
磁盘读/写。当磁盘放入正确位置后，我们需要从中读取数据。对于现代的磁盘，一个磁盘至少传输10-20Mb/s的吞吐。这比搜索要容易优化，因为你能从多个磁盘并行地读。
CPU周期。我们将数据读入内存后，需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。但是对于小表，速度通常不成问题。
　　内存带宽。当CPU需要的数据超出CPU缓存时，主缓存带宽就成为内存的一个瓶颈。这在大多数系统正是一个不常见的瓶颈但是你应该知道它。
　　
7.1.1. MySQL设计局限与折衷
　　当使用MyISAM存储引擎时，MySQL使用极快速的表锁定，以便允许多次读或一次写。使用该存储引擎的最大问题出现在同一个表中进行混合稳定数据流更新与慢速选择。如果这只是某些表的问题，你可以使用另一个存储引擎。参见第15章：存储引擎和表类型。
　　MySQL可以使用事务表和非事务表。为了更容易地让非事务表顺利工作(如果出现问题不能回滚)，MySQL采用下述规则。请注意这些规则只适用于不运行在严格模式下或为INSERT或UPDATE使用IGNORE规定程序时。
　　·所有列有默认值。请注意当运行在严格SQL模式(包括TRADITIONAL SQL模式)时，必须为NOT NULL列指定默认值。
　　·如果向列内插入不合适的或超出范围的值，MySQL将该列设定为“最好的可能的值”，而不是报告错误。对于数字值，为0、可能的最小值或最大值。对于字符串，为空字符串或列内可以保存的字符串。请注意当运行在严格模式或TRADITIONAL SQL模式时该行为不 适用。
　　·所有表达式的计算结果返回一个表示错误状况的信号。例如，1/0返回NULL。(使用ERROR_FOR_DIVISION_BY_ZERO SQL模式可以更改该行为)。
　　如果正使用非事务表，不应该使用MySQL来检查列的内容。一般情况，最安全的(通常是最快的)方法径是让应用程序确保只向数据库传递合法值。
　　相关详细信息参见1.8.6节，“MySQL处理约束的方式”和13.2.4节，“INSERT语法”或5.3.2节，“SQL服务器模式”。
　　
7.1.2. 为可移植性设计应用程序
　　因为不同SQL服务器实现了标准SQL的不同部分，需要花功夫来编写可移植的SQL应用程序。对很简单的选择/插入，很容易实现移植，但是需要的功能越多则越困难。如果想要应用程序对很多数据库系统都快，它变得更难！
　　为了使一个复杂应用程序可移植，你需要选择它应该工作的SQL服务器，并确定这些服务器支持什么特性。
　　所有数据库都有一些弱点。这就是它们不同的设计折衷导致的不同行为。
　　可以使用MySQL的crash-me程序来找出能用于数据库服务器选择的函数、类型和限制。crash-me并不能找出所有的特性，但是其广度仍然很合理，可以进行大约450个测试。
　　crash-me可以提供的一种类型的信息的例子：如果想要使用Informix或DB2，不应该使用超过18个字符的列名。
　　crash-me程序和MySQL基准程序是独立于数据库的。通过观察它们是如何编写的，编可以知道必须为编写独立于数据库的应用程序做什么。基准本身可在MySQL源码分发的“sql-bench”目录下找到。它们用DBI数据库接口以Perl写成。使用DBI本身即可以解决部分移植性问题，因为它提供与数据库无关的的存取方法。
　　如果你为数据库的独立性而努力，需要很好地了解每个SQL服务器的瓶颈。例如，MySQL在检索和更新MyISAM表记录方面很快，但是在同一个表上混合慢速读者和写者方面有一个问题。另一方面，当你试图访问最近更新了(直到它们被刷新到磁盘上)的行时，在Oracle中有一个很大的问题。事务数据库总的来说在从记录文件表中生成总结表方面不是很好，因为在这种情况下，行锁定几乎没有用。
　　为了使应用程序“确实”独立于数据库，需要定义一个容易扩展的接口，用它可操纵数据。因为C++在大多数系统上可以适用，使用数据库的一个C++ 类接口是有意义的。
　　如果你使用某个数据库特定的功能(例如MySQL专用的REPLACE语句)，应该为SQL服务器编码一个方法以实现同样的功能。尽管慢些，但确允许其它服务器执行同样的任务。
　　用MySQL，可以使用/! /语法把MySQL特定的关键词加到查询中。在/**/中的代码将被其它大多数SQL服务器视为注释(并被忽略)。
　　如果高性能真的比准确性更重要，就像在一些web应用程序那样，一种可行的方法是创建一个应用层，缓存所有的结果以便得到更高的性能。通过只是让旧的结果在短时间后‘过期’，能保持缓存合理地刷新。这在极高负载的情况下是相当不错的，在此情况下，能动态地增加缓存并且设定较高的过期时限直到一切恢复正常。
　　在这种情况下，表创建信息应该包含缓存初始大小和表刷新频率等信息。
　　实施应用程序缓存的一种方法是使用MySQL查询缓存。启用查询缓存后，服务器可以确定是否可以重新使用查询结果。这样简化了你的应用程序。参见5.13节，“MySQL查询高速缓冲”。
　　
7.1.3. 我们已将MySQL用在何处？
　　该节描述了Mysql的早期应用程序。
　　在MySQL最初开发期间，MySQL的功能适合大多数客户。MySQL为瑞典的一些最大的零售商处理数据仓库。
　　我们从所有商店得到所有红利卡交易的每周总结，并且我们期望为所有店主提供有用的信息以帮助他们得出他们的广告战如何影响他们的顾客。
　　数据是相当巨量的(大约每月7百万宗交易总结)并且我们保存4-10年来的数据需要呈现给用户。我们每周从顾客那里得到请求，他们想要“立刻”访问来自该数据的新报告。
　　我们通过每月将所有信息存储在压缩的“交易”表中来解决它。我们有一套简单的宏/脚本用来生成来自交易表的不同条件( 产品组、顾客id，商店…)的总结表。报告是由一个进行语法分析网页的小perl脚本动态生成的网页，在脚本中执行SQL语句并且插入结果。我们很想使用PHP或mod_perl，但是那时它们还不可用。
　　对图形数据，我们用C语言编写了一个简单的工具，它能基于那些结果处理SQL查询结果并生成GIF图形。该工具也从分析Web网页的perl脚本中动态地执行。
　　在大多数情况下，一个新的报告通过简单地复制一个现有脚本并且修改其中的SQL查询来完成。在一些情况下，我们将需要把更多的列加到一个现有的总结表中或产生一个新的，但是这也相当简单，因为我们在磁盘上保存所有交易表。（目前我们大约有50G的交易表和200G的其它顾客数据)。
　　我们也让我们的顾客直接用ODBC访问总结表以便高级用户能自己用这些数据进行试验。
　　该系统工作得很好，我们可以毫无问题地用很适度的Sun Ultra SPARC工作站硬件(2x200MHz)来处理数据。该系统被逐步移植到了Linux中。
　　
7.1.4. MySQL基准套件
　　本节应该包含MySQL基准套件(和crash-me)的技术描述，但是该描述还没写成。目前，你可以通过在MySQL源码分发中的“sql-bench”目录下的代码和结果了解基准套件是如何工作的。
　　通过基准用户可以了解一个给定的SQL实现在哪方面执行得很好或很糟糕。
　　注意，这个基准是单线程的，它可以测量操作执行的最小时间。我们计划将来在基准套件中添加多线程测试。
　　要使用基准套件，必须满足下面的要求：
　　·　　　　 基准套件随MySQL源码分发提供。可以从下载分发，或者使用当前的开发源码树(参见2.8.3节，“从开发源码树安装”)。
　　·　　　　 基准脚本用Perl编写而成，使用Perl DBI模块访问数据库服务器，因此必须安装DBI。还需要为每个待测试的服务器提供服务器专用DBD驱动程序。例如，要测试MySQL、PostgreSQL和DB2，必须安装DBD::mysql、DBD::Pg和DBD::DB2模块。参见2.13节，“Perl安装注意事项”。
　　获得MySQL源码分发后，可以在sql-bench目录找到基准套件。要运行基准测试，应构建MySQL，然后进入sql-bench目录并执行run-all-tests脚本：
　　shell> cd sql-bench
　　shell> perl run-all-tests –server=server_name
　　server_name是一个支持的服务器。要获得所有选项和支持的服务器，调用命令：
　　shell> perl run-all-tests –help
　　crash-me脚本也位于sql-bench目录。crash-me尝试通过实际运行查询确定数据库支持的特性以及其功能和限制。例如，它确定：
　　·　　　　 支持什么列类型
　　·　　　　 支持多少索引
　　·　　　　 支持什么函数
　　·　　　　 查询可以多大
　　·　　　　 VARCHAR列可以多大
　　关于一些可移植的基准程序的例子，参见MySQL基准套件。请参见7.1.4节，“MySQL基准套件”。可以利用这个套件的任何程序并且根据你的需要修改它。通过这样做，可以尝试不同的问题的解决方案并测试哪一个是最好的解决方案。
　　另一个免费基准套件是开放源码数据库基准套件，参见。
　　在系统负载繁重时出现一些问题是很普遍的，并且很多客户已经与我们联系了，他们在生产系统中有一个(测试)系统并且有负载问题。大多数情况下，性能问题经证明是与基本数据库设计有关的问题(例如，表扫描在高负载时表现不好)或操作系统或库问题。如果系统已经不在生产系统中，它们大多数将很容易修正。
　　为了避免这样的问题，应该把工作重点放在在可能最坏的负载下测试你的整个应用程序。你可以使用Super Smack。该工具可以从获得。正如它的名字所建议，它可以根据你的需要提供合理的系统，因此确保只用于你的开发系统。
　　
7.2. 优化SELECT语句和其它查询
　　
7.2.1. EXPLAIN语法（获取SELECT相关信息）
7.2.2. 估计查询性能
7.2.3. SELECT查询的速度
7.2.4. MySQL怎样优化WHERE子句
7.2.5. 范围优化
7.2.6. 索引合并优化
7.2.7. MySQL如何优化IS NULL
7.2.8. MySQL如何优化DISTINCT
7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN
7.2.10. MySQL如何优化嵌套Join
7.2.11. MySQL如何简化外部联合
7.2.12. MySQL如何优化ORDER BY
7.2.13. MySQL如何优化GROUP BY
7.2.14. MySQL如何优化LIMIT
7.2.15. 如何避免表扫描
7.2.16. INSERT语句的速度
7.2.17. UPDATE语句的速度
7.2.18. DELETE语句的速度
7.2.19. 其它优化技巧
　　首先，影响所有语句的一个因素是：你的许可设置得越复杂，所需要的开销越多。
　　执行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)
　　上面结果在PentiumII 400MHz系统上获得。它显示MySQL在该系统上在0.32秒内可以执行1,000,000个简单的+表达式运算。
　　所有MySQL函数应该被高度优化，但是总有可能有一些例外。BENCHMARK()是一个找出是否查询有问题的优秀的工具。
7.2.1. EXPLAIN语法（获取SELECT相关信息）
　　EXPLAIN tbl_name
　　或：
　　EXPLAIN [EXTENDED] SELECT select_options
　　EXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息：
　　·　　　　 EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。
　　·　　　　 如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。
　　该节解释EXPLAIN的第2个用法。
　　借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
　　如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计（例如关键字集的势），这样会影响优化器进行的选择。参见13.5.2.1节，“ANALYZE TABLE语法”。
　　还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是SELECT开头。
　　EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。
　　当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。
　　EXPLAIN的每个输出行提供一个表的相关信息，并且每个行包括下面的列：
　　·　　　　 id
　　SELECT识别符。这是SELECT的查询序列号。
　　·　　　　 select_type
　　SELECT类型，可以为以下任何一种：
　　o　　　　SIMPLE
　　简单SELECT(不使用UNION或子查询)
　　o　　　　PRIMARY
　　最外面的SELECT
　　o　　　　UNION
　　UNION中的第二个或后面的SELECT语句
　　o　　　　DEPENDENT UNION
　　UNION中的第二个或后面的SELECT语句，取决于外面的查询
　　o　　　　UNION RESULT
　　UNION的结果。
　　o　　　　SUBQUERY
　　子查询中的第一个SELECT
　　o　　　　DEPENDENT SUBQUERY
　　子查询中的第一个SELECT，取决于外面的查询
　　o　　　　DERIVED
　　导出表的SELECT(FROM子句的子查询)
　　·　　　　 table
　　输出的行所引用的表。
　　·　　　　 type
　　联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：
　　o　　　　system
　　表仅有一行(=系统表)。这是const联接类型的一个特例。
　　o　　　　const
　　表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！
　　const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：
　　SELECT * from tbl_name WHERE primary_key=1；
　　SELECT * from tbl_name
　　WHERE primary_key_part1=1和 primary_key_part2=2；
　　o　　　　eq_ref
　　对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
　　eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
　　在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：
　　SELECT * FROM ref_table,other_table
　　WHERE ref_table.key_column=other_table.column;
　　SELECT * FROM ref_table,other_table
　　WHERE ref_table.key_column_part1=other_table.column
　　AND ref_table.key_column_part2=1;
　　o　　　　ref
　　对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。
　　ref可以用于使用=或<=>操作符的带索引的列。
　　在下面的例子中，MySQL可以使用ref联接来处理ref_tables：
　　SELECT * FROM ref_table WHERE key_column=expr;
　　SELECT * FROM ref_table,other_table
　　WHERE ref_table.key_column=other_table.column;
　　SELECT * FROM ref_table,other_table
　　WHERE ref_table.key_column_part1=other_table.column
　　AND ref_table.key_column_part2=1;
　　o　　　　ref_or_null
　　该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
　　在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：
　　SELECT * FROM ref_table
　　WHERE key_column=expr OR key_column IS NULL;
　　参见7.2.7节，“MySQL如何优化IS NULL”。
　　o　　　　index_merge
　　该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。详细信息参见7.2.6节，“索引合并优化”。
　　o　　　　unique_subquery
　　该类型替换了下面形式的IN子查询的ref：
　　value IN (SELECT primary_key FROM single_table WHERE some_expr)
　　unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
　　o　　　　index_subquery
　　该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：
　　value IN (SELECT key_column FROM single_table WHERE some_expr)
　　o　　　　range
　　只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。
　　当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range：
　　SELECT * FROM tbl_name
　　WHERE key_column = 10;
　　SELECT * FROM tbl_name
　　WHERE key_column BETWEEN 10 and 20;
　　SELECT * FROM tbl_name
　　WHERE key_column IN (10,20,30);
　　SELECT * FROM tbl_name
　　WHERE key_part1= 10 AND key_part2 IN (10,20,30);
　　o　　　　index
　　该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。
　　当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。
　　o　　　　ALL
　　对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。
　　·　　　　 possible_keys
　　possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
　　如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。参见13.1.2节，“ALTER TABLE语法”。
　　为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。
　　·　　　　 key
　　key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见13.2.7节，“SELECT语法”。
　　对于MyISAM和BDB表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，可以使用myisamchk –analyze。参见13.5.2.1节，“ANALYZE TABLE语法”和5.9.4节，“表维护和崩溃恢复”。
　　·　　　　 key_len
　　key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。
　　·　　　　 ref
　　ref列显示使用哪个列或常数与key一起从表中选择行。
　　·　　　　 rows
　　rows列显示MySQL认为它执行查询时必须检查的行数。
　　·　　　　 Extra
　　该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：
　　o　　　　Distinct
　　MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。
　　o　　　　Not exists
　　MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。
　　下面是一个可以这样优化的查询类型的例子：
　　SELECT * 从t1 LEFT JOIN t2 ON t1.id=t2.id
　　WHERE t2.id IS NULL；
　　假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。
　　o　　　　range checked for each record (index map: #)
　　MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。关于适用性标准的描述参见7.2.5节，“范围优化”和7.2.6节，“索引合并优化”，不同的是前面表的所有列值已知并且认为是常量。
　　这并不很快，但比执行没有索引的联接要快得多。
　　o　　　　Using filesort
　　MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。参见7.2.12节，“MySQL如何优化ORDER BY”。
　　o　　　　Using index
　　从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
　　o　　　　Using temporary
　　为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
　　o　　　　Using where
　　WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。
　　如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。
　　o　　　　Using sort_union(…), Using union(…), Using intersect(…)
　　这些函数说明如何为index_merge联接类型合并索引扫描。详细信息参见7.2.6节，“索引合并优化”。
　　o　　　　Using index for group-by
　　类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。详情参见7.2.13节，“MySQL如何优化GROUP BY”。
　　通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。参见7.5.2节，“调节服务器参数”。
　　下列例子显示出一个多表JOIN如何能使用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;
　　对于这个例子，假定：
　　·　　　　 被比较的列声明如下：
　　表
　　列
　　列类型
　　tt
　　ActualPC
　　CHAR(10)
　　tt
　　AssignedPC
　　CHAR(10)
　　tt
　　ClientID
　　CHAR(10)
　　et
　　EMPLOYID
　　CHAR(15)
　　do
　　CUSTNMBR
　　CHAR(15)
　　·　　　　 表有下面的索引：
　　表
　　索引
　　tt
　　ActualPC
　　tt
　　AssignedPC
　　tt
　　ClientID
　　et
　　EMPLOYID(主键)
　　do
　　CUSTNMBR(主键)
　　·　　　　 tt.ActualPC值不是均匀分布的。
　　开始，在进行优化前，EXPLAIN语句产生下列信息：
　　table type possible_keys key key_len ref rows Extra
　　et　 ALL PRIMARY　　　 NULL NULL　 NULL 74
　　do　 ALL PRIMARY　　　 NULL NULL　 NULL 2135
　　et_1 ALL PRIMARY　　　 NULL NULL　 NULL 74
　　tt　 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能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。
　　为了修正在列长度上的不同，使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符：
　　mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);
　　现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再执行EXPLAIN语句产生这个结果：
　　table type　 possible_keys key　　 key_len ref　　　　 rows　 Extra
　　tt　 ALL 　 AssignedPC,　 NULL　 NULL　 NULL　　　 3872　 Using
　　ClientID,　　　　　　　　　　　　　　　　　　　　 where
　　ActualPC
　　do　 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 Extra
　　et　 ALL　 PRIMARY　　　 NULL　　 NULL　 NULL　　　　 74
　　tt　 ref　 AssignedPC,　 ActualPC 15　　 et.EMPLOYID　 52　 Using
　　ClientID,　　　　　　　　　　　　　　　　　　　　 where
　　ActualPC
　　et_1 eq_ref PRIMARY　　　 PRIMARY 15　　 tt.AssignedPC 1
　　do　 eq_ref PRIMARY　　　 PRIMARY 15　　 tt.ClientID　 1
　　这几乎很好了。
　　剩下的问题是，默认情况，MySQL假设在tt.ActualPC列的值是均匀分布的，并且对tt表不是这样。幸好，很容易告诉MySQL来分析关键字分布：
　　mysql> ANALYZE TABLE tt；
　　现在联接是“完美”的了，而且EXPLAIN产生这个结果：
　　table type　 possible_keys key　　 key_len ref　　　　　 rows Extra
　　tt　 ALL　 AssignedPC　 NULL　 NULL　 NULL　　　　 3872 Using
　　ClientID,　　　　　　　　　　　　　　　　　　　 where
　　ActualPC
　　et　 eq_ref PRIMARY　　　 PRIMARY 15　　 tt.ActualPC　 1
　　et_1 eq_ref PRIMARY　　　 PRIMARY 15　　 tt.AssignedPC 1
　　do　 eq_ref PRIMARY　　　 PRIMARY 15　　 tt.ClientID　 1
　　注意在从EXPLAIN输出的rows列是一个来自MySQL联接优化器的“教育猜测”。你应该检查数字是否接近事实。如果不是，可以通过在SELECT语句里面使用STRAIGHT_JOIN并且试着在FROM子句以不同的次序列出表，可能得到更好的性能。
　　
7.2.2. 估计查询性能
　　在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B－树索引进行估计，将需要log(row_count)/log(index_block_length/3 * 2/(index_length+ data_pointer_length))+1次搜索才能找到行。
　　在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，这对于有一个长度为3(中等整数)的索引的500,000行的表，通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。
　　上面的索引需要大约500,000 * 7 * 3/2 = 5.2MB，(假设典型情况下索引缓存区填充率为2/3)，可以将大部分索引保存在内存中，仅需要1-2调用从OS读数据来找出行。
　　然而对于写，将需要4次搜索请求(如上)来找到在哪儿存放新索引，并且通常需要2次搜索来更新这个索引并且写入行。
　　注意，上述讨论并不意味着应用程序的性能将缓慢地以logN 退化！当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题，随数据增加而增加 键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制 键高速缓冲区大小。参见7.5.2节，“调节服务器参数”。
　　
7.2.3. SELECT查询的速度
　　总的来说，要想使一个较慢速SELECT … WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。参见7.4.5节，“MySQL如何使用索引”和7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。
　　下面是一些加速对MyISAM表的查询的一般建议：
　　·　　　　 为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk –analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk –description –verbose可以显示索引分布信息。
　　·　　　　 要想根据一个索引排序一个索引和数据，使用myisamchk –sort-index –sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！
　　
7.2.4. MySQL怎样优化WHERE子句
　　该节讨论为处理WHERE子句而进行的优化。例子中使用了SELECT语句，但相同的优化也适用DELETE和UPDATE语句中的WHERE子句。
　　请注意对MySQL优化器的工作在不断进行中，因此该节并不完善。MySQL执行了大量的优化，本文中所列的并不详尽。
　　下面列出了MySQL执行的部分优化：
　　·　　　　 去除不必要的括号：
　　·　　　　　　　 　　　 ((a AND b) AND c OR (((a AND b) AND (c AND d))))
　　·　　　　　　　 -> (a AND b AND c) OR (a AND b AND c AND d)
　　·　　　　 常量重叠：
　　·　　　　　　　 　 (a