【檀越剑指大厂--mysql】mysql高阶篇

客官°小女子只卖身不卖艺 2024-04-08 09:31 155阅读 0赞

#### 文章目录 ####

*   *   *  一.Mysql 基础
         *   *  1.数据库与实例?
             *  2.mysql 的配置文件
             *  3.mysql 体系结构
             *  4.innodb 的特点?
             *  5.innodb 和 myisam 的区别
             *  6.其他存储引擎?
             *  7.什么是物理日志和逻辑日志?
             *  8.什么是异步 IO?
             *  9.QPS 和 TPS
         *  二.Innodb 引擎特性
         *   *  1.InnoDB 的关键特性?
             *  2.什么是插入缓冲?
             *  3.什么是 Change Buffer?
             *  4.什么是 merge insert buffer?
             *  5.什么是二次写?
             *  6.什么是自适应哈希索引?
             *  7.什么是刷新邻接页?
             *  8.什么事 MRR 优化?
             *  9.什么是 ICP 优化?
             *  10.如何避免离散读?
             *  11.说说 purge 操作?
             *  12.如何保证二进制和事务提交的一致性?
             *  13.InnoDB 的后台线程?
             *  14.innodb 内存分配?
             *  15.LRU List,Free List,Flush list 区别?
             *  16.针对全表扫描,如何保证热点数据不被冲掉?
             *  17.压缩页的表?
             *  18.什么是 checkpoint?
             *  19.group commit 有什么好处?使用时需要注意什么?
         *  三.Mysql 文件
         *   *  1.mysql 文件有哪些?
             *  2.二进制日志的作用?
             *  3.说说 binlog\_format 参数?
             *  4.表空间文件?
             *  5.重做日志块?
             *  6.重做日志缓冲?
             *  7.重做日志文件?
             *  8.重做日志的格式?
             *  9.undo log 和 redo log?
             *  10.什么是 LSN?
             *  11.详细说下 undo log?
             *  12.redo log 和 bin log?
         *  四.Mysql 数据结构
         *   *  1.mysql 使用的索引数据结构是什么?
             *  2.高度为 4 的 B+树能存储多少数据?
             *  3.为什么选用 B+树做索引?
             *  4.为什么不用 hash 表做索引?
             *  5.什么是索引组织表?
             *  6.innodb 逻辑存储结构
             *  7.共享表空间
             *  8.innodb 中的段
             *  9.innodb 中的区
             *  10.innodb 中的页
             *  11.innodb 中的行
             *  12.行格式的种类
             *  13.行溢出数据
             *  14.innodb 的页结构
         *  五.Mysql 索引
         *   *  1.什么是聚集索引?
             *  2.什么是辅助索引?
             *  3.什么是联合索引?
             *  4.什么是覆盖索引?
             *  5.什么是最左前缀原则?
             *  6.什么是自适应 hash 索引?
             *  7.什么是全文索引?
             *  8.全文索引的语法有了解吗?
             *  9.如何选择表的列作为索引更加有效?
             *  10.约束
             *  11.什么是视图?
             *  12.优化器不使用索引的情况
         *  六.Mysql 锁相关
         *   *  1.事务的分类?
             *  2.什么是事务的 ACID?
             *  3.mysql 事务隔离级别?
             *  4.innodb 中的锁有哪几种?
             *  5.innodb 意向锁?
             *  6.自增长锁?
             *  7.lock 和 latch 的区别?
             *  8.什么是一致性非锁定读的?
             *  9.一致性锁定读?
             *  10.innodb 行锁的三种算法?
             *  11.next-key lock 有什么作用?
             *  12.什么是丢失更新?如何避免?
             *  13.什么是脏读?
             *  14.如何预防数据库死锁?
             *  15.mysql 分布式事务有了解吗?
             *  16.mysql 自身有没有需要考虑分布式事务的?
             *  17.如何解决幻读问题?
         *  七.主从复制
         *   *  1.主从复制的原理?
             *  2.SBR 和 RBR?
             *  3.主从复制有几种方式?
             *  4.主从复制有什么好处?
             *  5.mysql 的热备和冷备?
         *  八.高阶原理
         *   *  1.什么事 FIC?
             *  2.有没有比 FIC 更好的方式?
             *  3.详细说说 Cardinality?
             *  4.什么是离散读?
             *  7.Mysql 如何获取数据页?
             *  8.innodb 主键自增是如何保证的?
             *  9.mysql 单表优化有什么经验吗?
             *  10.表分区有什么优缺点?
             *  11.表分区有几种方式?
             *  12.垂直拆分和水平拆分?
             *  13.分片有什么需要注意的吗?

#### 一.Mysql 基础 ####

##### 1.数据库与实例? #####

*  数据库：物理操作系统文件或其他形式文件类型的集合。在 MySQL 数据库中，数据库文件可以是 frm、MYD、MYI、ibd 结尾的文件。当使用 NDB 引擎时，数据库的文件可能不是操作系统上的文件，而是存放于内存之中的文件，但是定义仍然不变。
 *  实例：MySQL 数据库由后台线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。需要牢记的是，数据库实例才是真正用于操作数据库文件的。

从概念上来说，**数据库是文件的集合**，是依照某种数据模型组织起来并存放于二级存储器中的数据集合合；

**数据库实例是程序**，是位于用户与操作系统之间的一层数据管理软件，用户对数据库数据的任何操作，包括数据库定义、数据查询、数据维护、数据库运行控制等都是在数据库实例下进行的，应用程序只有通过数据库实例才能和数据库打交道。

##### 2.mysql 的配置文件 #####

MySQL 数据库是按/etc/mycnf>/etc/mysql/my.cnf→/usr/local/mysql/etc/mycnf~/mycnf 的顺序读取配置文件的。  
如果几个配置文件中都有同一个参数，MySQL 数据库会以读取到的最后一个配置文件中的参数为准。在 Linux 环境  
下，配置文件一般放在/etc/my.cnf 下。在 Windows 平台下，配置文件的后缀名可能是 cnf，也可能是 ini。

##### 3.mysql 体系结构 #####

![image-20220413004330195][]

##### 4.innodb 的特点? #####

InnoDB 存储引擎支持事务，其设计目标主要面向在线事务处理(OLTP)的应用。其特点是行锁设计、支持外键，并支持类似于 Oracle 的非锁定读，即默认读取操作不会产生锁。从 MySQL 数据库 5.5.8 版本开始，InnoDB 存储引擎是默认的存储引擎。innodb 将数据放在一个独立的表空间.索引和数据存储在表独立的 idb 文件.

InnoDB 通过使用多版本并发控制 (MVCC) 来获得高并发性，并且实现了 SQL 标准的 4 种隔离级别，默认为 REPEATABLE 级别。同时，使用一种被称为 next-key locking 的策略来避免幻读(phantom）现象的产生。除此之外，InnoDB 储存引擎还提供了插入缓冲 (insert buffer)、二次写 (double write）、自适应哈希索引(adaptive hash index）、预读 (read ahead）等高性能和高可用的功能。

对于表中数据的存储，InnoDB 存储引擎采用了聚集 (clustered）的方式，因此每张表的存储都是按主键的顺序进行存放。如果没有显式地在表定义时指定主键，InnoDB 存储引擎会为每一行生成一个 6 字节的 ROWID，并以此作为主键。

**innodb 的架构**

![image-20220815160603290][]

##### 5.innodb 和 myisam 的区别 #####

\*\*MyISAM:\*\*MyISAM 引擎是 MySQL5.5.8 及之前版本的默认引擎，它的特点是:

*  不支持行锁，读取时对需要读到的所有表加锁，写入时则对表加排它锁不支持事务不支持外键
 *  不支持崩溃后的安全恢复
 *  在表有读取查询的同时，支持往表中插入新纪录
 *  支持 BLOB 和 TEXT 的前 500 个字符索引，支持全文索引。支持延迟更新索引，极大提升写入性能
 *  对于不会进行修改的表，支持压缩表，极大减少磁盘空间占用
 *  存储数据使用 MYD(数据文件)和 MYI(索引文件)

\*\*InnoDB:\*\*InnoDB 在 MySQL5.5.8 后成为默认引擎，它的特点是:

*  支持行锁，默认为行级锁
 *  采用 MVCC 来支持高并发,支持事务,支持外键
 *  支持崩溃后的安全恢复。
 *  使用 idb 文件存储索引和数据

##### 6.其他存储引擎? #####

*  NDB:集群存储引擎,将数据全部放在内存中,不是磁盘中.
 *  Memory:数据存放在内存,适合纬度表,使用 hash 索引
 *  Archive:只支持 insert 和 update 操作.高速的插入和压缩.
 *  Maria:myisam 的后续版本

##### 7.什么是物理日志和逻辑日志? #####

物理日志和逻辑日志在存储内容上有很大区别，存储内容是区分它们的最重要手段。

物理日志：

*  存储内容：存储数据库中特定记录的变更，通常是 page oriented，即描述具体某一个 page 的修改操作；
 *  例子：一条更新请求对应的初始值（original value）以及更新值（after value）；
 *      "Page 42:image at 367,2; before:'ke';after:'ca'”
    
     *  Page 42 用于说明更新操作作用的 page；
     *  367：用于说明更新操作相对于 page 的 offset；
     *  2：用于说明更新操作的作用长度，即 length，2 代表仅仅修改了两个字符；
     *  before:‘Ke’：这里表示 undo information，也可以称为 undo log；
     *  after:‘ca’：这里表示 redo log information，也可以称为 redo log；

逻辑日志：

*  存储内容：存储事务中的一个操作；
 *  例子：事务中的 UPDATE、DELETE 以及 INSERT 操作。

##### 8.什么是异步 IO? #####

为了提高磁盘操作性能，当前的数据库系统都采用异步 IO(AsynchronousO，AIO)的方式来处理磁盘操作。InnoDB 存储引擎亦是如此。与 AIO 对应的是 Sync IO，即每进行一次 O 操作，需要等待此次操作结束才能继续接下来的操作。用户可以在发出一个 IO 请求后立即再发出另一个 IO 请求，当全部请求发送完毕后，等待所有 IO 操作的完成，这就是 AIO。

AIO 的另一个优势是可以进行 IO Merge 操作，也就是将多个 IO 合并为 1 个 IO，这样可以提高 IOPS 的性能。例如用户需要访问页的(space，page\_no)为:(8,6)、(8,7)，(8,8)  
每个页的大小为 16KB，那么同步 IO 需要进行 3 次 IO 操作。而 AIO 会判断到这三个页是连续的(显然可以通过(space，page\_no)得知。因此 AIO 底层会发送一个 IO 请求，从(8，6)开始，读取 48KB 的页。

##### 9.QPS 和 TPS #####

*  QPS:每秒请求数(Question Per Se econd)
 *  TPS:每秒事务处理的能力(Transac tion Per Second, TPS)

#### 二.Innodb 引擎特性 ####

##### 1.InnoDB 的关键特性? #####

*  插入缓冲(Insert Buffer)
 *  两次写(Double Write)
 *  自适应哈希索引(Adaptive Hash Index)
 *  异步 IO(Async IO)
 *  刷新邻接页(Flush Neighbor Page)

##### 2.什么是插入缓冲? #####

InnoDB 存储引擎开创性地设计了 Insert Buffer，对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入;若不在，则先放入到一个 Insert Buffer 对象中，好似欺骗。数据库这个聚集的索引已经插到叶子节点，而实际并没有，只是存放在另一个位置。然后再以一定的频率和情况进行 Insert Buffer 和辅助索引页子节点的 merge(合并)操作，这时通常能将多个插入合并到一个操作中(因为在一个索引页中)，这就大大提高了对于非聚集索引插入的性能。

然而 Insert Buffer 的使用需要同时满足以下两个条件:

*  索引是辅助索引(secondaryindex);
 *  索引不是唯一(unique)的。

当满足以上两个条件时，InnoDB 存储引擎会使用 Insert Buffer，这样就能提高插入操作的性能了。

辅助索引不能是唯一的，因为在插入缓冲时，数据库并不去查找索引页来判断插入的记录的唯一性。如果去查找肯定又会有离散读取的情况发生，从而导致 Insert Buffer 失去了意义。

**内部实现**

Insert Buffer 的数据结构是一棵 B+树。在 MvSQL4.1 之前的版本中每张表有一棵 Insert BufferB+树。而在现在的版本中，全局只有一棵 Insert BufferB+树，负责对所有的表的辅助索引进行 Insert Buffer。而这棵 B+树存放在共享表空间中，默认也就是 ibdata1 中。

非叶节点存放的是查询的 search key(键值)，其构造如图所示。

![image-20220816215001307][]

search key 一共占用 9 个字节，其中 space 表示待插入记录所在表的表空间 id，在 InnoDB 存储引擎中，每个表有一个唯一的 space id 可以通过 space id 查询得知是哪张表。space 占用 4 字节。marker 占用 1 字节，它是用来兼容老版本的 InsertBuffer。offset 表示页所在的偏移量，占用 4 字节。

当一个辅助索引要插入到页(space，offset)时，如果这个页不在缓冲池中，那么 InnoDB 存储引擎首先根据上述规则构造一个 search key，接下来查询 Insert Buffer 这棵 B+树，然后再将这条记录插入到 Insert BufferB+树的叶子节点中。  
对于插入到 InsertBufferB+树叶子节点的记录(如图 2-4 所示)，并不是直接将待插入的记录插入，而是需要根据如下的规则进行构造:

![image-20220816215054710][]  
space、marker、offset 字段和之前非叶节点中的含义相同，一共占用 9 字节。第 4 个字段 metadata 占用 4 字节，其存储的内容如表 2-2 所示。

![image-20220816215152529][]

IBUFREC\_OFFSET\_COUNT 是保存两个字节的整数，用来排序每个记录进入 InsertBuffer 的顺序。因为从 InnoDB1.0.x 开始支持 Change Buffer，所以这个值同样记录进入 InsertBuffer 的顺序。通过这个顺序回放(replay)才能得到记录的正确值。从 InsertBuffer 叶子节点的第 5 列开始，就是实际插入记录的各个字段了。因此较之原插入记录，Insert Buffer B+树的叶子节点记录需要额外 13 字节的开销。

因为启用 Insert Buffer 索引后，\*\*辅助索引页(space，page\_no)\*\*中的记录可能被插入到 Insert Buffer B+树中，所以为了保证每次 Merge Insert Buffer 页必须成功，还需要有一个特殊的页用来标记每个辅助索引页(space，page\_no)的可用空间。这个页的类型为 Insert Buffer Bitmap。每个 Insert Buffer Bitmap 页用来追踪 16384 个辅助索引页，也就是 256 个区(Extent)。每个 Insert Buffer Bitmap 页都在 16384 个页的第二个页中。

每个辅助索引页在 Insert BufferBitmap 页中占用 4 位(bit).

![image-20220816215307055][]

##### 3.什么是 Change Buffer? #####

INSERT、DELETE、UPDATE 都进行缓冲，他们分别是:Insert Buffer Delete Buffer Purge buffer  
当然和之前 Insert Buffer 一样，Change Buffer 适用的对象依然是非唯一的辅助索引。  
对一条记录进行 UPDATE 操作可能分为两个过程:

*  将记录标记为已删除;
 *  真正将记录删除。

因此 Delete Buffer 对应 UPDATE 操作的第一个过程，即将记录标记为删除。Purge Buffer 对应 UPDATE 操作的第二个过程，即将记录真正的删除。同时，InnoDB 存储引擎提供了参数 innodb\_change\_buffering，用来开启各种 Buffer 的选项。该参数可选的值为:inserts、deletes、purges、changes、allnone。 inserts、deletes purges 就是前面讨论过的三种情况。changes 表示启用 inserts 和 deletes，all 表示启用所有，none 表示都不启用。该参数默认值为 all。

innodb\_change\_buffermaxsize 来控制 ChangeBuffer 最大使用内存的数量:

innodb\_change\_buffermax\_size 值默认为 25，表示最多使用 1/4 的缓冲池内存空间。而需要注意的是，该参数的最大有效值为 50,最大使用 1/2 的缓冲池内存空间.

##### 4.什么是 merge insert buffer? #####

将 insert buffer 中的数据合并到辅助索引页中.

概括地说，MergeInsertBuffer 的操作可能发生在以下几种情况下:

*  辅助索引页被读取到缓冲池时;
 *  Insert Buffer Bitmap 页追踪到该辅助索引页已无可用空间时:
 *  Master Thread.

Insert Buffer Bitmap 页用来追踪每个辅助索引页的可用空间，并至少有 1/32 页的空间。若插入辅助索引记录时检测到插入记录后可用空间会小于 1/32 页，则会强制进行一个合并操作，即强制读取辅助索引页，将 Insert Buffer B+树中该页的记录及待插入的记录插入到辅助索引页中。这就是上述所说的第二种情况

##### 5.什么是二次写? #####

如果说 Insert Buffer 带给 InnoDB 存储引擎的是性能上的提升，那么 double write(两次写)带给 InnoDB 存储引擎的是数据页的可靠性。当发生数据库宕机时，可能 InnoDB 存储引擎正在写入某个页到表中，而这个页只写了一部分，比如 16KB 的页，只写了前 4KB，之后就发生了宕机，这种情况被称为部分写失效(partial page write)。

在应用(apply)重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是 double write。在 InnoDB 存储引擎中 double write 的体系架构如图

![image-20220817091355684][]

doublewrite 由两部分组成，一部分是内存中的 double write buffer，大小为 2MB，另一部分是物理磁盘上共享表空间中连续的 128 个页，即 2 个区(extent)，大小同样为 2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是会通过 memcpy 函数将脏页先复制到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次，每次 1MB 顺序地写入共享表空间的物理磁盘上,然后马上调用 fsync 函数，同步磁盘，避免缓冲写带来的问题。在这个过程中，因为 doublewrite 页是连续的，因此这个过程是顺序写的，开销并不是很大。在完成 doublewrite 页的写入后，再将 doublewrite buffer 中的页写入各个表空间文件中，此时的写入则是离散的。

如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB 存储引擎可以从共享表空间中!的 doublewrite 中找到该页的一个副本，将其复制到表空间文件，再应用重做日志。

##### 6.什么是自适应哈希索引? #####

哈希(hash)是一种非常快的查找方法，在一般情况下这种查找的时间复杂度为 O(1)，即一般仅需要一次查找就能定位数据。

InnoDB 存储引擎会监控对表上各索引页的查询。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index AHI)。AHI 是通过缓冲池的 B+树页构造而来，因此建立的速度很快，而且不需要对整张表构建哈希索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动地为某些热点页建立哈希索引。

*  AHI 有一个要求，即对这个页的连续访问模式必须是一样的。访问模式一样指的是查询的条件一样，若交替进行上述两种查询
 *  以该模式访问了 100 次
 *  页通过该模式访问了 N 次，其中 N=页中记录\*1/16

自适应哈希索引采用哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA 本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如

SELECT * FROM TABLE WHERE index_col='xxx';

但是对于范围查找就无能为力了。通过命令可以看到当前自适应哈希索引的使用状况

SHOW ENGINE INNODB STATUS;

哈希索引只能用来搜索等值的查询

##### 7.什么是刷新邻接页? #####

InnoDB 存储引擎还提供了 Flush NeighborPage(刷新邻接页)的特性。其工作原理为:当刷新一个脏页时，InnoDB 存储引擎会检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过 AIO 可以将多个 IO 写入操作合并为一个 IO 操作，故该工作机制在传统机械磁盘下有着显著的优势。械硬盘建议启用该特性，而对于固态硬盘有着超高 IOPS 性能的磁盘，则建议将该参数设置为 0，即关闭此特性。

参数 innodb flush neighbors，用来控制是否启用该特性。

##### 8.什么事 MRR 优化? #####

multi-range read 优化

MySQL5.6 版本开始支持 Multi-Range Read(MRR)优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。Multi-RangeRead 优化可适用于 range，ref，eq\_ref 类型的询。  
MRR 优化有以下几个好处

*  MRR 使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
 *  减少缓冲池中页被替换的次数。
 *  批量处理对键值的查询操作。

对于 InnoDB 和 MyISAM 存储引擎的范围查询和 JOIN 查询操作，MRR 的工作方式如下:

*  将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
 *  将缓存中的键值根据 RowlD 进行排序。
 *  根据 RowlD 的排序顺序来访问实际的数据文件。

此外，若 InnoDB 存储引擎或者 MyISAM 存储引擎的缓冲池不是足够大，即不能存放下一张表中的所有数据，此时频繁的离散读操作还会导致缓存中的页被替换出缓冲池，然后又不断地被读入缓冲池。若是按照主键顺序进行访问，则可以将此重复行为降为最低。在执行计划中可以看到 Using MRR

##### 9.什么是 ICP 优化? #####

lndex Condition Pushdown (ICP)优化

和 Multi-Range Read 一样，Index Condition Pushdown 同样是 MySQL5.6 开始支持的一种根据索引进行查询的优化方式。之前的 MySQL 数据库版本不支持 Index Condition Pushdown，当进行索引查询时，首先根据索引来查找记录，然后再根据 WHERE 条件来过滤记录。在支持 Index Condition Pushdown 后，MySQL 数据库会在取出索引的同时，判断是否可以进行 WHERE 条件的过滤，也就是将 WHERE 的部分过滤操作放在了存储引擎层。在某些查询下，可以大大减少上层 SQL 层对记录的索取(fetch)，从而提高数据库的整体性能。

Index Condition Pushdown 优化支持 range、ref、eq\_ref 、ref\_or\_null 类型的查询，当前支持 MyISAM 和 InnoDB 存储引擎。当优化器选择 Index Condition Pushdown 优化时，可在执行计划的列 Extra 看到 Using index condition 提示。

##### 10.如何避免离散读? #####

MySQL5.6 之前，优化器在进行离散读决策的时候，如果数据量比较大，会选择使用聚集索引，全表扫描。

MySQL5.6 版本开始支持 Multi-Range Read（MRR）优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，这对于 IO-bound 类型的 SQL 查询语句可带来性能极大的提升。Multi-Range Read 优化可适用于 range，ref，eq\_ref 类型的查询。

MRR 优化有以下几个好处：

*  MRR 使数据访问变得较为顺序。在查询辅助索引时，首先根据得到的查询结果，按照主键进行排序，并按照主键排序的顺序进行书签查找。
 *  减少缓冲池中页被替换的次数。（顺序查找可以对一个页进行顺序查找，无需离散加载数据页）
 *  批量处理对键值的查询操作。
 *  对于 InnoDB 和 MyISAM 存储引擎的范围查询和 JOIN 查询操作，MRR 的工作方式如下：
 *  将查询得到的辅助索引键值存放于一个缓存中，这时缓存中的数据是根据辅助索引键值排序的。
 *  将缓存中的键值根据 RowID 进行排序。
 *  根据 RowID 的排序顺序来访问实际的数据文件。

举例说明：

SELECT * FROM salaries WHERE salary＞10000 AND salary＜40000;

salary 上有一个辅助索引 idx\_s，因此除了通过辅助索引查找键值外，还需要通过书签查找来进行对整行数据的查询。当不启用 Multi-Range Read 特性时，看到的执行计划如图：

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-imoHSyZP-1661597294025)(http://qinyingjie.cn/pic/61dc9d624129a7248020bd6f35dfb110-20220827175532172.png)\]

若启用 Mulit-Range Read 特性,则除了会在列 Extra 看到 Using index condition 外，还会看见 Using MRR 选项

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PytaXvIJ-1661597294025)(http://qinyingjie.cn/pic/99d6dee7a4da0c21b58f2205917a1b6e-20220827175534823.png)\]

![img][]

Multi-Range Read 还可以将某些范围查询， 拆分为键值对， 以此来进行批量的数据查询 。 这样做的好处是可以在 拆分过程中， 直接过滤一些不符合查询条件的数据， 例如：

SELECT * FROM t WHERE key_part1 ＞=1000 AND key_part1 ＜ 2000 AND key_part2=10000;

表 t 有（key\_part1，key\_part2）的联合索引，因此索引根据 key\_part1，key\_part2 的位置关系进行排序。若没有 Multi-Read Range，此时查询类型为 Range，SQL 优化器会先将 key\_part1 大于 1000 且小于 2000 的数据都取出，即使 key\_part2 不等于 1000。待取出行数据后再根据 key\_part2 的条件进行过滤。这会导致无用数据被取出。如果有大量的数据且其 key\_part2 不等于 1000，则启用 Mulit-Range Read 优化会使性能有巨大的提升。

倘若启用了 Multi-Range Read 优化，优化器会先将查询条件进行拆分，然后再进行数据查询。就上述查询语句而言，优化器会将查询条件拆分为（1000，10000），（1001，10000），（1002，10000），…，（1999，10000），最后再根据这些拆分出的条件进行数据的查询。

我是如何优化的：在非必要的情况下，拒绝使用 select\*；在必须 select\*的情况下，尽量使用 MySQL5.6+的版本开启 MRR；在必须 select\*的情况下且 MySQL 小于 5.6 版本下，可以根据数据量进行离散读和聚集索引两种情况下的性能进行对比，必要时采用 force index 语句强制指定索引。

##### 11.说说 purge 操作? #####

purge 用于最终完成 delete 和 update 操作。这样设计是因为 InnoDB 存储引擎支持 MVCC，所以记录不能在事务提交时立即进行处理。这时其他事物可能正在引用这行，故 InnoDB 存储引擎需要保存记录之前的版本。而是否可以删除该条记录通过 purge 来进行判断。若该行记录已不被任何其他事务引用，那么就可以进行真正的 delete 操作。可见，purge 操作是清理之前的 delete 和 update 操作，将上述操作“最终”完成。而实际执行的操作为 delete 操作，清理之前行记录的版本。

delete 和 update 操作可能并不直接删除原有的数据。例如，

DELETE FROM t WHERE a=1;

表 t 上列 a 有聚集索引，列 b 上有辅助索引。对于上述的 delete 操作，仅是将主键列等于 1 的记录 delete flag 设置为 1，记录并没有被删除，即记录还是存在于 B+树中。其次，对辅助索引上 a 等于 1，b 等于 1 的记录同样没有做任何处理。而真正删除这行记录的操作其实被“延时”了，最终在 purge 操作中完成。

##### 12.如何保证二进制和事务提交的一致性? #####

MySQL5.6 采用了 Binary LogGroup Commit (BLGC).  
MySQL5.6 BLGC 的实现方式是将事务提交的过程分为几个步骤

在 MySQL 数据库上层进行提交时首先按顺序将其放入一个队列中,队列中的第一个事务称为 leader，其他事务称为 follower，leade 制着 follower 的行为。BLGC 的步骤分为以下三个阶段:

*  Flush 阶段，将每个事务的二进制日志写入内存中。
 *  Sync 阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次 fsync 操作就完成了二进制日志的写入，这就 BLGC。
 *  Commit 阶段，leader 根据顺序调用存储引擎层事务的提交 InnoDB 存储引擎本就支持 group commit，因此修复了原先由于 prepare\_commit\_mutex 导致 group commit 失效的问题。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1f0Ijmsy-1661597294025)(http://qinyingjie.cn/pic/image-20220827155017240.png)\]

因为备份及恢复的需要，例如通过工具 xtrabackup 或者 ibbackup 进行备份，并用来建立 replication，如下图所示。

![image-20220412185319769][]

可以看到若通过在线备份进行数据库恢复来重新建立 replication，事务 T1 的数据会产生丢失。因为在 InnoDB 存储引擎层会检测最后一次的事务 T3 在上下两层都完成了提交，不需要再进行恢复，故认为之前的 T1,T2 也都完成了提交。

因此通过锁 prepare\_commit\_mutex 以串行的方式来保证顺序性，然而这会使 group commit 无法生效

##### 13.InnoDB 的后台线程? #####

核心线程如下：

*  **Master Thread**：是一个非常核心的后端线程，主要负责将缓冲池中的数据异步刷新到磁盘中，保证数据一致性。包括脏页的刷新、insert buffer、undo 页的回收等。
 *  **IO Thread**： InnoDB 引擎使用了大量的异步 IO 来处理 写 IO 请求，这样可以极大的提高数据库的性能。而 IO Thread 线程主要就是这些 IO 请求的一个回调处理。
 *  **Purge Thread**：当事务被提交之后，用于回收可能不再需要的 undo log 所使用的页。
 *  **Page Cleaner Thread**：为了提高 InnoDB 引擎的性能，在 1.2x 版本引入该线程，主要用于将之前版本中脏页的刷新操作放入单独线程，目的为了减轻原 Master Thread 线程的压力。

##### 14.innodb 内存分配? #####

读取数据是基于页的,会首先判断页是否存在缓冲池中,如果存在,直接使用,不存在从磁盘读取.

*  可以有多个缓冲池实例,默认为 1
 *  使用 checkpoint 机制刷新到磁盘

![image-20220815160912443][]

*  缓冲池
    
     *  数据页
     *  索引页
     *  插入缓冲
     *  锁信息
     *  自适应哈希索引
     *  数据字典信息
 *  重做日志缓冲
 *  额外内存池:缓冲控制对象等需要从额外内存池分配内存.

##### 15.LRU List,Free List,Flush list 区别? #####

*  LRU List(lru 列表):在缓冲池中,使用了 lru 算法,存储缓冲池中的页,缓冲池中页的大小默认为 16kb,但是并不是插入到首位置,而是有个 midpoint 的概念,使用 innodb\_old\_blocks\_pct 参数进行设置,默认为 37,插入在距离末尾 37%的位置,midpoint 之后的数据为 old 数据,之前的位 new 数据,也是热点数据.这样做的优点是防止热点数据被刷出缓存池.并且通过 innodb\_old\_blocks\_time 参数,表示等待多久加入到缓冲池的热端.同样是防止热点数据不被刷出.
 *  Free List:刚启动的时候,lru 列表是空的,页是存放在 free 列表中的.需要时从 free 列表划分到 LRU 列表.
 *  Flush list:脏页列表,缓存行中的页数据和磁盘不一致.脏页既存在 Flush 列表,也存在于 Lru 列表,Lru 列表用于页的可用性,Flush 列表用于刷新到磁盘.

##### 16.针对全表扫描,如何保证热点数据不被冲掉? #####

针对全表扫描时，短时间内访问大量使用频率非常低的页面情况的优化,在进行全表扫描时，虽然首次被加载到 Buffer Pool 的页被放到了 old 区域的头部，但是后续会被马上访问到，每次进行访问的时候又会把该页放到 young 区域的头部，这样仍然会把那些使用频率比较高的页给顶下去。全表扫描有一个特点，那就是它的执行频率非常低，谁也不会没事儿老在那写全表扫描的语句玩，而且在执行全表扫描的过程中，即使某个页面中有很多条记录，也就是去多次访问这个页面所花费的时间也是非常少的。

所以我们只需要规定，**在对某个处在 old 区域的缓存页进行第一次访问时就在它对应的控制块中记录下来这个访问时间，如果后续的访问时间与第一次访问的时间在某个时间间隔内，那么该页面就不会被从 old 区域移动到 voung 区域的头部，否则将它移动到 voung 区域的头部**。上述的这个间隔时间是由系统变 innodb\_old\_blocks\_time 控制的.这个 innodb\_old\_blocks\_time 的默认值是 1000，它的单位是毫秒，也就意味着对于从磁盘上被加载到 LRU 链表的 old 区域的某个页来说，如果第一次和最后一次访问该页面的时间间隔小于 1s (很明显在一次全表扫描的过程中，多次访问一个页面中的时间不会超过 1s)，那么该页是不会被加入到 young 区域的.如果我们把 innodb old blockstime 的值设置为 0，那么每次我们访问一个页面时就会把该页面放到 young 区域的头部。

##### 17.压缩页的表? #####

首先，在 unzipLRU 列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存的分配。例如对需要从缓冲池中申请页为 4KB 的大小，其过程如下:

1.  检查 4KB 的 unzip\_LRU 列表，检查是否有可用的空闲页;
2.  若有，则直接使用;
3.  否则，检查 8KB 的 unzip\_LRU 列表;
4.  若能够得到空闲页，将页分成 2 个 4KB 页，存放到 4KB 的 unzip\_LRU 列表;
5.  若不能得到空闲页，从 LRU 列表中申请一个 16KB 的页，将页分为 1 个 8KB 的页、2 个 4KB 的页，分别存放到对应的 unzip\_LRU 列表中。

##### 18.什么是 checkpoint? #####

为了避免发生数据丢失的问题，当前事务数据库系统普遍都采用了 Write Ahead log 策略即当事务提交时，先写重做日志，再修改页。当由干发生宕机而导致数据丢失时，通过重做日志来完成数据的恢复。这也是事务 ACID  
中 D(Durability 持久性)的要求。

**checkpoint 的作用**

*  缩短数据库的恢复时间;
 *  缓冲池不够用时，将脏页刷新到磁盘;Lru 列表不够用时,强制刷新脏页
 *  重做日志不可用时，刷新脏页。

对于 InnoDB 存储引擎而言，其是通过 LSN(LogSequence Number)来标记版本的。而 LSN 是 8 字节的数字，其单位是字节。每个页有 LSN，重做日志中也有 LSN，Checkpoint 也有 LSN。

**有两种 Checkpoint**，分别为:

*  Sharp Checkpoint
 *  Fuzzy Checkpoint

Sharp Checkpoint 发生在数据库关闭时将所有的脏页都刷新回磁盘，这是默认的工作方式，即参数 innodb\_fast\_shutdown=1

但是若数据库在运行时也使用 Sharp Checkpoint，那么数据库的可用性就会受到很大的影响。故在 InnoDB 存储引擎内部使用 Fuzzy Checkpoint 进行页的刷新，即只刷新一部分脏页，而不是刷新所有的脏页回磁盘。  
在 InnoDB 存储引擎中可能发生如下几种情况的 Fuzzy Checkpoint:

*  Master Thread Checkpoint 固定频率刷新到磁盘
 *  FLUSH LRU LIST Checkpoint 缓存池中页的数量不足
 *  Async/Sync Flush Checkpoint 保证重做日志的循环使用
 *  Dirty Page too much Checkpoint 脏页太多,innodb\_max\_dirty\_pages\_pct,默认 90%

##### 19.group commit 有什么好处?使用时需要注意什么? #####

若事务为非只读事务，则每次事务提交时需要进行一次 fsync 操作，以此保证重做日志都已经写入磁盘。当数据库发生宕机时，可以通过重做日志进行恢复。虽然固态硬盘的出现提高了磁盘的性能，然而磁盘的 fsync 性能是有限的。为了提高磁盘 fsync 的效率，当前数据库都提供了 group commit 的功能，即一次 fsync 可以刷新确保多个事务日志被写入文件。对于 InnoDB 存储引擎来说，事务提交时会进行两个阶段的操作：

1.  修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
2.  调用 fsync 将确保日志都从重做日志缓冲写入磁盘。

步骤 2）相对步骤 1）是一个较慢的过程，这是因为存储引擎需要与磁盘打交道。但当有事务进行这个过程时，其他事务可以进行步骤 1）的操作，正在提交的事物完成提交操作后，再次进行步骤 2）时，可以将多个事务的重做日志通过一次 fsync 刷新到磁盘，这样就大大地减少了磁盘的压力，从而提高了数据库的整体性能。对于写入或更新较为频繁的操作，group commit 的效果尤为明显。

然而在 InnoDB1.2 版本之前，在开启二进制日志后，InnoDB 存储引擎的 group commit 功能会失效，从而导致性能的下降。并且线上环境多使用 replication 环境，因此二进制日志的选项基本都为开启状态，因此这个问题尤为显著。

导致这个问题的原因是在开启二进制日志后，为了保证存储引擎层中的事务和二进制日志的一致性，二者之间使用了两阶段事务，其步骤如下：

1.  当事务提交时 InnoDB 存储引擎进行 prepare 操作。
2.  MySQL 数据库上层写入二进制日志。
3.  InnoDB 存储引擎层将日志写入重做日志文件。
    
     *  a)修改内存中事务对应的信息，并且将日志写入重做日志缓冲。
     *  b)调用 fsync 将确保日志都从重做日志缓冲写入磁盘。

![image-20220412185022416][]

为了保证 MySQL 数据库上层二进制日志的写入顺序和 InnoDB 层的事务提交顺序一致，MySQL 数据库内部使用了 prepare\_commit\_mutex 这个锁。但是在启用这个锁之后，步骤 3）中的步骤 a）步不可以在其他事务执行步骤 b）时进行，从而导致了 group commit 失效。

一旦步骤 2）中的操作完成，就确保了事务的提交，即使在执行步骤 3）时数据库发生了宕机。此外需要注意的是，每个步骤都需要进行一次 fsync 操作才能保证上下两层数据的一致性。步骤 2）的 fsync 由参数 sync\_binlog 控制，步骤 3）的 fsync 由参数 innodb\_flush\_log\_at\_trx\_commit 控制。因此上述整个过程如下图所示。

这个问题最早在 2010 年的 MySQL 数据库大会中提出，Facebook MySQL 技术组，Percona 公司都提出过解决方案。最后由 MariaDB 数据库的开发人员 Kristian Nielsen 完成了最终的“完美”解决方案。在这种情况下，不但 MySQL 数据库上层的二进制日志写入是 group commit 的，InnoDB 存储引擎层也是 group commit 的。此外还移除了原先的锁 prepare\_commit\_mutex，从而大大提高了数据库的整体性。MySQL5.6 采用了类似的实现方式，并将其称为 Binary Log Group Commit（BLGC）。

MySQL5.6 BLGC 的实现方式是将事务提交的过程分为几个步骤来完成，如下图所示。

![image-20220412185059402][]

在 MySQL 数据库上层进行提交时首先按顺序将其放入一个队列中，队列中的第一个事务称为 leader，其他事务称为 follower，leader 控制着 follower 的行为。BLGC 的步骤分为以下三个阶段：

*  Flush 阶段，将每个事务的二进制日志写入内存中。
 *  Sync 阶段，将内存中的二进制日志刷新到磁盘，若队列中有多个事务，那么仅一次 fsync 操作就完成了二进制日志的写入，这就是 BLGC。
 *  Commit 阶段，leader 根据顺序调用存储引擎层事务的提交，InnoDB 存储引擎本就支持 group commit，因此修复了原先由于锁 prepare\_commit\_mutex 导致 group commit 失效的问题。

参数 binlog\_max\_flush\_queue\_time 用来控制 Flush 阶段中等待的时间，即使之前的一组事务完成提交，当前一组的事务也不马上进入 Sync 阶段，而是至少需要等待一段时间。这样做的好处是 group commit 的事务数量更多，然而这也可能会导致事务的响应时间变慢。该参数的默认值为 0，且推荐设置依然为 0。除非用户的 MySQL 数据库系统中有着大量的连接（如 100 个连接），并且不断地在进行事务的写入或更新操作。

#### 三.Mysql 文件 ####

##### 1.mysql 文件有哪些? #####

*  参数文件:告诉 MvSQL 实例启动时在哪里可以找到数据库文件。并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
 *  日志文件:用来记录 MySQL 实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。
    
     *  错误日志(error log)
     *  二进制日志(binlog)
     *  慢查询日志(slow query log) long\_query\_time 默认是 10s
     *  查询日志(log)
 *  socket 文件:当用 UNIX 域套接字方式进行连接时需要的文件。
 *  pid 文件:MySQL 实例的进程 ID 文件。
 *  MySQL 表结构文件:用来存放 MySQL 表结构定义文件,文件后缀为 frm。
 *  存储引擎文件:因为 MySQL 表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。本章主要介绍与 InnoDB 有关的存储引擎文件。

##### 2.二进制日志的作用? #####

*  二进制日志(binary log)记录了对 MySQL 数据库执行更改的所有操作，但是不包括 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改。然而，若操作本身并没有导致数据库发生变化，那么该操作可能也会写入二进制日志。单个二进制文件最大为 1G.
 *  如果用户想记录 SELECT 和 SHOW 操作，那只能使用查询日志，而不是二进制日志。
 *  恢复(recovery):某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行 point- in-time 的恢复。
 *  复制(replication):其原理与恢复类似，通过复制和执行二进制日志使一台远程的 MySQL 数据库(一般称为 slave 或 stand by)与一台 MySQL 数据库(一般称为 master 或 primary)进行实时同步。
 *  审计(audit):用户可以通过二进制日志中的信息来进行审计，判断是否有对数据库进行注入的攻击。

在 MySQL 数据库中还有一种二进制日志(bin log)，其用来进行 POINT-IN-TIME(PIT)的恢复及主从复制(Replication)环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，两者有着非常大的不同。

首先，重做日志是在 InnoDB 存储引擎层产生，而二进制日志是在 MySQL 数据库的上层产生的，并且二进制日志不仅仅针对于 InnoDB 存储引擎，MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

其次，两种日志记录的内容形式不同。MySQL 数据库上层的二进制日志是一种逻辑日志，其记录的是对应的 SQL 语句。而 InnoDB 存储引擎层面的重做日志是物理格式日志，其记录的是对于每个页的修改。

此外，两种日志记录写入磁盘的时间点不同司，。二进制日志只在事务提交完成后进行一次写入。 而 InnoDB 存储引擎的重做日志在事务进行中不断地被写入，这表现为日志并不是随事务提交的顺序进行写入的。

二进制日志仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。而对于 InoDB 存储引擎的重做日志，由于其记录的是物理操作日志，因此每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，故其在文件中记录的顺序并非是事务开始的顺序。

##### 3.说说 binlog\_format 参数? #####

binlog\_format 参数十分重要，它影响了记录二进制日志的格式。在 MySQL5.1 版本之前，没有这个参数。所有二进制文件的格式都是基于 SQL 语句(statement)级别的。同时，对于复制是有一定要求的。如在主服务器运行 rand、uuid 等函数，又或者使用触发器等操作，这些都可能会导致主从服务器上表中数据的不一致(not sync)。另一个影响是，会发现 InnoDB 存储引擎的默认事务隔离级别是 REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果使用 READ COMMITTED 的事务隔离级别(大多数数据库，如 Oracle， MicrosoftSQLServer 数据库的默认隔离级别)，会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。

MySQL5.1 开始引入了 binlog format 参数，该参数可设的值有 STATEMENT、ROW 和 MIXED。

*  STATEMENT 格式和之前的 MySQL 版本一样，二进制日志文件记录的是日志的逻辑 SQL 语句。
 *  在 ROW 格式下，二进制日志记录的不再是简单的 SQL 语句了，而是记录表的行更改情况。同时，对上述提及的 Statement 格式下复制的问题予以解决。从 MySQL 5.1 版本开始，如果设置了 binlog\_format 为 ROW，可以将 InnoDB 的事务隔离基本设为 READ COMMITTED，以获得更好的并发性。
 *  在 MIXED 格式下，MySQL 默认采用 STATEMENT 格式进行二进制日志文件的记录，但是在一些情况下会使用 ROW 格式，可能的情况有:
    
     *  表的存储引擎为 NDB，这时对表的 DML 操作都会以 ROW 格式记录。
     *  使用了 UUIDO)、USERO、CURRENT USERO)、 FOUND ROWS()、ROW COUNT()等不确定函数。
     *  使用了 INSERTDELAY 语句。
     *  使用了用户定义函数(UDF)。
     *  使用了临时表(temporarytable)。

##### 4.表空间文件? #####

*  共享表空间
 *  独立表空间

![image-20220818181415925][]

##### 5.重做日志块? #####

在 InnoDB 存储引擎中，重做日志都是以 512 字节进行存储的。这意味着重做日志缓存、重做日志文件都是以块(block)的方式进行保存的，称之为重做日志块(redo log block)，每块的大小为 512 字节。若一个页中产生的重做日志数量大于 512 字节，那么需要分割为多个重做日志块进行存储。此外，由于重做日志块的大小和磁盘扇区大小一样，都是 512 字节，因此重做日志的写入可以保证原子性，不需要 double write 技术。

重做日志块除了日志本身之外，还由日志块头(log block header)及日志块尾(log blocktailer)两部分组成。重做日志头一共占用 12 字节，重做日志尾占用 8 字节。故每个重做日志块实际可以存储的大小为 492 字节(512-12-8)。

![image-20220827152212386][]

日志块头(log block header)由四部分组成.

log buffer 是由 log block 组成，在内部 log buffer 就好似一个数组，因此 LOG\_BLOCK\_HDR\_NO 用来标记这个数组中的位置。其是递增并且循环使用的，占用 4 个字节，但是由于第一位用来判断是否是 flush bit，所以最大的值为 2G。

LOG\_BLOCK\_HDR\_DATA\_LEN 占用 2 字节，表示 log block 所占用的大小。当 log block 被写满时，该值为 0x200，表示使用全部 log block 空间，即占用 512 字节。

LOG\_BLOCK\_FIRST\_REC\_GROUP 占用 2 个字节，表示 log block 中第一个日志所在的偏移量。如果该值的大小和 LOG\_BLOCK\_HDR\_DATA\_LEN 相同，则表示当前 log block 不包含新的日志。

LOG\_BLOCK\_CHECKPOINT\_NO 最后被写入的检查点的位置.占用 4 个字节.

##### 6.重做日志缓冲? #####

InnoDB 存储引擎的内存区域除了有缓冲池外，还有重做日志缓冲(redo log buffer)。InnoDB 存储引擎首先将重  
做日志信息先放入到这个缓冲区，然后按一定频率将其刷新到重做日志文件。重做日志缓冲一般不需要设置得很大，因为一般情况下每一秒钟会将重做日志缓冲刷新到日志文件，因此用户只需要保证每秒产生的事务量在这个缓冲大小之内即可。该值可由配置参数 innodb\_log\_buffer\_size 进行设置,8.0 版本默认是 16M.

**重做日志缓冲刷新到磁盘的情况**

*  Master Thread 每一秒将重做日志缓冲刷新到重做日志文件;
 *  每个事务提交时会将重做日志缓冲刷新到重做日志文件;
 *  当重做日志缓冲池剩余空间小于 1/2 时，重做日志缓冲刷新到重做日志文件。

##### 7.重做日志文件? #####

在默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为 ib logfile0 和 ib logfile1 的文件。是重做日志文件(redo log file)。它们记录了对于 InnoDB 存储引擎的事务日志。当实例或介质失败(mediafailure)时，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB 存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。  
每个 InnoDB 存储引擎至少有 1 个重做日志文件组(group)，每个文件组下至少有 2 个重做日志文件，如默认的 ib\_logfile0 和 ib\_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组(mirrored loggroups)，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB 存储引擎先写重做日志文件 1，当达到文件的最后时，会切换至重做日志文件 2

*  innodb\_log\_fle\_size:每个重做日志文件的大小 最大 512G
 *  innodb\_log\_flesin\_group:组中重做日志文件的数量 默认为 2
 *  innodb\_mirrored\_log\_groups:日志镜像文件组的数量，默认为 1
 *  innodb\_log\_group\_home\_dir:日志文件组所在路径

##### 8.重做日志的格式? #####

在 InnoDB 存储引擎中，对干各种不同的操作有着不同的重做日志格式。到 InnoDB1.2.x 版本为止，总共定义了 51 种重做日志类型。虽然各种重做日志的类型不同，但是它们有着基本的格式，表 3-2 显示了重做日志条目的结构:

![image-20220819130723440][]

*  redo\_log\_type 占用 1 字节，表示重做日志的的类型
 *  space 表示表空间的 ID，但采用压缩的方式，因此占用的空间可能小于 4 字节
 *  page\_no 表示页的偏移量，同样采用压缩的方式
 *  redo\_log\_body 表示每个重做日志的数据部分，恢复时需要调用相应的函数进行解析

![image-20220819130750549][]

从重做日志缓冲往磁盘写入时，是按 512 个字节，也就是一个扇区的大小进行写入。因为扇区是写入的最小单位，因此可以保证写入必定是成功的。因此在重做日志的写入过程中不需要有 double write.

从日志缓冲写入磁盘上的重做日志文件是按一定条件进行的，

*  在主线程中每秒会将重做日志缓冲写入磁盘的重做日志文件中，不论事务是否已经提交。
 *  另一个触发写磁盘的过程是由参数 innodb\_flush\_log\_at\_trx\_commit 控制，表示在提交(commit)操作时，处理重做日志的方式。参数 innodb\_flush\_log\_at\_trx\_commit 的有效值有 0 1 2。
    
     *  0 代表当提交事务时，并不将事务的重做日志写入磁盘上的日志文件，而是等待主线程每秒的刷新。
     *  1 表示在执行 commit 时将重做日志缓冲同步写到磁盘，即伴有 fsync 的调用。
     *  2 表示将重做日志异步写到磁盘，即写到文件系统的缓存中。因此不能完全保证在执行 commit 时肯定会写入重做日志文件，只是有这个动作发生。

因此为了保证事务的 ACID 中的持久性，必须将 innodb\_flush\_log\_attrxcommit 设置为 1，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件。那么当数据库因为意外发生宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务。而将重做日志文件设置为 0 或 2，都有可能发生恢复时部分事务的丢失。不同之处在于，设置为 2 时，当 MvSQL 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

##### 9.undo log 和 redo log? #####

事务隔离性由锁来实现。原子性、一致性、持久性由数据库的 redo log 和 undo log 来完成。redo log 称为重做日志  
来保证事务的原子性和持久性。undo log 用来保证事务的一致性,有的 DBA 或许会认为 undo 是 redo 的逆过程，其实不然。redo log 和 undo log 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改页操作，而 undo 回滚行记录到某个特定版本。因此两者记录的不同，redo 通常是物理日志，记录的是页的物理修改操作。undo 逻辑日志，根据每行记录进行记录。

redo log 分为两部分,重做日志缓冲和重做日志文件.

参数 innodb\_flush\_log\_at\_trxcommit 用来控制重做日志刷新到磁盘的策略。该参数的默认值为 1，表示事务提交时必须调用一次 fsvnc 操作，还可以设置该参数的值为 0 和 2.0 表示事务提交时不进行写入重做日志操作，这个操作仅在 master thread 中完成，而在 master thread 中每 1 秒会进行一次重做日志文件的 fsync 操作。2 表示事务提交时将重做日志写入重做日志文件，但仅写入文件系统的缓存中，不进行 fsync 操作。在这个设置下，当 MySQL 数据库发生  
宕机而操作系统不发生宕机时，并不会导致事务的丢失。而当操作系统宕机时，重启数据库后会丢失未从文件系统缓存刷新到重做日志文件那部分事务。

##### 10.什么是 LSN? #####

LSN 是 Log Sequence Number 的缩写，其代表的是日志序列号。在 InnoDB 存储引擎中，LSN 占用 8 字节，并且单调递增。LSN 表示的含义有:

*  重做日志写入的总量
 *  checkpoint 的位置
 *  页的版本

##### 11.详细说下 undo log? #####

undo 是逻辑日志，因此只是将数据库逻辑地恢复到原来的样子。所有修改都被逻辑地取消了，但是数据结构和页本身在回滚之后可能大不相同。这是因为在多用户并发系统中，可能会有数十、数百甚至数千个并发事务。数据库的主要任务就是协调对数据记录的并发访问。比如，个个事务在修改当前一个页中某几条记录，同时还有别的事务在对同一个页中另几条记录进行修改。因此，不能将一个页回滚到事务开始的样子，因为这样会影响其他事务正在进行的工作。

重做日志记录了事务的行为，可以很好地通过其对页进行“重做”操作。但是事务有时还需要进行回滚操作，这时就需要 undo。因此在对数据库进行修改时，InnoDB 存储引擎不但会产生 redo，还会产生一定量的 undo。这样如果用户执行的事务或语句由于某种原因失败了，又或者用户用一条 ROLLBACK 语句请求回滚，就可以利用这些 undo 信息将数据回滚到修改之前的样子。

redo 存放在重做日志文件中，与 redo 不同，undo 存放在数据库内部的一个特殊段（segment）中，这个段称为 undo 段（undo segment）。undo 段位于共享表空间内。可以通过 py\_innodb\_page\_info.py 工具来查看当前共享表空间中 undo 的数量。

除了回滚操作，undo 的另一个作用是 MVCC，即在 InnoDB 存储引擎中 MVCC 的实现是通过 undo 来完成。当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过 undo 读取之前的行版本信息，以此实现非锁定读取。

最后也是最为重要的一点是，undo log 会产生 redo log，也就是 undo log 的产生会伴随着 redo log 的产生，这是因为 undo log 也需要持久性的保护。

##### 12.redo log 和 bin log? #####

*  首先，二进制日志会记录所有与 MySQL 数据库有关的日志记录，包括 InnoDB、MylSAM、Heap 等其他存储引擎的日志。而 InnoDB 存储引擎的重做日志只记录有关该存储引擎本身的事务日志。
 *  其次，记录的内容不同，无论用户将二进制日志文件记录的格式设为 STATEMENT 还是 ROW，又或者是 MIXED，其记录的都是关于一个事务的具体操作内容，即该日志是逻辑日志。而 InnoDB 存储引擎的重做日志文件记录的是关于每个页(Page)的更改的物理情况。
 *  此外，写入的时间也不同，二进制日志文件仅在事务提交前进行提交，即只写磁盘一次，不论这时该事务多大。而在事务进行的过程中，却不断有重做日志条目(redo entry)被写入到重做日志文件中。

在 MySQL 数据库中还有一种二进制日志（binlog），其用来进行 POINT-IN-TIME（PIT）的恢复及主从复制（Replication）环境的建立。从表面上看其和重做日志非常相似，都是记录了对于数据库操作的日志。然而，从本质上来看，两者有着非常大的不同。

首先，重做日志是在 InnoDB 存储引擎层产生，而二进制日志是在MySQL 数据库的上层产生的，并且二进制日志不仅仅针对于 InnoDB 存储引擎，MySQL 数据库中的任何存储引擎对于数据库的更改都会产生二进制日志。

其次，两种日志记录的内容形式不同。MySQL 数据库上层的二进制日志 bin log 是一种**逻辑日志**，其记录的是对应的 SQL 语句。而 InnoDB 存储引擎层面的重做日志是**物理格式日志**，其记录的是对于每个页的修改。用于数据的持久化,重做日志缓冲,重做日志文件都是为了持久化.

#### 四.Mysql 数据结构 ####

##### 1.mysql 使用的索引数据结构是什么? #####

底层使用的数据结构是 b+树,查询时间复杂度是 O(logN).

B+树是为磁盘或其他直接存取辅助设备设计的一种平衡查找树 。在 B+树中， 所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接。

B+索引在数据库中有一个特点是高扇出性，因此在数据库中，B+树的高度一般都在 2 ～ 4 层， 这也就是说查找某一键值的行记录时最多只需要 2 到 4 次 IO， 这倒不错 。 因为当前一般的机械磁盘每秒至少可以做 100 次 IO， 2 ～ 4 次的 IO 意味着查询时间只需 0.02 ～ 0.04 秒。

数据库中的 B+树索引可以分为聚集索引 （clustered inex） 和辅助索引 （secondary index） 。

innodb 常用的索引结构为 B+树索引,B 是指 balance,不是指 binary.

数据库中的 B+树索引可以分为聚集索引(clustered index)和辅助索引(secondary index)，但是不管是聚集还是辅助的索引，其内部都是 B+树的，即高度平衡的，叶子节点存放着所有的数据。聚集索引与辅助索引不同的是，叶子节点存放的是否是一整行的信息。

聚集索引,是按表中的主键顺序存放数据的,叶子节点也称为数据页,每个数据页通过双向链表和其他页进行关联.

对于辅助索引(SecondaryIndex，也称聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签(bookmark)。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。

SHOW INDEX FROM mysql_user_InnoDB;

![image-20220821184138466][]

*  Table:索引所在的表名。
 *  Non\_unique:非唯一的索引，可以看到 primary key 是 0，因为必须是唯一的。
 *  Key\_name:索引的名字，用户可以通过这个名字来执行 DROP INDEX。
 *  Seg\_in\_index:索引中该列的位置，如果看联合索引 idxac 就比较直观了。
 *  Column\_name:索引列的名称。
 *  Collation:列以什么方式存储在索引中。可以是 A 或 NULL。B+树索引总是 A，即排序的。如果使用了 Heap 存储引擎，并且建立了 Hash 索引，这里就会显示 NULL 了。因为 Hash 根据 Hash 桶存放索引数据，而不是对数据进行排序。
 *  Cardinality:非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 表的行数应尽可能接近 1，如果非常小，那么用户需要考虑是否可以删除此索引。
 *  Sub\_part:是否是列的部分被索引。如果看 idx\_b 这个索引，这里显示 100，表示只对 b 列的前 100 字符进行索引。如果索引整个列则该字段为 NULL。
 *  Packed:关键字如何被压缩。如果没有被压缩，则为 NULL。
 *  Null:是否索引的列含有 NULL 值。可以看到 idxb 这里为 Yes，因为定义的列 b 允许 NULL 值。
 *  Index\_type:索引的类型。InnoDB 存储引擎只支持 B+树索引，所以这里显示的都是 BTREE。
 *  Comment:注释。

Cardinality 值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。因此这个值是不太准确的，只是一个大概的值。上面显示的结果主键的 Cardinality 为 2，但是很显然我们的表中有 4 条记录，这个值应该是 4。Cardinality 为 NULL，在某些情况下可能会发生索引建立了却没有用到的情况。或者对两条基本一样的语句执行 EXPLAIN，但是最终出来的结果不一样:一个使用索引，另外一个使用全表扫描。这时最好的解决办法就是做一次 ANALYZE TABLE 的操作，因此在一个非高峰时间，对应用程序下的几张核心表做 ANALYZE TABLE 操作，这能使优化器和索引更好的工作。

在实际应用中，Cardinality/n\_rows\_in\_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。故在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加 B+树索引是非常有必要的。

InnoDB 存储引擎内部对更新 Cardinality 信息的策略为:

*  表中 1/16 的数据已发生过变化。
 *  stat\_modifed\_counter>2000 000 000

第一种策略为自从一次统计 Cardinality 信息后，表中 1/16 的数据已经发生过变化，这时需要更新 Cardinality 信息。第二种情况考虑的是，如果对表中某一行数据频繁地进行更新操作，这时表中的数据实际并没有增加，实际发生变化的还是这一行数据，则第一种更新策略就无法适用这这种情况。故在 InnoDB 存储引擎内部有一个计数  
器 stat\_modifed\_counter，用来表示发生变化的次数，当 stat\_modifed\_counter 大于 2000 000 000 时，则同样需要更新 Cardinality 信息。

##### 2.高度为 4 的 B+树能存储多少数据? #####

假设每条 SQL 是 1kb,主键 id 是 bigint 类型,一棵高度为 4 的 b+树能存储多少数据

在 innodb 存储引擎里面， 最小的存储单元是页(page),一个页的大小是 16KB.

查看页的大小语句:

show variables like 'innodb_page_size';
    #innodb_page_size	 16384

这就说明了一个页的大小为 16384B, 也就是 16kb

一个页的大小为 16kb 假设一行数据的大小是 1kb，那么一个页可以存放 16 行这样的数据.那如果想查找某个页里面的一个数据的话,得首先找到他所在的页.innodb 存储引擎使用 B+树的结构来存储数据。如果是在主键上建立的索引就是聚簇索引，即只有在叶子节点才存储行数据，而非叶子节点里面的内容其实是键值和指向数据页的指针。

因此，我们首先解决一个简单一点的问题：如果是 2 层的 B+树，最多可以存储多少行数据？如果是 2 层的 B+树，即存在一个根节点和若干个叶子节点，那么这棵 B+树的存放总记录数为：根节点指针数单个叶子节点记录行数。因为单个页的大小为 16kb，而一行数据的大小为 1kb，也就是说一页可以存放 16 行数据。然后因为非叶子节点的结构是：“页指针+键值”，我们假设主键 ID 为 bigint 类型，长度为 8 字节（byte），而指针大小在 InnoDB 源码中设置为 6 字节（byte），这样一共 14 字节（byte），因为一个页可以存放 16k 个 byte，所以一个页可以存放的指针个数为16384/14=1170个。因此一个两层的 B+树可以存放的数据行的个数为：1170x16=18720（行）。

那么对于高度为 3 的 B+树呢？也就是说第一层的页，即根页可以存放 1170 个指针，然后第二层的每个页也可以存放 1170 个指针。这样一共可以存放 1170x1170 个指针，所以一共可以存放 1170x1170x16=21902400（2 千万左右）行记录。也就是说一个三层的 B+树就可以存放千万级别的数据了

而每经过一个节点都需要 IO 一次，把这个页数据从磁盘读取到缓存，也就是说读取一个数据只需要三次 IO。  
继续来说，高度为 4 的 B+树呢？1170x1170x1170x16 约等于 2000 万 1000。5 个 2000 万是 1 个亿。1000 个 2000 万就是 200 亿。

##### 3.为什么选用 B+树做索引? #####

为什么选用 B+树做索引,不选用二叉树或者是 B 树  
b 树（balancetree）和 b+树应用在数据库索引，可以认为是 m 叉的多路平衡查找树，但是从理论上讲，二叉树查找速度和比较次数都是最小的，为什么不用二叉树呢？  
因为我们要考虑磁盘 IO 的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少 IO 次数，对于树来说，IO 次数就是树的高度，而“矮胖”就是 b 树的特征之一，它的每个节点最多包含 m 个孩子，m 称为 b 树的阶。  
为什么不用 B 树呢？  
B+树，是 B 树的一种变体，查询性能更好。  
B+树相比于 B 树的查询优势：

*  B+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”。B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低；
 *  B+树查询必须查找到叶子节点，B 树只要匹配到即可直接返回。因此 B+树查找更稳定（并不慢），必须查找到叶子节点；而 B 树，如果数据在根节点，最快，在叶子节点最慢，查询效率不稳定。
 *  对于范围查找来说，B+树只需遍历叶子节点链表即可，并且不需要排序操作，因为叶子节点已经对索引进行了排序操作。B 树却需要重复地中序遍历，找到所有的范围内的节点。

##### 4.为什么不用 hash 表做索引? #####

为什么用 B+树做索引,而不用 hash 表做索引

*  **模糊查找不支持**：哈希表是把索引字段映射成对应的哈希码然后再存放在对应的位置，这样的话，如果我们要进行模糊查找的话，显然哈希表这种结构是不支持的，只能遍历这个表。而 B+树则可以通过最左前缀原则快速找到对应的数据。
 *  **范围查找不支持**：如果我们要进行范围查找，例如查找 ID 为 100~400 的人，哈希表同样不支持，只能遍历全表。
 *  **哈希冲突问题**：索引字段通过哈希映射成哈希码，如果很多字段都刚好映射到相同值的哈希码的话，那么形成的索引结构将会是一条很长的链表，这样的话，查找的时间就会大大增加。

##### 5.什么是索引组织表? #####

在 InnoDB 存储引擎中，表都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table)在 InnoDB 存储引擎表中，每张表都有个主键(Primary Key)，如果在创建表时没有显式地定义主键，则 InnoDB 存储引擎会按如下方式选择或创建主键:

*  首先判断表中是否有非空的唯一索引(UniqueNOT NULL)，如果有，则该列即为主键。
 *  如果不符合上述条件，InnoDB 存储引擎自动创建一个 6 字节大小的指针。

当表中有多个非空唯一索引时，InnoDB 存储引警将选择建表时第一个定义的非空唯一索引为主键。这里需要非常注意的是，主键的选择根据的是定义索引的顺序，而不是建表时列的顺序。

##### 6.innodb 逻辑存储结构 #####

从 InnoDB 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间(tablespace)。表空间又由段(segment)、区(extent)、页(page)组成。页在一些文档中有时也称为块(block)，InnoDB 存储引擎的逻辑存储结构大致如图所示。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-O9XsZSs4-1661597294027)(http://qinyingjie.cn/pic/image-20220821092622284.png)\]

##### 7.共享表空间 #####

在默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数 innodb\_file\_per\_table，则每张表内的数据可以单独放到一个表空间内。如果启用了 innodbfle\_pertable 的参数，需要注意的是每张表的表空间内存放的只是**数据、索引和插入缓冲 Bitmap 页**，其他类的数据，如\*\*回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)\*\*等还是存放在原来的共享表空间  
内。这同时也说明了另一个问题:即使在启用了参数 innodb\_fle\_per\_table 之后，共享表空间还是会不断地增加其大小。

##### 8.innodb 中的段 #####

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB 存储引擎表是索引组织的(index organized)，因此数据即索引，索引即数据。那么数据段即为 B+树的叶子节点(Leaf node segment)，索引段即为 B+树的非索引节点(Non-leaf node segment)。

##### 9.innodb 中的区 #####

区是由连续页组成的空间，在任何情况下每个区的大小都为 1MB。为了保证区中页的连续性，InnoDB 存储引擎一次从磁盘申请 4~5 个区。在默认情况下，**InnoDB 存储引擎页的大小为 16KB，即一个区中一共有 64 个连续的页**。

InnoDB1.0.x 版本开始引入压缩页，即每个页的大小可以通过参数 KEY\_BLOCK\_SIZE 设置为 2K、4K、8K，因此每个区对应页的数量就应该为 512 256、128。

InnoDB1.2x 版本新增了参数 innodb\_page\_size，通过该参数可以将默认页的大小设置为 4K、8K，但是页中的数据库不是压缩。这时区中页的数量同样也为 256、128。总之，不论页的大小怎么变化，区的大小总是为 1M。

##### 10.innodb 中的页 #####

同大多数数据库一样，InnoDB 有页(Page)的概念(也可以称为块)，页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中。默认每个页的大小为 16KB。而从 InnoDB12x 版本开始，可以通过参数 innodb\_page\_size 将页的大小设置为 4K、8K、16K。若设置完成，则所有表中页的大小都为 innodb\_page\_size，不可以对其再次进行修改。除非通过 mysql dump 导入和导出操作来产生新的库。

在 InnoDB 存储引擎中，常见的页类型有:

*  数据页(B-tree Node)
 *  undo 页(undo Log Page)
 *  系统页(System Page)
 *  事务数据页(Transaction system Page)
 *  插入缓冲位图页(Insert Buffer Bitmap)
 *  插入缓冲空闲列表页(Insert Buffer Free List)
 *  未压缩的二进制大对象页(Uncompressed BLOB Page)
 *  压缩的二进制大对象页(compressed BLOB Page)

##### 11.innodb 中的行 #####

InnoDB 存储引擎是面向行的(row-oriented)，也就说数据是按行进行存放的。每个页存放的行记录也是有硬性定义的，最多允许存放 16KB/2~200 行的记录，即 7992 行记录。这里提到了 row-oriented 的数据库，也就是说，存在有 column-oriented 的数据库。比如 ClockHouse，这对于数据仓库下的分析类 SQL 语句的执行及数据压缩非常有帮助。

**行格式**

*  Compact: 在 MySQL5.1 版本中，默认设置为 Compact 行格式
 *  Redundant:Redundant 格式是为兼容之前版本而保留的
 *  Dynamic:

用户可以通过命令查看当前表使用的行格式。其中 row\_format 属性表示当前所使用的行记录结构类型。如:

SHOW TABLE STATUS LIKE 'mysql_user';

![image-20220821095211606][]

##### 12.行格式的种类 #####

Compact 行记录是在 MySQL5.0 中引入的，其设计目标是高效地存储数据。简单来说，一个页中存放的行数据越多，其性能就越高。

**Compact 行格式结构**

<table> 
 <thead> 
  <tr> 
   <th>变长字段长度列表</th> 
   <th>NULL 标志位</th> 
   <th>记录头信息</th> 
   <th>列 1 数据</th> 
   <th>列 2 数据</th> 
   <th>列数据 xxxx</th> 
  </tr> 
 </thead> 
</table>

Compact 行记录格式的首部是一个非 NULL 变长字段长度列表，并且其是按照列的顺序逆序放置的，其长度为:

*  若列的长度小于 255 字节，用 1 字节表示;
 *  若大于 255 个字节，用 2 字节表示。

变长字段的长度最大不可以超过 2 字节，这是因在 MySQL 数据库中,VARCHAR 类型的最大长度限制为 65535。变长字段之后的第二个部分是 NULL 标志位，该位指示了该行数据中是否有 NULL 值，有则用 1 表示。该部分所占的字节应该为 1 字节。接下来的部分是记录头信息(record header)，固定占用 5 字节(40 位)，每位的含义见表 4-1。

![image-20220821104918456][]

最后的部分就是实际存储每个列的数据。需要要特别注意的是，NULL 不占该部分任何空间，即 NULL 除了占有 NULL 标志位，实际存储不占有任何空间。另外有一点需要注意的是，每行数据除了用户定义的列外，还有两个隐藏列，事务 ID 列和回滚指针列，分别为 6 字节和 7 字节的大小。若 InnoDB 表没有定义主键，每行还会增加一个 6 字节的 rowid 列。

**Redundant 行格式结构**

为了兼容以前版本的页格式

<table> 
 <thead> 
  <tr> 
   <th>字段长度偏移列表</th> 
   <th>记录头信息</th> 
   <th>列 1 数据</th> 
   <th>列 2 数据</th> 
   <th>列数据 xxxx</th> 
  </tr> 
 </thead> 
</table>

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UZq2admD-1661597294027)(http://qinyingjie.cn/pic/image-20220821105619609.png)\]

不同于 Compact 行记录格式，Redundant 行记录格式的首部是一个字段长度偏移列表，同样是按照列的顺序逆序  
放置的。若列的长度小于 255 字节，用 1 字节表示;若大于 255 字节，用 2 字节表示。第二个部分为记录头信息(record header)，不同于 Compact 行记录格式，Redundant 行记录格式的记录头占用 6 字节(48 位)，每位的含义见表 4-2。从表 4-2 中可以发现， n\_felds 值代表一行中列的数量，占用 10 位。同时这也很好地解释了  
为什么 MySQL 数据库一行支持最多的列为 1023。2 的 10 次方为 1024.

对于 VARCHAR 类型的 NULL 值，Redundant 行记录格式同样不占用任何存储空间，而 CHAR 类型的 NULL 值需要占用空间。

**Compressed 和 Dynamic**

InnoDB1.0.x 版本开始引入了新的文件格式(file format，用户可以理解为新的页格式)，以前支持的 Compact 和 Redundant 格式称为 Antelope 文件格式，新的文件格式称为 Barracuda 文件格式。  
Barracuda 文件格式下拥有两种新的行记录格式:Compressed 和 Dynamic。

新的两种记录格式对于存放在 BLOB 中的数据采用了完全的行溢出的方式，如图 4-5 所示，在数据页中只存放 20 个字节的指针，实际的数据都存放在 Off Page 中，而之前的 Compact 和 Redundant 两种格式会存放 768 个前缀字节。

Compressed 行记录格式的另一个功能就是， 存储在其中的行数据 会以 zlib 的算法进行压缩，因此对于 BLOB、T EXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

![image-20220821112047254][]

##### 13.行溢出数据 #####

varchar 最大支持 65535 字节,实际执行会报错,实际支持 65532 字节

65535 是指所有 varchar 列的总和,不是单个

一个页为 16kb,为 16384 字节,如何存储 65535 字节的呢?

但是当发生行溢出时，数据存放在项类型为 Uncompress BLOB 页中。

# 执行报错
    CREATE TABLE tmp_max_length_one_field
    (
      a VARCHAR(65535)
    ) CHARSET = latin1
      ENGINE = InnoDB;
    
    # 可以执行
    CREATE TABLE tmp_max_length_one_field
    (
      a VARCHAR(65532)
    ) CHARSET = latin1
      ENGINE = InnoDB;
     # 报错
      CREATE TABLE tmp_max_length_mut_field
    (
      a VARCHAR(22222),
      b VARCHAR(22222),
      c VARCHAR(22222),
      d VARCHAR(22222)
    ) CHARSET = latin1
      ENGINE = InnoDB;

![image-20220821111726238][]

##### 14.innodb 的页结构 #####

InnoDB 数据页由以下 7 个部分组成

*  File Header(文件头)
 *  Page Header(页头)
 *  Infimun 和 Supremum Records
 *  User Records(用户记录，即行记录)
 *  Free Space(空闲空间)
 *  Page Directory(页目录)
 *  File Trailer(文件结尾信息)

![image-20220821114216827][]

**File Header**

File Header 用来记录页的一些头信息，由表表 4-3 中 8 个部分组成，共占用 38 字节。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vMRHqAPP-1661597294028)(http://qinyingjie.cn/pic/image-20220821114532302.png)\]

FILE\_PAGE\_TYPE 页类型

![image-20220821114551869][]

**Page Header**

接着 File Header 部分的是 Page Header，该音部分用来记录数据页的状态信息，由 14 个部分组成，共占用 56 字节， 如表 4-5 所示。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DTaPufTZ-1661597294028)(http://qinyingjie.cn/pic/image-20220821114802130.png)\]

**Infimun 和 Supremum Records**

在 InnoDB 存储引擎中，每个数据页中有两个虚拟的行记录，用来限定记录的边界。Infimum 记录是比该页中任何主键值都要小的值， Supremum 指比任何可能大的值还要大的值。这两个值在页创建时被建立，并且在任何情况下不会被删除。

![image-20220821115020740][]

**User Records 和 Free Space**

*  User Records(用户记录，即行记录)
 *  Free Space(空闲空间)

UserRecord 就是之前讨论过的部分，即实际存储行记录的内容。再次强调，InnoDB 存储引擎表总是 B+树索引组织的。

Free Space 很明显指的就是空闲空间，同样也是个链表数据结构。在一条记录被删除后，该空间会被加入到空闲链表中。

**Page Directory(页目录)**

Page Directory(页目录)中存放了记录的相对位置(注意，这里存放的是页相对位置，而不是偏移量)，有些时候这些记录指针称为 Slots(槽)或目录槽(DirectorySlots)。与其他数据库系统不同的是，在 InnoDB 中并不是每个记录拥有一个槽，InnoDB 存储引擎的槽是一个稀疏目录(sparse directory)，即一个槽中可能包含多个记录。伪记录 Infmum 的 n\_owned 值总是为 1，记录 Supremum 的 n\_owned 的取值范围为\[1，8\]，其他用户记录 nowned 的取值范围为\[4，8\]。当记录被插入或删除时需要对槽进行分裂或平衡的维护操作。

在 Slots 中记录按照索引键值顺序存放，这样可以利用二叉查找迅速找到记录的指针。假设有(‘i’，‘d’，‘c’，‘b’，‘e’,‘g’,‘t’,‘h’,'f"，"j，‘k’，‘a’)，同时假设一个槽中包含 4 条记录，则 Slots 中的记录可能是(‘a’，‘e’，‘i’)。  
由于在 InnoDB 存储引擎中 Page Direcotry 是稀疏目录，二叉查找的结果只是一个粗略的结果，因此 InnoDB 存储引擎必须通过 recorder header 中的 next\_record 来继续查找相关记录。同时，Page Directory 很好地解释了 recorder header 中的 n\_owned 值的含义因为这些记录并不包括在 Page Directory 中。

需要牢记的是，B+树索引本身并不能找到具体的一条记录，能找到只是该记录所在的页。数据库把页载入到内存，然后通过 Page Directory 再进行二叉查找。只不过二叉查找的时间复杂度很低，同时在内存中的查找很快，因此通常忽略这部分查找所用的时间。

**File Trailer(文件结尾信息)**

为了检测页是否已经完整地写入磁盘(如可能发生的写入过程中磁盘损坏、机器关机等)，InnoDB 存储引擎的页中设置了 File Trailer 部分。

File Trailer 只有一个 FIL\_PAGE\_END\_LSN 部分，占用 8 字节。前 4 字节代表该页的 checksum 值，最后 4 字节和 File Header 中的 FIL\_PAGE\_LSN 相同。将这两个值与 File Header 中的 FIL\_PAGE\_SPACE\_OR\_CHKSUM 和 FIL\_PAGE\_LSN 值进行比较看是否一致(checksum 的比较需要通过 InnoDB 的 checksum 函数来进行比较，不是简单的等值比较)，以此来保证页的完整性(not corrupted)。

在默认配置下，InnoDB 存储引擎每次从磁盘读取一个页就会检测该页的完整性，即页是否发生损坏，这就是通过 File Trailer 部分进行检测，而该部分的检测会有一定的开销。用户可以通过参数 innodb\_log\_checksums 来开启或关闭对这个页完整性的检查。默认是开启的.

#### 五.Mysql 索引 ####

##### 1.什么是聚集索引? #####

InnoDB 存储引擎表是索引组织表，即表中数据按照主键顺序存放。而聚集索引（clusteredindex）就是按照每张表的主键构造一棵 B+树，同时叶子节点中存放的即为整张表的行记录数据，也将聚集索引的叶子节点称为数据页。每个数据页之间都通过一个双向链表来进行链接。  
由于实际的数据页只能按照一棵 B+树进行排序，因此每张表只能拥有一个聚集索引。在多数情况下，查询优化器倾向于采用聚集索引。因为聚集索引能够在 B+树索引的叶子节点上直接找到数据。此外，由于定义了数据的逻辑顺序，它对于主键的排序查找和范围查找速度非常快。叶子节点的数据就是用户所要查询的数据  
如:用户需要查询一张注册用户的表，查询最后注册的 10 位用户，由于 B+树索引是双向链表的，用户可以快速找到最后一个数据页，并取出 10 条记录

SELECT * FROM Profile ORDER BY id LIMIT 10;

虽然使用 ORDER BY 对主键 id 记录进行排序，但是在实际过程中并没有进行所谓的 filesort 操作，而这就是因为聚集索引的特点。另一个是范围查询（rangequery），即如果要查找主键某一范围内的数据，通过叶子节点的上层中间节点就可以得到页的范围，之后直接读取数据页即可。如：

SELECT * FROM Profile where id>1 and id<100;

##### 2.什么是辅助索引? #####

对于辅助索引（SecondaryIndex，也称非聚集索引），叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了一个书签（bookmark）。该书签用来告诉 InnoDB 存储引擎哪里可以找到与索引相对应的行数据。由于 InnoDB 存储引擎表是索引组织表，因此 InnoDB 存储引擎的辅助索引的书签就是相应行数据的聚集索引键。  
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引。当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引来找到一个完整的行记录。举例来说，如果在一棵高度为 3 的辅助索引树中查找数据，那需要对这棵辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度同样为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此一共需要 6 次逻辑 IO 访问以得到最终的一个数据页。

##### 3.什么是联合索引? #####

联合索引是有多个索引列组成的索引.

# 创建联合索引
    CREATE TABLE tmp_kwan_muti_fileld
    (
        a INT,
        b INT,
        PRIMARY KEY (a),
        KEY idx_a_b (a, b)
    ) ENGINE = INNODB;
    
    SHOW INDEX FROM tmp_kwan_muti_fileld;

![image-20220821223536623][]

联合索引是指对表上的多个列进行索引

CREATE TABLE buy_log(
    	userid INT UNSIGNED NOT NULL,
    	buy_date DATE
    )ENGINE=InnoDB;
    ALTER TABLE buy_log ADD KEY(userid);
    ALTER TABLE buy_log ADD KEY(userid,buy_date);

![image-20220811234352958][]

以上代码建立了两个索引来进行比较。两个索引都包含了 userid 字段。  
情况 1：如果只对于 userid 进行查询，如：

SELECT * FROM buy_log WHERE userid=2;

索引选择：优化器最终的选择是索引 userid，因为该索引的叶子节点包含单个键值，所以理论上一个页能存放的记录应该更多。  
情况 2：

SELECT * FROM buy_log WHERE userid=1 ORDER BY buy_date DESC LIMIT 3；

索引选择：优化器使用了（userid，buy\_date）的联合索引 userid\_2，因为在这个联合索引中 buy\_date 已经排序好了。根据该联合索引取出数据，无须再对 buy\_date 做一次额外的排序操作。  
情况 3：假如三个字段的联合索引。如：对于联合索引（a，b，c）来说，下列语句同样可以直接通过联合索引得到结果，不需要 filesort 的排序操作：

SELECT * FROM TABLE WHERE a=xxx ORDER BY b;
    SELECT * FROM TABLE WHERE a=xxx AND b=xxx ORDER BY c;

但是对于下面的语句，联合索引不能直接得到结果，其还需要执行一次 filesort 排序操作，因为索引（a，c）并未排序：

CREATE TABLE buy_log_01(
    	a INT UNSIGNED NOT NULL,
      b INT  default NULL,
    	c DATE
    )ENGINE=InnoDB;
    ALTER TABLE buy_log ADD KEY(a,b,c);

explain SELECT * FROM buy_log_01 WHERE a='xxx' ORDER BY c;

![image-20220812000811658][]

*  Using index:使用到了 a 索引
 *  Using filesort:需要对 c 进行排序

explain SELECT b from buy_log_01 where b=1

![image-20220812000647705][]

*  Using index:使用到了 a 索引
 *  Using where:使用了筛选条件
 *  联合索引中只用到了 b 字段进行查询,也用到了索引

##### 4.什么是覆盖索引? #####

InnoDB 存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是覆盖索引不包含整行记录的所有信息，故其大小要远小于聚集索引， 因此可以减少大量的 IO 操作。

**覆盖索引的好处**:

*  不会回表,在查询结果中有列信息和主键信息
 *  统计操作 count 时,优化器会进行优化,选择覆盖索引

执行 count(\*)的执行计划，possible\_keys 列为 NULL，但是实际执行时优化器却选择了 userid 索引，而列 Extra 列的 Using index 就是代表了优化器进行了覆盖索引操作。

此外，在通常情况下，诸如(a，b)的联合索引，一般是不可以选择列 b 中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择.所以在使用联合索引时,带头的兄弟断了,也有可能使用到索引.

什么情况下优化器会选择覆盖索引  
InnoDB 存储引擎支持覆盖索引（coveringindex，或称索引覆盖），即从辅助索引中就可以得到查询的记录（此时不能够使用select \* 操作，只能对特定的索引字段进行 select），而不需要查询聚簇索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚簇索引，因此可以减少大量的 IO 操作。  
对于 InnoDB 存储引擎的辅助索引而言，由于其包含了主键信息，因此其叶子节点存放的数据为（primarykey1，primarykey2，…，key1，key2，…）。例如，下列语句都可仅使用一次辅助联合索引来完成查询：

SELECT key2 FROM table WHERE key1=xxx；
    
    SELECT primary key2,key2 FROM table WHERE key1=xxx；
    
    SELECT primary key1,key2 FROM table WHERE key1=xxx；

覆盖索引的另一个好处是对某些统计问题而言的。还是对于上题创建的表 buy\_log，要进行举例说明。

SELECT COUNT(1) FROM buy_log;

InnoDB 存储引擎并不会选择通过查询聚集索引来进行统计。由于 buy\_log 表上还有辅助索引，而辅助索引远小于聚集索引，选择辅助索引可以减少 IO 操作。  
在通常情况下，诸如（a，b）的联合索引，一般是不可以选择列 b 中所谓的查询条件。但是如果是统计操作，并且是覆盖索引的，则优化器会进行选择，如下述语句：

explain SELECT COUNT(1) FROM buy_log WHERE buy_date >=  '2011-01-01' AND buy_date <='2011-02-01'

表 buy\_log 有（userid，buy\_date）的联合索引，这里只根据列 b 进行条件查询，一般情况下是不能进行该联合索引的，但是这句 SQL 查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引：

![image-20220812000104551][]

从图中可以发现列 possible\_keys 为 userid\_2，列 key 为 userid\_2，即表示（userid，buy\_date）的联合索引。在列 Extra 同样可以发现 Using index 提示，表示为覆盖索引。

##### 5.什么是最左前缀原则? #####

CREATE TABLE mysql_user
    (
        id   INT UNSIGNED NOT NULL,
        name VARCHAR(64) DEFAULT NULL,
        age  int         DEFAULT NULL
    )ENGINE=InnoDB;

INSERT INTO kwan.mysql_user (id, name, age) VALUES(100, '张一', 10);
    INSERT INTO kwan.mysql_user (id, name, age) VALUES(300, '张二', 20);
    INSERT INTO kwan.mysql_user (id, name, age) VALUES(400, '张三', 40);
    INSERT INTO kwan.mysql_user (id, name, age) VALUES(600, '李四', 10);

![image-20220812001413165][]

如果我们按照 name 字段来建立索引的话，采用 B+树的结构，大概的索引结构如右图：

![name索引][name]

如果我们要进行模糊查找，查找 name 以“张"开头的所有人的 ID，即 sql 语句为

select ID from mysql_user where name like '张%';

由于在 B+树结构的索引中，索引项是按照索引定义里面出现的字段顺序排序的，索引在查找的时候，可以快速定位到 ID 为 100 的张一，然后直接向右遍历所有张开头的人，直到条件不满足为止。  
也就是说，我们找到第一个满足条件的人之后，直接向右遍历就可以了，由于索引是有序的，所有满足条件的人都会聚集在一起。  
而这种定位到最左边，然后向右遍历寻找，就是我们所说的最左前缀原则。

##### 6.什么是自适应 hash 索引? #####

自适应哈希索引采用之前讨论的哈希表的方式实现。不同的是，这仅是数据库自身创建并使用的，DBA 本身并不能对其进行干预。自适应哈希索引经哈希函数映射到一个哈希表中，因此对于字典类型的查找非常快速，如

SELECT * FROM TABLE WHERE index_col='xxx';

但是对于范围查找就无能为力了。通过命令

SHOW ENGINE INNODB STATUS;

可以看到当前自适应哈希索引的使用状况。

![image-20220812002726727][]

=====================================
    2022-08-12 00:25:40 0x16d00b000 INNODB MONITOR OUTPUT
    =====================================
    Per second averages calculated from the last 23 seconds
    -----------------
    BACKGROUND THREAD
    -----------------
    srv_master_thread loops: 16 srv_active, 0 srv_shutdown, 44234 srv_idle
    srv_master_thread log flush and writes: 0
    ----------
    SEMAPHORES
    ----------
    OS WAIT ARRAY INFO: reservation count 26
    OS WAIT ARRAY INFO: signal count 25
    RW-shared spins 0, rounds 0, OS waits 0
    RW-excl spins 0, rounds 0, OS waits 0
    RW-sx spins 0, rounds 0, OS waits 0
    Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
    ------------
    TRANSACTIONS
    ------------
    Trx id counter 30554
    Purge done for trx's n:o < 30554 undo n:o < 0 state: running but idle
    History list length 0
    LIST OF TRANSACTIONS FOR EACH SESSION:
    ---TRANSACTION 281479827004408, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479827003616, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479827002032, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479827001240, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479827002824, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479827000448, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479826999656, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    ---TRANSACTION 281479826998864, not started
    0 lock struct(s), heap size 1128, 0 row lock(s)
    --------
    FILE I/O
    --------
    I/O thread 0 state: waiting for i/o request (insert buffer thread)
    I/O thread 1 state: waiting for i/o request (log thread)
    I/O thread 2 state: waiting for i/o request (read thread)
    I/O thread 3 state: waiting for i/o request (read thread)
    I/O thread 4 state: waiting for i/o request (read thread)
    I/O thread 5 state: waiting for i/o request (read thread)
    I/O thread 6 state: waiting for i/o request (write thread)
    I/O thread 7 state: waiting for i/o request (write thread)
    I/O thread 8 state: waiting for i/o request (write thread)
    I/O thread 9 state: waiting for i/o request (write thread)
    Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
     ibuf aio reads:, log i/o's:, sync i/o's:
    Pending flushes (fsync) log: 0; buffer pool: 0
    896 OS file reads, 997 OS file writes, 537 OS fsyncs
    0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
    -------------------------------------
    INSERT BUFFER AND ADAPTIVE HASH INDEX
    -------------------------------------
    Ibuf: size 1, free list len 0, seg size 2, 0 merges
    merged operations:
     insert 0, delete mark 0, delete 0
    discarded operations:
     insert 0, delete mark 0, delete 0
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 0 buffer(s)
    Hash table size 34679, node heap has 1 buffer(s)
    Hash table size 34679, node heap has 4 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s
    ---
    LOG
    ---
    Log sequence number          20057064
    Log buffer assigned up to    20057064
    Log buffer completed up to   20057064
    Log written up to            20057064
    Log flushed up to            20057064
    Added dirty pages up to      20057064
    Pages flushed up to          20057064
    Last checkpoint at           20057064
    193 log i/o's done, 0.00 log i/o's/second
    ----------------------
    BUFFER POOL AND MEMORY
    ----------------------
    Total large memory allocated 0
    Dictionary memory allocated 412452
    Buffer pool size   8191
    Free buffers       7147
    Database pages     1039
    Old database pages 399
    Modified db pages  0
    Pending reads      0
    Pending writes: LRU 0, flush list 0, single page 0
    Pages made young 1, not young 0
    0.00 youngs/s, 0.00 non-youngs/s
    Pages read 873, created 166, written 596
    0.00 reads/s, 0.00 creates/s, 0.00 writes/s
    No buffer pool page gets since the last printout
    Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
    LRU len: 1039, unzip_LRU len: 0
    I/O sum[0]:cur[0], unzip sum[0]:cur[0]
    --------------
    ROW OPERATIONS
    --------------
    0 queries inside InnoDB, 0 queries in queue
    0 read views open inside InnoDB
    Process ID=366, Main thread ID=0x16be6f000 , state=sleeping
    Number of rows inserted 50, updated 0, deleted 0, read 50
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    Number of system rows inserted 117, updated 361, deleted 44, read 13262
    0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
    ----------------------------
    END OF INNODB MONITOR OUTPUT
    ============================

##### 7.什么是全文索引? #####

当前 InnoDB 存储引擎的全文检索还存在以下的限制:

*  每张表只能有一个全文检索的索引。
 *  由多列组合而成的全文检索的索引列必须使用相同的字符集与排序规则。
 *  不支持没有单词界定符(delimiter)的语言，如中文、日语、韩语等。

#添加全文索引
    alter  table  mysql_user  add  fulltext  (name);

SELECT * FROM blog WHERE content like '%xxx%';

根据 B+树索引的特性，上述 SQL 语句即便添加了 B+树索引也是需要进行索引的扫描来得到结果。类似这样的需求在互联网应用中还有很多。例如，搜索引擎需要根据用户输入的关键字进行全文查找，电子商务网站需要根据用户的查询条件，在可能需要在商品的详细介绍中进行查找，这些都不是 B+树索引所能很好地完成的工作。  
全文检索（Full-TextSearch）是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。  
在之前的 MySQL 数据库中，InnoDB 存储引擎并不支持全文检索技术。大多数的用户转向 MyISAM 存储引擎，这可能需要进行表的拆分，并将需要进行全文检索的数据存储为 MyISAM 表。这样的确能够解决逻辑业务的需求，但是却丧失了 InnoDB 存储引擎的事务性，而这在生产环境应用中同样是非常关键的。  
从 InnoDB1.2.x 版本开始，InnoDB 存储引擎开始支持全文检索，其支持 MyISAM 存储引擎的全部功能，并且还支持其他的一些特性。  
InnoDB 存储引擎从 1.2.x 版本开始支持全文检索的技术，其采用 full inverted index 的方式。在 InnoDB 存储引擎中，将(DocumentId，Position)视为一个“ilist”。因此在全文检索的表中，有两个列，一个是 word 字段，另一个是 ilist 字段，并且在 word 字段上有设有索引。  
全文检索通常使用倒排索引（inverted index)来实现。倒排索引同 B+树索引一样，也是一种索引结构。它在辅助表（auxiliary table）中存储了单词与单词自身在一个或多个文档中所在位置之间的映射。这通常利用关联数组实现，其拥有两种表现形式：

*  inverted file index，其表现形式为\{单词，单词所在文档的 ID\}
 *  full inverted index，其表现形式为\{单词，(单词所在文档的 ID，在具体文档中的位置)\}

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qcq6N33C-1661597294029)(http://qinyingjie.cn/pic/76377d7d388ccb8e09a87a8984d06944.jpeg)\]

从图 1 可以看出,可以看到单词 code 存在于文档 1 和 4 中，单词 days 存在与文档 3 和 6 中。  
从图 2 可以看出,full inverted index 还存储了单词所在的位置信息，如 code 这个单词出现在（1∶6），即文档 1 的第 6 个单词为 code。相比之下，full inverted index 占用更多的空间，但是能更好地定位数据

##### 8.全文索引的语法有了解吗? #####

MySQL 数据库支持全文检索（Full-TextSearch）的查询，其语法为：

MATCH(col1,col2,...)AGAINST(expr[search_modifier])
    search_modifier:
    {
      IN NATURAL LANGUAGE MODE
    | IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
    | IN BOOLEAN MODE
    | WITH QUERY EXPANSION
    }

MySQL 数据库通过 MATCH()…AGAINST()语法支持全文检索的查询，MATCH 指定了需要被查询的列，AGAINST 指定了使用何种方法去进行查询。

**NATURAL LANGUAGE MODE**

全文检索通过 MATCH 函数进行查询，默认采用 Natural-Language 模式，其表示查询带有指定 word 的文档

SELECT*
    FROM mysql_user
    WHERE MATCH(name) AGAINST('张三' IN NATURAL LANGUAGE MODE);

**BOOLEAN MODE**

MySQL 数据库允许使用 IN BOOLEAN MODE 修饰符来进行全文检索。当使用该修饰符时，查询字符串的前后字符会有特殊的含义，例如下面的语句要求查询有字符串张三但没有 hot 的文档，其中+和-分别表示这个单词必须出现，或者一定不存在。

SELECT*
    FROM mysql_user
    WHERE MATCH(name) AGAINST('+张三-hot' IN BOOLEAN MODE);

**WITH QUERY EXPANSION 或 IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION**

MySQL 数据库还支持全文检索的扩展查询。这种查询通常在查询的关键词太短，用户需要 implied knowledge（隐含知识）时进行。例如，对于单词 database 的查询，用户可能希望查询的不仅仅是包含 database 的文档，可能还指那些包含 MySQL、Oracle、DB2、RDBMS 的单词。而这时可以使用 QueryExpansion 模式来开启全文检索的 implied knowledge。

SELECT*
    FROM mysql_user
    WHERE MATCH(name) AGAINST('张三' WITH QUERY EXPANSION);

##### 9.如何选择表的列作为索引更加有效? #####

并不是在所有的查询条件中出现的列都需要添加索引。对于什么时候添加 B+树索引，一般的经验是，在访问表中很少一部分时使用 B+树索引才有意义。对于性别字段、地区字段、类型字段，它们可取值的范围很小，称为低选择性不建议添加索引，当然也要根据自身项目和场景的需求。如：

SELECT * FROM student WHERE sex='M';

按性别进行查询时，可取值的范围一般只有’M’、‘F’。因此上述 SQL 语句得到的结果可能是该表 50%的数据（假设男女比例 1∶1），这时添加 B+树索引是完全没有必要的。相反，如果某个字段的取值范围很广，几乎没有重复，即属于高选择性，则此时使用 B+树索引是最适合的。

怎样查看索引是否是高选择性的呢？可以通过 SHOWINDEX 结果中的列 Cardinality 来观察。Cardinality 值非常关键，表示索引中不重复记录数量的预估值。同时需要注意的是，Cardinality 是一个预估值，而不是一个准确值，基本上用户也不可能得到一个准确的值。在实际应用中，Cardinality/n\_rows\_in\_table 应尽可能地接近 1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

举例：

ALTER TABLE mysql_user
        ADD UNIQUE (id);

EXPLAIN
    SELECT *
    FROM mysql_user
    WHERE id = '500';

表 mysql\_user 大约有 500 万行数据。id 字段上有一个唯一的索引。这时如果查找 id 为 500 的用户，将会得到如下的执行计划：

![image-20220812181948111][]

SHOW INDEX FROM mysql_user;

![image-20220812182054722][]

可以看到使用了 id 这个索引，这也符合之前提到的高选择性，即 SQL 语句选取表中较少行的原则。

##### 10.约束 #####

对于 InnoDB 存储引擎本身而言，提供了以下几种约束:

*  Primary Key
 *  OUnique Key
 *  OForeign Key
 *  Default
 *  NOT NULL

约束的创建可以采用以下两种方式:

*  表建立时就进行约束定义
 *  利用 ALTERTABLE 命令来进行创建约束

约束和索引的区别，约束更是是一个逻辑的概念，用来保证数据的完整性，而索引是一个数据结构，既有逻辑上的概念，在数据库中还代表着物理存储的方式。

##### 11.什么是视图? #####

在 MySQL 数据库中，视图(View)是一个命名的虚表，它由一个 SQL 查询来定义，可以当做表使用。与持久表(permanent table)不同的是，视图中的数据没有实际的物理存储。

起到安全层的作用,不用关心原表的全部字段.

可更新视图.视图是可以被更新的.根据视图更新基本表.

物化视图,存在物理存储.

##### 12.优化器不使用索引的情况 #####

在某些情况下，当执行 EXPLAIN 命令进行 select 语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这利中情况多发生于范围查找、JOIN 链接操作等情况下。

SELECT*
    FROM tmp_kwan_muti_fileld
    WHERE a > 10000
      AND a < 102000;

比如,表 tmp\_kwan\_muti\_fileld 有(a，b)的联合主键，此外还有对于列 a 的单个索引。上述这句 SQL 显然是可以通过扫描 a 上的索引进行数据的查找。然而通过 EXPLAIN 命令。用户会发现优化器并没有按照 OrderlD 上的索引来查找数据，使用了 a 的联合主键索引,也就是表扫描.而非辅助索引扫描.

原因在于用户要选取的数据是整行信息，而 a 索引不能覆盖到我们要查询的信息，因此在对 a 索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然 a 索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时(一般是 20%左右)，优化器会选择通过聚集索引来查找数据。因为顺序读要远远快于离散读。  
因此对于不能进行索引覆盖的情况，优化器选择辅助索引的情况是，通过辅助索引查找的数据是少量的。这是由当前传统机械硬盘的特性所决定的，即利用顺序读来替换随机读的查找。若用户使用的磁盘是固态硬盘，随机读操作非常快，同时有足够的自信来确认使用辅助索引可以带来更好的性能，那么可以使用关键字 FORCE INDEX 来强制使用某个索引.

#### 六.Mysql 锁相关 ####

##### 1.事务的分类? #####

从事务理论的角度来说，可以把事务分为以下几种类型:

*  扁平事务(Flat Transactions)
 *  带有保存点的扁平事务(Flat Transactions with Savepoints)
 *  链事务(Chained Transactions)
 *  嵌套事务(Nested Transactions)
 *  分布式事务(DistributedTransactions)

##### 2.什么是事务的 ACID? #####

*  原子性(atomicity)
 *  一致性(consistency)
 *  隔离性(isolation)
 *  持久性(durability)

##### 3.mysql 事务隔离级别? #####

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

*  READ UNCOMMITTED （导致脏读）
 *  READ COMMITTED （导致幻读）
 *  REPEATABLE READ （默认使用， 避免幻读， 也能避免脏读）
 *  SERIALIZABLE （更高级别隔离， 避免幻读， 避免脏读）

InnoDB 存储引擎默认支持的隔离级别是 REPEATABLE READ，但是与标准 SQL 不同的是，InnoDB 存储引擎在 REPEATABLE READ 事务隔离级别下，使用 Next-Key Lock 锁的算法，因此避免幻读的产生。这与其他数据库系统（如 Microsoft SQL Server 数据库）是不同的。所以说，InnoDB 存储引擎在默认的 REPEATABLE READ 的事务隔离级别下已经能完全保证事务的隔离性要求，即达到 SQL 标准的 SERIALIZABLE 隔离级别。

隔离级别越低，事务请求的锁越少或保持锁的时间就越短。这也是为什么大多数数据库系统默认的事务隔离级别是 READ COMMITTED。

在 InnoDB 存储引擎中，可以使用以下命令来设置当前会话或全局的事务隔离级别：

SET
    [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL
    {
    READ UNCOMMITTED
    |READ COMMITTED
    |REPEATABLE READ
    |SERIALIZABLE
    };

在 SERIALIABLE 的事务隔离级别，InnoDB 存储引擎会对每个 SELECT 语句后自动加上 LOCK IN SHARE MODE，即为每个读取操作加一个共享锁。因此在这个事务隔离级别下，读占用了锁，对一致性的非锁定读不再予以支持。

##### 4.innodb 中的锁有哪几种? #####

# 查看锁信息
    SELECT *
    FROM information_schema.INNODB_TRX;
    SELECT *
    FROM `sys`.`innodb_lock_waits`;
    SELECT *
    FROM performance_schema.data_locks;
    SELECT *
    FROM performance_schema.data_lock_waits;

InnoDB 存储引擎实现了如下两种标准的行级锁：

*  共享锁（SLock），允许事务读一行数据。
 *  排他锁（XLock），允许事务删除或更新一行数据。

如果一个事务 T1 已经获得了行 r 的共享锁，那么另外的事务 T2 可以立即获得行 r 的共享锁，因为读取并没有改变行 r 的数据，称这种情况为锁兼容（Lock Compatible）。但若有其他的事务 T3 想获得行 r 的排他锁，则其必须等待事务 T1、T2 释放行 r 上的共享锁——这种情况称为锁不兼容。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AJo3nfxd-1661597294031)(http://qinyingjie.cn/pic/c6866aa9340cef598d731ab72ea02549.png)\]

##### 5.innodb 意向锁? #####

此外，InnoDB 存储引擎支持多粒度（granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁（IntentionLock）。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度（finegranularity）上进行加锁

InnoDB 存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

*  意向共享锁（ISLock），事务想要获得一张表中某几行的共享锁
 *  意向排他锁（IXLock），事务想要获得一张表中某几行的排他锁

由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

![image-20220412101003089][]

##### 6.自增长锁? #####

插入操作会依据这个自增长的计数器值加 1 赋予自增长列。这个方式称做 AUTO-INC Locking。这种锁其实是采用一种特殊的机制，为了提高插入的性能，锁不是在一个事务完成后才释放,而是在完成对自增长值插入的 SQL 语句后立即释放。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f2BKRxbw-1661597294032)(http://qinyingjie.cn/pic/image-20220826235504397.png)\]

innodb\_autoinc\_lock\_mode 默认值为 1

![image-20220826235409702][]

##### 7.lock 和 latch 的区别? #####

latch 一般称为闩锁(轻量级的锁)，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在 InnoDB 存储引擎中，latch 又可以分为 mutex(互斥量)和 rwlock(读写锁)。其目的是用来保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。  
lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放(不同事务隔离级别释放的时间可能不同)。此外，lock，正如在大多数数据库中一样，是有死锁机制的。

![image-20220823085948035][]

##### 8.什么是一致性非锁定读的? #####

一致性的非锁定读（consistent nonlocking read）是指 InnoDB 存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。

之所以称其为非锁定读，因为不需要等待访问的行上 X 锁的释放。快照数据是指该行的之前版本的数据，该实现是通过 undo log 来完成。而**undo log 用来在事务中回滚数据，因此快照数据本身是没有额外的开销**。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本。如右图显示的，一个行记录可能有不止一个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control，MVCC）。

![MVCC (1)][MVCC _1]

在事务隔离级别 READ COMMITTED 和 REPEATABLE READ(InnoDB 存储引擎的默认事务隔离级别)下，InnoDB 存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在 READ COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

举个例子,a 开启事务,读取 id=1 的数据,未提交事务,b 开启事务,set id=3,b 事务未提交时,在 READ COMMITTED 和 REPEATABLE READ 隔离级别下,读到的都是 id=1,因为只有一个快照,当 b 提交后,再读 id=1 时,READ COMMITTED 会读到空数据,因为读的是最新的行快照,REPEATABLE READ 读到的还是 id=1 的数据,会一直用事务开始读到的快照.

对于 READ COMMITTED 的事务隔离级别而言，从数据库理论的角度来看，其违反了事务 ACID 中的 I 的特性，即隔离性。

##### 9.一致性锁定读? #####

SELECT 语句支持两种一致性的锁定读(locking read)操作:

*  SELECT…FOR UPDATE
 *  SELECT…LOCKIN SHARE MODE

SELECT…FOR UPDATE 对读取的行记录加一个 X 锁，其他事务不能对已锁定的行加上任何锁。

SELECT…LOCK IN SHARE MODE 对读取的行记录加一个 S 锁，其他事务可以向被锁定的行加 S 锁，但是如果加 X 锁，则会被阻塞。

当事务提交了，锁锁也就释放了。因此在使用上述两句 SELECT 锁定语句时，务必加上 BEGIN，START  
TRANSACTION 或者 SET AUTOCOMMIT=0。

##### 10.innodb 行锁的三种算法? #####

InnoDB 存储引擎有 3 种行锁的算法，其分别是：

*  Record Lock：单个行记录上的锁
 *  Gap Lock：间隙锁，锁定一个范围，但不包含记录本身
 *  Next-Key Lock ∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用隐式的主键来进行锁定。

Gap Lock:锁定是一个范围,但是不包含边界,开开区间.

Next-Key Lock 是结合了 Gap Lock 和 Record Lock 的一种锁定算法，在 Next-Key Lock 算法下，InnoDB 对于行的查询都是采用这种锁定算法。

在 InnoDB 默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB 存储引擎采用 Next-Key Locking 这种锁定算法。例如一个索引有 10，11，13 和 20 这四个值，那么该索引可能被 Next-Key Locking 的区间为：

> (-∞,10\] (10,11\] (11,13\] (13，20\] (20,+∞)

DROP TABLE IF EXISTS t;
    CREATE TABLE t( a INT PRIMARY KEY);
    INSERT INTO t SELECT 1;
    INSERT INTO t SELECT 2;
    INSERT INTO t SELECT 5;

当查询的索引含有唯一属性时，InnoDB 存储引擎会对 Next-KeyLock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

什么是唯一属性，其实就是我们所说的能够标识该行数据唯一的标识。unique 字段。比如：主键就是唯一的，不重复的。我们也可以自己设计多个字段组合不重复，唯一的。

表 t 共有 1、2、5 三个值。在上面的例子中，在会话 A 中首先对 a=5 进行 X 锁定。而由于 a 是主键且唯一，因此锁定的仅是 5 这个值，而不是(2，5)这个范围，这样在会话 B 中插入值 4 而不会阻塞，可以立即插入并返回。即锁定由 Next-Key Lock 算法降级为了 Record Lock，从而提高应用的并发性。

![image-20220812194855318][]

CREATE TABLE z(a INT,b INT,PRIMARY KEY(a),KEY(b));
    INSERT INTO z SELECT 1,1;
    INSERT INTO z SELECT 3,1;
    INSERT INTO z SELECT 5, 3;
    INSERT INTO z SELECT 7,6;
    INSERT INTO z SELECT 10,8;

SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE;
    INSERT INTO z SELECT 4,2;
    INSERT INTO z SELECT 6,5;

表 z 的列 b 是辅助索引，若在会话 A 中执行下面的 SQL 语句：

SELECT * FROM z WHERE b=3 FOR UPDATE;

很明显，这时 SQL 语句通过索引列 b 进行查询，该列不是唯一属性，因此其使用传统的 Next-Key Locking 技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚簇索引（primay-keya），其仅对列 a 等于 5 的索引加上 Record Lock。而对于辅助索引 b，其加上的是 Next-Key Lock，锁定的范围是(1，3)。特别需要注意的是，InnoDB 存储引擎还会对辅助索引下一个键值加上 gap lock，即还有一个辅助索引范围为(3，6)的锁。

第一个 SQL 语句不能执行，因为在会话 A 中执行的 SQL 语句已经对聚集索引中列 a=5 的值加上 X 锁，因此执行会被阻塞。

第二个 SQL 语句，主键插入 4，没有问题，但是插入的辅助索引值 2 在锁定的范围(1，3)中，因此执行同样会被阻塞。

第三个 SQL 语句，插入的主键 6 没有被锁定，5 也不在范围(1，3)之间。但插入的值 5 在另一个锁定的范围(3，6)中，故同样需要等待。

##### 11.next-key lock 有什么作用? #####

在默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB 存储引擎采用 Next-Key Locking 机制来避免 Phantom Problem（幻读问题，也称不可重复读）。Phantom Problem 是指在同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果。（在 READ COMMITTED 事务隔离级别下会出现）

在同一事务中，若此时执行语句：

SELECT * FROM z WHERE b=3 FOR UPDATE;

执行两次，中间间隔 10 秒时间执行。可以肯定的说，我们会得到第三行数据的结果，即（5,3）。此时我们知道，会有一个 Record Lock 锁定主键 5，还会有一个 gap lock 锁定（1,3）和（3,6）。

假设：我们分析下，若此时没有 gaplock（1,3）和（3,6），如果只有 Record Lock 锁定主键 5 会不会造成幻读。

分析：我们在第一次 select 完成之后，第二次 select 之前，插入一条数据:

INSERT INTO z SELECT 20,3;

这条数据是可以插入成功的，因为我们只有一个 record lock 锁定了主键 5，对于新插入的数据主键为 20，可以插入，且无重复。插入完成后，第二次 select 得到了两个值，（5,3）（20,3）。这就造成了同一事物中，第一次读取和第二次读取的结果不一样，出现幻读。如果是 gap lock,不能锁定记录本身 3,如果有 next-key lock，插入就会被阻塞，不会出现幻读。

##### 12.什么是丢失更新?如何避免? #####

丢失更新是锁导致的问题，简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。例如：  
1）事务 T1 将行记录 r 更新为 v1，但是事务 T1 并未提交。  
2）与此同时，事务 T2 将行记录 r 更新为 v2，事务 T2 未提交。  
3）事务 T1 提交。  
4）事务 T2 提交。

但是，在当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。这是因为，即使是 READ UNCOMMITTED 的事务隔离级别，对于行的 DML 操作(增删改查)，需要对行或其他粗粒度级别的对象加锁。因此在上述步骤 2）中，事务 T2 并不能对行记录 r 进行更新操作，其会被阻塞，直到事务 T1 提交。

虽然数据库能阻止丢失更新问题的产生，但是在生产应用中还有另一个逻辑意义的丢失更新问题，而导致该问题的并不是因为数据库本身的问题。实际上，在所有多用户计算机系统环境下都有可能产生这个问题。简单地说来，出现下面的情况时，就会发生丢失更新：

1）事务 T1 查询一行数据，放入本地内存，并显示给一个终端用户 User1。  
2）事务 T2 也查询该行数据，并将取得的数据显示给终端用户 User2。  
3）User1 修改这行记录，更新数据库并提交。  
4）User2 修改这行记录，更新数据库并提交。

显然，这个过程中用户 User1 的修改更新操作“丢失”了，而这可能会导致一个“恐怖”的结果。  
要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行的操作。即在上述四个步骤的 1）中，对用户读取的记录加上一个排他 X 锁。同样，在步骤 2）的操作过程中，用户同样也需要加一个排他 X 锁。通过这种方式，步骤 2）就必须等待一步骤 1）和步骤 3）完成，最后完成步骤 4)

##### 13.什么是脏读? #####

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

<table> 
 <thead> 
  <tr> 
   <th align="center">Time</th> 
   <th>回话 A</th> 
   <th>回话 B</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">1</td> 
   <td>set @@tx_isolation=‘read-uncommitted’;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="center">2</td> 
   <td></td> 
   <td>set @@tx_isolation=‘read-uncommitted’;</td> 
  </tr> 
  <tr> 
   <td align="center">3</td> 
   <td></td> 
   <td>begin;</td> 
  </tr> 
  <tr> 
   <td align="center">4</td> 
   <td></td> 
   <td>select * from t //得到一行</td> 
  </tr> 
  <tr> 
   <td align="center">5</td> 
   <td>insert into t select 2;</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td align="center">6</td> 
   <td></td> 
   <td>select * from t //得到二行</td> 
  </tr> 
 </tbody> 
</table>

事务的隔离级别进行了更换，由默认的 REPEATABLE READ 换成了 READ UNCOMMITTED。因此在会话 A 中，在事务并没有提交的前提下，会话 B 中的两次 SELECT 操作取得了不同的结果，并且 2 这条记录是在会话 A 中并未提交的数据，即产生了脏读，违反了事务的隔离性。脏读现象在生产环境中并不常发生，从上面的例子中就可以发现，脏读发生的条件是需要事务的隔离级别为 READ UNCOMMITTED，而目前绝大部分的数据库都至少设置成 READ COMMITTED。

##### 14.如何预防数据库死锁? #####

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。然而在线上环境中，这可能导致并发性能的下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

解决死锁问题最简单的**一种方法是超时**，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在 InnoDB 存储引擎中，参数 innodb\_lock\_wait\_timeout 用来设置超时的时间。

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据 FIFO 的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用 FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

因此，除了超时机制，**当前数据库还都普遍采用 wait-for graph（等待图）的方式来进行死锁检测**。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB 存储引擎也采用的这种方式。wait-for graph 要求数据库保存以下两种信息:

*  锁的信息链表
 *  事务等待链表

![image-20220812201245142][]

![image-20220812201358622][]

在等待图中,事务 T1 指向 T2 边的定义为：

*  事务 T1 等待事务 T2 所占用的资源
 *  事务 T1 最终等待 T2 所占用的资源，也就是事务之间在等待相同的资源，而事务 T1 发生在事务 T2 的后面

通过等待图可以发现存在回路（t1，t2），因此存在死锁。通过上述的介绍，可以发现 wait-for graph 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。

##### 15.mysql 分布式事务有了解吗? #####

InnoDB 存储引擎提供了对 XA 事务的支持，并通过 XA 事务来支持分布式事务的实现。分布式事务指的是允许多个独立的事务资源（transactional resources）参与到一个全局的事务中。事务资源通常是关系型数据库系统，但也可以是其他类型的资源。全局事务要求在其中的所有参与的事务要么都提交，要么都回滚，这对于事务原有的 ACID 要求又有了提高。另外，在使用分布式事务时，InnoDB 存储引擎的事务隔离级别必须设置 SERIALIZABLE。

XA 事务允许不同数据库之间的分布式事务，如一台服务器是 MySQL 数据库的，另一台是 Oracle 数据库的，又可能还有一台服务器是 SQLServer 数据库的，只要参与在全局事务中的每个节点都支持 XA 事务。分布式事务可能在银行系统的转账中比较常见，如用户 David 需要从上海转 10000 元到北京的用户 Mariah 的银行卡中：

#Bank@Shanghai：
    UPDATE account SET money=money-10000 WHERE user='David';
    
    #Bank@Beijing
    UPDATE account SET money=money+10000 WHERE user='Mariah';

在这种情况下，一定需要使用分布式事务来保证数据的安全。如果发生的操作不能全部提交或回滚，那么任何一个结点出现问题都会导致严重的结果。要么是 David 的账户被扣款，但是 Mariah 没收到，又或者是 David 的账户没有扣款，Mariah 却收到钱了。

XA 事务由一个或多个资源管理器（Resource Managers）、一个事务管理器（Transaction Manager）以及一个应用程序（Application Program）组成。

*  资源管理器：提供访问事务资源的方法。通常一个数据库就是一个资源管理器。
 *  事务管理器：协调参与全局事务中的各个事务。需要和参与全局事务的所有资源管理器进行通信。
 *  应用程序：定义事务的边界，指定全局事务中的操作。

在 MySQL 数据库的分布式事务中，资源管理器就是 MySQL 数据库，事务管理器为连接 MySQL 服务器的

客户端。下图显示了一个分布式事务的模型。

![image-20220412185740452][]

分布式事务使用两段式提交（two-phase commit）的方式。在第一阶段，所有参与全局事务的节点都开始准备（PREPARE），告诉事务管理器它们准备好提交了。在第二阶段，事务管理器告诉资源管理器执行 ROLLBACK 还是 COMMIT。如果任何一个节点显示不能提交，则所有的节点都被告知需要回滚。可见与本地事务不同的是，分布式事务需要多一次的 PREPARE 操作，待收到所有节点的同意信息后，再进行 COMMIT 或是 ROLLBACK 操作。

MySQL 数据库 XA 事务的 SQL 语法如下：

XA{
       START|BEGIN}xid[JOIN|RESUME]XAENDxid[SUSPEND[FORMIGRATE]]XAPREPARExid
    XACOMMITxid[ONEPHASE]
    XAROLLBACKxid
    XARECOVER

##### 16.mysql 自身有没有需要考虑分布式事务的? #####

最为常见的内部 XA 事务存在于 bin log 与 InnoDB 存储引擎之间。由于复制的需要，因此目前绝大多数的数据库都开启了 bin log 功能。在事务提交时，先写二进制日志，再写 InnoDB 存储引擎的重做日志。对上述两个操作的要求也是原子的，即二进制日志和重做日志必须同时写入。若二进制日志先写了，而在写入 InnoDB 存储引擎时发生了宕机，那么 slave 可能会接收到 master 传过去的二进制日志并执行，最终导致了主从不一致的情况。

![image-20220412185900734][]

如果执行完 ①、② 后在步骤 ③ 之前 MySQL 数据库发生了宕机，则会发生主从不一致的情况。为了解决这个问题，MySQL 数据库在 bin log 与 InnoDB 存储引擎之间采用 XA 事务。当事务提交时，InnoDB 存储引擎会先做一个 PREPARE 操作，将事务的 xid 写入，接着进行二进制日志的写入。

![image-20220412190112199][]

如果 innodb 存储引擎提交前，MySQL 数据库宕机了，那么 MySQL 数据库在重启后会先检查准备的 UXID 事务是否已经提交，若没有，则在存储引擎层再进行一次提交.

##### 17.如何解决幻读问题? #####

在默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB 存储引擎采用 Next-KeyLocking 机制来避免 Phantom Problem(幻读问题)。这点可能不同于与其他的数据库，如 Oracle 数据库，因为其可能需要在 SERIALIZABLE 的事务隔离级别下才能解决 Phantom Problem.

InnoDB 存储引擎采用 Next-KeyLocking 的 Problem。对于 SQL 语句 SELECT \* FROM t where a>2 FOR UPDATE，其锁住的不是 5 这单个值，而是对范围(2,+无穷大)加了 X 锁。因此任何对于这个范围的插入都加了 x 锁,从而避免 Phantom Problem。

InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ,该隔离级别下，其采用 Next-Key Locking 的方式来进行加锁,在隔离级别为 READ COMMITTED 下，其仅采用 Record lock 进行加锁.

#### 七.主从复制 ####

##### 1.主从复制的原理? #####

复制(replication)是 MySQL 数据库提供的一种高可用高性能的解决方案，一般用来建立大型的应用。总体来说，replication 的工作原理分为以下 3 个步骤:

*  1)主服务器(master)把数据更改记录到二进制日志(binlog)中。
 *  2)从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relaylog)中。
 *  3)从服务器重做中继日志中的日志，把更改应用到自己的数据库上，以达到数据的最终一致性。

复制的工作原理并不复杂，其实就是一个完全备份加上二进制日志备份的还原。不同的是这个二进制日志的还原操作基本上实时在进行中。这里特别需要注意的是，复制不是完全实时地进行同步，而是异步实时。这中间存在主从服务器之间的执行延时，如果主服务器的压力很大，则可能导致主从服务器延时较大。

![image-20220827161654519][]

##### 2.SBR 和 RBR? #####

主从复制 bin log 日志有几种记录方式?说说各自的优缺点?

Replication 之所以能够工作，主要还是归结于 bin log（binary log），所以在 Replication 模式下必须开启 bin log 功能；slave 从 masters 上增量获取 bin log 信息，并在本地应用日志中的变更操作（即“重放”）。变更操作将根据选定的格式类型写入 bin log 文件，目前支持三种 format：

*  statement-based Replication（SBR）：master 将 SQL statements 语句写入 bin log，slave 也将 statements 复制到本地执行；简单而言，就是在 master 上执行的 SQL 变更语句，也同样在 slaves 上执行。SBR 模式是 MySQL 最早支持的类型，也是 Replication 默认类型。
 *  row-based Replication（RBR）：master 将每行数据的变更信息写入 bin log，每条 bin log 信息表示一行（row）数据的变更内容，对于 slaves 而言将会复制 bin log 信息，然后单条或者批量执行变更操作；
 *  mix-format Replication：混合模式，在这种模式下，master 将根据存储引擎、变更操作类型等，从 SBR、RBR 中来选择更合适的日志格式，默认为 SBR；具体选择那种格式，这取决于变更操作发生的存储引擎、statement 的类型以及特征，优先选择“数据一致性”最好的方式（RBR），然后才兼顾性能，比如 statement 中含有“不确定性”方法或者批量变更，那么将选择 RBR 方式，其他的将选择 SBR 以减少 bin log 的大小。我们建议使用 mix 方式。

SBR 和 RBR 都有各自的优缺点，对于大部分用而言，mix 方式在兼顾数据完整性和性能方面是最佳的选择。

**SBR 的优点**

*  因为 bin log 中只写入了变更操作的 statements，所以日志量将会很小；
 *  当使用 SQL 语句批量更新、删除数据时，只需要在 bin log 中记录 statement 即可，可以大大减少 log 文件对磁盘的使用
 *  当然这也意味着 slave 复制信息量也更少，以及通过 binlog 恢复数据更加快速；

**SBR 的缺点**

有些变更操作使用 SBR 方式会带来数据不一致的问题，一些结果具有不确定性的操作使用 SBR 将会引入数据不一致的问题。

*  statement 中如果使用了 UDF(User Defination Fuction)，UDF 的计算结果可能依赖于 SQL 执行的时机和系统变量，这可能在 slave 上执行的结果与 master 不同，此外如果使用了 trigger，也会带来同样的问题；
 *  statement 中如果使用了如下函数的（举例）：UUID()，SYSDATE()，RAND()等，不过 NOW()函数可以正确的被 Replication（但在 UDF 或者触发器中则不行）；这些函数的特点就是它们的值依赖于本地系统，RAND()本身就是随机所以值是不确定的。如果 statement 中使用了上述函数，那么将会在日志中输出 warning 信息；
 *  对于“INSERT…SELECT”语句，SBR 将比 RBR 需要更多的行锁。（主要是为了保障数据一致性，需要同时锁定受影响的所有的行，而 RBR 则不必要）；
 *  对于 InnoDB，使用“AUTO\_INCREMENT”的 insert 语句，将会阻塞其他“非冲突”的 INSERT。（因为 AUTO\_INCREMENT，为了避免并发导致的数据一致性问题，只能串行，但 RBR 则不需要）；
 *  对于复杂的 SQL 语句，在 slaves 上仍然需要评估（解析）然后才能执行，而对于 RBR，SQL 语句只需要直接更新相应的行数据即可；在 slave 上评估、执行 SQL 时可能会发生错误，这种错误会随着时间的推移而不断累加，数据一致性的问题或许会不断增加。

RBR 的优点：

*  所有的变更操作，都可以被正确的 Replication，这是最安全的方式；
 *  对于“INSERT…SELECT”、包含“AUTO\_INCREMENT”的 inserts、没有使用索引的 UPDATE/DELETE，相对于 SBR 将需要更少的行锁。（意味着并发能力更强）；

RBR 的缺点：

*  最大的缺点：就是 RBR 需要更多的日志量。任何数据变更操作都将被写入 log，受影响的每行都要写入日志，日志包含此行所有列的值（即使没有值变更的列）；因此 RBR 的日志条数和尺寸都将会远大于 SBR，特别是在批量的 UPDATE/DELETE 时，可能会产生巨大的 log 量，反而对性能带来影响，尽管这确实保障了数据一致性，确导致 Replication 的效率较低；

##### 3.主从复制有几种方式? #####

**异步复制**

MySQL 默认的复制策略，Master 处理事务过程中，将其写入 Binlog 就会通知 Dump thread 线程处理，然后完成事务的提交，不会关心是否成功发送到任意一个 slave 中

![image-20220412191130572][]

**半同步复制**

![image-20220412191217549][]

Master 处理事务过程中，提交完事务后，必须等至少一个 Slave 将收到的 binlog 写入 relay log 返回 ack 才能继续执行处理用户的事务。

**相关配置**

#【这里MySQL5.5并没有这个配置，MySQL5.7为了解决半同步的问题而设置的】
    rpl_semi_sync_master_wait_point=AFTER_COMMIT
    
    #（最低必须收到多少个slave的ack）
    rpl_semi_sync_master_wait_for_slave_count=1
    
    #（等待ack的超时时间）
    rpl_semi_sync_master_timeout=100

**增强半同步复制**

![image-20220412191333973][]

增强半同步和半同步不同是，等待 ACK 时间不同

rpl\_semi\_sync\_master\_wait\_point=AFTER\_SYNC（唯一区别）

半同步的问题是因为等待 ACK 的点是 Commit 之后，此时 Master 已经完成数据变更，用户已经可以看到最新数据，当 Binlog 还未同步到 Slave 时，发生主从切换，那么此时从库是没有这个最新数据的，用户又看到老数据。

增强半同步将等待 ACK 的点放在提交 Commit 之前，此时数据还未被提交，外界看不到数据变更，此时如果发送主从切换，新库依然还是老数据，不存在数据不一致的问题。

##### 4.主从复制有什么好处? #####

*  在从服务器可以执行查询工作， 降低主服务器压力； （主库写， 从库读，降压） 读写分离
 *  对主服务器进行数据备份， 避免备份期间影响主服务器服务； 容灾
 *  当主服务器出现问题时， 可以切换到从服务器 。提高可用性

##### 5.mysql 的热备和冷备? #####

热备 Hot Backup 是指数据库运行中直接备份，对正在运行的数据库操做没有任何的影响。这种方式在 MySQL 官方手册中称为 Online Backup(在线备份)。

冷备 Cold Backup 是指备份操作是在数据库停止的情况下，这种备份最为简单，一般只需要复制相关的数据库物理  
文件即可。这种方式在 MySQL 官方手册中称为 Offline Backup(离线备份)。对于 InnoDB 存储引擎的冷备非常简单，只需要备份 MySQL 数据库的 frm 文件，共享表空间文件，独立表空间文件(\*.ibd)，重做日志文件。另外建议定期备份 MySQL 数据库的配置文件 my.cnf，这样有利于恢复的操作。

温备 Warm Backup 备份同样是在数据库运行中进行的，但是会对当前数据库的操作有所影响，如加一个全局读锁以保证备份数据的一致性.

#### 八.高阶原理 ####

##### 1.什么事 FIC? #####

说说 FIC(first index creation)原理,与普通 index 有什么不同?

MySQL5.5 版本之前（不包括 5.5）存在的一个普遍被人诟病的问题是：MySQL 数据库对于索引的添加或者删除的这类 DDL 操作，MySQL 数据库的操作过程为：

*  首先创建一张新的临时表，表结构为通过命令 ALTERTABLE 新定义的结构。
 *  然后把原表中数据导入到临时表。
 *  接着删除原表。
 *  最后把临时表重命名为原来的表名。

可以发现，若用户对于一张大表进行索引的添加和删除操作，那么这会需要很长的时间。更关键的是，若有大量事务需要访问正在被修改的表，这意味着数据库服务不可用。MySQL 数据库的索引维护始终让使用者感觉非常痛苦。

InnoDB 存储引擎从 InnoDB1.0.x 版本开始支持一种称为 Fast Index Creation（快速索引创建）的索引创建方式——简称 FIC。

对于辅助索引的创建，InnoDB 存储引擎会对创建索引的表加上一个 S 锁。在创建的过程中，不需要重建表，因此速度较之前提高很多，并且数据库的可用性也得到了提高。删除辅助索引操作就更简单了，InnoDB 存储引擎只需更新内部视图，并将辅助索引的空间标记为可用（不影响辅助索引的使用，因为可读，后边的同时删除四个字非常传神），同时删除 MySQL 数据库内部视图上对该表的索引定义即可。

由于 FIC 在索引的创建的过程中对表加上了 S 锁，因此在创建的过程中只能对该表进行读操作，若有大量的事务需要对目标表进行写操作，那么数据库的服务同样不可用。此外，FIC 方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表.

##### 2.有没有比 FIC 更好的方式? #####

虽然 FIC 可以让 InnoDB 存储引擎避免创建临时表，从而提高索引创建的效率。但正如前面面试题中所说的，索引创建时会阻塞表上的 DML 操作（除读操作）。OSC（一个 FaceBook 的 PHP 脚本）虽然解决了上述的部分问题，但是还是有很大的局限性。MySQL5.6 版本开始支持 Online DDL（在线数据定义）操作，其允许辅助索引创建的同时，还允许其他诸如 INSERT、UPDATE、DELETE 这类 DML 操作，这极大地提高了 MySQL 数据库在生产环境中的可用性。

不仅是辅助索引，以下这几类 DDL 操作都可以通过“在线”的方式进行操作：

*  辅助索引的创建与删除
 *  改变自增长值
 *  添加或删除外键约束
 *  列的重命名

使用语法：

altertabletba_name
    |ADD{
       INDEX|KEY}[index_name]
    [index_type](index_col_name,...)[index_option]...
    ALGORITHM[=]{
       DEFAULT|INPLACE|COPY}
    LOCK[=]{
       DEFAULT|NONE|SHARED|EXCLUSIVE}

ALGORITHM 指定了创建或删除索引的算法，COPY 表示按照 MySQL5.1 版本之前的工作模式，即创建临时表的方式。INPLACE 表示索引创建或删除操作不需要创建临时表。DEFAULT 表示根据参数 old\_alter\_table 来判断是通过 INPLACE 还是 COPY 的算法，该参数的默认值为 OFF，表示采用 INPLACE 的方式。

LOCK 部分为索引创建或删除时对表添加锁的情况：  
（1）NONE  
执行索引创建或者删除操作时，对目标表不添加任何的锁，即事务仍然可以进行读写操作，不会收到阻塞。因此这种模式可以获得最大的并发度。  
（2）SHARE  
这和之前的 FIC 类似，执行索引创建或删除操作时，对目标表加上一个 S 锁。对于并发地读事务，依然可以执行，但是遇到写事务，就会发生等待操作。如果存储引擎不支持 SHARE 模式，会返回一个错误信息。  
（3）EXCLUSIVE  
在 EXCLUSIVE 模式下，执行索引创建或删除操作时，对目标表加上一个 X 锁。读写事务都不能进行，因此会阻塞所有的线程，这和 COPY 方式运行得到的状态类似，但是不需要像 COPY 方式那样创建一张临时表。  
（4）DEFAULT  
DEFAULT 模式首先会判断当前操作是否可以使用 NONE 模式，若不能，则判断是否可以使用 SHARE 模式，最后判断是否可以使用 EXCLUSIVE 模式。也就是说 DEFAULT 会通过判断事务的最大并发性来判断执行 DDL 的模式。

InnoDB 存储引擎实现 Online DDL 的原理是在执行创建或者删除操作的同时，将 INSERT、UPDATE、DELETE 这类 DML 操作日志写入到一个缓存中。待完成索引创建后再将重做应用到表上，以此达到数据的一致性。这个缓存的大小由参数 innodb\_online\_alter\_log\_max\_size 控制，默认的大小为 128MB。

需要特别注意的是，由于 Online DDL 在创建索引完成后再通过重做日志达到数据库的最终一致性，这意味着在索引创建过程中，SQL 优化器不会选择正在创建中的索引。

##### 3.详细说说 Cardinality? #####

因为 MySQL 数据库中有各种不同的存储引擎，而每种存储引擎对于 B+树索引的实现又各不相同，所以对 Cardinality 的统计是放在存储引擎层进行的。

在生产环境中，索引的更新操作可能是非常频繁的。如果每次索引在发生操作时就对其进行 Cardinality 的统计，那么将会给数据库带来很大的负担。另外需要考虑的是，如果一张表的数据非常大，如一张表有 50G 的数据，那么统计一次 Cardinality 信息所需要的时间可能非常长。这在生产环境下，也是不能接受的。因此，数据库对于 Cardinality 的统计都是通过采样（Sample）的方法来完成的。默认 InnoDB 存储引擎对 8 个叶子节点（Leaf Page）进行采样。采样的过程如下：

*  取得 B+树索引中叶子节点的数量，记为 A。
 *  随机取得 B+树索引中的 8 个叶子节点。统计每个页不同记录的个数，即为 P1，P2，…，P8。
 *  根据采样信息给出 Cardinality 的预估值：Cardinality=（P1+P2+…+P8）\*A/8。

在 InnoDB 存储引擎中，Cardinality 值是通过对 8 个叶子节点预估而得的，不是一个实际精确的值。

在 InnoDB 存储引擎中，Cardinality 统计信息的更新发生在两个操作中：INSERT 和 UPDATE。根据前面的叙述，不可能在每次发生 INSERT 和 UPDATE 时就去更新 Cardinality 信息，这样会增加数据库系统的负荷，同时对于大表的统计，时间上也不允许数据库这样去操作。因此，InnoDB 存储引擎内部对更新 Cardinality 信息的策略为：

*  表中 1/16 的数据已发生过变化。自从上次统计 Cardinality 信息后，表中 1/16 的数据已经发生过变化，这时需要更新 Cardinality 信息。
 *  stat\_modified\_counter ＞ 2000000000（20 亿）。InnoDB 存储引擎内部有一个计数器 stat\_modified\_counter，用来表示发生变化的次数，当 stat\_modified\_counter 大于 2000000000 时，则同样需要更新 Cardinality 信息。

当执行 SQL 语句 ANALYZE TABLE、SHOW TABLE STATUS、SHOW INDEX 以及访问 INFORMATION\_SCHEMA 架构下的表 TABLES 和 STATISTICS 时会导致 InnoDB 存储引擎去重新计算索引的 Cardinality 值。若表中的数据量非常大，并且表中存在多个辅助索引时，执行上述这些操作可能会非常慢。虽然用户可能并不希望去更新 Cardinality 值。

Cardinality 值非常关键，优化器会根据这个值来判断是否使用这个索引。但是这个值并不是实时更新的，即并非每次索引的更新都会更新该值，因为这样代价太大了。如果需要更新索引 Cardinality 的信息，除了上边所说的，也可以使用 ANALYZE TABLE 命令。

在某些情况下可能会发生索引建立了却没有用到的情况，可能会出现 Cardinality 为 NULL，致使优化器不选择使用索引。这时最好的解决办法就是做一次 ANALYZE TABLE 的操作。因此在场景允许的情况下，建议在一个非高峰时间，对应用程序下的几张核心表做 ANALYZE TABLE 操作，这能使优化器和索引更好地为你工作。

##### 4.什么是离散读? #####

在某些情况下，当执行 EXPLAIN 命令进行 SQL 语句的分析时，会发现优化器并没有选择索引去查找数据，而是通过扫描聚集索引，也就是直接进行全表的扫描来得到数据。这种情况多发生于范围查找、JOIN 链接操作等情况下。

假设表：t\_index。其中 id 为主键；c1 与 c2 组成了联合索引（c1，c2）；此外，c1 还是一个单独索引。进行如下查询操作：

SELECT * FROM t_index WHERE c1＞1 and c1＜100000;

可以看到表 t\_index 有（c1，c2）的联合主键，此外还有对于列 c1 的单个索引。上述这句 SQL 显然是可以通过扫描 OrderID 上的索引进行数据的查找。然而通过 EXPLAIN 命令，用户会发现优化器并没有按照 OrderID 上的索引来查找数据。

在最后的索引使用中，优化器选择了 PRIMARY id 聚集索引，也就是表扫描（tablescan），而非 c1 辅助索引扫描（index scan）。

这是为什么呢？因为如果强制使用 c1 索引，就会造成离散读。具体原因在于用户要选取的数据是整行信息，而 c1 作为辅助索引不能覆盖到我们要查询的信息，因此在对 c1 索引查询到指定数据后，还需要一次书签访问来查找整行数据的信息。虽然 c1 索引中数据是顺序存放的，但是再一次进行书签查找的数据则是无序的，因此变为了磁盘上的离散读操作。如果要求访问的数据量很小，则优化器还是会选择辅助索引，但是当访问的数据占整个表中数据的蛮大一部分时（一般是 20%左右），优化器会选择通过聚集索引来查找数据。

##### 7.Mysql 如何获取数据页? #####

哈希表，利用哈希算法。  
哈希算法是一种常见算法，时间复杂度为 O（1），且不只存在于索引中，每个数据库应用中都存在该数据库结构。  
数据库中一般采用除法散列的方法，发生碰撞时采用链地址法。  
在哈希函数的除法散列法中，通过取 k 除以 m 的余数，将关键字 k 映射到 m 个槽的某一个去，即哈希函数为：

h(k)=kmodm

InnoDB 存储引擎使用哈希算法来对字典进行查找，其冲突机制采用链表方式，哈希函数采用除法散列方式。对于缓冲池页的哈希表来说，在缓冲池中的 Page 页都有一个 chain 指针，它指向相同哈希函数值的页。而对于除法散列，m 的取值为略大于 2 倍的缓冲池页数量的质数。例如：当前参数 innodb\_buffer\_pool\_size 的大小为 10M，则共有 640 个 16KB 的页。对于缓冲池页内存的哈希表来说，需要分配 640×2=1280 个槽，但是由于 1280 不是质数，需要取比 1280 略大的一个质数，应该是 1399，所以在启动时会分配 1399 个槽的哈希表，用来哈希查询所在缓冲池中的页。

InnoDB 存储引擎的表空间都有一个 space\_id，用户所要查询的应该是某个表空间的某个连续 16KB 的页，即偏移量 offset。InnoDB 存储引擎将 space\_id 左移 20 位，然后加上这个 space\_id 和 offset，即关键字 K=space\_id ＜＜ 20+space\_id+offset，然后通过除法散列到各个槽中去。

##### 8.innodb 主键自增是如何保证的? #####

自增长在数据库中是非常常见的一种属性，也是很多 DBA 或开发人员首选的主键方式。在 InnoDB 存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器（auto-increment counter）。当对含有自增长的计数器的表进行插入操作时，这个计数器会被初始化，执行如下的语句来得到计数器的值：

SELECT MAX(auto_inc_col) FROM t FOR UPDATE;

插入操作会依据这个自增长的计数器值加 1 赋予自增长列。这个实现方式称做 AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的 SQL 语句后立即释放。

虽然 AUTO-INC Locking 从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。其次，对于 INSERT…SELECT 的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从 MySQL5.1.22 版本开始，InnoDB 存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并且从该版本开始，InnoDB 存储引擎提供了一个参数 innodb\_autoinc\_lock\_mode 来控制自增长的模式，该参数的默认值为 1。

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UFbnURrQ-1661597294034)(http://qinyingjie.cn/pic/241b02728e270b0f2756d08cc072a29a.png)\]

statement-base replicion（SBR），row-base replication（RBR).主从复制的方式

![image-20220412102248283][]

##### 9.mysql 单表优化有什么经验吗? #####

*  尽量使用 TINYINT、SMALLINT、MEDIUM INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
 *  VARCHAR 的长度只分配真正需要的空间
 *  使用枚举或整数代替字符串类型
 *  尽量使用 TIMESTAMP 而非 DATETIME，
 *  单表不要有太多字段，建议在 20 以内
 *  避免使用 NULL 字段，很难查询优化且占用额外索引空间
 *  用整型来存 IP

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL 在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL 在内部会将每个值在列表中的位置保存为整数，并且在表的.frm 文件中保存“数字-字符串”映射关系的“查找表”。如 enum（’man‘，‘woman’）实际存储为 1 和 2，减少数据占用空间。

TIMESTAMP 占用 4 字节，DATETIME 占用 8 字节，且 TIMESTAMP 在多数场景下容易转换。

可为 NULL 的列会使索引、索引统计和值比较都更复杂。可为 NULL 的列会使用更多的存储空间，在 MySQL 里也需要做特殊处理。当可为 NULL 的列被索引时，每个索引记录需要一个额外的字节，在 MyISAM 里面甚至可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

整型字段的比较比字符串效率高很多，这也符合一项优化原则：字段类型定义使用最合适(最小)，最简单的数据类型。

inet\_aton()算法，其实借用了国际上对各国 IP 地址的区分中使用的 ipnumber。

**查询 SQL**

*  可通过开启慢查询日志来找出较慢的 SQL
 *  不做列运算:SELECT id WHERE age+1=10，任何对列的操作都将导致表扫描，它包括数据库教
 *  程函数、计算表达式等等，查询时要尽可能将操作移至等号右边
 *  sql 语句尽可能简单:一条 sql 只能在一个 cpu 运算;大语句拆小语句，减少锁时间;一条大 sq 可以堵死整个库不用 SELECT \*
 *  OR 改写成 IN:OR 的效率是 n 级别，IN 的效率是 log(n)级别 in 的个数建议控制在 200 以内
 *  不用函数和触发器，在应用程序实现避免%xxx 式查询。少用 JOIN
 *  ·使用同类型进行比较，比如用’123’和’123’比，123 和 123 比
 *  尽量避免在 WHERE 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
 *  对于连续数值，使用 BETWEEN 不用 IN:SELECT id FROM t WHERE nUm BETWEEN 1 AND 5
 *  列表数据不要拿全表，要使用 LIMIT 来分页，每页数量也不要太大

**表分区**

MySQL 在 51 版引入的分区是一种简单的水平拆分，用户需要在建表的时候加上分区参数，对应用是透明的无需修改代码

对用户来说，分区表是一个独立的逻辑表，但是底层由多个物理子表组成，实现分区的代码实际上是通过对一组底层表的对象封装，但对 SQL 层来说是一个完全封装底层的黑盒子。MySQL 实现分区的方式也意味着索引也是按照分区的子表定义，没有全局索引

用户的 SOL 语句是需要针对分区表做优化，SOL 条件中要带上分区条件的列，从而使查询定位到少量的  
分区上，否则就会扫描全部分区，可以通过 EXPLATN PARTITTONS 来查看草条 SOL 语句会落在那些分区  
上，从而进行 SQL 优化，如下图 5 条记录落在两个分区上:

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2jwkXv6J-1661597294034)(http://qinyingjie.cn/pic/9b181faf512c188aadadcc417087827a.png)\]

##### 10.表分区有什么优缺点? #####

*  分区的好处是:
    
     *  可以让单表存储更多的数据
     *  分区表的数据更容易维护，可以通过清楚整个分区批量删除大量数据，也可以增加新的分区来支持新插入的数据。另外，还可以对一个独立分区进行优化、检查、修复等操作部分查询能够从查询条件确定只落在少数分区上，速度会很快
     *  分区表的数据还可以分布在不同的物理设备上，从而搞笑利用多个硬件设备
     *  可以使用分区表赖避免某些特殊瓶颈，例如 InnoDB 单个索引的互斥访问、ext3 文件系统的 inode 锁竞争
     *  可以备份和恢复单个分区
 *  分区的限制和缺点:
    
     *  一个表最多只能有 1024 个分区
     *  如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来
     *  分区表无法使用外键约束

##### 11.表分区有几种方式? #####

mysql 只支持水平分区,不支持垂直分区,当前 MySQL 数据库支持以下几种类型的分区。

*  分区的类型:
    
     *  RANGE 分区:基于属于一个给定连续区间的列值，把多行分配给分区
     *  LIST 分区:类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个值来进行选择
     *  HASH 分区:基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式
     *  KEY 分区:类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值
 *  分区适合的场景有:
    
     *  最适合的场景数据的时间序列性比较强，则可以按时间来分区

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分.如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列.

子分区(sub partitioning)是在分区的基础上再进行分区，有时也称这种分区为复合分区(composite partitioning)。MySQL 数据库允许在 RANGE 和 LIST 的分区上再进行 HASH 或 KEY 的子分区.

分区中对 NULL 值的处理为,放在最左边,相当于最小.

##### 12.垂直拆分和水平拆分? #####

**垂直拆分**

垂直分库是根据数据库里面的数据表的相关性进行拆分，比如 一个数据库里面既存在用户数据，又存  
在订单数据，那么垂直拆分可以把用户数据放到用户库、把订身单数据放到订单库。垂直分表是对数据表进行垂直拆分的一种方式，常见的是把一个多字段的大表按常用用字段和非常用字段进行拆分，每个表里面的数据记录数一般情况下是相同的，只是字段不一样，使用三主键关联

垂直拆分的优点是:

*  可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少 1/O 次数(每次查询时读取的 Block 就少)
 *  可以达到最大化利用 Cache 的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起数据维护简单

缺点是:

*  主键出现冗余，需要管理冗余列
 *  会引起表连接 JOIN 操作(增加 CPU 开销)可以通过在业务服务器上进行 join 来减少数据库压力。依然存在单表数据量过大的问题(需要水平拆分)。事务处理复杂

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dUNRtBg2-1661597294034)(http://qinyingjie.cn/pic/d294ee4918e42fc26c2727e833de42b3.png)\]

**水平拆分**

水平拆分是通过某种策略将数据分片来存储，分库内分表和分库 i 两部分，每片数据会分散到不同的  
MySQL 表或库，达到分布式的效果，能够支持非常大的数据量。 前面的表分区本质上也是一种特殊的库  
内分表  
库内分表，仅仅是单纯的解决了单一表数据过大的问题，由于没有把表的数据分布到不同的机器上，因此对于减轻 MySQL 服务器的压力来说，并没有太大的作用，大家家还是竞争同一个物理机上的 IO CPU、网络，这个就要通过分库来解决

![image-20220813231543377][]

水平拆分的优点是:

*  不存在单库大数据和高并发的性能瓶颈应用端改造较少
 *  提高了系统的稳定性和负载能力

缺点是:

*  分片事务一致性难以解决
 *  跨节点 Join 性能差，逻辑复杂数据多次扩展难度跟维护量极大

##### 13.分片有什么需要注意的吗? #####

*  能不分就不分，参考单表优化
 *  分片数量尽量少，分片尽量均匀分布在多个数据结点上，因为一个查询 SQL 跨分片越多，则总体性能越差，虽然要好于所有数据在一个分片的结果，只在必要的时候进行扩容，增加分片数量
 *  分片规则需要慎重选择做好提前规划，分片规则的选择，需要考虑数据的增长模式，数据的访问模式，分片关联性问题，以及分片扩容问题，最近的分片策略为范围分片，枚举分片，一致性 Hash 分片，这几种分片都有利于扩容
 *  尽量不要在一个事务中的 SQL 跨越多个分片，分布式事务一直是个不好处理的问题
 *  查询条件尽量优化，尽量避免 Select\*的方式，大量数据结果集下，会消耗大量带宽和 CPU 资源，查询尽量避免返回大量结果集，并且尽量为频繁使用的查询语句建立索引。。通过数据冗余和表分区赖降低跨库 Join 的可能

这里特别强调一下分片规则的选择问题，如果某个表的数据有明显的时间特征，比如订单、交易记录等，则他们通常比较合适用时间范围分片，因为具有时效性的数据，我们往往关注其近期的数据，查询条件中往往带有时间字段进行过滤，比较好的方案是，当前活跃的数据，采用跨度比较短的时间段进行分片，而历史性的数据，则采用比较长的跨度存储。  
总体上来说，分片的选择是取决于最频繁的查询 SQL 的条件，因因为不带任何 Where 语句的查询 SQL，会遍历所有的分片，性能相对最差，因此这种 SQL 越多，对系级统的影响越大，所以我们要尽量避免这种 SQL 的产生。

分片（分库分表)后，如何保证全局的唯一主键 id 呢？？

生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太⻓了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

数据库自增 id:两台数据库分别设置不同步⻓，生成不重复 ID 的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

利用 redis 生成 id:性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

[image-20220413004330195]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/a5a178671de341799731ac42dac00178.png
[image-20220815160603290]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5280fdc798974e35ac4c94961ff0999c.png
[image-20220816215001307]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ebeca2115bba4f899f0fe5d338dbe4e0.png
[image-20220816215054710]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/d5a1693a3f6345758d78bfa60683cd54.png
[image-20220816215152529]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/93abc7b7521c487fb8273b6cebcc2a86.png
[image-20220816215307055]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/cdfe5bd778be4c82b071cfd3bb02e9ed.png
[image-20220817091355684]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/8d889ac92fe14a0bb6714475e92f4ee7.png
[img]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/1516b153ee954529894cb97a4b56f4c8.png
[image-20220412185319769]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/81c2bdfa5c29415a90379c4a81beba96.png
[image-20220815160912443]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/14d0b731179147989b7fc2ea834ddd07.png
[image-20220412185022416]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ebfbff484682474983424742045c6d7c.png
[image-20220412185059402]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/87d19cedfcd84f2998f4081eaf6384b1.png
[image-20220818181415925]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/a38b9b96d73742dca5cbc913b146ae02.png
[image-20220827152212386]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/3c8bfa10caaf45edab4f6138be32ca61.png
[image-20220819130723440]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/d23dd9fac60e43e5ba303e7b22d55c41.png
[image-20220819130750549]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/bd714b2b9e444d9fb20d3d1ed966b1dd.png
[image-20220821184138466]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/37a11030b861442593d06e3e33539bcf.png
[image-20220821095211606]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ca283473797e4053895f0f31524fdede.png
[image-20220821104918456]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/fcd9158412ed44c8bd331bba83f070f1.png
[image-20220821112047254]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/849eb904b839431c8cee3cd575ba34c4.png
[image-20220821111726238]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/04d6aa28b52f41e59a864bd8d7b37e24.png
[image-20220821114216827]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/5236f82f9a0f409a82b3d1214f323388.png
[image-20220821114551869]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/b5ce2543b4e64e0db589a1b96052162b.png
[image-20220821115020740]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/715d0e18a0794aa49d9f540a5405a2b6.png
[image-20220821223536623]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/9fa82ff2e5dd4228b50b29931c9d247d.png
[image-20220811234352958]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/f73bb68696834ba88871d599eda24d5f.png
[image-20220812000811658]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/7765d088064e47059d5d7eeeafd43875.png
[image-20220812000647705]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/fa0030a057634294acf4f119a57851ab.png
[image-20220812000104551]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/89fe660e3aff4dee8997ff7bacc0413a.png
[image-20220812001413165]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/7f1fba90e27d4ef381159d4cfe42ed04.png
[name]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/ffc2ce1e310f43729179cf9f74b4c4a2.png
[image-20220812002726727]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/d76da665ada046e89519c2d013bcd1ae.png
[image-20220812181948111]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/bcab9da3b5214cdcaaec33cb36d125d2.png
[image-20220812182054722]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/9d4a357447a142f7a6ccd484a028a38e.png
[image-20220412101003089]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/77496235c8f94648ae6ecfe209331bc4.png
[image-20220826235409702]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/582dab3bb6b543dca493936c079ea874.png
[image-20220823085948035]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/e20dba934c49440d990c4f40d6b5631c.png
[MVCC _1]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/f2b6abec465246f68e904b4523da8e3c.png
[image-20220812194855318]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/e6398f1db92749c4a61dcedc3bed80da.png
[image-20220812201245142]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/18a2d2a327114fae838b6642d793de04.png
[image-20220812201358622]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/77ba5a33707d42809d03c94edd096e5c.png
[image-20220412185740452]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/8edac830ff664196a8bfb6af89c632c1.png
[image-20220412185900734]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/1ef4e7cb1a444f5190a205ca6b92238e.png
[image-20220412190112199]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/f8fc732bc40d4e74868306dec0d54128.png
[image-20220827161654519]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/a1499c1a818e42949003133fb455d2e6.png
[image-20220412191130572]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/c1b4ee6eb5a642689818cfd07125d02b.png
[image-20220412191217549]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/54e4be5b62e94a089d2fac8b46daecdd.png
[image-20220412191333973]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/d4c9aff360394417a2d2ac65835a40a8.png
[image-20220412102248283]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/efb7979e4ae341adae620acffa7b1a60.png
[image-20220813231543377]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/08/e5e47cc79e0c4614bf4bb708702c172c.png