mysql优化——1.字段设计

概述

为什么要优化

  • 系统的吞吐量瓶颈往往出现在数据库的访问速度上
  • 随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢
  • 数据是存放在磁盘上的,读写速度无法和内存相比

如何优化

  • 设计数据库时:数据库表、字段的设计,存储引擎
  • 利用好 MySQL 自身提供的功能,如索引等
  • 横向扩展:MySQL 集群、负载均衡、读写分离
  • SQL 语句的优化(收效甚微)

字段设计

字段类型的选择,设计规范,范式,常见设计案例

原则:尽量使用整型表示字符串

存储 IP

INET_ATON(str),address to number

INET_NTOA(number),number to address

MySQL 内部的枚举类型(单选)和集合(多选)类型

但是因为维护成本较高因此不常使用,使用关联表的方式来替代 enum

原则:定长和非定长数据类型的选择

decimal 不会损失精度,存储空间会随数据的增大而增大。double 占用固定空间,较大数的存储会损失精度。非定长的还有 varchar、text

金额

对数据的精度要求较高,小数的运算和存储存在精度问题(不能将所有小数转换成二进制)

定点数 decimal

price decimal(8,2) 有 2 位小数的定点数,定点数支持很大的数(甚至是超过 int,bigint 存储范围的数)

小单位大数额避免出现小数

元-> 分

字符串存储

定长 char,非定长 varchar、text(上限 65535,其中 varchar 还会消耗 1-3 字节记录长度,而 text 使用额外空间记录长度)

原则:尽可能选择小的数据类型和指定短的长度

原则:尽可能使用 not null

null 字段的处理要比 null 字段的处理高效些!且不需要判断是否为 null

null 在 MySQL 中,不好处理,存储需要额外空间,运算也需要特殊的运算符。如 select null = nullselect null <> null<> 为不等号)有着同样的结果,只能通过 is nullis not null 来判断字段是否为 null

如何存储?MySQL 中每条记录都需要额外的存储空间,表示每个字段是否为 null。因此通常使用特殊的数据进行占位,比如 int not null default 0string not null default ‘’

原则:字段注释要完整,见名知意

原则:单表字段不宜过多

二三十个就极限了

原则:可以预留字段

在使用以上原则之前首先要满足业务需求

关联表的设计

外键 foreign key 只能实现一对一或一对多的映射

一对多

使用外键

多对多

单独新建一张表将多对多拆分成两个一对多

一对一

如商品的基本信息(item)和商品的详细信息(item_intro),通常使用相同的主键或者增加一个外键字段(item_id

范式 Normal Format

数据表的设计规范,一套越来越严格的规范体系(如果需要满足 N 范式,首先要满足 N-1 范式)。N

第一范式 1NF:字段原子性

字段原子性,字段不可再分割。

关系型数据库,默认满足第一范式

注意比较容易出错的一点,在一对多的设计中使用逗号分隔多个外键,这种方法虽然存储方便,但不利于维护和索引(比如查找带标签 java 的文章)

第二范式:消除对主键的部分依赖

即在表中加上一个与业务逻辑无关的字段作为主键

主键:可以唯一标识记录的字段或者字段集合。

course_namecourse_classweekday(周几)course_teacher
MySQL教育大楼 1525周一张三
Java教育大楼 1525周三李四
MySQL教育大楼 1525周五张三

依赖:A 字段可以确定 B 字段,则 B 字段依赖 A 字段。比如知道了下一节课是数学课,就能确定任课老师是谁。于是周几下一节课和就能构成复合主键,能够确定去哪个教室上课,任课老师是谁等。但我们常常增加一个 id 作为主键,而消除对主键的部分依赖。

对主键的部分依赖:某个字段依赖复合主键中的一部分。

解决方案:新增一个独立字段作为主键。

第三范式:消除对主键的传递依赖

传递依赖:B 字段依赖于 A,C 字段又依赖于 B。比如上例中,任课老师是谁取决于是什么课,是什么课又取决于主键 id。因此需要将此表拆分为两张表日程表和课程表(独立数据独立建表)。

| id | weekday | course_class | course_id |
| - | - | - | - |
| 1001 | 周一 | 教育大楼1521 | 3546 |

| course_id | course_name | course_teacher |
| - | - | - |
| 3546 | Java | 张三 |

这样就减少了数据的冗余(即使周一至周日每天都有Java课,也只是 course_id:3546出现了7次)


标题:mysql优化——1.字段设计
作者:sun
地址:http://sunjuhui.top/articles/2020/05/18/1589795674476.html