mysql优化之八

　　译者：叶金荣（Email:），手册来源：MySQL手册版本 5.0.20，出处：http://imysql.cn，转载请注明译者和出处，并且不能用于商业用途，违者必究。 7.4.6.3 中点插入策略 默认地，MySQL 4.1的索引缓存管理系统采用LRU策略来选择要被清除的缓存区块，不过它也支持更完善的方法，叫做"中点插入策略"。 使用中点插入策略时，LRU链就被分割成两半：一个热子链，一个温子链。两半分割的点不是固定的，不过缓存管理系统会注意不让温子链部分"太短"，总是至少包括全部缓存区块的 key_cache_division_limit 比率。key_cache_division_limit 是缓存结构体变量的组件部分，因此它是每个缓存都可以设置这个参数值。 当一个索引区块从表中读入缓存时，它首先放在温子链的末尾。当达到一定的点击率(访问这个区块)后，它就提升到热子链中去。目前，要提升一个区块的点击率(3)对每个区块来说都是一样的。将来，我们会让点击率依靠B树中对应的索引区块节点的级别：包含非叶子节点的索引区块所要求的提升点击率就低一点，包含叶子节点的B索引树的区块的值就高点。 提升起来的区块首先放在热子链的末尾。这个区块在热子链内一直循环。如果这个区块在该子链开头位置停留时间足够长了，它就会被降级回温子链。这个时间是由索引缓存结构体变量的组件 key_cache_age_threshold 值来决定的。 这个阀值是这么描述的，一个索引缓存包含了 N 个区块，热子链开头的区块在低于 N*key_cache_age_threshold/100 次访问后就被移动到温子链的开头位置。它又首先成为被删除的候选对象，因为要被替换的区块还是从温子链的开头位置开始的。 中点插入策略就能在缓存中总能保持更有价值的区块。如果更喜欢采用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 索引缓存大小 MySQL 4.1引进了对每个索引缓存的新变量 key_cache_block_size。这个变量可以指定每个索引缓存的区块大小。用它就可以来调整索引文件I/O操作的性能。 当读缓冲的大小和本地操作系统的I/O缓冲大小一样时，就达到了I/O操作的最高性能了。但是设置索引节点的大小和I/O缓冲大小一样未必能达到最好的总体性能。读比较大的叶子节点时，服务器会读进来很多不必要的数据，这大大阻碍了读其他叶子节点。 目前，还不能控制数据表的索引区块大小。这个大小在服务器创建索引文件 `.MYI' 时已经设定好了，它根据数据表的索引大小的定义而定。在很多时候，它设置成和I/O缓冲大小一样。在将来，可以改变它的值，并且会全面采用变量 key_cache_block_size。 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 MySQL 如何统计打开的表数量 执行命令 mysqladmin status 时，可以看到类似如下结果： Uptime: 426 Running threads: 1 Questions: 11082 Reloads: 1 Open tables: 12 如果只有6个数据表时，Open tables 的值确实12，这可能会有些疑惑。 MySQL是多线程的，因此可能会有多个客户端同时发起查询某个表的请求。为了最小化多个客户端线程在同一个表上的不同状态，针对每个并发的线程单独打开数据表。这会占用一些内存，但是通常会提高性能。如果是 MyISAM 表，针对每个打开数据表的客户端都要有一个额外的文件描述符打开数据文件的情况(不过索引文件的描述符则可以在所有的线程间共享)。ISAM 存储引擎则共享这些操作。 想要了解更多，可以看下一章的详细内容"7.4.8 How MySQL Opens and Closes Tables"。 7.4.8 MySQL 如何打开和关闭数据表 系统变量 table_cache, max_connections 和 max_tmp_tables 影响着服务器保持打开的文件最大数量。提高一个或多个这些变量，就可以提高操作系统在每次处理时能打开的文件描述符限制。很多操作系统都可以增加文件打开的数量限制，不过每个系统的方法都各不一样。查阅一下操作系统文档来判断是否可以提高限制以及怎么去做。 table_cache 和 max_connections 相关。例如，有200个并发的连接，那么则必须至少有 200 * N 大小的表缓存，N 是在一个表连接中最大的表数量。同时也需要为临时表和临时文件保留一些额外的文件描述符。 请确认操作系统可以通过设定 table_cache 来处理对应数量的打开文件。如果 table_cache 设得太高了，MySQL可能会用完全部的文件描述符而拒绝新连接，就无法执行查询，变得很不可靠。因此必须要考虑到 MyISAM 存储引擎要为每个独立打开的表使用两个文件描述符。可以在启动 mysqld_safe 的时候增加 --open-files-limit 参数来提高MySQL打开文件描述符的数量。详情请看"A.2.17 File Not Found". 缓存中会保存 table_cache 个打开的表会。它的默认值是64；它可以在启动 mysqld 的时候通过修改 --table_cache 参数来改变。注意，MySQL可能会临时打开比这个数更多的表来执行查询。 在以下情况中，一个不用的表会被关闭并且从缓存中被删除： * 如果缓存满了，并且一个线程试图打开一个不在缓存中的表。 * 如果缓存中已经包含了不止 table_cache 个表目，并且线程无需再使用该表。 * 当发生刷新表操作。当提交一个 FLUSH TABLES 语句或者执行 mysqladmin flush-tables or mysqladmin refresh 命令时就会这样。 当表缓存满了，服务器遵循以下步骤来分配一个新的缓存表目： * 当前没使用的表都释放，依照最近最少使用的顺序。 * 如果有一个新的表要被打开，但是缓存满了且没有表被释放，缓存就临时根据需要扩充一下。 当缓存处于临时扩充状态，且有表处于从使用变成不使用状态时，就关闭这个表并且从缓存中释放。 每个并行访问都打开一个表。这意味着当有两个线程同时访问一个表，或者一个线程在同一个查询中访问两次这个表(例如，在表连接中连接自己)，那么这个表就需要被打开两次。每次并发的打开都在表缓存中请求一个表目。每个表第一次打开时都需要两个文件描述符：一个给数据文件，一个给索引文件。其他新增的对该表的打开则只需要一个文件，给数据文件。索引文件的描述符在所有的线程间是共享的。 如果使用 HANDLER tbl_name OPEN 语句打开表，则有一个专用的表对象给该线程。这个表对象不和其他线程共享，并且除非调用 HANDLER tbl_name CLOSE 语句或者线程结束，否则它不会关闭；这样的话，它就重新放回表索引中(如果索引还未满)。详情请看"14.1.3 HANDLER Syntax"。 可以检查 mysqld 的状态变量 Opened_tables 来判断表索引是否太小了： mysql> SHOW STATUS LIKE 'Opened_tables'; --------------- ------- | Variable_name | Value | --------------- ------- | Opened_tables | 2741 | --------------- ------- 如果这个值比较大，不过完全没必要执行一大堆的 FLUSH TABLES 语句。只需加大表索引缓存大小即可。详情请看"5.2.3 Server System Variables"和"5.2.4 Server Status Variables"。 7.4.9 在一些数据库中创建太多表的缺点 如果在一个目录下有很多的 MyISAM 或 ISAM 表，打开，关闭，创建等操作就会变慢。如果在很多不同的表上执行 SELECT 语句，当表缓存满了之后这就会有些开销，因为每个表都需要被打开，其他的必须关闭。可以加大表缓存来降低这个开销。