Chapter 7. Optimization

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

| Table   | Op                 | Msg_type | Msg_text |

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

| test.t1 | assign_to_keycache | status   | OK       |

| test.t2 | assign_to_keycache | status   | OK       |

| test.t3 | assign_to_keycache | status   | OK       |

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

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

mysql> SET GLOBAL keycache1.key_buffer_size=0;

mysql> set global key_buffer_size = 0;

mysql> show variables like 'key_buffer_size';

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

| Variable_name   | Value   |

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

| key_buffer_size | 8384512 |

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

key_buffer_size = 4G

hot_cache.key_buffer_size = 2G

cold_cache.key_buffer_size = 2G

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

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

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

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

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

| Table   | Op           | Msg_type | Msg_text |

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

| test.t1 | preload_keys | status   | OK       |

| test.t2 | preload_keys | status   | OK       |

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

使用myisam_stats_method的一些警告：

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

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

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

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

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

mysql> SHOW STATUS LIKE 'Opened_tables';

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

| Variable_name | Value |

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

| Opened_tables | 2741  |

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

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

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

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

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

其它技巧：

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

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

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

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

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

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

shell> mysqld --verbose --help

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

help                              TRUE

abort-slave-event-count           0

allow-suspicious-udfs             FALSE

auto-increment-increment          1

auto-increment-offset             1

automatic-sp-privileges           TRUE

basedir                           /home/jon/bin/mysql/

bdb                               FALSE

bind-address                      (No default value)

character-set-client-handshake    TRUE

character-set-server              latin1

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

chroot                            (No default value)

collation-server                  latin1_swedish_ci

completion-type                   0

concurrent-insert                 1

console                           FALSE

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

default-character-set             latin1

default-collation                 latin1_swedish_ci

default-time-zone                 (No default value)

disconnect-slave-event-count      0

enable-locking                    FALSE

enable-pstack                     FALSE

engine-condition-pushdown         FALSE

external-locking                  FALSE

gdb                               FALSE

large-pages                       FALSE

init-connect                      (No default value)

init-file                         (No default value)

init-slave                        (No default value)

innodb                            TRUE

innodb_checksums                  TRUE

innodb_data_home_dir              (No default value)

innodb_doublewrite                TRUE

innodb_fast_shutdown              1

innodb_file_per_table             FALSE

innodb_flush_log_at_trx_commit    1

innodb_flush_method               (No default value)

innodb_locks_unsafe_for_binlog    FALSE

innodb_log_arch_dir               (No default value)

innodb_log_group_home_dir         (No default value)

innodb_max_dirty_pages_pct        90

innodb_max_purge_lag              0

innodb_status_file                FALSE