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

  1. 云栖社区>
  2. 博客>
  3. 正文

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

余二五 2017-11-15 14:33:00 浏览1087
展开阅读全文

一、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】:

 

图中可以看出,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的安装模式,在任何操作系统上安装界面一致


 

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

http://blog.csdn.net/lqx0405/article/details/46443259

Binary 版本安装  

  1. 1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    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  
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 &  


源码包安装:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
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 &    
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 体系架构

 



2、MySQL体系架构

 

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

 

innodb存储引擎:

面向oltp(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeaable级别(可重复读)通过next- keylocking策略避免幻读、插入缓冲、二次写、自适应哈希索引、预读myisam存储引擎:不支持事务、表锁、全文索引、适合olap(在线分析处理),其中myd:放数据文件,myi:放索引文件ndb存储引擎:集群存储引擎,share nothing,可提高可用性memory存储引擎:数据存放在内存中,表锁,并发性能差,默认使用哈希索引

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

InnoDB体系结构如下


  

后台线程:

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

2、另外,将以修改的数据文件刷新到磁盘文件;

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

内存池:InnoDB有多个内存块,这些内存块组成了一个大的内存池。这些内存块包括有:缓冲池(innodb_buffer_pool)和日志缓冲(log_buffer)以及额外内存池(innodb_addtional_mem_pool_size)。

  

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

  

Mysql5.1 innodb_version 为1.0.7 

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

  

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

  

Mysql5.5 innodb_version 为1.1.8

  

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

 

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

每秒一次的操作包括:

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

2、合并插入缓冲(可能)

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

3、至多刷新100InnoDB的缓冲池的脏页到磁盘(可能)

这个,刷新100个脏页也不是每秒都在做

每10秒一次的操作包括:

1、刷新100个脏页到磁盘(可能)。

2、合并至多5个插入缓冲(总是)。

3、将日志缓冲刷新到磁盘(总是)。

4、删除无用的undo页(总是)。

5、产生一个检查点(checkpoing)。

四、INNODB 表空间

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

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


最小单位是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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 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的原理图,并且建议把这张图看到吐)



看红色框框的那部分 

每个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%'; 



常用设置的参数有:

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










本文转自 客居天涯 51CTO博客,原文链接:http://blog.51cto.com/tiany/1664591,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注