MySQL相关

一、三范式与反范式

1.三范式

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性，对于没有包含在主键中的列（非主键的其他列）必须完全依赖于主键，而不能只依赖于主键的一部分（比如某一个主键）。；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余（不能存在传递依赖）。

2.范式与反范式

范式具备以下优点：
避免数据冗余，减少维护数据完整性的麻烦；
减少数据库的空间；
数据变更速度快。
同时，也有如下缺点：
按照范式的规范设计的表，等级越高的范式设计出来的表数量越多。
获取数据时，表关联过多，性能较差。
表的数量越多，查询所需要的时间越多。也就是说所用的范式越高，对数据操作的性能越低。
反范式设计就是用空间来换取时间，提高业务场景的响应时间，减少多表关联。主要的优点如下：
允许适当的数据冗余，业务场景中需要的数据几乎都可以在一张表上显示，避免关联；
可以设计有效的索引。

二、索引

1.索引简介

什么是索引？
在关系数据库中，索引是一种单独的，物理的对数据库表中一列或者多列的值进行排序的一种存储结构。相当于图书的目录，可以根据目录中的页码快速找到需要的内容。

索引的作用

• 快速读取数据；
• 加快orderby、groupby速度；
• 保证数据记录的唯一性；

2.索引分类

主键索引
主键
唯一索引
索引列中的值必须唯一，允许空值。
普通索引
没有限制，可以有重复值和空值。
组合索引
多个字段组合创建索引，要注意最左前缀原则。

3 .常见索引

B+Tree索引
数据结构
数据都存在叶子结点，非叶子结点不保存数据，只负责查找。
叶子结点有顺序，之间相互有连接。
插入删除操作会破坏平衡树的平衡，因此在操作后需要对树进行分裂、合并和旋转等操作来维护平衡性。

InnoDB的B+ Tree索引
是MySQLInnoDB默认的索引类型。
不需要进行全表扫描，只需要对树进行搜索，提高了查找速度。
分为主索引和辅助索引，主索引的叶子结点data域记录着完整数据记录，这种索引称为聚簇索引，一个表只有一个聚簇索引；辅助索引的叶子结点data域记录着主键的值，使用辅助索引，需要先查到主键值，再到主索引中查找。

哈希索引
哈希索引能以O(1)时间进行查找，但是失去了有序性。
note：
InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优点。
缺点
无法用于排序和分组；
只支持精确查找，无法用于范围查找。3.其他索引
全文索引、空间数据索引。

4.创建索引的方式

在建表同时添加索引
index index_no(no)（普通索引）
unique index index_no(no)（唯一索引）
key index_no_name(no,name)（多列索引）

以修改的方式添加索引
alter table t_dept add index index_name(name);
alter table t_dept add unique index_name(name);
alter table t_dept add index index_name_no(name,no);
修改主键：
ALTER TABLE test2 DROP PRIMARY KEY ,ADD PRIMARY KEY ( id )

以添加的方式添加索引
create index index_name on t_dept(name);

alter和create添加索引的区别：

• CREATE INDEX必须提供索引名，对于ALTER TABLE，将会自动创建，如果你不提供；
• CREATE INDEX一个语句一次只能建立一个索引，ALTER TABLE可以在一个语句建立多个，如：
  ALTER TABLE HeadOfState ADD PRIMARY KEY (ID), ADD INDEX (LastName,FirstName);
• 只有ALTER TABLE 才能创建主键，

5.适合建立索引的列

• 适合建立索引的列经常搜索的列；
• 经常用于表连接的列；
• 经常进行范围搜索的列；
• 经常需要排序的列；
• 在where子句中经常出现的列；

三、事务

1.事务简介

事务是逻辑上的一组操作，要么都执行成功，要么都不执行。
举例：转账
小明给小红转账1000元，两个操作：
小明余额减1000，小红余额增加1000元。
事务会把两个操作看做逻辑上一个整体，这个整体包含的操作要不都成功，要不都失败。

2.事务的特性

原子性（Atomicity） ： 事务是最小的执行单位，一个事务中的所有操作要么全部完成，要么全部不完成，不会结束于中间某个环节。
一致性（Consistency）： 执行事务前后，数据保持一致，例如转账业务中，无论事务是否成功，转账者和收款人的总额应该是不变的；
隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
持久性（Durabilily）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

3.事务特性如何实现

原子性：
InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
持久性：
如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
隔离性：
两方面：
(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性
一致性：
保证了事务的持久性、原子性和隔离性后，一致性才能保证。

4.并发事务带来的问题

脏读：当前事务(A)中可以读到其他事务(B)未提交的数据（脏数据），这种现象是脏读。
不可重复读：在事务A中先后两次读取同一个数据，两次读取的结果不一样，这种现象称为不可重复读。
幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，这种现象称为幻读。

5.事务的隔离级别

在大多数数据库系统中，默认的隔离级别是读已提交(如Oracle)或可重复读（MySQL）。

RR级别下的并发问题
解决幻读的方式 MVCC+间隙锁。
可重复隔读离级别下MVCC解决快照读的幻读，gap lock和next-key lock解决当前读的幻读。
GAP lock锁和next-key lock锁的引入，可以帮助解决幻读。
GAP lock锁是指对索引记录的间隙上锁；
next-keylock是间隙锁和行锁的结合版，是前开后闭区间，查找过程中访问到的对象才会加锁（锁定一个范围并且锁定记录本身 ）。

5.锁机制

隔离性要求同一时刻只能有一个事务对数据进行写操作，InnoDB通过锁机制来保证这一点。
锁机制的基本原理可以概括为：事务在修改数据之前，需要先获得相应的锁；获得锁之后，事务便可以修改数据；该事务操作期间，这部分数据是锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁。

行锁与表锁
按照粒度，锁可以分为表锁、行锁以及其他位于二者之间的锁。表锁在操作数据时会锁定整张表，并发性能较差；行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。
MySQL中不同的存储引擎支持的锁是不一样的，例如MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。

6.MVCC

RR解决脏读、不可重复读等问题，使用的是MVCC：MVCC全称Multi-Version Concurrency Control，即多版本的并发控制协议。

MVCC实现
1）隐藏列：InnoDB中每行数据都有隐藏列，隐藏列中包含了本行数据的事务id、指向undo log的指针等。

2）基于undo log的版本链：前面说到每行数据的隐藏列中包含了指向undo log的指针，而每条undo log也会指向更早版本的undo log，从而形成一条版本链。

3）ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据ReadView来确定。所谓ReadView，是指事务（记做事务A）在某一时刻给整个事务系统（trx_sys）打快照（快照读，发生在事务的第一个select），之后再进行读操作时，会将读取到的数据中的事务id与trx_sys快照比较，从而判断数据对该ReadView是否可见，即对事务A是否可见。

事务系统trx_sys中的主要内容，以及判断可见性的方法如下：

low_limit_id：表示生成ReadView时系统中应该分配给下一个事务的id。如果数据的事务id大于等于low_limit_id，则对该ReadView不可见。
up_limit_id：表示生成ReadView时当前系统中活跃的读写事务中最小的事务id。如果数据的事务id小于up_limit_id，则对该ReadView可见。
rw_trx_ids：表示生成ReadView时当前系统中活跃的读写事务的事务id列表。如果数据的事务id在low_limit_id和up_limit_id之间，则需要判断事务id是否在rw_trx_ids中：如果在，说明生成ReadView时事务仍在活跃中，因此数据对ReadView不可见；如果不在，说明生成ReadView时事务已经提交了，因此数据对ReadView可见。

7.当前读与快照读

当前读, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题。

参考资料
https://www.cnblogs.com/kismetv/p/10331633.html