Java 4.2 - MySQL

MySQL 基础

关系型数据库

关系型数据库就是建立在关系模型上的数据库。关系模型描述了实体属性以及实体和实体之间的关系。

在关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一行存放着一条数据。

常见的关系型数据库：MySQL、Oracle、SQL Server、SQLite

MySQL 简介

MySQL 是一种关系型数据库，可以持久化存储我们系统中的一些数据，例如用户数据。

MySQL 是开源免费且比较成熟的数据库，被应用在各种系统中。MySQL 的默认端口为 3306。

MySQL 基础架构

连接器：身份验证和权限相关

查询缓存：执行 select 语句时，先查询缓存（8.0后移除）

分析器：分析语法和词法

优化器：按照 MySQL 认为最优的方案去执行

执行器：执行语句

插件式存储引擎：MySQL 采用的是插件式架构，引擎包括 InnoDB、MyISAM、Memory 等多种存储引擎。

MySQL 存储引擎

MySQL 的核心存储是存储引擎

MySQL 支持哪些存储引擎？默认使用哪个？

支持 MyISAM、InnoDB。默认使用 InnoDB。

所有的存储引擎中只有 InnoDB 是支持事务的，它是唯一的事务性存储引擎。

MySQL 5.5.5 之前，MyISAM 是默认存储引擎。

MySQL 存储引擎架构了解吗？

MySQL 采用的是插件式架构，支持多种存储引擎，对于不同的表的特点还可以使用不同的存储引擎。存储引擎是基于表而不是数据库的。

我们也可以根据 MySQL 定义的存储引擎接口来编写一个属于自己的存储引擎。例如 InnoDB 本身是一个第三方的存储引擎，但因为它太过优秀，最后被 Oracle 收购成为 MySQL 默认的存储引擎了。

MyISAM 和 InnoDB 的区别是什么？

1、InnoDB 支持事务

2、InnoDB 支持行级锁

3、InnoDB 支持外键

4、InnoDB 支持数据库异常崩溃后的安全恢复

5、InnoDB 支持 MVCC

6、索引实现不一样：都是B+树，但是 InnoDB 数据文件本身就是索引文件。（数据和索引在物理上是存储在一起的）

MyISAM 和 InnoDB 如何选择？

因为我们非常关心崩溃后可以恢复这个事情，所以基本上无脑 InnoDB。

MySQL 查询缓存

查询缓存在 8.0 之后默认不开启，因为这个功能并不很实用。

查询缓存不命中的情况：

1、两个 select 任何字符不同

2、select 包含自定义函数、存储函数、用户变量、临时表、系统表

3、如果表发生变化

缓存虽然能够提升查询效率，但是同样带来了额外的开销，并且命中缓存的条件非常苛刻。

MySQL 事务

事务（数据库事务）：一系列 SQL 操作，要么全部做，要么全部不做。

关系型数据库事务都存在 ACID 的特性：

1、A：atomic 原子性，一系列操作，要么全部都做，要么全都不做。

2、C：consistency 一致性，在事务前后数据库状态需保持一致。

3、I：Isolation 隔离性，在并发访问数据库的时候，一个用户的事务不会被其他事务干扰。

4、D：durability 持久性，一个事务被提交之后，它对数据库的改变是持久的

另提一句：实际上事务的特性应该是 AID，对于 C 一致性来说，它应该是事务满足 AID 这三个特性后，执行事务的结果。AID 是手段，而 C 是目的。

并发事务带来了哪些问题？

1、脏读：读到未提交的脏数据。

2、丢失修改：一个事务修改数据后，另外一个事务也修改了，这样第一个事务修改的结果丢失。

3、不可重复读：在一个事务中多次读，发现原来读的数据被修改，前后读取数据不一致。

4、幻读：在一个事务中读取了几行数据，随后的查询发现多了一些不存在的数据。

不可重复读和幻读有什么区别？

不可重复读的重点在于读取同一条数据发现结果不同

幻读的重点在于执行同一条 DQL 的时候，发现查到的记录增加了

它们的区别还在于解决它们的方案不同，幻读比不可重复读更难解决，而且幻读在某些情况下是可以接受的。

SQL 标准定义了哪些事务隔离级别？

1、读未提交：允许读取未提交的更改

2、读已提交：允许读取已经提交的更改

3、可重复读：同一事物内读取同一数据结果一致

4、可串行化：最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行。

MySQL 的隔离级别是怎么实现的？

InnoDB 默认的隔离级别是 可重复读。我们前面说，可重复不能解决幻读问题。

但实际上，InnoDB 实现的可重复读可以解决幻读问题，主要有以下两种情况：

1、快照读：由 MVCC 来保证不出现幻读

2、当前读：使用 Next-Key Lock 进行加锁来保证不出现幻读。

MySQL 锁

InnoDB 不仅支持表级锁，而且支持更细粒度的行级锁。

对比：

1、表级锁：MySQL 中锁粒度最大的一种，针对非索引字段加的锁。实现简单，消耗资源少，加锁快，不会出现死锁。但并发度比较低

2、行级锁：MySQL 中锁粒度最小的一种，针对索引字段加的锁。行级锁可以提高并发，减少数据库操作的冲突。但加速慢，代价高，可能出现死锁。

行级锁的使用有什么注意事项？

InnoDB 行锁针对索引字段，表锁针对非索引字段。当我们执行 update、delete语句的时候，如果没有命中唯一索引或者索引失效的时候，就会导致扫描全表并对表中所有行记录进行加锁！！请多多注意这一点。

共享锁和排他锁

共享锁也叫读锁（S锁）：事务在读取记录的时候需要加共享锁，允许多个事务同时加共享锁。

排他锁也叫写锁（X锁）：事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录被加了排他锁，那么不允许再加任何类型的锁。

意向锁的作用

意向共享锁（IS 锁）：事务有意向对表中某些记录加 S 锁，加 S 锁前必须要获得该表的 IS 锁

意向排他锁（IX 锁）：事务有意向对表中某些记录加 X 锁，加 X 锁前必须要获得该表的 IX 锁

意向锁是数据引擎自己维护的，不允许手动操作意向锁。

意向锁之间是相互兼容的

意向锁和共享锁和排他锁互斥（表级别的共享锁和排他锁，意向锁不会和行级锁互斥）

InnoDB 有哪几类行锁？

记录锁：单个行记录的锁

间隙锁：锁定一个范围，不包括当前记录

临键锁：上述两个锁的集合，包括记录本身

InnoDB 的默认隔离级别 RR，在当前读（一致性锁定读）中就使用的是 临键锁 来保证不出现幻读

当前读和快照读有什么区别？

快照读（一致性非锁定读）是单纯的 select 语句

快照即记录的历史版本，每行记录可能存在多个历史版本。

对于 RC 的情况下，我们读取被锁定行的最新一份快照。

对于 RR 的情况下，我们读取事务开始时的行快照。

当前读（一致性锁定读）就是给行记录加 X 锁 或者 S 锁。