MySQL数据库优化的一些个人思考

题外话：一个系统上线后，运行性能瓶颈的原因是多方面的，要进行系统的优化也是一项复杂的工作，因为你不得不了解整个系统的方方面面，例如你的硬件资源，你的网络情况等。作为开发人员，主要负责应用程序的开发，作为运维人员，主要负责应用程序的部署和监控，有时候可能应用程序没有问题，但部署到线上之后，出现数据库连接超时，发送请求Scoket错误等各种奇奇怪怪的问题，这就需要双方一个良好的沟通，同时双方尽力实现各自的优化工作。

 

实践表明，作为开发人员，对数据库的优化应该有一定的认识，因为它作为信息支撑的基础与核心，往往是系统瓶颈的一个重要方面。

 

数据库也是一个系统，它本身有一些配置参数，这些配置参数在不同的应用环境中应该有不同的配置，即不同的配置会带来不同的性能，对于MySQL数据库服务器来说，非常重要的参数配置位于安装目录下的my.ini文件中，其中两个最重要的参数变量是key_buffer_size和table_cache，这两个值的配置需要根据你机器的硬件资源和数据库表的情况以及你要进行的数据库操作来做决定，另外这个配置文件中还有许多其他的配置，这个都需要根据实际情况来做决定。

 

有时候造成系统瓶颈的也许与你的硬件资源有关，例如磁盘的传输性能，CPU的处理能力，内存的大小等。当然作为生产环境的服务器，拿目前的服务器配置来说，相信这些都已经不是问题了，但我们要知道它是引起性能瓶颈的一个因素。

 

你使用的操作系统对MySQL发挥最好的性能也有很重要的影响，一般在互联网应用中，我们常采用的Linux系统，但要注意最好用Linux内核是2.4版本之后的版本，因为之前的linux版本支持的文件大小最大值为2G，如果数据库文件大于2G，就有问题了。

 

MySQL服务器自己有一个查询优化器，它的职责就是搜索一个最佳的SQL查询方案，一般的单表并且数据字段也比较少的查询引起的查询方案往往比较少，但复杂的sql查询则会引起多种查询方案，MySQL查询优化器就需要从多个方案中评估最优的方案，这需要花费一定的时间，一般地，优化器搜索的方案越少，它编译一个查询所花费的时间越少。但另一方面，因为优化器跳过了一些方案，它可能错过一个最佳方案，所以有时也需要根据情况来合理地折中。这种关于MySQL优化器的工作，对于开发人员来说，我觉得暂时可以不必深入了解，而且随着MySQL服务器的不断升级和改进，它的优化会越来越好。

 

其实对于广大开发人员来说，直接接触的是对SQL语句的优化，对于SQL的优化，关心最多的是查询的优化，其基本原则是：优化数据的访问。查询性能低下的基本原因是访问了太多的数据，常常可以通过减少数据访问的方式来提高性能，虽然有时不可避免要查询很大的数据，但这种情况在实际的应用程序中并不常见，因此：
1、确定你的应用程序是否访问了超过需要的列，例如你select * from tblName，不需要的列不要查询（没人不知道，但我看到程序中却有这么写的）。
2、确定你是否访问了不需要的数据，如果你先向服务器请求不需要的数据，然后再在应用程序中丢弃，这会造成服务器额外的负担，增加了网络开销和CPU内存资源，当应用程序和数据库服务器不在同一台主机上时，网络开销会更加大（没人不知道，但我看到程序中却有这么写的）。

 

当你获取的是自己需要的行数和列数，这时就要开始考虑你的查询是否检查了过多的数据行，衡量查询的非常简单的指标就是：
1.执行的时间；
2.检查的行数；

执行的时间：往往是因为你检查了太多的行数引起的，但也很可能与你的硬件环境等有一定的关系，我们不必过分强调这一个，但你一定要注意那些非常影响执行时间的因素。

 

检查的行数：理想的情况是返回的行和检查的行一样多，但在实际应用中几乎不可能，例如很多多表连接查询，不可能返回的行和检查的行一样多。通常情况下，检查的行和返回的行的比率：1:1 到 10:1之间是很正常的。

 

我们的每个查询都有几种访问方式来返回需要的数据行，
1.全表扫描
2.索引扫描
3.范围扫描
4.唯一索引扫描
5.常量扫描
这几种数据访问方式的访问速度依次递增。

在MySQL中写SQL语句时，请经常使用EXPLAIN语句来分析查询情况，特别是操作比较频繁的查询语句，非常建议使用EXPLAIN语句来分析一下。
例如： EXPLAIN  SELECT  *  FROM  USER；
执行这样的语句之后，MySQL服务器会返回一个详尽的执行描述，根据描述，我们可以知道这条语句的执行效率，例如如果得到的访问类型type：all，这表示全表扫描，那你就最好添加一个索引来避免全表扫描。有时候你添加了索引之后还得不到一个好的访问类型，你就要考虑是不是数据库表设计得不太合理了。

 

有时候优化可能需要你重构你的查询语句，将你的复杂查询拆分成几个简单的查询语句，按照传统的观念，大家都是尽可能少的去数据库查询，但目前的网络通信情况明显比以前好很多，而且真正的应用程序和数据库服务器一般都是部署在内网专用通道上，网络连接已经不是什么问题了，所以别害怕多个查询，MySQL对简单的查询响应非常好。但你也要知道，连接数据库服务器比执行查询要慢很多，所以你要根据情况来作出合理的折中，看是否必要拆分复杂查询。

 

网络上有很多关于SQL语句的优化原则，我发现有些优化方式对Oracle适用，但对MySQL也许并不适用，这可能是由于MySQL优化器优化规则的不同而引起的。

 

开发人员要了解更多的SQL优化知识，数据库DBA或运维人员要了解更多的数据库服务器优化的知识，这样两力合二为一才是开发出一个高性能的应用系统的基础和保障。

 

而这一切都是在不断实践中成长。

 

 一个Java技术交流群，一起交流，共同进步，扣扣群号：513086638