MySql优化3——服务器与配置优化

mysql中常见的引擎

• MyISAM 注重性能
• Memory 内存存储，数据不可持久
• InnoDB
  
  • 对事务支持比较好；
  • 它采用行级锁，在性能上低于MyISAM的表级锁；
  • 它可以缓存数据和索引，高并发下比较占优；
  • 对内存消耗比较大，但在这个时代内存比较廉价。

存储引擎的选择方法

• 在数据库中执行以下命令可以查看当前数据库的状态 show global status;
• 查看读写比例
  
  • 理想的读写比例是100:1
  • 当比例达到10：1证明是以写为主
  • 一般来说应该是30：1

选择存储引擎的基本原则

• MyISAM
  
  • R/W > 100:1 且update较少
  • 并发不高，不需要事务。
  • 表数据量小
  • 硬件资源有限
• InnoDB
  
  • R/W比较小，频繁更新大字段
  • 表数据超过1000万，并发高
  • 安全性和可用性要求高
• Memory
  
  • 有足够的内存
  • 对数据一致性要求不高，如在线人数。
  • 需要定期归档的数据。

mysql服务器调整优化措施

• 关闭不必要的二进制日志和慢查询日志，公在内存足够时或开发调试时才打开。
  
  • show variables like ‘%slow%’;
• 查看慢查询的条数
  
  • show global status like ‘%slow%’;
• 增加mysql允许的最大连接数
  
  • show global status like ‘max_connections’; // 查看最大连接数
• 对于MyISAM表增加key_buffer_size，但是需要根据key_cache的命中率进行计算。
  
  • show global status like ‘key_read%’;
  • rate = key_reads/key_read_requests
  • 当rate大于1%时就应该增加key_buf_size了。
  • 对于MyISAM表，如果table_cache不够用时会用LRU算法踢掉最长时间没有使用的表，如果cache设置过小就会反复打开关闭FRM文件，造成一定的性能损失；如果cache设置过大，mysql就会消耗很多CPU资源去处理table_cahe算法，因此table_cache值设置一定要合理，可以参考opened_tables参数的值，如果这个值一直增涨，就需要适当增加table_cache的值。
  • 而对于InnoDB，应该注意innodb_buffer_pool_size参数。
• 从表中删除大量行后，可运行optimize table name进行碎片整理。

mysql瓶颈及应对措施

• 为什么进行优化？
  
  • mysql单表数据量达到千万级的时候，无论如何优化，查询如何简单，性能都会有显著的降低。
• 硬件优化：增加配置中buffer和Cache的数值，增加服务器中CPU和内存的大小。
• 使用第三方引擎和衍生版本
  
  • Percona 在功能和性能上较mysql有显著的提升
  • MariaDB在InnoDB引擎上的性能也比MySql优秀。
  • 据官网介绍，TokuDB性能是mysql的10倍以上。
  • 以上的这些都是针对InnoDB进行优化的，InnoDB为了持久化数据，每次都需要fsync到磁盘，比较耗费性能，直到MariaDB才复用group commit解决这个问题。
• 迁移到其它数据库
  
  • mysql优点：轻巧快速，系统资源消耗少，支持更多并发连接，进程模式能更充分的利用系统资源，在应对复杂复杂业务查询上更有优势。
  • Oracle 单表性能瓶颈经验值在2亿数据量级别，mysql只是千万级别；在关联查询和内置函数等功能上，Oracle完胜mysql。
  • PostgreSQL数据库相比mysql，拥有更强大的查询优化器，不会频繁重建索引，支持物化视图等优势。
• 对数据库进行分区分表操作，减少单表体积。
• 使用NoSql等辅助解决方案，如Memcached、Redis
• 使用中间件做数据拆分和分布式部署，这方面的典型案例有阿里巴巴开源数据库中间件Cobar.
• 使用数据库连接池：让连接进行排除和复用，一定程度上可以缓解高并发下的压力。

总结

mysql的瓶颈是真实存在的，但是不少大型互联网公司仍在使用，并且能使用得很好，一方面是因为公司技术实力足以对mysql进行二次开发；另一方面则得益于其成熟的架构，所以一个工作能否好用，人的因素占很大的比重。==

推荐：

MySql优化1——SQL语句优化
MySql优化2——索引与性能分析