MySQL

三范式

  • 第一范式：确保每一列保持原子性，不可再分
  • 第二范式：确保每一列都和主键有关
  • 第三范式：确保每一列都和主键有直接关系而不是间接关系

四大特性ACID

原子性 一致性 隔离性 持久性

索引

索引是什么？

简单点来说，索引就是行记录根据某一列的值大小进行排序作为叶子节点的一棵B+数。当然实际上叶子节点并不就是行记录，而是一个数据页，数据页里面才是行记录。

索引的分类

根据索引列的不同，索引又可以分为3种。

  • 第一种：聚簇索引

聚簇索引是根据主键值大小进行排序的，叶子节点处储存的是完整的行记录数据。所谓完整的行记录数据，就是指这个记录中包含了所有列的值，包括那些隐藏列。因为聚簇索引这个特性呢，所以就有索引即数据，数据即索引的说法。

  • 第二种：二级索引

二级索引是根据某一个非主键列的值的大小进行排序的，叶子节点上存放就不是完整的行数据了，而是只有索引列和主键这两个值。为什么要有主键这个值呢，就是为了回表用的。

  • 第三种：联合索引

联合索引就是用多个列一起建立的索引，假如有C1、C2两个列，先按照C1列的值进行排序，C1列的值相等，则按照C2列的值进行排序。它的叶子节点上有C1、C2列的值和主键值。所以从本质上讲，联合索引也是一个二级索引。

索引覆盖

利用二级索引进行查询的时候，往往都需要回表操作，但是回表操作往往都是随机IO，所以尽量使用索引覆盖，只查索引列和主键值，就能够避免回表带来的性能消耗。

为什么是B+树不是二叉树?

二叉树一个父节点最多只有两个子节点，当数据较多的时候，二叉树的高度就会很大。但是磁盘跟内存交互是一个节点一个节点进行IO的，树越高磁盘IO的开销就越大，磁盘IO本来就慢，加上频繁读写，那么效率就会很低。为了减少磁盘I/O，就要降低树的高度，这就需要用到B树和B+树了，这种多叉树结构可以大大降低树的高度，减少I/O，提高效率。那为什么不是B树，而是B+树呢，因为B+树的叶子节点可以构成有序链表，在全表扫描或者范围查询的时候，只要顺着链表直接读就可以了。

索引的代价

虽然索引是个好东西，但是也不能乱建，它是有代价的。

  • 空间上的代价

每多建一颗索引，都要多建一颗B+树，树的每一个节点都是一个16KB大小的数据页，一颗很大的B+树会有非常多个数据页，所占的存储空间也是比较恐怖的。

  • 时间上的代价

每次对表中的数据进行增删改操作，增删改操作很有可能会对节点和记录的排序造成破坏，所以存储引擎需要花费额外的时间去进行记录位移、页面分裂、页面回收这些操作来维护节点和记录的排序。如果建了许多索引，每一个索引都要搞这些操作，那所花费的时间成本是很高的。

如何优化性能？

  • 为必要的列创建索引（比如用于查询、排序、分组的列）
  • 索引列等值匹配
  • 匹配左前缀
  • 使用limit
  • 单数据库使用主键自增方案
  • 使用索引覆盖，避免回表
  • 增加缓冲池容量

表连接的原理

什么是连接

连接的本质就是把各个连接表的记录都取出来依次匹配组合，加入结果集并返回给用户。

连接当中第一个被查询的表也叫驱动表，第二个叫被驱动表。在外连接当中，左连接一般是左表为驱动表，右表为被驱动表，右连接则是右表为驱动表，左表为被驱动表。

嵌套循环连接

连接一般有两种方式，第一种是嵌套循环连接。它的连接过程是：

第一步，选取驱动表，使用与驱动表相关的过滤条件，选择成本最低的单表访问方法来执行对驱动表的单表查询。然后对上一步查询驱动表得到的结果集中的每一条记录，都分别到被驱动表中查找匹配的记录。这样就完成连接查询了。

嵌套循环连接，只对驱动表访问一次，被驱动表则可能访问多次，它的次数取决于从驱动表中查询到的结果集当中的记录数。但是扫描一个表的过程其实是先把表数据从磁盘上加载到内存中，然后在内存中进行比较匹配，而现实生活中的表很多都有百万级别的数据，当缓冲池的空间不足时，扫一次全表就要从磁盘上读很多次数据，更不用说被驱动表可是要访问好多遍的，如果被驱动表的数据非常多又不能使用索引，那这个磁盘IO的代价就非常大了。所以我们要尽量减少访问被驱动表的次数。

基于块的嵌套循环连接

于是Mysql又用了join buffer的方法，join buffer就是执行连接查询前向操作系统申请的一块固定大小的内存，先把第一步查询驱动表得到的结果集中的记录装在这个join buffer当中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer当中的多条驱动表记录做匹配，只要join buffer足够大，能容纳驱动表结果集中的所有记录，这样就只需要访问一次被驱动表就能完成连接操作了。这样就能显著减少被驱动表的磁盘IO，这种加入了join buffer的嵌套循环连接又叫作基于块的嵌套循环连接。

单表访问方法简介

方法内容
const利用主键或者唯一二级索引进行等值匹配查询
ref利用普通二级索引进行等值匹配
range利用索引进行范围匹配
index扫描整个二级索引
all全表扫描
index_merge 索引合并使用多个索引完成一次查询

其中索引合并又可分为取交集、取并集、先排序再取并集3种

分类内容
取交集（Intersection合并）使用多个二级索引进行等值查询，从所有查询结果集当中根据主键取交集
取并集（Union合并）使用多个二级索引进行等值查询，从所有结果集当中根据主键取并集
先排序再取并集（Sort-Union合并）使用多个二级索引进行范围查询，得到的所有结果集当中的记录先按照主键值进行排序，再根据主键取并集

查询优化

基于成本的优化

  • 什么是成本？

一条查询语句的执行成本可以分为两个方面，一个是磁盘IO成本，一个是CPU成本。在innoDB引擎中，页是磁盘与内存交互的基本单位，MySQL就规定读取一个页面花费的成本默认是 1.0，检测一条记录是否符合查询条件的成本默认是 0.2，1.0 和 0.2 也叫做成本常数。

  • 基于成本的优化步骤

在一条单表查询语句真正执行之前，查询优化器会找出该查询语句所有可能执行的方案，对比之后找出成本最低的方案，这个成本最低的方案就是执行计划，之后才会调用存储引擎提供的接口真正执行查询，总结一下这个步骤就是：

敲黑板

  1. 先根据查询条件，找出所有可能使用的索引。

  2. 计算全表扫描的成本。

  3. 计算使用不同索引查询的成本。

  4. 对比所有的方案找出成本最低的那一个。

  • 多表连接的成本计算

多表连接的成本又怎么计算呢？假如使用嵌套循环连接算法，它的成本主要是单次查询驱动表的成本和多次查询被驱动表的成本，具体多少次取决于驱动表的结果集中有多少条记录。所以两个表连接查询的总成本就是：

单次访问驱动表的成本 + 驱动表结果集记录数 x 单次访问被驱动表的成本

如果是内连接的话，还要计算和对比不同的表连接顺序所需要的成本，选择成本最低的表连接顺序方案。

基于规则的优化（查询优化规则）

在查询语句真正执行前，MySQL会根据一些规则，把SQL语句优化成可以高效执行的形式，这个过程也被称作查询重写。

重写规则有哪些？

  • 查询条件化简（移除不必要的括号、常量传递、等值传递、移除没用的条件、表达式计算 等等）
  • 外连接消除（被驱动表使用了空值拒绝）
  • 子查询优化（主要是in查询优化）

为什么进行外连接消除

因为外连接的驱动表和被驱动表的连接顺序是固定的，无法被MySQL查询优化器进行顺序优化，而转换成内连接之后，就可以通过改变表的连接顺序来优化查询成本，所以如果被驱动表的查询条件当中有不为空的条件限制，它的查询结果就跟内连接是一样的，所以就可以转化成内连接来达到优化的效果。这也叫空值拒绝。

子查询优化

首先是物化表的提出，对于不相关子查询，子查询结果集不直接作为外层查询的参数，而是将该结果集作为一个临时表放到内存中，称作物化表，然后外层查询的表就直接与物化表进行内连接，一转化成内连接，查询优化器就又可以进行表顺序优化等一系列操作了。

半连接（semi-join）

在某些特定的查询条件下，MySQL甚至跳过了物化表的步骤直接把in子查询转换成半连接，利用表连接的可优化性，让查询优化器充分发挥作用，减少性能消耗。

缓冲池

缓冲池是什么？

缓冲池是MySQL为了在操作系统的内存中缓存磁盘中的数据页，大幅提升数据库操作性能，在数据库启动的时候就向操作系统申请了一片连续的内存空间，叫做Buffer Pool，我们通常叫它缓冲池。它的大小我们也可以自定义的。

[server]
innodb_buffer_pool_size = 268435456

缓冲池的内部组成

缓冲池主要是有缓存页和控制块组成，一个缓存页对应一个控制块，控制块里面记录了对应缓存页所属的表空间编号、页号、缓存页在缓冲池中的地址、链表节点信息、锁信息等等。然后他们都在缓冲池里面，不过他们存放的位置不一样，控制块被放在缓冲池的前边，缓存页被放到缓冲池的后边。这些就是缓冲池的一个大体的内部组成结构。

除此之外，MySQL还引入很多链表来整理这些数据页，其中有3个比较主要的。

free 空闲地址链表

第一个是空闲链表，缓冲池在初始化的时候，会把申请到的内存空间划分成若干对控制块和缓存页，每个缓存页对应的控制块都会被加到空闲链表中，当有数据页从磁盘中加载到缓冲池时，就从空闲链表中取一个控制块，填上相对应的信息，比如表空间、页号之类的，然后把数据页放到控制块对应的缓存页空间上，再把这个控制块从空闲链表中移除，表示这个控制块对应的缓存页已经被使用了不再是空闲的了。所以这个空闲链表呢就解决了从磁盘加载数据到缓冲池的时候不知道哪些地方是空的可以放数据，哪些地方是已经被使用了不能再放数据的问题。

flush 刷新链表

第二个比较重要的链表就是flush链表了。如果我们修改了缓存页中的数据，又不想立即同步到磁盘中去，因为磁盘io实在太慢，很影响程序性能，那之后要同步脏页数据到磁盘持久化，怎么知道哪些缓存页是被修改过的脏页呢，总不能把所有的缓存页全都同步到磁盘吧，如果缓冲池有上百G甚至几百G，那就没得玩了，所以这里不得不创建一个链表，用来记录被修改过的缓存页，凡是修改过的缓存页对应的控制块全都作为一个节点加入到这个链表中，等待同步，这个链表就是flush链表了。

LRU冷热链表

缓冲池的空间毕竟是有限的，当缓冲池中没有足够的空间容纳新的数据页的时候，就需要清理掉一些很少被访问的缓存页。那怎么确定哪些缓存页是最近很少被访问到的呢？LRU链表按照一定的比例分成冷数据和热数据两段，热数据在前，冷数据在后，当磁盘上的某个页面在初次加载到缓冲池的时候，该数据页对应的控制块会被放在冷数据区域的头部，当隔一段时间这个数据页再次被访问的时候，才把它放到热数据区域的头部， 这样对于那些加载进来又不进行后续访问的页面就会被逐步排挤到冷数据区域尾部，慢慢被清理出缓冲池，而对于那些再次被访问的数据页将会被移到热数据区域的头部，这样就可以大大提高了缓存的命中率。

注意，划重点

空闲地址链表的作用就是标记缓冲池里面还有没有地方放数据的，又有哪些地方可以放数据的，如果空闲链表没数据了，就代表缓冲池已经满了，放不下新的数据了。 刷新链表的作用就是记录了哪些缓存的数据页被修改过了，但还没有同步到硬盘上的，就在这个链表上记录着等待同步、持久化到硬盘就行。 LRU冷热链表的作用就是提高缓存性能的，它记录了哪些数据是经常访问的数据，哪些是很少被访问的数据，经常被访问的数据就会放到链表头部，很少被访问的数据就会慢慢的被清理出缓冲池，这样就极大提高了缓存的命中率，这不就是缓存要的效果吗？ 然后，除了刚才说的3个链表，缓冲池其实还有很多其他的链表啊，锁信息等等这些内容。这些我就没怎么了解了。

事务的隔离级别

在事务的四大特性当中有一个隔离性，理论上在某个事务对某个数据进行访问时，其他事务应该等待，当该事务提交之后，其他事务才可以进行操作。但是这样子对性能的影响太大，我们即想要保持隔离性，又想让多事务访问同一数据的性能高一些，就要舍掉一部分隔离性换取性能了。

什么是事务

事务是指程序中一系列操作，要么全部成功执行，要么全部失败回滚。

事务并发中遇到的问题

脏写：一个事务修改了另一个未提交事务修改过的数据。

脏读：一个事务读到了另一个未提交事务修改过的数据。

不可重复读：一个事务每次都读到其他已提交事务修改过的最新的数据。

幻读：一个事务当中，相同的查询条件下，事务读取到了之前没有读取到的数据。

四种隔离级别

READ UNCOMMITTED：未提交读。

READ COMMITTED：已提交读。

REPEATABLE READ：可重复读。

SERIALIZABLE：可串行化。

隔离级别脏读不可重复读幻读
READ UNCOMMITTEDPossiblePossiblePossible
READ COMMITTEDNot PossiblePossiblePossible
REPEATABLE READNot PossibleNot PossiblePossible
SERIALIZABLENot PossibleNot PossibleNot Possible

MVCC（多版本并发控制）原理

版本链

在聚簇索引的用户记录当中都包含两个必要的隐藏列，一个是事务ID，另一个就是roll_pointer上一个版本的指针，通过这个指针就可以找到该记录上一个版本的数据，其实就是undo日志。

ReadView

作用：判断版本链中的哪个版本是当前事务可见的版本。

ReadView中主要包含4个比较重要的东西：

  • m_ids: 生成ReadView时，当前系统中活跃的事务id列表。
  • min_trx_id: 表示当前最小的活跃事务id，就是上面事务id列表中的最小值。
  • max_trx_id: 下一个事务的id值。（事务id值是递增的）
  • creator_trx_id: 生成ReadView的事务id（当前事务id）。

有了这个ReadView，这样在访问某条记录的时候，就很容易判断出某个版本是否可见：

① =自己：如果被访问版本的事务id列的值与当前事务id一致，就说明当前事务正在访问它自己修改过的记录，所以该版本可以被当前事务访问。

② <最小：如果被访问版本的事务id值小于ReadView当中的最小事务id（min_trx_id），就说明该版本的事务在当前事务开启之前就已经提交，所以该版本可以被访问。

③ >最大：如果被访问版本的事务id值大于等于ReadView当中的下一个事务id（max_trx_id），说明该版本的事务是在当前事务之后才开启的，所以该版本不可以被访问。

④ 在最小和最大之间：如果被访问版本的事务id值在ReadView的最小值和下一个事务id值之间，那就要判断一下该版本的事务id值是不是在ReadView的活跃事务id列表当中，如果在，说明生成这个版本的事务还是活跃的，该版本不可以被访问，如果不在，说明生成该版本的事务已经提交了，该版本可以被访问。

提示

如果某个版本的记录对当前事务不可见的话，就顺着版本链找到下一个版本的数据，继续按照上面说的规则去判断可见性，如果到最后一个版本也不可见的话，那就说明该记录对该事务完全不可见，查询结果中就不包含该记录。

注意

在MySQL中，已提交读和可重复读这两个隔离级别的一个非常大的区别就在于他们生成ReadView的时机不同，在已提交读的隔离级别中，事务每次查询时都会生成一个新的ReadView，如果这中间有其它事务提交了，就会读到提交的最新的值，所以会有不可重复读的情况发生。而在可重复读的事务隔离级别当中，事务只会在第一次查询的时候生成一个ReadView，之后的查询都会复用这个ReadView，所以每次的查询结果都一样，就避免了不可重复读和幻读的现象发生。

锁

怎么解决并发当中的脏读，不可重复读，幻读这些问题？

方案1：读操作利用多版本并发控制（MVCC），写操作加锁。读写操作不冲突。
方案2：读、写操作都采用加锁的方式。读-写和写-写操作一样会阻塞。
很明显，采用MVCC方式的话，读-写操作彼此并不冲突，性能更高，采用加锁方式的话，读-写操作彼此需要排队执行，影响性能。一般情况下我们当然愿意采用MVCC来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行，那也是没有办法的事。

一致性读：利用事务MVCC进行的读取操作称之为一致性读，也叫一致性无锁读，有的地方也叫快照读。

锁定读的语句：

--共享锁（S锁）
select ... lock in share mode;  
  
--独占锁（X锁）
select ... for update;

表锁

IS锁：意向共享锁，英文名：Intention Shared Lock，简称IS锁。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。

IX锁：意向独占锁，英文名：Intention Exclusive Lock，简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

总结一下：IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。我们画个表来看一下表级别的各种锁的兼容性：

兼容性XIXSIS
X不兼容不兼容不兼容不兼容
IX不兼容兼容不兼容兼容
S不兼容不兼容兼容兼容
IS不兼容兼容兼容兼容

在对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，InnoDB存储引擎是不会为这个表添加表级别的S锁或者X锁的。

另外，在对某个表执行一些诸如ALTER TABLE、DROP TABLE这类的DDL语句时，其他事务对这个表并发执行诸如SELECT、INSERT、DELETE、UPDATE的语句会发生阻塞，同理，某个事务中对某个表执行SELECT、INSERT、DELETE、UPDATE语句时，在其他会话中对这个表执行DDL语句也会发生阻塞。这个过程其实是通过在server层使用一种称之为元数据锁（英文名：Metadata Locks，简称MDL）东东来实现的，一般情况下也不会使用InnoDB存储引擎自己提供的表级别的S锁和X锁。

AUTO-INC锁

在使用MySQL过程中，我们可以为表的某个列添加AUTO_INCREMENT属性，之后在插入记录时，可以不指定该列的值，系统会自动为它赋上递增的值。

auto-inc锁是表锁，在插入语句执行结束后，auto-inc锁就会被释放掉，这样一个事务在持有auto-inc锁的过程中，其他插入语句都要被阻塞，可以保证分配的递增值是连续的。

其他引擎的锁

对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。比方说在Session 1中对一个表执行SELECT操作，就相当于为这个表加了一个表级别的S锁，如果在SELECT操作未完成时，Session 2中对这个表执行UPDATE操作，相当于要获取表的X锁，此操作会被阻塞，直到Session 1中的SELECT操作完成，释放掉表级别的S锁后，Session 2中对这个表执行UPDATE操作才能继续获取X锁，然后执行具体的更新语句。

InnoDB存储引擎既支持表锁，也支持行锁。表锁实现简单，占用资源较少，不过粒度很粗，有时候你仅仅需要锁住几条记录，但使用表锁的话相当于为表中的所有记录都加锁，所以性能比较差。行锁粒度更细，可以实现更精准的并发控制。

行锁

记录锁（LOCK_REC_NOT_GAP）

有共享锁和独占锁（排他锁）之分。

GAP锁（LOCK_GAP）

MySQL解决幻读有两种方法，可以使用MVCC方案解决，也可以采用加锁方案解决。

GAP锁的作用：不允许其他事务在被GAP锁的记录与前一条记录之间插入新的记录。

这个gap锁的提出仅仅是为了防止插入幻影记录而提出的，虽然有共享gap锁和独占gap锁这样的说法，但是它们起到的作用都是相同的。而且如果你对一条记录加了gap锁（不论是共享gap锁还是独占gap锁），并不会限制其他事务对这条记录加正经记录锁或者继续加gap锁，再强调一遍，gap锁的作用仅仅是为了防止插入幻影记录的而已。

next-key锁（LOCK_ORDINARY）

next-key锁的本质就是一个正经记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

插入意向锁（LOCK_INSERT_INTENTION）

我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的gap锁（next-key锁也包含gap锁，后边就不强调了），如果有的话，插入操作需要等待，直到拥有gap锁的那个事务提交。

隐式锁

在没有gap锁干扰的情况下，insert操作是不加锁的。那如果此时有其他事务也要操作这条记录的话，其他事务会查询该条记录的隐藏列当中的事务id，看是不是当前活跃事务，如果是当前活跃事务，说明当前操作的事务还没提交，那么其他事务就会为当前事务创建一个独占锁，自己则进入等待状态。

MySQL用法笔记

查询/新增用户

SELECT * FROM USER
CREATE user 'magic'@'%' IDENTIFIED by 'password' ;
GRANT ALL ON web_magic.* TO 'magic'@'%' with grant option;
flush privileges;

MySql 8.0 版本

CREATE user 'nacos'@'%' IDENTIFIED by 'password' ;
GRANT ALL PRIVILEGES ON nacos_config.* TO 'nacos' with grant option;
最近更新：:
Contributors: womeng