oracle、mysql、sqlserver的对比&&数据库引擎的对比与选型&&InnoDB解决幻读

1. 所属公司：
MySQL是由瑞典MySQL AB公司开发，目前属于 Oracle 旗下产品；
Oracle是由美国ORACLE公司（甲骨文）开发的一组核心软件产品；
SqlServer是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的，目前主属Microsoft；

2. sql语句的灵活性：
MySQL对sql语句更灵活，比如：limit功能，insert可以一次插入多行数据，select某些管理数据可以不加from；
oracle对sql语句的感觉更加稳重传统一些；
SqlServer对sql语句那绝了，两个成语概括：脚踏实地，步步为营；

3. 保存数据的持久性：
MySQL是在数据库更新或者重启，则会丢失数据；
Oracle把提交的sql操作线写入了在线联机日志文件中，保持到了磁盘上，可以随时恢复；
SqlServer从2014之后，也拥有了完全持久和延迟持久特性；

4. 提交方式：
MySQL默认是自动提交（变量autocommit为ON）；
oracle默认不自动提交，需要用户手动提交（commit）；
SqlServer默认是自动提交，用户可手动停止（KILL）；

5. 对事务的支持：
MySQL在innodb存储引擎的行级锁的情况下才可支持事务；
Oracle则完全支持事务；
SqlServer在非自动提交时才可支持事务；

6. 对并发性的支持：
MySQL以表级锁（行级锁依赖于表索引）为主，如果一个session的加锁时间过长，会让其他session无法更新此表中的数据，即“悲观并发控制”；
Oracle使用行级锁，对资源锁定的粒度要小很多，并且不依赖索引，对并发性的支持要好很多，即“乐观并发控制”；
SqlServer在表、表的分页、索引键以及单独的数据行上支配共享锁、排它锁、以及更新锁，简单地说：读取时用共享锁，修改时用排它锁，更新锁则是共享锁和排他锁的一种混合；

8. 逻辑备份：
MySQL逻辑备份时要锁定数据，才能保证备份的数据是一致的，会影响正常业务；
Oracle逻辑备份时不锁定数据，且备份的数据是一致；
SqlServer逻辑备份时要锁定数据，才能保证备份的数据是一致的，会影响正常业务；

9. 复制备份：
MySQL复制服务器配置简单，但主库出问题时，丛库有可能丢失一定的数据；
Oracle既有推或拉式的传统数据复制，也有dataguard的双机或多机容灾机制，还有成熟的热备工具rman，主库出现问题时，可以自动切换备库到主库，但配置管理较复杂；
SqlServer的仅复制备份是独立于常规SQL Server备份序列的SQL Server备份。通常，进行备份会更改数据库并影响其后备份的还原方式。只用于：在不影响数据库总体备份和还原过程的情况下，为特殊目的而进行的备份。

10. 性能诊断：
MySQL的诊断调优方法较少，主要有慢查询日志；
Oracle有各种成熟的性能诊断调优工具，能实现很多自动分析、诊断功能。比如awr、addm、sqltrace、tkproof等；
SqlServer性能检测工具有sql server profiler，性能调优工具有数据库引擎优化顾问，简单好用还炫酷；

11. 权限与安全：
MySQL的用户与主机有关，更容易被仿冒主机及ip，有可乘之机，没什么意义；
Oracle的用户和root用户权限彼此分离，权限与安全概念比较传统，中规中矩；
SqlServer本机可用windows验证登录，远程则用SqlServer验证登录（即sa登录）；

12. 相关服务：
MySQL是轻量型数据库，并且免费开源，没有服务恢复数据；
Oracle是重量型数据库，收费，Oracle公司对Oracle数据库有任何服务；
SqlServer是重量型数据库，收费（与Java有合作），也有支持供应商；

总结

• MySQL优点：体积小、速度快、总体拥有成本低，开放源码，搭配“L(Linux)A(Apache)M(MySQL)P(PHP/Perl/Python)“或“LN(Nginx)MP”就可以建立起一个稳定、免费的网站系统，适合中小型网站。MySQL数据库被广泛地应用在Internet上的中小型网站中。MySQL数据库体积小、速度快、总体拥有成本低、开放源码。
• Oracle优点：使用方便、功能强大，可靠性好、安全性好、可移植性好、适应高吞吐量，适用于各类大、中、小、微机环境。ORACLE数据库适用于业务逻辑较复杂、数据量大的较大型项目开发。
• SqlServer优点：图形化用户界面，丰富的编程接口工具，与Windows NT完全集成，支持分布式的分区视图，适用于Win的Web技术的开发。SQL Server数据库的功能比较全面、效率高，适用于中型企业或单位的数据库平台。缺点是只能在Windows系统下运行。

数据库引擎的选择

1. MYISAM：支持3中存储方式：静态型，动态型，压缩型

• 优点：占用的空间小，存储的速度快
• 缺点：不支持事务和并发

如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率
它不支持外键,不支持事务

2. innoDB:

• 优点：提供事务的支持，回滚，崩溃修复佛如能力，多版本事务并发控制
• 缺点：读写效率较差，占用的数据库空间较大

如果要提供提交、回滚、崩溃恢复能力的事务安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

3. Memory：内存中对数据创建表，数据全部存储在内存

• 缺点：生命周期短
• 优点：读写速度非常快，对数据的安全性要求比较低的时候可以选择memory

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive，存历史数据,图书馆的资料等

数据库引擎的对比

• ISAM：ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
• MyISAM：MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。

MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。MyISAM主要特性有：

1、大文件（达到63位文件长度）在支持大文件的文件系统和操作系统上被支持

2、当把删除和更新及插入操作混合使用的时候，动态尺寸的行产生更少碎片。这要通过合并相邻被删除的块，以及若下一个块被删除，就扩展到下一块自动完成

3、每个MyISAM表最大索引数是64，这可以通过重新编译来改变。每个索引最大的列数是16

4、最大的键长度是1000字节，这也可以通过编译来改变，对于键长度超过250字节的情况，一个超过1024字节的键将被用上

5、BLOB和TEXT列可以被索引

6、NULL被允许在索引的列中，这个值占每个键的0~1个字节

7、所有数字键值以高字节优先被存储以允许一个更高的索引压缩

8、每个MyISAM类型的表都有一个AUTO_INCREMENT的内部列，当INSERT和UPDATE操作的时候该列被更新，同时AUTO_INCREMENT列将被刷新。所以说，MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快

9、可以把数据文件和索引文件放在不同目录

10、每个字符列可以有不同的字符集

11、有VARCHAR的表可以固定或动态记录长度

12、VARCHAR和CHAR列可以多达64KB

使用MyISAM引擎创建数据库，将产生3个文件。文件的名字以表名字开始，扩展名之处文件类型：frm文件存储表定义、数据文件的扩展名为.MYD（MYData）、索引文件的扩展名时.MYI（MYIndex）

• InnoDB：InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL+API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。

InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。InnoDB主要特性有：

1、InnoDB给MySQL提供了具有提交、回滚和崩溃恢复能力的事物安全（ACID兼容）存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供一个类似Oracle的非锁定读。这些功能增加了多用户部署和性能。在SQL查询中，可以自由地将InnoDB类型的表和其他MySQL的表类型混合起来，甚至在同一个查询中也可以混合

2、InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘的关系型数据库引擎锁不能匹敌的

3、InnoDB存储引擎完全与MySQL服务器整合，InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将它的表和索引在一个逻辑表空间中，表空间可以包含数个文件（或原始磁盘文件）。这与MyISAM表不同，比如在MyISAM表中每个表被存放在分离的文件中。InnoDB表可以是任何尺寸，即使在文件尺寸被限制为2GB的操作系统上

4、InnoDB支持外键完整性约束，存储表中的数据时，每张表的存储都按主键顺序存放，如果没有显示在表定义时指定主键，InnoDB会为每一行生成一个6字节的ROWID，并以此作为主键

5、InnoDB被用在众多需要高性能的大型数据库站点上

InnoDB不创建目录，使用InnoDB时，MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件，以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件

• MEMORY: MEMORY是MySQL中一类特殊的存储引擎。它使用存储在内存中的内容来创建表，而且数据全部放在内存中。这些特性与前面的两个很不同。每个基于MEMORY存储引擎的表实际对应一个磁盘文件。该文件的文件名与表名相同，类型为frm类型。该文件中只存储表的结构。而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。值得注意的是，服务器需要有足够的内存来维持MEMORY存储引擎的表的使用。如果不需要了，可以释放内存，甚至删除不需要的表。MEMORY默认使用哈希索引。速度比使用B型树索引快。当然如果你想用B型树索引，可以在创建索引时指定。注意，MEMORY用到的很少，因为它是把数据存到内存中，如果内存出现异常就会影响数据。如果重启或者关机，所有数据都会消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。MEMORY主要特性有：

1、MEMORY表的每个表可以有多达32个索引，每个索引16列，以及500字节的最大键长度

2、MEMORY存储引擎执行HASH和BTREE缩影

3、可以在一个MEMORY表中有非唯一键值

4、MEMORY表使用一个固定的记录长度格式

5、MEMORY不支持BLOB或TEXT列

6、MEMORY支持AUTO_INCREMENT列和对可包含NULL值的列的索引

7、MEMORY表在所由客户端之间共享（就像其他任何非TEMPORARY表）

8、MEMORY表内存被存储在内存中，内存是MEMORY表和服务器在查询处理时的空闲中，创建的内部表共享

9、当不再需要MEMORY表的内容时，要释放被MEMORY表使用的内存，应该执行DELETE FROM或TRUNCATE TABLE，或者删除整个表（使用DROP TABLE）

在实际工作中，选择一个合适的存储引擎是一个比较复杂的问题。每种存储引擎都有自己的优缺点，不能笼统地说谁比谁好。

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

主要是MyISAM和InnoDB两个引擎，主要的区别如下
1、innodb 支持事务，这一点非常重要，事务是一种高级的处理方式，如果在一些增删中如果那个出错可以换源回滚，myisam不支持事务
2、myisam适合查询以及插入为主的应用，Innodb适合频繁的修改以及设计到安全性能较高的应用
3、innodb适合外键，mysiam不适合外键
4、mysql中默认的mysiam引擎，如如果要是用innodb需要制定
5、innodb不支持富文本
6、InnoDB 中不保存表的行数，如 select count() from table 时，InnoDB；需要
扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即
可。注意的是，当 count()语句包含 where 条件时 MyISAM 也需要扫描整个表；
7、对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM
表中可以和其他字段一起建立联合索引；
8、清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重
建表；
9、InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where
user like ‘%lee%’

注意，同一个数据库也可以使用多种存储引擎的表。如果一个表要求比较高的事务处理，可以选择InnoDB。这个数据库中可以将查询要求比较高的表选择MyISAM存储。如果该数据库需要一个用于查询的临时表，可以选择MEMORY存储引擎。

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

并发事务

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

1、原子性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
(Undo Log实现)  

2、一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态（Redo Log实现）

3、隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”
环境执行

4、持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持
（binlog实现）

并发事务带来的问题

相对于串行处理来说，并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持更多用户的并发操作，但与此同时，会带来一下问题：

 - 脏读：
   一个事务正在对一条记录做修改，在这个事务并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”
   
 - 不可重复读：一个事务在读取某些数据已经发生了改变、或某些记录已经被删除了！这种现象叫做“不可重复读”。
 - 幻读： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”

注意：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

注意：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

上述出现的问题都是数据库读一致性的问题，可以通过事务的隔离机制来进行保证。

数据库的事务隔离越严格，并发副作用就越小，但付出的代价也就越大，因为事务隔离本质上就是使事务在一定程度上串行化，需要根据具体的业务需求来决定使用哪种隔离级别

InnoDB是如何在RR条件下解决幻读的

InnoDB在RR条件下解决幻读有两种手段：mvcc（多版本控制）+ 范围锁
1. MVCC
对于快照读来说，幻读的解决是依赖mvcc解决

每次开启事务后都会递增创建一个版本号（version），之后的增删查改都是基于这个版本号进行操作的

SELECT (version)
读取创建版本小于或等于当前事务版本号，并且删除版本为空或大于当前事务版本号的记录。这样可以保证在读取之前记录是存在的。version >= createVersion and version < deleteVersion

INSERT (createVersion)
将当前事务的版本号保存至行的创建版本号。 createVersion = version

UPDATE (createVersion)
新插入一行，并以当前事务的版本号作为新行的创建版本号，同时将原记录行的删除版本号设置为当前事务版本号。 新行createVersion = version，旧行deleteVersion = version

DELETE (deleteVersion)
将当前事务的版本号保存至行的删除版本号。 deleteVersion = version

2. 间隙锁
对于当前读则依赖于间隙锁解决

mysql的间隙所是基于索引的，对于唯一索引innode会把间隙所降级为行锁，非唯一索引的话就需要用到间隙锁（也叫范围锁）

* 事务一：select * from test where age = 3 for update对于number索引可以分为多个范围

(无穷小,1)(1,3)(3,3)(3,11)(11，无穷大)

这时候锁住的是(3,3)区间，对应的临界记录是(id=1,age=1)(id=31,age=11)

对于这范围内的数据都是被锁住的

* 事务二：insert into test(id, age) value(5, 3) //是会被阻塞

* 事务三：insert into test(id, age) value(25, 4) //也是会被阻塞

* 事务四：insert into test(id, age) value(35, 4) //也是会被阻塞

* 事务五：insert into test(id, age) value(22, 12) //插入成功 （因为12>11所以在锁区间外）

* 事务六：insert into test(id, age) value(71, 11) //插入成功 

（age，但是id71>31所以在锁区间外）

参考：

https://www.jianshu.com/p/4bb9f78b4f6d
https://www.cnblogs.com/wangjian941118/p/10103131.html
https://blog.csdn.net/qq_42989565/article/details/82468078
https://www.cnblogs.com/yblackd/p/12019818.html
https://segmentfault.com/a/1190000022050887?utm_source=tag-newest