数据库相关知识点总结

范式

第一范式（1NF）

字段不可分，强调表的原子性。

 

反例：

第二范式（2NF）

就是要有主键，要求其他字段都依赖于主键。

表中的字段必须完全依赖于全部主键而非部分主键

第三范式（3NF）

就是要消除传递依赖，数据只在一个地方存储，不重复出现在多张表中。

非主键外的所有字段必须互不依赖

索引

1、B+树：

B+树 结构：

1、其他节点只存键，不保存数据，2、所有叶子节点存键和值，3、从小到大构成一个链表

 

B+树 优点：

1. 查询稳定：因为只有叶子存值，所以每次都遍历到叶子节点。
2. 减少磁盘IO：节点存元素更多，IO次数少。
3. 方便范围查询：因为值是在叶子节点上，结构是链表，方便范围查询

 

数据库为什么用B+树而不用B树：

1、B树只适合随机检索，而B+树同时支持随机检索和顺序检索；

2、B+树空间利用率高，可减少I/O次数

3、B+树的查询效率更加稳定，每次查询到叶子节点（叶子节点存值）。

4、B+树遍历效率高，因为数据在叶子节点构成的链表上

5、增删改快，因为B+是数据存链表上

 

2、索引类型（聚集、非聚集）：

·  聚集索引：

表中各行的物理顺序与键值的索引顺序相同，每个表只能有一个

 

·  非聚集索引：

非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个

 

3、MyISAM和InnoDB

此处可查看：https://blog.youkuaiyun.com/hguisu/article/details/7786014

MyISAM 索引

非聚集索引。采用B+树，叶子节点保存数据地址，一个保存地址，一个保存数

据

 

特点：OLAP、不支持事物、支持全文索引

InnoDB 索引

聚集索引。采用B+树，叶子节点保存数据，本身就是一个主索引

 

特点：OLTP、支持事物、表空间大、支持行锁

二者对比图：

4、创建索引时需要注意什么？

避免非空字段（NULL）：索引以及比较运算更加复杂。

索引字段越小越好：IO操作获取效率高。

5、最左匹配原则

mysql的查询器，会从最左开始向右匹配，直到遇到范围查询就停止匹配(>、<、between、like)，右边的索引不生效。（因为底层是B+树，从左到右建立搜索树的）

 

例子：

a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

 

事物

1、数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。

2、四大特性(简称ACID)

数据库如果支持事务的操作，那么就具备以下四个特性：

 

    原子性(Atomicity)

    事务是数据库的逻辑工作单位，事务中包括的诸操作要么全做，要么全不做。

    一致性(Consistency)

    事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。

    隔离性(Isolation)

    一个事务的执行不被其他事务干扰。

    持续性/永久性(Durability)

    一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。

3、事务隔离级别

 

脏读（中间有事物取消）：（1更新->2读->1回滚 ===》 2不准确）

一个事务读到了另一个事务中尚未提交的数据

 

不可重复读（中间有条更新）：（1查询->2更新->1查询 ===》 1不一致）

在一个事务的两次读取的数据不一致，这可能是两次查询过程中间插入了一个事务更新update的原有的数据。

 

幻读（中间列更新）：

一个事务中两次读取的数据的数量不一致，要求在一个事务多次读取的数据的数量是一致的，这是insert或delete时引发的问题

4、MySQL数据库有四种隔离级别

上面的级别最低，下面的级别最高。

 

数据库-锁

1、分类：

从数据库系统角度分为三种：排他锁、共享锁、更新锁。
从程序员角度分为两种：一种是悲观锁，一种乐观锁。

 

 

2、悲观锁

一、按行为划分

1）共享锁（S）

读锁，用于所有的只读数据操作，可并发读

2）排它锁（X）

写锁，和试衣间类似，换衣服上锁，换完出来让出锁来。只能一个事物

3）更新锁（U）

修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁升级排它锁造成的死锁现象

 

更新锁实现步骤：

1. 用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；
   2. 当被读取的页要被更新时，则升级为X锁；
   3. U锁一直到事务结束时才能被释放。

 

二、按锁作用范围划分

1）行锁

锁的作用范围是行级别。 ... where id =xxx  (id是主键，知道用哪个行，用行锁)

2）表锁

锁的作用范围是整张表。 ... where name =xxx  (name不是主键，不知道用哪个行，锁整个表)

 

 

3、乐观锁

每次去拿数据的时候都认为别人不会修改，所以，不会上锁。但是在更新的时候会判断一下在此期间别人有没有更新这个数据，可以使用版本号等机制。

1）版本号 （新建一个字段version来+1操作）

2）时间戳 （同版本号，只是改为时间戳）

3）待更新字段 （更新提交前，查看现在读的和之前读的值是否一致，不一致则更不更新）

活锁、死锁（并发）

活锁：

多个事物封锁同一数据。  解决：先来先服务

 

死锁：

我锁你，你又锁我。  预防：1、一次全封锁法 2、顺序封锁法

判定死锁的方法：

1、超时法

2、等待图法：事务等待图出现了回路。

 

 

主从复制（binlog）

将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

MYSQL binlog格式

binlog的格式有三种：STATEMENT、ROW、MIXED 。

1、STATEMENT：基于SQL语句的复制 ，IO小、主从数据可能不一致

2、ROW：基于行的复制，IO大，数据保证一致

3、MIXED：二者混用

数据库优化：（包含索引失效）

1. sql语句优化

1. 最左前缀规则，而导致索引失效
2. 避免select *
3. 不用NULL，导致索引失效
4. != 和 <> 导致索引失效
5. like不要 %abc ，导致索引失效
6. explain  select 分析查询语句
7. 连接来代替子查询
8. 对于经常查询的，可开启缓存

2、索引优化

1）建立索引

2）避免索引失效：like %、NULL、!=、最左前缀

3）索引中，重复数据不要太多

4）Order by与group by字段最好是索引字段。

 

3、表优化

1）水平切分，分库分表，mycat

2）垂直切分，分布式系统

3）对于常查询的，建立中间表

4）尽量使用数字类型

4、其他优化

1）第二缓存，redis

2）集群、读写分离

3）配置参数优化（增加最大连接数）

4）开启慢查询，查看相关的来优化