数据库-面经

1. 索引的本质

• 官方的话：索引是帮助MySQL高效获取数据的排好序的数据结构

• 索引的数据结构

  • 二叉查找树(不使用，缺点是因为会变成一颗斜树)

  

  

• 红黑树（特化二叉平衡树AVL）
  • 缺点：维护平衡二叉树的代价非常大，树的高度会很大
• Hash表
• B-Tree（）

2. B 树 和 B+树

2.1 B-Tree

• 所有的叶子节点在同一层
• 根节点至少有两个子女

2.2 B+Tree

• 它是B树的变种

• 非叶子节点没有指针，叶子节点由指针顺序相连

• data元素放到了叶子节点去

• 高度为3的B+ 树 根节点可以存放 16KB /( 8B + 6B) = 1170个索引

  • 对于叶子节点来说可以存放 1170 * 1170 * 16 = 2000万左右的数据

3. B + 树索引

• B+ 树索引的本质就是B+树在数据库中的实现

• B + 索引的特点就是高扇出性，树一般高度在 2 到 4层 ，这也就是说明了查找某一个键值记录最多需要2到4次的 IO

• B + 索引 分为 聚集索引和非聚集索引

• 因为有双向指针的原因，所以可以快速的进行范围查找

对于MyISAM来说（frm,MYD,MYI）：

• frm 表结构
• MYD 存数据
• MYI 存索引

对于Innodb来说(frm ibd)：

• frm 表结构
• ibd : 数据 + 索引
• innodb 表必须有主键，并且推荐使用整型的自增主键

3.1 聚集索引

• 聚集索引就是按照每张表的主键构造一棵B + 树，同时叶子节点存放的就是整张表的行记录（也就是说聚集索引的叶子节点称为数据页）

3.2 非聚集索引

• 非聚集索引会有辅助查找树，辅助查找树存放的是主键记录的地址，通过查到地址再回表查询主键行记录
• 需要进行回表查询

4. 哈希 索引

• 对于哈希索引，数据库一般采用除法散列的方法

h(k) = k mod m

解释： k个关键字映射到m个槽中

• 只对等值查找(where)有效果，对范围查找没效果

select XXX where id = 1

5. 联合索引

• 联合索引是指多表上多个列进行索引
• 从本质上来说，不同的是联合索引的键值的数量不是1，而是大于等于2.

5.1 最左前缀原则

1. 创建表

CREATE TABLE test(
a INT,
b INT,
c INT,
d INT,
KEY index_abc(a,b,c)
)ENGINE=INNODB DEFAULT CHARSET = utf8;

2. 插入 10000 条数据

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
    INSERT INTO test(a,b,c,d) VALUES(i,i,i,i);
    SET i = i+1;
END WHILE;
END $
CALL proc_initData();

这样就建立好了联合索引（a，b，c）

3. 接下来我们来验证最左前缀原则

• 关于explain 指令详解可以查看：[Explain指令][https://www.cnblogs.com/gomysql/p/3720123.html]

• 接下来几个查询都是满足最左前缀原则的

# a为主键，
explain select * from test where a<10 ;

explain select * from test where a<10 and b <10;

explain select * from test where a<10 and b <10 and c<10;

• 能不能将 a，b出现顺序换一下，a，b，c出现顺序换一下

# 这样也是满足最左前缀原则的
# 原因是：即使a不是在where的最左方，但mysql优化器会判断纠正这条sql以设么样执行效率最高，最后才生成最后的计划
explain select * from test where b<10 and a <10;

explain select * from test where b<10 and a <10 and c<10;

以上都是用到了联合索引的。

• 重点

# 不会用到索引
explain select * from test where b<10 and c <10;
# 会用到索引
explain select * from test where a<10 and c <10;

# 为什么 b<10 and c <10,没有用到索引？而 a<10 and c <10用到了？

# 原因：当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树会按照从左到右的原则来建立搜索树，
# 比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name 相同再比较sex和age,最后得到检索的数据
# 但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。
# 比如当(张三,F)这样的数据来检索时，B+树可以用name来制定搜索方向，即使下一个字段age缺失，也可以把名字等于张三的数据都找到了，然后在匹配到F的数据，这就是复杂索引的最左匹配原则

6. Mysql事务

• 事务的定义：事务会把数据会把数据库从一种一致的状态转换到另外一种一致的状态，在数据提交的工作时候，可以保证要么所有的修改都已经保存好了，要么所有的修改都不保存（要么成功，要么失败）
• 对于Innodb而言，其默认的事务隔离级别是READ REPEATABLE,完全遵循和符合事务的ACID的特性

• 将一组SQL放在同一个批次去执行
• 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
• MySQL事务处理只支持InnoDB和BDB数据表类型

6.1 事务的ACID原则

1. 原子性（Atomicity）

参考博客（https://blog.csdn.net/dengjili/article/details/82468576）

核心：要么都成功，要么都失败

• 事务中任何一个SQL语句执行失败，已经成功执行的SQL语句也必须要撤销，数据库状态应该退回到执行事务前的状态。

这个过程包含两个步骤

A： 800 - 200 = 600
B: 200 + 200 = 400

2. 一致性： (Consistency)

核心：事务前后的一致性要保持一致

操作前A：800，B：200
操作后A：600，B：400

一致性表示事务完成后，符合逻辑运算

3. 隔离性（Isolaiton）

核心：事务的隔离性是多个用户并发访问数据库的时候，数据库为每一个用户开启的事务，不能被其他用户访问，事务之间需要相互隔离。（通常使用锁来实现）

• 事务的隔离性要求每个读写事务的对象对其他事务的操作对象能相互分离，即事务提交前对其他事务都不可见，通常这使用锁来实现。

4. 持久性（Durability）

核心：事务一旦提交则不可逆，被持久化到数据库中

操作前A：800，B：200
操作后A：600，B：400

• 如果在操作前（事务还没提交）服务器宕机或者断电，那么重启数据库之后，数据状态应该为 A：800 B: 200
• 如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库之后，数据状态应该为 A: 600 B : 400

6.2 并发操作带来的隔离性的问题

• 并发操作带来的隔离性的问题：本质上是锁带来的问题

写的还可以的参考博客：https://www.cnblogs.com/shan-kylin/p/9543294.html

1. 脏读：一个事务读取了另一个事务未提交的数据

首先分清楚脏页和脏数据的完全不同的概念

• 脏页： 缓冲池中已经修改的页，但还没有刷新到磁盘中，即数据库中的页和磁盘中的页数据是不一样的。
• 脏数据：事务对缓冲池中行记录的修改，并且还没有提交

2. 不可重复读：

• 在一个事务中多次读取同一个数据集合，在这个事务还没有结束的时候，另外一个事务也访问该同一个数据集合，并做了一些DML操作。因此在第一个事务中两次读取数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能不一样，这种情况叫做不可重复读。
• 与脏读的区别是，不可重复读读到的是已提交的数据，脏读读到的是未提交的数据。

不可重复读（Non-repeatable Reads）：一个事务对同一行数据重复读取两次，但是却得到了不同的结果。

解决方法——引入隔离级别更高事务隔离：可重复读

包括以下情况：

• 虚读：事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读取该数据时得到与前一次不同的值。
• 幻读：事务在操作过程中进行两次查询，第二次查询的结果包含了第一次查询中未出现的数据或者缺少了第一次查询中出现的数据。这是因为在两次查询过程中有另外一个事务插入数据造成的。

3. 丢失更新

很明显的看出，旺财对A添加的20块不翼而飞了，这就是“数据丢失”，对事务不加任何锁（不存在事务隔离），就会导致这种问题。

• 简单的来说就是一个事务的更新操作会被另外一个事务的更新操作所覆盖。
• 要解决这个问题需要让事务的操作变成串行化，而不是并行操作。（Select … for update : 读的时候加一把排他锁）

逻辑意义的丢失更新出现的场景：

1. 事务T1 查询一行数据，放入本地内存，并显示给User1
2. 事务T2 查询同一行数据，放入本地内存，并显示给User2
3. User1修改数据，更新数据库并提交
4. User2修改数据，更新数据库并提交

6.3 事务的隔离级别

为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同。

• 读未提交（Read Uncommitted） ： 只处理更新丢失。如果一个事务已经开始写数据，则不允许其他事务进行写操作，但允许其他事务读数据。可通过“排他写锁”实现。（ 在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。）
• 简单一句话：操作：写数据的时候添加一个X锁（排他锁），也就是在写数据的时候不允许其他事务进行写操作，但是读不受限制，读不加锁。（这样就可以解决了多个人一起写数据而导致了“数据丢失”的问题，但是会引发新的问题——脏读。）

• 读已提交（Read Committed）：处理更新丢失，脏读。读取数据的事务允许其他事务继续访问修改数据，但是未提交的写事务会禁止其他事务访问修改。可通过“瞬间共享读锁”和“排他写锁”实现。（它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。）

操作：写数据的时候加上X锁（排他锁），读数据的时候添加S锁（共享锁），而且有约定：如果一个数据加了X锁就没法加S锁；同理如果加了S锁就没法加X锁，但是一个数据可以同时存在多个S锁（因为只是读数据），并且规定S锁读取数据，一旦读取完成就立刻释放S锁（不管后续是否还有很多其他的操作，只要是读取了S锁的数据后，就立刻释放S锁）。

这样就解决了脏读的问题，但是又有新的问题出现——不可重复读。

解决方法——引入隔离级别更高事务隔离：可重复读

• 可重复读（Repeatable Read）: 处理更新丢失，脏读和不可重复读。读取数据的事务会禁止写事务，但允许读事务，写事务则禁止任何其他事务。可通过“共享读锁”和“排他写锁”实现。（Mysql在REPEATABLE READ 事务隔离的级别下，使用next-key-lock算法，来避免幻读的产生。）（Mysql 默认的隔离级别，它保证同一个事务在多个实例并发读取的时候，会看到同样的数据行。）

操作：对S锁进行修改，之前的S锁是：读取了数据之后就立刻释放S锁，现在修改是：在读取数据的时候加上S锁，但是要直到事务准备提交了才释放该S锁，X锁还是一致。

这样就解决了不可重复读的问题了，但是又有新的问题出现——幻读。

例如：

有一次旺财对一个“学生表”进行操作，选取了年龄是18岁的所有行， 用X锁锁住， 并且做了修改。

改完以后旺财再次选择所有年龄是18岁的行， 想做一个确认， 没想到有一行竟然没有修改！

这是怎么回事？ 出了幻觉吗？

原来就在旺财查询并修改的的时候， 小强也对学生表进行操作， 他插入了一个新的行，其中的年龄也是18岁！ 虽然两个人的修改都没有问题， 互不影响， 但从最终效果看， 还是出了事。

（码农翻身注： 正是小强的操作， 让旺财出现了“幻读”）

解决幻读的方式——串行化

• 序列化（Serializable） ： 提供严格的事务隔离级别。要求失去序列化执行，事务只能一个一个的执行，不能并发的执行。仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。（主要用于Innodb的分布式事务）（最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读的问题，简而言之，就是在每个读的数据行上加上共享锁。）

事务只能一件一件的进行，不能并发进行。

注意：

• 隔离级别越高，越能保证数据的完整性和统一性，但是对并发性能的影响也越大。
• 对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读，而且具有较好的并发性能。
• 尽管它会导致不可重复读、幻读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

打赏