JavaGuide知识点整理——MySQL知识点总结

MySQL基础

关系型数据库介绍

顾名思义，关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系。
关系型数据库中，我们的数据都被存放在各种表中，表中的每一行就存放着一条数据。
大部分关系型数据库都是用sql来操作数据库中的数据。并且大部分关系型数据库都支持事务的四大特性。
常见的关系型数据库
MySQL,PostgreSQL，Oracle,SQL Server,SQLite(微信本地的聊天记录的存储就是用的SQLite)

MySQL介绍

MySQL是一种关系型数据库，主要用于持久化存储我们的系统中的一些数据比如用户信息等。
由于MySQL是开源免费且比较成熟的数据库，因此MySQL被大量使用在各种系统中。任何人都可以在GPL的许可下下载并根据个性化的需求对其进行修改。MySQL的默认端口是3306.

MySQL基础架构

下图是一个MySQL的简要架构图。图中可以很清晰的看到客户端的一条sql语句在MySQL内部是如何执行的。

sql执行过程

从上图可以看出，MySQL主要由下面几部分构成：

• 连接器：身份认证和权限相关(登录MySQL的时候)
• 查询缓存：执行查询语句的时候，会先查询缓存(MySQL8.0以后此功能移除，因为不实用)
• 分析器：没有命中缓存的话，SQL语句就会经过分析器，分析器说白了就是先看你的sql语句要干啥，再检查sql语句语法是不是正确。
• 优化器：按照MySQL认为最优的方案去执行。
• 执行器：执行语句，然后从存储引擎返回数据。执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
• 插件式存储引擎：主要复杂数据的存储和读取。采用的是插件式架构，支持I你弄DB，MyISAM，Memory等多种存储引擎。

MySQL存储引擎

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

MySQL支持多种存储引擎，可以通过show engines 命令来查看MySQL支持的所有存储引擎。

MySQL支持的存储引擎

MySQL默认的存储引擎的InnoDB。并且所有的存储引擎中只有InnoDB是事务性存储引擎，也就是说只有InnoDB支持事务。
这里要注意一下，mysql5.5之前默认的存储引擎是MyISAM。5.5版本之后默认的才是InnoDB.

MySQL存储引擎架构

MySQL存储引擎采用的是插件式架构，支持多种存储引擎。我们甚至可以为不同的表设置不同的存储引擎以适应不同场景的需要。存储引擎是基于表的而不是数据库。
并且 我们还可以根据MySQL定义的存储引擎实现标准接口来编写一个属于自己的存储引擎。这些非官方提供的存储引擎可以称为第三方存储引擎。区别于官方存储引擎，，像目前最常用的InnoDB其实最开始就是一个第三方存储引擎。后面因为过于优秀，被Oracle收购了。
MySQL 官方文档也有介绍到如何编写一个自定义存储引擎，地址：https://dev.mysql.com/doc/internals/en/custom-engine.html 。

MyISAM和InnoDB的区别

MySQL5.5之前默认MyISAM，虽然MyISAM性能还行，各种特性也还不错，但是MyISAM不支持事务和行级锁，而且最大的缺陷是崩溃后无法安全恢复。下面简单说下两者的区别：

1. 是否支持行级锁
   MyISAM只有表级锁，而InnoDB支持行级锁和表级锁，默认是行级锁。
   也就是说MyISAM一锁就是锁住了整张表，这在并发的情况下是有问题的。
2. 是否支持事务
   MyISAM不提供事务支持。InnoDB提供事务支持，实现了SQL标准定义了四个隔离级别，具有提交和回滚事务的能力。并且InnoDB默认使用的是可重复读隔离级别，是可以解决幻读问题发生的。
3. 是否支持外键
   MyISAM不支持，而InnoDB支持。外键对于维护数据一致性非常有帮助。但是对性能有一定的损耗。因此通常情况下我们不建议使用外键。但是使不使用和支不支持是两件事。
4. 是否支持数据库异常崩溃后的安全恢复
   MyISAM不支持，InnoDB支持。
   使用InnoDB的数据库在异常崩溃后，数据库重新启动的时候会保证数据库恢复到崩溃之前的状态。这个恢复的过程依赖于redo log.
5. 是否支持MVCC
   MyISAM不支持，InnoDB支持。
   MVCC可以看作是行级锁的一个升级，MyISAM连行级锁都不支持，自然也不支持MVCC.
6. 索引实现不一样
   虽然MyISAM引擎和InnoDB引擎都是使用B+树作为索引结构，但是两者的实现方式不太一样。
   InnoDB引擎中，其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的。其表数据文件本身就是按照B+树组织的一个索引结构，树的叶节点data域保存了完整的数据记录。

B树和B+树的区别

• B树所有节点既存key也存数据。而B+树只有叶子节点存放key和data，其余只存key。
• B树的叶子节点是独立的，B+树的叶子节点指向与它相邻的叶子节点。
• B树的检索过程就是不断二分，可能没到叶子节点检索就结束了。B+树任何查找都是从根节点到叶子节点。

MyISAM和InnoDB如何选择？

如果是读密集的情况下，MyISAM也是合适的。前提是不介意MyISAM不支持事务，崩溃等缺点。当然一般情况下我们都不会不介意，所以还是InnoDB是常用的引擎。

MySQL事务

何谓事务？

我们设想一个场景吗，这个场景中我们要插入多条相关联的数据到数据库，不幸的是，这个过程可能会遇到下面这些问题：

• 数据库突然挂了
• 客户端因为网络原因连不上数据库了
• 并发访问时，多个线程同时写入数据库，覆盖了彼此的更改。

上面任何一个问题都会导致数据不一致性。而事务就是我们抽象出来简化这些问题的首选机制。事务的概念起源于数据库，目前已经是一个比较广泛的概念了。
何谓事务？一言蔽之，事务是逻辑上的一组操作，要么都执行，要么都不执行。

何谓数据库事务?

大多数情况下，我们谈论事务的时候，如果没有特指分布式事务，往往说的就是数据库事务。
数据库事务的作用就是：保证多个对数据库的操作构成一个逻辑上的整体。构成这个逻辑上的整体的这些数据库操作遵循：要么全部执行成功，要么全部不执行。

image.png
另外，关系型数据库(MySQL,SQL Server,Oracle等)事务都有ACID特性。
ACID指原子性，一致性，隔离性，持久性：

1. 原子性(Atomicity)：事务是最小的执行单位，不允许分割。事务的原子性保证动作要么全部完成，要么完全不起作用。
2. 一致性(Consistency)：执行事务前后，数据保持一致，例如转帐中，无论事务是否成功，转账者和收款人的总额是不变的。
3. 隔离性(Isolation)：并发访问数据库时，一个用户的事务不被其他事务干扰。各个事务之间数据库是独立的。
4. 持久性(Durabilily)：一个事务被提交后，它对数据库中数据的改变是持久的，即使数据库发生故障也不应该有影响。

需要注意一点:ACID中，保证了持久性，原子性，隔离性。一致性才能得到保障。也就是说AID是手段，C是目的。

ACID关系

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

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务，并发是必须的，但是可能会导致一些问题。

• 脏读：当一个事务正在访问数据并且对数据进行了修改。而这个修改还没提交到数据库中，另外一个事务也访问到了这个数据，使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的
• 丢失修改：指两个事务同时读取并修改一个数据。这样可能会使得第一个事务内的修改丢失。
• 不可重复读：指在一个事务内多次读同一数据。可能在此期间其他事务修改了数据，所以导致两次读取的数据是不一致的，因此称为不可重复读。
• 幻读：也是指一次事务中，多次读取。有其他事务插入数据，所以二次读取时候会多几条数据。

不可重复读和幻读的区别是：

• 不可重复读的重点是内容修改或者记录减少
• 幻读的重点在于新增记录。

幻读其实也是不可重复读的一种，之所以单独区分是因为解决幻读和不可重复读的方案不一样。
举个例子：修改和删除都可以通过对记录枷锁保证事务安全。
而执行insert操作的时候，记录锁(Record Lock)只能锁住已存在的记录，所以避免插入新纪录，需要依赖间隙锁(Gap Lock).

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

SQL标准定义了四个隔离级别：

• 读未提交：最低的隔离级别，允许读取稍微提交的数据变更，可能导致脏读，幻读，不可重复读。
• 读已提交：允许读取并发事务已提交的数据，可以阻止脏读，但是幻读和不可重复读仍然可能发生。
• 可重复读：对同一字段的多次读取结果都是一致的，除非数据是被本身事务所修改。可以防止脏读和不可重复读。幻读仍有可能发生。
• 序列化：最高的隔离级别，完全服从ACID的隔离级别。所有事务依次逐个执行，这样事务之间就不会有干扰。可以防止脏读，幻读，不可重复读。

MySQL的隔离级别是基于锁实现的么？

MySQL是隔离级别是基于锁和MVCC机制共同实现的。
序列化是通过锁实现的，其余都是基于MVCC实现。

MySQL的默认隔离级别是什么?

InnoDB存储引擎的默认支持的隔离级别是可重复读。可以用命令select @@tx_isolation; 来查看。

image.png

MySQL锁

表级锁和行级锁的区别

MyISAM仅仅支持表级锁，一锁就是一张表，这在并发的情况下效率非常差。InnoDB不光支持表级锁，也支持行级锁，默认是行级锁。行级锁的粒度更小，仅对相关的记录上锁即可。所以对于并发写入操作来说，InnoDB性能更高。
表级锁与行级锁对比：

• 表级锁：MySQL中锁定粒度最大的一种锁。是针对非索引字段加的锁。对当前操作的整张表加锁。实现简单，资源消耗也比较少。加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高。并发度最低，MyISAM和InnoDB都支持表级锁。
• 行级锁：MySQL中锁粒度最小的一种锁，是针对索引字段加的锁。只针对当前操作的行记录进行枷锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但是加锁的开销也最大，加锁慢，会出现死锁。

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

InnoDB的行锁是针对索引字段加的锁，表级锁是针对非索引字段加的锁。当我们执行UPDATE,DELETE语句时，如果where条件中字段没有命中唯一索引或者索引失效的话，就会导致扫描全表对表中的所有行记录进行加锁。这在我们工作中经常遇到，要多多注意。
不过有时候即使使用了索引也有可能会走全表扫描，这是因为MySQL优化器的原因。

共享锁和排他锁呢？

不论是表级锁还是行级锁，都存在共享锁和排他锁两类：

• 共享锁(s锁)：又称为读锁，事务在读取记录的时候获取共享锁，允许多个事务同时获取。
• 排他锁(x锁)：又称写锁/独占锁。事务在修改记录的时候获取排他锁，不允许多个事务同时获取。如果一个记录已经被加了排他锁，那其他事务不能再对这条记录加任何类型的锁。

排他锁和任何的锁都不兼容，共享锁仅和共享锁兼容。
由于MVCC的存在，对于一般的SELECT语句，InnoDB不会加任何锁，不过我们可以显示加锁。

# 共享锁
SELECT ... LOCK IN SHARE MODE;
# 排他锁
SELECT ... FOR UPDATE;

意向锁有什么作用？

如果需要用到表锁的话，如何判断表中的记录有没有行锁？一行一行的遍历不现实，我们需要用到一个叫做意向锁的东西来快速判断是否可以对某个表使用表锁。
意向锁是表级锁，共有两种：

• 意向共享锁(IS锁)：事务有意向对表中的某些记录加共享锁，加共享锁前必须先取得该表的IS锁。
• 意向排他锁(IX锁)：事务有意向对表中某些记录加排他锁，加排他锁之前必须先取得该表的IX锁。

意向锁是由数据引擎自己维护的，用户无法手动操作意向锁。在为数据行加共享锁/排他锁之前，InnoDB会先获取该数据行所在数据表的对应意向锁。
意向锁之间是互相兼容的。
意向锁会与表级的共享锁和排他锁互斥，行级的不会。

InnoDB有哪几类行锁？

InnoDB支持三种行锁定方式：

• 记录锁(Record Lock):属于单个行记录上的锁。
• 间隙锁(Gap Lock):锁定一个范围，不包括记录本身。
• 临键锁(Next-key Lock):可以理解成是记录锁+间隙锁。锁定一个范围，包含记录本身，记录锁只能锁住已经存在的记录，为了避免插入新记录引起幻读，需要依赖间隙锁。

InnoDB的默认隔离级别RR(可重复读)是可以解决幻读问题发生的，主要有下面两种情况：

• 快照读(一致性非锁定读)：由MVCC机制来保证不出现幻读
• 当前读(一致性锁定读)：使用临键锁进行加锁来保证不出现幻读。

当前读和快照读有神恶魔区别？

快照读
快照读就是单纯的select语句，但是不包括显示加锁的语句。快照即记录的历史版本，每行记录可能由多个历史版本，快照读的情况下，如果读取的记录正在执行修改/删除操作，读取操作不会等待记录上的X锁释放，而是会去读取一个快照。
只有在事务隔离级别是RC(读已提交)和RR(可重复读)下，InnoDB才会使用非一致性锁定读。

• 在RC级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。
• 在RR级别下，对于快照数据，非一致性读总是读取本事务开始时的行数据版本。

快照读比较适合对于数据一致性要求不是特别高且追求极致性能的业务场景。

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

本篇笔记就记到这里，如果稍微帮到你了记得点个喜欢点个关注。也祝大家工作顺顺利利！