MYSQL优化数据库对象

 

预计阅读时间：8分钟

1.优化表的数据类型

在创建数据库表的时候，要判断对应的数据类型，选择合适的数据类型，虽然表设计的字段的长度可以预留一定的长度，但不推荐很多字段都出现大量冗余。这样又浪费存储空间，同是浪费物理内存。

我们可以使用  PROCEDURE ANALYSE() 函数分析当前表，该函数会建议提出优化建议，用户可以根据建议和实际情况考虑是否优化

/*表：tb_sys_user*/--------------------

/*列信息*/-----------

Field          Type         Collation        Null    Key     Default  Extra   Privileges                       Comment   
-------------  -----------  ---------------  ------  ------  -------  ------  -------------------------------  ----------
id             int(11)      (NULL)           NO      PRI     (NULL)           select,insert,update,references  id        
name           varchar(20)  utf8_general_ci  NO              (NULL)           select,insert,update,references  名字    
department_id  int(11)      (NULL)           YES             (NULL)           select,insert,update,references  部门id  
create_time    datetime     (NULL)           NO              (NULL)           select,insert,update,references            
update_time    datetime     (NULL)           NO              (NULL)           select,insert,update,references            

/*索引信息*/--------------

Table        Non_unique  Key_name  Seq_in_index  Column_name  Collation  Cardinality  Sub_part  Packed  Null    Index_type  Comment  Index_comment  
-----------  ----------  --------  ------------  -----------  ---------  -----------  --------  ------  ------  ----------  -------  ---------------
tb_sys_user           0  PRIMARY              1  id           A                    2    (NULL)  (NULL)          BTREE

/*DDL 信息*/------------

CREATE TABLE `tb_sys_user` (
  `id` int(11) NOT NULL COMMENT 'id',
  `name` varchar(20) NOT NULL COMMENT '名字',
  `department_id` int(11) DEFAULT NULL COMMENT '部门id',
  `create_time` datetime NOT NULL,
  `update_time` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

使用函数 PROCEDURE ANALYSE（）

SELECT * FROM tb_sys_user PROCEDURE ANALYSE()

Field_name                      Min_value            Max_value            Min_length  Max_length  Empties_or_zeros   Nulls  Avg_value_or_avg_length  Std     Optimal_fieldtype                                           
------------------------------  -------------------  -------------------  ----------  ----------  ----------------  ------  -----------------------  ------  ------------------------------------------------------------
test.tb_sys_user.id             0                    2                             1           1                 1       0  1.0000                   1.0000  ENUM('0','2') NOT NULL                                      
test.tb_sys_user.name           12                   234                           2           3                 0       0  2.5000                   (NULL)  ENUM('12','234') NOT NULL                                   
test.tb_sys_user.department_id  12                   12                            2           2                 1       0  6.0000                   6.0000  ENUM('0','12') NOT NULL                                     
test.tb_sys_user.create_time    2019-07-03 17:31:12  2019-07-03 17:31:27          19          19                 0       0  19.0000                  (NULL)  ENUM('2019-07-03 17:31:12','2019-07-03 17:31:27') NOT NULL  
test.tb_sys_user.update_time    2019-07-03 17:31:14  2019-08-02 17:31:28          19          19                 0       0  19.0000                  (NULL)  ENUM('2019-07-03 17:31:14','2019-08-02 17:31:28') NOT NULL  

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

所为拆分表，如果指定MyISAM的类型，有2种方式进行拆分。

(1)垂直拆分:把主键和重要的列放在一个表，另外把主键和其他的列放在另外一张表。

应用场景：

某张表，有些列经常使用，有些列不经常使用，可以进行垂直拆分，另外，垂直拆分可以使数据行变小，一个数据也能放更多的数据，在查询时就会减少IO的次数，其次缺点是需要管理冗余，查询所以需要使用JOIN操作。

(2)水平拆分:可以根据某列和某多个列的数据的值放在单独的两个表中。

应用场景：

a.表很大，分割后可以查询需要读的数据和索引的页数，同时也降低索引的层数，提高查询速度。

b.表中的数据本来就有独立性。

c.需要把数据存储在多个介质。（比如说，某些和时间有关的数据，某半年把数据分一张表，超过半年归档历史数据表）

 

3.逆规则化

数据库设计时要满足数据库的设计规范化，但是数据是否的规范化程度越来越高了呢？其实很多是需求决定的，因为规范程度高，数据之间的关系越来复杂

就会导致表之间的操作会越来越频繁，表之间的连接是会降低性能的，直接影响查询的速度，所以有时候可以通过反规则设计可以提供性能，提高查询速度。

反规则的好处不但可以提高查询速度，还可以降低索引数目，减少表之间的关联，反规则化有利有弊，根据实际的情况，可以解决性能问题，用的不好，反而降低查询速度。

常用反规则化的方法:

（1)字段冗余:指多个表存在相同的列，他也是经常使用查询的可以避免表连接的操作。

（2)增加派生列:指增加的列的来自其他的表的数据，由其他的数据经过计算生成，增加派生列作用减少查询连接操作。

 

 

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

对于数据量比较大的表，在查询的速度上效率会比较低，并且考虑查询速度是否会负面影响，通常下，我们可以通过中间表提查询的速度，比如说，每天的订单都有很多，财务报表有统计一周的销量，那么我们可以新建一张中间表字段和数据类型一样，把7天数据搬到这里，直接统计，不用在总表查询。

使用中间表的好处：

(1)中间表复制原表的数据，并且和原表隔离，在中间表查询不会影响查询负面影响

(2)中间表可以灵活添加字段，或者添加索引，灵活使用，从而提升性能。

 

总结

数据库对象设计的好坏是一个数据库设计的基础，从而一旦数据库设计完成投入生产，将来进行修改就比较麻烦，因此数据库设计的时候要多方面考虑。