第7章：优化

第7章：优化

目录

7.1. 优化概述

7.1.1. MySQL设计局限与折衷 7.1.2. 为可移植性设计应用程序 7.1.3. 我们已将MySQL用在何处？ 7.1.4. MySQL基准套件 7.1.5. 使用自己的基准

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. 其它优化技巧 7.3. 锁定事宜

7.3.1. 锁定方法 7.3.2. 表锁定事宜 7.4. 优化数据库结构

7.4.1. 设计选择

7.4.2. 使你的数据尽可能小 7.4.3. 列索引 7.4.4. 多列索引

7.4.5. MySQL如何使用索引 7.4.6. MyISAM键高速缓冲 7.4.7. MyISAM索引统计集合 7.4.8. MySQL如何计算打开的表 7.4.9. MySQL如何打开和关闭表

7.4.10. 在同一个数据库中创建多个表的缺陷 7.5. 优化MySQL服务器

7.5.1. 系统因素和启动参数的调节 7.5.2. 调节服务器参数

7.5.3. 控制查询优化器的性能

7.5.4. 编译和链接怎样影响MySQL的速度 7.5.5. MySQL如何使用内存 7.5.6. MySQL如何使用DNS 7.6. 磁盘事宜

7.6.1. 使用符号链接 优化是一个复杂的任务，因为最终要求了解整个待优化的系统。尽管可以进行局部优化而不需要了解系统或应用程序，为了优化得更好，你必须知道更多的信息。 本章解释并给出不同的优化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章：存储引擎和表类型。

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本身即可以解决部分移植性问题，因为它提供与数据库无关的的存取方法。

关于crash-me结果，访问

http://dev.mysql.com/tech-resources/crash-me.php。到

http://dev.mysql.com/tech-resources/benchmarks/看这个基准的结果。

如果你为数据库的独立性而努力，需要很好地了解每个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源码分发提供。可以从

http://dev.mysql.com/downloads/下载分发，或者使用当前的开发源码树(参见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列可以多大

可以从http://dev.mysql.com/tech-resources/crash-me.php发现许多不同数据库服务器的crash-me的结果。关于基准测试结果的详细信息，访问http://dev.mysql.com/tech-resources/benchmarks/。

7.1.5. 使用自己的基准

一定要测试应用程序和数据库，以发现瓶颈在哪儿。通过修正它(或通过用一个“哑模块”代替瓶颈)，可以很容易地确定下一个瓶颈。即使你的应用程序的整体性能目前可以接受，至少应该对每个瓶颈做一个计划，如果某天确实需要更好的性能，应知道如何解决它。

关于一些可移植的基准程序的例子，参见MySQL基准套件。请参见7.1.4节，“MySQL基准套件”。可以利用这个套件的任何程序并且根据你的需要修改它。通过这样做，可以尝试不同的问题的解决方案并测试哪一个是最好的解决方案。 另一个免费基准套件是开放源码数据库基准套件，参见http://osdb.sourceforge.net/。

在系统负载繁重时出现一些问题是很普遍的，并且很多客户已经与我们联系了，他们在生产系统中有一个(测试)系统并且有负载问题。大多数情况下，性能问题经证明是与基本数据库设计有关的问题(例如，表扫描在高负载时表现不好)或操作系统或库问题。如果系统已经不在生产系统中，它们大多数将很容易修正。 为了避免这样的问题，应该把工作重点放在在可能最坏的负载下测试你的整个应用程序。你可以使用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语法”。

还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个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

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计算并且也尽快跳过记录。

所有常数的表在查询中比其它表先读出。常数表为： o 空表或只有1行的表。 o 与在一个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节，“单元素索引的范围访问方法”。 例如，下面为三元素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.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”。

在大多数情况下，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”。

结合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语法”中描述的联接语法。

同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个查询结合左联接操作则形成嵌套的联接，而在第二个查询中结合内联接操作形成嵌套联接。 在第1个查询中，括号可以忽略：联接表达式的语法结构与联接操作的执行顺序相同。但对于第2个查询，括号不能省略，尽管如果没有括号，这里的联接表达式解释不清楚。(在外部扩展语法中，需要第2个查询的(t2，t3)的括号，尽管从理论上对查询分析时不需要括号：这些查询的语法结构将仍然不清楚，因为LEFT JOIN和ON将充当表达式(t2,t3)的左、右界定符的角色)。 前面的例子说明了这些点：

· 对于只包含内联接(而非外联接)的联接表达式，可以删除括号。你可以移除括号并从左到右评估(或实际上，你可以按任何顺序评估表)。 · 总的来说，对外联接却不是这样。去除括号可能会更改结果。 · 总的来说，对外联接和内联接的结合，也不是这样。去除括号可能会更改结果。

含嵌套外联接的查询按含内联接的查询的相同的管道方式执行。更确切地说，利用了嵌套环联接算法。让我们回忆嵌套环联接执行查询时采用什么算法。 假定我们有一个如下形式的表T1、T2、T3的联接查询：

SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2) INNER JOIN T3 ON P2(T2,T3) WHERE P(T1,T2,T3).

这里，P1(T1,T2)和P2(T3,T3)是一些联接条件(表达式)，其中P(t1,t2,t3)是表T1、T2、T3的列的一个条件。

嵌套环联接算法将按下面的方式执行该查询：

FOR each row t1 in T1 {

FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) {

t:=t1||t2||t3; OUTPUT t; } } } }

符号t1||t2||t3表示“连接行t1、t2和t3的列组成的行”。在下面的一些例子中，出现行名的NULL表示NULL用于行的每个列。例如，t1||t2||NULL表示“连接行t1和t2的列以及t3的每个列的NULL组成的行”。 现在让我们考虑带嵌套的外联接的查询：

SELECT * FROM T1 LEFT JOIN

(T2 LEFT JOIN T3 ON P2(T2,T3)) ON P1(T1,T2) WHERE P(T1,T2,T3)。

对于该查询我们修改嵌套环模式可以得到：

FOR each row t1 in T1 { BOOL f1:=FALSE;

FOR each row t2 in T2 such that P1(t1,t2) { BOOL f2:=FALSE;

FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) {

t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; }

IF (!f2) {

IF P(t1,t2,NULL) {

t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } }

IF (!f1) {

IF P(t1,NULL,NULL) {

t:=t1||NULL||NULL; OUTPUT t; } } }

总的来说，对于外联接操作中的第一个内表的嵌套环，引入了一个标志，在环之前关闭并且在环之后打开。如果对于外部表的当前行，如果匹配表示内操作数的表，则标志打开。如果在循环结尾处标志仍然关闭，则对于外部表的当前行，没有发现匹配。在这种情况下，对于内表的列，应使用NULL值补充行。结果行被传递到输出进行最终检查或传递到下一个嵌套环，但只能在行满足所有嵌入式外联接的联接条件时。

在我们的例子中，嵌入了下面表达式表示的外联接表：

(T2 LEFT JOIN T3 ON P2(T2,T3))

请注意对于有内联接的查询，优化器可以选择不同的嵌套环顺序，例如：

FOR each row t3 in T3 {

FOR each row t2 in T2 such that P2(t2,t3) { FOR each row t1 in T1 such that P1(t1,t2) { IF P(t1,t2,t3) {

t:=t1||t2||t3; OUTPUT t;

} } } }

对于有外联接的查询，优化器可以只选择这样的顺序：外表的环优先于内表的环。这样，对于有外联接的查询，只可能有一种嵌套顺序。在下面的查询中，优化器将评估两个不同的嵌套：

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3) WHERE P(T1,T2,T3)

嵌套为：

FOR each row t1 in T1 { BOOL f1:=FALSE;

FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t1,t3) { IF P(t1,t2,t3) {

t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } }

IF (!f1) {

IF P(t1,NULL,NULL) {

t:=t1||NULL||NULL; OUTPUT t; } } }

和

FOR each row t1 in T1 { BOOL f1:=FALSE;

FOR each row t3 in T3 such that P2(t1,t3) { FOR each row t2 in T2 such that P1(t1,t2) { IF P(t1,t2,t3) {

t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } }

IF (!f1) {

IF P(t1,NULL,NULL) {

t:=t1||NULL||NULL; OUTPUT t; } } }

在两个嵌套中，必须在外环中处理T1，因为它用于外联接中。T2和T3用于内联接中，因此联接必须在内环中处理。但是，因为该联接是一个内联接，T2和T3可以以任何顺序处理。

当讨论内联接嵌套环的算法时，我们忽略了部分详情，可能对查询执行的性能的影响会很大。我们没有提及所谓的“下推”条件。假定可以用连接公式表示我们的WHERE条件P(T1,T2,T3)：

P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3)。

在这种情况下，MySQL实际使用了下面的嵌套环方案来执行带内联接得到查询：

FOR each row t1 in T1 such that C1(t1) {

FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) {

t:=t1||t2||t3; OUTPUT t; } } } }

你会看见每个连接 C1(T1)，C2(T2)，C3(T3)被从最内部的环内推出到可以对它进行评估的最外的环中。如果C1(T1)是一个限制性很强的条件，下推条件可以大大降低从表T1传递到内环的行数。结果是查询大大加速。

对于有外联接的查询，只有查出外表的当前的行可以匹配内表后，才可以检查WHERE条件。这样，对内嵌套环下推的条件不能直接用于带外联接的查询。这里我们必须引入有条件下推前提，由遇到匹配后打开的标志保护。 对于带下面的外联接的例子

P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)

使用受保护的下推条件的嵌套环方案看起来应为：

FOR each row t1 in T1 such that C1(t1) { BOOL f1:=FALSE; FOR each row t2 in T2

such that P1(t1,t2) AND (f1?C2(t2):TRUE) { BOOL f2:=FALSE; FOR each row t3 in T3

such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; }

IF (!f2) {

IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE;

} }

IF (!f1 && P(t1,NULL,NULL)) { t:=t1||NULL||NULL; OUTPUT t; } }

总的来说，可以从联接条件（例如P1(T1,T2)和P(T2,T3)）提取下推前提。在这种情况下，下推前提也受一个标志保护，防止检查由相应外联接操作所产生的NULL-补充的行的断言。

请注意如果从判断式的WHERE条件推导出，根据从一个内表到相同嵌套联接的另一个表的关键字进行的访问被禁止。(在这种情况下，我们可以使用有条件关键字访问，但是该技术还未用于MySQL 5.1中）。

7.2.11. MySQL如何简化外部联合

在许多情况下，一个查询的FROM子句的表的表达式可以简化。 在分析阶段，带右外联接操作的查询被转换为只包含左联接操作的等效查询。总的来说，根据以下原则进行转换：

(T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) = (T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)

所有T1 INNER JOIN T2 ON P(T1,T2)形式的内联接表达式被替换为T1,T2、P(T1,T2)，并根据WHERE条件(或嵌入连接的联接条件，如果有)联接为一个连接。 当优化器为用外联接操作的联接查询评估方案时，它只考虑在访问内表之前访问外表的操作的方案。优化器选项受到限制，因为只有这样的方案允许我们用嵌套环机制执行带外联接操作的查询。 假定我们有一个下列形式的查询： SELECT * T1 LEFT JOIN T2 ON P1(T1,T2) WHERE P(T1,T2) AND R(T2)

R(T2)大大减少了表T2中匹配的行数。如果我们这样执行查询，优化器将不会有

其它选择，只能在访问表T2之前访问表T1，从而导致执行方案非常低。

幸运的是，如果WHERE条件拒绝null，MySQL可以将此类查询转换为没有外联接操作的查询。如果为该操作构建的NULL补充的行评估为FALSE或UNKNOWN，则该条件称为对于某个外联接操作拒绝null。 因此，对于该外联接：

T1 LEFT JOIN T2 ON T1.A=T2.A

类似下面的条件为拒绝null：

T2.B IS NOT NULL, T2.B > 3, T2.C <= T1.C, T2.B < 2 OR T2.C > 1

类似下面的条件不为拒绝null：

T2.B IS NULL,

T1.B < 3 OR T2.B IS NOT NULL, T1.B < 3 OR T2.B > 3

检查一个外联接操作的条件是否拒绝null的总原则很简单。以下情况下为拒绝null的条件：

· 形式为A IS NOT NULL，其中A是任何内表的一个属性

· 包含内表引用的判断式，当某个参量为NULL时评估为UNKNOWN ??????????包含用于连接的拒绝null的条件的联合 · 拒绝null的条件的逻辑和

一个条件可以对于一个查询中的一个外联接操作为拒绝null的而对于另一个不为拒绝null的。在下面的查询中：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T1.B WHERE T3.C > 0

WHERE条件对于第2个外联接操作为拒绝null的但对于第1个不为拒绝null的。

如果WHERE条件对于一个查询中的一个外联接操作为拒绝null的，外联接操作被一个内联接操作代替。

例如，前面的查询被下面的查询代替： SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T1.B WHERE T3.C > 0

对于原来的查询，优化器将评估只与一个访问顺序T1、T2、T3兼容的方案。在替换的查询中，还考虑了访问顺序T3、T1、T2。

一个外联接操作的转化可以触发另一个的转化。这样，查询： SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0 将首先转换为查询：

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A INNER JOIN T3 ON T3.B=T2.B

WHERE T3.C > 0 该查询等效于查询：

SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B

现在剩余的外联接操作也可以被一个内联接替换，因为条件T3.B=T2.B为拒绝null的，我们可以得到一个根本没有外联接的查询： SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3 WHERE T3.C > 0 AND T3.B=T2.B

有时我们可以成功替换嵌入的外联接操作，但不能转换嵌入的外联接。下面的查询：

SELECT * FROM T1 LEFT JOIN

(T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0 被转换为：

SELECT * FROM T1 LEFT JOIN

(T2 INNER JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0，

只能重新写为仍然包含嵌入式外联接操作的形式： SELECT * FROM T1 LEFT JOIN (T2,T3)

ON (T2.A=T1.A AND T3.B=T2.B) WHERE T3.C > 0。

如果试图转换一个查询中的嵌入式外联接操作，我们必须考虑嵌入式外联接的联接条件和WHERE条件。在下面的查询中：

SELECT * FROM T1 LEFT JOIN

(T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A AND T3.C=T1.C WHERE T3.D > 0 OR T1.D > 0

WHERE条件对于嵌入式外联接不为拒绝null的，但嵌入式外联接T2.A=T1.A AND T3.C=T1.C的联接条件为拒绝null的。因此该查询可以转换为： SELECT * FROM T1 LEFT JOIN (T2, T3)

ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B WHERE T3.D > 0 OR T1.D > 0

7.2.12. MySQL如何优化ORDER BY

在某些情况中，MySQL可以使用一个索引来满足ORDER BY子句，而不需要额外的排序。

即使ORDER BY不确切匹配索引，只要WHERE子句中的所有未使用的索引部分和所有额外的ORDER BY 列为常数，就可以使用索引。下面的查询使用索引来解决ORDER BY部分： SELECT * FROM t1

ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1

WHERE key_part1=constant ORDER BY key_part2;

SELECT * FROM t1

ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1 WHERE key_part1=1

ORDER BY key_part1 DESC, key_part2 DESC;

在某些情况下，MySQL不能使用索引来解决ORDER BY，尽管它仍然使用索引来找到匹配WHERE子句的行。这些情况包括：

· 对不同的关键字使用ORDER BY： ?????????????????SELECT * FROM t1 ORDER BY key1, key2； · 对关键字的非连续元素使用ORDER BY： ?????????????????SELECT * FROM t1 WHERE key2=constant ORDER BY

key_part2；

· 混合ASC和DESC： ?????????????????SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC；

· 用于查询行的关键字与ORDER BY中所使用的不相同： ?????????????????SELECT * FROM t1 WHERE key2=constant ORDER BY key1； · 你正联接许多表，并且ORDER BY中的列并不是全部来自第1个用于搜索行的非常量表。(这是EXPLAIN输出中的没有const联接类型的第1个表）。

· 有不同的ORDER BY和GROUP BY表达式。

· 使用的表索引的类型不能按顺序保存行。例如，对于HEAP表的HASH索引情况即如此。

通过EXPLAIN SELECT ...ORDER BY，可以检查MySQL是否可以使用索引来解决查询。如果Extra列内有Using filesort，则不能解决查询。参见7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

文件排序优化不仅用于记录排序关键字和行的位置，并且还记录查询需要的列。这样可以避免两次读取行。文件排序算法的工作象这样： 1. 读行匹配WHERE子句的行，如前面所示。

2. 对于每个行，记录构成排序关键字和行位置的一系列值，并且记录查询需要的列。

3. 根据排序关键字排序元组

4. 按排序的顺序检索行，但直接从排序的元组读取需要的列，而不是再一次访问表。

该算法比以前版本的Mysql有很大的改进。

为了避免速度变慢，该优化只用于排序元组中的extra列的总大小不超过

max_length_for_sort_data系统变量值的时候。(将该变量设置得太高的的迹象是将看到硬盘活动太频繁而CPU活动较低）。

如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试下面的策略： · 增加sort_buffer_size变量的大小。

· 增加read_rnd_buffer_size变量的大小。

· 更改tmpdir指向具有大量空闲空间的专用文件系统。该选项接受几个使用round-robin(循环)模式的路径。在Unix中路径应用冒号(‘:’)

区间开，在Windows、NetWare和OS/2中用分号(‘；’)。可以使用该特性将负载均分到几个目录中。注释：路径应为位于不同物理硬盘上的文件系统的目录，而不是同一硬盘的不同的分区。

默认情况下，MySQL排序所有GROUP BY col1，col2，...查询的方法如同在查询中指定ORDER BY col1，col2，...。如果显式包括一个包含相同的列的ORDER BY子句，MySQL可以毫不减速地对它进行优化，尽管仍然进行排序。如果查询包括GROUP BY但你想要避免排序结果的消耗，你可以指定ORDER BY NULL禁止排序。例如： INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

7.2.13. MySQL如何优化GROUP BY

7.2.13.1. 松散索引扫描 7.2.13.2. 紧凑索引扫描

满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中，MySQL能够做得更好，通过索引访问而不用创建临时表。 为GROUP BY使用索引的最重要的前提条件是 所有GROUP BY列引用同一索引的属性，并且索引按顺序保存其关键字(例如，这是B-树索引，而不是HASH索引)。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的累积函数。

有两种方法通过索引访问执行GROUP BY查询，如下面的章节所描述。在第1个方法中，组合操作结合所有范围判断式使用(如果有)。第2个方法首先执行范围扫描，然后组合结果元组。 7.2.13.1. 松散索引扫描

使用索引时最有效的途径是直接搜索组域。通过该访问方法，MySQL使用某些关键字排序的索引类型(例如，B-树)的属性。该属性允许使用 索引中的查找组而不需要考虑满足所有WHERE条件的索引中的所有关键字。既然该访问方法只考虑索引中的关键字的一小部分，它被称为松散索引扫描。如果没有WHERE子句， 松散索引扫描读取的关键字数量与组数量一样多，可以比所有关键字数小得多。如果WHERE子句包含范围判断式(关于range联接类型的讨论参见7.2.1节，

“EXPLAIN语法（获取关于SELECT的信息）”)， 松散索引扫描查找满足范围条件的每个组的第1个关键字，并且再次读取尽可能最少数量的关键字。在下面的条件下是可以的：

· 查询针对一个单表。

· GROUP BY包括索引的第1个连续部分(如果对于GROUP BY，查询有一个DISTINCT子句，则所有显式属性指向索引开头)。

· 只使用累积函数(如果有)MIN()和MAX()，并且它们均指向相同的列。

· 索引的任何其它部分（除了那些来自查询中引用的GROUP BY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX() 函数的参数例外。

此类查询的EXPLAIN输出显示Extra列的Using indexforgroup-by。

下面的查询提供该类的几个例子，假定表t1(c1,c2,c3,c4)有一个索引idx(c1，c2，c3)：

SELECT c1, c2 FROM t1 GROUP BY c1, c2; SELECT DISTINCT c1, c2 FROM t1; SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2; SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2; SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2； 由于上述原因，不能用该快速选择方法执行下面的查询： 1. 除了MIN()或MAX()，还有其它累积函数，例如：

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

2. GROUP BY子句中的域不引用索引开头，如下所示：

SELECT c1,c2 FROM t1 GROUP BY c2, c3;

3. 查询引用了GROUP BY部分后面的关键字的一部分，并且没有等于常

量的等式，例如：

SELECT c1,c3 FROM t1 GROUP BY c1, c2；

7.2.13.2. 紧凑索引扫描

紧凑式索引扫描可以为索引扫描或一个范围索引扫描，取决于查询条件。 如果不满足松散索引扫描条件，GROUP BY查询仍然可以不用创建临时表。如果WHERE子句中有范围条件，该方法只读取满足这些条件的关键字。否则，进行索引扫描。该方法读取由WHERE子句定义的每个范围的所有关键字，或没有范围条件式扫描整个索引，我们将它定义为紧凑式索引扫描。请注意对于紧凑式索引扫描，只有找到了满足范围条件的所有关键字后才进行组合操作。

要想让该方法工作，对于引用GROUP BY关键字元素的前面、中间关键字元素的查询中的所有列，有一个常量等式条件即足够了。等式条件中的常量填充了搜索关键字中的“差距”，可以形成完整的索引前缀。这些索引前缀可以用于索引查找。如果需要排序GROUP BY结果，并且能够形成索引前缀的搜索关键字，MySQL

还可以避免额外的排序操作，因为使用有顺序的索引的前缀进行搜索已经按顺序检索到了所有关键字。

上述的第一种方法不适合下面的查询，但第2种索引访问方法可以工作(假定我们已经提及了表t1的索引idx)：

· GROUP BY中有一个差距，但已经由条件c2 = 'a'覆盖。

SELECT c1，c2，c3 FROM t1 WHERE c2 = 'a' GROUP BY c1，c3;

· GROUP BY不以关键字的第1个元素开始，但是有一个条件提供该元素的常量：

SELECT c1，c2，c3 FROM t1 WHERE c1 = 'a' GROUP BY c2，c3;

7.2.14. MySQL如何优化LIMIT

在一些情况中，当你使用LIMIT row_count而不使用HAVING时，MySQL将以不同方式处理查询。

· 如果你用LIMIT只选择一些行，当MySQL选择做完整的表扫描时，它将在一些情况下使用索引。

· 如果你使用LIMIT row_count与ORDER BY，MySQL一旦找到了排序结果的第一个row_count行，将结束排序而不是排序整个表。如果使用索引，将很快。如果必须进行文件排序（filesort），必须选择所有匹配查询没有LIMIT子句的行，并且在确定已经找到第1个row_count行前，必须对它们的大部分进行排序。在任何一种情况下，一旦找到了行，则不需要再排序结果的其它部分，并且MySQL不再进行排序。

· 当结合LIMIT row_count和DISTINCT时，MySQL一旦找到row_count个唯一的行，它将停止。

· 在一些情况下，GROUP BY能通过顺序读取键(或在键上做排序)来解决，然后计算摘要直到关键字的值改变。在这种情况下，LIMIT row_count将不计算任何不必要的GROUP BY值。 · 只要MySQL已经发送了需要的行数到客户，它将放弃查询，除非你正使用SQL_CALC_FOUND_ROWS。

· LIMIT 0将总是快速返回一个空集合。这对检查查询的有效性是有用的。当使用MySQL API时，它也可以用来得到结果列的列类型。(该技巧在MySQL Monitor中不工作，只显示Empty set；应使用SHOW COLUMNS或DESCRIBE）。

· 当服务器使用临时表来进行查询时，使用LIMIT row_count子句来计算需要多少空间。

7.2.15. 如何避免表扫描

EXPLAIN的输出显示了当

MySQL使用表扫描来解决查询时使用的所有类型列。这

通常在如下条件下发生：

· 表很小，扫描表比查找关键字速度快。这对于少于10行并且行较短的表比较普遍。

· 在ON或WHERE子句中没有适用的索引列的约束。

· 正用常量值比较索引列，并且MySQL已经计算到(基于索引树)常数覆盖了表的很大部分并且表扫描将会比较快。参见7.2.4节，“MySQL怎样优化WHERE子句”。

· 你正通过另一个列使用一个低的集的势的关键字(许多行匹配关键字)。在这种情况下，MySQL假设通过使用关键字它可能会进行许多关键字查找，表扫描将会更快。

对于小表，表扫描通常合适。对于大表，尝试下面的技巧以避免优化器错选了表扫描：

· 使用ANALYZE TABLE tbl_name为扫描的表更新关键字分布。参见13.5.2.1节，“ANALYZE TABLE语法”。

· 对扫描的表使用FORCE INDEX告知MySQL，相对于使用给定的索引表扫描将非常耗时。参见13.2.7节，“SELECT语法”。 ?????????????????SELECT * FROM t1, t2 FORCE INDEX (index_for_column)

????????????????? WHERE t1.col_name=t2.col_name；

· 用--max-seeks-for-key=1000选项启动mysqld或使用SET

max_seeks_for_key=1000告知优化器假设关键字扫描不会超过1,000次关键字搜索。参见5.3.3节，“服务器系统变量”。

7.2.16. INSERT语句的速度

插入一个记录需要的时间由下列因素组成，其中的数字表示大约比例：

? ? ? ? ? ?

连接：(3)

发送查询给服务器：(2) 分析查询：(2)

插入记录：（1x记录大小） 插入索引：（1x索引） 关闭：(1)

这不考虑打开表的初始开销，每个并发运行的查询打开。 表的大小以logN (B树)的速度减慢索引的插入。 加快插入的一些方法：

· 如果同时从同一个客户端插入很多行，使用含多个VALUE的INSERT语句同时插入几行。这比使用单行INSERT语句快(在某些情况下快几倍)。如果你正向一个非空表添加数据，可以调节

bulk_insert_buffer_size变量，使数据插入更快。参见5.3.3节，“服务器系统变量”。

· 如果你从不同的客户端插入很多行，能通过INSERT DELAYED语句加快速度。参见13.2.4节，“INSERT语法”。