mysql优化之七

核心提示：译者：叶金荣（Email:），手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，mysql优化之七，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，它无需先从磁盘中读取出来，当临时表的索引大小和冷缓存大小一样时，并且不能用于商业用途，违者必究

　　译者：叶金荣（Email:），手册来源：MySQL(和PHP搭配之最佳组合)手册版本 5.0.20，出处：http://iMySQL(和PHP搭配之最佳组合).cn，转载请注明译者和出处，并且不能用于商业用途，违者必究。
7.4.5 MySQL(和PHP搭配之最佳组合)如何使用索引

索引用于快速找到特定一些值的记录。如果没有索引，MySQL(和PHP搭配之最佳组合)就必须从第一行记录开始读取整个表来检索记录。表越大，资源消耗越大。如果在字段上有索引的话，MySQL(和PHP搭配之最佳组合)就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。

大部分MySQL(和PHP搭配之最佳组合)索引(Prima(最完善的虚拟主机管理系统)RY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储，MEMORY (HEAP)表支持哈希索引。

字符串默认都是自动压缩前缀和后缀中的空格，详情请看"14.2.5 CREATE INDEX Syntax"。

通常，如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY 表)的独特之处在后面会讨论到。

* 想要尽快找到匹配 WHERE 子句的记录。
* 根据条件排除记录。如果有多个索引可共选择的话，MySQL(和PHP搭配之最佳组合)通常选择能找到最少记录的那个索引。
* 做表连接查询时从其他表中检索记录。
* 想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。优化程序会在检查索引的 key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# = constant 子句。这样的话， MySQL(和PHP搭配之最佳组合)会为 MIN() 或 MAX() 表达式分别单独做一次索引查找，并且将它替换成常数。当所有的表达式都被替换成常数后，查询就立刻返回。如下：

SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;

* 对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时(如 ORDER BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。详情请看"7.2.9 How MySQL(和PHP搭配之最佳组合) Optimizes ORDER BY"。
* 有些时候，查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果：

SELECT key_part3 FROM tbl_name WHERE key_part1=1

假设有如下 SELECT 语句：

MySQL(和PHP搭配之最佳组合)> SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;

如果在 col1 和 col2 上有一个多字段索引的话，就能直接取得对应的记录了。如果在 col1 和 col2 分别有独立的索引，那么优化程序会先找到限制最多的那个索引，然后根据哪个索引能找到更少的记录就决定使用哪个索引。

如果表里有一个多字段索引的话，那么该索引的任何最左前缀部分都可以被优化程序用来检索记录。例如，在 (col1, col2, col3) 上有一个索引，那么按字段组合 (col1), (col1, col2), 和 (col1, col2, col3) 搜索的时候都会用到索引。

MySQL(和PHP搭配之最佳组合)无法使用非最左前缀索引中的部分索引。假如有以下 SELECT 语句：

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

如果在 (col1, col2, col3) 上有一个索引，只有第一个查询用到索引了。第二和第三个尽管包括了索引字段，但是 (col2) 和 (col2, col3) 并非索引 (col1, col2, col3) 的最左前缀部分。当对字段做 =, >, >=, <, <=, 或 BETWEEN 比较操作时，也会用到索引。 MySQL(和PHP搭配之最佳组合)在做 LIKE 比较时也可能用到索引，如果 LIKE 的参数是非通配字符开始的固定字符串的话。以下的 SELECT 语句就用到了索引：

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

第一个查询中，只有的 'Patrick' <= key_col < 'Patricl' 记录才会被检索到。第二个查询中，只检索 'Pat' <= key_col < 'Pau' 的记录。以下 SELECT 语句不使用索引：

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

第一个语句中，LIKE 的参数是以通配符开始的。第二个语句中，LIKE 的参数不是一个常值。 MySQL(和PHP搭配之最佳组合) 4.0及更高会做一个额外的 LIKE 优化。如果使用 ... LIKE '%string%' 并且 string 超过3个字符，MySQL(和PHP搭配之最佳组合)就会用 Turbo Boyer-Moore 算法来初始化模式，并且利用这个模式来加快搜索。用 col_name IS NULL 搜索时也会使用索引，如果字段 col_name 上有索引的话。任何在 WHERE 子句中没有跨越全部 AND 级分句的索引都不会用来优化查询。换言之，想要启用一个索引，那么在任何 AND 分句中都必须使用索引的前缀字段。以下 WHERE 子句使用索引：

... WHERE index_part1=1 AND index_part2=2 AND other_column=3
/* index = 1 OR index = 2 */
... WHERE index=1 OR A=10 AND index=2
/* 优化了 like "index_part1='hello'" */
... WHERE index_part1='hello' AND index_part3=5
/* 使用索引 index1，但没有用到 index2 或 index3 */
... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;

以下 WHERE 子句不使用索引：

/* 没用到 index_part1 */
... WHERE index_part2=1 AND index_part3=2
/* 所有的 AND 部分没用到索引 */
... WHERE index=1 OR A=10
/* 索引没有跨越全部字段 */
... WHERE index_part1=1 OR index_part2=10

有些时候尽管有可用的索引，MySQL(和PHP搭配之最佳组合)也不会用到它们。一种情况是优化程序认为如果使用索引会需要检索更大部分的表记录(这时候，扫描表可能更快，因为这支需要更少的搜索)。尽管如此，如果有一个查询用 LIMIT 限制只检索部分记录，MySQL(和PHP搭配之最佳组合)就一定会使用索引，因为这样能更快检索到更少记录来返回给结果。以下是哈希索引的一些不同的特性：

* 它们只用于 = 或 <=> 比较(但并不很快)。
* 优化程序无法使用哈希索引来加速 ORDER BY 操作(这种索引不能用于按顺序搜索下一个记录)。
* MySQL(和PHP搭配之最佳组合)大致无法判断出介于两个值之间有多少记录(这由范围优化程序来决定使用哪个索引)。这在把 MyISAM 表类型改为采用哈希索引的 MEMORY 类型后可能会影响一些查询。
* 只有全部索引键才能用于检索记录(如果是B树索引，任何前缀部分索引也能用于检索记录)。

7.4.6 MyISAM 索引缓存

为了能最小化磁盘I/O，MyISAM 存储引擎采用了很多数据库系统使用的一种策略。它采用一种机制将最经常访问的表保存在内存区块中：

* 对索引区块来说，它维护着一个叫索引缓存(索引缓冲)的结构体。这个结构体中放着许多那些最常使用的索引区块的缓冲区块。
* 对数据区块来说，MySQL(和PHP搭配之最佳组合)没有使用特定的缓存。它依靠操作系统的本地文件系统缓存。

本章首先描述了 MyISAM 索引缓存的基本操作。然后讨论在MySQL(和PHP搭配之最佳组合) 4.1中所做的改进，它提高了索引缓存性能，同时能更好地控制缓存操作：

* 线程之间不再是串行地访问索引缓存。多个线程可以并行地访问索引缓存。
* 可以设置多个索引缓存，同时也能指定数据表索引到特定的缓存中。

索引缓存机制对 ISAM 表同样适用。不过，这种有效性正在减弱。自从MySQL(和PHP搭配之最佳组合) 3.23开始 MyISAM 表类型引进之后，ISAM 就不再建议使用了。MySQL(和PHP搭配之最佳组合) 4.1更是延续了这个趋势，ISAM 类型默认被禁用了。可以通过系统变量 key_buffer_size 来控制索引缓存区块的大小。如果这个值大小为0，那么就不使用缓存。当这个值小得于不足以分配区块缓冲的最小数量(8)时，也不会使用缓存。当索引缓存无法操作时，索引文件就只通过操作系统提供的本地文件系统缓冲来访问(换言之，表索引区块采用的访问策略和数据区块的一致)。一个索引区块在 MyISAM 索引文件中是一个连续访问的单元。通常这个索引区块的大小和B树索引节点大小一样(索引在磁盘中是以B树结构来表示的。这个树的底部时叶子节点，叶子节点之上则是非叶子节点)。在索引缓存结构中所有的区块大小都是一样的。这个值可能等于，大于，或小于表的索引区块大小。通常这两个值是不一样的。当必须访问来自任何表的索引区块时，服务器首先检查在索引缓存中是否有可用的缓冲区块。如果有，服务器就访问缓存中的数据，而非磁盘。就是说，它直接存取缓存，而不是存取磁盘。否则，服务器选择一个(多个)包含其它不同表索引区块的缓存缓冲区块，将它的内容替换成请求表的索引区块的拷贝。一旦新的索引区块在缓存中了，索引数据就可以存取了。当发生被选中要替换的区块内容修改了的情况时，这个区块就被认为'脏'了。那么，在替换之前，它的内容就必须先刷新到它指向的标索引。通常服务器遵循LRU(最近最少使用)策略：当要选择替换的区块时，它选择最近最少使用的索引区块。为了想要让选择变得更容易，索引缓存模块会维护一个包含所有使用区块特别的队列(LRU链)。当一个区块被访问了，就把它放到队列的最后位置。当区块要被替换时，在队列开始位置的区块就是最近最少使用的，它就是第一候选删除对象。
7.4.6.1 共享访问索引缓存

在MySQL(和PHP搭配之最佳组合) 4.1以前，访问索引缓存是串行的：两个线程不能并行地访问索引缓存缓冲。服务器处理一个访问索引区块的请求只能等它之前的请求处理完。结果，新的请求所需的索引区块就不在任何索引缓存环冲区块中，因为其他线程把包含这个索引区块的缓冲给更新了。
从MySQL(和PHP搭配之最佳组合) 4.1.0开始，服务器支持共享方式访问索引缓存：

* 没有正在被更新的缓冲可以被多个线程访问。
* 缓冲正被更新时，需要使用这个缓冲的线程只能等到更新完成之后。
* 多个线程可以初始化需要替换缓存区块的请求，只要它们不干扰别的线程(也就是，它们请求不同的索引区块，因此不同的缓存区块被替换)。

共享方式访问索引缓存令服务器明显改善了吞吐量。
7.4.6.2 多重索引缓存

共享访问索引缓存改善了性能，却不能完全消除线程间的冲突。它们仍然争抢控制管理存取索引缓存缓冲的结构。为了更进一步减少索引缓存存取冲突，MySQL(和PHP搭配之最佳组合) 4.1.1提供了多重索引缓存特性。这能将不同的表索引指定到不同的索引缓存。当有多个索引缓存，服务器在处理指定的 MyISAM 表查询时必须知道该使用哪个。默认地，所有的 MyISAM 表索引都缓存在默认的索引缓存中。想要指定到特定的缓存中，可以使用 CACHE INDEX 语句。如下语句所示，指定表的索 t1, t2 和 t3 引缓存到名为 hot_cache 的缓存中：

MySQL(和PHP搭配之最佳组合)> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status | OK |
| test.t2 | assign_to_keycache | status | OK |
| test.t3 | assign_to_keycache | status | OK |
+---------+--------------------+----------+----------+

注意，如果服务器编译支持存 ISAM 储引擎了，那么 ISAM 表也使用索引缓存机制。不过，ISAM 表索引只能使用默认的索引缓存而不能自定义。 CACHE INDEX 语句中用到的索引缓存是根据用 SET GLOBAL 语句的参数设定的值或者服务器启动参数指定的值创建的，如下：

MySQL(和PHP搭配之最佳组合)> SET GLOBAL keycache1.key_buffer_size=128*1024;

想要删除索引缓存，只需设置它的大小为0：

MySQL(和PHP搭配之最佳组合)> SET GLOBAL keycache1.key_buffer_size=0;

索引缓存变量是一个结构体变量，由名字和组件构成。例如 keycache1.key_buffer_size， keycache1 就是缓存名，key_buffer_size 是缓存组件。详情请看"10.4.1 Structured System Variables"，它描述了构造索引缓存系统变量的使用语法。默认地，表索引在服务器启动时指定到主(默认的)索引缓存中。当一个索引缓存被删掉后，指定到这个缓存的所有索引都被重新指向到了默认索引缓存中去。对一个繁忙的系统来说，我们建议以下三条策略来使用索引缓存：

* 热缓存占用20%的总缓存空间。用于繁重搜索但很少更新的表。
* 冷缓存占用20%的总缓存空间。用于中等强度更新的表，如临时表。
* 冷缓存占用60%的总缓存空间。作为默认的缓存，用于所有其他表。

使用三个缓存的一个原因是好处在于，存取一个缓存结构时不会阻止对其他缓存的访问。访问一个表索引的查询不会跟指定到其他缓存的查询竞争。性能提高还表现在以下几点原因：

* 热缓存只用于检索记录，因此它的内容总是不需要变化。所以，无论什么时候一个索引区块需要从磁盘中引入，被选中要替换的缓存区块的内容总是要先被刷新。
* 索引被指向热缓存中后，如果没有需要扫描全部索引的查询，那么对应到B树中非叶子节点的索引区块极可能还保留在缓存中。
* 在临时表里必须频繁执行一个更新操作是相当快的，如果要被更新的节点已经在缓存中了，它无需先从磁盘中读取出来。当临时表的索引大小和冷缓存大小一样时，那么在需要更新一个节点时它已经在缓存中存在的几率是相当高的。