MySQL参数调整方法

共计 4323 个字符，预计需要花费 11 分钟才能阅读完成。

这篇文章主要讲解了“MySQL 参数调整方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着丸趣 TV 小编的思路慢慢深入，一起来研究和学习“MySQL 参数调整方法”吧！

1  MySQL 参数调整 1.1  tx_isolation 事务参数 1.1.1  事务的特征

事务具有四个特性：原子性（Atomiocity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability），这四个特性简称 ACID 特性。

原子性：事务是数据库的逻辑工作单位，事务中包括的所有操作要么都做，要么都不做。

一致性：事务执行的结果必须是使数据库从一个一致性的状态变到另外一个一致性状态。

隔离性：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对其他事务是隔离的，并发执行的各个事务之间互相不干扰。

持久性：一个事务一旦成功提交，对数据库中数据的修改就是持久性的。接下来其他的其他。

1.1.2  四种事务隔离级别

READ UNCOMMITED：SELECT 的时候允许脏读，即 SELECT 会读取其他事务修改而还没有提交的数据。

READ COMMITED：SELECT 的时候不能重复读，即同一个事务中两次执行同样的查询语句，若在第一次与第二次查询之间时间段，其他事务又刚好修改了其查询的数据且提交了，则两次读到的数据不一致。

REPEATABLE READ：SELECT 的时候可以重复读，即同一个事务中两次执行同样的查询语句，得到的数据始终都是一致的。

SERIALIZABLE：与可重复读的唯一区别是，默认把普通的 SELECT 语句改成 SELECT ….
LOCK IN SHARE MODE。即为查询语句涉及到的数据加上共享琐，阻塞其他事务修改真实数据。

1.1.3  隔离级别选择

综合考虑系统的应用和性能，建议使用默认的 REPEATABLE READ 级别，或者 READ COMMITED 级别。

1.2  innodb_buffer_pool_size

这是 InnoDB 最重要的设置，对 InnoDB 性能有决定性的影响。默认的设置只有 8M，所以默认的数据库设置下面 InnoDB 性能很差。在只有 InnoDB 存储引擎的数据库服务器上面，可以设置 60-80% 的内存。更精确一点，在内存容量允许的情况下面设置比 InnoDB
tablespaces 大 10% 的内存大小。它用来设置用于缓存 InnoDB 索引及数据块的内存区域大小，类似于 MyISAM 存储引擎的 key_buffer_size 参数，当然，可能更像是 Oracle 的 db_cache_size。简单来说，当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。

和 key_buffer_size 对于 MyISAM 引擎一样，innodb_buffer_pool_size 设置了 InnoDB 存储引擎需求最大的一块内存区域的大小，直接关系到 InnoDB 存储引擎的性能，所以如果我们有足够的内存，尽可将该参数设置到足够打，将尽可能多的 InnoDB 的索引及数据都放入到该缓存区域中，直至全部。

我们可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率，并根据命中率来调整 innodb_buffer_pool_size 参数大小进行优化。

1.3  innodb_flush_log_at_trx_commit

该参数设定了事务提交时内存中 log 信息的刷新频率，有 3 个值, 默认设置的是 1，也就是同步刷新：

= 0 时，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何 mysqld 进程的崩溃会删除崩溃前最后一秒的事务；

= 1 时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢；

= 2 时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。

1.4  innodb_log_buffer_size

INNODB 日志缓存的大小，磁盘速度是很慢的，直接将 log 写道磁盘会影响 InnoDB 的性能，该参数设定了 log buffer 的大小，一般 4M。如果有大的 blob 操作，可以适当增大。

1.5  innodb_log_file_size

INNODB 日志文件的大小，该参数决定了 recovery speed。太大的话 recovery 就会比较慢，太小了影响查询性能，一般取 1GB 可以兼顾性能和 recovery 的速度。

1.6  innodb_data_file_path

该参数指定表数据和索引存储的空间，可以是一个或者
多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以 8MB 为单位）以 容纳额外的数据。例如：innodb_data_file_path=/disk1
/ibdata1:900M;/disk2/ibdata2:50M:autoextend 两个数据文件放在不同的磁盘上（）。数据首先放在 ibdata1 中，当达到 900M 以后，数据就放在 ibdata2 中。一旦达到 50MB，ibdata2 将以 8MB 为单位自动增长。如果磁盘满了，需要在另外的磁盘上面 增加一个数据文件。

1.7  innodb_data_home_dir

该参数设置了放置表空间数据的目录，默认在 mysql 的数据目录，由 MySQL 参数 datadir 指定。设置到和 MySQL 安装文件不同的分区可以提高性能。可以与 innodb_data_file_path 配合使用，用 innodb_data_home_dir 来指定共同位置，然后在通过 inndo_data_file_path 来指定文件名即可。

1.8  innodb_file_io_threads

此参数指定 InnoDB 表可用的文件 I/O 线程数，建议在非 Windows 平台中这个参数设置为 4。

1.9  innodb_flush_logs_at_trx_commit

该参数设定了事务提交时内存中 log 信息的处理。

1) = 1 时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。默认为此值。

2) = 2 时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。

3) = 0 时，日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何 mysqld 进程的崩溃会删除崩溃前最后一秒的事务。

1.10  innodb_flush_method

影响了服务器 flush 数据或日志文件的方法。设置 InnoDB 同步 IO 的方式

1)  Default
使用 fsync（）。

2)  O_SYNC
以 sync 模式打开文件，通常比较慢。

3)  O_DIRECT，在 Linux 上使用 Direct
IO。跳过了操作系统的文件系统 Disk Cache，让 MySQL 直接读写磁盘，可以显著提高速度，特别是在 RAID 系统上。避免额外的数据复制和 double buffering（mysql buffering 和 OS buffering）。避免双缓冲 (double buffering) 和降低 swap 的压力。大多数情况下可以提高性能. 但是注意如果 RAID cache 不够的话, 写 IO 的操作会有麻烦。

1.11  tmp_table_size

tmp_table_size，它规定了内存临时表的最大值，每个线程都要分配。（实际起限制作用的是 tmp_table_size 和 max_heap_table_size 的最小值。）如果内存临时表超出了限制，MySQL 就会自动地把它转化为基于磁盘的 MyISAM 表，此参数不限制 create tables 创建的内存表，存储在指定的 tmpdir 目录下，默认:

mysql show variables like tmpdir

1.12  max_heap_table_size

这个变量定义了用户可以创建的内存表 (memory table) 的大小。这个值用来计算内存表的最大行数值。如果内存内的临时表超过该值，MySQL 自动将它转换为硬盘上的 MyISAM 表。这个变量支持动态改变，即 set @max_heap_table_size=#，但是对于已经存在的内存表就没有什么用了，除非这个表被重新创建 (create table) 或者修改 (alter table) 或者 truncate table。服务重启也会设置已经存在的内存表为全局 max_heap_table_size 的值。这个变量和 tmp_table_size 一起限制了内部内存表的大小。

1.13  long_query_time

long_query_time，对 SQL 执行设定一个时间限制，单位为秒，超出这个时间，测返回错误。该参数可动态设置：set
global long_query_time=1800;

1.14  join_buffer_size

如果两个表关联查询，但关联的字段又没有索引时，使用此参数分配的内存空间。

1.15  key_buffer_size

MyISAM 表的索引块分配了缓冲区，由所有线程共享。key_buffer_size 是索引块缓冲区的大小。键值缓冲区即为键值缓存. 用 key_buffer_size 结合 Key_blocks_unused 状态变量和缓冲区块大小，可以确定使用的键值缓冲区的比例。从 key_cache_block_size 服务器变量可以获得缓冲区块大小。使用的缓冲区的比例为：

1 – ((Key_blocks_unused * key_cache_block_size) /
key_buffer_size)

注意：MyISAM 的数据文件读取依赖于操作系统自身的 IO 缓存，如果有 MyISAM 表，要预留更多的内存给操作系统。

1.16  wait_timeout

wait_timeout，设置非互交连接会话的空闲超时时间，单位为 s。

1.17  interactive_timeout

interactive_timeout，设置互交连接会话的空闲超时时间，单位为 s。

感谢各位的阅读，以上就是“MySQL 参数调整方法”的内容了，经过本文的学习后，相信大家对 MySQL 参数调整方法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是丸趣 TV，丸趣 TV 小编将为大家推送更多相关知识点的文章，欢迎关注！