【八股文】MySQL
1.数据库的三范式是什么
2.什么是事务
3.说一下ACID是什么
4.mysql索引是如何实现的
5.mysql支持哪几种锁
6.sql的几种连接查询方式（内连接，外连接，全连接，联合查询）
7.sql查询的基本原理
8.MySQL体系结构，以及各自的作用
9.MySQL运行机制
10.MySQL存储引擎MyISAM与InnoDB区别，以及选哪个
11.InnoDB内存结构和磁盘结构
12.InnoDB线程模型
13.索引的分类
14.索引的优缺点
15.什么是情况下需要建立索引
16.索引的数据结构
17.最左匹配原则
18.聚簇索引
19.回表和覆盖索引
20.并发事务带来的问题
21.四大隔离级别
22.MVCC并发版本实现原理
23.快照读和当前读
24.行锁原理
25.数据库的乐观锁和悲观锁是什么？怎么实现的
26.sql性能优化
27.复杂sql训练
1.数据库的三范式是什么
- 第一范式：强调的是列的原子性，即数据库表的每一列是不可分割的原子数据项
- 第二范式：要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖关键字一部分的属性
- 第三范式：任何非主属性不依赖于其他非主属性，不能有传递依赖
2.什么是事务
一个完整的操作逻辑
3.说一下ACID是什么
- 原子性：一个事务要么成功，要么失败
- 一致性：最终结果一致
- 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。读未提交，读已提交，可重复读，可串行化
- 持久性：事务处理结束后，对数据的修改是永久的，即便系统故障也不会丢失
4.mysql索引是如何实现的
索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据
具体来说mysql的索引，主流的数据引擎的索引都是通过B+树实现的。
5.mysql支持哪几种锁
- 按锁的类别：读锁（共享锁）和写锁（排他锁）
- 按锁的粒度：表级锁，行级锁，页级锁
- 按锁的性能：乐观锁，悲观锁
6.sql的几种连接查询方式（内连接，外连接，全连接，联合查询）
内连接：最常用的连接方式，查询两张表的交集
外连接：
左外连接（left join），以左表为主表，全部显示
右外连接（right join）
全连接：两个表的所有数据都展示出来（mysql不识别）
联合查询
7.sql查询的基本原理
单表查询：跟据WHERE条件过滤表中的记录，形成中间表（对用户不可见），然后跟据SELECT的选择列选择相应的列进行返回结果
两张表查询：对两张表求笛卡尔积，并用ON条件和连接类型过滤形成中间表，然后进行单表查询
多表连接查询：先对第一个表和第二个表按照两张表连接做查询，然后用查询结果和第三个表做连接查询
8.MySQL体系结构，以及各自的作用
一、网络连接层
提供与MySQL服务器建立的连接
二、核心层
1.连接池：负责存储和管理客户端和数据库的连接，一个线程负责管理一个连接
2.系统管理和控制工具：例如备份恢复，安全管理，集群管理
3.SQL接口：用于接受客户端发送的各种SQL命令，并且返回用户需要查询的结果。比如DML,DDL，存储过程，视图，触发器
4.解析器：负责将请求的SQL解析生成一个“解析树”，然后跟据一些MySQL规则进一步检查解析树是否合法
5.查询优化器：当解析树通过解析器语法检查后，将交由优化器将其转化成执行计划，然后与存储引擎交互
6.缓存：缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，权限缓存，引擎缓存等。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
三、存储引擎层
存储引擎负责MySQL中数据的存储与提取，与底层系统文件进行交互。MySQL存储引擎是插件式的，服务器中的查询执行引擎通过接口与存储引擎进行通信，接口屏蔽了不同的存储引擎之间的差异。现在有很多种存储引擎。最常见的是MyISAM,InnoDB
四、系统文件层
该层负责将数据库的数据和日志存储在文件系统之上，并完成与存储引擎的交互，是文件的物理存储层，主要包含日志文件，数据文件，配置文件，pid文件，socket文件
9.MySQL运行机制
10.MySQL存储引擎MyISAM与InnoDB区别，以及选哪个
InnoDB引擎：InnoDB引擎提供了对数据库ACID事务的支持，并且还提供了行级锁和外键的约束。他的设计的目标就是处理大数据容量的数据库系统
MyISAM：不提供事务的支持，也不提供行级锁和外键
11.InnoDB内存结构和磁盘结构
12.InnoDB线程模型
13.索引的分类
普通索引：没有任何限制
唯一索引：索引字段的值必须唯一，但可以为空。在创建或修改表时追加唯一索引，就会自动创建对应的唯一索引
主键索引：在创建或修改表时追加主键约束即可，每个表只能有一个主键
复合索引：索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引超过2列的索引
全文索引：使用match和against关键字
14.索引的优缺点
优点：
缺点：
- 建立索引需要占用物理空间
- 会降低表的增删改的效率，因为每次对表记录进行增删改，需要进行动态维护索引，导致增删改时间变长
15.什么是情况下需要建立索引
1）为常作为查询条件的字段建立索引，where字句的列，或者连接子句中指定的列
2）为经常需要排序，分组操作的字段建立索引
3）更新频繁字段不适合创建索引
4）不能有效区分数据的列不适合做索引列
5）对于定义为text，image和bit的数据类型的列不要建立索引
6）最左前缀原则
7）非空字段：应该指定列为NOT NULL，除非你想存储NULL。含有空值的列很难进行查询优化
8）不要过度索引。索引需要额外的磁盘空间，并降低写操作的性能。在修改表内容的时候，索引会进行更新甚至重构
怎么提升索引的使用效率
先使用explain分析一下原因
select_type：每个select子句的类型
type：对表访问方式，表示MySQL在表中找到所需行的方式。all：（全表），index：（索引树），
key：实际使用到的索引
1）选择唯一性索引：唯一性索引的值是唯一的，可以更快速的该索引来确定某条记录。（过滤性不好）
2）为经常作为查询条件的字段建立索引
3）限制索引的数目：
16.索引的数据结构
B+树和Hash
进行查找操作时，首先在根节点进行二分查找，找到key所在的指针，然后递归地在指针所指向的节点进行查找。直到查找到叶子节点，然后在叶子节点上进行二分查找，找出key所对应的数据项。
B+树和B树的区别？
- 由于B+树所有的索引都在叶子结点上，并且结点之间有指针连接，在找某个大于关键字或者小于关键字的数据的时候，B+树只需要找到该关键字然后沿着链表遍历即可
- 由于B树每个结点都存储索引+实际数据，B+树非叶子节点只存储索引信息。同一页B+树可以存储的数据更多。
- 总体来说B+树提高了更好的范围查询
17.最左匹配原则
如果sql语句中用到了组合索引中的最左边的索引，那么这条sql语句就可以利用这个组合索引去进行匹配。当遇到范围查询（>, <, between，like）就会停止匹配，后面的字段就不用到索引。
对（a，b，c，d）建立索引，查询条件为a = 1 and b = 2 and c > 3 and d = 4，那么abc都能走索引，d无法使用索引
对（a，b）建立索引，a在索引树是全局有序的，而b是局部有序（a相等时，会对b进行排序）
18.聚簇索引
聚簇索引：索引的叶子节点就是数据节点，是物理连续，一张表只能有一个聚簇索引（一般用于主键）
非聚簇索引：索引的叶子节点仍为索引，有一个指针指向对应的数据块
19.回表和覆盖索引
回表查询：先通过普通索引的值定位到聚簇索引值，再通过聚簇索引的值定位到行记录数据，要通过扫描两次索引B+树，它的性能较扫描一次比较低
索引覆盖：只需要在一颗索引树上就能获取sql所需的所有列数据，无需回表，速度更快
实现方式：将被查询的字段，建立到联合索引里去（若查询有where条件，where后面的字段必须为索引字段）
哪些地方需要用到索引覆盖来优化SQL：
- 全表count查询优化
- 分页查询
20.并发事务带来的问题
- 更新丢失
回滚覆盖：一个事务回滚操作，把其他事务已提交的数据给覆盖了
提交覆盖：一个事务提交操作，把其他事务已提交的数据给提交了
这个其实就是不加任何修改导致的问题，两个事务可以随意操作数据。
- 脏读：一个事务读取到了另一个事务修改但未提交的数据
事务A和事务B同时开启事务，事务A修改了数据，但未提交，事务B读到了事务A修改但未提交的事务。这个时候事务A回滚事务或者事务B修改数据都会出现问题
- 不可重复读：一个事务多次读取同一行记录不一致
事务A和事务B同时开启事务，事务A读取工资为1000，事务B修改工资为2000，并提交事务。事务A还没有提交事务，而且不知道事务B对工资进行修改，这个时候事务A再次读取数据时工资变成了2000，产生了不可重复读
- 幻读：一个事务多次查询，结果不一致，行记录多了或少了
事务A和事务B同时开启事务，事务A读取工资为1000的员工为10个，事务B插入一个新的员工工资为1000，并提交事务。事务A再次读取时员工变成11个。产生了幻读。幻读强调的是新增或者删除
21.四大隔离级别
1）读未提交：一个事务可以读取另一个事务更新但未提交的数据。可能导致脏读，不可重读，幻读
2）读已提交：一个事务提交后才能被其他事务读取到，可以阻止脏读，但可能导致不可重复读，幻读
3）可重复读（MySQL默认）：对同一记录的多次读取结果都是一致的，除非数据是被本身事务所修改。可能导致幻读
4）可串行化：所有的事务串行执行
事务隔离级别的实现基于锁机制和并发调度，其中并发调度使用的MVCC（多版本并发控制），通过保存修改的旧版本信息来支持并发一致性读和回滚等特征。
22.MVCC并发版本实现原理
多版本控制，是指在数据库中为了实现高并发的数据访问，对数据进行多版本控制，并通过事务的可见性来保证事务能看到自己应该看到的数据版本，只在读已提交和可重复读下有效。
MVCC实现原理
MVCC是通过每行记录后面保存两个隐藏的列来实现的。一个保存了行的创建时间，一个保存了行的过期时间。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较
举例：在可重复读的情况
SELECT:
InnoDB会跟据以下两个条件检查每行记录：
InnoDB只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或修改过的
行的删除版本要么未定义，要么大于当前事务版本号。这样可以确保事务读取到的行，在事务开始之前未被删除
INSERT：
InnoDB为新插入的每一行保存当前系统版本号为行版本号
DELETE:
InnoDB为删除的每一行保存当前系统版本号作为行删除标识
UPDATE：
InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识
保存这两个额外系统版本号，使大多数读操作都可以不用加锁。
23.快照读和当前读
- 快照读：读取的是快照版本，普通的select就是快照读。通过mvcc来进行并发控制的，不用加锁（有可能读到历史版本）
- 当前读：读取的是最新版本，update，delete，insert，for update是当前读
快照读情况下，InnoDB通过mvcc机制避免了幻读现象。而mvcc机制无法避免当前读情况下出现的幻读现象。因为当前读每次读取的都是最新数据，这时如果两次查询中间有其它事务插入数据，就会产生幻读。
那么MySQL是如何避免幻读？
- 在快照读情况下，MySQL通过
mvcc来避免幻读。 - 在当前读情况下，MySQL通过
next-key来避免幻读（加行锁和间隙锁来实现的）。
next-key包括两部分：行锁和间隙锁。行锁是加在索引上的锁，间隙锁是加在索引之间的。
Serializable隔离级别也可以避免幻读，会锁住整张表，并发性极低，一般不会使用。
24.行锁原理
InnoDB行锁是通过对索引数据页上的记录加锁实现的
主要实现算法有三种：Record Lock，Gap Lock和Next-key Lock
Record Lock（记录锁）：锁定单个行记录的锁，RC（读已提交），RR（可重复读）隔离级别都支持
GapLock（范围锁）：锁定索引记录间隙，确保索引记录的间隙不变。RR
Next-key Lock（记录锁+范围锁）：记录锁和间隙锁组合，同时锁住数据，并且锁住数据前后范围。RR
25.数据库的乐观锁和悲观锁是什么？怎么实现的
数据库管理系统中的并发控制的任务是确保在多个事务同时存取同一数据时不破坏事务的隔离性和一致性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段
悲观锁：假定会发生并发冲突，每次去查询数据的时候都认为别人会修改，每次查询完数据的时候就把事务锁起来，直到提交事务。
实现方式：使用数据库中的锁机制
乐观锁：假设不会发生并发冲突，每次去查询数据的时候都会认为别人不会修改，所以不会上锁，在修改数据的时候才把事务锁起来。
实现方式：乐观锁一般会使用版本号机制或CAS算法实现
26.sql性能优化
- select尽量不要*，务必写明字段，避免不必要的消耗
- 合理使用索引
- 尽量减少join，join太多的时候，mysql容易选错索引
- 避免类型转换
- 当只需要一条数据的时候，使用limit 1，查找就不用继续往后找了
27.复杂sql训练
distinct用来查询不重复记录的条数
#Java##MySQL##八股文#本专栏是我总结的八股大全
查看25道真题和解析