当使用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服务器模式”。

因为不同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查询高速缓冲”。

该节描述了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中。

本节应该包含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/。

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

关于一些可移植的基准程序的例子，参见MySQL基准套件。请参见7.1.4节，“MySQL基准套件”。可以利用这个套件的任何程序并且根据你的需要修改它。通过这样做，可以尝试不同的问题的解决方案并测试哪一个是最好的解决方案。

另一个免费基准套件是开放源码数据库基准套件，参见http://osdb.sourceforge.net/。

在系统负载繁重时出现一些问题是很普遍的，并且很多客户已经与我们联系了，他们在生产系统中有一个(测试)系统并且有负载问题。大多数情况下，性能问题经证明是与基本数据库设计有关的问题(例如，表扫描在高负载时表现不好)或操作系统或库问题。如果系统已经不在生产系统中，它们大多数将很容易修正。

为了避免这样的问题，应该把工作重点放在在可能最坏的负载下测试你的整个应用程序。你可以使用Super Smack。该工具可以从http://jeremy.zawodny.com/mysql/super-smack/获得。正如它的名字所建议，它可以根据你的需要提供合理的系统，因此确保只用于你的开发系统。

mysql> SELECT BENCHMARK(1000000,1+1)；

+------------------------+

| BENCHMARK(1000000,1+1) |

+------------------------+

|                      0 |

+------------------------+

1 row in set (0.32 sec)

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值不是均匀分布的。

开始，在进行优化前，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子句以不同的次序列出表，可能得到更好的性能。

在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在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节，“调节服务器参数”。

总的来说，要想使一个较慢速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上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！

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;

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 ...);

SELECT * FROM t1, t2

     WHERE (t1.a=t2.a AND t2.a IS NULL)

     OR (t1.b=t2.b AND t2.b IS NULL);

在许多情况下结合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;

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;

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;

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;

表示联接的语法允许嵌套联接。下面的讨论引用了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中）。

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

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

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

T2.B IS NOT NULL,

T2.B > 3,

T2.C <= T1.C,

T2.B < 2 OR T2.C > 1

T2.B IS NULL,

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

T1.B < 3 OR T2.B > 3

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的。因此该查询可以转换为：

在某些情况中，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;

满足GROUP BY子句的最一般的方法是扫描整个表并创建一个新的临时表，表中每个组的所有行应为连续的，然后使用该临时表来找到组并应用累积函数(如果有)。在某些情况中，MySQL能够做得更好，通过索引访问而不用创建临时表。

为GROUP BY使用索引的最重要的前提条件是 所有GROUP BY列引用同一索引的属性，并且索引按顺序保存其关键字(例如，这是B-树索引，而不是HASH索引)。是否用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的累积函数。

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

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

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

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

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

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

在一些情况中，当你使用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子句来计算需要多少空间。

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节，“服务器系统变量”。

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

• 连接：(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节，“调节服务器参数”。

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

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

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

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

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

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

·         使用持久的连接数据库以避免连接开销。如果不能使用持久的连接并且你正启动许多新的与数据库的连接，可能要更改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 。
• 使用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保证不丢失数据，因为关键字信息总是可以从数据行产生）。

·                UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;

·                DELETE FROM tbl_name WHERE unique_key_col=key_value;

为达到最高锁定速度，除InnoDB和BDB之外，对所有存储引擎，MySQL使用表锁定(而不是页、行或者列锁定)。

对于InnoDB和BDB表，如果你用LOCK TABLES显式锁定表，MySQL只使用表锁定。对于这些表类型，我们建议你根本不要使用LOCK TABLES，因为InnoDB使用自动行级锁定而BDB使用页级锁定来保证事务隔离。

对于大表，对于大多数应用程序，表锁定比行锁定更好，但存在部分缺陷。

表锁定使许多线程同时从一个表中进行读取操作，但如果一个线程想要对表进行写操作，它必须首先获得独占访问。更新期间，所有其它想要访问该表的线程必须等待直到更新完成。

表更新通常情况认为比表检索更重要，因此给予它们更高的优先级。这应确保更新一个表的活动不能“饿死”，即使该表上有很繁重的SELECT活动。

表锁定在这种情况下会造成问题，例如当线程正等待，因为硬盘已满并且在线程可以处理之前必须有空闲空间。在这种情况下，所有想要访问出现问题的表的线程也被设置成等待状态，直到有更多的硬盘空间可用。

表锁定在下面的情况下也存在问题：

·         一个客户发出长时间运行的查询。

·         然后，另一个客户对同一个表进行更新。该客户必须等待直到SELECT完成。

·         另一个客户对同一个表上发出了另一个SELECT语句。因为UPDATE比SELECT优先级高，该SELECT语句等待UPDATE完成，并且等待第1个SELECT完成。

下面描述了一些方法来避免或减少表锁定造成的竞争：

·         试图使SELECT语句运行得更快。你可能必须创建一些摘要(summary)表做到这点。

·         用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下，在先前情形的第2个SELECT语句将在UPDATE语句前执行，而不需要等候第1个SELECT完成。

·         可以使用SET LOW_PRIORITY_UPDATES=1语句指定具体连接中的所有更新应使用低优先级。参见13.5.3节，“SET语法”

·         可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。

·         可以用HIGH_PRIORITY属性给与一个特定的SELECT语句较高优先级。参见13.2.7节，“SELECT语法”。

·         为max_write_lock_count系统变量指定一个低值来启动mysqld来强制MySQL在具体数量的插入完成后临时提高所有等待一个表的SELECT语句的优先级。这样允许在一定数量的WRITE锁定后给出READ锁定。

·         如果你有关于INSERT结合SELECT的问题，切换到使用新的MyISAM表，因为它们支持并发的SELECT和INSERT。

·         如果你对同一个表混合插入和删除，INSERT DELAYED将会有很大的帮助。参见13.2.4.2节，“INSERT DELAYED语法”。

·         如果你对同一个表混合使用SELECT和DELETE语句出现问题，DELETE的LIMIT选项可以有所帮助。参见13.2.1节，“DELETE语法”。

·         对SELECT语句使用SQL_BUFFER_RESULT可以帮助使表锁定时间变短。参见13.2.7节，“SELECT语法”。

·         可以更改mysys/thr_lock.c中的锁代码以使用单一的队列。在这种情况下，写锁定和读锁定将具有相同的优先级，对一些应用程序会有帮助。

这里是一些MySQL中表锁定相关的技巧：

·         如果不混合更新与需要在同一个表中检查许多行的选择，可以进行并行操作。

·         可以使用LOCK TABLES来提高速度，因为在一个锁定中进行许多更新比没有锁定的更新要快得多。将表中的内容切分为几个表也可以有所帮助。

·         如果在MySQL中表锁定时遇到速度问题，可以将你的表转换为InnoDB或BDB表来提高性能。参见15.2节，“InnoDB存储引擎”和15.5节，“BDB (BerkeleyDB)存储引擎”。

最基本的优化之一是使表在磁盘上占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快，并且在查询执行过程中小表的内容被处理时占用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。

MySQL支持许多不同的存储引擎(表类型)和行格式。对于每个表，可以确定使用哪个存储引擎和索引方法。为应用程序选择合适的表格式可以大大提高性能。参见第15章：存储引擎和表类型。

可以使用下面的技术可以使表的性能更好并且使存储空间最小：

• 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。
• 尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25%。
• 如果可能，声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免 默认地在所有列上有它。
• 对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。参见15.1.3节，“MyISAM表的存储格式”。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。
• 在MySQL/InnoDB中，InnoDB表使用更紧凑的存储格式。在以前版本的MySQL中，InnoDB记录包含一些冗余信息，例如列数目和每个列的长度，即使对于固定大小的列。默认情况，创建的表为紧凑格式(ROW_FORMAT=COMPACT)。如果想要降级旧版本的MySQL/InnoDB，可以用ROW_FORMAT=REDUNDANT要求旧的格式。
• 紧凑的InnoDB格式也改变了包含UTF-8数据的CHAR列的保存方式。在ROW_FORMAT=REDUNDANT格式中，UTF-8 CHAR(n)占用3*n字节，假定UTF-8编码的字符的最大长度是3字节。许多语言可以主要用单字节UTF-8字符来编写，固定的存储长度通常会浪费空间。通过根据需要剥离尾部的空格，ROW_FORMAT=COMPACT格式为这些列分配可变数量的n..3*n字节。最小存储长度按顺序保存为n字节，以在典型情况下帮助更新。
• 每张表的主索引应该尽可能短。这使一行的识别容易而有效。
• 只创建你确实需要的索引。索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好的索引压缩。
• 如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持对一个字符列的最左边部分创建一个索引(参见13.1.4节，“CREATE INDEX语法”)。更短的索引会更快，不仅因为它们占较少的磁盘空间，而且因为它们将在索引缓存中提供更多的访问，因此磁盘搜索更少。参见7.5.2节，“调节服务器参数”。

·         在一些情形下，将一个经常被扫描的表分割为2个表是有益的。特别是如果它是一个动态格式的表，并且可能使用一个扫描表时能用来找出相关行的较小静态格式的表。

所有MySQL列类型可以被索引。对相关列使用索引是提高SELECT操作性能的最佳途径。

根据存储引擎定义每个表的最大索引数和最大索引长度。参见第15章：存储引擎和表类型。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。

在索引定义中用col_name(length)语法，你可以创建一个只使用CHAR或VARCHAR列的第1个length字符的索引。按这种方式只索引列值的前缀可以使索引文件小得多。

MyISAM和InnoDB存储引擎还支持对BLOB和TEXT列的索引。当索引一个BLOB或TEXT列时，你必须为索引指定前缀长度。例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

在MySQL 5.1中，对于MyISAM和InnoDB表，前缀可以达到1000字节长。请注意前缀的限制应以字节为单位进行测量，而CREATE TABLE语句中的前缀长度解释为字符数。当为使用多字节字符集的列指定前缀长度时一定要加以考虑。

还可以创建FULLTEXT索引。该索引可以用于全文搜索。只有MyISAM存储引擎支持FULLTEXT索引，并且只为CHAR、VARCHAR和TEXT列。索引总是对整个列进行，不支持局部(前缀)索引。详情参见12.7节，“全文搜索功能”。

也可以为空间列类型创建索引。只有MyISAM存储引擎支持空间类型。空间索引使用R-树。

默认情况MEMORY(HEAP)存储引擎使用hash索引，但也支持B-树索引。

MySQL可以为多个列创建索引。一个索引可以包括15个列。对于某些列类型，可以索引列的前缀(参见7.4.3节，“列索引”)。

多列索引可以视为包含通过连接索引列的值而创建的值的排序的数组。

MySQL按这样的方式使用多列索引：当你在WHERE子句中为索引的第1个列指定已知的数量时，查询很快，即使你没有指定其它列的值。

假定表具有下面的结构：

CREATE TABLE test (

    id INT NOT NULL,

    last_name CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    PRIMARY KEY (id),

    INDEX name (last_name,first_name)

);

name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。因此，name索引用于下面的查询：

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test

    WHERE last_name='Widenius' AND first_name='Michael';

SELECT * FROM test

    WHERE last_name='Widenius'

    AND (first_name='Michael' OR first_name='Monty');

SELECT * FROM test

    WHERE last_name='Widenius'

    AND first_name >='M' AND first_name < 'N';

然而，name索引不用于下面的查询：

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test

    WHERE last_name='Widenius' OR first_name='Michael';

MySQL使用索引提高查询性能的方式将在7.4.5节，“MySQL如何使用索引”中讨论。

索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。

大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。

字符串自动地压缩前缀和结尾空格。参见13.1.4节，“CREATE INDEX语法”。

总的来说，按后面的讨论使用索引。本节最后描述hash索引(用于MEMORY表)的特征。

索引用于下面的操作：

·         快速找出匹配一个WHERE子句的行。

·         删除行。如果可以在多个索引中进行选择，MySQL通常使用找到最少行的索引。

·         当执行联接时，从其它表检索行。

·         对具体有索引的列key_col找出MAX()或MIN()值。由预处理器进行优化，检查是否对索引中在key_col之前发生所有关键字元素使用了WHERE key_part_# = constant。在这种情况下，MySQL为每个MIN()或MAX()表达式执行一次关键字查找，并用常数替换它。如果所有表达式替换为常量，查询立即返回。例如：

·                SELECT MIN(key_part2),MAX(key_part2)

·                    FROM tbl_name WHERE key_part1=10;

·         如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)，排序或分组一个表。如果所有关键字元素后面有DESC，关键字以倒序被读取。参见7.2.12节，“MySQL如何优化ORDER BY”。

·         在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列，为了更快，可以从索引树检索出值。

·                SELECT key_part3 FROM tbl_name

·                    WHERE key_part1=1

假定你执行下面的SELECT语句：

mysql> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果col1和col2上存在一个多列索引，可以直接取出相应行。如果col1和col2上存在单列索引，优化器试图通过决定哪个索引将找到更少的行来找出更具限制性的索引并且使用该索引取行。

如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。

如果列不构成索引最左面的前缀，MySQL不能使用局部索引。假定有下面显示的SELECT语句。

SELECT * FROM tbl_name WHERE col1=val1;

SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

SELECT * FROM tbl_name WHERE col2=val2;

SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

如果 (col1，col2，col3)有一个索引，只有前2个查询使用索引。第3个和第4个查询确实包括索引的列，但(col2)和(col2，col3)不是 (col1，col2，col3)的最左边的前缀。

也可以在表达式通过=、>、>=、<、<=或者BETWEEN操作符使用B-树索引进行列比较。如果LIKE的参数是一个不以通配符开头的常量字符串，索引也可以用于LIKE比较。例如，下面的SELECT语句使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

在第1个语句中，只考虑带'Patrick' <=key_col < 'Patricl'的行。在第2个语句中，只考虑带'Pat' <=key_col < 'Pau'的行。

下面的SELECT语句不使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';

SELECT * FROM tbl_name WHERE key_col LIKE other_col;

在第一条语句中，LIKE值以一个通配符字符开始。在第二条语句中，LIKE值不是一个常数。

如果使用... LIKE '%string%'并且string超过3个字符，MySQL使用Turbo Boyer-Moore算法初始化字符串的模式然后使用该模式来更快地进行搜索。

如果col_name被索引，使用col_name IS NULL的搜索将使用索引。

任何不跨越WHERE子句中的所有AND级的索引不用于优化查询。换句话说，为了能够使用索引，必须在每个AND组中使用索引前缀。

下面的WHERE子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3

... WHERE index=1 OR A=10 AND index=2

... WHERE index_part1='hello' AND index_part3=5

... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

下面的WHERE子句不使用索引：

... WHERE index_part2=1 AND index_part3=2

... WHERE index=1 OR A=10

... WHERE index_part1=1 OR index_part2=10

有时MySQL不使用索引，即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下，表扫描可能会更快些，因为需要的搜索要少）。然而，如果此类查询使用LIMIT只搜索部分行，MySQL则使用索引，因为它可以更快地找到几行并在结果中返回。

Hash索引还有一些其它特征：

·         它们只用于使用=或<=>操作符的等式比较(但很快)。它们用于比较 操作符，例如发现范围值的<。

·         优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目）。

·         MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引)。如果你将一个MyISAM表改为hash-索引的MEMORY表，会影响一些查询。

·         只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的最左面的前缀可用来找到行）。

为了使硬盘I/O最小化，MyISAM存储引擎使用一个被许多数据库管理系统使用的策略。它使用一个缓存机制将经常访问的表锁在内存中：

·         对于索引块，维护一个称之为键高速缓冲(或键高速缓冲区)的特殊结构。该结构包含大量块缓存区，其中放置了最常用的索引块。

·         对于数据块，MySQL不使用特殊缓存。而使用原生的操作系统文件系统的缓存。

本节首先描述了MyISAM键高速缓冲的基本操作。然后讨论了提高 键高速缓冲性能并使你更好地控制缓存操作的最新的更改：

·         多个线程可以并行访问缓存。

·         可以设置多个键高速缓冲，并将表索引指定给具体缓存。

可以使用key_buffer_size系统变量控制 键高速缓冲的大小。如果该变量设置为零，不使用键高速缓冲。如果key_buffer_size值太小不能分配最小数量的块缓存区(8)，也不使用 键高速缓冲。

如果键高速缓冲不工作，只使用操作系统提供的原生文件系统缓存区访问索引文件。(换句话说，使用与表数据块相同的策略表来访问索引块）。

索引块是一个连续的访问MyISAM索引文件的单位。通常一个索引块的大小等于索引B-树节点的大小。(在硬盘上使用B-树数据结构表示索引。树底部的节点为叶子节点。叶子节点上面的节点为非叶子节点）。

键高速缓冲结构中的所有块缓存区大小相同。该大小可以等于、大于或小于表索引块的大小。通常这两个值中的一个是另一个的几倍。

当必须访问表索引块中的数据时，服务器首先检查是否它可以用于键高速缓冲中的某些块缓存区。如果适用，服务器访问键高速缓冲中的数据而不是硬盘上的数据。也就是说，从缓存读取或写入缓存，而不是从硬盘读写。否则，服务器选择一个包含一个不同的表索引块的缓存块缓存区，并用需要的表索引块的拷贝替换那里的数据。一旦新的索引块位于缓存中，可以访问索引数据。

如果用于替换的块已经被修改了，块被视为“脏了”。在这种情况下，在替换前，其内容被刷新到它来自的表索引。

通常服务器遵从LRU(最近最少使用)策略：当选择一个块用于替换时，它选择最近最少使用的索引块。为了使该选择更容易， 键高速缓冲模块维护所有使用的块的专门队列(LRU链)。当访问块时，它被放到队列最后。当块需要替换时，队列开头的块是最近最少使用的块，并成为第1个候选者。

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;

+---------+--------------------+----------+----------+

| Table   | Op                 | Msg_type | Msg_text |

+---------+--------------------+----------+----------+

| test.t1 | assign_to_keycache | status   | OK       |

| test.t2 | assign_to_keycache | status   | OK       |

| test.t3 | assign_to_keycache | status   | OK       |

+---------+--------------------+----------+----------+

mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;

mysql> SET GLOBAL keycache1.key_buffer_size=0;

mysql> set global key_buffer_size = 0;

mysql> show variables like 'key_buffer_size';

+-----------------+---------+

| Variable_name   | Value   |

+-----------------+---------+

| key_buffer_size | 8384512 |

+-----------------+---------+

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

init_file=/path/to/data-directory/mysqld_init.sql

CACHE INDEX a.t1, a.t2, b.t3 IN hot_cache

CACHE INDEX a.t4, b.t5, b.t6 IN cold_cache

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;

+---------+--------------+----------+----------+

| Table   | Op           | Msg_type | Msg_text |

+---------+--------------+----------+----------+

| test.t1 | preload_keys | status   | OK       |

| test.t2 | preload_keys | status   | OK       |

+---------+--------------+----------+----------+

mysql> SET GLOBAL cold_cache。key_buffer_size=4*1024*1024；

存储引擎搜集优化器使用的表的统计信息。表统计基于数数值组，其中数数值组是一系列有相同的关键字前缀值的记录。对于优化器，重要的统计即为数数值组的平均大小。

MySQL用下述方式使用平均数数值组：

·         估计必须为每个ref访问读取多少行

·         估计部分联接将产生多少行；也就是说，下述形式的操作将产生的行数：

·                  (...) JOIN tbl_name ON tbl_name.key = expr

随着索引的平均数数值组大小的增加，索引将更没有用，因为每个查找的平均行数增加：为了让索引有利于优化目的，最好是每个索引值对应表内的少量行数。当某个给定的索引值产生较多行时，索引更加没有用，MySQL更不可能使用它。

平均数数值组大小与表的集的势相关，即数数值组的数目。SHOW INDEX语句显示集的势值（基于N/S），其中N是表内的记录数，S是平均数数值组大小。该比例产生表内数数值组的大约数。

对于基于<=>比较 操作符的联接，NULL并不视为与任何其它值不同：NULL <=> NULL，正如对于其它N ，N <=> N。

然而，对于基于=操作符的联接，NULL与非NULL值不同：当expr1或expr2(或两者)为NULL时，expr1 = expr2不为真。这样影响比较形式tbl_name.key = expr的ref访问：如果expr当前的值为NULL，MySQL不会访问表，因为比较不能为真。

对于=比较，表内有多少NULL值并不重要。为了优化目的，相关值为非NULL数值组的平均大小。然而，MySQL目前不允许搜集或使用该平均大小。

对于MyISAM表，你可以使用myisam_stats_method系统变量部分控制表统计信息的搜集。该变量有两个可能的不同值，如下所示：

·         当myisam_stats_method为nulls_equal时，所有NULL值被视为相等的(也就是说，它们都形成一个数值组)。

如果NULL数值组大小远大于平均非NULL数值组大小，该方法向上倾斜平均数数值组大小。这样使索引对于优化器来说比它实际为查找非NULL值的联接更加没有用。结果是，nulls_equal方法会使优化器进行ref访问时本应使用索引而没有使用。

·         当myisam_stats_method为nulls_unequal时，NULL值不视为相同。相反，每个NULL值形成一个单独的数值组，大小为1。

如果你有许多NULL值，该方法向下倾斜平均数数值组大小。如果平均非NULL数值组较大，统计大小为1的每个组的NULL值会使优化器过高估计查找非NULL值的联接的索引值。结果是，当其它方法会更好时，nulls_unequal方法会使优化器为ref查找使用该索引。

如果你要使用许多使用<=>而不是=的联接，在比较过程中NULL值并不特殊，一个NULL等于另一个NULL。在这种情况下，nulls_equal是合适的统计方法。

myisam_stats_method系统变量有全局和会话值。设置全局值会影响MyISAM 为所有MyISAM表的统计的搜集。设置会话值只影响当前客户连接的统计的搜集。这说明你可以强制用给定的方法重新生成表的统计的搜集，而不需要因为设置myisam_stats_method的会话值而影响其它客户。

可以使用下面任一方法来重新生成表的统计信息：

·         设置myisam_stats_method，然后执行CHECK TABLE语句

·         执行myisamchk --stats_method=method_name --analyze

·         更改表，使其统计信息不为最新(例如，插入一行然后删除它)，然后设置myisam_stats_method并执行ANALYZE TABLE语句

使用myisam_stats_method的一些警告：

你可以强制显式搜集表的统计信息，如上所述。然而，MySQL也可以自动搜集统计信息。例如，如果在为表执行语句的过程中，一些语句修改了表，MySQL可以搜集统计信息。(例如，大批插入或删除，或者执行ALTER TABLE语句时可能发生）。如果发生，使用myisam_stats_method此时所有的值搜集统计信息。这样，如果你使用一个方法搜集统计信息，但当后面自动搜集一个表的统计信息时myisam_stats_method被设置为另一个方法，将使用其它方法。

对于给定的MyISAM表，还不能说出使用哪个方法来产生统计信息。

myisam_stats_method只适合MyISAM表。其它存储引擎只有一个方法来搜集表的统计信息。通常它接近于nulls_equal方法。

当运行mysqladmin status时，将看见象这样的一些东西：

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

如果你仅有6个表，Open tables值为12可能有点令人困惑。

MySQL是多线程的，因此许多客户可以同时在同一个表上进行查询。为了使多个客户线程在同一个表上有不同状态的问题减到最小，表被每个并发进程独立地打开。这样需要额外的内存但一般会提高性能。对于MyISAM表，数据文件需要为每个打开表的客户提供一个额外的文件描述符。(索引文件描述符在所有线程之间共享)。

下一节中提供了该主题的更多的信息。参见7.4.9节，“MySQL如何打开和关闭表”。

table_cache、max_connections和max_tmp_tables系统变量影响服务器保持打开的文件的最大数量。如果你增加这些值其中的一个或两个，会遇到操作系统为每个进程打开文件描述符的数量强加的限制。许多操作系统允许你增加打开的文件的限制，尽管该方法随系统的不同而不同。查阅操作系统文档以确定是否可以增加限制以及如何操作。

table_cache与max_connections有关。例如，对于200个并行运行的连接，应该让表的缓存至少有200 * N，这里N是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。

确保操作系统可以处理table_cache设置所指的打开的文件描述符的数目。如果table_cacheis设得太高，MySQL可能为文件描述符耗尽资源并拒绝连接，不能执行查询，并且很不可靠。还必须考虑到MyISAM存储引擎需要为每个打开的表提供两个文件描述符。可以在mysqld_safe中使用--open-files-limit启动选项来增加MySQL适用的文件描述符的数量。参见A.2.17节，“文件未找到”。

打开表的缓存可以保持在table_cache条。 默认为64；可以用mysqld的--table_cache选项来更改。请注意 MySQL可以临时打开更多的 表以执行查询。

在下面的条件下，未使用的表将被关闭并从表缓存中移出：

·         当缓存满了并且一个线程试图打开一个不在缓存中的表时。

·         当缓存包含超过table_cache个条目，并且缓存中的表不再被任何线程使用。

·         当表刷新操作发生。当执行FLUSH TABLES语句或执行mysqladmin flush-tables或mysqladmin refresh命令时会发生。

当表缓存满时，服务器使用下列过程找到一个缓存入口来使用：

·         当前未使用的表被释放，以最近最少使用顺序。

·         如果缓存满了并且没有表可以释放，但是一个新表需要打开，缓存必须临时被扩大。

如果缓存处于一个临时扩大状态并且一个表从在用变为不在用状态，它被关闭并从缓存中释放。

对每个并发访问打开一个表。这意味着，如果2个线程访问同一个表或在同一个查询中访问表两次(例如，将表连接为自身时)，表需要被打开两次。每个并行的打开要求在表缓存中有一个条目。任何表的第一次打开占2个文件描述符：一个用于数据文件另一个用于索引文件。表的每一次额外使用仅占一个数据文件的文件描述符。索引文件描述符在所有线程之间共享。

如果你正用HANDLER tbl_name OPEN语句打开一个表，将为该线程专门分配一个表。该表不被其它线程共享，只有线程调用HANDLER tbl_name CLOSE或线程终止后才被关闭。表关闭后，被拉回表缓存中(如果缓存不满)。参见13.2.3节，“HANDLER语法”。

可以通过检查mysqld的状态变量Opened_tables确定表缓存是否太小：

mysql> SHOW STATUS LIKE 'Opened_tables';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Opened_tables | 2741  |

+---------------+-------+

如果值很大，即使你没有发出许多FLUSH TABLES语句，也应增加表缓存的大小。参见5.3.3节，“服务器系统变量”和5.3.4节，“服务器状态变量”。

我们从系统级因素开始，因为必须尽早地进行部分决策以获得较大性能。在其它情况下，快速浏览该节就足够了。但是，了解一下更改该层次的参数能够获得多少性能提高是很有意义的。

使用的操作系统很重要。为了更好地使用多CPU机器，应使用Solaris(因为其线程工作得很好)或Linux(因为2.4和以后的内核有很好的SMP支持)。请注意默认情况旧的Linux内核有一个2GB的文件大小限制。如果有这样的一个内核并且需要文件大于2GB，应得到ext2文件系统的大文件支持(LFS)补丁。其它文件系统例如ReiserFS和XFS没有此2GB限制。

将MySQL用于生产前，我们建议你在想用的平台上对它进行测试。

其它技巧：

·         如果有足够的RAM，可以移除所有的交换设备。有些操作系统即使有自由内存也使用交换设备。

·         使用--skip-external-locking MySQL选项以避免外部锁定。该选项默认开启。

请注意只要你只运行一个服务器，--skip-external-locking选项不影响MySQL的功能。只要记住运行myisamchk之前关闭服务器(或锁定并刷新相关表)。在一些系统上该选项是强制的，因为在任何情况下外部锁定均不工作。

不能使用--skip-external-locking的唯一情况是对相同的数据运行多个MySQL服务器(非客户)的情况，或者如果没有事先告诉服务器刷新并锁定一个表即运行myisamchk来检查(非修复)该表。请注意一般不建议使用多个MySQL服务器来并行访问相同的数据，除了使用MySQL Cluster时。

即使使用--skip-external-locking，仍然可以使用LOCK TABLES和UNLOCK TABLES。

可以用这个命令得到mysqld服务器 默认缓存区的大小：

shell> mysqld --verbose --help

这个命令生成所有mysqld选项和可配置变量的列表。输出包括 默认值并且看上去象这样：

help                              TRUE

abort-slave-event-count           0

allow-suspicious-udfs             FALSE

auto-increment-increment          1

auto-increment-offset             1

automatic-sp-privileges           TRUE

basedir                           /home/jon/bin/mysql/

bdb                               FALSE

bind-address                      (No default value)

character-set-client-handshake    TRUE

character-set-server              latin1

character-sets-dir                /home/jon/bin/mysql/share/mysql/charsets/

chroot                            (No default value)

collation-server                  latin1_swedish_ci

completion-type                   0

concurrent-insert                 1

console                           FALSE

datadir                           /home/jon/bin/mysql/var/

default-character-set             latin1

default-collation                 latin1_swedish_ci

default-time-zone                 (No default value)

disconnect-slave-event-count      0

enable-locking                    FALSE

enable-pstack                     FALSE

engine-condition-pushdown         FALSE

external-locking                  FALSE

gdb                               FALSE

large-pages                       FALSE

init-connect                      (No default value)

init-file                         (No default value)

init-slave                        (No default value)

innodb                            TRUE

innodb_checksums                  TRUE

innodb_data_home_dir              (No default value)

innodb_doublewrite                TRUE

innodb_fast_shutdown              1

innodb_file_per_table             FALSE

innodb_flush_log_at_trx_commit    1

innodb_flush_method               (No default value)

innodb_locks_unsafe_for_binlog    FALSE

innodb_log_arch_dir               (No default value)

innodb_log_group_home_dir         (No default value)

innodb_max_dirty_pages_pct        90

innodb_max_purge_lag              0

innodb_status_file                FALSE

innodb_table_locks                TRUE

innodb_support_xa                 TRUE

isam                              FALSE

language                          /home/jon/bin/mysql/share/mysql/english

local-infile                      TRUE

log                               /home/jon/bin/mysql/var/master1.log

log-bin                           /home/jon/bin/mysql/var/master1

log-bin-index                     (No default value)

log-bin-trust-routine-creators    FALSE

log-error                         /home/jon/bin/mysql/var/master1.err

log-isam                          myisam.log

log-queries-not-using-indexes     FALSE

log-short-format                  FALSE

log-slave-updates                 FALSE

log-slow-admin-statements         FALSE

log-slow-queries                  (No default value)

log-tc                            tc.log

log-tc-size                       24576

log-update                        (No default value)

log-warnings                      1

low-priority-updates              FALSE

master-connect-retry              60

master-host                       (No default value)

master-info-file                  master.info

master-password                   (No default value)

master-port                       3306

master-retry-count                86400

master-ssl                        FALSE

master-ssl-ca                     (No default value)

master-ssl-capath                 (No default value)

master-ssl-cert                   (No default value)

master-ssl-cipher                 (No default value)

master-ssl-key                    (No default value)

master-user                       test

max-binlog-dump-events            0

memlock                           FALSE

myisam-recover                    OFF

ndbcluster                        FALSE

ndb-connectstring                 (No default value)

ndb-mgmd-host                     (No default value)

ndb-nodeid                        0

ndb-autoincrement-prefetch-sz     32

ndb-distibution                   KEYHASH

ndb-force-send                    TRUE

ndb_force_send                    TRUE

ndb-use-exact-count               TRUE