范式：Normal Format，符合某一种级别的关系模式的集合，表示一个关系内部各属性之间的联系的合理化程度。一个数据库表之间的所有字段之间的联系的合理性。

学生表，班级表

• 范式是离散数学里的概念

• 范式目标是在满足组织和存储的前提下使数据结构冗余最小化

• 范式级别越高，表的级别就越标准

目前数据库应用到的范式有以下几层

• 第一范式：1NF

• 第二范式：2NF

• 第三范式：3NF

除此之外还有BCNF范式,4NF,5NF

一个数据库表设计的是否合理，要从增删改查的角度去考虑，操作是否方便

第一范式：确保表中每一列数据的原子性，不可再分！

 第二范式：在满足第一范式的基础上，确保列数据要跟主键关联，不能出现部分依赖。

第三范式设计表：再满足第二范式的基础上，保证每一列数据都要跟主键直接关联，不能出现传递依赖。

在设计数据库的时候，要在满足自己需求的前提下，尽可能的满足三大范式。

表间的关系

需求分析：

• 一对一关系： 一个学号对一个姓名
• 一对多（多对一）关系：一个老师多个班级 ，一个班级对多个学生
• 多对多关系： 学生对课程 ,用户购买商品

设计数据库时，多对多关系，需要一个中间表进行关联！

事务管理

为什么需要事务 （一件完整的事情）

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。即一组sql中哪怕有一条失败也会失败

例子: 转账操作,一个账户减钱，一个账户加钱

默认情况下，mysql的事务是自动提交的，也就是执行了增删改语句之后，数据直接持久化到磁盘上，不能撤销。

但是如果改为手动事务之后，更新过的数据，再没有使用commit提交时，可以通过rollback进行撤回。

数据库事务的原理

start transaction/begin;开始事务

rollback;回滚事务

commit;提交事务

什么是数据库事务

从开启到提交为一个事务。

由此可见，一个事务对应一组业务。一个事务中间可以有一条sql,多条sql。

所以 一个业务开始之前 开启事务 一个业务结束之后 提交事务。

我们这个转账案例：需要几个事务？

可以写成两个事务，但是不合适。因为我们的需求 让张三减的同时让李四加钱。只能写成一个事务。

事务的特征ACID 面试题

1、原子性（Atomicity）：

事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。

2、一致性（Consistency）：

​ 事务开始前和结束后，数据库的数据完整性约束没有被破坏，事务前后操作数据是一致的 。比如A向B转账，不可能A扣了钱，B却没收到。

能量守恒。

3、隔离性（Isolation）：

​ 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。

​ 两个事务之间是有隔离级别，隔离级别的不同会导致出现不同的问题。此时产生三种问题：脏读 幻读 不可重复读。

4、持久性（Durability）：

​ 持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

事务的并发问题

脏读：

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据

不可重复读：

事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。

幻读：

已知有两个事务A和B，A从一个表中读取了数据，然后B在该表中插入了一些新数据，导致A再次读取同一个表, 就会多出几行，简单地说，一个事务中先后读取一个范围的记录，但每次读取的纪录数不同，称之为幻象读

　　小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

目录

数据库范式

表与表的关系

表字段的设置

数据库表设计规范

创建表结构示例

左连接 右连接 内连接

DELETE TRUNCATE和DROP

数据库的优化

数据库表结构设计工具

---

数据库范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。目前数据库有六种范式：第一范式、第二范式、第三范式、巴斯-科德范式、第四范式、第五范式（又称完美范式）。

 第一范式：列不可再分，具有原子性，即数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。

 第二范式：在满足第一范式的前提下，每一列都依赖于主键，表中的每一个字段都依赖于主键。

 第三范式：在满足第二范式的前提下，每一列都直接依赖于主键，而不能通过其他列来间接的依赖主键。 三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。

表与表的关系

 一对一

 一对多

 多对多

表字段的设置

字段类型相关表字段是用来存储数据内容大小的，其大小的设置跟存储到磁盘分配的磁盘块多少有直接关系。

换算单位：1KB=1024B，1B=8bit(位);
数据存储是以二进制存储的，一个子节占8位，
例：十进制数 9大小占一个子节，用二进制表示为：0000 1001。
查看数据库表结构文件存放地址执行：show global variables like "%datadir%"。

 char(n) ：固定长度类型，char(10)，当你输入"abc"三个字符的时候，它们占的空间还是10个字节，其他7个是空字节。其优点是存取数据效率高，缺点是占用空间大，以空间换时间，适用场景：存储密码的md5值，固定长度的，使用char非常合适。

 varchar(n) ：可变长度，存储的值是每个值占用的字节再加上一个用来记录其长度的字节的长度。从空间上考虑varcahr比较合适；从效率上考虑char比较合适。

 float(m,d)：单精度浮点数， m显示长度，d表示小数点位数，占用空间4个字节。例如：float(4,2) 表示的范围是-99.99~99.99。

 decimal(m,d):m显示长度，d表示小数点位数。例如：decimal(5,2) 表示范围：-999.99 ~ 999.99。往数据库字段类型为float(10.8)、decimal(10,8)分别插入数据10.12345678，10.12345678，然后执行select * from 表,会发现float类型的字段显示数据为10.12345695，而decimal字段显示的数据为10.12345678，float单精度小数位数小于decimal，decimal如果不指定精度，默认为（10，0）。

 double：浮点类型有两种，分别是单精度浮点数（float）和双精度浮点数（double），定点类型只有一种，就是decimal，可以存储16位的十进制数，占空间8字节

数据库表设计规范

1.推荐utf-8字符集。

2.固定字符串的列尽可能多用定长char，少用varchar。

3.所有的InnoDB表都设计一个无业务用途的自增列做主键。

4.字段长度满足需求前提下，尽可能选择长度小的。

5.字段属性尽量都加NOT NULL约束（空的字段不能走索引，查询速度慢）对于某些文本字段，例如“省份”或者“性别”我们可以将他们定义为ENUM类型

6.尽可能不使用TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT的时候读性能太差。

7.读取数据时，只选取所需要的列，不要每次都SELECT * 尤其是读到一些TEXT/BLOB类型，确实需要的话，建议拆分到子表中，不要和主表放在一起，避免SELECT的时候读性能太差

8.对一个VARCHAR（N）列创建索引时，通常取其50%（甚至更小）左右长度创建前缀索引就足以满足80%以上的查询需求了，没必要创建整列的全长度索引。

9.多用复合索引，少用多个独立索引。

创建表结构示例

创建表：
    CREATE TABLE `user` (
      `id` INT AUTO_INCREMENT,
      `name` VARCHAR (20),
      PRIMARY KEY (`id`)
    );
UPDATE:
  UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
INSERT: 
  INSERT INTO 表名称 VALUES (值1, 值2,....)
  INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)
DELETE:
  DELETE FROM 表名称 WHERE 列名称 = 值
修改表结构：
    ALTER TABLE table_name add column_name datatype
    ALTER TABLE table_name drop COLUMN column_name
    ALTER TABLE table_name modify COLUMN column_name datatype

左连接 右连接 内连接

  INNER JOIN（内连接，或等值连接）：取得两个表中存在连接匹配关系的记录。

  select * from A inner join B on A.name = B.name;

  LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。

  select * from A left join B on A.name = B.name;
  select * from A left join B on A.name=B.name where A.id is null or B.id is null;

  RIGHT JOIN（右连接）：与LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。drop、truncate和delete的区别

DELETE TRUNCATE和DROP

 DELETE 对数据一行一行的删除，将每行的删除操作记录在日志中保存，以便进行回滚操作。

 TRUNCATE 一次性地从表中删除所有的数据不记录操作日志，数据不能恢复，删除的过程中不会激活与表有关的删除触发器。执行速度快。

 DROP 语句将表所占用的空间全释放掉。TRUNCATE和DELETE只删除数据， DROP则删除整个表（结构和数据）。

数据库的优化

硬件层

1.CPU（运算）：48核CPU。
2.内存：96G-256G，跑3-4个实例。
3.disk(磁盘IO)：机械盘：选SAS，数量越多越好。性能：SSD(高并发)>SAS(普通业务线上)>SATA(线下)
选SSD：使用SSD或者PCIe SSD设备，可提升上千倍的IOPS效率。随机IO：SAS单盘能力300IOPS 
SSD随机IO：单盘能力可达35000IOPS Flashcache HBA卡 
4.raid磁盘阵列：4快盘：RAID0>RAID1(推荐)>RAID5(少用)>RAID1。主库选择raid10，
从库可选raid5/raid0/raid10，从库配置等于或大于主库
5.网卡：使用多块网卡bond，以及buffer，tcp优化。千兆网卡及千兆、万兆交换机
数据库属于IO密集型服务，硬件尽量不要使用虚拟化

操作系统层面优化

1.选择x86_64系统，推荐使用CentOS6.8 linux，关闭NUMA特性
2.将操作系统和数据分开（逻辑上和物理上）
3.避免使用Swap交换分区
4.避免使用软件磁盘阵列
5.避免使用LVM逻辑卷
6.删除服务器上未使用的安装包和守护进程

网络优化

#优化系统套接字缓冲区
#Increase TCP max buffer size
net.core.rmem_max=16777216 #最大socket读buffer
net.core.wmem_max=16777216 #最大socket写buffer
net.core.wmem_default = 8388608 #该文件指定了接收套接字缓冲区大小的缺省值(以字节为单位)
net.core.rmem_default = 8388608
#优化TCP接收/发送缓冲区

# Increase Linux autotuning TCPbuffer limits
net.ipv4.tcp_rmem=4096 87380 16777216
net.ipv4.tcp_wmem=4096 65536 16777216
net.ipv4.tcp_mem = 94500000 915000000927000000
#优化网络设备接收队列
net.core.netdev_max_backlog=3000

数据库层面优化

 my.cnf参数优化 此优化主要针对innodb引擎，default-storage-engine=Innodb，

innodb_buffer_pool_size:指定用于索引的缓冲区大小
innodb_file_per_table = 1：使用独立表空间
innodb_data_file_path = ibdata1:1G:autoextend,不要使用默认的10%
innodb_log_file_size=256M,设置innodb_log_files_in_group=2，基本可满足90%以上的场景；
innodb_log_file_size
long_query_time = 1记录那些执行较慢的SQL，用于后续的分析排查；
max_connection（最大连接数）max_connection_error（最大错误数，建议设置为10万以上，而open_files_limit、innodb_open_files、table_open_cache、table_definition_cache这几个参             数则可设为约10倍于max_connection的大小；）不要设置太大，会将数据库撑爆 
tmp_table_size = 64M ：设置内存临时表最大值。如果超过该值，则会将临时表写入磁盘，其范围1KB到4GB。
max_heap_table_size = 64M：独立的内存表所允许的最大容量。
table_cache = 614：给经常访问的表分配的内存，物理内存越大，设置就越大。调大这个值，一般情况下可以降低磁盘IO，但相应的会占用更多的内存,这里设置为614。

sql语句的优化

索引的优化： DBA参与，抓出慢SQL,减少上线后的慢SQL数据,

配置my.cnf long_query_time = 2 log-slow-queries=/data/3306/slow-log.log log_queries_not_using_indexs 按天轮询：slow-log.log

慢查询的日志分析工具——mysqlsla或pt-query-digest（推荐） pt-quey-diges,mysqldumpslow,mysqlsla,myprofi,mysql-explain-slow-log,mysqllogfileter 3)

每天晚上0点定时分析慢查询，发到核心开发，DBA分析，及高级运维，CTO的邮箱里 DBA分析给出优化建议-->核心开发确认更新-->DBA线上操作处理

定期使用pt-duplicate-key-checker检查并删除重复的索引

定期使用pt-index-usage工具检查并删除使用频率很低的索引

使用pt-online-schema-change来完成大表的ONLINE DDL需求

有时候MySQL会使用错误的索引，对于这种情况使用USE INDEX

使用explain及set profile优化SQL语句

数据库表结构设计工具

• CHINER元数建模:http://chiner-release.httpchk.com/CHINER-win_v3.5.7.rar
• Excel文档表结构设计生成建表语句函数：=""&B2&""&" "&C2&" "&IF(F2="Y","NULL","NOT NULL")&IF(D2="Y"," "&"PRIMARY KEY ","")&"COMMENT="""&E2&""","
• vba工具

数据库三大范式

数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数据库是简洁的、结构明晰的，同时，不会发生插入（insert）、删除（delete）和更新（update）操作异常。

1.第一范式（1NF）:列不可再分

1.每一列属性都是不可再分的属性值，确保每一列的原子性

2.两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

 

2.第二范式（2NF）属性完全依赖于主键

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主键

 

 

3.第三范式（3NF）属性不依赖于其它非主属性    属性直接依赖于主键

数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。像：a-->b-->c  属性之间含有这样的关系，是不符合第三范式的。

比如Student表（学号，姓名，年龄，性别，所在院校，院校地址，院校电话）

这样一个表结构，就存在上述关系。 学号--> 所在院校 --> (院校地址，院校电话)

这样的表结构，我们应该拆开来，如下。

   （学号，姓名，年龄，性别，所在院校）--（所在院校，院校地址，院校电话）

总结：三大范式只是一般设计数据库的基本理念，可以建立冗余较小、结构合理的数据库。如果有特殊情况，当然要特殊对待，数据库设计最重要的是看需求跟性能，需求>性能>表结构。所以不能一味的去追求范式建立数据库。