mysql 存储引擎以及对比oracle 相关信息

Mysql进行创建表结构时候我们也许会忽略选择存储引擎的类型，所以在进行Mysql优化学习时候，就研究了下，对比其他人的博客文档，在这博主用的 mysql客户端工具为早年安装的 SQLyog ，进行创建表时候，如图示意：

 

 

 

此处进行说明两种主要的存储引擎,MyISAM和InnoDB:

• MyISAM基于ISAM存储引擎，并对其进行扩展，不支持事务。
  InnoDB给mysql提供了事物提交、回滚等事物安全的存储引擎。

• MyISAM采用表级锁。表级锁开销小，加锁快，锁粒度大，冲突概率高，并发度低，不会出现死锁。
  InnoDB支持表级锁与行级锁，默认为行级锁。行级锁开销大，加锁慢，锁粒度小，冲突概率低，并发度高，会出现死锁。

• MyISAM读写操作是串行的，如果读写同时操作同一张表，写进程优先获取锁，所以不适合有大量更新与读取操作的项目，适合读操作多的少量数据。
  InnoDB存储引擎完全与mysql服务整合，是为处理巨大数据量的最大性能设计。

• MyISAM不支持外键。对一个包含外键的InnoDB表转为MYISAM会失败。
  InnoDB支持外键。

• MyISAM不保存表记录数，select count(1) from xxx会全表扫描。
  InnoDB保存。但是，select count(1) from xxx where xxx仍然会全表扫描。

• MyISAM引擎创建数据库将会产生3个文件，.frm存储表结构，.MYD存储表数据，.MYI存储表索引，索引被压缩过。
  InnoDB索引与数据紧密捆绑，索引没有压缩。在索引方面的内存使用率，不如MyISAM。

如果所以在进行一般存储时候，选用InnoDB存储。

此处先不进行讲述，存储引擎相关的概念 ，先进行事实上说明，锁机制的论述，此处会引入 oracle 方面的相对应的锁机制，不做对比，只是方便大家查看 。

Mysql方面 ： 表级共享锁和 行级排他锁。

    表级锁有两种，表共享读锁和表独占写锁

     两者区别如下：

       共享读锁：允许 ，其他客户端进行读锁，不允许其他客户端进行写锁

       表独占写锁：不允许 其他客户端进行读写锁操作

下面大概讲下两种引擎的区别：

MyISAM

在执行查询前，会自动执行表的加锁、解锁操作。

读锁不会影响查询，但会限制更新本客户端与其他客户端的更新。

如下图进行表的加锁

左边进行整表查询，这时候右边查询没有影响，但进行插入时候，右边客户端就在等待状态，直到左边把锁释放掉了，然后右边执行成功了。

如果定义成 独占写锁就会形成以下情况：

所以当左侧定义了表的独占写锁，右边连查询也在等待着，直到左边释放掉锁以后才能查询出结果，释放锁中间等待了 四十几秒，这样看起来更明显。

当进行更新操作时候更是如此，只有自己本身事务控制才能进行查询和更新操作 。

InnoDB表级锁与行级锁

共享锁（读锁）：允许其他客户端进行读操作，但不允许写操作。不允许其他客户端给这几行记录上排他锁，但允许上共享锁。
上共享锁写法：lock in share mode

 

排它锁的写法

死锁：排他锁，A事务锁住1-5行，B事务锁住6-10行。A事务请求锁住6-10行，阻塞。B事务请求锁住1-5行，阻塞。共享锁是对表操作的，自如表锁不存在死锁。

注：

• 行锁必须有索引才能实现，否则就会锁全表。
• 两个事务不能同时锁同一个索引。
• insert，delete，update在事物中会默认自动加上排他锁。

此处要进行 oracle 的锁机制说明：

Oracle两种锁机制
在Oracle数据库中锁机制分为两种：独占锁与共享锁，数据库利用这两种基本的锁机制来对数据库的事务进行并发控制。
独占锁（Exclusive Lock）：即X锁，又称排它锁，是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改。
共享锁（Share Lock）：即S锁，是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取，但是不能被其它事务修改。

mysql:	定义	读	写
共享锁（读锁和独占写锁）	进行整张表的锁住操作	读锁 允许 其他客户端进行 读锁操作；独占写锁，只允许一个客户端进行读	读锁 不允许 其他客户端进行 读写锁操作；独占写锁，只允许一个客户端进行写
排他锁（行锁）	将需要操作的几行数据锁住	不允许其他客户端读	不允许其他客户端写
oracle:	定义	读锁	写锁
独占锁（Exclusive Lock）	即X锁，又称排它锁，是用来防止同时共享相同资源的锁。加了独占锁的数据库对象不能被其它事务读取和修改	不允许其他客户端读	不允许其他客户端写
共享锁（Share Lock）	即S锁，是通过对数据存取的高并行性来实现的。加了共享锁的数据库对象可以被其它事务读取，但是不能被其它事务修改。	允许 ，其他客户端进行读锁，	允许 ，其他客户端进行读写锁

————————————————

Oracle锁类型
根据保护的对象不同，Oracle数据库锁可以分为以下几大类：DML锁、DDL锁、内部锁和闩，前两种我们经常遇到。
DML锁（data locks，数据锁）：用于保护数据的完整性，能够防止同步冲突的DML和DDL操作的破坏性交互。
DDL锁（dictionary locks，字典锁）：用于保护数据库对象的结构，如表、索引等的结构定义。
内部锁和闩（internal locks and latches）：保护数据库的内部结构，如数据文件，对用户是不可见的。

1、    DML锁
用来保证并行访问数据的完整性。能够防止同步冲突的DML和DDL操作的破坏性交互。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

1）、TM锁
当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。TM锁包括了SS、SX、S、SSX 等多种模式，在数据库中用0－6来表示。不同的SQL操作产生不同类型的TM锁。

锁模式    锁描述    解释    SQL操作
0    None        
1    NULL    空    Select
2    SS (Row-S)    行级共享锁，其他对象只能查询这些数据行；是锁的类型中限制最少的锁，也是在表的并发程度中使用最多的    Select for update、Lock for update、Lock row share
3    SX (Row-X)    行级排它锁，在提交前不允许做DML操作    Insert、Update、 Delete、Lock row share
4    S(Share)    共享锁    Create index、Lock share
5    SSX (Share-Row-X)    共享行级排它锁    Lock share row exclusive
6    X(Exclusive)    排它锁    Alter table、Drop able、Drop index、Truncate table 、Lock exclusive
2）、TX锁
TX锁是Transaction Exclusive Lock行级排它锁，对一条记录加上TX锁后，其他用户不能修改、删除该记录
在数据行上只有X锁（排他锁）。在Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。当两个或多个会话在表的同一条记录上执行 DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。
当Oracle数据库发生TX锁等待时，如果不及时处理常常会引起Oracle数据库挂起，或导致死锁的发生，产生ORA-60的错误。这些现象都会对实际应用产生极大的危害，如长时间未响应，大量事务失败等。

2、    DDL锁
当 DDL命令发出时，Oracle会自动在被处理的对象上添加DDL锁定，从而防止对象被其他用户所修改。当DDL命令结束以后，则释放DDL锁定。DDL锁定不能显式的被请求，只有当对象结构被修改或者被引用时，才会在对象上添加DDL锁定。比如创建或者编译 存储过程时会对引用的对象添加DDL锁定。在创建视图时，也会对引用的表添加DDL锁定等。
在执行DDL命令之前，Oracle会自动添加一个隐式提交命令，然后执行具体的DDL命令，在DDL命令执行结束之后，还会自动添加一个隐式提交命令。实际上，Oracle在执行DDL命令时，都会将其转换为对数据字典表的DML操作。比如我们发出创建表的DDL命令时，Oracle会将表的名称插入数据字典表tab里，同时将表里的列名以及列的类型插入col 里，同 时将表里的列名以及列的类型插入col里，同时将表里的列名以及列的类型插入col表里等。因此，在DDL命令中需要添加隐式的提交命令，从而提交那些对数据字典表的DML操作。即使DDL命令失 败，它也会发出提交命令。DDL锁包括三种类型：排它的DDL锁定、共享的DDL锁定、可打破的解析锁定。

1）、排它的DDL锁定(Exclusive DDL Lock)
大部分的DDL操作都会在被操作的对象上添加排他的DDL锁定，从而防止在DDL命令执行期间，对象被其他用户所修改。当对象上添加了排他的DDL锁定以后，该对象上不能再添加任何其他的DDL锁定。如果是对表进行DDL命令，则其他进程也不能修改表里的数据。

2）、共享的DDL锁定(Shared DDL Lock )
用来保护被DDL的对象不被其他用户进程所更新，但是允许其他进程在对象上添加共享的DDL锁定。如果是对表进行DDL命令，则其他进程可以同时修改表里的数据。比如我们发出create view命令创建视图时，在视图的所引用的表(这种表也叫基表)上添加的就是共享的DDL命令。也就是说，在创建视图时，其他用户不能修改基表的结构，但是可以更新基表里的数据。

3）、可打破的解析锁定(Breakable Parsed Lock)
在shared pool里缓存的SQL游标或者PL/SQL程序代码都会获得引用对象上的解析锁定。如果我们发出DDL命令修改了某个对象的结构时，该对象相关的、位于 shared pool里的解析锁定就被打破，从而导致引用了该对象的SQL游标或者PL/SQL程序代码全都失效。下次再次执行相同的SQL语句时，需要重新解析，这 也就是所谓的SQL语句的reload了。可打破的解析锁定不会阻止其他的DDL锁定，如果发生与解析锁定相冲突的DDL锁定，则解析锁定也会被打破。

3、    内部锁和闩
内部锁保护内部数据库结构，如数据文件，对用户是不可见的。

四、悲观锁和乐观锁
当一个会话保持另一个会话正在请求的资源上的锁定时，就会发生阻塞。被阻塞的会话将一直挂起，直到持有锁的会话放弃锁定的资源为止。锁机制的适当应用保证了数据的完成性，应用不当会导致死锁，从而我们又将锁分为悲观锁与乐观锁。

1、悲观封锁
锁在用户修改之前就发挥作用，如：Select …for update（nowait) ，Select * from tab1 for update 用户发出这条命令之后，oracle将会对返回集中的数据建立行级封锁，以防止其他用户的修改。 如果此时其他用户对上面返回结果集的数据进行dml或ddl操作都会返回一个错误信息或发生阻塞。
1）对返回结果集进行update或delete操作会发生阻塞。
2）对该表进行ddl操作将会报：Ora-00054:resource busy and acquire with nowait specified.
此时Oracle已经对返回的结果集上加了排它的行级锁，所有其他对这些数据进行的修改或删除操作都必须等待这个锁的释放，产生的外在现象就是其他的操作将发生阻塞，这个操作commit或rollback.
同样这个查询的事务将会对该表加表级锁，不允许对该表的任何ddl操作，否则将会报出ora-00054错误：:resource busy and acquire with nowait specified.

2、乐观封锁
乐观的认为数据在select出来到update进去并提交的这段时间数据不会被更改。这里面有一种潜在的危险就是由于被选出的结果集并没有被锁定，是存在一种可能被其他用户更改的可能。因此Oracle仍然建议是用悲观封锁，因为这样会更安全。

注：死锁
死锁-deadlock ，当两个用户希望持有对方的资源时就会发生死锁. 即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚。
4个常见的dml语句会产生阻塞 INSERT ，UPDATE，DELETE，SELECT…FOR UPDATE 。
INSERT：Insert发生阻塞的唯一情况就是用户拥有一个建有主键约束的表。当2个的会话同时试图向表中插入相同的数据时，其中的一个会话将被阻塞，直到另外一个会话提交或会滚。一个会话提交时，另一个会话将收到主键重复的错误。回滚时，被阻塞的会话将继续执行。
UPDATE 和DELETE：当执行Update和delete操作的数据行已经被另外的会话锁定时，将会发生阻塞，直到另一个会话提交或会滚。
Select …for update：当一个用户发出select…for update的错作准备对返回的结果集进行修改时，如果结果集已经被另一个会话锁定，就是发生阻塞。需要等另一个会话结束之后才可继续执行。可以通过发出 select… for update nowait的语句来避免发生阻塞，如果资源已经被另一个会话锁定，则会返回以下错误：Ora-00054:resource busy and acquire with nowait specified.

 

版权声明：本文为CSDN博主「gangma2」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/mgxiaomage/article/details/86252602