文章目录
一、三范式与反范式
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