MYSQL面试版

一部分来源于公众号JavaGuide，一部分来源于视频：

B站：视频1 视频2

一、MySQL架构

1. MySQL 基本架构 = Server层+存储引擎

Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图，函数等，还有一个通用的日志模块 binglog 日志模块。

存储引擎： 主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。

server层

• 连接器： 身份认证和权限相关(登录 MySQL 的时候)。

• 查询缓存： 执行查询语句的时候，会先查询缓存（移除），以 Key-Value 的形式缓存在内存中，Key 是查询预计，Value 是结果集。

• 分析器： 没有命中缓存的话，SQL 语句就会经过分析器。

  • 词法分析：SQL语句有多个字符串组成。首先要提取关键字，比如 select，提出查询的表，提出字段名，提出查询条件等等。
  • 语法分析：判断sql语句是否符合MySQl的语法。

• 优化器： 按MySQL 认为最优的方案去执行(可能不优)，比如多个索引的时候该如何选择索引，多表查询的时候如何选择关联顺序。

• 执行器： 执行语句，首先执行前会校验该用户有没有权限，有权限，就会去调用存储引擎的接口，返回接口执行的结果。

• 日志模块： binlog日志模块所有执行引擎都可以共用，InnoDB多一个redolog模块（更新操作）。

• binlog（归档日志）： 一个无限大小，“追加写”，记录的是逻辑操作的日志，如：记录了“id=2的记录的c加上1”。

  • redo log（物理日志）： 固定大小，“循环写”，记录物理日志即记录了这个数据页 “做了什么改动”。

身份认证：

​ Mysql的客户端 如Navcat 键入账号密码–>MySql去系统的用户表中验证，验证成功后将用户权限加载到内存， 每次执行操作都要去内存验证用户是否有该权限。如果已经建立连接后，修改用户权限时是不会更新内存中的用户权限，只有重新连接才会生效

缓存设置

• query_cache_type=0 关闭缓存

• query_cache_type=1打开缓存

• query_cache_type=2按需缓存：按需使用缓存:select SQL_CACHE * from test;

查看缓存运行状态：

show status like '%Qcache%'

查询/更新语句分析（如何在SQL执行的）

查询语句执行流程如上。

更新语句执行流程多了日志记录模块。

update tb_student A set A.age='19' where A.name=' 张三 ';

执行更新的时候肯定要记录日志，MySQL 自带的日志模块式 ：

• 先查询到张三这一条数据，如果有缓存，也是会用到缓存。
• 然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后通知执行器，执行器收到通知后先记录 binlog，后调用存储引擎接口，提交 redo log 为commit 状态。
• 更新完成。

为什么InnoDB要用两个日志模块，用一个日志模块不行吗?(有机会深入)

为什么要 redo log物理日志

MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用来归档，而InnoDB是第三方公司以插件的形式引入MySQL的，因为只依靠binlog是没有crash-safe的能力，所以要使用InnoDB的日志系统redo log。

为什么要同时用两个

InnoDB 引擎就是通过 redo log 来支持事务的，binlog日志只能用来归档和主从，只用一个会出现数据一致性的问题。

二、数据结构

1. B-Tree与B+Tree

B-Tree：

• 非叶子节点存储data指针，所有索引元素不重复

• 节点中的数据索引从左到右递增排列 (data 指向表存储的地址)

• 叶节点的指针为空
  

B+ Tree

• 非叶子节点不存储data指针，索引元素存在重复

• 叶子节点包含所有索引字段

• 叶子节点用指针连接，提高区间访问的性能
  

选择B+树的原因：

磁盘读写代价低： B+树的非叶子节点不存储data指针，所以节点大小比B树小，假设一页存储的是16KB，单个非叶子节点越小，其页上存储的节点数越多，导致树的高度越低，磁盘读写代价越低。

B+树的查询效率更加稳定： 由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

B+树区间查询效率更高： 由于B+树的数据都存储在叶子结点中，叶子节点间存在双向指针连接，方便范围查找(扫库)。但是B树因为其分支结点同样存储着数据，我们要找到具体范围的数据，需要进行一次中序遍历按序来扫。

为什么索引结构默认使用B-Tree，而不是hash，二叉树，红黑树？

hash： 虽然可以快速定位，适合单记录查询。但是没有顺序，范围查找，IO复杂度高。

二叉树： 树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。

红黑树： 树的高度随着数据量增加而增加，IO代价高。

字符集及校对规则

字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每一种字符集都会对应一系列的校对规则。

MySQL采用的是类似继承的方式指定字符集的默认值。比如：为数据库所指定的字符集则库中所有表的默认字符集同数据库。

三、存储引擎

​ MyISAM性能极佳，但比如用到了聚簇索引，或者需要访问的数据都可以放入内存的应用时，InnoDB的访问速度比MyISam快！

InnoDB与MyISAM两者的对比：

1. 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
2. 是否支持事务和崩溃后的安全恢复：
   • MyISAM 强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。
   • InnoDB 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
3. 是否支持外键： MyISAM不支持，而InnoDB支持。
4. 是否支持MVCC (多版本并发控制)：仅 InnoDB 支持，应对高并发事务, MVCC比单纯的加锁更高效。
5. 索引方式： InnoDB聚簇索引与MyISAM非聚簇索引

四、索引

​ MySQL索引使用的数据结构主要有B+Tree索引 和 哈希索引 。

​ 哈希索引：底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候(不支持范围查找)，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择B+Tree索引。

B+树索引

MyISAM的B+树索引结构（非聚簇索引）

​ B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址（数据表的物理地址）读取相应的数据记录。非叶子节点也存data指针的！

InnoDB的数据结构（聚簇索引）

​ 树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时(辅助索引的data域存储相应记录主键的值而不是地址)，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

主键索引（聚集索引）：

叶节点包含了完整的数据记录，而MyISAM存储引擎存放的是数据表的物理地址。

辅助(普通)索引： 例如查找到Alice所在的记录，先通过名字的辅助索引找到alice对应的主键值，在通过主键值查找一次主键索引，得到该记录的所有数据。

为什么建议InnoDB表必须建主键,并且推荐使用整型的自增主键?

主键： 聚簇索引默认主键索引，如果没设置主键，则MySQL会查找每一列，找到一列数据都不重复，该列设置定义为“主键”，进行B+树的构建。若每列数据都存在重复的，则MySql会自动帮我们建一列（rowId），作为“主键”构建B+树。

推荐整数自增：

1. 字符串比大小效率低于整型、整型占用的空间资源少（生产环境中 MySQl物理表是存在SSD中的）。

2. 自增排序：聚簇索引的数据的物理存放顺序与索引顺序是一致的(只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的)，如果不设置自增，在构建B+树时，会导致非叶子节点分裂，则需要不断的调整数据的物理地址、分页，影响性能。

联合索引-最左前缀原则

原理：最左前缀原理（第一个字段能够排好序了，就直接排序，遇到相等的字段，则按照第二个排序字段排序，以此类推）

单独看某一个地段，只有name第一个从左到右是有序的，其他的字段不一定有序！！！。

五、MySQL事务

5.1 事务的执行 begin-commit-rollback

1. 事务自动提交的属性

show VARIABLES LIKE 'autocommit' # 默认开启

2. 事务的手动提交

BEGIN
# xxxxx
COMMIT

只要没有执行commit(提交事务)，数据库表数据并不会被更新。但是自己可以在内存中查看到数据的变化，只是其他连接用户查看不到。

3. 事务的隐式提交（了解）

当你处理执行更新操作，并没有提交事务时，此时有执行DDL操作（表结构的更新）等等，则MySQL会自动帮你提交事务。

4. 事务回滚ROLLBACK：在手动commit时可以回滚修改，恢复原来操作

BEGIN
# xXXXX
ROLLBACK

5. 保存点：（了解）

设置保存点，回滚时会回滚到保存点（保存你的SQL语句快照）

ROLLBACK [WORK] TO [SAVEPOINT]

5.2 事务的四大特性（ACID）

原子性： 转账操作不可分割，转账要么成功要么失败

一致性： 执行事务前后，数据保持一致。小红向小白转账，银行账户的总额还是不变的。

隔离性： 各个事务场景间相互独立。

持久性： 转账记录要永久保存，即使数据库发生故障也不应该对其有任何影响。

5.3 并发事务带来哪些问题?

• 脏读（Dirty read）: 一个未提交的事务读取到另一个未提交事务修改的数据。

• 丢失修改（Lost to modify）: 一个未提交的事务修改某一个数据后，该数据又被另一个事务修改。

• 不可重复读（Unrepeatableread）: 一个未提交事务在第二次读取某一个数据前，该数据被另一个事务修改，导致两次读取数据不一致。

• 幻读（Phantom read）: 幻读与不可重复读类似。一个未提交事务在第二次读取多行数据前，该多行数据被另一个事务增加或者删除一行，导致两次读取数据不一致。

5.4 事务隔离性

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

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

这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读）事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经达到了 SQL标准的**SERIALIZABLE(可串行化)隔离级别，但在 分布式事务 的情况下一般会用到SERIALIZABLE(可串行化)**隔离级别。

设置隔离级别：

set session transaction isolation level read uncommited

1. 读未提交（用的不多，有点违背事务的特点）

一个未提交事务可以读到还未提交事务的数据，会出现脏读。

实现： 最简单，改了就是改了别人可以直接读到。

2. 读已提交（解决脏读）

一个未提交事务只能读取到另一个已提交事务修改过的数据。但是还未提交，该数据又被另一个已提交事务修改数据，导致不可重复读、幻读。

解决脏读的实现原理(版本链)：

数据表存在三个隐藏列：

• row_id：唯一标识一条记录，存在主键，则不需要生成。
• transaction_id：事务id，每次对记录进行改动时，会设置新的事务ID。
• roll_pointer：回滚指针，指向前一个事务ID，通过该指针可以记录之前修改的信息。

版本链：

修改 1->2->3->4 改动到4时，事务Id不断增大，回滚指针指向前一个事务Id

原理：

通过读视图(readview)来判断，当select时会隐藏的带上当前未提交的事务的id ，所有活跃的事务id的数据都不能读

select readview m_ids:[81,82,200] # 所以A读到事务Id=80的记录
# 若此时B事务提交了，A事务再次查找则会
select readview m_ids:[81,82] # A直接取最大事务id=200的数据。	

**3.可重复度（**解决不可重复读）

未提交事务对同一字段的多次读取结果都是一致的，但会出现幻读

解决不可重复读的原理(多版本并发控制 MVCC)

# A第一次查询
select readview m_ids:[81,82,200] #所以A或读到事务Id=80的记录
# 若此时B事务id=200提交修改
# A事务再次读取时，复用第一次的readview id，从而解决不可重复读的问题
select readview m_ids:[81,82,200]

MVCC： 可以是不同事务的读写操作并发执行。读已提交的Readview在每一次查询时都会生成一个Readview，可重复读在第一次查询时生成，之后的查询复用第一次生成的Readview。

4.串行化

不允许读写并发操作，对每一条操作都是串行的，所以不会出现脏读、幻读现象。

六、锁机制

6.1 锁机制与InnoDB锁算法

MyISAM和InnoDB存储引擎使用的锁：

• MyISAM采用表级锁(table-level locking)。
• InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

表级锁和行级锁对比：

• 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁。资源消耗也比较少，加锁快，不会出现死锁。触发锁冲突的概率最高，并发度最低。
• 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的锁冲突。并发度高，加锁慢，会出现死锁。

6.2 读写锁

读锁(共享锁 shared lock=> S锁)： 查到到数据后添加读锁，运行其他事务继续添加读锁，但是不能获得该记录的写锁。

使用场景：读出数据后，其他事务不能修改。

select * from t1 where a = 1 lock in share;

写锁(排他锁 Exclusive lock=>X锁)： 其他人不能更新，也不能读它。

删除：先对记录加写锁，再执行删除操作。

插入：插入记录时，会先加隐式锁(给插入的事务保存本次事务id，其他事务获取的事务id错误则)来保护新插入的数据在本事务未提交前不被其他事务访问到。

更新：如果更新的列导致存储空间没有发生变化，则给记录加写锁后进行修改，如果存储空间发生变化，会给记录加写锁，之后执行删除、插入操作。

select * from t1 where a = 1 for update;

若A对数据加写锁、B也要加写锁，但是B被A的锁给阻塞，但是C通过简单的sql语句，不带锁则会直接读取数据，不被锁影响。

6.3 行锁

InnoDB存储引擎的锁的算法有三种：

• Record lock：单个行记录上的锁
• Gap lock：间隙锁，锁定一个范围，不包括记录本身，为了防止同一事务的两次当前读，但会出现幻读的情况。
• Next-key lock：record+gap 锁定一个范围，包含记录本身，主要解决幻读问题。

隔离级别：读已提交时：

select 语句加for update(写锁 记录锁record lock)：对于没有查询出来的数据，不会加锁

但可以正常插入

隔离级别：可重复读时

普通索引：对于插入是无法插入的。

在e=‘b’ 设置写锁，其实在该隔离机制下会对e='b’设置行锁的间隙锁（gap lock），所以插入的内容有e='b’是被锁定阻塞的。

**间隙加锁（e='b’和其上下行都被加锁！）：就是为了防止幻读。**因为你的插入提交被阻塞了，则行数不会变化，就不会出现幻读的现象。

范围的a>1 则改范围以内的都会加间隙锁。

走全表扫描：（表锁）

如果没走索引，则整个表的所有行都会被锁住==》 为了防止幻读。

反例：假设查找a>1，若你在某一些行加写锁，其他行可以自由修改。若其他行本来a=-1被你改成a=2，则查询a>1就会出现幻读现象。

七、性能优化

7.1 查询缓存（不实用）

执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用

开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果也不会被缓存。

缓存建立之后，表（数据或结构）发生变化，那么和这张表相关的所有缓存数据失效。

缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 （还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存 select sql_no_cache count(*) from usr;）

阿里巴巴手册-泰山版 公司查表高效：百分之95%是单表查询

7.2 大表优化

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

1. 查询-限定数据范围

我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

2. 读/写分离

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

3. 垂直分区

垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

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

4. 水平分区

保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库(分表、分库)，达到了分布式的目的。

分表： 仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义。

分库： 支持非常大的数据量存储，应用端改造也少， 但分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度。

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

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

方案

链接

• 方案一： 优化现有MySQL数据库。优点：不影响现有业务，源程序不需要修改代码，成本最低。缺点：有优化瓶颈，数据量过亿就玩完了。
• 方案二： 升级数据库类型，换一种100%兼容MySQL的数据库。优点：不影响现有业务，源程序不需要修改代码，你几乎不需要做任何操作就能提升数据库性能，缺点：多花钱
• 方案三： 一步到位，大数据解决方案，更换newSQL/noSQL数据库。优点：没有数据容量瓶颈，缺点：需要修改源程序代码，影响业务，总成本最高。

SQL语句执行得很慢的原因有哪些？

分类讨论

一条 SQL 语句执行的很慢，那是每次执行都很慢呢？还是大多数情况下是正常的，偶尔出现很慢呢？所以我觉得，我们还得分以下两种情况来讨论。

1、大多数情况是正常的，只是偶尔会出现很慢的情况。

2、在数据量不变的情况下，这条SQL语句一直以来都执行的很慢。

1. 偶尔很慢的情况

（1）数据库在刷新脏页（内存数据页与磁盘数据页内容不一致）

• redo log 日志满时，无法等待空闲时同步磁盘，而是暂停其他操作，先进行持久化。（当我们要往数据库更新一条数据的时候，数据库会在内存中把对应字段的数据更新了，但不会马上同步到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。）
• 内存不够用，当查询的数据不在内存中时，需要申请内存，而内存不够时，需要淘汰一些内存的数据页，该数据页是干净页直接淘汰，是脏页需要刷新脏页。
• MySQL正常关闭的时候，内存的脏页都刷新到磁盘上。

（2）拿不到锁我能怎么办

拿不到锁，只能慢慢等待别人释放锁（show processlist 查看当前是否在等待锁）

2. 一直都这么慢的情况

（1）没用到索引。c字段没设置索引

select * from t where 100 <c and c < 100000;

（2）设置索引，但索引不生效：字段的左边做了运算或调用函数

select * from t where c - 1 = 1000;
select * from t where pow(c,2) = 1000;

（3）设置索引，但数据库选错是否走索引

select * from t where 100 < c and c < 100000;

c设置了索引，但是c是非主键索引，所以需要走两次的索引树，若全表都满足100<c<100000则走索引效率更低！ 索引数据库很聪明的使用抽样检测是否走索引，但是抽样检测存在检测出错（show index from t ； analyze table t 重写抽样检测），导致选错走了索引，效率变慢。

抽样检测：依据 索引的区分度(基数)： 基数越大，以为着100<c<10000这个条件的函数越少，则数据分散，走索引优势更大。

cesslist 查看当前是否在等待锁）

2. 一直都这么慢的情况

（1）没用到索引。c字段没设置索引

select * from t where 100 <c and c < 100000;

（2）设置索引，但索引不生效：字段的左边做了运算或调用函数

select * from t where c - 1 = 1000;
select * from t where pow(c,2) = 1000;

（3）设置索引，但数据库选错是否走索引

select * from t where 100 < c and c < 100000;

c设置了索引，但是c是非主键索引，所以需要走两次的索引树，若全表都满足100<c<100000则走索引效率更低！ 索引数据库很聪明的使用抽样检测是否走索引，但是抽样检测存在检测出错（show index from t ； analyze table t 重写抽样检测），导致选错走了索引，效率变慢。

抽样检测：依据 索引的区分度(基数)： 基数越大，以为着100<c<10000这个条件的函数越少，则数据分散，走索引优势更大。