MySQL学习之数据库设计规范讲解 - 数据库编程 - 编程开发
设为首页 加入收藏

TOP

MySQL学习之数据库设计规范讲解(一)
2018-05-08 06:06:57 】 浏览:99
Tags:MySQL 习之 数据库 设计 规范 讲解

一.数据库命名规范

1.所有的数据库名称和表名称必须使用小写字母并使用下划线分割

这是因为MySQL数据文件就是Linux下的一个问题,Linux是大小写敏感的,所以MySQL数据库和表的名称也是大小写敏感的:

Dbname和dbName代表两个不同的数据库

Table和table代表两个不同的表

为了不引起奇异,规定数据库名称和表名称必须使用小写字母+下划线的方式

2.所有的数据库名称禁止使用MySQL保留关键字

selectid.username,from,agefromtb_user;

上面的SQL语句有两个from,第一个是字段,第二个是SQL关键字,MySQL在执行查询时,并不能区分是关键字还是数据字段,那么需要:

selectid.username,`from`,agefromtb_user;

需要在from数据字段加反向引号,为了避免出现奇异,禁止数据字段使用SQL关键字。

3.数据库名称命名要见名识意,不要超过32字符

4.临时库表的前缀必须以tmp开头,并以日期为后缀

备份库表的前缀必须以bak开头,并以日期为后缀

5.所有存储相同数据的列名和列类型必须一致

e.g.我们在用户信息表和订单表中都会存储用户ID,customer_id,那么要求两个表中用户ID的名称和类型必须一致。

这样的列一般作为关联列使用,要是数据类型不一致,在进行联合查询的时候,MySQL需要先进行类型转换,有可能导致索引失效,降低查询效率。

二.数据库基本设计规范

1.表引擎使用InnoDB

在MySQL5.5以前Myisam是默认的存储引擎,MySQL5.5以后InnoDB为默认的存储引擎。

InnoDB在MySQL5.6及以后进行了很好的优化,InnoDB是一种支持事务、行级锁、有更好的恢复性,高并发下性能更好的存储引擎。

2.数据库和表的字符集统一使用UTF8编码格式

[说明]UTF8中一个中文字符占3个字节,其他的字符占一个字节。

3.所有的表和字段都需要添加注释comment

4.尽量控制单表数据量的大小,建议控制在500万以内

[说明]MySQL在32位操作系统下,单个表的大小是不能超过2G的;

当数据库数据过大的时候,可以考虑历史数据归档,分库分表的手段来控制数据量的大小。

5.尽量做到冷热数据分离,减小表的宽度

减小磁盘IO,保证热数据的内存缓存命中率;

更有效的利用缓存,避免过多的读入无用的冷数据,避免使用select*

把经常使用的数据放到一个表里面。

6.禁止在表中建立预留字段

7.禁止在数据库中存储图片、文件等二进制的数据

8.禁止在生产环境做数据库压力测试

三.数据库索引设计规范

(建立索引的目的,通过索引查找数据,减少磁盘的随机IO,提高查询的性能)

1.限制每张表的索引个数,建议单张表的索引不超过5个

索引并不是越多越好,索引可以提高查询效率,同样降低可以降低查询效率,这是因为MySQL在进行查询的时候,会根据现有的索引生产一个最有的查询方案,索引过多导致生成查询方案的时间变长,降低查询的效率;

索引可以提高查询的效率,但降低了插入、修改数据的效率。

2.InnoDB表必须有一个主键

InnoDB是一个索引组织表,即数据存储的逻辑顺序和索引顺序是一致的。

每个表可以有多个索引,但是每个表的存储顺序只有一种,当有多个索引的时候,InnoDB表会按照主键的顺序来进行存储,所以要求每个InnoDB在创建的时候,必须有一个主键,若果没有主键,MySQL就会选择第一个非空唯一索引作为存储顺序,如果没有非空索引的话,MySQL就会自动生成一个6个字节的主键,而自动生成的主键并不是性能最好的。

【注意】

(1)InnoDB的主键不能够频繁的变动,这是因为InnoDB是索引组织表,主键的变动会导致表中数据存储的顺序发生改变,影响表的性能。

(2)主键不建议使用类似UUID,MD5,HASH,字符串作,因为这些值一般无法保证数据的顺序增长。

以MD5值为列子,我们无法保证后面插入的数据的MD5主键值要比之前数据的MD5值要大,那么存储的时候所以会将其插入到比他大的索引后面,导致大量的数据需要向后移动,造成大量IO操作和占用CPU资源的情况,建议使用MySQL的自增ID作为主键。

(3)常见的索引建议

SELECT,UPDATE,DELETE语句的WHERE从句中的列;

包含ORDERBY,GROUPBY,DISTINCT中的字段;

多表JOIN的关联列

(4)对于频繁的查找优先考虑使用覆盖索引;

InnoDB中主键作为一级索引,当我们按照一个二级索引查找数据的时候,InnoDB首先按照二级索引查找到对应的子节点的位置,而二级索引的子节点中保存的数据是该行主键的信息,要得到真实的数据,还要通过主键进行二次查找,而覆盖索引中,二级索引的键值是可以获得所有数据的,这样避免了进行二次查询。

四.数据库字段设计规范

1.优先选择符合存储需求的最小数据类型

将字符串转化为数字类型进行存储,e.g.将IP地址转化为数字进行存储,MySQL提供了两个函数,在我们存储的时候,调用INET_ATOM('255.255.255.255')=429****95,将字符串类型的IP地址转换为数字类型,同样在取数据的时候,使用INET_NTOA(429****95)='255.255.255.255'函数,将数字类型的IP地址还原为字符串

2.对于非负数据采用无符号整型进行存储

无符号相对于有符号数据,可以多出一倍的存储空间,e.g.

SIGNEDINT有符int类型的范围在-2147483648到2147483647

UNSIGNEDINT无符int类型的存储范围在0-4294967295之间。

3.MySQL中的VARCHAR(N)的N代表的是字符数,而不是字节数,这和其他的一些数据库中VARCHAR代表的存储单位不太一样,使用UTF8存储汉字的时候,每个汉字占3个字节。

4.避免使用TEXT、BLOB数据类型

建议把BLOB和TEXT列单独分离到扩展表中,TEXT和BLOB只能使用前缀索引

5.尽可能把所有的列定义为NOTNULL

索引列为NULL时,需要额外占用索引空间存储NULL状态;

进行列的比较和计算时,需要对NULL进行特殊处理,这样有可能导致索引失效

6.时间日期的存储使用TIMESTAMP或DATETIME类型,不要使用字符串存储

缺点:无法使用日期函数进行比较;用字符串存储日期占用更多的空间,使用字符串需要使用16字节,要是使用DateType,那么只需要8字节;

同样的效果TIMESTAMP存储只需要4个字节,而DATETIME需要占用8个字节,但是TIMESTAMP只能存储时间在1970-01-0100:00:01到2038-01-1903:14:07之间的时间,TIMESTAMP实质上是使用INT类型来存储数据的,只是在显示的时候,进行转化。

7.数据库中存储的浮点类型数据包括非精准浮点(float,double)和精准浮点数据类型(decimal)

约定同财务相关的金额数据,一律使用decimal数据类型;

decimal数据类型占用的空间是由定义的宽度决定的,每4个字节可以存储9
编程开发网

首页 上一页 1 2 下一页 尾页 1/2/2
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
上一篇关于数据库概念及SQL语句整理 下一篇Navicat连接MySql8.0的各种问题及..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

最新文章

热门文章

C 语言

C++基础

windows编程基础

linux编程基础

C/C++面试题目