MySQL5.1性能优化方案

MySQL5.1性能优化方案

1. 平台数据库

1.1. 操作系统

Red Hat Enterprise Linux Server release 5。4 (Tikanga)

ELF 32-bit LSB executable, Intel 80386， version 1 （SYSV）， for GNU/Linux 2。6.9， dynamically linked （uses shared libs), for GNU/Linux 2。6.9, stripped 32位Linux服务器，单独作为MySQL服务器使用。

1.2. MySQL

系统使用的是MySQL5。1，最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面： 1）默认存储引擎更改为InnoDB

InnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但MySQL5。1之前的版本默认引擎均为MyISAM,此次MySQL5。5终于将默认数据库存储引擎改为InnoDB，并且引进了Innodb plugin 1。0.7。此次更新对数据库的好处是显而易见的：InnoDB的数据恢复时间从过去的一个甚至几个小时,缩短到几分钟（InnoDB plugin 1。0。7，InnoDB plugin 1。1, 恢复时采用红—黑树).InnoDB Plugin 支持数据压缩存储，节约存储，提高内存命中率,并且支持adaptive flush checkpoint， 可以在某些场合避免数据库出现突发性能瓶颈。

Multi Rollback Segments： 原来InnoDB只有一个Segment，同时只支持1023的并发。现已扩充到128个Segments，从而解决了高并发的限制. 2）多核性能提升

Metadata Locking （MDL) Framework替换LOCK_open mutex (lock），使得MySQL5。1及过去版本在多核心处理器上的性能瓶颈得到解决。 3）制功能（Replication）加强

过去的异步复制方式意味着极端情况下的数据风险,MySQL5。5将首次支持半同步（semi-sync replication)在MySQL的高可用方案中将产生更多更加可靠的方案。 4）增强表分区功能

MySQL 5。5的分区更易于使用的增强功能，以及TRUNCATE PARTITION命令都可以为管理和维护数据库节省大量的时间,并且具有更加灵活高效的分区方式。

1.3. CPU

系统所用CPU是单个4核CPU。对于CPU密集的负载,MySQL通常从更快的CPU中获益，而不是更多CPU.MySQL5。1的架构对多CPU的扩展性不好，并且MySQL不能在多个CPU上并行地运行某个查询，因此在对于单个CPU进行密集的查询时，CPU速度限制了响应时间。为了实现低延迟，即快速响应时间，需要快速的CPU,因为单个查询只能使用一个CPU。值得注意的是，MySQL5。5在多核心处理器上的性能有了很大的提升.另外，MySQL在64位架构上工作得更好，比32位架构更能有效地使用大量内存。

尽管本系统使用的是32位操作系统，CPU运行在32位模式下，但它仍支持64位计算。（cat /proc/cpuinfo ｜ grep flags ｜ grep ’ lm ' | wc —l）

1.4. 磁盘空间

系统的磁盘空间目前没有压力.

1.5. 内存

内存总大小为4G，只供操作系统和数据库使用。

1.6. 数据库的表和文件

数据库addb共有339张表:其中InnoDB表303张，MyISAM表34张,MEMORY表2张。

InnoDB数据文件ibdata1大小为30138MB，一周后ibdata1大小为30234MB，

MyISAM数据文件（包括表结构、索引及数据）总大小约为1642MB，一周后约为1639MB.可以看出，数据库的数据量较稳定，InnoDB数据文件增加了约106MB,总大小一周内没有大的变化。MyISAM表中,值得注意的是表terminalalarm_bak，该表总大小约为1623MB，占整个MyISAM表总大小比重近99％。

二进制日志单个文件大小为1GB，二进制日志文件总大小接近20GB。

1.7. 数据分布情况

服务器某时间点非精确值:

数据量范围 1000万〈rows<5000万 500万〈rows<1000万 100万 adrotateresultdetail_fail的数据量达到4千万,createTime列是datatime类型,且

有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。

 terminalalarm的数据量也突破千万，AlarmTime列是datatime类型，且有索

引，意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区.在事件ev_terminalalarm中会查询该表,若进行表分区，也能一定程度上提高事件的执行效率。

 terminalalarminfo表仅自增列有索引,主要用于存储数据,可不用分区。  Terminallogin表的loginTime列是datatime类型,且有索引，意味着存在以该

列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表

分区.

 adplayinfo_bak表存在多个以INT类型为索引的列，根据实际业务情况选择

查询频率高且能以范围值来分区的整型列对该表进行分区。

 adrotateresultdetail的createTime列是datatime类型，且有索引，意味着存在

以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区.

 upfile_bak表仅自增列有索引,若存在查询或者统计业务则可以createTime列

进行分区，若该表没有查询方面业务可不必进行分区。

除去配置参数等属性表，对于数据量大且不断递增的业务数据表，最直接的办法可以按照时间字段进行分区，或是根据查询业务来选择合适的列进行表分区和创建索引，这样能够有效提高存储和查询效率.

1.8. 服务器配置参数

记录查询：普通日志log、慢速日志log_slow_queries

MySQL有两种查询日志：普通日志和慢速日志，它们都会记录查询。普通日志记录了服务器接收到的每一个查询,也包含了没有被执行的查询,比如因为错误而未被执行的查询,还有一些非查询事件，比如连接和断开连接，普通日志不包含执行时间或其他只有在查询结束之后才能得到的信息.相反，慢速日志只包含了已经执行过的查询,如果是启动状态，它记录了执行时间超过了特定长度的查询.两种日志都有助于分析,但是慢速日志更有利找到性能较慢的查询. 一个相关配置是log_queries_not_using_indexes,它使服务器把没有使用索引的查询记录到慢速查询日志中,无论它们执行速度有多快.尽管打开慢速日志相对于执行慢速查询来说，通常只增加了很少的时间，但是如果没有使用索引的查询非常快，例如从小数据量表中查询，这样就会记录它们可能导致服务器变慢,甚至还会使用大量的磁盘空间，慢速日志也许就会被那些快速高效的查询塞满。 慢查询日志可以用来找到执行时间长的查询，可以用于优化.慢日志打开后,通过设置long_query_time来配置记录查询超过的指定时间,默认值为10秒,根据系统的负载和性能要求进行设置(SET GLOBAL long_query_time = …）。

检查又长又慢的查询日志会很麻烦,可以使用MySQLdumpslow命令获得日

志中显示的查询摘要来处理慢查询日志。系统两种日志都没有开启,可以在需要的时候打开慢速日志来帮助分析性能较慢的查询.具体实施参考MySQL手册。

需要注意的是查询在日志中只出现一次并不意味着它是一个不好的查询，也不意味将来也会慢,查询时快是慢有多种原因: 1）表也许被锁定，导致查询处于等待状态； 2）数据或索引也许没有被缓存在内存中；

3）或者正在进行批处理大量的数据，使得磁盘I/O变慢； 4）服务器可能同时在运行其他的查询,影响了当前查询的效率。

因此，只能把慢速查询日志看成调优工作的一部分，可以用它来找到可疑的查询，但需要对它们进行仔细地排查和分析。

 启用系统慢速日志，分析查询性能慢的时候可以观察该日志信息。 Qcache_hits Com_select Qcache_inserts

检查是否从查询缓存中受益的最直接办法就是检查缓存命中率。它是提供缓存提供的查询结果的数量,而不是服务器执行的数量。当服务器收到select语句的时候，Qcache_hits和Com_select这两个变量会根据查询缓存的情况进行递增. 查询缓存命中率的计算公式：Qcache_hits/（Qcache_hits+Com_select），根据公式计算得出查询缓存命中率为7％。初看上去该命中率很低,但注意到com_select等于qcache_inserts + qcache_not_cache + 权限检查错误的总和，即这个比率中包含了缓存失效的因素，而对于数据变更频繁的系统来说，缓存是及其容易失效的，表的任何时刻的数据插入或更新都会使该表的缓存失效，所以本系统缓存的插入率很低，抛开失效的缓存因素，用如下公式计算缓存命中率：Qcache_hits/（Qcache_hits+Qcache_inserts)= 84。87％，该比值要好得多，意味着大部分的查询都命中了缓存,换一种说法就是仍有一小部分查询没有被缓存。没被缓存和缓存失效是两个概念，分别计数，但都会引起com_select的值增加。

命中率要多少才好,这视情况而定，因为对于每一个查询,不执行它所节约的资源远大于缓存中保存结果以及让查询失效的开销,如果缓存命中代表了开销最大的查询，那么即使很低的命中率也是有好处的。缓存可能会因为碎片、内存不

足或数据改变而失效。如果已经给缓存分配了足够的内存，并且把Query_cache_min_res_unit调整到了合适的值，那么大部分缓存失效都应该是由数据改变而引起的。Com_update, Com_delete等的值知道有多少查询修改了数据，也可以通过检查Qcache_lowmen_prunes的值了解有多少查询因为内存不足而失效。

 接近85％的命中率可以满足系统要求，如果该命中率持续降低则需要对系统进行性能分析并调整。系统表数据变更频繁，查询缓存的失效率较高,如果对变更频繁大表的查询频率较高，则使用SQL_NO_CACHE 和SQL_CACHE来控制是否需要使用查询缓存。 Query_cache_size

分配给查询的总内存必须是1024的倍数，系统设置为128MB.在服务器启动的时候,MySQL会为查询缓存一次性分配变量所定义数量的内存。如果更新了变量,MySQL会立即删除所有缓存的查询，重新把缓存设置为定义的大小，并重新初始化缓存的内存。 Query_cache_type

Query_cache_type设置在何场景下使用 Query Cache。系统的查询缓存是开启状态。_cache_type可以设置为0(OFF），1（ON）或者2(DEMOND），分别表示完全不使用query cache，除显式要求不使用query cache(使用sql_no_cache）之外的所有的select都使用query cache，只有显示要求才使用query cache（使用sql_cache）。 Query_cache_limit

该选项限制了MySQL存储的最大结果为2M，如果查询的结果比这个值大，那么就不会被缓存.服务器在产生结果的同时进行缓存，它无法预先知道结果是否会超过这一限制.如果在缓存的过程中发现已经超过了限制,MySQL会自动增加Qcache_not_cached的值，并且丢掉已经缓存过的值.如果预先判断会有这种情况，可以给查询加上SQL_NO_CHACHE来避免这种开销.

 以查询某表（18列）中的5000条结果为例，结果集数据大小约为1.4M，该设置是能满足要求的，保持该值即可.但如果查询结果数据过万的情况较多的话则应适当增加该值,最大不要超过4M. Qcache_free_memory

如果缓存由大结果和小结果混合而成，那么就很难找到一个合适的大小,既能避免碎片，也能避免过多的内存分配,但是缓存大结果没有太大的益处，可以通过降低Query_cache_limit的值阻止缓存大结果,它有时有助于在碎片和在缓存中保存结果的开销中得到平衡. Query_cache_min_res_unit Qcache_free_blocks Qcache_total_blocks Qcache_lowmen_prunes

可以通过检查Qcache_free_blocks的值来观察缓存中碎片的情况，它可以显示缓存中有多少内存块处于空闲状态.碎片最严重的情况就是在每两个存储了数据的块之间都有一个比最小值稍小的可用块,这样每隔一个存储块就有一个自由块，因此，如果Qcache_free_blocks大致等于Qcache_total_blocks/2，则说明碎片非常严重.Qcache_lowmem_prunes表示由于缓存内存不足被清除出查询缓存的条数，如果Qcache_lowmem_prunes的值正在增加，并且有大量的自由块，就说明碎片导致查询正被从缓存中永久删除。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks ＊ 100％

如果查询缓存碎片率超过20％，可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话.使用FLUSH QUERY CACHE命令移除碎片，该命令会把所有的存储块向上移动，并把自由块移到底部.当它运行的时候，会阻止访问查询缓存，这会锁定整个服务器，但它通常会很快,除非缓存特别大.

如果缓存没有碎片，但是命中率却不高，那么就应该给缓存分配较少的内存，如果服务器找不到足够大小的块来存储结果，就应该从缓存中清理掉一些查询,可以使用RESET QUERY CACHE命令从缓存中移除查询。当服务器清理查询的时候，Qcache_lowmen_prunes值会增加，如果它的值增加得很快,可能有两个原因:1）如果有很多自由块，就可能是有碎片引起的;2)如果自由块比较少，就可能表示工作负载使用的内存大小超过了所分配的内存，可以检查Qcache_free_memory知道为使用的内存数量。

如果有很多自由块，碎片很少,由于内存不足引起的清理工作也很少,但命中率仍然不高，这说明工作负载也许不能从缓存中受益,一定有什么阻止了查询使用缓存,很多update语句可能会是原因,另一个原因可能是查询是不可缓存的。

查询缓存分配的最小块的大小Query_cache_min_res_unit为4MB。 当查询进行的时候，MySQL把查询结果保存在查询缓存中,但如果要保存的结果比较大，超过query_cache_min_res_unit的值 ,这时候MySQL会一边检索结果，一边保存结果，所以，有时候并不是把所有结果全部得到后再进行一次性保存，而是每次分配一块query_cache_min_res_unit 大小的内存空间保存结果集,使用完后，接着再分配一个这样的块,如果还不够,接着再分配一个块，依此类推,也就是说,有可能在一次查询中,MySQL要进行多次内存分配的操作.当一块分配的内存没有完全使用时，MySQL会把这块内存截掉，把没有使用的那部分归还以重复利用，当连续操作后剩下的内存大小不足以分配一个内存单元时，内存碎片便产生了。

通常无法避免所有的碎片,但是仔细选择Query_cache_min_res_unit可以避免在查询缓存中造成大量的内存浪费，关键在于每一个新块和服务器已分配给存储结果的块的数量之间找到平衡，如果值过小，服务器将会浪费较少的内存，但会更频繁地分配块，这对服务器意味着更多的工作。如果值过大，碎片将会很多，合适的折中是在浪费内存和增加处理时间上取得平衡.

 空缓存百分比:Qcache_free_blocks / Qcache_total_blocks ≈ 16％,且系统Qcache_free_blocks值较高,有可能是出现碎片了,使用flush query cache整理查询缓存并消除碎片，该命令不会从缓存中移除任何查询。同时定期观察内存碎片情况。 Key_buffer_size Key_reads Key_reads_requests

键缓存读命中率：100—（（Key_reads*100)/ Key_reads_requests）= 99.975 Key_read_requests和Key_reads是两个计数器,Key_read_requests是从缓存读取索引的请求次数，Key_reads是从磁盘读取索引的请求次数。

key_buffer_size指定MyISAM表索引缓冲区的大小，它决定索引处理的速

度，尤其是索引读的速度。MyISAM键缓存默认只有一个缓冲区，MyISAM自身只缓存了索引，没有数据，它让操作系统缓存数据，它的值应该占到所有保留内存的25％到50％，操作系统缓存用来保存从MYD文件中读取出来的数据。该变量给键缓冲分配指定大小的空间，但是操作系统只有在实际用到这些空间的时候才会进行分配,也可以创建多个键缓存,如果对于一个非默认大小的键缓存设置为0,MySQL就会把每一个索引从特定的缓存移到默认的缓存中,并且在没有对象使用特定的缓存时就将其删掉,给一个不存在的缓存设置这个变量将会创建缓存,对一个已有的缓存设置非零值将会冲洗缓存，这是一个在线操作，它会阻止所有访问该缓存的动作，直到缓存冲洗完成.另一个参考指标是单位时间内Key_reads值的变化情况。

 系统使用MyISAM表查询频率较低，键缓存读命中率在99％以上,表明键缓存能满足系统的性能要求。 Key_blocks_unused Key_blocks_used

键缓存使用率= Key_blocks_used/ （Key_blocks_used+ Key_blocks_unused)=37％  尽管键缓存使用率较低,说明key_buffer_size设置较高，MySQL没有将其使用完，基于键缓存各方面都能满足系统要求且内存够用,不必调整。 table_cache_size/table_open_cache （5。1。2之后叫做table_open_cache） Open_tables Opened_tables

Open_tables表示当前打开的表缓存数，如果执行flush tables操作，则此系统会关闭一些当前没有使用的表缓存而使得此状态值减小；opend_tables表示曾经打开的表缓存数,会一直进行累加，如果执行flush tables操作,值不会减小。 应该将Open_tables的值和table_cache进行对照.如果每秒有太多Opened_tables，那么说明table_cache还不够大，表缓存没有被完全利用上时，显式的临时表也能导致Opened_tables增加。

table_cache指定表高速缓存的大小.设置该变量不会立即生效,要等到下一个线程打开表的时候才会生效，当它生效的时候,MySQL会检查变量的值,如果值大于缓存表中的数量,线程就可以把新打开的表插入到缓存中,这样可以更快地访问

表内容。如果值小于缓存表中的数量，MySQL就会从缓存中删除掉没有使用的表。通过检查峰值时间的状态值Open_tables和Opened_tables,可以决定是否需要增加table_cache的值.如果发现open_tables等于table_cache，并且opened_tables在不断增长，那么就需要增加table_cache的值了。

 Open_tables值与table_cache相等，且观察到Opened_tables较大,应适当增加table_cache，可将其设置为512。 thread_cache_size

thread_cache_size是缓存的同时操作的线程数.线程缓存保存了和当前连接无关的线程，这些线程可以供新连接使用。当一个新连接被创建出来并且缓存中有一个线程的时候，MySQL会把这个线程从缓存中删除，并且把它赋给连接。连接关闭时,MySQL会回收线程，把它放回到缓存中。如果缓存中没空间了,MySQL就会销毁该线程。只要缓存中有自由的线程，MySQL就能很快地响应连接请求,因为它不需要为每个连接都创建新的线程.

设置该变量不会立即生效,需要等到下一次线程关闭的时候，MySQL会检查缓存中是否有空间存储线程.如果是，他会把线程缓存起来，供另外一个连接使用，如果不是，它会直接结束线程,这种情况下，缓存中线程的数量,以及线程缓存使用的内存数量不会立即下降。只有当新连接为了使用线程而把它从缓存中移走的时候才会看到下降。MySQL只有在连接关闭的时候才会把线程加入缓存,也只有在创建新连接的时候才从缓存中移除线程。 Connections thread_connected threads_created

Connections变量表示连接意图的数量,而不是当前接连的数量（threads_connected)，如果它的值快速增加，比如每秒几百，就应该检查连接以及操作系统的网络设置。本系统中该值正常.

thread_cache_size定义了MySQL能在缓存中保存的线程数量，可以通过观察threads_created变量的值，以确定线程缓存是否足够大。如果Threads_created的值较大或正在增加，可以尝试增加thread_cache_size的值,通过检查Threads_created知道有多少缓存已经在缓存中了.

如果每秒创建的线程数量少于10个，缓存的大小就是足够的。另外，可以观察thread_connected值的变化来设置线程缓存，本系统中它的值保持在100以下。大多数情况，非常大的线程缓存是没有必要的,通常需要把线程缓存保持足够大以使threads_created不会经常增加，但是如果它的值非常大，本系统已超过一万就属于非常大了，那么就应该把它设置得小一点，因为操作系统不能很好地处理太多的线程，即使它们处于睡眠状态也不行.通常情况，据物理内存设置规则如下：1G内存设为8,2G内存设为16,3G内存设为32，4G或4G以上设为64。  本系统内存为4G，且thread_connected 的增幅并不大，thread_cache_size设置为64，不需要更改。 read_buffer_size

read_buffer_size是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁,并且频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。MySQL只有在查询需要的时候才会为该缓存分配内存，并且是一次性把指定的大小分配给该缓存.

read_rnd_buffer_size

read_rnd_buffer_size是MySQL的随机读缓冲区大小.当按任意顺序读取行时（例如,按照排序顺序），将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索,提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。MySQL只有在查询需要的时候才会为该缓存分配内存，并且只会分配所需的内存。 sort_buffer_size

sort_buffer_size是MySQL执行排序使用的缓冲大小.如果想要增加ORDER BY的速度,首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。MySQL只有在查询需要排序的时候才会为该缓冲区分配内存，只要发生了排序,MySQL会立即分配变量定义的所有内存,不管是否需要这么大的空间.如果它的值大于排序需要的空间，那么就

意味着浪费。sort_buffer_size有可能会受CPU缓存的影响. Sort_merge_passes

Sort_merge_passes的值较大说明应该增加sort_buffer_size，也许仅仅是为某些查询，最好的办法就是优化排序性能较慢的查询。

 sort_buffer_size为3M,能够满足系统的查询要求.对于排序没有额外要求的情况下不需要调整。 innodb_log_file_size Innodb_log_files_in_group

InnoDB

日志文件总体大小由

innodb_log_file_size

和

innodb_log_files_in_group控制，并且它们对写入的性能影响较大。这两个文件默认大小都较小，对于高性能的负载，这个大小是不够的,日志文件总大小的上限是4GB，但是即使是写入负载极高的查询也只需要几百兆，比如总共256MB。 innodb_log_buffer_size

InnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 Innodb_log _buffer中，当满足innodb_flush_log_at_trx_commit参数所设置的相应条件（或者日志缓冲区写满)之后，才会将日志写到文件中。可以通过 innodb_log_buffer_size 参数设置其可以使用的最大内存空间。控制缓冲大小的变量是innodb_log_buffer_size。不需要把缓冲区变得很大.推荐值是1MB到8MB。除非要写入大量的巨型BLOB记录，否则这个大小就足够了,日志相对InnoDB的正常数据要紧凑得多.它们不是基于页面的,所以它们不会在存储数据的时候浪费整个页面。 innodb_os_log_written

可以通过show innodb status命令的log部分检测InnoDB向日志文件写入了多少数据,一个好的办法就是观察10秒到100秒时间间隔内的数据，并且注意最大值，可以使用这个值来判断日志缓冲大小是否合适。例如，如果最大数据是每秒写入100KB，那么1MB的日志缓存可能就足够了.也可以使用这个指标来决定日志文件的合适大小。如果最大值是每秒100KB,256MB日志文件就已足够了. innodb_flush_log_at_trx_commit

如果比起持久性而更在意性能,可以通过设置

innodb_flush_log_at_trx_commit的值来控制日志缓存被刷写到什么地方及刷写的频率.

该参数可以设置为0,1，2，解释如下：

0：log buffer中的数据将以每秒一次的频率写入到log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的提交并不会触发任何log buffer 到log file的刷新或者文件系统到磁盘的刷新操作；

1：在每次事务提交的时候将log buffer 中的数据都会写入到log file,同时也会触发文件系统到磁盘的同步;

2：事务提交会触发log buffer 到log file的刷新，但并不会触发磁盘文件系统到磁盘的同步.此外,每秒会有一次文件系统到磁盘同步操作。

上午10点抽样查看半小时的日志数据情况，每分钟InnoDB向日志文件写入了多少数据，抽取其中数据较大的10条信息分析查看：

 InnoDB平均每分钟向日志文件写入约9.2MB，而本系统大部分情况下每分钟写入约5MB～6MB，每秒写入约100KB,本系统innodb_log_file_size设置为64MB，可以将该值设置为256MB，使其足够大满足性能要求，日志文件越大,越节省IO，但是会增长恢复时间。该抽样可能不是系统最高峰值，在系统负载最大时分析得出的结果更加准确。 innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct不是用来设置用于缓存某种数据的内存大小的一个参数，而是用来控制在 InnoDB缓冲池中可以不用写入数据文件中的脏数据页的比例（本系统为默认值:90%)，即已经被修改但还没有从内存中写入到数据文件的脏数据。这个比例值越大,从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘I/O.  保持默认值。 innodb_file_per_table

innodb_file_per_table选项使InnoDB为每一个表使用一个文件，它在数据库目录中以表名。ibd文件形式保存数据,这使得删除表后回收数据变得比较容易，并且它对于把表分布到多个磁盘上也很有用处，但将数据放在多个文件中能导致浪费更多的存储空间,因为它把单个InnoDB表空间的碎片都放在了ibd文件中，

这对于小表尤其会成为一个问题，因为InnoDB的页面大小是16KB，即使表只有1KB数据,它也需要至少16KB的磁盘空间。即使开启了innodb_file_per_table选项，还需要为撤销日志和其他系统数据定义表空间，而且不能简单地通过拷贝文件来移动、备份或恢复表,且肯定不能在服务器之间拷贝数据。

 本系统数据小于1万条表比重超过80%，不需要为每个表使用一个文件，保持默认值。 concurrent_insert

可以使用concurrent_insert变量配置MyISAM表的并发插入行为，它有下面的值：0，MyISAM不允许并发插入，每一次插入都会把表锁住；1，默认值,只要表中没有空缺，MyISAM就允许并发插入；2，它强制并发插入到表尾，即使表有空缺也不例外，如果没有线程从表中读取数据，MySQL就会把新数据插入到空缺中。使用了该值，表的碎片会增多,也就需要更经常地对表进行优化。  保持默认值。 delay_key_write

对于MyISAM表,可以通过配置把一些操作延迟，然后合并到一起执行,例如可以使用delay_key_write延迟写入索引,但也会带来一些矛盾：立即写入索引，安全但代价较高，或者等待写入并邪王在写入前不要断电，这样更快,但断电就会导致大规模的索引损坏。 innodb_thread_concurrency

InnoDB控制并发最基本的方式是使用innodb_thread_concurrency变量,它限制了一次有多少线程,它限制了一次有多少线程能进入内核，没有办法为所有的架构和负载确定最佳的并发数量，但通常情况下可以这样计算：  并发=CPU数量×磁盘数量×2,本系统计算并发数为8。 innodb_thread_sleep_delay

如果InnoDB内核中已经有了允许数量的线程，那么线程就不能再进入内核了,InnoDB采用了一种两阶段的过程来保证线程可以尽可能高效地进入内核,这种策略减少了操作系统引起的开销。线程首先睡眠innodb_thread_sleep_delay所规定的微秒数，然后再进行尝试，如果还是不能进入,它就会进入一个等待线程的队列中并且把控制权交给操作系统。第一阶段默认的睡眠时间是10000微秒，

当有很多线程都处于‘正在等待进入队列’这一状态时，改变这个值有助于提高系统并发，而默认值在有大量小查询的时候会太大了，因为它给查询增加了10毫秒延时。

 保持默认值，当并发使用大查询时才有必要调整该值。 innodb_commit_concurrency

InnoDB在提交阶段还有另外一种形式的并发瓶颈，就是刷写操作造成的密集I/O操作.innodb_commit_concurrency变量决定了某一时刻有多少线程能进行提交。当系统有大量线程状况不佳时，可以尝试将该变量增加.  保持默认值，即不限制并发提交线程数。 max_length_for_sort_data max_sort_length

MySQL有两种文件排序算法，如果需要进行排序的列的总大小超过了max_length_for_sort_data定义的字节，MySQL就会使用双路排序，反之就会选择单路排序,双路排序需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作.如果将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存，为了尽可能地提高排序性能,尽量使用第二种排序算法，所以在查询中仅取出需要的列是非常有必要的。  对于本系统,默认值足够大，能满足性能要求. Aborted_clients

如果Aborted_clients变量随时间增加，那么就要确定是否正常地关闭了连接.如果不是，就要检查网络性能，并且检查max_allowed_packet变量,超多了max_allowed_packet的查询会被强制地中断。 Aborted_connects

Aborted_connects变量的值应接近于0，否则就可能有网络问题，有几个被中断的连接是正常的。例如，试着从错误的主机连接、使用了错误的用户名和密码,或者定义了无效的数据库，都会发生这样的情况.

 观察本系统10分钟内的Aborted_clients变化，一直保持为0,说明没有连接方面的异常情况,可以定期观察该变量分析连接问题. binlog_cache_size Binlog_cache_disk_use

Binlog_cache_use

当使用事务的表存储引擎InnoDB时，所有未提交的二进制日志会被记录到一个缓存中，等该事务提交时直接将缓冲中的二进制日志写入二进制日志文件，而该缓冲的大小由binlog_cache_size决定,默认大小为32KB.此外，binlog_cache_size是基于会话的,当一个线程开始一个事务时，MySQL会自动分配一个大小为binlog_cache_size的缓存，因此该值不能设置过大。当一个事务的记录大于设定的binlog_cache_size时，MySQL会把缓冲中的日志写入一个临时文件中，因此该值又不能设得太小。通过查看binlog_cache_use、binlog_cache_disk_use的状态,可以判断当前binlog_cache_size的设置是否合适。Binlog_cache_use记录了使用缓冲写二进制日志的次数，binlog_cache_disk_use记录了使用临时文件写二进制日志的次数。如果binlog_cache_disk_use与Binlog_cache_use之间的比值很大，就应该增加binlog_cache_size的值，只要保证大部分的事务都在二进制日志缓存里就可以了。

 binlog_cache_disk_use/Binlog_cache_use比值非常小，说明本系统绝大部份事务都能下入在二进制日志缓存。 Created_tmp_disk_tables Created_tmp_tables

每次创建临时表时，Created_tmp_tables增加，如果是在磁盘上创建临时表,则Created_tmp_disk_tables也会增加，通常可以通过Created_tmp_tables/（Created_tmp_disk_tables + Created_tmp_tables)来判断基于内存的临时表利用率.如果Created_tmp_disk_tables太大，则需要检查并优化查询语句,或有可能是tmp_table_size和max_heap_table_size不够大。Created_tmp_disk_tables / Created_tmp_tables 〈= 5%就属于比较好的情况，如果该比值超过25%则需要调整，本系统中该比值约为11.24％。 tmp_table_size max_heap_table_size

tmp_table_size规定了内部内存临时表的最大值，每个线程都要分配,但实际起限制作用的是tmp_table_size和max_heap_table_size的最小值。如果内存临时表超出了限制，MySQL就会自动地把它转化为基于磁盘的MyISAM表，存储在

指定的tmpdir目录下(show variables like 'tmpdir').

max_heap_table_size变量定义了用户可以创建的内存表（memory table）的大小.这个值用来计算内存表的最大行数值.这个变量支持动态改变，改变后对于已经存在的内存表就没有什么用了，除非这个表被重新创建(create table）或者修改（alter table)或者truncate table。当然也可以在配置文件中设置全局变量。本系统中,只有32MB以下的临时表才能全部放内存,超过的就会用到硬盘临时表。 基于内存的临时表利用率：

 Created_tmp_tables/(Created_tmp_disk_tables + Created_tmp_tables） ≈90%，该比值较理想，如果需要使用大数据量临时表时，则手工创建新表来存储数据，避免临时表目录空间不够的问题。 Max_connections和Max_used_connections

max_connections表示允许的并行客户端连接数目。增大该值则增加MySQLd 需要的文件描述符的数量。table_cache、max_connections和max_tmp_tables系统变量影响服务器保持打开的文件的最大数量。如果你增加这些值其中的一个或两个，会遇到操作系统为每个进程打开文件描述符的数量强加的限制.table_cache与max_connections有关。例如，对于200个并行运行的连接，应该让表的缓存至少有200 ＊ N,这里N是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符.

max_used_connections是服务器启动后已经同时使用的连接的最大数量. max_used_connections / max_connections > 100％ （理想值 ≈ 85％）。MySQL服务器实际上允许max_connections+1个客户端进行连接。额外的连接保留给具有SUPER权限的账户.通过为系统管理员而不是普通用户授予SUPER权限(普通用户不应具有该权限）,系统管理员能够连接到服务器来诊断问题，即使已连接的无特权客户端数已达到最大值也同样。

 本系统中设置的最大连接数是600，而响应的连接数是601，应适当增加Max_connections变量的值. Open_files和Open_files_limit

如果Open_files的值与Open_files_limit的值较为接近，那就应该增加Open_files_limit。

max_connections 和 table_open_cache 与 open_files_limit 的关系： max_1 = 10 + max_connections + table_cache ＊ 2,该值为1122； max_2 = max_connections ＊ 5，该值为3000；

max_3 = max_os_open_files,该值为1024，表示操作系统单个进程最大允许打开文件句柄（文件描述符）。

 open_files_limit 取三个值中的最大值 ,设置3000较合理，不需要调整. Select_full_join

全联接是无索引联接，它真正影响性能,最好能避免全联接,即使是每分钟一次也较多，如果联接没有索引,则最好能优化查询和索引. select_full_range_join

如果select_full_range_join的值过高，就说明运行了许多使用了范围查询联接表,有时大的范围查询也会比较慢，可以从中进行优化。 Select_range_check

Select_range_check变量记录了在联接时,对每一行数据重新检查索引的查询计划的数量，它的性能开销很大，如果该值较高或正在增加，则说明一些查询没有找到好索引.

 上述变量目前正常,如果发生明显变化,则结合慢查询日志跟踪全联接性能较差的查询。 Slow_launch_threads

该变量如果较大则说明某些因素正在延迟联接的新线程，服务器存在一些问题。它通常表示系统过载，导致操作系统不能给新创建的线程分配时间片。 Table_locks_waited

Table_locks_waited变量显示了有多少表被锁住了并且导致服务器级的锁等待，InnoDB的行级锁不会使该变量增加。如果该值较高并且正在增加，则说明存在严重的并发瓶颈，这时应该考虑使用InnoDB或另外使用行级锁的存储引擎，或者手动对大表进行分区，并优化查询，启用并发插入或对锁设置进行优化。  本系统该变量在半小时内变化幅度不超过3，不必调整.

2. MySQL优化策略

2.1. 索引策略

索引是帮助MySQL高效获取数据的数据结构，它对于高性能非常关键，因此建立索引是现实中性能问题的首要原因。索引在数据越大的时候越重要。规模小，负载轻的数据库即使没有索引，也能有好的性能，但是当数据增加的时候，性能就会下降。

MySQL有多种类型类型的索引,它们各有自己的性能特点.索引是在存储引擎层实现的,而不是在服务器层,因此它们并不是标准化的，每个引擎的索引工作方式略有不同，并不是所有的引擎都支持所有类型的索引。即使多个引擎支持同样的索引,他们的实现方式也可能有所不同。MySQL索引类型的各自特点可以查阅相关资料去进一步了解。

即使已经了解了关于索引的知识，但也许还不知道如何从实际的表开始。虽然通常情况下是检查系统中最常运行的查询,但往往性能瓶颈可能就出现在不那么经常进行的插入或更新操作，要避免在不知道什么查询会使用索引之前就创建它这种常见错误,并且要考虑是否所有的索引能形成一个优化的配置.

有时只从查询就可以知道需要什么索引，只要把它们加上就可以了.但是有时各种类型的查询，却不能找到适合他们的完美索引,这种情况就需要一些折中。为了找到最佳平衡，应该进行测试和剖析.

第一个要检查的就是响应时间，要考虑为任何耗时很长的查询创建索引，然后检查导致最大负载的查询,并且添加索引予以支持。如果系统正好碰到了内存、CPU或磁盘瓶颈，也要把他们考虑进去。例如,如果运行了很长的聚合查询以生成汇总,那么磁盘会因为使用了支持GROUP BY查询的索引而得益。

通常情况下,都要试着扩展索引，而不是新增索引，维护一个多列索引要比维护多个单列索引容易，但不能过多否则就会有麻烦，使用太多这种列表也会造成优化器要计算的组合激增，并最终降低查询速度。如果不知道查询的分布，就尽可能使索引变得更有选择性,这样更有好处。即使已经创建了正确的索引,还需要维护表和索引以确保它们能很好的工作。

2.2. 查询性能优化 2.2.1 优化数据访问

查询性能低下的最基本原因就是访问了太多数据.一些查询不可避免地要筛选大量数据,大部分性能欠佳的查询都可以用减少数据访问的方式进行修改。在分析性能较低的查询的时候,查明应用程序是否正在获取超过需要的数据，即访问了过多的行或列,以及MySQL服务器是否分析了超过需要的行.

一些查询先向服务器请求不需要的数据，然后再丢掉它们。这给服务器造成了额外的负担,增加了网络开销,消耗了内存和CPU资源. 2.2.2 重构查询

当优化有问题的查询时，并不意味着一定要从MySQL得到完全一样的结果,偶尔可以用完全等价的方式得到更好的性能。如果不同的查询能提供更高的效率，尽管得到的结果不同，也可以考虑重写查询。也许最终应用程序的代码也会和查询一起被改写。

查询的执行路径

2.2.3 复杂查询

一个重要的查询设计问题就是是否可以把一个复杂查询分解成多个简单的查询.大多数时候，都强调用用尽可能少的查询做尽可能多的事情.这种方式的积极意义在于它可以减少查询解析和优化的步骤,并且代码量较少。但是对于数据量较大的查询来说,通过分解查询可以得到更高的效率，同时也使程序更易于维护和扩展.可以使用分治法，让查询在本质上不变，但是每次只执行一小部分，以减少受影响的行数，清理陈旧数据也是一种很好的方式。

想得到高性能,最佳的方式就是了解MySQL如何优化和执行查询，一旦理解了背后的机制,那么很多优化工作就简化为纯粹的推理，并且也可以理解查询优化过程中的逻辑性。

2.2.4 分解联接

在较复杂实际应用中，可以采用“分解联接\"把一个多表联接分解成多个单个查询,然后在应用程序端实现联接操作，尽管从代码初看上去比较浪费,其实也只是增加了查询的数量而已,但是这种分解方式有很好的性能优势，查询本身会更高效,能使缓存的效率更高，如果一个表经常改变，那么分解联接就可以减少缓存失效的次数;同时对于MyISAM表来说,每个表一个查询可以更有效地利用表锁，因为查询会锁住单个表较短时间,而不是把多个表长时间锁住,有时在应用程序端进行联接可以更方便扩展数据库。 2.2.5 查询缓存

MySQL有一种称为查询缓存的缓存机制，它可以保留查询返回给客户端的完整结果。当缓存命中的时候，服务器马上返回保存的结果，并跳过解析、优化和执行步骤.MySQL将查询缓存完全存储在内存中，缓存不仅仅存储了查询结果，它在某种程度上像一个文件系统，它保持了自身的结构,而这些结构有助于它了解那块内存是空闲的、表和查询之间的映射关系。

查询缓存保留了查询使用过的表,如果表发生了改变,那么缓存就失效了,这样看上去不够高效，某些表的改变不会导致查询结果的改变。但是这种简单方式的开销比较小，而这对于繁忙的系统是很重要的。查询缓存对应用程序完全透明,程序不用知道MySQL是从缓存中返回结果还是通过实际计算返回结果。两种方式返回的结果是一样的，即不会改变语义,不管缓存是打开的还是关闭的,服务器的行为都一样.

缓存并不会自动地比非缓存高校。缓存也需要开销，只有在节省的资源大于开销的时候,缓存才是真正有效率的,这和服务器的负载有关。理论上，可以通过对比在缓存开启和关闭时服务器需要做的工作来了解缓存是否有帮助，但实际上很难精确地计算或者预测查询缓存的好处，有时必须考虑外部因素。例如，查询缓存可以减少产生结果的时间,但它不会减少将结果发送到客户端的时间，而这有可能是主要因素。

从缓存中受益最多的查询可能是需要很多资源来产生结果，但是不需要很多空间来保存的类型，比如集合查询.所以用于存储、返回和失效的代价都较小。

当然也有其他类型的查询值得缓存。

检查是否从查询缓存中受益的最简单的办法就是检查缓存命中率，它是缓存提供的查询结果的数量，而不是服务器执行的数量。当服务器收到SELECT语句的时候，Qcache_hits和Com_select这两个变量会根据查询缓存的情况进行递增，查询缓存命中率的计算公式是：Qcache_hits / （ Qcache_hits + Com_select ）。命中率多少才好视情况而定,如果缓存命中率代表了开销最大的查询，那么即使是很低的命中率也有很大的好处。缓存可能会因为碎片、内存不足或数据改变而失效.如果已经给缓存分配了足够的内存，并且把query_cache_min_res_unit调整到了合适的值,那么大部分缓存失效应该是由数据改变引起的。可以通过检查Com_update, Com_delete等的值知道有多少查询修改了数据，也可以通过检查Qcache_lowmen_prunes的值了解有多少查询因为内存不足而失效.

应该监视服务器实际使用的缓存数量,如果它没有用到分配的内存,就应该把分配给它的内存减少一点.如果由于内存限制引起了缓存失效，那么就应该多分配一些内存，但不用太在意缓存的大小,它比有实际影响的稍大一点或小一点都没有问题，只有在内存有严重浪费或缓存失效太多的时候才需要去考虑它的大小，有时还应该在服务器其他缓存和查询缓存之间找到某种平衡。

3. 调整配置文件

MySQL的默认配置不适用于使用大量资源，具体的配置文件依赖于服务器的硬件、数据量、查询类型、响应时间、事务持久性和连续性等因素.不能一直期望改变配置文件而带来巨大的性能替身，提升的具体大小取决于工作负载，通常可以通过选择适当的配置参数得到两到三倍的性能提升.在这之后，性能提升就是增量的，通过改变一两个配置参数，可能会看到某些原本运行较慢的查询快了一些,但是，服务器的性能通常不会提升一个数量级，为了达到更大的提升，通常需要检查服务器架构、查询及应用程序的架构。

最好的方式每次只小幅度地改动一两个设置，并且在每次改动之后都进行测试查看效果,也许增减一点就带来了性能提升,但是再增加一点就让性能下降了，那么有可能是过多地请求了某种资源,也有可能是在服务器和操作系统或硬件之

间造成了某种不匹配，sort_buffer_size有可能会受到CPU缓存的影响。一些变量也会依赖于其他变量，innodb_log_file_size的最佳大小就依赖于innodb_buffer_pool_size。了解这些需要经验，也需要对系统架构有一定了解. 在对配置进行调优之前,应该对查询和结构进行调优，进行一些最基本的优化,比如添加索引。如果已经对配置文件做了很多调整，再回过头来更改查询和架构，那么就有可能要重新调整配置文件，调优是一个渐进的过程，可以把调整配置文件看成一个两步的过程：在安装的时候使用适当的初始值,然后基于工作负载进行细节调整。

4. 内存使用调优

配置MySQL正确地使用内存对性能至关重要.可以认为MySQL的内存消耗有两种范畴：可以控制的和不可控制的。不能控制MySQL使用多少内存来运行服务器、解析查询及管理内部运行，但是可以控制它为特定工作使用多少内存。 在特定的系统上，MySQL可能使用的内存有一个绝对的上线。起始点是服务器物理内存的数量。MySQL只需要很少的内存保持连接开启。它也需要一定的基本内存来执行查询,需要在MySQL工作负载处于顶峰的时候为它分配足够的内存，否则查询就会因为内存不足而变得很慢，甚至失败。和为查询指定内存一样，还应当为操作系统保留足够的内存.

5. MySQL I/O调优

某些配置选项可以影响MySQL把数据同步到硬盘和进行恢复的方式,它们通常对性能有很大的影响,因为这其中牵涉了昂贵的I/O操作，同时也代表了性能和数据安全的折中。一般情况下,保证数据被立即而连续地写入磁盘的代价是很高的。

MyISAM表通常在每次写入之后就会把索引的改变刷写到磁盘上。如果准备对一个表做很多改变，那么把它们组成一个批处理就会快很多。

系统绝大多数表使用的是InnoDB引擎，我们不仅可以控制它如何恢复,还可

以控制它如何打开表及刷写数据,它们影响了数据恢复和总体性能.InnoDB的恢复过程是自动的并且在InnoDB启动的时候总会运行，但人为可以影响它。 InnoDB有复杂的链式缓冲区和文件，使它可以改进性能并且保证ACID属性。对于普通使用，一些较重要的配置是InnoDB日志文件大小、InnoDB如何刷写日志缓冲区,以及InnoDB如何执行I/O。

InnoDB的缓冲区和文件

6. InnoDB调优

InnoDB是系统主要用到的存储引擎，它直接影响着系统的事务控制、安全性、备份恢复以及日常的业务查询等。

6.1 InnoDB事务日志

InnoDB使用日志来减少提交事务的开销,它不是在每次事务提交的时候就把缓冲池刷写到磁盘上，而是记录了事务。事务对数据和索引做出的改变通常会被映射到表空间的随机位置，所以将这些改变写道磁盘上就会引起随机I/O,随机I/O比顺序I/O开销要高得多。InnoDB使用自身的日志把随机I/O转换为顺序I/O,一旦日志被记录到磁盘上，事务就是持久的了,尽管这时候改变还没有被写道数据文件中。如果一旦发生意外比如断电，InnoDB可以回放日志并恢复提交了的事务。当然，InnoDB最终要把改变写到数据文件中，因为日志的大小是固定的。它以循环的方式写日志,当记录达到日志的底部，就会又从顶部开始，它不会覆盖改变没有被应用到数据文件的记录，因为这会消除提交的事务唯一持久性的记录。

InnoDB使用后台线程智能地把改变写入到文件中。该线程可以把写入集中在一起，然后以更高的顺序写入的方式执行。实际上,事务日志把随机数据文件I/O转换为顺序日志文件和数据文件I/O。把刷写工作后台进行可以让查询完成得更迅速，并且在查询负载很大的时候可以对I/O系统进行缓冲。

InnoDB用多个文件组成一个循环日志系统,通常不用改变默认的日志数量，只须改变每个日志文件的大小就可以了.为了改变日志文件的大小，先关闭

MySQL，然后移走旧日志,再重新配置大小，最后重新启动MySQL就行了。要确保干净地关闭MySQL，否则日志文件中就有可能还保留着需要被应用到数据文件的记录。重新启动服务器的时候可以观察错误日志，完成启动后就可以删除旧日志。

为了决定日志文件的理想大小，需要衡量通常数据改变的开销和崩溃时恢复的时间.如果日志太小,InnoDB将会设置更多的检查点，并且导致更多日志写入。如果日志太大，InnoDB在恢复的时候可能就会做大量的工作，增加恢复的时间。另外，数据大小和访问模式也会影响恢复时间。如果数据量很大，在缓冲池中也有许多不干净的页面，比如那些更改没有被应用到数据文件的页面，并且他们在整个数据中均匀分布,那么从崩溃中恢复就会花很长时间，InnoDB不得不扫描日志、检查数据文件，并且按照需要把改动应用到文件上,这意味着大量的读写，另一方面，如果更改是局部化的，比如只有很少比例的数据经常被改动,恢复就会很快，即使数据和日志文件很大也是这样。恢复时间也依赖于典型更改的大小,它和数据行的平均长度有关，较短的行可以在日志中存储更多的改动，所以InnoDB在恢复的时候就会回放更多的改动.

在InnoDB改变数据的时候，它会把这次改动的记录写到日志缓冲里面。日志缓冲被保存在内存中.缓冲写满或事务提交，不管哪种情况先发生，InnoDB都会把缓冲区写道磁盘上的日志文件中。如果有大型事务,就可以增加缓存文件来减少I/O动作。

日志缓冲区必须被刷写到持久性存储中，以保证提交了的事务能完全持久化。重要的是知道把日志缓冲写入日志文件和把日志刷写到持久性存储中的区别。在大多数操作系统中，把缓冲写入日志只是简单地数据从InnoDB的内存缓冲区移到操作系统的缓存中,它也在内存中,实际上不会把数据写入持久性存储中。相反的是，将日志刷写到持久性存储中意味着InnoDB要求操作系统把数据刷到缓存外部并且确保写入到磁盘上,这会阻止I/O掉用，直到数据被完全写入。

6.2 InnoDB表空间

InnoDB把数据保存在表空间中。表空间实际上是跨越了磁盘上的一个或多个文件的虚拟文件系统。InnoDB出于很多考虑使用了表空间，而不仅仅是为了

存储表和索引，它保留了自己的撤销日志、插入缓存以及表空间的其他内部结构.可以使用innodb_data_file_path定义表空间文件，这些文件都在innodb_data_home_dir定义的目录中.

管理表空间也许是一件费力的事情,尤其是它能自动增长,而同时又想收回空间.回收空间的唯一办法就是将数据进行转储、关闭MySQL、删除旧的文件、改变配置、重新启动、让InnoDB创建新文件并且恢复数据。InnoDB对空间的控制很严格，不能简单地移除文件或改变大小.如果表空间崩溃了的话,InnoDB就不会启动，它在写入负荷很重的情况下会增长的很大,但不能像在MyISAM中那样随便移动数据。

7. MySQL并发调优

当MySQL在高并发条件下工作的时候,可能会遇到以前未曾遇到过的性能瓶颈。

7.1 MyISAM并发调优

需要很仔细地控制同时进行的读写，以避免读取到不连续的数据。MyISAM在某些条件下允许并发插入和读取，并且还可以认为调度某些操作,以尽可能少地阻止工作。MyISAM的删除操作不会重新安排整个表，它只是把行标记为已经删除,并且在表中留下了一些空余标识，MyISAM在可能的情况下会优先使用这些空余，为插入复用空间.如果表是完整的，它就会把新的行拼接在表的最后。即使MyISAM有表级别的锁，它也能在读取的同时把行拼接到表尾,通过禁止读取最后一行做到这一点,这避免了不连续的读取。但当表中间的数据改变的时候，要提供连续读取就困难得多,它只有在到达表尾的时候才允许并发插入。可以使用concurrent_insert变量配置MyISAM的并发插入行为。

7.2 InnoDB并发调优

InnoDB是为高并发设计的,它的结构仍然基于有限内存、单CPU和单磁盘系统(新发布的5。5版本针对InnoDB已有较大改善)。InnoDB某些方面的性能

在高并发条件下下降得很快,并且唯一的解决办法就是限制并发。

8. 操作系统和硬件优化

MySQL服务器中最弱的部分决定了其性能，它的操作系统和硬件通常也会成为限制因素。磁盘大小、可用内存、CPU资源、网络和连接它们的组件一起决定了系统的最终容量。通常情况下，安装更多内存、重新配置磁盘或升级I/O是较好的选择.CPU饱和发生在MySQL使用的数据能被装入内存，或者能够尽快根据需要从磁盘上读取的时候，密集地在多表之间执行无索引的连接操作都可能引发CPU饱和。I/O饱和通常发生在需要的数据比内存多得多的时候。如果应用程序分布在网络上，查询数据相当巨大,或者要求很低的延时，瓶颈就会转移到网络上.发现瓶颈通常不是显而易见的事情。某个区域的弱点通常会给其他方面带来压力，问题可能就会表现在其他子系统上面。例如没有足够的内存,MySQL就会刷写数据，为需要的数据腾出空间，然后再马上把数据重新读回来,因此内存不足就变线为I/O容量不够,同样地，达到饱和的内存问题也会表现为CPU问题.

拥有大量内存的最大原因不是能够在内存中保存大量的数据，最终目的是可以避免磁盘I/O,访问磁盘的速度比访问内存慢几个数量级，关键就是在于平衡内存大小、速度、开销和其他因素,以得到好的性能。

数据库服务器同时使用了随机I/O和顺序I/O，随机I/O从缓存中得益是最多的。磁盘中的顺序操作都比随机操作快，顺序访问内存中的数据也比随机访问快,两者可能会出现几个数量级的差别。存储引擎能更快地执行顺序读取，随机读取通常意味着存储引擎需要做索引操作，因此，对于随机读取I/O问题,添加内存是最佳选择。

9. 大数据表的设计优化

平台的主要性能瓶颈主要是在大表查询这块,表写入目前还没有形成压力，以下主要阐述几种设计思路,有的只会在某种特定业务中会用到,需要通过具体业

务来对表设计进行具体分析。

9.1. 垂直切分

垂直切分主要是针对数据库架构而非表的设计而言的,在这里提出来是因为它会影响到大表结构的设计。

系统的总体功能是由多个功能模块所组成，而每一个功能模块所需要的数据对应到数据库中就是一个或多个表.各个功能模块相互之间的接口越统一,越少，系统的耦合度就越低，实现数据的垂直切分就越容易。若某一个功能模块其整体数据量特别大或者因该功能并发读写特别频繁而形成瓶颈,且与系统的其他功能模块耦合度很低，则可以考虑将该功能模块整体垂直切分，将其部署在单独的主机上，分摊整体系统的压力。

9.2. 水平切分

水平切分是针对表按照数据行来切分，即将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中.切分必须按照某种特定的规则来进行，这样才能够较容易地判定各行数据被切分到那个数据库中了.可根据关键字段的取值（奇偶性,取模）,时间字段的范围等来进行水平切分，结合实际业务需要和数据量来定义切分的粒度.

如果某个功能存在一张大数据量表而影响性能，而整个功能模块的大部分核心表都可以通过某个字段（如：userid)来进行关联,那这个字段就是一个进行水平切分的关键字段，这样所有可以与该字段关联的表都可以按照特定规则被水平切分到同一个数据库中，既能有效分摊单一数据库的数据承载压力,又不影响原库表的关联操作。

如果只单独切分这一张大表，这样也能减轻该表的大数据量瓶颈，但原来的关联操作就需要跨库操作或是需借助应用程序来完成.

9.3. 拆分表

水平切分是根据数据行来操作，拆分表是在保持原表的列属性不变的情况下

将表拆分为二个表或多个表，是按照表列来操作。

当对一个大数据量表频按照某一维度频繁查询统计多项指标数据时，由于各个事务都会争用同一个大数据量表资源而使效率低下。例如一张同时记录发生额信息和余额信息的大数据量表，系统一个或多个功能点需要查询统计发生额的相关信息，另一个或多个功能点需要查询统计余额的相关信息,且发生额和余额之间没有数据关联操作,为了解决同时争用一个大表资源的问题,可以将该表一分为二，其中一个表只存储发生额的相关信息,另一个表只存储余额的相关信息，在查询统计这两项指标数据时，就可以分别从两张表来获取数据，从而解决大表资源争用的问题。可以认为拆分表即是针对表而言的垂直切分。

虽然列属性没有改变,但数据结构变更（表名和列数量）,对于已有系统中耦合度高,功能关联性强的模块需要修改与之相关的程序，包括数据入库，读写表等操作.

9.4. 数据转移

数据转移是常用的将大数据量表瘦身为小数据量表的实施方式，在大多数既定业务规则下,系统中需使用的是时间较为靠近的业务数据（短时间的可能一年，长时间可能为三年五年），而由于时间范围大，会使得某些业务数据表数据量庞大，但有效使用数据却只占很小一部分，这种情况下可以将过期不用的数据转移到另外一张归档表中,该表只做数据存储,没有任何其他功能。

9.5. 表分区

表分区可以通俗的认为是将一张大表，根据条件分割成若干“小表”，但不会改变任何表结构，而是将“小表”数据分别存储在独立的分区内，分割后的分区仍然是一个整体，在进行分区设计时,不能够只对数据分区而不对索引分区，也不能够对索引分区而不对数据分区。MySQL可支持多种分区类型(RANGE、LIST、HASH…）。

既然需要分区,则会有一个分区的规则，一般情况下是根据数据量的分布规则来制定，如表数据量是按照入库时间来递增存储的，则可按照入库时间进行分区，也可以按照区域来进行分区，分区的数据粒度具有一定规则，要避免个分区

数据严重不均的情况,如一部分的分区数据量非常大，而另外一部分的分区数据量却又很小,分区的主要目的就是将大数据量均匀的分布存储到各个独立的分区内，这样获取数据时就可以避免全表扫描，而只需要在数据存储的指定分区内查找就可以了，不必去查询其他的分区，提高查询效率，必要时也可以对单个分区建立索引。如果多个分区可以存放在多快硬盘上，硬盘的吞吐量可以得到显著提高，也可以提高查询的效率.同时，也可以根据需要能够方便的删除分区或新增分区，对于海量的大数据表尽量避免这么做，这样做需要对数据进行重新组织,会大量的消耗服务器的资源，在前期，如果有必要采用分区管理的表，应提早去做.