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

如果你为数据库的独立性而努力，需要很好地了解每个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列可以多大

关于一些可移植的基准程序的例子，参见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

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<b AND b=c) AND a=5

·                -> 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节，“单元素索引的范围访问方法”。

例如，下面为三元素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语法”。

·         用MyISAM，如果在表中没有删除的行，能在SELECT语句正在运行的同时插入行。

·         当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多INSERT语句快20倍。参见13.2.5节，“LOAD DATA INFILE语法”。

·         当表有很多索引时，有可能要多做些工作使得LOAD DATA INFILE更快些。使用下列过程：

•  
  1. 有选择地用CREATE TABLE创建表。
  2. 执行FLUSH TABLES语句或命令mysqladmin flush-tables。
  3. 使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name。这将从表中取消所有索引的使用。
  4. 用LOAD DATA INFILE把数据插入到表中，因为不更新任何索引，因此很快。
  5. 如果只想在以后读取表，使用myisampack压缩它。参见15.1.3.3节，“压缩表特性”。
  6. 用myisamchk -r -q /path/to/db/tbl_name重新创建索引。这将在写入磁盘前在内存中创建索引树，并且它更快，因为避免了大量磁盘搜索。结果索引树也被完美地平衡。
  7. 执行FLUSH TABLES语句或mysqladmin flush-tables命令。

请注意如果插入一个空MyISAM表，LOAD DATA INFILE也可以执行前面的优化；主要不同处是可以让myisamchk为创建索引分配更多的临时内存，比执行LOAD DATA INFILE语句时为服务器重新创建索引分配得要多。

也可以使用ALTER TABLE tbl_name DISABLE KEYS代替myisamchk --keys-used=0 -rq /path/to/db/tbl_name，使用ALTER TABLE tbl_name ENABLE KEYS代替myisamchk -r -q /path/to/db/tbl_name。使用这种方式，还可以跳过FLUSH TABLES。

·         锁定表可以加速用多个语句执行的INSERT操作：

• LOCK TABLES a WRITE;
• INSERT INTO a VALUES (1,23),(2,34),(4,33);
• INSERT INTO a VALUES (8,26),(6,29);
• UNLOCK TABLES;

这样性能会提高，因为索引缓存区仅在所有INSERT语句完成后刷新到磁盘上一次。一般有多少INSERT语句即有多少索引缓存区刷新。如果能用一个语句插入所有的行，就不需要锁定。

对于事务表，应使用BEGIN和COMMIT代替LOCK TABLES来加快插入。

锁定也将降低多连接测试的整体时间，尽管因为它们等候锁定最大等待时间将上升。例如：

Connection 1 does 1000 inserts

Connections 2, 3, and 4 do 1 insert

Connection 5 does 1000 inserts

如果不使用锁定，2、3和4将在1和5前完成。如果使用锁定，2、3和4将可能不在1或5前完成，但是整体时间应该快大约40%。

INSERT、UPDATE和DELETE操作在MySQL中是很快的，通过为在一行中多于大约5次连续不断地插入或更新的操作加锁，可以获得更好的整体性能。如果在一行中进行多次插入，可以执行LOCK TABLES，随后立即执行UNLOCK TABLES(大约每1000行)以允许其它的线程访问表。这也会获得好的性能。

INSERT装载数据比LOAD DATA INFILE要慢得多，即使是使用上述的策略。

·         为了对LOAD DATA INFILE和INSERT在MyISAM表得到更快的速度，通过增加key_buffer_size系统变量来扩大 键高速缓冲区。参见7.5.2节，“调节服务器参数”。

 

7.2.17. UPDATE语句的速度

更新查询的优化同SELECT查询一样，需要额外的写开销。写速度依赖于更新的数据大小和更新的索引的数量。没有更改的索引不被更新。

使更改更快的另一个方法是推迟更改然后在一行内进行多次更新。如果锁定表，同时做多个更新比一次做一个快得多。

请注意对使用动态记录格式的MyISAM表，更新一个较长总长的记录可能会切分记录。如果经常这样该，偶尔使用OPTIMIZE TABLE很重要。参见13.5.2.5节，“OPTIMIZE TABLE语法”。

7.2.18. DELETE语句的速度

删除一个记录的时间与索引数量确切成正比。为了更快速地删除记录，可以增加键高速缓冲的大小。参见7.5.2节，“调节服务器参数”。

如果想要删除一个表的所有行，使用TRUNCATE TABLE tbl_name 而不要用DELETE FROM tbl_name。参见13.2.9节，“TRUNCATE语法”。

7.2.19. 其它优化技巧

该节列出了提高查询速度的各种技巧：

·         使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改thread_cache_size变量的值。参见7.5.2节，“调节服务器参数”。

·         总是检查所有查询确实使用已经在表中创建了的索引。在MySQL中，可以用EXPLAIN命令做到。参见7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

·         尝试避免在频繁更新的表上执行复杂的SELECT查询，以避免与锁定表有关的由于读、写冲突发生的问题。

·         对于没有删除的行的MyISAM表，可以在另一个查询正从表中读取的同时在末尾插入行。如果这很重要，应考虑按照避免删除行的方式使用表。另一个可能性是在删除大量行后运行OPTIMIZE TABLE。参见15.1节，“MyISAM存储引擎”。

·         要修复任何ARCHIVE表可以发生的压缩问题，可以执行OPTIMIZE TABLE。参见15.8节，“ARCHIVE存储引擎”。

·         如果你主要按expr1，expr2，...顺序检索行，使用ALTER TABLE ... ORDER BY expr1, expr2, ...。对表大量更改后使用该选项，可以获得更好的性能。

·         在一些情况下，使得基于来自其它表的列的信息引入一个“ 哈希”的列有意义。如果该列较短并且有合理的唯一值，它可以比在许多列上的一个大索引快些。在MySQL中，很容易使用这个额外列：

·                SELECT * FROM tbl_name

·                     WHERE hash_col=MD5(CONCAT(col1,col2))

·                     AND col1='constant' AND col2='constant';

·         对于频繁更改的MyISAM表，应试图避免所有变长列(VARCHAR、BLOB和TEXT)。如果表包括单一的变长列则使用动态记录格式。参见第15章：存储引擎和表类型。

·         只是因为行太大，将一张表分割为不同的表一般没有什么用处。为了访问行，最大的性能冲击是磁盘搜索以找到行的第一个字节。在找到数据后，大多数新型磁盘对大多数应用程序来说足够快，能读入整个行。确实有必要分割的唯一情形是如果它是使用动态记录格式使之变为固定的记录大小的MyISAM表(见上述)，或如果你需要很频繁地扫描表而不需要大多数列。参见第15章：存储引擎和表类型。

·         如果你需要很经常地计算结果，例如基于来自很多行的信息的计数，引入一个新表并实时更新计数器可能更好一些。下面形式的更新会更快一些：

UPDATE tbl_name SET count_col=count_col+1 WHERE key_col=constant;

当你使用象MyISAM那样的只有表级锁定的MySQL存储引擎(多重读/单个写)时，这确实很重要。这也给大多数数据库较好的性能，因为行锁定管理器在这种情况下有较少的事情要做。

·         如果你需要从大的记录文件表中收集统计信息，使用总结性的表而不是扫描整个表。维护总结应该比尝试做“实时”统计要快些。当有变化时从日志重新生成新的总结表比改变运行的应用(取决于业务决策)要快得多。

• 如果可能，应该将报告分类为“实时”或“统计”，这里统计报告所需的数据仅仅基于从实际数据定期产生的总结表中产生。
• 充分利用列有默认值的事实。只有当插入的值不同于默认值时，才明确地插入值。这减少MySQL需要做的语法分析从而提高插入速度。
• 在一些情况下，包装并存储数据到一个BLOB列中是很方便的。在这种情况下，必须在你的应用中增加额外的代码来打包/解包信息，但是这种方法可以在某些阶段节省很多访问。当有不符合行和列表结构的数据时，这很实用。
• 在一般情况下，应该尝试以非冗余方式(查看数据库理论中的第三正则形式)保存数据，但是为了获得更快的速度，可以复制信息或创建总结表。
• 存储过程或UDF(用户定义函数)可能是获得更好性能的一个好方法，详细信息参见第20章：存储程序和函数和27.2节，“为MySQL添加新函数”。
• 总是能通过在应用程序中缓存查询/答案并尝试同时执行很多插入/更新来获得一些好处。如果数据库支持锁定表(象MySQL和Oracle)，这应该有助于确保索引缓存只在所有更新后刷新一次。还可以利用MySQL的查询缓存来获得类似的结果；参见5.13节，“MySQL查询高速缓冲”。
• 当不需要知道何时写入数据时，使用INSERT DELAYED。这样可以加快处理，因为很多记录可以通过一次磁盘写入被写入。
• 当你想要让选择显得更重要时，使用INSERT /*! LOW_PRIORITY */。
• 使用INSERT LOW_PRIORITY来取得插入队列的检索，也就是即使有另一个客户等待写入也要执行SELECT。
• 使用多行INSERT语句通过一个SQL命令来存储很多行(许多SQL服务器支持它，包括MySQL)。
• 使用LOAD DATA INFILE装载较大数量的数据。这比使用INSERT要快得多。
• 使用AUTO_INCREMENT列构成唯一值。
• 当MyISAM使用动态表格式时，偶尔使用OPTIMIZE TABLE可以避免碎片。参见15.1.3节，“MyISAM表的存储格式”。
• 可能时使用MEMORY表以得到更快的速度。参见15.4节，“MEMORY (HEAP)存储引擎”。
• 在Web服务器中，图象和其它二进制资产应该作为文件存储。也就是仅在数据库中存储的本文件的引用而不是文件本身。大多数Web服务器在缓存文件方面比数据库内容要好得多，因此使用文件一般要快得多。
• 对经常访问的不重要数据(如为没有在Web 浏览器中启用cookie的用户最后显示的标语的相关信息)使用内存表。在许多Web应用程序环境中也可以使用用户会话来处理可变状态数据。
• 在不同表中具有相同信息的列应该被声明为相同的并有相同的名字。尝试使名字简单化。例如，在customer表中使用name而不是customer_name。为了使名字能移植到其它SQL服务器，应该使名字短于18个字符。
• 如果确实需要很高的速度，应该研究一下不同SQL服务器支持的数据存储的低层接口！例如直接访问MySQL MyISAM存储引擎，比起使用SQL接口，速度可以提高2-5倍。为了能实现，数据必须与应用程序在同一台服务器上，并且通常只应该被一个进程访问(因为外部文件锁定确实很慢)。通过在MySQL服务器中引进低层MyISAM命令能消除以上问题(如果需要，这可能是获得更好性能的一个简单的方法)。通过精心设计数据库接口，应该能相当容易地支持这类优化。
• 如果正使用数字数据，在许多情况下，从一个数据库访问信息(使用实时连接)比访问一个文本文件快些。这是因为数据库中的信息比文本文件更紧凑，因此这将涉及更少的磁盘访问。还可以在应用程序中节省代码，因为不须分析文本文件来找出行和列的边界。

·         通过复制可以提高某些操作的性能。可以在复制服务器中分布客户的检索以均分负载。为了防止备份时主服务器变慢，可以使用一个从服务器来备份。参见第6章：MySQL中的复制。

·         用DELAY_KEY_WRITE=1选项声明MyISAM表可以使索引更新更快，因为在表关闭之前它们不刷新到硬盘上。不利之处是当表打开时如果杀掉服务器，应确保用--myisam-recover选项运行服务器保证没有问题，或者在重启服务器之前运行myisamchk。(然而，即使在这种情况下，应通过使用DELAY_KEY_WRITE保证不丢失数据，因为关键字信息总是可以从数据行产生）。