Mysql高级-范式、索引、事务、锁

一、三大范式

1、原子性

确保每列的原子性，即每个字段不能再拆分为多列。
比如：通信工程A班，可以拆分为通信工程和A班两列，就不符合第一范式原子性

2、完全依赖主键

一张表代表一种内容，描述一件事。
比如说学生ID，宿舍楼ID，宿舍房间ID的字段，其中第三个字段是依赖宿舍楼ID的而不依赖主键学生ID，就不符合第二范式

2.1 两张表的关系，在第三张表得以体现

比如学生和课程表。为多对多的关系。这种关系需要在第三张表中体现

3、直接依赖主键，非传递依赖

确保每列都和主键列直接相关而非间接相关，其他列和列之间不存在相互依赖关系。
比如说，前面几列是学生的基本信息，然后后几列是学生的班主任和班主任年龄，后几列也依赖于学生的基本信息，但是是通过班主任传递依赖的，因此不符合第三范式

二、索引

索引概念

1、什么是索引？

本质：是一种辅助检索数据的数据结构，采用的是B+平衡树；
B+平衡树：数据存放在叶子节点上，非叶子节点不存储数据；非叶子节点只存储key值，深度比较低；支持range检索；

2、索引的创建和查看

查看已创建的索引：show index from 表名;
删除索引：alter table 表名 drop key 索引名

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理sql语句的。用来分析查询语句或是表结构的性能瓶颈：explain select * from student

3、索引失效的原因

1、违反左前缀原则；
2、模糊查询：使用like ‘…’
3、在索引上进行运算：加减乘除
4、用or时后面的字段如果没有建立索引，前面的索引也就不会生效
5、in null，is not null，not in等排除法查询
…

4、适合建立索引的字段

1、经常作为条件查询的字段；
2、数据不能太小

5、索引的分类

主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
主键索引是最快的，其他索引都是二级索引，借助于聚簇索引（在MySQL中主键索引就是聚簇索引）
唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。

创建唯一索引：alter table 表名 add unique (字段名); 
创建唯一组合索引：alter table 表名 add unique (字段名1,字段名2); 

-- 创建 唯一 索引 索引名字 on 表名(字段)
create unique index ind_eventtype_eventid on dim_eventtype(eventid);

普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。

创建普通索引：alter table 表名 add index 索引名(字段名);
创建组合索引：alter table 表名 add index 索引名(字段名1, 字段名2, 字段名3);

全文索引： 是目前搜索引擎使用的一种关键技术。

创建全文索引：alter table 表名 add fulltext (字段名);

三、事务

数据库事务具备四大特性ACID，锁是事务特性的保障

1、Atomicity原子性：

指事务是一个最小单元，不可再分隔。事务包含的所有操作要么全部成功，要么全部失败回滚，因此事务的操作如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响。

2、Consistency一致性：

事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。转账的总和总是一致的。

3、Isolation隔离性：

是指当多个事务操作数据库中同一个记录或多个记录时，对事务进行隔离开来有序执行，避免同时对同一数据做操作。这时候就需要使用锁来解决这个问题了。

4、Durability持久性：

当成功插入一条数据库记录时，数据库必须保证有一条数据永久的写入到数据库磁盘中。

set autocommit=0;--关闭自动提交：打开事务
commit;#提交，从内存读到文件--提交结束
rollback;#回滚，未提交的修改则被恢复--回滚后结束事务

事务的四大特征中，所有的操作都会走向磁盘，所以：
持久性是事务操作的目的，
而原子性是实现事务的基础，
隔离性是实现数据安全达到一致性的一种策略、手段，
而最终维护的，就是数据的一致性，一致性才是事务中最重要的。

四、锁(InNoDB存储引擎)

并发事务时，可能会导致数据不一致。因此，锁是保证事务操作先后顺序的一种机制
select查询时，查的是快照

1、共享锁：读，其他线程只能读，不能写，mysql默认读不加锁

在查询语句后面增加lock in share mode，MySQL 就会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。
可以理解为：一件商品，可以多个人同时查看

2、排他/独占锁：写，其他线程不能读，也不能写

在查询语句后面增加for update，MySQL 就会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞
在查询后面追加共享锁：for update
可以理解为：一件商品，被一个人租用后，其他人不能看也不能使用了，只能等租期到了才能用

五、缓冲池
冷热数据区