MySql优化之InnoDB,4GB内存,多查询的my.ini中文配置方案详解

本文是一个针对 4G 内存系统（主要运行只有 InnoDB 表的 MySQL 并使用几个连接数执行复杂的查询）的 MySQL 配置文件方案

#开始配置信息
#描述：4GB 内存、只有 InnoDB、ACID、几个连接数、繁重的查询
#类型：系统
#结束配置信息

# 你可以复制该文件到 /etc/my.cnf 以设置全局的选项，复制到 mysql-data-dir/my.cnf 以设置服务器特有的选项（在本安装中该目录是 C:mysqldata ），复制到 ~/.my.cnf 以设置用户特有的选项。
#
# 在该文件中，你可以使用一个程序所支持的全部永久选项。
# 如果你想了解哪些选项是程序支持的，在运行程序时使用“--help”选项。
#
# 更多有关个别选项的详细信息也可以在手册中找到。

# 下面的选项将被 MySQL 客户端应用程序所读取。
# 注意，只有 MySQL 标准的客户端应用程序是被保证能读取到该章节的。
# 如果你希望你自己的 MySQL 客户端程序能够承兑这些值，你需要在 MySQL 客户端库初始化中作为一个选项来指定它。
#
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

# *** 应用程序特定的选项在下面 ***

# MySQL 服务器。
[mysqld]

# 通用配置选项
port = 3306
socket = /tmp/mysql.sock

# back_log 是指保持在操作系统监听队列中的连接数量，即在 MySQL 连接管理器线程处理它们之前的连接数量。
# 如果你有一个非常高的连接率并见到过“拒绝连接”的错误，你可能需要提高该值。
# 在你的系统文档中检查该参数的最大值。
# 试图将 back_log 设置得高于你操作系统的限制将不会起到任何作用。
back_log = 50

# 根本不用监听一个 TCP/IP 端口。
# 如果运行在相同主机上的所有进程都需要连接到 mysqld，这可能是一个安全增强。
# 所有与 mysqld 的互动都必须通过 Unix sockets（套接字）或命名管道进行。
# 注意，在 Windows 上使用该选项但却不启用命名管道（通过“enable-named-pipe”选项）将使得 mysqld 变得无用。
#
#skip-networking

# MySQL 允许的并发会话的最大数量。
# 其中的一个连接将被保留给拥有 SUPER 特权的用户，即使已经到达了连接限制，仍可以允许管理者登录。
max_connections = 100

# 每个主机允许的最大错误数量。
# 如果已到达该限制，主机将阻止对 MySQL 服务器的连接，直到运行“FLUSH HOSTS”或者服务器被重启。
# 在连接阶段的无效密码和其它错误将导致该值被提高。
# 请看全局计数器的“Aborted_connects”状态变量。
max_connect_errors = 10

# 所有线程打开表的数量。
# 提高该值将提高 mysqld 需要的文件描述符的数量。
# 因此，你必须确定要设置的打开文件数量，在“mysqld 安全”章节的“open-file-limit”变量中，允许到至少为 4096。
table_open_cache = 2048

# 启用外部文件级锁定。
# 启用文件锁定将有一个性能上的负面影响，因此，只有在如果你有多个数据库实例运行在相同的文件上（注意，有些限制仍旧被应用）或者如果你使用其它软件依靠在文件级上锁定 MyISAM 表时，才使用。
#external-locking

# 服务器可以处理的一个查询包的最大容量，以及服务器可以处理的最大查询大小（当工作在大型 BLOB 字段时很重要）。
# 动态扩大，对于每一个连接。
max_allowed_packet = 16M

# 在一个事务中能够为二进制日志 SQL 语句保持的缓存大小。
# 如果你经常使用大的、多语句的事务，你可以提高该值以获得更好的性能。
# 来自事务的所有语句被缓冲到二进制日志缓存，并在 COMMIT 之后立即被写入到二进制日志中。
# 如果事务大于该值，磁盘上的临时文件将被替代使用。
# 该缓冲在事务中第一个更新语句时分配给每个连接。
binlog_cache_size = 1M

# 一个单一的 HEAP（在内存中）表的最大允许大小。
# 该选项对偶然创建的一个非常大的 HEAP 表起保护作用，否则它将会使用完所有的内存资源。
max_heap_table_size = 64M

# 排序缓冲被用来执行一些 ORDER BY 和 GROUP BY 查询的排序。
# 如果已排序的数据没有进入到排序缓冲，一个基于磁盘的合并排序将被替代使用 - 请看“Sort_merge_passes”状态变量。
# 如果排序是需要的，将分配给每个线程。
sort_buffer_size = 8M

# 该缓冲被用来优化 FULL JOIN（没有索引的 JOIN）。
# 无论如何，该 JOIN 在大多数情况下对性能是非常坏的，但是设置该变量为一个大值将减少对性能的影响。
# 请看针对一定数量的 FULL JOIN 的“Select_full_join”状态变量。
# 如果 FULL JOIN 被发现，将分配给每个线程。
join_buffer_size = 8M

# 我们保持在一个缓存中的可重用的线程有好多。
# 当一个客户端断开连接时，如果在这之前的线程没有超过 thread_cache_size，客户端的线程将放在缓存中。
# 如果你有很多新的连接，这将大幅减少创建所需线程的数量。
# （如果你有一个很好的线程实现，这通常不会给出一个显著的性能改善。）
thread_cache_size = 8

# 这允许应用程序给予线程系统一个针对运行在相同时间的线程所需数量的提示。
# 该值只在支持 thread_concurrency() 函数调用的系统上有意义（例如 Sun Solaris）。
# 你应该对 thread_concurrency 尝试 CPU 数量的 2/4/6/... 倍。
thread_concurrency = 8

# 查询缓存被用来缓存 SELECT 结果并在稍后返回它们，不会再次实际执行相同的查询。
# 如果你有很多相同的查询并且很少改变表的话，查询缓存的启用将导致显著的速度改善。
# 请看“Qcache_lowmem_prunes”状态变量，以检查当前值对于你的加载是否足够高。
# 注意：如果你的表经常改变，或者如果你的查询每次是不同的原文，那么查询缓存将导致变慢，替代性能的改善。
query_cache_size = 64M

# 只有缓存结果集是小于该限制的。
# 这可以保护一个非常大结果集的查询缓存覆盖所有其它查询结果。
query_cache_limit = 2M

# 编制到全文检索索引的最小单词长度。
# 如果你需要检索更短的单词，你可能希望减小它。
# 注意，在你修改了该值以后，你需要重建你的 FULLINDEX 索引。
ft_min_word_len = 4

# 如果你的系统支持 memlock() 函数调用，你可能想要启用该选项（运行 MySQL 以保持它锁定到内存，并在出现高内存压力时避免潜在的交换输出）。
# 这对性能是很有益的。
#memlock

# 如果在 CREATE TABLE 语句期间没有指定不同的，当创建一个新表时所使用的默认表类型。
default-storage-engine = MYISAM

# 使用的线程堆栈大小。
# 该内存量总是在连接时间被保留的。
# MySQL 自己通常需要不超过 64K 的内存，然而如果你使用的是你自己的堆栈 UDF 函数或者你的系统针对某些操作需要更多堆栈，你可能需要设置该值为一个更高的值。
thread_stack = 192K

# 设置默认的事务隔离等级。
# 可用的级别有：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。
transaction_isolation = REPEATABLE-READ

# 内部（内存中的）临时表的最大容量。
# 如果一个表的增长超过该值，它将自动地转换到基于磁盘的表。
# 该限制是针对一个单一的表，但可以有很多这样的表。
tmp_table_size = 64M

# 启用二进制日志。
# 这在一个复制配置中，对于充当 MASTER 的是必要的。
# 如果你需要有能力及时从你最后的备份点中进行恢复，你也需要二进制日志。
log-bin = mysql-bin

# 推荐的二进制日志格式 - mixed。
binlog_format = mixed

# 如果你正在使用连锁从服务器（A-〉B-〉C）进行复制，你需要在服务器 B 上启用该选项。
# 它允许通过从服务器线程将日志记录到从服务器的二进制日志中来实现日志的更新。
#log_slave_updates

# 启用完整的查询日志。服务器接收到的每一个查询（甚至是错误的语法）都将被记录。
# 这对于调试是很有用的，它通常在产品使用时被禁用。
#log

# 打印警告到错误日志文件。
# 如果你有任何 MySQL 的问题，你应该启用警告日志并检查错误日志中可能的解释。
#log_warnings

# 记录慢查询。
# 慢查询是指消耗时间超过“long_query_time”中定义的总时间的查询，或者如果 log_short_format 没有启用，不使用索引的查询。
# 如果你频繁地添加新查询到系统中，打开这个是一个比较好的注意。
slow_query_log

# 所有消耗时间超过该总时间的查询都将被视为是缓慢的。
# 不要在这里使用“1”值，因为这会导致甚至非常快的查询都会被不时地被记录（MySQL 当前的度量时间只精确到秒）。
long_query_time = 2

# 被 MySQL 用来存储临时文件的目录。
# 例如，它被用来执行基于磁盘的大的排序，以及内部和显式的临时表。
# 如果你不会创建一个非常大的临时文件，将它放在一个 swapfs/tmpfs 文件系统中是有好处的。
# 另外，你可以把它放在一个专用的磁盘上。
# 你可以指定以“;”分隔的多个路径 - 它们将在稍后被用在一个循环方式中。
#tmpdir = /tmp

# *** 与复制有关的设置

# 1 到 2^32-1 之间的唯一服务器标识号。
# 该值对于主服务器和从服务器都是必须的。
# 如果“master-host”没有设置则默认为 1，但若是忽略，MySQL 将不会作为一个主服务器的功能。
server-id = 1

# 复制从服务器（注释掉主服务器章节以便使用这个）。
#
# 要配置该主机为一个复制从服务器，你可以选择以下两种方法：
#
# 1）使用 CHANGE MASTER TO 命令（在我们的手册中有完整的描述） - 其语法是：
#
# CHANGE MASTER TO MASTER_HOST = 〈host〉, MASTER_PORT = 〈port〉, MASTER_USER = 〈user〉, MASTER_PASSWORD = 〈password〉;
#
# 使用带引号的字符串替换 〈host〉、〈user〉、〈password〉，并且 〈port〉 是主服务器的端口号（默认为 3306）。
#
# 例子：
#
# CHANGE MASTER TO MASTER_HOST = '125.564.12.1', MASTER_PORT = 3306, MASTER_USER = 'joe', MASTER_PASSWORD = 'secret';
#
# 或者
#
# 2）设置下面的变量。然而，如果你选择了该方法，请在第一时间内启动复制（就算不成功，例如，如果你在 MASTER_PASSWORD 中未键入密码，并且从服务器连接失败），从服务器将创建一个 master.info 文件，稍后在该文件中对下面变量值的任何改变都将被忽略，并被 master.info 文件中的连接所覆盖，除非你关闭从服务器、删除 master.info 并重新启动从服务器。
# 基于这种因素，你可能想要离开下面未接触的行（已注释的）并替代使用 CHANGE MASTER TO（请看上面）。
#
# 需要 2 到 2^32-1 之间的唯一 id（与主服务器不同）。
# 如果“master-host”已被设置，默认设置为 2。
# 但若是忽略，将不会作为一个从服务器的功能。
#server-id = 2
#
# 针对该从服务器的复制主服务器 - 必须的。
#master-host = 〈hostname〉
#
# 用户名，当连接到主服务器时，从服务器将用此来进行认证 - 必须的。
#master-user = 〈username〉
#
# 密码，当连接到主服务器时，从服务器将用此来进行认证 - 必须的。
#master-password = 〈password〉
#
# 端口，主服务器正在监听的。
# 可选的 - 默认为 3306。
#master-port = 〈port〉

# 让从服务器只读。
# 只有拥有 SUPER 特权的用户和复制从服务器线程能够修改它的数据。
# 你可以使用这个来确保不会有应用程序在无意中替代主服务器修改从服务器上的数据。
#read_only

#*** MyISAM 特有的选项

# 键缓冲区的大小，用来为 MyISAM 表缓存索引块。
# 不要将它设置为超过你可用内存的 30% 以上，因为操作系统也需要一些内存来缓存行。
# 即使你不使用 MyISAM 表，你仍应该将它设置为 8-64M，因为它也被用于内部的临时磁盘表。
key_buffer_size = 32M

# 用于进行 MyISAM 表全表扫描的缓冲区大小。
# 如果全表扫描是需要的，将分配给每个线程。
read_buffer_size = 2M

# 当在一个有序的排序中读取行时，可以通过该缓冲区来读取行，以避免对磁盘的查找。
# 如果将该值设置为一个很高的值，你可以大幅度提高 ORDER BY 的性能。
# 当需要时，分配给每个线程。
read_rnd_buffer_size = 16M

# MyISAM 使用特殊的类似于树的缓存来让大批量插入（亦即 INSERT ... SELECT、INSERT ... VALUES(...) 和 LOAD DATA INFILE）操作变得更快。
# 该变量限制每个线程的缓存树的字节大小。
# 将它设置为 0 将禁用该优化。
# 为了优化性能，不要将它设置得比“key_buffer_size”大。
# 当检测到大量的插入时，该缓冲区被分配。
bulk_insert_buffer_size = 64M

# 当 MySQL 需要通过 REPAIR、OPTIMIZE、ALTER 表语句重建索引，以及 LOAD DATA INFILE 到一个空表时，该缓冲区被分配。
# 它是给每个线程分配的，因此小心比较大的设置。
myisam_sort_buffer_size = 128M

# 当重建索引（在 REPAIR、ALTER TABLE 或 LOAD DATA INFILE 期间）时，MySQL 允许使用的临时文件的最大大小。
# 如果“file-size”比这个值大，索引将通过键缓存（更慢一些）创建。
myisam_max_sort_file_size = 10G

# 如果一个表有超过一个的索引，MyISAM 能够在排序时并行地使用超过一个的线程来修复它们。
# 如果你有多个 CPU 和足够的内存，这是很有意义的。
myisam_repair_threads = 1

# 自动地检查和修复没有正确关闭的 MyISAM 表。
myisam_recover

# *** INNODB 特定的选项 ***

# 如果你有一个支持 InnoDB 启用的 MySQL 服务器，而你却并不计划使用它，请使用该选项。
# 这可以保存一些内存和磁盘空间，并提高速度。
#skip-innodb

# 附加的内存池，InnoDB 用来存储元数据信息。
# 如果 InnoDB 因该目的而需要更多的内存，它将开始从操作系统来分配它。
# 由于这在大多数最近的操作系统上是足够快的，你通常不需要改变这个值。
# SHOW INNODB STATUS 将显示当前使用总量。
innodb_additional_mem_pool_size = 16M

# InnoDB，不像 MyISAM，使用一个缓冲池来缓存索引和行数据。
# 你将该值设得越大，在表中访问需要的数据时，磁盘 I/O 就越少。
# 在一个专用的数据库服务器上，你可以设置该参数到机器物理内存大小的 80%。
# 不要把它设置得太大，因为物理内存的竞争可能导致操作系统中的分页。
# 注意，在 32 位的系统上，你可能在每个处理器的用户级内存上被限制在 2-3.5G，因此不要把它设置得太高。
innodb_buffer_pool_size = 2G

# InnoDB 存储数据到一个或多个数据文件，形成表空间。
# 如果对于你对你的数据有一个单一的物理设备，那么一个单一的自动扩展文件就已经足够了。
# 在其它情况下，每设备一个单一文件是一个非常好的选择。
# 你也可以配置 InnoDB 来使用原始的磁盘分区 - 请参考手册以获取更多有关这个的信息。
innodb_data_file_path = ibdata1:10M:autoextend

# 如果你希望 InnoDB 表空间文件存储到其它的地方，设置该选项。
# 默认的是 MySQL 数据目录。
#innodb_data_home_dir = 〈directory〉

# 异步 IO 操作所使用的 IO 线程数。
# 该值在 Unix 系统上被硬编码为 4，但在 Windows 上，磁盘 I/O 可能受益于一个更大的数字。
innodb_file_io_threads = 4

# 如果你遇到 InnoDB 表空间腐烂，设置该值为一个非零值，将很容易地帮助你导出你的表。
# 以值 1 开始并提高它，直到你能够成功地导出表。
#innodb_force_recovery=1

# InnoDB 内核里面允许的线程数量。
# 最佳的值高度取决于应用程序、硬件以及操作系统的调度属性。
# 一个太高的值可能导致线程颠簸。
innodb_thread_concurrency = 16

# 如果设置为 1，InnoDB 在每次提交（提供完整的 ACID 行为）时刷新事务日志到磁盘。
# 如果你想安全地进行折中，并且你正在运行小事务，你可以为 0 或 2 来减少日志的磁盘 I/O。
# 值 0 表示日志只被写入到日志文件，并且日志文件大约每秒一次刷新到磁盘。
# 值 2 表示日志在每次提交时被写入到日志文件，但是日志文件只是大约每秒一次被刷新到磁盘。
innodb_flush_log_at_trx_commit = 1

# 加速 InnoDB 的关闭。
# 这在关闭时将禁用 InnoDB 做一个完整的清除和插入缓冲合并。
# 它可能会提高不少关闭的时间，但替代的是 InnoDB 将在下一次启动时来完成它。
#innodb_fast_shutdown

# InnoDB 缓冲日志数据所使用的缓冲区大小。
# 一旦它满了，InnoDB 将刷新它到磁盘。
# 因为不管怎么它都是每秒刷新一次，所以没有必要让它变得很大（甚至是很长的事务）。
innodb_log_buffer_size = 8M

# 一个日志组中每个日志文件的大小。
# 你可以设置日志文件的联合大小为你的缓冲池大小的 25%-100%，以避免对日志文件不必要的缓冲池动态刷新重写。
# 然而，注意，一个更大的日志文件大小将增加恢复处理所需的时间。
innodb_log_file_size = 256M

# 日志组中文件的总数。
# 通常值为 2-3 就已足够了。
innodb_log_files_in_group = 3

# InnoDB 日志文件的位置。
# 默认为 MySQL 的数据目录。
# 你可能希望指定它到一个专用的硬盘或一个 RAID1 卷标来改善性能。
#innodb_log_group_home_dir

# InnoDB 缓冲池中允许的脏页面的最大百分比。
# 如果它到达了，InnoDB 将开始积极地清理它们，以避免消耗完所有的干净页面。
# 这是一个软限制，不保证能够一直保持。
innodb_max_dirty_pages_pct = 90

# InnoDB 对日志使用的刷新方法。
# 表空间总是使用双写刷新逻辑。
# 默认值为“fdatasync”，其它选项是“O_DSYNC”。
#innodb_flush_method = O_DSYNC

# 一个 InnoDB 事务应等待的在回滚之前被授权锁定的时长。
# InnoDB 在它自己的锁定表中自动地检测事务死锁，并回滚事务。
# 如果你在相同的事务中使用 LOCK TABLES 命令，或者其它比 InnoDB 更加事务安全的存储引擎，那么稍后会出现 InnoDB 不能提示的死锁。
# 如果像这样，超时对于解决问题是很有用的。
innodb_lock_wait_timeout = 120

[mysqldump]
# 在写入到文件之前，不要缓冲整个结果集。
# 导出非常大的表时是必须的。
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash

# 只允许 UPDATE 和 DELETE 使用键。
#safe-updates

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
# 增加每次处理所允许打开的文件数量。
# 警告：确保你已经设置全局系统限制足够高！
# 对于一个大数量的打开表，高值是必须的。
open-files-limit = 8192

以上的参数设置大家可以根据自己实现情况参考一下