MySQL 书
时间：2009-01-06 来源：militala
数据库优化是一项很复杂的工作，因为这最终需要对系统优化的很好理解才行。尽管对系统或应用系统的了解不多的情况下优化效果还不错，但是如果想优化的效果更好，那么就需要对它了解更多才行。
本章主要讲解了几种优化MySQL的方法，并且给出了例子。记着，总有各种办法能让系统运行的更快，当然了，这需要更多的努力。
优化概述
让系统运行得快得最重要因素是数据库基本的设计。并且还必须清楚您的系统要用来做什么，以及存在的瓶颈。
最常见的系统瓶颈有以下几种：
磁盘搜索。它慢慢地在磁盘中搜索数据块。对现代磁盘来说，平时的搜索时间基本上小于10毫秒，因此理论上每秒钟可以做100次磁盘搜索。这个时间对于全新的新磁盘来说提高的不多，并且对于只有一个表的情况也是如此。加快搜索时间的方法是将数据分开存放到多个磁盘中。
磁盘读/写。当磁盘在正确的位置上时，就需要读取数据。对现代磁盘来说，磁盘吞吐量至少是10-20MB/秒。这比磁盘搜索的优化更容易，因为可以从多个媒介中并行地读取数据。
CPU周期。数据存储在主内存中(或者它已经在主内存中了)，这就需要处理这些数据以得到想要的结果。存在多个?
内存带宽。当CPU要将更多的数据存放在CPU缓存中时，主内存的带宽就是瓶颈了。在大多数系统中，这不是常见的瓶颈，不过也是要注意的一个因素。
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"。
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 的结果，可以访问：http://dev.mysql.com/tech-resources/crash-me.php. 访问 http://dev.mysql.com/tech-resources/benchmarks 可以看到基准的结果。
如果您想努力做到独立于数据库，这就需要对各种SQL服务器的瓶颈都有一些很好的想法。例如，MySQL对于 MyISAM 类型的表在检索以及更新记录时非常快，但是在有并发的慢速读取及写入记录时却有一定的问题。作为Oracle来说，它在访问刚刚被更新的记录时有很大的问题(直到结果被刷新到磁盘中)。事务数据库一般地在从日志表中生成摘要表这方面的表现不怎么好，因为在这种情况下，行记录锁几乎没用。
为了能让应用程序真正的做到独立于数据库，就必须把操作数据的接口定义的简单且可扩展。由于C++在很多系统上都可以使用，因此使用C++作为数据库的基类结果很合适。
如果使用了某些数据库独有的特定功能(比如 REPLACE 语句就只在MySQL中独有)，这就需要通过编写替代方法来在其他数据库中实现这个功能。尽管这些替代方法可能会比较慢，但是它能让其他数据库实现同样的功能。
在MySQL中，可以在查询语句中使用 /*! */ 语法来增加MySQL特有的关键字。然而在很多其他数据库中，/**/ 却被当成了注释(并且被忽略)。
如果有时候更高的性能比数据结果的精确更重要，就像在一些Web应用中那样，这可以使用一个应用层来缓存结果，这可能会有更高的性能。通过让旧数据在一定时间后过期，来合理的更新缓存。这是处理负载高峰期时的一种方法，这种情况下，可以通过加大缓存容量和过期时间直到负载趋于正常。
这种情况下，建表信息中就要包含了初始化缓存的容量以及正常刷新数据表的频率。
一个实现应用层缓存的可选方案是使用MySQL的查询缓存(query cache)。启用查询缓存后，数据库就会根据一些详情来决定哪些结果可以被重用。它大大简化了应用程序，详情请看"5.11 The MySQL Query Cache"。
.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上。
.4 MySQL 基准套件
本章本来要包括MySQL基准套件(以及 crash-me)的技术描述的，但是至今还未写。现在，您可以通过查看MySQL发布源代码 `sql-bench' 目录下的代码以及结果有一个更好的想法。
基准套件就是想告诉用户执行什么样的SQL查询表现的更好或者更差。
请注意，这个基准是单线程的，因此它度量了操作执行的最少时间。我们未来打算增加多线程测试的基准套件。
想要使用基准套件，必备以下几个条件：
基准套件在MySQL的发布源代码中就有。可以去 http://dev.mysql.com/downloads/ 下载发布版或者使用现有开发代码树(详情请看"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-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。
1.5 使用您自己的基准
请确定对您的数据库或者应用程序做基准测试，以发现它们的瓶颈所在。解决这个瓶颈(或者使用一个假的模块来代替)之后，就能很容易地找到下一个瓶颈了。即使应用程序当前总体的表现可以接受，不过还是至少要做好找到每个瓶颈的计划，说不定某天您就希望应用程序能有更好的性能。
从MySQL的基准套件中就能找到一个便携可移植的基准测试程序了。详情请看"7.1.4 The MySQL Benchmark Suite"。您可以从基准套件中的任何一个程序，做适当的修改以适合您的需要。通过整个方式，您就可以有各种不同的办法来解决问题，知道哪个程序才是最快的。
另一个基准套件是开放源码的数据库基准，可以在 http://osdb.sourceforge.net 上找到。
当系统负载十分繁重的时候，通常就会发生问题。我们就有很多客户联系我们说他们有一个(测试过的)生产系统也遭遇了负载问题。在很多情况下，性能问题归结于数据库的基本设计(例如，在高负载下扫描数据表的表现不好)、操作系统、或者程序库等因素。很多时候，这些问题在还没有正式用于生产前相对更容易解决。
为了避免发生这样的问题，最好让您的应用程序在可能的最差的负载下做基准测试!可以使用Super Smack，在 http://jeremy.zawodny.com/mysql/super-smack 可以找到。从它名字的意思就能想到，只要您愿意，它就能让您的系统死掉，因此确认只在开发系统上做测试。
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() 是一个用于检查查询语句中是否存在问题的非常好的工具。
1. EXPLAIN 语法(得到SELECT 的相关信息)
EXPLAIN tbl_name
或者:
EXPLAIN SELECT select_options
tableIN 语句可以被当作 DESCRIBE 的同义词来用，也可以用来获取一个MySQL要执行的 SELECT 语句的相关信息。EXPLAIN tbl_name 语法和 DESCRIBE tbl_name 或 SHOW COLUMNS FROM tbl_name 一样。当在一个 SELECT 语句前使用关键字 EXPLAIN 时，MYSQL会解释了即将如何运行该 SELECT 语句，它显示了表如何连接、连接的顺序等信息。本章节主要讲述了第二种 EXPLAIN 用法。
在 EXPLAIN 的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让 SELECT 运行更快。
如果由于不恰当使用索引而引起一些问题的话，可以运行 ANALYZE TABLE 来更新该表的统计信息，例如键的基数，它能帮您在优化方面做出更好的选择。
您还可以查看优化程序是否以最佳的顺序来连接数据表。为了让优化程序按照 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 结果的每行记录显示了每个表的相关信息，每行记录都包含以下几个字段：
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_name
WHERE 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_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;
ref
该表中所有符合检索值的记录都会被取出来和从上一个表中取出来的记录作联合。ref 用于连接程序使用键的最左前缀或者是该键不是 PRIMARY KEY 或 UNIQUE 索引(换句话说，就是连接程序无法根据键值只取得一条记录)的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。ref 还可以用于检索字段使用 = 操作符来比较的时候。以下的几个例子中，MySQL将使用 ref 来处理 ref_table：
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;
ref_or_null
这种连接类型类似 ref，不同的是MySQL会在检索的时候额外的搜索包含 NULL 值的记录。这种连接类型的优化是从MySQL 4.1.1开始的，它经常用于子查询。在以下的例子中，MySQL使用 ref_or_null 类型来处理 ref_table：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column 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_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);
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.id
WHERE 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.CUSTNAME
FROM tt, et, et AS et_1, do
WHERE tt.SubmitTime IS NULL
AND tt.ActualPC = et.EMPLOYID
AND tt.AssignedPC = et_1.EMPLOYID
AND tt.ClientID = do.CUSTNMBR;
在这个例子中，先做以下假设：
要比较的字段定义如下：
Table 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 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就可以在这些字段上更快的是用索引(对 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 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
分句中用不同的次序列出各个表。
2.1. 估算查询性能
在大多数情况下，可以通过统计磁盘搜索次数来估算查询的性能。对小表来说，通常情况下只需要搜索一次磁盘就能找到对应的记录(因为索引可能已经缓存起来了)。对大表来说，大致可以这么估算，它使用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 控制的。
2.2 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 上做排序)。这对于有一个唯一索引并且想根据这个索引的顺序依次读取记录的话来说是一个提高查询速度的好办法。不过要注意的是，第一次在一个大表上做排序的话将会耗费很长时间。
2.3 MySQL如何优化 WHERE 子句
这个章节讲述了优化程序如何处理 WHERE 子句。例子中使用了 SELECT 语句，但是在 DELETE 和 UPDATE 语句中对 WHERE 子句的优化是一样的。注意，关于MySQL优化的工作还在继续，因此本章节还没结束。MySQL做了很多优化工作，而不仅仅是文档中提到的这些。
MySQL的一些优化做法如下：
去除不必要的括号：
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
展开常量：
(a
-> b>5 AND b=c AND a=5
去除常量条件(在展开常量时需要)：
(B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)
-> B=5 OR B=6
常量表达示在索引中只计算一次
在单独一个表上做 COUNT(*) 而不使用 WHERE 时， 对于 MyISAM 和 HEAP 表就会直接从表信息中检索结果。在单独一个表上做任何表 NOT NULL 达式查询时也是这样做。
预先探测无效的常量表达式。MySQL会快速探测一些不可能的 SELECT 语句并且不返回任何记录。
当没用 GROUP BY 或分组函数时，HAVING 和 WHERE 合并(COUNT(), MIN() 等也是如此)。
为表连接中的每个表构造一个简洁的 WHERE 语句，以得到更快的 WHERE 计算值并且尽快跳过记录。
查询中所有的常量表都会比其他表更早读取。一个常量表符合以下几个条件：
空表或者只有一条记录。
与在一个 UNIQUE 索引、或一个 PRIMARY KEY 的 WHERE 子句一起使用的表，这里所有的索引部分和常数表达式做比较并且索引部分被定义为 NOT NULL。
以下的几个表都会被当成常量表：
SELECT * FROM t WHERE primary_key=1; |
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 key_part1,key_part2 FROM tbl_name WHERE key_part1=val; |
以下几个查询使用索引来取得经过顺序排序后的记录而无需经过独立的排序步骤：
|
2.4 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 字段包含了使用的索引的最长索引部分列表。
例如：
|
\
2.5 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_name
WHERE 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 有索引了：
|
ref_or_null 首先读取引用键，然后独立扫描键值为 NULL 的记录。
请注意，优化程序只会处理一个 IS NULL 级别。下面的查询中，MySQL只会使用键来查询表达式 (t1.a=t2.a AND t2.a IS NULL) 而无法使在 b 上使用索引部分：
|
2.6 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;
2.7 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。
2、7 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,... ; |
在另一些情况下，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无法使用索引。当必须对结果进行排序时，MySQL 4.1 以前它使用了以下 filesort 算法：
根据索引键读取记录，或者扫描数据表。那些无法匹配 WHERE 分句的记录都会被略过。
在缓冲中每条记录都用一个‘对’存储了2个值(索引键及记录指针)。缓冲的大小依据系统变量 sort_buffer_size 的值而定。
当缓冲慢了时，就运行 qsort(快速排序)并将结果存储在临时文件中。将存储的块指针保存起来(如果所有的‘对’值都能保存在缓冲中，就无需创建临时文件了)。
执行上面的操作，直到所有的记录都读取出来了。
做一次多重合并，将多达 MERGEBUFF(7)个区域的块保存在另一个临时文件中。重复这个操作，直到所有在第一个文件的块都放到第二个文件了。
重复以上操作，直到剩余的块数量小于 MERGEBUFF2 (15)。
在最后一次多重合并时，只有记录的指针(排序索引键的最后部分)写到结果文件中去。
通过读取结果文件中的记录指针来按序读取记录。想要优化这个操作，MySQL将记录指针读取放到一个大的块里，并且使用它来按序读取记录，将记录放到缓冲中。缓冲的大小由系统变量 read_rnd_buffer_size 的值而定。这个步骤的代码在源文件 `sql/records.cc' 中。
这个逼近算法的一个问题是，数据库读取了2次记录：一次是估算 WHERE 分句时，第二次是排序时。尽管第一次都成功读取记录了(例如，做了一次全表扫描)，第二次是随机的读取(索引键已经排好序了，但是记录并没有)。
在MySQL 4.1 及更新版本中，filesort 优化算法用于记录中不只包括索引键值和记录的位置，还包括查询中要求的字段。这么做避免了需要2次读取记录。改进的 filesort 算法做法大致如下：
跟以前一样，读取匹配 WHERE 分句的记录。
相对于每个记录，都记录了一个对应的;‘元组’信息信息，包括索引键值、记录位置、以及查询中所需要的所有字段。
根据索引键对‘元组’信息进行排序。
按序读取记录，不过是从已经排序过的‘元组’列表中读取记录，而非从数据表中再读取一次。
使用改进后的 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 foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
2、9 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 可以用来计算需要多少空间。INSERT INTO foo
2、10 如何避免全表扫描
如果MySQL需要做一次全表扫描来处理查询时，在 EXPLAIN 的结果中 type 字段的值是 ALL。在以下几种条件下，MySQL就会做全表扫描：
数据表实在太小了，做一次全表扫描比做索引键的查找来得快多了。当表的记录总数小于10且记录长度比较短时通常这么做。
没有合适用于 ON 或 WHERE 分句的索引字段。
让索引字段和常量值比较，MySQL已经计算(基于索引树)到常量覆盖了数据表的很大部分，因此做全表扫描应该会来得更快。详情请看"7.2.4 How MySQL Optimizes WHERE Clauses"。
通过其他字段使用了一个基数很小(很多记录匹配索引键值)的索引键。这种情况下，MySQL认为使用索引键需要大量查找，还不如全表扫描来得更快。
对于小表来说，全表扫描通常更合适。但是对大表来说，尝试使用以下技术来避免让优化程序错误地选择全表扫描：
执行 ANALYZE TABLE tbl_name 更新要扫描的表的索引键分布。
使用 FORCE INDEX 告诉MySQL，做全表扫描的话会比利用给定的索引更浪费资源。
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 来告诉优化程序，所有的索引都不会导致超
2.11 加速 INSERT
插入一条记录花费的时间由以下几个因素决定，后面的数字大致表示影响的比例：
连接：(3)
发送查询给服务器：(2)
解析查询：(2)
插入记录：(1 x 记录大小)
插入索引：(1 x 索引数量)
关闭：(1)
这里并没有考虑初始化时打开数据表的开销，因为每次运行查询只会做这么一次。
如果是 B-tree 索引的话，随着索引数量的增加，插入记录的速度以 log N 的比例下降。
可以使用以下几种方法来提高插入速度：
如果要在同一个客户端在同一时间内插入很多记录，可以使用 INSERT 语句附带有多个 VALUES 值。这种做法比使用单一值的 INSERT 语句快多了(在一些情况下比较快)。如果是往一个非空的数据表里增加记录，可以调整变量 bulk_insert_buffer_size 的值使之更快。
如果要从不同的客户端中插入大量记录，使用 INSERT DELAYED 语句也可以提高速度。
对 MyISAM 而言，可以在 SELECT 语句正在运行时插入记录，只要这时候没有正在删除记录。
想要将一个文本文件加载到数据表中，可以使用 LOAD DATA INFILE。这通常是使用大量 INSERT 语句的20倍。
通过一些额外的工作，就可能让 LOAD DATA INFILE 在数据表有大量索引的情况下运行的更快。步骤如下：
用 CREATE TABLE 随便创建一个表。
执行 FLUSH TABLES 语句或 mysqladmin flush-tables 命令。
执行 myisamchk --keys-used=0 -rq /path/to/db/tbl_name 命令，删掉数据表的所有索引。
执行 LOAD DATA INFILE，数据插入到表中，由于无需更新表索引，因此这将非常快。
如果将来只是读取改表，运行 myisampack 让数据表变得更小点。详情查看"15.1.3.3 Compressed Table Characteristics"。
运行 myisamchk -r -q /path/to/db/tbl_name 重建索引。创建的索引树在写入磁盘前先保存在内存中，这省去了磁盘搜索，因此速度快多了。重建后的索引树分布非常均衡。
执行 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 语句都完成之后，索引缓存一次性刷新到磁盘中。通常情况是，多有少次 INSERT 语句就会有多数次索引缓存刷新到磁盘中的开销。如果能在一个语句中一次性插入多个值的话，显示的锁表操作也就没必要了。对事务表而言，用 BEGIN/COMMIT 代替 LOCK TABLES 来提高速度。锁表也回降低多次连接测试的总时间，尽管每个独立连接为了等待锁的最大等待时间也会增加。例如：
Connection 1 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 的值。
2.12 加速 UPDATE
UPDATE 语句的优化和 SELECT 一样，只不过它多了额外的写入开销。写入的开销取决于要更新的记录数以及索引数。如果索引没有发生变化，则就无需更新。
另一个提高更新速度的办法是推迟更新并且把很多次更新放在后面一起做。如果锁表了，那么同时做很多次更新比分别做更新来得快多了。
注意，如果是在 MyISAM 表中使用了动态的记录格式，那么记录被更新为更长之后就可能会被拆分。如果经常做这个，那么偶尔做一次 OPTIMIZE TABLE 就显得非常重要了。详情请看"14.5.2.5 OPTIMIZE TABLE Syntax"。
2.13 加速 DELETE
删除单个记录的时间和它的索引个数几乎成正比。想更快地删除记录，可以增加索引键的缓存。详情请看"7.5.2 Tuning Server Parameters"。
如果想要删除数据表的所有记录，请使用 TRUNCATE TABLE tbl_name 而不是 DELETE FROM tbl_name。
2.14 其他优化点子
本章节列出了一些改善查询处理速度的其他点子：
使用永久连接到数据库，避免连接的开销。如果需要初始化很多连接，而又不能用永久连接，那么可以修改变量 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 表产生碎片。
采用 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 的话也不会丢失任何东西，因为索引总是可以从数据中重新生成)。
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; |
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已经支持 ISAM, MyISAM, MEMORY (HEAP) 类型表的表级锁了，BDB 表支持页级锁，InnoDB 表支持行级锁。很多时候，可以通过经验来猜测什么样的锁对应用程序更合适，不过通常很难说一个锁比别的更好，这全都要依据应用程序来决定，不同的地方可能需要不同的锁。
表级锁和行级锁或页级锁之间的不同之处还在于：
将同时有一个写和多个读的地方做版本(例如在MySQL中的并发插入)。也就是说，数据库/表支持根据开始访问数据时间点的不同支持各种不同的试图。其它名有：时间行程，写复制，或者是按需复制。
按需复制在很多情况下比页级锁或行级锁好多了。尽管如此，最坏情况时还是比其它正常锁使用了更多的内存。
可以用应用程序级锁来代替行级锁，例如MySQL中的 GET_LOCK() 和 RELEASE_LOCK()。但它们是劝告锁(原文：These are advisory locks)，因此只能用于安全可信的应用程序中。
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 命令，指定所有的更新操作都放到一个指定的链接中去完成。
用 LOW_PRIORITY 属性来降低 INSERT，UPDATE，DELETE 的优先级。
用 HIGH_PRIORITY 来提高 SELECT 语句的优先级。
从MySQL 3.23.7 开始，可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值，它能强制临时地提高表的插入数达到一个特定值后的所有 SELECT 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。
当 INSERT 和 SELECT 一起使用出现问题时，可以转而采用 MyISAM 表，它支持并发的SELECT 和 INSERT 操作。
当在同一个表上同时有插入和删除操作时，INSERT DELAYED 可能会很有用。
当 SELECT 和 DELETE 一起使用出现问题时，DELETE 的 LIMIT 参数可能会很有用。
执行 SELECT 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间.
可以修改源代码 `mysys/thr_lock.c'，只用一个所队列。这种情况下，写锁和读锁的优先级就一样了，这对一些应用可能有帮助。
以下是MySQL锁的一些建议：
只要对同一个表没有大量的更新和查询操作混在一起，目前的用户并不是问题。
执行 LOCK TABLES 来提高速度(很多更新操作放在一个锁之中比没有锁的很多更新快多了)。将数据拆分开到多个表中可能也有帮助。
当MySQL碰到由于锁表引起的速度问题时，将表类型转换成 InnoDB 或 BDB 可能有助于提高性能。
相关阅读 更多 +
