大三后端暑期实习面经总结——MySQL篇_小言_互联网的博客

大三后端暑期实习面经总结——MySQL篇

2021-05-30 01:43 383人阅读评论(0)

博主现在大三在读，从三月开始找暑期实习，暑假准备去tx实习啦！总结下了很多面试真题，希望能帮助正在找工作的大家！相关参考都会标注原文链接，尊重原创！

1. mysql索引数据结构

索引是一种建立在特定列上的数据结构，索引的原理是把无序的数据变为有序的查询

mysql索引的数据结构是树，常用的存储引擎innodb采用的是B+树
非关系型数据库MongoDB使用B树作为数据库索引

B-Tree 是最常用的用于索引的数据结构。因为它们是时间复杂度低，查找、删除、插入操作都可以可以在对数时间内完成。另外一个重要原因存储在B-Tree中的数据是有序的。

索引为什么没有使用二叉树来实现呢？

其实从算法逻辑上讲，二叉查找树的查找速度和比较次数都是最小的，但是从Mysql的角度讲，我们不得不考虑一个现实问题：磁盘IO。

查找都是索引操作，一般来说索引非常大，尤其是关系型数据库这种，当数据量比较大的时候，索引的大小有可能几个G甚至更多，数据量大的索引能达到亿级别，所以为了减少内存的占用，数据库索引是存储在外部磁盘上的。

当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载每个磁盘页，磁盘页对应索引树的节点。

那么Mysql衡量查询效率的标准就是磁盘IO次数。

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的。

那么为了提高查询效率，就需要减少磁盘IO数。为了减少磁盘IO的次数，就需要尽量降低树的高度，需要把原来“瘦高”的树结构变的“矮胖”，树的每层的分叉越多越好，因此B树正好符合我们的要求，这也是B-树的特征之一。

原来的二叉树一个节点只存储一个数据，要想把它变“矮胖”，就需要在一个节点存储多个数据，同时为了查找必须保持节点结构的有序，这样B树就应运而生了。

B-树（B类树）的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数。

B-树是一种多路平衡查找树，它的每一个节点最多包含K个孩子，k称为B树的阶。k的大小取决于磁盘页的大小。

B+树和B-树的区别

B+树中间节点没有存储数据，只有叶节点存放数据，其余节点用来索引，所以同样大小的磁盘页可以容纳更多的节点元素，而B-树是每个索引节点都会有Data域。这就意味着，数据量相同的情况下，B+树的结构比B-树更加“矮胖”，因此查询是IO次数也更少。这就决定了B+树更适合用来存储外部数据，也就是所谓的磁盘数据。

其次，B+树的查询必须最终查询到叶子节点，而B-树只要找到匹配元素即可，无论匹配元素处于中间节点还是叶子节点。

因此，B-树的查询性能并不稳定（最好情况是只查根节点，最坏情况是查到叶子节点）。而B+树每一次查找都是稳定的。

综合起来，B+树相比B-树的优势有三个：

单一节点存储更多的元素，使得查询的IO次数更少。

所有查询都要查找到叶子节点，查询性能稳定。

所有叶子节点形成有序链表，便于范围查询。

数据库索引为什么不用AVL（平衡二叉树）？

数据库索引是存储在磁盘上，磁盘IO操作比较耗时，为了提高查询效率就需要减少磁盘IO的次数。而磁盘IO次数和树的高度有关，所以为了减少磁盘IO就需要降低树的高度，这是查找的结构就可以把二叉树变成B类的树。

数据库索引为什么用B+树而不用B-树？

数据库索引采用B+树的主要原因是B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

那么MongoDB为什么使用B-树而不是B+树？

至于MongoDB为什么使用B-树而不是B+树，可以从它的设计角度来考虑，它并不是传统的关系性数据库，而是以Json格式作为存储的nosql，目的就是高性能，高可用，易扩展。首先它摆脱了关系模型，上面所述的范围查询的优点就没那么强烈了，其次Mysql由于使用B+树，数据都在叶节点上，每次查询都需要访问到叶节点，而MongoDB使用B-树，所有节点都有Data域，只要找到指定索引就可以进行访问，无疑单次查询平均快于Mysql（但侧面来看Mysql至少平均查询耗时差不多）

总体来说，Mysql选用B+树和MongoDB选用B-树还是以自己的需求来选择的

2. mysql索引类型

1️⃣ 主键索引

设定为主键后数据库会自动建立索引(innodb为聚簇索引)

2️⃣ 单值索引/单列索引/普通索引

给表中的某一个列创建索引，即一个索引只包含单个列；一个表可以有多个单列索引

3️⃣ 唯一索引

索引列的值必须唯一，但允许有空值(主键索引不允许索引列=null,唯一索引允许为null,但只能有一个null)

4️⃣ 复合索引

基于表中的多个列共同创建一个索引，即一个索引包含多个列

5️⃣ 全文索引

全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。
全文索引可以在CHAR、VARCHAR、TEXT类型(文本类型)列上创建。
MySQL5.7 前只有MYISAM引擎支持全文所以，5.7后INNODB引擎也增加了支持,但很少使用

3. 索引对数据库性能的影响

索引可以极大的提高数据的查询速度。
通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件
索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变。

4. mysql存储引擎

查看当前所使用存储引擎命令

show engines;

 show variables like '%storage_engine%';

存储引擎对比图

阿里巴巴使用的是我们使用的MySQL吗？数据引擎是INNODB吗？

不是，阿里对MySQL进行了很大的改进

5. InnoDB和MyISAM区别

事务和外键

InnoDB支持事务和外键，具有安全性和完整性，适合大量insert或update操作
MyISAM不支持事务和外键，它提供高速存储和检索，适合大量的select查询操作

锁机制

InnoDB支持行级锁，锁定指定记录。基于索引来加锁实现
MyISAM支持表级锁，锁定整张表

索引结构

InnoDB使用聚集索引（聚簇索引），索引和记录在一起存储，既缓存索引，也缓存记录
MyISAM使用非聚集索引（非聚簇索引），索引和记录分开，只缓存索引

并发处理能力

InnoDB读写阻塞可以与隔离级别有关，可以采用多版本并发控制（MVCC）来支持高并发
MyISAM使用表锁，会导致写操作并发率低，读之间并不阻塞，读写阻塞

存储文件

InnoDB表对应两个文件，一个.frm表结构文件，一个.ibd数据文件。InnoDB表最大支持64TB；
MyISAM表对应三个文件，一个.frm表结构文件，一个MYD表数据文件，一个.MYI索引文件；从MySQL5.0开始默认限制是256TB。

6. mysql体系架构

和其它数据库相比，MySQL与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。

首先，MySQL体系结构是分层的，这样的好处是出错可以去对应的层寻找问题，分门别类，提高了效率；

其次，在MySQL储引擎的架构上：插件式的存储引擎架构将査询处理和其它的系统仼务以及数据的存储提取相分离。这种架枃可以根据业务的需求和实际需要选择合适的存储引擎。

1️⃣ 连接层

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

2️⃣ 服务层

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

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

3️⃣ 存储引擎层：

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

4️⃣ 文件存储层

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

7. 聚簇索引和非聚簇的区别

首先要弄清楚，聚簇和非聚簇索引都是采用B+树作为数据结构存储

聚簇索引：将数据存储于索引放在一块，并且是按照一定的顺序组织的，找到了索引也就找到了对应的数据，数据的物理存放位置与索引顺序是一致的，即只要索引是相邻的，那么对应的数据也一定是相邻存储在磁盘上

非聚簇索引：叶子结点不存放数据，存储的是数据行地址，也就是说根据索引查找到的数据行的位置再到磁盘中找到数据，就类似于书目录，我们要找第三章第一节，首先会去目录获取页码再去指定页码寻找

# 聚簇索引的优势
1. 查询通过聚簇索引可以直接获得数据,相比非聚簇索引二次查询效率更高(非覆盖索引情况下)
2. 聚簇索引对于范围查询的效率高,因为数据是按照大小排列的,适合用于排序的场合

# 聚簇索引的劣势
1. 维护索引很昂贵,特别插入新行或者主键更新导致分页时,由于保证有序,就必须移动行数据,此时可能会造成碎片
2. 数据表如果使用UUID作为主键,会使数据存储稀疏,会出现聚簇索引查询可能比全表扫描还慢,建议使用自增int类型作为主键
3. 如果主键很大,那辅助键索引会变的更大,会导致非叶子结点占用更多的物理空间,因为辅助索引的叶子存储的是主键值

InnoDB与MyISAM的中索引的区别：

InnoDB聚簇索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次査找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值
MyISAM使用的是非聚簇索引，非聚簇索引的B+树与聚簇索引的B+树节点的结构完全一致，只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MSAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的

8. mysql索引的数据结构，各自优劣

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择B+Tree索引

B+树

B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过1，而且叶子节点间有指针相互链接。在B+树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动，效率非常高。因此，B+树索引被广泛应用于数据库、文件系统等场景。

哈希索引

哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快

如果是等值查询，那么哈希索引明显有绝对优势，因为只需要经过一次算法即可找到相应的键值；前提是键值都是唯一的。如果键值不是唯一的，就需要先找到该键所在位置，然后再根据链表往后扫描，直到找到相应的数据
如果是范围查询，这时候哈希索引就毫无用武之地了，因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引院完成范围查询检索
哈希索引也没办法利用索引完成排序，以及likexx%这样的部分模糊査迿（这种部分模糊査询，其实本质上也是范围查询）
哈希索引也不支持多列联合索引的最左匹配规则
B+树索引的关键字检索效率比较平均，不像B树那样波动幅度大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在哈希碰撞问题。

9. 索引的设计原则

查询更快、占用空间更小

适合索引的列是出现在where子句中的列，或者连接子句中指定的列
基数较小的表，索引效果较差，没有必要在此列建立索引
使用短索引，如果对长字符串列进行索引，应该指定一个前缀长度，这样能够节省大量索引空间，如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，然后检查其余行是否可能匹配
不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表內容的时候，索引会进行更新甚至重构，索引列越多，这个时间就会越长。所以只保持需要的索引有利于查询即可
定义有外键的数据列一定要建立索引
更新频繁字段不适合创建索
若是不能有效区分数据的列不适合做索引列（如性别，男女未知，最多也就三种，区分度实在太低）
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可
对于那些查询中很少涉及的列，重复值比较多的列不要建立索引
对于定义为text、image和bit的数据类型的列不要建立索引

10. mysql中锁的类型

基于锁的类型分类：共享锁、排他锁
基于锁的粒度分类：行级锁、表级锁、页级锁、记录锁、间隙锁、临键锁
基于锁的状态分类：意向共享锁、意向排他锁

共享锁(Share lock)

共享锁又称读锁，简称S锁：当一个事务为数据加上读锁之后，其他事务只能对该数据加读锁，而不能对数据加写锁，直到所有的读锁释放之后其他事务才能对其进行加持写锁。共享锁的特性主要是为了支持并发的读取数据，读取数据的时候不支持修改，避兔出现重复读的问题

排他锁(Exclusve lock)

排他锁又称写锁，简称X锁：当一个事务为数据加上写时，其他请求将不能再为数据加任何锁，直到该锁释放之后，其他事务才能对数据进行加锁。排他锁的目的是在数据修改时候，不允许其他人同时修改，也不允许其他人读取。避免了出现脏数据和脏读的问题

表锁

表锁是指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表进行访问

特点：粒度大，加锁简单，容易冲突

行锁

行锁是指上锁的时候锁住的是表的某一行或多条记录，其他事务访问同一张表时，只有被锁住的记录不能访问，其他的记录可正常访问

特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高，会出现死锁

记录锁(Record Lock)

记录锁也属于行锁中的一种，只不过记录锁的范围只是表中的某一条记录，记录锁是说事务在加锁后锁住的只是表的某一条记录，实现精准条件命中，并且命中的条件字段是唯一索引

加了记录锁之后数据可以避免数据在査询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

页锁

页级锁是Mysql中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速慢；所以取了折衷的页级，一次锁定相邻的一组记录

特点：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

间隙锁(Gap Lock)

间隙锁属于行锁中的一种，间隙锁是在事务加锁后其锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成个区间，遵循左开右闭原则

比如表里面的数据ID为1，4，5，7，10，那么会形成以下几个间隙区间，-n-1区间，1-4区间，7-10区间，10-n区间（-n代表负无穷大，n代表正无穷大）

范围査询并且查询未命中记录，査询条件必须命中索引、间隙锁只会出现在 REPEATABLE_READ（重复读）的事务级别中。
触发条件：防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。

临建锁(Next-Key Lock)

临建锁也属于行锁的一种，并且它是INNODB的行锁默认算法，总结来说它就是记录锁和间隙锁的组合，临键锁会把査询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住

触发条件：范围查询并命中，查询命中了索引。

结合记录锁和间隙锁的特性，临键锁避免了在范围査询时岀现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入

如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁，而这个状态就是意向锁。

意向共享锁

当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁

当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

11. mysql执行计划怎么看

执行计划就是sq的执行查询的顺序，以及如何使用索引查询，返回的结果集的行数；在查询语句前面加上explain可以查看执行计划

我们只需要注意一个最重要的type（ref表示用了索引)的信息很明显的提现是否用到索引

`id`: 查询序列号
`select_type`: 查询类型
`table`: 查询的表
`partitions`: 没有分区
`type`: 连接类型
`possible_keys`: 可能用到的索引
`key`: 实际用到索引
`key_len`: 索引长度
`ref`: 哪些列或常量被用于查找索引列上的值
`Extra`: 包含MySQL解决查询的详细信息

id：查询的顺序号，有几个select就显示几行，有序。id的顺序是按select出现的顺序增长的。id列的值越大执行优先级越高越先执行，id列的值相同则从上往下执行，id列的值为null最后执行。
selectType：表示查询中每个select子句的类型
- SIMPLE：表示此查询不包含UNON查询或子查询
- PRIMARY：表示此查询是最外层的查询（包含子查询）
- SUBQUERY：子查询中的第一个 SELECT
- UNON：表示此查询是UNON的第二或随后的查询
- DEPENDENT UNION：UNON中的第二个或后面的查询语句，取决于外面的查询
- UNION RESULT，UNON的结果
- DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询。即子查询依赖于外层查询的结果
- DERIVED：衍生，表示导出表的 SELECT（FROM子句的子查询）
table：表示该语句查询的表
type：优化sql的重要字段，也是我们判断sql性能和优化程度重要指标。他的取值类型范围：
- const：通过索引一次命中，匹配一行数据
- system：表中只有一行记录，相当于系统表
- eq_ref：唯一性索引归扫描，对于每个索引键，表中只有一条记录与之匹配
- ref：非唯一性索引扫描，返回匹配某个值的所有
- range：只检索给定范围的行，使用一个索引来选择行，一般用于between、<、>
- index：只遍历索引树
- ALL：表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本就是随着表的数量增多，执行效率越慢
执行效率：ALL<index<range<ref<eq_ref<const<system，最好是避免ALL和index
possible_keys：它表示Mysql在执行该sql语句的时候，可能用到的索引信息，仅仅是可能，实际不一定会用到
key：此字段是mysql在当前查询时所真正使用到的索引。他是possible_keys的子集
key_len：表示查询优化器使用了索引的字节数，这个字段可以评估组合索引是否完全被使用，这也是我们优化sql时，评估索引的重要指标
rows：mysql查询优化器根据统计信息，估算该sql返回结果集需要扫描读取的行数，这个值相关重要，索引优化之后，扫描读取的行数越多，说明索引设置不对，或者字段传入的类型之类的问题，说明要优化空间越大
filtered：返回结果的行占需要读到的行（rows列的值）的百分比，就是百分比越高，说明需要查询到数据越准确，百分比越小，说明查询到的数据量大，而结果集很少
extra
- using filesort：表示mysql对结果集进行外部排序，不能通过索引顺序达到排序效果。一般有using filesort都建议优化去掉，因为这样的查询cpu资源消耗大，延时大
- using index：覆盖索引扫描，表示査询在索引树中就可査找所需数据，不用扫描表数据文件，往往说明性能不错
- using temporary：查询有使用临时表，一般出现于排序，分组和多表join的情况，查询效率不高，建议优化
- using where：sql使用了where过滤，效率较高

12. sql语句的执行流程

查询语句的执行分为以下几步：

查询缓存
解析器生成解析树
预处理再次生成解析树
查询优化器
查询执行计划
查询执行引擎
查询数据返回结果

13. 数据库三大范式

规范化理论：改造关系模式，通过分解关系模式来消除其中不合适的数据依赖，以解决插入异常、删除异常、更新异常和数据冗余的问题。

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定规范化理论。在关系型数据库中这种规则就称为范式

三大范式的通俗理解

如果一个关系模式R的所有属性都是不可分的数据项，则R属于第一范式
如果关系模式R属于第一范式，且每一个非主属性完全函数依赖于码，则R属于第二范式
若关系模式R属于第二范式，且R中所有的非主属性都直接依赖于码，则R属于第三范式

规范性问题：

数据库的范式是为了规范数据库的设计，但是实际中相比规范性，往往更需要看中性能、成本、用户体验等问题；

因此有时会故意给某些表增加一个冗余的字段，使多表查询变为单表查询。有时还会增加一些计算列，从大数据量变为小数据量（数据量大时，count(*)很耗时，可以直接添加一列，每增加一行+1，查该列即可）；阿里也曾提出关联查询的表最多不超过三张表。

这些就是为了性能、成本而舍弃一定规范性的例子

14. 事务的原则ACID

一致性是事务最终的目的，为了保证事务的一致性，需要保证原子性、持久性、隔离性

原子性（Atomicity）：一个事务中的操作要么一起成功，要么一起失败
一致性（Consistency）：数据库总是从一个一致性的状态转换到另一个一致性的状态。
隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其他事务是不可见的。
持久性（Durability）：事务一旦提交，就会持久化到数据库（提交则不可逆）

15. 事务并发导致的问题

脏读：一个事务读取了另一个事务未提交的数据（这是相当危险的，因为很可能所有的操作都被回滚）
不可重复读：一个事务多次读取，会读取到不同的数据（比如事务A多次读取同一数据，但是事务B在A读取的过程中，对数据进行了修改并且提交，则事务A每次读取到的数据值都不同）
幻读：一个事务两次读取，读取到了新增的数据/数据减少，导致两次读取到的数据不同

16. 事务隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别

SQL标准中定义了四种隔离级别，并规定了每种隔离级别下上述几个问题是否存在。一般来说，==隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差。==隔离级别与读问题的关系如下：

读未提交（read uncommit）：一个事务读取到其他事务未提交的数据；这种隔离级别下，查询不会加锁，一致性最差，会产生脏读、不可重复读、幻读的问题
读已提交（read commit）：一个事务只能读取到其他事务已经提交的数据；该隔离级别避免了脏读问题的产生，但是不可重复读和幻读的问题仍然存在；

读提交事务隔离级别是大多数流行数据库的默认事务隔离级别，比如 Oracle，但是不是 MySQL 的默认隔离界别
可重复读（repeatable read）：事务在执行过程中可以读取到其他事务已提交的新插入的数据，但是不能读取其他事务对数据的修改，也就是说多次读取同一记录的结果相同；该个里级别避免了脏读、不可重复度的问题，但是仍然无法避免幻读的问题

可重复读是MySQL默认的隔离级别
串行化（serializable）：事务串行化执行，事务只能一个接着一个地执行，并且在执行过程中完全看不到其他事务对数据所做的更新；缺点是并发能力差，最严格的事务隔离，完全符合ACID原则，但是对性能影响比较大

事务隔离级别	脏读	不可重复读	幻读
读未提交（read-uncommitted）	是	是	是
读已提交（read-committed）	否	是	是
可重复读（repeatable-read）	否	否	是
串行化（serializable）	否	否	否

17. 统计过慢查询吗？怎么优化？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明自慢的原因是什么，是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

首先分析语包，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写
分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引
如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表

18. ACID实现原理

A原子性由undo log回滚日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经成功的sql
C一致性由其他三大特征来保证，程序代码要保证业务上的一致性
I隔离性由MVCC（读）和锁机制（写）来保证
D持久性缓存+redo log来保证，mysql修改数据同时在缓存和redo log记录这次操作，宕机时可从redo log中恢复

数据库acid实现原理（二）_bob的博客-CSDN博客_acid实现原理

1. 原子性的实现

首先介绍一下MySQL的事务日志。MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。

实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。

undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。

以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

2. 持久性的实现

InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，但如果每次读写数据都需要磁盘IO，效率会很低。为此，InnoDB提供了缓存(Buffer Pool)，Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲：当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，会首先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（这一过程称为刷脏）。

Buffer Pool的使用大大提高了读写数据的效率，但是也带了新的问题：如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证。

于是，redo log被引入来解决这个问题：当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作；当事务提交时，会调用fsync接口对redo log进行刷盘。如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。redo log采用的是WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。redo log的刷盘会在系统空闲时进行

既然redo log也需要在事务提交时将日志写入磁盘，为什么它比直接将Buffer Pool中修改的数据写入磁盘(即刷脏)要快呢？主要有以下两方面的原因：

刷脏是随机IO，因为每次修改的数据位置随机，但写redo log是追加操作，属于顺序IO。
刷脏是以数据页（Page）为单位的，MySQL默认页大小是16KB，一个Page上一个小修改都要整页写入；而redo log中只包含真正需要写入的部分，无效IO大大减少。

此外，在MySQL中还存在bin log(二进制日志)也可以记录写操作并用于数据的恢复，但二者是有着根本的不同的：

作用不同：redo log是用于crash recovery的，保证MySQL宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制。
层次不同：redo log是InnoDB存储引擎实现的，而bin log是MySQL的服务器层实现的，同时支持InnoDB和其他存储引擎。
内容不同：redo log是物理日志，内容基于磁盘的Page；bin log的内容是二进制的，根据binlog_format参数的不同，可能基于sql语句、基于数据本身或者二者的混合。
写入时机不同：binlog在事务提交时写入；redo log的写入时机相对多元：
- 前面曾提到：当事务提交时会调用fsync对redo log进行刷盘；这是默认情况下的策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性将无法保证。
- 除了事务提交时，还有其他刷盘时机：如master thread每秒刷盘一次redo log等，这样的好处是不一定要等到commit时刷盘，commit速度大大加快。

那么怎么保证redo log和bin log中的数据一致呢？

innodb首先会进行redo log写盘，然后innodb事务进入prepare状态。如果前面prepare成功，就将bin log写盘，将事务日志持久化到bin log中，如果bin log持久化成功，那么innodb会将redo log中的事务写一个commit记录

3. 隔离性的实现

隔离性的探讨，主要可以分为两个方面：

(一个事务)写操作对(另一个事务)写操作的影响：锁机制保证隔离性
(一个事务)写操作对(另一个事务)读操作的影响：MVCC保证隔离性

InnoDB默认的隔离级别是RR，RR解决脏读、不可重复读、幻读等问题，使用的是MVCC

4. 一致性的实现

可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。此外，除了数据库层面的保障，一致性的实现也需要应用层面进行保障。

实现一致性的措施包括：

保证原子性、持久性和隔离性，如果这些特性无法保证，事务的一致性也无法保证
数据库本身提供保障，例如不允许向整形列插入字符串值、字符串长度不能超过列的限制等
应用层面进行保障，例如如果转账操作只扣除转账者的余额，而没有增加接收者的余额，无论数据库实现的多么完美，也无法保证状态的一致

19. 什么是MVCC

多版本并发控制(Multi-Version Concurrency Control)：读取数据时通过一种类似快照的方式将数据保存下来，不同的事务session会看到自己特定版本的数据——版本链，这样读锁和写锁就不会冲突

下面的例子很好的体现了MVCC的特点：在同一时刻，不同的事务读取到的数据可能是不同的(即多版本)——在T5时刻，事务A和事务C可以读取到不同版本的数据。

MVCC最大的优点是读不加锁，因此读写不冲突，并发性能好。InnoDB实现MVCC，多个版本的数据可以共存，主要基于以下技术及数据结构：

隐藏列：Innodb聚簇索引记录中有两个必要的隐藏列
- trx_id：存储每次对某条聚簇索引记录进行修改的时候的事务id
- roll-pointer：是一个指针，指向undo log中该行记录上一个版本的位置
基于undo log的版本链：每次对某条聚簇索引数据行有修改的时候，都会把老版本写入undo log日志中，而通过前面的roll-pointer指针可获得上一个版本的记录信息，进而可以生成一个版本链（注意插入操作的undo log没有这个属性，因为它没有老版本）
ReadView：通过隐藏列和版本链，MySQL可以将数据恢复到指定版本；但是具体要恢复到哪个版本，则需要根据ReadView来确定。

开始事务时创建readview， readview维护当前活动的事务id，即未提交的事务id，排序生成一个数组

查询数据时，首先获取数据中的事务id（获取的是事务id最大的记录），对比rearview：
- 如果在readview的左边（比readview都小），则代表该事务已经提交，可以访问
- 如果在readview的右边（比readview都大）或者就在readview中，则代表该事务还未提交，不可以访问，此时通过roll_pointer取上一版本重新对比，以此类推

MVCC只在 读已提交(RC)和可重复读(RR)两种隔离级别下工作，其他两个隔离级别和MVCC不兼容，因为读未提交(RU)总是读取最新的数据行，而不是符合当前事务版本的数据行，而串行化则对所有读取的行都加锁

已提交读和可重复读的区别就在于它们生成Readview的策略不同，已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView；而可重复读隔离级别则在第一次读的时候生成一个Readview，之后的读都复用之前的Readview

这就是Mysql的MVCC，通过版本链，实现多版本，可并发读写，写-读。通过Readview生成策略的不同实现不同的隔离级别

20. mysql主从同步原理

mysql主从同步中主要有三个线程：Master一条线程bin log dump thread、以及Slave的两条线程I/O thread、sql thread

主节点中有bin log日志文件，是数据库服务启动时用于保存所有修改数据库结构或内容的文件，主从复制的基础就是主库记录所有的变更到bin log中；每bin log有变动时，主节点的bin log dump线程就会读取其内容发送到从节点；然后从节点I/O线程接受到bin log的内容后，将其写入到relay log文件中；最后主节点的sql线程读取到relay log文件内容对数据更新进行重放，最终保证主从数据库的一致性

注意：主节点使用bin log文件+posotion偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机，则会自动从position的位置发起同步。也就是说是一种增量同步而不是全量同步！

由于mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生两个概念。

全同步复制

主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给主库劝人信息，但是该方式性能会受到严重影响
半同步复制

和全同步不同的是，半同步不需要等待所有从库执行完成再返回给主库确认信息，而是从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

21. 当查询数据量比较大时怎么处理

MySQL数据库中，数据量越来越大，有什么具体的优化方案么？

1. 单表优化

大表的优化，不一定上来就要分库分表，因为表一旦被拆分，开发、运维的复杂度会直线上升，而大多数公司和开发人员是欠缺这种能力的。所以MySQL中几百万甚至小几千万的表，先考虑做单表的优化。单表优化可以从以下几个角度出发：

1️⃣ 表分区

MySQL数据库表分区功能详解 - 周国伟 - 博客园 (cnblogs.com)

MySQL在5.1之后才有的，可以看做是水平拆分，分区表需要在建表的需要加上分区参数，用户需要在建表的时候加上分区参数；

分区表底层由多个物理子表组成，但是对于代码来说，分区表是透明的；

SQL中的条件中最好能带上分区条件的列，这样可以定位到少量的分区上，否则就会扫描全部分区。

2️⃣ 增加缓存

主要的思想就是减少对数据库的访问，缓存可以在整个架构中的很多地方；

比如：数据库本身有就缓存，客户端缓存，数据库访问层对SQL语句的缓存，应用程序内的缓存，第三方缓存（如Redis等）；

3️⃣ 字段设计

单表不要有太多字段；
VARCHAR的长度尽量只分配真正需要的空间；
尽量使用TIMESTAMP而非DATETIME；
避免使用NULL，可以通过设置默认值解决。

4️⃣ 索引优化

索引不是越多越好，针对性地建立索引，索引会加速查询，但是对新增、修改、删除会造成一定的影响；
值域很少的字段不适合建索引；
尽量不用UNIQUE，不要设置外键，由程序保证；
尽量使用索引，也要保证不要因为错误的写法导致索引失效；

比如：避免前导模糊查询，避免隐式转换，避免等号左边做函数运算，in中的元素不宜过多等等；

5️⃣ NoSQL

有一些场景，可以抛弃MySQL等关系型数据库，拥抱NoSQL；

比如：统计类、日志类、弱结构化的数据；事务要求低的场景。

2. 分表分库

在数据库分表分库原则中，遵循二个设计理论垂直拆分、水平拆分。垂直拆分是把不同的表拆到不同的数据库中，而水平拆分是把同一个表拆到不同的数据库中。

数据库做拆分的时候，第一步做的就是垂直拆分，这次拆分，应用改动最少，获得性能提升却很大。

1️⃣ 垂直拆分

在数据库里将表按照不同的业务属性，拆分到不同库中，就是垂直拆分；比如会员数据库、订单数据库、支付数据库、消息数据库等，垂直拆分在大型电商项目中使用比较常见。

优点：拆分后业务清晰，拆分规则明确，系统之间整合或扩展更加容易。
缺点：部分业务表无法join，跨数据库查询比较繁琐(必须通过接口形式通讯(http+json))、会产生分布式事务的问题，提高了系统的复杂度。举栗子：不可能出现，在订单服务中，订单服务直接连接会员服务的数据库这种情况。

2️⃣ 水平拆分

把同一张表中的数据拆分到不同的数据库中进行存储、或者把一张表拆分成 n 多张小表

相对于垂直拆分，水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，主要有分表，分库两种模式该方式提高了系统的稳定性跟负载能力，但是跨库join性能较差。

22. 如果有40亿个qq号，如何存储

题目描述：

如果有40亿个QQ号，如何存储在内存中。

解题思路：

此题目，实际上是一个海量数据处理问题。首先我们需要清楚，如果有40亿个QQ号，假设每个QQ号用一个int类型存储则大约需要16G的空间，所以如果不做任何处理直接存储，缺点是空间消耗比较大，并且也有可能导致内存不够的问题。在这里，我们存储qq号的目的主要是用于查询，而查询只是一个二值逻辑，即在不在的问题，完全可以用一个bit位保存，这样，40亿个QQ号的存储空间我们可以减少32倍，即只需要消耗大概500M的空间

位图实现原理

用一个bit位存储数据的数据结构称作为位图，位图其实是用数组实现的，数组中每一个元素的每一个二进制位都可以表示一个数据在或者不在，0表示数据不存在，1表示数据存在；位图中的数不能重复，否则会冲突