MySQL参数调整方法

开发技术作者：chen 2024-03-27 18:20:02

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

这篇文章主要讲解了“MySQL参数调整方法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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表，要预留更多的内存给操作系统。