数据库物理设计

骑猪看日落 2022-05-26 00:57 66阅读 0赞

## 物理设计 ##

物理设计就是根据所选择的关系型数据库的特点对逻辑模型进行存储结构设计。它涉及的内容包含以下4方面：1. 定义数据库、表及字段的命名规范；2. 选择合适的存储引擎；3. 为表中的字段选择合适的数据类型；4. 建立数据库结构。

## 定义数据库、表及字段的命名规范 ##

*  数据库、表及字段的命名要遵守可读性原则
 *  数据库、表及字段的命名要遵守表意性原则
 *  数据库、表及字段的命名

## 存储引擎 ##

*  MyISAM存储引擎是非事务的，锁粒度是表级的，主要应用于`select，insert`，不适合应用于读写操作频繁的场景，因为对于读写操作会进行锁表操作。
 *  MRG\_MYISAM和MyISAM差不多
 *  Innodb存储引擎是支持事务，支持MVCC行级锁，适合任何场景
 *  Archive存储引擎不支持事务，支持行级锁，支持insert、select，适用于随机读取、更新、删除。
 *  Ndb Cluster是MySQL集群存储引擎 ，支持事务，支持行级锁，具有高可用性

## 数据类型 ##

原则：当一个列可以选择多种数据类型时，应该优先考虑数值类型，其次是日期或二进制类型，最后是字符类型。对于相同级别的数据

类型，应该优先选择占用空间小的数据类型。

#### 1. 数值类型 ####

数值类型可以分为整数类型和实数类型。

![image.png][]

其中，M表示整数类型的最大显示宽度； 对于浮点和定点类型，M是可以存储的总位数（精度）；对于字符串类型，M是最大长度。 M

的最大允许值取决于数据类型。注意：当我们只存储两位数时，一种错误用法就是int(2)，其实int(i)存储空间是由数据类型和是否是

unsigned决定的，i只是表示显示长度。int(11)和int(1)的存储空间是没差别的， int(11)的数据长度如果小于11，则默认补充空格，如果

int (11)字段被zerofill修饰，则默认补充0，而且字段被zerofill修饰会自动添加unsigned。因此，当我们只存储两位数时，使用tinyint才能

达到我们简约空间的目的。

注意：Decimal类型是精确类型的，如果我们需要存储精确数据，例如财务数据就必须使用Decimal类型，而不能使用float和double类型。

#### 2. 字符串类型 ####

字符串类型中有两种类型：char和varchar。  
![image.png][image.png 1]

##### varchar(n)的存储规范 #####

*  varchar存储变长内容，varchar需要额外的空间记录内容长度，当内容小于255字节时，需要一个额外字节，当内容大于255字节时，需要2个额外字节；
 *  在jbk编码下，char占2个字节，在UTF-8编码下，char占用3个字节；
 *  MySQL每行最多存储65535个字节；
 *  varchar中的第1个字节表示是否为空，第二个字节和第3个字节表示长度，剩下字节表示实际内容，因此最大可用存储65535-1-2=65532 ；

create table t1(c varchar(n)) charset=gbk,则此处n的最大值为(65535-1-2)/2=32766
    
    create t(c int ,c2 char(30),c3 varchar(n)) charset=utf8,则此处n的最大值（65535-1-2-4-90）/3=21812

\* varchar（100）中的100指的是100个字符数量；

*  使用场景：**字符串列的最大长度比平均长度大很多**；**字符串列很少被更新**，由于varchar类型长度不固定，那么进行更新时，可

能导致存储页的分裂，引起存储碎片；使用多字节字符集存储字符串，以UTF-8为例，存储中文需要3个字节，而存储英文或数字只需

要1个字节。

##### char(n)的存储规范 #####

*  char类型是定长的；
 *  字符串存储在char类型的列中会删除末尾的空格（见下图）；
 *  char类型的最大宽度是255字节。
 *  适用场景： **char类型适合存储长度近似的值**，例如身份证、md5值，手机号等；**char类型适合存储短字符串**，例如性别字段，

使用char(1)就比varchar(1)更节省空间，因为varchar还需要存储额外字节存储其他信息；**char类型适合存储经常更新的字符序列**，

由于char类型的长度是固定的，MySQL会一次性地分配存储空间，在多次更新时也不会出现页分裂的情况，减少存储碎片。

![image.png][image.png 2]

#### 3. 日期类型 ####

![image.png][image.png 3]

注意：timestamp占4byte，实际上是用int存储的。由于只有4byte，因此它只能显示1970-01-01 到 2038-01-19，也正是这个原因，如

果在这个时间内的字段推荐使用timestamp。**timestamp类型显示依赖于所指定的时区**。除此之外，还需要注意一点，\*\*在行的数据

修改时可以自动修改timestamp列的值\*\*，这个特性可以帮助我们在进行数据分析时自动提取出最新变化的数据。

MySQL5.5 datetime类型字段不能设置默认值为now()

MySQL5.6 datetime类型字段可以设置默认值为now()

*  from\_unixtime():数字转换成时间
 *  unix\_timestamp():时间转换成数字
 *  Date\_sub/DATE\_ADD:对时间进行加减
 *  CURDATE():将当前日期按照’YYYY-MM-DD’
 *  NOW():返回当前日期和时间值

## 建立数据库结构 ##

#### 1. DML（Data manipulation language，数据操作语言） ####

*  DML分成数据查询和数据更新两类，数据更新又分成插入、删除和修改三种操作，主要命令：`select、insert、delete、update、merge、call、explain、plan`。

#### 2. DDL（Data definition language，数据定义语言） ####

*  DDL包括两种类型数据：数据字典以及数据类型和结构定义。主要命令：`create 、alter、drop、truncate、rename、comment`。
 *  `Create`，用于创建语句，用于创建表或者数据库或者存储过程或者其他内容

create table tea(id int not null auto_increment,name varchar(6) not null,remark char(6),primary key(id));
    show create table tea;

*  `Alter`,一般用于添加或者修改表中的字段名或者字段定义。也可以用于修改字段顺序等。同样的也可以用于修改数据库的名字或者编码

修改字段定义:
    ALTER TABLE emp MODIFY first_name VARCHAR(20) NOT NULL DEFAULT '-';  
    添加字段:
    alter table emp add column age int(3) not null default 0;  
    修改字段名字，同时修改定义:
    alter table emp change age age1 int(4) not null default 0;  
    在表Column的尾部追加新的column:
    alter table emp add birth date not null after empno;  
    移动column到表column的首位:
    alter table emp modify age1 int(3) not null default 0 first;  
    将某个column移动到另一个columon后面:
    alter table department change deptno deptno int(11) NOT NULL after deptname;  
    删除主键:
    alter visit_log drop primary key;  
    修改表名:
    alter table emp rename employees; 
    删除字段
    alter table guess_product_info  drop column backstyle;

#### 3. DCL（Data Control Language，数据控制语言） ####

*  设置或更改数据库用户或角色权限，包括对基本表和视图的授权，完整性规则的描述，事务控制等内容。
 *  grant

赋予权限
    grant all privileges on *.* to 'root'@'localhost' with grant option; 授权远程用户注意不要with grant option
    grant select,update,create,delete on *.* to guest@'115.220.46.%' identified by 'test@home';创建业务用户，并要求只限制IP段
    查看权限
    Select user,password,host from mysql.user;
    Show grants for root@'localhost';
    select user,password,host ,grant_priv,super_priv from mysql.user;
    重命名用户
    rename mysql.user root@' ' to admin@' ';
    刷新权限
    flush privileges
    回收权限
    revoke select,insesrt,update,delete,create on *.* from 'guest'@'115.220.46.%';
    show grants for guest@'115.220.46.%';
    修改密码
    update mysql.user set password =password('test') where user='guest';password()函数对字符串进行MD5加密
    mysqladmin -uroot -ptest password 12345
                                        -p 旧密码  password 新密码
    
    忘记密码
    mysqld_safe --defaults-file=/etc/mysql/my3306.cnf --skip-grant-tables &
    删除用户
    drop user root@'';
    删除test数据库
    select * from mysql.db where db like '%test%'\G;
    delete from mysql.db where db like '%test%';
    删除不需要的用户
    
    mysql> select user,password,host from mysql.user;
    +------+-------------------------------------------+------------+
    | user | password                                  | host       |
    +------+-------------------------------------------+------------+
    | root |                                           | localhost  |
    | root |                                           | nbview.com |
    | root |                                           | 127.0.0.1  |
    | root |                                           | ::1        |
    |      |                                           | localhost  |
    |      |                                           | nbview.com |
    | kewy | kewy126@home                              | %          |
    | root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %          |
    +------+-------------------------------------------+------------+
    8 rows in set (0.00 sec)
    mysql> delete from mysql.user where user='kewy';
    ERROR 2006 (HY000): MySQL server has gone away
    No connection. Trying to reconnect...
    Connection id:    59
    Current database: student_db1
    Query OK, 1 row affected (0.01 sec)
    mysql> delete from mysql.user where user='root' and host='::1';
    Query OK, 1 row affected (0.00 sec)
    mysql> delete from mysql.user where user='root' and host='nbview.com';
    Query OK, 1 row affected (0.00 sec)
    mysql> delete from mysql.user where host='nbview.com';
    Query OK, 1 row affected (0.00 sec)
    
    mysql> delete from mysql.user where host='localhost' and user is null;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delete from mysql.user where host='127.0.0.1' ;
    Query OK, 1 row affected (0.00 sec)
    mysql> select user,password,host from mysql.user;
    +------+-------------------------------------------+-----------+
    | user | password                                  | host      |
    +------+-------------------------------------------+-----------+
    | root |                                           | localhost |
    | root | *08F411191A8F7130F09F0A961DB8E87983620D5B | %         |
    +------+-------------------------------------------+-----------+
    2 rows in set (0.00 sec)

#### 4. TCL（Transaction Control Language，事务控制语言） ####

*  TCL用于控制事务内执行流程的语言
 *  start transaction|Begin:开始一个事务
 *  Savepoint：创建一个记录点，方便回滚到这个地方
 *  Rollback:回滚事务
 *  Commit：提交事务

开始事务
    START TRANSACTION  
    可选:创建一个存档点
    SAVEPOINT sp  
    开始操作SQL
    select  
    insert  
    ...
    操作完成提交数据
    COMMIT  
    或者，操作失败，回滚数据(回滚到某个存档点)
    ROLLBACK  
    ROLLBACK TO sp  
    释放存档点
    RELEASE SAVEPOINT sp

## 总结 ##

在进行数据库物理设计时，我们需要计算每张表的存储空间， 选择存储引擎和表中的数据类型。

--------------------

![image][]

欢迎关注微信公众号：木可大大，所有文章都将同步在公众号上。

[image.png]: /images/20220526/363e657992184189b9f15d82c42aab46.png
[image.png 1]: /images/20220526/bbf391903f814a4b9cf21c05021e3e36.png
[image.png 2]: /images/20220526/16905765c1be491a978c24c702fef9af.png
[image.png 3]: /images/20220526/b7638f8698e7430a80d8cccb9e5977a6.png
[image]: /images/20220526/c643ecf94d4f45a09ea5694b31db0a1d.png