MySQL常见面试题

1 主键 超键 候选键 外键

主 键：
数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。

超键：
在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。

候选键：
是最小超键，即没有冗余元素的超键。

外键：
在一个表中存在的另一个表的主键称此表的外键。

2 数据库事务的四个特性及含义

数据库事务transanction正确执行的四个基本要素。ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。

原子性:整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性:在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性:隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性:在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3 视图的作用,视图可以更改么？

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询；不包含任何列或数据。
使用视图可以简化复杂的sql操作，隐藏具体的细节，保护数据；视图创建后，可以使用与表相同的方式利用它们。
视图不能被索引，也不能有关联的触发器或默认值，如果视图本身内有order by 则对视图再次order by将被覆盖。
创建视图：create view XXX as XXXXXXXXXXXXXX;
对于某些视图比如未使用联结子查询分组聚集函数Distinct Union等，是可以对其更新的，对视图的更新将对基表进行更新；但是视图主要用于简化检索，保护数据，并不用于更新，而且大部分视图都不可以更新。

4 drop,delete与truncate的区别

• drop直接删掉表
• truncate删除表中数据，再插入时自增长id又从1开始
• delete删除表中数据，可以加where字句。

（1）

• DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
• TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2） 表和索引所占空间。

• 表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小
• 而DELETE操作不会减少表或索引所占用的空间
• drop语句将表所占用的空间全释放掉

（3） 一般而言，drop > truncate > delete

（4） 应用范围

• TRUNCATE 只能对TABLE
• DELETE可以是table和view

（5）

• TRUNCATE 和DELETE只删除数据
• 而DROP则删除整个表（结构和数据）

（6）

• truncate与不带where的delete :只删除数据,而不删除表的结构（定义）
• drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）

• delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发
• truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（8）

• 在没有备份情况下,谨慎使用 drop 与 truncate
• 要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。
• 要删除表用drop;
• 若想保留表而将表中数据删除
  
  • 如果与事务无关，用truncate即可实现。
  • 如果和事务有关，或老是想触发trigger,还是用delete

（9） Truncate table 表名 速度快,而且效率高,因为:
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（10）

• TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。
• 如果想保留标识计数值，请改用 DELETE。
• 如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（11） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器

5 索引的工作原理及其种类

数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询、更新数据库表中数据
索引的实现通常使用B树及其变种B+树

在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用（指向）数据,这样就可以在这些数据结构上实现高级查找算法.这种数据结构,就是索引.

为表设置索引要付出代价的:一是增加了数据库的存储空间,二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)

图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。