数据库 - MySQL - 《Java 面试整理》

0. 什么是事务？
1. ACID四大特性
2. 事务隔离级别
- 2.1 读未提交 Read Uncommitted
- 2.2 读已提交 Read Committed
- 2.3 可重复读 Repeatable Read
- 2.4 串行化 Serializable
3. MySQL中锁的类型重点：悲观锁和乐观锁 P58
4. 索引的基本原理
5. 乐观锁之版本号和CAS
- 5.1 CAS 底层原理
- 5.2 CAS 的典型应用——多线程下的计数（原子类、LongAdder）
- 5.3 版本号实现乐观锁（MySQL乐观锁电商库存并发问题应用）
6. 悲观锁
- 6.1 乐观锁和悲观锁的选择（三高（高性能、高并发、高可用）架构提出后，悲观锁应用在生产环境中越来越少）
7. 数据库的三范式
8. MySQL 支持哪些存储引擎？
9. 索引问题目录
- 9.1 索引是什么及工作机制
- 9.2 面试：MySQL数据库为什么要使用B+TREE作为索引的数据结构？
- 9.3 InnoDB中B+树的聚集索引
- 9.4 三层高的B+树可以存放多少数据？
10. 超键、候选键、主键、外键分别是什么？
11. SQL 约束有哪几种？
12. MySQL 中的 varchar 和 char 有什么区别？
13. MySQL中 in 和 exists 区别
- 理解简记（面试回答）
14. drop、delete与truncate的区别
15. 什么是存储过程及优缺点
16. MySQL 执行查询的过程
17. SQL中的通配符
18. 事务的实现原理
19. 在事务中可以混合使用存储引擎吗？
20. MVCC 概念及实现原理
- 理解简记
21. 为什么要加锁？什么是死锁？
22. MySQL 的连接查询
23. UNION和UNION ALL的区别
24. 优化问题
- 24.1 大表数据查询如何进行优化？
- 24.2 慢查询如何优化？
- ">24.3 如何优化查询过程中的数据访问？
- 覆盖索引
- 24.4 如何优化长难的查询语句？
- 24.5 如何优化WHERE子句？
- 24.6 大表如何优化？
- 24.7 怎么优化索引？
- 24.8 索引在什么情况下会失效？
25. 为什么设置主键，主键用自增ID还是UUID？
26. 分库分表
- 26.1 什么是垂直分表、垂直分库、水平分表、水平分库？
- 26.2 分库分表后，ID键如何处理？
27. MySQL的复制原理及流程？如何实现主从复制？什么是读写分离？
28. 锁升级

0. 什么是事务？

事务是一个不可分割的数据库操作序列，
也是数据库并发控制的基本单位，
其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。
事务是逻辑上的一组操作，要么都执行，要么都不执行。

1. ACID四大特性

原子性 Atomicity：事务不可再分割，事务中的操作要么都发生，要么都不发生。
一致性 Consistency：事务的执行不能破环数据库数据的完整性和一致性。
一个事务在执行之前和执行之后，数据库都必须处于一致性状态。
例如：如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确的状态，也就是不一致的状态。
隔离性 Isolation：并发环境中，并发的事务相互隔离，事务的执行互不干扰。
持久性 Durability：一旦事务提交，对数据库数据的状态的变更就会永久保存。

2. 事务隔离级别

解决问题：
脏读：一个事务中访问到了另外一个事务未提交的数据。
具体来说假如有两个事务A和B同时更新一个数据d=1，事务B先执行了select获取到d=1,然后更新d=2但是没有提交，这时候事务A在B没有提交的情况下执行搜索结果d=2，这就是脏读。
不可重复读：不可重复读是指一个事务内在未提交的前提下多次搜索一个数据，搜出来的结果不一致。
发生不可重复读的原因是在多次搜索期间这个数据被其他事务更新了。
幻读：幻读是指同一个事务内多次查询(注意查询的sql不一定一样)返回的结果集的不一样。
（比如新增或者少了一条数据），比如同一个事务A内第一次查询时候有n条记录，但是第二次同等条件下查询却又n+1条记录，这就好像产生了幻觉，为啥两次结果不一样那。其实和不可重复读一样，发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据。不同在于不可重复读是数据内容被修改了，幻读是数据变多了或者少了。

2.1 读未提交 Read Uncommitted

一个事务中可以读取其他事务未提交的数据，会脏读

2.2 读已提交 Read Committed

一个事务只能读取到其他事务已经提交的数据，可能导致同一个事务中多次搜查结果不一样，会不可重复读
一个事务由多句sql语句构成，每执行一句，就释放锁（因为你可能做到一半就去干别的了），导致别的事务会修改

2.3 可重复读 Repeatable Read

这一级别下，当前事务提交前，不允许别的事务进行修改。一个事务内多次查询数据结果都一致，解决了不可重复读，但会幻读
因为这一隔离级别下，锁住了当前查询的数据，比如7~20行，但是当别的事务对表其他行进行插入或删除操作时，会影响到7~20行，所以避免不了幻读
MySQL默认这一级别，但是其InnoDB引擎实际上解决了幻读。它通过MVCC和next-key locks解决幻读。
MVCC（Multi Version Concurrency Control，多版本并发控制）：给每行元组加一些辅助字段，记录创建版本号和删除版本号。用多版本控制（MVCC）来读取某个时间点创建的快照（历史版本）。这个请求只能看到这个时间点之前提交的事物的修改，看不到之后的或者未提交的事务的修改。
Next-Key Locks：行锁和间隙锁的组合。加锁规则：
next-key lock 是前开后闭区间。
查找过程中访问到的对象才会加锁。
索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
唯一索引上的范围查询会访问到不满足条件的第一个值为止。
操作时没索引列，则表锁。

间隙锁：锁的是两个索引记录的间隙，或者是第一个索引记录之前或者最后一个索引之后的间隙。

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;
无论c1=15这个值是否在表中存在，其他的事务都不可以对t.c1=15这个值进行插入操作。
因为10到20这个间隙（gap）被加锁了。

number:1 2 3 4 5 6  6  6  11
id:    1 3 5 7 9 10 11 12 23
select * from t where number=6;
那么间隙锁锁定的间隙为：（5，11），所以你再想插入5到11之间的数就会被阻塞。
更需要你注意的是，当你再执行update t set number = 6 where id = 1也会被阻塞。
因为要保证每次查询number=6的数据行数不变，如果你将另外一条数据修改成了6，岂不会多了一条？
所以此时不会允许任何一条数据被修改成6。

2.4 串行化 Serializable

多个事务串行化一个个按顺序执行，不存在并发，解决了脏读、不可重复读、幻读

3. MySQL中锁的类型重点：悲观锁和乐观锁 P58

真正的锁：基于锁的粒度分类那些

间隙锁：锁区间，左开右闭。比如上一条记录为1，下条记录为4，（中间是不存在的行），行锁锁住1和4行，间隙锁锁住234行。临建锁就会把1234都锁了。

意向共享锁和意向排他锁，提高了加锁的效率，避免了遍历。

4. 索引的基本原理

![image.png](https://cdn.nlark.com/yuque/0/2021/png/22162061/1628407652853-5f31d93d-6481-4965-8ad2-6e817ee6982b.png#height=179&id=eLUHv&margin=%5Bobject%20Object%5D&name=image.png&originHeight=238&originWidth=776&originalType=binary&ratio=1&size=151312&status=done&style=none&width=582)<br />**理解简记**<br />索引的原理：把无序的数据变成有序的查询，目的快速查找<br />1.**排序**：对创建索引的列内容<br />2.**倒排表**：排序结果就叫倒排表<br />3.**内容拼上数据地址链**：比如哈希索引中倒排表本身放的是哈希值，现在把哈希值对应的数据的地址也拿过来<br />4.**查询**：拿倒排表内容，取数据地址链，取数据

哈希索引：将每个数据计算哈希值，哈希值有序的放于哈希表。
B-Tree：
B+Tree：

https://www.nowcoder.com/discuss/tiny/715998
https://mp.weixin.qq.com/s/6muMmp-gnDgc7Bm4xmYAQg

5. 乐观锁之版本号和CAS

https://mp.weixin.qq.com/s/ySArYhfBKqVOBVQZwD7hMQ
乐观锁，每次去拿数据的时候都认为，都认为别人不会修改，不会加锁，但在更新的时候会去判断一下，此期间别人有没有更新数据。
乐观锁采取了更加宽松的加锁机制。也是为了避免数据库幻读、业务处理时间过长等原因引起数据处理错误的一种机制，但乐观锁不会刻意使用数据库本身的锁机制，而是依据数据本身来保证数据的正确性。
乐观锁的实现：
CAS
Java 中java.util.concurrent.atomic包下面的原子变量使用了乐观锁的一种 CAS 实现方式。
CAS(compare and swap) 比较并交换，有三个操作数，内存地址V ，预期值B，要替换得到的目标值A；
　　CAS指令执行时，比较内存地址V与预期值B是否相等，若相等则将A赋给B，（不相等则会循环比较直到相等）整个比较赋值操作是一个原子操作；
　　CAS有三个缺点
　　1.CAS自旋操作：当内存地址V与预期值B不相等时会一直循环比较直到相等，CPU开销大
　　2.只能保证一个共享变量的原子操作，多个变量依然要加锁。
　　3.出现ABA问题：如果内存值初V次读取的时候为A，在将要赋值的时候再次检查还是A，能说明V没有改变过吗？
　　有一种可能是当读取内存值V的时候是A，有一个线程将A改为B，后又改为A，CAS会误认为内存值V没有改变，这称为CAS操作的ABA问题； ABA：被另一个线程改了两次，又改回去了，然后CAS以为没变。
版本号：数据版本机制和时间戳机制
数据表中除数据外还有一个version字段，更新时会+1。
假设线程A在读取数据和version(version = 1)的期间，有另一个线程B也读取了version(version = 1),
线程A修改数据，更新version(version = 2),提交更新时，在更新version前读取的version(version = 1)和当前数据表中的version(version = 1)相同，则更新成功。
线程B也修改数据，更新version(version = 2)提交更新时，由于读取时version = 1 而当前数据表version = 2 不相等，则更新失败。

时间戳机制，同样是在需要乐观锁控制的 table 中增加一个字段，字段类型使用时间戳(timestamp)，和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则继续，否则就是版本冲突。

5.1 CAS 底层原理

自旋；
unsafe类。

假如说有 3 个线程并发的要修改一个 AtomicInteger 的值，底层机制如下：

首先，每个线程都会先获取当前的值，接着走一个原子的 CAS 操作。原子的意思就是这个 CAS 操作一定是自己完整执行完的，不会被别人打断。
然后 CAS 操作里，会比较一下，现在的值是不是刚才获取到的那个值。如果是，说明没人改过这个值，然后设置成累加 1 之后的一个值。
同理，如果有人在执行 CAS 的时候，发现之前获取的值跟当前的值不一样，会导致 CAS 失败。失败之后，进入一个无限循环，再次获取值，接着执行 CAS 操作。
5.2 CAS 的典型应用——多线程下的计数（原子类、LongAdder）
1.计数Demo
并发下count自增不安全，因为不是原子操作，而是三个原子操作的组合：读取、+1、赋值
```
public class Increment {
 private int count = 0;
 public void add() {
     count++;
 }
}
```
2.解决方案
1.synchronized 加锁
缺点：造成多个线程排队的问题，相当于让各个线程串行化了，一个接一个的排队、加锁、处理数据、释放锁，下一个再进来。同一时间只有一个线程执行，这样的锁有点“重量级”了。这类似于悲观锁的实现，需要获取这个资源，就给它加锁，别的线程都无法访问该资源，直到操作完后释放对该资源的锁。虽然随着 Java 版本更新，也对 synchronized 做了很多优化，但是处理这种简单的累加操作，仍然显得“太重了”。
```
public class Increment {
 private int count = 0;
 public synchronized void add() {
     count++;
 }
}
```
2.Atomic 原子类
Java 并发包下面提供了一系列的 Atomic 原子类，比如说 AtomicInteger。
多个线程可以并发的执行 AtomicInteger 的 incrementAndGet()，意思就是把 count 的值累加 1，接着返回累加后最新的值。实际上，Atomic 原子类底层用的不是传统意义的锁机制，而是无锁化的 CAS 机制，通过 CAS 机制保证多线程修改一个数值的安全性。
```
//import java.util.concurrent.atomic.AtomicInteger;
public static void main(String[] args) {
 public static AtomicInteger count = new AtomicInteger(0);
 public static void increase() {
     count.incrementAndGet();
 }
}
```
3.优化 使用LongAdder类代替AtomicInteger
问题：大量的线程同时并发修改一个 AtomicInteger，可能有很多线程会不停的自旋，进入一个无限重复的循环中。这些线程不停地获取值，然后发起 CAS 操作，但是发现这个值被别人改过了，于是再次进入下一个循环，获取值，发起 CAS 操作又失败了，再次进入下一个循环。在大量线程高并发更新 AtomicInteger 的时候，这种问题可能会比较明显，导致大量线程空循环，自旋转，性能和效率都不是特别好。
Java8 有一个新的类，LongAdder，它就是尝试使用分段 CAS 以及自动分段迁移的方式来大幅度提升多线程高并发执行 CAS 操作的性能。

LongAdder 核心思想就是热点分离，这一点和 ConcurrentHashMap 的设计思想相似。就是将 value 值分离成一个数组，当多线程访问时，通过 hash 算法映射到其中的一个数字进行计数。而最终的结果，就是这些数组的求和累加。这样一来，就减小了锁的粒度。

5.3 版本号实现乐观锁（MySQL乐观锁电商库存并发问题应用）

商品 goods 表中有一个字段 status，status 为 1 代表商品未被下单，status 为 2 代表商品已经被下单。那么对某个商品下单时必须确保该商品 status 为 1。假设商品的 id 为 1。下单操作包括三步：
1.查询出商品信息：（把版本号一并查询）

select name,status,version from goods where id=#{id}

2.根据商品信息生成订单
3.修改商品 status 为 2：（把版本号作为条件对比）

update goods set status=2,version=version+1
where id=#{id} and version=#{version};

为使用乐观锁，修改 goods 表，增加一个 version 字段，数据默认 version 值为 1。
goods表初始数据如下：

mysql> select * from t_goods;  
+----+--------+------+---------+  
| id | status | name | version |  
+----+--------+------+---------+  
|  1 |      1 | 道具 |       1 |  
|  2 |      2 | 装备 |       2 |  
+----+--------+------+---------+  
2 rows in set  
mysql>

…
产品库存更新，使用条件限制实现乐观锁

update goods
set quantity = quantity- #{buyQuantity} 
where id = #{id} 
AND quantity - #{buyQuantity} >= 0 
AND status = 1

quantity -#{buyQuantity}>=0此种做数据安全校验，适合库存模型，扣份额和回滚份额，性能更高.
注意：乐观锁的更新操作，最好用主键或者唯一索引来更新，这样是行锁，否则更新时会锁表。

6. 悲观锁

1.悲观锁，具有强烈的独占和排他特性。它指的是对数据被外界(包括本系统当前的其他事务，以及来自外部系统的事务处理)修改持保守态度。因此，在整个数据处理过程中，将数据处于锁定状态。悲观锁的实现，往往依靠数据库提供的锁机制(也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据)。

悲观锁，对数据的修改持有悲观态度的并发控制方式。总是假设最坏的情况，每次读取数据的时候都默认其他线程会更改数据，因此需要进行加锁操作，当其他线程想要访问数据时，都需要阻塞挂起。
悲观锁的实现：
1.传统的关系型数据库使用这种锁机制，比如行锁、表锁、读锁、写锁等，都是在操作之前先上锁。
2.Java 里面的同步 synchronized 关键字的实现。

数据库
举例：不用锁的时候，会有并发问题

//1.查询出商品信息
select status from t_goods where id=1;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;

使用悲观锁必须先关闭mysql的自动提交属性，因为mysql默认使用autocommit模式。即：执行一个更新操作，mysql会立即提交。
关闭了自动提交后，需要手动提交事务。不同于普通的select from…
现在使用：select … for update 显式加锁 这样就使用了悲观锁

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

在事务中，只有SELECT … FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT … 则不受此影响。
注意：只有MySQL InnoDB默认Row-Level Lock，所以只有「明确」地指定主键，MySQL 才会执行Row lock (只锁住被选取的数据) ，否则MySQL 将会执行Table Lock (将整个数据表单给锁住)。

2.悲观锁主要分为共享锁和排他锁：

共享锁【shared locks】又称为读锁，简称 S 锁。顾名思义，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁【exclusive locks】又称为写锁，简称 X 锁。顾名思义，排他锁就是不能与其他锁并存，如果一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁。获取排他锁的事务可以对数据行读取和修改。

3.悲观并发控制实际上是“先取锁再访问”的保守策略，为数据处理的安全提供了保证。但是在效率方面，处理加锁的机制会让数据库产生额外的开销，还有增加产生死锁的机会。另外还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数据。

6.1 乐观锁和悲观锁的选择（三高（高性能、高并发、高可用）架构提出后，悲观锁应用在生产环境中越来越少）

1️⃣响应效率：如果需要非常高的响应速度，建议采用乐观锁方案，成功就执行，不成功就失败，不需要等待其他并发去释放锁。乐观锁并未真正加锁，效率高。一旦锁的粒度掌握不好，更新失败的概率就会比较高，容易发生业务失败。
2️⃣冲突频率：如果冲突频率非常高，建议采用悲观锁，保证成功率。冲突频率大，选择乐观锁会需要多次重试才能成功，代价比较大。
3️⃣重试代价：如果重试代价大，建议采用悲观锁。悲观锁依赖数据库锁，效率低。更新失败的概率比较低。
4️⃣乐观锁如果有人在你之前更新了，你的更新应当是被拒绝的，可以让用户重新操作。悲观锁则会等待前一个更新完成。这也是区别。

7. 数据库的三范式

第一范式：强调的是列的原子性，即数据库表的每一列都是不可分割的原子数据项。
第二范式：要求实体的属性完全依赖于主键。所谓完全依赖是指不能存在仅依赖主键一部分的属性（因为主键可以一类或多列的组合）。
第三范式：任何非主属性不依赖于其它非主属性。就是每列属性都要和主键直接相关（完全依赖主键），不能存在传递关系。

8. MySQL 支持哪些存储引擎？

MySQL 支持多种存储引擎,比如 InnoDB,MyISAM,Memory,Archive ，默认引擎是InnoDB。
MyISAM 和 InnoDB 的区别：

InnoDB 支持事务，MyISAM 不支持
InnoDB 支持外键，而 MyISAM 不支持
InnoDB 是聚集索引：数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高；MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针，主键索引和辅助索引是独立的。
InnoDB 不支持全文索引，而 MyISAM 支持全文索引，查询效率上 MyISAM 要高；（但是可以通过sphinx插件支持，5.6.4之后都支持）
- 全文索引主要用来解决where name like %zhang% 等针对文本的模糊查询效率低的问题
InnoDB 不保存表的具体行数，MyISAM 用一个变量保存了整个表的行数。
InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁，MyISAM 采用表级锁(table-level locking)。

InnoDB ：支持事务、外键、聚集索引、行级锁，不支持全文索引、不保存表的具体行数
MyISAM：不支持事务、外键、聚集索引、表级锁，支持全文索引、用变量保存表的行数

9. 索引问题目录

9.1 索引是什么及工作机制

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构。// 对无序的数据进行有序的查询
对中大型表建立索引非常有效，对小表一般全部表扫描速度更快。
对超大型的表，建立和维护索引代价高，一般考虑分区技术。
查询多、增删改少的可以建立索引，不然没必要，因为维护索引也要代价。

其工作机制如下图：

select * from user where id = 40

如果没有索引的条件下，我们要找到这条记录，我们就需要在数据中进行全表扫描，匹配id = 40的数据。
有了索引，我们就可以通过索引进行快速查找，如上图中，可以先在索引中通过id = 40进行二分查找，再根据定位到的地址取出对应的行数据。

9.2 面试：MySQL数据库为什么要使用B+TREE作为索引的数据结构？

二叉查找（排序）树为什么不行
二叉查找树定义为：左子树上所有结点的值均小于它的根结点的值；右子树上所有结点的值均大于或等于它的根结点的值；左、右子树也分别为二叉查找树。
二叉查找树的查找时间复杂度可以达到Olog(n),二叉查找树搜索相当于一个二分查找。二叉查找能大大提升查询的效率。
存在问题：有时候由于插入数据，会退化成线性链表结构，需要遍历，相当于全盘扫描，所以不合适。

平衡二叉树为什么不行
平衡二叉查找树定义为：节点的子节点高度差不能超过1。保证二叉树平衡的方式为左旋，右旋等操作。

查找过程：（例如查id=8）

把根节点加载进内存，用8和10进行比较，发现8比10小，继续加载10的左子树。
把5加载进内存，用8和5比较，同理，加载5节点的右子树。
此时发现命中，则读取id为8的索引对应的数据。

索引保存数据的两种方式：

数据区保存id 对应行数据的所有数据具体内容。
数据区保存的是真正保存数据的磁盘地址。

存在问题：

搜索效率不足。树结构深度决定搜索的IO次数，数据量几百万的时候，树太高了。
查询不稳定。查询数据在根上，一次IO，在叶子节点上需多次IO。
存储数据内容太少。操作系统和磁盘之间数据交换是以页为单位，一页是4k。但是二叉树每个节点的结构只保存一个关键字、一个数据区、两个子节点的引用，不能填满4k，浪费了。

多路平衡查找树（Balance Tree， B Tree）
B Tree定义为：一个绝对平衡树，所有的叶子节点在同一高度。

这是一个2-3树（每个节点存储2个关键字，有3路），每个节点保存的关键字的个数和路数关系为：关键字个数 = 路数 – 1。
查找过程：（查id=X）

取出根磁盘块，加载40和60两个关键字。
如果X等于40，则命中；如果X小于40走P1；如果40 < X < 60走P2；如果X = 60，则命中；如果X > 60走P3。
根据以上规则命中后，接下来加载对应的数据，数据区中存储的是具体的数据或者是指向数据的指针。

B Tree 能够很好的利用操作系统和磁盘的交互特性， MySQL为了很好的利用磁盘的预读能力，将页大小设置为16K，即将一个节点（磁盘块）的大小设置为16K，一次IO将一个节点（16K）内容加载进内存。这里，假设关键字类型为 int，即4字节，若每个关键字对应的数据区也为4字节，不考虑子节点引用的情况下，则上图中的每个节点大约能够存储（16 * 1000）/ 8 = 2000个关键字，共2001个路数。对于二叉树，三层高度，最多可以保存7个关键字，而对于这种有2001路的B树，三层高度能够搜索的关键字个数远远的大于二叉树。
注意：在B Tree保证树的平衡的过程中，每次关键字的变化，都会导致结构发生很大的变化，这个过程是特别浪费时间的，所以创建索引一定要创建合适的索引，而不是把所有的字段都创建索引，创建冗余索引只会在对数据进行新增，删除，修改时增加性能消耗。

B+Tree
B+Tree是B Tree的一个变种，在B+Tree中，B树的路数和关键字的个数的关系不再成立了，数据检索规则采用的是左闭合区间，路数和关键个数关系为1比1，具体如下图所示：

查找过程：
如果上图中是用ID做的索引，如果是搜索X = 1的数据：
取出根磁盘块，加载1，28，66三个关键字。
X <= 1 走P1，取出磁盘块，加载1，10，20三个关键字。
X <= 1 走P1，取出磁盘块，加载1，8，9三个关键字。
已经到达叶子节点，命中1，接下来加载对应的数据，图中数据区中存储的是具体的数据。

B Tree 和 B+Tree的区别（2点）

1.B树内部节点和叶子节点都放键和值，B+树内部节点只放键，叶子节点存放所有键值
2.B+树的叶子节点相连在一起，方便顺序检索

9.3 InnoDB中B+树的聚集索引

InnoDB存储引擎支持B+树索引、哈希索引、全文索引、空间索引。
哈希索引多用于等值查询，时间复杂度为O1，效率高，但不支持排序、范围查询、模糊查询。
MySQL中的B+树索引可分为聚集索引（clustered index）和非聚集索引。
InnoDB聚集索引就是按照每张表的主键构造一颗B+树，并且叶子节点上存放着整行记录数据，而非聚集索引的叶子节点上仅保存键值以及指向数据页的偏移量。聚集索引的这个特性决定了索引组织表中数据也是索引的一部分。
ps：MyISAM索引的叶子节点上存放的是数据记录的地址。

9.4 三层高的B+树可以存放多少数据？

在InnoDB存储引擎中，最小存储单元是页，B+树每个节点都是一个页，一页大小是16KB，即16384B.
非叶子节点存索引值和页偏移量的指针，叶子节点放完整数据
索引值/主键ID，最大为bigint类型，8字节
指针：在源码中是6字节
所以一页可以放的索引数（指针数）=16384/（8+6）=1170个

叶子节点要考虑数据大小（以实际为准）、索引值（最大8B）、回滚指针（7B）、事务ID（6B）等。
设行数据占1KB.
则可存放 11701170（16/1）=2190万行数据

注：以上考虑忽略了FileHeader、PageHeader等占用较小的内容。

10. 超键、候选键、主键、外键分别是什么？

超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。

超键包含候选键和主键。

候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。

理解：
学生信息（学号身份证号性别年龄身高体重宿舍号）
宿舍信息（宿舍号楼号）
超键：只要含有“学号”或者“身份证号”两个属性的集合就叫超键，例如R1（学号性别）、R2（身份证号身高）、R3（学号身份证号）等等都可以称为超键！
候选键：不含有多余的属性的超键，比如（学号）、（身份证号）都是候选键，又比如R1中（学号）这一个属性就可以唯一标识元组了，而有没有性别这一属性对是否唯一标识元组没有任何的影响！
主键：就是用户从很多候选键选出来的一个键就是主键，比如你要求学号是主键，那么身份证号就不可以是主键了！
外键：宿舍号就是学生信息表的外键。

11. SQL 约束有哪几种？

NOT NULL: 用于控制字段的内容一定不能为空（NULL）。
UNIQUE: 控件字段内容不能重复，一个表允许有多个 Unique 约束。
PRIMARY KEY: 也是用于控件字段内容不能重复，但它在一个表只允许出现一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
CHECK: 用于控制字段的值范围。
默认约束：插入新数据时，如果该行没有指定数据，就赋默认值。

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

12. MySQL 中的 varchar 和 char 有什么区别？

char 是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用 10 个字符,
而 varchar 是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.
在检索效率上来讲,char > varchar,因此在使用中,如果确定某个字段的值的长度,可以使用 char,
否则应该尽量使用 varchar.例如存储用户 MD5 加密后的密码,则应该使用 char。

定长/变长
varchar占用空间实际字符长度+1
定长的char查询效率高
平时尽量用varchar

13. MySQL中 in 和 exists 区别

in的执行原理：（外表查询用索引）
是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

exists的执行原理：（内表查询用索引）
对外表做loop循环，每次loop循环再对内表（子查询）进行查询，那么因为对内表的查询使用的索引（内表效率高，故可用大表），而外表有多大都需要遍历，不可避免（尽量用小表），故内表大的使用exists，可加快效率；

not in 和not exists：
not in：那么内外表都进行全表扫描，没有用到索引；
not extsts：子查询依然能用到表上的索引。
所以无论那个表大，用not exists都比not in要快。

理解：

select    *      from   a     where    id    in  （select  id  from  b） ;    
select    *      from   a     where    id    exists  （select  id  from  b） ;

对于这样的sql查询同一个库，结果是一样的，但是查询速度对于不同情况，差别较大；

使用in ,先执行子查询，也就是先查询b表，再查a表
使用exists是先查主表a ,再查子表b;

对于主表数据较多时，我们使用in速度比exist更快，反之，从表b较大时，使用exist插叙速度更快（都会使用索引）,
如果使用的是not in与not exists，直接使用not exists，
因为not in会进行全表扫描不走索引，not exists会走索引。

理解简记（面试回答）

in：先查子表，再查主表。先获得子表结果集，然后外表查询匹配结果集，返回数据。主表、子表查询都用了索引。
exists：先查主表，再查子表。对主表进行loop循环，将查询到的每行数据带入到子表查询是否满足条件，子表查询用了索引。

使用场景：
子查询结果集大的，外表小的，用exists。
子查询结果集小的，外表大的，用in效率高。

无论什么时候，not exists都比not in优先使用。因为not exist还是主表遍历，子表用索引，而not in主子表都是遍历（全盘扫描）。没用上索引。

14. drop、delete与truncate的区别

SQL语言四大类：DQL、DML、DDL、TCL
DQL（Data Query Language）：数据查询语言——select、from、where组成的查询块：select <字段名> from <表或视图名> where <查询条件>
DML（Data Manipulate Language）：数据操纵语言——insert、update、delete
DDL（Data Define Languge）：数据定义语言——创建/删除数据库中的各种对象—-表、视图、索引…create、drop、truncate、alter table /view /index /syn /cluster 不能rollback
TCL（Transaction Control Language）：事务控制语言——grant（授权）、roolback[work] to [savepoint]、commit
Delete：一行一行删全部或部分数据，表结构还在
Truncate：删除全部数据，表结构还在
Drop：表结构被删除

15. 什么是存储过程及优缺点

存储过程是一些预编译的 SQL 语句。
即：一个提前写好的语句块，实现了某种功能，给这个语句块起个名字，用到的时候直接调用。
优点：执行效率高，一个存储过程代替大量SQL语句，降低网络通信量，提高数据安全
缺点：不推荐使用，管理不方便，复用性没有直接写在服务层好

16. MySQL 执行查询的过程

客户端通过 TCP 连接发送连接请求到 MySQL 连接器，连接器会对该请求进行权限验证及连接资源分配
查缓存。（当判断缓存是否命中时，MySQL 不会进行解析查询语句，而是直接使用 SQL 语句和客户端发送过来的其他原始信息。所以，任何字符上的不同，例如空格、注解等都会导致缓存的不命中。）
语法分析（SQL 语法是否写错了）。如何把语句给到预处理器，检查数据表和数据列是否存在，解析别名看是否存在歧义。
优化。是否使用索引，生成执行计划。
交给执行器，将数据保存到结果集中，同时会逐步将数据缓存到查询缓存中，最终将结果集返回给客户端。

注：更新语句执行会复杂一点。需要检查表是否有排它锁，写 binlog，刷盘，是否执行 commit。

17. SQL中的通配符

% 替代 0或多个字符
_ 替代 1个字符
[ ] 字符列中的任何单一字符
[^]、[!] 不在字符列中的任何单一字符

18. 事务的实现原理

事务是基于 重做日志(redo log) 和回滚日志(undo log) 实现的。
每提交一个事务必须先将该事务的所有日志写入到重做日志文件进行持久化，数据库就可以通过重做日志来保证事务的原子性和持久性。
每当有修改事务时，会产生 undo log，如果需要回滚，则根据 undo log 的反向语句进行逻辑操作，比如 insert 一条记录就 delete 一条记录。undo log 主要实现数据库的一致性。
重做保证原子性和持久性，回滚保证一致性

19. 在事务中可以混合使用存储引擎吗？

尽量不要在同一个事务中使用多种存储引擎，MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。
如果在事务中混合使用了事务型和非事务型的表（例如InnoDB和MyISAM表）,在正常提交的情况下不会有什么问题。
但如果该事务需要回滚，非事务型的表上的变更就无法撤销，这会导致数据库处于不一致的状态，这种情况很难修复，事务的最终结果将无法确定。所以，为每张表选择合适的存储引擎非常重要。

20. MVCC 概念及实现原理

MVCC，即多版本并发控制。
MVCC 的实现，是通过保存数据在某个时间点的快照（Snapshot）来实现的。
根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

实现原理：
InnoDB 每一行数据都有一个隐藏的回滚指针，用于指向该行修改前的最后一个历史版本，这个历史版本存放在 undo log 中。如果要执行更新操作，会将原记录放入 undo log 中，并通过隐藏的回滚指针指向 undo log 中的原记录。其它事务此时需要查询时，就是查询 undo log 中这行数据的最后一个历史版本。

MVCC 最大的好处是读不加锁，读写不冲突，极大地增加了 MySQL 的并发性。通过 MVCC，保证了事务 ACID 中的 I（隔离性）特性。

对于 InnoDB ，聚簇索引记录中包含 3 个隐藏的列：

ROW ID：隐藏的自增 ID，如果表没有主键，InnoDB 会自动按 ROW ID 产生一个聚集索引树。
事务 ID：记录最后一次修改该记录的事务 ID。
回滚指针：指向这条记录的上一个版本。

首先 insert 语句向表 t1 中插入了一条数据，a 字段为 1，b 字段为 1， ROW ID 也为 1 ，事务 ID 假设为 1，回滚指针假设为 null。当执行 update t1 set b=666 where a=1 时，步骤：

数据库会先对满足 a=1 的行加排他锁；
然后将原记录复制到 undo 表空间中；
修改 b 字段的值为 666，修改事务 ID 为 2；
并通过隐藏的回滚指针指向 undo log 中的历史记录；
事务提交，释放前面对满足 a=1 的行所加的排他锁。

理解简记

InnoDB每一行数据都有个隐藏的回滚指针，执行更新操作时，这个指针会指向修改前的原记录（存在undolog回滚日志中），当另一线程的事务查询时，查询的就是原来的最后一个历史版本。

21. 为什么要加锁？什么是死锁？

当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。
保证多用户环境下保证数据库完整性和一致性。

死锁：并发线程中，由于竞争资源或由彼此通信造成的一种阻塞现象。

22. MySQL 的连接查询

外连接、内连接、交叉连接
外连接又分成左外连接（left join）、右外连接、全外连接（MySQL不支持全外连接）
左外：显示左表中所有的数据及右表中符合条件的数据，右外反之

内连接：只显示符合条件的数据

交叉连接：使用笛卡尔乘积的一种连接

23. UNION和UNION ALL的区别

都是将两个结果集合并到一起
union回对结果集去重并排序
union all性能更好

24. 优化问题

24.1 大表数据查询如何进行优化？

24.2 慢查询如何优化？

慢查询：MySQL慢查询就是在日志中记录运行比较慢的SQL语句，这个功能需要开启才能用。

通过explain命令查看索引使用情况

在MySQL的配置文件my.cnf中写上：

slow_query_log=On
long_query_time = 10
slow_query_log_file = /var/lib/mysql/mysql-slow.log

24.3 如何优化查询过程中的数据访问？

覆盖索引

非聚集组合索引（联合索引？）的一种形式，包括查询中用到的所有列（就是建立索引的字段正好是覆盖查询语句中所涉及的字段），这样可以直接通过索引查询到数据。
SQL语句只通过索引，就能取到所需的数据，这个过程叫索引覆盖。
不然，可能会先通过辅助索引查到主键索引，再通过主键索引查到数据，这叫回表，会查两次，浪费时间。

24.4 如何优化长难的查询语句？

24.5 如何优化WHERE子句？

从使用索引考虑：
不使用不等于的判断
在涉及的列上建索引
不用not in
不用参数
不在语句中进行表达式或函数操作

24.6 大表如何优化？

24.7 怎么优化索引？

24.8 索引在什么情况下会失效？

1.不符合最左匹配原则：从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。
2.条件中有or
3.在索引上进行计算
4.在索引的类型上进行数据类型的隐形转换，如字符串一定要加引号
5.使用函数
6.使用like查询时以%开头，即不是前缀匹配就失效
7.索引字段上使用了is null/is not null判断如：select….where a is null

25. 为什么设置主键，主键用自增ID还是UUID？

主键：唯一区分表中每一行的唯一标识
自增ID优点：顺序

  - 字段长度短
  - 自动编号，按序存放，利于检索
  - 不会重复

自增ID缺点：

  - 自增，容易被被别人查到业务量
  - 数据迁移或表合并时麻烦
  - 高并发下，竞争自增锁会降低数据库的吞吐能力

UUID：通用唯一标识码，基于当前时间、计数器和硬件标识等数据计算而成 随机生成
优点：

  - 唯一标识
  - 可以在应用层生成，提高数据库的吞吐能力
  - 无需担心业务量泄露问题

缺点：

  - **随机生成**，会发生随机IO，影响插入速度
  - 占用空间大，建立的索引越多，影响越大
  - 比较大小 太慢

26. 分库分表

26.1 什么是垂直分表、垂直分库、水平分表、水平分库？

系统设计时，根据业务耦合来确定垂直分库和垂直分表
数据访问量小时，考虑缓存、读写分离
数据量大或持续增长时，考虑水平分库分表

分库：垂直按业务对表分类，水平将同一表数据拆分到不同数据库
分表：垂直按字段，水平将数据按一定规则

26.2 分库分表后，ID键如何处理？

分库分表后，要全局ID，方法：UUID、数据库自增ID、Redis生成ID、美团的Leaf分布式ID
UUID：本地生成，全局唯一，占用大，不适合作索引
数据库自增ID：需要一个专门用于生成主键的库，每次插数据都先往这库里插一条没意义的数据，获取自增ID，利用这个ID去分库分表里写数据。简单易实现，高并发下有瓶颈。

Redis生成ID：不依赖数据库，性能好，引入新组建复杂度增加

27. MySQL的复制原理及流程？如何实现主从复制？什么是读写分离？

简图：

28. 锁升级

无锁——偏向锁——轻量级锁——重量级锁

MySQL