MySQL篇

1、数据库的三范式是什么

第一范式：列不可再分

第二范式：行可以唯一区分，主键约束

第三范式：表的非主属性不能依赖与其他表的非主属性外键约束且三大范式是一级一级依赖的，第二范式建立在第一范式上，第三范式建立第一第二范式上。

2、MySQL 数据库引擎有哪些

如何查看 MySQL 提供的所有存储引擎

1	mysql> show engines;

MySQL 常用引擎包括：MYISAM、Innodb、Memory、MERGE

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert 为主的应用基本上可以使用这引擎
Innodb：行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM 的 2.5 倍，处理效率相对会差一些
Memory：全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在 MySQL 重启时会丢失，默认使用 HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表
MERGE：是一组 MYISAM 表的组合

3、说说 InnoDB 与 MyISAM 的区别

在 MySQL 5.5 及之前的版本中，MyISAM 是默认的存储引擎，而在 MySQL 5.5 版本以后，默认使用 InnoDB 存储引擎。

InnoDB 支持事务，MyISAM 不支持，对于 InnoDB 每一条 SQL 语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条 SQL 语言放在 begin 和 commit 之间，组成一个事务；InnoDB 需要更多存储空间，会在内存中建立其专用的缓冲池用于高速缓冲数据和索引。InnoDB 支持自动奔溃恢复特性。
InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
InnoDB 是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。而 MyISAM 用一个变量保存了整个表的行数，MyISAM 不支持行级锁，执行上述语句时只需要读出该变量即可，速度很快；
Innodb 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；

InnoDB 和 MyISAM 是使用 MySQL 时最常用的两种引擎类型，我们重点来看下两者区别。

事务和外键
InnoDB 支持事务和外键，具有安全性和完整性，适合大量 insert 或 update 操作
MyISAM 不支持事务和外键，它提供高速存储和检索，适合大量的 select 查询操作
锁机制
InnoDB 支持行级锁，锁定指定记录。基于索引来加锁实现。
MyISAM 支持表级锁，锁定整张表。
索引结构
InnoDB 使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录。
MyISAM 使用非聚集索引（非聚簇索引），索引和记录分开。
并发处理能力
MyISAM 使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞。
InnoDB 读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发
存储文件
InnoDB 表对应两个文件，一个.frm 表结构文件，一个.ibd 数据文件。InnoDB 表最大支持 64 TB；
MyISAM 表对应三个文件，一个.frm 表结构文件，一个 MYD 表数据文件，一个 .MYI 索引文件。从 MySQL5.0 开始默认限制是256 TB。

MyISAM 适用场景

不需要事务支持（不支持）
并发相对较低（锁定机制问题）
数据修改相对较少，以读为主
数据一致性要求不高

InnoDB 适用场景

需要事务支持（具有较好的事务特性）
行级锁定对高并发有很好的适应能力
数据更新较为频繁的场景
数据一致性要求较高
硬件设备内存较大，可以利用 InnoDB 较好的缓存能力来提高内存利用率，减少磁盘 IO

两种引擎该如何选择？

是否需要事务？有，InnoDB
是否存在并发修改？有，InnoDB
是否追求快速查询，且数据修改少？是，MyISAM
在绝大多数情况下，推荐使用 InnoDB

扩展资料：各个存储引擎特性对比

4、数据库的事务

什么是事务？： 多条 sql 语句，要么全部成功，要么全部失败。

事务的特性：

数据库事务特性：原子性（Atomic）、一致性（Consistency）、隔离性（Isolation）、持久性（Durabiliy）。简称 ACID。

原子性：组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有操作都成功，整个事务才会提交。任何一个操作失败，已经执行的任何操作都必须撤销，让数据库返回初始状态。
一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。如 A 转账 100 元给 B，不管操作是否成功，A 和 B 的账户总额是不变的。
隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对彼此产生干扰
持久性：一旦事务提交成功，事务中的所有操作都必须持久化到数据库中。

5、索引是什么

官方介绍索引是帮助 MySQL **高效获取数据 **的 数据结构。更通俗的说，数据库索引好比是一本书前面的目录，能 加快数据库的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此 索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用 B+ 树结构组织（多路搜索树，并不一定是二叉的）的索引。

6、SQL 优化手段有哪些

1、查询语句中不要使用 select *

2、尽量减少子查询，使用关联查询（left join，right join，inner join）替代

3、减少使用 IN 或者 NOT IN，使用 exists，not exists 或者关联查询语句替代

4、or 的查询尽量用 union 或者 union all 代替（在确认没有重复数据或者不用剔除重复数据时，union all 会更好）

5、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描。

6、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询： select id from t where num = 0

7、简单说一说 drop、delete 与 truncate 的区别

SQL 中的 drop、delete、truncate 都表示删除，但是三者有一些差别

delete 和 truncate 只删除表的数据不删除表的结构速度，一般来说：drop > truncate > delete delete 语句是 dml，这个操作会放到rollback segement 中,事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。 truncate，drop 是 ddl，操作立即生效，原数据不放到 rollback segment中，不能回滚操作不触发 trigger。

8、什么是视图

视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

9、什么是内联接、左外联接、右外联接？

内联接（Inner Join）：匹配2张表中相关联的记录。
左外联接（Left Outer Join）：除了匹配2张表中相关联的记录外，还会匹配左表中剩余的记录，右表中未匹配到的字段用 NULL 表示。
右外联接（Right Outer Join）：除了匹配 2 张表中相关联的记录外，还会匹配右表中剩余的记录，左表中未匹配到的字段用 NULL 表示。在判定左表和右表时，要根据表名出现在 Outer Join 的左右位置关系。

10、并发事务带来哪些问题？

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

脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。例如：事务 1 读取某表中的数据 A = 20，事务 2 也读取 A = 20，事务 1 修改 A = A - 1，事务 2 也修改 A = A - 1，最终结果 A = 19，事务 1 的修改被丢失。
不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

11、事务隔离级别有哪些？MySQL 的默认隔离级别是？

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

READ-UNCOMMITTED（读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
READ-COMMITTED（读取已提交）： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ（可重复读）： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
SERIALIZABLE（可串行化）： 最高的隔离级别，完全服从 ACID 的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。

隔离级别	脏读	不可重复读	幻影读
READ-UNCOMMITTED	√	√	√
READ-COMMITTED	×	√	√
REPEATABLE-READ	×	×	√
SERIALIZABLE	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 。我们可以通过 SELECT @@tx_isolation；命令来查看

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是 Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统（如 SQL Server）是不同的。所以说 InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLEREAD（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL 标准的 SERIALIZABLE（可串行化）隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED（读取提交内容） ，但是你要知道的是 InnoDB 存储引擎默认使用(REPEATABLE-READ（可重读） 并不会有任何性能损失。

InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE（可串行化） 隔离级别。

12、大表如何优化？

当 MySQL 单表记录数过大时，数据库的 CRUD 性能会明显下降，一些常见的优化措施如下：

1. 限定数据的范围

务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读 / 写分离

经典的数据库拆分方案，主库负责写，从库负责读；

3. 垂直分区

根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下所示，这样来说大家应该就更容易理解了。

参考链接：https://github.com/gsjqwyl/JavaInterview

垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的 Block 数，减少 I / O 次数。此外，垂直分区可以简化表的结构，易于维护。
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起 Join 操作，可以通过在应用层进行 Join 来解决。此外，垂直分区会让事务变得更加复杂；

4. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过 200 万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。

水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升 MySQL 并发能力没有什么意义，所以 水平拆分最好分库 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点 Join 性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络 I/O。

下面补充一下数据库分片的两种常见方案：

客户端代理：分片逻辑在应用端，封装在 jar 包中，通过修改或者封装 JDBC 层来实现。 当当网的 Sharding-JDBC 、阿里的 TDD L是两种比较常用的实现。
中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360 的 Atlas、网易的DDB 等等都是这种架构的实现。

详细内容可以参考： MySQL 大表优化方案: https://segmentfault.com/a/1190000006158186 【值得细看】

13、分库分表之后， id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id来支持。

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

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
Twitter 的 snowflake 算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
美团的 Leaf 分布式 ID 生成系统 ：Leaf 是美团开源的分布式 ID 生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper 等中间件。感觉还不错。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。

14、说说在 MySQL 中一条查询 SQL 是如何执行的？

比如下面这条 SQL 语句：

1	select name from t_user where id=1

取得链接，使用到 MySQL 中的连接器。
查询缓存，key 为 SQL 语句，value 为查询结果，如果查到就直接返回。不建议使用次缓存，在 MySQL 8.0 版本已经将查询缓存删除，也就是说 MySQL 8.0 版本后不存在此功能。
分析器，分为词法分析和语法分析。此阶段只是做一些 SQL 解析，语法校验。所以一般语法错误在此阶段。
优化器，是在表里有多个索引的时候，决定使用哪个索引；或者一个语句中存在多表关联的时候（join），决定各个表的连接顺序。
执行器，通过分析器让 SQL 知道你要干啥，通过优化器知道该怎么做，于是开始执行语句。执行语句的时候还要判断是否具备此权限，没有权限就直接返回提示没有权限的错误；有权限则打开表，根据表的引擎定义，去使用这个引擎提供的接口，获取这个表的第一行，判断 id 是都等于 1。如果是，直接返回；如果不是继续调用引擎接口去下一行，重复相同的判断，直到取到这个表的最后一行，最后返回。

15、索引有什么优缺点？

16、MySQL 中 varchar 与 char 的区别？ varchar(30) 中的 30 代表的涵义？

varchar 与 char 的区别，char 是一种固定长度的类型，varchar 则是一种可变长度的类型。
CHAR 和 VARCHAR 类型在存储和检索方面有所不同

CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255；当 CHAR 值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。
varchar(30) 中 30 的涵义最多存放 30 个字符。varchar(30) 和 (130) 存储 hello 所占空间一样，但后者在排序时会消耗更多内存，因为 ORDER BY col 采用 fixed_length 计算 col 长度（memory 引擎也一样）。
对效率要求高用 char，对空间使用要求高用 varchar。

17、int(11) 中的 11 代表什么涵义？

int(11) 中的 11，不影响字段存储的范围，只影响展示效果。

18、为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢？

对于 SELECT COUNT( ) FROM table 语句，在没有 WHERE 条件的情况下，InnoDB 比 MyISAM 可能会慢很多，尤其在大表的情况下。因为，InnoDB 是去实时统计结果，会 全表扫描；而 MyISAM 内部维持了一个计数器，预存了结果，所以直接返回即可。

19、MySQL索引类型有哪些？

主键索引

索引列中的值必须是唯一的，不允许有空值。

普通索引

MySQL 中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

唯一索引

索引列中的值必须是唯一的，但是允许为空值。

全文索引

只能在文本类型 CHAR，VARCHAR，TEXT 类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行 like 模糊查询时效率比较低，这时可以创建全文索引。MyISAM 和 InnoDB 中都可以使用全文索引。

空间索引

MySQL 在 5.7 之后的版本支持了空间索引，而且支持 OpenGIS 几何数据模型。MySQL 在空间索引这方面遵循 OpenGIS 几何数据模型规则。

前缀索引

在文本类型如 CHAR，VARCHAR，TEXT 类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。其他（按照索引列数量分类）

单列索引
组合索引

组合索引的使用，需要遵循 最左前缀匹配原则（最左匹配原则） 。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

20、什么时候不要使用索引？

经常增删改的列不要建立索引；
有大量重复的列不建立索引；
表记录太少不要建立索引。

21、说说什么是 MVCC？

多版本并发控制（MVCC=Multi-Version Concurrency Control），是一种用来解决读 - 写冲突的无锁并发控制。也就是为事务分配单向增长的时间戳，为每个修改保存一个版本。版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

MVCC 可以为数据库解决什么问题？

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

说说 MVCC 的实现原理

MVCC 的目的就是多版本并发控制，在数据库中的实现，就是为了解决读写冲突，它的实现原理主要是依赖记录中的 3 个隐式字段、undo 日志、Read View 来实现的。

三个隐式字段：

DB_ROW_ID：隐含的自增 ID（隐藏主键）

DB_TRX_ID：记录最近修改这条记录的事务 ID

DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本

22、请说说 MySQL 数据库的锁？

关于 MySQL 的锁机制，可能会问很多问题，不过这也得看面试官在这方面的知识储备。

MySQL 中有共享锁和排它锁，也就是读锁和写锁。

共享锁：不堵塞，多个用户可以同一时刻读取同一个资源，相互之间没有影响。
排它锁：一个写操作阻塞其他的读锁和写锁，这样可以只允许一个用户进行写入，防止其他用户读取正在写入的资源。
表锁：系统开销最小，会锁定整张表，MyISAM 使用表锁。
行锁：容易出现死锁，发生冲突概率低，并发高，InnoDB 支持行锁（必须有索引才能实现，否则会自动锁全表，那么就不是行锁了）。

23、说说什么是锁升级？

MySQL 行锁只能加在索引上，如果操作不走索引，就会升级为表锁。因为 InnoDB 的行锁是加在索引上的，如果不走索引，自然就没法使用行锁了，原因是 InnoDB 是将 primary key index 和相关的行数据共同放在 B+ 树的叶节点。InnoDB 一定会有一个 primary key，secondary index 查找的时候，也是通过找到对应的 primary，再找对应的数据行。

当非唯一索引上记录数超过一定数量时，行锁也会升级为表锁。测试发现 当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时，索引需要的性能比全文检索还要大，查询语句优化时会选择不走索引，造成索引失效，行锁自然就会升级为表锁。

24、说说悲观锁和乐观锁

悲观锁

说的是数据库被外界（包括本系统当前的其他事物以及来自外部系统的事务处理）修改保持着保守态度，因此在整个数据修改过程中，将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制，也只有数据库层面提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统汇总实现了加锁机制，也是没有办法保证系统不会修改数据。

在悲观锁的情况下，为了保证事务的隔离性，就需要一致性锁定读。读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁

相对悲观锁而言，乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。

而乐观锁机制在一定程度上解决了这个问题。乐观锁，大多是基于数据版本（Version）记录机制实现。何谓数据版本？即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。

25、怎样尽量避免死锁的出现？

设置获取锁的超时时间，至少能保证最差情况下，可以退出程序，不至于一直等待导致死锁；
设置按照同一顺序访问资源，类似于串行执行；
避免事务中的用户交叉；
保持事务简短并在一个批处理中；
使用低隔离级别；
使用绑定链接。

26、使用 MySQL 的索引应该注意些什么？

27、主键和候选键有什么区别？

表格的每一行都由主键唯一标识，一个表只有一个主键。主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。

28、主键与索引有什么区别？

主键一 定会创建一个唯一索引，但是有唯一索引的列不一定是主键；

主键不允许为空值，唯一索引列允许空值；

一个表只能有一个主键，但是可以有多个唯一索引；

主键可以被 其他表引用为外键，唯一索引列不可以；

主键是一种约束，而唯一索引是一种索引，是表的冗余数据结构

29、MySQL 如何做到高可用方案？

MySQL 高可用，意味着不能一台 MySQL 出了问题，就不能访问了。

MySQL 高可用：分库分表，通过 MyCat 连接多个 MySQL
MyCat 也得高可用：Haproxy，连接多个 MyCat
Haproxy 也得高可用：通过 keepalived 辅助 Haproxy

30、什么是 BufferPool？

Buffer Pool 基本概念

Buffer Pool：缓冲池，简称 BP。其作用是用来缓存表数据与索引数据，减少磁盘 IO 操作，提升效率。

Buffer Pool由 缓存数据页(Page) 和对缓存数据页进行描述的 控制块 组成, 控制块中存储着对应缓存页的所属的表空间、数据页的编号、以及对应缓存页在 Buffer Pool 中的地址等信息.

Buffer Pool 默认大小是 128 M, 以 Page 页为单位，Page 页默认大小 16K，而控制块的大小约为数据页的 5%，大概是 800 字节。

注: Buffer Pool 大小为 128 M 指的就是缓存页的大小，控制块则一般占 5 %，所以每次会多申请 6 M 的内存空间用于存放控制块

如何判断一个页是否在 BufferPool 中缓存 ?

MySQL 中有一个哈希表数据结构，它使用表空间号 + 数据页号，作为一个 key，然后缓冲页对应的控制块作为 value。

当需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看是否存在对应的缓冲页。
如果有，则直接使用；如果没有，就从free链表中选出一个空闲的缓冲页，然后把磁盘中对应的页加载到该缓冲页的位置

31、InnoDB 如何管理 Page 页？

Page页分类

BP 的底层采用链表数据结构管理 Page。在 InnoDB 访问表记录和索引时会在 Page 页中缓存，以后使用可以减少磁盘 IO 操作，提升效率。

Page 根据状态可以分为三种类型：

free page ：空闲 page，未被使用
clean page：被使用 page，数据没有被修改过
dirty page：脏页，被使用 page，数据被修改过，Page 页中数据和磁盘的数据产生了不一致

Page 页如何管理

针对上面所说的三种 page 类型，InnoDB 通过三种链表结构来维护和管理

free list：表示空闲缓冲区，管理 free page

free 链表是把所有空闲的缓冲页对应的控制块作为一个个的节点放到一个链表中，这个链表便称之为 free 链表
基节点: free 链表中只有一个基节点是不记录缓存页信息（单独申请空间），它里面就存放了 free 链表的头节点的地址，尾节点的地址，还有 free 链表里当前有多少个节点。

2.flush list：表示需要刷新到磁盘的缓冲区，管理 dirty page，内部 page 按修改时间排序。

InnoDB 引擎为了提高处理效率，在每次修改缓冲页后，并不是立刻把修改刷新到磁盘上，而是在未来的某个时间点进行刷新操作. 所以需要使用到 flush 链表存储脏页，凡是被修改过的缓冲页对应的控制块都会作为节点加入到 flush 链表.
flush 链表的结构与 free 链表的结构相似

3.lru list：表示正在使用的缓冲区，管理 clean page 和 dirty page，缓冲区以 midpoint 为基点，前面链表称为 new 列表区，存放经常访问的数据，占 63 %；后面的链表称为 old 列表区，存放使用较少数据，占 37 %

32、为什么写缓冲区，仅适用于非唯一普通索引页？

change Buffer 基本概念

Change Buffer：写缓冲区，是针对二级索引（辅助索引）页的更新优化措施。

作用: 在进行 DML 操作时，如果请求的辅助索引（二级索引）没有在缓冲池中时，并不会立刻将磁盘页加载到缓冲池，而是在 CB 记录缓冲变更，等未来数据被读取时，再将数据合并恢复到 BP 中。

ChangeBuffer 用于存储 SQL 变更操作，比如 Insert/Update/Delete 等 SQL 语句
ChangeBuffer 中的每个变更操作都有其对应的数据页，并且该数据页未加载到缓存中；
当 ChangeBuffer 中变更操作对应的数据页加载到缓存中后，InnoDB 会把变更操作 Merge 到数据页上；
InnoDB 会定期加载 ChangeBuffer 中操作对应的数据页到缓存中，并 Merge 变更操作；

change buffer更新流程

写缓冲区，仅适用于非唯一普通索引页，为什么？

如果在索引设置唯一性，在进行修改时，InnoDB 必须要做唯一性校验，因此必须查询磁盘，做一次 IO 操作。会直接将记录查询到BufferPool 中，然后在缓冲池修改，不会在 ChangeBuffer 操作。

33、MySQL 为什么改进 LRU 算法？

普通 LRU 算法

LRU = Least Recently Used（最近最少使用）：就是末尾淘汰法，新数据从链表头部加入，释放空间时从末尾淘汰.

当要访问某个页时，如果不在 Buffer Pool，需要把该页加载到缓冲池,并且把该缓冲页对应的控制块作为节点添加到 LRU 链表的头部。
当要访问某个页时，如果在 Buffer Pool 中，则直接把该页对应的控制块移动到 LRU 链表的头部
当需要释放空间时，从最末尾淘汰

普通 LRU 链表的优缺点

优点

所有最近使用的数据都在链表表头，最近未使用的数据都在链表表尾，保证热数据能最快被获取到。

缺点

如果发生全表扫描（比如：没有建立合适的索引 or 查询时使用 select * 等），则有很大可能将真正的热数据淘汰掉.
由于 MySQL 中存在预读机制，很多预读的页都会被放到 LRU 链表的表头。如果这些预读的页都没有用到的话，这样，会导致很多尾部的缓冲页很快就会被淘汰。

改进型 LRU 算法

改进型 LRU：将链表分为 new 和 old 两个部分，加入元素时并不是从表头插入，而是从中间 midpoint 位置插入（就是说从磁盘中新读出的数据会放在冷数据区的头部），如果数据很快被访问，那么 page 就会向 new 列表头部移动，如果数据没有被访问，会逐步向 old 尾部移动，等待淘汰。

冷数据区的数据页什么时候会被转到到热数据区呢 ?

如果该数据页在 LRU 链表中存在时间超过 1 s，就将其移动到链表头部 ( 链表指的是整个 LRU 链表)
如果该数据页在 LRU 链表中存在的时间短于 1 s，其位置不变（由于全表扫描有一个特点，就是它对某个页的频繁访问总耗时会很短）
1 s 这个时间是由参数 innodb_old_blocks_time 控制的

34、使用索引一定可以提升效率吗？

索引就是排好序的,帮助我们进行快速查找的数据结构.

简单来讲，索引就是一种将数据库中的记录按照特殊形式存储的数据结构。通过索引，能够显著地提高数据查询的效率，从而提升服务器的性能.

索引的优势与劣势

优点
- 提高数据检索的效率，降低数据库的 IO 成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了 CPU 的消耗
缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度
创建索引的原则
- 在经常需要搜索的列上创建索引，可以加快搜索的速度；
- 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构；
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

35、介绍一下 Page 页的结构？

Page 是整个 InnoDB 存储的最基本构件，也是 InnoDB 磁盘管理的最小单位，与数据库相关的所有内容都存储在这种 Page 结构里。

Page 分为几种类型，常见的页类型有数据页（B+tree Node）Undo 页（Undo Log Page）系统页（System Page）事务数据页（Transaction System Page）等

Page 各部分说明

名称	占用大小	说明
File Header	38字节	文件头, 描述页信息
Page Header	56字节	页头,页的状态
Infimum + Supremum	26字节	最大和最小记录,这是两个虚拟的行记录
User Records	不确定	用户记录,存储数据行记录
Free Space	不确定	空闲空间,页中还没有被使用的空间
Page Directory	不确定	页目录,存储用户记录的相对位置
File Trailer	8字节	文件尾,校验页是否完整

File Header 字段用于记录 Page 的头信息，其中比较重要的是 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段，通过这两个字段，我们可以找到该页的上一页和下一页，实际上所有页通过两个字段可以形成一条双向链表
Page Header 字段用于记录 Page 的状态信息。
Infimum 和 Supremum 是两个伪行记录，Infimum（下确界）记录比该页中任何主键值都要小的值，Supremum （上确界）记录比该页中任何主键值都要大的值，这个伪记录分别构成了页中记录的边界。
User Records 中存放的是实际的数据行记录
Free Space 中存放的是空闲空间，被删除的行记录会被记录成空闲空间
Page Directory 记录着与二叉查找相关的信息
File Trailer 存储用于检测数据完整性的校验和等数据。

页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分。

通用部分 (File Header&File Trailer )

通用部分：主要指文件头和文件尾，将页的内容进行封装，通过文件头和文件尾校验的 CheckSum 方式来确保页的传输是完整的。

其中比较重要的是在文件头中的 FIL_PAGE_PREV 和 FIL_PAGE_NEXT 字段，通过这两个字段，我们可以找到该页的上一页和下一页，实际上所有页通过两个字段可以形成一条双向链表

记录部分(User Records&Free Space)

页的主要作用是存储记录，所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间。另外空闲空间是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录

3)数据目录部分 (Page Directory)

数据页中行记录按照主键值由小到大顺序串联成一个单链表(页中记录是以单向链表的形式进行存储的)，且单链表的链表头为最小记录，链表尾为最大记录。并且为了更快速地定位到指定的行记录，通过 Page Directory实现目录的功能，借助 Page Directory使用二分法快速找到需要查找的行记录。

36、说一下聚簇索引与非聚簇索引？

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录

聚簇索引：将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据.
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置.

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。

在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

聚簇索引（聚集索引）

聚簇索引是一种数据存储方式，InnoDB 的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
InnoDB 的表要求 必须要有聚簇索引：
- 如果表定义了主键，则主键索引就是聚簇索引
- 如果表没有定义主键，则第一个非空 unique 列作为聚簇索引
- 否则 InnoDB 会从建一个隐藏的 row-id 作为聚簇索引
辅助索引
InnoDB 辅助索引，也叫作二级索引，是根据索引列构建 B+Tree 结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个表 InnoDB 只能创建一个聚簇索引，但可以创建多个辅助索引。

非聚簇索引

与 InnoDB 表存储不同，MyISM 使用的是非聚簇索引，非聚簇索引的两棵 B+ 树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引 B+ 树的节点存储了主键，辅助键索引 B+ 树存储了辅助键。

表数据存储在独立的地方，这两颗 B+ 树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

聚簇索引的优点

当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。
当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址,即多一次I/O。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引的缺点

插入速度严重依赖于插入顺序。
更新主键的代价很高，因为将会导致被更新的行移动。
二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

37、索引有哪几种类型？

1）普通索引

这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。

1
2
3

CREATE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) );

2）唯一索引

与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值。

1
2
3

CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名) ;

3）主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。

1 2	CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) ); ALTER TABLE tablename ADD PRIMARY KEY (字段名);

4）复合索引

用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。

CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);

ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);

CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

复合索引使用注意事项：
- 何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
- 如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

5) 全文索引

查询操作在数据量比较少时，可以使用 like 模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比 like快很多倍。

在 MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引，从 MySQL 5.6 开始 MyISAM 和 InnoDB 存储引擎均支持。

CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);

ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);

CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

全文索引方式有自然语言检索 IN NATURAL LANGUAGE MODE和布尔检索 IN BOOLEAN MODE两种和常用的 like 模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aabb');

-- * 表示通配符,只能在词的后面
SELECT * FROM users3 WHERE MATCH(NAME) AGAINST('aa*'  IN BOOLEAN MODE);

全文索引使用注意事项：

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

38、介绍一下最佳左前缀法则？

1)最佳左前缀法则

最佳左前缀法则: 如果创建的是联合索引，就要遵循该法则。使用索引时，where 后面的条件需要从索引的最左前列开始使用，并且不能跳过索引中的列使用。

场景1: 按照索引字段顺序使用，三个字段都使用了索引,没有问题。

1 2	EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17 AND user_level = 'A';

场景2: 直接跳过 user_name 使用索引字段，索引无效，未使用到索引。
1
EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';
场景3: 不按照创建联合索引的顺序,使用索引
1
2
EXPLAIN SELECT * FROM users WHERE
user_age = 17 AND user_name = 'tom' AND user_level = 'A';
where后面查询条件顺序是 user_age、user_level、user_name与我们创建的索引顺序 user_name、user_age、user_level不一致，为什么还是使用了索引，原因是因为MySql底层优化器对其进行了优化。
最佳左前缀底层原理
MySQL创建联合索引的规则是: 首先会对联合索引最左边的字段进行排序( 例子中是 user_name ), 在第一个字段的基础之上再对第二个字段进行排序 ( 例子中是 user_age ) .
最佳左前缀原则其实是和 B+ 树的结构有关系，最左字段肯定是有序的，第二个字段则是无序的(联合索引的排序方式是：先按照第一个字段进行排序，如果第一个字段相等再根据第二个字段排序)。所以如果直接使用第二个字段 user_age 通常是使用不到索引的。

39、什么是索引下推？

索引下推（index condition pushdown ）简称 ICP，在 Mysql 5.6 的版本上推出，用于优化查询。

需求：查询 users 表中 “名字第一个字是张，年龄为 10 岁的所有记录”。

1	SELECT * FROM users WHERE user_name LIKE '张%' AND user_age = 10;

根据最左前缀法则，该语句在搜索索引树的时候，只能匹配到名字第一个字是‘张’的记录，接下来是怎么处理的呢？当然就是从该记录开始，逐个回表，到主键索引上找出相应的记录，再比对 age 这个字段的值是否符合。

图1: 在 (name,age) 索引里面特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次

MySQL 5.6 引入了索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

图2: InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过,减少回表次数.

总结

如果没有索引下推优化（或称 ICP 优化），当进行索引查询时，首先根据索引来查找记录，然后再根据 where 条件来过滤记录；

在支持 ICP 优化后，MySQL 会在取出索引的同时，判断是否可以进行 where 条件过滤再进行索引查询，也就是说提前执行 where 的部分过滤操作，在某些场景下，可以大大减少回表次数，从而提升整体性能。

40、什么是自适应哈希索引？

自适应 Hash 索引（Adatptive Hash Index，内部简称 AHI）是 InnoDB 的三大特性之一，还有两个是 Buffer Pool 简称 BP、双写缓冲区（Doublewrite Buffer）。

1、自适应即我们不需要自己处理，当 InnoDB 引擎根据查询统计发现某一查询满足 hash 索引的数据结构特点，就会给其建立一个 hash索引；

2、hash 索引底层的数据结构是散列表（Hash 表），其数据特点就是比较适合在内存中使用，自适应 Hash 索引存在于 InnoDB 架构中的缓存中（不存在于磁盘架构中），见下面的 InnoDB 架构图。

3、自适应 hash 索引只适合搜索等值的查询，如 select * from table where index_col=’xxx’，而对于其他查找类型，如范围查找，是不能使用的；

Adaptive Hash Index 是针对 B+ 树 Search Path 的优化，因此所有会涉及到 Search Path 的操作，均可使用此 Hash 索引进行优化.

根据索引键值(前缀)快速定位到叶子节点满足条件记录的 Offset，减少了 B+ 树 Search Path 的代价，将 B+ 树从 Root 节点至 Leaf 节点的路径定位，优化为 Hash Index 的快速查询。

InnoDB 的自适应 Hash 索引是默认开启的，可以通过配置下面的参数设置进行关闭。

1	innodb_adaptive_hash_index = off

自适应 Hash 索引使用分片进行实现的，分片数可以使用配置参数设置：

1	innodb_adaptive_hash_index_parts = 8

41、为什么 LIKE 以 % 开头索引会失效？

like 查询为范围查询，% 出现在左边，则索引失效。% 出现在右边索引未失效.

场景1: 两边都有 % 或者字段左边有 %,索引都会失效。

1
2
3

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';

EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';

场景2: 字段右边有 %,索引生效

1	EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';

解决 % 出现在左边索引失效的方法，使用覆盖索引<创建包含所有查询列如：user_name,user_age,user_level 的所索引>

1
2
3

EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';

EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';

对比场景 1 可以知道, 通过使用覆盖索引 type = index,并且 extra = Using index，从全表扫描变成了全索引扫描.

like 失效的原因

%号在右: 由于 B+ 树的索引顺序，是按照首字母的大小进行排序，% 号在右的匹配又是匹配首字母。所以可以在 B+ 树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引.
%号在左: 是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引.
两个 %% 号: 这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

42、自增还是 UUID？数据库主键的类型该如何选择？

auto_increment 的优点：

字段长度较 uuid 小很多，可以是 bigint 甚至是 int 类型，这对检索的性能会有所影响。
在写的方面，因为是自增的，所以主键是趋势自增的，也就是说新增的数据永远在后面，这点对于性能有很大的提升。
数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利。
数字型，占用空间小，易排序，在程序中传递也方便。

auto_increment 的缺点：

由于是自增，很容易通过网络爬虫知晓当前系统的业务量。
高并发的情况下，竞争自增锁会降低数据库的吞吐能力。
数据迁移或分库分表场景下，自增方式不再适用。

UUID 的优点：

不会冲突。进行数据拆分、合并存储的时候，能保证主键全局的唯一性
可以在应用层生成，提高数据库吞吐能力

UUID 的缺点：

影响插入速度，并且造成硬盘使用率低。与自增相比，最大的缺陷就是随机io，下面我们会去具体解释
字符串类型相比整数类型肯定更消耗空间，而且会比整数类型操作慢。

uuid 和自增 id 的索引结构对比

1、使用自增 id 的内部结构

自增的主键的值是顺序的，所以 InnoDB 把每一条记录都存储在一条记录的后面。

当达到页面的最大填充因子时候（InnoDB 默认的最大填充因子是页大小的 15/16，会留出 1/16 的空间留作以后的修改）。
下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。
新插入的行一定会在原有的最大数据行下一行，MySQL 定位和寻址很快，不会为计算新行的位置而做出额外的消耗。减少了页分裂和碎片的产生。

2、使用 uuid 的索引内部结构

插入UUID：新的记录可能会插入之前记录的中间，因此需要移动之前的记录

被写满已经刷新到磁盘上的页可能会被重新读取

因为 uuid 相对顺序的自增 id 来说是毫无规律可言的，新行的值不一定要比之前的主键的值要大，所以 innodb 无法做到总是把新行插入到索引的最后，而是需要为新行寻找新的合适的位置从而来分配新的空间。

这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

写入的目标页很可能已经刷新到磁盘上并且从缓存上移除，或者还没有被加载到缓存中，innodb 在插入之前不得不先找到并从磁盘读取目标页到内存中，这将导致大量的随机 IO。
因为写入是乱序的，innodb 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改三个页以上。
由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。
在把随机值（uuid 和雪花 id）载入到聚簇索引（InnoDB 默认的索引类型）以后，有时候会需要做一次 OPTIMEIZE TABLE 来重建表并优化页的填充，这将又需要一定的时间消耗。

结论：使用 InnoDB 应该尽可能的按主键的自增顺序插入，并且尽可能使用单调的增加的聚簇键的值来插入新行。如果是分库分表场景下，分布式主键 ID 的生成方案优先选择雪花算法生成全局唯一主键（雪花算法生成的主键在一定程度上是有序的）。

43、B 树和 B+ 树的区别是什么？

1）B-Tree 介绍

B-Tree 是一种平衡的多路查找树，B 树允许一个节点存放多个数据。这样可以在尽可能减少树的深度的同时，存放更多的数据(把瘦高的树变的矮胖).

B-Tree 中所有节点的子树个数的最大值称为 B-Tree 的阶，用 m 表示。一颗 m 阶的 B 树，如果不为空，就必须满足以下条件。

m 阶的 B-Tree 满足以下条件:

每个节点最多拥有 m-1 个关键字(根节点除外)，也就是 m 个子树
根节点至少有两个子树(可以没有子树,有就必须是两个)
分支节点至少有（m/2）颗子树 (除去根节点和叶子节点其他都是分支节点)
所有叶子节点都在同一层,并且以升序排序

什么是 B-Tree 的阶 ?
所有节点中，节点【60,70,90】拥有的子节点数目最多，四个子节点（灰色节点），所以上面的 B-Tree 为 4 阶 B 树。

B-Tree 结构存储索引的特点

为了描述 B-Tree 首先定义一条记录为一个键值对[key, data] ，key 为记录的键值，对应表中的主键值(聚簇索引)，data 为一行记录中除主键外的数据。对于不同的记录，key值互不相同

索引值和 data 数据分布在整棵树结构中
白色块部分是指针，存储着子节点的地址信息。
每个节点可以存放多个索引值及对应的 data 数据
树节点中的多个索引值从左到右升序排列

B-Tree 的查找操作

B-Tree 的每个节点的元素可以视为一次 I/O 读取，树的高度表示最多的 I/O 次数，在相同数量的总元素个数下，每个节点的元素个数越多，高度越低，查询所需的 I/O 次数越少.

B-Tree 总结

优点: B 树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在靠近根节点的位置将大大提高热点数据的查询效率。
缺点: B 树中每个节点不仅包含数据的 key 值，还有 data 数据.。所以当 data 数据较大时，会导致每个节点存储的 key 值减少，并且导致 B 树的层数变高。增加查询时的 IO 次数。
使用场景: B 树主要应用于文件系统以及部分数据库索引，如 MongoDB，大部分关系型数据库索引则是使用 B+ 树实现

2）B+Tree

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。

B+Tree 的特征

非叶子节点只存储键值信息.
所有叶子节点之间都有一个链指针.
数据记录都存放在叶子节点中.

B+Tree 的优势

B+Tree 是 B Tree 的变种，B Tree 能解决的问题，B+Tree 也能够解决（降低树的高度，增大节点存储数据量）
B+Tree 扫库和扫表能力更强，如果我们要根据索引去进行数据表的扫描，对 B Tree 进行扫描，需要把整棵树遍历一遍，而 B+Tree只需要遍历他的所有叶子节点即可（叶子节点之间有引用）。
B+Tree 磁盘读写能力更强，他的根节点和支节点不保存数据区，所有根节点和支节点同样大小的情况下，保存的关键字要比 B Tree要多。而叶子节点不保存子节点引用。所以，B+Tree 读写一次磁盘加载的关键字比 B Tree 更多。
B+Tree 排序能力更强，如上面的图中可以看出，B+Tree 天然具有排序功能。
B+Tree 查询效率更加稳定，每次查询数据，查询 IO 次数一定是稳定的。当然这个每个人的理解都不同，因为在 B Tree 如果根节点命中直接返回，确实效率更高。

44、一个 B+ 树中大概能存放多少条索引记录？

MySQL 设计者将一个 B+Tree 的节点的大小设置为等于一个页. (这样做的目的是每个节点只需要一次 I/O 就可以完全载入)， InnoDB 的一个页的大小是 16 KB，所以每个节点的大小也是 16KB, 并且 B+Tree 的根节点是保存在内存中的，子节点才是存储在磁盘上。

假设一个 B+ 树高为 2，即存在一个根节点和若干个叶子节点，那么这棵 B+ 树的存放总记录数为：

根节点指针数单个叶子节点记录行数.

计算根节点指针数: 假设表的主键为 INT 类型,占用的就是 4 个字节,或者是 BIGINT 占用 8 个字节, 指针大小为 6 个字节,那么一个页(就是 B+Tree 中的一个节点) ,大概可以存储: 16384B / (4B+6B) = 1638 ,一个节点最多可以存储 1638 个索引指针.
计算每个叶子节点的记录数:我们假设一行记录的数据大小为1k,那么一页就可以存储16行数据,16KB / 1KB = 16.
一颗高度为 2 的 B+Tree 可以存放的记录数为: 1638 * 16=26208 条数据记录, 同样的原理可以推算出一个高度 3 的 B+Tree 可以存放: 1638 * 1638 * 16 = 42928704 条这样的记录.

所以 InnoDB 中的 B+Tree 高度一般为 1-3 层,就可以满足千万级别的数据存储,在查找数据时一次页的查找代表一次 IO，所以通过主键索引查询通常只需要 1-3 次 IO 操作即可查找到数据。

45、explain 用过吗，有哪些主要字段？

使用 EXPLAIN 关键字可以模拟优化器来执行SQL查询语句，从而知道 MySQL 是如何处理我们的 SQL 语句的。分析出查询语句或是表结构的性能瓶颈。

MySQL 查询过程

通过 explain 我们可以获得以下信息：

表的读取顺序
数据读取操作的操作类型
哪些索引可以被使用
哪些索引真正被使用
表的直接引用
每张表的有多少行被优化器查询了

Explain使用方式: explain+sql 语句, 通过执行 explain 可以获得 sql 语句执行的相关信息

1	explain select * from users;

46、type 字段中有哪些常见的值？

type 字段在 MySQL 官网文档描述如下：

The join type. For descriptions of the different types.

type 字段显示的是连接类型 ( join type 表示的是用什么样的方式来获取数据)，它描述了找到所需数据所使用的扫描方式, 是较为重要的一个指标。

下面给出各种连接类型,按照从最佳类型到最坏类型进行排序:

-- 完整的连接类型比较多
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

-- 简化之后,我们可以只关注一下几种
system > const > eq_ref > ref > range > index > ALL

一般来说,需要保证查询至少达到 range 级别,最好能到 ref,否则就要就行 SQL 的优化调整

下面介绍 type 字段不同值表示的含义:

type类型	解释
system	不进行磁盘 IO,查询系统表,仅仅返回一条数据
const	查找主键索引,最多返回1条或0条数据. 属于精确查找
eq_ref	查找唯一性索引,返回数据最多一条, 属于精确查找
ref	查找非唯一性索引,返回匹配某一条件的多条数据,属于精确查找,数据返回可能是多条.
range	查找某个索引的部分索引,只检索给定范围的行,属于范围查找. 比如: > 、 < 、in 、between
index	查找所有索引树,比ALL快一些,因为索引文件要比数据文件小.
ALL	不使用任何索引,直接进行全表扫描

47、Extra 有哪些主要指标，各自的含义是什么？

Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示 MySQL 在查询过程中的一些详细信息

extra类型	解释
Using filesort	MySQL中无法利用索引完成的排序操作称为 “文件排序”
Using index	表示直接访问索引就能够获取到所需要的数据（覆盖索引），不需要通过索引回表
Using index condition	搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。
Using join buffer	使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using where	意味着全表扫描或者在查找使用索引的情况下，但是还有查询条件不在索引字段当中

48、如何进行分页查询优化？

一般性分页

一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：
1
SELECT * FROM 表名 LIMIT [offset,] rows
- 第一个参数指定第一个返回记录行的偏移量，注意从0开始；
- 第二个参数指定返回记录行的最大数目；
- 如果只给定一个参数，它表示返回最大的记录行数目；
思考1：如果偏移量固定，返回记录量对执行时间有什么影响？
1
2
3
4
5
select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;
结果：在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。

思考2：如果查询偏移量变化，返回记录数固定对执行时间有什么影响？
1
2
3
4
5
select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100;
结果：在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）

分页优化方案

优化1: 通过索引进行分页

直接进行 limit 操作会产生全表扫描，速度很慢. Limit 限制的是从结果集的 M 位置处取出 N 条输出，其余抛弃.

假设 ID 是连续递增的，我们根据查询的页数和查询的记录数可以算出查询的 id 的范围，然后配合 limit 使用

1	EXPLAIN SELECT * FROM user WHERE id >= 100001 LIMIT 100;

优化2：利用子查询优化

-- 首先定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;

-- 根据获取到的id值向后查询.
EXPLAIN SELECT * FROM user_contacts WHERE id >=
(SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

原因：使用了 id 做主键比较 (id>=)，并且子查询使用了覆盖索引进行优化。

49、如何做慢查询优化？

MySQL 慢查询的相关参数解释：

slow_query_log：是否开启慢查询日志，ON(1)表示开启,
OFF(0) 表示关闭。
slow-query-log-file：新版（5.6 及以上版本）MySQL 数据库慢查询日志存储路径。
long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

慢查询配置方式

默认情况下 slow_query_log 的值为 OFF，表示慢查询日志是禁用的

mysql> show variables like '%slow_query_log%';
+---------------------+------------------------------+
| Variable_name       | Value                        |
+---------------------+------------------------------+
| slow_query_log      | ON                           |
| slow_query_log_file | /var/lib/mysql/test-slow.log |
+---------------------+------------------------------+

可以通过设置 slow_query_log 的值来开启

1	mysql> set global slow_query_log=1;

使用 set global slow_query_log=1 开启了慢查询日志只对当前数据库生效，MySQL 重启后则会失效。如果要永久生效，就必须修改配置文件 my.cnf（其它系统变量也是如此）

-- 编辑配置
vim /etc/my.cnf

-- 添加如下内容
slow_query_log =1
slow_query_log_file=/var/lib/mysql/ruyuan-slow.log

-- 重启MySQL
service mysqld restart

mysql> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /var/lib/mysql/ruyuan-slow.log |
+---------------------+--------------------------------+

那么开启了慢查询日志后，什么样的 SQL 才会记录到慢查询日志里面呢？这个是由参数 long_query_time控制，默认情况下long_query_time 的值为 10 秒.

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

mysql> set global long_query_time=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

修改了变量 long_query_time，但是查询变量 long_query_time 的值还是10，难道没有修改到呢？注意：使用命令 set global long_query_time=1 修改后，需要重新连接或新开一个会话才能看到修改值。

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

log_output 参数是指定日志的存储方式。log_output='FILE' 表示将日志存入文件，默认值是’FILE’。log_output='TABLE' 表示将日志存入数据库，这样日志信息就会被写入到 mysql.slow_log 表中。

mysql> SHOW VARIABLES LIKE '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

MySQL 数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output=’FILE,TABLE’。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件.

系统变量 log-queries-not-using-indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

mysql> set global log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+

3) 慢查询测试

执行 test_index.sql 脚本,监控慢查询日志内容

[root@localhost mysql]# tail -f /var/lib/mysql/ruyuan-slow.log 
/usr/sbin/mysqld, Version: 5.7.30-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument

执行下面的 SQL，执行超时 (超过1秒) 我们去查看慢查询日志

1
2
3

SELECT * FROM test_index WHERE  
hobby = '20009951' OR hobby = '10009931' OR hobby = '30009931' 
OR dname = 'name4000' OR dname = 'name6600' ;

日志内容

我们得到慢查询日志后，最重要的一步就是去分析这个日志。我们先来看下慢日志里到底记录了哪些内容。

如下图是慢日志里其中一条 SQL 的记录内容，可以看到有时间戳，用户，查询时长及具体的 SQL 等信息.

# Time: 2022-02-23T13:50:45.005959Z
# User@Host: root[root] @ localhost []  Id:     3
# Query_time: 3.724273  Lock_time: 0.000371 Rows_sent: 5  Rows_examined: 5000000
SET timestamp=1645624245;
select * from test_index where hobby = '20009951' or hobby = '10009931' or hobby = '30009931' or dname = 'name4000' or dname = 'name6600';

Time: 执行时间
User: 用户信息 ,Id信息
Query_time: 查询时长
Lock_time: 等待锁的时长
Rows_sent:查询结果的行数
Rows_examined: 查询扫描的行数
SET timestamp: 时间戳
SQL的具体信息

慢查询 SQL 优化思路

1) SQL性能下降的原因

在日常的运维过程中，经常会遇到 DBA 将一些执行效率较低的 SQL 发过来找开发人员分析，当我们拿到这个 SQL 语句之后，在对这些SQL 进行分析之前，需要明确可能导致 SQL 执行性能下降的原因进行分析，执行性能下降可以体现在以下两个方面：

等待时间长

1	锁表导致查询一直处于等待状态，后续我们从 MySQL 锁的机制去分析 SQL 执行的原理

执行时间长

1.查询语句写的烂
2.索引失效 
3.关联查询太多join 
4.服务器调优及各个参数的设置

2) 慢查询优化思路

优先选择优化高并发执行的 SQL，因为高并发的 SQL 发生问题带来后果更严重.

比如下面两种情况:
   SQL1: 每小时执行10000次, 每次20个IO 优化后每次18个IO,每小时节省2万次IO
   SQL2: 每小时10次,每次20000个IO,每次优化减少2000个IO,每小时节省2万次IO
   SQL2更难优化,SQL1更好优化.但是第一种属于高并发SQL,更急需优化 成本更低

定位优化对象的性能瓶颈(在优化之前了解性能瓶颈在哪)

在去优化 SQL 时,选择优化分方向有三个: 
  1.IO(数据访问消耗的了太多的时间,查看是否正确使用了索引) , 
  2.CPU(数据运算花费了太多时间, 数据的运算分组 排序是不是有问题) 
  3.网络带宽(加大网络带宽)

明确优化目标

需要根据数据库当前的状态
数据库中与该条SQL的关系
当前SQL的具体功能
最好的情况消耗的资源,最差情况下消耗的资源,优化的结果只有一个给用户一个好的体验

从 explain 执行计划入手

1	只有 explain 能告诉你当前 SQL 的执行状态

永远用小的结果集驱动大的结果集

小的数据集驱动大的数据集,减少内层表读取的次数

类似于嵌套循环
for(int i = 0; i < 5; i++){
	for(int i = 0; i < 1000; i++){

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

尽可能在索引中完成排序

排序操作用的比较多,order by 后面的字段如果在索引中,索引本来就是排好序的,所以速度很快,没有索引的话,就需要从表中拿数据,在内存中进行排序,如果内存空间不够还会发生落盘操作

只获取自己需要的列

1	不要使用select * ,select * 很可能不走索引,而且数据量过大

只使用最有效的过滤条件

1	误区 where后面的条件越多越好,但实际上是应该用最短的路径访问到数据

尽可能避免复杂的join和子查询

1
2
3

每条SQL的JOIN操作 建议不要超过三张表
将复杂的SQL, 拆分成多个小的SQL 单个表执行,获取的结果 在程序中进行封装
如果join占用的资源比较多,会导致其他进程等待时间变长

合理设计并利用索引

如何判定是否需要创建索引?
 1.较为频繁的作为查询条件的字段应该创建索引.
 2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件.（唯一性太差的字段主要是指哪些呢？如状态字段，类型字段等等这些字段中的数据可能总共就是那么几个几十个数值重复使用）（当一条Query所返回的数据超过了全表的15%的时候，就不应该再使用索引扫描来完成这个Query了）.
 3.更新非常频繁的字段不适合创建索引.（因为索引中的字段被更新的时候，不仅仅需要更新表中的数据，同时还要更新索引数据，以确保索引信息是准确的）.
 4.不会出现在WHERE子句中的字段不该创建索引.
    
如何选择合适索引?
 1.对于单键索引，尽量选择针对当前Query过滤性更好的索引.
 2.选择联合索引时,当前Query中过滤性最好的字段在索引字段顺序中排列要靠前.
 3.选择联合索引时,尽量索引字段出现在w中比较多的索引.

50、Hash 索引有哪些优缺点？

MySQL 中索引的常用数据结构有两种: 一种是 B+Tree，另一种则是 Hash.

Hash 底层实现是由 Hash 表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据 key 查找 value 值，也就是单个 key 查询，或者说等值查询。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值,如果出现哈希码值相同的情况会拉出一条链表.

Hsah索引的优点

因为索引自身只需要存储对应的 Hash 值,所以索引结构非常紧凑, 只需要做等值比较查询，而不包含排序或范围查询的需求，都适合使用哈希索引 .
没有哈希冲突的情况下,等值查询访问哈希索引的数据非常快.(如果发生 Hash 冲突,存储引擎必须遍历链表中的所有行指针,逐行进行比较,直到找到所有符合条件的行).

Hash 索引的缺点

哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。
哈希索引只支持等值比较查询。不支持任何范围查询和部分索引列匹配查找。
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

51、说一下 InnoDB 内存相关的参数优化？

Buffer Pool 参数优化

1.1 缓冲池内存大小配置

一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除操作，通过设置这个参数会大量的减少磁盘 I/O 的次数数。
建议：在专用数据库服务器上，可以将缓冲池大小设置为服务器物理内存的 60 % - 80 %.

查看缓冲池大小

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

mysql> select 134217728 / 1024 / 1024;
+-------------------------+
| 134217728 / 1024 / 1024 |
+-------------------------+
|            128.00000000 |
+-------------------------+

在线调整 InnoDB 缓冲池大小
innodb_buffer_pool_size 可以动态设置，允许在不重新启动服务器的情况下调整缓冲池的大小.

mysql> SET GLOBAL innodb_buffer_pool_size = 268435456; -- 512
Query OK, 0 rows affected (0.10 sec)

mysql> show variables like '%innodb_buffer_pool_size%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 268435456 |
+-------------------------+-----------+

监控在线调整缓冲池的进度

mysql> SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';
+----------------------------------+----------------------------------------------------------------------+
| Variable_name                    | Value                                                        |
+----------------------------------+----------------------------------------------------------------------+
| Innodb_buffer_pool_resize_status | Size did not change (old size = new size = 268435456. Nothing to do. |
+----------------------------------+----------------------------------------------------------------------+

1.3 InnoDB 缓存性能评估

当前配置的 innodb_buffer_pool_size 是否合适，可以通过分析 InnoDB 缓冲池的缓存命中率来验证。

以下公式计算 InnoDB buffer pool 命中率:

命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests+innodb_buffer_pool_reads)* 100

参数1: innodb_buffer_pool_reads：表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
参数2: innodb_buffer_pool_read_requests：表示从内存中读取页的请求数。

mysql> show status like 'innodb_buffer_pool_read%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| Innodb_buffer_pool_read_ahead_rnd     | 0     |
| Innodb_buffer_pool_read_ahead         | 0     |
| Innodb_buffer_pool_read_ahead_evicted | 0     |
| Innodb_buffer_pool_read_requests      | 12701 |
| Innodb_buffer_pool_reads              | 455   |
+---------------------------------------+-------+

-- 此值低于90%，则可以考虑增加innodb_buffer_pool_size。
mysql> select 12701 / (455 + 12701) * 100 ;
+-----------------------------+
| 12701 / (455 + 12701) * 100 |
+-----------------------------+
|                     96.5415 |
+-----------------------------+

1.4 Page 管理相关参数

**查看 Page 页的大小(默认16KB),**innodb_page_size只能在初始化 MySQL 实例之前配置，不能在之后修改。如果没有指定值，则使用默认页面大小初始化实例。

mysql> show variables like '%innodb_page_size%'; 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

Page 页管理状态相关参数

mysql> show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_buffer_pool_pages_data    | 515   |
| Innodb_buffer_pool_pages_dirty   | 0     |
| Innodb_buffer_pool_pages_flushed | 334   |
| Innodb_buffer_pool_pages_free    | 15868 |
| Innodb_buffer_pool_pages_misc    | 0     |
| Innodb_buffer_pool_pages_total   | 16383 |
+----------------------------------+-------+

pages_data: InnoDB 缓冲池中包含数据的页数。该数字包括脏页面和干净页面。

pages_dirty: 显示在内存中修改但尚未写入数据文件的 InnoDB 缓冲池数据页的数量（脏页刷新）。

pages_flushed: 表示从 InnoDB 缓冲池中刷新脏页的请求数。

pages_free: 显示 InnoDB 缓冲池中的空闲页面

pages_misc: 缓存池中当前已经被用作管理用途或 hash index 而不能用作为普通数据页的数目

pages_total: 缓存池的页总数目。单位是 page。

52、InnoDB 日志相关的参数优化了解过吗？

1.日志缓冲区相关参数配置

日志缓冲区的大小。一般默认值 16MB 是够用的，但如果事务之中含有 blog/text 等大字段，这个缓冲区会被很快填满会引起额外的IO 负载。配置更大的日志缓冲区,可以有效的提高 MySQL 的效率.

innodb_log_buffer_size 缓冲区大小

mysql> show variables like 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

innodb_log_files_in_group 日志组文件个数
日志组根据需要来创建。而日志组的成员则需要至少 2 个，实现循环写入并作为冗余策略。

mysql> show variables like 'innodb_log_files_in_group';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_log_files_in_group | 2     |
+---------------------------+-------+

innodb_log_file_size 日志文件大小
参数innodb_log_file_size 用于设定 MySQL 日志组中每个日志文件的大小(默认 48M)。此参数是一个全局的静态参数，不能动态修改。
参数 innodb_log_file_size 的最大值，二进制日志文件大小（innodb_log_file_size * innodb_log_files_in_group）不能超过512GB.所以单个日志文件的大小不能超过 256G.

mysql> show variables like 'innodb_log_file_size';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| innodb_log_file_size | 50331648 |
+----------------------+----------+

2.日志文件参数优化

首先我们先来看一下日志文件大小设置对性能的影响

设置过小
1. 参数 innodb_log_file_size设置太小，就会导致 MySQL 的日志文件( redo log）频繁切换，频繁的触发数据库的检查点（Checkpoint），导致刷新脏页到磁盘的次数增加。从而影响 IO 性能。
2. 处理大事务时，将所有的日志文件写满了，事务内容还没有写完，这样就会导致日志不能切换.
设置过大
参数 innodb_log_file_size如果设置太大，虽然可以提升 IO 性能，但是当 MySQL 由于意外宕机时，二进制日志很大，那么恢复的时间必然很长。而且这个恢复时间往往不可控，受多方面因素影响。

优化建议:

如何设置合适的日志文件大小 ?

根据实际生产场景的优化经验,一般是计算一段时间内生成的事务日志（redo log）的大小，而 MySQL 的日志文件的大小最少应该承载一个小时的业务日志量(官网文档中有说明)。

想要估计一下 InnoDB redo log 的大小，需要抓取一段时间内 Log SequenceNumber（日志顺序号）的数据,来计算一小时内产生的日志大小.

Log sequence number

自系统修改开始，就不断的生成 redo 日志。为了记录一共生成了多少日志，于是 mysql 设计了全局变量 log sequence number，简称 lsn，但不是从 0 开始，是从 8704 字节开始。

-- pager分页工具, 只获取 sequence的信息
mysql> pager grep sequence;
PAGER set to 'grep sequence'

-- 查询状态,并倒计时一分钟
mysql> show engine innodb status\G select sleep(60);
Log sequence number 5399154
1 row in set (0.00 sec)

1 row in set (1 min 0.00 sec)

-- 一分时间内所生成的数据量 5406150
mysql> show engine innodb status\G;
Log sequence number 5406150

-- 关闭pager
mysql> nopager;
PAGER set to stdout

有了一分钟的日志量,据此推算一小时内的日志量

mysql> select (5406150 - 5399154) / 1024 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|     6.8320 |
+------------+

mysql> select (5406150 - 5399154) / 1024 * 60 as kb_per_min;
+------------+
| kb_per_min |
+------------+
|   409.9219 |
+------------+

太大的缓冲池或非常不正常的业务负载可能会计算出非常大(或非常小)的日志大小。这也是公式不足之处，需要根据判断和经验。但这个计算方法是一个很好的参考标准。

53、InnoDB IO 线程相关参数优化了解过吗？

数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。从内存中读取一个数据库数据的时间是微秒级别，而从一块普通硬盘上读取一个 IO 是在毫秒级别。要优化数据库，IO 操作是必须要优化的，尽可能将磁盘 IO 转化为内存 IO。

1) 参数: query_cache_size&have_query_cache
MySQL 查询缓存会保存查询返回的完整结果。当查询命中该缓存，会立刻返回结果，跳过了解析，优化和执行阶段。
查询缓存会跟踪查询中涉及的每个表，如果这些表发生变化，那么和这个表相关的所有缓存都将失效。

查看查询缓存是否开启

-- 查询是否支持查询缓存
mysql> show variables like 'have_query_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| have_query_cache | YES   |
+------------------+-------+

-- 查询是否开启查询缓存 默认关闭
mysql> show variables like '%query_cache_type%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| query_cache_type | OFF   |
+------------------+-------+

开启缓存,在 my.ini 中添加下面一行参数

query_cache_size=128M
query_cache_type=1

query_cache_type:
设置为0，OFF,缓存禁用
设置为1，ON,缓存所有的结果
设置为2，DENAND,只缓存在select语句中通过SQL_CACHE指定需要缓存的查询

测试能否缓存查询

mysql> show status like '%Qcache%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Qcache_free_blocks      | 1       |
| Qcache_free_memory      | 1031832 |
| Qcache_hits             | 0       |
| Qcache_inserts          | 0       |
| Qcache_lowmem_prunes    | 0       |
| Qcache_not_cached       | 1       |
| Qcache_queries_in_cache | 0       |
| Qcache_total_blocks     | 1       |
+-------------------------+---------+

Qcache_free_blocks:缓存中目前剩余的 blocks 数量（如果值较大，则查询缓存中的内存碎片过多）
Qcache_free_memory:空闲缓存的内存大小
Qcache_hits:命中缓存次数
Qcache_inserts: 未命中然后进行正常查询
Qcache_lowmem_prunes:查询因为内存不足而被移除出查询缓存记录
Qcache_not_cached: 没有被缓存的查询数量
Qcache_queries_in_cache:当前缓存中缓存的查询数量
Qcache_total_blocks:当前缓存的block数量

优化建议: Query Cache 的使用需要多个参数配合，其中最为关键的是 query_cache_size 和 query_cache_type ，前者设置用于缓存 ResultSet 的内存大小，后者设置在何场景下使用 Query Cache。

MySQL 数据库数据变化相对不多，query_cache_size 一般设置为 256MB 比较合适 ,也可以通过计算 Query Cache 的命中率来进行调整

1	( Qcache_hits / ( Qcache_hits + Qcache_inserts ) * 100) )

参数: innodb_max_dirty_pages_pct 该参数是 InnoDB 存储引擎用来控制 buffer pool 中脏页的百分比，当脏页数量占比超过这个参数设置的值时，InnoDB 会启动刷脏页的操作。

-- innodb_max_dirty_pages_pct 参数可以动态调整，最小值为0， 最大值为99.99，默认值为 75。
mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+

优化建议: 该参数比例值越大，从内存到磁盘的写入操作就会相对减少，所以能够一定程度下减少写入操作的磁盘 IO。但是，如果这个比例值过大，当数据库 Crash 之后重启的时间可能就会很长，因为会有大量的事务数据需要从日志文件恢复出来写入数据文件中.最大不建议超过90,一般重启恢复的数据在超过 1G B的话,启动速度就会变慢.

3) 参数: innodb_old_blocks_pct&innodb_old_blocks_time
innodb_old_blocks_pct 用来确定 LRU 链表中 old sublist 所占比例,默认占用 37%

mysql> show variables like '%innodb_old_blocks_pct%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_old_blocks_pct | 37    |
+-----------------------+-------+

innodb_old_blocks_time 用来控制 old sublist 中 page 的转移策略，新的 page 页在进入 LRU 链表中时，会先插入到 old sublist 的头部，然后 page 需要在 old sublist 中停留 innodb_old_blocks_time 这么久后，下一次对该 page 的访问才会使其移动到 new sublist 的头部，默认值 1 秒.

mysql> show variables like '%innodb_old_blocks_time%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_old_blocks_time | 1000  |
+------------------------+-------+

优化建议: 在没有大表扫描的情况下，并且数据多为频繁使用的数据时，我们可以增加 innodb_old_blocks_pct 的值，并且减小innodb_old_blocks_time 的值。让数据页能够更快和更多的进入的热点数据区。

54、什么是写失效？

InnoDB 的页和操作系统的页大小不一致，InnoDB 页大小一般为 16K，操作系统页大小为 4 K，InnoDB 的页写入到磁盘时，一个页需要分 4 次写。

如果存储引擎正在写入页的数据到磁盘时发生了宕机，可能出现页只写了一部分的情况，比如只写了 4 K，就宕机了，这种情况叫做部分写失效（partial page write），可能会导致数据丢失。

双写缓冲区 Doublewrite Buffer

为了解决写失效问题，InnoDB 实现了 double write buffer Files, 它位于系统表空间，是一个存储区域。

在 BufferPool 的 page 页刷新到磁盘真正的位置前，会先将数据存在 Doublewrite 缓冲区。这样在宕机重启时，如果出现数据页损坏，那么在应用 redo log 之前，需要通过该页的副本来还原该页，然后再进行 redo log 重做，double write 实现了 InnoDB 引擎数据页的可靠性.

默认情况下启用双写缓冲区，如果要禁用 Doublewrite 缓冲区，可以将 innodb_doublewrite设置为 0。

mysql> show variables like '%innodb_doublewrite%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |
+--------------------+-------+
1 row in set (0.01 sec)

数据双写流程

step1：当进行缓冲池中的脏页刷新到磁盘的操作时,并不会直接写磁盘,每次脏页刷新必须要先写 double write .
step2：通过 memcpy 函数将脏页复制到内存中的 double write buffer .
step3: double write buffer 再分两次、每次 1 MB, 顺序写入共享表空间的物理磁盘上, 第一次写.
step4: 在完成 double write 页的写入后，再将 double wirite buffer 中的页写入各个表的 **独立表空间 **文件中(数据文件 .ibd), 第二次写。

为什么写两次 ?

可能有的同学会有疑问，为啥写两次，刷一次数据文件保存数据不就可以了，为什么还要写共享表空间 ?其实是因为共享表空间是在ibdbata 文件中划出 2 M 连续的空间，专门给 double write 刷脏页用的, 由于在这个过程中，double write 页的存储是连续的，因此写入磁盘为顺序写，性能很高；完成 double write 后，再将脏页写入实际的各个表空间文件，这时写入就是离散的了.

55、什么是行溢出？

行记录格式

1) 行格式分类

表的行格式决定了它的行是如何物理存储的，这反过来又会影响查询和 DML 操作的性能。如果在单个 page 页中容纳更多行，查询和索引查找可以更快地工作，缓冲池中所需的内存更少，写入更新时所需的 I/O 更少。

InnoDB 存储引擎支持四种行格式：Redundant、Compact、Dynamic 和 Compressed .

查询 MySQL 使用的行格式，默认为: dynamic

mysql> show variables like 'innodb_default_row_format';
+---------------------------+---------+
| Variable_name             | Value   |
+---------------------------+---------+
| innodb_default_row_format | dynamic |
+---------------------------+---------+

指定行格式语法

1 2	CREATE TABLE <table_name(column_name)> ROW_FORMAT=行格式名称 ALTER TABLE <table_name> ROW_FORMAT=行格式名称

2) COMPACT 行记录格式

Compact 设计目标是高效地存储数据，一个页中存放的行数据越多，其性能就越高。

Compact行记录由两部分组成: 记录放入额外信息和记录的真实数据.

记录额外信息部分

服务器为了描述一条记录而添加了一些额外信息(元数据信息)，这些额外信息分为 3 类，分别是: 变长字段长度列表、NULL 值列表和记录头信息.

变长字段长度列表

MySQL 支持一些变长的数据类型，比如 VARCHAR(M)、VARBINARY(M)、各种 TEXT 类型，各种 BLOB 类型，这些变长的数据类型占用的存储空间分为两部分：
1. 真正的数据内容
2. 占用的字节数
变长字段的长度是不固定的，所以在存储数据的时候要把这些数据占用的字节数也存起来，读取数据的时候才能根据这个长度列表去读取对应长度的数据。

在 Compact行格式中，把所有变长类型的列的长度都存放在记录的开头部位形成一个列表，按照列的顺序逆序存放,这个列表就是 变长字段长度列表。
NULL 值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL 值列表中。( 如果表中所有列都不允许为 NULL，就不存在 NULL 值列表 )

记录头信息

记录头信息是由固定的 5 个字节组成，5 个字节也就是 40 个二进制位，不同的位代表不同的意思，这些头信息会在后面的一些功能中看到。

名称	大小(单位:bit)	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	标记该记录是否是本层 B+ 树的非叶子节点中的最小记录
n_owned	4	表示当前分组中管理的记录数
heap_no	13	表示当前记录在记录堆中的位置信息
record_type	3	表示当前记录的类型: 0 表示普通记录, 1 表示 B+ 树非叶子节点记录, 2 表示最小记录,3表示最大记录
next_record	16	表示下一条记录的相对位置

delete_mask

这个属性标记着当前记录是否被删除，占用 1 个二进制位，值为 0 的时候代表记录并没有被删除，为 1 的时候代表记录被删除掉了
min_rec_mask

B+ 树的每层非叶子节点中的最小记录都会添加该标记。
n_owned

代表每个分组里，所拥有的记录的数量，一般是分组里主键最大值才有的。
heap_no

在数据页的 User Records 中插入的记录是一条一条紧凑的排列的，这种紧凑排列的结构又被称为堆。为了便于管理这个堆，把记录在堆中的相对位置给定一个编号——heap_no。所以 heap_no 这个属性表示当前记录在本页中的位置。
record_type

这个属性表示当前记录的类型，一共有 4 种类型的记录， 0 表示普通用户记录， 1 表示 B+ 树非叶节点记录， 2 表示最小记录， 3 表示最大记录。
next_record

表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量，可以理解为指向下一条记录地址的指针。值为正数说明下一条记录在当前记录后面，为负数说明下一条记录在当前记录的前面。

记录真实数据部分

记录的真实数据除了插入的那些列的数据，MySQL 会为每个记录默认的添加一些列（也称为隐藏列），具体的列如下：<**MVCC 中的三个隐式字段**>

列名是否必须占用空间描述

row_id 否 6字节行 ID,唯一标识一条记录

transaction_id 是 6字节事务 ID

roll_pointer 是 7字节回滚指针

生成隐藏主键列的方式有:
1. 服务器会在内存中维护一个全局变量，每当向某个包含隐藏的row_id列的表中插入一条记录时，就会把该变量的值当作新记录的row_id列的值，并且把该变量自增1。
2. 每当这个变量的值为256的倍数时，就会将该变量的值刷新到系统表空间的页号为7的页面中一个Max Row ID的属性处。
3. 当系统启动时，会将页中的Max Row ID属性加载到内存中，并将该值加上256之后赋值给全局变量，因为在上次关机时该全局变量的值可能大于页中Max Row ID属性值。
3) Compact 中的行溢出机制

什么是行溢出 ?

MySQL 中是以页为基本单位,进行磁盘与内存之间的数据交互的,我们知道一个页的大小是16 KB,16 KB = 16384 字节.而一个 varchar(m) 类型列最多可以存储 65532 个字节,一些大的数据类型比如 TEXT 可以存储更多.

如果一个表中存在这样的大字段,那么一个页就无法存储一条完整的记录.这时就会发生行溢出,多出的数据就会存储在另外的溢出页中.

总结: 如果某些字段信息过长，无法存储在B树节点中，这时候会被单独分配空间，此时被称为溢出页，该字段被称为页外列。

Compact 中的行溢出机制

InnoDB 规定一页至少存储两条记录(B+ 树特点)，如果页中只能存放下一条记录，InnoDB 存储引擎会自动将行数据存放到溢出页中.
当发生行溢出时，数据页只保存了前 768 字节的前缀数据，接着是 20 个字节的偏移量，指向行溢出页.

56、如何进行 JOIN 优化？

JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN 操作有多种方式，取决于最终数据的合并效果。常用连接方式的有以下几种:

什么是驱动表 ?

多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.
驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

驱动表的选择要遵循一个规则:

在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表

3) 三种 JOIN算法

1.Simple Nested-Loop Join（简单的嵌套循环连接 )

简单来说嵌套循环连接算法就是一个双层for 循环，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果.
这种算法是最简单的方案，性能也一般。对内循环没优化。

例如有这样一条SQL:

1
2
3

-- 连接用户表与订单表 连接条件是 u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表为驱动表,order表为被驱动表

转换成代码执行时的思路是这样的:

for(user表行 uRow : user表){
    for(Order表的行 oRow : order表){
        if(uRow.id = oRow.user_id){
            return uRow;
        }
    }
}

匹配过程如下图
SNL 的特点
- 简单粗暴容易理解，就是通过双层循环比较数据来获得结果
- 查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：
  - A 表扫描 1 次。
  - B 表扫描 M 次。
  - 一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N * M 次

2) Index Nested-Loop Join（索引嵌套循环连接）

Index Nested-Loop Join 其优化的思路: 主要是为了减少内层表数据的匹配次数 , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。
从原来的 匹配次数 = 外层表行数 * 内层表行数 , 变成了 匹配次数 = 外层表的行数 * 内层表索引的高度 ，极大的提升了 join的性能。
当 order 表的 user_id 为索引的时候执行过程会如下图：

注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。

3) Block Nested-Loop Join( 块嵌套循环连接 )

如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？

因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进行比较，而是整个 buffer 和 order表进行批量比较。
如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要访问一次。

MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个 join buffer。

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+

mysql> set session join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)

4) JOIN 优化总结

永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
为匹配的条件增加索引(减少内层表的循环匹配次数)
增大 join buffer size 的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多

57、索引哪些情况下会失效？

查询条件包含 or，会导致索引失效。
隐式类型转换，会导致索引失效，例如 age 字段类型是 int，我们 where age = “1”，这样就会触发隐式类型转换
like 通配符会导致索引失效，注意:”ABC%” 不会失效，会走 range 索引，”% ABC” 索引会失效
联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
对索引字段进行函数运算。
对索引列运算（如，+、-、*、/），索引失效。
索引字段上使用（!= 或者 < >，not in）时，会导致索引失效。
索引字段上使用 is null， is not null，可能导致索引失效。
相 join 的两个表的字符编码不同，不能命中索引，会导致笛卡尔积的循环计算
mysql 估计使用全表扫描要比使用索引快，则不使用索引。

58、什么是覆盖索引？

覆盖索引是一种避免回表查询的优化策略: 只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快。

具体的实现方式:

将被查询的字段建立普通索引或者联合索引，这样的话就可以直接返回索引中的的数据，不需要再通过聚集索引去定位行记录，避免了回表的情况发生。

1 2	EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name = 'tom' AND user_age = 17;

覆盖索引的定义与注意事项:

如果一个索引包含了所有需要查询的字段的值 (不需要回表)，这个索引就是覆盖索引。
MySQL 只能使用 B+Tree 索引做覆盖索引 (因为只有 B+ 树能存储索引列值)
在 explain 的 Extra 列, 如果出现 Using index 表示使用到了覆盖索引 , 所取的数据完全在索引中就能拿到

59、介绍一下 MySQL 中事务的特性？

在关系型数据库管理系统中，一个逻辑工作单元要成为事务，必须满足这 4 个特性，即所谓的 ACID：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。

1）原子性

原子性：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。

InnoDB 存储引擎提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中 redo log 用于保证事务持久性；undo log 则是事务原子性和隔离性实现的基础。

每写一个事务,都会修改 Buffer Pool,从而产生相应的 Redo/Undo日志:

如果要回滚事务，那么就基于 undo log 来回滚就可以了，把之前对缓存页做的修改都给回滚了就可以了。
如果事务提交之后，redo log 刷入磁盘，结果 MySQL 宕机了，是可以根据 redo log 恢复事务修改过的缓存数据的。

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。

InnoDB 实现回滚，靠的是 undo log ：当事务对数据库进行修改时，InnoDB 会生成对应的 undo log ；如果事务执行失败或调用了rollback ，导致事务需要回滚，便可以利用 undo log 中的信息将数据回滚到修改之前的样子。

2）一致性

一致性：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。

约束一致性：创建表结构时所指定的外键、唯一索引等约束。
数据一致性：是一个综合性的规定，因为它是由原子性、持久性、隔离性共同保证的结果，而不是单单依赖于某一种技术。

3）隔离性

隔离性：指的是一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对其他的并发事务是隔离的。

不考虑隔离性会引发的问题:

脏读 : 一个事务读取到了另一个事务修改但未提交的数据。
不可重复读: 一个事务中多次读取同一行记录的结果不一致，后面读取的跟前面读取的结果不一致。
幻读 : 一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录。

数据库事务的隔离级别有 4 个，由低到高依次为Read uncommitted 、Read committed、Repeatable read 、Serializable ，这四个级别可以逐个解决脏读、不可重复读、幻读这几类问题。

4）持久性

持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不应该对其有任何影响，不会丢失。

MySQL 事务的持久性保证依赖的日志文件: redo log

redo log 也包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。redo log 是物理日志，记录的是数据库中物理页的情况。
当数据发生修改时，InnoDB 不仅会修改 Buffer Pool 中的数据，也会在 redo log buffer 记录这次操作；当事务提交时，会对 redo log buffer 进行刷盘，记录到 redo log file 中。如果 MySQL 宕机，重启时可以读取 redo log file 中的数据，对数据库进行恢复。这样就不需要每次提交事务都实时进行刷脏了。

5）ACID 总结

事务的持久化是为了应对系统崩溃造成的数据丢失.
只有保证了事务的一致性，才能保证执行结果的正确性
在非并发状态下，事务间天然保证隔离性，因此只需要保证事务的原子性即可保证一致性.
在并发状态下，需要严格保证事务的原子性、隔离性。

60、MySQL 的可重复读怎么实现的？

可重复读（repeatable read）定义：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

MVCC

MVCC，多版本并发控制, 用于实现读已提交和可重复读隔离级别。
MVCC 的核心就是 Undo log 多版本链 + Read view，“MV”就是通过 Undo log来保存数据的历史版本，实现多版本的管理，“CC”是通过 Read-view来实现管理，通过 Read-view原则来决定数据是否显示。同时针对不同的隔离级别， Read view 的生成策略不同，也就实现了不同的隔离级别。

Undo log 多版本链

每条数据都有三个隐藏字段:

列名	是否必须	占用空间	描述
row_id	否	6字节	行 ID,唯一标识一条记录
transaction_id	是	6字节	事务 ID，记录最近一次更新这条数据的事务id
roll_pointer	是	7字节	回滚指针，指向之前生成的 undo log

每一条数据都有多个版本,版本之间通过 undo log 链条进行连接通过这样的设计方式，可以保证每个事务提交的时候，一旦需要回滚操作,可以保证同一个事务只能读取到比当前版本更早提交的值,不能看到更晚提交的值。

ReadView

Read View是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC（Read Committed，读提交）和 RR（Repeatable Read，可重复读）隔离级别的实现.

Read View简单理解就是对数据在某个时刻的状态拍成照片记录下来。那么之后获取某时刻的数据时就还是原来的照片上的数据，是不会变的.

Read View中比较重要的字段有4个:

m_ids : 用来表示MySQL中哪些事务正在执行,但是没有提交.
min_trx_id: 就是m_ids里最小的值.
max_trx_id : 下一个要生成的事务id值,也就是最大事务id
creator_trx_id: 就是你这个事务的id

当一个事务第一次执行查询 sql 时，会生成一致性视图 read-view（快照），查询时从 undo log 中最新的一条记录开始跟 read-view 做对比，如果不符合比较规则，就根据回滚指针回滚到上一条记录继续比较，直到得到符合比较条件的查询结果。

Read View判断记录某个版本是否可见的规则如下

1.如果当前记录的事务id落在绿色部分（trx_id < min_id），表示这个版本是已提交的事务生成的，可读。
2.如果当前记录的事务id落在红色部分（trx_id > max_id），表示这个版本是由将来启动的事务生成的，不可读。

如果当前记录的事务id落在黄色部分（min_id <= trx_id <= max_id），则分为两种情况：
若当前记录的事务id在未提交事务的数组中，则此条记录不可读；
若当前记录的事务id不在未提交事务的数组中，则此条记录可读。

RC 和 RR 隔离级别都是由 MVCC 实现，区别在于：

读取的一致性：在 RC 隔离级别下，读取的数据是提交事务时的最新版本，即读取已经提交的数据。而在 RR 隔离级别下，读取的数据是事务开始时的快照版本，即读取事务开始时已经存在的数据。在 RR 级别下，事务在读取数据时，即使其他事务对数据进行了修改，也不会看到这些修改，保证了读取的一致性。
读取的锁定：在 RC 隔离级别下，读取的数据不会被锁定，即其他事务可以修改被读取的数据。而在 RR 隔离级别下，读取的数据会被共享锁定，即其他事务不能修改被读取的数据，从而避免了脏读和不可重复读的问题。
并发冲突：由于 RR 隔离级别下的读取会锁定数据，可能会导致更多的并发冲突和锁竞争，从而影响性能。而在 RC 隔离级别下，读取不会锁定数据，可以提高并发性能。

61、Repeatable Read 解决了幻读问题吗？

可重复读（repeatable read）定义：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。

不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行。

注意在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现（查询语句添加for update，表示当前读）；

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读（Current Read）。

快照读
快照读是指读取数据时不是读取最新版本的数据，而是基于历史版本读取的一个快照信息（mysql 读取 undo log 历史版本) ，快照读可以使普通的 SELECT 读取数据时不用对表数据进行加锁，从而解决了因为对数据库表的加锁而导致的两个如下问题
1. 解决了因加锁导致的修改数据时无法对数据读取问题.
2. 解决了因加锁导致读取数据时无法对数据进行修改的问题.
当前读
当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据而且要保证事务的隔离性，所以当前读是需要对数据进行加锁的（插入/更新/删除操作，属于当前读，需要加锁 , select for update 为当前读）

表结构

id	key	value
0	0	0
1	1	1

假设 select * from where value=1 for update，只在这一行加锁（注意这只是假设），其它行不加锁，那么就会出现如下场景：

Session A 的三次查询 Q1-Q3 都是 select * from where value=1 for update，查询的 value=1 的所有 row。

T1：Q1 只返回一行(1,1,1)；
T2：session B 更新 id=0 的 value 为 1，此时表 t 中 value=1 的数据有两行
T3：Q2 返回两行(0,0,1),(1,1,1)
T4：session C 插入一行(6,6,1)，此时表 t 中 value=1 的数据有三行
T5：Q3 返回三行(0,0,1),(1,1,1),(6,6,1)
T6：session A 事物 commit。

其中 Q3 读到 value=1 这一样的现象，就称之为幻读，幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

先对“幻读”做出如下解释：

要讨论「可重复读」隔离级别的幻读现象，是要建立在「当前读」的情况下，而不是快照读,因为在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。

Next-key Lock 锁

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，Innodb 引擎为了解决「可重复读」隔离级别使用「当前读」而造成的幻读问题，就引出了 next-key 锁，就是记录锁和间隙锁的组合。

RecordLock 锁：锁定单个行记录的锁。（记录锁，RC、RR 隔离级别都支持）
GapLock 锁：间隙锁，锁定索引记录间隙(不包括记录本身)，确保索引记录的间隙不变。（范围锁，RR 隔离级别支持）
Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR 隔离级别支持）

总结

RR 隔离级别下间隙锁才有效，RC 隔离级别下没有间隙锁；
RR 隔离级别下为了解决“幻读”问题：“快照读”依靠 MVCC 控制，“当前读”通过间隙锁解决；
间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间；
间隙锁的引入，可能会导致同样语句锁住更大的范围，影响并发度。

62、请说一下数据库锁的种类？

MySQL 数据库由于其自身架构的特点,存在多种数据存储引擎, MySQL 中不同的存储引擎支持不同的锁机制。

**MyISAM **和 MEMORY 存储引擎采用的表级锁，
InnoDB 存储引擎既支持行级锁，也支持表级锁，默认情况下采用行级锁。
BDB 采用的是页面锁，也支持表级锁

按照数据操作的类型分

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

按照数据操作的粒度分

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

按照操作性能可分为乐观锁和悲观锁

乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，如果发现冲突了，则提示错误信息。
悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。

63、请说一下共享锁和排他锁？

行级锁分为共享锁和排他锁两种。

行锁的是 mysql 锁中粒度最小的一种锁，因为锁的粒度很小，所以发生资源争抢的概率也最小，并发性能最大，但是也会造成死锁，每次加锁和释放锁的开销也会变大。

使用 MySQL 行级锁的两个前提

使用 innoDB 引擎
开启事务 (隔离级别为 Repeatable Read)

InnoDB行锁的类型

共享锁（S）：当事务对数据加上共享锁后, 其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。
排他锁（X）：如果事务 T 对数据 A 加上排他锁后，则其他事务不能再对数据 A 加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

加锁的方式

InnoDB 引擎默认更新语句，update,delete,insert 都会自动给涉及到的数据加上排他锁，select 语句默认不会加任何锁类型，如果要加可以使用下面的方式:
加共享锁（S）：select * from table_name where … lock in share mode;
加排他锁（x）：select * from table_name where … for update;

锁兼容

共享锁只能兼容共享锁, 不兼容排它锁
排它锁互斥共享锁和其它排它锁

64、InnoDB 的行锁是怎么实现的？

InnoDB 行锁是通过对索引数据页上的记录加锁实现的，主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

RecordLock 锁：锁定单个行记录的锁。（记录锁，RC、RR 隔离级别都支持）
GapLock 锁：间隙锁，锁定索引记录间隙，确保索引记录的间隙不变。（范围锁，RR 隔离级别支持）
Next-key Lock 锁：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。（记录锁+范围锁，RR 隔离级别支持）

注意： InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁

在 RR 隔离级别，InnoDB 对于记录加锁行为都是先采用 Next-Key Lock，但是当 SQL 操作含有唯一索引时，Innodb 会对 Next-Key Lock 进行优化，降级为 RecordLock，仅锁住索引本身而非范围。

各种操作加锁的特点

1）select … from 语句：InnoDB 引擎采用 MVCC 机制实现非阻塞读，所以对于普通的 select 语句，InnoDB 不加锁

2）select … from lock in share mode语句：追加了共享锁，InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock 锁。

3）select … from for update 语句：追加了排他锁，InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock 锁。

4）update … where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock锁。

5）delete … where 语句：InnoDB 会使用 Next-Key Lock 锁进行处理，如果扫描发现唯一索引，可以降级为 RecordLock锁。

6）insert语句：InnoDB 会在将要插入的那一行设置一个排他的 RecordLock 锁。

下面以“update t1 set name=‘lisi’ where id=10”操作为例，举例子分析下 InnoDB 对不同索引的加锁行为，以RR隔离级别为例。

主键加锁

加锁行为：仅在id=10的主键索引记录上加X锁。
唯一键加锁

加锁行为：现在唯一索引 id 上加 X 锁，然后在 id=10 的主键索引记录上加 X 锁。
非唯一键加锁

加锁行为：对满足id=10条件的记录和主键分别加X锁，然后在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)-(11,f)范围分别加Gap Lock。
无索引加锁

加锁行为：表里所有行和间隙都会加X锁。（当没有索引时，会导致全表锁定，因为 InnoDB 引擎锁机制是基于索引实现的记录锁定）。

65、并发事务会产生哪些问题

事务并发处理可能会带来一些问题，如下：

更新丢失
当两个或多个事务更新同一行记录，会产生更新丢失现象。可以分为回滚覆盖和提交覆盖。
- 回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了。
- 提交覆盖：一个事务提交操作，把其他事务已提交的数据给覆盖了。
脏读
一个事务读取到了另一个事务修改但未提交的数据。
不可重复读
一个事务中多次读取同一行记录不一致，后面读取的跟前面读取的不一致。
幻读
一个事务中多次按相同条件查询，结果不一致。后续查询的结果和面前查询结果不同，多了或少了几行记录。

“更新丢失”、”脏读”、“不可重复读”和“幻读”等并发事务问题，其实都是数据库一致性问题，为了解决这些问题，MySQL数据库是通过事务隔离级别来解决的，数据库系统提供了以下 4 种事务隔离级别供用户选择。

事务隔离级别	回滚覆盖	脏读	不可重复读	提交覆盖	幻读
读未提交	x	可能发生	可能发生	可能发生	可能发生
读已提交	x	x	可能发生	可能发生	可能发生
可重复读	x	x	x	x	可能发生
串行化	x	x	x	x	x

读未提交
Read Uncommitted 读未提交：解决了回滚覆盖类型的更新丢失，但可能发生脏读现象，也就是可能读取到其他会话中未提交事务修改的数据。
已提交读
Read Committed 读已提交：只能读取到其他会话中已经提交的数据，解决了脏读。但可能发生不可重复读现象，也就是可能在一个事务中两次查询结果不一致。
可重复度
Repeatable Read 可重复读：解决了不可重复读，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上会出现幻读，简单的说幻读指的的当用户读取某一范围的数据行时，另一个事务又在该范围插入了新行，当用户在读取该范围的数据时会发现有新的幻影行。
可串行化
所有的增删改查串行执行。它通过强制事务排序，解决相互冲突，从而解决幻度的问题。这个级别可能导致大量的超时现象的和锁竞争，效率低下。

数据库的事务隔离级别越高，并发问题就越小，但是并发处理能力越差（代价）。读未提交隔离级别最低，并发问题多，但是并发处理能力好。以后使用时，可以根据系统特点来选择一个合适的隔离级别，比如对不可重复读和幻读并不敏感，更多关心数据库并发处理能力，此时可以使用 Read Commited 隔离级别。

事务隔离级别，针对 Innodb 引擎，支持事务的功能。像 MyISAM 引擎没有关系。

事务隔离级别和锁的关系

1）事务隔离级别是 SQL92 定制的标准，相当于事务并发控制的整体解决方案，本质上是对锁和 MVCC 使用的封装，隐藏了底层细节。

2）锁是数据库实现并发控制的基础，事务隔离性是采用锁来实现，对相应操作加不同的锁，就可以防止其他事务同时对数据进行读写操作。

3）对用户来讲，首先选择使用隔离级别，当选用的隔离级别不能解决并发问题或需求时，才有必要在开发中手动的设置锁。

MySQL 默认隔离级别：可重复读

Oracle、SQLServer 默认隔离级别：读已提交

一般使用时，建议采用默认隔离级别，然后存在的一些并发问题，可以通过悲观锁、乐观锁等实现处理。

66、说一下MVCC内部细节

MVCC概念

MVCC（Multi Version Concurrency Control）被称为多版本并发控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本处理，并通过事务的可见性来保证事务能看到自己应该看到的数据版本。

MVCC 最大的好处是读不加锁，读写不冲突。在读多写少的系统应用中，读写不冲突是非常重要的，极大的提升系统的并发性能，这也是为什么现阶段几乎所有的关系型数据库都支持 MVCC 的原因，不过目前 MVCC 只在 Read Commited 和 Repeatable Read 两种隔离级别下工作。

回答这个面试题时，主要介绍以下的几个关键内容：

1）行记录的三个隐藏字段

![i

DB_ROW_ID : 如果没有为表显式的定义主键，并且表中也没有定义唯一索引，那么 InnoDB 会自动为表添加一个 row_id 的隐藏列作为主键。
DB_TRX_ID : 事务中对某条记录做增删改时,就会将这个事务的事务 ID 写入到 trx_id 中.
DB_ROLL_PTR: 回滚指针,指向 undo log 的指针

2）Undo log 多版本链

举例：事务 T-100 和 T-120 对表中 id = 1 的数据行做 update 操作，事务 T-130 进行 select 操作，即使 T-100 已经提交修改，三次 select 语句的结果都是“lisi”。

每一条数据都有多个版本,版本之间通过 undo log 链条进行连接

3）ReadView

Read View 简单理解就是对数据在每个时刻的状态拍成照片记录下来。那么之后获取某时刻的数据时就还是原来的照片上的数据，是不会变的.

Read View中比较重要的字段有 4 个:

m_ids : 用来表示 MySQL 中哪些事务正在执行,但是没有提交.
min_trx_id: 就是 m_ids 里最小的值.
max_trx_id : 下一个要生成的事务 id 值,也就是最大事务 id
creator_trx_id: 就是你这个事务的 id

通过 Read View 判断记录的某个版本是否可见的方式总结:

trx_id = creator_trx_id
如果被访问版本的trx_id,与readview中的creator_trx_id值相同,表明当前事务在访问自己修改过的记录,该版本可以被当前事务访问.
trx_id < min_trx_id
如果被访问版本的trx_id,小于readview中的min_trx_id值,表明生成该版本的事务在当前事务生成readview前已经提交,该版本可以被当前事务访问.
trx_id >= max_trx_id
如果被访问版本的trx_id,大于或等于readview中的max_trx_id值,表明生成该版本的事务在当前事务生成readview后才开启,该版本不可以被当前事务访问.
trx_id > min_trx_id && trx_id < max_trx_id
如果被访问版本的trx_id,值在readview的min_trx_id和max_trx_id之间，就需要判断trx_id属性值是不是在m_ids列表中？
- 在：说明创建readview时生成该版本的事务还是活跃的,该版本不可以被访问
- 不在：说明创建readview时生成该版本的事务已经被提交,该版本可以被访问

何时生成 ReadView 快照

在 读已提交（Read Committed，简称RC） 隔离级别下，**每一次 **读取数据前都生成一个ReadVIew。
在 可重复读 （Repeatable Read，简称RR）隔离级别下，在一个事务中，只在 第一次读取数据前生成一个 ReadVIew。

4）快照读（Snapshot Read ）与当前读（Current Read ）

在 MVCC 并发控制中，读操作可以分为两类: 快照读（Snapshot Read）与当前读（Current Read）。

快照读
快照读是指读取数据时不是读取最新版本的数据，而是基于历史版本读取的一个快照信息（mysql读取undo log历史版本) ，快照读可以使普通的SELECT 读取数据时不用对表数据进行加锁，从而解决了因为对数据库表的加锁而导致的两个如下问题
1. 解决了因加锁导致的修改数据时无法对数据读取问题.
2. 解决了因加锁导致读取数据时无法对数据进行修改的问题.
当前读
当前读是读取的数据库最新的数据，当前读和快照读不同，因为要读取最新的数据而且要保证事务的隔离性，所以当前读是需要对数据进行加锁的（Update delete insert select ....lock in share mode , select for update 为当前读）

总结一下

并发环境下，写-写操作有加锁解决方案，但为了提高性能，InnoDB 存储引擎提供 MVCC，目的是为了解决读-写，写-读操作下不加锁仍能安全进行。
MVCC 的过程，本质就是访问版本链，并判断哪个版本可见的过程。该判断算法是通过版本上的 trx_id 与快照 ReadView 的若干个信息进行对比。
快照生成的时机因隔离级别不同，读已提交隔离级别下，每一次读取前都会生成一个快照 ReadView；而可重复读则仅在一个事务中，第一次读取前生成一个快照。

67、说一下 MySQL 死锁的原因和处理方法

1) 表的死锁

产生原因:

用户A访问表A（锁住了表A），然后又访问表B；另一个用户B访问表B（锁住了表B），然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

用户A–》A表（表锁）–》B表（表锁）

用户B–》B表（表锁）–》A表（表锁）

解决方案：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。

仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理，必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

2) 行级锁死锁

产生原因1：

如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或死锁。

解决方案1：

SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划”对SQL语句进行分析，对于有全表扫描和全表锁定的SQL语句，建立相应的索引进行优化。

产生原因2：

两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁

产生原因3：每个事务只有一个SQL,但是有些情况还是会发生死锁.

事务1,从name索引出发 , 读到的[hdc, 1], [hdc, 6]均满足条件, 不仅会加name索引上的记录X锁, 而且会加聚簇索引上的记录X锁, 加锁顺序为先[1,hdc,100], 后[6,hdc,10]
事务2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。
但是加锁时发现跟事务1的加锁顺序正好相反，两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

解决方案: 如上面的原因2和原因3, 对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；

68、介绍一下 MySQL 的体系架构？

MySQL Server架构自顶向下大致可以分网络连接层、服务层、存储引擎层和系统文件层。

一、网络连接层

客户端连接器（Client Connectors）：提供与MySQL服务器建立的支持。目前几乎支持所有主流的服务端编程技术，例如常见的 Java、C、Python、.NET等，它们通过各自API技术与MySQL建立连接。

二、服务层（MySQL Server）

服务层是MySQL Server的核心，主要包含系统管理和控制工具、连接池、SQL接口、解析器、查询优化器和缓存六个部分。

连接池（Connection Pool） ：负责存储和管理客户端与数据库的连接，一个线程负责管理一个连接。
系统管理和控制工具（Management Services & Utilities） ：例如备份恢复、安全管理、集群管理等
SQL接口（SQL Interface） ：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML、DDL、存储过程、视图、触发器等。
解析器（Parser） ：负责将请求的SQL解析生成一个”解析树”。然后根据一些MySQL规则进一步检查解析树是否合法。
查询优化器（Optimizer） ：当“解析树”通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互。

select uid,name from user where gender=1;

选取–》投影–》联接策略

1）select先根据where语句进行选取，并不是查询出全部数据再过滤

2）select查询根据uid和name进行属性投影，并不是取出所有字段

3）将前面选取和投影联接起来最终生成查询结果
缓存（Cache&Buffer） ：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

三、存储引擎层（Pluggable Storage Engines）

存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同存储引擎之间的差异。现在有很多种存储引擎，各有各的特点，最常见的是MyISAM和InnoDB。

四、系统文件层（File System）

该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层。主要包含日志文件，数据文件，配置文件，pid 文件，socket 文件等。

日志文件
- 错误日志（Error log）
  默认开启，show variables like ‘%log_error%’
- 通用查询日志（General query log）
  记录一般查询语句，show variables like ‘%general%’;
- 二进制日志（binary log）
  记录了对MySQL数据库执行的更改操作，并且记录了语句的发生时间、执行时长；但是它不记录select、show等不修改数据库的SQL。主要用于数据库恢复和主从复制。
  show variables like ‘%log_bin%’; //是否开启
  show variables like ‘%binlog%’; //参数查看
  show binary logs;//查看日志文件
- 慢查询日志（Slow query log）
  记录所有执行时间超时的查询SQL，默认是10秒。
  show variables like ‘%slow_query%’; //是否开启
  show variables like ‘%long_query_time%’; //时长
配置文件
用于存放MySQL所有的配置信息文件，比如my.cnf、my.ini等。
数据文件
- db.opt 文件：记录这个库的默认使用的字符集和校验规则。
- frm 文件：存储与表相关的元数据（meta）信息，包括表结构的定义信息等，每一张表都会有一个frm 文件。
- MYD 文件：MyISAM 存储引擎专用，存放 MyISAM 表的数据（data)，每一张表都会有一个 .MYD 文件。
- MYI 文件：MyISAM 存储引擎专用，存放 MyISAM 表的索引相关信息，每一张 MyISAM 表对应一个 .MYI 文件。
- ibd文件和 IBDATA 文件：存放 InnoDB 的数据文件（包括索引）。InnoDB 存储引擎有两种表空间方式：独享表空间和共享表空间。独享表空间使用 .ibd 文件来存放数据，且每一张 InnoDB 表对应一个 .ibd 文件。共享表空间使用 .ibdata 文件，所有表共同使用一个（或多个，自行配置）.ibdata 文件。
- ibdata1 文件：系统表空间数据文件，存储表元数据、Undo日志等。
- ib_logfile0、ib_logfile1 文件：Redo log 日志文件。
pid 文件
pid 文件是 mysqld 应用程序在 Unix/Linux 环境下的一个进程文件，和许多其他 Unix/Linux 服务端程序一样，它存放着自己的进程 id。
socket 文件
socket 文件也是在 Unix/Linux 环境下才有的，用户在 Unix/Linux 环境下客户端连接可以不通过 TCP/IP 网络而直接使用 Unix Socket 来连接 MySQL。

69、undo log、redo log、 bin log 的作用是什么？

undo log 基本概念

undo log是一种用于撤销回退的日志，在数据库事务开始之前，MySQL 会先记录更新前的数据到 undo log 日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log 来进行回退。
Undo Log产生和销毁：Undo Log 在事务开始前产生；事务在提交时，并不会立刻删除 undo log，innodb 会将该事务对应的 undo log放入到删除列表中，后面会通过后台线程 purge thread 进行回收处理。

注意: undo log 也会产生 redo log，因为 undo log 也要实现持久性保护。

undo log 的作用

提供回滚操作【undo log 实现事务的原子性】
在数据修改的时候，不仅记录了 redo log，还记录了相对应的 undo log，如果因为某些原因导致事务执行失败了，可以借助 undo log进行回滚。
undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当 delete 一条记录时，undo log 中会记录一条对应的 insert 记录，反之亦然，当 update 一条记录时，它记录一条对应相反的 update 记录。
提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC） 】
MVCC，即多版本控制。在 MySQL 数据库 InnoDB 存储引擎中，用 undo Log 来实现多版本并发控制（MVCC）。当读取的某一行被其他事务锁定时，它可以从 undo log 中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据【快照读】。

redo log 基本概念

InnoDB 引擎对数据的更新，是先将更新记录写入 redo log 日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少 IO 操作的频率，提升数据刷新的效率。
redo log：被称作重做日志, 包括两部分：一个是内存中的日志缓冲： redo log buffer，另一个是磁盘上的日志文件： redo log file 。

redo log的作用

mysql 每执行一条 DML 语句，先将记录写入 redo log buffer 。后续某个时间点再一次性将多个操作记录写到 redo log file 。当故障发生致使内存数据丢失后，InnoDB 会在重启时，经过重放 redo，将 Page 恢复到崩溃之前的状态 通过 Redo log 可以实现事务的持久性。

bin log 基本概念

binlog 是一个二进制格式的文件，用于记录用户对数据库更新的 SQL 语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。
binlog在MySQL的Server层实现(引擎共用)
binlog为逻辑日志,记录的是一条SQL语句的原始逻辑
- binlog不限制大小,追加写入,不会覆盖以前的日志.
- 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

bin log 的作用

主从复制：在主库中开启 Binlog 功能，这样主库就可以把 Binlog 传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
数据恢复：通过mysqlbinlog工具来恢复数据。

70、redo log 与 undo log 的持久化策略？

redo log持久化

缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统缓冲区( OS Buffer )。因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer，然后再通过系统调用 fsync() 将其刷到 redo log file.

Redo Buffer 持久化到 redo log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

参数值	含义
0 (延迟写)	事务提交时不会将 `redo log buffer`中日志写入到 `os buffer`，而是每秒写入 `os buffer`并调用 `fsync()`写入到 `redo log file`中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
1 (实时写,实时刷)	事务每次提交都会将 `redo log buffer`中的日志写入 `os buffer`并调用 `fsync()`刷到 `redo log file`中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
2 (实时写, 延时刷)	每次提交都仅写入到 `os buffer`，然后是每秒调用 `fsync()`将 `os buffer`中的日志写入到 `redo log file`。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据

undo log持久化

MySQL中的Undo Log严格的讲不是Log，而是数据，因此他的管理和落盘都跟数据是一样的：

Undo的磁盘结构并不是顺序的，而是像数据一样按Page管理
Undo写入时，也像数据一样产生对应的Redo Log (因为undo也是对页面的修改，记录undo这个操作本身也会有对应的redo)。
Undo的Page也像数据一样缓存在Buffer Pool中，跟数据Page一起做LRU换入换出，以及刷脏。Undo Page的刷脏也像数据一样要等到对应的Redo Log 落盘之后

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

71、bin log 与 undo log 的区别？

1）redo log 是 InnoDB 引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

2）redo log是物理日志，记录的是“在XXX数据页上做了XXX修改”；binlog 是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句。

物理日志: 记录的是每一个page页中具体存储的值是多少，在这个数据页上做了什么修改. 比如: 某个事物将系统表空间中的第100个页面中偏移量为1000处的那个字节的值1改为2.
逻辑日志: 记录的是每一个page页面中具体数据是怎么变动的，它会记录一个变动的过程或SQL语句的逻辑, 比如: 把一个page页中的一个数据从1改为2，再从2改为3,逻辑日志就会记录1->2,2->3这个数据变化的过程.

3）redo log是循环写的，空间一定会用完，需要write pos和check point搭配；binlog是追加写，写到一定大小会切换到下一个，并不会覆盖以前的日志

Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

write pos: 表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；
check point: 表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即 write pos -> checkpoint 之间的部分是redo log空着的部分，用于记录新的记录，checkpoint -> write pos 之间是redo log 待落盘的数据修改记录
如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

3）Redo Log 作为服务器异常宕机后事务数据自动恢复使用，Binlog 可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力

CrashSafe指MySQL服务器宕机重启后，能够保证：

所有已经提交的事务的数据仍然存在。
所有没有提交的事务的数据自动回滚。

72、MySQL 的 binlog 有几种日志格式？分别有什么区别？

binlog日志有三种模式

1）ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function无法被正确复制的问题。
缺点：批量操作，会产生大量的日志，尤其是alter table会让日志量暴涨。

2）STATMENT（statement-based replication, SBR）：记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

优点：日志量小，减少磁盘IO，提升存储和恢复速度
缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

3）MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用 STATEMENT 模式保存 binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

企业场景如何选择binlog的模式

如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement。
如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式；但是要注意，该模式的binlog日志量增长非常快.

73、MySQL 线上修改大表结构有哪些风险？

在线修改大表的可能影响

在线修改大表的表结构执行时间往往不可预估，一般时间较长。
由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作。
如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入。
修改大表结构容易导致数据库 CPU、IO 等性能消耗，使 MySQL 服务器性能降低。
在线修改大表结构容易导致主从延时，从而影响业务读取。

修改方式：

对表加锁(表此时只读)
复制原表物理结构
修改表的物理结构
把原表数据导入中间表中，数据同步完后，**锁定中间表，并删除原表
rename中间表为原表
刷新数据字典，并释放锁

使用工具： online-schema-change ，是 percona 推出的一个针对 mysql 在线 ddl 的工具。percona 是一个mysql分支维护公司，专门提供mysql技术服务的。

74、count(列名)、count(1) 和 count(*) 有什么区别?

进行统计操作时,count 中的统计条件可以三种选择:

EXPLAIN  SELECT COUNT(*) FROM user;

EXPLAIN  SELECT COUNT(列名) FROM user;

EXPLAIN  SELECT COUNT(1) FROM user;

执行效果上：

count(*) 包括了所有的列,在统计时不会忽略列值为null的数据。
count(1) 用1表示代码行,在统计时,不会忽略列值为null的数据。
count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计。

执行效率上：

InnoDB引擎：count（字段) < count(1) = count(*)
- InnoDB通过遍历最小的可用二级索引来处理select count(*) 语句，除非索引或优化器提示指示优化器使用不同的索引。如果二级索引不存在，则通过扫描聚集索引来处理。
- InnoDB已同样的方式处理count(1)和count(*)
MyISAM引擎：count（字段) < count(1) <= count(*)
- MyISAM存储了数据的准确行数，使用 count(*)会直接读取该行数，只有当第一列定义为NOT NULL时，count（1），才会执行该操作，所以优先选择 count(*)
count(列名) 会遍历整个表，但不同的是，它会先获取列，然后判断是否为空，然后累加，因此count(列名)性能不如前两者。

注意：count(*)，这是 SQL92 定义的标准统计行数的语法，跟数据库无关，与NULL也无关。而count(列名) 是统计列值数量，不计NULL，相同列值算一个。

75、什么是分库分表？什么时候进行分库分表？

什么是分库分表

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。

分库分表解决的问题

分库分表的目的是为了解决由于数据量过大而导致数据库性能降低的问题，将原来单体服务的数据库进行拆分.将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。
什么情况下需要分库分表
- 单机存储容量遇到瓶颈.
- 连接数,处理能力达到上限.

注意:

分库分表之前,要根据项目的实际情况确定我们的数据量是不是够大,并发量是不是够大,来决定是否分库分表.

数据量不够就不要分表,单表数据量超过1000万或100G的时候, 速度就会变慢(官方测试),

分库分表包括：垂直分库、垂直分表、水平分库、水平分表四种方式。

垂直分库

数据库中不同的表对应着不同的业务，垂直切分是指按照业务的不同将表进行分类,分布到不同的数据库上面
- 将数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果

垂直分表

表中字段太多且包含大字段的时候，在查询时对数据库的IO、内存会受到影响，同时更新数据时，产生的binlog文件会很大，MySQL在主从同步时也会有延迟的风险

将一个表按照字段分成多表，每个表存储其中一部分字段。
对职位表进行垂直拆分, 将职位基本信息放在一张表, 将职位描述信息存放在另一张表

垂直拆分带来的一些提升
- 解决业务层面的耦合，业务清晰
- 能对不同业务的数据进行分级管理、维护、监控、扩展等
- 高并发场景下，垂直分库一定程度的提高访问性能
垂直拆分没有彻底解决单表数据量过大的问题

水平分库

将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈.
简单讲就是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面, 例如将订单表按照id是奇数还是偶数, 分别存储在不同的库中。

水平分表

针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。
总结
- 垂直分表: 将一个表按照字段分成多表，每个表存储其中一部分字段。
- 垂直分库: 根据表的业务不同,分别存放在不同的库中,这些库分别部署在不同的服务器.
- 水平分库: 把一张表的数据按照一定规则,分配到不同的数据库,每一个库只有这张表的部分数据.
- 水平分表: 把一张表的数据按照一定规则,分配到同一个数据库的多张表中,每个表只有这个表的部分数据.

76、说说 MySQL 的主从复制？

主从复制的用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

主从部署必要条件

主库开启 binlog 日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

主从复制的原理

Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

主库db的更新事件(update、insert、delete)被写到binlog
主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动并发起连接，连接到主库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内容写入到slave的db

77、说一下 MySQL 执行一条查询语句的内部执行过程？

①建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。
通讯机制：
- 全双工：能同时发送和接收数据，例如平时打电话。
- 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机
- 单工：只能发送数据或只能接收数据。例如单行道
线程状态：
show processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的
- id：线程ID，可以使用kill xx；
- user：启动这个线程的用户
- Host：发送请求的客户端的IP和端口号
- db：当前命令在哪个库执行
- Command：该线程正在执行的操作命令
  - Create DB：正在创建库操作
  - Drop DB：正在删除库操作
  - Execute：正在执行一个PreparedStatement
  - Close Stmt：正在关闭一个PreparedStatement
  - Query：正在执行一个语句
  - Sleep：正在等待客户端发送语句
  - Quit：正在退出
  - Shutdown：正在关闭服务器
- Time：表示该线程处于当前状态的时间，单位是秒
- State：线程状态
  - Updating：正在搜索匹配记录，进行修改
  - Sleeping：正在等待客户端发送新请求
  - Starting：正在执行请求处理
  - Checking table：正在检查数据表
  - Closing table : 正在将表中数据刷新到磁盘中
  - Locked：被其他查询锁住了记录
  - Sending Data：正在处理Select查询，同时将结果发送给客户端
- Info：一般记录线程执行的语句，默认显示前100个字符。想查看完整的使用show full processlist;
②查询缓存（Cache&Buffer），这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。
- 缓存Select查询的结果和SQL语句
- 执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
- 即使开启查询缓存，以下SQL也不能缓存
  - 查询语句使用SQL_NO_CACHE
  - 查询的结果大于query_cache_limit设置
  - 查询中有一些不确定的参数，比如now()
- show variables like ‘%query_cache%’; //查看查询缓存是否启用，空间大小，限制等
- show status like ‘Qcache%’; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等
③解析器（Parser）将客户端发送的 SQL 进行语法解析，生成”解析树”。预处理器根据一些 MySQL 规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。
④查询优化器（Optimizer）根据“解析树”生成最优的执行计划。MySQL 使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。
- 等价变换策略
  - 5=5 and a>5 改成 a > 5
  - a < b and a=5 改成b>5 and a=5
  - 基于联合索引，调整条件位置等
- 优化count、min、max等函数
  - InnoDB引擎min函数只需要找索引最左边
  - InnoDB引擎max函数只需要找索引最右边
  - MyISAM引擎count(*)，不需要计算，直接返回
- 提前终止查询
  - 使用了limit查询，获取 limit 所需的数据，就不在继续遍历后面数据
- in的优化
  - MySQL 对 in 查询，会先进行排序，再采用二分法查找数据。比如 where id in (2,1,3)，变成 in (1,2,3)
⑤查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将 SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。
- 如果开启了查询缓存，先将查询结果做缓存操作
- 返回结果过多，采用增量模式返回

78、Mysql 内部支持缓存查询吗？

使用缓存的好处：当 MySQL 接收到客户端的查询 SQL 之后，仅仅只需要对其进行相应的权限验证之后，就会通过 Query Cache 来查找结果，甚至都不需要经过 Optimizer 模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互.

mysql 5.7 支持内部缓存，8.0 之后已废弃

mysql 缓存的限制

mysql 基本没有手段灵活的管理缓存失效和生效，尤其对于频繁更新的表
SQL 必须完全一致才会导致 cache 命中
为了节省内存空间，太大的 result set 不会被 cache (< query_cache_limit)；
MySQL 缓存在分库分表环境下是不起作用的；
执行 SQL 里有触发器,自定义函数时，MySQL 缓存也是不起作用的；
在表的结构或数据发生改变时，基于该表相关 cache 立即全部失效。

替代方案

应用层组织缓存，最简单的是使用 redis，ehcached 等

79、ORM 可以防止 SQL 注入攻击吗?

ORM（对象关系映射）框架本身并不能完全防止 SQL 注入攻击，但它可以在一定程度上减少 SQL 注入的风险。下面是一些ORM框架如何帮助减少SQL注入攻击的方式：

参数化查询：ORM 框架通常会使用参数化查询来执行数据库操作，而不是直接将用户输入的数据拼接到SQL语句中。参数化查询可以将用户输入的数据作为参数传递给SQL语句，而不是将其直接嵌入到 SQL 语句中，从而避免了 SQL 注入攻击。
自动转义：一些 ORM 框架会自动对用户输入的数据进行转义，以确保输入数据中的特殊字符不会被误认为是SQL语句的一部分。这样可以防止恶意的SQL注入攻击。
输入验证和过滤：ORM 框架通常提供了输入验证和过滤的功能，可以对用户输入的数据进行检查和过滤，以确保输入的数据符合预期的格式和类型。这样可以防止一些常见的SQL注入攻击。

尽管 ORM 框架可以提供一些保护机制来减少SQL注入攻击的风险，但仍然需要开发人员自己保证应用程序的安全性。开发人员应该遵循安全的编码实践，对用户输入的数据进行充分验证和过滤，以防止 SQL 注入攻击。