《mysql性能优化解决方案》由会员分享，可在线阅读，更多相关《mysql性能优化解决方案（40页珍藏版）》请在金锄头文库上搜索。
1、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.
2、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 支持数据压缩存储，节约存储，提高内存命
3、中率，并且支持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将首次支持半
4、同步(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速度限制了响应时间。为了实现低延迟，即快速响应时间
5、，需要快速的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张，MEMOR
6、Y表2张。InnoDB数据文件ibdata1大小为30138MB，一周后ibdata1大小为30234MB， MyISAM数据文件（包括表结构、索引及数据）总大小约为1642MB，一周后约为1639MB。可以看出，数据库的数据量较稳定，InnoDB数据文件增加了约106MB，总大小一周内没有大的变化。MyISAM表中，值得注意的是表terminalalarm_bak，该表总大小约为1623MB，占整个MyISAM表总大小比重近99%。二进制日志单个文件大小为1GB，二进制日志文件总大小接近20GB。1.7. 数据分布情况服务器某时间点非精确值：数据量范围表数量（总共339张，其中分区表2张）1
7、000万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类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此
8、可以在该列上以自然月份进行表分区。l terminalalarm的数据量也突破千万，AlarmTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。在事件ev_terminalalarm中会查询该表，若进行表分区，也能一定程度上提高事件的执行效率。l terminalalarminfo表仅自增列有索引，主要用于存储数据，可不用分区。l Terminallogin表的loginTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。l adplay
9、info_bak表存在多个以INT类型为索引的列，根据实际业务情况选择查询频率高且能以范围值来分区的整型列对该表进行分区。l adrotateresultdetail的createTime列是datatime类型，且有索引，意味着存在以该列为查询条件或关联条件查询的需求，因此可以在该列上以自然月份进行表分区。l up表仅自增列有索引，若存在查询或者统计业务则可以createTime列进行分区，若该表没有查询方面业务可不必进行分区。除去配置参数等属性表，对于数据量大且不断递增的业务数据表，最直接的办法可以按照时间字段进行分区，或是根据查询业务来选择合适的列进行表分区和创建索引，这样能够有效提高存
10、储和查询效率。1.8. 服务器配置参数记录查询：普通日志log、慢速日志log_slow_queriesMySQL有两种查询日志：普通日志和慢速日志，它们都会记录查询。普通日志记录了服务器接收到的每一个查询，也包含了没有被执行的查询，比如因为错误而未被执行的查询，还有一些非查询事件，比如连接和断开连接，普通日志不包含执行时间或其他只有在查询结束之后才能得到的信息。相反，慢速日志只包含了已经执行过的查询，如果是启动状态，它记录了执行时间超过了特定长度的查询。两种日志都有助于分析，但是慢速日志更有利找到性能较慢的查询。一个相关配置是log_queries_not_using_indexes，它使服
11、务器把没有使用索引的查询记录到慢速查询日志中，无论它们执行速度有多快。尽管打开慢速日志相对于执行慢速查询来说，通常只增加了很少的时间，但是如果没有使用索引的查询非常快，例如从小数据量表中查询，这样就会记录它们可能导致服务器变慢，甚至还会使用大量的磁盘空间，慢速日志也许就会被那些快速高效的查询塞满。慢查询日志可以用来找到执行时间长的查询，可以用于优化。慢日志打开后，通过设置long_query_time来配置记录查询超过的指定时间，默认值为10秒，根据系统的负载和性能要求进行设置(SET GLOBAL long_query_time = )。检查又长又慢的查询日志会很麻烦，可以使用MySQLdu
12、mpslow命令获得日志中显示的查询摘要来处理慢查询日志。系统两种日志都没有开启，可以在需要的时候打开慢速日志来帮助分析性能较慢的查询。具体实施参考MySQL手册。需要注意的是查询在日志中只出现一次并不意味着它是一个不好的查询，也不意味将来也会慢，查询时快是慢有多种原因：1） 表也许被锁定，导致查询处于等待状态；2） 数据或索引也许没有被缓存在内存中；3） 或者正在进行批处理大量的数据，使得磁盘I/O变慢；4） 服务器可能同时在运行其他的查询，影响了当前查询的效率。因此，只能把慢速查询日志看成调优工作的一部分，可以用它来找到可疑的查询，但需要对它们进行仔细地排查和分析。u 启用系统慢速日志，分
13、析查询性能慢的时候可以观察该日志信息。Qcache_hitsCom_selectQcache_inserts检查是否从查询缓存中受益的最直接办法就是检查缓存命中率。它是提供缓存提供的查询结果的数量，而不是服务器执行的数量。当服务器收到select语句的时候，Qcache_hits和Com_select这两个变量会根据查询缓存的情况进行递增。查询缓存命中率的计算公式：Qcache_hits/(Qcache_hits+Com_select)，根据公式计算得出查询缓存命中率为7%。初看上去该命中率很低，但注意到com_select等于qcache_inserts + qcache_not_cache
14、 + 权限检查错误的总和，即这个比率中包含了缓存失效的因素，而对于数据变更频繁的系统来说，缓存是及其容易失效的，表的任何时刻的数据插入或更新都会使该表的缓存失效，所以本系统缓存的插入率很低，抛开失效的缓存因素，用如下公式计算缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)= 84.87%，该比值要好得多，意味着大部分的查询都命中了缓存，换一种说法就是仍有一小部分查询没有被缓存。没被缓存和缓存失效是两个概念，分别计数，但都会引起com_select的值增加。命中率要多少才好，这视情况而定，因为对于每一个查询，不执行它所节约的资源远大于缓存中保存结果以及
15、让查询失效的开销，如果缓存命中代表了开销最大的查询，那么即使很低的命中率也是有好处的。缓存可能会因为碎片、内存不足或数据改变而失效。如果已经给缓存分配了足够的内存，并且把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
