论文部分内容阅读
[摘 要]数据库性能的高低直接影响应用程序的性能，因此数据库优化是一项至关重要的工作。本文从数据库设计、SQL优化和硬件资源三个方面探讨了数据库的优化，提高了数据库的执行效率。
[关键词]数据库优化、数据库性能、SQL优化
中图分类号：TG333.7 文献标识码：A 文章编号：1009-914X（2015）41-0253-01
随着信息技术不断发展，信息化应用领域越来越广泛，应用系统越来越多，而大部分的应用数据都要依托数据库系统来管理，优化数据库的性能变得尤为重要。一般来说，要保证数据库的效率，需要做好数据库设计、sql语句优化和硬件资源三个方面的工作。
一、数据库设计
1、合理设计冗余
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。例如得分情况表和学生考试总分表的结构如下：
得分情况表（得分标识，考试安排标识，试卷标识，人员标识，扣分，实得分数，答案）
学生考试总分表（总分标识号，人员标识，考试安排标识，成绩标识号，考试总分，是否缺考）
从以上两表可以看出学生考试总分表中的考试总分字段是冗余的，这个分数完全可以通过得分情况表汇总得到。在学生考试总分表中，一个学生一次考试只有一条记录，而在得分情况表中，一个学生针对试卷里每一小题都要生成一条记录，一份试卷就有上百条记录。如果总分通过得分情况表汇总得到，查询速度就会比用冗余实现慢很多，由于判卷子得分是不会轻易变的，更新的频率不高，所以设计这样的冗余是比较好的。
2、合理设计索引
索引是提高数据库性能最物美价廉的手段了，不用增加硬件投入，也不用修改程序，只要选择设计正确的索引，数据查询速度就可能提高上百倍。在设计阶段，我们主要依据功能和性能的需求和预计的数据量以及查询数据来设计索引。选择索引应注意以下几条准则：
（1）根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
（2）根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
（3）把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
（4）一个表不要加太多索引，因为索引影响插入和更新的速度。
3、水平分割数据表
对于一个拥有上百万条记录以上的表，并且经常被查询，如果此表的数据在逻辑上可以划分时，我们就可以对数据表进行水平分割以提高查询速度。一个较好的划分依据，对应用程序的简单实现有很大帮助，也能充分发挥水平分表的优势。例如应用程序只要求按月查询数据的功能，那么就可以把表按月拆分成12个，每次查询只须查询一个分表就行了。如果非要按照其它字段来分，即使将表拆分的再小，查询时还是要联合所有表，这样还不如不拆的效果好。故好的拆分依据是最非常重要的。例如：学生做题本表（学生错题本号，班级号，试题号，人员号，科目，题目来源，答题时间，对错），每位学生做过的题都保存在这张表中，每道题都对应一个或多个知识点，依据学生做错的题来分析在哪些知识点上比较薄弱。这张表在应用一段时间后，很容易达到千万条数据记录，急需拆分，那么依据什么来拆分呢？从需求上看，不管老师还是学生，最终都会把焦点落到一个学生的身上。学生会关心自己，老师会关心自己班的学生，而且每个学科的知识点是不同的，所以我们可以较容易地想到，联合科目和知识点两个字段来拆分这个表，拆分后每个表大概2万条数据，查询效率就非常高了。
4、合理设计表的字段
数据库最基本的单位就是字段，字段的设计好坏将对性能产生很大的影响。在设计时应注意如下几点：
（1）尽可能用数字型的数据类型，数字型的比较比字符型的要快很多。
（2）选择字段的数据类型尽量小，能用占用字节小的字段就不用大字段。
（3）尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
（4）尽量少用IMAGE和TEXT，读写二进制字段会比较慢。（例如在为时间选择数据类型时，尽量使用timestamp，因为datetime占用8个字节，timestamp只占用4个字节，所占空间只有datetime类型的一半，查询起来会比较快些。
二、SQL优化
大多数的数据库都或多或少遇到一些性能问题。特别是随着业务的发展、数据量的增加、系统用户数的增多、以及系统之间越来越复杂的接口，都会导致系统的性能越来越恶化。较差性能的SQL语句是导致数据库性能问题的主要原因之一，所以优化SQL语句可以明显改善数据库的性能问题。下面按着SQL优化的一般步骤来探讨SQL语句的优化。
1、使用show status了解各种SQL的执行频率
使用SHOW STATUS可以获取服务器状态信息，可以根据需要显示session级别的统计结果和global级别的统计结果。对Myisam和Innodb存储引擎，Com_select参数用于统计select操作的次数，Com_insert 用于统计insert操作的次数，Com_update 用于统计update操作的次数；Com_delete用于统计delete操作的次數。通过这些参数可以很容易地了解当前数据库的应用是以查询操作为主还是以插入更新为主，以及各种SQL的执行比例的情况。对于事务型的应用，使用Com_commit和Com_rollback参数了解事务提交和回滚的情况，如果数据库回滚操作非常频繁，说明SQL编写存在问题。
2、找出执行效率低的SQL语句
可以通过启动--log-slow-queries[=file_name]选项，写一个包含所有执行时间超过long_query_time的SQL语句的日志文件。然后通过慢查询日志找出执行效率较低的sql语句。还可以用show processlist命令查看当前MySQL在进行的线程，可以实时的了解SQL执行情况。
3、使用EXPLAIN分析较低效率SQL的执行计划
通过以上步骤找到低效率的SQL语句后，使用explain可以得到MySQL是如何执行SELECT语句的信息。explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
4、确定问题，并采取相应的优化措施
经过上面的步骤后，我们基本可以确认问题出现的原因，采取相应的措施进行优化，以提高SQL执行的效率。从上面的例子可以确认导致效率不理想的原因是对a表的全表扫描，因此我们可以通过对a表的year字段创建索引，查询需要扫描的行数就明显减少，所以查询效率得到了提高。
三、硬件资源升级
当我们无法再对数据库进行优化时，数据库还是运行慢的话，这时我们就要考虑通过升级服务器的硬件资源，如扩大内存容量、增加CPU个数，或者将应用与数据进行分开部署在不同的服务器上。如果数据库运行压力还是很大，一台机器支撑不了，那么可以如下图所示，通过增加服务器，将数据库的压力分散，从而解决数据库的性能问题。
四、小结
数据库优化是一个很广的范围，涉及到的东西比较多，并且每个特定的数据库，其具体的优化过程也是不一样的，因此要想真正地做好数据库的优化，需要不断地参与数据库优化实践中积累经验。
[关键词]数据库优化、数据库性能、SQL优化
中图分类号：TG333.7 文献标识码：A 文章编号：1009-914X（2015）41-0253-01
随着信息技术不断发展，信息化应用领域越来越广泛，应用系统越来越多，而大部分的应用数据都要依托数据库系统来管理，优化数据库的性能变得尤为重要。一般来说，要保证数据库的效率，需要做好数据库设计、sql语句优化和硬件资源三个方面的工作。
一、数据库设计
1、合理设计冗余
完全按照规范化设计的系统几乎是不可能的，除非系统特别的小，在规范化设计后，有计划地加入冗余是必要的。冗余可以是冗余数据库、冗余表或者冗余字段，不同粒度的冗余可以起到不同的作用。冗余可以是为了编程方便而增加，也可以是为了性能的提高而增加。从性能角度来说，冗余数据库可以分散数据库压力，冗余表可以分散数据量大的表的并发压力，也可以加快特殊查询的速度，冗余字段可以有效减少数据库表的连接，提高效率。例如得分情况表和学生考试总分表的结构如下：
得分情况表（得分标识，考试安排标识，试卷标识，人员标识，扣分，实得分数，答案）
学生考试总分表（总分标识号，人员标识，考试安排标识，成绩标识号，考试总分，是否缺考）
从以上两表可以看出学生考试总分表中的考试总分字段是冗余的，这个分数完全可以通过得分情况表汇总得到。在学生考试总分表中，一个学生一次考试只有一条记录，而在得分情况表中，一个学生针对试卷里每一小题都要生成一条记录，一份试卷就有上百条记录。如果总分通过得分情况表汇总得到，查询速度就会比用冗余实现慢很多，由于判卷子得分是不会轻易变的，更新的频率不高，所以设计这样的冗余是比较好的。
2、合理设计索引
索引是提高数据库性能最物美价廉的手段了，不用增加硬件投入，也不用修改程序，只要选择设计正确的索引，数据查询速度就可能提高上百倍。在设计阶段，我们主要依据功能和性能的需求和预计的数据量以及查询数据来设计索引。选择索引应注意以下几条准则：
（1）根据数据量决定哪些表需要增加索引，数据量小的可以只有主键。
（2）根据使用频率决定哪些字段需要建立索引，选择经常作为连接条件、筛选条件、聚合查询、排序的字段作为索引的候选字段。
（3）把经常一起出现的字段组合在一起，组成组合索引，组合索引的字段顺序与主键一样，也需要把最常用的字段放在前面，把重复率低的字段放在前面。
（4）一个表不要加太多索引，因为索引影响插入和更新的速度。
3、水平分割数据表
对于一个拥有上百万条记录以上的表，并且经常被查询，如果此表的数据在逻辑上可以划分时，我们就可以对数据表进行水平分割以提高查询速度。一个较好的划分依据，对应用程序的简单实现有很大帮助，也能充分发挥水平分表的优势。例如应用程序只要求按月查询数据的功能，那么就可以把表按月拆分成12个，每次查询只须查询一个分表就行了。如果非要按照其它字段来分，即使将表拆分的再小，查询时还是要联合所有表，这样还不如不拆的效果好。故好的拆分依据是最非常重要的。例如：学生做题本表（学生错题本号，班级号，试题号，人员号，科目，题目来源，答题时间，对错），每位学生做过的题都保存在这张表中，每道题都对应一个或多个知识点，依据学生做错的题来分析在哪些知识点上比较薄弱。这张表在应用一段时间后，很容易达到千万条数据记录，急需拆分，那么依据什么来拆分呢？从需求上看，不管老师还是学生，最终都会把焦点落到一个学生的身上。学生会关心自己，老师会关心自己班的学生，而且每个学科的知识点是不同的，所以我们可以较容易地想到，联合科目和知识点两个字段来拆分这个表，拆分后每个表大概2万条数据，查询效率就非常高了。
4、合理设计表的字段
数据库最基本的单位就是字段，字段的设计好坏将对性能产生很大的影响。在设计时应注意如下几点：
（1）尽可能用数字型的数据类型，数字型的比较比字符型的要快很多。
（2）选择字段的数据类型尽量小，能用占用字节小的字段就不用大字段。
（3）尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT代替。
（4）尽量少用IMAGE和TEXT，读写二进制字段会比较慢。（例如在为时间选择数据类型时，尽量使用timestamp，因为datetime占用8个字节，timestamp只占用4个字节，所占空间只有datetime类型的一半，查询起来会比较快些。
二、SQL优化
大多数的数据库都或多或少遇到一些性能问题。特别是随着业务的发展、数据量的增加、系统用户数的增多、以及系统之间越来越复杂的接口，都会导致系统的性能越来越恶化。较差性能的SQL语句是导致数据库性能问题的主要原因之一，所以优化SQL语句可以明显改善数据库的性能问题。下面按着SQL优化的一般步骤来探讨SQL语句的优化。
1、使用show status了解各种SQL的执行频率
使用SHOW STATUS可以获取服务器状态信息，可以根据需要显示session级别的统计结果和global级别的统计结果。对Myisam和Innodb存储引擎，Com_select参数用于统计select操作的次数，Com_insert 用于统计insert操作的次数，Com_update 用于统计update操作的次数；Com_delete用于统计delete操作的次數。通过这些参数可以很容易地了解当前数据库的应用是以查询操作为主还是以插入更新为主，以及各种SQL的执行比例的情况。对于事务型的应用，使用Com_commit和Com_rollback参数了解事务提交和回滚的情况，如果数据库回滚操作非常频繁，说明SQL编写存在问题。
2、找出执行效率低的SQL语句
可以通过启动--log-slow-queries[=file_name]选项，写一个包含所有执行时间超过long_query_time的SQL语句的日志文件。然后通过慢查询日志找出执行效率较低的sql语句。还可以用show processlist命令查看当前MySQL在进行的线程，可以实时的了解SQL执行情况。
3、使用EXPLAIN分析较低效率SQL的执行计划
通过以上步骤找到低效率的SQL语句后，使用explain可以得到MySQL是如何执行SELECT语句的信息。explain可以知道什么时候必须为表加入索引以得到一个使用索引来寻找记录的更快的SELECT。
4、确定问题，并采取相应的优化措施
经过上面的步骤后，我们基本可以确认问题出现的原因，采取相应的措施进行优化，以提高SQL执行的效率。从上面的例子可以确认导致效率不理想的原因是对a表的全表扫描，因此我们可以通过对a表的year字段创建索引，查询需要扫描的行数就明显减少，所以查询效率得到了提高。
三、硬件资源升级
当我们无法再对数据库进行优化时，数据库还是运行慢的话，这时我们就要考虑通过升级服务器的硬件资源，如扩大内存容量、增加CPU个数，或者将应用与数据进行分开部署在不同的服务器上。如果数据库运行压力还是很大，一台机器支撑不了，那么可以如下图所示，通过增加服务器，将数据库的压力分散，从而解决数据库的性能问题。
四、小结
数据库优化是一个很广的范围，涉及到的东西比较多，并且每个特定的数据库，其具体的优化过程也是不一样的，因此要想真正地做好数据库的优化，需要不断地参与数据库优化实践中积累经验。