MySQL面试整合

索引

什么是索引？

索引是一种数据结构，作用是帮助快速的对数据进行查找

索引是什么样的数据结构？

索引的数据结构与存储引擎的具体实现有关，MySQL中有Hash索引，B树索引，B+树索引等，InnoDB中使用的是B+树索引。

为什么使用索引（索引的好处）？

• 索引能大大加快检索速度，这是创建索引的主要原因、
• 通过唯一索引能够保证数据表中的数据唯一性
• 索引能够避免排序和临时表
• 将随机IO转变为顺序IO

为什么InnoDB建议使用自增的ID作为主键？

首先InnoDB会默认为主键创建聚集索引（与其说是为主键创建的索引，更形象来说应该是InnoDb的数据直接保存在一颗以主键为索引的索引树上）。
如果使用自增ID作为主键，那么在每次新数据插入时，就会顺序的将记录添加到索引树的末尾处，不需要调整树中其他节点位置。
而如果使用UUID等随机ID的话，由于每次插入的ID都是随机的，所以每次新纪录都要被插入到树的中间位置，那么就会移动索引树，增加性能消耗。同时，由于大量的移动操作，会造成索引树中大量的空间碎片，为了得到更加紧凑的索引结构，就不得不去重建表来优化页面。

什么是聚簇索引？

聚簇索引就是按照表中的主键构造的一颗B+树，其叶子节点储存的是整张表的完整的行记录数据

在 InnoDB 中，只有主键索引是聚簇索引，如果没有主键，则挑选一个唯一键建立聚簇索引。如果没有唯一键，则MySQL自动为InnoDB表生成一个隐含字段来建立聚簇索引，这个字段长度为6个字节，类型为长整形。

当查询使用聚簇索引时，在对应的叶子节点，可以获取到整行数据，因此不用再次进行回表查询。

什么是回表？

当使用非聚簇索引进行查找时，非聚簇索引的叶子节点储存的是主键值，通过查找到的主键值回到聚簇索引中查找实际元素的操作称之为回表。

什么是覆盖索引？

如果一个索引包含了所需要查询的字段，那么这个索引就被称之为覆盖索引。

如果一个查询查询到了对应的覆盖索引（需要查询的字段与索引字段相等），那么就不需要进行回表操作。

什么是联合索引？

联合索引就是使用多个字段建立一个索引，这样的索引就是联合索引。如果想要命中联合索引，就必须满足联合索引命中条件。

为什么InnoDB使用B+树的结构（B+树与其他索引结构的不同）？

1. 首先，索引和数据的保存是放在磁盘中的，当进行一条查询的时候，就需要将磁盘中的索引读取到内存中进行查询，但是由于文件较大，所以应该分区进行读取，
2. 于是确定的树形结构作为索引，树形结构可以每次只加载一个节点进入内存，在通过被加载的节点获取到下一个需要加载的节点。直到加载到需要的页，相当于每一次io操作读取的都是一个小的树节点，这就解决了IO读取文件过大的问题，但是同时引入了一个新的问题，普通的搜索树结构具有很高的深度，导致查询一个节点需要大量的IO
3. B树（多路搜索树），使用多路分叉的方式降低了树的深度，从而降低了IO次数。但是B树同样有问题，B树中非叶子节点中保存了数据，会占用内存，降低了节点的宽度（多叉树的叉的数量），而且在范围查找会遇到回旋查找问题（下级节点向上级节点回溯），增加了IO次数
4. 于是确定了B+数作为索引结构，B+数在子节点值保存下一级的索引，不保存数据，所有数据都保存在了叶子节点中，提升了节点的宽度（叉数），同时避免了回旋搜索的问题。
5. mysql中对B+数进行了优化,在所有叶子节点中增加了指向相邻叶子节点的双向指针,加快了范围查找即排序操作的速度

为什么不使用其他结构的原因

• 不使用Hash索引:是因为Hash值是无序的，无法进行索引排序，同时Hash表会产生冲突，形成拉链，降低查找效率
• 不使用平衡二叉树是因为，平衡二叉树是因为，平衡二叉树高度过高，大数据量下会进行大量IO，同时会产生回旋查找
• 不使用B树是因为，B树虽然解决了高度问题，但是高度问题还可以进一步优化（节点不存储数据，提升一个节点能够存储的向下索引的数量），而且没有经济范围查找回旋的问题

事务

什么是事务？

事务是一系列的数据库操作，这一系列操作需要满足事务的ACID特性。事务是数据库应用的基本单位。

MySQL的回滚机制？

在MySQL中，恢复机制是通过回滚日志（undo log）提供的，所有进行修改的记录都会先记录到回滚日志中，然后再对数据库进行写入。**当事务已经被提交后，就无法进行回滚了。回滚日志(undo log)就失效了。 **归档日志（bin log）和重做日志（redo log）日志这时候才开始工作。

事务隔离级别，并发事务产生的问题？

事务相关

InnoDB默认隔离级别？

MySQL 的InnoDB 默认使用可重复读（RR）级别。

但RR级别并不能解决掉幻读问题，为了解决幻读问题，InnoDB引入了间隙锁。

存储引擎

一条SQL的执行过程，InnoDB与MyISAM的区别，MySQL内部结构？

一条SQL的执行过程

优化

表结构优化？

1. 尽量使用数字型字段（提高性能）
2. 尽可能使用varchar代替char （可变长度）
3. 大量重复的数据不建立索引，如性别等字段 （索引冗余）

查询优化

应尽量避免在 where 子句中使用!=或<>操作符 （索引失效）
应尽量避免在 where 子句中使用 or 来连接条件（索引失效）
任何查询也不要出现select * （回表，增加解析器成本，无用字段增加网络消耗）
避免在 where 子句中对字段进行 null 值判断（索引失效）

索引优化

对作为查询条件和 order by的字段建立索引 （提高索引命中率）
避免建立过多的索引，多使用组合索引 （减少索引文件数量，提高索引文件复用率）

慢查询优化

分析语句，是否加载了不必要的字段/数据
分析 SQL 执行句话，是否命中索引等
如果 SQL 很复杂，优化 SQL 结构
如果表数据量太大，考虑分表

说一说三个范式?

• 第一范式: 每个列都不可以再拆分。
• 第二范式: 非主键列完全依赖于主键，而不能是依赖于主键的一部分。（无部分依赖）
• 第三范式: 非主键列只依赖于主键，不依赖于其他非主键。（无传递依赖）