优化数据库对象

最新推荐文章于 2022-10-21 11:23:12 发布

迟日暖风

最新推荐文章于 2022-10-21 11:23:12 发布

阅读量184

点赞数

分类专栏： Java随笔文章标签：数据库 mysql

本文链接：https://blog.csdn.net/qq_50379749/article/details/126243427

版权

Java随笔专栏收录该内容

20 篇文章 0 订阅

订阅专栏

优化数据库对象

文章目录

- - 优化数据库对象

sql提供： http://downloads.mysql.com/docs/sakila-db.zip

原文：《深入浅出MySQL++数据库开发、优化与管理维护》

压缩文件中3个文件：sakila.schema.sql是表结构创建，sakila-data是表数据，sakila.mwb是数据模型

在数据库设计过程中，用户可能会经常遇到这种问题:是否应该把所有表都按照第三范式来设计?表里面的字段到底该设置为多大长度合适?这些问题虽然很小，但是如果设计不当则可能会给将来的应用带来很多的性能问题。本文将介绍MySQL中些数据库对象的优化方法，
其中一些方法不仅仅适用于MySQL,也适用于其他类型的数据库管理系统。

1.优化表的数据类型

表需要使用何种数据类型是需要根据应用来判断的。虽然应用设计的时候需要考虑字段的长度留有一定的冗余，但是不推荐让很多字段都留有大量的冗余，这样既浪费磁盘存储空间,同时在应用程序操作时也浪费物理内存。

在MySQL中，可以使用函数PROCEDURE ANALYSE0对当前应用的表进行分析,该函数可以对数据表中列的数据类型提出优化建议,用户可以根据应用的实际情况酌情考虑是否实施优化。
以下是函数PROCEDURE ANALYSEO的使用方法:

SELECT * FROM tbl_name PROCEDURE ANALYSE();
SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

输出的每一列信息都会对数据表中的列的数据类型提出优化建议。以上第二个语句告诉PROCEDURE ANALYSE0不要为那些包含的值多于16个或者256个字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长; ENUM定义通常很难阅读。

根据PROCEDURE ANALYSE(函数的输出信息，用户可能会发现，一些表中的字段可以修改为效率更高的数据类型。如果决定改变某个字段的类型,则需要使用ALTER TABLE语句。
下面分析一下表actor 的数据类型是否需要优化。

mysql> SELECT * FROM actor PROCEDURE ANALYSE(16,256)\G
*************************** 1. row ***************************
             Field_name: sakila.actor.actor_id
              Min_value: 1
              Max_value: 1000
             Min_length: 1
             Max_length: 4
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 104.9751
                    Std: 85.5688
      Optimal_fieldtype: SMALLINT(4) UNSIGNED NOT NULL
*************************** 2. row ***************************
             Field_name: sakila.actor.first_name
              Min_value: ADAM
              Max_value: ZERO
             Min_length: 2
             Max_length: 11
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 5.3184
                    Std: NULL
      Optimal_fieldtype: VARCHAR(11) NOT NULL

可以从结果上看到插入数据的最大和最小长度，以及其他参数作为优化的参考，以及下面也给出了Optimal_fieldtype，即优化建议。

注意：如果字段表记录太少，字段唯一值太少，函数可能会觉得使用枚举类型更合理，但是如果是对大表进行分析，提出的建议会更准确。

2.通过拆分提高表的访问效率

这里所说的“拆分”，是指对数据表进行拆分。如果针对MyISAM类型的表进行，那么有两种拆分方法。
(1) 第一种方法是垂直拆分，即把主码和一些列放到-一个表，然后把主码和另外的列放到另一个表中。
如果一个表中某些列常用，而另一些列不常用，则可以采用垂直拆分，另外，垂直拆分可以使得数据行变小，- -个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要联合( JOIN)操作。

(2) 第二种方法是水平拆分，即根据一列或多列数据的值把数据行放到两个独立的表中。

水平拆分通常在以下几种情况下使用。

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

例如，移动电话的账单表就可以分成两个表或多个表。最近3个月的账单数据存在一个表中，3个月前的历史账单存放在另外一个表中，超过1年的历史账单可以存储到单独的存储介质上，这种拆分是最常使用的水平拆分方法。

水平拆分会给应用增加复杂度，它通常在查询时需要多个表名,查询所有数据需要UNION操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只要索引关键字不大，则在索引用于查询时，表中增加2~3倍数据量，查询时也就增加读-一个索引层的磁盘次数，所
以水平拆分要考虑数据量的增长速度，根据实际情况决定是否需要对表进行水平拆分。

3.逆规范化

数据库设计时要满足规范化这个道理大家都非常清楚,但是否数据的规范化程度越高越好呢?这还是由实际需求来决定。因为规范化越高，那么产生的关系就越多，关系过多的直接结果就是导致表之间的连接操作越频繁,而表之间的连接操作是性能较低的操作，直接影响到查
询的速度，所以，对于查询较多的应用，就需要根据实际情况运用逆规范化对数据进行设计,通过逆规范化来提高查询的性能。

例如，移动电话的用户每月都会查询自己的账单，账单信息- -般包含用户的名字和本月消费总金额，设想一下，如果用户的姓名和属性信息存放在一个表中，假设表名为A,而用户的编号和他对应的账单信息存放在另外一张B表中，那么，用户每次查询自己的月账单时，数据
库查询时都要进行表连接，因为账单表B中并不包含用户的名字，所以必须通过关联A表取过来，如果在数据库设计时考虑到这一点，就可以在B表增加一个冗余字段存放用户的名字，这样在查询账单时就不用再做表关联，可以使查询有更好的性能。

反规范的好处是降低连接操作的需求、降低外码和索引的数目，还可能减少表的数目，相应带来的问题是可能出现数据的完整性问题。加快查询速度，但会降低修改速度。因此，决定，做反规范时，一定要权衡利弊，仔细分析应用的数据存取需求和实际的性能特点，好的索引和其他方法经常能够解决性能问题，而不必采用反规范这种方法。

在进行反规范操作之前，要充分考虑数据的存取需求、常用表的大小、一些特殊的计算(例如合计)数据的物理存储位置等。常用的反规范技术有增加冗余列、增加派生列、重新组表和分割表。

增加冗余列: 指在多个表中具有相同的列，它常用来在查询时避免连接操作。
增加派生列: 指增加的列来自其他表中的数据，由其他表中的数据经过计算生成。增加的派生列其作用是在查询时减少连接操作，避免使用集函数。
重新组表: 指如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接而提高性能。
分割表:可以参见2小节的内容。

另外，逆规范技术需要维护数据的完整性。无论使用何种反规范技术，都需要一定的管理来维护数据的完整性，常用的方法是批处理维护、应用逻辑和触发器。

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