mysql性能优化解决方案.doc

MySQL数据库性能优化Renhao 2011/11/301. 资源管理平台数据库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, stripped32位Linux服务器，单独作为MySQL服务器使用。1.2. MySQL系统使用的是MySQL5.1，最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面：1） 默认存储引擎更改为InnoDBInnoDB作为成熟、高效的事务引擎，目前已经广泛使用，但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. 数据分布情况服务器某时间点非精确值：数据量范围表数量（总共339张，其中分区表2张）1000万rows5000万4张（MyISAM表1张）500万rows1000万6张100万rows500万5张50万rows100万4张10万rows50万12张（MyISAM表1张）5万rows10万9张（MyISAM表1张）1万rows5万23张（MyISAM表2张）1 rows1万136张（MyISAM表9张，MEMORY表2张）rows=0（无数据）140张观察系统中数据量很大且未进行表分区的InnoDB表l adrotateresultdetail_fail的数据量达到4千万，createTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。l terminalalarm的数据量也突破千万，AlarmTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。在事件ev_terminalalarm中会查询该表，若进行表分区，也能一定程度上提高事件的执行效率。l terminalalarminfo表仅自增列有索引，主要用于存储数据，可不用分区。l Terminallogin表的loginTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。l adplayinfo_bak表存在多个以INT类型为索引的列，根据实际业务情况选择查询频率高且能以范围值来分区的整型列对该表进行分区。l adrotateresultdetail的createTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。l upfile_bak表仅自增列有索引，若存在查询或者统计业务则可以createTime列进行分区，若该表没有查询方面业务可不必进行分区。除去配置参数等属性表，对于数据量大且不断递增的业务数据表，最直接的办法可以按照时间字段进行分区，或是根据查询业务来选择合适的列进行表分区和创建索引，这样能够有效提高存储和查询效率。1.8. 服务器配置参数记录查询：普通日志log、慢速日志log_slow_queriesMySQL有两种查询日志：普通日志和慢速日志，它们都会记录查询。普通日志记录了服务器接收到的每一个查询，也包含了没有被执行的查询，比如因为错误而未被执行的查询，还有一些非查询事件，比如连接和断开连接，普通日志不包含执行时间或其他只有在查询结束之后才能得到的信息。相反，慢速日志只包含了已经执行过的查询，如果是启动状态，它记录了执行时间超过了特定长度的查询。两种日志都有助于分析，但是慢速日志更有利找到性能较慢的查询。一个相关配置是log_queries_not_using_indexes，它使服务器把没有使用索引的查询记录到慢速查询日志中，无论它们执行速度有多快。尽管打开慢速日志相对于执行慢速查询来说，通常只增加了很少的时间，但是如果没有使用索引的查询非常快，例如从小数据量表中查询，这样就会记录它们可能导致服务器变慢，甚至还会使用大量的磁盘空间，慢速日志也许就会被那些快速高效的查询塞满。慢查询日志可以用来找到执行时间长的查询，可以用于优化。慢日志打开后，通过设置long_query_time来配置记录查询超过的指定时间，默认值为10秒，根据系统的负载和性能要求进行设置(SET GLOBAL long_query_time = )。检查又长又慢的查询日志会很麻烦，可以使用MySQLdumpslow命令获得日志中显示的查询摘要来处理慢查询日志。系统两种日志都没有开启，可以在需要的时候打开慢速日志来帮助分析性能较慢的查询。具体实施参考MySQL手册。需要注意的是查询在日志中只出现一次并不意味着它是一个不好的查询，也不意味将来也会慢，查询时快是慢有多种原因：1） 表也许被锁定，导致查询处于等待状态；2） 数据或索引也许没有被缓存在内存中；3） 或者正在进行批处理大量的数据，使得磁盘I/O变慢；4） 服务器可能同时在运行其他的查询，影响了当前查询的效率。因此，只能把慢速查询日志看成调优工作的一部分，可以用它来找到可疑的查询，但需要对它们进行仔细地排查和分析。u 启用系统慢速日志，分析查询性能慢的时候可以观察该日志信息。Qcache_hitsCom_selectQcache_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的值了解有多少查询因为内存不足而失效。u 接近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来避免这种开销。u 以查询某表（18列）中的5000条结果为例，结果集数据大小约为1.4M，该设置是能满足要求的，保持该值即可。但如果查询结果数据过万的情况较多的话则应适当增加该值，最大不要超过4M。Qcache_free_memory如果缓存由大结果和小结果混合而成，那么就很难找到一个合适的大小，既能避免碎片，也能避免过多的内存分配，但是缓存大结果没有太大的益处，可以通过降低Query_cache_limit的值阻止缓存大结果，它有时有助于在碎片和在缓存中保存结果的开销中得到平衡。Query_cache_min_res_unitQcache_free_blocksQcache_total_blocksQcache_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可以避免在查询缓存中造成大量的内存浪费，关键在于每一个新块和服务器已分配给存储结果的块的数量之间找到平衡，如果值过小，服务器将会浪费较少的内存，但会更频繁地分配块，这对服务器意味着更多的工作。如果值过大，碎片将会很多，合适的折中是在浪费内存和增加处理时间上取得平衡。u 空缓存百分比：Qcache_free_blocks / Qcache_total_blocks 16%，且系统Qcache_free_blocks值较高，有可能是出现碎片了，使用flush query cache整理查询缓存并消除碎片，该命令不会从缓存中移除任何查询。同时定期观察内存碎片情况。Key_buffer_sizeKey_readsKey_reads_requests键缓存读命中率：100-（Key_reads*100）/ Key_reads_requests）= 99.975Key_read_requests和Key_reads是两个计数器，Key_read_requests是从缓存读取索引的请求次数，Key_reads是从磁盘读取索引的请求次数。key_buffer_size指定MyISAM表索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。MyISAM键缓存默认只有一个缓冲区，MyISAM自身只缓存了索引，没有数据，它让操作系统缓存数据，它的值应该占到所有保留内存的25%到50%，操作系统缓存用来保存从MYD文件中读取出来的数据。该变量给键缓冲分配指定大小的空间，但是操作系统只有在实际用到这些空间的时候才会进行分配，也可以创建多个键缓存，如果对于一个非默认大小的键缓存设置为0，MySQL就会把每一个索引从特定的缓存移到默认的缓存中，并且在没有对象使用特定的缓存时就将其删掉，给一个不存在的缓存设置这个变量将会创建缓存，对一个已有的缓存设置非零值将会冲洗缓存，这是一个在线操作，它会阻止所有访问该缓存的动作，直到缓存冲洗完成。另一个参考指标是单位时间内Key_reads值的变化情况。u 系统使用MyISAM表查询频率较低，键缓存读命中率在99%以上，表明键缓存能满足系统的性能要求。Key_blocks_unusedKey_blocks_used键缓存使用率= Key_blocks_used/ (Key_blocks_used+ Key_blocks_unused)=37%u 尽管键缓存使用率较低，说明key_buffer_size设置较高，MySQL没有将其使用完，基于键缓存各方面都能满足系统要求且内存够用，不必调整。table_cache_size/table_open_cache (5.1.2之后叫做table_open_cache)Open_tablesOpened_tablesOpen_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的值了。u Open_tables值与table_cache相等，且观察到Opened_tables较大，应适当增加table_cache，可将其设置为512。thread_cache_sizethread_cache_size是缓存的同时操作的线程数。线程缓存保存了和当前连接无关的线程，这些线程可以供新连接使用。当一个新连接被创建出来并且缓存中有一个线程的时候，MySQL会把这个线程从缓存中删除，并且把它赋给连接。连接关闭时，MySQL会回收线程，把它放回到缓存中。如果缓存中没空间了，MySQL就会销毁该线程。只要缓存中有自由的线程，MySQL就能很快地响应连接请求，因为它不需要为每个连接都创建新的线程。设置该变量不会立即生效，需要等到下一次线程关闭的时候，MySQL会检查缓存中是否有空间存储线程。如果是，他会把线程缓存起来，供另外一个连接使用，如果不是，它会直接结束线程，这种情况下，缓存中线程的数量，以及线程缓存使用的内存数量不会立即下降。只有当新连接为了使用线程而把它从缓存中移走的时候才会看到下降。MySQL只有在连接关闭的时候才会把线程加入缓存，也只有在创建新连接的时候才从缓存中移除线程。Connectionsthread_connectedthreads_createdConnections变量表示连接意图的数量，而不是当前接连的数量（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。u 本系统内存为4G，且thread_connected 的增幅并不大，thread_cache_size设置为64，不需要更改。read_buffer_sizeread_buffer_size是MySQL读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySQL会为它分配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。MySQL只有在查询需要的时候才会为该缓存分配内存，并且是一次性把指定的大小分配给该缓存。read_rnd_buffer_sizeread_rnd_buffer_size是MySQL的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。MySQL只有在查询需要的时候才会为该缓存分配内存，并且只会分配所需的内存。sort_buffer_sizesort_buffer_size是MySQL执行排序使用的缓冲大小。如果想要增加ORDER BY的速度，首先看是否可以让MySQL使用索引而不是额外的排序阶段。如果不能，可以尝试增加sort_buffer_size变量的大小。MySQL只有在查询需要排序的时候才会为该缓冲区分配内存，只要发生了排序，MySQL会立即分配变量定义的所有内存，不管是否需要这么大的空间。如果它的值大于排序需要的空间，那么就意味着浪费。sort_buffer_size有可能会受CPU缓存的影响。Sort_merge_passesSort_merge_passes的值较大说明应该增加sort_buffer_size，也许仅仅是为某些查询，最好的办法就是优化排序性能较慢的查询。u sort_buffer_size为3M，能够满足系统的查询要求。对于排序没有额外要求的情况下不需要调整。innodb_log_file_sizeInnodb_log_files_in_groupInnoDB日志文件总体大小由innodb_log_file_size和innodb_log_files_in_group控制，并且它们对写入的性能影响较大。这两个文件默认大小都较小，对于高性能的负载，这个大小是不够的，日志文件总大小的上限是4GB，但是即使是写入负载极高的查询也只需要几百兆，比如总共256MB。innodb_log_buffer_sizeInnoDB 在写事务日志的时候，为了提高性能，也是先将信息写入 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条信息分析查看：u InnoDB平均每分钟向日志文件写入约9.2MB，而本系统大部分情况下每分钟写入约5MB6MB，每秒写入约100KB，本系统innodb_log_file_size设置为64MB，可以将该值设置为256MB，使其足够大满足性能要求，日志文件越大，越节省IO，但是会增长恢复时间。该抽样可能不是系统最高峰值，在系统负载最大时分析得出的结果更加准确。innodb_max_dirty_pages_pctinnodb_max_dirty_pages_pct不是用来设置用于缓存某种数据的内存大小的一个参数，而是用来控制在 InnoDB缓冲池中可以不用写入数据文件中的脏数据页的比例（本系统为默认值：90%），即已经被修改但还没有从内存中写入到数据文件的脏数据。这个比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘I/O。u 保持默认值。innodb_file_per_tableinnodb_file_per_table选项使InnoDB为每一个表使用一个文件，它在数据库目录中以表名.ibd文件形式保存数据，这使得删除表后回收数据变得比较容易，并且它对于把表分布到多个磁盘上也很有用处，但将数据放在多个文件中能导致浪费更多的存储空间，因为它把单个InnoDB表空间的碎片都放在了ibd文件中，这对于小表尤其会成为一个问题，因为InnoDB的页面大小是16KB，即使表只有1KB数据，它也需要至少16KB的磁盘空间。即使开启了innodb_file_per_table选项，还需要为撤销日志和其他系统数据定义表空间，而且不能简单地通过拷贝文件来移动、备份或恢复表，且肯定不能在服务器之间拷贝数据。u 本系统数据小于1万条表比重超过80%，不需要为每个表使用一个文件，保持默认值。concurrent_insert可以使用concurrent_insert变量配置MyISAM表的并发插入行为，它有下面的值：0，MyISAM不允许并发插入，每一次插入都会把表锁住；1，默认值，只要表中没有空缺，MyISAM就允许并发插入；2，它强制并发插入到表尾，即使表有空缺也不例外，如果没有线程从表中读取数据，MySQL就会把新数据插入到空缺中。使用了该值，表的碎片会增多，也就需要更经常地对表进行优化。u 保持默认值。delay_key_write对于MyISAM表，可以通过配置把一些操作延迟，然后合并到一起执行，例如可以使用delay_key_write延迟写入索引，但也会带来一些矛盾：立即写入索引，安全但代价较高，或者等待写入并邪王在写入前不要断电，这样更快，但断电就会导致大规模的索引损坏。innodb_thread_concurrencyInnoDB控制并发最基本的方式是使用innodb_thread_concurrency变量，它限制了一次有多少线程，它限制了一次有多少线程能进入内核，没有办法为所有的架构和负载确定最佳的并发数量，但通常情况下可以这样计算：u 并发=CPU数量磁盘数量2，本系统计算并发数为8。innodb_thread_sleep_delay如果InnoDB内核中已经有了允许数量的线程，那么线程就不能再进入内核了，InnoDB采用了一种两阶段的过程来保证线程可以尽可能高效地进入内核，这种策略减少了操作系统引起的开销。线程首先睡眠innodb_thread_sleep_delay所规定的微秒数，然后再进行尝试，如果还是不能进入，它就会进入一个等待线程的队列中并且把控制权交给操作系统。第一阶段默认的睡眠时间是10000微秒，当有很多线程都处于正在等待进入队列这一状态时，改变这个值有助于提高系统并发，而默认值在有大量小查询的时候会太大了，因为它给查询增加了10毫秒延时。u 保持默认值，当并发使用大查询时才有必要调整该值。innodb_commit_concurrencyInnoDB在提交阶段还有另外一种形式的并发瓶颈，就是刷写操作造成的密集I/O操作。innodb_commit_concurrency变量决定了某一时刻有多少线程能进行提交。当系统有大量线程状况不佳时，可以尝试将该变量增加。u 保持默认值，即不限制并发提交线程数。max_length_for_sort_datamax_sort_lengthMySQL有两种文件排序算法，如果需要进行排序的列的总大小超过了max_length_for_sort_data定义的字节，MySQL就会使用双路排序，反之就会选择单路排序，双路排序需要两次访问数据，尤其是第二次读取操作会导致大量的随机I/O操作。如果将并不需要的Columns也取出来，就会极大地浪费排序过程所需要的内存，为了尽可能地提高排序性能，尽量使用第二种排序算法，所以在查询中仅取出需要的列是非常有必要的。u 对于本系统，默认值足够大，能满足性能要求。Aborted_clients如果Aborted_clients变量随时间增加，那么就要确定是否正常地关闭了连接。如果不是，就要检查网络性能，并且检查max_allowed_packet变量，超多了max_allowed_packet的查询会被强制地中断。Aborted_connectsAborted_connects变量的值应接近于0，否则就可能有网络问题，有几个被中断的连接是正常的。例如，试着从错误的主机连接、使用了错误的用户名和密码，或者定义了无效的数据库，都会发生这样的情况。u 观察本系统10分钟内的Aborted_clients变化，一直保持为0，说明没有连接方面的异常情况，可以定期观察该变量分析连接问题。binlog_cache_sizeBinlog_cache_disk_useBinlog_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的值，只要保证大部分的事务都在二进制日志缓存里就可以了。u binlog_cache_disk_use/Binlog_cache_use比值非常小，说明本系统绝大部份事务都能下入在二进制日志缓存。Created_tmp_disk_tablesCreated_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 100% （理想值 85%）。MySQL服务器实际上允许max_connections+1个客户端进行连接。额外的连接保留给具有SUPER权限的账户。通过为系统管理员而不是普通用户授予SUPER权限（普通用户不应具有该权限），系统管理员能够连接到服务器来诊断问题，即使已连接的无特权客户端数已达到最大值也同样。u 本系统中设置的最大连接数是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，表示操作系统单个进程最大允许打开文件句柄（文件描述符）。u open_files_limit 取三个值中的最大值 ，设置3000较合理，不需要调整。Select_full_join全联接是无索引联接，它真正影响性能，最好能避免全联接，即使是每分钟一次也较多，如果联接没有索引，则最好能优化查询和索引。select_full_range_join如果select_full_range_join的值过高，就说明运行了许多使用了范围查询联接表，有时大的范围查询也会比较慢，可以从中进行优化。Select_range_checkSelect_range_check变量记录了在联接时，对每一行数据重新检查索引的查询计划的数量，它的性能开销很大，如果该值较高或正在增加，则说明一些查询没有找到好索引。u 上述变量目前正常，如果发生明显变化，则结合慢查询日志跟踪全联接性能较差的查询。Slow_launch_threads该变量如果较大则说明某些因素正在延迟联接的新线程，服务器存在一些问题。它通常表示系统过载，导致操作系统不能给新创建的线程分配时间片。Table_locks_waitedTable_locks_waited变量显示了有多少表被锁住了并且导致服务器级的锁等待，InnoDB的行级锁不会使该变量增加。如果该值较高并且正在增加，则说明存在严重的并发瓶颈，这时应该考虑使用InnoDB或另外使用行级锁的存储引擎，或者手动对大表进行分区，并优化查询，启用并发插入或对锁设置进行优化。u 本系统该变量在半小时内变化幅度不超过3，不必调整。2. MySQL优化策略2.1. 索引策略索引是帮助MySQL高效获取数据的数据结构，它对于高性能非常关键，因此建立索引是现实中性能问题的首要原因。索引在数据越大的时候越重要。规模小，负载轻的数据库即使没有索引，也能有好的性能，但是当数据增加的时候，性能就会下降。MySQL有多种类型类型的索引，它们各有自己的性能特点。索引是在存储引擎层实现的，而不是在服务器层，因此它们并不是标准化的，每个引擎的索引工作方式略有不同，并不是所有的引擎都支持所有类型的索引。即使多个引擎支持同样的索引，他们的实现方式也可能有所不同。MySQL索引类型的各自特点可以查阅相关资料去进一步了解。即使已经了解了关于索引的知识，但也许还不知道如何从实际的表开始。虽然通常情况下是检查系统中最常运行的查询，但往往性能瓶颈可能就出现在不那么经常进行的插入或更新操作，要避免在不知道什么查询会使用索引之前就创建它这种常见错误，并且要考虑是否所有的索引能形成一个优化的配置。有时只从查询就可以知道需要什么索引，只要把它们加上就可以了。但是有时各种类型的查询，却不能找