MySQL基础笔记(11)-参数优化小结

MySQL服务器内置许多参数，就MySQL5.5版本而言，就有三百多个参数，大部分参数不需要开发者去配置，但是有小部分参数需要开发者熟悉其含义和懂得配置。本文将从以下角度小结一下对于MySQL服务器的性能有较大影响的参数，即内存参数、I/O参数、并发参数、其他参数四个方面展开；本文基于Innodb引擎，MySQL5.5版本。

对于MySQL服务器性能来说有许多的影响因素，主要分为以下：
1、SQL优化（慢查询优化，插入速度优化）
2、MySQL服务器优化（也就是接下来要说的参数优化）
3、服务器自身性能（内存大小）
4、磁盘I/O

---

一、全局参数和局部参数

MySQL的内置参数有全局参数和局部参数之分。全局参数是当前连接的用户设置之后会在整个数据库中起作用，而局部参数只会在当前连接下起作用，它们的语法为：

• 设置全局参数
  set global 参数名=值
set @@global.参数名=值
• 设置局部参数
  set [session] 参数名=值
set @@参数名=值

常见的全局参数有最大连接数、最大并发数等；常见的局部参数有事务隔离级别、自动提交事务等。

• 查询内置参数
  show [global] variables like 参数名

二、内存参数调优

对于MySQL服务器的性能来说，内存参数对其影响很大,以下参数均为单个连接占用的缓冲区内存大小，不能设置得过大，要根据预计的连接数和服务器可用内存进行设置：

• join_buffer_size:当我们的join是ALL,index,rang或者Index_merge的时候使用的buffer，当频繁使用连接查询时需要将其提高。
• sort_buffer_size:系统对数据进行排序时用到的buffer，当使用group by或者order by的频繁高时需要将其提高。
• read_buffer_size:如果对表的顺序扫描非常频繁，可以通过增加该变量值以及内存缓冲区大小提高其性能。
• read_rnd_buffer_size:这是单条数据的读取缓冲区，一般不需要设置。

以上四个是单个连接占用的缓冲区，而对于InnoDB引擎来说，还有一个缓冲池大小需要设置，这将影响缓冲池中能产生多少个缓存区。一般设置为服务器内存的75％以上。
innodb_buffer_pool_size

三、I/O参数调优

因为InnoDB是一种事务性的存储引擎，在每次事务提交后并不是把磁盘中的数据立即更新，而是将事务写入到binlog中，根据不同的策略将binlog中的记录在写入到磁盘中，这时产生了大量的I/O。故对日志参数的优化即是对I/O性能的优化。

• innodb_log_file_size :每个事务日志的大小，如果事务繁忙，可将其适度加大。
• innodb_log_files_in_group:事务日志的个数
• innodb_log_buffer_size:事务日志缓冲区大小，一般在32M~128M，由
  innodb_log_file_size * innodb_log_files_in_group确定。
• 　　innodb_flush_log_at_trx_commit :将事务日志刷新到磁盘中的策略，有三种选择：
  0：事务提交到事务日志中的每秒钟刷新一次到磁盘中；
  1：事务每次提交到事务日志中后立即刷新到磁盘中，这是默认选项；
  2：事务提交到事务日志文件中后，每秒钟刷新一次到磁盘中，这是推荐的选项。
• innodb_file_per_table:为1时表示为每个表单独建立表空间，这增加了磁盘内存占用量，但是提高了性能。

四、并发参数调优

MySQL服务器有两个个参数对于并发影响重大。一是max_connections，也就是最大的同时连接数量，它的默认值为200，一般来说这是不够的，需要根据业务去设置它的值。二是max_used_connections,即是同时并发操作事务的数量，一般它的大小是max_connections的80%。

五、其他优化配置

• thread_cache_size:表示线程池中存放的线程数，一般1G内存服务器使用8,2G则使用16,3G使用32，3G以上使用64。
• thread_stack:每个线程创建时所占用的大小,max_connctions*thread_stack < innodb_buffer_pool_size。
• table_open_cache:MySQL服务器会对打开的表进行缓存，而这个参数可以用来设置需要被缓存的表的数量，一般2G内存的服务器会被设置在256~512之间，4G的话这个值可设置为2024。通过show status like 'open_tables'可查看已打开表的个数。