【笔记】MySQL的理解篇

一 数据库概念

1. 数据库三范式

（1）第一范式

解释：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。

理解：属性不可以再分割。（例如：时间属性，不可切割为创建时间和修改时间两个属性）

（2）第二范式

解释：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

理解：所有的非主属性都依赖于每一个主属性。（例如：主键是【姓名，性别】，其他的属性必须与这两个属性有依赖关系）

（3）第三范式

解释：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.

理解：非主属性不能依赖于其他非主属性，消除传递依赖。

2. 数据库事务

（1）事务四大特性

    原子性：表示组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交。事务中的任何一个数据库操作失败，已经执行的任何操作都必须被撤销，让数据库返回初始状态。

    隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，他们的操作不会对对方产生敢逃。准确地说，并非要求做到完全无干扰。数据库规定了多种事务隔离界别，不同的隔离级别对应不用的干扰程度，隔离级别越高，数据一致性越好，但并发行越弱。

    一致性：事务操作成功后，数据库所处的状态和他的业务规则是一致的，即数据不会被破坏。如A账户转账100元到B账户，不管操作成功与否，A和B账户的存款总额是不变的。

    持久性：一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中。即使在事务提交后，数据库马上崩溃，在数据库重启时，也必须保证能够通过某种机制恢复数据。

（2）事务的隔离级别

     读未提交：脏读。最低级别

     读已提交：可避免脏读。

       原理：MySQL的MVVC机制。（第二章节提及）通过MySQL的MVVC机制，可以避免脏读。

     可重复读：避免不可重复读和幻读问题。

       举例1：A事务执行 (1) select id, name from tb_anbal t where t.id = 1; (2) select id, name from tb_anbal t where t.id = 1

                 B事务执行  update tb_anbal t set t.name = 'curry' where t.id = 1.

                 A事务执行（1）操作后，B事务执行。接着A事务执行（2）操作。 在可重复读隔离级别下，A事务（1）操作和（2）操作的name字段值是一样的。

             举例2：A事务执行 (1) update tb_anbal t set t.name = 'curry' where t.id = 1; 

                                    (2) update tb_anbal t set t.name = 'curry' where t.id = 1;

                 B事务执行  insert into tb_anbal(id, name) values(1, "anbal");

                 A事务执行（1）操作后，B事务执行。接着A事务执行（2）操作。 在可重复读隔离级别下，name变为 curry。

       原理：举例1中，由于MySQL的MVVC机制，可以解决不可重复读问题。

                  举例2中， B事务执行时，会触发 gap锁和next-key锁，事务提交后释放。当B事务执行时，A事务（2）由于互斥锁，导致不能执行，事务B之后提交，A事务执行。

     串行化：表锁。

（3）思考

    Q1：原子性和一致性的区别是什么？

    原子性表示的是要么一起成功，要么一起失败。而一致性表示的是数据在一次事务完成之后仍然是准确的。什么意思呢！举个例子。以转账为例，一共需要执行两步骤操作。A账户-100，B账户+100。当两个操作都执行成功的时候，表示事务的原子性得到了满足。但是事务不一定满足了一致性。因为如果这个时候，又有一个事务给B+100。在不考虑事务隔离级别的情况下，得到的答案应该是错误的。所以其实一致性是通过原子性和隔离性等来保证的。

二. MySQL的锁机制

1. Mysql的MVCC机制

（1）什么是MVCC

即是多版本并发控制。其实就是乐观锁的一种实现方式。

（2）基本原理

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

每一行数据都有额外两列（创建时版本号，删除时版本号）。版本号指的是系统版本号。每当开启新的事务，系统版本号都会递增。事务开始时刻的系统版本号会作为事务版本号，用来和每行查询记录的版本号进行对比。每个事务又有自己的版本号，这样事务内执行CRUD操作时，就通过版本号的比较来达到数据版本控制的目的。

（3）MVCC的使用

InnoDB采用了MVCC(Multiversion Concurrency Control)多版本并发控制，避免不可重复读。MVCC只在 READ COMMITTED  和 REPEATABLE READ  2个隔离级别下工作。MVCC会建立多个ReadView，RC是语句级多版本(事务的多条只读语句，创建不同的ReadView，代价更高)，RR是事务级多版本(一个事务有一个ReadView)。由于RC中未提交的事务，不允许其他事务看到，但是本身事务需要看到，所以执行多条语句时，都需要建立新的ReadView，以支持本事务后面的查询。

 2. MySQL的锁机制

（1）锁的分类

共享/排他锁

共享锁（Share Locks，记为S锁），读取数据时加S锁

排他锁（eXclusive Locks，记为X锁），修改时加排它锁

共享锁之间不会互斥，读读是可行的。

排它锁和任何锁都是互斥，写读和写写都不可行。

可以发现，一旦带有写操作的事务没有完成时，该数据是不能被其他事务所读取的，这对并发度有较大的影响。写事务没有提交时，加了读锁的事务是会被阻塞的。普通的select基于快照读，只有带了读锁的select才会被阻塞。

（2）意向锁

InnoDB为了支持多粒度锁机制，即允许表级锁和行级锁同时存在，引入了意向锁。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向是一个表级别的锁（table-level locking）

意向锁又分为：意向共享锁（IS）和意向排他锁（IX）

意向锁是为了解决锁类型的锁共存问题。（具体事例见Q1）

正常来说数据库是如何解决Q1的这个冲突的呢？

Step1：判断表是否已被其他事务锁表

Step2：判断表中的每一行是否已被行锁锁住。

Step2中通过遍历查询，这样的判断方法效率实在不高，因为需要遍历整个表。于是，就产生了意向锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。在意向锁存在的情况下，上面的判断可以改成

Step1：不变

Step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

最终结论：

（1）申请意向锁的动作是数据库完成的，就是说，事务A申请一行的行锁的时候，数据库会自动先开始申请表的意向锁，不需要开发者指定。

（2）IX，IS是表级锁，不会和行级的X, S锁发生冲突。只会和表级的X, S发生冲突。

Q1：为什么没有意向锁的话，表锁和行锁不能共存？

假设表锁和行锁可以共存。场景如下：

事务A锁住表中的一行（写锁），事务B锁住整个表（写锁）。

这样会有一个很明显的问题：当“事务A锁住一行的时候”与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，行锁和表锁共存就会有问题。

（3）记录锁（Record Locks）

例如执行如下Sql：

SELECT * FROM TB_CONFIG WHERE ID = 1 FOR UPDATE;

此时先获取该表的意向排它锁，再获取这行记录的排它锁。防止其他事务插入，更新，删除id=1这行数据。此时锁定了一条记录。

（4） 间隙锁(Gap Locks)

间隙锁，它封锁索引记录中的间隔，或者第一条索引记录之间的范围，又或者最后一条索引记录之后的范围。

间隙锁可以解决会出现的幻读问题。

（5） 临键锁(Next-Key Locks)

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

默认情况下，innodb使用next-key locks来锁定记录。但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。

假设，在 T_USER表中，AGE字段为普通索引。执行如下SQL语句。

SELECT * FROM T_USER T WHERE T.AGE = 12 FOR UPDATE;

由于AGE不是主键索引，所以此时就是NEXT-KEY LOCK。假设表中有年龄的数据是【8,15】此时执行如下的SQL语句，在RR隔离级别下，会发生阻塞行为。

INSERT INTO T_USERNAME(ID, USERNAME, AGE) VALUES(1, 'ANBAL', 10);

发生阻塞的原因很简单，由于加了NEXT-KEY LOCK，(8, 12]这个区间是有锁的。此时你插入的年龄为10，在此区间范围内，所以不允许插入。

（6）插入意向锁(Insert Intention Locks)

对已经已有的数据的修改或者删除，需要互斥锁（X）。但是对于数据的插入是不需要加互斥锁的。插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

（7）自增锁(Auto-inc Locks)

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

很好理解。为了维持一定的顺序关系，在两个事务有并发关系的时候，必须要等一个事务提交之后，另一个事务才能执行。

三. MySQL的索引策略

1. 索引概念

（1）什么是索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

MySQL中最常见的就是B+ Tree结构。

索引是帮助MySQL高效获取数据的排好序的数据结构。

索引的数据结构一般有：二叉树、红黑树、Hash表、B-Tree

（2）B+ Tree结构

B+Tree索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最频繁的索引。B+Tree中的B代表的是Balance。

说到B+Tree就先说一下B-Tree。

B-Tree：

B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。

（1）叶节点具有相同的深度，叶节点的指针为空

（2）所有索引元素不重复

（3）节点中的数据索引从左到右排序。

B+Tree：

（1）非叶子节点不存储data，只存储索引（冗余），可以放更多的索引。

（2）叶子节点包含所有索引字段

（3）叶子结点用指针连接，提高区间访问的性能。

系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。

InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

show variables like 'innodb_page_size';

（3）MySQL的存储引擎

1. MyISAM引擎

  MyISAM索引文件和数据文件是分离的。

  执行流程

执行以下SQL语句，在MyISAM中的执行流程如下：

SELECT * FROM TB_ANBAL T WHERE T.ID = 20;

（1）首先判断 ID 是否为索引字段。

（2）为索引字段则去 .myi文件查找该索引字段，获取实际数据地址。

（3）根据实际地址去.myd文件中将数据 Load 出来。

2. Innodb引擎

（1）表数据文件本身就是B+Tree组织的一个索引文件结构。

（2）聚集索引-叶节点包含了完整的数据记录。

（3）为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

（4）为什么非主键索引结构叶子节点存储的是主键值？（一致性和节省空间）

联合索引

假设建立联合索引（a, b, c）

索引排序时，首先排序 a字段，如果a相同，则排序b字段。依次类推。

四. Expain分析实战

1. 准备工作

  （1）实验环境

 运行环境：centos7  mysql5.8 

 SQL脚本：如下所示

DROP TABLE IF EXISTS `student`; 
CREATE TABLE `student`(
`id` bigint(14) NOT NULL,
`name` varchar(45) NOT NULL, 
`sex` CHAR(1) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
INSERT INTO `student`(`id`,`name`,`sex`)VALUES (201921190203, 'Alice', 'W'),(201921140908, 'Bob', 'M'),(201921305566, 'Cali', 'W'); 

DROP TABLE IF EXISTS `course`; 
CREATE TABLE `course`(
`id` int(11) NOT NULL , 
`name` varchar(10) NOT NULL, 
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 
INSERT INTO `course`(`id`,`name`)VALUES(1001,'math'),(2003,'English'),(2005,'physics'); 

DROP TABLE IF EXISTS `student_course`;
CREATE TABLE `student_course`(
`id` int(11) NOT NULL,
`student_id` bigint(14) NOT NULL,
`course_id` int(11) NOT NULL,
`remark` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `idx_student_course_id` (`student_id`,`course_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student_course`(`id`,`student_id`,`course_id`,`remark`)VALUES(1,201921140908,1001,'good'), (2,201921190203,2005,'not bad'),(3,201921305566,2005,'bad');

   

  （2）操作步骤

 a. 执行 docker ps -a 找到创建的mysql容器。

 b. docker start container_id

 c. 创建连接，执行sql脚本

 

2. 字段含义解释

执行以下的SQL语句

EXPLAIN SELECT * FROM student WHERE ID = 201921190203;

结果如下图所示：

从上图中的每个字段去分别解释所代表含义：

  （1）id

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。
id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

  （2）select_type

select_type 表示对应行是简单还是复杂的查询。

1）simple: 简单查询。查询不包含子查询和union ，如下图的根据主键查询。

2）primary: 复杂查询中最外层的 select

3）subquery: 包含在 select 中的子查询(不在 from 子句中)

4）derived: 包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)

执行以下SQL可区分subquery 和 derived

set session optimizer_switch='derived_merge=off';

EXPLAIN SELECT
	( SELECT 1 FROM student WHERE id = 201921305566 ) 
FROM
	( SELECT * FROM student WHERE id = 201921305566 ) derivDb;

 从以上结果可以看出，首先执行 SELECT 的子查询，接着执行 FROM 语句的子查询（派生表查询）。

5）union

  3）table列

这一列表示 explain 的一行正在访问哪个表。
当 from 子句中有子查询时，table列是"derivenN"格式，表示当前查询依赖 id=N 的查 询，于是先执行 id=N 的查询。
当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

  4）type列

这一列表示关联类型或者访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为

1) system

2) const：

mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例。

3) eq_ref：

primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的select 查询不会出现这种 type。

例如：explain select * from student_course left join student on student_course.student_id = student.id;

4) ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要 和某个值相比较，可能会找到多个符合条件的行。

例如：explain select * from student t where t.name = "anbal". （此时name不是唯一索引）

例如：explain select student_id from student left join student_course on student.id = student_course.student_id;

5) range

范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定 范围的行。

6) index

扫描全表索引，这通常比ALL快一些。

7) all

即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了。

 

一般来说，得保证查询达到Range级别，最好达到Ref。如果为空，在执行阶段用不着再访问表或索引。例如:在 索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。