数据库面试题

第一章：数据库事务

一、事务的特性：

①原子性：事务作为一个整体被执行，要么全部执行，要么全部不执行。
②一致性：保证数据库的状态从一个一致状态转变为另一个一致状态。
③隔离性：多个事务并发执行时，一个事务的执行并不影响其他事务的执行。
④持久性：一个事务一旦提交，对数据库的修改应该永久保存。

事务的并发访问问题（由隔离性引起）：
①脏读：B事务读取到了A事务尚未提交的数据。
②不可重复读：一个事务中，两次读取的数据的内容不一致。
③幻读/虚读：一个事务中，两次读取的数据的数量不一致。

事务的隔离级别：
答：读未提交、读已提交、可重复读和序列化
①读取尚未提交的数据：哪个问题都不能解决。
②读取已经提交的数据：可以解决脏读。oracle默认。
③重读读取：可以解决脏读，不可重复读。mysql默认。
④串行化：都可以解决。---相当于锁表，一般没人用，效率太低。

二、数据库锁之悲观锁和乐观锁：

悲观锁的含义：锁如其名，他对世界是悲观的，他认为别人访问正在改变的数据的概率是很高的，所以从数据开始更改时就将数据锁住，直到更改完成才释放。

缺点：悲观锁可能会造成加锁的时间很长，并发性不好，特别是长事务，影响系统的整体性能。
悲观锁的实现方式：
  悲观锁，也是基于数据库的锁机制实现。 传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。

乐观锁的含义：他对世界比较乐观，认为别人访问正在改变的数据的概率是很低的，所以直到修改完成，准备提交所做的修改到数据库的时候才会将数据锁住， 当你读取以及改变该对象时并不加锁，完成更改后释放。乐观锁不能解决脏读的问题。
优点：乐观锁加锁的时间要比悲观锁短，大大提升了大并发量下的系统整体性能表现。
乐观锁的实现方式：

1、大多是基于数据版本（Version ）记录机制实现， 需要为每一行数据增加一个版本标识（也就是每一行数据多一个字段 version），每次更新数据都要更新对应的版本号+1。

2、使用时间戳来实现
  同样是在需要乐观锁控制的 table 中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp），和上面的 version 类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则 OK，否则就是版本冲突。

更加详细的数据库锁，请参考博客https://blog.csdn.net/zengxiantao1994/article/details/90141792

三、数据库索引

创建索引：

Create Index <Index-Name> On <Table_Name>(Column_Name);

什么样的字段适合建索引

唯一、不为空、经常被查询的字段、两表连接的字段。

创建索引可以大大提高系统的性能（优点）：

1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

增加索引也有许多不利的方面(缺点)：

1.创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

2.索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

什么时候索引失效：

① Not Null/Null 如果某列建立索引,当进行Select * from emp where depto is not null/is null。 则会是索引失效。
② 索引列上不要使用函数,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = 'ABC' 
或者SELECT Col FROM tbl WHERE name LIKE '%ABC%' 而SELECT Col FROM tbl WHERE name LIKE 'ABC%' 会使用索引。

③ 索引列上不能进行计算SELECT Col FROM tbl WHERE col / 10 > 10 则会使索引失效，应该改成
SELECT Col FROM tbl WHERE col > 10 * 10

④ 索引列上不要使用NOT （ != 、 <> ）如:SELECT Col FROM tbl WHERE col ! = 10 
应该 改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

四、MySQL索引

MySQL中的索引设计成树，order by，group by，< >这种排序查询，遇到这种情况，hash就会退化成O(n)，而树因为它的有序性依然保持O(log(n))高效率。

B树和B+树：

1.B+树的非叶子节点只保存索引，不保存行记录。而B树叶子节点和非叶子节点都存储索引和行记录，这样的话，同样的空间，B+树能存储更多的索引。这就意味着同样的数据，B+树比B树更”矮胖“，减少IO次数。
2.查询的时候，由于B树叶子节点和非叶子节点都存在行记录，也就是是说，B树查询其实是不稳定的（好的时候，只查根节点，坏的时候，查到叶子节点）。而B+树查询最终必须到叶子节点，查询销量稳定。
3.B树范围查询只能通过中序遍历查询来定位最小和最大值，而B+树通过链表就能实现，查询更方便。
综合起来：B+树比B树优势有三个：1.IO次数更少；2.查询性能更稳定；3.范围查询简便。

MYSQL的俩种存储引擎区别（事务、锁级别等等），各自的使用场所

引擎	特性
MYISAM	不支持外键、表锁、插入数据时、锁定真个表、查表总行数时、不需全表扫描
INNODB	支持外键、行锁、查表总行数时、全表扫描

Innodb存储引擎

使用的是B+树，他存在有一个主键索引和辅助索引两种索引，主键索引是在生成主键时就有的索引，他的叶子节点中存放的就是数据行，所以又称之为聚集索引。

而另一类索引，辅助索引，就是我们人为新建的索引，他的叶子节点中存放的是主键，当我们通过辅助索引查找到主键之后，再通过查找的主键去查找主键索引。

MyIsam存储引擎

MyIsam虽然他用的是B+树，但是没有用聚集索引，他的主键索引和辅助索引没有任何区别，都是在叶子中存储数据行的物理地址，这也使得他的读性能更高。

总结：

1、innodb支持事务，且默认是Autocommit，所以每一条SQL语句都会封装成一个事务，如果执行多条事务，最好加上begin和commit。MyIsam不支持事务，也就无法回滚；

2、另外Innodb支持行锁，MyIsam进行写操作会全表上锁，所以MyIsam的写操作性能会差些；

3、所以，在查询较多的表中，使用MyIsam较优，写比较多的表，使用Innodb；