数据库面试相关知识点总结

读未提交：读取另外一个未提交的事物数据

读提交： 事物要等另外一个事物提交之后才能读数据

重复读： 开始读数据时，不允许修改操作

幻读： 别的插入一条记录，并提交，读时候发现多了一条记录

二叉树 -> avl树 -> 红黑树 -> B-树 -> B+树

二叉树 ： 缺点 ，倾斜
avl树： 平衡树 任意两个子节点的高度查为1
红黑树 ：每个节点都是红色或者黑色 根节点是黑色 每个叶子节点都是黑色的空节点 
B+ ： 非叶子节点不存储数据，所有数据都存叶子节点，每个叶子节点都存相邻叶子节点的指针

jdk：小于8 是链表 大于把引入红黑树

聚簇索引就是数据和索引在一起的

MyISAM使用的是非聚簇索引，树的子节点上的data不是数据本身，而是数据存放的地址，InnoDB采用的是聚簇索引，树的叶子节点上的data就是数据本身

聚簇索引减少物理上的回表查询，维护成本其实更高，

因为物理存放顺序和索引顺序是一样的，所以一个表中，只能有一个聚簇索引，（主键就是聚簇索引，如果没有就会选下一个非空的数据，如果都没有，就会inner建一个隐藏的row-id聚簇索引）

MYSQL的覆盖索引和回表

如果只需要在一颗索引树上就可以获取SQL所需的所有列，就不需要在回表查询，这样查询速度就可以更快。
实现索引覆盖最简单的方式就是将要查询的字段，全部建立到联合索引当中

user(PK,id,name,sex)
select count(name) from user; -> 在name字段上建立一个索引
select id,name,sex from user; ->将name上的索引升成为（name,sex)的联合索引

五百万/数据文件超过2G

共同点:都是B+树的数据结构
聚簇索引:查询快，效率高，范围查询效率高，适合排序，本身是按顺序排列的(将数据存储与索引放到了一块，并且是按照一定的顺序组织的，找到索引就找到了数据，数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的)
非聚簇索引: 叶子节点不仓储数据，仓储的是数据行地址，也就是是根据索引查找到数据行的位置再取磁盘查到数据，这个有点类似一本书的目录，根据页码再去看文章

优势:
1.查询通过聚簇索引可以直接获取数据，相对非聚簇索引需要二次查询(非覆盖索引情况下)效率要搞很多
2.聚簇索引对应范围查询的效率很高，因为其数据是按照大小排序的
3.聚簇索引适合用在排序的场合，非聚簇索引不适合

劣势:
1.维护索引很昂贵，特别是插入新行或者主键被更新导致要分页(page split)的时候。建议在大量插入新行后，选在负载较低的时间段，通过OPTIMIZE TABLE优化表，因为必须倍移动的行数据可以造成碎片。使用独享表空间可以弱化碎片
2.表因为使用UUId(随机ID)作为主键，使数据存储稀疏，这就会出现聚簇索引有可能有比全表扫描更慢，所以建议使用int的auto_increment作为主键
3.如果主键比较大的话，那辅助索引将会变的更大，因为辅助索引的叶子存储的是主键值;过长的主键值，会导致非叶子节点占用更多的物理空间

InnoDB中一定有主键，主键一定是聚簇索引，不手动设置，则会使用unique索引，没有unique索引，则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引，辅助索访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

MYISM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的结构完全一致，只是存储的内容不同而已，主键索引B+树，主键索引B+树的节点仓储了主键，辅助键索引B+树仓储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真的的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检查无需访问主键的索引树。

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

索引的数据结构

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

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

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

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

执行计划参数type：
执行效率：ALL < index < range < ref < eq_ref < const < system (最好避免All和index)

const:通过索引一次命中，匹配一行数据
system:表中只有一行记录，相当于系统表
eq_ref:唯一索引扫码，对于每个索引键，表中只有一条记录与之匹配
ref:非唯一性索引扫码，返回匹配某个值的所有
range:只检索给定范围的行，使用一个索引来选择行，一般用于between,<,>;
index:只遍历索引树
All:表示全表扫描，这个类型的查询是性能最差的查询之一。那么基本就是随着表的数量增多，效率越慢；

索引的基本原理
索引用来快速地寻找那些特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理：就是把无序的数据变成有序的数据
1.把创建了索引的列的内容进行排序
2.对排序结果生成倒排表
3.在倒排表内容上拼上数据地址链
4.在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

简述MyISAM和InnoDB的区别
不支持事物，但是每次查询都是原子的;
支持表级锁，即每次操作是对整个表加锁
存储表的总行数：
一个MYISAM表有三个文件：索引文件，表结构文件，数据文件；
采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。
InnoDB
支持ACID的事物，支持事物的四种隔离级别
支持行级锁及外键约束；因此可以支持写并发；
不存储总行数；
一个InnoDb引擎存储在一个文件空间(共享表空间，表大小不受操作系统控制，一个表可能发布在多个文件里),
也有可能为多个(设置为独立表空，表大小受操作系统文件大小限制，一般为2G)，受操作系统文件大小的限制；

MySQL中索引类型及对数据库的性能的影响
普通索引:允许倍索引的数据列包含重复值
唯一索引:可以保证数据记录的唯一性
主键：是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字PRIMARY KEY来创建。
联合索引：索引可以覆盖多个数据列，如像：INDEX(columnA,columnB)索引。
全文索引：通过建立倒排索引，可以极大的提升检索效率，解决判断字段是否包含的问题，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT(column);创建全文索引

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

简述MySQL中的日志log
redo log: 存储引擎级别的log（InnoDB有，MyISAM没有），该log关注于事务的恢复.在重启mysql服务
的时候，根据redo log进行重做，从而使事务有持久性。
undo log：是存储引擎级别的log（InnoDB有，MyISAM没有）保证数据的原子性，该log保存了事务发
生之前的数据的一个版本，可以用于回滚，是MVCC的重要实现方法之一。
bin log：数据库级别的log，关注恢复数据库的数据。

数据库中多个事务同时进行可能会出现什么问题？
1.丢失修改
2.脏读：当前事务可以查看到别的事务未提交的数据。
3.不可重读：在同一事务中，使用相同的查询语句，同一数据资源莫名改变了。
4.幻读：在同一事务中，使用相同的查询语句，莫名多出了一些之前不存在的数据，或莫名少了一些
原先存在的数据。

读提交和可重复读都基于MVCC实现，有什么区别？
在可重复读级别下，只会在事务开始前创建视图，事务中后续的查询共用一个视图。而读提交级别下每
个语句执行前都会创建新的视图。因此对于可重复读，查询只能看到事务创建前就已经提交的数据。而
对于读提交，查询能看到每个语句启动前已经提交的数据。

InnoDB如何保证事务的原子性、持久性和一致性？
利用undo log保障原子性。该log保存了事务发生之前的数据的一个版本，可以用于回滚，从而保证事务
原子性。
利用redo log保证事务的持久性，该log关注于事务的恢复.在重启mysql服务的时候，根据redo log进行
重做，从而使事务有持久性。
利用undo log+redo log保障一致性。事务中的执行需要redo log，如果执行失败，需要undo log 回滚。

redo log与binlog的区别？
1. redo log是InnoDB引擎特有的，只记录该引擎中表的修改记录。binlog是MySQL的Server层实现
的，会记录所有引擎对数据库的修改。
2. redo log是物理日志，记录的是在具体某个数据页上做了什么修改；binlog是逻辑日志，记录的是这
个语句的原始逻辑。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的，binlog文件写到一定大小后会切
换到下一个，并不会覆盖以前的日志。

WAL技术是什么？
WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。事务在提交写入磁盘前，
会先写到redo log里面去。如果直接写入磁盘涉及磁盘的随机I/O访问，涉及磁盘随机I/O访问是非常消耗
时间的一个过程，相比之下先写入redo log，后面再找合适的时机批量刷盘能提升性能。

两阶段提交是什么？
为了保证binlog和redo log两份日志的逻辑一致，最终保证恢复到主备数据库的数据是一致的，采用两阶
段提交的机制。
1. 执行器调用存储引擎接口，存储引擎将修改更新到内存中后，将修改操作记录redo log中，此时
redo log处于prepare状态。
2. 存储引擎告知执行器执行完毕，执行器生成这个操作对应的binlog，并把binlog写入磁盘。
3. 执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交commit状态，更新完成。
只靠binlog可以支持数据库崩溃恢复吗？
不可以。
历史原因：
1. InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持
的引擎了。InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那引入InnoDB原有的
redo log来保证崩溃恢复能力。
实现原因：
2. binlog没有记录数据页修改的详细信息，不具备恢复数据页的能力。binlog记录着数据行的增删改，
但是不记录事务对数据页的改动，这样细致的改动只记录在redo log中。当一个事务做增删改时，
其实涉及到的数据页改动非常细致和复杂，包括行的字段改动以及行头部以及数据页头部的改动，
甚至b+tree会因为插入一行而发生若干次页面分裂，那么事务也会把所有这些改动记录下来到redo
log中。因为数据库系统进程crash时刻，磁盘上面页面镜像可以非常混乱，其中有些页面含有一些
正在运行着的事务的改动，而一些已提交的事务的改动并没有刷上磁盘。事务恢复过程可以理解为
是要把没有提交的事务的页面改动都去掉，并把已经提交的事务的页面改动都加上去这样一个过
程。这些信息，都是binlog中没有记录的，只记录在了存储引擎的redo log中。
3. 操作写入binlog可细分为write和fsync两个过程，write指的就是指把日志写入到文件系统的page
cache，并没有把数据持久化到磁盘,fsync才是将数据持久化到磁盘的操作。通过参数设置
sync_binlog为0的时候，表示每次提交事务都只write，不fsync。此时数据库崩溃可能导致部分提交
的事务以及binlog日志由于没有持久化而丢失。
简述MySQL主从复制
MySQL提供主从复制功能，可以方便的实现数据的多处自动备份，不仅能增加数据库的安全性，还能进
行读写分离，提升数据库负载性能。
主从复制流程：
1. 在事务完成之前，主库在binlog上记录这些改变，完成binlog写入过程后，主库通知存储引擎提交事
物2. 从库将主库的binlog复制到对应的中继日志，即开辟一个I/O工作线程，I/O线程在主库上打开一个普
通的连接，然后开始binlog dump process，将这些事件写入中继日志。从主库的binlog中读取事
件，如果已经读到最新了，线程进入睡眠并等待ma主库产生新的事件。
读写分离：即只在MySQL主库上写，只在MySQL从库上读，以减少数据库压力，提高性能。