Jade Dungeon

MySQL优化

配置优化

即使是经验老道的人也会犯错，会引起很多麻烦。所以在盲目的运用这些推荐之前，请记住 下面的内容：

  • 一次只改变一个设置！这是测试改变是否有益的唯一方法。
  • 大多数配置能在运行时使用SET GLOBAL改变。这是非常便捷的方法它能使你在出问题 后快速撤销变更。但是，要永久生效你需要在配置文件里做出改动。
  • 一个变更即使重启了MySQL也没起作用？请确定你使用了正确的配置文件。请确定你把 配置放在了正确的区域内(所有这篇文章提到的配置都属于 [mysqld])
  • 服务器在改动一个配置后启不来了：请确定你使用了正确的单位。例如， innodb_buffer_pool_size的单位是MB而max_connection是没有单位的。
  • 不要在一个配置文件里出现重复的配置项。如果你想追踪改动，请使用版本控制。
  • 不要用天真的计算方法，例如」现在我的服务器的内存是之前的2倍，所以我得把所有 数值都改成之前的2倍「。

基本配置

你需要经常察看以下3个配置项。不然，可能很快就会出问题。

innodb_buffer_pool_size

这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方：

这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。

典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到 MySQL 5.1，它都难于调整，因为一方面你想让它更大来提高性能，另一方面你想让它更小 来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后，崩溃恢复的性能的到了很大提升， 这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5，redo日志的 总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。

一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的 写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6， 你可以一开始就把它这是成4G。

max_connections

如果你经常看到‘Too many connections’错误，是因为max_connections的值太低了。这 非常常见因为应用程序没有正确的关闭数据库连接，你需要比默认的151连接数更大的值。 max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个 或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池 有助于解决这一问题。

InnoDB配置

从MySQL 5.5版本开始，InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要 多得多。那也是为什么它需要小心配置的原因。

innodb_file_per_table

这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里 （innodb_file_per_table = OFF） 或者为每张表的数据单独放在一个.ibd文件 （innodb_file_per_table = ON）。每张表一个文件允许你在drop、truncate或者 rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。 但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是： 有非常多的表（比如10k+）。

MySQL 5.6中，这个属性默认值是ON，因此大部分情况下你什么都不需要做。对于之前的 版本你必需在加载数据之前将这个属性设置为ON，因为它只对新创建的表有影响。

innodb_flush_log_at_trx_commit

默认值为1，表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是 最合适的，比如在一个主节点上。但是对于磁盘（读写）速度较慢的系统，它会带来很巨大 的开销，因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致 不太可靠（reliable）因为提交的事务仅仅每秒才flush一次到redo日志，但对于一些场景 是可以接受的，比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了 ，但在系统崩溃时可能丢失一些数据：只适用于备份节点。

innodb_flush_method

这项配置决定了数据和日志写入硬盘的方式。一般来说，如果你有硬件RAID控制器，并且其 独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT； 否则，大多数情况下应将其设为fdatasync（默认值）。sysbench是一个可以帮助你决定 这个选项的好工具。

innodb_log_buffer_size

这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了， 但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满 并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加 innodb_log_buffer_size。

其他设置

query_cache_size

query cache（查询缓存）是一个众所周知的瓶颈，甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用，设置query_cache_size = 0（现在MySQL 5.6的默认值 ）并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如 memcache或redis）。如果你已经为你的应用启用了query cache并且还没有发现任何问题， query cache可能对你有用。这是如果你想停用它，那就得小心了。

log_bin

如果你想让数据库服务器充当主节点的备份节点，那么开启二进制日志是必须的。如果这么 做了之后，还别忘了设置server_id为一个唯一的值。就算只有一个服务器，如果你想做 基于时间点的数据恢复，这（开启二进制日志）也是很有用的：从你最近的备份中恢复 （全量备份），并应用二进制日志中的修改（增量备份）。二进制日志一旦创建就将永久 保存。所以如果你不想让磁盘空间耗尽，你可以用 PURGE BINARY LOGS 来清除旧文件，或者设置expire_logs_days来指定过多少天日志将被自动清除。

记录二进制日志不是没有开销的，所以如果你在一个非主节点的复制节点上不需要它的话， 那么建议关闭这个选项。

skip_name_resolve

当客户端连接数据库服务器时，服务器会进行主机名解析，并且当DNS很慢时，建立连接 也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。 唯一的局限是之后GRANT语句中只能使用IP地址了，因此在添加这项设置到一个已有系统中 必须格外小心。

总结

当然还有其他的设置可以起作用，取决于你的负载或硬件：在慢内存和快磁盘、高并发和 写密集型负载情况下，你将需要特殊的调整。然而这里的目标是使得你可以快速地获得一个 稳健的MySQL配置，而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档找出哪些 设置对你来说很重要上。

批量插入

一条SQL语句插入多条数据。

常用的插入语句如： 

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('1', 'userid_1', 'content_1', 1);

修改成： 

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并 后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率 ，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的 IO。

在事务中进行插入处理

把插入修改成： 

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一 个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有 插入都在执行后才进行提交操作。

数据有序插入

数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键： 

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('2', 'userid_2', 'content_2',2);

修改成： 

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('1', 'userid_1', 'content_1', 1);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)
    VALUES ('2', 'userid_2', 'content_2',2);

由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照 innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高， 并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会 消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘 操作。

从测试结果来看，该优化方法的性能有所提高，但是提高并不是很明显。

性能综合测试

这里提供了同时使用上面三种方法进行INSERT效率优化的测试。

从测试结果可以看到，合并数据+事务的方法在较小数据量时，性能提高是很明显的，数据 量较大时（1千万以上），性能会急剧下降，这是由于此时数据量超过了innodb_buffer的 容量，每次定位索引涉及较多的磁盘读写操作，性能下降较快。而使用合并数据+事务+有序 数据的方式在数据量达到千万级以上表现依旧是良好，在数据量较大时，有序数据索引定位 较为方便，不需要频繁对磁盘进行读写操作，所以可以维持较高的性能。

注意事项

  1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过 max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。
  2. 事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个 值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是， 在数据达到这个这个值前进行事务提交。
© 2015 jade-dungeon.net. Valid CSS & XHTML
Website template by Arcsin