数据库详解

触发器的一些常见用途。

1.保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。

3.基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。

3.进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。

4.计算计算列的值或更新时间戳

索引的作用？和它的优点缺点是什么？

索引就一种特殊的查询表，数据库的搜索可以利用它加速对数据的检索。索引可以是唯一的，创建索引允许指定单个列或者是多个列。主键也是索引。

 

Mysql各种索引区别：

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

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

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

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

组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。

 

优点：

惟一索引，能够在索引和信息之间形成一对一映射关系，增加数据的惟一性特点。

能提高数据的搜索及检索速度，符合数据库建立的初衷。

能够加快表与表之间的连接速度。

在信息检索过程中，若使用分组及排序子句进行时，通过建立索引能有效的减少检索过程中所需的分组及排序时间，提高检索效率。

缺点：

需花费较多的时间去建立并维护索引

创建的索引需要占用一定的物理存储空间

对表中的数据进行修改时，例如对其进行增加、删除或者是修改操作时，索引还需要进行动态的维护

 

什么样的字段适合建索引？

唯一、不为空、经常被查询的字段

 

索引的结构：Mysql索引主要有两种结构：B+Tree索引和Hash索引.

Hash索引

MySQL中，只有Memory（Memory表只存在内存中，断电会消失，适用于临时表）存储引擎显示支持Hash索引，是Memory表的默认索引类型，尽管Memory表也可以使用B+Tree索引。hsah索引把数据的索引以hash形式组织起来，因此当查找某一条记录的时候,速度非常快。当时因为是hash结构，每个键只对应一个值，而且是散列的方式分布。所以他并不支持范围查找和排序等功能。

B+树索引

B+tree是mysql使用最频繁的一个索引数据结构，是Inodb和Myisam存储引擎模式的索引类型。相对Hash索引，B+树在查找单条记录的速度比不上Hash索引，但是因为更适合排序等操作，所以他更受用户的欢迎。毕竟不可能只对数据库进行单条记录的操作。

带顺序访问指针的B+Tree

B+Tree所有索引数据都在叶子结点上，并且增加了顺序访问指针,每个叶子节点都有指向相邻叶子节点的指针。

这样做是为了提高区间查询效率，例如查询key为从18到49的所有数据记录，当找到18后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率。

 

聚集索引：每张表只能有一个聚集索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后，便可以确保包含后续索引值的行在物理相邻，这样有助于提高此类查询的性能

1.当定义一个主键时，InnnodDB存储引擎则把它当做聚集索引

2.如果你没有定义一个主键，则InnoDB定位到第一个唯一索引，且该索引的所有列值均非空的，则将其当做聚集索引。

3如果表没有主键或合适的唯一索引INNODB会产生一个隐藏的行ID值6字节的行ID聚集索引，

二级索引：一个表中的所有索引除了聚集索引，其他的都是二级索引（secondary index）

辅助索引：其叶子节点并不包含行记录的全部数据，叶子结点除了包含键值以外，每个叶子结点中的索引行还包含了一个书签，该书签用来告诉存储引擎可以在哪找到相应的数据行，由于innodb引擎表是索引组织表，因此innodb存储引擎的辅助索引的书签就是相应行数据的聚集索引键，

 

数据库事务(Database Transaction)

是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。 事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。

一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

事务是数据库运行中的逻辑工作单位，由DBMS中的事务管理子系统负责事务的处理。

原子性：

事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。

比如银行转账，将A的1000元转给B，A的余额-1000后突然断电，这时B余额还未+1000，为了保持原子性，这个操作将会全部不执行，即A余额恢复原来值。

一致性：

事务在完成时，必须使所有的数据都保持一致状态。

隔离性：

由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据，这称为隔离性。

持久性：

事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

 

下面的四条数据不一致属于隔离性的范畴：即数据库事务隔离级别

数据库事务的隔离级别有4个，由低到高依次为Read uncommitted（读未提交） 、Read committed（读已提交） 、Repeatable read（重复读） 、Serializable（序列化） ，这四个级别可以逐个解决脏读、不可重复读 、幻读 这几类问题。

由于并发操作带来的数据不一致性包括：

丢失数据修改、

读”脏”数据（脏读）、

不可重复读、

产生幽灵数据（幻读）

 

（1）丢失数据修改：

丢失修改指事务1和事务2同时读入相同的数据并进行修改，事务2提交的结果破坏了事务1提交的结果，导致事务1进行的修改丢失。

（2）读“脏”数据（脏读）

脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是脏数据，依据脏数据所做的操作可能是不正确的。

例如：领导把5000元打到singo的账号上，但是该事务并未提交，而singo正好去查看账户，发现工资已经到账，是5000元整，非常高兴。可是不幸的是，领导发现发给singo的工资金额不对，是2000元，于是迅速回滚了事务，修改金额后，将事务提交，最后singo实际的工资只有 2000元。（读取到的5000元是脏数据）

（3）不可重复读（就是说在重复读取的过程中结果不一致）

不可重复读，是指在数据库访问中，一个事务范围内两个相同的查询却返回了不同数据。

在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。

（4）产生幽灵数据（幻读）

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。最终，操作第一个事务的用户发现表中还存在没有修改的数据行。以为产生了幻觉。

主键和外键的区别？

主键在本表中是唯一的、不可唯空的，外键可以重复可以唯空；外键和另一张表的主键关联，不能创建对应表中不存在的外键。

 

视图是一个虚拟表，其内容由查询定义，把经常使用的数据定义为视图。

优点：

①简化了操作，把经常使用的数据定义为视图。

我们在使用查询时，在很多时候我们要使用聚合函数，同时还要 显示其它字段的信息，可能还会需要关联到其它表，这时写的语句可能会很长，如果这个动作频繁发生的话，我们可以创建视图，这以后，我们只需要select * from view就可以啦，这样很方便。

②安全性，用户只能查询和修改能看到的数据。

因为视图是虚拟的，物理上是不存在的，只是存储了数据的集合，我们可以将基表中重要的字段信息，可以不通过视图给用户，视图是动态的数据的集合，数据是随着基表的更新而更新。同时，用户对视图不可以随意的更改和删除，可以保证数据的安全性。

③逻辑上的独立性，屏蔽了真实表的结构带来的影响。

当对通过视图看到的数据进行修改时，相应的基本表的数据也要发生变化，同时，若基本表的数据发生变化，则这种变化也可以自动地反映到视图中。

缺点:

①  性能差

数据库必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询，数据库也要把它变成一个复杂的结合体，需要花费一定的时间。

②  修改限制

当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但是，对于比较复杂的试图，可能是不可修改的。

数据库表的连接方式：

1.  内连接 ：( 对于不匹配的都会进行舍弃)

select * from a inner join b on a.id=b.id;

select * from a,b where a.id = b.id;

2、外连接（左外连接、右外连接、全外连接）

--左外连接：(以左表为基表（驱动表），将左表的每一条数据都与右表匹配，如果在右表中没有匹配数据，则右表补null)

select * from a left join b on a.id=b.id;

select * from a,b where a.id=b.id(+);

--右外连接：(以右表为基表（驱动表），将右表的每一条数据都与左表匹配，如果在左表中没有匹配数据，则左表补null)

select * from a right join b on a.id = b.id;

select * from a,b where a.id(+) = b.id;

--全外连接：左表和右表没有符合条件的都补null值

select * from a full join b on a.id = b.id;

select * from a,b where a.id(+)=b.id unionselect * from a,b where a.id = b.id(+);

3、笛卡尔集（交叉连接）

select * from a cross join b;

select * from a,b;

 

在数据库中查询语句速度很慢，如何优化？

1.建索引

2.减少表之间的关联

3.优化sql，尽量让sql很快定位数据，不要让sql做全表查询，应该走索引,把数据 量大的表排在前面

4.简化查询字段，没用的字段不要，已经对返回结果的控制，尽量返回少量数据

 

union和union all有什么不同

Union，对两个结果集进行并集操作，筛选掉重复的记录，同时进行默认规则的排序；

Union All，对两个结果集进行并集操作，包括重复行，不进行排序；

 

数据库三大范式：

第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列

第二范式（2NF）：（不能部分依赖）

首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

 

第一范式（1NF）：

考虑这样一个表：【联系人】（姓名，性别，电话）

如果在实际场景中，一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF。要符合 1NF 我们只需把列（电话）拆分，即：【联系人】（姓名，性别，家庭电话，公司电话）。

第二范式（2NF）：

考虑一个订单明细表：

【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName）。

因为我们知道在一个订单中可以订购多种产品，所以单单一个 OrderID 是不足以成为主键的，主键应该是（OrderID，ProductID）。显而易见 Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID。所以 OrderDetail 表不符合 2NF。不符合 2NF 的设计容易产生冗余数据。

可以把【OrderDetail】表拆分为

【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）来消除原订单表中UnitPrice，ProductName多次重复的情况。

第三范式（3NF）：

考虑一个订单表【Order】

（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID）。其中 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主键列都完全依赖于主键（OrderID），所以符合 2NF。

不过问题是 CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF。

通过拆分【Order】为

【Order】（OrderID，OrderDate，CustomerID）和

【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）

从而达到 3NF。

 

数据库锁分类

一般可以分为两类，一个是悲观锁，一个是乐观锁，悲观锁一般就是我们通常说的数据库锁机制，乐观锁一般是指用户自己实现的一种锁机制，比如hibernate实现的乐观锁甚至编程语言也有乐观锁的思想的应用

 

悲观锁：顾名思义，就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制

关系数据库中的行锁，表锁不论是读写锁都是悲观锁。

悲观锁按照使用性质划分：

共享锁（Share locks简记为S锁）：也称读锁，事务A对对象T加s锁，其他事务也只能对T加S，多个事务可以同时读，但不能有写操作，直到A释放S锁。

排它锁（Exclusivelocks简记为X锁）：也称写锁，事务A对对象T加X锁以后，其他事务不能对T加任何锁，只有事务A可以读写对象T直到A释放X锁。

更新锁（简记为U锁）：用来预定要对此对象施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；当被读取的对象将要被更新时，则升级为X锁，主要是用来防止死锁的。

因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为排它锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个对象申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为排它锁。这些事务都不会释放共享锁而是一直等待对方释放，这样就造成了死锁。如果一个数据在修改前直接申请更新锁，在数据修改的时候再升级为排它锁，就可以避免死锁。

悲观锁按照作用范围划分：

行锁：锁的作用范围是行级别，数据库能够确定那些行需要锁的情况下使用行锁，如果不知道会影响哪些行的时候就会使用表锁。举个例子，一个用户表user，有主键id和用户生日birthday当你使用update … where id=?这样的语句数据库明确知道会影响哪一行，它就会使用行锁，当你使用update … where birthday=?这样的的语句的时候因为事先不知道会影响哪些行就可能会使用表锁。

表锁：锁的作用范围是整张表。

乐观锁：

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

既然都有数据库提供的悲观锁可以方便使用为什么要使用乐观锁呢？

对于读操作远多于写操作的时候，大多数都是读取，这时候一个更新操作加锁会阻塞所有读取，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，我们只要想办法解决极少量的更新操作的同步问题。换句话说，如果是读写比例差距不是非常大或者你的系统没有响应不及时，吞吐量瓶颈问题，那就不要去使用乐观锁，它增加了复杂度，也带来了额外的风险。

乐观锁的实现

1.使用数据版本（Version）记录机制实现

这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的“version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

2．时间戳（timestamp）：

和版本号基本一样，只是通过时间戳来判断而已，注意时间戳要使用数据库服务器的时间戳不能是业务系统的时间。

3.待更新字段：

和版本号方式相似，只是不增加额外字段，直接使用有效数据字段做版本控制信息，因为有时候我们可能无法改变旧系统的数据库表结构。假设有个待更新字段叫count,先去读取这个count,更新的时候去比较数据库中count的值是不是我期望的值（即开始读的值），如果是就把我修改的count的值更新到该字段，否则更新失败。java的基本类型的原子类型对象如AtomicInteger就是这种思想。

4.所有字段：

和待更新字段类似，只是使用所有字段做版本控制信息，只有所有字段都没变化才会执行更新