7. 优化 · mysql5.1参考手册

### 第7章：优化优化是一个复杂的任务，因为最终要求了解整个待优化的系统。尽管可以进行局部优化而不需要了解系统或应用程序，为了优化得更好，你必须知道更多的信息。本章解释并给出不同的优化MySQL的方法示例。但要记住总有一些其它方法使系统更快，尽管需要更多的工作。 ### 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章：](#)[_存储引擎和表类型_](# "Chapter 15. Storage Engines and Table Types")。 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处理约束的方式”](# "1.8.6. How MySQL Deals with Constraints")和[13.2.4节，“INSERT语法”](# "13.2.4. INSERT Syntax")或[5.3.2节，“SQL服务器模式”](# "5.3.2. The Server SQL Mode")。 ### 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本身即可以解决部分移植性问题，因为它提供与数据库无关的的存取方法。关于**crash-me**结果，访问[http://dev.mysql.com/tech-resources/crash-me.php](http://dev.mysql.com/tech-resources/crash-me.php)。到[http://dev.mysql.com/tech-resources/benchmarks/](http://dev.mysql.com/tech-resources/benchmarks/)看这个基准的结果。如果你为数据库的独立性而努力，需要很好地了解每个SQL服务器的瓶颈。例如，**MySQL**在检索和更新MyISAM表记录方面很快，但是在同一个表上混合慢速读者和写者方面有一个问题。另一方面，当你试图访问最近更新了(直到它们被刷新到磁盘上)的行时，在Oracle中有一个很大的问题。事务数据库总的来说在从记录文件表中生成总结表方面不是很好，因为在这种情况下，行锁定几乎没有用。为了使应用程序“确实”独立于数据库，需要定义一个容易扩展的接口，用它可操纵数据。因为C++在大多数系统上可以适用，使用数据库的一个C++ 类接口是有意义的。如果你使用某个数据库特定的功能(例如**MySQL专用的**REPLACE语句)，应该为SQL服务器编码一个方法以实现同样的功能。尽管慢些，但确允许其它服务器执行同样的任务。用**MySQL**，可以使用/*! */语法把**MySQL**特定的关键词加到查询中。在/**/中的代码将被其它大多数SQL服务器视为注释(并被忽略)。如果高性能真的比准确性更重要，就像在一些web应用程序那样，一种可行的方法是创建一个应用层，缓存所有的结果以便得到更高的性能。通过只是让旧的结果在短时间后‘过期’，能保持缓存合理地刷新。这在极高负载的情况下是相当不错的，在此情况下，能动态地增加缓存并且设定较高的过期时限直到一切恢复正常。在这种情况下，表创建信息应该包含缓存初始大小和表刷新频率等信息。实施应用程序缓存的一种方法是使用MySQL查询缓存。启用查询缓存后，服务器可以确定是否可以重新使用查询结果。这样简化了你的应用程序。参见[5.13节，“MySQL查询高速缓冲”](# "5.13. The MySQL Query Cache")。 ### 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源码分发提供。可以从[http://dev.mysql.com/downloads/](http://dev.mysql.com/downloads/)下载分发，或者使用当前的开发源码树(参见[2.8.3节，“从开发源码树安装”](# "2.8.3. Installing from the Development Source Tree"))。・基准脚本用Perl编写而成，使用Perl DBI模块访问数据库服务器，因此必须安装DBI。还需要为每个待测试的服务器提供服务器专用DBD驱动程序。例如，要测试MySQL、PostgreSQL和DB2，必须安装DBD::mysql、DBD::Pg和DBD::DB2模块。参见[2.13节，“Perl安装注意事项”](# "2.13. Perl Installation Notes")。获得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列可以多大可以从[http://dev.mysql.com/tech-resources/crash-me.php](http://dev.mysql.com/tech-resources/crash-me.php)发现许多不同数据库服务器的**crash-me**的结果。关于基准测试结果的详细信息，访问[http://dev.mysql.com/tech-resources/benchmarks/](http://dev.mysql.com/tech-resources/benchmarks/)。 ### 7.1.5. 使用自己的基准一定要测试应用程序和数据库，以发现瓶颈在哪儿。通过修正它(或通过用一个“哑模块”代替瓶颈)，可以很容易地确定下一个瓶颈。即使你的应用程序的整体性能目前可以接受，至少应该对每个瓶颈做一个计划，如果某天确实需要更好的性能，应知道如何解决它。关于一些可移植的基准程序的例子，参见MySQL基准套件。请参见[7.1.4节，“MySQL基准套件”](# "7.1.4. The MySQL Benchmark Suite")。可以利用这个套件的任何程序并且根据你的需要修改它。通过这样做，可以尝试不同的问题的解决方案并测试哪一个是最好的解决方案。另一个免费基准套件是开放源码数据库基准套件，参见[http://osdb.sourceforge.net/](http://osdb.sourceforge.net/)。在系统负载繁重时出现一些问题是很普遍的，并且很多客户已经与我们联系了，他们在生产系统中有一个(测试)系统并且有负载问题。大多数情况下，性能问题经证明是与基本数据库设计有关的问题(例如，表扫描在高负载时表现不好)或操作系统或库问题。如果系统已经不在生产系统中，它们大多数将**很**容易修正。为了避免这样的问题，应该把工作重点放在在可能最坏的负载下测试你的整个应用程序。你可以使用Super Smack。该工具可以从[http://jeremy.zawodny.com/mysql/super-smack/](http://jeremy.zawodny.com/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语法”](# "13.5.2.1. ANALYZE TABLE Syntax")。还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个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``”](# "7.2.7. How MySQL Optimizes IS NULL")。 o index_merge 该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。详细信息参见[7.2.6节，“索引合并优化”](# "7.2.6. Index Merge Optimization")。 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语法”](# "13.1.2. ALTER TABLE Syntax")。为了看清一张表有什么索引，使用SHOW INDEX FROM _tbl_name_。・ key key列显示**MySQL**实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见[13.2.7节，“SELECT语法”](# "13.2.7. SELECT Syntax")。对于MyISAM和BDB表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，可以使用**myisamchk --analyze**。参见[13.5.2.1节，“ANALYZE TABLE语法”](# "13.5.2.1. ANALYZE TABLE Syntax")和[5.9.4节，“表维护和崩溃恢复”](# "5.9.4. Table Maintenance and Crash Recovery")。・ 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.5. Range Optimization")和[7.2.6节，“索引合并优化”](# "7.2.6. Index Merge Optimization")，不同的是前面表的所有列值已知并且认为是常量。这并不很快，但比执行没有索引的联接要快得多。 o Using filesort MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。参见[7.2.12节，“MySQL如何优化ORDER BY``”](# "7.2.12. How MySQL Optimizes 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节，“索引合并优化”](# "7.2.6. Index Merge Optimization")。 o Using index for group-by 类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。详情参见[7.2.13节，“MySQL如何优化GROUP BY``”](# "7.2.13. How MySQL Optimizes GROUP BY")。通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你**MySQL**必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。参见[7.5.2节，“调节服务器参数”](# "7.5.2. Tuning Server Parameters")。下列例子显示出一个多表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; 对于这个例子，假定：・被比较的列声明如下： <table border="1" cellpadding="0" id="table1"><tr><td> 表</td> <td width="113"> 列</td> <td width="105"> 列类型</td> </tr><tr><td> tt</td> <td width="113"> ActualPC</td> <td width="105"> CHAR(10)</td> </tr><tr><td> tt</td> <td width="113"> AssignedPC</td> <td width="105"> CHAR(10)</td> </tr><tr><td> tt</td> <td width="113"> ClientID</td> <td width="105"> CHAR(10)</td> </tr><tr><td> et</td> <td width="113"> EMPLOYID</td> <td width="105"> CHAR(15)</td> </tr><tr><td> do</td> <td width="113"> CUSTNMBR</td> <td width="105"> CHAR(15)</td> </tr></table> ・表有下面的索引： <table border="1" cellpadding="0" id="table2"><tr><td> 表</td> <td> 索引</td> </tr><tr><td> tt</td> <td> ActualPC</td> </tr><tr><td> tt</td> <td> AssignedPC</td> </tr><tr><td> tt</td> <td> ClientID</td> </tr><tr><td> et</td> <td> EMPLOYID(主键)</td> </tr><tr><td> do</td> <td> CUSTNMBR(主键)</td> </tr></table> ・ 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次搜索来更新这个索引并且写入行。注意，上述讨论并不意味着应用程序的性能将缓慢地以log_N_ 退化！当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以log_N_增加)。为了避免这个问题，随数据增加而增加键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制键高速缓冲区大小。参见[7.5.2节，“调节服务器参数”](# "7.5.2. Tuning Server Parameters")。 ### 7.2.3. SELECT查询的速度总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。参见[7.4.5节，“MySQL如何使用索引”](# "7.4.5. How MySQL Uses Indexes")和[7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）``”](# "7.2.1. EXPLAIN Syntax (Get Information About a 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 b>5 AND b=c AND a=5 ・去除常量条件(由于常量重叠需要)：・ (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6) ・ -> B=5 OR B=6 ・索引使用的常数表达式仅计算一次。 - 对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。 - 无效常数表达式的早期检测。**MySQL**快速检测某些SELECT语句是不可能的并且不返回行。 - 如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。 - 对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。 - 所有常数的表在查询中比其它表先读出。常数表为： - 空表或只有1行的表。 - 与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。下列的所有表用作常数表： mysql> SELECT * FROM t WHERE primary_key=1; mysql> SELECT * FROM t1,t2 WHERE t1.primary_key=1 AND t2.primary_key=t1.id; - 尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。 - 如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。 - 如果使用SQL_SMALL_RESULT，**MySQL**使用内存中的一个临时表。 - 每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。 - 在一些情况下，**MySQL**能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。 - 输出每个记录前，跳过不匹配HAVING子句的行。下面是一些快速查询的例子： SELECT COUNT(*) FROM tbl_name; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; SELECT MAX(key_part2) FROM tbl_name WHERE key_part1=constant; SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... LIMIT 10; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10; 下列查询仅使用索引树就可以解决(假设索引的列为数值型)： SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; SELECT COUNT(*) FROM tbl_name WHERE key_part1=val1 AND key_part2=val2; SELECT key_part2 FROM tbl_name GROUP BY key_part1; 下列查询使用索引按排序顺序检索行，不用另外的排序： SELECT ... FROM tbl_name ORDER BY key_part1,key_part2,... ; SELECT ... FROM tbl_name ORDER BY key_part1 DESC, key_part2 DESC, ... ; ### 7.2.5. 范围优化 [ 7.2.5.1. 单元素索引的范围访问方法](#)[ 7.2.5.2. 多元素索引的范围访问方法](#) range访问方法使用单一索引来搜索包含在一个或几个索引值距离内的表记录的子集。可以用于单部分或多元素索引。后面的章节将详细描述如何从WHERE子句提取区间。 #### 7.2.5.1. 单元素索引的范围访问方法对于单元素索引，可以用WHERE子句中的相应条件很方便地表示索引值区间，因此我们称为_范围条件_而不是“区间”。单元素索引范围条件的定义如下：・对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。・对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE '_pattern_'(其中 '_pattern_'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。・对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。前面描述的“常量值”系指：・查询字符串中的常量・同一联接中的const或system表中的列・无关联子查询的结果・完全从前面类型的子表达式组成的表达式下面是一些WHERE子句中有范围条件的查询的例子： SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10; SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20); SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo'; 请注意在常量传播阶段部分非常量值可以转换为常数。 MySQL尝试为每个可能的索引从WHERE子句提取范围条件。在提取过程中，不能用于构成范围条件的条件被放弃，产生重叠范围的条件组合到一起，并且产生空范围的条件被删除。例如，考虑下面的语句，其中key1是有索引的列，nonkey没有索引： SELECT * FROM t1 WHERE (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z'); key1的提取过程如下： 1. 用原始WHERE子句开始： 2. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR 3. (key1 < 'bar' AND nonkey = 4) OR 4. (key1 < 'uux' AND key1 > 'z') 5. 删除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。删除它们的正确途径是用TRUE替换它们，以便进行范围扫描时不会丢失匹配的记录。用TRUE替换它们后，可以得到： 6. (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR 7. (key1 < 'bar' AND TRUE) OR 8. (key1 < 'uux' AND key1 > 'z') 9. 取消总是为true或false的条件：・ (key1 LIKE 'abcde%' OR TRUE)总是true ・ (key1 < 'uux' AND key1 > 'z')总是false 用常量替换这些条件，我们得到： (key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE) 删除不必要的TRUE和FALSE常量，我们得到 (key1 < 'abc') OR (key1 < 'bar') 10.将重叠区间组合成一个产生用于范围扫描的最终条件： 11. (key1 < 'bar') 总的来说(如前面的例子所述)，用于范围扫描的条件比WHERE子句限制少。MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。范围条件提取算法可以处理嵌套的任意深度的AND/OR结构，并且其输出不依赖条件在WHERE子句中出现的顺序。 #### 7.2.5.2. 多元素索引的范围访问方法多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。例如，考虑定义为key1(_key_part1_, _key_part2_, _key_part3_)的多元素索引，以及下面的按关键字顺序所列的关键元组： key_part1 key_part2 key_part3 NULL 1 'abc' NULL 1 'xyz' NULL 2 'foo' 1 1 'abc' 1 1 'xyz' 1 2 'abc' 2 1 'aaa' 条件_key_part1_ = 1定义了下面的范围： (1，-inf，-inf) <= (key_part1，key_part2，key_part3) < (1，+inf，+inf) 范围包括前面数据集中的第4、5和6个元组，可以用于范围访问方法。通过对比，条件_key_part3_ = 'abc'不定义单一的区间，不能用于范围访问方法。下面更加详细地描述了范围条件如何用于多元素索引中。・对于HASH索引，可以使用包含相同值的每个区间。这说明区间只能由下面形式的条件产生：・ key_part1 cmp const1 ・ AND key_part2 cmp const2 ・ AND ... ・ AND key_partN cmp constN; 这里，_const1_，_const2_，...为常量，_cmp_是=、<=>或者IS NULL比较操作符之一，条件包括所有索引部分。(也就是说，有_N_个条件，每一个对应_N_-元素索引的每个部分）。关于常量的定义，参见[7.2.5.1节，“单元素索引的范围访问方法”](# "7.2.5.1. Range Access Method for Single-Part Indexes")。例如，下面为三元素HASH索引的范围条件： key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo' ・对于BTREE索引，区间可以对结合AND的条件有用，其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE '_pattern_' (其中'_pattern_'不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=，为两个区间)的记录的单一的关键元组。例如，对于条件：・ key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10 单一区间为： ('foo'，10，10) < (key_part1，key_part2，key_part3) < ('foo'，+inf，+inf) 创建的区间可以比原条件包含更多的记录。例如，前面的区间包括值('foo'，11，0)，不满足原条件。・如果包含区间内的一系列记录的条件结合使用OR，则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND，则形成包括一系列包含在区间交集内的记录的一个条件。例如，对于两部分索引的条件：・ (key_part1 = 1 AND key_part2 < 2) ・ OR (key_part1 > 5) 区间为： (1, -inf) < (_key_part1_, _key_part2_) < (1, 2) (5, -inf) < (_key_part1_, _key_part2_) 在该例子中，第1行的区间左侧的约束使用了一个关键元素，右侧约束使用了两个关键元素。第2行的区间只使用了一个关键元素。EXPLAIN输出的key_len列表示所使用关键字前缀的最大长度。在某些情况中，key_len可以表示使用的关键元素，但可能不是你所期望的。假定_key_part1_和_key_part2_可以为NULL。则key_len列显示下面条件的两个关键元素的长度： key_part1 >= 1 AND key_part2 < 2 但实际上，该条件可以变换为： key_part1 >= 1 AND key_part2 IS NOT NULL [ 7.2.5.1节，“单元素索引的范围访问方法”](# "7.2.5.1. Range Access Method for Single-Part Indexes")描述了如何进行优化以结合或删除单元素索引范围条件的区间。多元素索引范围条件的区间的步骤类似。 ### 7.2.6. 索引合并优化 [ 7.2.6.1. 索引合并交集访问算法](#)[ 7.2.6.2. 索引合并并集访问算法](#)[ 7.2.6.3. 索引合并排序并集访问算法](#) _索引合并_方法用于通过range扫描搜索行并将结果合成一个。合并会产生并集、交集或者正在进行的扫描的交集的并集。在EXPLAIN输出中，该方法表现为type列内的index_merge。在这种情况下，key列包含一列使用的索引，key_len包含这些索引的最长的关键元素。例如： SELECT * FROM _tbl_name_ WHERE _key_part1_ = 10 OR _key_part2_ = 20; SELECT * FROM _tbl_name_ WHERE (_key_part1_ = 10 OR _key_part2_ = 20) AND _non_key_part_=30; SELECT * FROM t1, t2 WHERE (t1._key1 IN (1,2) OR t1.key2_ LIKE '_value_%') AND t2._key1_=t1._some_col_; SELECT * FROM t1, t2 WHERE t1._key1_=1 AND (t2._key1_=t1._some_col_ OR t2._key2_=t1._some_col2_); 索引合并方法有几种访问算法 (参见EXPLAIN输出的Extra字段)：・交集・联合・排序并集后面几节更加详细地描述了这些方法。 **注释：**索引合并优化算法具有以下几个已知缺陷：・如果可以对某些关键字进行范围扫描，则不考虑索引合并。例如，下面的查询：・ SELECT * FROM t1 WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; 对于该查询，可以有两个方案： 1. 使用(goodkey1 < 10 OR goodkey2 < 20)条件进行索引合并扫描。 2. 使用badkey < 30条件进行范围扫描。然而，优化器只考虑第2个方案。如果这不是你想要的，你可以通过使用IGNORE INDEX或FORCE INDEX让优化器考虑index_merge。下面的查询使用索引合并执行： SELECT * FROM t1 FORCE INDEX(goodkey1,goodkey2) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; SELECT * FROM t1 IGNORE INDEX(badkey) WHERE (goodkey1 < 10 OR goodkey2 < 20) AND badkey < 30; ・如果查询有一个复杂的WHERE子句，有较深的AND/OR嵌套关系，MySQL不选择该优选方案，通过下面的识别法则尝试分布各条件：・ (x AND y) OR z = (x OR z) AND (y OR z) ・ (x OR y) AND z = (x AND z) OR (y AND z) index_merge访问方法的不同变量之间的选择和其它访问方法基于各适用选项的成本估计。 #### 7.2.6.1. 索引合并交集访问算法该访问算法可以用于当WHERE子句结合AND被转换为不同的关键字的几个范围条件，每个条件为下面之一：・以这种形式，即索引有确切的_N_部分(即包括了所有索引部分)：・ key_part1=const1 AND key_part2=const2 ... AND key_partN=constN ・任何InnoDB或BDB表的主键的范围条件。下面是一些例子： SELECT * FROM _ innodb_table_ WHERE _primary_key_ < 10 AND _key_col1_=20; SELECT * FROM _tbl_name_ WHERE (_key1_part1_=1 AND _key1_part2_=2) AND _key2_=2; 索引合并交集算法同时对所有使用的索引进行扫描，并产生从合并的索引扫描接收的行序列的交集。如果使用的索引包括查询中使用的所有列，所有表记录均不搜索，并且在这种情况下EXPLAIN的输出包含Extra字段中的Using index。下面是一个此类查询的例子： SELECT COUNT(*) FROM t1 WHERE key1=1 AND key2=1; 如果使用的索引未包括查询中使用的所有列，只有满足所有使用的关键字的范围条件才搜索所有记录。如果某个合并条件是InnoDB或BDB表的主键的一个条件，不用于记录查询，但用于过滤使用其它条件搜索的记录。 #### 7.2.6.2. 索引合并并集访问算法该算法的适用标准类似于索引合并方法交集算法的标准。算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件的时候，每个条件为下面之一：・以这种形式，即索引有确切的_N_部分(即包括了所有索引部分)：・ key_part1=const1 AND key_part2=const2 ... AND key_partN=constN ・任何InnoDB或BDB表的主键的范围条件。・索引合并方法交集算法适用的一个条件。下面是一些例子： SELECT * FROM t1 WHERE key1=1 OR key2=2 OR key3=3; SELECT * FROM innodb_table WHERE (key1=1 AND key2=2) OR (key3='foo' AND key4='bar') AND key5=5; #### 7.2.6.3. 索引合并排序并集访问算法该访问算法可以用于当WHERE子句结合OR被转换为不同的关键字的几个范围条件，但索引合并方法联合算法并不适用的时候。下面是一些例子： SELECT * FROM tbl_name WHERE key_col1 < 10 OR key_col2 < 20; SELECT * FROM tbl_name WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col=30; 排序联合算法和联合算法的区别是排序联合算法必须先索取所有记录的行ID，然后在返回记录前对它们进行排序。 ### 7.2.7. MySQL如何优化IS NULL`` MySQL可以对可以结合_col_name_=_constant_value_使用的_col_name_IS NULL进行相同的优化。例如，MySQL可以使用索引和范围用IS NULL搜索NULL。 SELECT * FROM tbl_name WHERE key_col IS NULL; SELECT * FROM tbl_name WHERE key_col <=> NULL; SELECT * FROM tbl_name WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL; 如果WHERE子句包括声明为NOT NULL的列的_col_name_IS NULL条件，表达式则优化。当列会产生NULL时，不会进行优化；例如，如果来自LEFT JOIN右侧的表。 MySQL也可以优化组合_col_name_ = _expr_ AND _col_name_ IS NULL，这是解决子查询的一种常用形式。当使用优化时EXPLAIN显示ref_or_null。该优化可以为任何关键元素处理IS NULL。下面是一些优化的查询例子，假定表t2的列a和b有一个索引： SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL; SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL; SELECT * FROM t1, t2 WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b; SELECT * FROM t1, t2 WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL); SELECT * FROM t1, t2 WHERE (t1.a=t2.a AND t2.a IS NULL AND ...) OR (t1.a=t2.a AND t2.a IS NULL AND ...); ref_or_null首先读取参考关键字，然后单独搜索NULL关键字的行。请注意该优化只可以处理一个IS NULL。在后面的查询中，MySQL只对表达式(t1.a=t2.a AND t2.a IS NULL)使用关键字查询，不能使用b的关键元素： SELECT * FROM t1, t2 WHERE (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL); ### 7.2.8. MySQL如何优化DISTINCT`` 在许多情况下结合ORDER BY的DISTINCT需要一个临时表。请注意因为DISTINCT可能使用GROUP BY，必须清楚MySQL如何使用所选定列的一部分的ORDER BY或HAVING子句中的列。参见[12.10.3节，“具有隐含字段的GROUP BY”](# "12.10.3. GROUP BY with Hidden Fields")。在大多数情况下，DISTINCT子句可以视为GROUP BY的特殊情况。例如，下面的两个查询是等效的： SELECT DISTINCT c1, c2, c3 FROM t1 WHERE c1 > const; SELECT c1, c2, c3 FROM t1 WHERE c1 > const GROUP BY c1, c2, c3; 由于这个等效性，适用于GROUP BY查询的优化也适用于有DISTINCT子句的查询。这样，关于DISTINCT查询的优化的更详细的情况，参见[7.2.13节，“MySQL如何优化GROUP BY``”](# "7.2.13. How MySQL Optimizes GROUP BY")。结合LIMIT _row_count_和DISTINCT后，MySQL发现唯一的_row_count_行后立即停止。如果不使用查询中命名的所有表的列，MySQL发现第1个匹配后立即停止扫描未使用的表。在下面的情况中，假定t1在t2之前使用(可以用EXPLAIN检查)，发现t2中的第1行后，MySQL不再(为t1中的任何行)读t2： SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a; ### 7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN 在MySQL中，_A_ LEFT JOIN _B_ join_condition执行过程如下：・根据表_A_和_A_依赖的所有表设置表B。・根据LEFT JOIN条件中使用的所有表(除了_B_)设置表_A_。・ LEFT JOIN条件用于确定如何从表_B_搜索行。(换句话说，不使用WHERE子句中的任何条件）。・可以对所有标准联接进行优化，只是只有从它所依赖的所有表读取的表例外。如果出现循环依赖关系，MySQL提示出现一个错误。・进行所有标准WHERE优化。・如果_A_中有一行匹配WHERE子句，但_B_中没有一行匹配ON条件，则生成另一个_B_行，其中所有列设置为NULL。・如果使用LEFT JOIN找出在某些表中不存在的行，并且进行了下面的测试：WHERE部分的_col_name_ IS NULL，其中_col_name_是一个声明为 NOT NULL的列，MySQL找到匹配LEFT JOIN条件的一个行后停止(为具体的关键字组合)搜索其它行。 RIGHT JOIN的执行类似LEFT JOIN，只是表的角色反过来。联接优化器计算表应联接的顺序。LEFT JOIN和STRAIGHT_JOIN强制的表读顺序可以帮助联接优化器更快地工作，因为检查的表交换更少。请注意这说明如果执行下面类型的查询，MySQL进行全扫描b，因为LEFT JOIN强制它在d之前读取： SELECT * FROM a,b LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key; 在这种情况下修复时用a的相反顺序，b列于FROM子句中： SELECT * FROM b,a LEFT JOIN c ON (c.key=a.key) LEFT JOIN d ON (d.key=a.key) WHERE b.key=d.key; MySQL可以进行下面的LEFT JOIN优化：如果对于产生的NULL行，WHERE条件总为假，LEFT JOIN变为普通联接。例如，在下面的查询中如果t2.column1为NULL，WHERE子句将为false： SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5; 因此，可以安全地将查询转换为普通联接： SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1; 这样可以更快，因为如果可以使查询更佳，MySQL可以在表t1之前使用表t2。为了强制使用表顺序，使用STRAIGHT_JOIN。 ### 7.2.10. MySQL如何优化嵌套Join 表示联接的语法允许嵌套联接。下面的讨论引用了[13.2.7.1节，“JOIN语法”](# "13.2.7.1. JOIN Syntax")中描述的联接语法。同SQL标准比较，_table_factor_语法已经扩展了。后者只接受_table_reference_，而不是括号内所列的。 _table_reference_项列表内的每个逗号等价于内部联接，这是一个保留扩展名。例如： SELECT * FROM t1 LEFT JOIN (t2, t3, t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 等价于： SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4) ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c) 在MySQL中，CROSS JOIN语法上等价于INNER JOIN (它们可以彼此代替。在标准SQL中，它们不等价。INNER JOIN结合ON子句使用；CROSS JOIN用于其它地方。总的来说，在只包含内部联接操作的联接表达式中可以忽略括号。删除括号并将操作组合到左侧后，联接表达式： t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a 转换为表达式： (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL 但是这两个表达式不等效。要说明这点，假定表t1、t2和t3有下面的状态：・表t1包含行{1}、{2} ・表t2包含行{1,101} ・表t3包含行{101} 在这种情况下，第1个表达式返回包括行{1,1,101,101}、{2,NULL,NULL,NULL}的结果，第2个表达式返回行{1,1,101,101}、{2,NULL,NULL,101}： mysql> SELECT * -> FROM t1 -> LEFT JOIN -> (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) -> ON t1.a=t2.a; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql> SELECT * -> FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) -> LEFT JOIN t3 -> ON t2.b=t3.b OR t2.b IS NULL; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+ 在下面的例子中，外面的联接操作结合内部联接操作使用： t1 LEFT JOIN (t2，t3) ON t1.a=t2.a 该表达式不能转换为下面的表达式： t1 LEFT JOIN t2 ON t1.a=t2.a，t3. 对于给定的表状态，第1个表达式返回行{1,1,101,101}、{2,NULL,NULL,NULL}，第2个表达式返回行{1,1,101,101}、{2,NULL,NULL,101}： mysql> SELECT * -> FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | NULL | +------+------+------+------+ mysql> SELECT * -> FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3; +------+------+------+------+ | a | a | b | b | +------+------+------+------+ | 1 | 1 | 101 | 101 | | 2 | NULL | NULL | 101 | +------+------+------+------+ 因此，如果我们忽略联接表达式中的括号连同外面的联接操作符，我们会改变原表达式的结果。更确切地说，我们不能忽视左外联接操作的右操作数和右联接操作的左操作数中的括号。换句话说，我们不能忽视外联接操作中的内表达式中的括号。可以忽视其它操作数中的括号(外部表的操作数)。对于任何表t1、t2、t3和属性t2.b和t3.b的任何条件P，下面的表达式： (t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b) 等价于表达式 t1，t2 LEFT JOIN t3 ON P(t2.b,t3.b) 如果联接表达式(_join_table_)中的联接操作的执行顺序不是从左到右，我们则应讨论嵌套的联接。这样，下面的查询： SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a WHERE t1.a > 1 SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1 联接表： t2 LEFT JOIN t3 ON t2.b=t3.b t2, t3 认为是嵌套的。第1个查询结合左联接操作则形成嵌套的联接，而在第二个查询中结合内联接操作形成嵌套