数据库考点

数据库事务

数据库有哪几种分类？特点是？

通常把数据库分为两类，即关系型数据库 和 非关系型数据库。
1.关系型数据库
关系型数据库是指采用了关系模型来组织数据的数据库，而关系模型是由二维表及其联系组成的数据组织。
目前主流的关系型数据库有：MySQL、SQL Server、Oracle、Postgresql
优点：
易于维护：都是使用表结构，格式一致；
使用方便：SQL语言通用，可用于复杂查询；
复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。
缺点
读写性能比较差，尤其是海量数据的高效率读写；
固定的表结构，灵活度稍欠；
高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。
2.非关系型数据库
非关系型数据库也称为NOSQL（Not Only SQL），作为关系型数据库的一个补充，能在特定场景和特点问题下发挥高效率和高性能。
常见的非关系型数据库类型有键值（Key-Value）存储数据库和面向文档数据库（Document-oriented）。
键值存储数据库类似hash，通过key做添加、删除、查询，性能高，优势在于简单、易部署、高并发。
目前主流的非关系型数据库有：Redis、Memcaced、MongoDB
【其中MongoDB适用于网站后台数据库（更新快、实时复制）、小文件系统（json，二进制）、日志分析系统（数据量大的文件）。】

mysql 有关权限表都有哪几个？

MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库里，由mysql_install_db脚本初始化。这些权限表分别 user，db，table_priv，columns_priv 和 host。
user：记录允许连接到服务器的用户帐号信息，里面的权限是全局级的。
db：记录各个帐号在各个数据库上的操作权限。
table_priv：记录数据表级的操作权限。
columns_priv：记录数据列级的操作权限。
host：配合db权限表对给定主机上数据库级操作权限作更细致的控制。这个权限表不受GRANT和REVOKE语句的影响。

B树和B+树

B树的原理
动态查找树主要包括：二叉搜索树，平衡二叉树，红黑树，B树，B-树时间复杂度O(log2N)，通过对树高度的降低可以提升查找效率；
尤其是在大量数据进行存储的时候会存储到外部 磁盘，通过对外部磁盘的读取时需要快速的查找到对应的位置，所以需要一种高效的外村数据结构。
B树：就是为了存储设备或者磁盘设计的一种平衡查找树

B树与红黑树的区别
B树的节点可以有很多孩子节点，红黑树是一种近似平衡的二叉搜索树即每个节点只有两个孩子
一颗含有N个节点的B树和红黑树的高度是一样的O（lgn）。

B树的定义
对于一颗M阶的B树
1）树中的每个节点最多有m个孩子
2）除了根节点和叶子节点外，其他节点最少含有m/2（取上限）个孩子
3）若根节点不是叶子节点，则根节点最少含有两个孩子
4）所以叶子节点都在同一层，叶子节点不包含任何关键字信息

B+树的原理
B+树特点
1）B+树是B树的一种变形，它把数据都存储在叶子节点，内部只存关键字（其中叶子节点的最小值作为索引）和孩子指针，简化了内部节点。
2）B+树的遍历高效，将所以叶子节点串联成链表即可从头到尾遍历。

B+树的定义
1）有n棵子树的结点含有n个关键字，每个关键字都不保存数据，只用来索引，并且所有的数据都存储在叶子节点；
2）所有叶子结点包含所有关键字信息和指向关键字记录的指针，其中关键字从小到大顺序链接。

为什么B+树比B树更适合做系统的数据库索引和文件索引

1）B+树的磁盘读写代价更低
因为B+树内部结点没有指向关键字具体信息的指针，内部结点相对B树小
2）B+树的查询更加稳定
因为非终端结点并不是指向文件内容的结点，仅仅是作为叶子结点的关键字索引，因此所有的关键字查询都会走一条从根节点到叶子结点的路径。即s所有关键字查询的长度是一样的，查询效率稳定。

B树和B+树有什么区别？

B树是一颗多路平衡查找树。

· 每个节点最多有m-1个关键字（可以存有的键值对）。
· 根节点最少可以只有1个关键字。
· 非根节点至少有m/2个关键字。
· 每个节点中的关键字都按照从小到大的顺序排列，每个关键字的左子树中的所有关键字都小于它，而右子树中的所有关键字都大于它。
· 所有叶子节点都位于同一层，或者说根节点到每个叶子节点的长度都相同。
· 每个节点都存有索引和数据，也就是对应的key和value。
所以，根节点的关键字数量范围：1 <= k <= m-1，非根节点的关键字数量范围：m/2 <= k <= m-1。

另外，我们需要注意一个概念，描述一颗B树时需要指定它的阶数，阶数表示了一个节点最多有多少个孩子节点，一般用字母m表示阶数。

B+树其实和B树是非常相似的，我们首先看看相同点。

根节点至少一个元素
非根节点元素范围：m/2 <= k <= m-1
不同点

• B+树有两种类型的节点：内部结点（也称索引结点）和叶子结点。内部节点就是非叶子节点，内部节点不存储数据，只存储索引，数据都存储在叶子节点。
• 内部结点中的key都按照从小到大的顺序排列，对于内部结点中的一个key，左树中的所有key都小于它，右子树中的key都大于等于它。叶子结点中的记录也按照key的大小排列。
• 每个叶子结点都存有相邻叶子结点的指针，叶子结点本身依关键字的大小自小而大顺序链接。
• 父节点存有右孩子的第一个元素的索引。

B+树相对于B树有一些自己的优势，可以归结为下面几点。

• 单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
• 所有的查询都要查找到叶子节点，查询性能是稳定的，而B树，每个节点都可以查找到数据，所以不稳定。
• 所有的叶子节点形成了一个有序链表，更加便于查找。
  参考：
• B+树的非叶子节点只是存储key，占用空间非常小，因此每一层的节点能索引到的数据范围更加的广。换句话说，每次IO操作可以搜索更多的数据。
• 叶子节点两两相连，符合磁盘的预读特性。比如叶子节点存储50和55，它有个指针指向了60和62这个叶子节点，那么当我们从磁盘读取50和55对应的数据的时候，由于磁盘的预读特性，会顺便把60和62对应的数据读取出来。这个时候属于顺序读取，而不是磁盘寻道了，加快了速度。
• 支持范围查询，而且部分范围查询非常高效，每个节点能索引的范围更大更精确,也意味着 B+树单次磁盘IO的信息量大于B-树,I/O效率更高。
  原因是数据都是存储在叶子节点这一层，并且有指针指向其他叶子节点，这样范围查询只需要遍历叶子节点这一层，无需整棵树遍历。

由于磁盘的存取速度与内存之间鸿沟,为了提高效率,要尽量减少磁盘I/O.磁盘往往不是严格按需读取，而是每次都会预读,磁盘读取完需要的数据,会顺序向后读一定长度的数据放入内存。而这样做的理论依据是计算机科学中著名的局部性原理：

当一个数据被用到时，其附近的数据也通常会马上被使用，程序运行期间所需要的数据通常比较集中
延伸问题：与哈希索引的区别？
简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。

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

红黑树和AVL树有什么区别？

AVL 和RBT 都是二叉查找树的优化。其性能要远远好于二叉查找树。他们之间都有自己的优势，其应用上也有不同。

结构对比： AVL的结构高度平衡，RBT的结构基本平衡。平衡度AVL > RBT.

查找对比： AVL 查找时间复杂度最好，最坏情况都是O(logN)。

RBT 查找时间复杂度最好为O(logN)，最坏情况下比AVL略差。

插入删除对比： 1. AVL的插入和删除结点很容易造成树结构的不平衡，而RBT的平衡度要求较低。因此在大量数据插入的情况下，RBT需要通过旋转变色操作来重新达到平衡的频度要小于AVL。

• 如果需要平衡处理时，RBT比AVL多一种变色操作，而且变色的时间复杂度在O(logN)数量级上。但是由于操作简单，所以在实践中这种变色仍然是非常快速的。
• 当插入一个结点都引起了树的不平衡，AVL和RBT都最多需要2次旋转操作。但删除一个结点引起不平衡后，AVL最多需要logN 次旋转操作，而RBT最多只需要3次。因此两者插入一个结点的代价差不多，但删除一个结点的代价RBT要低一些。
• AVL和RBT的插入删除代价主要还是消耗在查找待操作的结点上。因此时间复杂度基本上都是与O(logN) 成正比的。
  总体评价：大量数据实践证明，RBT的总体统计性能要好于平衡二叉树。

什么是事务

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性。

事务四大特性（ACID）介绍下

1. 原子性（Atomicity）
   事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。
   回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
2. 一致性（Consistency）
   数据库在事务执行前后都保持一致性状态。
   在一致性状态下，所有事务对一个数据的读取结果都是相同的。
3. 隔离性（Isolation）
   一个事务所做的修改在最终提交以前，对其它事务是不可见的。
4. 持久性（Durability）
   一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
   可以通过数据库备份和恢复来实现，在系统发生崩溃时，使用备份的数据库进行数据恢复。

事务并发带来的问题（并发一致性问题）

脏读：一个事务读取了另一个事务未提交的数据；
不可重复读：不可重复读的重点是修改，同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改；
幻读：幻读的重点在于新增或者删除，同样条件下两次读出来的记录数不一样。
或者如下
丢失修改：T1 和 T2 两个事务都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。
脏读数据：T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的数据是脏数据。
不可重复读：T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的结果和第一次读取的结果不同。
幻读：T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的数据，此时读取的结果和和第一次读取的结果不同。
延伸问题：不可重复度和幻读有什么区别？
不可重复读的重点是修改，幻读的重点在于新增或者删除。

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

1. 未提交读（READ UNCOMMITTED）
   事务中的修改，即使没有提交，对其它事务也是可见的。这样会提高性能，但是会导致脏读问题。
2. 提交读（READ COMMITTED）
   一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。该级别可以解决脏读为问题，但不能避免不可重复读。
3. 可重复读（REPEATABLE READ）
   保证在同一个事务中多次读取同样数据的结果是一样的。可以解决不可重复读的问题，但还是不能避免幻读的问题。
4. 可串行化（SERIALIZABLE）
   强制事务串行执行。可以解决所有问题。最高级别的隔离，
   
   MySQL默认的隔离级别是可重复读。

超键、候选键、主键、外键区别？

元组：元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行
属性：数据库属性即是数据库字段，指数据库中表的列。
1、书中的定义
超键(super key): 在关系中能唯一标识元组的属性集称为关系模式的超键
候选键(candidate key): 不含有多余属性的超键称为候选键。也就是在候选键中，若再删除属性，就不是键了！
主键(primary key): 用户选作元组标识的一个候选键程序主键
外键(foreign key)：如果关系模式R中属性K是其它模式的主键，那么k在模式R中称为外键。
2、咱们创建简单的两个表，说明一下各个键！
学生信息（学号 身份证号 性别 年龄 身高 体重 宿舍号）和 宿舍信息（宿舍号 楼号）
超键：只要含有“学号”或者“身份证号”两个属性的集合就叫超键，例如R1（学号 性别）、R2（身份证号 身高）、R3（学号 身份证号）等等都可以称为超键！
候选键：不含有多余的属性的超键，比如（学号）、（身份证号）都是候选键，又比如R1中学号这一个属性就可以唯一标识元组了，而有没有性别这一属性对是否唯一标识元组没有任何的影响！
主键：就是用户从很多候选键选出来的一个键就是主键，比如你要求学号是主键，那么身份证号就不可以是主键了！
外键：宿舍号就是学生信息表的外键

什么是元组,码,候选码,主码,外码,主属性,非主属性？

元组 ： 元组（tuple）是关系数据库中的基本概念，关系是一张表，表中的每行（即数据库中的每条记录）就是一个元组，每列就是一个属性。 在二维表里，元组也称为行。
码 ：码就是能唯一标识实体的属性，对应表中的列。
候选码 ： 若关系中的某一属性或属性组的值能唯一的标识一个元组，而其任何、子集都不能再标识，则称该属性组为候选码。例如：在学生实体中，“学号”是能唯一的区分学生实体的，同时又假设“姓名”、“班级”的属性组合足以区分学生实体，那么{学号}和{姓名，班级}都是候选码。
主码 : 主码也叫主键。主码是从候选码中选出来的。 一个实体集中只能有一个主码，但可以有多个候选码。
外码 : 外码也叫外键。如果一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。
主属性 ： 候选码中出现过的属性称为主属性。比如关系 工人（工号，身份证号，姓名，性别，部门）.显然工号和身份证号都能够唯一标示这个关系，所以都是候选码。工号、身份证号这两个属性就是主属性。如果主码是一个属性组，那么属性组中的属性都是主属性。
非主属性： 不包含在任何一个候选码中的属性称为非主属性。比如在关系——学生（学号，姓名，年龄，性别，班级）中，主码是“学号”，那么其他的“姓名”、“年龄”、“性别”、“班级”就都可以称为非主属性。

主键和外键有什么区别?

主键(主码) ：主键用于唯一标识一个元组，不能有重复，不允许为空。一个表只能有一个主键。
外键(外码) ：外键用来和其他表建立联系用，外键是另一表的主键，外键是可以有重复的，可以是空值。一个表可以有多个外键。

一些重要的概念：
函数依赖（functional dependency） ：若在一张表中，在属性（或属性组）X的值确定的情况下，必定能确定属性Y的值，那么就可以说Y函数依赖于X，写作 X → Y。
部分函数依赖（partial functional dependency） ：如果X→Y，并且存在X的一个真子集X0，使得X0→Y，则称Y对X部分函数依赖。比如学生基本信息表R中（学号，身份证号，姓名）当然学号属性取值是唯一的，在R关系中，（学号，身份证号）->（姓名），（学号）->（姓名），（身份证号）->（姓名）；所以姓名部分函数依赖与（学号，身份证号）；
完全函数依赖(Full functional dependency) ：在一个关系中，若某个非主属性数据项依赖于全部关键字称之为完全函数依赖。比如学生基本信息表R（学号，班级，姓名）假设不同的班级学号有相同的，班级内学号不能相同，在R关系中，（学号，班级）->（姓名），但是（学号）->(姓名)不成立，（班级）->(姓名)不成立，所以姓名完全函数依赖与（学号，班级）；
传递函数依赖 ： 在关系模式R(U)中，设X，Y，Z是U的不同的属性子集，如果X确定Y、Y确定Z，且有X不包含Y，Y不确定X，（X∪Y）∩Z=空集合，则称Z传递函数依赖(transitive functional dependency) 于X。传递函数依赖会导致数据冗余和异常。传递函数依赖的Y和Z子集往往同属于某一个事物，因此可将其合并放到一个表中。比如在关系R(学号 ,姓名, 系名，系主任)中，学号 → 系名，系名 → 系主任，所以存在非主属性系主任对于学号的传递函数依赖。

什么是存储过程?

我们可以把存储过程看成是一些 SQL 语句的集合，中间加了点逻辑控制语句。存储过程在业务比较复杂的时候是非常实用的，比如很多时候我们完成一个操作可能需要写一大串SQL语句，这时候我们就可以写有一个存储过程，这样也方便了我们下一次的调用。存储过程一旦调试完成通过后就能稳定运行，另外，使用存储过程比单纯SQL语句执行要快，因为存储过程是预编译过的。
存储过程在互联网公司应用不多，因为存储过程难以调试和扩展，而且没有移植性，还会消耗数据库资源

存储过程优缺点

优点：
存储过程是一个预编译的代码块，执行效率比较高,提高代码重用性，简化操作
存储过程在服务器端运行，减少客户端的压力
允许模块化程序设计，只需要创建一次过程，以后在程序中就可以调用该过程任意次，类似方法的复用
一个存储过程替代大量T_SQL语句 ，可以降低网络通信量，提高通信速率
可以一定程度上确保数据安全
缺点：
调试麻烦（没有像开发程序那样容易）
可移植性不灵活（因为存储过程依赖于具体的数据库）

什么是内存泄漏？

一般我们所说的内存泄漏指的是堆内存的泄漏。堆内存是程序从堆中为其分配的，大小任意的，使用完后要显示释放内存。当应用程序用关键字新等创建对象时，就从堆中为它分配一块内存，使用完后程序调用免费或者删除释放该内存，否则就说该内存就不能被使用，我们就说该内存被泄漏了。

drop、delete与truncate区别？

用法不同
drop(丢弃数据): drop table 表名 ，直接将表都删除掉，在删除表的时候使用。
truncate (清空数据) : truncate table 表名 ，只删除表中的数据，再插入数据的时候自增长id又从1开始，在清空表中数据的时候使用。
delete（删除数据） : delete from 表名 where 列名=值，删除某一列的数据，如果不加 where 子句和truncate table 表名作用类似。
truncate 和不带 where 子句的 delete、以及 drop 都会删除表内的数据，但是 truncate 和 delete 只删除数据不删除表的结构(定义)，执行drop语句，此表的结构也会删除，也就是执行 drop 之后对应的表不复存在。

属于不同的数据库语言
truncate和drop 属于DDL(数据定义语言)语句，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发 trigger。而 delete 语句是DML (数据库操作语言)语句，这个操作会放到 rollback segement 中，事务提交之后才生效。
DML 语句和 DDL 语句区别：
DML 是数据库操作语言（Data Manipulation Language）的缩写，是指对数据库中表记录的操作，主要包括表记录的插入（insert）、更新（update）、删除（delete）和查询（select），是开发人员日常使用最频繁的操作。
DDL （Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。
执行速度不同
一般来说:drop>truncate>delete。

什么是触发器

一张表发生了某件事（插入、删除、更新操作），然后自动触发了预先编写好的若干条SQL语句的执行；

为什么使用触发器

为了提高查询效率，在流过期后会从数据库中删除相关的流记录信息，精简主表，但是这样并不利于后期的查询需求，因此需要创建触发器，在删除一条主表记录的同时将删除的数据备份到另一张表格中。
mysql中触发器是一个特殊的存储过程，不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。

默认的系统数据库有哪些？

1）master数据库（主）；2）tempdb数据库（临时）；3）model 数据库（模板）；4）msdb数据库（计划任务）；

默认创建一个数据库，会生成哪些文件？

1）主文件（.mdf），2）日志文件（.ldf），无次要文件（.ndf）。

什么叫视图游标是什么？

视图:是一种虚拟的表，具有和物理表相同的功能可以对视图进行增，改，查，操作，试图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。
游标：是对查询出来的结果集作为一个单元来有效的处理游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行可以对结果集当前行做修改一般。不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。

列举几种表连接方式,有什么区别？

内连接、自连接、外连接（左、右、全）、交叉连接 内连接：只有两个元素表相匹配的才能在结果集中显示。 外连接： 左外连接:左边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 右外连接:右边为驱动表，驱动表的数据全部显示，匹配表的不匹配的不会显示。 全外连接：连接的表中不匹配的数据全部会显示出来。 交叉连接： 笛卡尔效应，显示的结果是链接表数的乘积。

视图的优缺点

优点：
1）对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。
2 ）用户通过简单的查询可以从复杂查询中得到结果。
3 ）维护数据的独立性，试图可从多个表检索数据。
4 ）对于相同的数据可产生不同的视图。
缺点：
性能：查询视图时，必须把视图的查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，那么就无法更改数据

锁的分类

一般可以分为两类，一个是悲观锁，一个是乐观锁，悲观锁一般就是我们通常说的数据库锁机制，乐观锁一般是指用户自己实现的一种锁机制。

悲观锁： 它对于数据被外界修改持保守态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制，事实上关系数据库中的行锁，表锁不论是读写锁都是悲观锁。
悲观锁按照使用性质划分：

1. 共享锁（Share locks简记为S锁）：也称读锁，事务A对对象T加s锁，其他事务也只能对T加S，多个事务可以同时读，但不能有写操作，直到A释放S锁。
2. 排它锁（Exclusivelocks简记为X锁）：也称写锁，事务A对对象T加X锁以后，其他事务不能对T加任何锁，只有事务A可以读写对象T直到A释放X锁。
3. 更新锁（简记为U锁）：用来预定要对此对象施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的对象将要被更新时，则升级为X锁，主要是用来防止死锁的。

悲观锁按照作用范围划分：
4. 行锁：锁的作用范围是行级别，数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。
5. 表锁：锁的作用范围是整张表。

乐观锁： 顾名思义，就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁，但是在更新的时候会去判断在此期间数据有没有被修改。

乐观锁实现方式：
版本号（记为version）：就是给数据增加一个版本标识，在数据库上就是表中增加一个version字段，每次更新把这个字段加1，读取数据的时候把version读出来，更新的时候比较version，如果还是开始读取的version就可以更新了，如果现在的version比老的version大，说明有其他事务更新了该数据，并增加了版本号，这时候得到一个无法更新的通知，用户自行根据这个通知来决定怎么处理，比如重新开始一遍。这里的关键是判断version和更新两个动作需要作为一个原子单元执行，否则在你判断可以更新以后正式更新之前有别的事务修改了version，这个时候你再去更新就可能会覆盖前一个事务做的更新，造成第二类丢失更新，所以你可以使用update … where … and version=”old version”这样的语句，根据返回结果是0还是非0来得到通知，如果是0说明更新没有成功，因为version被改了，如果返回非0说明更新成功。
时间戳（timestamp）：和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。
待更新字段： 和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。
所有字段：和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新。
乐观锁几种方式的区别：
新系统设计可以使用version方式和timestamp方式，需要增加字段，应用范围是整条数据，不论哪个字段修改都会更新version,也就是说两个事务更新同一条记录的两个不相关字段也是互斥的，不能同步进行。旧系统不能修改数据库表结构的时候使用数据字段作为版本控制信息，不需要新增字段，待更新字段方式只要其他事务修改的字段和当前事务修改的字段没有重叠就可以同步进行，并发性更高。

对MySQL的锁了解吗，mysql有哪些锁

数据库有并发事务时,可能产生数据不一致,这时需要一些机制来保证访问的次序,锁机制就是这样的一个机制.
共享锁：又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个
排他锁：又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.

两种锁的使用场景

从上面对两种锁的介绍，我们知道两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

乐观锁的缺点

1 ABA 问题
如果一个变量V初次读取的时候是A值，并且在准备赋值的时候检查到它仍然是A值，那我们就能说明它的值没有被其他线程修改过了吗？很明显是不能的，因为在这段时间它的值可能被改为其他值，然后又改回A，那CAS操作就会误认为它从来没有被修改过。这个问题被称为CAS操作的 "ABA"问题。

JDK 1.5 以后的 AtomicStampedReference 类就提供了此种能力，其中的 compareAndSet 方法就是首先检查当前引用是否等于预期引用，并且当前标志是否等于预期标志，如果全部相等，则以原子方式将该引用和该标志的值设置为给定的更新值。
2 循环时间长开销大
自旋CAS（也就是不成功就一直循环执行直到成功）如果长时间不成功，会给CPU带来非常大的执行开销。 如果JVM能支持处理器提供的pause指令那么效率会有一定的提升，pause指令有两个作用，第一它可以延迟流水线执行指令（de-pipeline）,使CPU不会消耗过多的执行资源，延迟的时间取决于具体实现的版本，在一些处理器上延迟时间是零。第二它可以避免在退出循环的时候因内存顺序冲突（memory order violation）而引起CPU流水线被清空（CPU pipeline flush），从而提高CPU的执行效率。
3 只能保证一个共享变量的原子操作
CAS 只对单个共享变量有效，当操作涉及跨多个共享变量时 CAS 无效。但是从 JDK 1.5开始，提供了AtomicReference类来保证引用对象之间的原子性，你可以把多个变量放在一个对象里来进行 CAS 操作.所以我们可以使用锁或者利用AtomicReference类把多个共享变量合并成一个共享变量来操作。

MyISAM 和 InnoDB 的区别有哪些？

InnoDB 支持事务，但是MyISAM 不支持事务。这也是 MySQL 选择 InnoDB作为 默认存储引擎的原因之一；
InnoDB 支持外键，但是 MyISAM 不支持。如果一个表包含外键，并且存储引擎是InnoDB，把它转为 MyISAM就会失败；
InnoDB 使用的是聚集索引，MyISAM使用非聚集索引。聚簇索引的文件存放在主键索引的叶子节点上，所以 InnoDB 必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。所以，主键不应该过大，因为主键太大，其他索引也都会很大。非聚集索引的话，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。
InnoDB 不保存表的具体行数，执行 select count(*) from table 时需要全表扫描。但是MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；
InnoDB 最小的锁粒度是行级锁，MyISAM 最小的锁粒度是表级锁。一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，所以并发访问受到很大的限制。

延伸问题：如何选择存储引擎？

1. 是否要支持事务，如果要请选择 InnoDB，如果不需要可以考虑 MyISAM；
2. 如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，那就使用InnoDB。
3. 系统崩溃后，MyISAM恢复起来更困难，能否接受，不能接受就选 InnoDB；
4. MySQL5.5版本开始InnoDB已经成为MySQL的默认引擎，说明其优势是有目共睹的。如果不知道用什么存储引擎，那就用InnoDB，跟着官方走，至少不会差。

数据库三范式

范式理论是为了解决以上提到四种异常。
高级别范式的依赖于低级别的范式，1NF 是最低级别的范式。

1. 第一范式 (1NF)
   强调的是列的原子性，即列不能够再分成其他几列。
2. 第二范式 (2NF)
   首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
   可以通过分解来满足。
3. 第三范式 (3NF)
   首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

索引（重要）

索引是一种数据结构,可以帮助我们快速的进行数据的查找.
索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引.

Hash索引和B+树所有有什么区别或者说优劣呢?

hash索引：底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据
B+树： 底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
区别：
​hash索引进行等值查询更快(一般情况下), 但是却无法进行范围查询.
B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.

什么是索引，有什么作用以及优缺点？

索引是对数据库表中一或多个列的值进行排序的结构，是帮助MySQL高效获取数据的数据结构。
你也可以这样理解：索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中，索引允许用户不必翻阅完整个书就能迅速地找到所需要的信息。在数据库中，索引也允许数据库程序迅速地找到表中的数据，而不必扫描整个数据库。
MySQL数据库几个基本的索引类型：普通索引、唯一索引、主键索引、全文索引
索引加快数据库的检索速度
索引降低了插入、删除、修改等维护任务的速度
唯一索引可以确保每一行数据的唯一性
通过使用索引，可以在查询的过程中使用优化隐藏器，提高系统的性能
索引需要占物理和数据空间

索引创建原则

1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。

什么情况下需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段、外键关系建立索引
4. 频繁更新的字段不适合创建索引、因为每次更新不单单是更新了记录还会更新索引
5. where条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，who
7. 查询中排序的字段、排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

什么情况下不需要创建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的表字段、因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为他建立索引就没太大的实际效果

B树和B+树的区别，使用B树的好处，使用B+树的好处

最开始，我们要讲的就是b树与b+树的区别，注意，b树与b+树的区别这个问题也是在各大面试场景当中经常会出现的面试题。
1、B+树当中查找，不管查找是不是成功的，每一次的查找，都是一条从根节点到叶节点的路径
2、B树当中，叶节点包含的关键字和其他节点包含的关键字是不重复的。
B+树的索引项只包含对应子树的最大关键字和指向该子树的指针，不含有这个关键字对应记录的存储地址
3、B树当中，每个节点(非根节点)关键字个数的范围为m/2(向上取整)-1,m-1，并且具有n个关键字的节点包含(n+1)棵子树
B+树当中，每个节点(非根节点)关键字个数的范围为m/2(向上取整),m，具有n个关键字的节点包含(n)棵子树
4、B树每个节点都存储数据，所有节点组成这棵树。
B+树只有叶子节点存储数据(B+数中有两个头指针：一个指向根节点，另一个指向关键字最小的叶节点)，叶子节点包含了这棵树的所有数据，所有的叶子结点使用链表相连，便于区间查找和遍历，所有非叶节点起到索引作用
那么b树和b+树分别都有什么样的优点呢?
二、b树的优点
B树的每一个节点都包含key和value。
所以，经常访问的元素可能离根节点更近，所以访问也是更加的迅速
三、b+树的优点
1、b+树的中间节点不保存数据，可以容纳更多的节点元素
2、所有的叶子结点使用链表相连，有助于区间查找和遍历
B树的话，就需要进行每一层的递归遍历
相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好
除此之外，b树和b+树两者的共同优点又是怎样的呢?
四、b树和b+树的共同优点
考虑到了IO的影响，它对于内存来说是非常的慢的。
数据库索引是存储在磁盘上的，在数据量大的时候，就不可以将整个索引全部加载到内存，只可以说一项一项的加载每一个磁盘页(对应索引树的节点)。
所以的话，要减少IO次数，对于树来讲的话，IO次数就是树的高度，而“矮胖”就是b树的特征之一，m的大小取决于磁盘页的大小。

联合索引是什么？为什么需要注意联合索引中的顺序？

使用多个字段同时建立一个索引,叫做联合索引。在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引。

数据库语句

union和union all有什么不同?

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。 UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。 从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

Varchar2和varchar有什么区别？

Char的长度是固定的，而varchar2的长度是可以变化的，比如，存储字符串“abc”对于char(20)，表示你存储的字符将占20个字节，包含17个空，而同样的varchar2（20）只占了3个字节，20只是最大值，当你存储的字符小于20时，按实际长度存储。 char的效率要被varchar2的效率高。 目前varchar是varchar2的同义词，工业标准的varchar类型可以存储空字符串，但是oracle不能这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型varchar2,这个类型不是一个标准的varchar，他将在数据库中varchar列可以存储空字符串的特性改为存储null值，如果你想有向后兼容的能力，oracle建议使用varchar2而不是varchar

Oracle和Mysql的区别？

1）库函数不同。 2）Oracle是用表空间来管理的，Mysql不是。 3）显示当前所有的表、用户、改变连接用户、显示当前连接用户、执行外部脚本的语句的不同。 4）分页查询时候时候，mysql用limit oracle用rownum

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1： mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.
//如果只给定一个参数，它表示返回最大的记录行数目： mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
//换句话说，LIMIT n 等价于 LIMIT 0,n。 5）sql的语法的不同。

SQL 约束有哪几种？

1.主键 用来标识表中的一行，定义方式有两种 ：

2.默认值约束 若某行无定义值，将会使用默认值
3.唯一约束 指定某一列不能有重复值，否则INSERT INTO 时会出错；
4.外键约束 一个表可以有多个外键，每个外键必须 REFERENCES (参考) 另一个表的主键，被外键约束的列，取值必须在它参考的列中有对应值
5.非空约束 插入值不能为空

5种关联查询

1. 交叉连接(CROSS JOIN)
2. 内连接(INNER JOIN)
3. 外连接(LEFT JOIN/RIGHT JOIN)
4. 联合查询(UNION 与 UNION ALL)
5. 全连接(FULL JOIN)
   交叉连接(没有任何关联条件,查询所有表)
   select * form a,b,c
   或者 :
   select * from a cross join b cross join c
   内连接(多表中同时符合某种条件的数据记录的集合)
   select * from a,b where a.id = b.id
   或者 :
   select * from a inner join b on a.id = b.id
   分析 :
   select * from 表1 inner join 表2 on 条件
   内连接分为三类
   ①等值连接: on a.id = b.id
   ②不等值连接 : on a.id > b.id
   ③自连接: select * from a as a1 inner join a as a2 on a.id = a2.pid
   [自连接就是自己连接自己 条件id和pid]

什么是子查询

在select中嵌套select

子查询的三种情况

子查询的三种使用方式：
1，子查询作为条件
1》如果是跟在> < >= <= = != <>后面就必须保证子查询 只返回单个值，如果是返回多行一列，那么就需要使用in来指定范围，如果是返回一行多列，那么就报错
Eg：（多行一列）
Select * from Student where ClassId in (select ClassId from grade)
2，子查询作为结果集（可以将from后面的表用虚拟结果集进行替换）
//根据主键StudentNo排序，Row_NUMBER()会生成行号，over（）指定排序字段
Select *,Row_NUMBER() over(order by StudentNo) from Student
Eg：//虚拟结果集需要设置别名（temp） 生成的行号也要指定别名（as id）
Select * from (select *,Row_NUMBER() over(order by StudentNo) as id from Student) temp where id>0 and id<=5//分页
3，子查询作为列的值
Select (select studentname from Studnet where
Student.StudentNo=Result.StudentNo),StudentResult from Result

mysql中 in 和 exists 区别

varchar与char的区别

区别一，定长和变长
char 表示定长，长度固定，varchar表示变长，即长度可变
区别之二，存储的容量不同
对 char 来说，最多能存放的字符个数 255，和编码无关。而 varchar ，最多能存放 65532 个字符

varchar(50)中50的涵义

最多存放50个字符

int(20)中20的涵义

int(M)只是用来显示数据的宽度

mysql中int(10)和char(10)以及varchar(10)的区别

varchar(10)中的10代表字符串最长宽度为10，存进库里的字符串长度超过10将报错无法进行存储
int(10)。。存储为int类型，占4个字节，非负存值为0到2的32次方，并不会因为10的限制存储范围为0到2的10次方
表示的是存储数字宽度，如果宽度不够10就会按照0进行填充。
示例(设计int(5))

数字达不到5的前面按照0进行填充补满5位。如果宽度超过5位就按照其数字正常展示

FLOAT和DOUBLE的区别是什么？

SQL优化

如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者可以知道这条语句运行很慢的原因？

explain命令来查看语句的执行计划

mysql 分页， 超大分页怎么处理？

一般从两个方向上来解决:
1、虽然也load了一百万的数据,但是由于索引覆盖,要查询的所有字段都在索引中,所以速度会很快. 同时如果ID连续的好,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.
2、解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

关心过业务系统里面的sql耗时吗？ 统计过慢查询日志吗？对慢查询都怎么优化过？

1、分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多不需要的列,对语句进行分析以及重写
2、分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
3、对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.

为什么要尽量设定一个主键？

主键是数据库确保数据行在整张表唯一性的保障，即使业务上本张表没有主键，也建议添加一个自增长的ID列作为主键。设定了主键之后，在后续的删改查的时候可能更加快速以及确保操作数据范围安全。

主键使用自增ID还是UUID？

在InnoDB存储引擎中,主键索引是作为聚簇索引存在的,也就是说,主键索引的B+树叶子节点上存储了主键索引以及全部的数据(按照顺序),如果主键索引是自增ID,那么只需要不断向后排列即可,如果是UUID,由于到来的ID与原来的大小不确定,会造成非常多的数据插入,数据移动,然后导致产生很多的内存碎片,进而造成插入性能的下降.

字段为什么要求定义为not null？

null值会占用更多的字节,且会在程序中造成很多与预期不符的情况.

如果要存储用户的密码散列，应该使用什么字段进行存储？

密码散列,盐,用户身份证号等固定长度字符串应该使用char而不是varchar来存储,这样可以节省空间且提高检索效率.

MySQL的binlog有有几种录入格式?分别有什么区别?

三种格式：statement、row 和 mixed
statement：模式下,记录单元为语句.即每一个sql造成的影响会记录。
row：级别下,记录单元为每一行的改动,基本是可以全部记下来但是由于很多操作。
mixed 一种折中的方案,普通操作使用statement记录,当无法使用statement的时候使用row。

数据库优化

为什么要优化、说说数据库结构优化

MySQL数据库cpu飙升到500%的话他怎么处理？

大表优化？某表有近千万数据，CRUD比较慢，如何优化？分库分表怎么做的？分表分库有什么问题？有用到中间件么？他们的原理知道么？

垂直分表：适用场景、缺点。水平分表：适用场景、缺点

垂直分割：　如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O次数。其缺点是需要管理冗余列，查询所有数据需要join操作。　
水平分割通常在下面的情况下使用。
　　•表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
　　•表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。
　　•需要把数据存放到多个介质上。
水平分割会给应用增加复杂度，它通常在查询时需要多个表名，查询所有数据需要union操作。在许多数据库应用中，这种复杂性会超过它带来的优点，因为只 要索引关键字不大，则在索引用于查询时，表中增加两到三倍数据量，查询时也就增加读一个索引层的磁盘次数。

MySQL的复制原理以及流程

读写分离有哪些解决方案？

1 程序修改mysql操作类
优点：直接和数据库通信，简单快捷的读写分离和随机的方式实现的负载均衡，权限独立分配
缺点：自己维护更新，增减服务器在代码处理
2 amoeba
优点：直接实现读写分离和负载均衡，不用修改代码，有很灵活的数据解决方案
缺点：自己分配账户，和后端数据库权限管理独立，权限处理不够灵活
3 mysql-proxy
优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号
缺点：字符集问题，lua语言编程，还只是alpha版本，时间消耗有点高

备份计划，mysqldump以及xtranbackup的实现原理

数据表损坏的修复方式有哪些？