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.4.1. 设计选择

MySQL将行数据和索引数据保存在不同的文件中。许多(几乎所有)其它数据库将行数据和索引数据混合保存在用一个文件中。我们认为MySQL选择对广范围的现代系统更好一些。

保存行数据的另一种方式是将每个列的信息保存在单独的区域(例如SDBM和Focus)。这样会对每个访问多个列的查询造成性能问题。因为当访问多个列时退化得很快，我们认为该模型对一般数据库不合适。

更常见的情形是索引和数据保存在一起(例如Oracle/Sybase)。在这种情况下，你可以在索引的叶级页找到行的信息。该布局比较好的事情是在许多情况下，根据索引缓存得怎样，可以保存一个硬盘读取。该布局的不利之处表现在：

·表扫描要慢得多，因为你必须通读索引以获得数据。

·你不能只使用表来检索查询的数据。

·你需要使用更多的空间，因为你必须从节点复制索引(你不能保存节点上的行)。

·删除要经过一段时间后才退化表(因为删除时通常不会更新节点上的索引)。

·只缓存索引数据会更加困难。

7.4.2. 使你的数据尽可能小

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

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

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

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

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

7.4.3. 列索引

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

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

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

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

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

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

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

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

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

7.4.4. 多列索引

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

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

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

假定表具有下面的结构：

CREATE TABLE test (

    id INT NOT NULL,

    last_name CHAR(30) NOT NULL,

    first_name CHAR(30) NOT NULL,

    PRIMARY KEY (id),

    INDEX name (last_name,first_name)

);

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

SELECT * FROM test WHERE last_name='Widenius';

SELECT * FROM test

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

SELECT * FROM test

    WHERE last_name='Widenius'

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

SELECT * FROM test

    WHERE last_name='Widenius'

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

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

SELECT * FROM test WHERE first_name='Michael';

SELECT * FROM test

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

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

7.4.5. MySQL如何使用索引

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

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

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

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

索引用于下面的操作：

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

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

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

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

·SELECT MIN(key_part2),MAX(key_part2)

·    FROM tbl_name WHERE key_part1=10;

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

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

·SELECT key_part3 FROM tbl_name

·    WHERE key_part1=1

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

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

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

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

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

SELECT * FROM tbl_name WHERE col1=val1;

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

SELECT * FROM tbl_name WHERE col2=val2;

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

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

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

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

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

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

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

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

SELECT * FROM tbl_name WHERE key_col LIKE other_col;

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

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

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

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

下面的WHERE子句使用索引：

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

    /* index = 1 OR index = 2 */

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

    /* optimized like "index_part1='hello'" */

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

    /* Can use index on index1 but not on index2 or index3 */

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

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

    /* index_part1 is not used */

... WHERE index_part2=1 AND index_part3=2

    /*  Index is not used in both parts of the WHERE clause  */

... WHERE index=1 OR A=10

    /* No index spans all rows  */

... WHERE index_part1=1 OR index_part2=10

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

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

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

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

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

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

7.4.6. MyISAM键高速缓冲

7.4.6.1. 共享键高速缓冲访问

7.4.6.2. 多键高速缓冲

7.4.6.3. 中点插入策略

7.4.6.4. 索引预加载

7.4.6.5. 键高速缓冲块大小

7.4.6.6. 重构键高速缓冲

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

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

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

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

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

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

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

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

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

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

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

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

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

7.4.6.1. 共享键高速缓冲访问

在以下条件下，线程可以同时访问键高速缓冲缓存区：

·没有被更新的缓存区可以被多个线程访问。

·正被更新的缓存区让需要使用它的线程等待直到更新完成。

·多个线程可以发起请求替换缓存块，只要它们不彼此干扰(也就是说，只要它们需要不同的索引块，并且使不同的缓存块被替换)。

对键高速缓冲的共享访问允许服务器大大提高吞吐量。

7.4.6.2. 多键高速缓冲

对键高速缓冲的共享访问可以提高性能但不能完全消除线程之间的竟争。它们仍然竞争对键高速缓冲缓存区的访问进行管理的控制结构。为了进一步降低键高速缓冲访问竟争，MySQL 5.1还提供了多个键高速缓冲，允许你为不同的键高速缓冲分配不同的表索引。

有多个键高速缓冲时，当为给定的MyISAM表处理查询时，服务器必须知道使用哪个缓存。默认情况，所有MyISAM表索引被缓存到默认键高速缓冲中。要想为具体键高速缓冲分配表索引，应使用CACHE INDEX语句(参见13.5.5.1节，“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       |

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

可以用SET GLOBAL参数设置语句或使用服务器启动选项设置在CACHE INDEX语句中引用的键高速缓冲的大小来创建键高速缓冲。例如：

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 |

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

键高速缓冲变量是结构式系统变量，有一个名和组件。对于keycache1.key_buffer_size，keycache1是缓存变量名，key_buffer_size是缓存组件。关于引用结构式键高速缓冲系统变量所使用的语法的描述，参见9.4.1节，“结构式系统变量”

默认情况下，表索引被分配给服务器启动时创建的主要(默认)键高速缓冲。当键高速缓冲被删除后，所有分配给它的索引被重新分配给默认键高速缓冲。

对于一个忙的服务器，我们建议采用使用三个键高速缓冲的策略：

·占用为所有键高速缓冲分配的空间的20%的“热”键高速缓冲。该缓存用于频繁用于搜索但没有更新的表。

·占用为所有键高速缓冲分配的空间的20%的“冷”键高速缓冲。该缓存用于中等大小、大量修改的表，例如临时表。

·占用键高速缓冲空间的20%的“温”键高速缓冲。使用它作为默认键高速缓冲，默认情况被所有其它表使用。

使用3个键高速缓冲有好处的一个原因是对一个键高速缓冲结构的访问不会阻挡对其它的访问。访问分配给一个缓存的表的查询不会与访问分配给其它缓存的表的查询竞争。由于其它原因也会提高性能：

·热缓存只用于检索查询，因此其内容决不会被修改。结果是，无论何时需要从硬盘上拉入索引块，选择用于替换的缓存块的内容不需要先刷新。

·对于分配给热缓存的索引，如果没有查询需要索引扫描，很有可能对应索引B-树的非叶子节点的索引块仍然在缓存中。

·当更新的节点位于缓存中并且不需要先从硬盘读入时，为临时表频繁执行的更新操作会执行得更快。如果临时表的索引的大小可以与冷键高速缓冲相比较，很可能更新的节点位于缓存中。

CACHE INDEX在一个表和键高速缓冲之间建立一种联系，但每次服务器重启时该联系被丢失。如果你想要每次服务器重启时该联系生效，一个发办法是使用选项文件：包括配置键高速缓冲的变量设定值，和一个init-file选项用来命名包含待执行的CACHE INDEX语句的一个文件。例如：

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

每次服务器启动时执行mysqld_init.sql中的语句。该文件每行应包含一个SQL语句。下面的例子分配几个表，分别对应hot_cache和cold_cache：

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

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

7.4.6.3. 中点插入策略

默认情况，键高速缓冲管理系统采用LRU策略选择要收回的键高速缓冲块，但它也支持更复杂的方法，称之为“中点插入策略”。

当使用中点插入策略时，LRU链被分为两个部分：一条热子链和一条温子链。两部分之间的划分点不固定，但键高速缓冲管理系统关注温部分不“太短”，总是包含至少key_cache_division_limit比例的键高速缓冲块。key_cache_division_limit是结构式键高速缓冲变量的一个组件，因此其值是一个可以根据每个缓存进行设置的参数。

当一个索引块从表中读入键高速缓冲，它被放入温子链的末端。经过一定量的访问后(访问块)，它被提升给热子链。目前，需要用来提升一个块(3)的访问次数与所有索引块的相同。

提升到热子链的块被放到子链的末端。块然后在该子链中循环。如果块在子链的开头停留足够长的时间，它被降到温链。该时间由键高速缓冲key_cache_age_threshold组件的值确定。

对于包含N个块的键高速缓冲，阈值表示，热子链开头的没有在最后N *key_cache_age_threshold/100次访问中被访问的块将被移动到温子链开头。该块然后变为第1个挤出的候选者，因为替换的块总是来自温子链的开头。

中点插入策略允许你将更有价值的块总是在缓存中。如果你想使用简单的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. 键高速缓冲块大小

可以使用key_cache_block_size变量为具体的键高速缓冲指定块缓存区的大小。这样允许为索引文件调节I/O操作的性能。

当读缓存区的大小等于原生操作系统I/O缓存区的大小时，可以获得I/O操作的最佳性能。但是将关键字节点的大小设置为等于I/O缓存区的大小并不总是能保证最佳整体性能。当读取大的叶节点时，服务器读入大量的不需要的数据，结果防止读入其它叶子的节点。

目前，你不能控制表内索引块的大小。该大小由服务器在创建.MYI索引文件时设置，取决于表定义中索引的关键字大小。在大多数情况下，它被设置为与I/O缓存区大小相等。

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. MyISAM索引统计集合

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

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方法。

7.4.8. MySQL如何计算打开的表

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

Uptime: 426 Running threads: 1 Questions: 11082

Reloads: 1 Open tables: 12

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

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

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

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个文件描述符：一个用于数据文件另一个用于索引文件。表的每一次额外使用仅占一个数据文件的文件描述符。索引文件描述符在所有线程之间共享。

如果你正用HANDLERtbl_name OPEN语句打开一个表，将为该线程专门分配一个表。该表不被其它线程共享，只有线程调用HANDLERtbl_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节，“服务器状态变量”。

7.4.10. 在同一个数据库中创建多个表的缺陷