MySQL 优化全攻略
数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。本章主要讲解了几种优化MySQL的方法，并且给出了例子。记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力。
7.1 优化概述
让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。
最常见的系统瓶颈有以下几种：
* 磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个磁盘中。
* 磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。
* CPU周期。数据存储在主内存中（或者它已经在主内存中了），这就需要处理这些数据以得到想要的结果。存在多个?硐啾饶诖嫒萘坷此蹈窍拗频囊蛩亍２还孕"砝此担俣韧ǔ２皇俏侍狻?
* 内存带宽。当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。
7.1.1 MySQL 设计的局限性
当使用MyISAM存储引擎时，MySQL会使用一个快速数据表锁以允许同时多个读取和一个写入。这种存储引擎的最大问题是发生在一个单一的表上同时做稳定的更新操作及慢速查询。如果这种情况在某个表中存在，可以使用另一种表类型。详情请看"15 MySQL Storage Engines and Table Types"。
MySQL可以同时在事务及非事务表下工作。为了能够平滑的使用非事务表（发生错误时不能回滚），有以下几条规则：
* 所有的字段都有默认值
* 如果字段中插入了一个"错误"的值，比如在数字类型字段中插入过大数值，那么MySQL会将该字段值置为"最可能的值"而不是给出一个错误。数字类型的值是0，最小或者最大的可能值。字符串类型，不是空字符串就是字段所能存储的最大长度。
* 所有的计算表达式都会返回一个值而报告条件错误，例如 1/0 返回 NULL。
这些规则隐含的意思是，不能使用MySQL来检查字段内容。相反地，必须在存储到数据库前在应用程序中来检查。详情请看"1.8.6 How MySQL Deals with Constraints 和 "14.1.4 Insert Syntax"。
7.1.2 应用设计的可移植性
由于各种不同的数据库实现了各自的SQL标准，这就需要我们尽量使用可移植的SQL应用。查询和插入操作很容易就能做到可移植，不过由于更多的约束条件的要求就越发困难。想要让一个应用在各种数据库系统上快速运行，就变得更困难了。
为了能让一个复杂的应用做到可移植，就要先看这个应用运行于哪种数据库系统之上，然后看这些数据库系统都支持哪些特性。
每个数据库系统都有某些不足。也就是说，由于设计上的一些妥协，导致了性能上的差异。
可以用MySQL的 crash-me 程序来看选定的数据库服务器上可以使用的函数，类型，限制等。crash-me 不会检查各种可能存在的特性，不过这仍然是合乎情理的理解，大约做了450次测试。
一个 crash-me 的信息类型的例子就是，它会告诉您如果想使用Informix 或 DB2的话，就不能使字段名长度超过18个字符。
crash-me 程序和MySQL基准使每个准数据库都实现了的。可以通过阅读这些基准程序是怎么写的，自己就大概有怎样做才能让程序独立于各种数据库这方面的想法了。这些程序可以在MySQL源代码的 `sql-bench' 目录下找到。他们大部分都是用Perl写的，并且使用DBI接口。由于它提供了独立于数据库的各种访问方式，因此用DBI来解决各种移植性的问题。
想要看到 crash-me 的结果，可以访问：[url]http://dev.mysql.com/tech-resources/crash-me.php.[/url] 访问 [url]http://dev.mysql.com/tech-resources/benchmarks[/url] 可以看到基准的结果。
如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法。例如，MySQL对于 MyISAM 类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。作为Oracle来说，它在访问刚刚被更新的记录时有很大的问题（直到结果被刷新到磁盘中）。事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。
为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于C++在很多系统上都可以使用，因此使用C++作为数据库的基类结果很合适。
如果使用了某些数据库独有的特定功能（比如 REPLACE 语句就只在MySQL中独有），这就需要通过编写替代方法来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。
在MySQL中，可以在查询语句中使用 /*! */ 语法来增加MySQL特有的关键字。然而在很多其他数据库中，/**/ 却被当成了注释（并且被忽略）。
如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果，这可能会有更高的性能。通过让旧数据在一定时间后过期，来合理的更新缓存。这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常。
这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。
一个实现应用层缓存的可选方案是使用MySQL的查询缓存（query cache）。启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用。它大大简化了应用程序，详情请看"5.11 The MySQL Query Cache"。
7.1.3 我们都用MySQL来做什么
本章描述了一个MySQL的早期应用。
在MySQL最开始的开发过程中，MySQL本来是要准备给大客户用的，他们是瑞典的2个最大的零售商，他们用于货物存储数据管理。
我们每周从所有的商店中得到交易利润累计结果，以此给商店的老板提供有用的信息，帮助他们分析如果更好的打广告以影响他们的客户。
数据量相当的大（每个月的交易累计结果大概有7百万），而且还需要显示4－10年间的数据。我们每周都得到客户的需求，他们要求能‘瞬间’地得到数据的最新报表。
我们把每个月的全部信息存储在一个压缩的‘交易’表中以解决这个问题。我们有一些简单的宏指令集，它们能根据不同的标准从存储的‘交易’表中根据字段分组（产品组、客户id、商店等等）取得结果。我们用一个小Perl脚本动态的生成Web页面形式的报表。这个脚本解析Web页面，执行SQL语句，并且插入结果。我们还可以用PHP或者mod_perl来做这个工作，不过当时还没有这2个工具。
为了得到图形数据，我们还写了一个简单的C语言工具，用于执行SQL查询并且将结果做成GIF图片。这个工具同样是Perl脚本解析Web页面后动态执行的。
很多情况下，只要拷贝现有的脚本简单的修改里面的SQL查询语句就能产生新的报表了。有时候，就需要在现存的累计表中增加更多的字段或者新建一个。这个操作十分简单，因为我们在磁盘上存储有所有的交易表（总共大概有50G的交易表以及20G的其他客户资料）。
我们还允许客户通过ODBC直接访问累计表，这样的话，那些高级用户就可以自己利用这些数据做试验了。
这个系统工作的很好，并且在适度的Sun Ultra SPARC工作站(2x200MHz)上处理数据没有任何问题。最终这个系统移植到了Linux上。
7.1.4 MySQL 基准套件
本章本来要包括MySQL基准套件（以及 crash-me）的技术描述的，但是至今还未写。现在，您可以通过查看MySQL发布源代码 `sql-bench' 目录下的代码以及结果有一个更好的想法。
基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差。
请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。
想要使用基准套件，必备以下几个条件：
* 基准套件在MySQL的发布源代码中就有。可以去 [url]http://dev.mysql.com/downloads/[/url] 下载发布版或者使用现有开发代码树（详情请看"2.3.3 Installing from the Development Source Tree"）。
* 基准脚本是用Perl写的，它用Perl的DBI模块来连接数据库，因此必须安装DBI模块。并且还需要每个要做测试的服务器上都有特定的BDB驱动程序。例如，为了测试MySQL、PostgreSQL和DB2，就必须安装 DBD::mysql, DBD::Pg 及 DBD::DB2 模块。详情请看"2.7 Perl Installation Note"。
取得MySQL的分发源代码后，就能在 `sql-bench' 目录下看到基准套件。想要运行这些基准测试，请先搭建好服务，然后进入 `sql-bench' 目录，执行 run-all-tests 脚本：
shell> cd sql-benchshell> perl run-all-tests --server=server_name
server_name 可以是任何一个可用的服务。想要列出所有的可用选项和支持的服务，只要调用以下命令：
shell> perl run-all-tests --help
crash-me 脚本也是放在 `sql-bench' 目录下。crash-me 通过执行真正的查询以试图判断数据库都支持什么特性、性能表现以及限制。例如，它可以判断：
* 都支持什么字段类型
* 支持多少索引
* 支持什么样的函数
* 能支持多大的查询
* VARCHAR 字段类型能支持多大
可以从 [url]http://dev.mysql.com/tech-resources/crash-me.php[/url] 上找到各种不同数据库 crash-me 的结果。更多的信息请访问 [url]http://dev.mysql.com/tech-resources/benchmarks[/url]。
7.1.5 使用您自己的基准
请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈（或者使用一个假的模块来代替）之后，就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。
从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看"7.1.4 The MySQL Benchmark Suite"。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。
另一个基准套件是开放源码的数据库基准，可以在 [url]http://osdb.sourceforge.net[/url] 上找到。
当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个（测试过的）生产系统也遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计（例如，在高负载下扫描数据表的表现不好）、操作系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。
为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试！可以使用Super Smack，在 [url]http://jeremy.zawodny.com/mysql/super-smack[/url] 可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试。
7.2 优化 Select 语句及其他查询
首先，影响所有语句的一个因素是：您的权限设置越复杂，那么开销就越大。
使用比较简单的 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)
上述结果是在Pentium II 400MHz的系统上执行得到的。它告诉我们：MySQL在这个系统上可以在0.32秒内执行 1,000,000 次简单的加法运算。
所有的MySQL函数都应该被最优化，不过仍然有些函数例外。BENCHMARK() 是一个用于检查查询语句中是否存在问题的非常好的工具。 7.2.1 EXPLAIN 语法（得到Select 的相关信息）
EXPLAIN tbl_name
或者:
EXPLAIN Select select_options
EXPLAIN 语句可以被当作 DESCRIBE 的同义词来用，也可以用来获取一个MySQL要执行的 Select 语句的相关信息。
* EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。
* 当在一个 Select 语句前使用关键字 EXPLAIN 时，MYSQL会解释了即将如何运行该 Select 语句，它显示了表如何连接、连接的顺序等信息。
本章节主要讲述了第二种 EXPLAIN 用法。
在 EXPLAIN 的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让 Select 运行更快。
如果由于不恰当使用索引而引起一些问题的话，可以运行 ANALYZE TABLE 来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择。详情请看"14.5.2.1 ANALYZE TABLE Syntax"。
您还可以查看优化程序是否以最佳的顺序来连接数据表。为了让优化程序按照 Select 语句中的表名的顺序做连接，可以在查询的开始使用 Select STRAIGHT_JOIN 而不只是 Select。
EXPLAIN 返回了一行记录，它包括了 Select 语句中用到的各个表的信息。这些表在结果中按照MySQL即将执行的查询中读取的顺序列出来。MySQL用一次扫描多次连接（single-sweep, multi-join）的方法来解决连接。这意味着MySQL从第一个表中读取一条记录，然后在第二个表中查找到对应的记录，然后在第三个表中查找，依次类推。当所有的表都扫描完了，它输出选择的字段并且回溯所有的表，直到找不到为止，因为有的表中可能有多条匹配的记录下一条记录将从该表读取，再从下一个表开始继续处理。
在MySQL version 4.1中，EXPLAIN 输出的结果格式改变了，使得它更适合例如 UNION 语句、子查询以及派生表的结构。更令人注意的是，它新增了2个字段： id 和 select_type。当你使用早于MySQL 4.1的版本就看不到这些字段了。
EXPLAIN 结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段：
id
本次 Select 的标识符。在查询中每个 Select 都有一个顺序的数值。
select_type
Select 的类型，可能会有以下几种：
SIMPLE
简单的 Select （没有使用 UNION 或子查询）
PRIMARY
最外层的 Select。
UNION
第二层，在Select 之后使用了 UNION 。
DEPENDENT UNION
UNION 语句中的第二个 Select，依赖于外部子查询
SUBQUERY
子查询中的第一个 Select
DEPENDENT SUBQUERY
子查询中的第一个 SUBQUERY 依赖于外部的子查询
DERIVED
派生表 Select（FROM 子句中的子查询）
table
记录查询引用的表。
type
表连接类型。以下列出了各种不同类型的表连接，依次是从最好的到最差的：
system
表只有一行记录（等于系统表）。这是 const 表连接类型的一个特例。
const
表中最多只有一行匹配的记录，它在查询一开始的时候就会被读取出来。由于只有一行记录，在余下的优化程序里该行记录的字段值可以被当作是一个恒定值。const 表查询起来非常快，因为只要读取一次！const 用于在和 PRIMARY KEY 或 UNIQUE 索引中有固定值比较的情形。下面的几个查询中，tbl_name 就是 const 表了：
Select * FROM tbl_name Where primary_key=1;Select * FROM tbl_nameWhere primary_key_part1=1 AND primary_key_part2=2;
eq_ref
从该表中会有一行记录被读取出来以和从前一个表中读取出来的记录做联合。与 const 类型不同的是，这是最好的连接类型。它用在索引所有部分都用于做连接并且这个索引是一个 PRIMARY KEY 或 UNIQUE 类型。eq_ref 可以用于在进行"="做比较时检索字段。比较的值可以是固定值或者是表达式，表达示中可以使用表里的字段，它们在读表之前已经准备好了。以下的几个例子中，MySQL使用了 eq_ref 连接来处理 ref_table：
Select * FROM ref_table,other_tableWhere ref_table.key_column=other_table.column;Select * FROM ref_table,other_tableWhere ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;
ref
该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref 用于连接程序使用键的最左前缀或者是该键不是 PRIMARY KEY 或 UNIQUE 索引（换句话说，就是连接程序无法根据键值只取得一条记录）的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。ref 还可以用于检索字段使用 = 操作符来比较的时候。以下的几个例子中，MySQL将使用 ref 来处理 ref_table：
Select * FROM ref_table Where key_column=expr;Select * FROM ref_table,other_tableWhere ref_table.key_column=other_table.column;Select * FROM ref_table,other_tableWhere ref_table.key_column_part1=other_table.columnAND ref_table.key_column_part2=1;
ref_or_null
这种连接类型类似 ref，不同的是MySQL会在检索的时候额外的搜索包含 NULL 值的记录。这种连接类型的优化是从MySQL 4.1.1开始的，它经常用于子查询。在以下的例子中，MySQL使用 ref_or_null 类型来处理 ref_table：
Select * FROM ref_tableWhere key_column=expr or key_column IS NULL;
详情请看"7.2.6 How MySQL Optimizes IS NULL"。
index_merge
这种连接类型意味着使用了 Index Merge 优化方法。这种情况下，key字段包括了所有使用的索引，key_len 包括了使用的键的最长部分。详情请看"7.2.5 How MySQL Optimizes OR Clauses"。
unique_subquery
这种类型用例如一下形式的 IN 子查询来替换 ref：
value IN (Select primary_key FROM single_table Where some_expr)
unique_subquery 只是用来完全替换子查询的索引查找函数效率更高了。
index_subquery
这种连接类型类似 unique_subquery。它用子查询来代替 IN，不过它用于在子查询中没有唯一索引的情况下，例如以下形式：
value IN (Select key_column FROM single_table Where some_expr)
range
只有在给定范围的记录才会被取出来，利用索引来取得一条记录。key 字段表示使用了哪个索引。key_len 字段包括了使用的键的最长部分。这种类型时 ref 字段值是 NULL。range 用于将某个字段和一个定植用以下任何操作符比较时 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, 或 IN：
Select * FROM tbl_nameWhere key_column = 10;Select * FROM tbl_nameWhere key_column BETWEEN 10 and 20;Select * FROM tbl_nameWhere key_column IN (10,20,30);Select * FROM tbl_nameWhere key_part1= 10 AND key_part2 IN (10,20,30);
index
连接类型跟 ALL 一样，不同的是它只扫描索引树。它通常会比 ALL 快点，因为索引文件通常比数据文件小。MySQL在查询的字段知识单独的索引的一部分的情况下使用这种连接类型。
ALL
将对该表做全部扫描以和从前一个表中取得的记录作联合。这时候如果第一个表没有被标识为 const 的话就不大好了，在其他情况下通常是非常糟糕的。正常地，可以通过增加索引使得能从表中更快的取得记录以避免 ALL。
possible_keys
possible_keys 字段是指MySQL在搜索表记录时可能使用哪个索引。注意，这个字段完全独立于 EXPLAIN 显示的表顺序。这就意味着 possible_keys 里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是 NULL，就表示没有索引被用到。这种情况下，就可以检查 Where 子句中哪些字段那些字段适合增加索引以提高查询的性能。就这样，创建一下索引，然后再用 EXPLAIN 检查一下。详细的查看章节"14.2.2 Alter TABLE Syntax"。想看表都有什么索引，可以通过 SHOW INDEX FROM tbl_name 来看。
key
key 字段显示了MySQL实际上要用的索引。当没有任何索引被用到的时候，这个字段的值就是 NULL。想要让MySQL强行使用或者忽略在 possible_keys 字段中的索引列表，可以在查询语句中使用关键字FORCE INDEX, USE INDEX, 或 IGNORE INDEX。如果是 MyISAM 和 BDB 类型表，可以使用 ANALYZE TABLE 来帮助分析使用使用哪个索引更好。如果是 MyISAM 类型表，运行命令 myisamchk --analyze 也是一样的效果。详细的可以查看章节"14.5.2.1 ANALYZE TABLE Syntax"和"5.7.2 Table Maintenance and Crash Recovery"。
key_len
key_len 字段显示了MySQL使用索引的长度。当 key 字段的值为 NULL 时，索引的长度就是 NULL。注意，key_len 的值可以告诉你在联合索引中MySQL会真正使用了哪些索引。
ref
ref 字段显示了哪些字段或者常量被用来和 key 配合从表中查询记录出来。
rows
rows 字段显示了MySQL认为在查询中应该检索的记录数。
Extra
本字段显示了查询中MySQL的附加信息。以下是这个字段的几个不同值的解释：
Distinct
MySQL当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。
Not exists
MySQL在查询时做一个 LEFT JOIN 优化时，当它在当前表中找到了和前一条记录符合 LEFT JOIN 条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：
Select * FROM t1 LEFT JOIN t2 ON t1.id=t2.idWhere t2.id IS NULL;
假使 t2.id 定义为 NOT NULL。这种情况下，MySQL将会扫描表 t1 并且用 t1.id 的值在 t2 中查找记录。当在 t2 中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是 NULL，就不会再在 t2 中查找相同 id 值的其他记录了。也可以这么说，对于 t1 中的每个记录，MySQL只需要在 t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。
range checked for each record (index map: #)
MySQL没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。
Using filesort
MySQL需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 Where 条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。详情请看"7.2.9 How MySQL Optimizes ORDER BY"。
Using index
字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。
Using temporary
MySQL需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了GROUP BY 和 ORDER BY 子句，它以不同的方式列出了各个字段。
Using where
Where 子句将用来限制哪些记录匹配了下一个表或者发送给客户端。除非你特别地想要取得或者检查表种的所有记录，否则的话当查询的 Extra 字段值不是 Using where 并且表连接类型是 ALL 或 index 时可能表示有问题。
如果你想要让查询尽可能的快，那么就应该注意 Extra 字段的值为Using filesort 和 Using temporary 的情况。
你可以通过 EXPLAIN 的结果中 rows 字段的值的乘积大概地知道本次连接表现如何。它可以粗略地告诉我们MySQL在查询过程中会查询多少条记录。如果是使用系统变量 max_join_size 来取得查询结果，这个乘积还可以用来确定会执行哪些多表 Select 语句。详情请看"7.5.2 Tuning Server Parameters"。
下面的例子展示了如何通过 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.CUSTNAMEFROM tt, et, et AS et_1, doWhere tt.SubmitTime IS NULLAND tt.ActualPC = et.EMPLOYIDAND tt.AssignedPC = et_1.EMPLOYIDAND tt.ClientID = do.CUSTNMBR;
在这个例子中，先做以下假设：
* 要比较的字段定义如下：
Table Column Column Type
tt ActualPC CHAR(10)
tt AssignedPC CHAR(10)
tt ClientID CHAR(10)
et EMPLOYID CHAR(15)
do CUSTNMBR CHAR(15)
* 数据表的索引如下：
Table Index
tt ActualPC
tt AssignedPC
tt ClientID
et EMPLOYID (primary key)
do CUSTNMBR (primary key)
* tt.ActualPC 的值是不均匀分布的。
在任何优化措施未采取之前，经过 EXPLAIN 分析的结果显示如下：
table type possible_keys key key_len ref rows Extraet ALL PRIMARY NULL NULL NULL 74do ALL PRIMARY NULL NULL NULL 2135et_1 ALL PRIMARY NULL NULL NULL 74tt ALL AssignedPC, NULL NULL NULL 3872ClientID,ActualPCrange checked for each record (key map: 35)
由于字段 type 的对于每个表值都是 ALL，这个结果意味着MySQL对所有的表做一个迪卡尔积；这就是说，每条记录的组合。这将需要花很长的时间，因为需要扫描每个表总记录数乘积的总和。在这情况下，它的积是 74 * 2135 * 74 * 3872 = 45,268,558,720 条记录。如果数据表更大的话，你可以想象一下需要多长的时间。
在这里有个问题是当字段定义一样的时候，MySQL就可以在这些字段上更快的是用索引（对 ISAM 类型的表来说，除非字段定义完全一样，否则不会使用索引）。在这个前提下，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 Extratt ALL AssignedPC, NULL NULL NULL 3872 UsingClientID, whereActualPCdo ALL PRIMARY NULL NULL NULL 2135range checked for each record (key map: 1)et_1 ALL PRIMARY NULL NULL NULL 74range 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 Extraet ALL PRIMARY NULL NULL NULL 74tt ref AssignedPC, ActualPC 15 et.EMPLOYID 52 UsingClientID, whereActualPCet_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do 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 Extratt ALL AssignedPC NULL NULL NULL 3872 UsingClientID, whereActualPCet eq_ref PRIMARY PRIMARY 15 tt.ActualPC 1et_1 eq_ref PRIMARY PRIMARY 15 tt.AssignedPC 1do eq_ref PRIMARY PRIMARY 15 tt.ClientID 1
请注意，EXPLAIN 结果中的 rows 字段的值也是MySQL的连接优化程序大致猜测的，请检查这个值跟真实值是否基本一致。如果不是，可以通过在 Select 语句中使用 STRAIGHT_JOIN 来取得更好的性能，同时可以试着在 FROM
分句中用不同的次序列出各个表。 7.2.2 估算查询性能
在大多数情况下，可以通过统计磁盘搜索次数来估算查询的性能。对小表来说，通常情况下只需要搜索一次磁盘就能找到对应的记录（因为索引可能已经缓存起来了）。对大表来说，大致可以这么估算，它使用B树做索引，想要找到一条记录大概需要搜索的次数为：log(row_count) / log(index_block_length / 3 * 2 / (index_length + data_pointer_length)) + 1。
在MySQL中，一个索引块通常是1024bytes，数据指针通常是4bytes。对于一个有500,000条记录、索引长度为3bytes（medium integer）的表来说，根据上面的公式计算得到需要做 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次搜索。
这个表的索引大概需要 500,000 * 7 * 3/2 = 5.2MB的存储空间（假定典型的索引缓冲区的2/3），因此应该会有更多的索引在内存中，并且可能只需要1到2次调用就能找到对应的记录。
对于写来说，大概需要4次（甚至更多）搜索才能找到新的索引位置，更新记录时通常需要2次搜索。
请注意，前面的讨论中并没有提到应用程序的性能会因为log N的值越大而下降。只要所有的东西都能由操作系统或者SQL服务器缓存起来，那么性能只会因为数据表越大而稍微下降。当数据越来越大之后，就不能全部放到缓存中去了，就会越来越慢了，除非应用程序是被磁盘搜索约束的（它跟随着的log N值增加而增加）。为了避免这种情况，可以在数据量增大以后也随着增大索引缓存容量。对 MyISAM 类型表来说，索引缓存容量是由系统变量 key_buffer_size 控制的。详情请看"7.5.2 Tuning Server Parameters"。
7.2.3 Select 查询的速度
通常情况下，想要让一个比较慢的 Select ... Where 查询变得更快的第一件事就是，先检查看看是否可以增加索引。所有对不同表的访问都通常使用索引。可以使用 EXPLAIN 语句来判断 Select 使用了哪些索引。详情请看"7.4.5 How MySQL Uses Indexes"和"7.2.1 EXPLAIN Syntax (Get Information About a Select)"。
以下是几个常用的提高 MyISAM 表查询速度的忠告：
* 想要让MySQL将查询优化的速度更快些，可以在数据表已经加载完全部数据后执行行 ANALYZE TABLE 或运行 myisamchk --analyze 命令。它更新了每个索引部分的值，这个值意味着相同记录的平均值（对于唯一索引来说，这个值则一直都是 1）。MySQL就会在当你使用基于一个非恒量表达式的两表连接时，根据这个值来决定使用哪个索引。想要查看结果，可以在分析完数据表后运行 SHOW INDEX FROM tbl_name 查看 Cardinality 字段的值。myisamchk --description --verbose 显示了索引的分布信息。
* 想要根据一个索引来排序数据，可以运行 myisamchk --sort-index --sort-records=1 （如果想要在索引 1 上做排序）。这对于有一个唯一索引并且想根据这个索引的顺序依次读取记录的话来说是一个提高查询速度的好办法。不过要注意的是，第一次在一个大表上做排序的话将会耗费很长时间。
7.2.4 MySQL如何优化 Where 子句
这个章节讲述了优化程序如何处理 Where 子句。例子中使用了 Select 语句，但是在 Delete 和 Update 语句中对 Where 子句的优化是一样的。
注意，关于MySQL优化的工作还在继续，因此本章节还没结束。MySQL做了很多优化工作，而不仅仅是文档中提到的这些。
MySQL的一些优化做法如下：
* 去除不必要的括号：
((a AND b) AND c or (((a AND b) AND (c AND d)))) -> (a AND b AND c) or (a AND b AND c AND d)
* 展开常量：
(a<b 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
* 常量表达示在索引中只计算一次
* 在单独一个表上做 COUNT(*) 而不使用 Where 时， 对于 MyISAM 和 HEAP 表就会直接从表信息中检索结果。在单独一个表上做任何表 NOT NULL 达式查询时也是这样做。
* 预先探测无效的常量表达式。MySQL会快速探测一些不可能的 Select 语句并且不返回任何记录。
* 当没用 GROUP BY 或分组函数时，HAVING 和 Where 合并（COUNT(), MIN() 等也是如此）。
* 为表连接中的每个表构造一个简洁的 Where 语句，以得到更快的 Where 计算值并且尽快跳过记录。
* 查询中所有的常量表都会比其他表更早读取。一个常量表符合以下几个条件：
o 空表或者只有一条记录。
o 与在一个 UNIQUE 索引、或一个 PRIMARY KEY 的 Where 子句一起使用的表，这里所有的索引部分和常数表达式做比较并且索引部分被定义为 NOT NULL。
以下的几个表都会被当成常量表：
Select * FROM t Where primary_key=1; Select * FROM t1,t2 Where t1.primary_key=1 AND t2.primary_key=t1.id;
* MySQL会进各种可能找到表连接最好的连接方法。 如果在 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_nameWhere key_part1=constant;Select ... FROM tbl_nameorDER BY key_part1,key_part2,... LIMIT 10;Select ... FROM tbl_nameorDER 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_nameWhere key_part1=val1 AND key_part2=val2;Select key_part2 FROM tbl_name GROUP BY key_part1;
以下几个查询使用索引来取得经过顺序排序后的记录而无需经过独立的排序步骤：
Select ... FROM tbl_nameorDER BY key_part1,key_part2,... ;Select ... FROM tbl_nameorDER BY key_part1 DESC, key_part2 DESC, ... ;
7.2.5 MySQL 如何优化 OR 子句
Index Merge 方法用于使用 ref, ref_or_null, 或 range 扫描取得的记录合并起来放到一起作为结果。这种方法在表条件是或条件 ref, ref_or_null, 或 range ，并且这些条件可以用不同的键时采用。
"join"类型的优化是从 MySQL 5.0.0 开始才有的，代表者在索引的性能上有着标志性的改进，因为使用老规则的话，数据库最多只能对每个引用表使用一个索引。
在 EXPLAIN 的结果中，这种方法在 type 字段中表现为 index_merge。这种情况下，key 字段包含了所有使用的索引列表，并且 key_len 字段包含了使用的索引的最长索引部分列表。
例如：
Select * FROM tbl_name Where key_part1 = 10 or key_part2 = 20;Select * FROM tbl_nameWhere (key_part1 = 10 or key_part2 = 20) AND non_key_part=30;Select * FROM t1,t2Where (t1.key1 IN (1,2) or t1.key2 LIKE 'value%')AND t2.key1=t1.some_col;Select * FROM t1,t2Where t1.key1=1AND (t2.key1=t1.some_col or t2.key2=t1.some_col2);
7.2.6 MySQL 如何优化 IS NULL
MySQL在 col_name IS NULL 时做和 col_name = constant_value 一样的优化。例如，MySQL使用索引或者范围来根据 IS NUL L搜索 NULL。
Select * FROM tbl_name Where key_col IS NULL;Select * FROM tbl_name Where key_col <=> NULL;Select * FROM tbl_nameWhere key_col=const1 or key_col=const2 or key_col IS NULL;
如果一个 Where 子句包括了一个 col_name IS NULL 条件，并且这个字段声明为 NOT NULL，那么这个表达式就会被优化。当字段可能无论如何都会产生 NULL 值时，就不会再做优化了；例如，当它来自一个 LEFT JOIN 中右边的一个表时。
MySQL 4.1.1或更高会对连接 col_name = expr AND col_name IS NULL 做额外的优化， 常见的就是子查询。EXPLAIN 当优化起作用时会显示 ref_or_null。
优化程序会为任何索引部分处理 IS NULL。
以下几个例子中都做优化了，假使字段 a 和 表 t2 中 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,t2Where (t1.a=t2.a or t2.a IS NULL) AND t2.b=t1.b;Select * FROM t1,t2Where t1.a=t2.a AND (t2.b=t1.b or t2.b IS NULL);Select * FROM t1,t2Where (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,t2Where (t1.a=t2.a AND t2.a IS NULL)or (t1.b=t2.b AND t2.b IS NULL);
7.2.7 MySQL 如何优化 DISTINCT
在很多情况下，DISTINCT 和 ORDER BY 一起使用时就会创建一个临时表。
注意，由于 DISTINCT 可能需要用到 GROUP BY，就需要明白MySQL在 ORDER BY 或 HAVING 子句里的字段不在选中的字段列表中时是怎么处理的。详情请看"13.9.3 GROUP BY with Hidden Fields"。
当 LIMIT row_count 和 DISTINCT 一起使用时，MySQL在找到 row_count 不同记录后就会立刻停止搜索了。
如果没有用到来自查询中任何表的字段时，MySQL在找到第一个匹配记录后就会停止搜索这些没没用到的表了。在下面的情况中，假使 t1 在 t2 前就使用了（可以通过 EXPLAIN 分析知道），MySQL就会在从 t2 中找到第一条记录后就不再读 t2 了（为了能和中 t1 的任何特定记录匹配）：
Select DISTINCT t1.a FROM t1,t2 where t1.a=t2.a;
7.2.8 MySQL 如何优化 LEFT JOIN 和 RIGHT JOIN
A LEFT JOIN B join_condition 在MySQL中实现如下：
* 表 B 依赖于表 A 以及其依赖的所有表。
* 表 A 依赖于在 LEFT JOIN 条件中的所有表（除了 B）。
* 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;
解决这种情况的方法是按照如下方式重写查询：
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;
从4.0.14开始，MySQL做如下 LEFT JOIN 优化：如果对产生的 NULL 记录 Where 条件总是 假，那么 LEFT JOIN 就会变成一个普通的连接。
例如，下面的查询中如果 t2.column1 的值是 NULL 的话，Where 子句的结果就是假了：
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.9 MySQL 如何优化 ORDER BY
在一些情况下，MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。
尽管 ORDER BY 不是和索引的顺序准确匹配，索引还是可以被用到，只要不用的索引部分和所有的额外的 ORDER BY 字段在 Where 子句中都被包括了。下列的几个查询都会使用索引来解决 ORDER BY 或 GROUP BY 部分：
Select * FROM t1 orDER BY key_part1,key_part2,... ;Select * FROM t1 Where key_part1=constant orDER BY key_part2;Select * FROM t1 Where key_part1=constant GROUP BY key_part2;Select * FROM t1 orDER BY key_part1 DESC, key_part2 DESC;Select * FROM t1Where 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 中的字段都不全是来自第一个非常数的表中（也就是说，在 EXPLAIN 分析的结果中的第一个表的连接类型不是 const）。
* 使用了不同的 ORDER BY 和 GROUP BY 表达式。
* 表索引中的记录不是按序存储。例如，HASH 和 HEAP 表就是这样。
通过执行 EXPLAIN Select ... orDER BY，就知道MySQL是否在查询中使用了索引。如果 Extra 字段的值是 Using filesort，则说明MySQL无法使用索引。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a Select)"。
当必须对结果进行排序时，MySQL 4.1 以前它使用了以下 filesort 算法：
1. 根据索引键读取记录，或者扫描数据表。那些无法匹配 Where 分句的记录都会被略过。
2. 在缓冲中每条记录都用一个‘对’存储了2个值（索引键及记录指针）。缓冲的大小依据系统变量 sort_buffer_size 的值而定。
3. 当缓冲慢了时，就运行 qsort（快速排序）并将结果存储在临时文件中。将存储的块指针保存起来（如果所有的‘对’值都能保存在缓冲中，就无需创建临时文件了）。
4. 执行上面的操作，直到所有的记录都读取出来了。
5. 做一次多重合并，将多达 MERGEBUFF（7）个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。
6. 重复以上操作，直到剩余的块数量小于 MERGEBUFF2 (15)。
7. 在最后一次多重合并时，只有记录的指针（排序索引键的最后部分）写到结果文件中去。
8. 通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。
这个逼近算法的一个问题是，数据库读取了2次记录：一次是估算 Where 分句时，第二次是排序时。尽管第一次都成功读取记录了（例如，做了一次全表扫描），第二次是随机的读取（索引键已经排好序了，但是记录并没有）。
在MySQL 4.1 及更新版本中，filesort 优化算法用于记录中不只包括索引键值和记录的位置，还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的 filesort 算法做法大致如下：
1. 跟以前一样，读取匹配 Where 分句的记录。
2. 相对于每个记录，都记录了一个对应的；‘元组’信息信息，包括索引键值、记录位置、以及查询中所需要的所有字段。
3. 根据索引键对‘元组’信息进行排序。
4. 按序读取记录，不过是从已经排序过的‘元组’列表中读取记录，而非从数据表中再读取一次。
使用改进后的 filesort 算法相比原来的，‘元组’比‘对’需要占用更长的空间，它们很少正好适合放在排序缓冲中（缓冲的大小是由 sort_buffer_size 的值决定的）。因此，这就可能需要有更多的I/O操作，导致改进的算法更慢。为了避免使之变慢，这种优化方法只用于排序‘元组’中额外的字段的大小总和超过系统变量 max_length_for_sort_data 的情况（这个变量的值设置太高的一个表象就是高磁盘负载低CPU负载）。
想要提高 ORDER BY 的速度，首先要看MySQL能否使用索引而非额外的排序过程。如果不能使用索引，可以试着遵循以下策略：
* 增加 sort_buffer_size 的值。
* 增加 read_rnd_buffer_size 的值。
* 修改 tmpdir，让它指向一个有很多剩余空间的专用文件系统。如果使用MySQL 4.1或更新，这个选项允许有多个路径用循环的格式。各个路径之间在 Unix 上用冒号（':'）分隔开来，在 Windows，NetWare以及OS/2 上用分号（';'）。可以利用这个特性将负载平均分摊给几个目录。注意：这些路径必须是分布在不同物理磁盘上的目录，而非在同一个物理磁盘上的不同目录。
默认情况下，MySQL也会对所有的 GROUP BY col1, col2, ... 查询做排序，跟 ORDER BY col1, col2, ... 查询一样。如果显式地包含一个有同样字段列表的 ORDER BY 分句，MySQL优化它的时候并不会损失速度，因为排序总是会发生。如果一个查询中包括 GROUP BY，但是想要避免对结果排序的开销，可以通过使用 ORDER BY NULL 来取消排序。例如：
Insert INTO fooSelect a, COUNT(*) FROM bar GROUP BY a orDER BY NULL;
7.2.10 MySQL 如何优化 LIMIT
在一些情况下，MySQL在碰到一个使用 LIMIT row_count 但没使用 HAVING
的查询时会做不同的处理：
* 如果只是用 LIMIT 来取得很少的一些记录， MySQL 有时会使用索引，但是更通常的情况是做一个全表扫描。
* 如果 LIMIT row_count 和 ORDER BY 一起使用，则MySQL在找到 row_count 条记录后就会停止排序了，而非对整个表进行排序。
* 当 LIMIT row_count 和 DISTINCT 一起联合起来时，MySQL在找到 row_count 条唯一记录后就不再搜索了。
* 在某些情况下， GROUP BY 可以通过按照顺序读取索引键来实现（或者在索引键上做排序）并且计算累计信息直到索引键改变了。在这种情况下，LIMIT row_count 不会计算任何非必须的 GROUP BY 值。
* 一旦MySQL将请求的记录全数发送给客户端后，它就中止查询除非使用了 SQL_CALC_FOUND_ROWS。
* LIMIT 0 总是返回一个空的结果集。这对于检查查询或者取得结果字段的类型非常有用。
* 当服务器使用临时表来处理查询，则 LIMIT row_count 可以用来计算需要多少空间。
7.2.11 如何避免全表扫描
如果MySQL需要做一次全表扫描来处理查询时，在 EXPLAIN 的结果中 type 字段的值是 ALL。在以下几种条件下，MySQL就会做全表扫描：
* 数据表是在太小了，做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。
* 没有合适用于 ON 或 Where 分句的索引字段。
* 让索引字段和常量值比较，MySQL已经计算（基于索引树）到常量覆盖了数据表的很大部分，因此做全表扫描应该会来得更快。详情请看"7.2.4 How MySQL Optimizes Where Clauses"。
* 通过其他字段使用了一个基数很小（很多记录匹配索引键值）的索引键。这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快。
对于小表来说，全表扫描通常更合适。但是对大表来说，尝试使用以下技术来避免让优化程序错误地选择全表扫描：
* 执行 ANALYZE TABLE tbl_name 更新要扫描的表的索引键分布。详情请看"14.5.2.1 ANALYZE TABLE Syntax"。
* 使用 FORCE INDEX 告诉MySQL，做全表扫描的话会比利用给定的索引更浪费资源。详情请看"14.1.7 Select Syntax"。
Select * FROM t1, t2 FORCE INDEX (index_for_column) Where t1.col_name=t2.col_name;
* 启动 mysqld 时使用参数 --max-seeks-for-key=1000 或者执行 SET max_seeks_for_key=1000 来告诉优化程序，所有的索引都不会导致超过1000次的索引搜索。请查看章节"5.2.3 Server System Variables"。
7.2.12 加速 Insert
插入一条记录花费的时间由以下几个因素决定，后面的数字大致表示影响的比例：
* 连接：（3）
* 发送查询给服务器：（2）
* 解析查询：（2）
* 插入记录：（1 x 记录大小）
* 插入索引：（1 x 索引数量）
* 关闭：（1）
这里并没有考虑初始化时打开数据表的开销，因为每次运行查询只会做这么一次。
如果是 B-tree 索引的话，随着索引数量的增加，插入记录的速度以 log N 的比例下降。
可以使用以下几种方法来提高插入速度：
* 如果要在同一个客户端在同一时间内插入很多记录，可以使用 Insert 语句附带有多个 VALUES 值。这种做法比使用单一值的 Insert 语句快多了（在一些情况下比较快）。如果是往一个非空的数据表里增加记录，可以调整变量 bulk_insert_buffer_size 的值使之更快。详情请看"5.2.3 Server System Variables"。
* 如果要从不同的客户端中插入大量记录，使用 Insert DELAYED 语句也可以提高速度。详情请看"14.1.4 Insert Syntax"。
* 对 MyISAM 而言，可以在 Select 语句正在运行时插入记录，只要这时候没有正在删除记录。
* 想要将一个文本文件加载到数据表中，可以使用 LOAD DATA INFILE。这通常是使用大量 Insert 语句的20倍。详情请看"14.1.5 LOAD DATA INFILE Syntax"。
* 通过一些额外的工作，就可能让 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 Compressed Table Characteristics"。
6. 运行 myisamchk -r -q /path/to/db/tbl_name 重建索引。创建的索引树在写入磁盘前先保存在内存中，这省去了磁盘搜索，因此速度快多了。重建后的索引树分布非常均衡。
7. 执行 FLUSH TABLES 语句或 mysqladmin flush-tables 命令。
注意，LOAD DATA INFILE 将数据插入一个空表时，也会做前接优化；主要的不同在于：运行 myisamchk 会分配更多的临时内存用于创建索引，而执行 LOAD DATA INFILE 命令则是让数据库服务器分配内存用于重建索引。从 MySQL 4.0 起，可以运行 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%。MySQL的 Insert, Update, Delete 操作都非常快，不过在一个语句中如果有超过5个插入或者更新时最好加锁以得到更好的性能。如果要一次性做很多个插入，最好是在每个循环（大约1000次）的前后加上 LOCK TABLES 和 UNLOCK TABLES，从而让其他进程也能访问数据表;这么做性能依然不错。Insert 总是比 LOAD DATA INFILE 插入数据来得慢，因为二者的实现策略有着分明的不同。
* 想要让 MyISAM 表更快，在 LOAD DATA
INFILE 和 Insert 时都可以增加系统变量 key_buffer_size 的值，详情请看"7.5.2 Tuning Server Parameters"。
7.2.13 加速 Update
Update 语句的优化和 Select 一样，只不过它多了额外的写入开销。写入的开销取决于要更新的记录数以及索引数。如果索引没有发生变化，则就无需更新。
另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做。如果锁表了，那么同时做很多次更新比分别做更新来得快多了。
注意，如果是在 MyISAM 表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分。如果经常做这个，那么偶尔做一次 OPTIMIZE TABLE 就显得非常重要了。详情请看"14.5.2.5 OPTIMIZE TABLE Syntax"。
7.2.14 加速 Delete
删除单个记录的时间和它的索引个数几乎成正比。想更快地删除记录，可以增加索引键的缓存。详情请看"7.5.2 Tuning Server Parameters"。
如果想要删除数据表的所有记录，请使用 TRUNCATE TABLE tbl_name 而不是 Delete FROM tbl_name。详情请看"14.1.9 TRUNCATE Syntax"。
7.2.15 其他优化点子
本章节列出了一些改善查询处理速度的其他点子：
* 使用永久连接到数据库，避免连接的开销。如果需要初始化很多连接，而又不能用永久连接，那么可以修改变量 thread_cache_size 的值，详情请看"7.5.2 Tuning Server Parameters"。
* 总是检查查询是否利用了表中已有的索引。在MySQL中，可以用 EXPLAIN 语句来分析。详情请看"7.2.1 EXPLAIN Syntax (Get Information About a Select)"。
* 尽量不要在经常需要更新的 MyISAM 表上用太过复杂的 Select 语句，这是为了避免在读和写之间争夺锁。
* 在 MyISAM 表中，如果没有正在删除记录，则可以在其他查询正在读取数据的同时插入记录。如果这种情况十分重要，那么就要尽量在表没有删除记录时才使用表。另一个可能的办法就是在删除一大堆记录之后执行 OPTIMIZE TABLE 语句。
* 如果总是需要按照 expr1, expr2, ... 的顺序取得记录，那么请使用 Alter TABLE ... orDER BY expr1, expr2, ... 修改表。通过这种方法扩充修改表之后，就可能获得更高的性能表现。
* 在一些情况下，让一个字段类型是 ``hashed`` ，它基于其他字段信息。如果这个字段比较短而且基本上都是唯一值的话，那么就可能会比在几个字段上使用一个大索引来得更快，很简单的就能使用这样的额外字段，如下：
Select * FROM tbl_name Where hash_col=MD5(CONCAT(col1,col2)) AND col1='constant' AND col2='constant';
* 如果 MyISAM 表经常大量修改，那么要尽量避免修改所有的变长字段（VARCHAR, BLOB，TEXT）。尽管表中只有一个变长字段，它也会采用动态记录格式的。详情请看"15 MySQL Storage Engines and Table Types"。
* 通常情况下，当数据表记录变 ``大`` 之后，将表拆分成几个不同的表并没有多大用处。访问一条记录是最大的性能点在于磁盘搜索时找到记录的第一个字节上。只要找到记录的位置后，现在的大部分磁盘对于大部分的应用程序来说都能很快的读取到记录。将 MyISAM 表拆分成多个唯一有关系的情况是，数据表中动态格式的字段（见上）就可以被修改成固定大小的记录，或者需要频繁的扫描表，但是却不需要读取出大部分的字段。详情请看"15 MySQL Storage Engines and Table Types"。
* 如果需要频繁的对一个表做基于很多字段信息的统计信息的话，那么可能新建一个表来存储这些实时更新的统计结果会更好。类似下面的更新就会非常快了：
Update tbl_name SET count_col=count_col+1 Where key_col=constant;
如果只需要表级锁（多个读/一个写），那么采用 MyISAM 存储引擎就非常重要了，例如 MyISAM 和 ISAM 表。这在很多的数据库中也会有不错的性能表现，因为行级锁管理程序在这种情况下也基本上没什么用。
* 如果需要从很大的日志表中搜集统计信息的话，可以用摘要表来代替扫描整个日志表。维护摘要表比保持 ``实时`` 的统计信息来得更快。当事情发生变化时（比如商业决策），重新建里摘要表比修改运营中的应用程序快多了。
* 如果可能，最好是分类报告 ``实时`` 还是 ``统计`` 的，报告所需要的数据只需要来自摘要表，摘要表的信息则是周期的从实时数据中产生。
* 应该认识到一个优点就是字段有默认值。当要插入的值和默认值不一致时才需要明确指定。这就省去了MySQL需要来提高插入速度这步了。
* 在一些情况下，将数据组装存储在 BLOB 类型字段中更方便。那么在应用程序中就需要增加额外的命令来组装和拆开 BLOB 字段中的值，不过这么做在一些时候就可以节省很多存储开销。这在数据无需遵从 记录-和-字段 格式的表结构是很实用。
* 通常地，应该保存所有的冗余数据（在数据库原理中叫做"第三范式"）。然而，为了能取得更高的效率复制一些信息或者创建摘要表也是划算的。
* 存储过程或者 UDFs（用户定义函数） 的方式在执行一些任务时可能性能更高。尽管如此，当数据库不支持这些特性时，还是有其他的替代方法可以达到目的，即使它们有点慢。
* 可以从查询缓存或应答中取得结果，然后将很多次的插入及更新操作放在一起做。如果数据库支持表锁（如MySQL和ORACLE），那么这就可以确保索引缓存在所有的更新操作之后只需要刷新一次。
* 当不需要直到数据什么时候写入表中时，可以用 Insert DELAYED。这就会提高速度，因为多条记录同时在一起做一次磁盘写入操作。
* 当想让 Select 语句的优先级比插入操作还高时，用 Insert LOW_PRIORITY。
* 用 Select HIGH_PRIORITY 来使检索记录跳过队列，也就是说即使有其他客户端正要写入数据，也会先让 Select 执行完。
* 在一条 Insert 语句中采用多重记录插入格式（很多数据库都支持）。
* 用 LOAD DATA INFILE 来导入大量数据，这比 Insert 快。
* 用 AUTO_INCREMENT 字段来生成唯一值。
* 定期执行 OPTIMIZE TABLE 防止使用动态记录格式的 MyISAM 表产生碎片。详情请看"15.1.3 MyISAM Table Storage Formats"。
* 采用 HEAP 表，它可能会提高速度。详情请看"15.1.3 MyISAM Table Storage Formats"。
* 正常的WEB服务器配置中，图片文件最好以文件方式存储，只在数据库中保存文件的索引信息。这么做的原因是，通常情况下WEB服务器对于文件的缓存总是做的比数据库来得好，因此使用文件存储会让系统更容易变得更快。
* 对于频繁访问的不是很重要的数据，可以保存在内存表中，例如对那些web客户端不能保存cookies时用于保存最后一次显示的标题等信息。
* 在不同表中值相同的字段应该将它们声明为一样的类型。在 MySQL 3.23 之前，不这么做的话在表连接时就会比较慢。让字段名尽可能简单，例如，在一个叫做 customer 的表中，用 name 来代替 customer_name 作为字段名。为了让字段名在其他数据库系统中也能移植，应该保持在18个字符长度以内。
* 如果需要真正的高速，建议看看各种数据库服务器支持的底层数据存储接口之间的区别。例如，通过直接访问MySQL的 MyISAM 存储引擎，会比通过其他的SQL接口快2-5倍。这要求数据必须和应用程序在同一个服务器上，并且它通常只被一个进程访问（因为外部文件锁确实慢）。只用一个进程就可以消除在MySQL服务器上引入底层的 MyISAM 指令引发的问题了（这容易获得更高性能，如果需要的话）。由于数据库接口设计的比较细心，就很容易支持这种优化方式了。
* 如果使用数字型数据的话，在很多情况下想要访问数据库（使用在线连接）的信息会比采用文本文件来得快。由于数字型信息相比文本文件在数据库中存储的更加紧凑，因此访问时只需要更少的磁盘搜索。而且在应用程序中也可以节省代码，因为无需解析文本文件以找到对应的行和字段。
* 数据库复制对一些操作会有性能上的益处。可以将客户端从多个复制服务器上取得数据，这就能将负载分摊了。为了避免备份数据时会让主服务器变慢，还可以将备份放在从服务器上。详情请看"6 Replication in MySQL"。
* 定义 MyISAM 表时增加选项 DELAY_KEY_WRITE=1，这样的话就会另索引更新更快，因为只有等到数据表关闭了才会刷新磁盘。不过缺点是可能会在数据表还打开时服务器被杀死，可以使用参数 --myisam-recover 来保证数据的安全，或者在数据库重启前运行 myisamchk 命令（尽管如此，在这种情况下，使用 DELAY_KEY_WRITE 的话也不会丢失任何东西，因为索引总是可以从数据中重新生成）。 7.3 锁
7.3.1 锁机制
当前MySQL已经支持 ISAM, MyISAM, MEMORY (HEAP) 类型表的表级锁了，BDB 表支持页级锁，InnoDB 表支持行级锁。
很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。
想要决定是否需要采用一个支持行级锁的存储引擎，就要看看应用程序都要做什么，其中的查询、更新语句是怎么用的。例如，很多的web应用程序大量的做查询，很少删除，主要是基于索引的更新，只往特定的表中插入记录。采用基本的MySQL MyISAM 表就很合适了。
MySQL中对表级锁的存储引擎来说是释放死锁的。避免死锁可以这样做到：在任何查询之前先请求锁，并且按照请求的顺序锁表。
MySQL中用于 WRITE（写） 的表锁的实现机制如下：
* 如果表没有加锁，那么就加一个写锁。
* 否则的话，将请求放到写锁队列中。
MySQL中用于 READ（读） 的表锁的实现机制如下：
* 如果表没有加写锁，那么就加一个读锁。
* 否则的话，将请求放到读锁队列中。
当锁释放后，写锁队列中的线程可以用这个锁资源，然后才轮到读锁队列中的线程。
这就是说，如果表里有很多更新操作的话，那么 Select 必须等到所有的更新都完成了之后才能开始。
从 MySQL 3.23.33 开始，可以通过状态变量 Table_locks_waited 和 Table_locks_immediate 来分析系统中的锁表争夺情况：
mysql> SHOW STATUS LIKE 'Table%';+-----------------------+---------+| Variable_name | Value |+-----------------------+---------+| Table_locks_immediate | 1151552 || Table_locks_waited | 15324 |+-----------------------+---------+
在 MySQL 3.23.7（在Windows上是3.23.25）以后，在 MyISAM 表中只要没有冲突的 Insert 操作，就可以无需使用锁表自由地并行执行 Insert 和 Select 语句。也就是说，可以在其它客户端正在读取 MyISAM 表记录的同时时插入新记录。如果数据文件的中间没有空余的磁盘块的话，就不会发生冲突了，因为这种情况下所有的新记录都会写在数据文件的末尾（当在表的中间做删除或者更新操作时，就可能导致空洞）。当空洞被新数据填充后，并行插入特性就会自动重新被启用了。
如果想要在一个表上做大量的 Insert 和 Select 操作，但是并行的插入却不可能时，可以将记录插入到临时表中，然后定期将临时表中的数据更新到实际的表里。可以用以下命令实现：
mysql> LOCK TABLES real_table WRITE, insert_table WRITE;mysql> Insert INTO real_table Select * FROM insert_table;mysql> TRUNCATE TABLE insert_table;mysql> UNLOCK TABLES;
InnoDB 使用行级锁，BDB 使用页级锁。对于 InnoDB 和 BDB 存储引擎来说，是可能产生死锁的。这是因为 InnoDB 会自动捕获行锁，BDB 会在执行 SQL 语句时捕获页锁的，而不是在事务的开始就这么做。
行级锁的优点有：
* 在很多线程请求不同记录时减少冲突锁。
* 事务回滚时减少改变数据。
* 使长时间对单独的一行记录加锁成为可能。
行级锁的缺点有：
* 比页级锁和表级锁消耗更多的内存。
* 当在大量表中使用时，比页级锁和表级锁更慢，因为他需要请求更多的所资源。
* 当需要频繁对大部分数据做 GROUP BY 操作或者需要频繁扫描整个表时，就明显的比其它锁更糟糕。
* 使用更高层的锁的话，就能更方便的支持各种不同的类型应用程序，因为这种锁的开销比行级锁小多了。
表级锁在下列几种情况下比页级锁和行级锁更优越：
* 很多操作都是读表。
* 在严格条件的索引上读取和更新，当更新或者删除可以用单独的索引来读取得到时：
Update tbl_name SET column=value Where unique_key_col=key_value; Delete FROM tbl_name Where unique_key_col=key_value;
* Select 和 Insert 语句并发的执行，但是只有很少的 Update 和 Delete 语句。
* 很多的扫描表和对全表的 GROUP BY 操作，但是没有任何写表。
表级锁和行级锁或页级锁之间的不同之处还在于：
将同时有一个写和多个读的地方做版本（例如在MySQL中的并发插入）。也就是说，数据库/表支持根据开始访问数据时间点的不同支持各种不同的试图。其它名有：时间行程，写复制，或者是按需复制。
原文： Versioning (such as we use in MySQL for concurrent inserts) where you can have one writer at the same time as many readers. This means that the database/table supports different views for the data depending on when you started to access it. Other names for this are time travel, copy on write, or copy on demand.
按需复制在很多情况下比页级锁或行级锁好多了。尽管如此，最坏情况时还是比其它正常锁使用了更多的内存。
可以用应用程序级锁来代替行级锁，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它们是劝告锁（原文：These are advisory locks），因此只能用于安全可信的应用程序中。
7.3.2 锁表
为了能有快速的锁，MySQL除了 InnoDB 和 BDB 这两种存储引擎外，所有的都是用表级锁（而非页、行、列级锁）。
对于 InnoDB 和 BDB 表，MySQL只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中，建议最好不要使用 LOCK TABLES，因为 InnoDB 自动采用行级锁，BDB 用页级锁来保证事务的隔离。
如果数据表很大，那么在大多数应用中表级锁会比行级锁好多了，不过这有一些陷阱。
表级锁让很多线程可以同时从数据表中读取数据，但是如果另一个线程想要写数据的话，就必须要先取得排他访问。正在更新数据时，必须要等到更新完成了，其他线程才能访问这个表。
更新操作通常认为比读取更重要，因此它的优先级更高。不过最好要先确认，数据表是否有很高的 Select 操作，而更新操作并非很‘急需’。
表锁锁在一个线程在等待，因为磁盘空间满了，但是却需要有空余的磁盘空间，这个线程才能继续处理时就有问题了。这种情况下，所有要访问这个出问题的表的线程都会被置为等待状态，直到有剩余磁盘空间了。
表锁在以下设想情况中就不利了：
* 一个客户端提交了一个需要长时间运行的 Select 操作。
* 其他客户端对同一个表提交了 Update 操作，这个客户端就要等到 Select 完成了才能开始执行。
* 其他客户端也对同一个表提交了 Select 请求。由于 Update 的优先级高于 Select，所以 Select 就会先等到 Update 完成了之后才开始执行，它也在等待第一个 Select 操作。
下列所述可以减少表锁带来的资源争夺：
* 让 Select 速度尽量快，这可能需要创建一些摘要表。
* 启动 mysqld 时使用参数 --low-priority-updates。这就会让更新操作的优先级低于 Select。这种情况下，在上面的假设中，第二个 Select 就会在 Insert 之前执行了，而且也无需等待第一个Select 了。
* 可以执行 SET LOW_PRIORITY_UpdateS=1 命令，指定所有的更新操作都放到一个指定的链接中去完成。详情请看“14.5.3.1 SET Syntax”。
* 用 LOW_PRIORITY 属性来降低 Insert，Update，Delete 的优先级。
* 用 HIGH_PRIORITY 来提高 Select 语句的优先级。详情请看“14.1.7 Select Syntax”。
* 从MySQL 3.23.7 开始，可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有 Select 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。
* 当 Insert 和 Select 一起使用出现问题时，可以转而采用 MyISAM 表，它支持并发的Select 和 Insert 操作。
* 当在同一个表上同时有插入和删除操作时，Insert DELAYED 可能会很有用。详情请看“14.1.4.2 Insert DELAYED Syntax”。
* 当 Select 和 Delete 一起使用出现问题时，Delete 的 LIMIT 参数可能会很有用。详情请看“14.1.1 Delete Syntax”
* 执行 Select 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间.详情请看“14.1.7 Select Syntax”。
* 可以修改源代码 `mysys/thr_lock.c'，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。
以下是MySQL锁的一些建议：
* 只要对同一个表没有大量的更新和查询操作混在一起，目前的用户并不是问题。
* 执行 LOCK TABLES 来提高速度（很多更新操作放在一个锁之中比没有锁的很多更新快多了）。将数据拆分开到多个表中可能也有帮助。
* 当MySQL碰到由于锁表引起的速度问题时，将表类型转换成 InnoDB 或 BDB 可能有助于提高性能。详情请看“16 The InnoDB Storage Engine”和“15.4 The BDB (BerkeleyDB) Storage Engine”。 7.4优化数据库结构
7.4.1设计选择
MySQL将记录数据和索引数据分别存放在不同的文件里。其他很多（几乎所有）数据库都将这记录和索引数据存在同一个文件中。我们相信MySQL的选择对于现在更大范围的系统更合适。
另一个存储记录数据的方法是将每个字段的信息保存在独立的区域中（例如 SDBM 和Focus）。这当每个查询都要访问不只一个字段的时候会打击性能。由于当访问越多的字段后，性能下降的越厉害，因此我们认为这种模式不适合正常目的的数据库。
更多的情况是把索引和数据保存在一起(例如 oracle/Sysbase等)。这样的话，就能在索引的叶子页面找到记录的信息。这种布局的有利之处在于，很多时候由于索引被缓存的比较好，因此就能节省磁盘读取，不过也有如下缺点：
* 由于需要通过读取索引才能得到数据，因此扫描表就更慢了。
* 查询时只能根据索引来取得数据。
* 需要更多的磁盘空间，因为必须从节点中复制索引(不能将记录保存在节点中)。
* 删除会使表变得更慢(因为删除时并没有更新节点中的索引)。
* 很难只缓存索引数据。
7.4.2让数据变得更小巧灵活
优化的最基本原则之一就是尽可能把数据表设计的占用更少磁盘空间。这能得到巨大的性能改善，因为磁盘读取比较快，并且越小的表在处理查询内容时只需更少的主内存。在小点的字段上做索引也只需更少的资源负载。
MySQL支持很多种不同的表类型以及记录格式。可以决定每个表要采用那种存储引擎/索引方式。根据不同的应用程序选择适当的表格式能大大提高性能。详情请看“15MySQL Storage Engines and Table Types”。
用以下方法可以提高表性能同时节省存储空间：
* 尽可能使用最有效(最小的)数据类型。MySQL有好几种特定的类型能节省磁盘和内存。
* 尽可能使用更小的整数类型。例如，MEDIUMINT通常比更合适 INT。
* 尽可能定义字段类型为 NOT NULL。这会运行的更快，而且每个字段都会节省1个bit。如果在应用程序中确实需要用到 NULL，那么就明确的指定它。不过要避免所有的字段默认值是 NULL。
* 在 MyISAM 表中，如果没有用到任何变长字段(VARCHAR, TEXT, 或 BLOB字段)的话，那么就采用固定大小的记录格式。这样速度更快，不过可能会浪费点空间。详情请看“15.1.3 MyISAMTable Storage Formats”。
* 表的主索引应尽可能短。这样的话会每条记录都有名字标识且更高效。
* 只创建确实需要的索引。索引有利于检索记录，但是不利于快速保存记录。如果总是要在表的组合字段上做搜索，那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段，首先就应该多复制这些字段，使索引更好的压缩。
* 一个字段很有可能在最开始的一些数量字符是各不相同的，因此在这些字符上做索引更合适。MySQL支持在一个字段的最左部分字符做索引。索引越短，速度越快，不仅是因为它占用更少的磁盘空间，也因为这提高了索引缓存的命中率，由此减少了磁盘搜索。详情请看“7.5.2Tuning Server Parameters”。
* 在某些情况下，把一个频繁扫描的表分割成两个更有利。在对动态格式表扫描以取得相关记录时，它可能使用更小的静态格式表的情况下更是如此。
7.4.3字段索引
所有的MySQL字段类型都能被索引。在相关字段上做索引对提高 Select 语句的性能最有效。
每个表的最大索引长度以及最多索引数量是由各自的存储引擎定义好了的。详情请看“15 MySQL Storage Engines and Table Types”。所有的存储引擎对每个表都至少可以支持16个索引，索引长度最小是 256 字节。大部分存储引擎的限制更高。
索引格式中使用 col_name(length) 语法，就能只对 CHAR 或 VARCHAR 字段最前面的 length 个字符做索引。象类似这样只对字段的前缀部分做索引能让索引文件更小。
MyISAM 和 InnoDB (从MySQL 4.0.14开始)存储引擎还支持在 BLOB 和 TEXT 字段上做索引，但是必须指定索引的前缀长度，例如：
Create TABLE test (blob_col BLOB, INDEX(blob_col(10)));
前缀的长度可以多达255字节(从MySQL 4.1.2开始，MyISAM 和 InnoDB 表支持1000字节)。注意，前缀长度限制是以字节数衡量的，然而 Create TABLE 语句中的前缀长度理解成为字符个数。因此在指定字段索引前缀长度时要考虑到使用多字节字符集字段的情况了。
从MySQL 3.23.23开始，就可以创建 FULLTEXT 索引了，它们使用全文搜索。只有 MyISAM 表支持对 CHAR,VARCHAR 和 TEXT 字段做 FULLTEXT 索引。只对整个字段检索有效，不支持部分(前缀)检索。详情请看“13.6 Full-Text Search Functions”。
从MySQL 4.1.0开始，还可以空间类型字段上做索引。目前，只有 MyISAM 存储引擎支持空间类型。空间索引使用R树索引。
MEMORY (HEAP) 存储引擎支持哈希索引，从MySQL 4.1.0开始，它叶支持B树索引。
7.4.4 多字段索引
MySQL可以在多个字段上创建索引，可以由多达15个字段组成。对特定的字段类型，还可以使用前缀索引(详情请看"7.4.3 Column Indexes”)。
多字段索引可以认为是由索引字段的值连接在一起而成，且经过排序之后的数组。
MySQL以如下方法使用多字段索引：在 Where 子句中指定了已知数量的索引的第一个字段，查询就很快了，甚至无需指定其他字段的值。
假定一个表结构如下：
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 一起的指定范围内查询时能起到作用。因此这个索引在以下几个查询中都会被用到：
Select * FROM test Where last_name='Widenius';Select * FROM testWhere last_name='Widenius' AND first_name='Michael';Select * FROM testWhere last_name='Widenius'AND (first_name='Michael' or first_name='Monty');Select * FROM testWhere last_name='Widenius'AND first_name >='M' AND first_name < 'N';
不过，索引 name 在以下几个查询中不会被用到：
Select * FROM test Where first_name='Michael';Select * FROM testWhere last_name='Widenius' or first_name='Michael';
关于MySQL如何使用索引来改善查询性能的方式在下个章节中具体讨论。 7.4.5 MySQL如何使用索引
索引用于快速找到特定一些值的记录。如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录。表越大，资源消耗越大。如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。
大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储，MEMORY (HEAP)表支持哈希索引。
字符串默认都是自动压缩前缀和后缀中的空格，详情请看"14.2.5 Create INDEX Syntax"。
通常，如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY 表)的独特之处在后面会讨论到。
* 想要尽快找到匹配 Where 子句的记录。
* 根据条件排除记录。如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引。
* 做表连接查询时从其他表中检索记录。
* 想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。优化程序会在检查索引的
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_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。详情请看"7.2.9
How MySQL Optimizes 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 分别有独立的索引，那么优化程序会先找到限制最多的那个索引，然后根据哪个索引能找到更少的记录就决定使用哪个索引。
如果表里有一个多字段索引的话，那么该索引的任何最左前缀部分都可以被优化程序用来检索记录。例如，在 (col1, col2, col3) 上有一个索引，那么按字段组合 (col1), (col1, col2), 和 (col1, col2,
col3) 搜索的时候都会用到索引。
MySQL无法使用非最左前缀索引中的部分索引。假如有以下 Select 语句：
Select * FROM tbl_name Where col1=val1;Select * FROM tbl_name Where col2=val2;Select * FROM tbl_name Where col2=val2 AND col3=val3;
如果在 (col1, col2, col3) 上有一个索引，只有第一个查询用到索引了。第二和第三个尽管包括了索引字段，但是 (col2) 和 (col2, col3) 并非索引 (col1, col2, col3) 的最左前缀部分。
当对字段做 =, >, >=, <, <=, 或 BETWEEN 比较操作时，也会用到索引。
MySQL在做 LIKE 比较时也可能用到索引，如果 LIKE 的参数是非通配字符开始的固定字符串的话。以下的 Select 语句就用到了索引：
Select * FROM tbl_name Where key_col LIKE 'Patrick%';Select * FROM tbl_name Where key_col LIKE 'Pat%_ck%';
第一个查询中，只有的 'Patrick' <= key_col < 'Patricl' 记录才会被检索到。第二个查询中，只检索 '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 的参数不是一个常值。
MySQL 4.0及更高会做一个额外的 LIKE 优化。如果使用 ... LIKE '%string%' 并且 string 超过3个字符，MySQL就会用 Turbo Boyer-Moore 算法来初始化模式，并且利用这个模式来加快搜索。
用 col_name IS NULL 搜索时也会使用索引，如果字段 col_name 上有索引的话。
任何在 Where 子句中没有跨越全部 AND 级分句的索引都不会用来优化查询。换言之，想要启用一个索引，那么在任何 AND 分句中都必须使用索引的前缀字段。
以下 Where 子句使用索引：
... Where index_part1=1 AND index_part2=2 AND other_column=3/* index = 1 or index = 2 */... Where index=1 or A=10 AND index=2/* 优化了 like "index_part1='hello'" */... Where index_part1='hello' AND index_part3=5/* 使用索引 index1，但没有用到 index2 或 index3 */... Where index1=1 AND index2=2 or index1=3 AND index3=3;
以下 Where 子句不使用索引：
/* 没用到 index_part1 */... Where index_part2=1 AND index_part3=2/* 所有的 AND 部分没用到索引 */... Where index=1 or A=10/* 索引没有跨越全部字段 */... Where index_part1=1 or index_part2=10
有些时候尽管有可用的索引，MySQL也不会用到它们。一种情况是优化程序认为如果使用索引会需要检索更大部分的表记录(这时候，扫描表可能更快，因为这支需要更少的搜索)。尽管如此，如果有一个查询用 LIMIT 限制只检索部分记录，MySQL就一定会使用索引，因为这样能更快检索到更少记录来返回给结果。
以下是哈希索引的一些不同的特性：
* 它们只用于 = 或 <=> 比较(但并不很快)。
* 优化程序无法使用哈希索引来加速 ORDER BY 操作(这种索引不能用于按顺序搜索下一个记录)。
* MySQL大致无法判断出介于两个值之间有多少记录(这由范围优化程序来决定使用哪个索引)。这在把 MyISAM 表类型改为采用哈希索引的 MEMORY 类型后可能会影响一些查询。
* 只有全部索引键才能用于检索记录(如果是B树索引，任何前缀部分索引也能用于检索记录)。
7.4.6 MyISAM 索引缓存
为了能最小化磁盘I/O，MyISAM 存储引擎采用了很多数据库系统使用的一种策略。它采用一种机制将最经常访问的表保存在内存区块中：
* 对索引区块来说，它维护着一个叫索引缓存(索引缓冲)的结构体。这个结构体中放着许多那些最常使用的索引区块的缓冲区块。
* 对数据区块来说，MySQL没有使用特定的缓存。它依靠操作系统的本地文件系统缓存。
本章首先描述了 MyISAM 索引缓存的基本操作。然后讨论在MySQL 4.1中所做的改进，它提高了索引缓存性能，同时能更好地控制缓存操作：
* 线程之间不再是串行地访问索引缓存。多个线程可以并行地访问索引缓存。
* 可以设置多个索引缓存，同时也能指定数据表索引到特定的缓存中。
索引缓存机制对 ISAM 表同样适用。不过，这种有效性正在减弱。自从MySQL 3.23开始 MyISAM 表类型引进之后，ISAM 就不再建议使用了。MySQL 4.1更是延续了这个趋势，ISAM 类型默认被禁用了。
可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小。如果这个值大小为0，那么就不使用缓存。当这个值小得于不足以分配区块缓冲的最小数量(8)时，也不会使用缓存。
当索引缓存无法操作时，索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之，表索引区块采用的访问策略和数据区块的一致)。
一个索引区块在 MyISAM 索引文件中是一个连续访问的单元。通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的。这个树的底部时叶子节点，叶子节点之上则是非叶子节点)。
在索引缓存结构中所有的区块大小都是一样的。这个值可能等于，大于，或小于表的索引区块大小。通常这两个值是不一样的。
当必须访问来自任何表的索引区块时，服务器首先检查在索引缓存中是否有可用的缓冲区块。如果有，服务器就访问缓存中的数据，而非磁盘。就是说，它直接存取缓存，而不是存取磁盘。否则，服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块，将它的内容替换成请求表的索引区块的拷贝。一旦新的索引区块在缓存中了，索引数据就可以存取了。
当发生被选中要替换的区块内容修改了的情况时，这个区块就被认为'脏'了。那么，在替换之前，它的内容就必须先刷新到它指向的标索引。
通常服务器遵循LRU(最近最少使用)策略：当要选择替换的区块时，它选择最近最少使用的索引区块。为了想要让选择变得更容易，索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链)。当一个区块被访问了，就把它放到队列的最后位置。当区块要被替换时，在队列开始位置的区块就是最近最少使用的，它就是第一候选删除对象。
7.4.6.1 共享访问索引缓存
在MySQL 4.1以前，访问索引缓存是串行的：两个线程不能并行地访问索引缓存缓冲。服务器处理一个访问索引区块的请求只能等它之前的请求处理完。结果，新的请求所需的索引区块就不在任何索引缓存环冲区块中，因为其他线程把包含这个索引区块的缓冲给更新了。
从MySQL 4.1.0开始，服务器支持共享方式访问索引缓存：
* 没有正在被更新的缓冲可以被多个线程访问。
* 缓冲正被更新时，需要使用这个缓冲的线程只能等到更新完成之后。
* 多个线程可以初始化需要替换缓存区块的请求，只要它们不干扰别的线程(也就是，它们请求不同的索引区块，因此不同的缓存区块被替换)。
共享方式访问索引缓存令服务器明显改善了吞吐量。
7.4.6.2 多重索引缓存
共享访问索引缓存改善了性能，却不能完全消除线程间的冲突。它们仍然争抢控制管理存取索引缓存缓冲的结构。为了更进一步减少索引缓存存取冲突，MySQL 4.1.1提供了多重索引缓存特性。这能将不同的表索引指定到不同的索引缓存。
当有多个索引缓存，服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个。默认地，所有的 MyISAM 表索引都缓存在默认的索引缓存中。想要指定到特定的缓存中，可以使用 CACHE INDEX 语句。
如下语句所示，指定表的索 t1, t2 和 t3 引缓存到名为 hot_cache 的缓存中：
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 |+---------+--------------------+----------+----------+
注意，如果服务器编译支持存 ISAM 储引擎了，那么 ISAM 表也使用索引缓存机制。不过，ISAM 表索引只能使用默认的索引缓存而不能自定义。
CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的，如下：
mysql> SET GLOBAL keycache1.key_buffer_size=128*1024;
想要删除索引缓存，只需设置它的大小为0：
mysql> SET GLOBAL keycache1.key_buffer_size=0;
索引缓存变量是一个结构体变量，由名字和组件构成。例如 keycache1.key_buffer_size， keycache1 就是缓存名，key_buffer_size 是缓存组件。详情请看"10.4.1 Structured System Variables"，它描述了构造索引缓存系统变量的使用语法。
默认地，表索引在服务器启动时指定到主(默认的)索引缓存中。当一个索引缓存被删掉后，指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去。
对一个繁忙的系统来说，我们建议以下三条策略来使用索引缓存：
* 热缓存占用20%的总缓存空间。用于繁重搜索但很少更新的表。
* 冷缓存占用20%的总缓存空间。用于中等强度更新的表，如临时表。
* 冷缓存占用60%的总缓存空间。作为默认的缓存，用于所有其他表。
使用三个缓存的一个原因是好处在于，存取一个缓存结构时不会阻止对其他缓存的访问。访问一个表索引的查询不会跟指定到其他缓存的查询竞争。性能提高还表现在以下几点原因：
* 热缓存只用于检索记录，因此它的内容总是不需要变化。所以，无论什么时候一个索引区块需要从磁盘中引入，被选中要替换的缓存区块的内容总是要先被刷新。
* 索引被指向热缓存中后，如果没有需要扫描全部索引的查询，那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中。
* 在临时表里必须频繁执行一个更新操作是相当快的，如果要被更新的节点已经在缓存中了，它无需先从磁盘中读取出来。当临时表的索引大小和冷缓存大小一样时，那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的。 7.4.6.3 中点插入策略
默认地，MySQL 4.1的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块，不过它也支持更完善的方法，叫做"中点插入策略"。
使用中点插入策略时，LRU链就被分割成两半：一个热子链，一个温子链。两半分割的点不是固定的，不过缓存管理系统会注意不让温子链部分"太短"，总是至少包括全部缓存区块的 key_cache_division_limit 比率。key_cache_division_limit 是缓存结构体变量的组件部分，因此它是每个缓存都可以设置这个参数值。
当一个索引区块从表中读入缓存时，它首先放在温子链的末尾。当达到一定的点击率(访问这个区块)后，它就提升到热子链中去。目前，要提升一个区块的点击率 (3)对每个区块来说都是一样的。将来，我们会让点击率依靠B树中对应的索引区块节点的级别：包含非叶子节点的索引区块所要求的提升点击率就低一点，包含叶子节点的B索引树的区块的值就高点。
提升起来的区块首先放在热子链的末尾。这个区块在热子链内一直循环。如果这个区块在该子链开头位置停留时间足够长了，它就会被降级回温子链。这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的。
这个阀值是这么描述的，一个索引缓存包含了 N 个区块，热子链开头的区块在低于 N*key_cache_age_threshold/100 次访问后就被移动到温子链的开头位置。它又首先成为被删除的候选对象，因为要被替换的区块还是从温子链的开头位置开始的。
中点插入策略就能在缓存中总能保持更有价值的区块。如果更喜欢采用LRU策略，只需让 key_cache_division_limit 的值低于默认值 100。
中点插入策略能帮助改善在执行需要有效扫描索引，它会将所有对应到B树中高级别的有价值的节点推出的查询时的性能。为了避免这样，就必须设定 key_cache_division_limit 远远低于100以采用中点插入策略。则在扫描索引操作时那些有价值的频繁点击的节点就会保留在热子链中了。
7.4.6.4 索引预载入
如果索引缓存中有足够的区块用来保存全部索引，或者至少足够保存全部非叶子节点，那么在使用前就载入索引缓存就很有意义了。将索引区块以十分有效的方法预载入索引缓存缓冲：从磁盘中顺序地读取索引区块。
没有预载入，查询所需的索引区块仍然需要被放到缓存中去。虽然索引区块要保留在缓存中，因为有足够的缓冲，它们可以从磁盘中随机读取到，而非顺序地。
想要预载入缓存，可以使用 LOAD INDEX INTO CACHE 语句。如下语句预载入了表 t1 和 t2 的索引节点(区块)：
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 |+---------+--------------+----------+----------+
增加修饰语 IGNORE LEAVES 就只预载入非叶子节点的索引区块。因此，上述语句加载了 t1 的全部索引区块，但是只加载 t2 的非叶子节点区块。
如果使用 CACHE INDEX 语句将索引指向一个索引缓存，将索引区块预先放到那个缓存中去。否则，索引区块只会加载到默认的缓存中去。
7.4.6.5 索引缓存大小
MySQL 4.1引进了对每个索引缓存的新变量 key_cache_block_size。这个变量可以指定每个索引缓存的区块大小。用它就可以来调整索引文件I/O操作的性能。
当读缓冲的大小和本地操作系统的I/O缓冲大小一样时，就达到了I/O操作的最高性能了。但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能。读比较大的叶子节点时，服务器会读进来很多不必要的数据，这大大阻碍了读其他叶子节点。
目前，还不能控制数据表的索引区块大小。这个大小在服务器创建索引文件 `.MYI' 时已经设定好了，它根据数据表的索引大小的定义而定。在很多时候，它设置成和I/O缓冲大小一样。在将来，可以改变它的值，并且会全面采用变量 key_cache_block_size。
7.4.6.6 重建索引缓存
索引缓存可以通过修改其参数值在任何时候重建它，例如：
mysql> SET GLOBAL cold_cache.key_buffer_size=4*1024*1024;
如果设定索引缓存的结构体变量组件变量 key_buffer_size 或 key_cache_block_size 任何一个的值和它当前的值不一样，服务器就会清空原来的缓存，在新的变量值基础上重建缓存。如果缓存中有任何的'脏'索引块，服务器会先把它们保存起来然后才重建缓存。重新设定其他的索引缓存变量并不会重建缓存。
重建缓存时，服务器会把所有的'脏'缓冲的内容先刷新到磁盘中去。之后，缓存的内容就无效了。不过，重建的时候并不阻止那些需要使用指向到缓存中的索引的查询。相反地，服务器使用本地文件系统缓存直接访问数据表索引。文件系统缓存不如索引缓存来的高效，因此，可以预见这时的查询会比较慢。一旦缓存重建完了，指向它的索引又可以使用了，同时也就不再使用文件系统缓存来访问索引了。
7.4.7 MySQL 如何统计打开的表数量
执行命令 mysqladmin status 时，可以看到类似如下结果：
Uptime: 426 Running threads: 1 Questions: 11082Reloads: 1 Open tables: 12
如果只有6个数据表时，Open tables 的值确实12，这可能会有些疑惑。
MySQL是多线程的，因此可能会有多个客户端同时发起查询某个表的请求。为了最小化多个客户端线程在同一个表上的不同状态，针对每个并发的线程单独打开数据表。这会占用一些内存，但是通常会提高性能。如果是 MyISAM 表，针对每个打开数据表的客户端都要有一个额外的文件描述符打开数据文件的情况(不过索引文件的描述符则可以在所有的线程间共享)。ISAM 存储引擎则共享这些操作。
想要了解更多，可以看下一章的详细内容"7.4.8 How MySQL Opens and Closes Tables"。
7.4.8 MySQL 如何打开和关闭数据表
系统变量 table_cache, max_connections 和 max_tmp_tables 影响着服务器保持打开的文件最大数量。提高一个或多个这些变量，就可以提高操作系统在每次处理时能打开的文件描述符限制。很多操作系统都可以增加文件打开的数量限制，不过每个系统的方法都各不一样。查阅一下操作系统文档来判断是否可以提高限制以及怎么去做。
table_cache 和 max_connections 相关。例如，有200个并发的连接，那么则必须至少有 200 * N 大小的表缓存，N 是在一个表连接中最大的表数量。同时也需要为临时表和临时文件保留一些额外的文件描述符。
请确认操作系统可以通过设定 table_cache 来处理对应数量的打开文件。如果 table_cache 设得太高了，MySQL可能会用完全部的文件描述符而拒绝新连接，就无法执行查询，变得很不可靠。因此必须要考虑到 MyISAM 存储引擎要为每个独立打开的表使用两个文件描述符。可以在启动 mysqld_safe 的时候增加 --open-files-limit 参数来提高MySQL打开文件描述符的数量。详情请看"A.2.17 File Not Found".
缓存中会保存 table_cache 个打开的表会。它的默认值是64；它可以在启动 mysqld 的时候通过修改 --table_cache 参数来改变。注意，MySQL可能会临时打开比这个数更多的表来执行查询。
在以下情况中，一个不用的表会被关闭并且从缓存中被删除：
* 如果缓存满了，并且一个线程试图打开一个不在缓存中的表。
* 如果缓存中已经包含了不止 table_cache 个表目，并且线程无需再使用该表。
* 当发生刷新表操作。当提交一个
FLUSH TABLES 语句或者执行 mysqladmin flush-tables or mysqladmin refresh 命令时就会这样。
当表缓存满了，服务器遵循以下步骤来分配一个新的缓存表目：
* 当前没使用的表都释放，依照最近最少使用的顺序。
* 如果有一个新的表要被打开，但是缓存满了且没有表被释放，缓存就临时根据需要扩充一下。
当缓存处于临时扩充状态，且有表处于从使用变成不使用状态时，就关闭这个表并且从缓存中释放。
每个并行访问都打开一个表。这意味着当有两个线程同时访问一个表，或者一个线程在同一个查询中访问两次这个表(例如，在表连接中连接自己)，那么这个表就需要被打开两次。每次并发的打开都在表缓存中请求一个表目。每个表第一次打开时都需要两个文件描述符：一个给数据文件，一个给索引文件。其他新增的对该表的打开则只需要一个文件，给数据文件。索引文件的描述符在所有的线程间是共享的。
如果使用 HANDLER tbl_name OPEN 语句打开表，则有一个专用的表对象给该线程。这个表对象不和其他线程共享，并且除非调用 HANDLER tbl_name CLOSE 语句或者线程结束，否则它不会关闭；这样的话，它就重新放回表索引中(如果索引还未满)。详情请看"14.1.3 HANDLER Syntax"。
可以检查
mysqld 的状态变量 Opened_tables 来判断表索引是否太小了：
mysql> SHOW STATUS LIKE 'Opened_tables';+---------------+-------+| Variable_name | Value |+---------------+-------+| Opened_tables | 2741 |+---------------+-------+
如果这个值比较大，不过完全没必要执行一大堆的 FLUSH
TABLES 语句。只需加大表索引缓存大小即可。详情请看"5.2.3 Server System Variables"和"5.2.4 Server Status Variables"。
7.4.9 在一些数据库中创建太多表的缺点
如果在一个目录下有很多的 MyISAM 或 ISAM 表，打开，关闭，创建等操作就会变慢。如果在很多不同的表上执行
Select 语句，当表缓存满了之后这就会有些开销，因为每个表都需要被打开，其他的必须关闭。可以加大表缓存来降低这个开销。 7.5 优化 MySQL 服务器
7.5.1 调整系统因素及启动参数
我们从系统级别的因素开始说起，因为有些方面的因素必须尽早决定才能取得较大性能改进。其他情况下，只需要快速看一下本章节即可。不过，在这个级别看看能做什么以取得更高性能更合适。
使用默认的操作系统这很重要。想要最有效地使用多CPU机器，就使用Solaris(因为它的线程实现确实很好)或Linux(因为2.2的内核对SMP有良好的支持)。请注意，老版本的Linux内核默认会有2GB文件大小限制。如果使用这样的内核而文件又确实需要大于2GB，那么就必须对ext2文件系统打大文件支持(LFS)补丁。其他文件系统诸如 ReiserFS 和 XFS 则没有这个限制。
在MySQL投入生产之前，我们建议你在欲使用的平台上先做一下测试。
其他tips：
* 如果有足够的RAM(随机存储器)，则应该去掉所有的交换设备。有些操作系统在一些情景中尽管有剩余内存也会使用交换设备。
* 使用MySQL选项 --skip-external-locking 来避免外部锁。从MySQL 4.0开始，这个选项默认是打开的。在这之前，只有编译支持
MIT-pthreads 才能默认打开，因为在所有平台上的MIT-pthreads 不能全部都支持 flock()。这在Linux上也是默认打开的，因为Linux的文件锁还不安全。注意，--skip-external-locking 选项在服务器运行时并不会影响其功能性。只要记住在运行
myisamchk 前要关闭服务器(或者锁定并且刷新相关数据表)。在一些操作系统上这个选项是强制的，因为外部锁在任何情况下都无法使用。不能使用 --skip-external-locking 选项的唯一情况是：在同一个数据上运行多个MySQL服务器(不是客户端)，或者运行
myisamchk 检查(不是修复)数据表前没有先告诉服务器要刷新并且锁定该表。使用 --skip-external-locking 选项后依旧可以使用 LOCK TABLES 和 UNLOCK TABLES 语句。
7.5.2 调整服务器参数
可以使用以下 mysqld 命令(在MySQL 4.1以前，忽略 --verbose)来确定默认的缓冲大小：
shell> mysqld --verbose --help
这个命令产生了所有的 mysqld 选项以及可以配置的系统变量列表。结果中包括默认值，看起来像是如下：
back_log current value: 5bdb_cache_size current value: 1048540binlog_cache_size current value: 32768connect_timeout current value: 5delayed_insert_limit current value: 100delayed_insert_timeout current value: 300delayed_queue_size current value: 1000flush_time current value: 0interactive_timeout current value: 28800join_buffer_size current value: 131072key_buffer_size current value: 1048540long_query_time current value: 10lower_case_table_names current value: 0max_allowed_packet current value: 1048576max_binlog_cache_size current value: 4294967295max_connect_errors current value: 10max_connections current value: 100max_delayed_threads current value: 20max_heap_table_size current value: 16777216max_join_size current value: 4294967295max_sort_length current value: 1024max_tmp_tables current value: 32max_write_lock_count current value: 4294967295myisam_sort_buffer_size current value: 8388608net_buffer_length current value: 16384net_read_timeout current value: 30net_retry_count current value: 10net_write_timeout current value: 60read_buffer_size current value: 131072read_rnd_buffer_size current value: 262144slow_launch_time current value: 2sort_buffer current value: 2097116table_cache current value: 64thread_concurrency current value: 10thread_stack current value: 131072tmp_table_size current value: 1048576wait_timeout current value: 28800
如果当前有 mysqld 服务器在运行，可以连接上去用以下命令来查看实际使用的系统变量：
mysql> SHOW VARIABLES;
也可以用以下语句来查看运行中的系统的统计结果及状态报告：
mysql> SHOW STATUS;
系统变量以及状态信息也可以通过
mysqladmin 来得到：
shell> mysqladmin variablesshell> mysqladmin extended-status
在章节"5.2.3 Server System Variables"和"5.2.4 Server Status Variables"中可以找到全部的系统描述及状态变量。
MySQL使用的算法有高伸缩性，因此它通常可以只使用很少内存就能运行。不过，给MySQL更多的内存通常能取得更好的性能。
调整MySQL服务器时，两个最重要的变量就是 key_buffer_size 和 table_cache。在试图修改其他变量前应该首先确认已经合理设定这两个变量了。
以下例子展示了在不同的运行时配置一些典型的变量值。这些例子使用 mysqld_safe 脚本和 --var_name=value 语法来设定变量 var_name 的值为 value。这个语法在MySQL 4.0以后就可以用了，在旧版本的MySQL中，考虑到如下一些不同之处：
* 使用 safe_mysqld 脚本而非 mysqld_safe。
* 使用 --set-variable=var_name=value 或 -O var_name=value 语法来设置变量。
* 如果变量名以 _size 结尾，就必须去掉 _size。例如，一个旧变量名为 sort_buffer_size 就是 sort_buffer，旧变量名read_buffer_size 就是 record_buffer。用 mysqld --help来要看那些变量是当前服务器版本可以识别的。
如果至少有256MB内存，且有大量的数据表，还想要在有中等数量的客户端连接时能有最大性能，可以这么设定：
shell> mysqld_safe --key_buffer_size=64M --table_cache=256 \--sort_buffer_size=4M --read_buffer_size=1M &
如果只有128MB内存，且只有少量表，但是需要做大量的排序，可以这么设定：
shell> mysqld_safe --key_buffer_size=16M --sort_buffer_size=1M
如果有大量的并发连接，除非 mysqld 已经设置成对每次连接只是用很少的内存，否则可能发生交换问题。mysqld 在对每次连接都有足够内存时性能更好。
如果只有很少内存且有大量连接，可以这么设定：
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=100K \--read_buffer_size=100K &
甚至这样：
shell> mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \--table_cache=32 --read_buffer_size=8K \--net_buffer_length=1K &
如果在一个比可用内存大很多的标上做 GROUP BY 或 ORDER BY 操作时，那么最好加大 read_rnd_buffer_size 的值以加速排序操作后的读数据。
安装MySQL后，在 `support-files' 目录下会有一些不同的 `my.cnf' 样例文件： `my-huge.cnf', `my-large.cnf', `my-medium.cnf' 和 `my-small.cnf'。可以把它们作为优化系统的蓝本。
注意，如果是通过命令行给 mysqld 或 mysqld_safe 指定参数，那么它只在那次启动服务器时有效。想要让这些选项在服务器启动时都有效，可以把它们放到配置文件中。
想要看参数改变后的效果，可以用以下方法(在MySQL 4.1以前，忽略 --verbose)：
shell> mysqld --key_buffer_size=32M --verbose --help
这个变量就会在结果的靠近末尾列出来。确认 --verbose 和 --help 选项是放在最后面，否则，在命令行上列出来的结果中在它们之后的其他选项效果就不会被反映出来了。
关于调整 InnoDB 存储引擎的详细信息请参考"16.12 InnoDB Performance Tuning Tips"。
7.5.3 控制查询优化性能
查询优化程序的任务就是找到最佳的执行SQL查询的方法。因为"好"和"坏"方法之间的性能差异可能有数量级上的区别(也就是说，秒相对小时，甚至是天)， MySQL中的大部分查询优化程序或多或少会穷举搜索可能的优化方法，从中找到最佳的方法来执行。拿连接查询来说，MySQL优化程序搜索的可能方法会随着查询中引用表数量的增加而指数增加。如果表数量较少(通常少于7-10个)，那么这基本上不是问题。不过，当提交一个很大的查询时，服务器的性能主要瓶颈很容易就花费在优化查询上。
MySQL 5.0.1引进了一个更灵活的方法，它允许用户控制在查询优化程序穷举搜索最佳优化方法的数量。一般的考虑是，优化程序搜索的方法越少，那么在编译查询时耗费的时间就越少。另一个方面，由于优化程序可能会忽略一些方法，因此可能错过找到最佳优化方法。
关于控制优化程序评估优化方法的数量可以通过以下两个系统变量：
* 变量 optimizer_prune_level 告诉优化程序在估算要访问的每个表的记录数基础上忽略一定数量的方法。我们的经验表明，这种"学习猜测"方法很少会错过最佳方法，因为它可能戏剧性地减少编译时间。这就是为什么这个选项默认是打开的(optimizer_prune_level=1)。不过，如果确信优化程序会错过更好的方法，这个选项可以关上(optimizer_prune_level=0)，不过要注意编译查询的时间可能会更长了。要注意尽管是用了这种试探方法，优化程序仍会调查指数级的方法。
* 变量 optimizer_search_depth
告诉优化程序"将来"的每次顺序调查不完全的方法是否需要扩充的更远的深度。optimizer_search_depth 的值越小，可能会导致查询编译时间的越少。例如，有一个12-13或更多表的查询很容易就需要几小时甚至几天的时间来编译，如果 optimizer_search_depth 的值和表数量相近的话。同样，如果 optimizer_search_depth 的值等于3或4，则编译器可能至需要花不到几分钟的时间就完成编译了。如果不能确定 optimizer_search_depth 的值多少才合适，就把它设置为0，让优化程序来自动决定。 7.5.4 编译和连接如何影响MySQL的速度
以下的大部分测试都是在Linux上用MySQL的基准套件来做的，不过它们对其他操作系统以及不同的工作量也是有一定启示的。
用 -static 连接的话，MySQL执行速度的速度是最快的。
在Linux上，用 pgcc 和 -O3 编译的代码最快。大概需要200MB的内存加上这些选项来编译 `sql_yacc.cc`，因为 gcc/pgcc 需要大量内存来编译所有的内部函数。想要配置MySQL以避免包含 libstdc++ 库就要加上 CXX=gcc 参数，不过这不是必须的。注意，使用某些版本的 pgcc 编译的结果代码只能在奔腾处理器上运行，尽管已经给编译器增加选项想让结果代码可以在所有的x586类型(如AMD)处理器上运行。
如果使用比较好的编译器以及编译选项，应用程序大概能提高10-30%的速度。这在自己编译MySQL服务器的时候尤为重要。
我们已经测试 Cygnus CodeFusion 和 Fujitsu 编译器了，不过测试它们时，无论哪个都没有完全地解决bug以允许最佳方式编译MySQL。
标准的MySQL二进制发布包已经编译支持所有的字符集了。如果想要自己编译MySQL，只需编译支持想要使用的字符集。可以在 configure 时增加 --with-charset 选项来控制。
以下是我们已经做过的一些测量：
* 如果使用 pgcc
编译任何东西时用 -O6 选项，则 mysqld 服务器会比用 gcc 2.95.2 编译快1%。
* 如果使用动态连接(没用 -static)，则在Linux上结果会慢13%。注意，这仍然可以让你的应用程序客户端动态连接MySQL的库。这对提高服务器性能相当关键。
* 如果做 strip mysqld 了，则结果会快4%。
* 在同一个主机上连接到服务器，如果使用TCP/IP而不是Unix套接字文件，那么会慢7.5%(在Unix上，如果使用主机名 localhost 连接，MySQL默认使用套接字文件)。
* 用TCP/IP连接时，直接连接本机服务器比连接不同主机上的服务器会慢8-11%，尽管是通过100Mb/s的以太网。
* 运行基准测试时如果采用安全连接(所有的数据都用内部支持的SSL加密)则会比用不加密链接慢55%。
* 编译时增加 --with-debug=full 选项，则大部分查询都会慢20%。一些查询可能会变得很慢；例如，MySQL的基准套件可能会慢35%。如果只增加选项 --with-debug(没有
=full)，则只会慢15%。一个使用 --with-debug=full 选项编译后的 mysqld 可以在启动时增加 --skip-safemalloc 选项来禁用运行时内存检查。则执行速度就会接近于增加 --with-debug 参数时的情况。
* 在 Sun UltraSPARC-IIe 上，用 Forte 5.0 编译的服务器会比用 gcc 3.2 编译的快4%。
* 在 Sun UltraSPARC-IIe 上，用 Forte 5.0 编译的服务器在32位平台上比在64位平台上快4%。
* 在 UltraSPARC 上用 gcc 2.95.2 编译，增加
-mcpu=v8 -Wa,-xarch=v8plusa 选项的话会快4%。
* 在 Solaris 2.5.1 上，MIT-pthreads 比 Solaris 原始的单处理器线程慢8-12%。在不同的负载或者CPU下，这个差异会更大。
* 在 Linux x86上用 gcc 编译，不增加 帧指针(-fomit-frame-pointer 或 -fomit-frame-pointer -ffixed-ebp)选项 的话会慢1-4%。
MySQL AB提供的Linux上的MySQL二进制发布包通常用 pgcc 编译。不过我们又重新使用 gcc 来编译了，因为用 pgcc 编译的代码不能在AMD上运行。我们会一直使用 gcc 来编译直到 pgcc 的这个bug解决了。与此同时，如果你有非AMD机器，就可以用 pgcc 来编译以使MySQL更快。标准的MySQL Linux二进制包是静态连接的，这使得它更快且移植性更好。
7.5.5 MySQL 如何使用内存
以下展示了 mysqld 服务器使用内存的几种方法。可适用的是，和使用内存相关的系统变量名已经给出了：
* 索引缓冲(key_buffer_size 变量)在所有的线程间是共享的；服务器所需的其他缓冲已经分配好了。详情请看"7.5.2 Tuning Server Parameters"。
* 每个连接使用的一些线程特有的空间：
o 一个堆栈(默认是64KB，thread_stack 变量)。
o 一个连接缓冲(net_buffer_length 变量)。
o 一个结果缓冲(net_buffer_length 变量)。
连接缓冲和结果缓冲可根据需要动态扩大到 max_allowed_packet。一个查询正在执行时，当前查询语句的一份拷贝也需要为之分配内存。
* 所有的线程共享一样的基本内存。
* 只有压缩后的 ISAM 和 MyISAM 表才是内存映射的。这是因为32位的内存只有4GB的内存空间，对大部分大表都不够。当64位寻址的操作系统更普遍时，我们会普遍支持内存映射的。
* 每个需要顺序扫描数据表的请求都需要分配一个读缓冲(read_buffer_size 变量)。
* 如果以"随机"顺序读取记录(例如，排序之后)，一个随机-读缓冲就必须分配以避免磁盘搜索(read_rnd_buffer_size 变量)。
* 所有的表连接都在一个步骤中完成，甚至大部分连接可以不使用临时表就能完成。大部分临时表都是基于内存(HEAP 表)的。记录长度很长(所有的字段长度之和)或者包含有 BLOB 字段的临时表都会存储在磁盘中。在MySQL 3.23.2之前有一个问题是，当一个内部的内存表大小超过 tmp_table_size 后，就会产生一个 The
table tbl_name is full 错误。在MySQL 3.23.2后，这会自动处理，在必须的时候把内存表转换成基于磁盘存储的 MyISAM 表。想要在旧版本的MySQL也能正常运行，可以给 mysqld 设置 tmp_table_size 选项以增加临时表大小，或者在客户端程序中设置 SQL_BIG_TABLES 选项。详情请看"14.5.3.1 SET Syntax"。在MySQL 3.20中，临时表的大小最多为
record_buffer*16；如果你使用这个版本，就必须加大
record_buffer 的值。可以在使用
--big-tables 选项启动 mysqld 来允许将临时表保存在磁盘中。不过这会影响很多查询编译。
* 大部分需要做排序的请求都会分配排序缓冲，且视结果集大小分配0到2个临时文件。详情请看"A.4.4 Where MySQL Stores Temporary Files"。
* 几乎所有的解析和计算在本地内存存储中已经做完了。无需为小的条目耗费内存，因此通常能避免比较慢的内存分配以及释放。内存只分配给不可预料的大字符串；这由 malloc() 和 free() 来完成。
* 每个打开着的 MyISAM 和 ISAM 表，索引文件只打开一次，而数据文件则是每次为同时运行的线程打开一次。在每个当前的线程中，表结构，每个字段的结构，需要分配大小 3 * N 的缓冲(N 是记录的最大长度，不计算 BLOB 字段)。BLOB 字段需要比 BLOB 数据长度多出来5到8字节。MyISAM 和 ISAM 存储引擎维护一个额外的缓冲字段供内部使用。
* 对于每个有 BLOB 字段的表，专门为其扩展一个动态缓冲来读取大 BLOB 数据。读表的时候，就会分配一个以最大长的 BLOB 数据长度的缓冲。
* 所有的使用中的表句柄都缓存起来并且被当作FIFO来管理。默认地，这个缓存有64个表目。如果有一个表同时被两个运行着的线程使用了，那么在缓存中包含了该表的2个表目。详情请看"7.4.8 How MySQL Opens and Closes Tables"。
* FLUSH TABLES 语句或 mysqladmin flush-tables 命令会关闭所有非使用中的表，并且也会在线程结束后关闭那些使用中的表。这对释放使用中的内�