MySQL_表定义与优化

原则

字段与业务
    常用与不常用分离
    范式与适当的冗余字段

数据类型
    定长与变长分离
    数据类型的宽度
    常用数据类型的性能优比较

 

---

 

字段与业务

常用与不常用分离

    根据业务的流程规划，将常用的数据放在同一个表中，不常用的数据放在另一张表。
    常用的数据（以学生表为例）：学号、姓名、性别、身高等
    不常用的数据（以学生表为例）：个人说明、老师评价等

 

范式与适当的冗余字段

    范式的作用：根据规范，合理设计表结构，目的消除数据冗余，插入异常，更新异常，删除异常。

    范式等级：1NF（一个字段不能存在多个值） -> 2NF（每行中的各个字段值都必须依赖主键：将一个表分拆多个表【每张表中必须要有主键】） -> 3NF（使用外键与其他表主键关联：此处基本上能达到范式的目的了） -> BCNF -> 4NF -> 5NF

    范式等级越高，表也越多，链表查询就越大，这样会导致系统的性能下降，所以一般在3NF的基础上适当添加一些冗余字段，以达到尽可能的减少链表查询（一般表关联最好在3张表以下）。

 

---

 

数据类型

定长与变长分离

    定长（char，数值，日期等）：表的字段定义尽可能使用固定长度的数据类型，固定表中的每条记录的总长度，以便于提高磁盘读与写的性能（若字符的长度小于30字符，可以使用char定义。）。

    变长（varchar等）：表的字段定义采用变长类的数据类型（varchar），导致表中的每条记录的总长度都不一致，对磁盘读与写的性能有点影响。

 

数据类型的宽度

    总的原则：够用就好。比如：int（8）能满足需求，就不需要采用默认的int宽度。

    字段的定义：最好设置默认值（不要使用NULL，NULL不能参与逻辑运算）。比如：name char(20) not null default ''

    时间，时间戳的数据类型，可以考虑使用数值类型（int等）取代，这样可以更方便以后的统计。

 

常用数据类型的性能比较

    性能：数值型 > date,time > enum,char > varchar > blob，text 

    数值型：能直接参与计算，速度最快

    enum：能对值进行约束，内部用整型存储，但与字符类型联查时，内部需要经历值的转换。

    字符型：涉及到校对集（排序规则）的运算。

 

---

 

举例

原表：

create table if not exists employees( 
id int unsigned not null default 0, 
uname varchar(20) not null default '',          --> 使用 char（20），提高磁盘寻址
gender enum('男','女') not null default '男',    --> 使用 tinyint 或 char(1)
birth date not null default '1900-01-01',       --> 可以考虑改用int取代
salary decimal(10,2) not null default 0.00, 
lastlogin datetime ,                            --> 使用int类型，方便以后查询统计。
intro varchar(1500) not null default '',        --> 根据常用与不常用的原则，将个人介绍独立为一张表
primary key (id) 
) engine=innodb default charset=utf8mb4;

 优化后：

create table if not exists employees( 
id int unsigned not null default 0, 
uname char(20) not null default '', 
gender tinyint(1) not null default 0 comment '0：表示女', 
birth date not null default '1900-01-01', 
salary decimal(10,2) not null default 0.00, 
lastlogin int unsigned not null default 0,
primary key (id) 
) engine=innodb default charset=utf8mb4;

create table intro(
id int unsigned not null default 0, 
uname char(20) not null default '', 
intro varchar(1500) not null default '', 
primary key (id) 
) engine=innodb default charset=utf8mb4;

 

转载于:https://www.cnblogs.com/BoNuo/articles/10166616.html