数据库原理

很详细的文章，来自https://blog.csdn.net/xiaomingdetianxia/article/details/72475924

1.存储过程

是什么？

我们常用的关系型数据库是MySQL，操作数据库的语言一般为SQL语句，SQL在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成某种特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对面向对象方法的模拟，它允许控制数据的访问方式。

优点

(1)存储过程增强了SQL语言的功能和灵活性：存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。 
(2)存储过程允许标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且可以随时对存储过程进行修改，对应用程序源代码毫无影响。 
(3)存储过程能实现较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。 
(4)存储过程能减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织成存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。 
(5)存储过程可被作为一种安全机制来充分利用：系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

2.索引

是什么？

索引（Index）是帮助MySQL高效获取数据的数据结构；在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，可以在这些数据结构上实现高级查找算法，提高查询速度，这种数据结构，就是索引。

索引存储分类

索引是在MySQL的存储引擎层中实现的，而不是在服务层实现的。所以各种存储引擎支持的索引并不相同，MySQL目前提供了以下4种索引。

B-Tree 索引：最常见的索引类型，大部分引擎都支持B树索引。 
HASH 索引：只有Memory引擎支持，使用场景简单。 
R-Tree 索引(空间索引)：空间索引是MyISAM的一种特殊索引类型，主要用于地理空间数据类型。 
Full-text (全文索引)：全文索引也是MyISAM的一种特殊索引类型，主要用于全文索引，InnoDB从MySQL5.6版本提供对全文索引的支持。

B-TREE索引类型

普通索引 
这是最基本的索引类型，而且它没有唯一性之类的限制，可以通过以下几种方式创建： 
（1）创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,…); 
（2）修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2,…); 
（3）创建表时指定索引：CREATE TABLE 表名 ( […], INDEX 索引名 (列名1，列名 2,…) ); 
UNIQUE索引 
表示唯一的，不允许重复的索引，若某一字段的信息不能重复（例如身份证号），可以将该字段的索引设置为unique： 
（1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名1，列名2,…); 
（2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列名1，列名2,…); 
（3）创建表时指定索引：CREATE TABLE 表名( […], UNIQUE 索引名 (列名1，列名2,…)); 
主键：PRIMARY KEY索引 
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。可以将其理解为 索引名固定为 PRIMARY KEY 的 UNIQUE索引。 
（1）主键一般在创建表的时候指定：“CREATE TABLE 表名( […], PRIMARY KEY (列的列表) ); ”。 
（2）但是，我们也可以通过修改表的方式加入主键：“ALTER TABLE 表名 ADD PRIMARY KEY (列的列表); ”。 
每个表只能有一个主键。 （主键相当于聚合索引，是查找最快的索引） 
注：不能用CREATE INDEX语句创建PRIMARY KEY索引

常用语法

设置索引 
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为数据表增加索引。

1．ALTER TABLE - ALTER TABLE可以用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

• 1
• 2
• 3
• 4
• 5

• 1
• 2
• 3
• 4
• 5

2．CREATE INDEX - CREATE INDEX可对表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

• 1
• 2
• 3

• 1
• 2
• 3

删除索引 
可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为一条语句处理，语法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

• 1
• 2
• 3
• 4
• 5

• 1
• 2
• 3
• 4
• 5

其中，前两条语句是等价的，删除掉table_name中名为index_name的索引。 
第3条语句只在删除PRIMARY KEY索引时使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有一个或多个UNIQUE索引，则MySQL将删除第一个UNIQUE索引。

如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。

查看索引

mysql> show index from tblname;

• 1

• 1

设置索引的原则

1. 较频繁的作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在 WHERE 子句中的字段不该创建索引
5. 索引的选择性较低不宜建索引 
   注：所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数的比值，显然选择性的取值范围为(0, 1]：

SELECT count(DISTINCT(column_name))/count(*) AS Selectivity FROM table_name;

• 1

• 1

索引的弊端

索引是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。

参考1

3.B+ 树

 
如上图，是一颗b+树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

b+树性质

1.通过上面的分析，我们知道IO次数取决于b+树的高度h，假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。 
2.当b+树的数据项是复合的数据结构的时候，比如(name,age,sex)，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

参考2

4.事务

是什么？

事务（Transaction）是并发控制的基本单位。所谓的事务，它是一个操作序列，由一条或者多条sql语句组成，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

ACID特性

事务应该具有4个属性：原子性、一致性、隔离性、持久性。

原子性（Atomicity）：指整个数据库事务是不可分割的工作单位。只有事务中所有的数据库操作都执行成功，整个事务的执行才算成功。事务中任何一个sql语句执行失败，那么已经执行成功的sql语句也必须撤销，数据库状态应该退回到执行事务前的状态。 
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束，也就是说在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏 
隔离性（Isolation）：隔离性也叫做并发控制、可串行化或者锁。事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见，这通常使用锁来实现多个事务并发执行时，一个事务的执行不应影响其他事务的执行。 
持久性（Durability）：表示事务一旦提交了，其结果就是永久性的，也就是数据就已经写入到数据库了，如果发生了宕机等事故，数据库也能将数据恢复。

事务的分类

事务分为一下5类：

1. 扁平事务；
2. 带有保存点的扁平事务；
3. 链事务；
4. 嵌套事务；
5. 分布式事务。

扁平事务 
扁平事务是最简单的一种，也是实际开发中使用的最多的一种事务。在这种事务中，所有操作都处于同一层次，最常见的方式如下：

 BEGIN WORK
     Operation 1
     Operation 2
     Operation 3
     ...
     Operation N
 COMMIT WORK

• 1
• 2
• 3
• 4
• 5
• 6
• 7

• 1
• 2
• 3
• 4
• 5
• 6
• 7

或者：

BEGIN WORK
     Operation 1
     Operation 2
     Operation 3
     ...
     Operation N
     (Error Occured)
 ROLLBACK WORK

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

扁平事务很简单，但有一个主要缺点是不能提交或回滚事务的某一部分，或者分几个独立的步骤去提交。 
比如有这样的一个例子，我从呼和浩特去深圳，为了便宜，我可能这么干：

 BEGIN WORK
     Operation1:呼和浩特---火车--->北京
     Operation2:北京---飞机--->深圳
 ROLLBACK WORK

• 1
• 2
• 3
• 4

• 1
• 2
• 3
• 4

但是，如果在Operation1中，从呼和浩特到北京的火车晚点了，错过了航班，怎么办？ 
因为扁平事务的特性，那我就需要回滚，我需要再回到呼和浩特，这样做的成本太高，所以就有了下面的第二种事务——带有保存点的扁平事务。

带有保存点的扁平事务 
这种事务除了支持扁平事务支持的操作外，允许在事务执行过程中回滚到同一事务中较早的一个状态，这是因为可能某些事务在执行过程中出现的错误并不会对所有的操作都无效，放弃整个事务不合乎要求，开销也太大。保存点用来通知系统应该记住事务当前的状态，以便以后发生错误时，事务能回到该状态。

链事务 
链事务，就是指回滚时，只能恢复到最近一个保存点；而带有保存点的扁平事务则可以回滚到任意正确的保存点。

嵌套事务

通过下面实例来说明什么叫嵌套事务

BEGIN WORK
     SubTransaction1:
             BEGIN WORK
                 SubOperationX
             COMMIT WORK
     SubTransaction2:
             BEGIN WORK
                 SubOperationY
             COMMIT WORK
     ...
     SubTransactionN:
             BEGIN WORK
                 SubOperationN
             COMMIT WORK
 COMMIT WORK

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

这就是嵌套事务，在事务中再嵌套事务，位于根节点的事务称为顶层事务。事务的前驱称为父事务，事务的下一层称为子事务。

子事务既可以提交也可以回滚，但是它的提交操作并不马上生效，除非由其父事务提交。因此就可以确定，任何子事务都在顶层事务提交后才真正的被提交了。同理，任意一个事务的回滚都会引起它的所有子事务一同回滚。

分布式事务 
分布式事务通常是指在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点，比如：通过建设银行向招商银行转账，建设银行和招商银行肯定用的不是同一个数据库，同时二者的数据库也不在一个网络节点上，那么当用户跨行转账，就是通过分布式事务来保证数据的ACID的。

在MySQL中使用事务

在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显示地开启一个事务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。

来看看我们可以使用哪些事务控制语句。

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

事务的隔离级别

在数据库操作中，为了有效保证并发读取数据的正确性，提出的事务隔离级别。 
InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。这些隔离级别之间的区别如下： 

脏读：一个事务读取到了另外一个事务没有提交的数据； 
比如：事务T1更新了一行记录的内容，但是并没有提交所做的修改。事务T2读取到了T1更新后的行，然后T1执行回滚操作，取消了刚才所做的修改。现在T2所读取的行就无效了； 
不可重复读：在同一事务中，两次读取同一数据，得到内容不同； 
比如：事务T1读取一行记录，紧接着事务T2修改了T1刚才读取的那一行记录。然后T1又再次读取这行记录，发现与刚才读取的结果不同。这就称为“不可重复”读，因为T1原来读取的那行记录已经发生了变化； 
幻读：同一事务中，用同样的操作读取两次，得到的记录数不相同； 
比如：事务T1读取一条指定的WHERE子句所返回的结果集。然后事务T2新插入 一行记录，这行记录恰好可以满足T1所使用的查询条件中的WHERE子句的条件。然后T1又使用相同的查询再次对表进行检索，但是此时却看到了事务T2刚才插入的新行。这个新行就称为“幻像”，因为对T1来说这一行就像突然出现的一样。 
隔离级别越低，事务请求的锁越少或保持锁的时间就越短。InnoDB存储引擎默认的支持隔离级别是REPEATABLE READ；在这种默认的事务隔离级别下已经能完全保证事务的隔离性要求，即达到SQL标准的SERIALIZABLE级别隔离。

我们可以可以用SET TRANSACTION语句改变单个会话或者所有新进连接的隔离级别。它的语法如下：

5.视图

视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增，改，查操作，视图通常是有一个表或者多个表的行或列的子集，对视图的修改不影响基本表，它使得我们获取数据更容易，相比多表查询。

6.超键 候选键 主键 外键

超键：在关系中能唯一标识元组(数据库中的一条记录)的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。 
候选键：是最小超键，即没有冗余元素的超键。 
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合，用户选作元组标识的一个侯选键称为主键。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值（Null）。 
外键：在一个表中存在的另一个表的主键称此表的外键，外键主要是用来描述两个表的关系。

7.三个范式

第一范式（1NF）：数据库表中的字段都是单一属性的，不可再分。这个单一属性由基本类型构成，包括整型、实数、字符型、逻辑型、日期型等。

第二范式（2NF）：数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），也即所有非关键字段都完全依赖于任意一组候选关键字。 
第三范式（3NF）：在第二范式的基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则符合第三范式。所谓传递函数依赖，指的是如 果存在”A → B → C”的决定关系，则C传递函数依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系： 关键字段 → 非关键字段 x → 非关键字段y。

8.E-R图

是什么？

E-R图也称实体-联系图(Entity Relationship Diagram)，提供了表示实体类型、属性和联系的方法，用来描述现实世界的概念模型。

E-R方法是“实体-联系方法”（Entity-Relationship Approach）的简称。它是描述现实世界概念结构模型的有效方法，是表示概念模型的一种方式，用矩形表示实体型，矩形框内写明实体名；用椭圆表示实体的属性，并用无向边将其与相应的实体型连接起来；用菱形表示实体型之间的联系，在菱形框内写明联系名，并用无向边分别与有关实体型连接起来，同时在无向边旁标上联系的类型（1:1,1:n或m:n）。

构成

在ER图中有如下四个成分： 
矩形框：表示实体，在框中记入实体名。 
菱形框：表示联系，在框中记入联系名。 
椭圆形框：表示实体或联系的属性，将属性名记入框中。对于主属性名，则在其名称下划一下划线。 
连线：实体与属性之间；实体与联系之间；联系与属性之间用直线相连，并在直线上标注联系的类型。（对于一对一联系，要在两个实体连线方向各写1； 对于一对多联系，要在一的一方写1，多的一方写N；对于多对多关系，则要在两个实体连线方向各写N,M。)

实体型(Entity)：具有相同属性的实体具有相同的特征和性质，用实体名及其属性名集合来抽象和刻画同类实体;在E-R图中用矩形表示，矩形框内写明实体名；比如学生张三丰、学生李寻欢都是实体。如果是弱实体的话，在矩形外面再套实线矩形。 
属性(Attribute)：实体所具有的某一特性，一个实体可由若干个属性来刻画。在E-R图中用椭圆形表示，并用无向边将其与相应的实体连接起来；比如学生的姓名、学号、性别、都是属性。如果是多值属性的话，在椭圆形外面再套实线椭圆，如果是派生属性则用虚线椭圆表示。 
联系(Relationship)：联系也称关系，信息世界中反映实体内部或实体之间的联系。实体内部的联系通常是指组成实体的各属性之间的联系；实体之间的联系通常是指不同实体集之间的联系。在E-R图中用菱形表示，菱形框内写明联系名，并用无向边分别与有关实体连接起来，同时在无向边旁标上联系的类型（1 : 1，1 : n或m : n），比如老师给学生授课存在授课关系，学生选课存在选课关系。如果是弱实体的联系则在菱形外面再套菱形。

联系可分为以下 3 种类型： 
(1) 一对一联系(1 ∶1) 
例如，一个班级有一个班长，而每个班长只在一个班级任职，则班级与班长的联系是一对一的。 
(2) 一对多联系(1 ∶N) 
例如，某校教师与课程之间存在一对多的联系“教”，即每位教师可以教多门课程，但是每门课程只能由一位教师来教。 
(3) 多对多联系(M ∶N) 
例如，学生与课程间的联系(“学 ”)是多对多的，即一个学生可以学多门课程，而每门课程可以有多个学生来学。联系也可能有属性。例如，学生“ 学” 某门课程所取得的成绩，既不是学生的属性也不是课程的属性。由于“ 成绩” 既依赖于某名特定的学生又依赖于某门特定的课程，所以它是学生与课程之间的联系“ 学”的属性。

作图步骤

⑴确定所有的实体集合 
⑵选择实体集应包含的属性 
⑶确定实体集之间的联系 
⑷确定实体集的关键字，用下划线在属性上表明关键字的属性组合 
⑸确定联系的类型，在用线将表示联系的菱形框联系到实体集时，在线旁注明联系的类型。

实例： 

参考3

参考4

9.处理重复记录的常用操作

(1)查找表中多余的重复记录，重复记录是根据单个字段(column_name)来判断。

select * from table_name where column_name in (select column_name from table_name group by column_name having count(column_name) > 1)

• 1

• 1

(2)删除表中多余的重复记录，重复记录是根据单个字段(column_name)来判断，只留有id最小的记录。

delete from table_name where column_name in (select b.column_name from (select column_name from table_name group by column_name having count(column_name)>1)b);

• 1

• 1

(3)查找表中多余的重复记录（多个字段）。

select * from table_name a where (a.column_name1,a.column_name2) in (select column_name1,column_name2 from vitae group by column_name1,column_name2 having count(*) > 1)

• 1

• 1

(4)删除表中多余的重复记录（多个字段），只留有rowid最小的记录 。

delete from table_name a where (a.column_name1,a.column_name2) in (select column_name1,column_name2 from table_name group by column_name1,column_name2 having count(*) > 1) and rowid not in (select min(rowid) from table_name group by column_name1,column_name2 having count(rowid)>1)

• 1

• 1

10.批处理

MySQL 支持以批处理的方式执行一批SQL语句，例如：

create table test(id int,name varchar(20));
insert into test values(1,'watson');

• 1
• 2

• 1
• 2

batchfile.txt里包含下面的一些SQL 语句，此文件在linux系统中的路径/home/wming/batchfile.txt：

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15

执行上面的文件，test 表在 MyZone 数据库中：

mysql -uroot -p -D MyZone < /home/wming/batchfile.txt

• 1

• 1

上面的例子通过批处理执行插入语句，下面的例子是通过批处理执行查询语句：

此时的batchfile2.txt里含有query的信息：

select * from test limit 200;
insert into test select * from test;
insert into test select * from test;

• 1
• 2
• 3

• 1
• 2
• 3

下面的mysql0716.out就记录了 select * from test limit 200 查询语句的结果集。

mysql -uroot -p -D MyZone < /home/wming/batchfile2.txt >/home/wming/mysql0716.out

• 1

• 1

11.MyISAM与InnoDB的区别是什么？

1、存储结构

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

2、存储空间

MyISAM：可被压缩，存储空间较小。支持三种不同的存储格式：静态表(默认，但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。 
InnoDB：需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

3、可移植性、备份及恢复

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

4、事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。 
InnoDB：支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

这一点是非常重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

5、AUTO_INCREMENT

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

6、表锁差异

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

7、 全文索引

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

8、表主键

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

9、 表的具体行数

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

10、CURD操作

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

11、 外键

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

12. 乐观锁 与 悲观锁

数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和一致性以及数据库的统一性。

乐观并发控制(乐观锁)和悲观并发控制（悲观锁）是并发控制采用的主要技术手段。

无论是悲观锁还是乐观锁，都是人们定义出来的概念，可以认为是一种思想。其实不仅仅是关系型数据库系统中有乐观锁和悲观锁的概念，像memcache、hibernate、tair等都有类似的概念。

针对不同的业务场景，应该选用不同的并发控制方式。所以，不要把乐观并发控制和悲观并发控制狭义的理解为仅在DBMS中存在的概念，更不要把他们和数据库中提供的锁机制（行锁、表锁、排他锁、共享锁）混为一谈。其实，在DBMS中，悲观锁正是利用数据库本身提供的锁机制来实现的。

悲观锁

在关系数据库管理系统里，悲观并发控制（又名“悲观锁”，Pessimistic Concurrency Control，缩写“PCC”）是一种并发控制的方法。它可以阻止一个事务以影响其他用户的方式来修改数据。如果一个事务执行的操作在某行数据上应用了锁，那只有当这个事务把锁释放，其他事务才能够执行与该锁冲突的操作。

悲观并发控制主要用于数据争用激烈的环境，以及发生并发冲突时使用锁保护数据的成本要低于回滚事务的成本的环境中。

悲观锁：正如其名，它指的是对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度(悲观)，因此，在整个数据处理过程中，将数据处于锁定状态。 悲观锁的实现，往往依靠数据库提供的锁机制 （也只有数据库层提供的锁机制才能真正保证数据访问的排他性，否则，即使在本系统中实现了加锁机制，也无法保证外部系统不会修改数据）。

悲观锁的流程： 
1.在对某一记录进行修改前，先尝试为该记录加上排他锁（exclusive locking）。 
2.如果加锁失败，说明该记录正在被修改，那么当前操作可能要等待或者抛出异常， 具体响应方式由开发者根据实际情况决定。 
3.如果成功加锁，那么就可以对记录做修改，事务完成后就会解锁了。 
4.其间如果有其他事务要对该记录做修改或加排他锁，都会等待该事务将该记录解锁或直接抛出异常。

MySQL InnoDB中使用悲观锁

注意：要使用悲观锁，必须先关闭mysql数据库的自动提交功能，因为MySQL默认使用autocommit模式，也就是说，当你执行一个更新操作后，MySQL会立刻将结果进行提交。

set autocommit=0;

• 1

• 1

//0.开始事务
begin;/begin work;/start transaction; (三者选一就可以)
//1.查询出商品信息
select status from t_goods where id=1 for update;
//2.根据商品信息生成订单
insert into t_orders (id,goods_id) values (null,1);
//3.修改商品status为2
update t_goods set status=2;
//4.提交事务
commit;/commit work;

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10

上面的查询语句中，我们使用了select…for update的方式，这样就通过开启排他锁的方式实现了悲观锁。此时在t_goods表中，id为1的 那条数据就被我们锁定了，其它事务必须等本次事务提交之后才能对该记录进行操作。这样我们可以保证当前的数据不会被其它事务修改。

注意：上面提到，使用select…for update会把数据给锁住，不过我们需要注意一下锁的级别，MySQL InnoDB默认为行级锁。行级锁都是基于索引的，如果一条SQL语句没有用到索引是不会使用行级锁的，会使用表级锁把整张表锁住，这点需要注意。

优点与不足： 
优点：悲观并发控制实际上是采用“先取锁再访问”的保守策略，为数据处理的安全性提供了保证； 
缺点：在效率方面，处理加锁的机制会让数据库产生额外的开销，同时会增加产生死锁的机率；另外，在只读型事务中由于不会产生冲突，也没必要使用锁，这样做只会增加系统负载；还会降低并行性，一个事务如果锁定了某行数据，其他事务就必须等待该事务处理完才可以处理那行数

乐观锁

在关系数据库管理系统里，乐观并发控制（又名“乐观锁”，Optimistic Concurrency Control，缩写“OCC”）是一种并发控制的方法。它假设多用户并发的事务在处理数据时不会彼此互相影响，各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前，每个事务会先检查在该事务读取数据后，有没有其他事务对该数据做过修改。如果其他事务更新过该数据的话，正在提交的事务会进行回滚。乐观事务控制最早是由孔祥重（H.T.Kung）教授提出。

乐观锁（ Optimistic Locking ）是相对悲观锁而言，乐观锁假设数据一般情况下不会造成冲突，所以在事务对数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则返回错误信息，让用户决定如何去做。

相对于悲观锁，在对数据库进行处理的时候，乐观锁并不会使用数据库提供的锁机制，一般用记录数据版本的方式实现乐观锁。

数据版本：为数据增加的一个版本标识。当读取数据时，将版本标识的值一同读出，数据每更新一次，便对版本标识进行一次更新。当事务提交更新的时候，需要判断数据库表对应记录的当前版本信息与第一次取出来的版本标识是否一致，如果数据库表当前版本号与第一次取出来的版本标识值相等，则予以更新，否则认为是过期数据。

实现数据版本有两种方式，第一种是使用版本号，第二种是使用时间戳。

使用版本号实现乐观锁

使用版本号时，可以在数据初始化时指定一个版本号，每次对数据的更新操作都对版本号执行+1操作。并判断当前版本号是不是该数据的最新的版本号。

1.查询出商品信息
select (status,status,version) from t_goods where id=#{id}
2.根据商品信息生成订单
3.修改商品status为2
update t_goods 
set status=2,version=version+1
where id=#{id} and version=#{version};

• 1
• 2
• 3
• 4
• 5
• 6
• 7

• 1
• 2
• 3
• 4
• 5
• 6
• 7

乐观并发控制假设事务之间的数据竞争(data race)概率比较小，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

13.左 右 连接 全连接 内连接

可参考