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的互动都必须通过Unixsockets（套接字）或命名管道进行。
#注意，在Windows上使用该选项但却不启用命名管道（通过“enable-named-pipe”选项）将使得mysqld变得无用。
#
#skip-networking
#MySQL允许的并发会话的最大数量。
#其中的一个连接将被保留给拥有SUPER特权的用户，即使已经到达了连接限制，仍可以允许管理者登录。
max_connections=100
#每个主机允许的最大错误数量。
#如果已到达该限制，主机将阻止对MySQL服务器的连接，直到运行“FLUSHHOSTS”或者服务器被重启。
#在连接阶段的无效密码和其它错误将导致该值被提高。
#请看全局计数器的“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
#排序缓冲被用来执行一些ORDERBY和GROUPBY查询的排序。
#如果已排序的数据没有进入到排序缓冲，一个基于磁盘的合并排序将被替代使用-请看“Sort_merge_passes”状态变量。
#如果排序是需要的，将分配给每个线程。
sort_buffer_size=8M
#该缓冲被用来优化FULLJOIN（没有索引的JOIN）。
#无论如何，该JOIN在大多数情况下对性能是非常坏的，但是设置该变量为一个大值将减少对性能的影响。
#请看针对一定数量的FULLJOIN的“Select_full_join”状态变量。
#如果FULLJOIN被发现，将分配给每个线程。
join_buffer_size=8M
#我们保持在一个缓存中的可重用的线程有好多。
#当一个客户端断开连接时，如果在这之前的线程没有超过thread_cache_size，客户端的线程将放在缓存中。
#如果你有很多新的连接，这将大幅减少创建所需线程的数量。
#（如果你有一个很好的线程实现，这通常不会给出一个显著的性能改善。）
thread_cache_size=8
#这允许应用程序给予线程系统一个针对运行在相同时间的线程所需数量的提示。
#该值只在支持thread_concurrency()函数调用的系统上有意义（例如SunSolaris）。
#你应该对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
#如果在CREATETABLE语句期间没有指定不同的，当创建一个新表时所使用的默认表类型。
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）使用CHANGEMASTERTO命令（在我们的手册中有完整的描述）-其语法是：
#
#CHANGEMASTERTOMASTER_HOST=〈host〉,MASTER_PORT=〈port〉,MASTER_USER=〈user〉,MASTER_PASSWORD=〈password〉;
#
#使用带引号的字符串替换〈host〉、〈user〉、〈password〉，并且〈port〉是主服务器的端口号（默认为3306）。
#
#例子：
#
#CHANGEMASTERTOMASTER_HOST='125.564.12.1',MASTER_PORT=3306,MASTER_USER='joe',MASTER_PASSWORD='secret';
#
#或者
#
#2）设置下面的变量。然而，如果你选择了该方法，请在第一时间内启动复制（就算不成功，例如，如果你在MASTER_PASSWORD中未键入密码，并且从服务器连接失败），从服务器将创建一个master.info文件，稍后在该文件中对下面变量值的任何改变都将被忽略，并被master.info文件中的连接所覆盖，除非你关闭从服务器、删除master.info并重新启动从服务器。
#基于这种因素，你可能想要离开下面未接触的行（已注释的）并替代使用CHANGEMASTERTO（请看上面）。
#
#需要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
#当在一个有序的排序中读取行时，可以通过该缓冲区来读取行，以避免对磁盘的查找。
#如果将该值设置为一个很高的值，你可以大幅度提高ORDERBY的性能。
#当需要时，分配给每个线程。
read_rnd_buffer_size=16M
#MyISAM使用特殊的类似于树的缓存来让大批量插入（亦即INSERT...SELECT、INSERT...VALUES(...)和LOADDATAINFILE）操作变得更快。
#该变量限制每个线程的缓存树的字节大小。
#将它设置为0将禁用该优化。
#为了优化性能，不要将它设置得比“key_buffer_size”大。
#当检测到大量的插入时，该缓冲区被分配。
bulk_insert_buffer_size=64M
#当MySQL需要通过REPAIR、OPTIMIZE、ALTER表语句重建索引，以及LOADDATAINFILE到一个空表时，该缓冲区被分配。
#它是给每个线程分配的，因此小心比较大的设置。
myisam_sort_buffer_size=128M
#当重建索引（在REPAIR、ALTERTABLE或LOADDATAINFILE期间）时，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因该目的而需要更多的内存，它将开始从操作系统来分配它。
#由于这在大多数最近的操作系统上是足够快的，你通常不需要改变这个值。
#SHOWINNODBSTATUS将显示当前使用总量。
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在它自己的锁定表中自动地检测事务死锁，并回滚事务。
#如果你在相同的事务中使用LOCKTABLES命令，或者其它比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
以上的参数设置大家可以根据自己实现情况参考一下