MySQL优化

MySQL优化
本文来自: IT运维专家网(LinuxTone.Org) 作者:
原文链接： IT运维专家网--"自由平等，互助分享！" 目录
[隐藏]

    * 1 通用
          o 1.1 目录
          o 1.2 域名解析
          o 1.3 Query Cache
                + 1.3.1 原理
                + 1.3.2 存储块
                + 1.3.3 调整大小
                + 1.3.4 启动参数
                + 1.3.5 状态
          o 1.4 排序缓冲
    * 2 MyISAM
          o 2.1 key_buffer_size
    * 3 innodb
    * 4 系统相关
    * 5 优化工具

通用
目录

以下目录或者文件尽可能指到不同的物理硬盘

    * tmpdir，临时文件目录。可以指定多个目录来负载均衡，unix/linux使用':'来分割多个目录，而windows则用'; '。需要注意的是如果作为replication机制中的slave，那

么不要把可能被随时清空的目录作为临时文件目录，例如/tmp和内存目录，它们会在系统重启之後被清空，导致一些replication需要的数据丢失。
    * datadir，数据目录
    * log-bin，binlog目录，记录一些和写入操作有关的sql语句
    * relay-log，作为slave的时候存放binlog的目录
    * relay-log-index，作为slave的时候存放binlog的索引文件

    * log，如果开启这个选项，数据库将会记录所有请求的sql语句。开启这个选项会带来很大的性能下降。所以除非你知道你在干什么，否则这个选项一定要关闭。

域名解析

    * 如果可能，请使用ip作为grant表的host认证规则，这样就可以使用选项skip-name-resolve。该选项会禁止所有的域名解析，域名解析可能会带来性能的下降。
    * 如果确实需要用hostname进行认证，并且需要连接的hosts很多，那么可以修改源代码的宏：HOST_CACHE_SIZE，它指定了可以cache的域名信息数量，默认是128。修改之後

重新编译MySQL。

Query Cache
原理

QueryCache(下面简称QC)是根据SQL语句来cache的。一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用QC。每个Cache都是以SQL文本作为key来存的。在应用QC之前

，SQL文本不会被作任何处理。也就是说，两个SQL语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两个SQL将使用不同的一个CACHE。

不过SQL文本有可能会被客户端做一些处理。例如在官方的命令行客户端里，在发送SQL给服务器之前，会做如下处理：

    * 过滤所有注释
    * 去掉SQL文本前後的空格,TAB等字符。注意，是文本前面和後面的。中间的不会被去掉。

下面的三条SQL里，因为SELECT大小写的关系，最後一条和其他两条在QC里肯定是用的不一样的存储位置。而第一条和第二条，区别在于後者有个注释，在不同客户端，会有不一样

的结果。所以，保险起见，请尽量不要使用动态的注释。在PHP的mysql扩展里，SQL的注释是不会被去掉的。也就是三条SQL会被存储在三个不同的缓存里，虽然它们的结果都是一

样的。

select * FROM people where name='surfchen';
select * FROM people where /*hey~*/name='surfchen';
SELECT * FROM people where name='surfchen';

目前只有select语句会被cache，其他类似show,use的语句则不会被cache。

因为QC是如此前端，如此简单的一个缓存系统，所以如果一个表被更新，那么和这个表相关的SQL的所有QC都会被失效。假设一个联合查询里涉及到了表A和表B，如果表A或者表B的

其中一个被更新（update或者delete），这个查询的QC将会失效。

也就是说，如果一个表被频繁更新，那么就要考虑清楚究竟是否应该对相关的一些SQL进行QC了。一个被频繁更新的表如果被应用了QC，可能会加重数据库的负担，而不是减轻负担

。我一般的做法是默认打开QC，而对一些涉及频繁更新的表的SQL语句加上SQL_NO_CACHE关键词来对其禁用 CACHE。这样可以尽可能避免不必要的内存操作，尽可能保持内存的连续

性。

那些查询很分散的SQL语句，也不应该使用QC。例如用来查询用户和密码的语句——“select pass from user where name='surfchen'”。这样的语句，在一个系统里，很有可能

只在一个用户登陆的时候被使用。每个用户的登陆所用到的查询，都是不一样的SQL 文本，QC在这里就几乎不起作用了，因为缓存的数据几乎是不会被用到的，它们只会在内存里

占地方。
存储块

在本节里“存储块”和“block”是同一个意思

QC缓存一个查询结果的时候，一般情况下不是一次性地分配足够多的内存来缓存结果的。而是在查询结果获得的过程中，逐块存储。当一个存储块被填满之後，一个新的存储块将

会被创建，并分配内存（allocate）。单个存储块的内存分配大小通过 query_cache_min_res_unit参数控制，默认为4KB。最後一个存储块，如果不能被全部利用，那么没使用的

内存将会被释放。如果被缓存的结果很大，那么会可能会导致分配内存操作太频繁，系统系能也随之下降；而如果被缓存的结果都很小，那么可能会导致内存碎片过多，这些碎片

如果太小，就很有可能不能再被分配使用。

除了查询结果需要存储块之外，每个SQL文本也需要一个存储块，而涉及到的表也需要一个存储块（表的存储块是所有线程共享的，每个表只需要一个存储块）。存储块总数量=查

询结果数量*2+涉及的数据库表数量。也就是说，第一个缓存生成的时候，至少需要三个存储块：表信息存储块，SQL文本存储块，查询结果存储块。而第二个查询如果用的是同一

个表，那么最少只需要两个存储块：SQL文本存储块，查询结果存储块。

通过观察Qcache_queries_in_cache和Qcache_total_blocks可以知道平均每个缓存结果占用的存储块。它们的比例如果接近1:2，则说明当前的query_cache_min_res_unit参数已经

足够大了。如果 Qcache_total_blocks比Qcache_queries_in_cache多很多，则需要增加 query_cache_min_res_unit的大小。

Qcache_queries_in_cache*query_cache_min_res_unit（sql文本和表信息所在的 block占用的内存很小，可以忽略）如果远远大于query_cache_size-Qcache_free_memory，那么

可以尝试减小 query_cache_min_res_unit的值。
调整大小

如果Qcache_lowmem_prunes增长迅速，意味着很多缓存因为内存不够而被释放，而不是因为相关表被更新。尝试加大query_cache_size，尽量使Qcache_lowmem_prunes零增长。
启动参数

show variables like 'query_cache%'可以看到这些信息。

query_cache_limit
    如果单个查询结果大于这个值，则不Cache
query_cache_size
    分配给QC的内存。如果设为0，则相当于禁用QC。要注意QC必须使用大约 40KB来存储它的结构，如果设定小于40KB,则相当于禁用QC。QC存储的最小单位是1024 byte，所以如

果你设定了一个不是1024的倍数的值，这个值会被四舍五入到最接近当前值的等于1024的倍数的值。
query_cache_type
    0 完全禁止QC，不受SQL语句控制（另外可能要注意的是，即使这里禁用，上面一个参数所设定的内存大小还是会被分配）；1启用QC，可以在SQL语句使用SQL_NO_CACHE禁用；

2可以在SQL语句使用SQL_CACHE启用。
query_cache_min_res_unit
    每次给QC结果分配内存的大小

状态

show status like 'Qcache%'可以看到这些信息。

Qcache_free_blocks
    当一个表被更新之後，和它相关的cache blocks将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。这些blocks将会被统计到这个值来。可以用FLUSH

QUERY CACHE语句来清空free blocks。
Qcache_free_memory
    可用内存，如果很小，考虑增加query_cache_size
Qcache_hits
    自mysql进程启动起，cache的命中数量
Qcache_inserts
    自mysql进程启动起，被增加进QC的数量
Qcache_lowmem_prunes
    由于内存过少而导致QC被删除的条数。加大query_cache_size，尽可能保持这个值0增长。
Qcache_not_cached
    自mysql进程启动起，没有被cache的只读查询数量（包括select,show,use,desc等）
Qcache_queries_in_cache
    当前被cache的SQL数量
Qcache_total_blocks
    在QC中的blocks数。一个query可能被多个blocks存储，而这几个blocks中的最後一个，未用满的内存将会被释放掉。例如一个QC结果要占6KB内存，如果

query_cache_min_res_unit是 4KB，则最後将会生成3个blocks，第一个block用来存储sql语句文本，这个不会被统计到query+cache_size里，第二个 block为4KB，第三个block为

2KB（先allocate4KB，然後释放多馀的2KB）。每个表，当第一个和它有关的SQL查询被CACHE 的时候，会使用一个block来存储表信息。也就是说，block会被用在三处地方：表信

息，SQL文本，查询结果。

排序缓冲

当一个查询需要对结果进行排序的时候，MySQL会分配一定的内存用来排序。这个内存大小由sort_buffer_size来控制。记得，这个参数是针对每个查询的，而不是所有查询总共可

分配的量。

如果sort_buffer_size不够大，排序的结果将会被分段写入临时文件里。每次结束之後再把文件中的排序结果拿出来合并，进行再次排序，直到得出最後结果。sort_buffer_size

越小，合并的次数就越多。合并次数可以通过状态变量Sort_merge_passes获得。理论上，Sort_merge_passes越小，排序越快。但是在实际应用中可能并非如此。

sort_buffer_size如何设置需要根据实际运行环境来进行测试。如果实在不知道如何测试，那么就设到使Sort_merge_passes为0吧。

read_buffer_size read_rnd_buffer_size join_buffer_size thread_cache
MyISAM
key_buffer_size

CACHE INDEX Syntax
innodb

innodb_buffer_pool_size
    这是和innodb有关的最重要的一个参数。这个参数指定了innodb缓存池的大小。这个缓存池被用来存储

innodb_file_per_table innodb_additional_mem_pool_size=80M innodb_log_file_size=1G innodb_log_buffer_size=16M innodb_flush_method=O_DIRECT

(max_connections * (sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size)) + key_buffer + innodb_bufer_pool_size + query_cache +

tmp_table_size
系统相关

linux:

echo -n 0 >/proc/sys/vm/swappiness

huge page

memlock
优化工具

mysqltuner