SQL

内连接、外连接、自然连接

自然连接: 不需要连接条件, 要求连接的两表属性相同, 并在结果集中消除重复的属性列
内连接: 连接的两表相同, 手动添加连接条件
左外连接: 在自然连接的基础之上, 保留左表中要舍弃的数据在结果集中
右外连接: 在自然连接的基础之上, 保留右表中要舍弃的数据在结果集中
全外连接: 在自然连接的基础之上, 保留左表与右表中要舍弃的数据在结果集中

局部性原理与磁盘预读

由于存储介质的特性, 磁盘本身存取就比主存慢很多, 再加上机械运动耗费, 磁盘的存取速度往往是主存的几百分分之一, 因此为了提高效率, 要尽量减少磁盘I/O. 为了达到这个目的, 磁盘往往不是严格按需读取, 而是每次都会预读, 即使只需要一个字节, 磁盘也会从这个位置开始, 顺序向后读取一定长度的数据放入内存. 这样做的理论依据是计算机科学中著名的局部性原理: 当一个数据被用到时, 其附近的数据也通常会马上被使用

程序运行期间所需要的数据通常比较集中。
由于磁盘顺序读取的效率很高(不需要寻道时间, 只需很少的旋转时间），因此对于1具有局部性的程序来说, 预读可以提高I/O效率。
预读的长度一般为页(page)的整倍数. 页是计算机管理存储器的逻辑块, 硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块, 每个存储块称为一页(在许多操作系统中, 页得大小通常为4k），主存和磁盘以页为单位交换数据. 当程序要读取的数据不在主存中时, 会触发一个缺页异常, 此时系统会向磁盘发出读盘信号, 磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中, 然后异常返回, 程序继续运行。

数据库连接池的作用

对于一个简单的数据库应用, 由于对于数据库的访问不是很频繁. 这时可以简单地在需要访问数据库时, 就新创建一个连接, 用完后就关闭它, 这样做也不会带来什么明显的性能上的开销. 但是对于一个复杂的数据库应用, 情况就完全不同了. 频繁的建立、关闭连接, 会极大的减低系统的性能, 因为对于连接的使用成了系统性能的瓶颈。

数据库连接池的基本原理是在内部对象池中维护一定数量的数据库连接, 并对外暴露数据库连接获取和返回方法. 如: 外部使用者可通过getConnection方法获取连接, 使用完毕后再通过releaseConnection方法将连接返回, 注意此时连接并没有关闭, 而是由连接池管理器回收, 并为下一次使用做好准备。

数据库连接池技术带来的优势：

1. 资源重用
   由于数据库连接得到重用, 避免了频繁创建、释放连接引起的大量性能开销. 在减少系统消耗的基础上, 另一方面也增进了系统运行环境的平稳性(减少内存碎片以及数据库临时进程/线程的数量）。
2. 更快的系统响应速度
   数据库连接池在初始化过程中, 往往已经创建了若干数据库连接置于池中备用. 此时连接的初始化工作均已完成. 对于业务请求处理而言, 直接利用现有可用连接, 避免了数据库连接初始化和释放过程的时间开销, 从而缩减了系统整体响应时间。
3. 新的资源分配手段
   对于多应用共享同一数据库的系统而言, 可在应用层通过数据库连接的配置, 实现数据库连接池技术, 几年前也许还是个新鲜话题, 对于目前的业务系统而言, 如果设计中还没有考虑到连接池的应用, 那么…….快在设计文档中加上这部分的内容吧. 某一应用最大可用数据库连接数的限制, 避免某一应用独占所有数据库资源。
4. 统一的连接管理, 避免数据库连接泄漏
   在较为完备的数据库连接池实现中, 可根据预先的连接占用超时设定, 强制收回被占用连接. 从而避免了常规数据库连接操作中可能出现的资源泄漏. 一个最小化的数据库连接池实现

Mysql的表空间方式, 各自特点

InnoDB存储引擎提供二种数据库表的存储方式: (1)共享表空间; (2)独占表空间

• 共享表空间
  整个数据库的表数据和索引存储在一个文件中, 默认放在/usr/local/mysqldata中, 每一个表有一个.frm表描述文件, 放在mysqldata下的具体数据库目录；

优点：
1、表空间可以自动分成多个文件存放到各个磁盘上, 一个表的数据可以分布在不同的文件上. 表数据的大小限制不再是文件系统单个文件大小的限制, 而是InnoDB自身的限制. 从Innodb官方文档可知, 其表空间的最大限制为64TB, 也就是说, Innodb的单表大小限制也在64TB左右。

缺点: (1) 单个文件过大, 但可切割; (2)大量删除操作后表空间会有大量空隙

• 独占表空间
  独占表空间方式, 在mysqldata/yourdatabase下, 每一个表都有一个.frm表描述文件, 还有一个.ibd, 每个表的数据和索引单独存放在以表命名的ibd文件中；

优点：
1、每个表都有自已独立的表空间物理文件, 数据存储清晰, 灾难恢复相对容易, 不会影响其他表。
2、可以实现单表在不同的数据库中移动。
3、空间更容易回收。
4、一般情况, 使用独占表空间的性能会更高一点。

对于使用独立表空间的表, 不管怎么删除, 表空间的碎片不会太严重的影响性能, 而且还有机会处理, 处理方式如下：
Drop table操作时自动回收表空间；
对于innodb-plugin的Innodb使用turncate table收缩表空间

缺点: 单个表空间大学限制为linux文件系统的大小

关系型数据库与非关系型数据库

关系数据库是数据项之间具有预定义关系的数据项的集合. 这些项被组织为一组具有列和行的表. 表用于保存有关要在数据库中表示的对象的信息. 表中的每列都保存着特定类型的数据, 字段存储着属性的实际值. 表中的行表示一个对象或实体的相关值的集合. 表中的每一行可标有一个称为主键的唯一标识符, 并且可使用外键在多个表中的行之间建立关联. 可以通过许多不同的方式访问此数据, 而无需重新组织数据库表本身。

完整性约束

• 域的完整性: 是指给定列的取值范围(即输入的有效性）
  强制域有效性的方法有: 限制类型(通过数据类型）、格式(通过 CHECK 约束和规则)或可能值的范围(通过FOREIGN KEY约束、CHECK约束、DEFAULT定义、NOT NULL 定义和规则）。

• 实体完整性: 是指使用主键来唯一地标识一个实体
  在关系数据库中, 一条记录代表一个实体. 而实体是可以相互区分、识别的, 也即它们应具有某种唯一性标识(该标识不能取相同的值, 也不能为空）。实体完整性强制表的标识符列或主键的完整性(通过索引、UNIQUE约束、PRIMARY KEY约束或 IDENTITY 属性）。

• 引用完整性: 定义外键与主键之间的引用规则
  引用完整性基于外键与主键之间或外键与唯一键之间的关系(通过 FOREIGN KEY 和 CHECK 约束）。引用完整性确保键值在所有表中一致. 这样的一致性要求不能引用不存在的值, 如果键值更改了, 那么在整个数据库中, 对该键值的所有引用要进行一致的更改。

• 用户定义完整性
  用户定义完整性让用户可以定义不属于其它任何完整性分类的特定业务规则. 所有的完整性类型都支持用户定义完整性(CREATE TABLE 中的所有列级和表级约束、存储过程和触发器）。

MySQL中的约束: (1)主键约束; (2)唯一性约束; (3)外键约束; (4)非空约束; (5)检查约束

MySQL基础笔记(二） 完整性约束

三范式

设计关系数据库时, 遵从不同的规范要求, 设计出合理的关系型数据库, 这些不同的规范要求被称为不同的范式, 各种范式呈递次规范, 越高的范式数据库冗余越小

第一范式(1NF)
强调的是列的原子性, 即列不能够再分成其他几列。 
考虑这样一个表：【联系人】（姓名, 性别, 电话） 
如果在实际场景中, 一个联系人有家庭电话和公司电话, 那么这种表结构设计就没有达到 1NF. 要符合 1NF 我们只需把列(电话)拆分, 即：【联系人】（姓名, 性别, 家庭电话, 公司电话）。

第二范式(2NF）
首先是 1NF, 另外包含两部分内容, 一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键, 而不能只依赖于主键的一部分。 所谓完全依赖是指不能存在仅依赖主关键字一部分的属性, 如果存在, 那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体, 新实体与原实体之间是一对多的关系. 为实现区分通常需要为表加上一个列, 以存储各个实例的唯一标识. 简而言之, 第二范式就是在第一范式的基础上属性完全依赖于主键。

考虑一个订单明细表：【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）

3NF是第二范式2NF的一个子集, 即满足第三范式3NF必须满足2NF. 首先是2NF, 另外非主键列必须直接依赖于主键, 不能存在传递依赖. 即不能存在: 非主键列A依赖于非主键列B, 非主键列B依赖于主键的情况

考虑一个订单表【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。

数据库(第一范式, 第二范式, 第三范式）

视图

视图(子查询）：是从一个或多个表导出的虚拟的表, 其内容由查询定义. 具有普通表的结构, 但是不实现数据存储。

对视图的修改：
单表视图一般用于查询和修改, 会改变基本表的数据，
多表视图一般用于查询, 不会改变基本表的数据。

--创建视图--
create view v_student as select * from student;
--从视图中检索数据--
select * from v_student;
--删除视图--
drop view v_student;

作用：

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

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

3. 逻辑上的独立性, 屏蔽了真实表的结构带来的影响。
   视图可以使应用程序和数据库表在一定程度上独立. 如果没有视图, 应用一定是建立在表上的. 有了视图之后, 程序可以建立在视图之上, 从而程序与数据库表被视图分割开来。

缺点:

1. 性能差
   数据库必须把视图查询转化成对基本表的查询, 如果这个视图是由一个复杂的多表查询所定义, 那么, 即使是视图的一个简单查询, 数据库也要把它变成一个复杂的结合体, 需要花费一定的时间。
2. 修改限制
   当用户试图修改视图的某些信息时, 数据库必须把它转化为对基本表的某些信息的修改, 对于简单的视图来说, 这是很方便的, 但是, 对于比较复杂的试图, 可能是不可修改的。

为什么B+树比B树更适合数据库索引

• B+树的磁盘读写代价更低: B+树的内部节点并没有指向关键字具体信息的指针, 因此内部节点相对B树更小. 如果把同一内部节点的关键字存放在同一块磁盘中, 那么盘块所能容纳的关键字数量也越多, 一次性读入内存的需要查找的关键字也就越多, 相对IO读写次数就降低了

• B+树的查询效率更加稳定: 由于非终结节点并不是最终指向文件内容的节点, 而只是叶子节点中关键字的索引. 所以关键字的查找必须走一条从根结点到叶子节点的路. 所有关键字查询的路径长度相同, 导致每一个数据查询效率相当

• B+树的数据都存储在叶子节点中, 分支节点均为索引, 方便扫库, 只需要扫一遍叶子节点即可, 但是B数因为其分支节点同样存储者数据, 要找到具体的数据, 需要进行一次中序遍历按序来扫, 所以B+树更加适合在区间查询的情况, 通常B+树用于数据库索引

• B树在提高IO性能的同时并没有解决元素遍历的效率低下问题, 这是为了解决这个问题, B+数应运而生. B+树只需要去遍历叶子节点就可以实现遍历整颗树的遍历. 而在数据库中基于范围查询是非常频繁的, 而B数不支持这样的操作或者说效率太低

B+ Tree索引和Hash索引区别？

哈希索引适合等值查询，但是无法进行范围查询
哈希索引没办法利用索引完成排序
哈希索引不支持多列联合索引的最左匹配规则
如果有大量重复键值的情况下，哈希索引的效率会很低，因为存在哈希碰撞问题

非主键索引需要回表查询多次

非簇族索引在索引覆盖的情况下可以不需要回表

查询优化器？

一条SQL语句的查询，可以有不同的执行方案，至于最终选择哪种方案，需要通过优化器进行选择，选择执行成本最低的方案。
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用的方案，对比之后找出成本最低的方案。
这个成本最低的方案就是所谓的执行计划。优化过程大致如下：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个

索引

Mysql 索引精讲

假设我们有一张数据表Emplyee, 该表有三列: Employee_Name,Employee_Age,Employee_Address. 表中有几万条记录. 现在, 我们要执行下面这条查询语句, 查找出所有名字叫“Jesus”的员工的详细信息：SELECT * FROM Employee WHERE Employee_Name = 'Jesus'

没有索引时: 执行上面这条查询后, 数据库系统会逐行的遍历整张表, 对于每一行都要检查其Employee_Name字段是否等于“Jesus”。因为我们要查找所有名字为“Jesus”的员工, 所以当我们发现了一条名字是“Jesus”的记录后, 并不能停止继续查找, 因为可能有其他员工也叫“Jesus”。这就意味着, 对于表中的几万条记录, 数据库每一条都要检查. 这就是所谓的“全表扫描”（ full table scan）。

使用索引时: 遍历索引, 索引后会有指向所在行的指针, 这样就不用遍历整张表了

索引: 一种数据结构, 保存表中的某一列或几列的值. 查询时, 通过查询数据结构中的数据项(会有指向行的指针)进而找到所在的行. 避免全表扫描节约时间

常见索引种类(实现层)

数据库有以下特点:

1. 存储的数据是非常非常多的
2. 并且还不断的动态变化
   所以排除静态查找(顺序查找, 二分法查找)

B-tree索引:
优点: (1)支持范围查找; (2)支持排序, 分组; (3)支持前缀匹配
缺点: 占用空间相对较大
之所以支持范围查找就是因为B+数的叶子节点是链表结构

Hash索引:
优点: (1)查找速度快
缺点: (1)不支持范围查找; (2)需要处理hash冲突; (3)必须全值精确匹配

hash表不如B-树灵活, 所以数据库系统通常会选择B-树作为索引的默认数据结构而不是hash表

常见索引种类(应用层)

• 主键索引: 唯一的, 通常以表的ID设置为主键索引, 一个表只能有一个主键索引, 这是他跟唯一索引的区别
  create table User(u_name varchar(50) not null, u_uid int(4) not null, u_gender int(2) not null, primary key(u_uid));

• 唯一索引: 主要用于业务上的唯一约束, 他跟主键索引的区别是, 一个表可以有多个唯一索引
  create table User(u_name varchar(50) not null, u_uid int(4) not null, u_gender int(2) not null, unique key(u_name));

• 单列索引(普通索引): 以某一个字段为索引
  create table User(u_name varchar(50) not null, u_uid int(4) not null, u_gender int(2) not null, key(u_name));

• 联合索引: 两个或两个以上字段联合组成一个索引. 使用时需要注意满足最左匹配原则！
  create table User(u_name varchar(50) not null, u_uid int(4) not null, u_gender int(2) not null, key(u_name,u_uid));

聚簇索引与非聚簇索引

聚簇索引: 通常由主键或者非空唯一键索引实现的, 他的索引和行数据在一起存储. 也就是在一颗B+树的叶子结点上存储的不仅是他的索引值, 还有对应的某一行的一整行数据.聚簇索引不是一种索引, 而是一种数据存储组织方式

非聚簇索引: 又称二级索引, 就是我们常用的普通索引, 与聚簇索引不同的是非聚簇索引在索引树叶子节点上除了索引值之外只存了主键值

select * from test where col2=93;
上面这条语句会经历两次从索引树查找过程

1. 第一步从非聚簇索引的索引树上找到包含col2=93的叶子节点，并定位到行的主键 3
2. 第二步 根据主键 3 在从聚簇索引定位包含 主键=3的叶子节点并返回全部行数据。

InnoDb存储引擎的,MyISAM是不支持聚簇索引的, 因为他的数据文件和索引文件是相互独立存储的 MyISAM存储引擎的索引树的叶子节点不会寸主键值, 而存一个指向对应行的地址或者说是指针, 然后再从表数据文件里去找

InnoDb 将通过主键来实现聚簇索引, 如果没有主键则

1. 主键索引既存储索引值, 又在叶子中存储行的数据
2. 如果没有主键, 则会Unique key做主键
3. 如果没有unique, 会选选一个唯一非空索引来实现. 如果没有唯一非空索引则会隐式生成一个主键

覆盖索引

覆盖索引就是指索引包含了所有需要查询的字段

覆盖索引好处

1. 避免了对主键索引(聚簇)的二次查询
2. 由于不需要回表查询(从表数据文件)所以大大提升了Mysql缓存的负载
   总之大大提升了读取数据的性能

最佳索引使用策略

• 独立的列
  独立的列不是指单列索引, 而是指索引列不能是表达式的一部分或者是函数的一部分。

select * FROM test where col1 + 1 =100;     // 不能是表达式一部分
select * FROM test where ABS(col1) =100;    // 不能是函数一部分

• 最左匹配原则
  假如有个联合索引 key (col1,col2)。那么以下查询是索引无效的

select * from test where col2 = 3;
select * from test where col1 like '%3';

对于最左匹配原则, 大家想一下B+树的叶子节点的关联就差不多知道为啥需要最左匹配原则了, 因为B+的叶子结点, 从左到右以链表的形式关联的, 索引我们查询的时候要么范围查询, 要么有明确的左边一个开始的索引值, 不能跳过或者不明确如 like '%XYZ'这种查询。

• 索引值不能是null值
  单列索引有null值会导致索引无效
  多列索引只要有个列有null值会导致索引无效

• 使用聚簇索引和覆盖索引大大提升读取性能
  因为聚簇索引和覆盖索引的索引树上就有了需要的字段, 所以不需要回表文件查询, 所以提升了查询速度

• 使用短索引
  如果很长的字符串进行查询, 只需匹配一个前缀长度, 这样能够节省大量索引空间

有索引，但用不上的情况：
在数据库表中，使用索引可以提高查询速度。但是索引并不是对所有的查询操作都会生效的。比如以下几种情况，将导致索引失效：
1、如果查询条件用or，必须or条件中的每个列都加上索引，否则无效。
2、对于复合索引（又叫多列索引或者联合索引），如果查询的列不使用复合索引的第一部分，则不使用索引。例如，tb表的复合索引为（key1，key2，key3），则查询select * from tb where key2=1 and key2>5将不会使用索引。
3、如果like是以%开头的，则该列上的索引不会被使用。例如：select * from tb where key1 like "%a"; 即使key1列上建立了索引，该查询也不会使用索引。
4、存在索引列的数据类型隐形转换，则不使用索引。例如：列key1的数据类型为字符串，select * from tb where key1=1; 将不会使用索引。要想使用索引，必须将字符串用引号印起来。
5、where字句里对索引列有数学运算或者使用函数，则不使用索引。

不推荐使用索引的情况：
1、数据唯一性差（一个字段的取值只有几种情况）的字段不要使用索引：
例如性别，只有两种可能数据。意味着索引的二叉树级别少，这样的二叉树查询无异于全表扫描。
2、频繁更新的字段不要使用索引：
例如login_count登陆次数，频繁变化导致索引也频繁变化，增大数据库工作量，降低效率。

添加索引原则

1. 在查询中很少使用或者参考的列不应该创建索引。
   这是因为, 既然这些列很少使用到, 因此有索引或者无索引, 并不能提高查询速度. 相反, 由于增加了索引, 反而降低了系统的维护速度和增大了空间需求。
2. 只有很少数据值的列也不应该增加索引。
   这是因为, 由于这些列的取值很少, 例如人事表的性别列, 在查询的结果中, 结果集的数据行占了表中数据行的很大比例, 即需要在表中搜索的数据行的比例很大. 增加索引, 并不能明显加快检索速度。
3. 定义为text、image和bit数据类型的列不应该增加索引。
   这是因为, 这些列的数据量要么相当大, 要么取值很少。
4. 当修改性能远远大于检索性能时, 不应该创建索引。
   这是因为, 修改性能和检索性能是互相矛盾的. 当增加索引时, 会提高检索性能, 但是会降低修改性能. 当减少索引时, 会提高修改性能, 降低检索性能. 因此, 当修改性能远远大于检索性能时, 不应该创建索引。

索引优缺点

优点：
通过创建唯一性索引, 可以保证数据库表中每一行数据的唯一性。
加快数据的检索速度，这也是创建索引的最主要的原因。
加快多表连接，特别是在实现数据的参考完整性方面特别有意义。
在使用分组和排序子句进行数据检索时, 同样可以显著减少查询中分组和排序的时间。
通过使用索引, 可以在查询的过程中, 使用优化隐藏器, 提高系统的性能。

缺点：
维护索引的额外时间开销, 创建索引和维护索引要耗费时间, 这种时间随着数据量的增加而增加. 当对表中的数据进行增加、删除和修改的时候, 索引也要动态的维护, 这样就降低了数据的维护速度。
额外空间开销, 索引需要占物理空间, 除了数据表占数据空间之外, 每一个索引还要占一定的物理空间, 如果要建立聚簇索引, 那么需要的空间就会更大。

索引越多越快？

并不是, 索引实实在在的一种数据结构(B+树/hash桶)要占内存、维护它要系统开销, 一般的插入删除都要进行结构的调整, 这要消耗时间, 所以索引太多反而拖慢查找时间.
有时候, 见数据量不多时, 建立索引还不如全表查询. 索引加快了检索的速度, 但是插入删除修改都需要DBMS动态更新内部索引结构, 要耗费开销

为什么索引结构默认使用B-Tree, 而不是hash, 二叉树, 红黑树

hash: 虽然可以快速定位, 但是没有顺序, IO复杂度高
二叉树: 树的高度不均匀, 不能自平衡, 查找效率跟数据有关(树的高度)并且IO代价高
红黑树: 树的高度随着数据量增加而增加, IO代价高。

为什么官方建议使用自增长主键作为索引

结合B+Tree的特点, 自增主键是连续的, 在插入过程中尽量减少页分裂, 即使要进行页分裂, 也只会分裂很少一部分. 并且能减少数据的移动, 每次插入都是插入到最后. 总之就是减少分裂和移动的频率。

锁

数据库的锁机制及原理
数据库的锁机制及原理
MySQL表锁和行锁机制

乐观锁

乐观锁不是数据库自带的, 需要用户自己实现的一种锁机制, 假设认为数据一般情况下不会造成冲突, 所以在数据进行提交更新的时候, 才会正式对数据的冲突与否进行检测, 如果发现冲突了, 则让返回用户错误的信息, 让用户决定如何去做。

乐观锁的实现方式一般来说有以下2种方式：

1. 使用数据版本(Version)记录机制实现, 这是乐观锁最常用的一种实现方式. 何谓数据版本？即为数据增加一个版本标识, 一般是通过为数据库表增加一个数字类型的 “version” 字段来实现. 当读取数据时, 将version字段的值一同读出, 数据每更新一次, 对此version值加一. 当我们提交更新的时候, 判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对, 如果数据库表当前版本号与第一次取出来的version值相等, 则予以更新, 否则认为是过期数据。
2. 同第一种差不多, 同样是在需要乐观锁控制的table中增加一个字段, 名称无所谓, 字段类型使用时间戳(timestamp）, 和上面的version类似, 也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比, 如果一致则OK, 否则就是版本冲突。

乐观锁详解

悲观锁

悲观锁就是在操作数据时, 认为此操作会出现数据冲突, 所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作

一般就是我们通常说的数据库锁机制, 悲观锁主要有: (1)表锁; (2)行锁; (3)页锁

MyISAM中只用到表锁, 不会有死锁的问题, 锁的开销也很小, 但是相应的并发能力很差。
InnoDB实现了行级锁和表锁，锁的粒度变小了, 并发能力变强, 但是相应的锁的开销变大, 很有可能出现死锁. 同时inodb需要协调这两种锁, 算法也变得复杂。
InnoDB行锁是通过给索引上的索引项加锁来实现的, 只有通过索引条件检索数据, InnoDB才使用行级锁, 否则, InnoDB将使用表锁。

表锁和行锁都分为共享锁和排他锁(独占锁），而更新锁是为了解决行锁升级(共享锁升级为独占锁)的死锁问题。

意向锁

注意: 申请意向锁的动作是数据库完成的，就是说, 事务A申请一行的行锁的时候, 数据库会自动先开始申请表的意向锁, 不需要我们程序员使用代码来申请。

1. 在mysql中有表锁, 读锁锁表, 会阻塞其他事务修改表数据. 写锁锁表, 会阻塞其他事务读和写
2. Innodb引擎又支持行锁, 行锁分为共享锁, 一个事务对一行的共享只读锁. 排它锁, 一个事务对一行的排他读写锁。
3. 这两中类型的锁共存的问题考虑这个例子: 事务A锁住了表中的一行, 让这一行只能读, 不能写. 之后, 事务B申请整个表的写锁. 如果事务B申请成功, 那么理论上它就能修改表中的任意一行, 这与A持有的行锁是冲突的. 数据库需要避免这种冲突, 就是说要让B的申请被阻塞, 直到A释放了行锁。

数据库要怎么判断这个冲突呢？
step1: 判断表是否已被其他事务用表锁锁表
step2: 判断表中的每一行是否已被行锁锁住

注意step2, 这样的判断方法效率实在不高, 因为需要遍历整个表。于是就有了意向锁. 在意向锁存在的情况下, 事务A必须先申请表的意向共享锁, 成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成
step1: 不变
step2: 发现表上有意向共享锁, 说明表中有些行被共享行锁锁住了, 因此, 事务B申请表的写锁会被阻塞。

行锁

InnoDB的行锁是针对索引加的锁, 不是针对记录加的锁. 并且该索引不能失效, 否则都会从行锁升级为表锁

CREATE TABLE `city` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)                        # 由于对于city表中,id字段为主键, 就也相当于索引. 执行加锁时, 会将id这个索引为1的记录加上锁, 那么这个锁就是行锁。
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

共享锁

1. 加锁与解锁: 当一个事务执行select语句时, 数据库系统会为这个事务分配一把共享锁, 来锁定被查询的数据. 在默认情况下, 数据被读取后, 数据库系统立即解除共享锁。
   例如: 当一个事务执行查询SELECT * FROM accounts语句时, 数据库系统首先锁定第一行, 读取之后, 解除对第一行的锁定, 然后锁定第二行. 这样, 在一个事务读操作过程中, 允许其他事务同时更新accounts表中未锁定的行。
2. 兼容性: 如果数据资源上放置了共享锁，还能再放置共享锁和更新锁
3. 并发性能: 具有良好的并发性能, 当数据被放置共享锁后, 还可以再放置共享锁或更新锁. 所以并发性能很好。

begin;
SELECT * from city where id = "1"  lock in share mode;  # lock in shared mode添加锁

排他锁

1. 加锁与解锁: 当一个事务执行insert、update或delete语句时, 数据库系统会自动对SQL语句操纵的数据资源使用独占锁. 如果该数据资源已经有其他锁(任何锁)存在时, 就无法对其再放置独占锁了。
2. 兼容性: 独占锁不能和其他锁兼容, 如果数据资源上已经加了独占锁, 就不能再放置其他的锁了. 同样, 如果数据资源上已经放置了其他锁, 那么也就不能再放置独占锁了。
3. 并发性能: 最差. 只允许一个事务访问锁定的数据, 如果其他事务也需要访问该数据, 就必须等待。

对于update,insert,delete语句会自动加排它锁, 在执行语句后添加for update

更新锁
更新锁在的初始化阶段用来锁定可能要被修改的资源, 这可以避免使用共享锁造成的死锁现象。
例如update语句UPDATE accounts SET balance=900 WHERE id=1
更新操作需要分两步：读取accounts表中id为1的记录 –> 执行更新操作。如果在第一步使用共享锁, 再第二步把锁升级为独占锁, 就可能出现死锁现象
例如: 两个事务都获取了同一数据资源的共享锁, 然后都要把锁升级为独占锁, 但需要等待另一个事务解除共享锁才能升级为独占锁, 这就造成了死锁。

更新锁有如下特征：

• 加锁与解锁: 当一个事务执行update语句时, 数据库系统会先为事务分配一把更新锁. 当读取数据完毕, 执行更新操作时, 会把更新锁升级为独占锁。
• 兼容性: 更新锁与共享锁是兼容的, 也就是说, 一个资源可以同时放置更新锁和共享锁, 但是最多放置一把更新锁。这样, 当多个事务更新相同的数据时, 只有一个事务能获得更新锁, 然后再把更新锁升级为独占锁, 其他事务必须等到前一个事务结束后, 才能获取得更新锁, 这就避免了死锁。
• 并发性能: 允许多个事务同时读锁定的资源, 但不允许其他事务修改它。

总结

1. InnoDB 支持表锁和行锁, 使用索引作为检索条件修改数据时采用行锁, 否则采用表锁
2. InnoDB 自动给修改操作加锁, 给查询操作不自动加锁
3. 行锁可能因为未使用索引而升级为表锁, 所以除了检查索引是否创建的同时, 也需要通过explain执行计划查询索引是否被实际使用
4. 行锁相对于表锁来说, 优势在于高并发场景下表现更突出, 毕竟锁的粒度小
5. 当表的大部分数据需要被修改, 或者是多表复杂关联查询时, 建议使用表锁优于行锁
6. 为了保证数据的一致完整性, 任何一个数据库都存在锁定机制. 锁定机制的优劣直接影响到一个数据库的并发处理能力和性能

数据库事务以及四个特性

事务(Transaction)是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元. 事务是DBMS中最基础的单位, 事务不可分割。
事务具有4个基本特征, 分别是: 原子性(Atomicity）、一致性(Consistency）、隔离性(Isolation）、持久性(Duration），简称ACID。

1)原子性(Atomicity）
原子性是指事务包含的所有操作要么全部成功, 要么全部失败回滚. 因此事务的操作如果成功就必须要完全应用到数据库, 如果操作失败则不能对数据库有任何影响。

2)一致性(Consistency）
一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态, 也就是说一个事务执行之前和执行之后都必须处于一致性状态。
拿转账来说, 假设用户A和用户B两者的钱加起来一共是5000, 那么不管A和B之间如何转账, 转几次账, 事务结束后两个用户的钱相加起来应该还得是5000, 这就是事务的一致性。

3)隔离性(Isolation）
隔离性是当多个用户并发访问数据库时, 比如操作同一张表时, 数据库为每一个用户开启的事务, 不能被其他事务的操作所干扰, 多个并发事务之间要相互隔离。
即要达到这么一种效果: 对于任意两个并发的事务T1和T2, 在事务T1看来, T2要么在T1开始之前就已经结束, 要么在T1结束之后才开始, 这样每个事务都感觉不到有其他事务在并发地执行。
多个事务并发访问时, 事务之间是隔离的, 一个事务不应该影响其它事务运行效果. 这指的是在并发环境中, 当不同的事务同时操纵相同的数据时, 每个事务都有各自的完整数据空间. 由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。

4)持久性(Durability）
持久性是指一个事务一旦被提交了, 那么对数据库中的数据的改变就是永久性的, 即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。
例如我们在使用JDBC操作数据库时, 在提交事务方法后, 提示用户事务操作完成, 当我们程序执行完成直到看到提示后, 就可以认定事务以及正确提交, 即使这时候数据库出现了问题, 也必须要将我们的事务完全执行完成, 否则就会造成我们看到提示事务处理完毕, 但是数据库因为故障而没有执行事务的重大错误。

事务的三个基本操作

1. 开始
2. 回滚. 事务的操作都是在缓存中进行的, 并没有更改到数据库中数据文件, 任何一个地方出错了都可以回滚.
3. 提交. 一旦提交数据的改变就是永久的, 事务的日志中的数据更新到数据库文件中, 对数据起永久性改变

事务是如何通过日志来实现的, 说得越深入越好

事务四个特性最终目的:

1. 可靠性: 通过undo log与redo log来实现
2. 并发性: 通过mvcc与锁机制实现隔离级别

回滚日志必须先于数据同步到磁盘中

redo log叫做重做日志, 分为重做日志缓存与重做日志文件, 前者存储在内存中, 后者存储在磁盘中. 数据的操作先同步到缓冲池中, 同时重做日志缓冲生成相应操作后的记录. 为提升性能, 后台以轮询的方式将缓存池中的数据同步到磁盘中, 提交事务时会把重做日志缓存同步到磁盘中. 那么就会有还未同步时宕机或断点, 可能会导致丢失部分已提交的事务, 所以redo log(因为已提交, 所以重做磁盘日志会有记录)来记录已成功提交事务的修改信息, 并且会把redo log持久化到磁盘, 系统重启之后在读取redo log恢复最新数据

redo log也需要存储, 也涉及磁盘IO为啥还用它？

1. redo log 的存储是顺序存储, 而缓存同步是随机操作
2. 缓存同步是以数据页为单位的, 每次传输的数据大小大于redo log

redo log是用来恢复数据的用于保障已提交事务的持久化特性

undo log叫做回滚日志, 用于记录数据被修改前的信息. 他正好跟前面所说的重做日志所记录的相反, 重做日志记录数据被修改后的信息. undo log主要记录的是数据的逻辑变化, 为了在发生错误时回滚之前的操作, 需要将之前的操作都记录下来, 然后在发生错误时才可以回滚, 所以每次写入数据或者修改文件之前会把修改之前版本的数据信息记录到undo log中

undo log是用来回滚数据的用于保障未提交事务的原子性

1. 每条数据变更(insert/update/delete)操作都伴随一条undo log的生成,并且回滚日志必须先于数据持久化到磁盘上
2. 所谓的回滚就是根据回滚日志做逆向操作, 比如delete的逆向操作为insert, insert的逆向操作为delete, update的逆向为update等。

前面讲的重做日志, 回滚日志以及锁技术就是实现事务的基础。

• 原子性: 使用 undo log ，从而达到回滚
• 持久性: 使用 redo log, 从而达到故障后恢复
• 隔离性: 使用锁以及MVCC,运用的优化思想有读写分离, 读读并行, 读写并行
• 一致性: 通过回滚, 以及恢复, 和在并发环境下的隔离做到一致性。

Mysql事务实现原理

mysql锁技术以及MVCC基础

当有多个请求来读取表中的数据时可以不采取任何操作, 但是多个请求里有读请求, 又有修改请求时必须有一种措施来进行并发控制. 不然很有可能会造成不一致. 可以读写锁解决上述问题很简单, 只需用两种锁的组合来对读写请求进行控制即可, 这两种锁被称为：

共享锁, 又叫做"读锁"
读锁是可以共享的, 或者说多个读请求可以共享一把锁读数据, 不会造成阻塞。

排他锁, 又叫做"写锁"
写锁会排斥其他所有获取锁的请求, 一直阻塞, 直到写入完成释放锁。

通过读写锁可以做到读读并行, 但是不能做到读写、写读、写写并行, 进而引入MVCC(MultiVersion Concurrency Control, 多版本并发控制), 主要实现思想是通过数据多版本来做到读写分离. 从而实现不加锁做到读写并行

隔离性的实现可以看出这是一场数据的可靠性与性能之间的权衡。

• 可靠性性高的, 并发性能低(比如 Serializable)
• 可靠性低的, 并发性能高(比如 Read Uncommited)

READ UNCOMMITTED --> 只有写锁, 没有读锁
事务中的修改即使还没提交, 对其他事务是可见的. 事务可以读取未提交的数据, 造成脏读。
因为读不会加任何锁, 所以写操作在读的过程中修改数据, 所以会造成脏读. 好处是可以提升并发处理性能，读不加锁写加锁, 因此能做到读写并行。换句话说, 读的操作不能排斥写请求。
优点: 读写并行, 性能高
缺点: 造成脏读

READ COMMITTED --> 写锁+MVCC
一个事务的修改在他提交之前的所有修改, 对其他事务都是不可见的. 其他事务能读到已提交的修改变化. 在很多场景下这种逻辑是可以接受的。
InnoDB在READ COMMITTED, 写操作使用排它锁, 读取操作不加锁而是使用了MVCC机制(即读时读取的是数据的不同副本)。或者换句话说他采用了读写分离机制.但是该级别会产生不可重读以及幻读问题。

REPEATABLE READ(Mysql默认隔离级别) --> 加读写锁或使用MVCC
在一个事务内的多次读取的结果是一样的. 这种级别下可以避免, 脏读, 不可重复读等查询问题. mysql有两种机制可以达到这种隔离级别的效果, 分别是采用读写锁以及MVCC

1. 采用读写锁实现: 为什么能可重复读？只要没释放读锁, 再次读的时候还是可以读到第一次读的数据。
   　　优点: 实现起来简单
   　　缺点: 无法做到读写并行
2. 采用MVCC实现: 为什么能可重复读？因为多次读取只生成一个版本, 读到的自然是相同数据。
   　　优点: 读写并行
   　　缺点: 实现的复杂度高
   　　但是在该隔离级别下仍会存在幻读的问题, 关于幻读的解决我打算另开一篇来介绍。

SERIALIZABLE --> 加锁读
该隔离级别理解起来最简单, 实现也最简单. 在隔离级别下除了不会造成数据不一致问题, 没其他优点。

Mysql事务实现原理

实现事务采取了哪些技术以及思想:
原子性：使用 undo log ，从而达到回滚
持久性：使用 redo log，从而达到故障后恢复
隔离性：使用锁以及MVCC,运用的优化思想有读写分离，读读并行，读写并行
一致性：通过回滚，以及恢复，和在并发环境下的隔离做到一致性。

数据库事务隔离

同一时间, 只允许一个事务请求同一数据, 不同的事务之间彼此没有任何干扰. 比如A正在从一张银行卡中取钱, 在A取钱的过程结束前, B不能向这张卡转账。

MySQL 4 种隔离级别

不同的隔离级别：
Read Uncommitted(读提交）
Read Committed(读未提交）
Repeated Read(可重复读）
Serialization(可串行化）

数据库事务的隔离级别有4种，由低到高分别为Read uncommitted 、Read committed 、Repeatable read 、Serializable 。而且，在事务的并发操作中可能会出现脏读，不可重复读，幻读。下面通过事例一一阐述它们的概念与联系。

Read uncommitted

读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。

Read committed

读提交，顾名思义，就是一个事务要等另一个事务提交后才能读取数据。

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。

Repeatable read

重复读，就是在开始读取数据（事务开启）时，不再允许修改操作

事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：重复读可以解决不可重复读问题。写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

Serializable 序列化

Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

详解

mysql的四种隔离状态

事务隔离级别	脏读	不可重复读	幻读	加锁读
读未提交(read-uncommitted）	是	是	是	否
不可重复读(read-committed）	否	是	是	否
可重复读(repeatable-read）	否	否	是	否
串行化(serializable）	否	否	否	是

mysql的MVCC机制

LBCC: Lock-Based Concurrency Control, 基于锁的并发控制。
MVCC(Multi-Version Concurrency Control)是一种多版本并发控制机制, 是MySQL的InnoDB存储引擎实现隔离级别的一种具体方式, 用于实现提交读和可重复读这两种隔离级别. MVCC是通过保存数据在某个时间点的快照来实现该机制, 其在每行记录后面保存两个隐藏的列, 分别保存这个行的创建版本号和删除版本号, 然后Innodb的MVCC使用到的快照存储在Undo日志中, 该日志通过回滚指针把一个数据行所有快照连接起来。

详解

MySQL引擎和区别

1、MySQL引擎
MySQL中的数据用各种不同的技术存储在文件(或者内存)中. 这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力. 通过选择不同的技术, 你能够获得额外的速度或者功能, 从而改善你的应用的整体功能。

数据库引擎是用于存储、处理和保护数据的核心服务. 利用数据库引擎可控制访问权限并快速处理事务, 从而满足企业内大多数需要处理大量数据的应用程序的要求. 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库. 这包括创建用于存储数据的表和用于查看、管理和保护数据安全的数据库对象(如索引、视图和存储过程）。

MySQL存储引擎主要有： MyIsam、InnoDB、Memory、Blackhole、CSV、Performance_Schema、Archive、Federated、Mrg_Myisam。
但是最常用的是InnoDB和Mylsam。

2、InnoDB
InnoDB是一个事务型的存储引擎, 有行级锁定和外键约束。
Innodb引擎提供了对数据库ACID事务的支持, 并且实现了SQL标准的四种隔离级别. 该引擎还提供了行级锁和外键约束, 它的设计目标是处理大容量数据库系统, 它本身其实就是基于MySQL后台的完整数据库系统, MySQL运行时Innodb会在内存中建立缓冲池, 用于缓冲数据和索引. 但是该引擎不支持FULLTEXT类型的索引, 而且它没有保存表的行数, 当SELECT COUNT(*) FROM TABLE时需要扫描全表. 当需要使用数据库事务时, 该引擎当然是首选. 由于锁的粒度更小, 写操作不会锁定全表, 所以在并发较高时, 使用Innodb引擎会提升效率. 但是使用行级锁也不是绝对的, 如果在执行一个SQL语句时MySQL不能确定要扫描的范围, InnoDB表同样会锁全表。

适用场景：
经常更新的表, 适合处理多重并发的更新请求。
支持事务。
可以从灾难中恢复(通过bin-log日志等）。
外键约束. 只有他支持外键。
支持自动增加列属性auto_increment。

索引结构：
InnoDB也是B+Treee索引结构. Innodb的索引文件本身就是数据文件, 即B+Tree的数据域存储的就是实际的数据, 这种索引就是聚集索引. 这个索引的key就是数据表的主键, 因此InnoDB表数据文件本身就是主索引。
InnoDB的辅助索引数据域存储的也是相应记录主键的值而不是地址, 所以当以辅助索引查找时, 会先根据辅助索引找到主键, 再根据主键索引找到实际的数据. 所以Innodb不建议使用过长的主键, 否则会使辅助索引变得过大. 建议使用自增的字段作为主键, 这样B+Tree的每一个结点都会被顺序的填满, 而不会频繁的分裂调整, 会有效的提升插入数据的效率。

3、Mylsam
MyIASM是MySQL默认的引擎, 但是它没有提供对数据库事务的支持, 也不支持行级锁和外键, 因此当INSERT或UPDATE数据时即写操作需要锁定整个表, 效率便会低一些. MyIsam 存储引擎独立于操作系统, 也就是可以在windows上使用, 也可以比较简单的将数据转移到linux操作系统上去。

适用场景：
不支持事务的设计, 但是并不代表着有事务操作的项目不能用MyIsam存储引擎, 可以在service层进行根据自己的业务需求进行相应的控制。
不支持外键的表设计。
查询速度很快, 如果数据库insert和update的操作比较多的话比较适用。
整天对表进行加锁的场景。
MyISAM极度强调快速读取操作。
MyIASM中存储了表的行数, 于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描. 如果表的读操作远远多于写操作且不需要数据库事务的支持, 那么MyIASM也是很好的选择。

缺点: 就是不能在表损坏后主动恢复数据。
索引结构：
MyISAM索引结构: MyISAM索引用的B+ tree来储存数据, MyISAM索引的指针指向的是键值的地址, 地址存储的是数据. B+Tree的数据域存储的内容为实际数据的地址, 也就是说它的索引和实际的数据是分开的, 只不过是用索引指向了实际的数据, 这种索引就是所谓的非聚集索引。

3、InnoDB和Mylsam的区别：
1)事务: MyISAM类型不支持事务处理等高级处理, 而InnoDB类型支持, 提供事务支持已经外部键等高级数据库功能。
2)性能: MyISAM类型的表强调的是性能, 其执行速度比InnoDB类型更快。
3)行数保存: InnoDB 中不保存表的具体行数, 也就是说, 执行select count() fromtable时, InnoDB要扫描一遍整个表来计算有多少行, 但是MyISAM只要简单的读出保存好的行数即可. 注意的是, 当count()语句包含where条件时, 两种表的操作是一样的。
4)索引存储: 对于AUTO_INCREMENT类型的字段, InnoDB中必须包含只有该字段的索引, 但是在MyISAM表中, 可以和其他字段一起建立联合索引. MyISAM支持全文索引(FULLTEXT）、压缩索引, InnoDB不支持。
MyISAM的索引和数据是分开的, 并且索引是有压缩的, 内存使用率就对应提高了不少. 能加载更多索引, 而Innodb是索引和数据是紧密捆绑的, 没有使用压缩从而会造成Innodb比MyISAM体积庞大不小。
InnoDB存储引擎被完全与MySQL服务器整合, InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池. InnoDB存储它的表＆索引在一个表空间中, 表空间可以包含数个文件(或原始磁盘分区）。这与MyISAM表不同, 比如在MyISAM表中每个表被存在分离的文件中. InnoDB 表可以是任何尺寸, 即使在文件尺寸被限制为2GB的操作系统上。

5)服务器数据备份: InnoDB必须导出SQL来备份, LOAD TABLE FROM MASTER操作对InnoDB是不起作用的, 解决方法是首先把InnoDB表改成MyISAM表, 导入数据后再改成InnoDB表, 但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
MyISAM应对错误编码导致的数据恢复速度快. MyISAM的数据是以文件的形式存储, 所以在跨平台的数据转移中会很方便. 在备份和恢复时可单独针对某个表进行操作。
InnoDB是拷贝数据文件、备份 binlog, 或者用 mysqldump, 在数据量达到几十G的时候就相对痛苦了。
6)锁的支持: MyISAM只支持表锁. InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能. 但是InnoDB的行锁, 只是在WHERE的主键是有效的, 非主键的WHERE都会锁全表的。

锁的优化策略

悲观并发控制
一个锁定系统，可以阻止用户以影响其他用户的方式修改数据。如果用户执行的操作导致应用了某个锁，只有这个锁的所有者释放该锁，其他用户才能执行与该锁冲突的操作。这种方法之所以称为悲观并发控制，是因为它主要用于数据争用激烈的环境中，以及发生并发冲突时用锁保护数据的成本低于回滚事务的成本的环境中。

乐观并发控制
在乐观并发控制中，用户读取数据时不锁定数据。当一个用户更新数据时，系统将进行检查，查看该用户读取数据后其他用户是否又更改了该数据。如果其他用户更新了数据，将产生一个错误。一般情况下，收到错误信息的用户将回滚事务并重新开始。这种方法之所以称为乐观并发控制，是由于它主要在以下环境中使用：数据争用不大且偶尔回滚事务的成本低于读取数据时锁定数据的成本。
具体的区别与实例说明如下：

悲观并发控制：假设A和B需要在SCC（Source Code Control）上修改同一个文件，那么在A锁定这个文件并修改的过程中，B无法修改这个文件，他只能等待A解锁文件后，他才能修改。由此可见，悲观并发控制是强调控制在前，确保整个过程不会出现文件版本的冲突。这样做会使得系统效率损耗在加锁机制上，尤其是加锁机制需要用到低速的外部存储（比如FileLocking）时，然而这样做就降低了事务的并发性，尤其是事务之间本来就不存在冲突的情况下。例如在A修改数据的时候，B只能等待。

由此可见，悲观并发控制通过使用显式的加锁机制或者时间戳，对每一个事务进行增量同步校验。如果加锁机制的成本较高的话，悲观并发控制就会出现一些弊端。首先就是效率问题，尤其是使用低效率的外部存储系统实现加锁机制时，这样的问题会更加突出。其次，在不会出现冲突的事务处理（例如只读型事务）中，使用加锁机制就显得没有必要了，这样做只能增加系统负载。再次，这种方式降低了系统的并发性。

乐观并发控制：同样假设A和B需要在SCC上修改同一个文件，他们都将这个文件获取到自己的机器上，A修改完以后，就把文件上传到SCC上了，此时B也修改完了，当他也打算将文件上传时，系统会告知B，已经有人上传了，并出现一个错误。剩下的问题只能由B手动解决，例如B可以在SCC上将文件中更改的内容再次复制一遍。乐观并发控制使得系统效率损耗在事务的后期处理中，比如B必须手动的去修改他已经修改过的东西，然而这种控制方式在极少出现冲突的多事务处理中显得十分高效。

乐观并发控制将事务分为三个阶段：读取阶段、校验阶段以及写入阶段。在读取阶段，事务将数据写入本地缓冲（如上所述，A和B将文件都获取到自己的机器上），此时不会有任何校验操作；在校验阶段，系统会对所有的事务进行同步校验（比如在A或者B打算，但还没有，往SCC上写入更改后的文件时）；在写入阶段，数据将被最终提交。在完成读取阶段以后，系统会对每个事务分派一个时间戳。

悲观并发控制中一个常见的问题就是死锁。例如A在修改文件T1，B在修改文件T2，他们分别锁定了这两个文件，假设T1和T2内容相关，B在修改T2的时候发现他还需要修改T1，可是T1却被A锁定；与此同时，A在修改T1的时候也发现了他还需要修改T2，可是T2又被B锁定了，这样就出现了死锁。当然，在实际操作中，这种情况可以由A和B协商解决，但是在错综复杂的多事务处理环境中，死锁将使得问题变得非常复杂。

悲观并发控制, 乐观并发控制