【申请加精】MySQL 5.1参考手册

luren04

限制所有中继日志在从服务器上所占用空间的上限(0值表示“无限制”)。从服务器主机硬盘空间有限时很有用。达到限制后，I/O线程停止从主服务器读取二进制日志中的事件，直到SQL线程被闭锁并且删除了部分未使用的中继日志。请注意该限制并不是绝对的：有可能SQL线程删除中继日志前需要更多的事件。在这种情况下，I/O线程将超过限制，直到SQL线程可以删除部分中继日志。(不这样做将会造成死锁）。--relay-log-space-limit的值不能小于--max-relay-logs-size(或如果--max-relay-logs-size为0，选--max-binlog-size)的值的两倍。在这种情况下，有可能I/O线程等待释放空间，因为超过了--relay-log-space-limit，但SQL线程没有要清空的中继日志，不能满足I/O线程的需求。强制I/O线程临时忽视--relay-log-space-limit。

·         --replicate-do-db=db_name

告诉从服务器限制默认数据库(由USE所选择)为db_name的语句的复制。要指定多个数据库，应多次使用该选项，每个数据库使用一次。请注意不复制跨数据库的语句，例如当已经选择了其它数据库或没有数据库时执行UPDATE some_db.some_table SET foo='bar'。如果需要跨数据库进行更新，使用--replicate-wild-do-table=db_name.%。请读取该选项列表后面的注意事项。

一个不能按照期望工作的例子：如果用--replicate-do-db=sales启动从服务器，并且在主服务器上执行下面的语句，UPDATE语句不会复制：

USE prices;UPDATE sales.january SET amount=amount+1000;如果需要跨数据库进行更新，应使用--replicate-wild-do-table=db_name.%。

“只检查默认数据库”行为的主要原因是语句自己很难知道它是否应被复制(例如，如果你正使用跨数据库的多表DELETE语句或多表UPDATE语句)。如果不需要，只检查默认数据库比检查所有数据库要快得多。

·         --replicate-do-table=db_name.tbl_name

告诉从服务器线程限制对指定表的复制。要指定多个表，应多次使用该选项，每个表使用一次。同--replicate-do-db对比，允许跨数据库更新。请读取该选项列表后面的注意事项。

·         --replicate-ignore-db=db_name

告诉从服务器不要复制默认数据库(由USE所选择)为db_name的语句。要想忽略多个数据库，应多次使用该选项，每个数据库使用一次。如果正进行跨数据库更新并且不想复制这些更新，不应使用该选项。请读取该选项后面的注意事项。

一个不能按照期望工作的例如：如果用--replicate-ignore-db=sales启动从服务器，并且在主服务器上执行下面的语句，UPDATE语句不会复制：

·                USE prices;·                UPDATE sales.january SET amount=amount+1000;如果需要跨数据库更新，应使用--replicate-wild-ignore-table=db_name.%。

·         --replicate-ignore-table=db_name.tbl_name

告诉从服务器线程不要复制更新指定表的任何语句(即使该语句可能更新其它的表)。要想忽略多个表，应多次使用该选项，每个表使用一次。同--replicate-ignore-db对比，该选项可以跨数据库进行更新。请读取该选项后面的注意事项。

·         --replicate-wild-do-table=db_name.tbl_name

告诉从服务器线程限制复制更新的表匹配指定的数据库和表名模式的语句。模式可以包含‘%’和‘_’通配符，与LIKE模式匹配操作符具有相同的含义。要指定多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。请读取该选项后面的注意事项。

例如：--replicate-wild-do-table=foo%.bar%只复制数据库名以foo开始和表名以bar开始的表的更新。

如果表名模式为%，可匹配任何表名，选项也适合数据库级语句(CREATE DATABASE、DROP DATABASE和ALTER DATABASE)。例如，如果使用--replicate-wild-do-table=foo%.%，如果数据库名匹配模式foo%，则复制数据库级语句。

要想在数据库或表名模式中包括通配符，用反斜线对它们进行转义。例如，要复制名为my_own%db的数据库的所有表，但不复制my1ownAABCdb数据库的表，应这样转义‘_’和‘%’字符：--replicate-wild-do-table=my\_own\%db。如果在命令行中使用选项，可能需要双反斜线或将选项值引起来，取决于命令解释符。例如，用bash外壳则需要输入--replicate-wild-do-table=my\\_own\\%db。

·         --replicate-wild-ignore-table=db_name.tbl_name

告诉从服务器线程不要复制表匹配给出的通配符模式的语句。要想忽略多个表，应多次使用该选项，每个表使用一次。该选项可以跨数据库进行更新。请读取该选项后面的注意事项。

例如：--replicate-wild-ignore-table=foo%.bar%不复制数据库名以foo开始和表名以bar开始的表的更新。

关于匹配如何工作的信息，参见--replicate-wild-do-table选项的描述。在选项值中包括通配符的规则与--replicate-wild-ignore-table相同。

·         --replicate-rewrite-db=from_name->to_name

告诉从服务器如果默认数据库(由USE所选择)为主服务器上的from_name，则翻译为to_name。只影响含有表的语句(不是类似CREATE DATABASE、DROP DATABASE和ALTER DATABASE的语句)，并且只有from_name为主服务器上的默认数据库时。该选项不可以跨数据库进行更新。请注意在测试--replicate-*规则之前翻译数据库名。

如果在命令行中使用该选项， ‘>’字符专用于命令解释符，应将选项值引起来。例如：

shell> mysqld --replicate-rewrite-db="olddb->newdb"·         --replicate-same-server-id

将用于从服务器上。通常可以默认设置为0以防止循环复制中的无限循环。如果设置为1，该从服务器不跳过有自己的服务器id的事件；通常只在有很少配置的情况下有用。如果使用--logs-slave-updates不能设置为1。请注意默认情况下如果有从服务器的id，服务器I/O线程不将二进制日志事件写入中继日志(该优化可以帮助节省硬盘的使用)。因此如果想要使用--replicate-same-server-id，让从服务器读取自己的SQL线程执行的事件前，一定要用该选项启动。

·         --report-host=slave_name

从服务器注册过程中报告给主服务器的主机名或IP地址。该值出现在主服务器上SHOW SLAVE HOSTS的输出中。如果不想让从服务器自己在主服务器上注册，则不设置该值。请注意从服务器连接后，主服务器仅仅从TCP/IP套接字读取从服务器的IP号是不够的。由于 NAT和其它路由问题，IP可能不合法，不能从主服务器或其它主机连接从服务器。

·         --report-port=slave_port

连接从服务器的TCP/IP端口号，从服务器注册过程中报告给主服务器。只有从服务器帧听非默认端口或如果有一个特殊隧道供主服务器或其它客户连接从服务器时才设置它。如果你不确定，不设置该选项。

·         --skip-slave-start

告诉从服务器当服务器启动时不启动从服务器线程。使用START SLAVE语句在以后启动线程。

·         --slave_compressed_protocol={0|1}

如果该选项设置为 1，如果从服务器和主服务器均支持，使用压缩从服务器/主服务器协议。

·         --slave-load-tmpdir=file_name

从服务器创建临时文件的目录名。该选项默认等于tmpdir系统变量的值。当从服务器SQL线程复制LOAD DATA INFILE语句时，从中继日志将待装载的文件提取到临时文件，然后将这些文件装入到表中。如果装载到主服务器上的文件很大，从服务器上的临时文件也很大。因此，建议使用该选项告诉从服务器将临时文件放到文件系统中有大量可用空间的目录下。在这种情况下，也可以使用--relay-log选项将中继日志放到该文件系统中，因为中继日志也很大。--slave-load-tmpdir应指向基于硬盘的文件系统，而非基于内存的文件系统：从服务器需要用临时文件在机器重启时用于复制LOAD DATA INFILE。系统启动过程中操作系统也不能清除该目录。

·         --slave-net-timeout=seconds

放弃读之前从主服务器等候更多数据的秒数，考虑到连接中断和尝试重新连接。超时后立即开始第1次重试。由--master-connect-retry选项控制重试之间的间隔。

·         --slave-skip-errors=[err_code1,err_code2,... | all]

通常情况，当出现错误时复制停止，这样给你一个机会手动解决数据中的不一致性问题。该选项告诉从服务器SQL线程当语句返回任何选项值中所列的错误时继续复制。

如果你不能完全理解为什么发生错误，则不要使用该选项。如果复制设置和客户程序中没有bug，并且MySQL自身也没有bug，应不会发生停止复制的错误。滥用该选项会使从服务器与主服务器不能保存同步，并且你找不到原因。

对于错误代码，你应使用从服务器错误日志中错误消息提供的编号和SHOW SLAVE STATUS的输出。服务器错误代码列于附录B：错误代码和消息。

你也可以(但不应)使用不推荐的all值忽略所有错误消息，不考虑所发生的错误。无需而言，如果使用该值，我们不能保证数据的完整性。在这种情况下，如果从服务器的数据与主服务器上的不相近请不要抱怨(或编写bug报告)。已经警告你了。

例如：

--slave-skip-errors=1062,1053--slave-skip-errors=all从服务器按下面评估--replicate-*规则，确定是否执行或忽视语句：

1.    是否有--replicate-do-db或--replicate-ignore-db规则？

·         有：测试--binlog-do-db和--binlog-ignore-db(参见5.11.3节，“二进制日志”)。测试结果是什么？

o        忽视语句：忽视并退出。

o        许可语句：不立即执行语句。推迟决策；继续下一步。

·         没有：继续下一步。

2.    我们目前正执行保存的程序或函数吗？

·         是：执行查询并退出。

·         否：继续下一步。

3.    是否有--replicate-*-table规则？

·         没有：执行查询并退出。

·         有：继续下一步并开始按所示顺序评估表规则(首先是非通配规则，然后是通配规则)。只有待更新的表根据这些规则进行比较(INSERT INTO sales SELECT * FROM prices:只有sales根据这些规则进行比较)。如果要更新几个表(多表语句)，第1个匹配的表(匹配“do”或“ignore”)获赢。也就是说，根据这些规则比较第1个表。然后，如果不能进行决策，根据这些规则比较第2个表等等。

4.    是否有--replicate-do-table规则？

·         有：表匹配吗？

o        是：执行查询并退出。

o        否：继续下一步。

·         没有：继续下一步。

5.    是否有--replicate-ignore-table规则？

·         有：表匹配吗？

o        是：忽视查询并退出。

o        否：继续下一步。

·         没有：继续下一步。

6.    是否有--replicate-wild-do-table规则？

·         有：表匹配吗？

o        是：执行查询并退出。

o        否：继续下一步。

·         没有：继续下一步。

7.    是否有--replicate-wild-ignore-table规则？

·         有：表匹配吗？

o        是：忽视查询并退出。

o        否：继续下一步。

·         没有：继续下一步。

8.    没有匹配的--replicate-*-table规则。要根据这些规则测试其它表吗？

·         是：执行循环。

·         否：我们现在已经测试了所有待更新的表，结果不能匹配任何规则。是否有--replicate-do-table或--replicate-wild-do-table规则？

o        有：有“do”规则但不匹配。忽视查询并退出。

o        没有：执行查询并退出。

luren04

Q：如果主服务器正在运行并且不想停止主服务器，怎样配置一个从服务器？

A：有多种方法。如果你在某时间点做过主服务器备份并且记录了相应快照的二进制日志名和偏移量(通过SHOW MASTER STATUS命令的输出)，采用下面的步骤：

1.    确保从服务器分配了一个唯一的服务器ID号。

2.    在从服务器上执行下面的语句，为每个选项填入适当的值：

            mysql> CHANGE MASTER TO

                ->     MASTER_HOST='master_host_name',                ->     MASTER_USER='master_user_name',                ->     MASTER_PASSWORD='master_pass',                ->     MASTER_LOG_FILE='recorded_log_file_name',              ->     MASTER_LOG_POS=recorded_log_position;3.    在从服务器上执行START SLAVE语句。

如果你没有备份主服务器，这里是一个创建备份的快速程序。所有步骤都应该在主服务器主机上执行。

1.    发出该语句：

     mysql> FLUSH TABLES WITH READ LOCK；2.    仍然加锁时，执行该命令（或它的变体）：

     shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql3.    发出该语句并且确保记录了以后用到的输出：

     mysql>SHOW MASTER STATUS；4.    释放锁：

     mysql> UNLOCK TABLES；一个可选择的方法是，转储主服务器的SQL来代替前面步骤中的二进制复制。要这样做，你可以在主服务器上使用mysqldump --master-data，以后装载SQL转储到到你的从服务器。然而，这比进行二进制复制速度慢。

不管你使用这两种方法中的那一个，当你有一个快照和记录了日志名与偏移量时，后来根据说明操作。你可以使用相同的快照建立多个从服务器。一旦你拥有主服务器的一个快照，可以等待创建一个从服务器，只要主服务器的二进制日志完整。两个能够等待的时间实际的限制是指在主服务器上保存二进制日志的可用硬盘空间和从服务器同步所用的时间。

你也可以使用LOAD DATA FROM MASTER。这是一个方便的语句，它传输一个快照到从服务器并且立即调整日志名和偏移量。将来，LOAD DATA FROM MASTER将成为创建从服务器的推荐方法。然而需要注意，它只工作在MyISAM 表上并且可能长时间持有读锁定。它并不象我们希望的那样高效率地执行。如果你有大表，执行FLUSH TABLES WITH READ LOCK语句后，这时首选方法仍然是在主服务器上制作二进制快照。

Q：从服务器需要始终连接到主服务器吗？

A：不，不需要。从服务器可以宕机或断开连接几个小时甚至几天，重新连接后获得更新信息。例如，你可以在通过拨号的链接上设置主服务器/从服务器关系，其中只是偶尔短时间内进行连接。这意味着，在任何给定时间，从服务器不能保证与主服务器同步除非你执行某些特殊的方法。将来，我们将使用选项来阻塞主服务器直到有一个从服务器同步。

Q：我怎样知道从服务器与主服务器的最新比较? 换句话说，我怎样知道从服务器复制的最后一个查询的日期？

A：你可以查看SHOW SLAVE STATUS语句的Seconds_Behind_Master列的结果。参见6.3节，“复制实施细节”。

当从服务器SQL线程执行从主服务器读取的事件时，它根据事件时间戳修改自己的时间（这是TIMESTAMP能够很好复制的原因）。在SHOW PROCESSLIST语句输出的Time列内，为从服务器SQL线程显示的秒数是最后一个复制事件的时间戳和从服务器主机的实际时间之间相差的秒数。你可以使用它来确定最后一个复制事件的日期。注意，如果你的从服务器与主服务器连接断开一个小时，然后重新连接，在SHOW PROCESSLIST结果中，你可以立即看到从服务器SQL线程的Time值为3600。这可能是因为从服务器执行的语句是一个一小时之前的。

Q：我怎样强制主服务器阻塞更新直到从服务器同步？

A：使用下面的步骤：

1.    在主服务器上，执行这些语句：

     mysql> FLUSH TABLES WITH READ LOCK;     mysql> SHOW MASTER STATUS;

记录SHOW语句的输出的日志名和偏移量。这些是复制坐标。

2.    在从服务器上，发出下面的语句，其中Master_POS_WAIT()函数的参量是前面步骤中的得到的复制坐标值：

     mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);SELECT语句阻塞直到从服务器达到指定的日志文件和偏移量。此时，从服务器与主服务器同步，语句返回。

3.    在主服务器上，发出下面的语句允许主服务器重新开始处理更新：

     mysql> UNLOCK TABLES；Q：当设置双向复制时我应该知道发出那些语句？

A：MySQL复制目前不支持主服务器和从服务器之间的任何锁定协议来保证分布式(跨服务器)更新的原子性。换句话说，这样做是可能的：客户A根据协作-主服务器1更新，同时，在它传给协作-主服务器2之前，客户B能够根据协作-主服务器2更新，这样客户A的更新与它在协作-主服务器1的更新不同。这样，当客户A根据协作-主服务器2更新时，它产生的表与在协作-主服务器1上的不同，即使所有根据协作-主服务器2的更新已经传过来。这意味着，在双向复制关系中，你不应该把两个服务器串连在一起，除非你确信任何顺序的更新是安全的，或者除非你在客户端代码中注意怎样避免更新顺序错误。

你还必须认识到从更新角度，双向复制实际上并不能显著地提高性能（或者根本不能提高性能）。两个服务器都需要做相同数量的更新，如同在一个服务器做的那样。唯一的差别是锁竞争要少，这因为源于另一个服务器的更新在一个从线程中序列化。即使这个益处可能被网络延迟抵消。

Q：怎样通过复制来提高系统的性能？

A：你应将一个服务器设置为主服务器并且将所有写指向该服务器。然后根据预算配置尽可能多的从服务器以及栈空间，并且在主服务器和从服务器之间分发读取操作。你也可以用--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL选项启动从服务器，以便在从服务器端提高速度。在这种情况下，为了提高速度，从服务器使用非事务MyISAM表来代替InnoDB和BDB表。

Q：为了使用高性能的复制，我应该在自己的应用程序中怎样准备客户端代码？

A：如果你的代码中数据库访问部分已经正确地模块化，应该能够平滑和容易地转换为在复制步骤中运行的代码。仅需要更改数据库访问执行部分，以便发送所有的写操作到主服务器，以及发送读操作到主服务器或某个从服务器。如果你的代码没有这个级别，设置一个复制系统以便清除。应先通过下面的函数创建一个包装库或模块：

·         safe_writer_connect()

·         safe_reader_connect()

·         safe_reader_statement()

·         safe_writer_statement()

每个函数名的safe_意味着函数比较小心地处理所有错误。你可以使用不同名的函数。重要是对于读连接、写连接、读和写有一个统一的接口。

然后，你应该转换客户端代码使用包装库。刚开始这可能是痛苦和恐慌的过程，但从长远来看是值得的。使用刚才讨论的方法的所有应用程序都能够利用主服务器/从服务器配置的优越性，即使是含有多个从服务器的配置。代码非常容易维护，并且添加排错选项也很容易。你仅需要修改一两个函数；例如，记录每个语句执行的时间，或者你的上千个语句中哪个语句发生了错误。

如果你已经编写了许多代码，你可能想使用replace工具自动进行转换，该工具随标准MySQL一起发布，或可以自己编写转换脚本。理想情况，你的代码使用一致的程序转换风格。否则，可能最好重新编写代码，或者至少手工对其进行规则化以使用一致的风格。

Q：MySQL复制能够何时和多大程度提高系统性能？

A：MySQL复制对于频繁读和频繁写的系统具有最大好处。理论上，通过使用单个主服务器/多从服务器设置，可以通过添加更多的从服务器来扩充系统，直到用完网络带宽，或者你的更新负载已经增长到主服务器不能处理的点。

在获得的收益开始吃平之前，为了确定可以有多少从服务器，以及可以将你的站点的性能提高多少，需要知道查询模式，并且要通过基准测试并根据经验确定一个典型的主服务器和从服务器中的读取（每秒钟读取量，或者max_reads）吞吐量和写（max_writes）吞吐量的关系。通过一个假设的带有复制的系统，本例给出了一个非常简单的计算结果。

假设系统负载包括10%的写和90%的读取，并且我们通过基准测试确定max_reads是1200 –2 × max_writes。换句话说，如果没有写操作，系统每秒可以进行1,200次读取操作，平均写操作是平均读操作所用时间的两倍，并且关系是线性的。我们假定主服务器和每个从服务器具有相同的性能，并且我们有一个主服务器和N个从服务器。那么，对于每个服务器（主服务器或从服务器），我们有：

reads = 1200 – 2 × writes

reads = 9 × writes / (N + 1) (读取是分离的, 但是写入所有服务器)

9 × writes / (N + 1) + 2 × writes = 1200

writes = 1200 / (2 + 9/(N+1))

最后的等式表明了N个从服务器的最大写操作数，假设最大可能的读取速率是每分钟1,200次，读操作与写操作的比率是9。

如上分析可以得到下面的结论：

·         如果N = 0（这表明没有复制），系统每秒可以处理大约1200/11 = 109个写操作。

·         如果N = 1，每秒得到184个写操作。

·         如果N = 8，每秒得到400个写操作。

·         如果N = 17，每秒得到480个写操作。

·         最后，当 N 趋于无穷大（以及我们预算的负无穷大）时，可以得到非常接近每秒600个写操作，系统吞吐量增加将近5.5倍。然而，如果只用8个服务器，增加接近4倍。

请注意，这些计算假设网络带宽无穷大并忽略掉了其它一些因素，那些因素可能对系统产生重要的影响。在许多情况下，不能执行与刚才类似的计算，即如果添加N台复制从服务器，应该准确预报系统将发生哪些影响。回答下面的问题应能够帮助你确定复制是否和在多大程度上能够提高系统的性能：

·         系统上的读取/写比例是什么?

·         如果减少读取操作，一个服务器可以多处理多少写负载？

·         网络带宽可满足多少从服务器的需求?

Q：如何使用复制来提供冗余/高可用性?

A：利用目前的可用特性，必须设置一个主服务器和一个从服务器（或多个从服务器），以及写一个脚本来监视主服务器是否启动。如果主服务器失败，通知应用程序和从服务器切换主服务器。下面是一些建议：

·         告知从服务器更改其主服务器，使用CHANGE MASTER TO语句。

·         通知应用程序主服务器位置的一个很好的方法是对主服务器提供动态DNS入口。用bind可以使用nsupdate动态更新DNS。

·         应该用--logs-bin选项而不用 --logs-slave-updates选项运行从服务器。这样，一旦你在其它从服务器上发出STOP SLAVE; RESET MASTER, 以及CHANGE MASTER TO语句，该从服务器可以切换为主服务器。例如，假设有下面的设置：

·                       WC·                        \·                         v·                 WC----> M·                       / | \·                      /  |  \·                     v   v   v·                    S1   S2  S3M代表主服务器，S代表从服务器，WC代表发出数据库写和读取操作的客户；只发出数据库读取操作的客户没有给出，因为它们不需要切换。S1、S2以及S3是从服务器，用--logs-bin选项而没有用--logs-slave-updates运行。因为从服务器收到的主服务器的更新没有记录在二进制日志中，除非指定 --logs-slave-updates选项，每个从服务器上的二进制日志是空的。如果因为某些原因M 变得不可用，你可以选取一个从服务器变为新的主服务器。例如，如果你选取了S1，所有WC应该重新指向S1和S2，并且S3然后应从S1复制。

确保所有从服务器已经处理了中继日志中的所有语句。在每个从服务器上，发出STOP SLAVE IO_THREAD语句，然后检查SHOW PROCESSLIST语句的输出，直到你看到Has read all relay log。当所有从服务器都执行完这些，它们可以被重新配置为一个新的设置。在被提升为主服务器的从服务器S1上，发出STOP SLAVE和RESET MASTER语句。

在其它从服务器S2和S3上，使用STOP SLAVE和CHANGE MASTER TO MASTER_HOST='S1'（其中'S1'表示S1实际的主机名）。为CHANGE MASTER添加关于从S2或S3如何连接到S1的所有信息（user、password、port）。在CHANGE MASTER命令中，不需要指定从其读取的S1的二进制日志名或二进制日志位置：我们知道它是第1个二进制日志，位置是4，这是CHANGE MASTER命令的默认值。最后，在S2和S3上使用START SLAVE 命令。

然后，指示所有WC 把它们的语句指向S1。此后，WC发出的所有发送到S1的更新语句被写入S1的二进制日志，S1则包含M死掉之后的发送到 S1的每一个更新语句。

结果是下面的配置：

       WC      /      | WC   |  M(unavailable)  \   |   \  |    v v     S1<--S2  S3      ^       |      +-------+当 M重新启动后，你必须在M上发出相同的CHANGE MASTER语句，与在S2和S3上发出的语句一样，以便M变为S1的从服务器并且恢复在它宕机后丢失的所有WC写操作。要把 M 再次作为主服务器（例如，因为它是功能最强的机器），使用前面的步骤，好像S1不可用并且M变为一个新的主服务器一样。在这个过程中，在S1、S2以及S3作为M的从服务器之前，不要忘记在M上运行RESET MASTER。否则，它们可能拾取M变得不可用之前的旧WC写操作。

我们目前正在MySQL集成自动主服务器选择系统，但在准备好之前，你必须创建自己的监控工具。

luren04

如果你遵从了上述说明，复制设置仍然不工作，首先检查下面各项：

·         检查错误日志的消息。许多用户遇到问题后没有及时地这样做而浪费了时间。

·         主服务器记录到了二进制日志？用SHOW MASTER STATUS检查。如果已经记录，Position应为非零。如果没有记录，确认正用log-bin和server-id选项运行主服务器。

·         是否从服务器在运行？使用SHOWSHOW SLAVE STATUS检查是否slave_IO_Running和slave_SQL_Running的值均为Yes。如果不是，验证当启动从服务器时使用的选项。

·         如果从服务器正在运行，建立了与主服务器的连接吗？使用SHOW PROCESSLIST，找出I/O和SQL线程并检查它们的State列看它们如何显示。参见6.3节，“复制实施细节”。如果I/O线程状态为Connecting to master，验证主服务器上复制用户的权限、主服务器主机名、DNS设置，是否主服务器真正在运行，以及是否可以从从属服务器访问。

·         如果从服务器以前在运行但是现在已经停止，原因通常是在主服务器上成功的部分语句在从服务器上失败了。如果你正确快照了主服务器，并且从来没有不通过服务器线程修改从服务器上的数据，这种现象不应发生。如果发生，应为一个bug或你遇到了一个6.7节，“复制特性和已知问题” 描述的已知的复制限制。如果是一个bug，参见6.11节，“通报复制缺陷”查阅如何通报的说明。

·         如果某个在主服务器上成功的语句拒绝在从服务器上运行，并且不能执行完全的数据库重新同步(即删除从服务器的数据库并从主服务器复制新的快照)，尝试：

1.    确定是否从服务器的表与主服务器的不同。尽力了解发生的原因。然后让从服务器的表与主服务器的一样并运行START SLAVE。

2.    如果前面的步骤不工作或不适合，尽力了解手动更新是否安全(如果需要)，然后忽视来自主服务器的下一个语句。

3.    如果你确定可以跳过来自主服务器的下一个语句，执行下面的语句：

4.                  mysql> SET GLOBAL SQL_slave_SKIP_COUNTER = n；5.                  mysql> START SLAVE；如果来自主服务器的下一个语句不使用AUTO_INCREMENT或LAST_INSERT_ID()，n 值应为1。否则，值应为2。使用AUTO_INCREMENT或LAST_INSERT_ID()的语句使用值2的原因是它们从主服务器的二进制日志中取两个事件。

6.    如果你确保从服务器启动时完好地与主服务器同步，并且没有更新从服务器线程之外的表，则大概诧异是由于bug。如果你正运行最近的版本，请通报该问题。如果你正运行旧版本MySQL，尽力升级到最新的产品版本。

luren04

如果你确定没有用户错误，但复制仍然不工作或不稳定，则是向我们发送bug通报的时候了。我们需要尽可能从你那儿获得更多的信息已跟踪bug。请花一些时间和努力编写一份好的bug通报。

如果你有一个重复的测试案例来说明bug，请把它输入我们的bug数据库，位置为http://bugs.mysql.com/。如果你有一个“phantom”问题(不能按照期望进行复制)，则使用下面的程序：

1.    确认未包括用户错误。例如，如果你不用从服务器线程来更新从服务器，数据将不同步，并且会遇到唯一的键值违背更新。在这种情况下，从服务器线程停止并等待你手动清理表使它们同步。这不是复制问题。这是一个外部接口问题造成复制失败。

2.    用--logs-slave-updates和--logs-bin选项运行从服务器。这些选项使从服务器将从主服务器接收的更新记入自己的二进制日志。

3.    重新设置复制状态之前保存所有的证据。如果我们没有信息或只有粗略的信息，则难以或不可能跟踪问题。应搜集的证据为：

·         所有主服务器的二进制日志

·         所有从服务器的二进制日志

·         你发现问题时主服务器的SHOW MASTER STATUS的输出

·         你发现问题时主服务器的SHOW SLAVE STATUS的输出

·         主服务器和从服务器的错误日志

4.    使用mysqlbinlog检查二进制日志。下面命令应有助于发现有问题的查询，例如：

5.            shell> mysqlbinlog -j pos_from_slave_status \6.                       /path/to/log_from_slave_status | head搜集了问题的证据后，首先作为一个测试案例隔离开。然后将问题输入我们的bug数据库，位置为http://bugs.mysql.com/，应提供尽可能多的信息。

luren04

当将多个服务器配置为复制主服务器时，使用auto_increment时应采取特殊步骤以防止键值冲突，否则插入行时多个主服务器会试图使用相同的auto_increment值。

服务器变量auto_increment_increment和auto_increment_offset可以帮助协调多主服务器复制和AUTO_INCREMENT列。每个变量有一个默认的(并且是最小的)值1，最大值为65,535。

将这些变量设置为非冲突的值，当在同一个表主插入新行时，多主服务器配置主的服务器将不会与AUTO_INCREMENT值冲突。

这两个变量这样影响AUTO_INCREMENT列：

·         auto_increment_increment控制列值增加的间隔。例如：

·                mysql> SHOW VARIABLES LIKE 'auto_inc%';·                +--------------------------+-------+·                | Variable_name            | Value |·                +--------------------------+-------+·                | auto_increment_increment | 1     |·                | auto_increment_offset    | 1     |·                +--------------------------+-------+·                2 rows in set (0.00 sec)·                 ·                mysql> CREATE TABLE autoinc1 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);·                Query OK, 0 rows affected (0.04 sec)·                 ·                mysql> SET @auto_increment_increment=10;·                Query OK, 0 rows affected (0.00 sec)·                 ·                mysql> SHOW VARIABLES LIKE 'auto_inc%';·                +--------------------------+-------+·                | Variable_name            | Value |·                +--------------------------+-------+·                | auto_increment_increment | 10    |·                | auto_increment_offset    | 1     |·                +--------------------------+-------+·                2 rows in set (0.01 sec)·                 ·                mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);·                Query OK, 4 rows affected (0.00 sec)·                Records: 4  Duplicates: 0  Warnings: 0·                 ·                mysql> SELECT col FROM autoinc1;·                +-----+·                | col |·                +-----+·                |   1 |·                |  11 |·                |  21 |·                |  31 |·                +-----+·                4 rows in set (0.00 sec)(这里注明如何使用SHOW VARIABLES以获得这些变量的当前值）。

·         auto_increment_offset确定AUTO_INCREMENT列值的起点。影响到在复制设置主可以有多少主服务器(例如将该值设置为10表示设置可以支持10个服务器)。

考虑下面的命令，假定在前面所示示例中的相同的会话中执行这些命令：

mysql> SET @auto_increment_offset=5;Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE 'auto_inc%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 10    || auto_increment_offset    | 5     |+--------------------------+-------+2 rows in set (0.00 sec) mysql> CREATE TABLE autoinc2 (col INT NOT NULL AUTO_INCREMENT PRIMARY KEY);Query OK, 0 rows affected (0.06 sec) mysql> INSERT INTO autoinc2 VALUES (NULL), (NULL), (NULL), (NULL);Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0 mysql> SELECT col FROM autoinc2;+-----+| col |+-----+|   5 ||  15 ||  25 ||  35 |+-----+4 rows in set (0.02 sec)详细信息参见5.3.3节，“服务器系统变量”。

luren04

7.1. 优化概述
7.1.1. MySQL设计局限与折衷
7.1.2. 为可移植性设计应用程序
7.1.3. 我们已将MySQL用在何处？
7.1.4. MySQL基准套件
7.1.5. 使用自己的基准
7.2. 优化SELECT语句和其它查询
7.2.1. EXPLAIN语法（获取SELECT相关信息）
7.2.2. 估计查询性能
7.2.3. SELECT查询的速度
7.2.4. MySQL怎样优化WHERE子句
7.2.5. 范围优化
7.2.6. 索引合并优化
7.2.7. MySQL如何优化IS NULL
7.2.8. MySQL如何优化DISTINCT
7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN
7.2.10. MySQL如何优化嵌套Join
7.2.11. MySQL如何简化外部联合
7.2.12. MySQL如何优化ORDER BY
7.2.13. MySQL如何优化GROUP BY
7.2.14. MySQL如何优化LIMIT
7.2.15. 如何避免表扫描
7.2.16. INSERT语句的速度
7.2.17. UPDATE语句的速度
7.2.18. DELETE语句的速度
7.2.19. 其它优化技巧
7.3. 锁定事宜
7.3.1. 锁定方法
7.3.2. 表锁定事宜
7.4. 优化数据库结构
7.4.1. 设计选择
7.4.2. 使你的数据尽可能小
7.4.3. 列索引
7.4.4. 多列索引
7.4.5. MySQL如何使用索引
7.4.6. MyISAM键高速缓冲
7.4.7. MyISAM索引统计集合
7.4.8. MySQL如何计算打开的表
7.4.9. MySQL如何打开和关闭表
7.4.10. 在同一个数据库中创建多个表的缺陷
7.5. 优化MySQL服务器
7.5.1. 系统因素和启动参数的调节
7.5.2. 调节服务器参数
7.5.3. 控制查询优化器的性能
7.5.4. 编译和链接怎样影响MySQL的速度
7.5.5. MySQL如何使用内存
7.5.6. MySQL如何使用DNS
7.6. 磁盘事宜
7.6.1. 使用符号链接
优化是一个复杂的任务，因为最终要求了解整个待优化的系统。尽管可以进行局部优化而不需要了解系统或应用程序，为了优化得更好，你必须知道更多的信息。

本章解释并给出不同的优化MySQL的方法示例。但要记住总有一些其它方法使系统更快，尽管需要更多的工作。

luren04

使一个系统更快的最重要因素当然是基本设计。此外，还需要知道系统正做什么样的事情，以及瓶颈是什么。

最常见的系统瓶颈是：

磁盘搜索。需要花时间从磁盘上找到一个数据，用在现代磁盘的平均时间通常小于10ms，因此理论上我们能够每秒大约搜索1000次。这个时间在新磁盘上提高不大并且很难为一个表进行优化。优化它的方法是将数据分布在多个磁盘上。
磁盘读/写。当磁盘放入正确位置后，我们需要从中读取数据。对于现代的磁盘，一个磁盘至少传输10-20Mb/s的吞吐。这比搜索要容易优化，因为你能从多个磁盘并行地读。
CPU周期。我们将数据读入内存后，需要对它进行处理以获得我们需要的结果。表相对于内存较小是最常见的限制因素。但是对于小表，速度通常不成问题。
·         内存带宽。当CPU需要的数据超出CPU缓存时，主缓存带宽就成为内存的一个瓶颈。这在大多数系统正是一个不常见的瓶颈但是你应该知道它。

7.1.1. MySQL设计局限与折衷
当使用MyISAM存储引擎时，MySQL使用极快速的表锁定，以便允许多次读或一次写。使用该存储引擎的最大问题出现在同一个表中进行混合稳定数据流更新与慢速选择。如果这只是某些表的问题，你可以使用另一个存储引擎。参见第15章：存储引擎和表类型。

MySQL可以使用事务表和非事务表。为了更容易地让非事务表顺利工作(如果出现问题不能回滚)，MySQL采用下述规则。请注意这些规则只适用于不运行在严格模式下或为INSERT或UPDATE使用IGNORE规定程序时。

·         所有列有默认值。请注意当运行在严格SQL模式(包括TRADITIONAL SQL模式)时，必须为NOT NULL列指定默认值。

·         如果向列内插入不合适的或超出范围的值，MySQL将该列设定为“最好的可能的值”，而不是报告错误。对于数字值，为0、可能的最小值或最大值。对于字符串，为空字符串或列内可以保存的字符串。请注意当运行在严格模式或TRADITIONAL SQL模式时该行为不 适用。

·         所有表达式的计算结果返回一个表示错误状况的信号。例如，1/0返回NULL。(使用ERROR_FOR_DIVISION_BY_ZERO SQL模式可以更改该行为)。

如果正使用非事务表，不应该使用MySQL来检查列的内容。一般情况，最安全的(通常是最快的)方法径是让应用程序确保只向数据库传递合法值。

相关详细信息参见1.8.6节，“MySQL处理约束的方式”和13.2.4节，“INSERT语法”或5.3.2节，“SQL服务器模式”。

7.1.2. 为可移植性设计应用程序
因为不同SQL服务器实现了标准SQL的不同部分，需要花功夫来编写可移植的SQL应用程序。对很简单的选择/插入，很容易实现移植，但是需要的功能越多则越困难。如果想要应用程序对很多数据库系统都快，它变得更难！

为了使一个复杂应用程序可移植，你需要选择它应该工作的SQL服务器，并确定这些服务器支持什么特性。

所有数据库都有一些弱点。这就是它们不同的设计折衷导致的不同行为。

可以使用MySQL的crash-me程序来找出能用于数据库服务器选择的函数、类型和限制。crash-me并不能找出所有的特性，但是其广度仍然很合理，可以进行大约450个测试。

crash-me可以提供的一种类型的信息的例子：如果想要使用Informix或DB2，不应该使用超过18个字符的列名。

crash-me程序和MySQL基准程序是独立于数据库的。通过观察它们是如何编写的，编可以知道必须为编写独立于数据库的应用程序做什么。基准本身可在MySQL源码分发的“sql-bench”目录下找到。它们用DBI数据库接口以Perl写成。使用DBI本身即可以解决部分移植性问题，因为它提供与数据库无关的的存取方法。

关于crash-me结果，访问http://dev.mysql.com/tech-resources/crash-me.php。到http://dev.mysql.com/tech-resources/benchmarks/看这个基准的结果。

如果你为数据库的独立性而努力，需要很好地了解每个SQL服务器的瓶颈。例如，MySQL在检索和更新MyISAM表记录方面很快，但是在同一个表上混合慢速读者和写者方面有一个问题。另一方面，当你试图访问最近更新了(直到它们被刷新到磁盘上)的行时，在Oracle中有一个很大的问题。事务数据库总的来说在从记录文件表中生成总结表方面不是很好，因为在这种情况下，行锁定几乎没有用。

为了使应用程序“确实”独立于数据库，需要定义一个容易扩展的接口，用它可操纵数据。因为C++在大多数系统上可以适用，使用数据库的一个C++ 类接口是有意义的。

如果你使用某个数据库特定的功能(例如MySQL专用的REPLACE语句)，应该为SQL服务器编码一个方法以实现同样的功能。尽管慢些，但确允许其它服务器执行同样的任务。

用MySQL，可以使用/*! */语法把MySQL特定的关键词加到查询中。在/**/中的代码将被其它大多数SQL服务器视为注释(并被忽略)。

如果高性能真的比准确性更重要，就像在一些web应用程序那样，一种可行的方法是创建一个应用层，缓存所有的结果以便得到更高的性能。通过只是让旧的结果在短时间后‘过期’，能保持缓存合理地刷新。这在极高负载的情况下是相当不错的，在此情况下，能动态地增加缓存并且设定较高的过期时限直到一切恢复正常。

在这种情况下，表创建信息应该包含缓存初始大小和表刷新频率等信息。

实施应用程序缓存的一种方法是使用MySQL查询缓存。启用查询缓存后，服务器可以确定是否可以重新使用查询结果。这样简化了你的应用程序。参见5.13节，“MySQL查询高速缓冲”。

7.1.3. 我们已将MySQL用在何处？
该节描述了Mysql的早期应用程序。

在MySQL最初开发期间，MySQL的功能适合大多数客户。MySQL为瑞典的一些最大的零售商处理数据仓库。

我们从所有商店得到所有红利卡交易的每周总结，并且我们期望为所有店主提供有用的信息以帮助他们得出他们的广告战如何影响他们的顾客。

数据是相当巨量的(大约每月7百万宗交易总结)并且我们保存4-10年来的数据需要呈现给用户。我们每周从顾客那里得到请求，他们想要“立刻”访问来自该数据的新报告。

我们通过每月将所有信息存储在压缩的“交易”表中来解决它。我们有一套简单的宏/脚本用来生成来自交易表的不同条件( 产品组、顾客id，商店...)的总结表。报告是由一个进行语法分析网页的小perl脚本动态生成的网页，在脚本中执行SQL语句并且插入结果。我们很想使用PHP或mod_perl，但是那时它们还不可用。

对图形数据，我们用C语言编写了一个简单的工具，它能基于那些结果处理SQL查询结果并生成GIF图形。该工具也从分析Web网页的perl脚本中动态地执行。

在大多数情况下，一个新的报告通过简单地复制一个现有脚本并且修改其中的SQL查询来完成。在一些情况下，我们将需要把更多的列加到一个现有的总结表中或产生一个新的，但是这也相当简单，因为我们在磁盘上保存所有交易表。（目前我们大约有50G的交易表和200G的其它顾客数据)。

我们也让我们的顾客直接用ODBC访问总结表以便高级用户能自己用这些数据进行试验。

该系统工作得很好，我们可以毫无问题地用很适度的Sun Ultra SPARC工作站硬件(2x200MHz)来处理数据。该系统被逐步移植到了Linux中。

7.1.4. MySQL基准套件
本节应该包含MySQL基准套件(和crash-me)的技术描述，但是该描述还没写成。目前，你可以通过在MySQL源码分发中的“sql-bench”目录下的代码和结果了解基准套件是如何工作的。

通过基准用户可以了解一个给定的SQL实现在哪方面执行得很好或很糟糕。

注意，这个基准是单线程的，它可以测量操作执行的最小时间。我们计划将来在基准套件中添加多线程测试。

要使用基准套件，必须满足下面的要求：

·         基准套件随MySQL源码分发提供。可以从http://dev.mysql.com/downloads/下载分发，或者使用当前的开发源码树(参见2.8.3节，“从开发源码树安装”)。

·         基准脚本用Perl编写而成，使用Perl DBI模块访问数据库服务器，因此必须安装DBI。还需要为每个待测试的服务器提供服务器专用DBD驱动程序。例如，要测试MySQL、PostgreSQL和DB2，必须安装DBD::mysql、DBD:g和DBD:B2模块。参见2.13节，“Perl安装注意事项”。

获得MySQL源码分发后，可以在sql-bench目录找到基准套件。要运行基准测试，应构建MySQL，然后进入sql-bench目录并执行run-all-tests脚本：

shell> cd sql-bench

shell> perl run-all-tests --server=server_name

server_name是一个支持的服务器。要获得所有选项和支持的服务器，调用命令：

shell> perl run-all-tests --helpcrash-me脚本也位于sql-bench目录。crash-me尝试通过实际运行查询确定数据库支持的特性以及其功能和限制。例如，它确定：

·         支持什么列类型

·         支持多少索引

·         支持什么函数

·         查询可以多大

·         VARCHAR列可以多大

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

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

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

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

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

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

luren04

7.2.1. EXPLAIN语法（获取SELECT相关信息）
7.2.2. 估计查询性能
7.2.3. SELECT查询的速度
7.2.4. MySQL怎样优化WHERE子句
7.2.5. 范围优化
7.2.6. 索引合并优化
7.2.7. MySQL如何优化IS NULL
7.2.8. MySQL如何优化DISTINCT
7.2.9. MySQL如何优化LEFT JOIN和RIGHT JOIN
7.2.10. MySQL如何优化嵌套Join
7.2.11. MySQL如何简化外部联合
7.2.12. MySQL如何优化ORDER BY
7.2.13. MySQL如何优化GROUP BY
7.2.14. MySQL如何优化LIMIT
7.2.15. 如何避免表扫描
7.2.16. INSERT语句的速度
7.2.17. UPDATE语句的速度
7.2.18. DELETE语句的速度
7.2.19. 其它优化技巧

首先，影响所有语句的一个因素是：你的许可设置得越复杂，所需要的开销越多。

执行GRANT语句时使用简单的许可，当客户执行语句时，可以使MySQL降低许可检查开销。例如，如果未授予任何表级或列级权限，服务器不需要检查tables_priv和columns_priv表的内容。同样地，如果不对任何 账户进行限制，服务器不需要对资源进行统计。如果查询量很高，可以花一些时间使用简化的授权结构来降低许可检查开销。

如果你的问题是与具体MySQL表达式或函数有关，可以使用mysql客户程序所带的BENCHMARK()函数执行定时测试。其语法为BENCHMARK(loop_count,expression)。例如：

mysql> SELECT BENCHMARK(1000000,1+1)；+------------------------+| BENCHMARK(1000000,1+1) |+------------------------+|                      0 |+------------------------+1 row in set (0.32 sec)上面结果在PentiumII 400MHz系统上获得。它显示MySQL在该系统上在0.32秒内可以执行1,000,000个简单的+表达式运算。

所有MySQL函数应该被高度优化，但是总有可能有一些例外。BENCHMARK()是一个找出是否查询有问题的优秀的工具。

7.2.1. EXPLAIN语法（获取SELECT相关信息）
EXPLAIN tbl_name或：

EXPLAIN [EXTENDED] SELECT select_optionsEXPLAIN语句可以用作DESCRIBE的一个同义词，或获得关于MySQL如何执行SELECT语句的信息：

·         EXPLAIN tbl_name是DESCRIBE tbl_name或SHOW COLUMNS FROM tbl_name的一个同义词。

·         如果在SELECT语句前放上关键词EXPLAIN，MySQL将解释它如何处理SELECT，提供有关表如何联接和联接的次序。

该节解释EXPLAIN的第2个用法。

借助于EXPLAIN，可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。

如果由于使用不正确的索引出现了问题，应运行ANALYZE TABLE更新表的统计（例如关键字集的势），这样会影响优化器进行的选择。参见13.5.2.1节，“ANALYZE TABLE语法”。

还可以知道优化器是否以一个最佳次序联接表。为了强制优化器让一个SELECT语句按照表命名顺序的联接次序，语句应以STRAIGHT_JOIN而不只是SELECT开头。

EXPLAIN为用于SELECT语句中的每个表返回一行信息。表以它们在处理查询过程中将被MySQL读入的顺序被列出。MySQL用一遍扫描多次联接（single-sweep multi-join）的方式解决所有联接。这意味着MySQL从第一个表中读一行，然后找到在第二个表中的一个匹配行，然后在第3个表中等等。当所有的表处理完后，它输出选中的列并且返回表清单直到找到一个有更多的匹配行的表。从该表读入下一行并继续处理下一个表。

当使用EXTENDED关键字时，EXPLAIN产生附加信息，可以用SHOW WARNINGS浏览。该信息显示优化器限定SELECT语句中的表和列名，重写并且执行优化规则后SELECT语句是什么样子，并且还可能包括优化过程的其它注解。

EXPLAIN的每个输出行提供一个表的相关信息，并且每个行包括下面的列：

·         id

SELECT识别符。这是SELECT的查询序列号。

·         select_type

SELECT类型，可以为以下任何一种：

o        SIMPLE

简单SELECT(不使用UNION或子查询)

o        PRIMARY

最外面的SELECT

o        UNION

UNION中的第二个或后面的SELECT语句

o        DEPENDENT UNION

UNION中的第二个或后面的SELECT语句，取决于外面的查询

o        UNION RESULT

UNION的结果。

o        SUBQUERY

子查询中的第一个SELECT

o        DEPENDENT SUBQUERY

子查询中的第一个SELECT，取决于外面的查询

o        DERIVED

导出表的SELECT(FROM子句的子查询)

·         table

输出的行所引用的表。

·         type

联接类型。下面给出各种联接类型，按照从最佳类型到最坏类型进行排序：

o        system

表仅有一行(=系统表)。这是const联接类型的一个特例。

o        const

表最多有一个匹配行，它将在查询开始时被读取。因为仅有一行，在这行的列值可被优化器剩余部分认为是常数。const表很快，因为它们只读取一次！

const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。在下面的查询中，tbl_name可以用于const表：

SELECT * from tbl_name WHERE primary_key=1； SELECT * from tbl_nameWHERE primary_key_part1=1和 primary_key_part2=2；o        eq_ref

对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。

eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。

在下面的例子中，MySQL可以使用eq_ref联接来处理ref_tables：

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column    AND ref_table.key_column_part2=1;o        ref

对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀，或如果键不是UNIQUE或PRIMARY KEY（换句话说，如果联接不能基于关键字选择单个行的话），则使用ref。如果使用的键仅仅匹配少量行，该联接类型是不错的。

ref可以用于使用=或<=>操作符的带索引的列。

在下面的例子中，MySQL可以使用ref联接来处理ref_tables：

SELECT * FROM ref_table WHERE key_column=expr; SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column; SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column    AND ref_table.key_column_part2=1;o        ref_or_null

该联接类型如同ref，但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。

在下面的例子中，MySQL可以使用ref_or_null联接来处理ref_tables：

SELECT * FROM ref_tableWHERE key_column=expr OR key_column IS NULL;参见7.2.7节，“MySQL如何优化IS NULL”。

o        index_merge

该联接类型表示使用了索引合并优化方法。在这种情况下，key列包含了使用的索引的清单，key_len包含了使用的索引的最长的关键元素。详细信息参见7.2.6节，“索引合并优化”。

o        unique_subquery

该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。

o        index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)o        range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。

当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range：

SELECT * FROM tbl_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);o        index

该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。

当查询只使用作为单索引一部分的列时，MySQL可以使用该联接类型。

o        ALL

对于每个来自于先前的表的行组合，进行完整的表扫描。如果表是第一个没标记const的表，这通常不好，并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL，使得行能基于前面的表中的常数值或列值被检索出。

·         possible_keys

possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意，该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询。参见13.1.2节，“ALTER TABLE语法”。

为了看清一张表有什么索引，使用SHOW INDEX FROM tbl_name。

·         key

key列显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。参见13.2.7节，“SELECT语法”。

对于MyISAM和BDB表，运行ANALYZE TABLE可以帮助优化器选择更好的索引。对于MyISAM表，可以使用myisamchk --analyze。参见13.5.2.1节，“ANALYZE TABLE语法”和5.9.4节，“表维护和崩溃恢复”。

·         key_len

key_len列显示MySQL决定使用的键长度。如果键是NULL，则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

·         ref

ref列显示使用哪个列或常数与key一起从表中选择行。

·         rows

rows列显示MySQL认为它执行查询时必须检查的行数。

·         Extra

该列包含MySQL解决查询的详细信息。下面解释了该列可以显示的不同的文本字符串：

o        Distinct

MySQL发现第1个匹配行后，停止为当前的行组合搜索更多的行。

o        Not exists

MySQL能够对查询进行LEFT JOIN优化，发现1个匹配LEFT JOIN标准的行后，不再为前面的的行组合在该表内检查更多的行。

下面是一个可以这样优化的查询类型的例子：

SELECT * 从t1 LEFT JOIN t2 ON t1.id=t2.id  WHERE t2.id IS NULL；假定t2.id定义为NOT NULL。在这种情况下，MySQL使用t1.id的值扫描t1并查找t2中的行。如果MySQL在t2中发现一个匹配的行，它知道t2.id绝不会为NULL，并且不再扫描t2内有相同的id值的行。换句话说，对于t1的每个行，MySQL只需要在t2中查找一次，无论t2内实际有多少匹配的行。

o        range checked for each record (index map: #)

MySQL没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL检查是否可以使用range或index_merge访问方法来索取行。关于适用性标准的描述参见7.2.5节，“范围优化”和7.2.6节，“索引合并优化”，不同的是前面表的所有列值已知并且认为是常量。

这并不很快，但比执行没有索引的联接要快得多。

o        Using filesort

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。参见7.2.12节，“MySQL如何优化ORDER BY”。

o        Using index

从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。

o        Using temporary

为了解决查询，MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。

o        Using where

WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。

如果想要使查询尽可能快，应找出Using filesort 和Using temporary的Extra值。

o        Using sort_union(...), Using union(...), Using intersect(...)

这些函数说明如何为index_merge联接类型合并索引扫描。详细信息参见7.2.6节，“索引合并优化”。

o        Using index for group-by

类似于访问表的Using index方式，Using index for group-by表示MySQL发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。详情参见7.2.13节，“MySQL如何优化GROUP BY”。

通过相乘EXPLAIN输出的rows列的所有值，你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时，也用这个乘积来确定执行哪个多表SELECT语句。参见7.5.2节，“调节服务器参数”。

下列例子显示出一个多表JOIN如何能使用EXPLAIN提供的信息逐步被优化。

假定你有下面所示的SELECT语句，计划使用EXPLAIN来检查它：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,               tt.ProjectReference, tt.EstimatedShipDate,               tt.ActualShipDate, tt.ClientID,               tt.ServiceCodes, tt.RepetitiveID,               tt.CurrentProcess, tt.CurrentDPPerson,               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,               et_1.COUNTRY, do.CUSTNAME        FROM tt, et, et AS et_1, do        WHERE tt.SubmitTime IS NULL          AND tt.ActualPC = et.EMPLOYID          AND tt.AssignedPC = et_1.EMPLOYID          AND tt.ClientID = do.CUSTNMBR;对于这个例子，假定：

·         被比较的列声明如下：

表
列
列类型

tt
ActualPC
CHAR(10)

tt
AssignedPC
CHAR(10)

tt
ClientID
CHAR(10)

et
EMPLOYID
CHAR(15)

do
CUSTNMBR
CHAR(15)


·         表有下面的索引：

表
索引

tt
ActualPC

tt
AssignedPC

tt
ClientID

et
EMPLOYID(主键)

do
CUSTNMBR(主键)


·         tt.ActualPC值不是均匀分布的。

开始，在进行优化前，EXPLAIN语句产生下列信息：



table type possible_keys key  key_len ref  rows  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 3872           ClientID,           ActualPC      range checked for each record (key map: 35) 因为type对每张表是ALL，这个输出显示MySQL正在对所有表产生一个笛卡尔乘积；即每一个行的组合！这将花相当长的时间，因为必须检查每张表的行数的乘积！对于一个实例，这是74 * 2135 * 74 * 3872 = 45,268,558,720行。如果表更大，你只能想象它将花多长时间……

这里的一个问题是MySQL能更高效地在声明具有相同类型和尺寸的列上使用索引。在本文中，VARCHAR和CHAR是相同的，除非它们声明为不同的长度。因为tt.ActualPC被声明为CHAR(10)并且et.EMPLOYID被声明为CHAR(15)，长度不匹配。

为了修正在列长度上的不同，使用ALTER TABLE将ActualPC的长度从10个字符变为15个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);现在tt.ActualPC和et.EMPLOYID都是VARCHAR(15)，再执行EXPLAIN语句产生这个结果：



table type   possible_keys key     key_len ref         rows    Extratt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using             ClientID,                                         where             ActualPCdo    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 Extraet    ALL    PRIMARY       NULL     NULL    NULL          74tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using             ClientID,                                         where             ActualPCet_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 Using             ClientID,                                        where             ActualPCet    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子句以不同的次序列出表，可能得到更好的性能。

luren04

7.2.2. 估计查询性能
在大多数情况下，可以通过计算磁盘搜索来估计性能。对小的表，通常能在1次磁盘搜索中找到行(因为索引可能被缓存)。对更大的表，可以使用B－树索引进行估计，将需要log(row_count)/log(index_block_length/3 * 2/(index_length + data_pointer_length))+1次搜索才能找到行。

在MySQL中，索引块通常是1024个字节，数据指针通常是4个字节，这对于有一个长度为3(中等整数)的索引的500,000行的表，通过公式可以计算出log(500,000)/log(1024/3*2/(3+4))+1= 4次搜索。

上面的索引需要大约500,000 * 7 * 3/2 = 5.2MB，(假设典型情况下索引缓存区填充率为2/3)，可以将大部分索引保存在内存中，仅需要1-2调用从OS读数据来找出行。

然而对于写，将需要4次搜索请求(如上)来找到在哪儿存放新索引，并且通常需要2次搜索来更新这个索引并且写入行。

注意，上述讨论并不意味着应用程序的性能将缓慢地以logN 退化！当表格变得更大时，所有内容缓存到OS或SQL服务器后，将仅仅或多或少地更慢。在数据变得太大不能缓存后，将逐渐变得更慢，直到应用程序只能进行磁盘搜索(以logN增加)。为了避免这个问题，随数据增加而增加 键高速缓冲区大小。对于MyISAM表, 由key_buffer_size系统变量控制 键高速缓冲区大小。参见7.5.2节，“调节服务器参数”。

7.2.3. SELECT查询的速度
总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。参见7.4.5节，“MySQL如何使用索引”和7.2.1节，“EXPLAIN语法（获取关于SELECT的信息）”。

下面是一些加速对MyISAM表的查询的一般建议：

·         为了帮助MySQL更好地优化查询，在一个装载数据后的表上运行ANALYZE TABLE或myisamchk --analyze。这样为每一个索引更新指出有相同值的行的平均行数的值（当然，如果只有一个索引，这总是1。）MySQL使用该方法来决定当你联接两个基于非常量表达式的表时选择哪个索引。你可以使用SHOW INDEX FROM tbl_name并检查Cardinality值来检查表分析结果。myisamchk --description --verbose可以显示索引分布信息。

·         要想根据一个索引排序一个索引和数据，使用myisamchk --sort-index --sort-records=1（如果你想要在索引1上排序）。如果只有一个索引，想要根据该索引的次序读取所有的记录，这是使查询更快的一个好方法。但是请注意，第一次对一个大表按照这种方法排序时将花很长时间！

7.2.4. MySQL怎样优化WHERE子句
该节讨论为处理WHERE子句而进行的优化。例子中使用了SELECT语句，但相同的优化也适用DELETE和UPDATE语句中的WHERE子句。

请注意对MySQL优化器的工作在不断进行中，因此该节并不完善。MySQL执行了大量的优化，本文中所列的并不详尽。

下面列出了MySQL执行的部分优化：

·         去除不必要的括号：

·                        ((a AND b) AND c OR (((a AND b) AND (c AND d))))·                -> (a AND b AND c) OR (a AND b AND c AND d)·         常量重叠：

·                   (a<b AND b=c) AND a=5·                -> b>5 AND b=c AND a=5·         去除常量条件(由于常量重叠需要)：

·                   (B>=5 AND B=5) OR (B=6 AND 5=5) OR (B=7 AND 5=6)·                -> B=5 OR B=6·         索引使用的常数表达式仅计算一次。

对于MyISAM和HEAP表，在一个单个表上的没有一个WHERE的COUNT(*)直接从表中检索信息。当仅使用一个表时，对NOT NULL表达式也这样做。
无效常数表达式的早期检测。MySQL快速检测某些SELECT语句是不可能的并且不返回行。
如果不使用GROUP BY或分组函数(COUNT()、MIN()……)，HAVING与WHERE合并。
对于联接内的每个表，构造一个更简单的WHERE以便更快地对表进行WHERE计算并且也尽快跳过记录。
所有常数的表在查询中比其它表先读出。常数表为：
空表或只有1行的表。
与在一个PRIMARY KEY或UNIQUE索引的WHERE子句一起使用的表，这里所有的索引部分使用常数表达式并且索引部分被定义为NOT NULL。
下列的所有表用作常数表：

mysql> SELECT * FROM t WHERE primary_key=1;mysql> SELECT * FROM t1,t2           WHERE t1.primary_key=1 AND t2.primary_key=t1.id;尝试所有可能性便可以找到表联接的最好联接组合。如果所有在ORDER BY和GROUP BY的列来自同一个表，那么当联接时，该表首先被选中。
如果有一个ORDER BY子句和不同的GROUP BY子句，或如果ORDER BY或GROUP BY包含联接队列中的第一个表之外的其它表的列，则创建一个临时表。
如果使用SQL_SMALL_RESULT，MySQL使用内存中的一个临时表。
每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。
在一些情况下，MySQL能从索引中读出行，甚至不查询数据文件。如果索引使用的所有列是数值类，那么只使用索引树来进行查询。
输出每个记录前，跳过不匹配HAVING子句的行。
下面是一些快速查询的例子：

SELECT COUNT(*) FROM tbl_name; SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name; SELECT MAX(key_part2) FROM tbl_name    WHERE key_part1=constant; SELECT ... FROM tbl_name    ORDER BY key_part1,key_part2,... LIMIT 10; SELECT ... FROM tbl_name    ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;下列查询仅使用索引树就可以解决(假设索引的列为数值型)：

SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val; SELECT COUNT(*) FROM tbl_name    WHERE key_part1=val1 AND key_part2=val2; SELECT key_part2 FROM tbl_name GROUP BY key_part1;下列查询使用索引按排序顺序检索行，不用另外的排序：

SELECT ... FROM tbl_name    ORDER BY key_part1,key_part2,... ; SELECT ... FROM tbl_name    ORDER BY key_part1 DESC, key_part2 DESC, ... ;

luren04

7.2.5. 范围优化
7.2.5.1. 单元素索引的范围访问方法
7.2.5.2. 多元素索引的范围访问方法
range访问方法使用单一索引来搜索包含在一个或几个索引值距离内的表记录的子集。可以用于单部分或多元素索引。后面的章节将详细描述如何从WHERE子句提取区间。

7.2.5.1. 单元素索引的范围访问方法
对于单元素索引，可以用WHERE子句中的相应条件很方便地表示索引值区间，因此我们称为范围条件而不是“区间”。

单元素索引范围条件的定义如下：

·         对于BTREE和HASH索引，当使用=、<=>、IN、IS NULL或者IS NOT NULL操作符时，关键元素与常量值的比较关系对应一个范围条件。

·         对于BTREE索引，当使用>、<、>=、<=、BETWEEN、!=或者<>，或者LIKE 'pattern'(其中 'pattern'不以通配符开始)操作符时，关键元素与常量值的比较关系对应一个范围条件。

·         对于所有类型的索引，多个范围条件结合OR或AND则产生一个范围条件。

前面描述的“常量值”系指：

·         查询字符串中的常量

·         同一联接中的const或system表中的列

·         无关联子查询的结果

·         完全从前面类型的子表达式组成的表达式

下面是一些WHERE子句中有范围条件的查询的例子：

SELECT * FROM t1     WHERE key_col > 1     AND key_col < 10; SELECT * FROM t1     WHERE key_col = 1     OR key_col IN (15,18,20); SELECT * FROM t1     WHERE key_col LIKE 'ab%'     OR key_col BETWEEN 'bar' AND 'foo'; 请注意在常量传播阶段部分非常量值可以转换为常数。

MySQL尝试为每个可能的索引从WHERE子句提取范围条件。在提取过程中，不能用于构成范围条件的条件被放弃，产生重叠范围的条件组合到一起，并且产生空范围的条件被删除。

例如，考虑下面的语句，其中key1是有索引的列，nonkey没有索引：

SELECT * FROM t1 WHERE   (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR   (key1 < 'bar' AND nonkey = 4) OR   (key1 < 'uux' AND key1 > 'z');key1的提取过程如下：

1.    用原始WHERE子句开始：

2.    (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR

3.     (key1 < 'bar' AND nonkey = 4) OR

4.     (key1 < 'uux' AND key1 > 'z')

5.    删除nonkey = 4和key1 LIKE '%b'，因为它们不能用于范围扫描。删除它们的正确途径是用TRUE替换它们，以便进行范围扫描时不会丢失匹配的记录。用TRUE替换它们后，可以得到：

6.            (key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR7.            (key1 < 'bar' AND TRUE) OR8.            (key1 < 'uux' AND key1 > 'z')9.    取消总是为true或false的条件：

·         (key1 LIKE 'abcde%' OR TRUE)总是true

·         (key1 < 'uux' AND key1 > 'z')总是false

用常量替换这些条件，我们得到：

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)删除不必要的TRUE和FALSE常量，我们得到

(key1 < 'abc') OR (key1 < 'bar')10.将重叠区间组合成一个产生用于范围扫描的最终条件：

11.        (key1 < 'bar')总的来说(如前面的例子所述)，用于范围扫描的条件比WHERE子句限制少。MySQL再执行检查以过滤掉满足范围条件但不完全满足WHERE子句的行。

范围条件提取算法可以处理嵌套的任意深度的AND/OR结构，并且其输出不依赖条件在WHERE子句中出现的顺序。

7.2.5.2. 多元素索引的范围访问方法
多元素索引的范围条件是单元素索引的范围条件的扩展。多元素索引的范围条件将索引记录限制到一个或几个关键元组内。使用索引的顺序，通过一系列关键元组来定义关键元组区间。

例如，考虑定义为key1(key_part1, key_part2, key_part3)的多元素索引，以及下面的按关键字顺序所列的关键元组：

key_part1  key_part2  key_part3  NULL       1          'abc'  NULL       1          'xyz'  NULL       2          'foo'   1         1          'abc'   1         1          'xyz'   1         2          'abc'   2         1          'aaa' 条件key_part1 = 1定义了下面的范围：

(1，-inf，-inf) <= (key_part1，key_part2，key_part3) < (1，+inf，+inf)范围包括前面数据集中的第4、5和6个元组，可以用于范围访问方法。

通过对比，条件key_part3 = 'abc'不定义单一的区间，不能用于范围访问方法。

下面更加详细地描述了范围条件如何用于多元素索引中。

·         对于HASH索引，可以使用包含相同值的每个区间。这说明区间只能由下面形式的条件产生：

·                     key_part1 cmp const1·                 AND key_part2 cmp const2·                 AND ...·                AND key_partN cmp constN;这里，const1，const2，...为常量，cmp是=、<=>或者IS NULL比较操作符之一，条件包括所有索引部分。(也就是说，有N 个条件，每一个对应N-元素索引的每个部分）。

关于常量的定义，参见7.2.5.1节，“单元素索引的范围访问方法”。

例如，下面为三元素HASH索引的范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'·         对于BTREE索引，区间可以对结合AND的条件有用，其中每个条件用一个常量值通过=、<=>、IS NULL、>、<、>=、<=、!=、<>、BETWEEN或者LIKE 'pattern' (其中'pattern'不以通配符开头)比较一个关键元素。区间可以足够长以确定一个包含所有匹配条件(或如果使用<>或!=，为两个区间)的记录的单一的关键元组。例如，对于条件：

·                  key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10单一区间为：

('foo'，10，10)   < (key_part1，key_part2，key_part3)      < ('foo'，+inf，+inf)创建的区间可以比原条件包含更多的记录。例如，前面的区间包括值('foo'，11，0)，不满足原条件。

·         如果包含区间内的一系列记录的条件结合使用OR，则形成包括一系列包含在区间并集的记录的一个条件。如果条件结合使用了AND，则形成包括一系列包含在区间交集内的记录的一个条件。例如，对于两部分索引的条件：

·                (key_part1 = 1 AND key_part2 < 2)·                OR (key_part1 > 5)区间为：

(1, -inf) < (key_part1, key_part2) < (1, 2)

(5, -inf) < (key_part1, key_part2)

在该例子中，第1行的区间左侧的约束使用了一个关键元素，右侧约束使用了两个关键元素。第2行的区间只使用了一个关键元素。EXPLAIN输出的key_len列表示所使用关键字前缀的最大长度。

在某些情况中，key_len可以表示使用的关键元素，但可能不是你所期望的。假定key_part1和key_part2可以为NULL。则key_len列显示下面条件的两个关键元素的长度：

key_part1 >= 1 AND key_part2 < 2但实际上，该条件可以变换为：

key_part1 >= 1 AND key_part2 IS NOT NULL7.2.5.1节，“单元素索引的范围访问方法”描述了如何进行优化以结合或删除单元素索引范围条件的区间。多元素索引范围条件的区间的步骤类似。