MYSQL服务几个提升性能的配置

讲解几个可以提升MYSQL服务性能的配置，在my.ini或my.cnf配置文件中进行配置。这几个参数主要是减少MYSQL与磁盘的交互次数，来提升性能。减少磁盘交互的必然后果就是占用内存空间大了，这样性能才能提升。

innodb_buffer_pool_size与innodb_buffer_pool_instances

innodb_buffer_pool_size:

与MyISAM不同，InnoDB使用缓冲池来缓存索引和行数据。设置的值越大，访问表中数据所需的磁盘I/O就越少。在专用数据库服务器上，您可以将此参数设置为机器物理内存大小的80%。不过，不要将其设置得太大，因为物理内存的竞争可能会导致操作系统中的分页。请注意，在32位系统上，每个进程的用户级内存可能限制为2-3.5G，因此不要将其设置得太高。

innodb_buffer_pool_instances:

InnoDB缓冲池划分的区域数。对于缓冲池在数千GB范围内的系统，通过减少不同线程对缓存页的读写时的争用，将缓冲池划分为不同的实例可以提高并发性。

innodb_buffer_pool主要用于缓冲索引数据和行数据。该区域越大，增删改查在内存中完成的机会就越多，磁盘交互就越少，性能就越好。通过配置innodb_buffer_pool_instances参数，配置多个实例，提高该区域的并发度，进一步提升性能。这两个参数配合使用，效果更好。

innodb_flush_log_at_trx_commit与sync_binlog

这两个参数都是设置日志文件刷新磁盘的频率。innodb_flush_log_at_trx_commit是InnoDB特有的。

innodb_flush_log_at_trx_commit:

1. 当 innodb_flush_log_at_trx_commit 取值为 0 的时候，log buffer 会 每秒写入到日志文件并刷写（flush）到磁盘。但每次事务提交不会有任何影响，也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下，MySQL性能最好，但如果 mysqld 进程崩溃，通常会导致最后 1s 的日志丢失。
2. 当取值为 1 时，每次事务提交时，log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置，但由于每次事务都需要进行磁盘I/O，所以也最慢。
3. 当取值为 2 时，每次事务提交会写入日志文件，但并不会立即刷写到磁盘，日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃，由于日志已经写入到系统缓存，所以并不会丢失数据；在操作系统崩溃的情况下，通常会导致最后 1s 的日志丢失。

上面说到的「最后 1s」并不是绝对的，有的时候会丢失更多数据。有时候由于调度的问题，每秒刷写（once-per-second
flushing）并不能保证 100% 执行。对于一些数据一致性和完整性要求不高的应用，配置为 2 就足够了；如果为了最高性能，可以设置为
0。有些应用，如支付服务，对一致性和完整性要求很高，所以即使最慢，也最好设置为 1.

由上面描述可知，innodb_flush_log_at_trx_commit参数是对redo日志刷盘频率的设定。这里解释一下设置为0，1和2时都是什么意思。
当设置为0时，log buffer定时一秒钟写入一次日志文件，并刷写到磁盘。如果这一秒钟内，有500个事务提交了，那就刷写500条数据。反正就是定时一秒刷写一次，不管你这一秒有多少事务。所以当mysqld进程崩溃后，最后这一秒的内提交的全部数据，都有丢失的风险。
当设置为1时，就是只要有一次事务提交，那就将log buffer数据写入日志文件并刷盘。可见这个设置的粒度小了很多，频率也大很多。这样是最安全的。
当设置为2时，每次事务提交，就会写入日志文件，但是不会立即刷盘，意思就是，logbuffer中数据写入了日志文件，但是写入的这部分内容，以内存的形式保存在日志文件中，并没有写入到磁盘中。类似于我们编辑了一个文件，没点保存。日志文件定时每秒写入一次磁盘中。所以，这种情况下，即使mysqld进程崩溃，数据也不受影响，因为已经写入了系统文件缓存中。但是当操作系统崩溃时，会丢失系统文件缓存的那1秒钟的数据。

所以，对于数据安全不是太严格的要求下，完全可以设置为0。即使丢，也是丢1秒钟的数据，损失量并不大。

sync_binlog：
该参数是设置binlog刷盘的频率，设置n次后，binlog文件写入n次，刷一次盘。
当n=0(默认)时，不主动同步，而依赖操作系统本身不定期把文件内容 flush 到磁盘。设为 1 最安全，在每个语句或事务后同步一次 binary log，即使在崩溃时也最多丢失一个语句或事务的日志，但因此也最慢。

大多数情况下，对数据的一致性并没有很严格的要求，所以并不会把 sync_binlog 配置成 1. 为了追求高并发，提升性能，可以设置为 100 或直接用 0. 而和 innodb_flush_log_at_trx_commit 一样，对于支付服务这样的应用，还是比较推荐 sync_binlog = 1.

bulk_insert_buffer_size

网上很多博客说加大这个参数的值，允许insert语句拼接更多的values值。通过查看mysql官网描述可以知道，该参数是对MyISAM 引擎下的表才起作用的。而对于InnoDB引擎的表没有作用。所以设置该参数没有作用。

tmp_table_size

内部（内存中）临时表的最大大小。如果表的增长大于此值，它将自动转换为基于磁盘的表。此限制仅适用于单个表。其中可能有很多。
此配置与innodb_buffer_pool的区别是，innodb_buffer_pool是索引和行数据的缓冲池，tmp_table_size是临时表的内存空间。注意区分。增大该配置，也有助于减少磁盘的使用次数。