文章目录
- 一、全局参数和局部参数
- 二、内存参数调优
- 三、I/O参数调优
- 四、并发参数调优
- 五、其他优化配置
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'可查看已打开表的个数。
