MySQL（十六）------ 优化数据库对象

        在数据库设计时，通常会有很多问题需要思考，比如是否需要把所有的表按第三范式来设计？表中各字段设计为多大的长度合适？等等这些问题都是需要考虑的；下面介绍的就是通过一些方法来分析，而后进行指导数据库的优化。

一、优化表的数据类型及长度

       表字段的类型需要根据应用来判断，字段的长度要留有一定的冗余，但不能有大量冗余，具体怎么定可以通过MySQL的函数 procrdure analyse() 对当前应用的表进行分析，分析显示的结果会对数据类型及长度提出优化建议，然后根据实际情况决定是否使用该种优化。

select * from tbl_name PROCEDURE ANALYSE();  对表的每一列进行优化分析
select * from tbl_name PROCEDURE ANALYSE(16，256);    不要为那些包含的值多余16个或者256个字节的ENUM类型提出建议（因为数据太长输出的信息很长难以阅读）

         具体的实例我这里就偷个懒暂时不贴出来，大概过程我简单说一下：比如你现在有一张表有很多的不同类型的字段，各字段的长度也不相同，当使用PROCEDURE ANALYSE() 进行分析后，对于每一个列字段都会给出一个分析结果，你可以根据结果中对于该列的最大值、最小值、最大长度、最小长度、平均长度、优化建议等信息综合判断出一个大概的结果，比如字段的最大长度也才2，那你的字段长度设置在3比较合理；当然也还要根据实际情况，比如该字段存在突增的情况但目前还没有显示，此时你就要留意这样问题。确定好优化方式后，通过修改表结构对字段类型进行修改即可。

二、通过表的拆分提高表的访问效率

       对于MyISAM类型的表有两种拆分方法：

1. 垂直拆分

      把主码和一些列放到一个表，然后把主码和另外的列放到另一个表中。如果表中某些列常用而另一些列不常用则可以采用垂直拆分；这种方法使得数据行变小，一个数据页就能存放更多的数据，在查询时会较少IO的次数；缺点就是需要管理冗余列，当要查询所有数据时需要联合（JOIN）操作。

2. 水平拆分

      根据一列或多列数据的值把数据行放到两个独立的表中。水平拆分通常在以下几种情况下使用：

• 表很大，分割后可以降低在查询时需要读取的数据和索引的页数，同时也降低了索引的层数；
• 表中的数据本来就有独立性，例如各个地区的数据，不同时期的数据；特别是有些数据常用而另外一些不常用；
• 需要把数据存放到多个介质上；

       水平拆分会给应用增加复杂度，因为在查询时需要多个表名，而且查询所有的数据需要UNION操作。有时候这种分表带来的复杂性会超过它带来的优点；因此，水平拆分要考虑数据量的增长速度，再根据实际情况决定是否拆分。

三、逆规范化

        数据库设计时需要满足规范化这个都清楚，但是规范化带来的问题各种关系增多，导致表之间的连接操作频繁，而这种连接操作是性能较低的操作，因此，有时候使用逆规范化的方法往往有奇效。

       在使用反规范化操作之前一定要权衡利弊，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算（例如合计）、数据的物理存储位置等。常用的反规范技术有以下几种：

• 增加冗余列：在多个表中具有相同的列，它常用来在查询时避免连接操作；
• 增加派生列：增加的列来自其它表中的数据，有其它表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数；
• 重新组表：如果许多用户需要查看两个表连接出来的结果数据，则把这两个表组成一个表来减少连接提高性能；
• 分割表：查看上面的介绍；

    当然，使用逆规范化的技术需要保持数据的完整性，因此需要一定的管理来实现，常用的方法有批处理维护，应用逻辑和触发器。

• 批处理维护是指对复制列或者派生列的修改积累一定的时间后，运行一批处理作业或储存过程对复制或派生列进行修改，这只能在实时性要求不高的情况下使用；
• 数据的完整性也可由相应的逻辑实现，这就要求必须在同一事务中对所有涉及的表进行增、删、改操作。用应用逻辑来实现数据的完整性风险较大，因为同一逻辑必须在所有的应用上使用和维护，容易遗漏，特别是在需求变化时不易于维护；
• 使用触发器，对数据的任何修改立即触发对复制列或派生列的相应修改；触发器是实时的，而且相应的处理逻辑只在一个地方实现，易于维护；一般来说触发器是解决这类问题最好的办法。

四、使用中间表提高统计查询速度

       对于数据量很大的表在上面进行统计查询等操作时往往效率很低，并且还要考虑这些操作是否会对在线应用产生负面影响；因此，往往这种时候会使用中间表来独立完成这些操作会好很多。

       这里举个例子，比如某个公司积累了大量用户，每时每刻这些用户的消费记录都被记在公司的消费记录表上，现在有个需求，需要统计一下上周用户的消费总额以及各不同时间段的消费总额。

       遇到这样的问题如果不使用中间表的话需要对原表进行条件筛选查询并进行统计，可想而知这么大体量的数据表这种操作很费时间，而且最重要的一点就是由于是直接对生产表进行操作，因此一旦出现一点错误将直接影响到线上生产，产生的后果将非常大；故而在这种情况下为了安全、为了效率往往使用中间表来完成（这就像平时我们修改东西的时候会建一个副本一样）。可以创建一个表结构与原表一模一样的中间表，然后近一周的数据插入到该表中，随后对该临时表进行统计查询。

        中间表在统计查询时经常会用到，其优点如下：

• 中间表复制源表的部分数据，与源表相互隔离，对中间表进行的相关操作不会影响到线上生产；
• 中间表上可以灵活的添加索引或增加辅助字段，从而达到提高统计查询效率和辅助查询的作用；

本文地址：https://blog.csdn.net/Coding___Man/article/details/85784549