数据库学习之--Oracle 架构与MySQL架构对比

比眉伴天荒 2022-08-01 06:06 392阅读 0赞

**数据库学习之--Oracle 架构与MySQL架构对比**

**一、Oracle 、MySQL应用对比**

如果要说明三者的区别，首先就要从历史入手。

Oracle：中文译作甲骨文，这是一家传奇的公司，有一个传奇的大老板Larry Ellision。 Ellision 32岁还一事无成，读了三个大学，没得到一个学位文凭，换了十几家公司，老婆也离他而去。开始创业时只有1200美元，却使得Oracle公司连续12年销售额每年翻一番。

Oracle成立于1977年，早期的理论基础，反而来自于一篇IBM的论文《A Relational Model of Data for Large Shared Data Banks》【1】。作者CODD选取了关系代数的五种运算，并基于运算，架构了一种新型的数据存储模型。基于这种模型，Oracle成为了一个非常典型的关系数据库。因此也变的严谨、安全、高速、稳定，并且变的越来越庞大。

由于其诞生早、结构严谨、高可用、高性能等特点，使其在传统数据库应用中大杀四方，金融、通信、能源、运输、零售、制造等各个行业的大型公司基本都是用了Oracle，早些年的时候，世界500强几乎100%都是Oracle的用户。

MySQL ：MySQL的最初的核心思想，主要是开源、简便易用。其开发可追溯至1985年，而第一个内部发行版本诞生，已经是1995年。到1998年，MySQL已经可以支持10种操作系统了，其中就包括win平台。但依然问题多多，如不支持事务操作、子查询 、外键、存储过程和视图等功能。下图是一个截止至2006年的数据库市场占有率【2】：

![Image 1][]![Image 1][]![Center][]

图中可以看出，MySQL的爆发实际是在01、02年，尤其是02年发布的4.0 Beta版，正式选定InnoDB作为默认引擎，对事务处理能力及数据缓存能力有了极大的提高。同年4.1版开始支持子查询，至此MySQL终于蜕变成一个成熟的关系型数据库系统。05年的5.0版本又添加了存储过程、服务端游标、触发器、查询优化以及分布式事务功能，但同年被Oracle抄了后路，InnoDB被Oracle收编。08年，MySQL被Sun收购，09年，Oracle收购了Sun和MySQL。

由于MySQL的早期定位，其主要应用场景就是互联网开发。基本上，互联网的爆发成就了MySQL，LAMP架构风靡天下。而由于MySQL更多的的追求轻量、易用，以及早期的事物操作及复杂查询优化的缺失，在传统的数据库应用场景中，份额极少。

SQL Server：一提到SQL Server，大家一般都只想到Microsoft SQL Server，而非Sybase SQL Server。SQL Server最初是由Microsoft, Sybase and Ashton-Tate三家公司拦下的生意，是为IBM（又出现了）公司的OS/2操作系统开发的。随着OS/2项目的失败，大家也分道扬镳。 Microsoft自然转向自己的win操作系统，作为windows NT软件方案的一部分。而Sybase则专注于Linux/Unix方向的数据库开发。

MS SQL Server:主要面向中小企业。其最大的优势就是在于集成了MS公司的各类产品及资源，提供了强大的可视化界面、高度集成的管理开发工具，在快速构建商业智能（BI）方面颇有建树。 MS SQL Server是MS公司在软件集成方案中的重要一环，也为WIN系统在企业级应用中的普及做出了很大贡献。

典型应用场景

关于“大型数据库”，并没有严格的界定，有说以数据量为准，有说以恢复时间为准。如果综合数据库应用场景来说，大型数据库应用有以下特点：海量数据、高吞吐量；复杂逻辑、高计算量，以及高可用性。从这点上来说，Oracle，DB2就是比较典型的大型数据库，Sybase SQL Server也算是吧。下面分别说明之前三种数据库的应用场景。

**Oracle：**

Oracle的应用，主要在传统行业的数据化业务中，比如：银行、金融这样的对可用性、健壮性、安全性、实时性要求极高的业务；零售、物流这样对海量数据存储分析要求很高的业务。此外，高新制造业如芯片厂也基本都离不开Oracle；电商也有很多使用者，如京东（正在投奔Oracle）、阿里巴巴（计划去Oracle化）。而且由于Oracle对复杂计算、统计分析的强大支持，在互联网数据分析、数据挖掘方面的应用也越来越多。一个典型场景是这样的：

**某电信公司（非国内）下属某分公司的数据中心，有4台Oracle Sun的大型服务器用来安装Solaris操作系统和Oracle并提供计算服务，3台Sun Storage磁盘阵列来提供Oracle数据存储，12台IBM小型机，一台Oracle Exadata服务器，一台500T的磁带机用来存储历史数据，San连接内网，使用Tuxedo中间件来保证扩展性和无损迁移。建立支持高并发的Oracle数据库，通过OLTP系统用来对海量数据实时处理、操作，建立高运算量的Oracle数据仓库，用OLAP系统用来分析营收数据及提供自动报表。总预算约750万美金。**

**MySQL：**

MySQL基本是生于互联网，长于互联网。其应用实例也大都集中于互联网方向，MySQL的高并发存取能力并不比大型数据库差，同时价格便宜，安装使用简便快捷，深受广大互联网公司的喜爱。并且由于MySQL的开源特性，针对一些对数据库有特别要求的应用，可以通过修改代码来实现定向优化，例如SNS、LBS等互联网业务。一个典型的应用场景是：

**某互联网公司，成立之初，仅有PC数台，通过LAMP架构迅速搭起网站框架。随着业务扩张、市场扩大，迅速发展成为6台Dell小型机的中型网站。现在花了三年，终于成为垂直领域的最大网站，计划中的数据中心，拥有Dell机架式服务器40台，总预算20万美金。**

**MS SQL Server：**

Windows生态系统的产品，好处坏处都很分明。好处就是，高度集成化，微软也提供了整套的软件方案，基本上一套win系统装下来就齐活了。因此，不那么缺钱，但很缺IT人才的中小企业，会偏爱 MS SQL Server 。例如，自建ERP系统、商业智能、垂直领域零售商、餐饮、事业单位等等。

1996年，Bill Gates亲自出手，从Borland挖来了大牛Anders，搞定了C\#语言。微软02年搞定了http://ASP.NET。成熟的.NET、Silverlight技术，为 MS SQL Server赢得了部分互联网市场，其中就有曾经的全球最大社交网站MySpace，其发展历程很有代表性，可作为一个比较特别的例子【3】。其巅峰时有超过1.5亿的注册用户及每月400亿的访问量。应该算是MS SQL Server支撑的最大的数据应用了。

架构。其实要说执行的区别，主要还是架构的区别。正是架构导致了相同SQL在执行过程中的解释、优化、效率的差异。这里只做粗略说明，就不细说了：

Oracle： 数据文件包括：控制文件、数据文件、重做日志文件、参数文件、归档文件、密码文件。这是根据文件功能行进行划分，并且所有文件都是二进制编码后的文件，对数据库算法效率有极大的提高。由于Oracle文件管理的统一性，就可以对SQL执行过程中的解析和优化，指定统一的标准：

RBO（基于规则的优化器）、CBO（基于成本的优化器）

通过优化器的选择，以及无敌的HINT规则，给与了SQL优化极大的自由，对CPU、内存、IO资源进行方方面面的优化。

MySQL：最大的一个特色，就是自由选择存储引擎。每个表都是一个文件，都可以选择合适的存储引擎。常见的引擎有 InnoDB、 MyISAM、 NDBCluster等。但由于这种开放插件式的存储引擎，比如要求数据库与引擎之间的松耦合关系。从而导致文件的一致性大大降低。在SQL执行优化方面，也就有着一些不可避免的瓶颈。在多表关联、子查询优化、统计函数等方面是软肋，而且只支持极简单的HINT。

SQL Server ：数据架构基本是纵向划分，分为：Protocol Layer（协议层）， Relational Engine（关系引擎）， Storage Engine（存储引擎）， SQLOS。SQL执行过程就是逐层解析的过程，其中Relational Engine中的优化器，是基于成本的（CBO），其工作过程跟Oracle是非常相似的。在成本之上也是支持很丰富的HINT，包括：连接提示、查询提示、表提示

安装

Oracle采用OUI的安装模式，在任何操作系统上安装界面一致

![Image 1][]

![Center 1][]

MySQL安装可以选择源码包安装和二进制安装模式，操作系统不同安装方式也不同

[http://blog.csdn.net/lqx0405/article/details/46443259][http_blog.csdn.net_lqx0405_article_details_46443259]

**Binary 版本安装：**

shell> groupadd mysql   
    shell> useradd -r -g mysql mysql   
    shell> cd /usr/local   
    shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz   
    shell> ln -s full-path-to-mysql-VERSION-OS mysql   
    shell> cd mysql   
    shell> chown -R mysql .   
    shell> chgrp -R mysql .   
    shell> scripts/mysql_install_db --user=mysql   
    shell> chown -R root .   
    shell> chown -R mysql data   
    # Next command is optional   
    shell> cp support-files/my-medium.cnf /etc/my.cnf   
    shell> bin/mysqld_safe --user=mysql &

**源码包安装：**

shell> groupadd mysql   
    shell> useradd -r -g mysql mysql   
    # Beginning of source-build specific instructions   
    shell> tar zxvf mysql-VERSION.tar.gz   
    shell> cd mysql-VERSION   
    shell> cmake .   
    shell> make   
    shell> make install   
    # End of source-build specific instructions   
    # Postinstallation setup   
    shell> cd /usr/local/mysql   
    shell> chown -R mysql .   
    shell> chgrp -R mysql .   
    shell> scripts/mysql_install_db --user=mysql   
    shell> chown -R root .   
    shell> chown -R mysql data   
    # Next command is optional   
    shell> cp support-files/my-medium.cnf /etc/my.cnf   
    shell> bin/mysqld_safe --user=mysql &

**三、系统架构**

1、Oracle 体系架构

![Image 1][]![Center 2][]

![Center 3][]

![Image 1][]

2、MySQL体系架构

![Image 1][]![Center 4][]

由:连接池组件、管理服务和⼯工具组件、sql接口组件、查询分析器组件、优化器组件、缓冲组件、插件式存储引擎、物理文件组成。mysql是独有的插件式体系结构,各个存储引擎有自己的特点。

![Image 1][]![Center 5][]

**innodb存储引擎:**

面向OLTP(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeaable级别(可重复读)通过next- keylocking策略避免幻读、插入缓冲、二次写、自适应哈希索引、预读

**myisam存储引擎:**

不支持事务、表锁、全文索引、适合OLAP(在线分析处理),其中myd:放数据文件,myi:放索引文件ndb存储引擎:集群存储引擎,share nothing,可提高可用性memory存储引擎:数据存放在内存中,表锁,并发性能差,默认使用哈希索引.

[InnoDB 存储引擎的线程与内存池][InnoDB]

**InnoDB****体系结构如下**：

![Center 6][]![Image 1][]

**后台线程：**

**1、后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据；**

**2、另外，将已修改的数据文件刷新到磁盘文件；**

**3、同时，保证在数据库发生异常的情况下，InnoDB****能恢复到正常运行状态。**

内存池：InnoDB有多个内存块，这些内存块组成了一个大的内存池。这些内存块包括有：缓冲池（innodb\_buffer\_pool）和日志缓冲(log\_buffer)以及额外内存池（innodb\_addtional\_mem\_pool\_size）。

![Center 7][]![Image 1][]

后台线程：后台默认的线程有7个\-----4个IO thread,1个master thread,1个锁监控线程，1个错误监控线程，但是在mysql5.5以及innodb plugin版本中，默认IO线程均增加到了4个，读写线程分别用参数innodb\_read\_io\_thread和innodb\_write\_io\_thread来表示。

![Image 1][]  ![Center 8][]

Mysql5.1 innodb\_version 为1.0.7

可以看到有1个Insert buffer thread ，1个log thread，4个read thread，4个write thread。

![Center 9][]  ![Image 1][]

或者直接通过查看变量 ‘innodb\_%threads’来查看。

![Image 1][]![Center 10][]

Mysql5.5 innodb\_version 为1.1.8

![Image 1][]![Center 11][]

Mysql5.5 innodb的线程和Innodb plugin的情况类似。

![Image 1][] ![Center 12][]

Master thread在主循环中，分两大部分操作：每秒钟的操作和每10秒钟的操作：

每秒一次的操作包括：

**1、日志缓冲刷新到磁盘，即使这个事务还没有提交（总是），这点解释了为什么再大的事务****commit****时都很快！**

**2、合并插入缓冲（可能）**

**合并插入并不是每秒都发生，****InnoDB****会判断当前一秒内发生的****IO****次数是否小于****5****，如果是，则系统认为当前的****IO****压力很小，可以执行合并插入缓冲的操作。**

**3、至多刷新****100****个****InnoDB****的缓冲池的脏页到磁盘（可能）**

**这个，刷新****100****个脏页也不是每秒都在做**。

每10秒一次的操作包括:

1、刷新100个脏页到磁盘（可能）。

2、合并至多5个插入缓冲（总是）。

3、将日志缓冲刷新到磁盘（总是）。

4、删除无用的undo页（总是）。

5、产生一个检查点（checkpoint）。

**四、innodb  表空间**

Oracle的数据存储有表空间、段、区、块、数据文件；MySQL InnoDB的存储管理也类似，但是MySQL增加了一个共享表空间和独立表空间的概念；

完整的表空间，会被分成如下结构供给InnoDB使用。

![Image 1][]![Center 13][]

最小单位是page，每个page为16K；64个连续的page组成一个extent； 多个extent和page构成一个segment。Segment初始时InnoDB会为它分配32个pages，之后根据需要会将extent分配给 segment，单次最多会分配4个extents给segment。

1、共享表空间

某一个数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在data目录下。 默认的文件名为ibdata1  初始化为10M。

由于是默认的方式，就暂且理解为Mysql官方推荐的方式。相对而言所有的数据都在一个（或几个）文件中，比较利于管理，而且在操作的时候只需要open这一个（或几个）文件即可，相对来说代价很低。

2、独占表空间

每一个表都将会以独立的文件方式来进行存储，每一个表都有一个.frm表描述文件，还有一个.ibd文件。 其中这个文件包括了单独一个表的数据内容以及索引内容。

**五、重做日志（redo log）**

前言:之前一直弄不清楚mysql里面bin log和innodb log文件的区别，在脑子里面一直有个疑问binlog日志文件已经可以用来进行数据库的日志备份恢复了，怎么又多了一个redo log文件了。相信也有很多人有这个疑惑，现在把整个过程文档整理出来，希望对大家有所帮忙。

如果对Oracle很了解,那么在整个学习innoDB log的时候,可以把联机重做日志的那套理论套在学习innoDB log上面,几乎是一样的;

**1、innodb log的基础知识**

innodb log顾名思义：即innodb存储引擎产生的日志，也可以称为重做日志文件，默认在innodb\_data\_home\_dir下面有两个文件ib\_logfile0和ib\_logfile1。MySQL官方手册中将这两个文件叫文InnoDB存储引擎的日志文件；

查看日志文件位置：

\[root@rh55 ~\]\# cat /etc/my.cnf

\# Uncomment the following if you are using InnoDB tables

\#innodb\_data\_home\_dir = /usr/local/mysql/data

\#innodb\_data\_file\_path = ibdata1:10M:autoextend

\#innodb\_log\_group\_home\_dir = /usr/local/mysql/data

\# You can set ..\_buffer\_pool\_size up to 50 - 80 %

\# of RAM but beware of setting memory usage too high

\#innodb\_buffer\_pool\_size = 16M

\#innodb\_additional\_mem\_pool\_size = 2M

\# Set ..\_log\_file\_size to 25 % of buffer pool size

\#innodb\_log\_file\_size = 5M

\#innodb\_log\_buffer\_size = 8M

\#innodb\_flush\_log\_at\_trx\_commit = 1

\#innodb\_lock\_wait\_timeout = 50

\[root@rh55 ~\]\# cd /usr/local/mysql/data/

\[root@rh55 data\]\# ls -l

total 28732

\-rw-rw---- 1 mysql mysql 18874368 Jun 10 16:52 ibdata1

\-rw-rw---- 1 mysql mysql  5242880 Jun 10 16:52 ib\_logfile0

\-rw-rw---- 1 mysql mysql  5242880 Jun 10 16:52 ib\_logfile1

drwxr-xr-x 5 mysql mysql     4096 Jun 19 14:12 mysql

drwx------ 2 mysql mysql     4096 Jun 10 16:52 performance\_schema

drwxr-xr-x 2 mysql mysql     4096 Jun 10 16:18 test

innodb log的作用：当MySQL的实例和介质失败的时候，Innodb存储引擎就会使用innodb log文件进行恢复，保证数据库的完整性；

innodb log的写原理：（请容许我再放下InnoDB的原理图，并且建议把这张图看到吐）

![Center 14][]

![Image 1][]

看红色框框的那部分

每个InnDB存储引擎至少有1个重做日志文件组（group），每个文件组下至少有两个重做日志文件，默认的为ib\_logfile0、ib\_logfile1；

日志组中每个重做日志的大小一致，并循环使用；

InnoDB存储引擎先写重做日志文件，当文件满了的时候，会自动切换到日志文件2，当重做日志文件2也写满时，会再切换到重做日志文件1；

为了保证安全和性能，请设置每个重做日志文件设置镜像，并分配到不同的磁盘上面；

（发现以上特性跟ORACLE的连接重做日志文件简直是一样的）

**2、innodb log的相关参数**

运行脚本：show variables like 'innodb%log%'; 查看重做日志的相关参数

mysql> show variables like 'innodb%log%';

![Center 15][]

![Image 1][]

常用设置的参数有：

innodb\_mirrored\_log\_groups  镜像组的数量，默认为1，没有镜像；

innodb\_log\_group\_home\_dir  日志组所在的路径，默认为data的home目录；

innodb\_log\_files\_in\_group    日志组的数量，默认为2；

innodb\_log\_file\_size              日志组的大小,默认为5M；

innodb\_log\_buffer\_size        日志缓冲池的大小，图上为30M

**3、参数的相关调优**

3.1 重做日志文件的大小设置跟ORACLE一样，面临的问题是相似的。

当innodb log设置过大的时候，可能会导致系统崩溃后恢复需要很长的时间；

当innodb log设置过小的时候，当一个事务产生大量的日志的时候，需要多次切换重做日志文件，会产生类似如下的报警；

130702 12:53:13  InnoDB: ERROR: the age of the last checkpoint is 2863217109,

InnoDB: which exceeds the log group capacity 566222311.

InnoDB: If you are using big BLOB or TEXT rows, you must set the

InnoDB: combined size of log files at least 10 times bigger than the

4、重做日志与二进制日志的区别

4.1  记录的范围不同：二进制日志会记录MySQL的所有存储引擎的日志记录（包括InnoDB、MyISAM等)，

而InnoDB存储引擎的重做日志只会记录其本身的事务日志。

4.2 记录的内容不同：二进制日志文件记录的格式可以为STATEMENT或者ROW也可以是MIXED，其记录的都是关于一个事务的具体操作内容。

InnoDB存储引擎的重做日志文件记录的关于每个页的更改的物理情况。

4.3 写入的时间也不同：二进制日志文件是在事务提交前进行记录的，而在事务进行的过程中，不断有重做日志条目被写入到重做日志文件中。

**总结：理论指导实践，理论的知识虽然比较枯燥，学习跟建房子一样，看着很漂亮的高楼大厦，地下往往要有一个坚实的基础，基础打好了，高楼也能建得稳了。**

MySQL InnoDB存储引擎锁机制实验 http://www.linuxidc.com/Linux/2013-04/82240.htm

InnoDB存储引擎的启动、关闭与恢复 http://www.linuxidc.com/Linux/2013-06/86415.htm

MySQL InnoDB独立表空间的配置 http://www.linuxidc.com/Linux/2013-06/85760.htm

MySQL Server 层和 InnoDB 引擎层 体系结构图http://www.linuxidc.com/Linux/2013-05/84406.htm

InnoDB 死锁案例解析 http://www.linuxidc.com/Linux/2013-10/91713.htm

MySQL Innodb独立表空间的配置 http://www.linuxidc.com/Linux/2013-06/85760.htm

本文永久更新链接地址：[http://www.linuxidc.com/Linux/2015-01/112554.htm][http_www.linuxidc.com_Linux_2015-01_112554.htm]

[Image 1]: 
[Center]: /images/20220731/2e50524f4da84c3d9b994fa5eb8e3028.png
[Center 1]: /images/20220731/a6cef3f79f2247d88749837cf53a846a.png
[http_blog.csdn.net_lqx0405_article_details_46443259]: http://blog.csdn.net/lqx0405/article/details/46443259
[Center 2]: /images/20220731/9c89b560325f4e34866390080c1f9820.png
[Center 3]: /images/20220731/1bb103933eea47c792a7d69df6bfee0a.png
[Center 4]: /images/20220731/87ff060d14514e6494ee83ac21b3cb4f.png
[Center 5]: /images/20220731/123f7a453b5c4b078e2d575fd377d15b.png
[InnoDB]: http://www.cnblogs.com/benshan/archive/2013/01/11/2855926.html
[Center 6]: https://img-blog.csdn.net/20150620001005956?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbHF4MDQwNQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 7]: https://img-blog.csdn.net/20150620001100261?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbHF4MDQwNQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 8]: /images/20220731/7cdf00b0c2af44b682b58e3994af257b.png
[Center 9]: https://img-blog.csdn.net/20150620001151371?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbHF4MDQwNQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center
[Center 10]: /images/20220731/05793e34ebee4d9b8c86ddf9525918fa.png
[Center 11]: /images/20220731/e9a7ceceebc44fccb5eb63624c275f8f.png
[Center 12]: /images/20220731/b781fcdb630d4df996ce5d6fb0b87e9c.png
[Center 13]: /images/20220731/5906fb709d124f3bb12400857a9d9eb3.png
[Center 14]: /images/20220731/046e2c4864724893811bf5aad922f03d.png
[Center 15]: /images/20220731/69ae1bb6ea874a36b055561b65e7fe38.png
[http_www.linuxidc.com_Linux_2015-01_112554.htm]: http://www.linuxidc.com/Linux/2015-01/112554.htm