MySQL优化必须调整的10项配置和101条调节和优化技巧

　　MySQL是一个功能强大的开源数据库。随着越来越多的数据库驱动的应用程序，人们一直在推动MySQL发展到它的极限。这里是MySQL优化必须调整的10项配置和101条调节和优化MySQL安装的技巧。一些技巧是针对特定的安装环境的，但这些思路是通用的。我已经把他们分成几类，来帮助你掌握更多MySQL的调节和优化技巧。

　　我们先来看MySQL优化必须调整的10项配置：

　　当我们被人雇来监测MySQL性能时，人们希望我们能够检视一下MySQL配置然后给出一些提高建议。许多人在事后都非常惊讶，因为我们建议他们仅仅改动几个设置，即使是这里有好几百个配置项。接下来内容的目的在于给你一份非常重要的配置项清单。

　　我们曾在几年前给出了这样的建议，但是MySQL的世界变化实在太快了！

　　写在开始前…

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

　　一次只改变一个设置！这是测试改变是否有益的唯一方法。

　　大多数配置能在运行时使用SETGLOBAL改变。这是非常便捷的方法它能使你在出问题后快速撤销变更。但是，要永久生效你需要在配置文件里做出改动。

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

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

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

　　InnoDB配置

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

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

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

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

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

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

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

　　下面我们来看101条调节和优化MySQL安装的技巧：

　　MySQL服务器硬件和操作系统调节:

　　1.拥有足够的物理内存来把整个InnoDB文件加载到内存中——在内存中访问文件时的速度要比在硬盘中访问时快的多。

　　2.不惜一切代价避免使用Swap交换分区–交换时是从硬盘读取的，它的速度很慢。

　　3.使用电池供电的RAM（注：RAM即随机存储器）。

　　4.使用高级的RAID（注：RedundantArraysofInexpensiveDisks，即磁盘阵列）–最好是RAID10或更高。

　　5.避免RAID5（注：一种存储性能、数据安全和存储成本兼顾的存储解决方案）–确保数据库完整性的校验是要付出代价的。

　　6.将操作系统和数据分区分开，不仅仅是逻辑上，还包括物理上–操作系统的读写操作会影响数据库的性能。

　　7.把MySQL临时空间和复制日志与数据放到不同的分区–当数据库后台从磁盘进行读写操作时会影响数据库的性能。

　　8.更多的磁盘空间等于更快的速度。

　　9.更好更快的磁盘。

　　10.使用SAS（注：SerialAttachedSCSI，即串行连接SCSI）代替SATA（注：SATA，即串口硬盘）。

　　11.较小的硬盘比较大的硬盘快，尤其是在RAID配置的情况下。

　　12.使用电池支持的高速缓存RAID控制器。

　　13.避免使用软件磁盘阵列。

　　14.考虑为数据分区使用固态IO卡(不是磁盘驱动器)–这些卡能够为几乎任何数量的数据支持2GB/s的写入速度。

　　15.在Linux中设置swappiness的值为0–在数据库服务器中没有理由缓存文件，这是一个服务器或台式机的优势。

　　16.如果可以的话，使用noatime和nodirtime挂载文件系统–没有理由更新访问数据库文件的修改时间。

　　17.使用XFS文件系统–一种比ext3更快、更小的文件系统，并且有许多日志选项，而且ext3已被证实与MySQL有双缓冲问题。

　　18.调整XFS文件系统日志和缓冲变量–为了最高性能标准。

　　19.在Linux系统中,使用NOOP或者DEADLINEIO定时调度程序–同NOOP和DEADLINE定时调度程序相比，这个CFQ和ANTICIPATORY定时调度程序显得非常慢。

　　20.使用64位的操作系统–对于MySQL，会有更大的内存支持和使用。

　　21.删除服务器上未使用的安装包和守护进程–更少的资源占用。

　　22.把使用MySQL的host和你的MySQLhost放到一个hosts文件中–没有DNS查找。

　　23.切勿强制杀死一个MySQL进程–你会损坏数据库和正在运行备份的程序。

　　24.把服务器贡献给MySQL–后台进程和其他服务能够缩短数据库占用CPU的时间。

　　MySQL配置:

　　25.当写入时，使用innodb_flush_method=O_DIRECT来避免双缓冲。

　　26.避免使用O_DIRECT和EXT3文件系统–你将序列化所有要写入的。

　　27.分配足够的innodb_buffer_pool_size来加载整个InnoDB文件到内存中–少从磁盘中读取。

　　28.不要将innodb_log_file_size参数设置太大，这样可以更快同时有更多的磁盘空间–丢掉多的日志通常是好的，在数据库崩溃后可以降低恢复数据库的时间。

　　29.不要混用innodb_thread_concurrency和thread_concurrency参数–这2个值是不兼容的。

　　30.分配一个极小的数量给max_connections参数–太多的连接会用尽RAM并锁定MySQL服务。

　　31.保持thread_cache在一个相对较高的数字，大约16–防止打开连接时缓慢。

　　32.使用skip-name-resolve参数–去掉DNS查找。

　　33.如果你的查询都是重复的，并且数据不常常发生变化，那么可以使用查询缓存。但是如果你的数据经常发生变化，那么使用查询缓存会让你感到失望。＼

　　34.增大temp_table_size值，以防止写入磁盘

　　35.增大max_heap_table_size值，以防止写入磁盘

　　36.不要把sort_buffer_size值设置的太高，否则的话你的内存将会很快耗尽

　　37.根据key_read_requests和key_reads值来决定key_buffer的大小，一般情况下key_read_requests应该比key_reads值高，否则你不能高效的使用key_buffer

　　38.将innodb_flush_log_at_trx_commit设置为0将会提高性能，但是如果你要保持默认值（1）的话，那么你就要确保数据的完整性，同时你也要确保复制不会滞后。

　　39.你要有一个测试环境，来测试你的配置，并且在不影响正常生产的情况下，可以常常进行重启。

　　MySQL模式优化:

　　40.保持你的数据库整理性。

　　41.旧数据归档-删除多余的行返回或搜索查询。

　　42.将您的数据加上索引.

　　43.不要过度使用索引，比较与查询.

　　44.压缩文字和BLOB数据类型-以节省空间和减少磁盘读取次数.

　　45.UTF8和UTF16都低于latin1执行效率.

　　46.有节制地使用触发器.

　　47.冗余数据保持到最低限度-不重复不必要的数据.

　　48.使用链接表，而不是扩展行.

　　49.注意数据类型，在您的真实数据中，尽可能使用最小的一个.

　　50.如果其他数据经常被用于查询时，而BLOB/TEXT数据不是，就把BLOB/TEXT数据从其他数据分离出来.

　　51.检查和经常优化表.

　　52.经常重写InnoDB表优化.

　　53.有时，当添加列时删除索引，然后在添加回来索引，这样就会更快.

　　54.针对不同的需求，使用不同的存储引擎.

　　55.使用归档存储引擎日志表或审计表-这是更有效地写道.

　　56.会话数据存储在缓存（memcache）的而不是MySQL中-缓存允许自动自动填值的，并阻止您创建难以读取和写入到MySQL的时空数据.

　　57.存储可变长度的字符串时使用VARCHAR而不是CHAR-节省空间，因为固定长度的CHAR，而VARCHAR长度不固定（UTF8不受此影响）.

　　58.逐步进行模式的变化-一个小的变化，可以有巨大的影响.

　　59.在开发环境中测试所有模式，反映生产变化.

　　60.不要随意更改你的配置文件中的值，它可以产生灾难性的影响.

　　61.有时候，在MySQL的configs少即是多.

　　62.有疑问时使用一个通用的MySQL配置文件.

　　查询优化:

　　63.使用慢查询日志去发现慢查询。

　　64.使用执行计划去判断查询是否正常运行。

　　65.总是去测试你的查询看看是否他们运行在最佳状态下–久而久之性能总会变化。

　　66.避免在整个表上使用count(*),它可能锁住整张表。

　　67.使查询保持一致以便后续相似的查询可以使用查询缓存。

　　68.在适当的情形下使用GROUPBY而不是DISTINCT。

　　69.在WHERE,GROUPBY和ORDERBY子句中使用有索引的列。

　　70.保持索引简单,不在多个索引中包含同一个列。

　　71.有时候MySQL会使用错误的索引,对于这种情况使用USEINDEX。

　　72.检查使用SQL_MODE=STRICT的问题。

　　73.对于记录数小于5的索引字段，在UNION的时候使用LIMIT不是是用OR.

　　74.为了避免在更新前SELECT，使用INSERTONDUPLICATEKEY或者INSERTIGNORE,不要用UPDATE去实现。

　　75.不要使用MAX,使用索引字段和ORDERBY子句。

　　76.避免使用ORDERBYRAND().

　　77。LIMITM，N实际上可以减缓查询在某些情况下，有节制地使用。

　　78。在WHERE子句中使用UNION代替子查询。

　　79。对于UPDATES（更新），使用SHAREMODE（共享模式），以防止独占锁。

　　80。在重新启动的MySQL，记得来温暖你的数据库，以确保您的数据在内存和查询速度快。

　　81。使用DROPTABLE，CREATETABLEDELETEFROM从表中删除所有数据。

　　82。最小化的数据在查询你需要的数据，使用*消耗大量的时间。

　　83。考虑持久连接，而不是多个连接，以减少开销。

　　84。基准查询，包括使用服务器上的负载，有时一个简单的查询可以影响其他查询。

　　85。当负载增加您的服务器上，使用SHOWPROCESSLIST查看慢的和有问题的查询。

　　86。在开发环境中产生的镜像数据中测试的所有可疑的查询。

　　MySQL备份过程:

　　87.从二级复制服务器上进行备份。

　　88.在进行备份期间停止复制，以避免在数据依赖和外键约束上出现不一致。

　　89.彻底停止MySQL，从数据库文件进行备份。

　　90.如果使用MySQLdump进行备份，请同时备份二进制日志文件–确保复制没有中断。

　　91.不要信任LVM快照–这很可能产生数据不一致，将来会给你带来麻烦。

　　92.为了更容易进行单表恢复，以表为单位导出数据–如果数据是与其他表隔离的。

　　93.当使用mysqldump时请使用–opt。

　　94.在备份之前检查和优化表。

　　95.为了更快的进行导入，在导入时临时禁用外键约束。

　　96.为了更快的进行导入，在导入时临时禁用唯一性检测。

　　97.在每一次备份后计算数据库，表以及索引的尺寸，以便更够监控数据尺寸的增长。

　　98.通过自动调度脚本监控复制实例的错误和延迟。

　　99.定期执行备份。

　　100.定期测试你的备份。

　　最后101:执行MySQL监控:MonitisUnveilsTheWorld’sFirstFreeOn-demandMySQLMonitoring.

　　小编结语：

　　更多内容尽在课课家教育！