深入思考：oracle、innoDB和mylsam

平时用的多的是mysql，但是公司项目用的是oracle，面试的时候不免问到各种对比，这里做一个基于个人理解的总结

堆表和索引组织表

mysql的存储引擎是基于表的，存储引擎又可以称为表类型。而且mysql是基于插件式的存储引擎，其中innoDB是面向索引组织表的、mylsam是面向堆表的、memory是面向内存的…
而oracle没有存储引擎的说法，创建表时也可以为表指定表的类型，oracle的create table是默认使用堆表结构的，当然了，oracle还支持索引组织表、聚集表等，需要额外使用organization进行声明。

之前我一直先入为主，认为索引组织表是主要的表类型。其实堆表是使用最多的，mysql最早默认存储引擎mylsam也是基于堆表的。postgreSQL也是完全面向堆表的

堆组织表的最大特点就是无序，数据是以堆的方式管理的，增加数据的时候，会使用堆中找到第一个能够放下此数据的自由空间。从表中删除数据的时候，允许以后的新插入的数据或者修改完毕的数据复用这块空间。当进行全表搜索的时候，数据获取的顺序和“命中”顺序有关（物理层面），和插入顺序无关。
堆组织表的主键和唯一索引区别不大，和普通索引的区别也基本是在唯一约束上的区别。创建索引可以优化查询性能，避免全表扫描。

索引组织表一定会有一个主键，即使不声明也会隐式分配一个主键。虽然从数据库层面来看，数据行还是无序的，但是索引组织表创建后，数据文件和索引文件是集中存放的，因此至少会存在一个主键索引。当数据进行查询的时候，优化器会看到表中存在索引，于是数据返回的顺序是按照主键排好序的。

在数据库层面看来，数据就是无序存放的，表空间中数据都是堆放着的，但是优化器在生成执行计划的时候如果发现存在索引，就会走索引进行优化。索引是给优化器准备。

对于序的理解

关系型数据库大多是基于集合的，而基于集合就需要保证每条数据都需要有唯一标识。同时从数据库层面来看，数据行都是无序的。也就是说，数据库中的所有数据最原始存放在数据库中的状态就是无序的。集合数据库的索引的一个最大的作用就是将无序的数据以某种方式组织起来。

这应该也是为什么采用B+树这种使得“数据有序”的结构作为基础的索引，而哈希索引这种底层无序的数据结构一般用于非基础索引。

也就是说，集合数据库中的数据最原始的状态就是基于堆的、无序的，如果想要查询某条数据将无可避免的进行全表扫描。而主键本质上是一条具有唯一约束的索引，唯一：它声明了可以唯一标识一条记录的字段，索引：它将无序的数据行进行排序，而且支持随机查找（这个依赖底层数据结构的实现，如果底层仅仅是单向链表结构就不支持随机查找，每次只能每次从头开始搜索）

以上说了这么多，就是想说：集合数据库底层的数据最原始的状态是无序的、堆放式存储的。而引入基础索引的最大意义是将数据行按照某些字段组织起来。

这种堆表+索引的方式，索引文件和数据文件是独立存放的，索引文件中保存了某个字段在数据文件的物理地址rowid。（不存在什么回表的说法，因为是都是根据rowid去数据文件中查询数据行）

而另一种设计方式是将索引文件和数据文件集中存放，称为聚集索引，同时还可以额外创建索引，称为secondary index，一张表只能有一个唯一索引，因此secondary index存放的是一个逻辑地址rowid，其中就是目标数据行的主键值，需要进行一个回表操作。

索引组织表的应用场景:不适合频繁删改、完全由主键组成的表、频繁需要查询主键的表、对排序有需求

innoDB和myLASM

【1】首先，innoDB是面向索引组织表的，而mylsam是面向堆表的。因此innoDB至少存在一个聚集索引，其中数据文件和索引文件是集中存放的。再次基础上还可以接着建立secondary index，secondary index存放的是逻辑上的rowid即目标数据行的主键值，通过辅助索引需要进行回表操作。而mylsam甚至可以不建立索引和约束，它的主键索引和普通索引保存的都是物理的rowid，mylsam的数据文件和索引文件分开存放（myd和myi）。

innoDB如果没有显示定义主键，那么innoDB表中定义的第一个唯一索引将被视为聚集索引。如果没有唯一索引，那么innoDB也会选择一个6字节长的rowid作为隐式主键，但是不能先oracle那样被获取出来（innoDB的主键索引就是聚集索引，而myLASM所有的索引是非聚集索引，甚至可以不设主键和索引）

myLASM的缓冲池仅仅缓存索引数据，数据文件是由操作系统维护的。而innoDB的缓冲池需要同时维护索引和数据部分。

【2】基于堆组织表的myLASM使用rowid唯一标识一条行数据，可以不建立主键约束，单独的行可以通过rowid直接访问。而基于索引组织表的innoDB则使用主键（可以看作innoDB的rowid）唯一标识行数据，主键约束必须创建，单独的行可以先找到当前行的主键，然后走聚集索引的B+树拿到目标节点

【3】innoDB支持事务，依靠redo log 、 undo log 等，是cash-safe崩溃安全的，这些都是存储引擎层面的，而mylsam并不支持事务。但是这也提升了mylsam的性能。mylsam的写和读性能高于innoDB，其中mylsam是基于堆表的，而且索引和文件分开，适合频繁读写。而innoDB的索引和数据是放在一起的，频繁读写将导致维护索引的开销上升如页合并、也分裂开销。另一方面，innoDB查询需要维护的东西比mylsam多：
1、innoDB需要缓存数据和索引部分的呼叫，以及支持事务所需要的undo log、redo log缓存块等，而mylsam只需要缓存索引块（数据部分由操作系统维护）
2、innoDB支持事务，为了避免读写冲突需要维护MVCC所需要的变量、undo log版本文件等
3、innoDB将索引文件与数据文件保存在一起，拿到最终的数据行，可能需要回表。而mylsam则直接根据rowid从文件系统读出目标内容（需要涉及系统调用）
【4】innoDB支持外键，mylsam不支持。
【5】innoDB可以支持行锁，但是mylsam仅支持表锁。
(以下为个人理解)
innoDB如果命中主键索引，不需要回表，直接能够定义到目标的数据行，可以采用行锁，别的事务想要修改这一行就会被阻塞。而如果命中普通索引，采用的是间隙锁，相当于加了多个行锁，其他事务不能通过任何一个索引字段回表并修改数据行。如果没有命中索引，那么innoDB会退化为表锁。（没有索引，那么修改数据的时候需要先全部扫描找到目标的数据行，上表锁可以防止其他事务抢先修改而导致数据丢失。)
而myLASM由于是堆表，数据和索引分开存放，多个事务可以通过rowid去修改数据文件中的数据，因此采用表锁实现加锁。（个人理解：一方面myLASM不支持事务，无法保证原子性，因此锁住全表强制串行化来避免数据更新的丢失，另一方面数据与索引分类，无法精确锁住数据）

总结：innoDB更加关注完整性和容错性，mylsam更加关注性能，具体如何使用应该看场景

如果读操作非常多，并且不需要事务支持，则可以采用myLASM。存储大数据集的时候，趋向于选择innoDB，因为它支持事务处理和故障的恢复、能够保证有一定的完整性和容错的保证。频繁插入数据的时候，mylasm引擎的执行也比较快。

Myisam的读写锁调度是写优先，因此myisam不适合做写为主表的引擎，因为其中一个线程获取写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁（查询请求饥饿），从而造成永久阻塞

oracle和mysql

oracle是完全面向事务的，表的默认类型是堆组织表，还可以支持索引组织表。mysql是基于插件式的存储引擎，每个存储引擎和表是绑定的。其中innoDB面向索引组织表，支持事务。mylsam面向堆表，不支持事务。
mysql innoDB的事务默认是自动提交，oracle默认不自动提交，需要手动提交，需要写commit语句。oracle默认的隔离级别是读已提交，而innoDB默认的是可重复读。

Oracle支持行级锁，对资源锁定的力度要小很多，只是锁定sql需要的资源，并且加锁是在数据库中的数据行上，不依赖于索引。所以oracle对并发性的支持要好很多

Oracle保证提交的事务均可以恢复，因为Oracle把提交的sql操作线写入了在线联机日志文件中，保存到磁盘上，如果出现数据库或者主机异常重启，重启Oracle可以靠联机在线日志恢复客户提交的数据

oracle属于大型数据库，占用内存空间很大，对硬件要求很高，而且是付费的。

oracle没有提供自动增长类型，通过rowid唯一定位一条记录。而mysql可以通过autoIncrement声明主键为自动增长类型。
oracle的数据类型可以统一使用number来定义，字符型使用varchar2，mysql则提供了更加具体的类型，包括tinyint、smallint等，字符型使用char和varchar

oracle默认端口号1521，mysql的默认端口号是3306

另外分页查询上也有一定区别，mysql使用的limit/offset，而oracle需要先查询出一个rownum伪列，然后使用这个查询出来的伪列作为添加进行二次分页。

select *
from(
select t.*, rownum row_num from mytable t
)t2
where t2.row_num between 16 and 17

Rownum是一个伪列，查询出结果集后会从1开始构造，并加在每一列上。用于限制返回的总行数。查出这个伪列之后就相当于构造了一个临时表，然后包含一个伪列row_num，起个别名变成逻辑列。

为什么oracle要提供伪列实现分页而不是limit？个人理解：oracle支持多种表的类型，因此分页的时候提供了一种“上层、通用、表类型无关”的方式，即先查出伪列构成的逻辑列，然后使用逻辑列的字段进行范围查询来实现分页。

其实mysql也可以通过变量来实现伪列的效果。

select @rownum := @rownum + 1 as rownum , n.*
from nums n , (select @rownum := 0) t