每天进步一点点————数据库对象优化

1.   优化表的数据类型

在MySQL中。能够使用函数procedure analyse()对当前应用的表进行分析。该函数能够对数据表中列的数据类型提出优化建议，用户能够依据应用的实际情况酌情考虑是否实施优化。

下面是函数的用法

select* from 表名 PROCEDUREANALYSE();

SELECT* FROM 表名 procedureanalyse(16,256)————表示不要为那些包括的值多余16个或者256个字节的ENUM类型提出建议。

比如：

mysql>select * from emp procedure analyse()\G;

*************************** 1. row***************************

            Field_name: test2.emp.id                        ————列

              Min_value: 2                                              ————最小值

              Max_value: 55                                          ————最大值

            Min_length: 1                                              ————最小长度

            Max_length: 2                                             ————最大长度

      Empties_or_zeros: 0                                              

                  Nulls: 0

Avg_value_or_avg_length: 16.6000

                    Std: 15.0280

     Optimal_fieldtype: ENUM('2','4','5','10','11','12','15','25','27','55')NOT NULL  ——建议更改值

*************************** 2. row***************************

            Field_name: test2.emp.ename

              Min_value: bzfys

              Max_value: bzfys

            Min_length: 5

            Max_length: 5

      Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 5.0000

                    Std: NULL

     Optimal_fieldtype: ENUM('bzfys') NOT NULL

*************************** 3. row***************************

            Field_name: test2.emp.hired

              Min_value: 1970-01-01

              Max_value: 1970-01-01

            Min_length: 10

            Max_length: 10

      Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 10.0000

                    Std: NULL

     Optimal_fieldtype: ENUM('1970-01-01') NOT NULL

*************************** 4. row***************************

            Field_name: test2.emp.separated

              Min_value: 9999-12-31

              Max_value: 9999-12-31

            Min_length: 10

            Max_length: 10

      Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 10.0000

                    Std: NULL

     Optimal_fieldtype: ENUM('9999-12-31') NOT NULL

*************************** 5. row***************************

            Field_name: test2.emp.job

              Min_value: aac

              Max_value: aac

            Min_length: 3

            Max_length: 3

      Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 3.0000

                    Std: NULL

     Optimal_fieldtype: ENUM('aac') NOT NULL

*************************** 6. row***************************

            Field_name: test2.emp.store_id

              Min_value: 20

              Max_value: 20

            Min_length: 2

            Max_length: 2

      Empties_or_zeros: 0

                  Nulls: 0

Avg_value_or_avg_length: 20.0000

                    Std: 0.0000

     Optimal_fieldtype: ENUM('20') NOT NULL

6 rows in set (0.00 sec)

 

ERROR:

No query specified

然后能够通过下面命令改动字段类型（自己依据业务考虑是否要更改)

mysql>alter table emp modify ename ENUM('bzfys') NOT NULL;

Query OK, 10 rows affected (0.18 sec)

Records: 10 Duplicates: 0  Warnings: 0

 

 

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

这里锁说的“拆分”，是指的对数据表进行拆分。

针对MyISAM类型的表进行。那么有两种拆分方法

1、第一种方法是垂直拆分，即把主码和一些列放到一个表，然后把主码和另外的列放到还有一个表中

假设一个表中某些列经常使用，而还有一些列不经常使用，则能够採用垂直拆分，另外。垂直拆分能够使得数据行变小。一个数据页就能存放很多其它的数据，在查询时就会降低I/O次数。其缺点是须要管理冗余列。查询锁有数据须要（JOIN）操作。

2、另外一种方法是水平拆分。即依据一列或者多列数据的值把数据行放到两个独立的表中。

水平拆分通常使用在下面几种情况：

         1）表非常大，切割后能够减少在查询时须要读取的数据和索引的页数，同事也减少了索引的层数，提高查询速度。

         2）表中的数据根本来就有独立性，比如。表中分别记录各个地区的数据或不同一时候期的数据，特别是有些数据经常使用，而另外一些数据不经常使用。

         3）须要把数据存放到多个介质。

水平拆分会给应用添加复杂度，它通常在查询时须要多个表名。查询全部数据须要UNION操作。

3.   逆规范化

数据库设时要求满足归法换。这个道理大家都清楚，可是是否数据的规范化程度越高越好呢？这个由实际需求来决定。

反规范的优点是减少连接操作的需求、减少外码和索引的数目。还能减少表的数目。对应带来的问题是可能出现数据完整性问题。

加快查询速度，但会减少改动速度。

因此，决定做逆规范时，一定要权衡利弊，细致分析应用的数据存取需求和实际的性能特点，好的索引和其它方法常常可以解决性能问题，而不必採用范规范这样的方法。

 

在进行反规范操作之前。要充分考虑数据的存取需求、经常使用表的大小、一些特殊的计算、数据的物理存储位置等。

经常使用的反规范技术有添加冗余列、添加派生列、又一次组表和切割表。

         添加冗余列：指在多个表中具有同样的列。它经常使用来在查询时避免连接操作。

         添加派生列：指添加的列来自其它表中的数据。由其它表中的数据经过计算生成。添加的派生列其作用是在查询时降低连接操作，避免使用集函数。

         又一次组表：指假设很多用户须要查看两个表连接出来的结果数据。则把这两个表又一次组成一个表来降低连接而提高性能。

         切割表：垂直拆分或者水平拆分。

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

         批处理维护是指对复制列或派生列的改动积累一定的时间后，执行一批处理作业或者存储过程对复制列或派生列进行改动，这仅仅能在对实时性要求不高的情况下使用。

         数据完整性也能够由应用逻辑实现，这就要求必须在同以事务中对全部涉及的表进行增删改操作。应用逻辑来实现数据完整性风险较大，由于同一逻辑必须在全部的应用中使用和维护，easy遗漏，特别是在需求变化时，不易维护。

         还有一种方式就是使用触发器。对数据不论什么改动马上出发对复制或者派生列的对应改动。触发器是实时的。并且对应的处理逻辑仅仅在一个地方出现。易于维护。一般来说，是解决这类问题的比較好的办法。

4.   使用中间表提高统计查询速度

中间表在统计查询中经经常使用到，其长处例如以下；

         中间表复制原表的部分数据。而且与原表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响。

         中间表上能够灵活的加入索引，或者添加暂时用的薪资段。从而达到提高统计查询效率和辅助统计查询作用。