mysql高阶

一.Mysql 基础与体系结构

1.什么是 Mysql

MySQL 是最流行的开源 SQL 数据库管理系统，由 Oracle 公司开发、分发和支持。Mysql 网站

MySQL 是一种关系型数据库管理系统，其全称为“My Structured Query Language”。

1. MySQL 是一个数据库管理系统;
   
2. MySQL 数据库是关系型数据库;
   
3. MySQL 是一种开源软件，可以在多个操作系统下使用；
   
4. MySQL 数据库服务器非常快速、可靠、可扩展且易于使用;
   
5. MySQL 支持多种存储引擎，如 MyISAM 和 InnoDB 等；
   
6. MySQL 具有强大的安全性和可靠性，可以通过 SSL 等加密方式进行数据传输。
   

2.内部结构和便携性

• 用 C 和 C++ 编写。
  
• 适用于许多不同的平台。
  
• 采用多层服务器设计，模块独立。
  
• 提供事务性和非事务性存储引擎。
  
• 使用非常快的 B 树磁盘表 ( MyISAM) 和索引压缩。
  
• 实现内存中的哈希表，用作临时表。
  
• 使用高度优化的类库实现 SQL 函数，该类库应该尽可能快。
  

3.Mysql8.0 新增功能

MySQL 8.0 是 MySQL 数据库管理系统的一个重要版本，它在 2018 年 4 月发布。以下是 MySQL 8.0 中的一些主要新增功能和改进：

1. 支持窗口函数：引入了窗口函数（Window Functions），允许在查询中进行更复杂的数据分析和聚合操作。
   
2. CTE（Common Table Expressions）：引入了公共表表达式，允许在查询中创建临时结果集，并在后续查询中引用它们。
   
3. 新增降序索引，可以支持降序的索引。
   
4. 增强的安全性：引入了更多的安全功能，如更强大的密码验证策略，支持 TLSv1.3 加密协议等。
   
5. 更好的 JSON 支持：增加了更多的 JSON 函数和操作符，使得在存储和查询 JSON 数据更加方便和高效。
   
6. 调优器改进：优化了查询优化器，使得一些查询在 MySQL 8.0 上执行更加高效。
   
7. 新的数据类型：新增了几种数据类型，例如 GEOMETRY 类型的空间数据支持，以及更好的 UUID 支持。
   
8. InnoDB 存储引擎改进：提供了更好的性能和可伸缩性，包括通过数据字典提高元数据的效率。
   
9. 新的字符集和排序规则：增加了新的 Unicode 字符集和排序规则，提供更好的全球化支持。
   
10. 自适应哈希索引：InnoDB 引擎中的自适应哈希索引可以提高特定查询的性能。
    
11. Group Replication：引入了 MySQL Group Replication，提供了原生的多主复制特性，使得多个 MySQL 实例可以组成一个高可用性和容错性的集群。
    

4.数据库与实例?

数据库与实例:

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

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

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

5.mysql 体系结构

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

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

6.其他存储引擎?

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

7.数据库语言

① 数据查询语言（Data Query Language，DQL）：

• DQL 主要用于数据的查询，其基本结构是使用 SELECT 子句，
  
• FROM 子句和 WHERE 子句的组合来查询一条或多条数据。
  

② 数据操作语言（Data Manipulation Language，DML）：

• DML 主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：
  
• INSERT：增加数据
  
• UPDATE：修改数据
  
• DELETE：删除数据
  

③ 数据定义语言（Data Definition Language，DDL）：

• DDL 主要用针对是数据库对象（表、索引、视图、>触发器、存储过程、函数、表空间等）进行创建、修改和删除操作。其主要包括：
  
• CREATE：创建数据库对象
  
• ALERT：修改数据库对象
  
• DROP：删除数据库对象
  

④ 数据控制语言（Data Control Language，DCL）：

• DCL 用来授予或回收访问数据库的权限，其主要包括：
  
• GRANT：授予用户某种权限
  
• REVOKE：回收授予的某种权限
  

⑤ TCL 用于数据库的事务管理。其主要包括：

• START TRANSACTION：开启事务
  
• COMMIT：提交事务
  
• ROLLBACK：回滚事务
  
• SET TRANSACTION：设置事务的属性
  

SQL 语言共分为以下几大类：查询语言 DQL，控制语言 DCL，操纵语言 DML，定义语言 DDL。事务控制 TCL.

• DQL（Data QUERY Languages）语句：即数据库定义语句，用来查询 SELECT 子句，FROM 子句，WHERE 子句组成的查询块，比如：select–from–where–grouop by–having–order by–limit
  
• DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构
  
• DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据
  
• DCL（Data Control Language）语句：即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。
  
• TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。
  

8.什么是异步 IO?

为了提高磁盘操作性能，当前的数据库系统都采用异步 IO(Asynchronous Input/Output，AIO)的方式来处理磁盘操作。InnoDB 存储引擎亦是如此。与 AIO 对应的是 Sync IO，即每进行一次 IO 操作，需要等待此次操作结束才能继续接下来的操作。

异步IO:用户可以在发出一个 IO 请求后立即再发出另一个 IO 请求，当全部请求发送完毕后，等待所有 IO 操作的完成，这就是 AIO。

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

(8，6)、(8，7)，(8，8)

9.QPS 和 TPS

QPS（Queries Per Second）和 TPS（Transactions Per Second）是衡量系统性能的指标，用于描述系统每秒处理的查询或事务数量。虽然它们在很多情况下是类似的概念，但在某些情况下有一些细微的区别。

1. QPS（Queries Per Second）：QPS 是指系统每秒处理的查询请求数量。这个指标通常用于描述数据库、Web 服务器、缓存服务器等系统的查询吞吐量。在数据库中，一个查询可以是一个 SELECT、UPDATE、INSERT 或 DELETE 操作。
   
2. TPS（Transactions Per Second）：TPS 是指系统每秒处理的事务数量。这个指标通常用于描述事务性应用程序（如银行交易、在线支付等）的处理能力。在这种情况下，一个事务通常涉及多个查询和更新操作，因此 TPS 通常比 QPS 更小。
   

需要注意的是，QPS 和 TPS 都是衡量系统处理能力的指标，但它们不能独立地表示系统性能的好坏。实际上，系统的性能取决于很多因素，包括硬件配置、软件优化、网络延迟等等。

10.数据库三大范式

数据库三大范式是关系数据库设计中的规范化原则，旨在减少数据冗余和提高数据的一致性。这些范式被命名为第一范式（1NF）、第二范式（2NF）和第三范式（3NF）。以下是它们的简要介绍：

1. 第一范式（1NF）：

• 数据表中的每个列都应该包含原子性的值，即每个列不能包含多个值或重复的值。
  
• 每个表必须有一个主键来唯一标识每一行。
  

2. 第二范式（2NF）：

• 在 1NF 的基础上，要求每个非主键列完全依赖于整个主键，而不是部分依赖。
  
• 如果一个表的主键由多个列组成，则每个非主键列必须依赖于所有主键列，而不是只依赖于部分主键列。
  

3. 第三范式（3NF）：

• 在 2NF 的基础上，要求每个非主键列之间没有传递依赖关系。
  
• 换句话说，如果一个非主键列依赖于另一个非主键列，那么这两个非主键列都应该成为一个新的表，并且非主键列与主键之间建立关联。
  

通过遵循这些范式，可以保持数据库结构的合理性和一致性，避免冗余数据和数据更新异常，提高数据库的性能和可维护性。需要注意的是，在实际设计数据库时，有时需要根据具体的业务需求和查询优化等因素来权衡是否严格遵循三大范式。有时会允许部分冗余数据以提高查询性能或简化查询操作。

11.什么是视图?

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

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

物化视图:存在物理存储。

12.常见约束

在 MySQL 中，常见的约束用于限制表中数据的有效性和完整性。以下是 MySQL 中常见的约束类型：

1. 主键约束（Primary Key Constraint）：用于唯一标识表中的每一行。主键字段的值必须是唯一的，且不能为 NULL。一个表只能有一个主键。
   
2. 唯一约束（Unique Constraint）：确保一个字段或一组字段的值在表中是唯一的，但允许字段为空值（NULL）。
   
3. 外键约束（Foreign Key Constraint）：用于确保表中的数据与另一个表中的数据保持引用完整性。外键约束通常用于创建表之间的关系。
   
4. 非空约束（NOT NULL Constraint）：确保字段不允许为空值（NULL）。
   
5. 默认约束（Default Constraint）：指定字段的默认值，如果在插入数据时没有提供该字段的值，则使用默认值。
   
6. 检查约束（Check Constraint）：用于指定数据必须满足的条件。只有满足条件的数据才能被插入或更新到表中。
   

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

13.drop,truncate,delete 区别?

1. DROP：

• DROP 删除表和数据
  
• DROP是用于删除数据库对象（表、视图、索引、存储过程等）的操作。
  
• 例如，使用DROP TABLE语句可以删除一个表，以及与该表相关的数据、索引和约束等。
  
• DROP操作是一个 DDL（Data Definition Language）命令，因此会立即提交事务，并且不能回滚。执行后数据和对象会永久删除。
  

2. TRUNCATE：

• TRUNCATE 清空表中的数据,会释放空间,重置主键
  
• TRUNCATE用于快速删除表中的所有数据，但保留表结构。
  
• 与DELETE相比，TRUNCATE操作通常更快，因为它不会逐行删除数据，而是直接删除表中的所有数据。
  
• TRUNCATE操作是一个 DDL 命令，也会立即提交事务，并且不能回滚。执行后数据会永久删除，但表结构仍然保留。
  

3. DELETE：

• DELETE 仅仅只是删除表中的数据
  
• DELETE用于从表中删除特定的行，可以根据条件删除满足条件的数据。
  
• DELETE是一个 DML（Data Manipulation Language）命令，因此可以包含在一个事务中，并且可以回滚。
  
• DELETE操作逐行删除数据，并在删除过程中生成事务日志，因此相对较慢，特别是对于大量数据的删除。
  

14.SQL 运行顺序?

sql 语句执行过程

#编写过程

select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

#解析过程

from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

查询 sql 的关键字书写顺序

select from where group by having order by limit

查询 sql 的关键字执行顺序

from where group by having select order by limit

编写技巧 根据需求挑选关键字，按照书写顺序依次排列关键字 按照关键字的执行顺序填空

15.JDBC 操作流程?

1. 加载数据库驱动类
   
2. 打开数据库连接
   
3. 执行 sql 语句
   
4. 处理返回结果
   
5. 关闭资源
   

二.Innodb 引擎特性

1.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，并以此作为主键。

2.InnoDB 的关键特性?

• 插入缓冲(Insert Buffer)
  
• 两次写(Double Write)
  
• 自适应哈希索引(Adaptive Hash Index)
  
• 异步 IO(Async IO)
  
• 刷新邻接页(Flush Neighbor Page)
  
• 行锁设计
  
• 高并发性 MVCC
  

3.innodb 和 myisam 的区别

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

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

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

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

4.表锁和行锁的区别?

数据即索引,索引即数据,行锁的实现是依赖于 innodb 存储引擎的索引设计,并且与记录锁,间隙锁,临键锁等都有一定的关系。本质来说,锁的还是索引。

在 MySQL 的 InnoDB 存储引擎中，表锁和行锁是两种不同的锁机制，用于控制并发访问数据库中的数据。它们在锁定粒度和底层实现上有一些区别。

1. 表锁（Table Locks）：

• 锁定粒度： 表锁是对整张表进行锁定，意味着当一个事务获取了表锁后，其他事务无法对该表进行任何读写操作，即使是不涉及锁定行的操作。
  
• 适用场景： 表锁适用于少量的、相对稳定的数据访问模式，或者当需要执行大规模的数据维护操作时，可以快速获取整张表的锁来保护数据完整性。
  
• 底层实现： InnoDB 存储引擎中的表锁是通过在数据字典中设置一个标志来实现的。这种锁定方式效率较低，因为它会导致大量并发事务之间的阻塞。
  

2. 行锁（Row Locks）：

• 锁定粒度： 行锁是针对表中的单行数据进行锁定，允许多个事务在同一时刻同时访问表的不同行。
  
• 适用场景： 行锁适用于高并发的数据访问模式，其中只有少数行会被同时访问，这可以减少并发事务之间的竞争，提高系统的并发性能。
  
• 底层实现： InnoDB 存储引擎实现行锁的方式是通过在每一行数据的存储上增加两个隐藏的字段，记录了这行数据的锁信息。这使得 InnoDB 能够在仅锁定需要的行时避免不必要的锁竞争，从而提高了并发性。
  

行锁相对于表锁具有更细粒度的控制能力，能够更好地支持高并发的访问模式。然而，行锁也可能引入一些额外的开销，如死锁的风险和锁管理的复杂性。因此，在选择使用表锁还是行锁时，需要根据具体的应用场景和性能需求进行权衡。在 InnoDB 存储引擎中，默认情况下会使用行级锁定来支持更好的并发控制。

5.什么是插入缓冲?

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

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

• 索引是辅助索引(secondary index)，聚集索引是唯一的，不合适
  
• 索引不是唯一(unique)的，因为如果是唯一的，需要校验将要插入的数据是否唯一，性能较低.
  

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

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

6.插入缓冲的实现

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

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

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

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

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

IBUF_REC_OFFSET_COUNT 是保存两个字节的整数，用来排序每个记录进入 Insert Buffer 的顺序。因为从 InnoDB1.0.x 开始支持 Change Buffer，所以这个值同样记录进入 Insert Buffer 的顺序。通过这个顺序回放(replay)才能得到记录的正确值。从 Insert Buffer 叶子节点的第 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 Buffer Bitmap 页中占用 4 位(bit).

7.什么是 merge insert buffer?

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

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

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

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

8.什么是 Change Buffer?

Change Buffer（变更缓冲区）是数据库引擎中的一种优化技术，用于提高数据修改操作（如更新或删除）的性能。类似于 Insert Buffer（插入缓冲区），Change Buffer 也是用于减少磁盘写入操作，从而提高数据库的写入性能。

当进行数据修改操作时，数据库引擎需要在磁盘中定位到对应的数据页（page），然后进行修改并将修改后的数据写回磁盘。在高并发的写入操作场景中，频繁的磁盘写入操作可能成为性能瓶颈。为了减少这种开销，数据库引擎引入了 Change Buffer。

Change Buffer 的工作原理如下：

1. 当进行更新或删除操作时，引擎会将数据的修改操作暂时存储在 Change Buffer 中，而不是直接写回磁盘。
   
2. Change Buffer 是一个内存中的数据结构，它存储了待修改的数据页（page）中发生的变更信息，例如需要更新的数据行和更新后的值，或者需要删除的数据行的标识。
   
3. 当 Change Buffer 达到一定大小或者满足一定条件时，数据库引擎将会把 Change Buffer 中的修改操作合并到对应的数据页上，并将合并后的数据页写回磁盘。这个过程通常在后台进行，不会阻塞当前的写入操作。
   

对一条记录进行 delete 操作可能分为两个过程:

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

因此 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_buffer_max_size 来控制 ChangeBuffer 最大使用内存的数量:

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

9.什么是二次写?

关于 IO 的最小单位：

• 数据库 IO 的最小单位是 16K(MySQL 默认，oracle 是 8K)
  
• 文件系统 IO 的最小单位是 4K(也有 1K 的)
  
• 磁盘 IO 的最小单位是 512 字节
  

因此，存在 IO 写入导致 page 损坏的风险

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

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

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

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

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

哈希(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;

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

11.什么是刷新邻接页?

InnoDB 存储引擎还提供了 Flush Neighbor Page(刷新邻接页)的特性。

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

参数 innodb_flush_neighbors，用来控制是否启用该特性。

12.什么是 MRR 优化?

MySQL5.6 版本开始支持 Multi-Range Read(MRR)优化。Multi-Range Read 优化的目的就是为了减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问。

MRR 优化是数据库中的一种优化技术，全称为 "Multi-Range Read Optimization"（多范围读取优化）。它是用于改善数据库查询性能的一种方法，特别适用于处理范围查询（Range Query）操作。

范围查询是指根据某个范围条件（例如：日期范围、数值范围等）来检索数据库中的数据。执行范围查询时，数据库引擎需要定位到满足条件的多个数据行，并将这些数据行返回给查询请求。在高并发或大规模数据量的情况下，范围查询可能会导致性能下降，因为需要大量的磁盘访问和数据检索操作。

MRR 优化通过改进数据的访问方式，使得范围查询的性能得到提升。它的主要原理如下：

1. 数据排序：MRR 优化首先会对查询的数据进行排序，以确保它们在物理存储上是连续的或者相邻的。这种排序可以减少随机磁盘访问的次数，从而提高范围查询的效率。
   
2. 批量读取：MRR 优化将范围查询的结果数据按照较大的块（例如页或者块）批量读取到内存中，而不是单个数据行一个接一个地读取。这样可以减少磁盘访问的次数，提高数据读取的效率。
   
3. 并行处理：MRR 优化还可以通过并行处理的方式来加速范围查询。它可以将查询结果分成多个子任务，并同时处理这些子任务，从而更好地利用多核处理器的计算能力。
   

13.什么是 ICP 优化?

ICP 优化是数据库查询中的一种优化技术，全称为 "Index Condition Pushdown"（索引条件下推）。它是用于改善数据库查询性能的一种方法，特别适用于复杂的查询操作，涉及多个条件和多个索引的情况。

• 禁用 ICP 时，存储引擎会通过遍历索引定位基表中的行，然后返回给 Server 层，再去为这些数据行进行 where 条件的过滤。
  
• 启用 ICP 时，如果 where 条件可以使用索引，MySQL 会把这部分过滤操作放到存储引擎层，存储引擎通过索引过滤，把满足的行从表中读取出。 ICP 可以减少存储引擎必须访问基表的次数以及 Server 曾必须访问存储引擎的次数。
  

和 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 提示。

14.ICP 的限制条件?

ICP（Index Condition Pushdown）优化在数据库查询中是一个有用的技术，但是它也有一些限制条件，这些限制条件可能影响它的适用性和效果。以下是一些常见的 ICP 优化的限制条件：

1. 范围条件：ICP 优化主要适用于等值条件，即对于索引列的查询条件是等于某个值的情况。对于范围条件（如大于、小于、区间等），ICP 优化的效果可能较差，因为范围条件的处理通常需要在数据层级进行，无法完全下推到索引层级。
   
2. 多列条件：ICP 优化通常对于多列条件的处理有限。当查询涉及多个条件，并且这些条件之间没有简单的逻辑关系时，ICP 优化的效果可能不明显。
   
3. 联合索引：对于联合索引，ICP 优化只能应用于最左前缀，即只有查询条件包含联合索引的最左列时才能进行优化。如果查询条件涉及联合索引的非最左列，ICP 优化无法生效。
   
4. NULL 值：对于包含 NULL 值的索引列，ICP 优化可能受到限制。由于 NULL 值的特殊性，它的处理可能需要额外的检查和判断，可能无法完全下推到索引层级。
   
5. 引用外部表：如果查询涉及到引用外部表的数据，ICP 优化可能无法生效。因为外部表的数据可能需要额外的访问和处理，无法直接下推到索引层级。
   
6. 子查询和复杂查询：对于包含子查询或者复杂查询逻辑的情况，ICP 优化可能有限或者无法应用。
   

尽管 ICP 优化有上述的限制条件，但它在很多情况下仍然是非常有用的优化技术，特别是对于单列等值查询的情况。数据库引擎通常会根据查询的复杂性和数据分布来自动选择是否应用 ICP 优化。同时，通过合理的索引设计和查询优化，可以进一步提高 ICP 优化的效果和数据库查询性能。

15.如何避免离散读?

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 特性时，看到的执行计划如图：

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

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 语句强制指定索引。

16.说说 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 操作中完成。

17.二进制和事务提交一致性?

MySQL5.6 采用了 Binary Log Group 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 失效的问题。
  

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

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

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

18.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 线程的压力。
  

19.innodb 内存分配?

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

• 可以有多个缓冲池实例，默认为 1
  
• 使用 check point 机制刷新到磁盘
  

• 缓冲池

• 数据页
  
• 索引页
  
• 插入缓冲
  
• 锁信息
  
• 自适应哈希索引
  
• 数据字典信息
  

• 重做日志缓冲
  
• 额外内存池:缓冲控制对象等需要从额外内存池分配内存.
  

20.LRU、Free、Flush 区别?

• 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 列表用于刷新到磁盘.
  

21.热点数据不被冲掉?

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

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

22.压缩页的表?

首先，在 unzip LRU 列表中对不同压缩页大小的页进行分别管理。其次，通过伙伴算法进行内存的分配。例如对需要从缓冲池中申请页为 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 列表中。
   

23.什么是 checkpoint?

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

checkpoint 的作用

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

对于 InnoDB 存储引擎而言，其是通过 LSN(Log Sequence 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%
  

三.Mysql 文件

1.mysql 文件有哪些?

• 参数文件:告诉 MvSQL 实例启动时在哪里可以找到数据库文件。并且指定某些初始化参数，这些参数定义了某种内存结构的大小等设置，还会介绍各种参数的类型。
  
• 日志文件:用来记录 MySQL 实例对某种条件做出响应时写入的文件，如错误日志文件、二进制日志文件、慢查询日志文件、查询日志文件等。

• 错误日志(error log)
  
• 二进制日志(bin log)
  
• 慢查询日志(slow query log) long_query_time 默认是 10s
  
• 查询日志(log)
  

• socket 文件:当用 UNIX 域套接字方式进行连接时需要的文件。
  
• pid 文件:MySQL 实例的进程 ID 文件。
  
• MySQL 表结构文件:用来存放 MySQL 表结构定义文件，文件后缀为 frm。
  

索引文件:

存储引擎文件:因为 MySQL 表存储引擎的关系，每个存储引擎都会有自己的文件来保存各种数据。这些存储引擎真正存储了记录和索引等数据。

• MyISAM

• myd 文件：存放表数据文件
  
• myi 文件：存放表索引文件
  

• InnoDB

• .ibd 文件:存放表数据和表索引文件
  

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

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

物理日志:

• 存储内容：存储数据库中特定记录的变更，通常是 oriented page，即描述具体某一个 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 information，也可以称为 redo log；
  

逻辑日志:

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

3.mysql 的配置文件

MySQL 数据库是按如下顺序读取配置文件的。

/etc/my.cnf > /etc/mysql/my.cnf > /usr/local/mysql/etc/my.cnf  >  ~/my.cnf

1. 系统级配置文件：MySQL 首先读取系统级配置文件，这通常是安装 MySQL 时指定的主配置文件。在 Unix/Linux 系统中，它通常是位于/etc/my.cnf 或/etc/mysql/my.cnf。
   
2. 用户级配置文件：接下来，MySQL 会读取用户级配置文件。在 Unix/Linux 系统中，用户级配置文件通常是位于用户的主目录下的 my.cnf 文件。在 Windows 系统中，它位于用户主目录下的 my.ini 文件。
   
3. 当前工作目录配置文件：如果在启动 MySQL 时指定了--defaults-file 选项，并且该选项指定了一个配置文件路径，则 MySQL 会读取该配置文件作为当前工作目录配置文件。
   
4. 内置默认值：如果上述配置文件都不存在或未设置某些选项，MySQL 将使用内置的默认值来设置这些选项。
   
5. 如果几个配置文件中都有同一个参数，MySQL 数据库会以读取到的最后一个配置文件中的参数为准。
   

4.表空间文件?

• 共享表空间:所有表的信息放在一个文件
  
• 独立表空间:每个表单独放在一个文件
  

5.二进制日志的作用?

二进制文件作用:

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

6.binlog 的刷盘时机

mysql 通过 sync_binlog 参数控制 biglog 的刷盘时机，取值范围是 0-N：

• 0：不去强制要求，由系统自行判断何时写入磁盘；
  
• 1：每次 commit 的时候都要将 binlog 写入磁盘；
  
• N：每 N 个事务，才会将 binlog 写入磁盘。
  

7.说说 binlog_format 参数?

binlog_format 参数十分重要，它影响了记录二进制日志的格式。在 MySQL5.1 版本之前，没有这个参数。所有二进制文件的格式都是基于 SQL 语句(statement)级别的。同时，对于复制是有一定要求的。如在主服务器运行 rand、uuid 等函数，又或者使用触发器等操作，这些都可能会导致主从服务器上表中数据的不一致(not sync)。

另一个影响是，会发现 InnoDB 存储引擎的默认事务隔离级别是 REPEATABLE READ。这其实也是因为二进制日志文件格式的关系，如果使用 READ COMMITTED 的事务隔离级别(大多数数据库，如 Oracle， SQLServer 数据库的默认隔离级别)，会出现类似丢失更新的现象，从而出现主从数据库上的数据不一致。

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

STATEMENT、ROW 的思想类似于 redis 的 RDB 和 AOF.

• 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)。
  

8.什么是 redo log?

MySQL 的Redo Log（重做日志）是一种持久化日志机制，用于确保即使在数据库崩溃或突然重启等异常情况下，事务的持久性（Durability，ACID 属性的一个组成部分）仍然能得到保证。

具体来说，当一个事务进行更改（例如，INSERT、UPDATE、DELETE 等操作）时，这些更改首先会被写入到 Redo Log 中。这个过程通常是非常快的，因为只需要写入到磁盘的顺序部分，而不需要直接更新磁盘上的数据文件。一旦 Redo Log 被安全地写入，事务就可以被认为是“提交”的，从而满足持久性的要求。

Redo Log 通常分为两部分：

1. In-memory Redo Log Buffer：这是内存中的一个缓冲区，用于暂存即将写入磁盘的 Redo Log 记录。
   
2. On-disk Redo Log Files：这些是磁盘上的物理文件，用于持久化存储 Redo Log 数据。这些文件通常是循环使用的（也就是说，当达到一定大小后，旧的日志记录会被覆盖）。
   

在数据库恢复（Recovery）过程中，Redo Log 用于“重做”事务，以确保所有提交的事务都得到应用，而所有未提交的事务都被回滚，从而达到一致性的状态。

使用 Redo Log 有多个好处：

1. 提高性能：由于 Redo Log 是顺序写入的，因此具有更高的 I/O 性能。
   
2. 数据安全性：即使在系统崩溃的情况下，也能通过 Redo Log 恢复数据，保证数据的持久性。
   
3. 简化恢复过程：由于 Redo Log 包含了如何从一个一致状态转变到另一个一致状态的所有信息，因此数据库恢复变得相对简单和快速。
   

MySQL 的 Redo Log 是一种关键的持久化机制，用于保证事务的持久性并提高系统的整体性能和可靠性。

9.重做日志块?

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

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

日志块头(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 个字节.

10.重做日志缓冲?

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

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

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

11.重做日志文件?

在默认情况下，在 InnoDB 存储引擎的数据目录下会有两个名为ib_logfile0 和 ib_logfile1的文件。是重做日志文件(redo log file)。它们记录了对于 InnoDB 存储引擎的事务日志。当实例或介质失败(media failure)时，重做日志文件就能派上用场。例如，数据库由于所在主机掉电导致实例失败，InnoDB 存储引擎会使用重做日志恢复到掉电前的时刻，以此来保证数据的完整性。

每个 InnoDB 存储引擎至少有 1 个重做日志文件组(group)，每个文件组下至少有 2 个重做日志文件，如默认的 ib_logfile0 和 ib_logfile1。为了得到更高的可靠性，用户可以设置多个的镜像日志组(mirrored log groups)，将不同的文件组放在不同的磁盘上，以此提高重做日志的高可用性。在日志组中每个重做日志文件的大小一致，并以循环写入的方式运行。InnoDB 存储引擎先写重做日志文件 1，当达到文件的最后时，会切换至重做日志文件 2

• innodb_log_fle_size:每个重做日志文件的大小 最大 512G
  
• innodb_log_flesin_group:组中重做日志文件的数量 默认为 2
  
• innodb_mirrored_log_groups:日志镜像文件组的数量，默认为 1
  
• innodb_log_group_home_dir:日志文件组所在路径
  
• 一组两个重做日志文件的默认大小通常是 48MB，即每个文件大小为 48MB。
  

12.重做日志块与重做日志文件

重做日志（Redo Log）是数据库管理系统中的一种重要机制，用于保证事务的持久性和数据恢复。在 MySQL 等数据库管理系统中，重做日志通常由多个重做日志文件组成，每个文件由多个重做日志块组成。

重做日志块（Redo Log Block）：

• 重做日志块是重做日志的最小单位，通常是一个固定大小的数据块。
  
• 它记录了数据库中发生的每个事务所进行的修改操作，如更新、插入和删除等。
  
• 当事务执行修改操作时，先将修改记录到重做日志块，然后再将修改应用到内存中的数据页上。
  
• 重做日志块是顺序写入的，因为它需要保证记录的顺序与事务的执行顺序一致。
  
• 一旦事务将修改记录到重做日志块并提交，数据库就可以认为该事务的修改已经持久化，并可以在必要时进行恢复。
  

重做日志文件（Redo Log File）：

• 重做日志文件是由多个重做日志块组成的逻辑文件，用于存储重做日志的内容。
  
• 重做日志文件是一个循环的文件组，当写满一个重做日志文件后，数据库会切换到下一个重做日志文件，以保证循环使用。
  
• 在数据库运行期间，重做日志文件会不断地写入新的重做日志块，并且不会删除已经写入的内容，这是为了确保发生故障时可以进行数据恢复。
  
• 当数据库发生崩溃或意外停机时，重做日志文件中的内容将被用于恢复未提交的事务和未写入磁盘的数据页，以保证数据的一致性。
  

总结:

• 重做日志块是重做日志的最小记录单位，记录每个事务的修改操作。
  
• 重做日志文件是由多个重做日志块组成的逻辑文件，用于存储重做日志的内容，确保数据的持久性和一致性，以及在数据库崩溃时进行恢复。
  

13.重做日志的格式?

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

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

14.重做日志刷盘机制

从重做日志缓冲往磁盘写入时，是按 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_at_trx_commit 设置为 1，也就是每当有事务提交时，就必须确保事务都已经写入重做日志文件。那么当数据库因为意外发生宕机时，可以通过重做日志文件恢复，并保证可以恢复已经提交的事务。而将重做日志文件设置为 0 或 2，都有可能发生恢复时部分事务的丢失。不同之处在于，设置为 2 时，当 MySQL 数据库发生宕机而操作系统及服务器并没有发生宕机时，由于此时未写入磁盘的事务日志保存在文件系统缓存中，当恢复时同样能保证数据不丢失。

15.什么是 LSN?

LSN（Log Sequence Number）是数据库中用于标识事务日志（重做日志）顺序的一种递增的序列号。LSN 在数据库中扮演着非常重要的角色，用于管理事务的提交和回滚、故障恢复以及数据一致性等方面。

LSN 的作用：

• 标识事务日志的顺序：每当一个事务进行数据修改时，相关的重做日志（Redo Log）记录会被生成，并分配一个唯一的 LSN 号。这样，数据库系统就可以通过 LSN 来确定事务日志的顺序，从而保证修改的顺序与事务的执行顺序一致。
  
• 事务的提交和回滚：数据库系统使用 LSN 来跟踪和管理事务的提交和回滚。当一个事务提交时，其对应的 LSN 会被记录在持久化存储中，以确保在故障恢复时，可以正确地重新执行已提交的事务。而在事务回滚时，数据库可以利用 LSN 来撤销事务所做的修改。
  
• 故障恢复：在数据库系统发生崩溃或异常停机时，通过 LSN 可以确定在故障发生前已经提交的事务，以及还未提交的事务。通过重做日志和 LSN，数据库可以重新执行已提交的事务，确保数据的持久性和一致性。
  
• 数据一致性：在数据库的恢复过程中，LSN 可以确保已提交事务的修改在崩溃后重新执行，从而维护数据的一致性。
  

总结： LSN（Log Sequence Number）是数据库中用于标识事务日志顺序的递增序列号。通过 LSN，数据库系统可以管理事务的提交和回滚，实现故障恢复，并保证数据的一致性。LSN 在数据库的可靠性和恢复方面起着重要的作用。

16.binlog 和重做日志区别?

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

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

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

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

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

17.什么是 undo log?

Undo Log（撤销日志）是数据库管理系统中的另一个关键机制，用于支持事务的回滚和并发控制。在 MySQL 等数据库中，每个事务执行期间所做的修改操作除了被记录到重做日志（Redo Log）中，还会被记录到 Undo Log 中。

undo log 叫做回滚日志，用于记录数据被修改前的信息。他正好跟前面所说的重做日志所记录的相反，重做日志记录数据被修改后的信息。undo log 主要记录的是数据的逻辑变化，为了在发生错误时回滚之前的操作，需要将之前的操作都记录下来，然后在发生错误时才可以回滚。

Undo Log 的作用：

• 支持事务的回滚：当一个事务需要回滚（撤销）时，数据库可以利用 Undo Log 中的信息将事务所做的修改操作逆向执行，从而撤销该事务对数据所做的改变。
  
• 实现并发控制：当一个事务在执行期间，可能会读取其他事务正在修改的数据。为了确保事务的读取操作能够得到一致性的数据，数据库会将其他事务正在修改的数据版本记录到 Undo Log，这样在读取时，可以根据 Undo Log 中的数据版本来获取一个一致性的数据视图。
  

Undo Log 的工作原理：

• 在事务执行期间，如果进行了数据的修改（例如插入、更新、删除等），数据库会先将修改前的数据记录到 Undo Log 中，保留原始数据的一个副本。
  
• 当事务提交时，Undo Log 中的记录被保留，用于可能的回滚操作，以确保事务的原子性和一致性。
  
• 如果事务执行过程中发生错误，或者事务被回滚，数据库可以利用 Undo Log 中的信息将事务的修改操作逆向执行，从而回滚事务。
  
• 并发控制方面，当一个事务需要读取数据时，数据库会检查其他正在执行的事务是否已经修改过该数据。如果是，数据库可以通过 Undo Log 中的数据版本来提供给读取操作一个一致性的数据视图。
  

总结:Undo Log 是数据库中用于支持事务回滚和实现并发控制的机制。它记录了事务执行过程中所做的数据修改操作的原始值，以便在事务回滚或提供一致性读取时使用。通过 Undo Log，数据库可以保证事务的原子性、一致性和隔离性。

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

18.举例说明重做日志的作用

• DB_TRX_ID：记录创建这条记录/最后一次修改该记录的事务 ID
  
• DB_ROW_ID: 隐含的自增 ID
  
• DB_ROLL_PTR: 指向这条记录的上一个版本
  

19.undo log 和 redo log?

事务隔离性由锁来实现。原子性、一致性、持久性由数据库的 redo log 和 undo log 来完成。

redo log 称为重做日志来保证事务的原子性和持久性。

undo log 用来保证事务的一致性。

undo 不是 redo 的逆过程。redo log 和 undo log 的作用都可以视为是一种恢复操作，redo 恢复提交事务修改页操作，而 undo 回滚行记录到某个特定版本。因此两者记录的不同，redo 通常是物理日志，记录的是页的物理修改操作。undo 逻辑日志，根据每行记录进行记录。

在 MySQL 中，重做日志（Redo Log）是用于保证事务的持久性（Durability）的一种机制。它由两部分组成：重做日志缓冲（Redo Log Buffer）和重做日志文件（Redo Log File）。

重做日志缓冲是一个内存区域，用于暂存事务执行过程中对数据的修改操作。当事务提交时，重做日志缓冲中的内容会被刷写到重做日志文件中。

重做日志文件是一个磁盘文件，用于记录所有的事务修改操作。当数据库发生宕机等异常情况时，重做日志文件可以用来恢复数据。重做日志文件的大小是固定的。重做日志的作用是保证事务的持久性和可恢复性。

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

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

20.慢查询日志

程序中定位一个执行慢的 SQL 可以根据慢查询日志，默认情况下，慢查询日志禁用，因为开启慢查询日志或多或少的会对 mysql 的性能产生一些影响。在慢查询日志功能开启时，只有 SQL 执行时间超过 long_query_time 参数值的的语句才会在慢查询日志中记录。long_query_time 参数，最小值和默认值分别为 0 10，单位为秒。

• slow_query_log:是否开启慢查询日志
  
• long_query_time:查询阈值，超过了该阈值则记录到慢查询日志中
  
• log_output:如何存储慢查询日志，可选项：FILE 或者 TABLE
  
• slow_query_log_file:以 FILE 类型存储慢查询日志时的存储位置
  

set global slow_query_log=on;

mysql> select sleep(11);

四.Mysql 表数据结构

1.mysql 的索引数据结构?

数据结构特点:

• 底层使用的数据结构是 b+树
  
• 查询时间复杂度是 O(logN)
  
• 叶子节点存储数据
  
• 非叶子节点不存储数据
  

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

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

2.聚集索引和辅助索引

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

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

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

3.高度为 4 的 B+树能存储的数据?

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

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

查看页的大小语句:这就说明了一个页的大小为 16384B， 也就是 16kb。

show variables like 'innodb_page_size';

#innodb_page_size  16384

一个页的大小为 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+树可以存放的数据行的个数为：

一页可以存放1170个指针，一页可以存放16行的数据，1170x16=18720(行)

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

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

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

众多的数据结构在逻辑层面可分为：线性结构 和 非线性结构。

• 线性结构有：数组、链表，基于它们衍生出的有哈希表（哈希表也称散列表）、栈、队列等。
  
• 非线性结构有：树、图。
  
• 还有其他数据结构如：跳表、位图 也都由基础数据结构演化而来，不同的数据结构存在即都是为了解决某些场景问题。
  

索引虽然存储在磁盘上，但使用索引查找数据时，可以从磁盘先读取索引放到内存中，再通过索引从磁盘找到数据；再然后将磁盘读取到的数据也放到内存里。索引就让磁盘和内存强强联手.

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

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

总结选用 B+树的原因:

• 要尽少在磁盘做 I/O 操作。
  
• 要能尽快的按照区间高效地范围查找。

• 等值查询：哈希、跳表，不适合范围查询。
  
• 范围查询：树结构，但是会带来磁盘 IO 的情况，也会造成树过高的问题，所以衍生出 B 树，
  
• 而 B 树又因为范围查询的情况下， 会一直到根节点再到叶子节点这样查询。所以延伸出了 B+树，解决范围查询的情况。
  
• 查询效率，B 树的话不稳定，O(1-logN)之间，而 B+树的话可以稳定在 O(logN)
  

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

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

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

6.什么是索引组织表?

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

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

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

7.innodb 逻辑存储结构

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

8.共享表空间

在默认情况下 InnoDB 存储引擎有一个共享表空间 ibdata1，即所有数据都存放在这个表空间内。如果用户启用了参数 innodb_file_per_table，则每张表内的数据可以单独放到一个表空间内。

如果启用了 innodb_file_per_table 的参数，需要注意的是每张表的表空间内存放的只是数据、索引和插入缓冲 Bitmap 页，其他类的数据，如回滚(undo)信息，插入缓冲索引页、系统事务信息，二次写缓冲(Double write buffer)等还是存放在原来的共享表空间内。这同时也说明了另一个问题:即使在启用了参数 innodb_fle_per_table 之后，共享表空间还是会不断地增加其大小。

9.innodb 中的段

表空间是由各个段组成的，常见的段有数据段、索引段、回滚段等。InnoDB 存储引擎表是索引组织的(index organized)，因此数据即索引，索引即数据。

• 数据段即为 B+树的叶子节点(Leaf node segment)
  
• 索引段即为 B+树的非索引节点(Non-leaf node segment)
  

10.innodb 中的区

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

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

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

11.innodb 中的页

同大多数数据库一样，InnoDB 有页(Page)的概念(也可以称为块)，页是 InnoDB 磁盘管理的最小单位。在 InnoDB 存储引擎中。默认每个页的大小为 16KB。而从 InnoDB1.2x 版本开始，可以通过参数 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)
  

12.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';

13.Compact 行格式

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

Compact 行格式结构

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

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

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

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

SQL 中的NULL值认为是列中最小的值，放在 b+树的最左边.

IS NULL、IS NOT NULL、!=不一定不用索引，需要看优化器的优化情况.

14.Redundant 行格式

Redundant 行格式结构

Redundant 行格式是为了兼容以前版本的页格式，Redundant 是冗余的意思

不同于 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 值需要占用空间。

15.Compressed 和 Dynamic

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

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

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

Compressed和Dynamic行格式:

Compact 和 Redundant行格式:

16.行溢出数据

在 MySQL 中一行除了 blob 及 text 类的大字段之外，其余字段的长度之和不能超过 65535，最大支持 65535 字节，实际执行会报错，实际支持 65532 字节.使用 latin1 字符集时是 65532 字节，使用 utf8 编码时，utf8 编码占 3 位，最大支持 21844 字节.

mysql 理论支持的Row Size Limit 是 65535 为什么实际是 65532

这是因为 MySQL 需要额外的字节来存储一些内部数据，例如行格式信息和 NULL 位图。

具体来说，每个行都需要几个字节来存储行格式信息和 NULL 位图。行格式信息用于指示行的格式，例如是否使用动态行格式或静态行格式。NULL 位图用于表示行中哪些列具有 NULL 值。这些额外的字节会占用行的空间，从而减少了实际可用的行大小。NULL 位图占一个字节

任何存储引擎都不能超过65535

mysql> CREATE TABLE t (a VARCHAR(10000)， b VARCHAR(10000)，

      c VARCHAR(10000)， d VARCHAR(10000)， e VARCHAR(10000)，

      f VARCHAR(10000)， g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used

table type， not counting BLOBs， is 65535. This includes storage overhead，

check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE t (a VARCHAR(10000)， b VARCHAR(10000)，

      c VARCHAR(10000)， d VARCHAR(10000)， e VARCHAR(10000)，

      f VARCHAR(10000)， g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;

ERROR 1118 (42000): Row size too large. The maximum row size for the used

table type， not counting BLOBs， is 65535. This includes storage overhead，

check the manual. You have to change some columns to TEXT or BLOBs

非空可以创建成功:

CREATE TABLE t2

      (c1 VARCHAR(65533) NOT NULL)

      ENGINE = InnoDB CHARACTER SET latin1;

可以为空的场景:

CREATE TABLE t2222

      (c1 VARCHAR(65532) DEFAULT NULL)

      ENGINE = InnoDB CHARACTER SET latin1;

65535 是指所有 varchar 列的总和，不是单个 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;

17.字段个数

Column Count Limits

Column Count Limits 表字段最大限制.官方文档的内容如下，主要意思是字段个数限制达不到理想的 4096 个，且和字段类型有关。

• MySQL 中 innodb 引擎的字段上限是 1017。
  
• MySQL 中 MyISAM 引擎表最多可以存 2598 个字段。
  

受影响的因素:

• 存储引擎
  
• 表的单行最大行影响
  
• 单个列的存储要求
  

18.行最大字节

Row Size Limits

Row Size Limits 行的最大字节数.MySQL 表的内部表示具有 65535 字节的最大行大小限制，即使存储引擎能够支持更大的行。BLOB 和 TEXT 列只占行大小限制的 9 到 12 个字节，因为它们的内容与行的其余部分分开存储。

对于 4KB、8KB、16KB 和 32KB InnoDB_page_size 设置，应用于本地存储在数据库页面中的数据的 InnoDB 表的最大行大小略小于页面的一半。例如，对于默认的 16KB InnoDB 页面大小，最大行大小略小于 8KB。对于 64KB 的页面，最大行大小略小于 16KB。

如果包含可变长度列的行超过了 InnoDB 的最大行大小，InnoDB 会选择可变长度列用于外部页外存储，直到该行符合 InnoDB 的行大小限制。对于页外存储的可变长度列，本地存储的数据量因行格式而异。

不同的存储格式使用不同数量的页眉和尾部数据，这会影响行的可用存储量。

19.数据页对行大小限定

在 InnoDB 中，每个数据页（database page）的大小可以通过设置 innodb_page_size 参数进行配置，常见的大小包括 4KB、8KB、16KB 和 32KB，还有更大的 64KB 页。

在 InnoDB 中，行数据存储在数据库页内部。然而，为了保证数据库的性能和效率，InnoDB 对于每个页内的数据行大小有一定的限制。对于 4KB、8KB、16KB 和 32KB 的页面大小设置，InnoDB 将数据行大小限制在略小于半个数据库页的大小。这意味着，对于 4KB 页，数据行大小将略小于 2KB，对于 8KB 页，数据行大小将略小于 4KB，以此类推。

对于 64KB 的页面大小设置，InnoDB 将数据行大小限制在略小于 16KB。换句话说，对于 64KB 页，数据行大小将略小于 16KB。

这些限制是为了确保在数据库页内存储的数据行不会过大，从而避免内存浪费和性能下降。同时，这些限制还有助于在处理和查询数据时提高效率，因为较小的数据行大小可以提高数据访问速度和内存利用率。

这些限制是为了优化 InnoDB 存储引擎的性能和效率，同时避免过大的数据行带来的问题。

20.innodb 的页结构

页的总体结构图:

21.数据页的结构

InnoDB为了不同的目的而设计了许多种不同类型的页。常见的页类型有数据页(索引页)、Undo 页、系统页、事务数据页等。InnoDB 数据页由以下 7 个部分组成

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

22.File Header

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

FILE_PAGE_TYPE 页类型:

23.Page Header

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

Infimun 和 Supremum Records

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

24.User Records

存储行格式的地方，行格式的记录头信息

delete_mask:删除标记为 1 记录不会立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打一个删除标记而已，所有被删除掉的记录都会组成一个所谓的垃圾链表，在这个链表中的记录占用的空间称之为所谓的可重用空间，之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空间覆盖掉。

next_record(单链表):表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。 比方说第一条记录的 next_record 值为 32，意味着从第一条记录的真实数据的地址处向后找 32 个字节便是下一条记录的真实数据。

注意：

• 下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 Infimum 记录（也就是最小记录）的下一条记录就是本页中主键值最小的用户记录
  
• 未被删除的记录(delete_mask = 0)会按照主键从小到大的顺序形成了一个单链表
  

25.Free Space

自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

26.Page Directory(页目录)

页目录的过程:

1. 将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。
   
2. 每个组的最后一条记录（也就是组内最大的那条记录）的头信息中的n_owned属性表示该记录拥有多少条记录，也就是该组内共有几条记录。
   
3. 将每个组的最后一条记录的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方，这个地方就是所谓的Page Directory，也就是页目录（此时应该返回头看看页面各个部分的图）。页面目录中的这些地址偏移量被称为槽（英文名：Slot），所以这个页面目录就是由槽组成的。
   

注意

对于最小记录所在的分组只能有 1 条记录，最大记录所在的分组拥有的记录条数只能在 1~8 条之间，剩下的分组中记录的条数范围只能在是 4~8 条之间。

分组过程

1. 初始情况下一个数据页里只有最小记录和最大记录两条记录，它们分属于两个分组。
   
2. 之后每插入一条记录，都会从页目录中找到主键值比本记录的主键值大并且差值最小的槽，然后把该槽对应的记录的n_owned值加 1，表示本组内又添加了一条记录，直到该组中的记录数等于 8 个。
   
3. 在一个组中的记录数等于 8 个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中 4 条记录，另一个 5 条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。
   

查询记录的过程:

过程分为两步：

1. 通过二分法确定该记录所在的槽，并找到该槽中主键值最小的那条记录。
   
2. 通过记录的 next_record 属性遍历该槽所在的组中的各个记录。
   

因为各个槽代表的记录的主键值都是从小到大排序的，所以我们可以使用所谓的二分法来进行快速查找。4 个槽的编号分别是：0、1、2、3、4，所以初始情况下最低的槽就是low=0，最高的槽就是high=4。比方说我们想找主键值为6的记录，过程是这样的：

1. 计算中间槽的位置：(0+4)/2=2，所以查看槽2对应记录的主键值为8，又因为8 > 6，所以设置high=2，low保持不变。
   
2. 重新计算中间槽的位置：(0+2)/2=1，所以查看槽1对应的主键值为4，又因为4 < 6，所以设置low=1，high保持不变。
   
3. 因为high - low的值为 1，所以确定主键值为5的记录在槽2对应的组中。此刻我们需要找到槽2中主键值最小的那条记录，然后沿着单向链表遍历槽2中的记录。但是我们前面又说过，每个槽对应的记录都是该组中主键值最大的记录，这里槽2对应的记录是主键值为8的记录，怎么定位一个组中最小的记录呢？别忘了各个槽都是挨着的，我们可以很轻易的拿到槽1对应的记录（主键值为4），该条记录的下一条记录就是槽2中主键值最小的记录，该记录的主键值为5。所以我们可以从这条主键值为5的记录出发，遍历槽2中的各条记录，直到找到主键值为6的那条记录即可。由于一个组中包含的记录条数只能是 1~8 条，所以遍历一个组中的记录的代价是很小的。
   

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

SELECT * FROM Profile ORDER BY id LIMIT 10;

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

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

聚簇索引与非聚簇索引的区别:

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。

在MySQL的InnoDB存储引擎中， 聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。

• 一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
  
• 一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。
  

2.什么是辅助索引?

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

3.什么是二级索引?

MySQL 的二级索引，也称为辅助索引或非聚集索引，是一种用于提高数据库查询性能的索引类型。与主键索引（聚集索引）不同，二级索引不是基于表的物理顺序排序的，而是独立于表数据的额外数据结构。

二级索引允许您在表中的一个或多个列上创建索引，以加快特定列或列组合的查询速度。当您在一个或多个列上创建二级索引后，MySQL 将会构建一个索引数据结构，该数据结构包含索引列的值和指向实际数据位置的指针。这样，当查询需要使用索引列进行筛选或排序时，MySQL 可以直接使用二级索引来定位相关的数据行，而无需扫描整个表。

与主键索引不同，二级索引不是表的物理排序顺序，因此在使用二级索引进行查询时，MySQL 可能需要额外的 I/O 操作来访问实际的数据行。这就是为什么使用二级索引可能会比使用主键索引稍慢的原因。然而，二级索引的存在仍然可以大大提高查询性能，特别是对于那些频繁使用特定列进行筛选或排序的查询。

需要注意的是，当对表进行更新（插入、更新或删除）时，MySQL 还需要维护二级索引的一致性，以保证索引的准确性和完整性。因此，在创建二级索引时，需要权衡查询性能和更新性能之间的平衡。

4.什么是联合索引?

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

# 创建联合索引

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;

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

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);

以上代码建立了两个索引来进行比较。两个索引都包含了 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;

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

explain SELECT b from buy_log_01 where b=1

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

5.什么是覆盖索引?

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

覆盖索引就是查询的结果中全部包含了索引字段

如果要查询辅助索引中不含有的字段，得先遍历辅助索引，再遍历聚集索引，而如果要查询的字段值在辅助索引上就有，就不用再查聚集索引了，这显然会减少 IO 操作。

覆盖索引的好处:

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

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

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

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

• select count(*) from table;
  
• select 覆盖索引 from table;
  

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 查询是统计操作，并且可以利用到覆盖索引的信息，因此优化器会选择该联合索引：

从图中可以发现列 possible_keys 为 userid_2，列 key 为 userid_2，即表示（userid，buy_date）的联合索引。在列 Extra 同样可以发现 Using index 提示，表示为覆盖索引。

不符合最左前缀时也可以走索引:全扫描联合索引树的方式查询到数据

执行计划里的 type 是 index，这代表着是通过全扫描联合索引树的方式查询到数据的，这是因为 where buy_date 并不符合联合索引最左匹配原则。

那么，如果写了个符合最左原则的 select 语句，那么 type 就是 ref，这个效率就比 index 全扫描要高一些。

因为联合索引树的记录比要小的多，而且这个 select * 不用执行回表操作，所以直接遍历联合索引树要比遍历聚集索引树要小的多，因此 MySQL 选择了全扫描联合索引树。

6.什么是最左前缀原则?

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);

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

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

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

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

7.MySQL 联合索引?

#联合索引

index(a,b,c)

#查询语句

1. select * from T where a=x and b=y and c=z;

2. select * from T where a=x and b>y and c=z;

3. select * from T where c=z and a=x and b=y;

4. select (a，b) from T where a=x and b>y;

5. select count(*) from T where a=x;

6. select count(*) from T where b=y;

7. select count(*) form T;

1. a、b、c 三个字段都可以走联合索引。
   
2. a 和 b 都会走联合索引，但是由于最左匹配原则， 范围查找后面的字段是无法走联合索引的，但是在 mysql 5.6 版本后，c 字段虽然无法走联合索引，但是因为有索引下推的特性，c 字段在 inndob 层过滤完满足查询条件的记录后，才返回给 server 层进行回表，相比没有索引下推，减少了回表的次数。
   
3. 查询条件的顺序不影响，优化器会优化，所以 a、b、c 三个字段都可以走联合索引。
   
4. a 和 b 都会走联合索引，查询是覆盖索引，不需要回表。
   
5. a 可以走联合索引。
   
6. 只有 b，无法使用联合索引，由于表存在联合索引，所以 count(*) 选择的扫描方式是扫描联合索引来统计个数，扫描的方式是 type=index。
   
7. 由于表存在联合索引，所以 count(*) 选择的扫描方式是扫描联合索引来统计个数，扫描的方式是 type=index。
   

关于 count(*) 为什么选择扫描联合索引（二级索引），而不扫描聚簇索引的原因：这是因为相同数量的二级索引记录可以比聚簇索引记录占用更少的存储空间，所以二级索引树比聚簇索引树小，这样遍历二级索引的 I/O 成本比遍历聚簇索引的 I/O 成本小，因此「优化器」优先选择的是二级索引。

8.什么是自适应 hash 索引?

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

SELECT * FROM TABLE WHERE index_col='xxx';

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

SHOW ENGINE INNODB STATUS;

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

9.什么是全文索引?

当前 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，在具体文档中的位置)}
  

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

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

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);

11.索引设计原则

设计原则：

1. 针对于数据量较大，且查询比较频繁的表建立索引
   
2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
   
3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高
   
4. 如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引
   
5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
   
6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率
   
7. 如果索引列不能存储 NULL 值，请在创建表时使用 NOT NULL 约束它。当优化器知道每列是否包含 NULL 值时，它可以更好地确定哪个索引最有效地用于查询
   

12.什么列作为索引更加有效?

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

SELECT * FROM student WHERE sex='M';

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

怎样查看索引是否是高选择性的呢？可以通过 SHOW INDEX 结果中的列 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 的用户，将会得到如下的执行计划：

SHOW INDEX FROM mysql_user;

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

选择合适的字段创建索引:

• 不为 NULL 的字段 ：索引字段的数据应该尽量不为 NULL，因为对于数据为 NULL 的字段，数据库较难优化。如果字段频繁被查询，但又避免不了为 NULL，建议使用 0，1，true，false 这样语义较为清晰的短值或短字符作为替代。
  
• 被频繁查询的字段 ：我们创建索引的字段应该是查询操作非常频繁的字段。
  
• 被作为条件查询的字段 ：被作为 WHERE 条件查询的字段，应该被考虑建立索引。
  
• 频繁需要排序的字段 ：索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。
  
• 被经常频繁用于连接的字段 ：经常用于连接的字段可能是一些外键列，对于外键列并不一定要建立外键，只是说该列涉及到表与表的关系。对于频繁被连接查询的字段，可以考虑建立索引，提高多表连接查询的效率。
  

13.索引关键属性

SHOW INDEX FROM chatbot;

• Table:索引所在的表名。
  
• Non_unique:非唯一的索引，可以看到 primary key 对应的 id 是 0，因为必须是唯一的。
  
• Key_name:索引的名字，用户可以通过这个名字来执行 DROP INDEX。
  
• Seg_in_index:索引中该列的位置，如果看联合索引 idxac 就比较直观了。
  
• Column_name:索引列的名称。
  
• Collation:列以什么方式存储在索引中。可以是 A 或 NULL。B+树索引总是 A，即排序的。如果使用了 Heap 存储引擎，并且建立了 Hash 索引，这里就会显示 NULL 了。因为 Hash 根据 Hash 桶存放索引数据，而不是对数据进行排序。
  
• Cardinality:非常关键的值，表示索引中唯一值的数目的估计值。Cardinality 的值应尽可能接近表的最大行数，如果非常小，那么用户需要考虑是否可以删除此索引。
  
• 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的值

ANALYZE TABLE kwan_t1;

在实际应用中，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 信息。

14.explain 使用?

Explain 执行计划中各个字段的含义:

id 详解

• id 相同，执行顺序从上到下
  
• id 值越大执行优先级越高
  

select_type 详解

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：

• SIMPLE:简单的 select 查询，查询中不包含子查询或 union 查询。
  
• PRIMARY:查询中若包含任何复杂的子部分，最外层查询为 PRIMARY，也就是最后加载的就是 PRIMARY。
  
• SUBQUERY:在 select 或 where 列表中包含了子查询，就为被标记为 SUBQUERY。
  
• DEPENDENT SUBQUERY：子查询中的第一个 SELECT，取决于外面的查询
  
• DERIVED:在 from 列表中包含的子查询会被标记为 DERIVED(衍生)，MySQL 会递归执行这些子查询，将结果放在临时表中。
  
• UNION:若第二个 select 出现在 union 后，则被标记为 UNION，若 union 包含在 from 子句的子查询中，外层 select 将被标记为 DERIVED。
  
• DEPENDENT UNION：UNION 中的第二个或后面的 SELECT 语句，取决于外面的查询
  
• UNION RESULT:从 union 表获取结果的 select。
  
• DERIVED：导出表的 SELECT(FROM 子句的子查询)
  

type 详解

• index：这种类型表示 mysql 会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个联合索引的一部分，mysql 都可能会采用 index 类型的方式扫描。但是呢，缺点是效率不高，mysql 会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。
  
• ref：这种类型表示 mysql 会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。
  

type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：

• ALL（全表扫描）；
  
• index（全索引扫描）；
  
• range（索引范围扫描）；
  
• ref（非唯一索引扫描）；
  
• eq_ref（唯一索引扫描）；
  
• const（结果只有一条的主键或唯一索引扫描）;
  
• system (系统表);
  
• 考虑到查询效率问题，全表扫描和全索引扫描要尽量避免(all 和 index)。
  
• 从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL
  

Extra：该列包含 MySQL 解决查询的详细信息。

• Distinct：MySQL 发现第 1 个匹配行后，停止为当前的行组合搜索更多的行。
  
• Not exists：MySQL 能够对查询进行 LEFT JOIN 优化，发现 1 个匹配 LEFT JOIN 标准的行后，不再为前面的的行组合在该表内检查更多的行。
  
• range checked for each record (index map: #)：MySQL 没有发现好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。对前面的表的每个行组合，MySQL 检查是否可以使用 range 或 index_merge 访问方法来索取行。
  
• Using filesort：MySQL 需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配 WHERE 子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。
  
• Using index：从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时，可以使用该策略。
  
• Using temporary：为了解决查询，MySQL 需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的 GROUP BY 和 ORDER BY 子句时。
  
• Using where：WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果 Extra 值不为 Using where 并且表联接类型为 ALL 或 index，查询可能会有一些错误。
  
• Using sort_union(...)， Using union(...)， Using intersect(...)：这些函数说明如何为 index_merge 联接类型合并索引扫描。
  
• Using index for group-by：类似于访问表的 Using index 方式，Using index for group-by 表示 MySQL 发现了一个索引，可以用来查询 GROUP BY 或 DISTINCT 查询的所有列，而不要额外搜索硬盘访问实际的表。并且，按最有效的方式使用索引，以便对于每个组，只读取少量索引条目。
  

15.不走索引的情况?

尽量用覆盖索引，注意查询的数据量，也就是查询条件的选取

• 不要在索引列上进行运算操作， 索引将失效。
  
• 字符串类型字段使用时，不加引号，索引将失效。数字类型加不加引号都走索引。
  
• 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
  
• 用 or 分割开的条件

• 如果 or 前后的查询字段有一个没有索引，则不走索引
  
• 如果 or 前后都有索引，在 8.0 中走索引，在 5.7 中不走索引
  

• not in ，not exist 不走索引
  
• <> 不等于的情况

• 在 8.0 中走索引
  
• 在 5.7 中跟数据量有关，如果不等于的结果集大于 20%不走索引，小于 20%走索引
  

• 在包含有 null 值的 table 列上建立索引，当时使用 select count(*) from table 时不会使用索引
  
• 如果 MySQL 评估使用索引比全表更慢，则不使用索引
  

16.优化器不使用单列索引？

在某些情况下，当执行 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 来强制使用某个索引.

17.desc 的索引实现?

在 mysql5.7 版本中添加降序索引后，并不会实际的添加降序索引，在 mysql8.0 中添加降序索引会添加降序索引.

降序的索引的执行计划中多了个 Backward index scan 反向索引扫描。

升序查询的时候，由于指定列是降序排列的，所以执行计划中多了个 using filesort 的结果，用到了文件排序，而在一些大型表的排序过程中，使用文件排序是非常消耗性能的。使用降序索引可以避免文件排序，这一点，就是降序索引能够带来的直观收益。

• 降序索引只能在 innodb 存储引擎中使用，其他存储引擎不支持。
  
• 使用聚合函数如果没有使用 group by 子句，不能使用降序索引进行优化。
  
• 降序索引只支持 BTREE 索引，不支持 HASH 索引。
  
• 升序索引支持的数据类型，降顺索引都支持。
  

18.为什么读小数据也是 16kb?

MySQL中执行一条SQL语句，相应表数据的读写都是由存储引擎去做（更新数据、查询数据）。

在这个过程，存储引擎需要决策一些事情

• 数据是从内存查还是从硬盘查
  
• 数据是更新在内存，还是硬盘
  
• 内存的数据什么时候同步到硬盘
  

InnoDB存储引擎在内存中有两个非常重要的组件，分别是缓冲池（Buffer Pool）和重做日志缓存（redo log buffer）。

缓冲池（Buffer Pool)里面会缓存很多的数据，比如数据页、索引页、锁信息等等。MySQL 表数据是以页为单位，你查询一条记录，会从硬盘把一页的数据加载出来，加载出来的数据叫数据页，会放入到 Buffer Pool中。后续的查询先从 Buffer Pool中找，没有命中再去硬盘加载，减少硬盘 IO 开销，提升性能。更新表数据的时，如果Buffer Pool里命中数据，就直接在Buffer Pool 里更新。

19.NULL 值问题

在 MySQL 中，创建二级索引时，索引树上通常不会包含 NULL 值。索引树的目的是加速查询，而 NULL 值并不提供有用的信息来加速查询过程。

如果您在一个可为空（nullable）的字段上创建了二级索引，并且该字段的某些行具有 NULL 值，这些 NULL 值的记录不会在索引树中显示。当执行查询时，如果您要查找具有 NULL 值的记录，MySQL 将不会使用该二级索引，而是需要执行全表扫描来找到包含 NULL 值的记录。

这是因为在 MySQL 中，对于包含 NULL 值的记录，查询优化器通常认为它们的数量相对较少，通过全表扫描来定位这些记录可能更为高效。因此，当您执行查询条件包含对 NULL 值的判断时，MySQL 可能会选择进行全表扫描。

如果您经常需要查询具有 NULL 值的记录，并且性能成为问题，您可以考虑使用其他技术，如使用一个额外的标志字段来指示是否为 NULL，或者使用特殊的值来替代 NULL 值，以便在二级索引中进行索引。

需要注意的是，MySQL 的行为在不同的版本和配置下可能会有所不同。因此，建议在具体的环境中进行测试和评估，以确定在包含 NULL 值的字段上使用二级索引时的实际性能和行为。

20.NULL 值是怎么存放的？

MySQL 的 Compact 行格式中会用「NULL值列表」来标记值为 NULL 的列，NULL 值并不会存储在行格式中的真实数据部分。

NULL 值列表会占用 1 字节空间，当表中所有字段都定义成NOT NULL，行格式中就不会有 NULL值列表，这样可节省 1 字节的空间。

在 MySQL 数据库中，NULL 值是一种特殊的值，用于表示缺少值或未知值。在内部，MySQL 使用一种称为 "NULL bitmap" 或 "null bitmap" 的机制来存储 NULL 值。

每个存储在表中的行都有一个对应的 NULL 位图，它是一个位数组，其中的每个位对应于该行中的一个列。如果列的值为 NULL，则对应的位被设置为 1；如果列的值不为 NULL，则对应的位被设置为 0。这样，数据库系统可以有效地跟踪每列是否包含 NULL 值。

这种位图的存储方式使得数据库可以更有效地使用存储空间，并且能够快速地进行 NULL 值的检索和比较。当查询需要检查 NULL 值时，数据库系统可以直接引用 NULL 位图，而无需实际检查每个列的值。

需要注意的是，NULL 值在数据库中有特定的语义，它表示缺少值或未知值，与空字符串或零值不同。在查询中，可以使用 IS NULL 或 IS NOT NULL 来检查 NULL 值。

MySQL 使用 NULL 位图来存储 NULL 值，这种机制在数据库中有效地管理和表示缺少值。

21.is null 查询问题

select age from table where age is null;

在 MySQL 中，当使用select age from table where age is null;这样的查询语句时，如果"age"列没有被设置为索引，那么 MySQL 通常不会使用索引来执行这个查询。原因是，对于包含IS NULL或IS NOT NULL条件的查询，MySQL 的优化器通常认为全表扫描是更有效的方式。

当没有索引可用时，MySQL 需要扫描整个表来找到满足条件的行，然后返回"age"列的值。如果"age"列被设置为索引，MySQL 可能会使用索引来快速定位满足条件的行，这样可以大大加快查询速度。

但是对于"age is null"这种条件，即使有索引，MySQL 通常也不会使用它。这是因为 NULL 值在数据库中是一种特殊的值，索引的存储方式会导致难以有效地使用索引来加速这类查询。因此，MySQL 倾向于执行全表扫描来查找 NULL 值，而不是使用索引。

如下图所示，通过 explain 查询出来的 type 为 ALL，ALL: 表示 MySQL 将执行全表扫描，将遍历表中的每一行来匹配查询条件。这通常发生在没有合适的索引可供优化查询时，或者查询条件无法使用索引优化。

22.表的最大索引个数

在 MySQL 中，每个表的索引数量是有限制的，但具体的限制取决于 MySQL 的版本和存储引擎。在某些情况下，一个表可以支持多于 64 个索引。

在 MySQL 5.7 及之前的版本中，每个表最多支持 64 个索引。这是因为 MySQL 5.7 及之前的版本使用了类似于file_per_table的存储模式，导致每个表的索引数量有限制。

然而，在 MySQL 8.0 及之后的版本中，情况有所改变。MySQL 8.0 引入了InnoDB存储引擎的innodb_large_prefix特性，默认情况下为开启状态。该特性允许索引的最大长度扩展到 3072 字节（之前为 767 字节），这使得每个表可以支持更多的索引数量。

总体而言，具体支持的最大索引数量取决于 MySQL 的版本和存储引擎，并且可能受到配置选项的影响。对于大多数应用场景而言，不太可能需要超过 64 个索引，因为合理使用较少数量的索引通常就能够满足查询优化的需求。

23.什么是 FIC?

说说 FIC(Fast 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 方式只限定于辅助索引，对于主键的创建和删除同样需要重建一张表.

大表如何添加索引:

如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？

我们需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故的。可以参考以下方法：

1. 先创建一张跟原表A数据结构相同的新表B。
   
2. 在新表B添加需要加上的新索引。
   
3. 把原表A数据导到新表B
   
4. rename新表B为原表的表名A，原表A换别的表名；
   

24.有没有比 FIC 更好的方式?

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

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

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

使用语法：

alter table tba_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 部分为索引创建或删除时对表添加锁的情况：

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

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

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

25.什么是离散读?

在某些情况下，当执行 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%左右），优化器会选择通过聚集索引来查找数据。

26.索引下推

select * from employee where name like '小%' and age=28 and sex='0';

其中，name和age为联合索引（idx_name_age）。

如果是Mysql5.6 之前，在idx_name_age索引树，找出所有名字第一个字是“小”的人，拿到它们的主键id，然后回表找出数据行，再去对比年龄和性别等其他字段。如图：

idx_name_age（name,age)不是联合索引嘛？为什么选出包含“小”字后，不再顺便看下年龄age再回表呢，不是更高效嘛？所以呀，MySQL 5.6就引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

因此，MySQL5.6 版本之后，选出包含“小”字后，顺表过滤age=28

27.如何创建联合索引

联合索引（也称为复合索引或组合索引）是数据库中的一种重要优化工具，用于提高数据库查询性能。创建联合索引时需要考虑一些原则，以确保索引的有效性和效率。以下是创建联合索引的一些原则：

1. 选择合适的列组合： 联合索引涉及多个列，因此需要仔细选择哪些列应该包含在索引中。选择常用于查询条件、连接条件或排序操作的列。避免在联合索引中包含过多的列，因为这可能会增加索引维护的开销，并且不一定会带来更好的性能提升。
   
2. 列的顺序： 列的顺序对联合索引的性能影响很大。应该将最常用于过滤数据的列放在索引的前面，这有助于数据库更快地定位到所需的数据。如果经常使用的列在索引的后面，数据库可能需要更多的操作才能达到所需的数据行。
   
3. 避免重复列： 不要在联合索引中重复包含相同的列。如果某列已经在索引的前面出现，就不需要再将其放在后面的位置。
   
4. 平衡性能需求： 联合索引需要权衡查询性能和索引维护的成本。过多的索引可能会导致写操作（如插入、更新和删除）变慢，因为数据库需要维护多个索引。因此，需要根据实际场景综合考虑查询性能和写操作性能。
   
5. 考虑查询选择性： 索引的选择性是指索引列中不同值的数量与总行数的比例。选择性越高，索引在过滤数据时的效率越高。因此，选择选择性较高的列作为索引列通常会带来更好的性能。
   
6. 避免过度索引： 不要为每个可能的查询都创建一个新的联合索引。过多的索引可能会导致索引失效，降低性能。根据实际使用情况选择最有价值的查询来创建索引。
   
7. 定期维护索引： 随着数据的增加和变化，索引的性能可能会下降。定期重新组织或重建索引，以保持其效率。数据库管理系统通常提供了工具来进行索引维护。
   
8. 考虑内存和存储限制： 索引需要内存和存储空间来维护。过多的索引可能会占用大量的资源。在创建索引时要考虑数据库服务器的内存和存储容量。
   

创建联合索引需要权衡多个因素，包括查询模式、写操作频率、数据量和数据库系统的特性。根据实际情况和性能测试，选择合适的列组合和顺序，以提高数据库的查询性能和整体效率。

举例:

有个笔试一道题：一张表，sql 语句是这样,select * from XXwhere sex=x and shengfen=x order by idCard limit 10,最好的建立索引的方式是什么?sex 两个类别，idCare-身份证，shengfeng-省份，如何建立联合索引呢?

首先排序字段不能放在联合索引的左边，因为排序字段后面的字段就没办法走索引了。所以建立联合索引 idCare 不能放在最左边。

然后 shengfen 和 sex，shengfen 的区分度会更高，所以可以把 shengfen 作为最左边的字段，因此(shengfen,sex,idCare)会比较好,这个联合索引索引可以保证三个字段都能走索引。

六.Mysql 锁

1.innodb 中的锁有哪几种?

# 这个查询用于查看当前正在运行的InnoDB事务的信息，包括事务ID、事务状态、锁定等信息。

SELECT * FROM information_schema.INNODB_TRX;

# 这个查询用于查看正在等待锁定的InnoDB事务的信息，包括等待锁的事务ID、等待锁的资源和锁等待时间等。

SELECT * FROM `sys`.`innodb_lock_waits`;

#这个查询用于查看当前正在持有的数据锁定信息，包括锁定的对象、锁定类型和锁定持有者等。

SELECT * FROM performance_schema.data_locks;

#这个查询用于查看正在等待数据锁定的事务信息，包括等待锁定的事务ID、等待锁的资源和锁等待时间等。

SELECT * FROM performance_schema.data_lock_waits;

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

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

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

2.innodb 意向锁?

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

如果需要用到表锁的话，如何判断表中的记录没有行锁呢？一行一行遍历肯定是不行，性能太差。我们需要用到一个叫做意向锁的东西来快速判断是否可以对某个表使用表锁。

意向锁是表级锁，共有两种：

• 意向共享锁（Intention Shared Lock，IS 锁）：事务有意向对表中的某些记录加共享锁（S 锁），加共享锁前必须先取得该表的 IS 锁。
  
• 意向排他锁（Intention Exclusive Lock，IX 锁）：事务有意向对表中的某些记录加排他锁（X 锁），加排他锁之前必须先取得该表的 IX 锁。
  

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

3.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-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住索引本身，而不是范围。

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

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

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;

#sql1

SELECT * FROM z WHERE a=5 LOCK IN SHARE MODE;

#sql2

INSERT INTO z SELECT 4，2;

#sql3

INSERT INTO z SELECT 6，5;

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

SELECT * FROM z WHERE b=3 FOR UPDATE;

很明显，这时 SQL 语句通过索引列 b 进行查询，该列不是唯一属性，因此其使用传统的 Next-Key Locking 技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚簇索引（primay-key a），其仅对列 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)中，故同样需要等待。

4.next-key lock 作用?

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

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 b=3 FOR UPDATE;

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

假设：我们分析下，若此时没有 gap lock（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，插入就会被阻塞，不会出现幻读。

Next-Key Locking解决幻读问题整理：

1. 幻读是连续读取同一个 sql，出现不同的结果；
   
2. a 是主键，b 是辅助索引；
   
3. 则查询 b=3 时，如果不采用临键锁，会读取到多条数据；
   
4. 记录锁针对的是主键唯一的锁定，gap lock 间隙锁是范围锁，左开右开区间，无法阻止 b 等于其他值的插入；
   
5. 如果是临键锁，既可以防止主键的重复，也可以用左闭右闭范围限定的 gap lock 间隙锁限定 b 的值；
   

5.自增长锁?

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

插入类型：

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

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

参数innodb_autoinc_lock_mode 的说明：

6.lock 和 latch 的区别?

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

7.一致性锁定读?

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

• SELECT...FOR UPDATE
  
• SELECT..LOCKIN SHARE MODE
  

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

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

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

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

MVCC 的核心:

• 表的隐藏列：DB_TRX_ID（记录操作当前数据的事务 ID）、DB_ROLL_PTR（记录上个版本数据的地址，指向 undo log）。
  
• undo log：记录数据各版本的修改历史，即“版本链”。
  
• Read View：读视图，用于判断哪些数据版本对当前 SELECT 可见。
  

RC 级别下，事务中每次 SELECT请求都会重新创建read view；

RR 级别下，事务中的第一个 SELECT请求才开始创建read view；

• DB_TRX_ID：记录插入或者更新该行数据的最后一个事务 ID。
  
• DB_ROW_ID：隐藏的自增 ID，当数据库表没有指定主键的时候，会自动生成。
  
• DB_ROLL_PTR：回滚指针，7 字节，指向写入回滚段的 undo log 记录。指向该行的上一个记录。
  

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

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

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

9.MVCC 实现原理

快照读和当前读:

• 快照读:不加锁的非阻塞读，select
  
• 当前读:

• select...lock in share mode;
  
• select...for update
  
• update、insert、delete
  

在事务隔离级别 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 的特性，即隔离性。

MVCC 具体实现过程?

Read View:读视图，某一时刻的一个 trx_id 事务快照

class ReadView {

 // 省略...

private:

 /** 高水位，大于等于这个ID的事务均不可见*/

 trx_id_t  m_low_limit_id;

 /** 低水位：小于这个ID的事务均可见 */

 trx_id_t m_up_limit_id;

 /** 创建该 Read View 的事务ID*/

 trx_id_t m_creator_trx_id;

 /** 创建视图时的活跃事务id列表*/

 ids_t m_ids;

 /** 配合purge，标识该视图不需要小于m_low_limit_no的UNDO LOG，

  * 如果其他视图也不需要，则可以删除小于m_low_limit_no的UNDO LOG*/

 trx_id_t m_low_limit_no;

 /** 标记视图是否被关闭*/

 bool m_closed;

 // 省略...

};

核心属性:

• m_ids：创建 ReadView 时当前系统中活跃的事务 Id 列表，可以理解为生成 ReadView 那一刻还未执行提交的事务，并且该列表是个升序列表。
  
• m_up_limit_id：低水位，取 m_ids 列表的第一个节点，因为 m_ids 是升序列表，因此也就是 m_ids 中事务 Id 最小的那个。
  
• m_low_limit_id：高水位，生成 ReadView 时系统将要分配给下一个事务的 Id 值。
  
• m_creator_trx_id：创建该 ReadView 的事务的事务 Id。
  

可见性：说白了，这个读视图的作用就是为了让我们能够得到当前事务该读到什么版本的数据，它遵循以下的算法：

• 如果被访问版本的 trx_id 与 ReadView 中的 m_creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
  
• 如果被访问版本的 trx_id 小于 ReadView 中的 m_up_limit_id（低水位），表明被访问版本的事务在当前事务生成 ReadView 前已经提交，所以该版本可以被当前事务访问。
  
• 如果被访问版本的 trx_id 大于等于 ReadView 中的 m_low_limit_id（高水位），表明被访问版本的事务在当前事务生成 ReadView 后才开启，所以该版本不可以被当前事务访问。
  
• 如果被访问版本的 trx_id 属性值在 ReadView 的 m_up_limit_id 和 m_low_limit_id 之间，那就需要判断 trx_id 属性值是不是在 m_ids 列表中，这边会通过二分法查找。如果在，说明创建 ReadView 时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建 ReadView 时生成该版本的事务已经被提交，该版本可以被访问。
  

10.什么是丢失更新?

丢失更新是锁导致的问题，简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。例如：

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。

11.什么是脏读?

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

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

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

12.什么是不可重复读?

不可重复读（Unrepeatable read）:

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

13.什么是幻读?

幻读（Phantom read）:幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

14.如何解决幻读问题?

快照读和当前读:

• 快照读:不加锁的非阻塞读，select
  
• 当前读:

• select...lock in share mode;
  
• select...for update
  
• update、insert、delete
  

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

• 快照读 ：由 MVCC 机制来保证不出现幻读。
  
• 当前读 ： 使用 Next-Key Lock 进行加锁来保证不出现幻读，Next-Key Lock 是行锁（Record Lock）和间隙锁（Gap Lock）的结合，行锁只能锁住已经存在的行，为了避免插入新行，需要依赖间隙锁。
  

Next-Key Locking解决幻读问题整理：

1. 幻读是连续读取同一个 sql，出现不同的结果；
   
2. a 是主键，b 是辅助索引；
   
3. 则查询 b=3 时，如果不采用临键锁，会读取到多条数据；
   
4. 记录锁针对的是主键唯一的锁定，gap lock 间隙锁是范围锁，左开右开区间，无法阻止 b 等于其他值的插入；
   
5. 如果是临键锁，既可以防止主键的重复，也可以用左闭右闭范围限定的 gap lock 间隙锁限定 b 的值；
   

15.什么是插入意向锁?

插入意向锁(Insert Intention Locks):我们说一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了 gap 锁( next-key 锁也包含 gap 锁)，如果有的话，插入操作需要等待，直到拥有 gap 锁的那个事务提交。但是”InnoDB 规定事务在等待的时候也需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待。InnoDB 就把这种类型的锁命名为 Insert Intention Locks ，官方的类型名称为: LOCK_INSERT_INTENTION，我们称为插入意向锁。插入意向锁是一种 Gap 锁，不是意向锁，在 insert 操作时产生。

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。该锁用以表示插入意向，当多个事务在同一区间(gap) 插入位置不同的多条数据时，事务之间不需要互相等待。假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的(排他)锁前，都会获取(4， 7)之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突(阻塞等待)。总结来说，插入意向锁的特性可以分成两部分:

• 插入意向锁是一种特殊的间隙锁，间隙锁可以锁定开区间内的部分记录。
  
• 插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身(主键、唯一索引)不冲突，那么事务之间就不会出现冲突等待。
  

注意，虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

插入意向锁的生成时机:每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，那 Insert 语句会被阻塞，并生成一个插入意向锁 。

16.临键锁退化机制?

1.唯一索引的等值查询

• 当查询的记录是存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「记录锁」。
  
• 当查询的记录是不存在的，在用「唯一索引进行等值查询」时，next-key lock 会退化成「间隙锁」。
  

2.唯一索引的范围查询

• next-key lock 不会退化
  

3.非唯一索引等值查询

• 当查询的记录存在时，除了会加 next-key lock 外，还额外加间隙锁，也就是会加两把锁。
  
• 当查询的记录不存在时，只会加 next-key lock，然后会退化为间隙锁，也就是只会加一把锁。
  

4.非唯一索引范围查询

• 普通索引范围查询，next-key lock 不会退化为间隙锁和记录锁。
  

17.如何预防数据库死锁?

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

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

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

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

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

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

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

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

18.Mysql 发生死锁情况

在 MySQL 数据库中，死锁是指两个或多个事务相互等待对方持有的资源，导致它们无法继续执行，并且没有办法通过自动机制解决这种互相等待的情况。这种情况下，MySQL 将选择其中一个事务进行回滚，以解除死锁。

死锁发生的常见情况包括：

1. 同时更新多个资源：如果多个事务同时尝试更新相同的数据行，而且更新操作涉及到对数据行的锁定，可能导致死锁。例如，事务 A 锁定了行 X，同时事务 B 锁定了行 Y，然后它们尝试更新对方所持有的资源，就可能出现死锁情况。
   
2. 交叉依赖：如果事务 A 持有资源 X 并等待资源 Y，而事务 B 持有资源 Y 并等待资源 X，就会发生死锁。这种情况下，两个事务互相依赖对方持有的资源，导致死锁。
   
3. 并发控制不当：如果数据库中的并发控制机制（如锁、事务隔离级别等）配置不当或实现有误，可能导致死锁的发生。
   
4. 索引缺失：如果数据库表没有适当的索引，可能导致某些操作涉及大量行的扫描，增加了死锁的风险。
   
5. 长时间的事务：如果某个事务持有锁的时间过长，而其他事务需要等待该锁，就可能出现死锁。
   

为了避免死锁，可以采取以下措施：

1. 合理设计数据库表的索引，以减少锁冲突和提高查询性能。
   
2. 尽量缩短事务的执行时间，减少事务持有锁的时间。
   
3. 选择合适的事务隔离级别，根据应用的需求进行调整。
   
4. 使用 MySQL 的死锁检测和解决机制，例如设置innodb_deadlock_detect参数为 ON，MySQL 将自动检测并回滚死锁事务。
   
5. 在代码中使用合理的事务管理，确保事务提交或回滚的顺序不会导致死锁。
   

19.mysql 出现死锁

锁等待超时 Lock wait timeout exceeded; try restarting transaction，是当前事务在等待其它事务释放锁资源造成的

查看当前数据库的线程情况：

show full PROCESSLIST;

查看事务表:

再到 INNODB_TRX 事务表中查看，看 trx_mysql_thread_id 是否在 show full processlist 里面的 sleep 线程中（INNODB_TRX 表的 trx_mysql_thread_id 字段对应 show full processlist 中的 Id）；如果在，就说明这个 sleep 的线程事务一直没有 commit 或者 rollback，而是卡住了，需要我们手动删除。

select * from information_schema.innodb_trx;

trx_mysql_thread_id:将找到的 trx_mysql_thread_id 手动删除。

20.全局锁和表级锁?

给 mysql 添加全局锁

Flush tables with read lock;

释放

unlock tables;

表级别的共享锁，也就是读锁；

lock tables t_student read;

表级别的独占锁，也就是写锁；

lock tables t_stuent wirte;

unlock tables;

21.查看加锁信息?

select * from performance_schema.data_locks\G;

通过执行上述 sql，共加了两个锁，分别是：

• 表锁：X 类型的意向锁；
  
• 行锁：X 类型的间隙锁；
  

这里我们重点关注行锁，LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思，通过 LOCK_MODE 可以确认是 next-key 锁，还是间隙锁，还是记录锁：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
  
• 如果 LOCK_MODE 为 X， REC_NOT_GAP，说明是记录锁；
  
• 如果 LOCK_MODE 为 X， GAP，说明是间隙锁；
  

注意！插入意向锁名字里虽然有意向锁这三个字，但是它并不是意向锁，它属于行级锁，是一种特殊的间隙锁。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管「插入意向锁」也属于间隙锁，但两个事务却不能在同一时间内，一个拥有间隙锁，另一个拥有该间隙区间内的插入意向锁（当然，插入意向锁如果不在间隙锁区间内则是可以的）。所以，插入意向锁和间隙锁之间是冲突的。

22.InnoDB 分析表锁定

为了研究行锁，暂时将自动 commit 关闭，set autocommit = 0;

show status like '%innodb_row_lock%';

• Innodb_row_lock_current_waits：当前正在等待锁的数量
  
• Innodb_row_lock_time：等待总时长。从系统启动到现在一共等待的时间
  
• Innodb_row_lock_time_avg：平均等待时长。从系统启动到现在一共等待的时间
  
• Innodb_row_lock_time_max：最大等待时长。从系统启动到现在一共等待的时间
  
• Innodb_row_lock_waits：等待次数。从系统启动到现在一共等待的时间
  

23.for update 的加锁过程

#执行普通的sql语句

select * from t_user where age < 20;

select * from performance_schema.data_locks;

可以看到，输出结果是空，说明普通 select 的查询语句， Innodb 存储引擎不会为事务加任何锁。

当我们对数据库表进行 DML 和 DDL 操作的时候，MySQL 会给这个表加上 MDL 锁，即元数据锁，MDL 锁是 server 层实现的表级锁，适用于所有存储引擎。

• 对一张表进行增删查改操作（DML 操作）的时候，加的是 MDL 读锁；
  
• 对一张表进行表结构变更操作（DDL 操作）的时候，加的是 MDL 写锁；
  

之所以需要 MDL 锁，就是因为事务执行的时候，不能发生表结构的改变，否则就会导致同一个事务中，出现混乱的现象，如果当前有事务持有 MDL 读锁，DDL 操作就不能申请 MDL 写锁，从而保证表元数据的数据一致性。

我们的事物 A 执行了普通 select 查询语句，如果要看该事务持有的 MDL 锁，可以通过这条命令

select * from performance_schema.metadata_locks;

因此，我们常说的普通查询不加锁，其实指的是不加 Innodb 的行级锁，但实际上是需要持有 MDL 锁的。

总结:在执行 select … for update 语句的时候，会有产生 2 个表级别的锁：

• 一个是 Server 层表级别的锁：MDL 锁。事务在进行增删查改的时候，server 层申请 MDL 锁都是 MDL 读锁，而 MDL 读锁之间是相互兼容的，MDL 读锁只会和 MDL 写锁发生冲突，在对表结构进行变更操作的时候，才会申请 MDL 写锁。
  
• 一个是 Inoodb 层表级别的锁：意向锁。事务在进行增删改和锁定读的时候，inoodb 层会申请意向锁，意向锁不会和行级锁发生冲突，而且意向锁之间也不会发生冲突，意向锁只会和共享表锁（lock tables ... read）和独占表锁（lock tables ... write）发生冲突。
  

如果 select … for update 语句的查询条件没有索引字段的话，整张表都无法进行增删改了，从这个现象看，好像是把表锁起来了，但是并不是因为上面这两个表级锁的原因。

而是因为如果锁定读查询语句，没有使用索引列作为查询条件，导致扫描是全表扫描。那么，每一条记录的索引上都会加 next-key 锁（行级锁），这样就相当于锁住的全表，这时如果其他事务对该表进行增、删、改操作的时候，都会被阻塞。

24.加锁规则

加锁规则，包含了两个“原则”、两个“优化”和一个“bug”。

• 原则 1:加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
  
• 原则 2：查找过程中访问到的对象才会加锁。
  
• 优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
  
• 优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
  
• 一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。
  

对于原则 1 说的：加锁的基本单位是 Next-Key 锁，意思是默认都是先加上 Next-Key，之后根据 2 个优化点选择性退化为行锁或间隙锁。

对于原则 2 说的：访问到的对象才会加锁，意思是如果直接索引覆盖到了，不需要回表，那么就不会对聚簇索引加锁。这样的话，其他事务就可以对聚簇索引进行操作，而不会阻塞。

七.Mysql 事务

1.事务的分类?

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

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

2.什么是事务的 ACID?

ACID 属性：MySQL 是一个支持 ACID（原子性、一致性、隔离性、持久性）属性的数据库管理系统。这些属性确保了一致性。具体来说：

• 原子性（Atomicity）：一个事务中的所有操作要么全部执行成功，要么全部失败回滚，不会出现部分执行的情况。
  
• 一致性（Consistency）：事务执行前后数据库必须保持一致状态。
  
• 隔离性（Isolation）：多个事务并发执行时，每个事务看起来好像是在独立的环境中执行，不会相互干扰。
  
• 持久性（Durability）：一旦事务提交，其结果将永久保存在数据库中，即使系统故障也不会丢失。
  

实现方式:

• 原子性:通过事务和 bin log 和 redo log 来保障
  
• 一致性: 通过 undo log 和各种锁和事务来保障
  
• 隔离性: 通过 undo log 和 mvcc 保障
  
• 持久性:通过 redo log 来保障
  

#查看提交模式

SELECT @@AUTOCOMMIT;

#是否开启自动提交

SHOW VARIABLES LIKE 'autocommit';

在 MySQL 中，AUTOCOMMIT是一个系统变量，用于确定是否将每个 SQL 语句作为一个单独的事务自动提交。当AUTOCOMMIT为 1 时，每个 SQL 语句都将作为一个独立的事务自动提交，即执行完即提交。当AUTOCOMMIT为 0 时，需要手动使用COMMIT语句提交事务或使用ROLLBACK语句回滚事务。

3.mysql 事务隔离级别?

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

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

#查看隔离级别

SELECT @@transaction_isolation;

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

};

#设置全局

set global transaction isolation level 隔离级别名称;

#设置session

set session transaction isolation level 隔离级别;

#设置单词

set transaction isolation level 隔离级别;

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

4.RR 和 RC 的区别？

"RR"和"RC"是在数据库中用于描述事务隔离级别的缩写：

1. RR - Repeatable Read（可重复读）： 在 RR 隔离级别下，一个事务在读取数据时会对其加锁，以防止其他事务修改这些数据。这意味着在一个事务内，多次读取同样的数据将得到相同的结果，即使其他事务对数据进行了修改。在 RR 隔离级别下，读取的数据是一致的，但可能导致其他事务的阻塞，因为锁定的数据在事务提交或回滚之前不能被其他事务访问。
   
2. RC - Read Committed（读取已提交）： 在 RC 隔离级别下，一个事务在读取数据时不会对其加锁，因此允许其他事务修改这些数据。在 RC 隔离级别下，读取的数据是已提交的数据，即其他事务已经对其进行了提交。这使得在 RC 隔离级别下，读取的数据可能不是一致的，因为其他事务可以在事务执行期间修改数据。RC 隔离级别通常比 RR 隔离级别具有更高的并发性，因为没有锁定数据的操作。
   

综上所述，RR 隔离级别提供了较高的数据一致性，但可能导致较多的锁冲突和阻塞，从而影响并发性能。而 RC 隔离级别提供了更高的并发性，允许其他事务修改数据，但可能导致读取的数据不一致。选择哪个隔离级别取决于具体的应用需求和对数据一致性和并发性的权衡。在大多数情况下，Read Committed 是一个常见的隔离级别，因为它提供了较好的性能和合理的一致性。

5.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 服务器的

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

分布式事务使用两段式提交（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

6.mysql 自身有的分布式事务?

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

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

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

7.隐藏列

在内部，InnoDB 为存储的每行数据，增加了如下 3 个字段：

1. DB_TRX_ID（6 字节）：称为”事务 ID“，标记 insert 或 update 该行数据的最后一个事务的事务 ID。此外，delete 操作在 InnoDB 内部被视为 update。InnoDB 通过标记行中的特殊位（bit）来表示”已删除“。
   
2. DB_ROLL_PTR（7 字节）：称为”回滚指针（roll pointer）“，用于指向 undo tablespace 中回滚段（rollback segment）的一条 undo log 记录。若数据行被更新，则该指针指向的 undo log 中包含重建更新之前该行数据所需的信息。回滚段中包含 insert undo logs 和 update undo logs。 Insert undo logs 只在事务回滚时需要，一旦事务提交就可以被丢弃（discarded ）。update undo logs 除了事务回滚时需要之外，也用于构建 InnoDB 一致性读。在一致性读中，需要 update undo logs 中的信息来构建早期版本的数据行。
   
3. DB_ROW_ID（6 字节）：称为”行 ID“， 是 MySQL 实例中全局（单个表内，可能不连续）分配的单调递增的值。即作为无主键表的隐式主键。当表中存在仅由 单个整型列 构成的 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，则可在 SELECT 语句中使用 _rowid 来引用索引列的值。即 _rowid 实际为索引列的别名。
   

以上 3 个隐藏字段，可通过 ibd2sdi（MySQL 8.0 开始提供） 工具来查看。

8.显示_rowid

当表中存在仅由 单个整型列 构成的 PRIMARY KEY 或 UNIQUE NOT NULL 索引时，则 _rowid 隐藏列实际为索引列的引用。可在 SELECT 语句中使用 _rowid 来查询索引列的值。这种 _rowid 称为“显式_rowid”。

_rowid 需要表具有的 3 个必备要素：

1. 单列索引

2. 数据类型为整型

3. 索引类型为 `PRIMARY KEY` 或 `UNIQUE NOT NULL` 索引

以下示例中，可以通过 SELECT 语句直接查询的 _rowid，称为 ”显式 _rowid“。

## 1. 单列整型主键的表中，包含隐藏列 _rowid

mysql> CREATE TABLE t_pk(id INT PRIMARY KEY, name VARCHAR(32)) SELECT id, name FROM (VALUES ROW(1,'one'), ROW(2,'two')) AS v(id, name);

mysql> SELECT id, name, _rowid FROM t_pk;

+----+------+--------+

| id | name | _rowid |

+----+------+--------+

|  1 | one  |      1 |

|  2 | two  |      2 |

+----+------+--------+

2 rows in set (0.00 sec)

## 2. 单列整型非空唯一索引的表中，包含隐藏列 _rowid

mysql> CREATE TABLE t_unik(id INT NOT NULL, name VARCHAR(32), UNIQUE KEY(id)) SELECT id, name FROM (VALUES ROW(3,'three'), ROW(4,'four')) AS v(id, name);

mysql> SELECT id, name, _rowid FROM t_unik;

+----+-------+--------+

| id | name  | _rowid |

+----+-------+--------+

|  3 | three |      3 |

|  4 | four  |      4 |

+----+-------+--------+

2 rows in set (0.00 sec)

## 3. 不满足 `单个整型列` 构成的 `主键` 或 `非空唯一索引` 时，表中没有 _rowid 列。

mysql> CREATE TABLE t_c_unik(id VARCHAR(8) NOT NULL, name VARCHAR(32), UNIQUE KEY(id)) SELECT id, name FROM (VALUES ROW('a','three'), ROW('b','four')) AS v(id, name);

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT id, name, _rowid FROM t_c_unik;

ERROR 1054 (42S22): Unknown column '_rowid' in 'field list'

9.隐式_rowid

当表中未设置主键时，InnoDB 会为数据行生成一个 6 字节长度（最大为 $2^{48}$-1，即 281474976710655）的无符号数（dictsys.row_id）作为 _rowid 列的值，只是这个 _rowid 无法显式通过 SELECT 获取。此种情况的 _rowid 称为 ”隐式 _rowid“。此_rowid 也是表的隐式主键。

分配的无符号数由 InnoDB 变量 dictsys.row_id 在 MySQL 实例中全局分配（由所有无主键的表共享）。当自增到最大值 $2^{48}$-1 后，会重新复位从 0 开始。当表中出现相同的 _rowid 时，新插入的数据会根据 _rowid 覆盖掉原有的旧数据。现象类似于根据 _rowid 进行更新覆盖。

安装 gdb 工具，可用于修改全局变量 dictsys.row_id 的值。

• 当 dictsys.row_id = 1，向无主键表插入数据时，InnoDB 将 dictsys.row_id = 1 的值作为数据行的隐式主键，并逐行自增。因此，第一次插入的 3 行数据的 _rowid 值分别为 1、2、3；
  
• 当 dictsys.row_id = 281474976710656（即 $2^{48}$）， 已超过最大值 $2^{48}$-1。InnoDB 在插入数据时将 dictsys.row_id 复位为 0。因此，第二次插入的 3 行数据的 _rowid 值分别为 0、1、2；
  
• 第二次插入的 3 行数据与第一次插入的 3 行数，_rowid 出现了重复值（即 1、2）。于是，InnoDB 在第二次插入数据时，根据 _rowid 的值覆盖了第一次插入的 2 行数据（图中蓝色箭头部分）。
  

10.自增 ID 用完怎么办?

指定了自增主键:

可以在创建表的时候，直接声明 AUTO_INCREMENT 的初始值。4294967295 是 2 的 32 次方减去 1。

具体来说：2^32 - 1 = 4294967296 - 1 = 4294967295

create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967295;

当再次插入时，使用的自增 ID 还是 4294967295，报主键冲突的错误,主键重复了。

insert into t1 values(null);

SQL 错误 [1062] [23000]: Duplicate entry '4294967295' for key 'chatbot_3.PRIMARY'

没有指定主键:

如果是这种情况，InnoDB 会自动帮你创建一个不可见的、长度为 6 字节的 row_id，而且 InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该 row_id，每次插入一条数据，都把全局 row_id 当成主键 id，然后全局 row_id 加 1

该全局 row_id 在代码实现上使用的是 bigint unsigned 类型，但实际上只给 row_id 留了 6 字节，这种设计就会存在一个问题：如果全局 row_id 一直涨，一直涨，直到 2 的 48 幂次-1 时，这个时候再+1，row_id 的低 48 位都为 0，结果在插入新一行数据时，拿到的 row_id 就为 0，存在主键冲突的可能性。

所以，为了避免这种隐患，每个表都需要定一个主键。

11.Update 语句执行过程

UPDATE 语句是用于修改数据库表中已有数据的 SQL 语句。以下是 UPDATE 语句执行的一般过程，具体细节可能根据数据库管理系统（DBMS）的不同而有所不同：

1. 解析 SQL 语句：首先，数据库管理系统会解析 UPDATE 语句，以确定要修改的目标表，要更新的列以及更新条件。解析过程还包括验证表和列的存在以及权限检查，确保用户有权执行这个更新操作。
   
2. 执行 WHERE 条件：如果 UPDATE 语句包含 WHERE 子句，数据库会执行这个子句，以确定哪些行将被更新。只有满足 WHERE 条件的行才会被更新。如果没有指定 WHERE 条件，UPDATE 将会影响表中的所有行，这通常是一个危险的操作，因此要谨慎使用。
   
3. 锁定行：在开始更新之前，数据库通常会锁定满足 WHERE 条件的行，以确保在更新过程中没有其他会话可以修改这些行。这可以防止并发问题，如丢失更新或死锁。
   
4. 执行更新：一旦行被锁定，数据库会执行实际的更新操作。这包括将新数据写入表中，用新值替换旧值。更新可以包括单个列或多个列，具体取决于 UPDATE 语句中的设置。
   
5. 事务处理：更新通常是在事务内执行的，这意味着它们要么全部成功，要么全部失败。如果有任何问题（例如，约束违反、数据类型错误等），事务将回滚，不会更改数据。否则，如果一切正常，事务将提交，数据将永久性地更新。
   
6. 释放锁：在事务完成后，数据库会释放行级锁，允许其他会话访问这些行。
   

总的来说，UPDATE 语句的执行过程包括解析 SQL 语句、确定要更新的行、锁定行以防止并发问题、执行实际的更新操作，然后通过事务来确保操作的原子性和一致性。这些步骤有助于维护数据库的完整性和数据的一致性。不同的数据库管理系统可能会有不同的优化和实现细节，但这是一般情况下的执行过程。

12.for update 的原理

SELECT ... FOR UPDATE 是一种在 SQL 数据库中常见的查询模式，用于锁定一或多行数据以便进行更新。当你执行一个 SELECT ... FOR UPDATE 查询时，数据库将对选中的行加锁，直到事务结束（提交或回滚）才会释放。这样做的目的是为了防止多个事务并发修改相同的数据，从而保证数据的一致性。

工作原理:

假设有一个简单的 users 表：

CREATE TABLE users (

   id INT PRIMARY KEY,

   username VARCHAR(50),

   balance INT

);

现在假设你想要更新某个用户的 balance 字段。在这种情况下，你可能会使用以下查询：

SELECT balance FROM users WHERE id = 1 FOR UPDATE;

执行这个查询后，数据库将锁定 id = 1 的那一行。其他事务如果也试图对这一行进行 FOR UPDATE 查询或更新，将会被阻塞，直到该锁被释放。

然后，在同一事务中，你可以安全地更新这一行：

UPDATE users SET balance = balance - 100 WHERE id = 1;

最后，你可以提交这个事务，从而释放锁并使得其他事务可以访问这一行。

COMMIT;

注意事项:

1. 死锁风险: 如果多个事务试图以不同的顺序锁定多行，可能会出现死锁。解决这个问题的一种方法是总是以相同的顺序锁定行。
   
2. 隔离级别: SELECT ... FOR UPDATE 的行为可能会因数据库的隔离级别而异。在某些隔离级别下，它可能不会如你所预期的那样工作。
   
3. 数据库支持: 并非所有的数据库都支持 SELECT ... FOR UPDATE。对于不支持的数据库，你需要使用其他机制来实现相同的效果。
   
4. 锁的范围: 在一些数据库中，你可以通过 NOWAIT 或其他选项来控制锁的行为。例如，SELECT ... FOR UPDATE NOWAIT 将会立即报错，如果所选行已经被另一个事务锁定。
   
5. 索引: 如果可能，使用索引来加速 SELECT ... FOR UPDATE 查询。如果没有合适的索引，数据库可能需要进行全表扫描，这将对性能产生严重影响。
   
6. 只锁定需要的行: 为了提高性能和减少锁冲突，尽量只锁定你确实需要更新的行。
   

通过合理地使用 SELECT ... FOR UPDATE，你可以在并发环境中更安全地进行数据修改。

八.备份与恢复

1.主从复制的原理?

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

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

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

2.什么是 SBR 和 RBR?

主从复制 binlog 日志有三种记录方式

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

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

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

3.SBR 和 RBR 的优缺点?

SBR 的优点

• 因为 binlog 中只写入了变更操作的 statements，所以日志量将会很小；
  
• 当使用 SQL 语句批量更新、删除数据时，只需要在 binlog 中记录 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 的效率较低；
  

4.主从复制有几种方式?

• 同步复制：MySQL 主库提交事务的线程要等待所有从库的复制成功响应，才返回客户端结果。这种方式在实际项目中，基本上没法用，原因有两个：一是性能很差，因为要复制到所有节点才返回响应；二是可用性也很差，主库和所有从库任何一个数据库出问题，都会影响业务。
  
• 异步复制（默认模型）：MySQL 主库提交事务的线程并不会等待 binlog 同步到各从库，就返回客户端结果。这种模式一旦主库宕机，数据就会发生丢失。
  
• 半同步复制：MySQL 5.7 版本之后增加的一种复制方式，介于两者之间，事务线程不用等待所有的从库复制成功响应，只要一部分复制成功响应回来就行，比如一主二从的集群，只要数据成功复制到任意一个从库上，主库的事务线程就可以返回给客户端。这种半同步复制的方式，兼顾了异步复制和同步复制的优点，即使出现主库宕机，至少还有一个从库有最新的数据，不存在数据丢失的风险。
  

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

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

rpl_semi_sync_master_wait_point=AFTER_SYNC（唯一区别）

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

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

5.主从复制有什么好处?

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

6.主节点自动切换

要让 MySQL 集群自动更换主节点而不被程序感知，可以按照以下步骤进行设置：

1. 配置 MySQL 集群：确保您的 MySQL 集群已正确设置和配置，并且已经正常运行。确保您有一个可用的备用节点。
   
2. 使用虚拟 IP（VIP）：为 MySQL 集群配置一个虚拟 IP（VIP），它将用作主节点的标识。这个 VIP 将会漂移到新的主节点上。
   
3. 监控主节点状态：设置一个监控机制来检测主节点的状态。您可以使用一个脚本或者专门的监控工具来监视主节点是否可用。
   
4. 监控节点之间的心跳：使用一个心跳监控机制，确保集群中的所有节点都能够相互通信。常用的方法是使用软件或硬件心跳。
   
5. 监测主节点的宕机：当监控机制检测到主节点不可用时，它应该触发一系列动作来进行故障转移。
   
6. 故障转移操作：在主节点宕机后，您需要执行一系列操作来进行故障转移，将备用节点升级为新的主节点。
   a. 在备用节点上启动 MySQL 服务，并确保它已经成为新的主节点。
   b. 将 VIP 从原来的主节点迁移到新的主节点上，这样客户端程序就会将请求发送到新的主节点。
   c. 更新集群配置信息，确保所有节点都知道新的主节点位置。
   
7. 更新程序连接信息：在故障转移完成后，您需要更新客户端程序的连接信息，使其连接到新的主节点。这可以通过动态获取主节点位置并更新程序配置来实现，或者使用服务发现机制来自动更新连接信息。
   

通过以上步骤，您可以实现 MySQL 集群的自动主节点切换，而不需要手动干预并且程序可以无感知地连接到新的主节点。请注意，这是一个高级配置，需要一定的专业知识和经验，确保在进行任何更改之前备份您的数据库以防止数据丢失。

7.主从同步延迟原因

MySQL的主从复制都是单线程的操作，主库对所有DDL和DML产生的日志写进binlog，由于binlog是顺序写，所以效率很高。Slave的SQL Thread线程将主库的DDL和DML操作事件在slave中重放。DML和DDL的IO操作是随即的，不是顺序的，成本高很多。另一方面，由于SQL Thread也是单线程的，当主库的并发较高时，产生的 DML 数量超过slave的SQL Thread所能处理的速度，或者当slave中有大型query语句产生了锁等待那么延时就产生了。

常见原因：

• Master 负载过高
  
• Slave 负载过高
  
• 网络延迟
  
• 机器性能太低
  
• MySQL 配置不合理
  

8.主从延时排查方法

通过监控 show slave status 命令输出的Seconds_Behind_Master参数的值来判断：

• NULL，表示 io_thread 或是 sql_thread 有任何一个发生故障；
  
• 0，该值为零，表示主从复制良好；
  
• 正值，表示主从已经出现延时，数字越大表示从库延迟越严重
  

9.解决数据丢失问题

解决数据丢失的问题：

• 半同步复制:从 MySQL5.5 开始，MySQL 已经支持半同步复制了，半同步复制介于异步复制和同步复制之间，主库在执行完事务后不立刻返回结果给客户端，需要等待至少一个从库接收到并写到 relay log 中才返回结果给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一个TCP/IP往返耗时的延迟。
  
• 主库配置sync_binlog=1，innodb_flush_log_at_trx_commit=1 sync_binlog的默认值是 0，MySQL 不会将binlog同步到磁盘，其值表示每写多少binlog同步一次磁盘。innodb_flush_log_at_trx_commit为 1 表示每一次事务提交或事务外的指令都需要把日志 flush 到磁盘。
  

注意:将以上两个值同时设置为 1 时，写入性能会受到一定限制，只有对数据安全性要求很高的场景才建议使用，比如涉及到钱的订单支付业务，而且系统 I/O 能力必须可以支撑！

10.解决从库复制延迟的问题

• 架构方面

1. 业务的持久化层的实现采用分库架构，mysql 服务可平行扩展，分散压力。
   
2. 单个库读写分离，一主多从，主写从读，分散压力。这样从库压力比主库高，保护主库。
   
3. 服务的基础架构在业务和 mysql 之间加入 memcache 或者 redis 的 cache 层。降低 mysql 的读压力。
   
4. 不同业务的 mysql 物理上放在不同机器，分散压力。
   
5. 使用比主库更好的硬件设备作为 slave，mysql 压力小，延迟自然会变小。
   

• 硬件方面

1. 采用好服务器，比如 4u 比 2u 性能明显好，2u 比 1u 性能明显好。
   
2. 存储用 ssd 或者盘阵或者 san，提升随机写的性能。
   
3. 主从间保证处在同一个交换机下面，并且是万兆环境。
   
4. 总结，硬件强劲，延迟自然会变小。一句话，缩小延迟的解决方案就是花钱和花时间。
   

• mysql 主从同步加速

1. sync_binlog在 slave 端设置为 0
   
2. –logs-slave-updates 从服务器从主服务器接收到的更新不记入它的二进制日志。
   
3. 直接禁用slave端的 binlog
   
4. slave端，如果使用的存储引擎是 innodb，innodb_flush_log_at_trx_commit =2
   

• 从文件系统本身属性角度优化master 端修改 linux、Unix 文件系统中文件的 etime 属性， 由于每当读文件时 OS 都会将读取操作发生的时间回写到磁盘上，对于读操作频繁的数据库文件来说这是没必要的，只会增加磁盘系统的负担影响 I/O 性能。可以通过设置文件系统的 mount 属性，组织操作系统写 atime 信息，在 linux 上的操作为：打开/etc/fstab，加上 noatime 参数/dev/sdb1 /data reiserfs noatime 1 2 然后重新 mount 文件系统#mount -oremount /data
  
• 同步参数调整主库是写，对数据安全性较高，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而 slave 则不需要这么高的数据安全，完全可以讲sync_binlog设置为 0 或者关闭 binlog，innodb_flushlog也可以设置为 0 来提高 sql 的执行效率
  

11.mysql 的热备和冷备?

• 冷备份发生在数据库已经正常关闭的情况下，将关键性文件拷贝到另外位置的一种说法
  
• 热备份是在数据库运行的情况下，采用归档方式备份数据的方法
  

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

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

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

12.冷备优缺点?

冷备优点

• 是非常快速的备份方法（只需拷贝文件）
  
• 容易归档（简单拷贝即可）
  
• 容易恢复到某个时间点上（只需将文件再拷贝回去）
  
• 能与归档方法相结合，作数据库“最新状态”的恢复。
  
• 低度维护，高度安全。
  

冷备缺点

• 单独使用时，只能提供到“某一时间点上”的恢复。
  
• 在实施备份的全过程中，数据库必须要作备份而不能作其它工作。也就是说，在冷备份过程中，数据库必须是关闭状态。
  
• 若磁盘空间有限，只能拷贝到磁带等其它外部存储设备上，速度会很慢。
  
• 不能按表或按用户恢复。
  

13.mysql 备份工具?

常用备份工具

• mysql 复制
  
• 逻辑备份(mysqldump，mydumper)
  
• 物理备份(copy，xtrabackup)
  

备份工具差异对比

• 1.mysql 复制相对于其他的备份来说，得到的备份数据比较实时。
  
• 2.逻辑备份：分表比较容易。

• mysqldump 备份数据时是将所有 sql 语句整合在同一个文件中；
  
• mydumper 备份数据时是将 SQL 语句按照表拆分成单个的 sql 文件，
  
• 每个 sql 文件对应一个完整的表。
  

• 3.物理备份：拷贝即可用，速度快。

• copy:直接拷贝文件到数据目录下，可能引起表损坏或者数据不一致。
  
• xtrabackup 对于 innodb 表是不需要锁表的，对于 myisam 表仍然需要锁表。
  

14.Canal 原理

主从复制步骤：

将 Master 的 binary-log 日志文件打开，mysql 会把所有的 DDL，DML，TCL 写入 BinaryLog 日志文件中 Master 会生成一个 log dump 线程，用来给从库的 i/o 线程传 binlog 从库的 i/o 线程去请求主库的 binlog，并将得到的 binlog 日志写到中继日志（relaylog）中从库的 sql 线程，会读取 relaylog 文件中的日志，并解析成具体操作，通过主从的操作一致，而达到最终数据一致，而 Canal 的原理就是伪装成 Slave 从 Binlog 中复制 SQL 语句或者数据。

Canal 作为 MySQL binlog 增量获取和解析工具，可将变更记录投递到 MQ 系统中，比如 Kafka/RocketMQ，可以借助于 MQ 的多语言能力，因此我们可以使用下面这种方案来同步数据。

15.Mysql 三种集群模式

MySQL 集群主要有三种模式：

• 主从复制
  
• 多主复制
  
• Galera Cluster
  

主从复制:

主从复制是 MySQL 集群的最基本形式，主要思想是将一台 MySQL 服务器定义为主服务器，另外一台或多台 MySQL 服务器定义为从服务器。主服务器上的数据修改会同步到从服务器上，从服务器只能读取数据，不具备写入的能力。主从复制的主要原理是通过 binlog 来实现，binlog 是 MySQL 数据库的二进制日志，记录了所有的 SQL 语句以及数据变化的情况，从服务器通过读取主服务器上的 binlog 进行数据同步。

主从复制的使用场景主要是读写分离或者备份。当主服务器承担写入操作时，从服务器可以负责读取操作，从而实现负载均衡；同时，从服务器可以作为备份数据库，主服务器出现故障时可以通过从服务器来恢复数据。

多主复制:

多主复制和主从复制不同的是，多主复制中每个节点都可以进行读写操作，同时节点之间的数据同步也是相互的。当一个节点修改数据后，相应的变化会自动同步到其它节点中。多主复制的主要原理是通过对每个节点的 binlog 进行多点复制，实现数据同步。

多主复制主要的使用场景是业务分区。将不同的业务模块分配给不同的节点，每个节点可以独立地进行数据修改，同时数据也会自动同步到其他节点中。

Galera Cluster:

Galera Cluster 是一种基于 MySQL InnoDB 存储引擎的集群解决方案，是一种完全同步的多主复制技术。它与多主复制最大的不同是采用了同步复制的方式，保证了数据修改的可靠性，同时支持自动分片。

Galera Cluster 的主要实现原理是采用了一种称为”Wating Commit”的机制，当一个数据变化时，该数据不仅要向主节点同步，还要向集群中其它节点同时进行同步，直到所有节点的数据完全一致，提交操作才算成功。

由于 Galera Cluster 中没有单点故障，因此该模式被广泛应用于高可用性的业务场景。同时，全局序列号机制和自动分片功能也使得 Galera Cluster 在处理大规模数据时表现出色。

MySQL 集群的三种模式各具特点，可以根据不同的业务需求进行选择。如果需要进行读写分离或备份操作，主从复制是不错的选择；如果需要进行业务分区，多主复制可以选用；如果需要高可用性和自动分片功能，那么 Galera Cluster 是不二之选。

16.数据迁移方案

数据迁移执行流程，大概有 10 个步骤：

1. 环境准备：线上库配置完成
   
2. 全量同步：数据迁移工具上新建 2 张表（积分表、明细表）的全量任务
   
3. 增量同步：全量迁移完成后开启增量(自动回溯全量开始时间，消息多次消费会进行幂等)
   
4. 数据校验：全量数据校验，查看数据是否一致
   
5. 切流测试：改造代码预发测试（采集线上流量进行回放，多种 case 跑一下，切流开关等校验），没问题发布上线
   
6. 二次校验：再次全量进行校验&订正（数据追平）
   
7. 开启双写：打开双写（保证数据实时性）既写老库，又写新库
   
8. 开启读灰度：低峰时段，进行灰度切流userId%x，进行验证，逐步流量打开，持续观察
   
9. 只写新库：写流量切到新库，只写新库，不写老库。完成数据迁移方案
   
10. 迁移完成：系统稳定运行一段时间，迁移&双写代码下线，老库进行资源释放
    

九.性能调优

1.单表优化经验?

• 最佳左前缀法则：带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死。
  
• like 百分加右边，加左边导致索引失效，解决方法：使用覆盖索引。
  
• 尽量使用覆盖索引，减少 select *
  
• 使用枚举或整数代替字符串类型
  
• VARCHAR 的长度只分配真正需要的空间
  
• 字符串不加单引号导致索引失效
  
• 避免使用 NULL字段，很难查询优化且占用额外索引空间,可以给 NULL 设置默认值
  
• 用整型来存 IP
  
• 不做列运算 SELECT id WHERE age+1=10，任何对列的操作都将导致表扫描-
  
• 对于连续数值，使用 BETWEEN 不用 IN:SELECT id FROM t WHERE nUm BETWEEN 1 AND 5
  
• 列表数据不要拿全表，要使用 LIMIT 来分页，每页数量也不要太大-
  
• OR 改写成 IN:OR 的效率是 n 级别，IN 的效率是 log(N)级别 in 的个数建议控制在 200 以内
  
• 尽量使用 TINYINT、SMALLINT、MEDIUM INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
  

2.SQL 优化流程

1. 预发跑 sql explain
   
2. 看一下行数对不对 不对可以用 analyze table t 矫正
   
3. 添加索引 索引不一定是最优的 force index 强制走索引 不建议用 关注 type 类型
   
4. 覆盖索引避免回表，不要*
   
5. 最左前缀原则 按照索引定义的字段顺序写 sql
   
6. 合理安排联合索引的顺序
   
7. 5.6 之后 索引下推 减少回表次数
   
8. 索引字段不要做函数操作，会破坏索引值的有序性，优化器会放弃走树结构
   
9. 如果触发隐式转换 那也会走 cast 函数 会放弃走索引
   
10. 可通过开启慢查询日志来找出较慢的 SQL
    

3.Mysql 在代码层的优化?

在业务代码中进行 MySQL 优化是提高数据库性能和响应时间的关键步骤之一。以下是一些您可以在业务代码中实施的 MySQL 优化方案：

1. 合理的查询设计：

• 使用适当的索引：确保数据库表上的关键字段有合适的索引，以加快查询速度。
  
• 避免全表扫描：确保查询语句能够充分利用索引，以避免全表扫描和性能问题。
  

2. 批量操作：

• 批量插入、更新和删除：在需要插入、更新或删除多行数据时，使用批量操作，减少单个操作的开销。
  
• 批量提交事务：将多个数据库操作放在一个事务中，然后进行批量提交，以减少事务的开销。
  

3. 分页查询优化：

• 使用分页查询时，避免跳过大量行，可以根据分页条件和索引进行优化。
  
• 考虑使用游标（Cursor）来处理大量数据的分页查询，而不是一次性取出全部数据。
  

4. 减少网络开销：

• 限制返回数据量：只选择所需的字段，避免不必要的数据传输。
  
• 避免重复查询：在一个请求内对同一数据进行多次查询，可以考虑合并为一个查询。
  

5. 事务管理：

• 控制事务的范围：尽量缩小事务的范围，以减少锁定的时间和冲突。
  
• 避免长事务：长时间的事务可能导致锁定问题和资源争用，需要谨慎处理。
  

6. 缓存：

• 缓存常用数据：使用缓存存储频繁访问的数据，减少对数据库的查询压力。
  

7. 连接管理：

• 使用连接池：确保使用连接池来管理数据库连接，避免频繁地创建和关闭连接。
  

8. 异步处理：

• 将一些不需要立即完成的操作异步处理，以减少前端请求的等待时间。
  

9. 避免 N+1 查询问题：

• 在一对多关系中，避免在循环中执行多次查询，可以使用 JOIN 或者批量查询。
  

10. 数据库分片：

• 对于大规模应用，考虑数据库分片以分散负载，但需要在业务代码中处理分片逻辑。
  

11. 使用 Explain 分析：

• 使用 MySQL 的EXPLAIN语句来分析查询的执行计划，以识别潜在的性能问题。
  

12. 定期维护：

• 定期清理无用数据、优化表结构、重新建立索引，以保持数据库的良好性能。
  

MySQL 优化是一个综合性的工作，需要根据具体的业务情况和性能问题来确定优化策略。持续的监控和性能测试可以帮助您了解优化的效果，并做出必要的调整。

4.深分页和浅分页

例如您的订单表数据量达到一定程度，例如 50 万条以上时，如果还是使用常规分页查询（例如：limit 470000，10 这样的查询）（一般页面端的分页控件有最后一页，如果点击这个按钮就会重现），查询速度会非常慢，会长达几秒或十几秒才能返回结果，这就是深分页问题。

MySQL 在执行 limit n，m 时，工作原理就是先读取前面 n 条记录，然后抛弃前 n 条，读后面 m 条想要的，所以 n 越大，偏移量越大，性能就越差。

5.带排序的分页查询优化

1. 浅分页可以给 order by 字段添加索引
   
2. 深分页可以给 order by 和 select 字段添加联合索引
   
3. 可以通过手动回表，强制去走索引
   
4. 从业务方着手，去限制他的分页查询或者修改前后端交互（将每页最后一条数据的 id 和分数传递过来）
   

分页查询

SELECT id FROM t LIMIT 10000， 10;

改为:

SELECT id FROM t WHERE id > 10000 LIMIT 10;

6.表分区优化?

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

分区的类型:

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

不论创建何种类型的分区，如果表中存在主键或唯一索引时，分区列必须是唯一索引的一个组成部分.如果建表时没有指定主键，唯一索引，可以指定任何一个列为分区列。分区中对 NULL 值的处理为，放在最左边，相当于最小。

7.使用函数一定影响性能?

EXPLAIN

SELECT MAX(sales)

FROM sales_amount_02;

#Extra字段显示为Select tables optimized away

表示 innodb 已经在系统表中把 max 存储起来了，不用走索引再查，所以使用函数不一定对性能有坏的影响。

8.count(*)和 count(1)性能?

count(\*)和 count(1)比较：

• myisam 中有表直接存储，汇总 count 的值会存储起来。
  
• 官方文档，在 innodb 存储引擎中，count(1)与 count(*)性能是一样的
  
• innodb: count(*) = count(1) > count(主键) > count(col)
  
• myisam: count(*) >= count(1) > count(col)
  

9.小表驱动大表?

类似循环嵌套，小的在外层，大的在内层。如果小的循环在外层，对于数据库连接来说就只连接 5 次，进行 5000 次操作，如果 1000 在外，则需要进行 1000 次数据库连接，从而浪费资源，增加消耗。这就是为什么要小表驱动大表。

in 和 exists 的原理也是基于小表驱动大表。优化原则：小表驱动大表，即小的数据集驱动大的数据集。

#当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from A where id in (select id from B)

等价于：

for select id from B

for select * from A where A.id = B.id

#当A表的数据集系小于B表的数据集时，用exists优于in。

select * from A where exists (select 1 from B where B.id = A.id)

等价于

for select * from A

for select * from B where B.id = A.id

10.Show Profile

Show Profile 是 mysql 提供的可以用来分析当前会话中 sql 语句执行的资源消耗情况的工具，可用于 sql 调优的测量。默认情况下处于关闭状态，并保存最近 15 次的运行结果。

开启 Show Profile 功能，默认该功能是关闭的，使用前需开启。

select *from tb_emp_bigdata group by id%10 limit 150000;

select *from tb_emp_bigdata group by id%20 order by 5;

-- 使用show profile对sql语句进行诊断。

-- Query_ID为#3步骤中show profiles列表中的Query_ID

show profile cpu，block io for query Query_ID;

show profile 的常用查询参数。

• ①ALL：显示所有的开销信息。
  
• ②BLOCK IO：显示块 IO 开销。
  
• ③CONTEXT SWITCHES：上下文切换开销。
  
• ④CPU：显示 CPU 开销信息。
  
• ⑤IPC：显示发送和接收开销信息。
  
• ⑥MEMORY：显示内存开销信息。
  
• ⑦PAGE FAULTS：显示页面错误开销信息。
  
• ⑧SOURCE：显示和 Source_function，Source_file，Source_line 相关的开销信息。
  
• ⑨SWAPS：显示交换次数开销信息。
  

日常开发需注意的结论。

• ①converting HEAP to MyISAM：查询结果太大，内存不够，数据往磁盘上搬了。
  
• ②Creating tmp table：创建临时表。先拷贝数据到临时表，用完后再删除临时表。
  
• ③Copying to tmp table on disk：把内存中临时表复制到磁盘上，危险！！！
  
• ④locked。
  

如果在 show profile 诊断结果中出现了以上 4 条结果中的任何一条，则 sql 语句需要优化。

11.什么是 idel conn?

在 MySQL 中，"idel conn"是指空闲连接（idle connections），也被称为闲置连接或空闲会话。这是指已经建立的与 MySQL 数据库服务器的连接，但目前没有进行任何数据库操作或查询的连接。

当应用程序与 MySQL 数据库建立连接后，连接会一直保持打开状态，即使在一段时间内没有进行任何数据库操作。这种情况下，连接就会被认为是空闲连接。

空闲连接可能会对数据库服务器的性能产生负面影响，因为每个连接都会占用一定的资源，包括内存和处理器资源。如果有大量的空闲连接积累，它们将占用宝贵的资源，并可能导致数据库性能下降。

为了管理空闲连接，通常会采取以下措施：

1. 连接池管理：使用连接池来管理数据库连接，连接池可以在需要时分配连接，并在连接不再使用时将其放回池中。这样可以避免频繁地打开和关闭连接，提高性能并减少空闲连接的数量。
   
2. 超时设置：通过设置连接的超时时间，可以确保空闲连接在一段时间内没有被使用后自动关闭。这样可以释放资源并减少空闲连接的数量。
   
3. 监控和优化：定期监控数据库服务器的连接数和性能指标，识别并优化可能导致空闲连接过多的瓶颈或性能问题。
   

通过合理管理和优化空闲连接，可以提高数据库服务器的性能和资源利用率，并确保连接数与实际需求相匹配，从而提供更好的数据库服务。

12.索引负优化

SELECT * from student s where age < 17 and name ='zhangsan12' and create_time < '2023-01-17 10:23:08' order by age LIMIT 2

在MySQL中LIMIT与ORDER BY是特殊的组合，尤其是当ORDER BY中的存在BTREE索引的情况下。

普通的查询是根据条件进行筛选，然后在结果集中排序，然后获取LIMIT条数的数据，但是在具备上述条件的特殊 sql 中执行逻辑是这样的，根据ORDER BY字段的 B+树索引来查找满足条件的数据，直到凑满LIMIT设定的数值为止，这就存在一个问题，在结果集中的数据大于LIMIT的场景下，这个性能固然是非常棒的，但是如果最后的结果集中的数据小于LIMIT，就会存在永远凑不满的情况，所以最终这个MySQL的性能优化就会变成全表扫描的“负优化”。

13.内存低但 cpu 高的原因?

内存使用比较低，cpu使用比较高:

MySQL 产生较高 CPU 负载可能是由于大量查询请求的产生导致的，一些复杂的查询语句可能会导致 SQL 优化失败，导致服务器需要耗费较长时间处理查询请求，从而导致 CPU 占用率较高。因此，对查询语句进行优化是减少 MySQL CPU 占用率的首要措施。

不恰当的MySQL配置也可能导致 CPU 占用率的上升，例如将缓存池设得过小，每次查询都要从硬盘读取数据，这样就容易导致CPU占用率增加。而如果将缓存池设得太大，虽然查询速度会更快，但也会增加内存负载，产生其他问题。

14.如何存储 IP 地址？

可以将 IP 地址转换成整形数据存储，性能更好，占用空间也更小。

MySQL 提供了两个方法来处理 ip 地址

• INET_ATON() ： 把 ip 转为无符号整型 (4-8 位)
  
• INET_NTOA() :把整型的 ip 转为地址
  

插入数据前，先用 INET_ATON() 把 ip 地址转为整型，显示数据时，使用 INET_NTOA() 把整型的 ip 地址转为地址显示即可。

15.对身份证做前缀索引?

1. 前面 6 位是区号，重复的概率是很大的，因此如果要在做前缀索引的话，至少是要 12 位的。但是这样的浪费了太多空间，一个数据页存放的数据就变少了。
   
2. 存储身份证的时候倒过来，这样后 6 位的区分度就很高了。
   
3. 数据类型选择：身份证号码是一个固定长度的字符串，通常为 18 位，所以可以选择适当的数据类型来存储。推荐使用 CHAR(18)或 VARCHAR(18)来存储身份证号码。
   
4. 索引：如果身份证号码是用于查询和检索的字段，可以为该字段创建索引。索引可以大大提高查询效率，尤其是在大数据量的情况下。
   
5. 唯一性约束：考虑将身份证号码设为唯一性约束，这样可以确保数据库中不会存在重复的身份证号码。
   
6. 数据校验：在应用层对输入的身份证号码进行校验，确保只有合法的身份证号码才能进入数据库。这可以防止无效或错误的数据污染数据库。
   
7. 数据加密：如果安全性要求较高，可以考虑对身份证号码进行加密存储。这样即使数据库被未经授权的访问，也不会泄露敏感信息。
   
8. 数据分区：如果数据库中的数据量很大，可以考虑将数据分区。将身份证号码的相关数据放在单独的分区中，可以提高查询效率和维护性。
   
9. 压缩存储：如果身份证号码的数据量很大，可以考虑使用压缩算法来存储数据，减少数据库占用空间。
   
10. 有限长度字段：如果你知道身份证号码的前几位是有规律的，可以将其拆分为两个字段，一个存储前几位，一个存储后几位，这样可以节省空间并提高查询效率。
    

16.批量插入优化?

问 MySQL 多条记录插入，不准拼接成一条记录，不准工具 orm 啥的批量插入，让设计方案?

使用 Java 中的 PreparedStatement 和 addBatch 方法批量插入多条记录到 MySQL 数据库，而不使用拼接 SQL 字符串，可以按照以下步骤进行操作：

1. 创建连接和 PreparedStatement： 首先，创建与数据库的连接并准备一个 PreparedStatement 对象，该对象包含插入语句，并使用占位符代替实际的值。
   

Connection connection = DriverManager.getConnection("jdbc:mysql://your_database_url", "username", "password");

String insertQuery = "INSERT INTO your_table_name (column1, column2, column3) VALUES (?, ?, ?)";

PreparedStatement preparedStatement = connection.prepareStatement(insertQuery);

2. 为多条记录设置参数并添加到批处理： 然后，为每条记录设置参数并将它们添加到 PreparedStatement 的批处理中，而不是执行单独的 SQL 语句。
   

preparedStatement.setString(1, value1_1);

preparedStatement.setString(2, value1_2);

preparedStatement.setString(3, value1_3);

preparedStatement.addBatch();

preparedStatement.setString(1, value2_1);

preparedStatement.setString(2, value2_2);

preparedStatement.setString(3, value2_3);

preparedStatement.addBatch();

// 添加更多记录...

3. 执行批处理： 一旦您添加了所有记录到批处理中，就可以执行批处理操作。
   

preparedStatement.executeBatch();

4. 关闭资源： 最后，记得关闭 PreparedStatement 和数据库连接。
   

preparedStatement.close();

connection.close();

这种方法允许您批量插入多条记录，而不必手动拼接 SQL 字符串，从而提高了性能和安全性。此外，使用占位符可以防止 SQL 注入攻击。

十.数据库缓存一致性

1.情况总结

• 两种更新情况 更新缓存的顺序是不确定的
  
• 先删缓存 读到错误数据插入缓存
  
• 后删缓存 删除了正确的数据
  
• 延迟双删 删除缓存两次
  

2.先更新数据库，再更新缓存

并发场景下，会出现缓存和数据库不一致的情况,请求 A 更新数据库为 1,这时还未更新缓存,请求 B 更新了数据库为 2,并且更新了缓存为 2,然后请求 A 更新了缓存为 1,导致数据库为 2,缓存为 1,数据库和缓存不一致。

3.先更新缓存，再更新数据库

请求 A 更新缓存为 1,这时还未更新数据库,请求 B 更新了缓存为 2,并且更新了数据库为 2,然后请求 A 更新了数据库为 1,导致缓存为 2,数据库为 1,数据库和缓存不一致。

4.先删除缓存，再更新数据库

请求 A 更新的时候先删除缓存,还未更新数据库,此时,请求 B 查询缓存,未查到数据,请求 B 查询数据库,然后更新缓存为 20,然后请求 A 更新数据库为 21,导致缓存和数据库不一致。这种情况是因为更新数据和读数据导致的缓存和数据库一致性问题。前面提到的两种都是覆盖导致的问题。

5.先更新数据库，再删除缓存

请求 A 读取数据时,缓存没有命中,读取数据库的值为 20。此时请求 B 是更新操作,先更新了数据库为 21,然后删除了缓存。然后请求 A 接着操作,写缓存将 20 的数据写入到缓存中,导致数据库与缓存数据不一致。

在更新数据时，不更新缓存，而是删除缓存中的数据。然后，到读取数据时，发现缓存中没了数据之后，再从数据库中读取数据，更新到缓存中。Cache Aside 策略，中文是叫旁路缓存策略。这种策略出现问题的概率最小,因为一般情况下读是比写要快很多的。

从上面的理论上分析，先更新数据库，再删除缓存也是会出现数据不一致性的问题，但是在实际中，这个问题出现的概率并不高。

因为缓存的写入通常要远远快于数据库的写入，所以在实际中很难出现请求 B 已经更新了数据库并且删除了缓存，请求 A 才更新完缓存的情况。

所以，「先更新数据库 + 再删除缓存」的方案，是可以保证数据一致性的.

如何保证 2 个消息成功呢?

• 「消息队列来重试缓存的删除」
  
• 「订阅 MySQL binlog 再操作缓存」
  

6.延迟双删

#删除缓存

redis.delKey(X)

#更新数据库

db.update(X)

#睡眠

Thread.sleep(N)

#再删除缓存

redis.delKey(X)

加了个睡眠时间，主要是为了确保请求 A 在睡眠的时候，请求 B 能够在这这一段时间完成「从数据库读取数据，再把缺失的缓存写入缓存」的操作，然后请求 A 睡眠完，再删除缓存。所以，请求 A 的睡眠时间就需要大于请求 B 「从数据库读取数据 + 写入缓存」的时间。

但是具体睡眠多久其实是个玄学，很难评估出来，所以这个方案也只是尽可能保证一致性而已，极端情况下，依然也会出现缓存不一致的现象。因此，还是比较建议用「先更新数据库，再删除缓存」的方案。