数据库范式

第一范式
- 列不可分
- eg:【联系人】（姓名，性别，电话），一个联系人有家庭电话和公司电话，那么这种表结构设计就没有达到 1NF
第二范式
- 有主键，保证完全依赖
- eg:订单明细表【OrderDetail】（OrderID，ProductID，UnitPrice，Discount，Quantity，ProductName），Discount（折扣），Quantity（数量）完全依赖（取决）于主键（OderID，ProductID），而 UnitPrice，ProductName 只依赖于 ProductID，不符合2NF
第三范式
- 无传递依赖(非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况)
- eg:订单表【Order】（OrderID，OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity）主键是（OrderID），CustomerName，CustomerAddr，CustomerCity 直接依赖的是 CustomerID（非主键列），而不是直接依赖于主键，它是通过传递才依赖于主键，所以不符合 3NF

数据库索引

索引是对数据库表中一个或多个列的值进行排序的数据结构，以协助快速查询、更新数据表中的数据
索引的实现通常使用B-Tree及其变种
索引加速了数据的访问，因为存储引擎不会再去扫描整张表得到需要的数据
相反，它从根节点开始，根节点保存了子节点的指针，存储引擎会根据指针快速寻找数据

上图显示了一种索引方式
左边是数据库中的数据表，有col1和col2两列，一共15条记录
右边是以col2为索引列的B-Tree索引
每个节点包含索引的键值和对应数据表地址的指针
这样就可以通过B-Tree在O(logn)的时间复杂度内获取相应数据，明显加快检索速度

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构

叶节点的data域存放的是数据记录的地址

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。使用辅助索引需要查找两次索引。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

InnoDB的主键选择与插入优化

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

上文讨论过InnoDB的索引实现，InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。

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

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

只要可以，请尽量在InnoDB上采用自增字段做主键。

索引底层实现原理和优化

在数据结构中，我们常见的搜索结构就是二叉搜索树和AVL树（高度平衡的二叉搜索树，为了提高二叉搜索树的效率，减少树的平均搜索长度）
然而，无论是二叉搜索树还是AVL树，当数据量比较大的时候，都会由于树的深度过大而造成IO读写过于频繁，进而导致查询效率低下
因而对于索引而言，多叉搜索树成为不二选择
特别的，B-Tree的各种操作能使B树保持较低的高度，从而保证高的查询效率

B-Tree 平衡多路查找树

B-Tree是一种平衡多路查找树，是一种动态查找效率很高的树形结构
B-Tree中所有结点的孩子结点的最大值成为B-Tree的阶，B-Tree的阶通常用m表示，简称m叉树
一般来说，m>3
一棵m阶的B-Tree或是一棵空树，或者是满足下列条件的m叉树
- 树中的每个结点最多有m个孩子结点
- 若根结点不是叶子结点，则根结点至少有2个孩子结点
- 除根结点外，其他结点至少有(m/2的上界)个孩子结点
- 结点的结构如下图所示，
  - 其中n为结点中关键字个数，(m/2的上界)-1<=n<=m-1
  - di(1<=i<=n)为该结点的n个关键字值的第i个，且di< d(i+1)
  - ci(0<=i<=n)为该结点孩子结点的指针，且ci所指向的节点的关键字均大于或等于di且小于d(i+1)
  - 所有的叶结点都在同一层上，并且不带信息（可以看作是外部结点或查找失败的结点，实际上这些结点不存在，指向这些结点的指针为空）
下图是一棵4阶B-Tree，4叉树结点的孩子结点的个数范围[2,4]，其中，有2个结点有4个孩子结点，有1个结点有3个孩子结点，有5个结点有2个孩子结点
B-Tree的查找类似二叉排序树的查找，所不同的是B-树每个结点上是多关键码的有序列表，在到达某个结点时，先在有序表中查找，若找到，则查找成功。否则，到按照对应的指针信息指向的子树中去查找，当到达叶子结点时，则说明树中没有对应的关键码
由于B-Tree的高检索效率，B-树主要应用在文件系统和数据库中，对于存储在硬盘上的大型数据库文件，可以极大程度减少访问硬盘次数，大幅度提高数据检索效率

B+ Tree InnoDB存储引擎的索引实现

B+Tree是应文件系统所需而产生的一种B-Tree树的变形树
一棵m阶的B+树和m阶的B_TREE的差异在于以下三点：
- n棵子树的结点中含有n个关键码
- 所有的叶子结点中包含了全部关键码的信息，及指向含有这些关键码记录的指针，且叶子结点本身依关键码的大小自小而大的顺序链接
- 非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键码
下图为一棵3阶的B+树
通常在B+树上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点
因此可以对B+树进行两种查找运算：一种是从最小关键字起顺序查找，另一种是从根节点开始，进行随机查找
在B+树上进行随机查找、插入和删除的过程基本上与B-树类似
只是在查找时，若非终端结点上的关键码等于给定值，并不终止，而是继续向下直到叶子结点
因此，对于B+树，不管查找成功与否，每次查找都是走了一条从根到叶子结点的路径

为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引

B+树的磁盘读写代价更低
- B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B树更小
- 如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多
- 一次性读入内存中的需要查找的关键字也就越多，相对来说IO读写次数也就降低了
B+树的查询效率更加稳定
- 由于内部结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引，所以，任何关键字的查找必须走一条从根结点到叶子结点的路
- 所有关键字查询的路径长度相同，导致每一个数据的查询效率相当
数据库索引采用B+树而不是B树的主要原因
- B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围的查询是非常频繁的
- 而B树只能中序遍历所有节点，效率太低

文件索引和数据库索引为什么使用B+树

文件与数据库都是需要较大的存储，也就是说，它们都不可能全部存储在内存中，故需要存储到磁盘上
而所谓索引，则为了数据的快速定位与查找
那么索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数，因此B+树相比B树更为合适。
数据库系统巧妙利用了局部性原理与磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入
而红黑树这种结构，高度明显要深的多，并且由于逻辑上很近的节点(父子)物理上可能很远，无法利用局部性
最重要的是，B+树还有一个最大的好处：方便扫库。
B树必须用中序遍历的方法按序扫库，而B+树直接从叶子结点挨个扫一遍就完了
B+树支持range-query非常方便，而B树不支持，这是数据库选用B+树的最主要原因

索引的优点

大大加快数据的检索速度，这也是创建索引的最主要的原因
加速表和表之间的连接
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性

什么情况下设置了索引但无法使用

以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配
OR语句前后没有同时使用索引
数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
对于多列索引，必须满足最左匹配原则
- eg：多列索引col1、col2和col3，则索引生效的情形包括 col1或col1，col2或col1，col2，col3)

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

经常作查询选择的字段
经常作表连接的字段
经常出现在order by, group by, distinct 后面的字段

创建索引时需要注意什么

非空字段：
- 应该指定列为NOT NULL，除非你想存储NULL。
- 在mysql中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。
- 应该用0、一个特殊的值或者一个空串代替空值
取值离散大的字段
- 变量各个取值之间的差异程度
- 差异大的列放到联合索引的前面
- 可以通过count()函数查看字段的差异值，返回值越大说明字段的唯一值越多，字段的离散程度高
索引字段越小越好
- 数据库的数据存储以页为单位一页存储的数据越多一次IO操作获取的数据越大效率越高

索引的缺点

时间方面
- 创建索引和维护索引要耗费时间
- 具体地，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护
- 这样就降低了数据的维护速度
空间方面
- 索引需要占物理空间

索引的分类

普通索引和唯一性索引
- 索引列的值的唯一性
单个索引和复合索引
- 索引列所包含的列数
聚簇索引与非聚簇索引
- 聚簇索引的叶子节点就是数据节点
- 非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针

主键、自增主键、主键索引与唯一索引概念区别

主键
- 指字段唯一、不为空值的列
主键索引
- 指的就是主键，主键是索引的一种，是唯一索引的特殊类型
- 创建主键的时候，数据库默认会为主键创建一个唯一索引
自增主键
- 段类型为数字、自增、并且是主键
唯一索引
- 索引列的值必须唯一，但允许有空值
- 主键是唯一索引，这样说没错
- 但反过来说，唯一索引也是主键就错误了
- 因为唯一索引允许空值，主键不允许有空值
- 所以不能说唯一索引也是主键

主键就是聚集索引吗？主键和索引有什么区别？

主键是一种特殊的唯一性索引，其可以是聚集索引，也可以是非聚集索引
在SQLServer中，主键的创建必须依赖于索引
默认创建的是聚集索引
但也可以显式指定为非聚集索引。
InnoDB作为MySQL存储引擎时，默认按照主键进行聚集
如果没有定义主键，InnoDB会试着使用唯一的非空索引来代替
如果没有这种索引，InnoDB就会定义隐藏的主键然后在上面进行聚集
所以，对于聚集索引来说，你创建主键的时候，自动就创建了主键的聚集索引

数据库事务

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位
其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态

事务的特征

原子性(Atomicity)
- 事务所包含的一系列数据库操作要么全部成功执行，要么全部回滚
一致性(Consistency)
- 事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态
隔离性(Isolation)
- 并发执行的事务之间不能相互影响
持久性(Durability)
- 事务一旦提交，对数据库中数据的改变是永久性的

事务并发带来的问题

脏读
- 一个事务读取了另一个事务未提交的数据
不可重复读
- 不可重复读的重点是修改
- 同样条件下两次读取结果不同，也就是说，被读取的数据可以被其它事务修改
幻读
- 幻读的重点在于新增或者删除
- 同样条件下两次读出来的记录数不一样

隔离级别

隔离级别决定了一个session中的事务可能对另一个session中的事务的影响
ANSI标准定义了4个隔离级别，MySQL的InnoDB都支持，分别是
- READ UNCOMMITTED
  - 最低级别的隔离
  - 通常又称为dirty read
  - 它允许一个事务读取另一个事务还没commit的数据
  - 这样可能会提高性能
  - 但是会导致脏读问题
- READ COMMITTED
  - 在一个事务中只允许对其它事务已经commit的记录可见
  - 该隔离级别不能避免不可重复读问题
- REPEATABLE READ
  - 在一个事务开始后，其他事务对数据库的修改在本事务中不可见，直到本事务commit或rollback
  - 但是，其他事务的insert/delete操作对该事务是可见的
  - 也就是说，该隔离级别并不能避免幻读问题。在一个事务中重复select的结果一样，除非本事务中update数据库
- SERIALIZABLE
  - 最高级别的隔离
  - 只允许事务串行执行
MySQL默认的隔离级别是REPEATABLE READ

MySQL事务支持

MySQL的事务支持不是绑定在MySQL服务器本身，而是与存储引擎相关
MyISAM：不支持事务，用于只读程序提高性能
InnoDB：支持ACID事务、行级锁、并发
Berkeley DB：支持事务

设置数据库事务

事务控制语句

BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；也可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
SAVEPOINT identifier；：SAVEPOINT允许在事务中创建一个保存点，一个事务中可以有多个SAVEPOINT
RELEASE SAVEPOINT identifier；删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
ROLLBACK TO identifier；把事务回滚到标记点；
SET TRANSACTION；用来设置事务的隔离级别

MYSQL事务处理的两种方法：

用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

直接用 SET 来改变 MySQL 的自动提交模式

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

MySQL存储引擎

MySQL5.5之前默认的存储引擎是MyISAM
MySQL5.5之后默认的存储引擎改为InnoDB
同时作为维护mysql内部结构的mysql和information_schema两个databases中的表，依然使用MyISAM存储引擎，而且不能被更改为InnoDB

MyISAM

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，其由早期的ISAM（Indexed Sequential Access Method：有索引的顺序访问方法）所改良
虽然MyISAM性能极佳，但却有一个显著的缺点：不支持事务处理
不过，MySQL也导入了另一种数据库引擎InnoDB，以强化参考完整性与并发违规处理机制，后来就逐渐取代MyISAM

InnoDB

InnoDB是MySQL的数据库引擎之一
与传统的ISAM、MyISAM相比，InnoDB的最大特色就是支持ACID兼容的事务功能，类似于PostgreSQL

MyISAM和InnoDB的区别

存储结构

每个MyISAM在磁盘上存储成三个文件
- 第一个文件的名字以表的名字开始，扩展名指出文件类型。
- .frm文件存储表定义
- 数据文件的扩展名为.MYD (MYData)
- 索引文件的扩展名是.MYI (MYIndex)
InnoDB所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件）
InnoDB表的大小只受限于操作系统文件的大小，一般为2GB

存储空间

MyISAM
- 可被压缩，占据的存储空间较小
- 支持静态表、动态表、压缩表三种不同的存储格式
InnoDB
- 需要更多的内存和存储
- 它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引

可移植性、备份及恢复

MyISAM
- 数据是以文件的形式存储
- 所以在跨平台的数据转移中会很方便
- 同时在备份和恢复时也可单独针对某个表进行操作
InnoDB
- 免费的方案可以是拷贝数据文件、备份binlog，或者用mysqldump，在数据量达到几十G的时候就相对痛苦了

事务支持

MyISAM
- 强调的是性能
- 每次查询具有原子性
- 其执行速度比InnoDB类型更快
- 但是不提供事务支持。
InnoDB
- 提供事务、外键等高级数据库功能
- 具有事务提交、回滚和崩溃修复能力

AUTO_INCREMENT

MyISAM
- 在MyISAM中，可以和其他字段一起建立联合索引
- 引擎的自动增长列必须是索引
- 如果是组合索引，自动增长可以不是第一列
- 它可以根据前面几列进行排序后递增
InnoDB
- InnoDB中必须包含只有该字段的索引
- 并且引擎的自动增长列必须是索引
- 如果是组合索引也必须是组合索引的第一列

表锁差异

MyISAM只支持表级锁，用户在操作MyISAM表时，select、update、delete和insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据
InnoDB支持事务和行级锁。行锁大幅度提高了多用户并发操作的性能，但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的

全文索引

MyISAM支持FULLTEXT类型的全文索引
InnoDB不支持FULLTEXT类型的全文索引，但是InnoDB可以使用sphinx插件支持全文索引，并且效果更好

表主键

MyISAM允许没有任何索引和主键的表存在，索引都是保存行的地址
对于InnoDB，如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分，附加索引保存的是主索引的值

表的具体行数

MyISAM保存表的总行数，select count() from table;会直接取出该值
而InnoDB没有保存表的总行数，如果使用select count() from table；就会遍历整个表，消耗相当大
但是在加了where条件后，MyISAM和InnoDB处理的方式都一样。

CURD操作

在MyISAM中，如果执行大量的SELECT，MyISAM是更好的选择
对于InnoDB，如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。
DELETE从性能上InnoDB更优
但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在InnoDB上如果要清空保存有大量数据的表，最好使用truncate table这个命令

外键

MyISAM不支持外键
InnoDB支持外键

总结

通过上述的分析，基本上可以考虑使用InnoDB来替代MyISAM引擎了
原因是InnoDB自身很多良好的特点，比如事务支持、存储过程、视图、行级锁、外键等等
尤其在并发很多的情况下，相信InnoDB的表现肯定要比MyISAM强很多
另外，必须需要注意的是，任何一种表都不是万能的，合适的才是最好的，才能最大的发挥MySQL的性能优势
如果是不复杂的、非关键的Web应用，还是可以继续考虑MyISAM的，这个具体情况具体考虑

实践中如何优化MySQL

实践中，MySQL的优化主要涉及SQL语句及索引的优化、数据表结构的优化、系统配置的优化和硬件的优化四个方面，如下图所示

SQL语句及索引的优化

SQL语句的优化

优化insert语句：一次插入多值
应尽量避免在where子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描
应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
优化嵌套查询：子查询可以被更有效率的连接(Join)替代
很多时候用exists代替in是一个好的选择
- EXISTS比较快
- 因为EXISTS返回一个Boolean型
- 而IN返回一个值

索引的优化

建议在经常作查询选择的字段、经常作表连接的字段以及经常出现在order by、group by、distinct 后面的字段中建立索引
但必须注意以下几种可能会引起索引失效的情形
- 以“%(表示任意0个或多个字符)”开头的LIKE语句，模糊匹配
- OR语句前后没有同时使用索引
- 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）
- 对于多列索引，必须满足最左匹配原则
  - eg,多列索引col1、col2和col3，则索引生效的情形包括col1或col1，col2或col1，col2，col3

数据库表结构的优化

表的垂直拆分

把含有多个列的表拆分成多个表，解决表宽度问题
具体包括以下几种拆分手段
- 把不常用的字段单独放在同一个表中
- 把大字段独立放入一个表中
- 把经常使用的字段放在一起
这样做的好处是非常明显的，具体包括
- 拆分后业务清晰
- 拆分规则明确
- 系统之间整合或扩展容易
- 数据维护简单

表的水平拆分

表的水平拆分用于解决数据表中数据过大的问题
水平拆分每一个表的结构都是完全一致的
一般地，将数据平分到N张表中的常用方法包括以下两种：
- 对ID进行hash运算，如果要拆分成5个表，mod(id,5)取出0~4个值
- 针对不同的hashID将数据存入不同的表中
表的水平拆分会带来一些问题和挑战
- 包括跨分区表的数据查询、统计及后台报表的操作等问题
但也带来了一些切实的好处：
- 表分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度
- 表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用
- 需要把数据存放到多个数据库中，提高系统的总体可用性(分库，鸡蛋不能放在同一个篮子里)

系统配置的优化

操作系统配置的优化
- 增加TCP支持的队列数
mysql配置文件优化
- Innodb缓存池设置
  - innodb_buffer_pool_size，推荐总内存的75%
  - 缓存池的个数innodb_buffer_pool_instances

存储过程

什么是存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合
进一步地说，存储过程是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查）
然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了

存储过程有什么优缺点

存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率
当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码
减少网络传输，在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小
通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全

drop、delete与truncate的区别

SQL中的drop、delete、truncate都表示删除

delete

delete用来删除表的全部或者一部分数据行
执行delete之后，用户需要提交(commmit)或者回滚(rollback)来执行删除或者撤销删除
delete命令会触发这个表上所有的delete触发器

truncate

truncate删除表中的所有数据
这个操作不能回滚，也不会触发这个表上的触发器
truncate比delete更快，占用的空间更小

drop

drop命令从数据库中删除表
所有的数据行，索引和权限也会被删除
所有的DML触发器也不会被触发
这个命令也不能回滚

视图

视图是一种虚拟的表
通常是有一个表或者多个表的行或列的子集
具有和物理表相同的功能
视图的建立和删除不影响基本表
可以对视图进行增，删，改，查等操作
- 对视图内容的更新（增删改）直接影响基本表
- 当视图来自多个基本表时，不允许添加和删除数据，会报错Can not modify more than one base table through a join view 'view_name'
相比多表查询，它使得我们获取数据更容易

游标

游标是对查询出来的结果集作为一个单元来有效的处理
游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行
可以对结果集当前行做修改
一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要
在操作mysql的时候，我们知道MySQL检索操作返回一组称为结果集的行。这组返回的行都是与 SQL语句相匹配的行（零行或多行）。使用简单的SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们），有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因
游标（cursor）是一个存储在MySQL服务器上的数据库查询
它不是一条SELECT语句，而是被该语句检索出来的结果集
在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据
游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改

触发器

触发器是与表相关的数据库对象
在满足定义条件时触发，并执行触发器中定义的语句集合
触发器的这种特性可以协助应用在数据库端确保数据库的完整性

表级锁、页级索和行级锁

表级，直接锁定整张表，在你锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则读也不允许
行级，仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进行操作。
页级，表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定，对BDB表进行页级锁定，对InnoDB表进行行级锁定。

为什么会出现死锁

InnoDB使用行锁定，BDB使用页锁定。对于这两种存储引擎，都可能存在死锁。这是因为，在SQL语句处理期间，InnoDB自动获得行锁定和BDB获得页锁定，而不是在事务启动时获得。

锁特点

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

行级锁的优缺点

优点

当在许多线程中访问不同的行时只存在少量锁定冲突。
回滚时只有少量的更改。
可以长时间锁定单一的行。

缺点

比页级或表级锁定占用更多的内存。
当在表的大部分中使用时，比页级或表级锁定速度慢，因为你必须获取更多的锁。
如果你在大部分数据上经常进行GROUP BY操作或者必须经常扫描整个表，比其它锁定明显慢很多。
用高级别锁定，通过支持不同的类型锁定，你也可以很容易地调节应用程序，因为其锁成本小于行级锁定。

什么情况下表锁定优先于行锁定

表的大部分语句用于读取。
对严格的关键字进行读取和更新，你可以更新或删除可以用单一的读取的关键字来提取的一行：
- UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
- DELETE FROM tbl_name WHERE unique_key_col=key_value;
SELECT结合并行的INSERT语句，并且只有很少的UPDATE或DELETE语句。
在整个表上有许多扫描或GROUP BY操作，没有任何写操作。

悲观锁与乐观锁

悲观锁与乐观锁是两种常见的资源并发锁设计思路，也是并发编程中一个非常基础的概念

悲观锁

原理

特点是先获取锁，再进行业务操作
即“悲观”的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作
通常来讲，在数据库上的悲观锁需要数据库本身提供支持，即通过常用的select … for update操作来实现悲观锁
当数据库执行select … for update时会获取被select中的数据行的行锁
因此其他并发执行的select … for update如果试图选中同一行则会发生排斥（需要等待行锁被释放）
select for update获取的行锁会在当前事务结束时自动释放，因此必须在事务中使用

特别注意

不同的数据库对select… for update的实现和支持都是有所区别的
例如oracle支持select for update no wait，表示如果拿不到锁立刻报错，而不是等待，mysql就没有no wait这个选项
mysql还有个问题是:select… for update语句执行中所有扫描过的行都会被锁上，这一点很容易造成问题。因此，如果在mysql中用悲观锁务必要确定使用了索引，而不是全表扫描

乐观锁

乐观锁的特点先进行业务操作，只在最后实际更新数据时进行检查数据是否被更新过
若未被更新过，则更新成功；否则，失败重试
乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持

一般的做法是在需要锁的数据上增加一个版本号或者时间戳,然后按照如下方式实现

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根据获取的数据进行业务操作，得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

乐观锁是否在事务中其实都是无所谓的
底层机制:
- 在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放
- 因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这其间没有发生并发的修改
- 如果更新失败，即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程

悲观锁与乐观锁的应用场景

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁
乐观锁在不发生取锁失败的情况下开销比悲观锁小
但是一旦发生失败回滚开销则比较大
因此乐观锁适合用在取锁失败概率比较小的场景，可以提升系统并发性能

共享锁和排他锁

行级锁是MySQL中锁定粒度最细的一种锁，行级锁能大大减少数据库操作的冲突。行级锁分为共享锁和排他锁两种。

共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。

用法

1	SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE，MySQL会对查询结果中的每行都加共享锁

当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。

其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

排他锁（eXclusive Lock）

排他锁又称写锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。

用法

1	SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE，MySQL会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

意向锁

意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB中的两个表锁：

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需要用户干预。

对于insert、update、delete，InnoDB会自动给涉及的数据加排他锁（X）；

对于一般的Select语句，InnoDB不会加任何锁，事务可以通过以下语句给显示加共享锁或排他锁。

共享锁：SELECT ... LOCK IN SHARE MODE;
排他锁：SELECT ... FOR UPDATE;

JDBC对事物的支持

对于JDBC而言，每条单独的语句都是一个事务，即每个语句后都隐含一个commit
实际上，Connection 提供了一个auto-commit的属性来指定事务何时结束
当auto-commit为true时，当每个独立SQL操作的执行完毕，事务立即自动提交，也就是说，每个SQL操作都是一个事务
当auto-commit为false时，每个事务都必须显式调用commit方法进行提交，或者显式调用rollback方法进行回滚

auto-commit默认为true

try {  
    conn.setAutoCommit(false);  //将自动提交设置为false        
    ps.executeUpdate("修改SQL"); //执行修改操作  
    ps.executeQuery("查询SQL");  //执行查询操作                 
    conn.commit();      //当两个操作成功后手动提交     
} catch (Exception e) {  
    conn.rollback();    //一旦其中一个操作出错都将回滚，使两个操作都不成功  
    e.printStackTrace();  
}

为了能够将多条SQL当成一个事务执行
- 首先通过Connection关闭auto-commit模式
- 然后通过Connection的setTransactionIsolation()方法设置事务的隔离级别
- 最后分别通过Connection的commit()方法和rollback()方法来提交事务和回滚事务

参考文章