数据库基础知识点

目录

1、mysql两种存储引擎的区别与场景？

2、mysql b+索引和hash索引的区别？

2.1、为啥b+树适合作为索引的结构

3、索引的分类

4、事务四大特性（ACID）

5、数据库隔离级别？每个级别会引发什么问题？

6、mysql的锁算法

7、MVCC机制

---

1、mysql两种存储引擎的区别与场景？

MyISAM 

不支持事务；支持表锁，每次操作都是对整表加锁；myisam 数据文件和索引文件分开保存；采用非聚集索引，索引文件数据域存储的是数据文件的指针地址；适用于OLAP；

InnoDb

支持ACID；支持事务的四种隔离级别；

支持行锁及外键约束；支持写并发；数据文件和索引文件在一块存储；主键索引采用聚集索引（索引的数据域存储数据本身），辅助索引存储主键值，所以通过辅助索引查询可能需要回表操作。适用OLTP。

2、mysql b+索引和hash索引的区别？

hash索引主要适用于等值查询，因为只需要经过一次算法即可查找到响应键值；当然前提是键值唯一，如果不唯一，则需要找到键位置后，根据链表往后扫描，直到找到数据为止。

如果是范围查询，hash索引就毫无用武之地。因为原先有序的数据，经过哈希算法，可能不再有序，就没有办法利用索引完成范围查询。

同理，哈希索引也没办法利用索引完成排序，以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；

B+树索引的关键检索效率比较平均，不想B树波动很大，在有大量重复键值情况下，哈希索引的效率也是极低的，因为存在所谓的哈希碰撞问题。

2.1、为啥b+树适合作为索引的结构

B树：有序数组+平衡多叉树

B+树：有序数组链表+平衡多叉树 叶子存储数据，空间占用小，且是双链表，修改效率快

B树适用于随机检索，B+树同时支持索引检索和顺序检索。

数据库采用B+树的主要原因是B树在提高了磁盘IO的同时并没有解决元素遍历效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。

2.2、B树和B+树的区别

请参考B树/B+树

3、索引的分类

索引主要分为以下：

普通索引：最普通的索引，没有任何限制

唯一索引：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

主键索引：它 是一种特殊的唯一索引，不允许有空值。

全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

4、事务四大特性（ACID）

    原子性(Atomicity):原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

    一致性(Consistency):如果事务执行之前数据库是一个完整性的状态,那么事务结束后,无论事务是否执行成功,数据库仍然是一个完整性状态。 (数据库的完整性状态:当一个数据库中的所有的数据都符合数据库中所定义的所有的约束,此时可以称数据库是一个完整性状态。)

    隔离性(Isolation):事务的隔离性是指多个用户并发访问数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

    持久性(durability):持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

5、数据库隔离级别？每个级别会引发什么问题？

SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。

    Read Uncommitted（读取未提交内容）

    在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

    Read Committed（读取提交内容）

    这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。 这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。

    Repeatable Read（可重读）

    这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。 简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

    Serializable（可串行化）

    这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。 这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。

    脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

    不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

    幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

    读不影响写：事务以排他锁的形式修改原始数据，读时不加锁，因为 MySQL 在事务隔离级别Read committed 、Repeatable Read下，InnoDB 存储引擎采用非锁定性一致读－－即读取不占用和等待表上的锁。即采用的是MVCC中一致性非锁定读模式。 因读时不加锁，所以不会阻塞其他事物在相同记录上加 X锁来更改这行记录。

    写不影响读：事务以排他锁的形式修改原始数据，当读取的行正在执行 delete 或者 update 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。

    间隙锁：间隙锁主要用来防止幻读，用在repeatable-read隔离级别下，指的是当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！ 当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock,即仅锁住索引本身，而不是范围！若是普通辅助索引，则会使用传统的next-key lock进行范围锁定！

6、mysql的锁算法

     Record Lock：单个行记录上的锁。

    Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。

    Next-Key Lock：1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

7、MVCC机制

MVCC的全称是“多版本并发控制”。这项技术使得InnoDB的事务隔离级别下执行一致性读操作有了保证，换言之，就是为了查询一些正在被另一个事务更新的行，并且可以看到它们被更新之前的值。 这是一个可以用来增强并发性的强大的技术，因为这样的一来的话查询就不用等待另一个事务释放锁。这项技术在数据库领域并不是普遍使用的。一些其它的数据库产品，以及mysql其它的存储引擎并不支持它。

    mysql的innodb采用的是行锁，而且采用了多版本并发控制来提高读操作的性能。

1.什么是多版本并发控制呢MVCC ？

    其实就是在每一行记录的后面增加两个隐藏列，记录创建版本号和删除版本号，而每一个事务在启动的时候，都有一个唯一的递增的版本号。 在InnoDB中，给每行增加两个隐藏字段来实现MVCC，两个列都用来存储事务的版本号，每开启一个新事务，事务的版本号就会递增。

2.默认的隔离级别（REPEATABLE READ）下，增删查改？

    SELECT

    读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的

    INSERT

    将当前事务的版本号保存至行的创建版本号

    UPDATE

    新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号

    DELETE

    将当前事务的版本号保存至行的删除版本号

3.什么是快照读和当前读？

    快照读：读取的是快照版本，也就是历史版本

    当前读：读取的是最新版本

    普通的SELECT就是快照读，而UPDATE、DELETE、INSERT、SELECT …  LOCK IN SHARE MODE、SELECT … FOR UPDATE是当前读。

4.什么是锁定读？

    在一个事务中，标准的SELECT语句是不会加锁，但是有两种情况例外。

    SELECT ... LOCK IN SHARE MODE 给记录假设共享锁，这样一来的话，其它事务只能读不能修改，直到当前事务提交

    SELECT ... FOR UPDATE 给索引记录加锁，这种情况下跟UPDATE的加锁情况是一样的

5.什么是一致性非锁定读？

    consistent read （一致性读），InnoDB用多版本来提供查询数据库在某个时间点的快照。如果隔离级别是REPEATABLE READ，那么在同一个事务中的所有一致性读都读的是事务中第一个这样的读读到的快照； 如果是READ COMMITTED，那么一个事务中的每一个一致性读都会读到它自己刷新的快照版本。Consistent read（一致性读）是READ COMMITTED和REPEATABLE READ隔离级别下普通SELECT语句默认的模式。 一致性读不会给它所访问的表加任何形式的锁，因此其它事务可以同时并发的修改它们。

    MVCC实现一致性非锁定读，这就有保证在同一个事务中多次读取相同的数据返回的结果是一样的，解决了不可重复读的问题。

6.什么是悲观锁和乐观锁？

        悲观锁：正如它的名字那样，数据库总是认为别人会去修改它所要操作的数据，因此在数据库处理过程中将数据加锁。其实现依靠数据库底层。

        乐观锁：如它的名字那样，总是认为别人不会去修改，只有在提交更新的时候去检查数据的状态。通常是给数据增加一个字段来标识数据的版本。

7.select时怎么加排它锁？

    使用锁定读，普通select不会引起加锁，而是去读取最新的快照。同上4

    事务以排他锁的形式修改原始数据，当读取的数据正在进行更新等操作，则直接去读取快照，而不是等锁释放