4个方面入手

l 设计层面, 使用的字段类型(重要), 存储引擎, 范式规范

l 功能层面, 索引, 数据分区, 查询缓存

l 架构层面, 读写分离, 负载均衡.(主从复制)

l SQL层面, select * 少用.

1 字段类型的设计(字段)

1.1 单表字段数量不要太多

30 极限!

1.2 预留字段

extra1, extra2, extra3

修改表结构的开销很大

alter table add column. 尽可能避免.

线上修改表结构

1, 创建新表

2, 复制旧数据

3, 使用新表

1.3 尽可能小

在满足存储需求的情况下, 少占用空间

tinyint-1, int-4, bigint-8

年月日时分秒:
timestamp-4(1970-2038, 4bytes整数), datetime-8(0000-9999)

存储时间, 使用int, 更容易计算. 存储整型的时间戳, 不用考虑时区差异.

1.4 尽可能定长,占用固定的存储空间

是否占用固定的存储空间, 是定长, 否变长.

varchar(32)变长在乎存储空间,, char(32)定长字符串处理效率

hellokang, hellokang

text不占用记录空间独立文档空间存储. varchar占用记录空间

存储小数

double-8双精度(选, 13-14位有效数字), float-4单精度, 都是定长类型. 在乎存储空间选浮点数.

php的的浮点类型, 就是双精度

decimal, 定点数. 不会出现精度丢失. 变长数据类型. 存储的有效数字越长, 占用的存储空间越大. 在乎精度, 选择定点数. 

实操中, 存储余额, 小单位, 大数值方式存储. int, bigint.. 

定长表, 如果一个表中所有的字段, 都是定长数据类型. 这样的表, 就是定长表. 每条记录占用的存储空间是一致的. 表的空间结构 更容易被优化.

1.5 尽可能使用整数代替字符串

整数的计算速度很快的.

enum, 枚举, (单选), set, 集合.(多选)

用起来是字符串类型, 内部存储是, 整数型. 

enum(‘男’, ‘女’, ‘保密’);

insert into user values (‘男’)
 

实操时, enum, 和 set类型, 不建议使用. 是因为, 结构维护的成本很高.

enum(‘男’, ‘女’, ‘保密’, ‘男转女’)

alter table modify gender enum(‘男’, ‘女’, ‘保密’, ‘男转女’)
复制代码

增加枚举选项, 意味着修改表结构. 意味着大量的锁表操作要执行.

enum类型, 使用表关联实现

set的实现方案, 多对多关联表实现

hobby set(‘篮球1’, ‘羽毛球2’, ‘足球4’, ‘各种球8’)

7 = 蓝,羽毛,球足球

10 = 羽毛球, 各种球

实操实现

用户表

爱好表

用户和爱好的关联表:

ip地址, ipv4地址

192.168.93.128

ipv4 char(15)

实操时: int unsigned

ipv4可以很容易的转换成整数:

MySQL:

PHP:

1.6 强制增加注释

字段 类型 其他属性 comment “字段描述”

1.7 尽可能not null

null, 在mysql中, 是一个特殊的字段属性.

存储, 计算时, 都比较麻烦.

null存储时, 记录需要开辟额外的存储空间, 记录下来哪些字段可以为null.

计算时, is null, is not Null, 需要使用特殊的运算符. null 和谁计算结果都是null

通常都使用, 一个特殊的默认值表示

goods表:

category_id int unsigned null. 

category_id int unsigned not null default 0.
复制代码

实操时, 更加通用的方案是, 在分类表中, 强制增加一个: 未分类 特殊分类. 用于管理哪些, 不属于任何分类的商品.

分类表:

商品表

1.8 外键约束,foreign key

分类表:

商品表

保证 商品和分类的完整性, 在商品的分类ID字段上建立外键约束. 与分类表的id建立关联即可.

外键约束的目的: 保证2个表数据之间的关系完整. 

此目的, 工作, 在PHP层面(在应用程序层面), 也可以实现.

程序员(应用程序设计人员): 业务逻辑程序员来说, 能使用应用程序做到的, 都使用程序完成.

1.9 字符集尽量使用utf8

utf8mb4, 在utf8字符集的基础上, 增加4字节的字符.

emoji表情, 就是存储在mb4字符集中的字符!

gbk:

2 范式,NF,Normal,Format,规范的格式

设计表结构的一些规范,约束等.

根据约束的等级, 形成1NF, 2NF, 3NF…6NF.

常规的设计,要求满足3NF即可.
结论: 

在设计数据表时, 要满足下面的要求(重要)

1, 每种类型的数据, 使用独立的表进行存储.

2, 每张表, 存在一个独立的主键字段, id.最好与业务逻辑无关.. 主键字段独立, 仅有标志性, 没有业务逻辑性.

3, 数据间的关联联系, 使用关联字段进行处理. 一对多(关联字段), 多对多(关联表).

2.1 1NF,第一范式,原子性

要求, 设计的表, 满足字段的原子性, 字段不能再次分割.
例如, 讲师授课信息.

时间段这个列, 在逻辑上, 由2个数据构成: 开始和结束

如果程序, 需要独立的获取开始时间. 以上的表的设计, 及没有满足原子性. 

应该: 拆分成2个字段, 开始和结束.

以上的原子性的例子, 基于业务逻辑(功能).

注意: 关系型数据库,默认满足原子性, 满足第一范式

2.2 2NF,第二范式,消除部分依赖

在满足1NF的基础上, 要求消除对主键的部分依赖. 

主键, 记录的标志主键. 

依赖, A字段确定, 那么B字段的值也随之确定, 那么说B字段依赖于A字段.

下面的例子: 讲师代课信息的例子

设置主键: 需要使用联合主键, primary key (讲师, 班级)
可见:

性别, 由讲师即可确定

教室, 由班级即可确定

开始, 结束, 由主键(讲师+班级)确定

部分依赖: 性别和教室, 就是部分依赖. 如果字段依赖于联合主键中的一部分字段, 称之为, 对主键的部分依赖.

2NF, 要求表, 没有部分依赖. 

实现方式: 增加一个独立的字段主键.

此时, 对主键的部分依赖就消失了.

2.3 3NF,第三范式,消除传递依赖

在满足2NF的前提下, 消除对主键的传递依赖.

A依赖于B, B依赖与主键, 则A传递依赖来于主键.

上面的例子, 性别依赖于讲师, 讲师依赖与主键. 性别传递依赖于主键.

3NF的设计, 消除以上的传递依赖.

方案: 

将讲师, 与 班级 信息, 独立到不同的表中. 

关系使用关联字段进行表示, 形成的结构如下:

讲师:

班级:

讲师授课信息

1, 减少数据冗余.

不要出现重复的数据

2, 便于更新维护.

2.5 逆范式, 打破范式, 不满足范式

有时, 为了优化某些操作.. 可能增加数据冗余.(相当于缓存的概念)

goods

goods_id, category_id

category

category_id, title

例如, 在查询商品信息时, 每次都需要获取到商品所属分类.

商品, 某某分类

商品, 某某分类

常规的, 使用join查询完成.

select g.*, c.title from goods g left join category c on g.category_id=c.category_id.
复制代码

此时, 在goods表中, 增加category_title字段:

goods

goods_id, category_id, category_title

select * from goods
复制代码

即可.

以上, 就是逆范式.

通过逆范式的手段, 达到优化某些查询(操作)的目的.
慎做, 一旦打破方式, 需要通过业务逻辑补偿, 数据的完整性.

(任何更新分类名称的操作, 都需要去同时维护商品表)

3 存储引擎选择

(之前的问题: innodb还是myisam?) 目前的答案: innodb. 

(现在的问题: mysql还是mariaDb?)

MySQL, 支持多种存储引擎. storageengine, 数据和索引存储的不同方式, 不同的文件系统. 

ISAM文件系统, 被MysQL拿来存储数据, 形成了MyISAM.

innodb以插件的形式, 进入到MySQL

3.1 ## 1.1 show engines,查看所有的引擎

3.2 myisam

高速的存取引擎, 适合做以查询和插入为主的业务逻辑.

3.2.1 存储方式

3.2.1.1 数据和索引是分开存储到不同的文件中,myd, myi

形成的文件: .frm结构文件, .myd数据文件, .myi索引文件

3.2.1.2 记录是按照插入顺序存储的:

插入数据

直接获取不排序, 使用原生的顺序获取:

每当插入一个数据, 自动在表的末端, 进行插入(追加)即可. 插入速度特别快.

3.2.2 功能上

支持, 全文索引. 但是不支持中文. 
节省存储空间.

3.2.3 处理并发

仅支持, 表级锁定. 并发能力相对较弱.

并发插入.

3.3 innodb

复杂的业务逻辑更新, 频繁的update, delete, 适合innodb. 查询性能也不差.

3.3.1 存储方式

3.3.1.1 数据和索引, 集中存储在一个文件中, 称之为表空间文件 tablespace.

存储的结构, 2个文件..frm结构文件. .ibd表空间文件,数据和索引都在其中.

3.3.1.2 记录是按照主键顺序存储的:

插入数据

获取数据, 与插入不一致, 自动主键顺序排序.

意味着, 插入时, 自动完成排序工作. 相对较慢.

注意: 主键对于innodb来说, 非常重要. 数据是按照主键顺序排序, 一旦更改了主键, 数据需要重新排序, 慢. innodb表一定要使用与业务逻辑无关的字段, 作为主键.

3.3.2 功能上

支持: 事务(ACID), 外键.

事务, 外键, 都是在数据库层面保证数据完整性的问题.

1.3.3 处理并发

支持, 行级锁定. 也支持表级别锁定.

并发能力强.

4 锁定