mysql优化之索引优化

Posted by Money Talks on 2012/02/23 |

第一篇 序章
第二篇 连接优化
第三篇 索引优化
第四篇 查询优化
第五篇 到实战中去

索引优化

索引优化涉及到几个方面,包括了索引的类型、如何让查询使用索引,查询是索引算法的选择等等操作。(原文链接http://ddbiz.com/?p=961)
涉及到数据库的查询时,大多数情况都是要建立索引的,MySQL的索引类型以及创建索引方式,可以参考其文档或者这里

  1. 服务器参数设置

    在于索引有关系的数据库参数中,有一些特别重要,如下:

    1. key_buffer_size

      命令行参数: –key_buffer_size=#
      ini/cnf参数: key_buffer_size
      mysql 变量: key_buffer_size
      全局变量,可动态调整,取值范围 8到4G(32bitOS), 在64bitOS上可以设置更高,只要你的系统有那么多物理内存。

      key_buffer_size 主要作用于MyISAM表的索引。我们知道,MyISAM的索引句柄是对全部连接用户共享的。状态参数 key_read_requests(响应用户请求时从缓存中获取的数据)和key_reads(响应用户请求时从文件中加载的数据) 可以用来检测key_buffer_size是否太小,根据文档, key_reads/key_read_request 应该远小于0.01(当然我们的测试环境可能完全无法达到此设定,只能尽量接近了). 如果一个MyISAM的数据表的索引文件过G,除非物理内存远远高于索引大小,否则让key_reads/key_read_request接近0.01也是一件不可能的任务。

      MySQL还支持把索引提前加载到内存,本节后面将描述此方法及其限制.

      受如下参数影响: 系统内存
      将影响如下参数: 无
      调整触发条件: key_reads/key_read_request >0.01

    2. key_cache_block_size

      与key_buffer_size相关联的另外一个参数是 key_cache_block_size
      命令行参数: –key_cache_block_size=#
      ini/cnf参数: key_cache_block_size
      mysql 变量: key_cache_block_size
      全局变量,可动态调整,取值范围 512bytes到16k。

      key_cache_block_size表明key_buffer_size被分割的区域的大小。可以通过 show status 来判断当前key_buffer_size被分为多少个块:

      Key_blocks_unused, 目前剩余可用的索引缓存空间
      Key_blocks_used(历史上的最大峰值)

      对于一个key_buffer_size=4G的数据库来说, key_cache_block_size*Key_blocks_unused 就是剩余的可用的缓存空间。只要这个空间允许,我们都应该尽可能的把索引预装入缓存中(见LOAD INDEX INTO CACHE

      理论上讲,key_cache_block_size与操作系统的I/O buffer相同大小时工作(读取、写入)效率最高,比如linux中i/o缓存一般为1k. 对于mysql来说,可以通过 –myisam-block-size来定义MyISAM索引文件的块大小,其最小为1k。

    3. key_cache_division_limit

      命令行参数: –key_cache_division_limit=1~100
      配置文件参数: key_cache_division_limit=#
      MySQL变量: key_cache_division_limit
      全局可调整变量,默认为100(即LRU队列)。
      当MyISAM的key_buffer_size中设置了key_cache_division_limit(

    4. key_cache_age_threshold

      命令行参数: –key_cache_age_threshold=100~4G(32bitOS)或者100~~(64bitOS)
      配置文件参数: key_cache_age_threshold=#
      MySQL变量: key_cache_age_threshold
      全局可调整变量,默认为300。
      当MyISAM的key_buffer_size被分为热链和温链时,key_cache_age_threshold就被用来控制什么样的情况下处于热链的数据会被转移到温链中。key_cache_age_threshold是指的block个数,如果在最近的 (key_cache_age_threshold * key_cache_division_limit/100)次访问中,处于热链顶部的block没有被访问到,那么这些块将被移到温链的顶部(处于温链顶部的block会很快被移除出key cache)。

      说明:
      key_buffer_size, key_cache_block_size, key_cache_division_limit, key_cache_age_threshold 这4个变量是一个结构变量中的一组变量。本文后续有部分详细介绍.

    5. read_buffer_size

      命令行参数: –read_buffer_size=#
      ini/cnf参数: read_buffer_size
      mysql 变量: read_buffer_size
      全局变量,可动态调整,默认128k,取值范围8k到2G.

      read_buffer_size主要作用于顺序读取一批数据时,减少数据库文件访问的次数。

      受如下参数影响:
      将影响如下参数: KEY_BLOCK_SIZE(表创建时的参数)
      调整触发条件:

    6. read_rnd_buffer_size

      命令行参数: –read_rnd_buffer_size=#
      ini/cnf参数: read_rnd_buffer_size
      mysql 变量: read_rnd_buffer_size
      全局变量,可动态调整,默认256k,取值范围8k到4G.

      read_rnd_buffer_size用于使用键/索引进行排序时的磁盘文件预读,一个足够大的read_rnd_buffer_size对ORDER BY语句会有很大影响。
      注意:read_rnd_buffer_size是和每一个session相关的,因此其大小需要谨慎处理,当并发连接很大时,尤其要小心。

  2. LOAD CACHE INTO CACHE

    MySQL可以把索引文件预先加载到指定的缓存中:

    LOAD INDEX INTO CACHE table_name;
    LOAD INDEX INTO CACHE table_name INDEX (index_name_1, index_name_2);
    LOAD INDEX INTO CACHE table_name INDEX (index_name_1, index_name_2) IGNORE LEAVES;

    对于查询型的超大型的表,当没有足够的内存时,可以使用 IGNORE LEAVES 的方式加载索引到缓存。比如一个接近1Billion的数据表,其索引可能也是几G大小的。下面这个例子就显示了当预先加载数据索引时,查询的速度变化,这个数据表有接近1亿条记录,3个索引,有超过3G的大小,对于同一个查询:

    #mysql> select * from tbigdatatable where username in (‘ABCDEF’, ‘19740821’)

    在非预先加载索引以及预载索引的情况下,查询的响应区别:

    #mysqld_safe –key-buffer-cache=1073741824
    ####不预载索引#### 5 rows in set (0.65 sec)
    ######预载索引#### 5 rows in set (1.94 sec)

    当然一个查询有很大的偶然性,不过从一个大量的查询平均来看,这个效果还是有很大的差别的。

    如果一个表的索引包含不同的key_block_size,那么这个预装载将会遇到困难, 如:

    alter table tbigdatatable add index idx_bigdatatable_username(username) key_block_size=8192, add index idx_bigdatatable_email(email) key_block_size=8192, add index idx_bigdatatable_id(id) key_block_size=1024;
    load index into cache tbigdatatable index (idx_bigdatatable_username, idx_bigdatatable_email) ignore leaves;
    +————————+————–+———-+————————————-+
    | Table | Op | Msg_type | Msg_text |
    +————————+————–+———-+————————————-+
    | coredata.tbigdatatable | preload_keys | error | Indexes use different block sizes |
    | coredata.tbigdatatable | preload_keys | error | Subpartition p178sp0 returned error |
    | coredata.tbigdatatable | preload_keys | status | Operation failed |
    +————————+————–+———-+————————————-+

    因为.MYI中包含不同block_size的索引,所以无法预加载!

    MySQL 5.1 对分区表不能预装载索引:
    preload_keys | note | The storage engine for the table doesn’t support preload_keys
    真是不幸, 这些版本包括: 5.1.47, 5.1.61-community-log

    因为手头上没有MySQL5.5的数据库可供使用,所以关于预装载索引到内存的方法只能暂时放一放,这也导致了我的另外一个测试项目差点夭折:9千万数据的分区表查询,每个查询要耗时1m左右,伤不起啊。

  3. CACHE INDEX

    LOAD INDEX INTO CACHE可以把索引装入缓存中,除此外,MySQL还可以更有针对性的把索引装入制定的缓存中。key_buffer_size是一个结构中一个变量,这个结构是系统的默认缓存空间,我们还可以设定其他的缓存空间,比如:

    set global key1.key_buffer_size=128*1024*1024;
    set global key2.key_buffer_size=512*1024*1024;

    这样我们就能定义不同的缓存空间,配合 CACHE INDEX IN cache_region语句,就可以把不同的索引放入不同的缓存中。

    (接上)
    cache index table_name index (index_name_1) in key1;
    cache index table_name index (index_name_2) in key2;
    load cache into cache table_name index (index_name_1);
    load cache into cache table_name index (index_name_2) ignore leaves;

    上面的语句就把 index_name_1放入了 key1, 把 index_name_2放入了 key2;至于何时使用不同的缓存,我们在后面的实战中再做讨论。

    结构化的变量,在mysql中没办法用 show variables like ‘structure_name_1.key_buffer%’ ; 的方式来查询。假如我们定义了不同的结构变量(创建不同的缓存空间的): staticdb.*,则可以用下面的方式来查询该空间的设置情况:

    set global staticdb.key_buffer_size=1000*1024*1024;
    —————————————————
    mysql> select @@global.staticdb.key_buffer_size KBS
    , @@global.staticdb.key_cache_division_limit KCDL
    , @@global.staticdb.key_cache_age_threshold KCAT
    , @@global.staticdb.key_cache_block_size KCBS;
    +————+——+——+——+
    | KBS | KCDL | KCAT | KCBS |
    +————+——+——+——+
    | 1048576000 | 100 | 300 | 1024 |
    +————+——+——+——+
    1 row in set (0.00 sec)

    目前我还没有办法查看指定一个结构空间的使用情况,如果你知道如何查看的话,请留言给我。

  4. 要点总结

    本节的要点是:

    对于MyISAM来说:设置尽可能大的缓存,最好把MyISAM表的索引能够全部装入缓存中

上一篇:异步加载图片到GridView上,防止OOM


下一篇:CRT工具远程登陆Google Cloud远程ssh登录方法