Oracle与Mysql那点事

拼搏现实的明天。 2022-05-31 04:05 210阅读 0赞

Oracle数据库公认的强大、稳定、牛掰、贵，所以爱它的人爱死他，恨它的人抛弃它，互联网行业将MySql慢慢的发扬光大，随之引发的去O潮流已经有了后浪拍前浪的趋势。本期集中精力好好聊聊数据库那点事，以及Oracle与MySql的共性和区别。

多引擎：

Mysql与Oracle最大的不同是支持多种引擎类型，每种引擎对应着不同的特性，也就对应着不同的应用场景。拿最常用的MyISAM引擎与InnoDB引擎来比较下：

首先总体上这两个引擎关心的重点不一样，MyISAM强调的是性能，InnoDB强调的是事务。

InnoDB支持事务（事务默认自动提交），MyISAM不支持事务；

InnoDB读写的阻塞与事务级别有关，MyISAM读写堵塞

InnoDB支持行锁和表锁，MyISAM只支持表锁；

InnoDB支持外键，MyISAM不支持；

InnoDB不支持全文索引，MyISAM支持；（这条已经可以废弃了，新的InnoDB版本也开始支持了）

MyISAM内置了一个计数器，不带条件的select count(\*)MyISAM效率更快，InnoDB没有。

此外MySql还推出了Archive、Memory等其它类型，也很有特色。

MySql多引擎带来的优点显而易见，具体问题具体分析，每张表都可以根据场景去订制引擎；缺点就是对开发人员的要求变高，学习成本变大，一旦使用不好会出现无可预估的后果（例如用memory存储银行数据。。。。。。）。

所有引擎中只有InnoDB是支持事务的，这也是与Oracle数据库差异最小的引擎，本文后续的比较都是基于InnoDB基础上的。

视图：

Oracle中视图仅供查询不能通过其修改数据，通过view可以限制用户的操作，可以说view在oracle中除开定制化的查询“表”以外最大的功能就是对数据的保护。但是在Mysql中view的作用被颠覆了，mysql可以直接通过view修改数据！！所以在Mysql中，视图不再是安全的。

索引：

Oracle中使用的是B树索引，Mysql中使用的是B+树索引，都属于平衡树，两者的区别我曾经写过一篇博文做了详细介绍《[索引的类型与详解][Link 1]》。

Mysql的B+树索引又分为两类，聚集索引（我个人喜欢称它为主键索引，字面意思容易理解就是基于主键而创建的）和非聚集索引（也叫辅助索引）。

聚集索引，索引既数据，Mysql根据主键创建索引并且将每行的数据都维护到叶子节点中！好牛逼的设计，颠覆了我们对关系型数据库表存储的理解，在Oracle中是专门一块表空间存好数据，然后专门一块索引空间创建索引，索引中维护表空间里的指针，通过B树索引只能检索到表数据的指针；而Mysql的聚集索引通过B+树检索到的直接就是表记录，一棵聚集索引就是表记录的本身！当然这种设计随之带来的问题是Mysql的表必须有主键，即便创建者没有显示的创建主键，引擎会自动给一个隐藏的主键，因为有了主键才会有聚集索引，有了聚集索引才有了表结构和表数据。

非聚集索引，与聚集索引的唯一区别是叶子上挂载的不是行记录而是主键。假设现在我们的数据主键索引是3层的，一个唯一列的非聚集索引也是3层的，那么通过该非聚集索引找到最终记录要通过3+3次寻址。

外键：

Oracle中外键缺索引经常会引起表级别竞争甚至死锁，而且外键的索引是非必须的需要人工创建的；在Mysql中索性直接默认给外键都创建了隐藏的索引，数据库自动维护了。

事务：

Sql标准定义的四个隔离级别为：

Read UnCommitted（读到未提交的数据）

Read Committed（读到已提交的数据）

Repeatable Read（事务开始时不再允许修改）

Serializable（串行，最严格）

Oracle的默认事务级别是Read Committed， MySql的事务级别是RepeatableRead；而且Oracle的事务默认是非自动提交的，MySql的事务默认是自动提交的。

Mysql数据存储结构：表\-段\-区\-页\-行

![20180206172458205][]

默认一个区1M大小，1个区包含64个页，每个页64k，InnoDB一次申请4-5个区。

MySql复制的原理：

![20180206172531738][]

1.主库将所有操作都记录到binlog中。当复制开启时，主库的DUMP线程根据从库IO线程的请求将binlog中的内容发送到从库。

2.从库的IO线程接受到主库DUMP线程发送的binlog事件后，将其写到本地的relay-log。

3.从库的SQL线程重放relay-log中的事件

根据线程数简称一主两从，原理是利用磁盘的顺序写来压缩主从库交互时间。

InnoDB引擎的四大特性：

1插入缓冲（性能）

利用InsertBuffer磁块做欺骗，当插入非主键非唯一的索引时先插入该缓冲区，一定策略整合后再插入到真正的索引。原理还是顺序写效率高于离散写，同时减少了B+数的分裂和缩减次数。

2两次写（可靠性）

解决宕机时只写了一半的情况

![20180206172605406][]

解决策略是先写入共享表空间，由于是连续的磁盘所以这一步很快，再从共享表空间慢慢的往数据文件里去更新数据，这一步是离散的，相对较慢。一旦宕机可以从共享表空间来恢复。

3自适应哈希索引（性能）

智能自动的根据频率对join操作的条件创建哈希索引，只能是==操作的才可以。该性能对于DBA和研发人员来说是隐藏的。

4预读（性能）

读page的时候把extend其它部分读出来，通过预估来减少后面的查询和检索。

PS：预读根据业务而定，不见得是好事。。

总结Mysql与Oracle最大的分歧在引擎分类和B+树索引上，其它部分都是围绕这两点做文章，所以B树索引、B+树索引一定要掌握。

最后给2个关于索引的“小故事”：

1现在某表有个联合索引（a,b），一般单个b为条件时是不会走这个索引的，但是一种情况“count类的统计”

2现在某表有个非聚集索引orderid，那么这个索引作为唯一条件查询时就一定会有效么？

Select \* from orderdetails where ordereid>10and ordereid<100000;

这个就不走索引，因为非聚集索引orderid的范围太大了而且只能寻址到主键，因为这里是select \*要获得行内容，还要再通过主键的聚集索引再寻址一次才能找到行记录，所以不如直接通过聚集索引来的方便。

Select \* from orderdetails where ordereid>10and ordereid<100;

范围缩小后执行计划就不一样了。

2018-4-5补充：

在考虑使用mysql还是oracle时还需要考虑到云平台的支持程度。因为大部分云平台都是支持mysql的，如果你的产品未来有上云的打算，那么请果断在起步阶段就选用mysql，否则上云时需要做一次数据割接，对生产环境做数据割接将会是一个很痛苦而且高风险的操作。

[Link 1]: http://blog.csdn.net/yejingtao703/article/details/78818876
[20180206172458205]: /images/20220531/c846d59329ea4554900e312dc77dc084.png
[20180206172531738]: /images/20220531/d973964e14f041e086deaff4039c48cf.png
[20180206172605406]: /images/20220531/3766d25b1e43458c86e14201f05a3a1a.png