my.cnf配置文件调优

mysql数据库的性能调优首先要考虑的就是表结构设计，一个糟糕的设计模式即使在性能强劲的服务器上运行时，也会表现得很差。与设计模式相似，查询语句也会影响mysql的性能，应该避免写出低效的sql查询语句。最后要考虑的就是参数优化，mysql数据库默认设置的性能非常差，只能起到功能测试的作用，不能在生产环境中运行，因此要对一些参数进行调整。

per_thread_buffers参数调优

我们可以将per_thread_buffers理解为Oracle的PGA（程序全局区域）为每个连接到mysql的用户进程分配的内存。其包含的参数及说明具体如下。

read_buffer_size：用于设置对表进行顺序扫描时每个线程分配的缓冲区大小。比如，在进行全表扫描时，mysql会按照数据的存储顺序依次读取数据块，每次读取的数据块会先暂存在read_buffer_size中，在缓冲区空间被写满或者数据全部被读取后，再将缓冲区中的数据返回给上层调用者，以提高效率。read_buffer_size的默认值为128KB。不要将这个参数设置得过大，一般在128KB~256KB即可。

read_rnd_buffer_size：用于设置对表进行随机读取时每个线程分配的缓冲区大小。比如，按照一个非索引字段做order by排序操作时，就会利用这个缓冲区来暂存读取的数据。read_rnd_buffer_size的默认值为256KB。不要将这个参数设置得过大，一般在128KB~256KB即可。

sort_buffer_size：在对表进行order by和group by排序操作时，由于排序的字段没有索引，因此会出现“Using filesort”。为了提高性能，可用此参数增加每个线程分配的缓冲区大小。sort_buffer_size的默认值为2MB。不要将这个参数设置得过大，一般在128KB~256KB即可。另外，当优化器执行计划中出现“Using filesort”的时候，一般需要通过增加索引的方式来消除它。

thread_stack：表示每个线程的堆栈大小，默认值为192KB。如果是64位操作系统，则将其设置为256KB即可，不要将这个参数设置得过大。

join_buffer_size：当表进行连接操作时，如果关联的字段没有索引，则优化器执行计划中会出现“Using join buffer”。为了提高性能，可用此参数增加每个线程分配的缓冲区大小。join_buffer_size的默认值为128KB。不要将这个参数设置得过大，一般在128KB~256KB即可。另外，当优化器执行计划中出现“Using join_buffer”的时候，一般需要通过增加索引的方式来消除它。

binlog_cache_size：一般来说，如果数据库中没有什么大事务，写入也不是特别频繁的话，那么将该参数的值设置为1MB~2MB是一个比较合适的选择。如果有很大的事务，则可以适当增加这个参数的大小，以获得更好的性能。

max_connections：可用来设置最大连接数，默认值为100，一般将其设置为512~1000即可。

global_buffers参数调优

我们可以将global_buffers理解为Oracle的SGA（系统全局区域）在内存中缓存从数据文件中检索出来的数据块，以提高数据查询和更新的性能。global_buffers主要包含的参数及说明如下。

innodb_buffer_pool_size：InnoDB存储引擎的核心参数，默认值为128MB，这个参数的大小通常设置为物理内存的60%~70%。

innodb_additional_mem_pool_size用于设置存储数据字典信息和其他内部数据结构的内存池大小。表越多，需要再这里分配的内存就越多。InnoDB如果用光了该内存池中的内存，就会从操作系统中分配内存，并且向MySQL错误日志中写入告警信息。innodb_additional_mem_pool_size的默认值是8MB，如果发现错误日志中已经有了相关的告警信息，就要适当地增加该参数的大小，一般设置为16MB即可。

innodb_log_buffer_size：用于设置重做日志所使用的缓冲区大小。InnoDB在写重做日志的时候，为了提高性能，会先将信息写入InnoDB日志缓冲区中。当日志缓冲区写满时，或者满足innodb_flush_log_trx_commit参数所设置的相应条件时，再将日志写到文件（或者同步到磁盘）中。其默认值为8MB，一般设置为16MB~64MB即可。

query_cache_size：用于设置缓存SELECT语句和结果集的缓冲区大小。

查询缓存在不同环境下的使用

查询缓存的功能是缓存查询语句和要传送到客户端的相应结果的集合。如果之后接收到同样的查询，那么服务器将会从查询缓存中检索结果，而不是再次分析和执行同样的查询。

注意：查询缓存绝不会返回过期数据。当数据被修改时，在查询缓存中的任何相关词条均会被转储清除。如果有某些表不常更改，同时又会接受大量相同的查询，那么查询缓存就非常有用了。

如果实际环境中的写操作很少，读操作很频繁，那么开启查询缓存的服务（即设置query_cache_type=1）就能明显提升性能。

如果实际环境中的写操作很频繁，那就不适合打开查询缓存的服务了，因为表的内容一旦发生更改，查询缓存的结果集就要随之刷新，频繁地刷新操作反而会大大降低性能。在这种情况下，建议关闭它（query_cache_type=0），同时设置query_cache_size=0和query_cache_limit=0。