mysql常用数据库对象_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()

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)中间表可以灵活添加字段，或者添加索引，灵活使用，从而提升性能。

总结

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