数据库维护和优化


数据库设计
数据库逻辑设计
数据库物理设计
数据库维护和优化

由于项目中需求是不断变化的,所以数据库中的结构也是需要不断的做相应的改变

维护和优化中要做什么

1)维护数据字典

数据字典对于应用是否便于维护是非常重要的,如果我们不清楚表中每一个列或者说每一个表中存储的内容是什么,那么今后对应用进行升级维护就很难入手。
特别是一些状态字段,比如用一些数字来表示的状态值,那么每一个数字代表的含义就一定要记录在数据字典中

2)维护索引

由于表结构和数据量的不断变化以及业务上使用的查询的不断变化我们就要对表中的索引去不断的进行维护和优化,有的索引在刚开始可能是适用的,但是随着数据量的增长和查询不断的变化,索引也可能从适用变成了不使用,这时候就要对老的索引进行删除并且建立新的索引

3)维护表结构

也是由于需求的不断变化,表结构也会随之跟着改变。如列的增加或者删除或者对于一些列的类型跟着更改

4)在适当的时候对表进行水平拆分或垂直拆分

随着数据量的不断增长,表中存储的数据量是有一定的瓶颈的,它超过某一个量级之后查询就会变得非常缓慢,这时就要进行拆分,拆分又分为水平拆分和垂直拆分

如何维护数据字典

维护数据字典有很多种方式

1)使用第三方工具对数据字典进行维护

不同的数据库管理系统有不同的数据字典工具。

2)利用数据库本身的备注字段来维护数据字典。以mysql为例

导出数据库就可以形成数据字典了。

如何维护索引

如何选择和是的列建立索引?

1) 出现在where从句,group by从句,order by从句中的列

但是从句中经常会出现多个列的情况,这种时候就要选择可选择性高的列要放到索引的前面

2) 可选择性高的列要放到索引的前面

这时大家可能会有一个误区:在索引列的顺序上要跟where条件中的列的顺序要保持一致,实际上这个要求并不是必须的,因为在现在的数据库中所有的sql在执行之前都要经过sql
优化器重新编译。在编译过程中sql优化器就会按照我们数据库中的索引和统计信息对查询列重新的排序和优化,会自动选择适合sql的索引。

3) 索引中不要包括太长的数据类型

数据库是页的形式存储的,页对于mysql来说就是16k,在16k的大小中能够存储的条数越多那么索引查找的速度就会越快,因此索引的列不要太长,例如在mysql中它可以对“前置”进行索引的,比如text类型可以选择对前16个text字节进行索引

索引维护注意事项

1)索引并不是越多越好,过多的索引不但会降低写效率,而且会降低读的效率

2)定期维护索引碎片

3)在SQL语句中不要使用强制索引关键字

如何维护表结构

数据库中适合的操作

如果我们在数据库中只执行它适合的操作的话,那么优化就成功了一大半

1) 批量操作 VS 逐条操作

数据库中适合批量操作,而逐条操作适合在程序中完成

2) 禁止使用 select * 这样的查询

这样会造成io的浪费,因为查询出大量的我们不使用的字段,另外如果表结构进行了变更使用select * 可能会使程序出错

3) 控制用户使用自定义函数

大量使用自定义函数会对索引的使用造成影响,因为如果使用了函数那么列中的索引就会不起作用,所以开发过程中尽量少使用自定义函数

4)不要使用数据中的全文索引

因为全文索引是需要另外建立索引文件来进行维护的,另外一方面是对中文的支持也不是太好

数据库表的垂直和水平拆分

垂直拆分就是把原来的一张表拆分成多张小的表,垂直拆分后的多张小表他们的列是不一样的,这就是表的垂直拆分

什么时候进行垂直拆分?

当我们的需求变得越来越多,我们可能会对某一张表的列进行增加,当这张表的列增加到几十列的时候,这个时候我们就要考虑对表进行垂直拆分。

那么垂直拆分有什么好处呢?

首先我们数据库是以“页”来进行存储的,页中我们存储的行数越多那么我们的IO效率也就越高,但当一张表变得非常宽的时候,那么也就意味着每一行的数据大小也会变大,这样在一页中所能存储的行数就会越来越少,那么IO效率也会变得很低,但当我们对一张非常宽的表进行垂直拆分后形成多张比较窄的窄表,那么这时候每一张小表中每一行的长度就不会太大,这样就优化了表的IO读取效率。

把哪些列放到小表中?

首先要把经常一起查询的列放大一张表中,另外把一些大字段放到另外的表中,这样就在查询的时候不需要在进行表的关联操作了,不但优化了IO,也减少了sql的复杂程度

但是通过表的垂直拆分之后表的数据量是不应该有变化的,原来大表有一百万条数据,那么拆分后的小表每个也应该有一百万条数据。

表的水平拆分

那么如果有一张大表,表中已经有了上亿条数据,我们希望把这张表的数据量减少,比如我们希望每张表中只有一千万行数据,那么这个时候就需要对表进行水平拆分

水平拆分后每张表的结构都是相同的,把原来的一张表复制成多份,这就叫做水平拆分,每张表中存储的数据是不一样的。

如何把一张大表中的数据分散到多个小表中呢?下面说种比较常用的方法:

通过主键哈希的方式来进行,每一张表中都是应该有主键的,通过对主键值进行哈希操作,比如说根据主键按模来取值,这样就可以把原来的一张大表平均的分散到多张小表中,每张小表的数据量是非常平均的,因为我们取的是哈希值,但是每张表中存储的数据都是不一样的,只有把这五张表全部汇总起来才是原来一张大表的数据,这就是比较常用的一种水平拆分的方式,水平拆分解决了表中数据量的问题,而垂直拆分解决了表的宽度问题,这就是在维护优化中经常用到的优化方式来优化表的IO


文章作者: 拾年
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 拾年 !
评论
 上一篇
数据库设计 数据库设计
数据库设计数据库逻辑设计数据库物理设计数据库维护和优化 为什么要进行数据库设计谈到数据库设计首先要弄明白什么是“数据库设计”,我们可以打一个不太恰当的比喻:把数据库应用程序的设计看作是在建立一座大厦,那么建立大厦之前通常要对大厦的整体结构
2020-05-12
下一篇 
数据库物理设计 数据库物理设计
数据库设计数据库逻辑设计数据库物理设计数据库维护和优化 物理设计要做什么 选择合适的数据库管理系统 定义数据库、表及字段的命名规范。 根据所选的DBMS系统选择合适的字段类型。 反范式化指的是在逻辑设计中已经确立好的非常规范的数据库结构模
2020-05-11
  目录