数据库面经

1、Mysql的超键，候选键，主键，外键：

（１）超键：在关系中能唯一标识元组的属性集称为超键，可以区分表中每一行记录的属性集（单个属性，多个属性组成也行）

　　1）例子：在一个学生的表中，假设有“学号”、“姓名”、“相关信息”、“生日”等字段， 其中学号是唯一的，那么（学号）是一个超键，同时（学号，姓名，生日）的组合也是唯一的，所以也可以为一个超键。反正记住一点，就是根据这些属性可以唯一确定一名学生的，就是超键。

（２）候选键：候选键是在超键的基础上定义的，在要求可以区分每一行的基础上，同时是最小唯一的，即候选键中删除任何一个属性后就不能再区分每一行。

（３）主键：从候选键中选出一个键就能区分每一行的就是主键．

（４）外键：在关系模式(表)R中，属性a是其他模式的主键，则a是关系模式R中的外键．

（5）自增主键和非自增主键

　　1）自增主键：如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

　　2）非自增主键：如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据。甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

（6）范式

　　1）第一范式就是属性不可分割，每个字段都应该是不可再拆分的。如一个字段是姓名（NAME），姓名是一个不可再拆分的单位，此时符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

　　2）第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，因此第二范式只要记住主键约束就好了。比如说有一个表是学生表，学生表中有一个值唯一的字段学号，那么学生表中的其他所有字段都可以根据这个学号字段去获取，依赖主键的意思也就是相关的意思，因为学号的值是唯一的，因此就不会造成存储的信息对不上的问题，即学生001的姓名不会存到学生002那里去。

　　3）第三范式就是要求表中不能有其他表中存在的、存储相同信息的字段，通常实现是在通过外键去建立关联，因此第三范式只要记住外键约束就好了。比如说有一个表是学生表，学生表中有学号，姓名等字段，那如果要把他的系编号，系主任，系主任也存到这个学生表中，那就会造成数据大量的冗余，一是这些信息在系信息表中已存在，二是系中有1000个学生的话这些信息就要存1000遍。因此第三范式的做法是在学生表中增加一个系编号的字段（外键），与系信息表做关联。

2、MySQL的基本存储结构：页，记录都存储在页里面。

（1）各个数据页可以组成一个双向链表。

（2）每个数据页中的行记录组成单向链表。

（3）每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

（4）以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

3、为什么没有任何优化的查询慢：

（1）需要遍历双向链表，找到所在的页

（2）从所在的页内中查找相应的记录

（3）由于不是根据你主键查询，只能遍历所在的页的单链表。

4、java如何连接数据库

（1）动态加载mysql驱动：

　　Class.forName("com.mysql.jdbc.Driver");

（2）定义数据库连接串，其中指明数据库安装服务器，数据库服务端口，数据库名称，用户名和密码

　　String url = "jdbc:mysql://localhost:3306/jdbcdemo?user=root&password=root";

（3）获取数据库连接

　　Connection conn = DriverManager.getConnection(url);

（4）获取 Statement 对象，这个对象中提供对数据库操作的各种方法

　　　Statement stmt = conn.createStatement();

（5）执行SQL语句，如向目标表中插入一条数据

　　String sql = "insert into student (id, name) values ('1000001', '张三')";

　　执行SQL语句，返回插入或者更新成功的数据行数：int executeResult = stmt.executeUpdate(sql);

5、数据库的索引：

（1）实例：表Employee(Name，Age，Address)，查询语句：SELECT * FROM Employee WHERE Name = 'ABC'

　　1）若无索引：运行此SQL语句时，在查找名字为Jesus的雇员的过程中，MySQL不得不从Employee表中的每一行并确定雇员的名字（Name）是否为 ‘Jesus’。由于我们想要得到每一个名字为Jesus的雇员信息，在查询到第一个符合条件的行后，不能停止查询，因为可能还有其他符合条件的行。所以，必须一行一行的查找直到最后一行-这就意味数据库不得不检查上千行数据才能找到所以名字为Jesus的雇员。这就是所谓的全表扫描

　　2）使用索引：使用索引的全部意义就是通过缩小一张表中需要查询的记录/行的数目来加快搜索的速度。索引基本上是用来存储列值的数据结构，这使查找这些列值更加快速。如果索引使用最常用的数据结构-B＋树-那么其中的数据是有序的。有序的列值可以极大的提升性能。我们在Name这一列上创建一个索引。这意味着当我们用之前的SQL查找姓名是‘Jesus’的雇员时，不需要再扫描全表。而是用索引查找去查找名字为‘Jesus’的雇员，因为索引已经按照按字母顺序排序。索引已经排序意味着查询一个名字会快很多，因为名字少字母为‘J’的员工都是排列在一起的。另外重要的一点是，索引同时存储了表中相应行的指针以获取其他列的数据。

　　3）索引定义：一个索引是存储的表中一个特定列的值数据结构（最常见的是B+Tree和hash表）。索引是在表的列上创建。所以，要记住的关键点是索引包含一个表中列的值，并且这些值存储在一个数据结构中。索引是一种数据结构 。

　　4）索引里存的是什么：索引并不存储这个表中其他列（字段）的值。例如，若我们在Name列创建索引，那么列Age和Address上的值并不会存储在这个索引当中。如果把其他所有字段也存储在个这个索引中，那就成了拷贝一整张表做为索引，这样会占用太大的空间而且会十分低效。同时存储了指向表中的相应行的指针。指针是指一块内存区域， 该内存区域记录的是对硬盘上记录的相应行的数据的引用。

　　5）创建索引：CREATE INDEX name_index ON Employee (Employee_Name)

　　　  创建联合索引：CREATE INDEX name_index ON Employee (Employee_Name, Employee_Age)

　　6）何时使用索引

　　　数据库什么时候使用索引 - 数据库自己决定，当这个SQL （SELECT * FROM Employee WHERE Name = "Jesus" ）运行时，数据库会检查在查询的列上是否有索引。假设Employee_Name列上确实创建了索引，数据库会接着检查使用这个索引做查询是否合理，若合理则使用索引。

　　　索引不应该用于小规模的表，当字段用于WHERE子句作为过滤器会返回表里的大部分记录时，该字段就不适合设置索引。经常被操作的字段不应该设置索引，因为对索引的维护会变得很繁重。

（2）什么样的数据结构可以作为MySQL索引：B/B＋树和哈希表，MySQL自带的全文索引

　　1）B＋树索引（不考虑磁盘IO，M阶的B+树时间复杂度为log(m,n)，m为底数）：：B+树是最常用的用于索引的数据结构。因为它们是时间复杂度低， 查找、删除、插入操作都可以可以在对数时间内完成。存储在B＋树中的数据是有序的。

　　2）数据库索引为何使用B+树而不用AVL、红黑树

　　　a、AVL 树和红黑树可以达到最高的查询效率，AVL树和红黑树常用于存储内存中的有序数据，增删很快．在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。

　　　b、B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。在Inoodb中使用B+树是用来充当索引的。在关系型数据库中索引占用内存，若索引数据量过大会存储在磁盘上。 Mysql通过磁盘IO次数衡量查询效率．

　　　c、B-树/B+树 的特点就是每层节点数目非常多，层数很少，目的就是为了就少磁盘IO次数，但是B-树的每个节点都有data域（指针），这就增大了节点的大小，其实增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时）；而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。 B+树所有的Data域在叶子节点，B+树将所有的叶子节点用指针串起来。若遍历叶子节点就能获得全部数据，这样就能进行区间访问。根据磁盘查找存取的次数往往由树的高度所决定，故只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。

　　3）哈希索引

　　　a、工作原理：因为查询时哈希表效率极高，如果使用哈希索引，对于比较字符串是否相等的查询能够极快的检索出值。比喻SELECT * FROM Employee WHERE Employee_Name = ‘Jesus’)就可以利用Employee_Name 列上的哈希索引。哈系索引的工作方式是将列的值作为索引key，value为指向该表中相应行的指针。在哈希索引中等值查询后，可以得到对应行在内存中的引用，明显要比扫描全表获得值为“Jesus”的行的方式快很多。

　　　b、优点：使用哈希算法，把键值换算成新的哈希值，检索时不需要像B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

　　　c、哈希索引也没办法利用索引完成排序；不支持最左匹配原则；在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题；不支持范围查询

（3）索引的特征：唯一性索引和复合索引。

　　1）唯一性索引保证索引列不包含重复数据，不会包含冗余数据；可以使用多个列，但是必须确保索引列中每个值组合都是唯一的。如果表中已经有一个主键约束或者唯一性键（可以是多个列）约束，那么当创建表或者修改表时，MySQL会自动创建一个唯一性索引。如果必须保证唯一性，那么应该创建主键约束或者唯一性键约束，而不是创建一个唯一性索引。如果表中已经包含有数据，那么当创建索引时，Mysql检查表中已有数据的冗余性；每当修改数据时，MySQL会检查数据的冗余性：如果有冗余值，会取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认。

　　　a、主键和唯一索引的区别：

　　　　1.主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

　　　    2.主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

　　　    3.唯一性索引列允许空值，而主键列不允许为空值。

　　　    4.一个表最多只能创建一个主键，但可以创建多个唯一索引。

　　2）复合索引就是一个索引创建在两个列或者多个列上。在查询时，当两个或者多个列作为一个关键值时，最好在这些列上创建复合索引。复合列的长度不能太长；在复合索引中，所有的列必须来自同一个表中，不能跨表建立复合列；在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，应该首先定义唯一的列，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同．

　　　a、索引(复合索引)的最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上，但是遇到范围查询(>、<、between、like)就会停止匹配。

　　　　1.索引可以简单如一个列 (a)，也可以复杂如多个列 (a,b,c,d)，即复合索引。

　　　　2.如果是联合索引，那么key也由多个列组成，同时，索引只能用于查找key是否存在（相等），遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。

　　　　3.列的排列顺序决定了可命中索引的列数

　　　例如：如有索引 (a,b,c,d)，查询条件 a=1 and b=2 and c>3 and d=4，则会在每个节点依次命中a、b、c，无法命中d。(c已经是范围查询了，d肯定是排不了序了)

（4）聚集索引和非聚集索引：

　　1）聚集索引：索引中键值的逻辑顺序决定了表中相应行的物理顺序；

　　2）非聚集索引：数据存储在一个地方，索引存储在另外一个地方，索引带有指针指向数据的存储位置；

　　区别：索引的叶子节点就是对应的数据节点，聚集索引的速度往往会更快，非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，如果使用非聚集索引查询，若查询列中包含了其他列，而该索引没有覆盖的列，那么他还要回表进行二次查询，查询节点上对应的数据行的数据。

　　3）回表：回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。

（5）索引的优缺点

　　1）使用索引的优点：

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

　　　b、可以加速表和表之间的连接

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

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

　　2）使用索引的缺点：

　　　a、索引会占用空间 - 你的表越大，索引占用的空间越大

　　　b、性能损失（主要值更新操作）当你在表中添加、删除或者更新行数据的时候， 在索引中也会有相同的操作。记住：建立在某列（或多列）索引需要保存该列最新的数据。

6、事务

（1）事务的定义：一组数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成。

（2）事务的四个特性

　　1）原子性(Atomic)：事务包含的所有操作要么全部成功，要么全部失败回滚;成功必须要完全应用到数据库，失败则不能对数据库产生影响；由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sql。

　　2）一致性(Consistency)：事务执行前和执行后必须处于一致性状态，例：用户A和用户B的前加起来一共是5000; 无论AB用户之间是如何相互转换的，事务结束后两个用户的钱加起来还是5000,这就是事务的一致性。一致性一般由代码层面来保证。

　　3）隔离性(Isolation)：当多个用户并发访问数据库时，数据库为每个用户开启的事务，不被其他事务的操作所干扰，多个并发事务之间要相互隔离；由MVCC来保证

　　4）持久性(Durability)：一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的；由内存+redo log来保证，mysql修改数据同时在内存和redo log记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复。

（3）事务的隔离级别：当多个线程都开启事务来操作数据库中的数据时，数据库系统要进行隔离操作，以保证各个线程获取数据的准确性。

　　1）不考虑事务的隔离性，会产生的几种问题：

　　　a、脏读：是指一个事务处理过程里读取了另一个未提交的事务中的数据，然后使用了这个数据；

　　　b、不可重复读：在一个事务内，多次读取同一个数据，在这个事务还没有结束 ，另一个事务也访问该同一数据，但是由于第二个事务的修改，那么第一个事务两次读取的数据可能不一样，因此称为不可重复读

　　　c、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这好像发生了幻觉一样，这就叫幻读。

　　注意：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

　　2）隔离级别：

　　　a、Read uncommitted(读未提交)：最低级别，任何情况都会发生。级别最低，执行效率最高，读取未提交的数据被称为脏读。

　　　b、Read Committed(读已提交)：可避免脏读的发生。

　　　c、Repeatable read(可重复读)：可避免脏读、不可重复读的发生。Mysql的默认级别，隔离级别高，执行效率低

　　　d、Serializable(串行化)：避免脏读、不可重复读，幻读的发生。

（4）就前三种隔离级别进行场景设计：

　　1）Read uncommitted 读未提交; 公司发工资了，领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有2000元，singo空欢喜一场。

　　2）Read committed 读已提交; singo拿着工资卡去消费，系统读取到卡里确实有2000元，而此时她的老婆也正好在网上转账，把singo工资卡的2000元转到另一账户，并在singo之前提交了事务，当singo扣款时，系统检查到singo的工资卡已经没有钱，扣款失败，singo十分纳闷，明明卡里有钱，为何......

　　3）Repeatable read 重复读 当singo拿着工资卡去消费时，一旦系统开始读取工资卡信息（即事务开始），singo的老婆就不可能对该记录进行修改，也就是singo的老婆不能在此时转账。

（5）MVCC多版本控制协议

　　1）解决什么样的问题：锁机制可以控制并发操作,但是其系统开销较大。使用MVCC代替行级锁,降低系统开销

　　2）实现：MVCC是通过保存数据在某个时间点的快照来实现的. 不同存储引擎的MVCC.实现是不同的,典型的有乐观并发控制和悲观并发控制.

　　　InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的,这两个列，分别保存了这个行的创建时间，一个保存的是行的删除时间。这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，没开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID.

　　　a、如何select：

　　　　1.InnoDB只会查找版本早于当前事务版本的数据行(也就是,行的系统版本号小于或等于事务的系统版本号)，这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的.

　　　　2.行的删除版本要么未定义,要么大于当前事务版本号,这可以确保事务读取到的行，在事务开始之前未被删除.

　　　b、如何insert：若此时事务id为1，则InnoDB为新插入的每一行保存当前系统版本号(事务id)作为版本号.每一行会增加创建时间(事务ID)和删除时间的列(未定义)

　　　c、如何delete：InnoDB会为删除的每一行保存当前系统的版本号(事务的ID)作为删除标识.

　　　d、如何update：InnoDB执行UPDATE，实际上是新插入了一行记录，并保存其创建时间为当前事务的ID，同时保存当前事务ID到要UPDATE的行的删除时间。

（6）mysql主从同步的原理

　　1）mysql主从同步的原理

　　　a、master提交完事务后，写入binlog，

　　　b、slave连接到master，获取binlog

　　　c、master创建dump线程，推送binglog到slave

　　　d、slave启动一个IO线程读取同步过来的master的binlog，记录到relay log中继日志中

　　　e、slave再开启一个sql线程读取relay log事件并在slave执行，完成同步

　　　f、slave记录自己的binglog

　　mysql默认的复制方式是异步的，主库把日志发送给从库后不关心从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。

　　2）解决方案

　　　a、主库写入binlog后强制同步日志到从库，所有的从库都执行完成后才返回给客户端

　　　b、从库写入日志成功后返回ACK确认给主库，主库收到至少一个从库的确认就认为写操作完成。

（7）mysql锁分为共享锁和排他锁，也叫做读锁和写锁

　　1）读锁是共享的，可以通过lock in share mode实现，这时候只能读不能写。

　　2）写锁是排他的，它会阻塞其他的写锁和读锁。从颗粒度来区分，可以分为表锁和行锁两种，表锁会锁定整张表并且阻塞其他用户对该表的所有读写操作，比如alter修改表结构的时候会锁表。行锁又可以分为乐观锁和悲观锁，悲观锁可以通过for update实现，乐观锁则通过版本号实现。

7、数据库的4种存储引擎：

（1）MyISAM存储引擎：不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有 要求或者以select，insert为主的应用基本上可以用这个引擎来创建表。

　　1）存储格式：静态表；动态表；压缩表

　　　a、静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

　　　b、动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

　　　c、压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支。

（2）innoDB：该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。

（3）Memory存储引擎：Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，memory类型的表访问非常的快，默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。

（4）Merge存储引擎：Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

（5）MyISAM与InnoDB的区别：

　　1）MyISAM不支持事务，是非事务安全的，适用于select操作。而InnoDB支持事务处理，具有ACID事务特性；适用于inser和update操作

　　2）MyISAM锁的粒度是表级的，不支持外键；而InnoDB支持行级锁，支持外键。

　　3）InnoDB是聚集索引，使用B+树作为索引结构，数据和索引是存储在一块的；MyISAM是非聚集索引，也是使用B+树作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针

8、Mysql的查询优化：

（1）Mysql是如何执行查询的：

　　１）客户端发送一条查询给服务器；

　　２）服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段；

　　３）服务器进行SQL解析、预处理、在由查询优化器生成对应的执行计划；

　　４）MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。

　　５）将结果返回给客服端，同时也会放入查询缓存中。

　　如下图所示：

　　

9、SQL的优化

注意：驱动表：指定了联接条件时，满足查询条件的记录行数少的表为驱动表；永远用小结果集驱动大结果集（Important）

（1）优化查询：

　　１）重新定义关联表的顺序；小表放在前面，大表放在后面．

　　２）将外连接转化为内连接

　　３）优化器主要根据定义的索引来提高性能，可以设置索引， 避免在索引列上使用IS NULL和IS NOT NULL，一般where后面使用索引列。

　　４） 用IN来替换OR 

　　５）避免SELECT *，因为它提取全行，产生额外的IO开销

　　６）去重时使用group by代替distinct

　　７）order by的列尽量索引．使用limit来实现分页逻辑即将查询出来的结果集，一段一段显示出来。

　　８）比较运算符能用 “=”就不用“<>”，“=”增加了索引的使用几率