谈谈作为DBA我对MySQL数据库优化的理解

    第一次听到别人问“MySQL怎么优化？”的问题是在一次电话面试里面，当时反应不及，就举了一个工作中给某张表添加索引然后优化了SQL查询性能的栗子。最后面试官说没有get到我的key。然后，当然没有什么然后了。后来，陆陆续续参加了好几个面试，竟然发现不论哪里、哪个公司、哪个岗位、哪个面试官，千篇一律几乎都会问到这个问题。后来想想，虽然这个问题就像你告诉别人“我是个好人”然后别人问你“你哪里好？”一样，是一个又大又空又宽泛，让人一脸懵逼的问题，但是作为一个MySQL DBA，还是应该对这个问题的回答做一个完整的准备。我对自己的定位是初级MySQL DBA，还处于坐井观天的水平，所以以下内容作为工作能力的简单总结，写的时候也以自己的理解和逻辑展开。

1.被动优化

    DBA对数据库的优化工作有很多都处在被动优化阶段。一方面，被动优化所面对的数据库问题是显性的，不可逃避；另一方面，被动优化往往是长期的人工运维工作，没有多少成熟有效的规避办法。
　　①错误日志（配置优化）
　　MySQL会将数据库服务器运行中的错误信息、警告信息以及与之相关的建议信息写入错误日志，这些error、warning和notice记录应该作为优先级比较高的维护工作。因为error log也会记录正确的服务器日志，所以可以写一个简单的脚本提取这些对优化工作有帮助的信息，然后进行优化工作。
　　②慢查询日志（SQL调优）
　　再精心的预优化，也不能避免慢查询语句的产生，只要数据库系统所服务的应用是动态活跃的，总会出现漏网之鱼。因此，定期处理和优化slow log里的SQL是又一项重要的被动优化工作。
　　③数据库版本升级
　　版本升级可能是容易被忽略的一个优化点，但往往却是优化工作中的一步大棋。MySQL的版本更新还是比较快的，一般几个月就会放出一个小版本（第三个版本号）。大版本（第二个版本号）的更新一般会非常大：MySQL5.5开始InnoDB成为默认存储引擎；5.6对InnoDB做了很多优化，比如Online DDL、MRR以及ICP等；5.7就当下关注的数据库安全、国际化以及InnoDB都做了很多的支持和优化工作，并且内置了JSON数据结构。总的来说，最新版的MySQL总是最好的MySQL。在旧版本上的很多优化工作，可能到了新版本就变成多余了。所以，升级版本总是没错的。

2.数据库系统架构优化

　　优良的数据库架构系统应该才是体现DBA能动性的地方，这是人为设计的部分，展现对业务场景中数据存取层的理解和实现。
　　①使用多类型数据库的设计
　　对MySQL数据库优化的一个最大的局限就是把优化停留在MySQL里。一个很简单的哲学道理，组件的合理搭配能让整体实现大于简单加和的结果。实际上关系型MySQL并非万能，至少在一些场景下有其他类型的数据库可以做的更好。例如，MongoDB的文档存储、memcached和Redis的内存缓存。在我之前参与的一个项目中，MongoDB和MySQL作为主数据库（primary database），前者存储原始文档后者存储文档信息的元数据，memcached和Redis作为辅助数据库（auxiliary database）缓存计算结果，这种设计有效地支撑了在线应用。RDBMS和NoSQL在数据库设计中应当是左膀右臂的关系。
　　②根据业务场景选择合适的存储引擎
　　一般情况下，选择MySQL默认的InnoDB存储引擎是没有什么问题的。InnoDB是事物型的存储引擎，适合OLTP应用场景。然而，为了有优化的余地，一些建好后就很少修改的非业务表也可以考虑选用MyISAM存储引擎。例如字典表。OLAP的场景，表主要是作为读表存在，数据的写入往往是整表写或周期性的批量写，这时候完全可以使用MyISAM。例如，报表数据库。另外，日志表和流水表可以考虑选用Archive存储引擎。
　　③分片和读写分离
　　Sharding是分担并发写的压力；读写分离是拆分数据库的职能。无论哪种，都是很好的优化策略。MySQL的复制（replication）是很好的高可用方案设计。

3.数据库服务器优化

　　MySQL服务器优化是DBA岗位运维性质的一个体现，涉及到物理机、操作系统和数据库服务器的方方面面。
　　①物理优化
　　MySQL服务器的性能与CPU、RAM以及I/O是直接相关的。一般在项目上线前都要做整个应用和数据库系统的基准测试。根据阿姆达尔定律，在预估和测试中，去找到整个系统的平衡配置。对于MySQL，最终的工作就是修改相应的内置变量。所以这和“被动优化”部分对错误日志处理的落脚点是一样的，区别在于这里是主动的工作。就个人的多次面试经历来看，当面试官问到数据库优化的时候，很多时候想听的东西，大概就是和MySQL的参数配置相关的。详情可以参考个人之前的一篇学习笔记：http://blog.csdn.net/sweeper_freedoman/article/details/73302130。
　　②表设计中范式与反范式结合
　　所有属性不可再分、非主属性完全依赖于全部主键、非主属性间没有依赖关系，即经典的关系型数据库范式思想。表设计中常常会遇到空间和性能的权衡，精简和冗余的取舍，这就需要深入理解业务场景，做好和产品大爷以及开发大爷的沟通。
　　③表空间优化
　　Analyze和optimize表，频繁改动的线上表可以写一个脚本在凌晨跑一下。