浅谈MySQL数据库优化

核心提示： 1.3 MySQL(和PHP搭配之最佳组合)服务器关键参数优化 key_buffer_size 这个参数对MyISAM表来说非常重要，如果我们的系统只是使用MyISAM表，浅谈MySQL数据库优化(3)，可以把它设置为操作系统物理内存的 30-40%，取决于索引大小、数据量以及负载，用crash-me测试检测出的许多

1.3 MySQL(和PHP搭配之最佳组合)服务器关键参数优化

key_buffer_size

这个参数对MyISAM表来说非常重要。如果我们的系统只是使用MyISAM表，可以把它设置为操作系统物理内存的 30-40%。取决于索引大小、数据量以及负载，MyISAM表会使用操作系统的缓存来缓存数据，因此需要留出部分内存给它们，很多情况下数据比索引大多了。尽管如此，需要总是检查是否所有的 key_buffer都被利用了 。如果*.MYI 文件只有 1GB，而 key_buffer 却设置为 4GB 的话就太浪费了。如果没有MyISAM表，那么也保留16-32MB 的 key_buffer_size 以供临时表索引使用，也就是不要禁止这个参数（设置为0）。

我们下边给出几个比较重要的MySQL(和PHP搭配之最佳组合)服务器参数，这里我们着重讲述InooDB引擎相关的参数。

innodb_buffer_pool_size

该参数对Innodb表来说非常重要。Innodb相比MyISAM表对缓冲更为敏感。MyISAM可以在默认的 key_buffer_size 设置下运行的可以，然而Innodb在默认的innodb_buffer_pool_size 设置下却跟蜗牛似的。由于Innodb把数据和索引都缓存起来，无需留给操作系统太多的内存，因此如果只需要用Innodb并且系统地数据量非常大的话则可以设置它高达 70-80% 的可用内存。

有些总结中说到，在 Linux x86 上不要把内存设置太高，内存使用下面的加起来不要超过2G，这个我们提醒一下。

innodb_buffer_pool_size+ key_buffer_size+
max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB < 2G

innodb_additional_mem_pool_size

InnoDB 用来存储数据字典(data dictionary)信息和其它内部数据结构(internal data structures)的存储器组合(memory pool)大小。理想的值为 2M，如果有更多的表你就需要在这里重新分配。如果 InnoDB 用尽这个池中的所有内存，它将从操作系统中分配内存，并将错误信息写入 MySQL(和PHP搭配之最佳组合) 的错误日志中。在 my.ini 中以数字格式设置。

innodb_log_file_size

在高写入负载尤其是大数据集的情况下很重要。这个值越大则性能相对越高，但是要注意到可能会增加恢复时间。我们经常设置为 64-512MB，根据服务器文件系统大小而异。

innodb_log_buffer_size

默认的设置在中等强度写入负载以及较短事务的情况下，服务器性能还可以。如果存在更新操作峰值或者负载较大，就应该考虑加大它的值了。如果它的值设置太高了，可能会浪费内存 -- 它每秒都会刷新一次，因此无需设置超过1秒所需的内存空间。通常 8-16MB 就足够了。越小的系统它的值越小。

innodb_flush_log_at_trx_commit

是否为Innodb比MyISAM慢1000倍而头大？看来也许你忘了修改这个参数了。默认值是 1，这意味着每次提交的更新事务（或者每个事务之外的语句）都会刷新到磁盘中，而这相当耗费资源。很多应用程序，尤其是从 MyISAM转变过来的那些，把它的值设置为 2 就可以了，也就是不把日志刷新到磁盘上，而只刷新到操作系统的缓存上。日志仍然会每秒刷新到磁盘中去，因此通常不会丢失每秒1-2次更新的消耗。如果设置为 0 就快很多了，不过也相对不安全了 -- MySQL(和PHP搭配之最佳组合)服务器崩溃时就会丢失一些事务。设置为 2 只会丢失刷新到操作系统缓存的那部分事务。

innodb_log_files_in_group

日志组中的日志文件数目。InnoDB 以环型方式(circular fashion)写入文件。数值 3 被推荐使用。在 my.ini 中以数字格式设置。

innodb_lock_wait_timeout

在回滚(rooled back)之前，InnoDB 事务将等待超时的时间(单位 秒)。InnoDB 会自动检查自身在锁定表与事务回滚时的事务死锁。如果使用 LOCK TABLES 命令，或在同一个事务中使用其它事务安全型表处理器(transaction safe table handlers than InnoDB)，那么可能会发生一个 InnoDB 无法注意到的死锁。在这种情况下超时将用来解决这个问题。这个参数的默认值为 50 秒。在 my.ini 中以数字格式设置。

table_cache

打开一个表的开销可能很大。例如MyISAM把MYI文件头标志该表正在使用中。你肯定不希望这种操作太频繁，所以通常要加大缓存数量，使得足以最大限度地缓存打开的表。它需要用到操作系统的资源以及内存，对当前的硬件配置来说当然不是什么问题了。如果你有200多个表的话，那么设置为 1024 也许比较合适（每个线程都需要打开表），如果连接数比较大那么就加大它的值。我曾经见过设置为 100,000 的情况。

tmp_table_size

如果内存内的临时表超过该值，MySQL(和PHP搭配之最佳组合)自动将它转换为硬盘上的MyISAM表,所以我们在前边讲述key_buffer_size的时候曾经说过即使我们的系统中没有MyISAM表也要保留key_buffer_size的值为16-32M。如果系统有很多GROUP BY查询并且有大量内存，则可以增加tmp_table_size的值。

thread_cache

线程的创建和销毁的开销可能很大，因为每个线程的连接/断开都需要。我通常至少设置为 16。如果应用程序中有大量的跳跃并发连接并且 Threads_Created的值也比较大，那么我就会加大它的值。它的目的是在通常的操作中无需创建新线程。

query_cache

如果你的应用程序有大量读，而且没有应用程序级别的缓存，那么这很有用。不要把它设置太大了，因为想要维护它也需要不少开销，这会导致MySQL(和PHP搭配之最佳组合)变慢。通常设置为 32-512Mb。设置完之后最好是跟踪一段时间，查看是否运行良好。在一定的负载压力下，如果缓存命中率太低了，就启用它。

max_connections

允许的并行客户端连接数目。根据系统的连接数来决定该参数的大小。

sort_buffer

就像你看到的上面这些全局表量，它们都是依据硬件配置以及不同的存储引擎而不同，但是会话变量通常是根据不同的负载来设定的。如果你只有一些简单的查询，那么就无需增加 sort_buffer_size 的值了，尽管你有 64GB 的内存。搞不好也许会降低性能。我通常在分析系统负载后才来设置会话变量。

innodb_file_io_threads

InnoDB 中的文件 I/O 线程。 通常设置为 4，但是在 Windows 下可以设定一个更大的值以提高磁盘 I/O。在 my.ini 中以数字格式设置。

innodb_fast_shutdown

如果把这个参数设置为0，InnoDB在关闭之前做一个完全净化和一个插入缓冲合并。这些操作要花几分钟时间，设置在极端情况下要几个小时。如果你设置这个参数为1，InnoDB在关闭之时跳过这些操作。默认值为1。如果你设置这个值为2 (在Netware无此值)， InnoDB将刷新它的日志然后冷关机，仿佛MySQL(和PHP搭配之最佳组合)崩溃一样。已提交的事务不会被丢失，但在下一次启动之时会做一个崩溃恢复。

innodb_max_dirty_pages_pct

这是一个范围从0到100的整数。默认是90。InnoDB中的主线程试着从缓冲池写页面，使得脏页（没有被写的页面）的百分比不超过这个值。如果你有SUPER权限，这个百分比可以在服务器运行时按下面来改变：
SET GLOBAL innodb_max_dirty_pages_pct = value;

innodb_thread_concurrency

InnoDB试着在InnoDB内保持操作系统线程的数量少于或等于这个参数给出的限制。如果有性能问题，并且SHOW INNODB STATUS显示许多线程在等待信号，可以让线程“thrashing” ，并且设置这个参数更小或更大。如果你的计算机有多个处理器和磁盘，你可以试着这个值更大以更好地利用计算机的资源。一个推荐的值是系统上处理器和磁盘的个数之和。值为500或比500大会禁止调用并发检查。默认值是20，并且如果设置大于或等于20，并发检查将被禁止。

THREAD_STAC

每个线程的堆栈大小。用crash-me测试检测出的许多限制取决于该值。 默认值足够大，可以满足普通操作。